Fix filepath when migrating a site...

Hello,

I'm working in a company and we have to migrate sites from one env to another.
Sometimes (often!), paths from one server to another are changing and if some changes in the databases are not done, it's impossible to get the site running.

I made a Drush Custom Command file (MCC = dummy name) to help us to migrate, it contains some functions that I'm sure will be useful for us and I hope people in the community will improve it.
The most important function is the drush_mccmigratedb().

Here it is:

/**
 * Implementation of hook_drush_command().
 *
 * In this hook, you specify which commands your
 * drush module makes available, what it does and
 * description.
 *
 * Notice how this structure closely resembles how
 * you define menu hooks.
 *
 * See `drush topic docs-commands` for a list of recognized keys.
 *
 * @return
 *   An associative array describing your command(s).
 */
function mcc_drush_command() {
  $items = array();

  $items['mccmigratedb'] = array(
      'description' => "Migrate DB",
      'arguments' => array(
      ),
      'options' => array(
      ),
      'examples' => array(
          'drush mccmigratedb',
      ),
      'aliases' => array('smmdb'),
      'bootstrap' => DRUSH_BOOTSTRAP_DRUPAL_FULL, // No bootstrap at all.
  );

  $items['mccmrr'] = array(
      'description' => "Menu Router rebuild",
      'arguments' => array(
      ),
      'options' => array(
      ),
      'examples' => array(
          'drush mccmrr',
      ),
      'aliases' => array('smmrr'),
      'bootstrap' => DRUSH_BOOTSTRAP_DRUPAL_FULL, // No bootstrap at all.
  );

  $items['mccresetpassword'] = array(
      'description' => "reset password of a given user",
      'arguments' => array(
          'user' => 'The username to reset password. The password will be the username.'
      ),
      'options' => array(
      ),
      'examples' => array(
          'drush mccresetpassword admin',
      ),
      'aliases' => array('smrpw'),
      'bootstrap' => DRUSH_BOOTSTRAP_DRUPAL_FULL, // No bootstrap at all.
  );

  return $items;
}

/**
 * Implementation of hook_drush_help().
 *
 * This function is called whenever a drush user calls
 * 'drush help <name-of-your-command>'. This hook is optional. If a command
 * does not implement this hook, the command's description is used instead.
 *
 * This hook is also used to look up help metadata, such as help
 * category title and summary.  See the comments below for a description.
 *
 * @param
 *   A string with the help section (prepend with 'drush:')
 *
 * @return
 *   A string with the help text for your command.
 */
function mcc_drush_help($section) {
  switch ($section) {
    case 'drush:mccmigratedb':
      return dt("This command will migrate tables 'files' and 'system' from an old db containing old filesystem to a new one.");
    // The 'title' meta item is used to name a group of
    // commands in `drush help`.  If a title is not defined,
    // the default is "All commands in ___", with the
    // specific name of the commandfile (e.g. sandwich).
    // Command files with less than four commands will
    // be placed in the "Other commands" section, _unless_
    // they define a title.  It is therefore preferable
    // to not define a title unless the file defines a lot
    // of commands.
    case 'meta:mccmigratedb:title':
      return dt("Migrate Database");
    // The 'summary' meta item is displayed in `drush help --filter`,
    // and is used to give a general idea what the commands in this
    // command file do, and what they have in common.
    case 'meta:mccmigratedb:summary':
      return dt("This command will migrate tables 'files' and 'system' from an old db containing old filesystem to a new one.");
    case 'meta:mccresetpassword:summary':
      return dt("This command will reset the password of a user to it's own username.");
  }
}

/**
 * Convert absolute paths in the DB to the correct absolute paths for this installation 
 *
 * @see drush_invoke()
 * @see drush.api.php
 */
