migration.module

<?php // $Id$

/**
 * @file
 * The Migration module, which helps keep databases in sync.
 *
 * The Migration module is intended to help keep development, staging and
 * production databases in sync. This is done by collecting queries and
 * formatting them as a hook_update_N function for running on the target
 * site.
 */

/**
 * Implementation of hook_perm().
 */
function migration_perm() {
  return array('administer migration', 'view query monitor start/stop link');
}

/**
 * Implementation of hook_menu().
 */
function migration_menu() {
  $items = array();

  $items['admin/settings/migration'] = array(
    'title' => 'Migration',
    'description' => 'Settings for the Migration module',
    'page callback' => 'drupal_get_form',
    'page arguments' => array('migration_settings_form'),
    'access arguments' => array('administer migration'),
    'file' => 'migration.admin.inc',
  );

  $items['admin/settings/migration/settings'] = array(
    'title' => 'Settings',
    'type' => MENU_DEFAULT_LOCAL_TASK,
    'weight' => 0,
  );

  $items['admin/settings/migration/tables'] = array(
    'title' => 'Monitored tables',
    'type' => MENU_LOCAL_TASK,
    'page callback' => 'drupal_get_form',
    'page arguments' => array('migration_tables_list_form'),
    'access arguments' => array('administer migration'),
    'file' => 'migration.admin.inc',
    'weight' => 1,
  );

  $items['admin/settings/migration/variables'] = array(
    'title' => 'Filter variables',
    'type' => MENU_LOCAL_TASK,
    'page callback' => 'drupal_get_form',
    'page arguments' => array('migration_variables_list_form'),
    'access arguments' => array('administer migration'),
    'file' => 'migration.admin.inc',
    'weight' => 1,
  );

  $items['migration/start'] = array(
    'title' => 'Start monitoring',
    'type' => MENU_CALLBACK,
    'page callback' => 'migration_monitor_control',
    'page arguments' => array('start'),
    'access arguments' => array('administer migration'),
    'file' => 'migration.admin.inc',
  );

  $items['migration/stop'] = array(
    'title' => 'Stop monitoring',
    'type' => MENU_CALLBACK,
    'page callback' => 'migration_monitor_control',
    'page arguments' => array('stop'),
    'access arguments' => array('administer migration'),
    'file' => 'migration.admin.inc',
  );

  return $items;
}

/**
 * Implementation of hook_exit().
 *
 * Collect all queries and stash them in a query file.
 */
function migration_exit($destination = NULL) {
  global $queries;

  if (variable_get('dev_query', FALSE) && is_array($queries)) {
    $page_queries = migration_global_queries_processor($queries);
    $valid_queries = migration_filter_queries($page_queries);

    foreach ($valid_queries as $query_struct) {
      $querystrings[] = $query_struct['query'];

      // TODO: for debugging - get rid of this later
      //$funcquery[$query_struct['function'] . $query_struct['query']] = $query_struct['function'] . $query_struct['query'];
      //$functions[$query_struct['function']] = $query_struct['function'];
      //$usernames[$query_struct['username']] = $query_struct['username'];
    }

    // TODO: Consider using $_GLOBAL['migration_query_monitor'].
    if (variable_get('migration_query_monitor', FALSE)) {
      // Log the sql strings to disk.
      if (is_array($querystrings)) {
        if (migration_file_append_data(implode(";\n", $querystrings) . ";", 'queries.sql')) {
          $output .= t('!querystrings Queries added to file: !file', array('!file' => realpath(migration_get_filename('queries.sql')), '!querystrings' => count($querystrings)));
        }
        else {
          $output .= t('Error saving sql file.');
        }
      }
    } 
  
    if (variable_get('migration_hook_update_pageviews', FALSE)){
      $output .= migration_format_update_hook($queries);
    }
    drupal_set_message($output, 'status');
  }
}

/**
 * Get the relative path to the query file.
 *
 * @param $file
 *   The name of the file for which to get the path.
 * @return
 *   The path to the query file.
 */
function migration_get_filename($file) {
  $filename = variable_get('migration_directory_path', file_directory_path() . '/migration') . '/' . $file;
  return $filename;
}

/**
 * Extracts the queries, user names, and functions called.
 *
 * @return
 *   An array containing the query data.
 */
function migration_global_queries_processor($queries) {
  foreach ($queries as $query) {

    // Regex explain: Using % instead of / for readability. The 's' option
    // extends the match past single lines and query is sometimes multiline.
    preg_match("%(?<function>.*)\n\/\* (?<username>.*) : .*\*\/ (?<query>.*)%s", $query[0], $match);

    $output[] = array(
      'function' => $match['function'],
      'username' => $match['username'],
      'query'    => str_replace("\n", ' ', trim($match['query'])), // Strip whitespace.
    );
  }

  return $output;
}

