<?php
// $Id: drush_sql.module,v 1.15 2008-11-01 04:31:53 weitzman Exp $

/**
 * @file
 *  The drush SQL commands
 */

/**
 * Implementation of hook_help().
 */
function drush_sql_help($section) {
  switch ($section) {
    case 'drush:sql dump':
      return t('Prints the whole database to STDOUT or save to a file by passing --result-file option. The file should be relative to Drupal root.');;
    case 'drush:sql query':
      return t('Usage: drush [options] sql query <query> <skip>...\n\n<query> is a SQL statement, which can alternatively be passed via STDIN. Any additional arguments are passed to the mysql command directly.');
    case 'drush:sql load':
      return t('Usage: drush [options] sql load <source_dir> <target_dir> <skip>. <source_dir> and <target_dir> are names of directories under \'sites\'. These determine from where and to where you want your database copied. Optional: specify \'common\' for <skip> if you wish to omit disposable tables like cache*, search*, etc. Your skip lists are specified in your drushrc.php file. Any additional arguments are passed to the mysqldump command directly.');
  }
}

/**
 * Implementation of hook_drush_command().
 */
function drush_sql_drush_command() {
  $items['sql url'] = array(
    'callback' => 'drush_sql_url',
    'description' => 'Print database connection details.'
  );
  $items['sql version server'] = array(
    'callback' => 'drush_sql_version_server',
    'description' => 'Print database server version number.'
  );
  $items['sql version client'] = array(
    'callback' => 'drush_sql_version_client',
    'description' => 'Print database client library version number.'
  );
  $items['sql dump'] = array(
    'callback' => 'drush_sql_dump',
    'description' => 'Exports the Drupal DB as SQL using mysqldump or pg_dump.'
  );
  $items['sql query'] = array(
    'callback' => 'drush_sql_query',
    'description' => 'Execute a query against the site database.'
  );
  $items['sql load'] = array(
    'callback' => 'drush_sql_load',
    'description' => 'Copy source database to target database.'
  );
  return $items;
}


/**
 * Command callback. Displays the Drupal site's database connection string.
 */
function drush_sql_url() {
  $drush_connection = Database::getConnectionInfo();
  print_r($drush_connection['default']);
}

/**
 * Command callback. Displays the MySQL or PostgreSQL server version number.
 */
function drush_sql_version_server() {
  switch (db_driver()) {
    case 'mysql':
    case 'mysqli':
      drush_print(mysql_get_server_info());
      break;
    case 'pgsql':
      // NOTE: apparently the server version is only available if PHP was
      // compiled with PostgreSQL 7.4 or later, so we'll fall back to
      // displaying the client version if that's the case.
      $info = pg_version();
      drush_print(isset($info['server_version']) ? $info['server_version'] : $info['client']);
      break;
    default:
      drush_die(_drush_sql_get_invalid_url_msg());
  }
}

/**
 * Command callback. Displays the MySQL or PostgreSQL client version number.
 */
function drush_sql_version_client() {
  switch (db_driver()) {
    case 'mysql':
    case 'mysqli':
      drush_print(mysql_get_client_info());
      break;
    case 'pgsql':
      $info = pg_version();
      drush_print($info['client']);
      break;
    default:
      drush_die(_drush_sql_get_invalid_url_msg());
  }
}

/**
 * Command callback. Outputs the entire Drupal database in SQL format using mysqldump or
 * pg_dump.
 *
 * @param db_url
 * @param execute
 * @param skip
 *    A key in the sql_skip array which specifies a list of tables to ignore when migrating.
 */
function drush_sql_dump($db_url = NULL, $execute = TRUE, $skip = NULL) {
  if (is_null($db_url)) {
    $db_url = $GLOBALS['db_url'];
  }

  switch (db_driver()) {
    case 'mysql':
    case 'mysqli':
      $exec = 'mysqldump' . (DRUSH_VERBOSE ? ' -v' : '');
      if ($file = drush_get_option('result-file')) {
        $exec .= ' --result-file '. $file;
      }
      $exec .= ' --opt -Q' . _drush_sql_get_credentials($db_url);
      break;
    case 'pgsql':
      drush_die(t('Sorry, pg_dump support not implemented yet.')); // TODO: pg_dump command.
      break;
    default:
      drush_die(_drush_sql_get_invalid_url_msg($db_url));
  }

  // Get any arguments to be passed through to the SQL client program.
  if (func_num_args() > 3 && ($args = func_get_args())) {
    $args = ' ' . implode(' ', array_slice($args, 3)); // skip standard arguments
  }
  
  // Skip large core tables if instructed.  Used by 'sql load' command.
    if ($skip && strpos($exec, '--ignore-table') === FALSE) {
      $all_skip_tables = (array)drush_get_option('skip-tables');
      $skip_tables = $all_skip_tables[$skip];
      $database = _drush_sql_get_database($db_url);
      foreach ($skip_tables as $table) {
        $ignores[] = "--ignore-table=$database.$table";
      }
      $exec .= ' '. implode(' ', $ignores);
    }
  
  if (!$execute) {
    return $exec;
  }
  
  if (DRUSH_VERBOSE) {
    drush_print(t('Executing: !cmd', array('!cmd' => $exec)));
  }

  return drush_op('system', $exec) !== FALSE; 
}

/**
 * Command callback. Executes the given SQL query on the Drupal database.
 * Reads from STDIN if no query is provided.
 */
function drush_sql_query($query = NULL) {
  // Get any arguments to be passed through to the SQL client program.
  if (func_num_args() > 1 && ($args = func_get_args())) {
    $args = ' ' . implode(' ', array_slice($args, 1)); // skip query argument
  }
  
  return drush_sql_query_execute($query, NULL, $args);
}

