modules/db_maintenance/db_maintenance.module

<?php
// $Id: db_maintenance.module,v 1.14 2009/08/22 04:52:39 deekayen Exp $

/**
 * @file
 * Optimizes database tables during cron runs.
 *
 * @author David Kent Norman
 * @link http://deekayen.net/
 */

/**
 * Implement 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.');
  }
}

/**
 * Implement 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) {
  global $databases;

  $table_names = array();
  // Set the database to query.
  $previous = db_set_active($db);
  if ($databases['default']['default']['driver'] == 'mysql') {
    $result = db_query('SHOW TABLES', array(), array('fetch' => PDO::FETCH_ASSOC));
  }
  elseif ($databases['default']['default']['driver'] == 'pgsql') {
    $result = db_query("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name", array(), array('fetch' => PDO::FETCH_ASSOC));
  }
  // Return to the previously set database.
  db_set_active($previous);
  foreach ($result as $table_name) {
    $table_name = current($table_name);
    $table_names[$table_name] = $table_name;
  }
  return $table_names;
}

/**
 * Implement hook_cron().
 */
function db_maintenance_cron() {
  $last_run = variable_get('db_maintenance_cron_last', 0);
  $interval = time() - variable_get('db_maintenance_cron_frequency', 86400);
  // Only run cron if enough time has elapsed.
  if ($interval > $last_run) {
    db_maintenance_optimize_tables();
  }
}

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

  $db_name = $db = $databases['default']['default']['database'];
  // 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) && sizeof($config_tables) > 0) {
      while (list(, $table_name) = each($config_tables)) {
        // Set the database to query.
        $previous = db_set_active($db);
        if (db_table_exists($table_name)) {
          // PDO doesn't replace table names with ? or :
          // db_table_exists and db_escape_table is redundant, but just doing both
          if ($databases['default']['default']['driver'] == 'mysql') {
            db_query(sprintf("OPTIMIZE TABLE %s", db_escape_table($table_name)));
          }
          elseif ($databases['default']['default']['driver'] == 'pgsql') {
            db_query(sprintf("VACUUM ANALYZE %s", db_escape_table($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);
        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 $databases;

  $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.')
  );
  $options = array(
    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'       => $options,
    '#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.
  $options = array();
  // Loop through each database and list the possible tables to optimize.
//  foreach ($databases as $db => $connection) {
    $options = _db_maintenance_list_tables($databases['default']['default']['database']);

    $form['db_maintenance_table_list_' . $databases['default']['default']['database']] = array(
      '#type'          => 'select',
      '#title'         => t('Tables in the !db database', array('!db' => $databases['default']['default']['database'] == 'default' ? 'Drupal' : $databases['default']['default']['database'])),
      '#options'       => $options,
      '#default_value' => variable_get('db_maintenance_table_list_' . $databases['default']['default']['database'], ''),
      '#description'   => t('Selected tables will be optimized during cron runs.'),
      '#multiple'      => TRUE,
      '#attributes'    => array('size' => count($options)),
    );
//  }

  return system_settings_form($form);
}
1	<?php
2	// $Id: db_maintenance.module,v 1.14 2009/08/22 04:52:39 deekayen Exp $
3
4	/**
5	* @file
6	* Optimizes database tables during cron runs.
7	*
8	* @author David Kent Norman
9	* @link http://deekayen.net/
10	*/
11
12	/**
13	* Implement hook_help().
14	*
15	* @param $section string
16	* @return string
17	*/
18	function db_maintenance_help($path, $arg) {
19	switch ($path) {
20	case 'admin/help#db_maintenance':
21	return t('<p>DB maintenance performs an optimization query on selected tables.</p>
22	<p>For MyISAM tables,
23	OPTIMIZE TABLE repairs a table if it has deleted or split rows, sorts table indexes,
24	and updates table statistics. For BDB and InnoDB, OPTIMIZE rebuilds the table. Note, MySQL
25	locks tables during the time OPTIMIZE TABLE is running. OPTIMIZE works best on tables with
26	large deletions (e.g. cache or watchdog), however MySQL will reuse old record positions,
27	therefore in most setups, OPTIMIZE TABLE is unnecessary unless you just like defragmenting.</p>
28	<p>The Overhead column in phpMyAdmin\'s database view is the most common way to determine the
29	need of an OPTIMIZE TABLE query. It essentially shows the amount of disk space you would
30	recover by running an optimize/defragmentation query.</p>
31	<p>For PostgreSQL tables, VACUUM reclaims storage occupied by deleted tuples.
32	In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not
33	physically removed from their table; they remain present until a VACUUM is done. Therefore
34	it\'s necessary to do VACUUM periodically, especially on frequently-updated tables.</p>');
35	case 'admin/settings/db_maintenance':
36	return t('Executes an optimization query on database tables during cron runs.');
37	}
38	}
39
40	/**
41	* Implement hook_menu().
42	*
43	* @return array
44	*/
45	function db_maintenance_menu() {
46	$items = array();
47
48	$items['admin/settings/db_maintenance'] = array(
49	'title' => 'DB maintenance',
50	'description' => 'Executes a cron-based query to optimize database tables.',
51	'page callback' => 'drupal_get_form',
52	'page arguments' => array('db_maintenance_admin_settings'),
53	'access callback' => 'user_access',
54	'access arguments' => array('administer site configuration'),
55	'type' => MENU_NORMAL_ITEM
56	);
57
58	$items['db_maintenance/optimize'] = array(
59	'page callback' => 'db_maintenance_optimize_tables_page',
60	'access callback' => 'user_access',
61	'access arguments' => array('administer site configuration'),
62	'type' => MENU_CALLBACK
63	);
64
65	return $items;
66	}
67
68	/**
69	* Callback page for manually optimizing tables.
70	*/
71	function db_maintenance_optimize_tables_page() {
72	db_maintenance_optimize_tables();
73	drupal_set_message(t('Database tables optimized'));
74	drupal_goto('admin/settings/db_maintenance');
75	}
76
77	/**
78	* Get a list of all the tables in a database.
79	*
80	* @param string $db The name of the database connection to query for tables.
81	* @return array representing the tables in the specified database.
82	*/
83	function _db_maintenance_list_tables($db) {
84	global $databases;
85
86	$table_names = array();
87	// Set the database to query.
88	$previous = db_set_active($db);
89	if ($databases['default']['default']['driver'] == 'mysql') {
90	$result = db_query('SHOW TABLES', array(), array('fetch' => PDO::FETCH_ASSOC));
91	}
92	elseif ($databases['default']['default']['driver'] == 'pgsql') {
93	$result = db_query("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name", array(), array('fetch' => PDO::FETCH_ASSOC));
94	}
95	// Return to the previously set database.
96	db_set_active($previous);
97	foreach ($result as $table_name) {
98	$table_name = current($table_name);
99	$table_names[$table_name] = $table_name;
100	}
101	return $table_names;
102	}
103
104	/**
105	* Implement hook_cron().
106	*/
107	function db_maintenance_cron() {
108	$last_run = variable_get('db_maintenance_cron_last', 0);
109	$interval = time() - variable_get('db_maintenance_cron_frequency', 86400);
110	// Only run cron if enough time has elapsed.
111	if ($interval > $last_run) {
112	db_maintenance_optimize_tables();
113	}
114	}
115
116	/**
117	* Perform the maintenance.
118	*/
119	function db_maintenance_optimize_tables() {
120	global $databases;
121
122	$db_name = $db = $databases['default']['default']['database'];
123	// Loop through each database optimizing any selected tables.
124	// foreach ($databases as $db => $connection) {
125	$config_tables = variable_get('db_maintenance_table_list_' . $db, NULL);
126	// Only proceed if tables are selected for this database.
127	if (is_array($config_tables) && sizeof($config_tables) > 0) {
128	while (list(, $table_name) = each($config_tables)) {
129	// Set the database to query.
130	$previous = db_set_active($db);
131	if (db_table_exists($table_name)) {
132	// PDO doesn't replace table names with ? or :
133	// db_table_exists and db_escape_table is redundant, but just doing both
134	if ($databases['default']['default']['driver'] == 'mysql') {
135	db_query(sprintf("OPTIMIZE TABLE %s", db_escape_table($table_name)));
136	}
137	elseif ($databases['default']['default']['driver'] == 'pgsql') {
138	db_query(sprintf("VACUUM ANALYZE %s", db_escape_table($table_name)));
139	}
140	}
141	else {
142	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);
143	}
144	// Return to the previously set database.
145	db_set_active($previous);
146	watchdog('db_maintenance', 'Optimized @table table in @db database.', array('@db' => $db_name, '@table' => $table_name), WATCHDOG_DEBUG);
147	}
148	if (variable_get('db_maintenance_log', 0)) {
149	$tables = implode(', ', $config_tables);
150	watchdog('db_maintenance', 'Optimized tables in @db database: @tables', array('@db' => $db_name, '@tables' => $tables), WATCHDOG_INFO);
151	}
152	}
153	// }
154	variable_set('db_maintenance_cron_last', time());
155	}
156
157	/**
158	* Administration settings
159	*
160	* options: log each optimization
161	* multi-select list of tables to optimize
162	*
163	* @return array
164	*/
165	function db_maintenance_admin_settings() {
166	global $databases;
167
168	$form = array();
169	$form['db_maintenance_log'] = array(
170	'#type' => 'checkbox',
171	'#title' => 'Log OPTIMIZE queries',
172	'#default_value' => variable_get('db_maintenance_log', 0),
173	'#description' => t('If enabled, a watchdog entry will be made each time tables are optimized, containing information which tables were involved.')
174	);
175	$options = array(
176	0 => t('Run during every cron'),
177	3600 => t('Hourly'),
178	7200 => t('Bi-Hourly'),
179	86400 => t('Daily'),
180	172800 => t('Bi-Daily'),
181	604800 => t('Weekly'),
182	1209600 => t('Bi-Weekly'),
183	2592000 => t('Monthly'),
184	5184000 => t('Bi-Monthly')
185	);
186	$form['db_maintenance_cron_frequency'] = array(
187	'#type' => 'select',
188	'#title' => t('Optimize tables'),
189	'#options' => $options,
190	'#default_value' => variable_get('db_maintenance_cron_frequency', 86400),
191	'#description' => t('Select how often database tables should be optimized.') . ' ' . l(t('Optimize now.'), 'db_maintenance/optimize'),
192	);
193	// Set the databases array if not already set in $db_url.
194	$options = array();
195	// Loop through each database and list the possible tables to optimize.
196	// foreach ($databases as $db => $connection) {
197	$options = _db_maintenance_list_tables($databases['default']['default']['database']);
198
199	$form['db_maintenance_table_list_' . $databases['default']['default']['database']] = array(
200	'#type' => 'select',
201	'#title' => t('Tables in the !db database', array('!db' => $databases['default']['default']['database'] == 'default' ? 'Drupal' : $databases['default']['default']['database'])),
202	'#options' => $options,
203	'#default_value' => variable_get('db_maintenance_table_list_' . $databases['default']['default']['database'], ''),
204	'#description' => t('Selected tables will be optimized during cron runs.'),
205	'#multiple' => TRUE,
206	'#attributes' => array('size' => count($options)),
207	);
208	// }
209
210	return system_settings_form($form);
211	}