/**
 * Appends collected queries to the specified file.
 *
 * @param $input
 *   The query data to append to the file.
 * @param $filename
 *   The name of the file to append the data.
 * @return
 *   A string containing the resulting filename or 0 on error.
 */
function migration_file_append_data($input, $filename) {
  $fullpath = getcwd() . '/' . variable_get('migration_directory_path', file_directory_path() . '/migration');

  if (!file_exists($fullpath . '/' . $filename)) {
    $file = file_create_filename($filename, $fullpath);
  }
  else {
    $file = $fullpath . '/' . $filename;
  }

  $data = file_get_contents($file);
  $data .= $input . "\n";
  $return = file_save_data($data, $file, FILE_EXISTS_REPLACE);

  return $return;
}

/**
 * Filters out unwanted queries.
 *
 * @param $query_struct
 *   An array containing all collected queries, function names and user names.
 * @return
 *   An array containing only queries we want to keep.
 */
function migration_filter_queries($query_struct) {
  $filtered_struct = array();

  $filter_selects = $filter_tables = $filter_vars = $filter_functions = $not_filtered = 0;

  foreach ($query_struct as $query) {

    // Filter out SELECT and SHOW queries.
    if (migration_is_select_or_show($query['query'])) {
      $filter_selects++;
      continue;
    }

    // Filter by table name.
    $tablename = migration_get_tablename($query['query']);
    if (migration_is_table_to_filter($tablename)) {
      $filter_tables++;
      continue;
    }

    // Filter by function.
    if (migration_is_function_to_filter($query['function'])) {
      $filter_functions++;
      continue;
    }

    if ($tablename == 'variable') {
      $variablename = migration_get_variable_from_query($query['query']);
      if (migration_is_variable_to_filter($variablename)) {
        $filter_vars++;
        continue;
      }
    }

    $not_filtered++;

    $filtered_struct[] = $query;
  }

  _migration_debug("SELECTs: $filter_selects", 'status');
  _migration_debug("Tables: $filter_tables", 'status');
  _migration_debug("Functions: $filter_functions", 'status');
  _migration_debug("Variables: $filter_vars", 'status');
  _migration_debug("Good: $not_filtered", 'status');

  $total = $not_filtered + $filter_selects + $filter_functions + $filter_vars + $filter_tables;
  _migration_debug("Total: $total", 'status');

  return $filtered_struct;
}

/**
 * Finds the variable name in a query to the variables table.
 *
 * @param $query
 *   The query from which to extract the variable name.
 * @return
 *   The variable name.
 */
function migration_get_variable_from_query($query) {
  if (stripos($query, 'UPDATE') === 0 ) {
    preg_match("|.*WHERE name = '(.*)'.*|", $query, $match);
  }
  elseif (stripos($query, 'DELETE') === 0 ) {
    preg_match("|.*WHERE name = '(.*)'.*|", $query, $match);
  }
  elseif (stripos($query, 'INSERT') === 0 ) {
    preg_match("|.*VALUES \('(.*?)'.*|", $query, $match);
  }
  else {
    _migration_debug("Failed to get variable from query: $query", 'error');
  }

  return $match[1];
}

/**
 * Finds the table name in a query.
 *
 * @param $query
 *   The query from which to extract the table name.
 * @return
 *   The table name.
 */
function migration_get_tablename($query) {
  //This is currently failing due to "DELETE FROM cache" - I think.. this is getting passed blank querystrings;
  preg_match("/(INSERT INTO|DELETE FROM|ALTER TABLE|UPDATE|DROP VIEW|CREATE VIEW|DROP TABLE|CREATE TABLE|LOCK TABLES|CREATE DOMAIN|CREATE INDEX|CREATE UNIQUE INDEX|CREATE SEQUENCE|DROP INDEX) (?<tablename>.*?)($| ).*/", $query, $match[]);
  if (strlen($match[0]['tablename']) < 2) {
    _migration_debug("Failed to get table name from query: $match[0]", 'error');
  }
  else {
    return $match[0]['tablename'];
  }
}

/**
 * Allows filtering by variable name.
 *
 * @param $variable_name
 *   The variable name to check.
 * @return
 *   Boolean value specifying whether or not keep the query based on the variable
 *   name.
 */
function migration_is_variable_to_filter($variable_name) {
  $skip_names = variable_get('migration_variable_name_filter_array', migration_get_default_variable_array());

  if (in_array($variable_name, $skip_names)) {
    _migration_debug("Variable filtered: $variable_name", 'notice');
    return TRUE;
  }
  else {
    _migration_debug("Variable not filtered: $variable_name", 'notice');
    return FALSE;
  }
}

