db_maintenance.module in DB Maintenance 6.2

Optimizes database tables during cron runs.

@author David Kent Norman @link http://deekayen.net/

@todo

• some sort of backup dump saver/emailer/ftp/etc

File

db_maintenance.module

<?php

/**
 * @file
 * Optimizes database tables during cron runs.
 *
 * @author David Kent Norman
 * @link http://deekayen.net/
 *
 * @todo
 * - some sort of backup dump saver/emailer/ftp/etc
 */

/**
 * Implementation of hook_help().
 *
 * @param $section string
 * @return string
 */
function db_maintenance_help($path, $arg) {
  switch ($path) {
    case 'admin/help#db_maintenance':
      return t('<p>DB maintenance performs an optimization query on selected tables.</p>
        <p>For MyISAM tables,
        OPTIMIZE TABLE repairs a table if it has deleted or split rows, sorts table indexes,
        and updates table statistics. For BDB and InnoDB, OPTIMIZE rebuilds the table. Note, MySQL
        locks tables during the time OPTIMIZE TABLE is running. OPTIMIZE works best on tables with
        large deletions (e.g. cache or watchdog), however MySQL will reuse old record positions,
        therefore in most setups, OPTIMIZE TABLE is unnecessary unless you just like defragmenting.</p>
        <p>The Overhead column in phpMyAdmin\'s database view is the most common way to determine the
        need of an OPTIMIZE TABLE query. It essentially shows the amount of disk space you would
        recover by running an optimize/defragmentation query.</p>
        <p>For PostgreSQL tables, VACUUM reclaims storage occupied by deleted tuples.
        In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not
        physically removed from their table; they remain present until a VACUUM is done. Therefore
        it\'s necessary to do VACUUM periodically, especially on frequently-updated tables.</p>');
    case 'admin/settings/db_maintenance':
      return t('Executes an optimization query on database tables during cron runs.');
  }
}

/**
 * Implementation of hook_menu().
 *
 * @return array
 */
function db_maintenance_menu() {
  $items = array();
  $items['admin/settings/db_maintenance'] = array(
    'title' => 'DB maintenance',
    'description' => 'Executes a cron-based query to optimize database tables.',
    'page callback' => 'drupal_get_form',
    'page arguments' => array(
      'db_maintenance_admin_settings',
    ),
    'access callback' => 'user_access',
    'access arguments' => array(
      'administer site configuration',
    ),
    'type' => MENU_NORMAL_ITEM,
  );
  $items['db_maintenance/optimize'] = array(
    'page callback' => 'db_maintenance_optimize_tables_page',
    'access callback' => 'user_access',
    'access arguments' => array(
      'administer site configuration',
    ),
    'type' => MENU_CALLBACK,
  );
  return $items;
}

/**
 * Callback page for manually optimizing tables.
 */
function db_maintenance_optimize_tables_page() {
  db_maintenance_optimize_tables();
  drupal_set_message(t('Database tables optimized'));
  drupal_goto('admin/settings/db_maintenance');
}

/**
 * Get a list of all the tables in a database.
 *
 * @param string $db The name of the database connection to query for tables.
 * @return array representing the tables in the specified database.
 */
function _db_maintenance_list_tables($db) {
  $table_names = array();

  // Set the database to query.
  $previous = db_set_active($db);
  if (_db_maintenance_determine_software() == 'mysql') {
    $result = db_query('SHOW TABLES');
  }
  elseif (_db_maintenance_determine_software() == 'pgsql') {
    $result = db_query("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name");
  }

  // Return to the previously set database.
  db_set_active($previous);
  while ($table_name = db_fetch_array($result)) {
    $table_name = current($table_name);
    $table_names[$table_name] = $table_name;
  }
  return $table_names;
}

/**
 * Implementation of hook_cron().
 */
