From 0b7a3f716ad59c1b6db88cc721b6d158cf4833a6 Mon Sep 17 00:00:00 2001
From: Marc Delisle <marc@infomarc.info>
Date: Fri, 12 Feb 2010 09:37:29 +0000
Subject: [PATCH] bug 2941037 - Database structure not sorted by table
 correctly

---
 ChangeLog                            |  1 +
 libraries/database_interface.lib.php | 81 +++++++++++++++---------------------
 2 files changed, 35 insertions(+), 47 deletions(-)

diff --git a/ChangeLog b/ChangeLog
index ecdea4c610..45e79dee20 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -6,6 +6,7 @@ $Id$
 $HeadURL: https://phpmyadmin.svn.sourceforge.net/svnroot/phpmyadmin/trunk/phpMyAdmin/ChangeLog $
 
 3.3.1.0 (not yet released)
+- bug #2941037 [core] Database structure not sorted by table correctly 
 
 3.3.0.0 (not yet released)
 + rfe #2308632 [edit] Use hex for (var)binary fields,
diff --git a/libraries/database_interface.lib.php b/libraries/database_interface.lib.php
index 50e1e1629d..0dab59013c 100644
--- a/libraries/database_interface.lib.php
+++ b/libraries/database_interface.lib.php
@@ -263,19 +263,19 @@ function PMA_DBI_get_tables_full($database, $table = false, $tbl_is_group = fals
 
     if (! $GLOBALS['cfg']['Server']['DisableIS']) {
       // get table information from information_schema
-      if ($table) {
-          if (true === $tbl_is_group) {
-              $sql_where_table = 'AND `TABLE_NAME` LIKE \''
+        if ($table) {
+            if (true === $tbl_is_group) {
+                $sql_where_table = 'AND `TABLE_NAME` LIKE \''
                   . PMA_escape_mysql_wildcards(addslashes($table)) . '%\'';
-          } elseif ('comment' === $tbl_is_group) {
-              $sql_where_table = 'AND `TABLE_COMMENT` LIKE \''
+            } elseif ('comment' === $tbl_is_group) {
+                $sql_where_table = 'AND `TABLE_COMMENT` LIKE \''
                   . PMA_escape_mysql_wildcards(addslashes($table)) . '%\'';
-          } else {
-              $sql_where_table = 'AND `TABLE_NAME` = \'' . addslashes($table) . '\'';
-          }
-      } else {
-          $sql_where_table = '';
-      }
+            } else {
+                $sql_where_table = 'AND `TABLE_NAME` = \'' . addslashes($table) . '\'';
+            }
+        } else {
+            $sql_where_table = '';
+        }
 
       // for PMA bc:
       // `SCHEMA_FIELD_NAME` AS `SHOW_TABLE_STATUS_FIELD_NAME`
@@ -284,9 +284,9 @@ function PMA_DBI_get_tables_full($database, $table = false, $tbl_is_group = fals
       // added BINARY in the WHERE clause to force a case sensitive
       // comparison (if we are looking for the db Aa we don't want
       // to find the db aa)
-      $this_databases = array_map('PMA_sqlAddslashes', $databases);
+        $this_databases = array_map('PMA_sqlAddslashes', $databases);
 
-      $sql = '
+        $sql = '
            SELECT *,
                   `TABLE_SCHEMA`       AS `Db`,
                   `TABLE_NAME`         AS `Name`,
@@ -312,41 +312,28 @@ function PMA_DBI_get_tables_full($database, $table = false, $tbl_is_group = fals
             WHERE ' . (PMA_IS_WINDOWS ? '' : 'BINARY') . ' `TABLE_SCHEMA` IN (\'' . implode("', '", $this_databases) . '\')
               ' . $sql_where_table;
 
-      // Sort the tables
-      if ($sort_by == 'Name' && $GLOBALS['cfg']['NaturalOrder']) {
-          // This crazy bit of SQL was inspired by a post here:
-          // http://forums.mysql.com/read.php?10,34908,35959#msg-35959
-
-          // Find the longest table name
-          $max_name_sql = "SELECT MAX(LENGTH(TABLE_NAME)) FROM `information_schema`.`TABLES`
-                           WHERE `TABLE_SCHEMA` IN ('" . implode("', '", $this_databases) . "')"; 
-          $max_name_array = PMA_DBI_fetch_result($max_name_sql);
-          $max_name_length = $max_name_array[0];
-
-          // Put the CASE statement SQL together.
-          $sql_case = '';
-          for ($i = 1; $i < $max_name_length; $i++) {
-              $sql_case .= " when substr(Name, $i) between '0' and '9' then $i";
-          }
-          $sql_case .= " ELSE $max_name_length end) ";
-
-          // Add the CASE statement to the main SQL
-          $sql .= " ORDER BY left(Name, (CASE ";
-          $sql .= $sql_case . "-1) $sort_order, 0+substr(Name, CASE";
-          $sql .= $sql_case . $sort_order;
-      } else {
-          // Just let MySQL sort as it normally does
-          $sql .= " ORDER BY $sort_by $sort_order";
-      }
-
-      if ($limit_count) {
-          $sql .= ' LIMIT ' . $limit_count . ' OFFSET ' . $limit_offset;
-      }
-
-      $tables = PMA_DBI_fetch_result($sql, array('TABLE_SCHEMA', 'TABLE_NAME'),
+        // Sort the tables
+        $sql .= " ORDER BY $sort_by $sort_order";
+
+        if ($limit_count) {
+            $sql .= ' LIMIT ' . $limit_count . ' OFFSET ' . $limit_offset;
+        }
+
+        $tables = PMA_DBI_fetch_result($sql, array('TABLE_SCHEMA', 'TABLE_NAME'),
           null, $link);
-      unset($sql_where_table, $sql);
-    }
+        unset($sql_where_table, $sql);
+        if ($sort_by == 'Name' && $GLOBALS['cfg']['NaturalOrder']) {
+            // here, the array's first key is by schema name
+            foreach($tables as $one_database_name => $one_database_tables) {
+                uksort($one_database_tables, 'strnatcasecmp');
+
+                if ($sort_order == 'DESC') {
+                    $one_database_tables = array_reverse($one_database_tables);
+                }
+                $tables[$one_database_name] = $one_database_tables;
+            }
+        }
+    } // end (get information from table schema)
 
     // If permissions are wrong on even one database directory,
     // information_schema does not return any table info for any database
-- 
2.11.4.GIT