/**
 * Allows filtering by table name.
 *
 * @param $table_name
 *   The table name to filter.
 * @return
 *   Boolean value specifying whether or not keep the query.
 */
function migration_is_table_to_filter($table_name) {
  $skip_tables = variable_get('migration_database_table_settings', migration_get_default_tables());

  if ($skip_tables[$table_name] === $table_name) {
    _migration_debug("Table filtered: $table_name", 'notice');
    return TRUE;
  }
  else {
    _migration_debug("Table not filtered: $table_name", 'notice');
    return FALSE;
  }
}

/**
 * Allows filtering by function name.
 *
 * @param $function
 *   The function name to filter.
 * @return
 *   Boolean value specifying whether or not to keep the query.
 */
function migration_is_function_to_filter($function) {  
  $skip_functions = array(
    'system_theme_data',
    'module_rebuild_cache',
  );

  if (in_array($function, $skip_functions)) {
    _migration_debug("Function filtered: $function", 'notice');
    return TRUE;
  }
  else {
    _migration_debug("Function not filtered: $function", 'notice');
    return FALSE;
  }
}

/**
 * Filters queries that begin with certain keywords.
 *
 * @param $query
 *   The query to test.
 * @return
 *   TRUE if query begins with a certain keyword.
 */
function migration_is_select_or_show($query) {
  if (stripos($query, 'SELECT') === 0 ||
      stripos($query, 'SHOW') === 0) {
    return TRUE;
  }

  return FALSE;
}

/**
 * Formats a hook_update_N function.
 *
 * @param $queries
 *   An array of all queries collected.
 * @return
 *   HTML displaying the formatted hook_update_N function.
 */
function migration_format_update_hook($queries) {
  global $queries;
  $count = migration_get_next_id();

  $output = '<code><pre>';
  $output .= "function mymodule_update_$count() {\n";
  $output .= '  $sql = array();';

  foreach ($queries as $data) {
    $bits = explode("\n", $data[0]);
    $query = preg_replace('|\/\*\s[a-z\s:_]*\*\/\s|', '', $bits[1]);

    //$process = migration_keepquery($query);
    if ($process) {
      $output .= "\n";
      $output .= "  /* $bits[0] */\n";
      $output .= '  $sql[] = update_sql($query);';
    }

  }
  $output .= "\n  return \$sql;\n}\n";
  $output .= '</pre></code>';

  return $output;
}

/**
 * Retrieves the number for the next update used in hook_update_N.
 */
function migration_get_next_id() {
  // TODO: Retrieve next update N from system table.
  return 6000;
}

/**
 * Implementation of hook_init().
 *
 * Adds the JavaScript and CSS files.
 */
function migration_init() {
  $path = drupal_get_path('module', 'migration');

  drupal_add_css($path . '/migration.css');
  drupal_add_js($path . '/migration.js');
}

/**
 * Implementation of hook_footer().
 *
 * Adds the start/stop link to the page.
 */
function migration_footer($main = 0) {
  if (user_access('view query monitor start/stop link')) {
    $output = '<div id="migration-monitor-switch">';

    if (variable_get('migration_query_monitor', FALSE)) {
      $output .= '<a href="#" id="migration-stop-monitor">Stop Query Monitor</a>';
    }
    else {
      $output .= '<a href="#" id="migration-start-monitor">Start Query Monitor</a>';
    }
    $output .= '</div>';

    return $output;
  }
}

/**
 * Provides default values for variables to exclude from collection.
 *
 * @return
 *   An array of variable names.
 */
function migration_get_default_variable_array() {
  $variables = array(
    'javascript_parsed',
    'cache_rebuild',
    'cache_flush',
    'menu_rebuild_needed',
    'admin_menu_rebuild_links',
    'menu_masks',
    'menu_expanded',
    'migration_query_monitor',
    'migration_filter_mode',
    'migration_database_tables',
    'migration_variable_patterns',
  );
  return $variables;
}

/**
 * Provides default values for tables to exclude from collection.
 *
 * @return
 *   An array of table names.
 */
function migration_get_default_tables() {
  $tables = array(
    'cache_views',
    'cache',
    'cache_content',
    'cache_menu',
    'cache_admin_menu',
    'cache_update',
    'cache_block',
    'cache_filter',
    'cache_page',
    'cache_calendar_ical',
    'cache_form',
    'history',
    'menu_router',
    'menu_links',
    'watchdogcache',
    'watchdog',
    'sessions',
    'search_dataset',
    'search_index',
    'search_keywords_log',
    'search_total',
    'accesslog',
    'devel_queries',
    'devel_times',
    'menu_router',
    'menu_links',
  );
  return array_combine($tables, $tables);
}

function _migration_debug($message = '', $type = '') {
  if (variable_get('migration_debug', FALSE)) {
    drupal_set_message($message, $type);
  }
}