function db_maintenance_cron() {
  $last_run = variable_get('db_maintenance_cron_last', 0);
  $now = time();
  $optimize_frequency = variable_get('db_maintenance_cron_frequency', 86400);
  $email = array();
  $interval = $now - $optimize_frequency;

  // Only run cron if enough time has elapsed
  if ($optimize_frequencty != -1 && $interval > $last_run) {
    db_maintenance_optimize_tables();
  }
  $last_db_backup = variable_get('db_maintenance_last_db_backup', 0);
  $db_backup_freq = variable_get('db_maintenance_db_backup_frequency', -1);
  $files_backup_freq = variable_get('db_maintenance_files_backup_frequency', -1);
  $last_files_backup = variable_get('db_maintenance_last_files_backup', 0);
  if ($db_backup_freq != -1 && $last_db_backup <= $now - $db_backup_freq) {
    $email['db_backup'] = db_maintenance_do_db_backup();
    variable_set('db_maintenance_last_db_backup', $now);
  }
  if ($files_backup_freq != -1 && $last_files_backup <= $now - $files_backup_freq) {
    $email['files_backup'] = db_maintenance_do_files_backup();
    variable_set('db_maintenance_last_files_backup', $now);
  }
  db_maintenance_email($email);
}

/**
 * Perform the maintenance.
 */
function db_maintenance_optimize_tables() {
  global $db_url;

  // Set the databases array if not already set in $db_url.
  if (is_array($db_url)) {
    $databases = $db_url;
  }
  else {
    $databases['default'] = $db_url;
  }

  // Loop through each database optimizing any selected tables.
  foreach ($databases as $db => $connection) {
    $config_tables = variable_get('db_maintenance_table_list_' . $db, NULL);

    // Only proceed if tables are selected for this database.
    if (is_array($config_tables) && count($config_tables) > 0) {
      $db_name = $db == 'default' ? 'Drupal' : $db;
      while (list(, $table_name) = each($config_tables)) {

        // Set the database to query.
        $previous = db_set_active($db);
        if (db_table_exists($table_name)) {
          if (_db_maintenance_determine_software() == 'mysql') {
            $result = db_query('OPTIMIZE TABLE %s', $table_name);
            $status = db_fetch_array($result);
            while ($status[] = db_fetch_array($result)) {

              // get all of the rows
            }
            db_maintenance_check_status($status, 'OPTIMIZE');
          }
          elseif (_db_maintenance_determine_software() == 'pgsql') {
            db_query('VACUUM ANALYZE %s', $table_name);
          }
        }
        else {
          watchdog('db_maintenance', '@table table in @db database was configured to be optimized but does not exist.', array(
            '@db' => $db_name,
            '@table' => $table_name,
          ), WATCHDOG_NOTICE);
        }

        // Return to the previously set database.
        db_set_active($previous);
        if (variable_get('db_maintenance_log', 0)) {
          watchdog('db_maintenance', 'Optimized @table table in @db database.', array(
            '@db' => $db_name,
            '@table' => $table_name,
          ), WATCHDOG_DEBUG);
        }
      }
      if (variable_get('db_maintenance_log', 0)) {
        $tables = implode(', ', $config_tables);
        watchdog('db_maintenance', 'Optimized tables in @db database: @tables', array(
          '@db' => $db_name,
          '@tables' => $tables,
        ), WATCHDOG_INFO);
      }
    }
  }
  variable_set('db_maintenance_cron_last', time());
}

/**
 * Administration settings
 *
 * options: log each optimization
 *          multi-select list of tables to optimize
 *
 * @return array
 */
