/[drupal]/contributions/modules/db_maintenance/db_maintenance.module
ViewVC logotype

Diff of /contributions/modules/db_maintenance/db_maintenance.module

Parent Directory Parent Directory | Revision Log Revision Log | View Revision Graph Revision Graph | View Patch Patch

revision 1.9.2.6, Wed Jul 30 19:49:25 2008 UTC revision 1.9.2.6.2.1, Fri Aug 1 02:03:58 2008 UTC
# Line 1  Line 1 
1  <?php  <?php
2  // $Id: db_maintenance.module,v 1.9.2.2 2007/01/28 03:10:20 deekayen Exp $  // $Id: db_maintenance.module,v 1.9.2.4 2008/06/26 06:26:25 jgraham Exp $
3    
4  /**  /**
5   * @file   * @file
6   * Optimizes database tables during cron runs.   * Optimizes database tables during cron runs.
7   *   *
8   * @version $Id: db_maintenance.module,v 1.9.2.2 2007/01/28 03:10:20 deekayen Exp $   * @version $Id: db_maintenance.module,v 1.9.2.4 2008/06/26 06:26:25 jgraham Exp $
9   * @author David Kent Norman   * @author David Kent Norman
10   * @link http://deekayen.net/   * @link http://deekayen.net/
11   *   *
# Line 28  function db_maintenance_help($section = Line 28  function db_maintenance_help($section =
28    switch ($section) {    switch ($section) {
29      case 'admin/help#db_maintenance':      case 'admin/help#db_maintenance':
30        $output = t('DB maintenance performs an OPTIMIZE TABLE query on selected tables. For MyISAM tables,        $output = t('DB maintenance performs an OPTIMIZE TABLE query on selected tables. For MyISAM tables,
31                    OPTIMIZE TABLE repairs a table if it has deleted or split rows, sorts table indexes,          OPTIMIZE TABLE repairs a table if it has deleted or split rows, sorts table indexes,
32                    and updates table statistics. For BDB and InnoDB, OPTIMIZE rebuilds the table. Note, MySQL          and updates table statistics. For BDB and InnoDB, OPTIMIZE rebuilds the table. Note, MySQL
33                    locks tables during the time OPTIMIZE TABLE is running. OPTIMIZE works best on tables with          locks tables during the time OPTIMIZE TABLE is running. OPTIMIZE works best on tables with
34                    large deletions (e.g. cache or watchdog), however MySQL will reuse old record positions,          large deletions (e.g. cache or watchdog), however MySQL will reuse old record positions,
35                    therefore in most setups, OPTIMIZE TABLE is unnecessary unless you just like defragmenting.          therefore in most setups, OPTIMIZE TABLE is unnecessary unless you just like defragmenting.
36                    The Overhead column in phpMyAdmin\'s database view is the most common way to determine the          The Overhead column in phpMyAdmin\'s database view is the most common way to determine the
37                    need of an OPTIMIZE TABLE query. It essentially shows the amount of disk space you would          need of an OPTIMIZE TABLE query. It essentially shows the amount of disk space you would
38                    recover by running an optimize/defragmentation query.');          recover by running an optimize/defragmentation query.');
39        break;        break;
40      case 'admin/settings/db_maintenance':      case 'admin/settings/db_maintenance':
41        $output = t('Executes an OPTIMIZE TABLE query on MyISAM, InnoDB, or BerkeleyDB database tables');        $output = t('Executes an OPTIMIZE TABLE query on MyISAM, InnoDB, or BerkeleyDB database tables');
# Line 109  function _db_maintenance_list_mysql_tabl Line 109  function _db_maintenance_list_mysql_tabl
109   */   */
110  function db_maintenance_cron() {  function db_maintenance_cron() {
111    $last_run = variable_get('db_maintenance_cron_last', 0);    $last_run = variable_get('db_maintenance_cron_last', 0);
112    $interval = time() - variable_get('db_maintenance_cron_frequency', 86400);    $now = time();
113    // Only run cron if enough time has elapsed.    $optimize_frequency = variable_get('db_maintenance_cron_frequency', 86400);
114    if ($interval > $last_run) {  
115      $email = array();
116    
117      $interval = $now - $optimize_frequency;
118      // Only run cron if enough time has elapsed
119      if ($optimize_frequencty != 0 && $interval > $last_run) {
120      db_maintenance_optimize_tables();      db_maintenance_optimize_tables();
121    }    }
122    
123      $last_db_backup = variable_get('db_maintenance_last_db_backup', 0);
124      $db_backup_freq = variable_get('db_maintenance_db_backup_frequency', 0);
125      $files_backup_freq = variable_get('db_maintenance_files_backup_frequency', 0);
126      $last_files_backup = variable_get('db_maintenance_last_files_backup', 0);
127    
128      if ($db_backup_freq != 0 && $last_db_backup <= $now - $db_backup_freq) {
129        $email['db_backup'] = db_maintenance_do_db_backup();
130        variable_set('db_maintenance_last_db_backup', $now);
131      }
132    
133      if ($files_backup_freq != 0 && $last_files_backup <= $now - $files_backup_freq) {
134        $email['files_backup'] = db_maintanence_do_files_backup();
135        variable_set('db_maintenance_last_files_backup', $now);
136      }
137    
138      db_maintenance_email($email);
139  }  }
140    
141  /**  /**
# Line 144  function db_maintenance_optimize_tables( Line 166  function db_maintenance_optimize_tables(
166          $tables = implode(', ', $config_tables);          $tables = implode(', ', $config_tables);
167          // Set the database to query.          // Set the database to query.
168          $previous = db_set_active($db);          $previous = db_set_active($db);
169          db_query('OPTIMIZE TABLE %s', $tables);          $result = db_query('OPTIMIZE TABLE %s', $tables);
170            $status = db_fetch_array($result);
171            while ($status[] = db_fetch_array($result)) {
172              // get all of the rows
173            }
174            db_maintenance_check_status($status, 'OPTIMIZE');
175    
176          // Return to the previously set database.          // Return to the previously set database.
177          db_set_active($previous);          db_set_active($previous);
         if (variable_get('db_maintenance_log', 0)) {  
           watchdog('db_maintenance', t('Optimized tables in !db database: ', array('!db' => $db_name)) . $tables);  
         }  
178        }        }
179      }      }
180    }    }
# Line 167  function db_maintenance_optimize_tables( Line 192  function db_maintenance_optimize_tables(
192  function db_maintenance_admin_settings() {  function db_maintenance_admin_settings() {
193    global $db_url;    global $db_url;
194    
195      $form = array();
196    $form['db_maintenance_log'] = array(    $form['db_maintenance_log'] = array(
197      '#type'          => 'checkbox',      '#type'          => 'checkbox',
198      '#title'         => 'Log OPTIMIZE queries',      '#title'         => 'Log OPTIMIZE queries',
199      '#default_value' => variable_get('db_maintenance_log', 0),      '#default_value' => variable_get('db_maintenance_log', 0),
200      '#description'   => t('If enabled, a watchdog entry will be made each time tables are optimized, containing information which tables were involved.')      '#description'   => t('If enabled, a watchdog entry will be made each time tables are optimized, containing information which tables were involved.')
201    );    );
202    $options = array(  
203      $form['db_maintenance_repair'] = array(
204        '#type'          => 'checkbox',
205        '#title'         => 'Attempt REPAIR of table if OPTIMIZE is problematic',
206        '#default_value' => variable_get('db_maintenance_repair', 0),
207        '#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.')
208      );
209    
210      // array keyed by time (in seconds) with 0 indicating never
211      $frequency = array(
212        0 => t('Never'),
213      3600 => t('Hourly'),      3600 => t('Hourly'),
214      86400 => t('Daily'),      86400 => t('Daily'),
215      604800 => t('Weekly'),      604800 => t('Weekly'),
# Line 182  function db_maintenance_admin_settings() Line 218  function db_maintenance_admin_settings()
218    $form['db_maintenance_cron_frequency'] = array(    $form['db_maintenance_cron_frequency'] = array(
219      '#type'          => 'select',      '#type'          => 'select',
220      '#title'         => t('Optimize tables'),      '#title'         => t('Optimize tables'),
221      '#options'       => $options,      '#options'       => $frequency,
222      '#default_value' => variable_get('db_maintenance_cron_frequency', 86400),      '#default_value' => variable_get('db_maintenance_cron_frequency', 86400),
223      '#description'   => t('Select how often database tables should be optimized.') .' '. l(t('Optimize now.'), 'db_maintenance/optimize'),      '#description'   => t('Select how often database tables should be optimized.') .' '. l(t('Optimize now.'), 'db_maintenance/optimize'),
224    );    );
225    
226    
227    
228    // Set the databases array if not already set in $db_url.    // Set the databases array if not already set in $db_url.
229    if (is_array(($db_url))) {    if (is_array(($db_url))) {
230      $databases = $db_url;      $databases = $db_url;
# Line 207  function db_maintenance_admin_settings() Line 246  function db_maintenance_admin_settings()
246      );      );
247    }    }
248    
249      $form['db_maintenance_db_backup_frequency'] = array(
250        '#type' => 'select',
251        '#title' => t('Database Backup frequency'),
252        '#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.'),
253        '#options' => $frequency,
254        '#default_value' => variable_get('db_maintenance_db_backup_frequency', 0),
255      );
256    
257      $form['db_maintenance_files_backup_frequency'] = array(
258        '#type' => 'select',
259        '#title' => t('Files Backup frequency'),
260        '#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.'),
261        '#options' => $frequency,
262        '#default_value' => variable_get('db_maintenance_files_backup_frequency', 0),
263      );
264    
265      $form['db_maintenance_backup_directory'] = array(
266        '#type' => 'textfield',
267        '#title' => t('Backup directory'),
268        '#description' => t('Directory to store backup files in'),
269        '#default_value' => variable_get('db_maintenance_backup_directory', '/tmp'),
270      );
271    
272      $form['db_maintenance_path_to_tar'] = array(
273        '#type' => 'textfield',
274        '#title' => t('Path to tar'),
275        '#default_value' => variable_get('db_maintenance_path_to_tar', '/bin/tar'),
276      );
277    
278      $form['db_maintenance_path_to_mysqldump'] = array(
279        '#type' => 'textfield',
280        '#title' => t('Path to mysqldump'),
281        '#default_value' => variable_get('db_maintenance_path_to_mysqldump', '/usr/bin/mysqldump'),
282      );
283    
284      $form['db_maintenance_email_notify'] = array(
285        '#type' => 'textfield',
286        '#title' => t('Email address to notify'),
287        '#default_value' => variable_get('db_maintenance_email_notify', ''),
288        '#description' => t('List of email addresses to notify when tasks run. Seperate multiple addressses with a comma. Leave empty for no notification')
289      );
290    
291    
292    return system_settings_form($form);    return system_settings_form($form);
293  }  }
294    
295    function db_maintenance_check_status($status, $op) {
296      // mysql return codes indicating okay/success all others assumed to be "bad"
297      $DB_MAINTENANCE_OKAY = array('Table is already up to date', 'OK');
298      foreach ($status as $key => $return) {
299        if (is_numeric($key) && !empty($return)) {
300          if (in_array($return['Msg_text'], $DB_MAINTENANCE_OKAY)) {
301            // everything okay only log if explicitly set or we did a repair
302            if (variable_get('db_maintenance_log', 0) || $op == 'REPAIR') {
303              watchdog('db_maintenance', t('Success: !op table !table, type: !type, message: !message', array('!op' => $op, '!table' => $return['Table'], '!type' => $return['Msg_type'], '!message' => $return['Msg_text'])));
304            }
305          }
306          else {
307            // problems encountered
308            watchdog('db_maintenance', t('Failure:  !op table !table type: !type, message: !message', array('!op' => $op, '!table' => $return['Table'], '!type' => $return['Msg_type'], '!message' => $return['Msg_text'])), WATCHDOG_ERROR);
309    
310            // attempt repair if config is set and makes sense
311            if (variable_get('db_maintenance_repair', 0) && $op == 'OPTIMIZE' && !empty($return['Table'])) {
312              $result = db_query('REPAIR TABLE %s', $return['Table']);
313              $status = array();
314              while ($status[] = db_fetch_array($result)) {
315                // get all of the rows
316              }
317              db_maintenance_check_status($status, 'REPAIR');
318            }
319          }
320        }
321      }
322    
323      // check non-numeric entries (mysql has such a lovely return setup)
324      if (isset($status['Table']) && isset($status['Op']) && isset($status['Msg_type']) && isset($status['Msg_text'])) {
325        $new_status = array();
326        $new_status[] = array('Table' => $status['Table'], 'Op' => $status['Op'], 'Msg_type' => $status['Msg_type'], 'Msg_text' => $status['Msg_text']);
327        db_maintenance_check_status($new_status, $op);
328      }
329    }
330    
331    function db_maintenance_email($email) {
332      global $base_url;
333      $email_list = variable_get('db_maintenance_email_notify', '');
334      if (!empty($email) && !empty($email_list)) {
335        $message = array();
336        $backupdir = variable_get('db_maintenance_backup_directory', '/tmp');
337        foreach ($email as $task => $status) {
338          switch ($task) {
339            case 'db_backup':
340              if ($status) {
341                $message[] = t('Database backed up. mysqldump is available at \'!STATUS\' when connected to \'!URL\'', array('!STATUS' => $status, '!URL' => $base_url));
342              }
343              else {
344                $message[] = t('Database backup failed. Please review watchdog for important messages');
345              }
346              break;
347            case 'files_backup':
348              if ($status) {
349                $message[] = t('Site files backed up. Files tarball are available at \'!STATUS\' when connected to \'!URL\'', array('!STATUS' => $status, '!URL' => $base_url));
350              }
351              else {
352                $message[] = t('Files directory backup failed. Please review watchdog for important messages');
353              }
354              break;
355            case 'db_maintenance':
356              $badstatus = db_maintenance_return_non_okay($status);
357              if (empty($badstatus)) {
358                $message[] = t('DB maintenance tasks ran successfully.');
359              }
360              else {
361                $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.');
362                $message[] = db_maintenance_format_status($badstatus);
363              }
364              break;
365          }
366        }
367        $body = implode('\n', $message);
368        $subject = t('[drupal db_maintenance] site: !URL', array('!URL' => $base_url));
369        drupal_mail('db_maintenance', $email_list, $subject, $body);
370      }
371    }
372    
373    function db_maintenance_do_db_backup() {
374      global $db_url;
375      $mysqldump = variable_get('db_maintenance_path_to_mysqldump', '/usr/bin/mysqldump');
376      $backupdir = variable_get('db_maintenance_backup_directory', '/tmp');
377      $dateformat = 'Ymd_H-i-s';
378      $dbname = db_maintenance_get_db_info('dbname');
379      $now = time();
380      $date = date($dateformat, $now);
381      $filename = "{$backupdir}/{$date}_{$dbname}_db.sql";
382      $mysqloptions = db_maintenance_get_mysql_options();
383    
384      $mysqldumpexec = "$mysqldump $mysqloptions > $filename";
385      if (is_dir($backupdir)) {
386        exec($mysqldumpexec, $output, $return);
387        // $return is return value of exec'd command (0 is okay status)
388        if (!$return) {
389          watchdog('db_maintenance', $output, WATCHDOG_ERROR);
390          return FALSE;
391        }
392        else if (!is_file($filename)) {
393          watchdog('db_maintenance', t('failed to backup database') .': '. $output, WATCHDOG_ERROR);
394          return FALSE;
395        }
396        else {
397          return $filename;
398        }
399      }
400      else {
401        watchdog('db_maintenance', t('backup directory does not exist'), WATCHDOG_ERROR);
402        return FALSE;
403      }
404    }
405    
406    function db_maintanence_do_files_backup() {
407      $filespath = file_directory_path();
408      $tarpath = variable_get('db_maintenance_path_to_tar', '/bin/tar');
409      $backupdir = variable_get('db_maintenance_backup_directory', '/tmp');
410      $dateformat = 'Ymd_H-i-s';
411      $dbname = db_maintenance_get_db_info('dbname');
412      $now = time();
413      $date = date($dateformat, $now);
414      if (is_file($tarpath) && is_dir($filespath) && is_dir($backupdir)) {
415        $backupname = "{$date}_{$dbname}_files.tar.gz";
416        $command = "tar -cvzf {$backupdir}/{$backupname} {$filespath}";
417        exec($command, $output, $return);
418    
419        // $return is return value of exec'd command (0 is okay status)
420        if ($return) {
421          watchdog('db_maintenance', $output, WATCHDOG_ERROR);
422          return FALSE;
423        }
424        else {
425          if (!is_file("{$backupdir}/{$backupname}")) {
426            watchdog('db_maintenance', t('failed to create files backup file') .': '. $output, WATCHDOG_ERROR);
427            return FALSE;
428          }
429          return "{$backupdir}/{$backupname}";
430        }
431      }
432      else {
433        watchdog('db_maintenance', t('Files dir not present, backup dir not present or path to tar incorrect'), WATCHDOG_ERROR);
434        return FALSE;
435      }
436    }
437    
438    function db_maintenance_get_db_info($param) {
439      global $db_url;
440      $url = parse_url($db_url);
441    
442      switch (strtolower($param)) {
443        case 'pass':
444        case 'password':
445          if (isset($url['pass'])) {
446            $return = urldecode($url['pass']);
447          }
448          else {
449            $return = '';
450          }
451          break;
452        case 'user':
453        case 'username':
454          $return = urldecode($url['user']);
455          break;
456        case 'host':
457        case 'hostname':
458          $return = urldecode($url['host']);
459          break;
460        case 'db':
461        case 'database':
462        case 'dbname':
463        case 'name':
464          $url['path'] = urldecode($url['path']);
465          $return = substr($url['path'], 1);
466          break;
467        case 'port':
468          if (isset($url['port'])) {
469            $return = urldecode($url['port']);
470          }
471          else {
472            $return = '';
473          }
474          break;
475        default:
476          $return = '';
477          break;
478      }
479      return $return;
480    }
481    
482    function db_maintenance_get_mysql_options() {
483      global $db_url;
484      $url = parse_url($db_url);
485    
486      $url['host'] = urldecode($url['host']);
487      $url['path'] = urldecode($url['path']);
488      $url['user'] = urldecode($url['user']);
489    
490      if (isset($url['pass'])) {
491        $url['pass'] = urldecode($url['pass']);
492      }
493      else {
494        $url['pass'] = '';
495      }
496    
497      if ($url['host']) {
498        $options[] = '--host='. $url['host'];
499      }
500      if (!empty($url['port'])) {
501        $options[] = '--port='. $url['port'];
502      }
503      if ($url['pass'] != '') {
504        $options[] = '--password='. $url['pass'];
505      }
506      if ($url['user']) {
507        $options[] = '--user='. $url['user'];
508      }
509    
510      $return = implode(' ', $options);
511    
512      $return .= ' '. substr($url['path'], 1); // db name is 'path' with '/' pre-pended
513    
514      return $return;
515    }
516    
517  /**  /**
518   * Implementation of hook_block().   * Implementation of hook_block().
519   *   *
# Line 224  function db_maintenance_block($op = 'lis Line 528  function db_maintenance_block($op = 'lis
528      return $blocks;      return $blocks;
529      break;      break;
530    
   case 'configure':  
   case 'save':  
     break;  
   
531    case 'view':    case 'view':
532      if (user_access('access content')) {      if (user_access('access content')) {
533        return array('content' => theme('db_maintenance_block'));        return array('content' => theme('db_maintenance_block'));
# Line 267  function db_maintenance_db_size_info($db Line 567  function db_maintenance_db_size_info($db
567    $db_size_info['type'] = $bytes[$i];    $db_size_info['type'] = $bytes[$i];
568    return $db_size_info;    return $db_size_info;
569  }  }
570    
571    /**
572     * Implementation of hook_simpletest().
573     */
574    function db_maintenance_simpletest() {
575      // Scan through mymodule/tests directory for any .test files to tell SimpleTest module.
576      $tests = file_scan_directory(drupal_get_path('module', 'db_maintenance') .'/tests', '\.test');
577      return array_keys($tests);
578    }
Legend:
Removed from v.1.9.2.6  
changed lines
  Added in v.1.9.2.6.2.1
  ViewVC Help
Powered by ViewVC 1.1.2