function drush_sql_query_execute($query, $db_url = NULL, $args = NULL) {
  // FIXME: currently we require the query due to an apparent inability to
  // run the mysql child process interactively. Need to look more into
  // proc_open() to see if it could be used for interactive execution.
  if (empty($query)) {
    if ($fstat = fstat(STDIN)) {
      $query = fread(STDIN,$fstat[size]);
    }
    else {
      drush_die(t('No SQL query given as argument.'));
    }
  }
  
  // Save query to a file. we will redirect it in.
  $filename = time(). '.sql';
  if ($file = file_save_data($query, $filename)) {
    switch (db_driver()) {
      case 'mysql':
      case 'mysqli':
        $exec = 'mysql' . (DRUSH_VERBOSE ? ' -v' : '');
        $exec .= _drush_sql_get_credentials($db_url);
        $exec .= " < ./$file";
        break;
      case 'pgsql':
        drush_die(t('Sorry, psql support not implemented yet.')); // TODO: psql command.
        break;
      default:
        drush_die(_drush_sql_get_invalid_url_msg($db_url));
    }
    $exec .= $args;

    if (DRUSH_VERBOSE) {
      drush_print(t('Executing: !cmd', array('!cmd' => $exec)));
    }
    $return = drush_op('system', $exec) !== FALSE;
    unlink($file);  
    return $return;
  }
}

/**
 * Copy an entire database to another database. For example, migrate from Production to dev
 * or dev to staging.
 * 
 * conf_path() uses a static var so we can't use it to figure out paths based on URIs.
 *
 * @param source
 *   The name of a subdirectory under sites. Its settings.php specifies the database which should be migrated.
 * @param target
 *    The name of a subdirectory under sites. Its settings.php specifies the database which whose tables will 
 *    be replaced with the contents of `source`.
 * @param skip
 *    A key in the sql_skip array which specifies a list of tables to ignore when migrating.
 * 
 **/
function drush_sql_load($source, $target, $skip = NULL) {
  // Don't use require_once - we need to ovewrite db_url under some circumstances.
  require "./sites/$source/settings.php";
  $db_url_source = is_array($db_url) ? $db_url['default'] : $db_url;
  require "./sites/$target/settings.php";
  $db_url_target = is_array($db_url) ? $db_url['default'] : $db_url;
  
  // Prompt for confirmation. This is destructive.
  if (!DRUSH_SIMULATE) {
    drush_print(t("You will destroy data from !target and replace with data from !source.", array('!source' => $db_url_source, '!target' => $db_url_target)));
    // TODO: actually make the backup if desired.
    drush_print(t("You might want to make a backup first, using sql_dump command.\n"));
    if (!drush_confirm(t('Do you really want to continue?'))) {
      drush_die('Aborting.');
    }
  }
  
  // Get command to export from source.
  if ($retrieve = drush_sql_dump($db_url_source, FALSE, $skip)) {
    // Get any arguments to be passed through to the mysqldump.
    $args = array();
    if (func_num_args() > 3 && ($args = func_get_args())) {
      $retrieve .= ' ' . implode(' ', array_slice($args, 3)); // skip standard arguments
    }
    
    // Build import command for target.
    $send = 'mysql' . (DRUSH_VERBOSE ? ' -v' : '');
    $send .= _drush_sql_get_credentials($db_url_target);
    
    // Build pipe command and redirect output to the bit bucket.
    // Redirection prevented loads of SQL from writing to screen.
    $exec = "$retrieve | $send > /dev/null 2>&1";

    if (DRUSH_VERBOSE) {
      drush_print(t('Executing: !cmd', array('!cmd' => $exec)));
    }
    
    $return = drush_op('system', $exec) !== FALSE;
  }
  return $return;
}

//////////////////////////////////////////////////////////////////////////////
// SQL SERVICE HELPERS

// this sets some globals so please beware.
function drush_sql_get_path($uri) {
  $drupal_base_url = parse_url($uri);
  $_SERVER['HTTP_HOST'] = $drupal_base_url['host'];
  $_SERVER['PHP_SELF'] = $drupal_base_url['path'].'/index.php';
  return conf_path();
}

function _drush_sql_get_database($db_url = NULL) {
  if (is_null($db_url)) {
    $db_url = $GLOBALS['db_url'];
  }
  $url = (object)parse_url($db_url);
  return substr($url->path, 1);
}

function _drush_sql_get_credentials($db_url = NULL) {
  if (is_null($db_url)) {
    $db_url = $GLOBALS['db_url'];
  }

  // NOTE: this regex could also parse the connection string URL:
  //   ^([\w]+)://([\w\d_]+):([^@]*)@([\w\d\.\-]*)/([\w\d_]+)$

  $url = (object)parse_url($db_url);
  $url->user = urldecode($url->user);
  $url->pass = urldecode($url->pass);
  $url->host = urldecode($url->host);
  $url->path = substr(urldecode($url->path), 1); // skip leading '/' character

  switch ($url->scheme) {
    case 'mysql':
    case 'mysqli':
      return ' -h' . $url->host .
        (!isset($url->port) ? '' : ' -P' . $url->port) .
        ' -u' . $url->user .
        (empty($url->pass) ? '' : ' -p' . $url->pass) . ' ' . $url->path;
    case 'pgsql':
      drush_die(t('Sorry, psql support not implemented yet.')); // TODO: psql credentials.
      return;
    default:
      drush_die(_drush_sql_get_invalid_url_msg());
  }
}

function _drush_sql_get_invalid_url_msg($db_url = NULL) {
  if (is_null($db_url)) {
    $db_url = $GLOBALS['db_url'];
  }
  return t('Unable to parse DB connection string: `%url\'.', array('%url' => $db_url));
}