function db_maintenance_admin_settings() {
  global $db_url;
  $form = array();
  $form['db_maintenance_log'] = array(
    '#type' => 'checkbox',
    '#title' => 'Log OPTIMIZE queries',
    '#default_value' => variable_get('db_maintenance_log', 0),
    '#description' => t('If enabled, a watchdog entry will be made each time tables are optimized, containing information which tables were involved.'),
  );
  $form['db_maintenance_repair'] = array(
    '#type' => 'checkbox',
    '#title' => 'Attempt REPAIR of table if OPTIMIZE is problematic',
    '#default_value' => variable_get('db_maintenance_repair', 0),
    '#description' => t('If enabled and a table receives a non-okay status from the OPTIMIZE then a repair of that table will be attempted. In the case of REPAIR all resulting status are logged via watchdog.'),
  );
  $frequency = array(
    -1 => t('Never'),
    0 => t('Run during every cron'),
    3600 => t('Hourly'),
    7200 => t('Bi-Hourly'),
    86400 => t('Daily'),
    172800 => t('Bi-Daily'),
    604800 => t('Weekly'),
    1209600 => t('Bi-Weekly'),
    2592000 => t('Monthly'),
    5184000 => t('Bi-Monthly'),
  );
  $form['db_maintenance_cron_frequency'] = array(
    '#type' => 'select',
    '#title' => t('Optimize tables'),
    '#options' => $frequency,
    '#default_value' => variable_get('db_maintenance_cron_frequency', 86400),
    '#description' => t('Select how often database tables should be optimized.') . ' ' . l(t('Optimize now.'), 'db_maintenance/optimize'),
  );

  // Set the databases array if not already set in $db_url.
  if (is_array($db_url)) {
    $databases = $db_url;
  }
  else {
    $databases['default'] = $db_url;
  }
  $options = array();

  // Loop through each database and list the possible tables to optimize.
  foreach ($databases as $db => $connection) {
    $options = _db_maintenance_list_tables($db);
    $form['db_maintenance_table_list_' . $db] = array(
      '#type' => 'select',
      '#title' => t('Tables in the !db database', array(
        '!db' => $db == 'default' ? 'Drupal' : $db,
      )),
      '#options' => $options,
      '#default_value' => variable_get('db_maintenance_table_list_' . $db, ''),
      '#description' => t('Selected tables will be optimized during cron runs.'),
      '#multiple' => TRUE,
      '#attributes' => array(
        'size' => count($options),
      ),
    );
  }
  $form['db_maintenance_db_backup_frequency'] = array(
    '#type' => 'select',
    '#title' => t('Database Backup frequency'),
    '#description' => t('How often to backup the database. This is the most frequent this will run, but no more often than the frequency of the drupal cron script.'),
    '#options' => $frequency,
    '#default_value' => variable_get('db_maintenance_db_backup_frequency', -1),
  );
  $form['db_maintenance_files_backup_frequency'] = array(
    '#type' => 'select',
    '#title' => t('Files Backup frequency'),
    '#description' => t('How often to backup the files directory. This is the most frequent this will run, but no more often than the frequency of the drupal cron script.'),
    '#options' => $frequency,
    '#default_value' => variable_get('db_maintenance_files_backup_frequency', -1),
  );
  $form['db_maintenance_backup_directory'] = array(
    '#type' => 'textfield',
    '#title' => t('Backup directory'),
    '#description' => t('Directory to store backup files in'),
    '#default_value' => variable_get('db_maintenance_backup_directory', '/tmp'),
  );
  $form['db_maintenance_path_to_tar'] = array(
    '#type' => 'textfield',
    '#title' => t('Path to tar'),
    '#default_value' => variable_get('db_maintenance_path_to_tar', '/bin/tar'),
  );
  $form['db_maintenance_path_to_mysqldump'] = array(
    '#type' => 'textfield',
    '#title' => t('Path to mysqldump'),
    '#default_value' => variable_get('db_maintenance_path_to_mysqldump', '/usr/bin/mysqldump'),
  );
  $form['db_maintenance_email_notify'] = array(
    '#type' => 'textfield',
    '#title' => t('Email address to notify'),
    '#default_value' => variable_get('db_maintenance_email_notify', ''),
    '#description' => t('List of email addresses to notify when tasks run. Seperate multiple addressses with a comma. Leave empty for no notification'),
  );
  return system_settings_form($form);
}
function db_maintenance_admin_settings_validate($form, &$form_state) {

  // if -1 then we do not backup so the extra field validation should only happen when appropriate
  $dbfreq = $form_state['values']['db_maintenance_db_backup_frequency'];
  $filesfreq = $form_state['values']['db_maintenance_files_backup_frequency'];

  // only validate backup directory if we need to
  if ($dbfreq != -1 || $filesfreq != -1) {
    if (!is_dir($form_state['values']['db_maintenance_backup_directory'])) {
      form_set_error('db_maintenance_backup_directory', t('Backup directory does not exist or is not a directory.'));
    }
    elseif (!is_writable($form_state['values']['db_maintenance_backup_directory'])) {
      form_set_error('db_maintenance_backup_directory', t('Backup directory is not writable.'));
    }
  }
  if ($filesfreq != -1) {
    if (!is_file($form_state['values']['db_maintenance_path_to_tar'])) {
      form_set_error('db_maintenance_path_to_tar', t('Path to tar is incorrect.'));
    }
    elseif (!is_executable($form_state['values']['db_maintenance_path_to_tar'])) {
      form_set_error('db_maintenance_path_to_tar', t('tar is not executable.'));
    }
  }
  if ($dbfreq != -1) {
    if (!is_file($form_state['values']['db_maintenance_path_to_mysqldump'])) {
      form_set_error('db_maintenance_path_to_mysqldump', t('Path to mysqldump is incorrect.'));
    }
    elseif (!is_executable($form_state['values']['db_maintenance_path_to_mysqldump'])) {
      form_set_error('db_maintenance_path_to_mysqldump', t('mysqldump is not executable.'));
    }
  }
  if (!empty($form_state['values']['db_maintenance_email_notify'])) {
    $emails = explode(',', $form_state['values']['db_maintenance_email_notify']);
    if (!empty($emails)) {
      $bad = array();
      foreach ($emails as $email) {
        $email = trim($email);
        if (!valid_email_address($email)) {
          $bad[] = $email;
        }
      }
      if (!empty($bad)) {
        form_set_error('db_maintenance_email_notify', t('The following email address(es) were invalid; @badmail', array(
          '@badmail' => implode(', ', $bad),
        )));
      }
    }
  }
}

