<?php 
// Check and repair all tables moodle 
// Ken Wilson 01/10/06

define('IN_MDLBB', true); 
$root_path = './'; 
include($root_path . 'config.php'); 

echo ("<h1>Moodle Database Optimization and Analysis</h1>");

// connect to the database server 
$db = mysql_connect($CFG->dbhost,$CFG->dbuser,$CFG->dbpass); 

if (!$db) {
	die ('Unable to connect to database!\n'.mysql_error());
} else {
	echo ("Database connected successfully\n" . "<br>\n");
}

// select the moodle database 
mysql_select_db($CFG->dbname,$db); 

// get a list of tables for this database 
$tablequery = "show tables like '".$$CFG->prefix."%'"; 
$tablelist = mysql_query($tablequery,$db); 
echo ("Got list of tables OK" . "<br><br>\n");

// cycle through them for repair 
while ($tar = mysql_fetch_array($tablelist)) 
{ 
   $tablename = $tar[0]; 
   // Check table indexes and attempt repair if needed 
   mtrace ("Now checking table $tablename ... "); 
   $repres = mysql_query("CHECK TABLE $tablename"); 
   $result = mysql_fetch_array($repres);
   mtrace ($result['Msg_text'] . "<br>\n");
   if ($result['Msg_text'] <> "OK") {
      //table has crashed so run a repair
      mtrace ("Crashed table $tablename. Now attempting to repair indexes ...");
      $repres = mysql_query("REPAIR TABLE $tablename QUICK"); 
      $result = mysql_fetch_array($repres); 
      mtrace ($result['Msg_text'] . "<br>\n");
      if ($result['Msg_text'] <> "OK") {
          // Quick repair did not work, so try extended
          mtrace ("Cannot quick repair $tablename. Now attempting full table repair ...");
          $repres = mysql_query("REPAIR TABLE $tablename EXTENDED"); 
          $result = mysql_fetch_array($repres); 
          mtrace ($result['Msg_text'] . "<br>\n");
          if ($result['Msg_text'] <> "OK") {
          // Extended repair did not work, so try use_frm
              mtrace ("Cannot repair $tablename. Now attempting index rebuild ...");
              $repres = mysql_query("REPAIR TABLE $tablename USE_FRM"); 
              $result = mysql_fetch_array($repres); 
              mtrace ($result['Msg_text'] . "<br>\n");
          } 
      }
   }
   // Optimize table
   mtrace ("Now optimizing table $tablename ... "); 
   $repres = mysql_query("OPTIMIZE TABLE $tablename"); 
   $result = mysql_fetch_array($repres);
   mtrace ($result['Msg_text'] . "<br>\n");
   // Analyze table 
   mtrace ("Now analyzing table $tablename ... "); 
   $repres = mysql_query("ANALYZE TABLE $tablename"); 
   $result = mysql_fetch_array($repres);
   mtrace ($result['Msg_text'] . "<br><br>\n"); 
} 
mtrace ("All Moodle tables have been checked, analyzed and optimized");
?>