2 /* vim: set expandtab sw=4 ts=4 sts=4: */
4 * Main interface for database interactions
6 * @package PhpMyAdmin-DBI
8 if (! defined('PHPMYADMIN')) {
12 require_once './libraries/logging.lib.php';
13 require_once './libraries/Index.class.php';
14 require_once './libraries/SystemDatabase.class.php';
17 * Main interface for database interactions
19 * @package PhpMyAdmin-DBI
21 class PMA_DatabaseInterface
24 * Force STORE_RESULT method, ignored by classic MySQL.
26 const QUERY_STORE
= 1;
28 * Do not read whole query.
30 const QUERY_UNBUFFERED
= 2;
32 * Get session variable.
34 const GETVAR_SESSION
= 1;
36 * Get global variable.
38 const GETVAR_GLOBAL
= 2;
41 * @var PMA_DBI_Extension
48 * @param PMA_DBI_Extension $ext Object to be used for database queries
50 function __construct($ext)
52 $this->_extension
= $ext;
56 * Checks whether database extension is loaded
58 * @param string $extension mysql extension to check
62 public static function checkDbExtension($extension = 'mysql')
64 if ($extension == 'drizzle' && function_exists('drizzle_create')) {
66 } else if (function_exists($extension . '_connect')) {
75 * @param string $query SQL query to execute
76 * @param mixed $link optional database link to use
77 * @param int $options optional query options
78 * @param bool $cache_affected_rows whether to cache affected rows
82 public function query($query, $link = null, $options = 0,
83 $cache_affected_rows = true
85 $res = $this->tryQuery($query, $link, $options, $cache_affected_rows)
86 or PMA_Util
::mysqlDie($this->getError($link), $query);
92 * Caches table data so PMA_Table does not require to issue
93 * SHOW TABLE STATUS again
95 * @param array $tables information for tables of some databases
96 * @param string $table table name
100 private function _cacheTableData($tables, $table)
102 // Note: I don't see why we would need array_merge_recursive() here,
103 // as it creates double entries for the same table (for example a double
104 // entry for Comment when changing the storage engine in Operations)
105 // Note 2: Instead of array_merge(), simply use the + operator because
106 // array_merge() renumbers numeric keys starting with 0, therefore
107 // we would lose a db name that consists only of numbers
109 foreach ($tables as $one_database => $its_tables) {
110 if (isset(PMA_Table
::$cache[$one_database])) {
111 // the + operator does not do the intended effect
112 // when the cache for one table already exists
114 && isset(PMA_Table
::$cache[$one_database][$table])
116 unset(PMA_Table
::$cache[$one_database][$table]);
118 PMA_Table
::$cache[$one_database]
119 = PMA_Table
::$cache[$one_database] +
$tables[$one_database];
121 PMA_Table
::$cache[$one_database] = $tables[$one_database];
127 * Stores query data into session data for debugging purposes
129 * @param string $query Query text
130 * @param object $link database link
131 * @param object|boolean $result Query result
132 * @param integer $time Time to execute query
136 private function _dbgQuery($query, $link, $result, $time)
139 $error_message = $this->getError($link);
140 if ($result == false && is_string($error_message)) {
142 = '<span style="color:red">'
143 . htmlspecialchars($error_message) . '</span>';
145 $dbgInfo['query'] = htmlspecialchars($query);
146 $dbgInfo['time'] = $time;
147 // Get and slightly format backtrace
148 $dbgInfo['trace'] = debug_backtrace();
149 foreach ($dbgInfo['trace'] as $key => $step) {
150 if (isset($step['file'])) {
151 $dbgInfo['trace'][$key]['file'] = PMA_Error
::relPath($step['file']);
154 $dbgInfo['hash'] = md5($query);
156 $_SESSION['debug']['queries'][] = $dbgInfo;
160 * runs a query and returns the result
162 * @param string $query query to run
163 * @param object $link mysql link resource
164 * @param integer $options query options
165 * @param bool $cache_affected_rows whether to cache affected row
169 public function tryQuery($query, $link = null, $options = 0,
170 $cache_affected_rows = true
172 $link = $this->getLink($link);
173 if ($link === false) {
177 if ($GLOBALS['cfg']['DBG']['sql']) {
178 $time = microtime(true);
181 $result = $this->_extension
->realQuery($query, $link, $options);
183 if ($cache_affected_rows) {
184 $GLOBALS['cached_affected_rows'] = $this->affectedRows($link, false);
187 if ($GLOBALS['cfg']['DBG']['sql']) {
188 $time = microtime(true) - $time;
189 $this->_dbgQuery($query, $link, $result, $time);
191 if ($result != false && PMA_Tracker
::isActive() == true ) {
192 PMA_Tracker
::handleQuery($query);
199 * Run multi query statement and return results
201 * @param string $multi_query multi query statement to execute
202 * @param mysqli $link mysqli object
204 * @return mysqli_result collection | boolean(false)
206 public function tryMultiQuery($multi_query = '', $link = null)
208 $link = $this->getLink($link);
209 if ($link === false) {
213 return $this->_extension
->realMultiQuery($link, $multi_query);
217 * converts charset of a mysql message, usually coming from mysql_error(),
218 * into PMA charset, usually UTF-8
219 * uses language to charset mapping from mysql/share/errmsg.txt
220 * and charset names to ISO charset from information_schema.CHARACTER_SETS
222 * @param string $message the message
224 * @return string $message
226 public function convertMessage($message)
228 // latin always last!
230 'japanese' => 'EUC-JP', //'ujis',
231 'japanese-sjis' => 'Shift-JIS', //'sjis',
232 'korean' => 'EUC-KR', //'euckr',
233 'russian' => 'KOI8-R', //'koi8r',
234 'ukrainian' => 'KOI8-U', //'koi8u',
235 'greek' => 'ISO-8859-7', //'greek',
236 'serbian' => 'CP1250', //'cp1250',
237 'estonian' => 'ISO-8859-13', //'latin7',
238 'slovak' => 'ISO-8859-2', //'latin2',
239 'czech' => 'ISO-8859-2', //'latin2',
240 'hungarian' => 'ISO-8859-2', //'latin2',
241 'polish' => 'ISO-8859-2', //'latin2',
242 'romanian' => 'ISO-8859-2', //'latin2',
243 'spanish' => 'CP1252', //'latin1',
244 'swedish' => 'CP1252', //'latin1',
245 'italian' => 'CP1252', //'latin1',
246 'norwegian-ny' => 'CP1252', //'latin1',
247 'norwegian' => 'CP1252', //'latin1',
248 'portuguese' => 'CP1252', //'latin1',
249 'danish' => 'CP1252', //'latin1',
250 'dutch' => 'CP1252', //'latin1',
251 'english' => 'CP1252', //'latin1',
252 'french' => 'CP1252', //'latin1',
253 'german' => 'CP1252', //'latin1',
256 $server_language = PMA_Util
::cacheGet(
259 return $GLOBALS['dbi']->fetchValue(
260 "SHOW VARIABLES LIKE 'language'", 0, 1
264 if ($server_language) {
267 '&(?:\\\|\\/)([^\\\\\/]*)(?:\\\|\\/)$&i',
272 $server_language = $found[1];
276 if (! empty($server_language) && isset($encodings[$server_language])) {
277 $encoding = $encodings[$server_language];
279 /* Fallback to CP1252 if we can not detect */
280 $encoding = 'CP1252';
283 return PMA_convertString($encoding, 'utf-8', $message);
287 * returns array with table names for given db
289 * @param string $database name of database
290 * @param mixed $link mysql link resource|object
292 * @return array tables names
294 public function getTables($database, $link = null)
296 return $this->fetchResult(
297 'SHOW TABLES FROM ' . PMA_Util
::backquote($database) . ';',
306 * returns a segment of the SQL WHERE clause regarding table name and type
308 * @param string $table table
309 * @param boolean $tbl_is_group $table is a table group
310 * @param string $table_type whether table or view
312 * @return string a segment of the WHERE clause
314 private function _getTableCondition($table, $tbl_is_group, $table_type)
316 // get table information from information_schema
318 if (true === $tbl_is_group) {
319 $sql_where_table = 'AND t.`TABLE_NAME` LIKE \''
320 . PMA_Util
::escapeMysqlWildcards(
321 PMA_Util
::sqlAddSlashes($table)
325 $sql_where_table = 'AND t.`TABLE_NAME` = \''
326 . PMA_Util
::sqlAddSlashes($table) . '\'';
329 $sql_where_table = '';
333 if ($table_type == 'view') {
335 $sql_where_table .= " AND t.`TABLE_TYPE` != 'BASE'";
337 $sql_where_table .= " AND t.`TABLE_TYPE` != 'BASE TABLE'";
339 } else if ($table_type == 'table') {
341 $sql_where_table .= " AND t.`TABLE_TYPE` = 'BASE'";
343 $sql_where_table .= " AND t.`TABLE_TYPE` = 'BASE TABLE'";
347 return $sql_where_table;
351 * returns the beginning of the SQL statement to fetch the list of tables
353 * @param string[] $this_databases databases to list
354 * @param string $sql_where_table additional condition
356 * @return string the SQL statement
358 private function _getSqlForTablesFull($this_databases, $sql_where_table)
361 $stats_join = $this->_getDrizzeStatsJoin();
363 // data_dictionary.table_cache may not contain any data
364 // for some tables, it's just a table cache
365 // auto_increment == 0 is cast to NULL because currently
367 // Drizzle doesn't provide correct value
370 t.TABLE_SCHEMA AS `Db`,
371 t.TABLE_NAME AS `Name`,
372 t.TABLE_TYPE AS `TABLE_TYPE`,
373 t.ENGINE AS `Engine`,
375 t.TABLE_VERSION AS `Version`,-- VERSION
376 t.ROW_FORMAT AS `Row_format`,
377 coalesce(tc.ROWS, stat.NUM_ROWS)
378 AS `Rows`,-- TABLE_ROWS,
379 coalesce(tc.ROWS, stat.NUM_ROWS)
381 tc.AVG_ROW_LENGTH AS `Avg_row_length`, -- AVG_ROW_LENGTH
382 tc.TABLE_SIZE AS `Data_length`, -- DATA_LENGTH
383 NULL AS `Max_data_length`, -- MAX_DATA_LENGTH
384 NULL AS `Index_length`, -- INDEX_LENGTH
385 NULL AS `Data_free`, -- DATA_FREE
386 nullif(t.AUTO_INCREMENT, 0)
388 t.TABLE_CREATION_TIME AS `Create_time`, -- CREATE_TIME
389 t.TABLE_UPDATE_TIME AS `Update_time`, -- UPDATE_TIME
390 NULL AS `Check_time`, -- CHECK_TIME
391 t.TABLE_COLLATION AS `Collation`,
392 NULL AS `Checksum`, -- CHECKSUM
393 NULL AS `Create_options`, -- CREATE_OPTIONS
394 t.TABLE_COMMENT AS `Comment`
395 FROM data_dictionary.TABLES t
396 LEFT JOIN data_dictionary.TABLE_CACHE tc
397 ON tc.TABLE_SCHEMA = t.TABLE_SCHEMA AND tc.TABLE_NAME
400 WHERE t.TABLE_SCHEMA IN ('" . implode("', '", $this_databases) . "')
401 " . $sql_where_table;
405 `TABLE_SCHEMA` AS `Db`,
406 `TABLE_NAME` AS `Name`,
407 `TABLE_TYPE` AS `TABLE_TYPE`,
408 `ENGINE` AS `Engine`,
410 `VERSION` AS `Version`,
411 `ROW_FORMAT` AS `Row_format`,
412 `TABLE_ROWS` AS `Rows`,
413 `AVG_ROW_LENGTH` AS `Avg_row_length`,
414 `DATA_LENGTH` AS `Data_length`,
415 `MAX_DATA_LENGTH` AS `Max_data_length`,
416 `INDEX_LENGTH` AS `Index_length`,
417 `DATA_FREE` AS `Data_free`,
418 `AUTO_INCREMENT` AS `Auto_increment`,
419 `CREATE_TIME` AS `Create_time`,
420 `UPDATE_TIME` AS `Update_time`,
421 `CHECK_TIME` AS `Check_time`,
422 `TABLE_COLLATION` AS `Collation`,
423 `CHECKSUM` AS `Checksum`,
424 `CREATE_OPTIONS` AS `Create_options`,
425 `TABLE_COMMENT` AS `Comment`
426 FROM `information_schema`.`TABLES` t
427 WHERE ' . (PMA_IS_WINDOWS ?
'' : 'BINARY') . ' `TABLE_SCHEMA`
428 IN (\'' . implode("', '", $this_databases) . '\')
429 ' . $sql_where_table;
435 * returns array of all tables in given db or dbs
436 * this function expects unquoted names:
438 * WRONG: `my_database`
439 * WRONG: my\_database
440 * if $tbl_is_group is true, $table is used as filter for table names
443 * $GLOBALS['dbi']->getTablesFull('my_database');
444 * $GLOBALS['dbi']->getTablesFull('my_database', 'my_table'));
445 * $GLOBALS['dbi']->getTablesFull('my_database', 'my_tables_', true));
448 * @param string $database database
449 * @param string $table table name
450 * @param boolean $tbl_is_group $table is a table group
451 * @param mixed $link mysql link
452 * @param integer $limit_offset zero-based offset for the count
453 * @param boolean|integer $limit_count number of tables to return
454 * @param string $sort_by table attribute to sort by
455 * @param string $sort_order direction to sort (ASC or DESC)
456 * @param string $table_type whether table or view
458 * @todo move into PMA_Table
460 * @return array list of tables in given db(s)
462 public function getTablesFull($database, $table = '',
463 $tbl_is_group = false, $link = null, $limit_offset = 0,
464 $limit_count = false, $sort_by = 'Name', $sort_order = 'ASC',
467 if (true === $limit_count) {
468 $limit_count = $GLOBALS['cfg']['MaxTableList'];
470 // prepare and check parameters
471 if (! is_array($database)) {
472 $databases = array($database);
474 $databases = $database;
479 if (! $GLOBALS['cfg']['Server']['DisableIS']) {
480 $sql_where_table = $this->_getTableCondition(
481 $table, $tbl_is_group, $table_type
485 // `SCHEMA_FIELD_NAME` AS `SHOW_TABLE_STATUS_FIELD_NAME`
487 // on non-Windows servers,
488 // added BINARY in the WHERE clause to force a case sensitive
489 // comparison (if we are looking for the db Aa we don't want
490 // to find the db aa)
491 $this_databases = array_map('PMA_Util::sqlAddSlashes', $databases);
493 $sql = $this->_getSqlForTablesFull($this_databases, $sql_where_table);
496 $sql .= " ORDER BY $sort_by $sort_order";
499 $sql .= ' LIMIT ' . $limit_count . ' OFFSET ' . $limit_offset;
502 $tables = $this->fetchResult(
503 $sql, array('TABLE_SCHEMA', 'TABLE_NAME'), null, $link
507 // correct I_S and D_D names returned by D_D.TABLES -
508 // Drizzle generally uses lower case for them,
509 // but TABLES returns uppercase
510 foreach ((array)$database as $db) {
511 $db_upper = /*overload*/mb_strtoupper($db);
512 if (!isset($tables[$db]) && isset($tables[$db_upper])) {
513 $tables[$db] = $tables[$db_upper];
514 unset($tables[$db_upper]);
519 if ($sort_by == 'Name' && $GLOBALS['cfg']['NaturalOrder']) {
520 // here, the array's first key is by schema name
521 foreach ($tables as $one_database_name => $one_database_tables) {
522 uksort($one_database_tables, 'strnatcasecmp');
524 if ($sort_order == 'DESC') {
525 $one_database_tables = array_reverse($one_database_tables);
527 $tables[$one_database_name] = $one_database_tables;
529 } else if ($sort_by == 'Data_length') { // Size = Data_length + Index_length
530 foreach ($tables as $one_database_name => $one_database_tables) {
532 $one_database_tables,
534 $aLength = $a['Data_length'] +
$a['Index_length'];
535 $bLength = $b['Data_length'] +
$b['Index_length'];
536 return ($aLength == $bLength)
538 : ($aLength < $bLength) ?
-1 : 1;
542 if ($sort_order == 'DESC') {
543 $one_database_tables = array_reverse($one_database_tables);
545 $tables[$one_database_name] = $one_database_tables;
548 } // end (get information from table schema)
550 // If permissions are wrong on even one database directory,
551 // information_schema does not return any table info for any database
552 // this is why we fall back to SHOW TABLE STATUS even for MySQL >= 50002
553 if (empty($tables) && !PMA_DRIZZLE
) {
554 foreach ($databases as $each_database) {
555 if ($table ||
(true === $tbl_is_group) ||
! empty($table_type)) {
556 $sql = 'SHOW TABLE STATUS FROM '
557 . PMA_Util
::backquote($each_database)
560 if ($table ||
(true === $tbl_is_group)) {
561 $sql .= " `Name` LIKE '"
562 . PMA_Util
::escapeMysqlWildcards(
563 PMA_Util
::sqlAddSlashes($table, true)
568 if (! empty($table_type)) {
572 if ($table_type == 'view') {
573 $sql .= " `Comment` = 'VIEW'";
574 } else if ($table_type == 'table') {
575 $sql .= " `Comment` != 'VIEW'";
579 $sql = 'SHOW TABLE STATUS FROM '
580 . PMA_Util
::backquote($each_database);
583 $useStatusCache = false;
585 if (extension_loaded('apc')
586 && isset($GLOBALS['cfg']['Server']['StatusCacheDatabases'])
587 && ! empty($GLOBALS['cfg']['Server']['StatusCacheLifetime'])
589 $statusCacheDatabases
590 = (array) $GLOBALS['cfg']['Server']['StatusCacheDatabases'];
591 if (in_array($each_database, $statusCacheDatabases)) {
592 $useStatusCache = true;
598 if ($useStatusCache) {
599 $cacheKey = 'phpMyAdmin_tableStatus_'
600 . sha1($GLOBALS['cfg']['Server']['host'] . '_' . $sql);
602 $each_tables = apc_fetch($cacheKey);
605 if (! $each_tables) {
606 $each_tables = $this->fetchResult($sql, 'Name', null, $link);
609 if ($useStatusCache) {
611 $cacheKey, $each_tables,
612 $GLOBALS['cfg']['Server']['StatusCacheLifetime']
616 // Sort naturally if the config allows it and we're sorting
618 if ($sort_by == 'Name' && $GLOBALS['cfg']['NaturalOrder']) {
619 uksort($each_tables, 'strnatcasecmp');
621 if ($sort_order == 'DESC') {
622 $each_tables = array_reverse($each_tables);
625 // Prepare to sort by creating array of the selected sort
626 // value to pass to array_multisort
628 // Size = Data_length + Index_length
629 if ($sort_by == 'Data_length') {
630 foreach ($each_tables as $table_name => $table_data) {
631 $
{$sort_by}[$table_name] = strtolower(
632 $table_data['Data_length'] +
$table_data['Index_length']
636 foreach ($each_tables as $table_name => $table_data) {
637 $
{$sort_by}[$table_name]
638 = strtolower($table_data[$sort_by]);
642 if (! empty($
$sort_by)) {
643 if ($sort_order == 'DESC') {
644 array_multisort($
$sort_by, SORT_DESC
, $each_tables);
646 array_multisort($
$sort_by, SORT_ASC
, $each_tables);
650 // cleanup the temporary sort array
655 $each_tables = array_slice(
656 $each_tables, $limit_offset, $limit_count
660 foreach ($each_tables as $table_name => $each_table) {
661 if (! isset($each_tables[$table_name]['Type'])
662 && isset($each_tables[$table_name]['Engine'])
664 // pma BC, same parts of PMA still uses 'Type'
665 $each_tables[$table_name]['Type']
666 =& $each_tables[$table_name]['Engine'];
667 } elseif (! isset($each_tables[$table_name]['Engine'])
668 && isset($each_tables[$table_name]['Type'])
670 // old MySQL reports Type, newer MySQL reports Engine
671 $each_tables[$table_name]['Engine']
672 =& $each_tables[$table_name]['Type'];
675 // MySQL forward compatibility
676 // so pma could use this array as if every server
677 // is of version >5.0
678 // todo : remove and check usage in the rest of the code,
679 // MySQL 5.0 is required by current PMA version
680 $each_tables[$table_name]['TABLE_SCHEMA']
682 $each_tables[$table_name]['TABLE_NAME']
683 =& $each_tables[$table_name]['Name'];
684 $each_tables[$table_name]['ENGINE']
685 =& $each_tables[$table_name]['Engine'];
686 $each_tables[$table_name]['VERSION']
687 =& $each_tables[$table_name]['Version'];
688 $each_tables[$table_name]['ROW_FORMAT']
689 =& $each_tables[$table_name]['Row_format'];
690 $each_tables[$table_name]['TABLE_ROWS']
691 =& $each_tables[$table_name]['Rows'];
692 $each_tables[$table_name]['AVG_ROW_LENGTH']
693 =& $each_tables[$table_name]['Avg_row_length'];
694 $each_tables[$table_name]['DATA_LENGTH']
695 =& $each_tables[$table_name]['Data_length'];
696 $each_tables[$table_name]['MAX_DATA_LENGTH']
697 =& $each_tables[$table_name]['Max_data_length'];
698 $each_tables[$table_name]['INDEX_LENGTH']
699 =& $each_tables[$table_name]['Index_length'];
700 $each_tables[$table_name]['DATA_FREE']
701 =& $each_tables[$table_name]['Data_free'];
702 $each_tables[$table_name]['AUTO_INCREMENT']
703 =& $each_tables[$table_name]['Auto_increment'];
704 $each_tables[$table_name]['CREATE_TIME']
705 =& $each_tables[$table_name]['Create_time'];
706 $each_tables[$table_name]['UPDATE_TIME']
707 =& $each_tables[$table_name]['Update_time'];
708 $each_tables[$table_name]['CHECK_TIME']
709 =& $each_tables[$table_name]['Check_time'];
710 $each_tables[$table_name]['TABLE_COLLATION']
711 =& $each_tables[$table_name]['Collation'];
712 $each_tables[$table_name]['CHECKSUM']
713 =& $each_tables[$table_name]['Checksum'];
714 $each_tables[$table_name]['CREATE_OPTIONS']
715 =& $each_tables[$table_name]['Create_options'];
716 $each_tables[$table_name]['TABLE_COMMENT']
717 =& $each_tables[$table_name]['Comment'];
719 if (strtoupper($each_tables[$table_name]['Comment']) === 'VIEW'
720 && $each_tables[$table_name]['Engine'] == null
722 $each_tables[$table_name]['TABLE_TYPE'] = 'VIEW';
723 } elseif ($each_database == 'information_schema') {
724 $each_tables[$table_name]['TABLE_TYPE'] = 'SYSTEM VIEW';
727 * @todo difference between 'TEMPORARY' and 'BASE TABLE'
730 $each_tables[$table_name]['TABLE_TYPE'] = 'BASE TABLE';
734 $tables[$each_database] = $each_tables;
739 // so PMA_Table does not require to issue SHOW TABLE STATUS again
740 $this->_cacheTableData($tables, $table);
742 if (is_array($database)) {
746 if (isset($tables[$database])) {
747 return $tables[$database];
750 if (isset($tables[/*overload*/mb_strtolower($database)])) {
751 // on windows with lower_case_table_names = 1
753 // with SHOW DATABASES or information_schema.SCHEMATA: `Test`
754 // but information_schema.TABLES gives `test`
756 // https://sourceforge.net/p/phpmyadmin/bugs/2036/
757 return $tables[/*overload*/mb_strtolower($database)];
760 // one database but inexact letter case match
761 // as Drizzle is always case insensitive,
762 // we can safely return the only result
763 if (!PMA_DRIZZLE ||
!count($tables) == 1) {
767 $keys = array_keys($tables);
768 if (/*overload*/mb_strlen(array_pop($keys)) == /*overload*/mb_strlen($database)) {
769 return array_pop($tables);
775 * Copies the table properties to the set of property names used by PMA.
777 * @param array $tables array of table properties
778 * @param string $database database name
780 * @return array array with added properties
782 public function copyTableProperties($tables, $database)
784 foreach ($tables as $table_name => $each_table) {
785 if (! isset($tables[$table_name]['Type'])
786 && isset($tables[$table_name]['Engine'])
788 // pma BC, same parts of PMA still uses 'Type'
789 $tables[$table_name]['Type']
790 =& $tables[$table_name]['Engine'];
791 } elseif (! isset($tables[$table_name]['Engine'])
792 && isset($tables[$table_name]['Type'])
794 // old MySQL reports Type, newer MySQL reports Engine
795 $tables[$table_name]['Engine']
796 =& $tables[$table_name]['Type'];
799 // MySQL forward compatibility
800 // so pma could use this array as if every server
801 // is of version >5.0
802 // todo : remove and check usage in the rest of the code,
803 // MySQL 5.0 is required by current PMA version
804 $tables[$table_name]['TABLE_SCHEMA']
806 $tables[$table_name]['TABLE_NAME']
807 =& $tables[$table_name]['Name'];
808 $tables[$table_name]['ENGINE']
809 =& $tables[$table_name]['Engine'];
810 $tables[$table_name]['VERSION']
811 =& $tables[$table_name]['Version'];
812 $tables[$table_name]['ROW_FORMAT']
813 =& $tables[$table_name]['Row_format'];
814 $tables[$table_name]['TABLE_ROWS']
815 =& $tables[$table_name]['Rows'];
816 $tables[$table_name]['AVG_ROW_LENGTH']
817 =& $tables[$table_name]['Avg_row_length'];
818 $tables[$table_name]['DATA_LENGTH']
819 =& $tables[$table_name]['Data_length'];
820 $tables[$table_name]['MAX_DATA_LENGTH']
821 =& $tables[$table_name]['Max_data_length'];
822 $tables[$table_name]['INDEX_LENGTH']
823 =& $tables[$table_name]['Index_length'];
824 $tables[$table_name]['DATA_FREE']
825 =& $tables[$table_name]['Data_free'];
826 $tables[$table_name]['AUTO_INCREMENT']
827 =& $tables[$table_name]['Auto_increment'];
828 $tables[$table_name]['CREATE_TIME']
829 =& $tables[$table_name]['Create_time'];
830 $tables[$table_name]['UPDATE_TIME']
831 =& $tables[$table_name]['Update_time'];
832 $tables[$table_name]['CHECK_TIME']
833 =& $tables[$table_name]['Check_time'];
834 $tables[$table_name]['TABLE_COLLATION']
835 =& $tables[$table_name]['Collation'];
836 $tables[$table_name]['CHECKSUM']
837 =& $tables[$table_name]['Checksum'];
838 $tables[$table_name]['CREATE_OPTIONS']
839 =& $tables[$table_name]['Create_options'];
840 $tables[$table_name]['TABLE_COMMENT']
841 =& $tables[$table_name]['Comment'];
843 $commentUpper = /*overload*/mb_strtoupper(
844 $tables[$table_name]['Comment']
846 if ($commentUpper === 'VIEW'
847 && $tables[$table_name]['Engine'] == null
849 $tables[$table_name]['TABLE_TYPE'] = 'VIEW';
852 * @todo difference between 'TEMPORARY' and 'BASE TABLE'
855 $tables[$table_name]['TABLE_TYPE'] = 'BASE TABLE';
862 * Get VIEWs in a particular database
864 * @param string $db Database name to look in
866 * @return array $views Set of VIEWs inside the database
868 public function getVirtualTables($db)
871 $tables_full = $this->getTablesFull($db);
874 foreach ($tables_full as $table=>$tmp) {
876 if (PMA_Table
::isView($db, $table)) {
888 * returns array with databases containing extended infos about them
890 * @param string $database database
891 * @param boolean $force_stats retrieve stats also for MySQL < 5
892 * @param object $link mysql link
893 * @param string $sort_by column to order by
894 * @param string $sort_order ASC or DESC
895 * @param integer $limit_offset starting offset for LIMIT
896 * @param bool|int $limit_count row count for LIMIT or true
897 * for $GLOBALS['cfg']['MaxDbList']
899 * @todo move into PMA_List_Database?
901 * @return array $databases
903 public function getDatabasesFull($database = null, $force_stats = false,
904 $link = null, $sort_by = 'SCHEMA_NAME', $sort_order = 'ASC',
905 $limit_offset = 0, $limit_count = false
907 $sort_order = strtoupper($sort_order);
909 if (true === $limit_count) {
910 $limit_count = $GLOBALS['cfg']['MaxDbList'];
913 $apply_limit_and_order_manual = true;
915 if (! $GLOBALS['cfg']['Server']['DisableIS']) {
917 * if $GLOBALS['cfg']['NaturalOrder'] is enabled, we cannot use LIMIT
918 * cause MySQL does not support natural ordering,
919 * we have to do it afterward
922 if (! $GLOBALS['cfg']['NaturalOrder']) {
924 $limit = ' LIMIT ' . $limit_count . ' OFFSET ' . $limit_offset;
927 $apply_limit_and_order_manual = false;
930 // get table information from information_schema
931 if (! empty($database)) {
932 $sql_where_schema = 'WHERE `SCHEMA_NAME` LIKE \''
933 . PMA_Util
::sqlAddSlashes($database) . '\'';
935 $sql_where_schema = '';
939 // data_dictionary.table_cache may not contain any data for some
940 // tables, it's just a table cache
943 s.DEFAULT_COLLATION_NAME';
945 // no TABLE_CACHE data, stable results are better than
946 // constantly changing
948 COUNT(t.TABLE_SCHEMA) AS SCHEMA_TABLES,
949 SUM(stat.NUM_ROWS) AS SCHEMA_TABLE_ROWS';
952 FROM data_dictionary.SCHEMAS s';
954 $stats_join = $this->_getDrizzeStatsJoin();
957 LEFT JOIN data_dictionary.TABLES t
958 ON t.TABLE_SCHEMA = s.SCHEMA_NAME
961 $sql .= $sql_where_schema . '
962 GROUP BY s.SCHEMA_NAME, s.DEFAULT_COLLATION_NAME
963 ORDER BY ' . PMA_Util
::backquote($sort_by) . ' ' . $sort_order
967 CAST(BIN_NAME AS CHAR CHARACTER SET utf8) AS SCHEMA_NAME
970 BINARY s.SCHEMA_NAME AS BIN_NAME,
971 s.DEFAULT_COLLATION_NAME';
974 COUNT(t.TABLE_SCHEMA) AS SCHEMA_TABLES,
975 SUM(t.TABLE_ROWS) AS SCHEMA_TABLE_ROWS,
976 SUM(t.DATA_LENGTH) AS SCHEMA_DATA_LENGTH,
977 SUM(t.MAX_DATA_LENGTH) AS SCHEMA_MAX_DATA_LENGTH,
978 SUM(t.INDEX_LENGTH) AS SCHEMA_INDEX_LENGTH,
979 SUM(t.DATA_LENGTH + t.INDEX_LENGTH)
981 SUM(t.DATA_FREE) AS SCHEMA_DATA_FREE';
984 FROM `information_schema`.SCHEMATA s';
987 LEFT JOIN `information_schema`.TABLES t
988 ON BINARY t.TABLE_SCHEMA = BINARY s.SCHEMA_NAME';
990 $sql .= $sql_where_schema . '
991 GROUP BY BINARY s.SCHEMA_NAME, s.DEFAULT_COLLATION_NAME
993 if ($sort_by == 'SCHEMA_NAME'
994 ||
$sort_by == 'DEFAULT_COLLATION_NAME'
998 $sql .= PMA_Util
::backquote($sort_by)
1004 $databases = $this->fetchResult($sql, 'SCHEMA_NAME', null, $link);
1006 $mysql_error = $this->getError($link);
1007 if (! count($databases) && $GLOBALS['errno']) {
1008 PMA_Util
::mysqlDie($mysql_error, $sql);
1011 // display only databases also in official database list
1012 // f.e. to apply hide_db and only_db
1013 $drops = array_diff(
1014 array_keys($databases), (array) $GLOBALS['pma']->databases
1016 foreach ($drops as $drop) {
1017 unset($databases[$drop]);
1020 $databases = array();
1021 foreach ($GLOBALS['pma']->databases
as $database_name) {
1022 // MySQL forward compatibility
1023 // so pma could use this array as if every server is of version >5.0
1024 // todo : remove and check the rest of the code for usage,
1025 // MySQL 5.0 or higher is required for current PMA version
1026 $databases[$database_name]['SCHEMA_NAME'] = $database_name;
1028 include_once './libraries/mysql_charsets.inc.php';
1029 $databases[$database_name]['DEFAULT_COLLATION_NAME']
1030 = PMA_getDbCollation($database_name);
1034 // get additional info about tables
1035 $databases[$database_name]['SCHEMA_TABLES'] = 0;
1036 $databases[$database_name]['SCHEMA_TABLE_ROWS'] = 0;
1037 $databases[$database_name]['SCHEMA_DATA_LENGTH'] = 0;
1038 $databases[$database_name]['SCHEMA_MAX_DATA_LENGTH'] = 0;
1039 $databases[$database_name]['SCHEMA_INDEX_LENGTH'] = 0;
1040 $databases[$database_name]['SCHEMA_LENGTH'] = 0;
1041 $databases[$database_name]['SCHEMA_DATA_FREE'] = 0;
1043 $res = $this->query(
1044 'SHOW TABLE STATUS FROM '
1045 . PMA_Util
::backquote($database_name) . ';'
1048 if ($res !== false) {
1049 while ($row = $this->fetchAssoc($res)) {
1050 $databases[$database_name]['SCHEMA_TABLES']++
;
1051 $databases[$database_name]['SCHEMA_TABLE_ROWS']
1053 $databases[$database_name]['SCHEMA_DATA_LENGTH']
1054 +
= $row['Data_length'];
1055 $databases[$database_name]['SCHEMA_MAX_DATA_LENGTH']
1056 +
= $row['Max_data_length'];
1057 $databases[$database_name]['SCHEMA_INDEX_LENGTH']
1058 +
= $row['Index_length'];
1060 // for InnoDB, this does not contain the number of
1061 // overhead bytes but the total free space
1062 if ('InnoDB' != $row['Engine']) {
1063 $databases[$database_name]['SCHEMA_DATA_FREE']
1064 +
= $row['Data_free'];
1066 $databases[$database_name]['SCHEMA_LENGTH']
1067 +
= $row['Data_length'] +
$row['Index_length'];
1069 $this->freeResult($res);
1077 * apply limit and order manually now
1078 * (caused by older MySQL < 5 or $GLOBALS['cfg']['NaturalOrder'])
1080 if ($apply_limit_and_order_manual) {
1081 $GLOBALS['callback_sort_order'] = $sort_order;
1082 $GLOBALS['callback_sort_by'] = $sort_by;
1085 array('PMA_DatabaseInterface', '_usortComparisonCallback')
1087 unset($GLOBALS['callback_sort_order'], $GLOBALS['callback_sort_by']);
1093 $databases = array_slice($databases, $limit_offset, $limit_count);
1102 * Generates JOIN part for the Drizzle query to get database/table stats.
1106 private function _getDrizzeStatsJoin()
1108 $engine_info = PMA_Util
::cacheGet('drizzle_engines');
1109 $stats_join = "LEFT JOIN (SELECT 0 NUM_ROWS) AS stat ON false";
1110 if (isset($engine_info['InnoDB'])
1111 && $engine_info['InnoDB']['module_library'] == 'innobase'
1114 = "LEFT JOIN data_dictionary.INNODB_SYS_TABLESTATS stat"
1115 . " ON (t.ENGINE = 'InnoDB' AND stat.NAME"
1116 . " = (t.TABLE_SCHEMA || '/') || t.TABLE_NAME)";
1123 * usort comparison callback
1125 * @param string $a first argument to sort
1126 * @param string $b second argument to sort
1128 * @return integer a value representing whether $a should be before $b in the
1129 * sorted array or not
1133 private static function _usortComparisonCallback($a, $b)
1135 if ($GLOBALS['cfg']['NaturalOrder']) {
1136 $sorter = 'strnatcasecmp';
1138 $sorter = 'strcasecmp';
1140 /* No sorting when key is not present */
1141 if (! isset($a[$GLOBALS['callback_sort_by']])
1142 ||
! isset($b[$GLOBALS['callback_sort_by']])
1147 // return -1 * strnatcasecmp($a["SCHEMA_TABLES"], $b["SCHEMA_TABLES"])
1148 return ($GLOBALS['callback_sort_order'] == 'ASC' ?
1 : -1) * $sorter(
1149 $a[$GLOBALS['callback_sort_by']], $b[$GLOBALS['callback_sort_by']]
1151 } // end of the '_usortComparisonCallback()' method
1154 * returns detailed array with all columns for sql
1156 * @param string $sql_query target SQL query to get columns
1157 * @param array $view_columns alias for columns
1161 public function getColumnMapFromSql($sql_query, $view_columns = array())
1163 $result = $this->tryQuery($sql_query);
1165 if ($result === false) {
1169 $meta = $this->getFieldsMeta(
1173 $nbFields = count($meta);
1174 if ($nbFields <= 0) {
1178 $column_map = array();
1179 $nbColumns = count($view_columns);
1181 for ($i=0; $i < $nbFields; $i++
) {
1184 $map['table_name'] = $meta[$i]->table
;
1185 $map['refering_column'] = $meta[$i]->name
;
1187 if ($nbColumns > 1) {
1188 $map['real_column'] = $view_columns[$i];
1191 $column_map[] = $map;
1198 * returns detailed array with all columns for given table in database,
1199 * or all tables/databases
1201 * @param string $database name of database
1202 * @param string $table name of table to retrieve columns from
1203 * @param string $column name of specific column
1204 * @param mixed $link mysql link resource
1208 public function getColumnsFull($database = null, $table = null,
1209 $column = null, $link = null
1211 if (! $GLOBALS['cfg']['Server']['DisableIS']) {
1212 $sql_wheres = array();
1213 $array_keys = array();
1215 // get columns information from information_schema
1216 if (null !== $database) {
1217 $sql_wheres[] = '`TABLE_SCHEMA` = \''
1218 . PMA_Util
::sqlAddSlashes($database) . '\' ';
1220 $array_keys[] = 'TABLE_SCHEMA';
1222 if (null !== $table) {
1223 $sql_wheres[] = '`TABLE_NAME` = \''
1224 . PMA_Util
::sqlAddSlashes($table) . '\' ';
1226 $array_keys[] = 'TABLE_NAME';
1228 if (null !== $column) {
1229 $sql_wheres[] = '`COLUMN_NAME` = \''
1230 . PMA_Util
::sqlAddSlashes($column) . '\' ';
1232 $array_keys[] = 'COLUMN_NAME';
1236 // `[SCHEMA_FIELD_NAME]` AS `[SHOW_FULL_COLUMNS_FIELD_NAME]`
1238 $sql = "SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
1239 column_name AS `Field`,
1241 WHEN character_maximum_length > 0
1242 THEN concat(lower(data_type), '(', character_maximum_length, ')')
1243 WHEN numeric_precision > 0 OR numeric_scale > 0
1244 THEN concat(lower(data_type), '(', numeric_precision,
1245 ',', numeric_scale, ')')
1246 WHEN enum_values IS NOT NULL
1247 THEN concat(lower(data_type), '(', enum_values, ')')
1248 ELSE lower(data_type) END)
1250 collation_name AS `Collation`,
1253 ELSE 'NO' END) AS `Null`,
1255 WHEN is_used_in_primary THEN 'PRI'
1256 ELSE '' END) AS `Key`,
1257 column_default AS `Default`,
1259 WHEN is_auto_increment THEN 'auto_increment'
1260 WHEN column_default_update
1261 THEN 'on update ' || column_default_update
1262 ELSE '' END) AS `Extra`,
1263 NULL AS `Privileges`,
1264 column_comment AS `Comment`
1265 FROM data_dictionary.columns";
1269 `COLUMN_NAME` AS `Field`,
1270 `COLUMN_TYPE` AS `Type`,
1271 `COLLATION_NAME` AS `Collation`,
1272 `IS_NULLABLE` AS `Null`,
1273 `COLUMN_KEY` AS `Key`,
1274 `COLUMN_DEFAULT` AS `Default`,
1276 `PRIVILEGES` AS `Privileges`,
1277 `COLUMN_COMMENT` AS `Comment`
1278 FROM `information_schema`.`COLUMNS`';
1280 if (count($sql_wheres)) {
1281 $sql .= "\n" . ' WHERE ' . implode(' AND ', $sql_wheres);
1283 return $this->fetchResult($sql, $array_keys, null, $link);
1286 if (null === $database) {
1287 foreach ($GLOBALS['pma']->databases
as $database) {
1288 $columns[$database] = $this->getColumnsFull(
1289 $database, null, null, $link
1293 } elseif (null === $table) {
1294 $tables = $this->getTables($database);
1295 foreach ($tables as $table) {
1296 $columns[$table] = $this->getColumnsFull(
1297 $database, $table, null, $link
1302 $sql = 'SHOW FULL COLUMNS FROM '
1303 . PMA_Util
::backquote($database) . '.' . PMA_Util
::backquote($table);
1304 if (null !== $column) {
1305 $sql .= " LIKE '" . PMA_Util
::sqlAddSlashes($column, true) . "'";
1308 $columns = $this->fetchResult($sql, 'Field', null, $link);
1309 $ordinal_position = 1;
1310 foreach ($columns as $column_name => $each_column) {
1312 // MySQL forward compatibility
1313 // so pma could use this array as if every server is of version >5.0
1314 // todo : remove and check the rest of the code for usage,
1315 // MySQL 5.0 or higher is required for current PMA version
1316 $columns[$column_name]['COLUMN_NAME']
1317 =& $columns[$column_name]['Field'];
1318 $columns[$column_name]['COLUMN_TYPE']
1319 =& $columns[$column_name]['Type'];
1320 $columns[$column_name]['COLLATION_NAME']
1321 =& $columns[$column_name]['Collation'];
1322 $columns[$column_name]['IS_NULLABLE']
1323 =& $columns[$column_name]['Null'];
1324 $columns[$column_name]['COLUMN_KEY']
1325 =& $columns[$column_name]['Key'];
1326 $columns[$column_name]['COLUMN_DEFAULT']
1327 =& $columns[$column_name]['Default'];
1328 $columns[$column_name]['EXTRA']
1329 =& $columns[$column_name]['Extra'];
1330 $columns[$column_name]['PRIVILEGES']
1331 =& $columns[$column_name]['Privileges'];
1332 $columns[$column_name]['COLUMN_COMMENT']
1333 =& $columns[$column_name]['Comment'];
1335 $columns[$column_name]['TABLE_CATALOG'] = null;
1336 $columns[$column_name]['TABLE_SCHEMA'] = $database;
1337 $columns[$column_name]['TABLE_NAME'] = $table;
1338 $columns[$column_name]['ORDINAL_POSITION'] = $ordinal_position;
1339 $columns[$column_name]['DATA_TYPE']
1341 $columns[$column_name]['COLUMN_TYPE'],
1343 strpos($columns[$column_name]['COLUMN_TYPE'], '(')
1346 * @todo guess CHARACTER_MAXIMUM_LENGTH from COLUMN_TYPE
1348 $columns[$column_name]['CHARACTER_MAXIMUM_LENGTH'] = null;
1350 * @todo guess CHARACTER_OCTET_LENGTH from CHARACTER_MAXIMUM_LENGTH
1352 $columns[$column_name]['CHARACTER_OCTET_LENGTH'] = null;
1353 $columns[$column_name]['NUMERIC_PRECISION'] = null;
1354 $columns[$column_name]['NUMERIC_SCALE'] = null;
1355 $columns[$column_name]['CHARACTER_SET_NAME']
1357 $columns[$column_name]['COLLATION_NAME'],
1359 strpos($columns[$column_name]['COLLATION_NAME'], '_')
1362 $ordinal_position++
;
1365 if (null !== $column) {
1367 $columns = current($columns);
1375 * Returns SQL query for fetching columns for a table
1377 * The 'Key' column is not calculated properly, use $GLOBALS['dbi']->getColumns()
1378 * to get correct values.
1380 * @param string $database name of database
1381 * @param string $table name of table to retrieve columns from
1382 * @param string $column name of column, null to show all columns
1383 * @param boolean $full whether to return full info or only column names
1389 public function getColumnsSql($database, $table, $column = null, $full = false)
1391 if (defined('PMA_DRIZZLE') && PMA_DRIZZLE
) {
1393 // * used in primary key => PRI
1394 // * unique one-column => UNI
1395 // * indexed, one-column or first in multi-column => MUL
1396 // Promotion of UNI to PRI in case no primary index exists
1397 // is done after query is executed
1399 column_name AS `Field`,
1401 WHEN character_maximum_length > 0
1403 lower(data_type), '(', character_maximum_length, ')'
1405 WHEN numeric_precision > 0 OR numeric_scale > 0
1406 THEN concat(lower(data_type), '(', numeric_precision,
1407 ',', numeric_scale, ')')
1408 WHEN enum_values IS NOT NULL
1409 THEN concat(lower(data_type), '(', enum_values, ')')
1410 ELSE lower(data_type) END)
1413 collation_name AS `Collation`," : '') . "
1416 ELSE 'NO' END) AS `Null`,
1418 WHEN is_used_in_primary THEN 'PRI'
1419 WHEN is_unique AND NOT is_multi THEN 'UNI'
1421 AND (NOT is_multi OR is_first_in_multi) THEN 'MUL'
1422 ELSE '' END) AS `Key`,
1423 column_default AS `Default`,
1425 WHEN is_auto_increment THEN 'auto_increment'
1426 WHEN column_default_update <> ''
1427 THEN 'on update ' || column_default_update
1428 ELSE '' END) AS `Extra`
1430 NULL AS `Privileges`,
1431 column_comment AS `Comment`" : '') . "
1432 FROM data_dictionary.columns
1433 WHERE table_schema = '" . PMA_Util
::sqlAddSlashes($database) . "'
1434 AND table_name = '" . PMA_Util
::sqlAddSlashes($table) . "'
1438 AND column_name = '" . PMA_Util
::sqlAddSlashes($column) . "'"
1441 // ORDER BY ordinal_position
1443 $sql = 'SHOW ' . ($full ?
'FULL' : '') . ' COLUMNS FROM '
1444 . PMA_Util
::backquote($database) . '.' . PMA_Util
::backquote($table)
1445 . ((! empty($column)) ?
"LIKE '"
1446 . PMA_Util
::sqlAddSlashes($column, true) . "'" : '');
1452 * Returns descriptions of columns in given table (all or given by $column)
1454 * @param string $database name of database
1455 * @param string $table name of table to retrieve columns from
1456 * @param string $column name of column, null to show all columns
1457 * @param boolean $full whether to return full info or only column names
1458 * @param mixed $link mysql link resource
1460 * @return false|array array indexed by column names or,
1461 * if $column is given, flat array description
1463 public function getColumns($database, $table, $column = null, $full = false,
1466 $sql = $this->getColumnsSql($database, $table, $column, $full);
1467 $fields = $this->fetchResult($sql, 'Field', null, $link);
1468 if (! is_array($fields) ||
count($fields) == 0) {
1471 // Check if column is a part of multiple-column index and set its 'Key'.
1472 $indexes = PMA_Index
::getFromTable($table, $database);
1473 foreach ($fields as $field => $field_data) {
1474 if (!empty($field_data['Key'])) {
1478 foreach ($indexes as $index) {
1479 /** @var PMA_Index $index */
1480 if (!$index->hasColumn($field)) {
1484 $index_columns = $index->getColumns();
1485 if ($index_columns[$field]->getSeqInIndex() > 1) {
1486 if ($index->isUnique()) {
1487 $fields[$field]['Key'] = 'UNI';
1489 $fields[$field]['Key'] = 'MUL';
1495 // fix Key column, it's much simpler in PHP than in SQL
1497 $has_pk_candidates = false;
1498 foreach ($fields as $f) {
1499 if ($f['Key'] == 'PRI') {
1502 } else if ($f['Null'] == 'NO'
1503 && ($f['Key'] == 'MUL'
1504 ||
$f['Key'] == 'UNI')
1506 $has_pk_candidates = true;
1509 if (! $has_pk && $has_pk_candidates) {
1510 $secureDatabase = PMA_Util
::sqlAddSlashes($database);
1511 // check whether we can promote some unique index to PRI
1513 SELECT i.index_name, p.column_name
1514 FROM data_dictionary.indexes i
1515 JOIN data_dictionary.index_parts p
1516 USING (table_schema, table_name)
1517 WHERE i.table_schema = '" . $secureDatabase . "'
1518 AND i.table_name = '" . PMA_Util
::sqlAddSlashes($table) . "'
1520 AND NOT i.is_nullable";
1521 $result = $this->fetchResult($sql, 'index_name', null, $link);
1522 $result = $result ?
array_shift($result) : array();
1523 foreach ($result as $f) {
1524 $fields[$f]['Key'] = 'PRI';
1529 return ($column != null) ?
array_shift($fields) : $fields;
1533 * Returns all column names in given table
1535 * @param string $database name of database
1536 * @param string $table name of table to retrieve columns from
1537 * @param mixed $link mysql link resource
1539 * @return null|array
1541 public function getColumnNames($database, $table, $link = null)
1543 $sql = $this->getColumnsSql($database, $table);
1544 // We only need the 'Field' column which contains the table's column names
1545 $fields = array_keys($this->fetchResult($sql, 'Field', null, $link));
1547 if (! is_array($fields) ||
count($fields) == 0) {
1554 * Returns SQL for fetching information on table indexes (SHOW INDEXES)
1556 * @param string $database name of database
1557 * @param string $table name of the table whose indexes are to be retrieved
1558 * @param string $where additional conditions for WHERE
1560 * @return string SQL for getting indexes
1562 public function getTableIndexesSql($database, $table, $where = null)
1566 ip.table_name AS `Table`,
1567 (NOT ip.is_unique) AS Non_unique,
1568 ip.index_name AS Key_name,
1569 ip.sequence_in_index+1 AS Seq_in_index,
1570 ip.column_name AS Column_name,
1572 WHEN i.index_type = 'BTREE' THEN 'A'
1573 ELSE NULL END) AS Collation,
1574 NULL AS Cardinality,
1575 compare_length AS Sub_part,
1577 ip.is_nullable AS `Null`,
1578 i.index_type AS Index_type,
1580 i.index_comment AS Index_comment
1581 FROM data_dictionary.index_parts ip
1582 LEFT JOIN data_dictionary.indexes i
1583 USING (table_schema, table_name, index_name)
1584 WHERE table_schema = '" . PMA_Util
::sqlAddSlashes($database) . "'
1585 AND table_name = '" . PMA_Util
::sqlAddSlashes($table) . "'
1588 $sql = "SELECT * FROM (" . $sql . ") A WHERE (" . $where . ")";
1591 $sql = 'SHOW INDEXES FROM ' . PMA_Util
::backquote($database) . '.'
1592 . PMA_Util
::backquote($table);
1594 $sql .= ' WHERE (' . $where . ')';
1601 * Returns indexes of a table
1603 * @param string $database name of database
1604 * @param string $table name of the table whose indexes are to be retrieved
1605 * @param mixed $link mysql link resource
1607 * @return array $indexes
1609 public function getTableIndexes($database, $table, $link = null)
1611 $sql = $this->getTableIndexesSql($database, $table);
1612 $indexes = $this->fetchResult($sql, null, null, $link);
1614 if (! is_array($indexes) ||
count($indexes) < 1) {
1621 * returns value of given mysql server variable
1623 * @param string $var mysql server variable name
1624 * @param int $type PMA_DatabaseInterface::GETVAR_SESSION |
1625 * PMA_DatabaseInterface::GETVAR_GLOBAL
1626 * @param mixed $link mysql link resource|object
1628 * @return mixed value for mysql server variable
1630 public function getVariable(
1631 $var, $type = self
::GETVAR_SESSION
, $link = null
1633 $link = $this->getLink($link);
1634 if ($link === false) {
1639 case self
::GETVAR_SESSION
:
1640 $modifier = ' SESSION';
1642 case self
::GETVAR_GLOBAL
:
1643 $modifier = ' GLOBAL';
1648 return $this->fetchValue(
1649 'SHOW' . $modifier . ' VARIABLES LIKE \'' . $var . '\';', 0, 1, $link
1654 * Sets new value for a variable if it is different from the current value
1656 * @param string $var variable name
1657 * @param string $value value to set
1658 * @param mixed $link mysql link resource|object
1660 * @return bool whether query was a successful
1662 public function setVariable($var, $value, $link = null)
1664 $link = $this->getLink($link);
1665 if ($link === false) {
1668 $current_value = $GLOBALS['dbi']->getVariable(
1669 $var, self
::GETVAR_SESSION
, $link
1671 if ($current_value == $value) {
1675 return $this->query("SET " . $var . " = " . $value . ';', $link);
1679 * Function called just after a connection to the MySQL database server has
1680 * been established. It sets the connection collation, and determines the
1681 * version of MySQL which is running.
1683 * @param mixed $link mysql link resource|object
1687 public function postConnect($link)
1689 if (! defined('PMA_MYSQL_INT_VERSION')) {
1690 if (PMA_Util
::cacheExists('PMA_MYSQL_INT_VERSION')) {
1692 'PMA_MYSQL_INT_VERSION',
1693 PMA_Util
::cacheGet('PMA_MYSQL_INT_VERSION')
1696 'PMA_MYSQL_MAJOR_VERSION',
1697 PMA_Util
::cacheGet('PMA_MYSQL_MAJOR_VERSION')
1700 'PMA_MYSQL_STR_VERSION',
1701 PMA_Util
::cacheGet('PMA_MYSQL_STR_VERSION')
1704 'PMA_MYSQL_VERSION_COMMENT',
1705 PMA_Util
::cacheGet('PMA_MYSQL_VERSION_COMMENT')
1709 PMA_Util
::cacheGet('PMA_MARIADB')
1713 PMA_Util
::cacheGet('PMA_DRIZZLE')
1716 $version = $this->fetchSingleRow(
1717 'SELECT @@version, @@version_comment',
1723 $match = explode('.', $version['@@version']);
1724 define('PMA_MYSQL_MAJOR_VERSION', (int)$match[0]);
1726 'PMA_MYSQL_INT_VERSION',
1728 '%d%02d%02d', $match[0], $match[1], intval($match[2])
1731 define('PMA_MYSQL_STR_VERSION', $version['@@version']);
1733 'PMA_MYSQL_VERSION_COMMENT',
1734 $version['@@version_comment']
1737 define('PMA_MYSQL_INT_VERSION', 50501);
1738 define('PMA_MYSQL_MAJOR_VERSION', 5);
1739 define('PMA_MYSQL_STR_VERSION', '5.05.01');
1740 define('PMA_MYSQL_VERSION_COMMENT', '');
1743 'PMA_MYSQL_INT_VERSION',
1744 PMA_MYSQL_INT_VERSION
1747 'PMA_MYSQL_MAJOR_VERSION',
1748 PMA_MYSQL_MAJOR_VERSION
1751 'PMA_MYSQL_STR_VERSION',
1752 PMA_MYSQL_STR_VERSION
1755 'PMA_MYSQL_VERSION_COMMENT',
1756 PMA_MYSQL_VERSION_COMMENT
1758 /* Detect MariaDB */
1759 if (mb_strpos(PMA_MYSQL_STR_VERSION
, 'MariaDB') !== false) {
1760 define('PMA_MARIADB', true);
1762 define('PMA_MARIADB', false);
1769 /* Detect Drizzle - it does not support charsets */
1770 $charset_result = $this->query(
1771 "SHOW VARIABLES LIKE 'character_set_results'",
1774 if ($this->numRows($charset_result) == 0) {
1775 define('PMA_DRIZZLE', true);
1777 define('PMA_DRIZZLE', false);
1779 $this->freeResult($charset_result);
1788 // Skip charsets for Drizzle
1790 if (PMA_MYSQL_INT_VERSION
> 50503) {
1791 $default_charset = 'utf8mb4';
1792 $default_collation = 'utf8mb4_general_ci';
1794 $default_charset = 'utf8';
1795 $default_collation = 'utf8_general_ci';
1797 if (! empty($GLOBALS['collation_connection'])) {
1799 "SET CHARACTER SET '$default_charset';",
1803 /* Automatically adjust collation to mb4 variant */
1804 if ($default_charset == 'utf8mb4'
1805 && strncmp('utf8_', $GLOBALS['collation_connection'], 5) == 0
1807 $GLOBALS['collation_connection'] = 'utf8mb4_' . substr(
1808 $GLOBALS['collation_connection'],
1813 "SET collation_connection = '"
1814 . PMA_Util
::sqlAddSlashes($GLOBALS['collation_connection'])
1821 "SET NAMES '$default_charset' COLLATE '$default_collation';",
1828 // Cache plugin list for Drizzle
1829 if (PMA_DRIZZLE
&& !PMA_Util
::cacheExists('drizzle_engines')) {
1830 $sql = "SELECT p.plugin_name, m.module_library
1831 FROM data_dictionary.plugins p
1832 JOIN data_dictionary.modules m USING (module_name)
1833 WHERE p.plugin_type = 'StorageEngine'
1834 AND p.plugin_name NOT IN ('FunctionEngine', 'schema')
1835 AND p.is_active = 'YES'";
1836 $engines = $this->fetchResult($sql, 'plugin_name', null, $link);
1837 PMA_Util
::cacheSet('drizzle_engines', $engines);
1842 * returns a single value from the given result or query,
1843 * if the query or the result has more than one row or field
1844 * the first field of the first row is returned
1847 * $sql = 'SELECT `name` FROM `user` WHERE `id` = 123';
1848 * $user_name = $GLOBALS['dbi']->fetchValue($sql);
1850 * // $user_name = 'John Doe'
1853 * @param string $query The query to execute
1854 * @param integer $row_number row to fetch the value from,
1855 * starting at 0, with 0 being default
1856 * @param integer|string $field field to fetch the value from,
1857 * starting at 0, with 0 being default
1858 * @param object $link mysql link
1860 * @return mixed value of first field in first row from result
1861 * or false if not found
1863 public function fetchValue($query, $row_number = 0, $field = 0, $link = null)
1867 $result = $this->tryQuery(
1873 if ($result === false) {
1877 // return false if result is empty or false
1878 // or requested row is larger than rows in result
1879 if ($this->numRows($result) < ($row_number +
1)) {
1883 // if $field is an integer use non associative mysql fetch function
1884 if (is_int($field)) {
1885 $fetch_function = 'fetchRow';
1887 $fetch_function = 'fetchAssoc';
1890 // get requested row
1891 for ($i = 0; $i <= $row_number; $i++
) {
1892 $row = $this->$fetch_function($result);
1894 $this->freeResult($result);
1896 // return requested field
1897 if (isset($row[$field])) {
1898 $value = $row[$field];
1905 * returns only the first row from the result
1908 * $sql = 'SELECT * FROM `user` WHERE `id` = 123';
1909 * $user = $GLOBALS['dbi']->fetchSingleRow($sql);
1911 * // $user = array('id' => 123, 'name' => 'John Doe')
1914 * @param string $query The query to execute
1915 * @param string $type NUM|ASSOC|BOTH returned array should either
1916 * numeric associative or both
1917 * @param object $link mysql link
1919 * @return array|boolean first row from result
1920 * or false if result is empty
1922 public function fetchSingleRow($query, $type = 'ASSOC', $link = null)
1924 $result = $this->tryQuery(
1930 if ($result === false) {
1934 // return false if result is empty or false
1935 if (! $this->numRows($result)) {
1941 $fetch_function = 'fetchRow';
1944 $fetch_function = 'fetchAssoc';
1948 $fetch_function = 'fetchArray';
1952 $row = $this->$fetch_function($result);
1953 $this->freeResult($result);
1958 * Returns row or element of a row
1960 * @param array $row Row to process
1961 * @param string|null $value Which column to return
1965 private function _fetchValue($row, $value)
1967 if (is_null($value)) {
1970 return $row[$value];
1975 * returns all rows in the resultset in one array
1978 * $sql = 'SELECT * FROM `user`';
1979 * $users = $GLOBALS['dbi']->fetchResult($sql);
1981 * // $users[] = array('id' => 123, 'name' => 'John Doe')
1983 * $sql = 'SELECT `id`, `name` FROM `user`';
1984 * $users = $GLOBALS['dbi']->fetchResult($sql, 'id');
1986 * // $users['123'] = array('id' => 123, 'name' => 'John Doe')
1988 * $sql = 'SELECT `id`, `name` FROM `user`';
1989 * $users = $GLOBALS['dbi']->fetchResult($sql, 0);
1991 * // $users['123'] = array(0 => 123, 1 => 'John Doe')
1993 * $sql = 'SELECT `id`, `name` FROM `user`';
1994 * $users = $GLOBALS['dbi']->fetchResult($sql, 'id', 'name');
1996 * $users = $GLOBALS['dbi']->fetchResult($sql, 0, 1);
1998 * // $users['123'] = 'John Doe'
2000 * $sql = 'SELECT `name` FROM `user`';
2001 * $users = $GLOBALS['dbi']->fetchResult($sql);
2003 * // $users[] = 'John Doe'
2005 * $sql = 'SELECT `group`, `name` FROM `user`'
2006 * $users = $GLOBALS['dbi']->fetchResult($sql, array('group', null), 'name');
2008 * // $users['admin'][] = 'John Doe'
2010 * $sql = 'SELECT `group`, `name` FROM `user`'
2011 * $users = $GLOBALS['dbi']->fetchResult($sql, array('group', 'name'), 'id');
2013 * // $users['admin']['John Doe'] = '123'
2016 * @param string $query query to execute
2017 * @param string|integer|array $key field-name or offset
2018 * used as key for array
2020 * @param string|integer $value value-name or offset
2021 * used as value for array
2022 * @param object $link mysql link
2023 * @param integer $options query options
2025 * @return array resultrows or values indexed by $key
2027 public function fetchResult($query, $key = null, $value = null,
2028 $link = null, $options = 0
2030 $resultrows = array();
2032 $result = $this->tryQuery($query, $link, $options, false);
2034 // return empty array if result is empty or false
2035 if ($result === false) {
2039 $fetch_function = 'fetchAssoc';
2041 // no nested array if only one field is in result
2042 if (null === $key && 1 === $this->numFields($result)) {
2044 $fetch_function = 'fetchRow';
2047 // if $key is an integer use non associative mysql fetch function
2049 $fetch_function = 'fetchRow';
2052 if (null === $key) {
2053 while ($row = $this->$fetch_function($result)) {
2054 $resultrows[] = $this->_fetchValue($row, $value);
2057 if (is_array($key)) {
2058 while ($row = $this->$fetch_function($result)) {
2059 $result_target =& $resultrows;
2060 foreach ($key as $key_index) {
2061 if (null === $key_index) {
2062 $result_target =& $result_target[];
2066 if (! isset($result_target[$row[$key_index]])) {
2067 $result_target[$row[$key_index]] = array();
2069 $result_target =& $result_target[$row[$key_index]];
2071 $result_target = $this->_fetchValue($row, $value);
2074 while ($row = $this->$fetch_function($result)) {
2075 $resultrows[$row[$key]] = $this->_fetchValue($row, $value);
2080 $this->freeResult($result);
2085 * Get supported SQL compatibility modes
2087 * @return array supported SQL compatibility modes
2089 public function getCompatibilities()
2091 // Drizzle doesn't support compatibility modes
2096 $compats = array('NONE');
2097 $compats[] = 'ANSI';
2099 $compats[] = 'MAXDB';
2100 $compats[] = 'MYSQL323';
2101 $compats[] = 'MYSQL40';
2102 $compats[] = 'MSSQL';
2103 $compats[] = 'ORACLE';
2104 // removed; in MySQL 5.0.33, this produces exports that
2105 // can't be read by POSTGRESQL (see our bug #1596328)
2106 //$compats[] = 'POSTGRESQL';
2107 $compats[] = 'TRADITIONAL';
2113 * returns warnings for last query
2115 * @param object $link mysql link resource
2117 * @return array warnings
2119 public function getWarnings($link = null)
2121 $link = $this->getLink($link);
2122 if ($link === false) {
2126 return $this->fetchResult('SHOW WARNINGS', null, null, $link);
2130 * returns an array of PROCEDURE or FUNCTION names for a db
2132 * @param string $db db name
2133 * @param string $which PROCEDURE | FUNCTION
2134 * @param object $link mysql link
2136 * @return array the procedure names or function names
2138 public function getProceduresOrFunctions($db, $which, $link = null)
2141 // Drizzle doesn't support functions and procedures
2144 $shows = $this->fetchResult(
2145 'SHOW ' . $which . ' STATUS;', null, null, $link
2148 foreach ($shows as $one_show) {
2149 if ($one_show['Db'] == $db && $one_show['Type'] == $which) {
2150 $result[] = $one_show['Name'];
2157 * returns the definition of a specific PROCEDURE, FUNCTION, EVENT or VIEW
2159 * @param string $db db name
2160 * @param string $which PROCEDURE | FUNCTION | EVENT | VIEW
2161 * @param string $name the procedure|function|event|view name
2163 * @return string the definition
2165 public function getDefinition($db, $which, $name)
2167 $returned_field = array(
2168 'PROCEDURE' => 'Create Procedure',
2169 'FUNCTION' => 'Create Function',
2170 'EVENT' => 'Create Event',
2171 'VIEW' => 'Create View'
2173 $query = 'SHOW CREATE ' . $which . ' '
2174 . PMA_Util
::backquote($db) . '.'
2175 . PMA_Util
::backquote($name);
2176 return($this->fetchValue($query, 0, $returned_field[$which]));
2180 * returns details about the PROCEDUREs or FUNCTIONs for a specific database
2181 * or details about a specific routine
2183 * @param string $db db name
2184 * @param string $which PROCEDURE | FUNCTION or null for both
2185 * @param string $name name of the routine (to fetch a specific routine)
2187 * @return array information about ROCEDUREs or FUNCTIONs
2189 public function getRoutines($db, $which = null, $name = '')
2192 // Drizzle doesn't support functions and procedures
2196 $routines = array();
2197 if (! $GLOBALS['cfg']['Server']['DisableIS']) {
2199 . " `ROUTINE_SCHEMA` AS `Db`,"
2200 . " `SPECIFIC_NAME` AS `Name`,"
2201 . " `ROUTINE_TYPE` AS `Type`,"
2202 . " `DEFINER` AS `Definer`,"
2203 . " `LAST_ALTERED` AS `Modified`,"
2204 . " `CREATED` AS `Created`,"
2205 . " `SECURITY_TYPE` AS `Security_type`,"
2206 . " `ROUTINE_COMMENT` AS `Comment`,"
2207 . " `CHARACTER_SET_CLIENT` AS `character_set_client`,"
2208 . " `COLLATION_CONNECTION` AS `collation_connection`,"
2209 . " `DATABASE_COLLATION` AS `Database Collation`,"
2210 . " `DTD_IDENTIFIER`"
2211 . " FROM `information_schema`.`ROUTINES`"
2212 . " WHERE `ROUTINE_SCHEMA` " . PMA_Util
::getCollateForIS()
2213 . " = '" . PMA_Util
::sqlAddSlashes($db) . "'";
2214 if (PMA_isValid($which, array('FUNCTION','PROCEDURE'))) {
2215 $query .= " AND `ROUTINE_TYPE` = '" . $which . "'";
2217 if (! empty($name)) {
2218 $query .= " AND `SPECIFIC_NAME`"
2219 . " = '" . PMA_Util
::sqlAddSlashes($name) . "'";
2221 $result = $this->fetchResult($query);
2223 $routines = $result;
2226 if ($which == 'FUNCTION' ||
$which == null) {
2227 $query = "SHOW FUNCTION STATUS"
2228 . " WHERE `Db` = '" . PMA_Util
::sqlAddSlashes($db) . "'";
2229 if (! empty($name)) {
2230 $query .= " AND `Name` = '"
2231 . PMA_Util
::sqlAddSlashes($name) . "'";
2233 $result = $this->fetchResult($query);
2235 $routines = array_merge($routines, $result);
2238 if ($which == 'PROCEDURE' ||
$which == null) {
2239 $query = "SHOW PROCEDURE STATUS"
2240 . " WHERE `Db` = '" . PMA_Util
::sqlAddSlashes($db) . "'";
2241 if (! empty($name)) {
2242 $query .= " AND `Name` = '"
2243 . PMA_Util
::sqlAddSlashes($name) . "'";
2245 $result = $this->fetchResult($query);
2247 $routines = array_merge($routines, $result);
2253 foreach ($routines as $routine) {
2254 $one_result = array();
2255 $one_result['db'] = $routine['Db'];
2256 $one_result['name'] = $routine['Name'];
2257 $one_result['type'] = $routine['Type'];
2258 $one_result['definer'] = $routine['Definer'];
2259 $one_result['returns'] = isset($routine['DTD_IDENTIFIER'])
2260 ?
$routine['DTD_IDENTIFIER'] : "";
2261 $ret[] = $one_result;
2264 // Sort results by name
2266 foreach ($ret as $value) {
2267 $name[] = $value['name'];
2269 array_multisort($name, SORT_ASC
, $ret);
2275 * returns details about the EVENTs for a specific database
2277 * @param string $db db name
2278 * @param string $name event name
2280 * @return array information about EVENTs
2282 public function getEvents($db, $name = '')
2285 // Drizzle doesn't support events
2289 if (! $GLOBALS['cfg']['Server']['DisableIS']) {
2291 . " `EVENT_SCHEMA` AS `Db`,"
2292 . " `EVENT_NAME` AS `Name`,"
2293 . " `DEFINER` AS `Definer`,"
2294 . " `TIME_ZONE` AS `Time zone`,"
2295 . " `EVENT_TYPE` AS `Type`,"
2296 . " `EXECUTE_AT` AS `Execute at`,"
2297 . " `INTERVAL_VALUE` AS `Interval value`,"
2298 . " `INTERVAL_FIELD` AS `Interval field`,"
2299 . " `STARTS` AS `Starts`,"
2300 . " `ENDS` AS `Ends`,"
2301 . " `STATUS` AS `Status`,"
2302 . " `ORIGINATOR` AS `Originator`,"
2303 . " `CHARACTER_SET_CLIENT` AS `character_set_client`,"
2304 . " `COLLATION_CONNECTION` AS `collation_connection`, "
2305 . "`DATABASE_COLLATION` AS `Database Collation`"
2306 . " FROM `information_schema`.`EVENTS`"
2307 . " WHERE `EVENT_SCHEMA` " . PMA_Util
::getCollateForIS()
2308 . " = '" . PMA_Util
::sqlAddSlashes($db) . "'";
2309 if (! empty($name)) {
2310 $query .= " AND `EVENT_NAME`"
2311 . " = '" . PMA_Util
::sqlAddSlashes($name) . "'";
2314 $query = "SHOW EVENTS FROM " . PMA_Util
::backquote($db);
2315 if (! empty($name)) {
2316 $query .= " AND `Name` = '"
2317 . PMA_Util
::sqlAddSlashes($name) . "'";
2322 if ($events = $this->fetchResult($query)) {
2323 foreach ($events as $event) {
2324 $one_result = array();
2325 $one_result['name'] = $event['Name'];
2326 $one_result['type'] = $event['Type'];
2327 $one_result['status'] = $event['Status'];
2328 $result[] = $one_result;
2332 // Sort results by name
2334 foreach ($result as $value) {
2335 $name[] = $value['name'];
2337 array_multisort($name, SORT_ASC
, $result);
2343 * returns details about the TRIGGERs for a specific table or database
2345 * @param string $db db name
2346 * @param string $table table name
2347 * @param string $delimiter the delimiter to use (may be empty)
2349 * @return array information about triggers (may be empty)
2351 public function getTriggers($db, $table = '', $delimiter = '//')
2354 // Drizzle doesn't support triggers
2359 if (! $GLOBALS['cfg']['Server']['DisableIS']) {
2360 $query = 'SELECT TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_MANIPULATION'
2361 . ', EVENT_OBJECT_TABLE, ACTION_TIMING, ACTION_STATEMENT'
2362 . ', EVENT_OBJECT_SCHEMA, EVENT_OBJECT_TABLE, DEFINER'
2363 . ' FROM information_schema.TRIGGERS'
2364 . ' WHERE EVENT_OBJECT_SCHEMA ' . PMA_Util
::getCollateForIS() . '='
2365 . ' \'' . PMA_Util
::sqlAddSlashes($db) . '\'';
2367 if (! empty($table)) {
2368 $query .= " AND EVENT_OBJECT_TABLE " . PMA_Util
::getCollateForIS()
2369 . " = '" . PMA_Util
::sqlAddSlashes($table) . "';";
2372 $query = "SHOW TRIGGERS FROM " . PMA_Util
::backquote($db);
2373 if (! empty($table)) {
2374 $query .= " LIKE '" . PMA_Util
::sqlAddSlashes($table, true) . "';";
2378 if ($triggers = $this->fetchResult($query)) {
2379 foreach ($triggers as $trigger) {
2380 if ($GLOBALS['cfg']['Server']['DisableIS']) {
2381 $trigger['TRIGGER_NAME'] = $trigger['Trigger'];
2382 $trigger['ACTION_TIMING'] = $trigger['Timing'];
2383 $trigger['EVENT_MANIPULATION'] = $trigger['Event'];
2384 $trigger['EVENT_OBJECT_TABLE'] = $trigger['Table'];
2385 $trigger['ACTION_STATEMENT'] = $trigger['Statement'];
2386 $trigger['DEFINER'] = $trigger['Definer'];
2388 $one_result = array();
2389 $one_result['name'] = $trigger['TRIGGER_NAME'];
2390 $one_result['table'] = $trigger['EVENT_OBJECT_TABLE'];
2391 $one_result['action_timing'] = $trigger['ACTION_TIMING'];
2392 $one_result['event_manipulation'] = $trigger['EVENT_MANIPULATION'];
2393 $one_result['definition'] = $trigger['ACTION_STATEMENT'];
2394 $one_result['definer'] = $trigger['DEFINER'];
2396 // do not prepend the schema name; this way, importing the
2397 // definition into another schema will work
2398 $one_result['full_trigger_name'] = PMA_Util
::backquote(
2399 $trigger['TRIGGER_NAME']
2401 $one_result['drop'] = 'DROP TRIGGER IF EXISTS '
2402 . $one_result['full_trigger_name'];
2403 $one_result['create'] = 'CREATE TRIGGER '
2404 . $one_result['full_trigger_name'] . ' '
2405 . $trigger['ACTION_TIMING'] . ' '
2406 . $trigger['EVENT_MANIPULATION']
2407 . ' ON ' . PMA_Util
::backquote($trigger['EVENT_OBJECT_TABLE'])
2408 . "\n" . ' FOR EACH ROW '
2409 . $trigger['ACTION_STATEMENT'] . "\n" . $delimiter . "\n";
2411 $result[] = $one_result;
2415 // Sort results by name
2417 foreach ($result as $value) {
2418 $name[] = $value['name'];
2420 array_multisort($name, SORT_ASC
, $result);
2426 * Formats database error message in a friendly way.
2427 * This is needed because some errors messages cannot
2428 * be obtained by mysql_error().
2430 * @param int $error_number Error code
2431 * @param string $error_message Error message as returned by server
2433 * @return string HML text with error details
2435 public function formatError($error_number, $error_message)
2437 if (! empty($error_message)) {
2438 $error_message = $this->convertMessage($error_message);
2441 $error_message = htmlspecialchars($error_message);
2443 $error = '#' . ((string) $error_number);
2445 if ($error_number == 2002) {
2446 $error .= ' - ' . $error_message;
2449 'The server is not responding (or the local server\'s socket'
2450 . ' is not correctly configured).'
2452 } elseif ($error_number == 2003) {
2453 $error .= ' - ' . $error_message;
2454 $error .= '<br />' . __('The server is not responding.');
2455 } elseif ($error_number == 1005) {
2456 if (strpos($error_message, 'errno: 13') !== false) {
2457 $error .= ' - ' . $error_message;
2459 . __('Please check privileges of directory containing database.');
2461 /* InnoDB constraints, see
2462 * http://dev.mysql.com/doc/refman/5.0/en/
2463 * innodb-foreign-key-constraints.html
2465 $error .= ' - ' . $error_message .
2466 ' (<a href="server_engines.php' .
2468 array('engine' => 'InnoDB', 'page' => 'Status')
2469 ) . '">' . __('Details…') . '</a>)';
2472 $error .= ' - ' . $error_message;
2479 * gets the current user with host
2481 * @return string the current user i.e. user@host
2483 public function getCurrentUser()
2485 if (PMA_Util
::cacheExists('mysql_cur_user')) {
2486 return PMA_Util
::cacheGet('mysql_cur_user');
2488 $user = $GLOBALS['dbi']->fetchValue('SELECT USER();');
2489 if ($user !== false) {
2490 PMA_Util
::cacheSet('mysql_cur_user', $user);
2491 return PMA_Util
::cacheGet('mysql_cur_user');
2497 * Checks if current user is superuser
2499 * @return bool Whether user is a superuser
2501 public function isSuperuser()
2503 return self
::isUserType('super');
2507 * Checks if current user has global create user/grant privilege
2508 * or is a superuser (i.e. SELECT on mysql.users)
2509 * while caching the result in session.
2511 * @param string $type type of user to check for
2512 * i.e. 'create', 'grant', 'super'
2514 * @return bool Whether user is a given type of user
2516 public function isUserType($type)
2518 if (PMA_Util
::cacheExists('is_' . $type . 'user')) {
2519 return PMA_Util
::cacheGet('is_' . $type . 'user');
2522 // when connection failed we don't have a $userlink
2523 if (! isset($GLOBALS['userlink'])) {
2524 PMA_Util
::cacheSet('is_' . $type . 'user', false);
2525 return PMA_Util
::cacheGet('is_' . $type . 'user');
2529 // Drizzle has no authorization by default, so when no plugin is
2530 // enabled everyone is a superuser
2531 // Known authorization libraries: regex_policy, simple_user_policy
2532 // Plugins limit object visibility (dbs, tables, processes), we can
2533 // safely assume we always deal with superuser
2534 PMA_Util
::cacheSet('is_' . $type . 'user', true);
2535 return PMA_Util
::cacheGet('is_' . $type . 'user');
2538 if (! $GLOBALS['cfg']['Server']['DisableIS'] ||
$type === 'super') {
2539 // Prepare query for each user type check
2541 if ($type === 'super') {
2542 $query = 'SELECT 1 FROM mysql.user LIMIT 1';
2543 } elseif ($type === 'create') {
2544 list($user, $host) = $this->_getCurrentUserAndHost();
2545 $query = "SELECT 1 FROM `INFORMATION_SCHEMA`.`USER_PRIVILEGES` "
2546 . "WHERE `PRIVILEGE_TYPE` = 'CREATE USER' AND "
2547 . "'''" . $user . "''@''" . $host . "''' LIKE `GRANTEE` LIMIT 1";
2548 } elseif ($type === 'grant') {
2549 list($user, $host) = $this->_getCurrentUserAndHost();
2550 $query = "SELECT 1 FROM ("
2551 . "SELECT `GRANTEE`, `IS_GRANTABLE` FROM "
2552 . "`INFORMATION_SCHEMA`.`COLUMN_PRIVILEGES` UNION "
2553 . "SELECT `GRANTEE`, `IS_GRANTABLE` FROM "
2554 . "`INFORMATION_SCHEMA`.`TABLE_PRIVILEGES` UNION "
2555 . "SELECT `GRANTEE`, `IS_GRANTABLE` FROM "
2556 . "`INFORMATION_SCHEMA`.`SCHEMA_PRIVILEGES` UNION "
2557 . "SELECT `GRANTEE`, `IS_GRANTABLE` FROM "
2558 . "`INFORMATION_SCHEMA`.`USER_PRIVILEGES`) t "
2559 . "WHERE `IS_GRANTABLE` = 'YES' AND "
2560 . "'''" . $user . "''@''" . $host . "''' LIKE `GRANTEE` LIMIT 1";
2564 $result = $GLOBALS['dbi']->tryQuery(
2566 $GLOBALS['userlink'],
2570 $is = (bool) $GLOBALS['dbi']->numRows($result);
2572 $GLOBALS['dbi']->freeResult($result);
2574 PMA_Util
::cacheSet('is_' . $type . 'user', $is);
2577 $grants = $GLOBALS['dbi']->fetchResult(
2578 "SHOW GRANTS FOR CURRENT_USER();",
2581 $GLOBALS['userlink'],
2585 foreach ($grants as $grant) {
2586 if ($type === 'create') {
2587 if (strpos($grant, "ALL PRIVILEGES ON *.*") !== false
2588 ||
strpos($grant, "CREATE USER") !== false
2593 } elseif ($type === 'grant') {
2594 if (strpos($grant, "WITH GRANT OPTION") !== false) {
2602 PMA_Util
::cacheSet('is_' . $type . 'user', $is);
2605 return PMA_Util
::cacheGet('is_' . $type . 'user');
2609 * Get the current user and host
2611 * @return array array of username and hostname
2613 private function _getCurrentUserAndHost()
2615 $user = $GLOBALS['dbi']->fetchValue("SELECT CURRENT_USER();");
2616 return explode("@", $user);
2620 * Get the list of system schemas
2622 * @return array list of system schemas
2624 public function getSystemSchemas()
2627 'information_schema', 'performance_schema', 'data_dictionary', 'mysql'
2629 $systemSchemas = array();
2630 foreach ($schemas as $schema) {
2631 if ($this->isSystemSchema($schema, true)) {
2632 $systemSchemas[] = $schema;
2635 return $systemSchemas;
2639 * Checks whether given schema is a system schema: information_schema
2640 * (MySQL and Drizzle) or data_dictionary (Drizzle)
2642 * @param string $schema_name Name of schema (database) to test
2643 * @param bool $testForMysqlSchema Whether 'mysql' schema should
2644 * be treated the same as IS and DD
2648 public function isSystemSchema($schema_name, $testForMysqlSchema = false)
2650 if (!defined("PMA_DRIZZLE")) {
2651 define("PMA_DRIZZLE", false);
2654 return strtolower($schema_name) == 'information_schema'
2656 && strtolower($schema_name) == 'performance_schema')
2658 && strtolower($schema_name) == 'data_dictionary')
2659 ||
($testForMysqlSchema && !PMA_DRIZZLE
&& $schema_name == 'mysql');
2663 * connects to the database server
2665 * @param string $user user name
2666 * @param string $password user password
2667 * @param bool $is_controluser whether this is a control user connection
2668 * @param array $server host/port/socket/persistent
2669 * @param bool $auxiliary_connection (when true, don't go back to login if
2672 * @return mixed false on error or a connection object on success
2674 public function connect(
2675 $user, $password, $is_controluser = false, $server = null,
2676 $auxiliary_connection = false
2678 $result = $this->_extension
->connect(
2679 $user, $password, $is_controluser, $server, $auxiliary_connection
2683 if (! $auxiliary_connection && ! $is_controluser) {
2684 $GLOBALS['dbi']->postConnect($result);
2689 if ($is_controluser) {
2692 'Connection for controluser as defined in your '
2693 . 'configuration failed.'
2700 // we could be calling $GLOBALS['dbi']->connect() to connect to another
2701 // server, for example in the Synchronize feature, so do not
2702 // go back to main login if it fails
2703 if ($auxiliary_connection) {
2707 PMA_logUser($user, 'mysql-denied');
2708 $GLOBALS['auth_plugin']->authFails();
2714 * selects given database
2716 * @param string $dbname database name to select
2717 * @param object $link connection object
2721 public function selectDb($dbname, $link = null)
2723 $link = $this->getLink($link);
2724 if ($link === false) {
2727 return $this->_extension
->selectDb($dbname, $link);
2731 * returns array of rows with associative and numeric keys from $result
2733 * @param object $result result set identifier
2737 public function fetchArray($result)
2739 return $this->_extension
->fetchArray($result);
2743 * returns array of rows with associative keys from $result
2745 * @param object $result result set identifier
2749 public function fetchAssoc($result)
2751 return $this->_extension
->fetchAssoc($result);
2755 * returns array of rows with numeric keys from $result
2757 * @param object $result result set identifier
2761 public function fetchRow($result)
2763 return $this->_extension
->fetchRow($result);
2767 * Adjusts the result pointer to an arbitrary row in the result
2769 * @param object $result database result
2770 * @param integer $offset offset to seek
2772 * @return bool true on success, false on failure
2774 public function dataSeek($result, $offset)
2776 return $this->_extension
->dataSeek($result, $offset);
2780 * Frees memory associated with the result
2782 * @param object $result database result
2786 public function freeResult($result)
2788 $this->_extension
->freeResult($result);
2792 * Check if there are any more query results from a multi query
2794 * @param object $link the connection object
2796 * @return bool true or false
2798 public function moreResults($link = null)
2800 $link = $this->getLink($link);
2801 if ($link === false) {
2804 return $this->_extension
->moreResults($link);
2808 * Prepare next result from multi_query
2810 * @param object $link the connection object
2812 * @return bool true or false
2814 public function nextResult($link = null)
2816 $link = $this->getLink($link);
2817 if ($link === false) {
2820 return $this->_extension
->nextResult($link);
2824 * Store the result returned from multi query
2826 * @param object $link the connection object
2828 * @return mixed false when empty results / result set when not empty
2830 public function storeResult($link = null)
2832 $link = $this->getLink($link);
2833 if ($link === false) {
2836 return $this->_extension
->storeResult($link);
2840 * Returns a string representing the type of connection used
2842 * @param object $link mysql link
2844 * @return string type of connection used
2846 public function getHostInfo($link = null)
2848 $link = $this->getLink($link);
2849 if ($link === false) {
2852 return $this->_extension
->getHostInfo($link);
2856 * Returns the version of the MySQL protocol used
2858 * @param object $link mysql link
2860 * @return integer version of the MySQL protocol used
2862 public function getProtoInfo($link = null)
2864 $link = $this->getLink($link);
2865 if ($link === false) {
2868 return $this->_extension
->getProtoInfo($link);
2872 * returns a string that represents the client library version
2874 * @return string MySQL client library version
2876 public function getClientInfo()
2878 return $this->_extension
->getClientInfo();
2882 * returns last error message or false if no errors occurred
2884 * @param object $link connection link
2886 * @return string|bool $error or false
2888 public function getError($link = null)
2890 $link = $this->getLink($link);
2891 if ($link === false) {
2894 return $this->_extension
->getError($link);
2898 * returns the number of rows returned by last query
2900 * @param object $result result set identifier
2902 * @return string|int
2904 public function numRows($result)
2906 return $this->_extension
->numRows($result);
2910 * returns last inserted auto_increment id for given $link
2911 * or $GLOBALS['userlink']
2913 * @param object $link the connection object
2915 * @return int|boolean
2917 public function insertId($link = null)
2919 $link = $this->getLink($link);
2920 if ($link === false) {
2923 // If the primary key is BIGINT we get an incorrect result
2924 // (sometimes negative, sometimes positive)
2925 // and in the present function we don't know if the PK is BIGINT
2926 // so better play safe and use LAST_INSERT_ID()
2928 // When no controluser is defined, using mysqli_insert_id($link)
2929 // does not always return the last insert id due to a mixup with
2930 // the tracking mechanism, but this works:
2931 return $GLOBALS['dbi']->fetchValue('SELECT LAST_INSERT_ID();', 0, 0, $link);
2935 * returns the number of rows affected by last query
2937 * @param object $link the connection object
2938 * @param bool $get_from_cache whether to retrieve from cache
2940 * @return int|boolean
2942 public function affectedRows($link = null, $get_from_cache = true)
2944 $link = $this->getLink($link);
2945 if ($link === false) {
2949 if ($get_from_cache) {
2950 return $GLOBALS['cached_affected_rows'];
2952 return $this->_extension
->affectedRows($link);
2957 * returns metainfo for fields in $result
2959 * @param object $result result set identifier
2961 * @return array meta info for fields in $result
2963 public function getFieldsMeta($result)
2965 return $this->_extension
->getFieldsMeta($result);
2969 * return number of fields in given $result
2971 * @param object $result result set identifier
2973 * @return int field count
2975 public function numFields($result)
2977 return $this->_extension
->numFields($result);
2981 * returns the length of the given field $i in $result
2983 * @param object $result result set identifier
2984 * @param int $i field
2986 * @return int length of field
2988 public function fieldLen($result, $i)
2990 return $this->_extension
->fieldLen($result, $i);
2994 * returns name of $i. field in $result
2996 * @param object $result result set identifier
2997 * @param int $i field
2999 * @return string name of $i. field in $result
3001 public function fieldName($result, $i)
3003 return $this->_extension
->fieldName($result, $i);
3007 * returns concatenated string of human readable field flags
3009 * @param object $result result set identifier
3010 * @param int $i field
3012 * @return string field flags
3014 public function fieldFlags($result, $i)
3016 return $this->_extension
->fieldFlags($result, $i);
3020 * Gets server connection port
3022 * @param array|null $server host/port/socket/persistent
3024 * @return null|integer
3026 public function getServerPort($server = null)
3028 if (is_null($server)) {
3029 $server = &$GLOBALS['cfg']['Server'];
3032 if (empty($server['port'])) {
3035 return intval($server['port']);
3040 * Gets server connection socket
3042 * @param array|null $server host/port/socket/persistent
3044 * @return null|string
3046 public function getServerSocket($server = null)
3048 if (is_null($server)) {
3049 $server = &$GLOBALS['cfg']['Server'];
3052 if (empty($server['socket'])) {
3055 return $server['socket'];
3060 * Gets correct link object.
3062 * @param object $link optional database link to use
3064 * @return object|boolean
3066 public function getLink($link = null)
3068 if (! is_null($link) && $link !== false) {
3072 if (isset($GLOBALS['userlink']) && !is_null($GLOBALS['userlink'])) {
3073 return $GLOBALS['userlink'];
3080 * Checks if this database server is running on Amazon RDS.
3084 public function isAmazonRds()
3086 if (PMA_Util
::cacheExists('is_amazon_rds')) {
3087 return PMA_Util
::cacheGet('is_amazon_rds');
3089 $sql = 'SELECT @@basedir';
3090 $result = $this->fetchResult($sql);
3091 $rds = ($result[0] == '/rdsdbbin/mysql/');
3092 PMA_Util
::cacheSet('is_amazon_rds', $rds);
3098 * Gets SQL for killing a process.
3100 * @param int $process Process ID
3104 public function getKillQuery($process)
3106 if ($this->isAmazonRds()) {
3107 return 'CALL mysql.rds_kill(' . $process . ');';
3109 return 'KILL ' . $process . ';';
3114 * Get the phpmyadmin database manager
3116 * @return PMA\SystemDatabase
3118 public function getSystemDatabase()
3120 return new PMA\
SystemDatabase($this);
3124 * Get a table with database name and table name
3126 * @param string $db_name DB name
3127 * @param string $table_name Table name
3131 public function getTable($db_name, $table_name)
3133 return new PMA_Table($table_name, $db_name, $this);