/**
 * Determine which database software is in use
 */
function _db_maintenance_determine_software() {
  global $db_url;
  static $db_type;
  if (!empty($db_type)) {
    return $db_type;
  }
  elseif (strpos($db_url, 'mysql://') === 0 || strpos($db_url, 'mysqli://') === 0) {
    $db_type = 'mysql';
    return $db_type;
  }
  elseif (strpos($db_url, 'pgsql://') === 0) {
    $db_type = 'pgsql';
    return $db_type;
  }
  else {
    return FALSE;
  }
}
function db_maintenance_check_status($status, $op) {

  // mysql return codes indicating okay/success all others assumed to be "bad"
  $DB_MAINTENANCE_OKAY = array(
    'Table is already up to date',
    'OK',
  );
  foreach ($status as $key => $return) {
    if (is_numeric($key) && !empty($return)) {
      if (in_array($return['Msg_text'], $DB_MAINTENANCE_OKAY)) {

        // everything okay only log if explicitly set or we did a repair
        if (variable_get('db_maintenance_log', 0) || $op == 'REPAIR') {
          watchdog('db_maintenance', 'Success: !op table !table, type: !type, message: !message', array(
            '!op' => $op,
            '!table' => $return['Table'],
            '!type' => $return['Msg_type'],
            '!message' => $return['Msg_text'],
          ));
        }
      }
      else {

        // problems encountered
        watchdog('db_maintenance', 'Failure:  !op table !table type: !type, message: !message', array(
          '!op' => $op,
          '!table' => $return['Table'],
          '!type' => $return['Msg_type'],
          '!message' => $return['Msg_text'],
        ), WATCHDOG_ERROR);

        // attempt repair if config is set and makes sense
        if (variable_get('db_maintenance_repair', 0) && $op == 'OPTIMIZE' && !empty($return['Table'])) {
          $result = db_query('REPAIR TABLE %s', $return['Table']);
          $status = array();
          while ($status[] = db_fetch_array($result)) {

            // get all of the rows
          }
          db_maintenance_check_status($status, 'REPAIR');
        }
      }
    }
  }

  // check non-numeric entries (mysql has such a lovely return setup)
  if (isset($status['Table']) && isset($status['Op']) && isset($status['Msg_type']) && isset($status['Msg_text'])) {
    $new_status = array();
    $new_status[] = array(
      'Table' => $status['Table'],
      'Op' => $status['Op'],
      'Msg_type' => $status['Msg_type'],
      'Msg_text' => $status['Msg_text'],
    );
    db_maintenance_check_status($new_status, $op);
  }
}
function db_maintenance_email($email) {
  global $base_url;
  $email_list = variable_get('db_maintenance_email_notify', '');
  if (!empty($email) && !empty($email_list)) {
    $message = array();
    $backupdir = variable_get('db_maintenance_backup_directory', '/tmp');
    foreach ($email as $task => $status) {
      switch ($task) {
        case 'db_backup':
          if (is_file($status)) {
            $message[] = t('Database backed up. mysqldump is available at \'@STATUS@\' when connected to \'@URL@\'', array(
              '@STATUS@' => $status,
              '@URL' => $base_url,
            ));
          }
          else {
            $message[] = t('Database backup failed. Please review watchdog for important messages');
          }
          break;
        case 'files_backup':
          if (is_file($status)) {
            $message[] = t('Site files backed up. Files tarball are available at \'@STATUS@\' when connected to \'@URL@\'', array(
              '@STATUS@' => $status,
              '@URL@' => $base_url,
            ));
          }
          else {
            $message[] = t('Files directory backup failed. Please review watchdog for important messages');
          }
          break;
        case 'db_maintenance':
          $badstatus = db_maintenance_return_non_okay($status);
          if (empty($badstatus)) {
            $message[] = t('DB maintenance tasks ran successfully.');
          }
          else {
            $message[] = t('DB maintenance tasks encountered at least one issue. These may have been automatically been recovered from, but at least one table was in a non-okay state. Please review the messages below, watchdog, and manually review the database table status to ensure the system is working properly.');
            $message[] = db_maintenance_format_status($badstatus);
          }
          break;
      }
    }
    $body = implode("\n", $message);
    $subject = t('[drupal db_maintenance] site: @URL@', array(
      '@URL@',
      $base_url,
    ));
    drupal_mail('db_maintenance', $email_list, $subject, $body);
  }
}
function db_maintenance_do_db_backup() {
  global $db_url;
  $mysqldump = variable_get('db_maintenance_path_to_mysqldump', '/usr/bin/mysqldump');
  $backupdir = variable_get('db_maintenance_backup_directory', '/tmp');
  $dateformat = 'Ymd_H-i-s';
  $dbname = db_maintenance_get_db_info('dbname');
  $now = time();
  $date = format_date($now, 'custom', $dateformat);
  $filename = $backupdir . '/' . $date . '_' . $dbname . '_db.sql';
  $mysqloptions = db_maintenance_get_mysql_options();
  $mysqldumpexec = $mysqldump . ' ' . $mysqloptions . ' > ' . $filename;
  if (is_dir($backupdir)) {
    exec($mysqldumpexec, $output, $return);
    $output = implode('<br />', $output);

    // variable_set('db_maintenance_debug', variable_get('db_maintenance_debug', '') . 'MYSQLDUMP: '.$mysqldumpexec.' ('. var_export($return, TRUE) .') '. var_export($output, TRUE)."\n");
    if (!$return && !is_file($filename)) {
      watchdog('db_maintenance', $output, NULL, WATCHDOG_ERROR);
      return FALSE;
    }
    else {
      return $filename;
    }
  }
  else {
    watchdog('db_maintenance', 'backup directory does not exist', array(), WATCHDOG_ERROR);
    return FALSE;
  }
}
function db_maintenance_do_files_backup() {
  $filespath = file_directory_path();
  $tarpath = variable_get('db_maintenance_path_to_tar', '/bin/tar');
  $backupdir = variable_get('db_maintenance_backup_directory', '/tmp');
  $dateformat = 'Ymd_H-i-s';
  $dbname = db_maintenance_get_db_info('dbname');
  $now = time();
  $date = format_date($now, 'custom', $dateformat);
  if (is_file($tarpath) && is_dir($filespath) && is_dir($backupdir)) {
    $backupname = $date . '_' . $dbname . '_files.tar.gz';
    $command = "tar -cvzf {$backupdir}/{$backupname} --exclude=*" . $dbname . "_files.tar.gz --exclude=*" . $dbname . "_db.sql {$filespath}";
    $output = array();
    exec($command, $output, $return);
    $output = implode('<br />', $output);

    // variable_set('db_maintenance_debug', variable_get('db_maintenance_debug', '') . 'TAR: '.$command.' ('. var_export($return, TRUE) .') '. var_export($output, TRUE) ."\n");
    if (!$return) {
      watchdog('db_maintenance', $output, NULL, WATCHDOG_ERROR);
      return FALSE;
    }
    else {
      if (!is_file($backupdir . '/' . $backupname)) {
        watchdog('db_maintenance', 'failed to create files backup file: !output', array(
          '!output' => $output,
        ), WATCHDOG_ERROR);
        return FALSE;
      }
      return $backupdir . '/' . $backupname;
    }
  }
  else {
    watchdog('db_maintenance', 'Files dir not present, backup dir not present or path to tar incorrect', array(), WATCHDOG_ERROR);
    return FALSE;
  }
}
function db_maintenance_get_db_info($param) {
  global $db_url;
  $url = parse_url($db_url);
  switch (drupal_strtolower($param)) {
    case 'pass':
    case 'password':
      if (isset($url['pass'])) {
        return urldecode($url['pass']);
      }
      else {
        return '';
      }
    case 'user':
    case 'username':
      return urldecode($url['user']);
    case 'host':
    case 'hostname':
      return urldecode($url['host']);
    case 'db':
    case 'database':
    case 'dbname':
    case 'name':
      $url['path'] = urldecode($url['path']);
      return drupal_substr($url['path'], 1);
    case 'port':
      if (isset($url['port'])) {
        return urldecode($url['port']);
      }
      else {
        return '';
      }
    default:
      return '';
  }
}
function db_maintenance_get_mysql_options() {
  global $db_url;
  $url = parse_url($db_url);
  $url['host'] = urldecode($url['host']);
  $url['path'] = urldecode($url['path']);
  $url['user'] = urldecode($url['user']);
  if (isset($url['pass'])) {
    $url['pass'] = urldecode($url['pass']);
  }
  else {
    $url['pass'] = '';
  }
  if ($url['host']) {
    $options[] = '--host=' . $url['host'];
  }
  if (!empty($url['port'])) {
    $options[] = '--port=' . $url['port'];
  }
  if ($url['pass'] != '') {
    $options[] = '--password=' . $url['pass'];
  }
  if ($url['user']) {
    $options[] = '--user=' . $url['user'];
  }
  $return = implode(' ', $options);
  $return .= ' ' . drupal_substr($url['path'], 1);

  // db name is 'path' with '/' pre-pended
  return $return;
}

Functions

Name	Description
db_maintenance_admin_settings	Administration settings
db_maintenance_admin_settings_validate
db_maintenance_check_status
db_maintenance_cron	Implementation of hook_cron().
db_maintenance_do_db_backup
db_maintenance_do_files_backup
db_maintenance_email
db_maintenance_get_db_info
db_maintenance_get_mysql_options
db_maintenance_help	Implementation of hook_help().
db_maintenance_menu	Implementation of hook_menu().
db_maintenance_optimize_tables	Perform the maintenance.
db_maintenance_optimize_tables_page	Callback page for manually optimizing tables.
_db_maintenance_determine_software	Determine which database software is in use
_db_maintenance_list_tables	Get a list of all the tables in a database.