Improvement issue 0000319:

Add new item on page administration/maintenance.
This item allow to repair and optimize database

Functionality tested with MySQL 4.1.9.
Please to report problem with previous version of MySql

git-svn-id: http://piwigo.org/svn/trunk@1111 68402e56-0260-453c-a942-63ccdbb3a9ee

2 files changed, 70 insertions, 27 deletions

diff --git a/admin/include/functions.php b/admin/include/functions.php
index 4dd2869ef..7168e79d9 100644
--- a/admin/include/functions.php
+++ b/admin/include/functions.php
@@ -2092,4 +2092,57 @@ UPDATE
     'id'   => $inserted_id,
     );
 }
+
+/**
+ * Do maintenance on all PWG tables
+ *
+ * @return nono
+ */
+function do_maintenance_all_tables()
+{
+  global $prefixeTable;
+  
+  $all_tables = array();
+
+  // List all tables
+  $query = 'SHOW TABLES LIKE \''.$prefixeTable.'%\';';
+  $result = pwg_query($query);
+  while ($row = mysql_fetch_array($result))
+  {
+    array_push($all_tables, $row[0]);
+  }
+
+  // Repair all tables
+  $query = 'REPAIR TABLE '.implode(', ', $all_tables).';';
+  pwg_query($query);
+
+  // Re-Order all tables
+  foreach ($all_tables as $table_name)
+  {
+    $all_primary_key = array();
+    
+    $query = 'DESC '.$table_name.';';
+    $result = pwg_query($query);
+    while ($row = mysql_fetch_array($result))
+    {
+      if ($row['Key'] == 'PRI')
+      {
+        array_push($all_primary_key, $row['Field']);
+      }
+    }
+    
+    if (count($all_primary_key) != 0)
+    {
+      $query = 'ALTER TABLE '.$table_name.' ORDER BY '.implode(', ', $all_primary_key).';';
+      pwg_query($query);
+    }
+  }
+
+  // Optimize all tables
+  $query = 'OPTIMIZE TABLE '.implode(', ', $all_tables).';';
+  pwg_query($query);
+
+}
+
+
 ?>
diff --git a/admin/maintenance.php b/admin/maintenance.php
index c7caf8e50..de6c2a4a1 100644
--- a/admin/maintenance.php
+++ b/admin/maintenance.php
@@ -41,7 +41,7 @@ check_status(ACCESS_ADMINISTRATOR);
 // |                                actions                                |
 // +-----------------------------------------------------------------------+
 
-$action = isset($_GET['action']) ? $_GET['action'] : '';
+$action = (isset($_GET['action']) and !is_adviser()) ? $_GET['action'] : '';
 
 switch ($action)
 {
@@ -84,6 +84,11 @@ DELETE
     pwg_query($query);
     break;
   }
+  case 'database' :
+  {
+    do_maintenance_all_tables();
+    break;
+  }
   default :
   {
     break;
@@ -98,32 +103,17 @@ $template->set_filenames(array('maintenance'=>'admin/maintenance.tpl'));
 
 $start_url = PHPWG_ROOT_PATH.'admin.php?page=maintenance&action=';
 
-if (!is_adviser())
-{
-  $template->assign_vars(
-    array(
-      'U_MAINT_CATEGORIES' => $start_url.'categories',
-      'U_MAINT_IMAGES' => $start_url.'images',
-      'U_MAINT_HISTORY' => $start_url.'history',
-      'U_MAINT_SESSIONS' => $start_url.'sessions',
-      'U_MAINT_FEEDS' => $start_url.'feeds',
-      'U_HELP' => PHPWG_ROOT_PATH.'/popuphelp.php?page=maintenance',
-      )
-    );
-}
-else
-{
-  $template->assign_vars(
-    array(
-      'U_MAINT_CATEGORIES' => $start_url,
-      'U_MAINT_IMAGES' => $start_url,
-      'U_MAINT_HISTORY' => $start_url,
-      'U_MAINT_SESSIONS' => $start_url,
-      'U_MAINT_FEEDS' => $start_url,
-      'U_HELP' => PHPWG_ROOT_PATH.'/popuphelp.php?page=maintenance',
-      )
-    );
-}
+$template->assign_vars(
+  array(
+    'U_MAINT_CATEGORIES' => $start_url.'categories',
+    'U_MAINT_IMAGES' => $start_url.'images',
+    'U_MAINT_HISTORY' => $start_url.'history',
+    'U_MAINT_SESSIONS' => $start_url.'sessions',
+    'U_MAINT_FEEDS' => $start_url.'feeds',
+    'U_MAINT_DATABASE' => $start_url.'database',
+    'U_HELP' => PHPWG_ROOT_PATH.'/popuphelp.php?page=maintenance',
+    )
+  );
 
 // +-----------------------------------------------------------------------+
 // |                           sending html code                           |