function drush_mccmigratedb() {

  $tables_to_look   = array("files.filepath",
                            "system.filename");
  $tables_to_modify = array("node_revisions.body",
                            "node_revisions.teaser");

  // Find the good Drupal path.
  $new_path = conf_path();

  drush_log(dt("-------------------------------------------------------"), 'notice');
  drush_log(dt("Running automatic filepath detection and replacement..."), 'notice');
  drush_log(dt("-------------------------------------------------------"), 'notice');
  drush_log(dt("Drupal instance path: !new", array('!new' => $new_path)) , 'notice');

  // Find potential bad paths in the database.
  $paths = array();
  foreach ($tables_to_look as $table) {
    list($table, $field) = explode(".", $table);

    //this query gets all bad paths (e.g. sites/www.webmcc.be) to let them be converted (e.g. to sites/ldapp001a.dummy.be.re_web)
    $sql = "SELECT SUBSTRING_INDEX(%s,'/',2) badpath FROM {" . $table . "} WHERE %s REGEXP 'sites/' AND %s NOT REGEXP 'sites/all' AND %s NOT REGEXP '%s' GROUP BY badpath";
    $badpaths = db_query($sql, array($field, $field, $field, $field, $new_path));

    while ($path = db_fetch_object($badpaths)) {
      if (!file_exists($path->badpath)) {
        $paths[] = $path->badpath;
      }
    }
  }

  $bad_pathcount = _check_for_bad_filepaths($tables_to_look);
  drush_log(dt("There are !count inconsistent filepath(s) in the database.", array('!count' => $bad_pathcount)), 'warning');
  
  if ($bad_pathcount != 0) {
    $paths = array_unique($paths); //for each query, these paths are ensured unique by the "GROUP BY badpath" statement, but multiple queries can give doubles as result
    drush_log(dt("Filepaths detection: !pathcount filepath(s) to be changed.", array('!pathcount' => count($paths))), 'success');

    $choice = drush_confirm("Proceed ?");

    if ($choice) {
      foreach (array_merge($tables_to_modify, $tables_to_look) as $table) {
        list($table, $field) = explode(".", $table);
        if (db_table_exists($table)) {
          drush_log(dt("Updating table [!table] and field [!field].", array('!table' => $table, '!field' => $field)), 'success');
          foreach ($paths as $old_filepath) {
            drush_log(dt(" Updating !old to !new.", array('!old' => $old_filepath, '!new' => $new_path)), 'success');
            $sql = "UPDATE {" . $table . "} SET %s = REPLACE(%s, '%s', '%s')";
            db_query($sql, array($field, $field, $old_filepath, $new_path));
          }
        }
      }
    }
  }
  
  $bad_pathcount = _check_for_bad_filepaths($tables_to_look);
  drush_log(dt("After processing, there are !count inconsistent filepath(s) in the database.", array('!count' => $bad_pathcount)), 'success');
  
  variable_set('file_directory_path', $new_path . '/files');
}

function _check_for_bad_filepaths($tables) {
  $bad_pathcount = 0;
  $new_path = conf_path();
  foreach ($tables as $table) {
    list($table, $field) = explode(".", $table);
    $sql = "SELECT %s filepath FROM {" . $table . "} WHERE %s REGEXP 'sites/' AND %s NOT REGEXP 'sites/all' AND %s NOT REGEXP '%s'";
    $filepaths = db_query($sql, array($field, $field, $field, $field, $new_path));
    while ($filepath = db_fetch_object($filepaths)) {
      if (!file_exists($filepath->filepath)) {
        $bad_pathcount++;
        drush_log(dt("Filepath !old doesn't exists.", array('!old' => $filepath->filepath)), 'notice');
      }
    }
  }
  
  return $bad_pathcount;
  
}


function drush_mccresetpassword($user_id = '1') {

  if (is_numeric($user_id)) {
    $username = db_result(db_query("SELECT name FROM {users} WHERE uid = %d", array($user_id)));
  } else {
    $username = (string) $user_id;
  }

  if (is_string($username)) {
    $choice = drush_confirm("Do you want to set the password of $username to $username ?");
    if ($choice) {
      db_query("UPDATE {users} SET pass = MD5('%s'), language='', theme='' WHERE name = '%s'", array($username, $username));
      drush_log(dt("Updating user $username"), 'success');
      drush_log(dt(" You can now login using login: $username and password: $username"));
    }
  }
}

function drush_mccmrr() {
  menu_router_build(TRUE);
  drush_log(dt('Rebuilding menu_router table...','success'));
}