2 /* vim: set expandtab sw=4 ts=4 sts=4: */
4 * Holds the Table class
8 namespace PMA\libraries
;
10 use PMA\libraries\plugins\export\ExportSql
;
11 use PhpMyAdmin\SqlParser\Components\Expression
;
12 use PhpMyAdmin\SqlParser\Components\OptionsArray
;
13 use PhpMyAdmin\SqlParser\Context
;
14 use PhpMyAdmin\SqlParser\Parser
;
15 use PhpMyAdmin\SqlParser\Statements\DropStatement
;
18 * Handles everything related to tables
20 * @todo make use of Message and Error
26 * UI preferences properties
28 const PROP_SORTED_COLUMN
= 'sorted_col';
29 const PROP_COLUMN_ORDER
= 'col_order';
30 const PROP_COLUMN_VISIB
= 'col_visib';
33 * @var string engine (innodb, myisam, bdb, ...)
38 * @var string type (view, base table, system view)
43 * @var array UI preferences
48 * @var array errors occurred
50 var $errors = array();
55 var $messages = array();
58 * @var string table name
60 protected $_name = '';
63 * @var string database name
65 protected $_db_name = '';
68 * @var DatabaseInterface
75 * @param string $table_name table name
76 * @param string $db_name database name
77 * @param DatabaseInterface $dbi database interface for the table
79 public function __construct($table_name, $db_name, DatabaseInterface
$dbi = null)
82 $dbi = $GLOBALS['dbi'];
85 $this->_name
= $table_name;
86 $this->_db_name
= $db_name;
92 * @see Table::getName()
93 * @return string table name
95 public function __toString()
97 return $this->getName();
101 * return the last error
103 * @return string the last error
105 public function getLastError()
107 return end($this->errors
);
111 * return the last message
113 * @return string the last message
115 public function getLastMessage()
117 return end($this->messages
);
123 * @param boolean $backquoted whether to quote name with backticks ``
125 * @return string table name
127 public function getName($backquoted = false)
130 return Util
::backquote($this->_name
);
136 * returns database name for this table
138 * @param boolean $backquoted whether to quote name with backticks ``
140 * @return string database name for this table
142 public function getDbName($backquoted = false)
145 return Util
::backquote($this->_db_name
);
147 return $this->_db_name
;
151 * returns full name for table, including database name
153 * @param boolean $backquoted whether to quote name with backticks ``
157 public function getFullName($backquoted = false)
159 return $this->getDbName($backquoted) . '.'
160 . $this->getName($backquoted);
165 * Checks the storage engine used to create table
167 * @param array or string $engine Checks the table engine against an
168 * array of engine strings or a single string, should be uppercase
170 * @return bool True, if $engine matches the storage engine for the table,
173 public function isEngine($engine)
175 $tbl_storage_engine = $this->getStorageEngine();
177 if (is_array($engine)){
178 foreach($engine as $e){
179 if($e == $tbl_storage_engine){
185 return $tbl_storage_engine == $engine;
190 * returns whether the table is actually a view
192 * @return boolean whether the given is a view
194 public function isView()
196 $db = $this->_db_name
;
197 $table = $this->_name
;
198 if (empty($db) ||
empty($table)) {
202 // use cached data or load information with SHOW command
203 if ($this->_dbi
->getCachedTableContent(array($db, $table)) != null
204 ||
$GLOBALS['cfg']['Server']['DisableIS']
206 $type = $this->getStatusInfo('TABLE_TYPE');
207 return $type == 'VIEW' ||
$type == 'SYSTEM VIEW';
210 // information_schema tables are 'SYSTEM VIEW's
211 if ($db == 'information_schema') {
215 // query information_schema
216 $result = $this->_dbi
->fetchResult(
218 FROM information_schema.VIEWS
219 WHERE TABLE_SCHEMA = '" . $GLOBALS['dbi']->escapeString($db) . "'
220 AND TABLE_NAME = '" . $GLOBALS['dbi']->escapeString($table) . "'"
222 return $result ?
true : false;
226 * Returns whether the table is actually an updatable view
228 * @return boolean whether the given is an updatable view
230 public function isUpdatableView()
232 if (empty($this->_db_name
) ||
empty($this->_name
)) {
236 $result = $this->_dbi
->fetchResult(
238 FROM information_schema.VIEWS
239 WHERE TABLE_SCHEMA = '" . $GLOBALS['dbi']->escapeString($this->_db_name
) . "'
240 AND TABLE_NAME = '" . $GLOBALS['dbi']->escapeString($this->_name
) . "'
241 AND IS_UPDATABLE = 'YES'"
243 return $result ?
true : false;
247 * Checks if this is a merge table
249 * If the ENGINE of the table is MERGE or MRG_MYISAM (alias),
250 * this is a merge table.
252 * @return boolean true if it is a merge table
254 public function isMerge()
256 return $this->isEngine(array('MERGE', 'MRG_MYISAM'));
260 * Returns full table status info, or specific if $info provided
261 * this info is collected from information_schema
263 * @param string $info specific information to be fetched
264 * @param boolean $force_read read new rather than serving from cache
265 * @param boolean $disable_error if true, disables error message
267 * @todo DatabaseInterface::getTablesFull needs to be merged
268 * somehow into this class or at least better documented
272 public function getStatusInfo(
275 $disable_error = false
277 $db = $this->_db_name
;
278 $table = $this->_name
;
280 if (! empty($_SESSION['is_multi_query'])) {
281 $disable_error = true;
284 // sometimes there is only one entry (ExactRows) so
285 // we have to get the table's details
286 if ($this->_dbi
->getCachedTableContent(array($db, $table)) == null
288 ||
count($this->_dbi
->getCachedTableContent(array($db, $table))) == 1
290 $this->_dbi
->getTablesFull($db, $table);
293 if ($this->_dbi
->getCachedTableContent(array($db, $table)) == null) {
294 // happens when we enter the table creation dialog
295 // or when we really did not get any status info, for example
296 // when $table == 'TABLE_NAMES' after the user tried SHOW TABLES
300 if (null === $info) {
301 return $this->_dbi
->getCachedTableContent(array($db, $table));
304 // array_key_exists allows for null values
305 if (!array_key_exists(
306 $info, $this->_dbi
->getCachedTableContent(array($db, $table))
309 if (! $disable_error) {
311 __('Unknown table status:') . ' ' . $info,
318 return $this->_dbi
->getCachedTableContent(array($db, $table, $info));
322 * Returns the Table storage Engine for current table.
324 * @return string Return storage engine info if it is set for
325 * the selected table else return blank.
327 public function getStorageEngine() {
328 $table_storage_engine = $this->getStatusInfo('ENGINE', false, true);
329 if ($table_storage_engine === false) {
332 return strtoupper($table_storage_engine);
336 * Returns the comments for current table.
338 * @return string Return comment info if it is set for the selected table or return blank.
340 public function getComment() {
341 $table_comment = $this->getStatusInfo('COMMENT', false, true);
342 if ($table_comment === false) {
345 return $table_comment;
349 * Returns the collation for current table.
351 * @return string Return blank if collation is empty else return the collation info from table info.
353 public function getCollation() {
354 $table_collation = $this->getStatusInfo('TABLE_COLLATION', false, true);
355 if ($table_collation === false) {
358 return $table_collation;
362 * Returns the info about no of rows for current table.
364 * @return integer Return no of rows info if it is not null for the selected table or return 0.
366 public function getNumRows() {
367 $table_num_row_info = $this->getStatusInfo('TABLE_ROWS', false, true);
368 if (false === $table_num_row_info) {
369 $table_num_row_info = $this->_dbi
->getTable($this->_db_name
, $showtable['Name'])
370 ->countRecords(true);
372 return $table_num_row_info ?
$table_num_row_info : 0 ;
376 * Returns the Row format for current table.
378 * @return string Return table row format info if it is set for the selected table or return blank.
380 public function getRowFormat() {
381 $table_row_format = $this->getStatusInfo('ROW_FORMAT', false, true);
382 if ($table_row_format === false) {
385 return $table_row_format;
389 * Returns the auto increment option for current table.
391 * @return integer Return auto increment info if it is set for the selected table or return blank.
393 public function getAutoIncrement() {
394 $table_auto_increment = $this->getStatusInfo('AUTO_INCREMENT', false, true);
395 return isset($table_auto_increment) ?
$table_auto_increment : '';
399 * Returns the array for CREATE statement for current table.
400 * @return array Return options array info if it is set for the selected table or return blank.
402 public function getCreateOptions() {
403 $table_options = $this->getStatusInfo('CREATE_OPTIONS', false, true);
404 $create_options_tmp = empty($table_options) ?
array() : explode(' ', $table_options);
405 $create_options = array();
406 // export create options by its name as variables into global namespace
407 // f.e. pack_keys=1 becomes available as $pack_keys with value of '1'
408 // unset($pack_keys);
409 foreach ($create_options_tmp as $each_create_option) {
410 $each_create_option = explode('=', $each_create_option);
411 if (isset($each_create_option[1])) {
412 // ensure there is no ambiguity for PHP 5 and 7
413 $create_options[$each_create_option[0]] = $each_create_option[1];
416 // we need explicit DEFAULT value here (different from '0')
417 $create_options['pack_keys'] = (! isset($create_options['pack_keys']) ||
strlen($create_options['pack_keys']) == 0)
419 : $create_options['pack_keys'];
420 return $create_options;
424 * generates column specification for ALTER or CREATE TABLE syntax
426 * @param string $name name
427 * @param string $type type ('INT', 'VARCHAR', 'BIT', ...)
428 * @param string $length length ('2', '5,2', '', ...)
429 * @param string $attribute attribute
430 * @param string $collation collation
431 * @param bool|string $null with 'NULL' or 'NOT NULL'
432 * @param string $default_type whether default is CURRENT_TIMESTAMP,
433 * NULL, NONE, USER_DEFINED
434 * @param string $default_value default value for USER_DEFINED
436 * @param string $extra 'AUTO_INCREMENT'
437 * @param string $comment field comment
438 * @param string $virtuality virtuality of the column
439 * @param string $expression expression for the virtual column
440 * @param string $move_to new position for column
442 * @todo move into class PMA_Column
443 * @todo on the interface, some js to clear the default value when the
444 * default current_timestamp is checked
446 * @return string field specification
448 static function generateFieldSpec($name, $type, $length = '',
449 $attribute = '', $collation = '', $null = false,
450 $default_type = 'USER_DEFINED', $default_value = '', $extra = '',
451 $comment = '', $virtuality = '', $expression = '', $move_to = ''
453 $is_timestamp = mb_strpos(
454 mb_strtoupper($type),
458 $query = Util
::backquote($name) . ' ' . $type;
460 // allow the possibility of a length for TIME, DATETIME and TIMESTAMP
461 // (will work on MySQL >= 5.6.4)
463 // MySQL permits a non-standard syntax for FLOAT and DOUBLE,
464 // see https://dev.mysql.com/doc/refman/5.5/en/floating-point-types.html
466 $pattern = '@^(DATE|TINYBLOB|TINYTEXT|BLOB|TEXT|'
467 . 'MEDIUMBLOB|MEDIUMTEXT|LONGBLOB|LONGTEXT|SERIAL|BOOLEAN|UUID)$@i';
468 if (strlen($length) !== 0 && ! preg_match($pattern, $type)) {
469 // Note: The variable $length here can contain several other things
470 // besides length - ENUM/SET value or length of DECIMAL (eg. 12,3)
471 // so we can't just convert it to integer
472 $query .= '(' . $length . ')';
474 if ($attribute != '') {
475 $query .= ' ' . $attribute;
478 && preg_match('/TIMESTAMP/i', $attribute)
479 && strlen($length) !== 0
482 $query .= '(' . $length . ')';
487 $query .= ' AS (' . $expression . ') ' . $virtuality;
490 $matches = preg_match(
491 '@^(TINYTEXT|TEXT|MEDIUMTEXT|LONGTEXT|VARCHAR|CHAR|ENUM|SET)$@i',
494 if (! empty($collation) && $collation != 'NULL' && $matches) {
495 $query .= Util
::getCharsetQueryPart($collation, true);
498 if ($null !== false) {
499 if ($null == 'NULL') {
502 $query .= ' NOT NULL';
506 switch ($default_type) {
507 case 'USER_DEFINED' :
508 if ($is_timestamp && $default_value === '0') {
509 // a TIMESTAMP does not accept DEFAULT '0'
510 // but DEFAULT 0 works
511 $query .= ' DEFAULT 0';
512 } elseif ($type == 'BIT') {
513 $query .= ' DEFAULT b\''
514 . preg_replace('/[^01]/', '0', $default_value)
516 } elseif ($type == 'BOOLEAN') {
517 if (preg_match('/^1|T|TRUE|YES$/i', $default_value)) {
518 $query .= ' DEFAULT TRUE';
519 } elseif (preg_match('/^0|F|FALSE|NO$/i', $default_value)) {
520 $query .= ' DEFAULT FALSE';
522 // Invalid BOOLEAN value
523 $query .= ' DEFAULT \''
524 . $GLOBALS['dbi']->escapeString($default_value) . '\'';
526 } elseif ($type == 'BINARY' ||
$type == 'VARBINARY') {
527 $query .= ' DEFAULT 0x' . $default_value;
529 $query .= ' DEFAULT \''
530 . $GLOBALS['dbi']->escapeString($default_value) . '\'';
533 /** @noinspection PhpMissingBreakStatementInspection */
535 // If user uncheck null checkbox and not change default value null,
536 // default value will be ignored.
537 if ($null !== false && $null !== 'NULL') {
540 // else fall-through intended, no break here
541 case 'CURRENT_TIMESTAMP' :
542 $query .= ' DEFAULT ' . $default_type;
544 if (strlen($length) !== 0
547 && $default_type !== 'NULL' // Not to be added in case of NULL
549 $query .= '(' . $length . ')';
557 if (!empty($extra)) {
558 $query .= ' ' . $extra;
561 if (!empty($comment)) {
562 $query .= " COMMENT '" . $GLOBALS['dbi']->escapeString($comment) . "'";
566 if ($move_to == '-first') { // dash can't appear as part of column name
568 } elseif ($move_to != '') {
569 $query .= ' AFTER ' . Util
::backquote($move_to);
575 * Checks if the number of records in a table is at least equal to
578 * @param int $min_records Number of records to check for in a table
580 * @return bool True, if at least $min_records exist, False otherwise.
582 public function checkIfMinRecordsExist($min_records = 0)
584 $check_query = 'SELECT ';
585 $fieldsToSelect = '';
587 $uniqueFields = $this->getUniqueColumns(true, false);
588 if (count($uniqueFields) > 0) {
589 $fieldsToSelect = implode(', ', $uniqueFields);
591 $indexedCols = $this->getIndexedColumns(true, false);
592 if (count($indexedCols) > 0) {
593 $fieldsToSelect = implode(', ', $indexedCols);
595 $fieldsToSelect = '*';
599 $check_query .= $fieldsToSelect
600 . ' FROM ' . $this->getFullName(true)
601 . ' LIMIT ' . $min_records;
603 $res = $GLOBALS['dbi']->tryQuery(
607 if ($res !== false) {
608 $num_records = $GLOBALS['dbi']->numRows($res);
609 if ($num_records >= $min_records) {
618 * Counts and returns (or displays) the number of records in a table
620 * @param bool $force_exact whether to force an exact count
622 * @return mixed the number of records if "retain" param is true,
625 public function countRecords($force_exact = false)
627 $is_view = $this->isView();
628 $db = $this->_db_name
;
629 $table = $this->_name
;
631 if ($this->_dbi
->getCachedTableContent(array($db, $table, 'ExactRows')) != null) {
632 $row_count = $this->_dbi
->getCachedTableContent(
633 array($db, $table, 'ExactRows')
639 if (! $force_exact) {
640 if (($this->_dbi
->getCachedTableContent(array($db, $table, 'Rows')) == null)
643 $tmp_tables = $this->_dbi
->getTablesFull($db, $table);
644 if (isset($tmp_tables[$table])) {
645 $this->_dbi
->cacheTableContent(
651 if ($this->_dbi
->getCachedTableContent(array($db, $table, 'Rows')) != null) {
652 $row_count = $this->_dbi
->getCachedTableContent(
653 array($db, $table, 'Rows')
659 // for a VIEW, $row_count is always false at this point
660 if (false !== $row_count
661 && $row_count >= $GLOBALS['cfg']['MaxExactCount']
667 $row_count = $this->_dbi
->fetchValue(
668 'SELECT COUNT(*) FROM ' . Util
::backquote($db) . '.'
669 . Util
::backquote($table)
672 // For complex views, even trying to get a partial record
673 // count could bring down a server, so we offer an
674 // alternative: setting MaxExactCountViews to 0 will bypass
675 // completely the record counting for views
677 if ($GLOBALS['cfg']['MaxExactCountViews'] == 0) {
680 // Counting all rows of a VIEW could be too long,
681 // so use a LIMIT clause.
682 // Use try_query because it can fail (when a VIEW is
683 // based on a table that no longer exists)
684 $result = $this->_dbi
->tryQuery(
685 'SELECT 1 FROM ' . Util
::backquote($db) . '.'
686 . Util
::backquote($table) . ' LIMIT '
687 . $GLOBALS['cfg']['MaxExactCountViews'],
689 DatabaseInterface
::QUERY_STORE
691 if (!$this->_dbi
->getError()) {
692 $row_count = $this->_dbi
->numRows($result);
693 $this->_dbi
->freeResult($result);
698 $this->_dbi
->cacheTableContent(array($db, $table, 'ExactRows'), $row_count);
702 } // end of the 'Table::countRecords()' function
705 * Generates column specification for ALTER syntax
707 * @param string $oldcol old column name
708 * @param string $newcol new column name
709 * @param string $type type ('INT', 'VARCHAR', 'BIT', ...)
710 * @param string $length length ('2', '5,2', '', ...)
711 * @param string $attribute attribute
712 * @param string $collation collation
713 * @param bool|string $null with 'NULL' or 'NOT NULL'
714 * @param string $default_type whether default is CURRENT_TIMESTAMP,
715 * NULL, NONE, USER_DEFINED
716 * @param string $default_value default value for USER_DEFINED default
718 * @param string $extra 'AUTO_INCREMENT'
719 * @param string $comment field comment
720 * @param string $virtuality virtuality of the column
721 * @param string $expression expression for the virtual column
722 * @param string $move_to new position for column
724 * @see Table::generateFieldSpec()
726 * @return string field specification
728 static public function generateAlter($oldcol, $newcol, $type, $length,
729 $attribute, $collation, $null, $default_type, $default_value,
730 $extra, $comment, $virtuality, $expression, $move_to
732 return Util
::backquote($oldcol) . ' '
733 . Table
::generateFieldSpec(
734 $newcol, $type, $length, $attribute,
735 $collation, $null, $default_type, $default_value, $extra,
736 $comment, $virtuality, $expression, $move_to
741 * Inserts existing entries in a PMA_* table by reading a value from an old
744 * @param string $work The array index, which Relation feature to
745 * check ('relwork', 'commwork', ...)
746 * @param string $pma_table The array index, which PMA-table to update
747 * ('bookmark', 'relation', ...)
748 * @param array $get_fields Which fields will be SELECT'ed from the old entry
749 * @param array $where_fields Which fields will be used for the WHERE query
750 * (array('FIELDNAME' => 'FIELDVALUE'))
751 * @param array $new_fields Which fields will be used as new VALUES.
752 * These are the important keys which differ
754 * (array('FIELDNAME' => 'NEW FIELDVALUE'))
756 * @global relation variable
758 * @return int|boolean
760 static public function duplicateInfo($work, $pma_table, $get_fields,
761 $where_fields, $new_fields
765 if (!isset($GLOBALS['cfgRelation']) ||
!$GLOBALS['cfgRelation'][$work]) {
769 $select_parts = array();
770 $row_fields = array();
771 foreach ($get_fields as $get_field) {
772 $select_parts[] = Util
::backquote($get_field);
773 $row_fields[$get_field] = 'cc';
776 $where_parts = array();
777 foreach ($where_fields as $_where => $_value) {
778 $where_parts[] = Util
::backquote($_where) . ' = \''
779 . $GLOBALS['dbi']->escapeString($_value) . '\'';
782 $new_parts = array();
783 $new_value_parts = array();
784 foreach ($new_fields as $_where => $_value) {
785 $new_parts[] = Util
::backquote($_where);
786 $new_value_parts[] = $GLOBALS['dbi']->escapeString($_value);
789 $table_copy_query = '
790 SELECT ' . implode(', ', $select_parts) . '
791 FROM ' . Util
::backquote($GLOBALS['cfgRelation']['db']) . '.'
792 . Util
::backquote($GLOBALS['cfgRelation'][$pma_table]) . '
793 WHERE ' . implode(' AND ', $where_parts);
795 // must use DatabaseInterface::QUERY_STORE here, since we execute
796 // another query inside the loop
797 $table_copy_rs = PMA_queryAsControlUser(
798 $table_copy_query, true, DatabaseInterface
::QUERY_STORE
801 while ($table_copy_row = @$GLOBALS['dbi']->fetchAssoc($table_copy_rs)) {
802 $value_parts = array();
803 foreach ($table_copy_row as $_key => $_val) {
804 if (isset($row_fields[$_key]) && $row_fields[$_key] == 'cc') {
805 $value_parts[] = $GLOBALS['dbi']->escapeString($_val);
809 $new_table_query = 'INSERT IGNORE INTO '
810 . Util
::backquote($GLOBALS['cfgRelation']['db'])
811 . '.' . Util
::backquote($GLOBALS['cfgRelation'][$pma_table])
812 . ' (' . implode(', ', $select_parts) . ', '
813 . implode(', ', $new_parts) . ') VALUES (\''
814 . implode('\', \'', $value_parts) . '\', \''
815 . implode('\', \'', $new_value_parts) . '\')';
817 PMA_queryAsControlUser($new_table_query);
818 $last_id = $GLOBALS['dbi']->insertId();
821 $GLOBALS['dbi']->freeResult($table_copy_rs);
824 } // end of 'Table::duplicateInfo()' function
827 * Copies or renames table
829 * @param string $source_db source database
830 * @param string $source_table source table
831 * @param string $target_db target database
832 * @param string $target_table target table
833 * @param string $what what to be moved or copied (data, dataonly)
834 * @param bool $move whether to move
835 * @param string $mode mode
837 * @return bool true if success, false otherwise
839 static public function moveCopy($source_db, $source_table, $target_db,
840 $target_table, $what, $move, $mode
845 // Try moving the tables directly, using native `RENAME` statement.
846 if ($move && $what == 'data') {
847 $tbl = new Table($source_table, $source_db);
848 if ($tbl->rename($target_table, $target_db)) {
849 $GLOBALS['message'] = $tbl->getLastMessage();
854 // Setting required export settings.
855 $GLOBALS['sql_backquotes'] = 1;
856 $GLOBALS['asfile'] = 1;
858 // Ensuring the target database is valid.
859 if (! $GLOBALS['dblist']->databases
->exists($source_db, $target_db)) {
860 if (! $GLOBALS['dblist']->databases
->exists($source_db)) {
861 $GLOBALS['message'] = Message
::rawError(
863 __('Source database `%s` was not found!'),
864 htmlspecialchars($source_db)
868 if (! $GLOBALS['dblist']->databases
->exists($target_db)) {
869 $GLOBALS['message'] = Message
::rawError(
871 __('Target database `%s` was not found!'),
872 htmlspecialchars($target_db)
880 * The full name of source table, quoted.
881 * @var string $source
883 $source = Util
::backquote($source_db)
884 . '.' . Util
::backquote($source_table);
886 // If the target database is not specified, the operation is taking
887 // place in the same database.
888 if (! isset($target_db) ||
strlen($target_db) === 0) {
889 $target_db = $source_db;
892 // Selecting the database could avoid some problems with replicated
893 // databases, when moving table from replicated one to not replicated one.
894 $GLOBALS['dbi']->selectDb($target_db);
897 * The full name of target table, quoted.
898 * @var string $target
900 $target = Util
::backquote($target_db)
901 . '.' . Util
::backquote($target_table);
903 // No table is created when this is a data-only operation.
904 if ($what != 'dataonly') {
906 include_once "libraries/plugin_interface.lib.php";
909 * Instance used for exporting the current structure of the table.
911 * @var \PMA\libraries\plugins\export\ExportSql
913 $export_sql_plugin = PMA_getPlugin(
916 'libraries/plugins/export/',
918 'export_type' => 'table',
919 'single_table' => false,
923 $no_constraints_comments = true;
924 $GLOBALS['sql_constraints_query'] = '';
925 // set the value of global sql_auto_increment variable
926 if (isset($_POST['sql_auto_increment'])) {
927 $GLOBALS['sql_auto_increment'] = $_POST['sql_auto_increment'];
931 * The old structure of the table..
932 * @var string $sql_structure
934 $sql_structure = $export_sql_plugin->getTableDef(
935 $source_db, $source_table, "\n", $err_url, false, false
938 unset($no_constraints_comments);
940 // -----------------------------------------------------------------
941 // Phase 0: Preparing structures used.
944 * The destination where the table is moved or copied to.
947 $destination = new Expression(
948 $target_db, $target_table, ''
951 // Find server's SQL mode so the builder can generate correct
953 // One of the options that alters the behaviour is `ANSI_QUOTES`.
955 $GLOBALS['dbi']->fetchValue("SELECT @@sql_mode")
958 // -----------------------------------------------------------------
959 // Phase 1: Dropping existent element of the same name (if exists
962 if (isset($_REQUEST['drop_if_exists'])
963 && $_REQUEST['drop_if_exists'] == 'true'
967 * Drop statement used for building the query.
968 * @var DropStatement $statement
970 $statement = new DropStatement();
972 $tbl = new Table($target_db, $target_table);
974 $statement->options
= new OptionsArray(
976 $tbl->isView() ?
'VIEW' : 'TABLE',
981 $statement->fields
= array($destination);
983 // Building the query.
984 $drop_query = $statement->build() . ';';
987 $GLOBALS['dbi']->query($drop_query);
988 $GLOBALS['sql_query'] .= "\n" . $drop_query;
990 // If an existing table gets deleted, maintain any entries for
992 $maintain_relations = true;
995 // -----------------------------------------------------------------
996 // Phase 2: Generating the new query of this structure.
999 * The parser responsible for parsing the old queries.
1000 * @var Parser $parser
1002 $parser = new Parser($sql_structure);
1004 if (!empty($parser->statements
[0])) {
1007 * The CREATE statement of this structure.
1008 * @var \PhpMyAdmin\SqlParser\Statements\CreateStatement $statement
1010 $statement = $parser->statements
[0];
1012 // Changing the destination.
1013 $statement->name
= $destination;
1015 // Building back the query.
1016 $sql_structure = $statement->build() . ';';
1019 $GLOBALS['dbi']->query($sql_structure);
1020 $GLOBALS['sql_query'] .= "\n" . $sql_structure;
1023 // -----------------------------------------------------------------
1024 // Phase 3: Adding constraints.
1025 // All constraint names are removed because they must be unique.
1027 if (($move ||
isset($GLOBALS['add_constraints']))
1028 && !empty($GLOBALS['sql_constraints_query'])
1031 $parser = new Parser($GLOBALS['sql_constraints_query']);
1034 * The ALTER statement that generates the constraints.
1035 * @var \PhpMyAdmin\SqlParser\Statements\AlterStatement $statement
1037 $statement = $parser->statements
[0];
1039 // Changing the altered table to the destination.
1040 $statement->table
= $destination;
1042 // Removing the name of the constraints.
1043 foreach ($statement->altered
as $idx => $altered) {
1044 // All constraint names are removed because they must be unique.
1045 if ($altered->options
->has('CONSTRAINT')) {
1046 $altered->field
= null;
1050 // Building back the query.
1051 $GLOBALS['sql_constraints_query'] = $statement->build() . ';';
1054 if ($mode == 'one_table') {
1055 $GLOBALS['dbi']->query($GLOBALS['sql_constraints_query']);
1057 $GLOBALS['sql_query'] .= "\n" . $GLOBALS['sql_constraints_query'];
1058 if ($mode == 'one_table') {
1059 unset($GLOBALS['sql_constraints_query']);
1063 // -----------------------------------------------------------------
1064 // Phase 4: Adding indexes.
1067 if (!empty($GLOBALS['sql_indexes'])) {
1069 $parser = new Parser($GLOBALS['sql_indexes']);
1071 $GLOBALS['sql_indexes'] = '';
1073 * The ALTER statement that generates the indexes.
1074 * @var \PhpMyAdmin\SqlParser\Statements\AlterStatement $statement
1076 foreach ($parser->statements
as $statement) {
1078 // Changing the altered table to the destination.
1079 $statement->table
= $destination;
1081 // Removing the name of the constraints.
1082 foreach ($statement->altered
as $idx => $altered) {
1083 // All constraint names are removed because they must be unique.
1084 if ($altered->options
->has('CONSTRAINT')) {
1085 $altered->field
= null;
1089 // Building back the query.
1090 $sql_index = $statement->build() . ';';
1093 if ($mode == 'one_table' ||
$mode == 'db_copy') {
1094 $GLOBALS['dbi']->query($sql_index);
1097 $GLOBALS['sql_indexes'] .= $sql_index;
1100 $GLOBALS['sql_query'] .= "\n" . $GLOBALS['sql_indexes'];
1101 if ($mode == 'one_table' ||
$mode == 'db_copy') {
1102 unset($GLOBALS['sql_indexes']);
1106 // -----------------------------------------------------------------
1107 // Phase 5: Adding AUTO_INCREMENT.
1109 if (! empty($GLOBALS['sql_auto_increments'])) {
1110 if ($mode == 'one_table' ||
$mode == 'db_copy') {
1112 $parser = new Parser($GLOBALS['sql_auto_increments']);
1115 * The ALTER statement that alters the AUTO_INCREMENT value.
1116 * @var \PhpMyAdmin\SqlParser\Statements\AlterStatement $statement
1118 $statement = $parser->statements
[0];
1120 // Changing the altered table to the destination.
1121 $statement->table
= $destination;
1123 // Building back the query.
1124 $GLOBALS['sql_auto_increments'] = $statement->build() . ';';
1127 $GLOBALS['dbi']->query($GLOBALS['sql_auto_increments']);
1128 $GLOBALS['sql_query'] .= "\n" . $GLOBALS['sql_auto_increments'];
1129 unset($GLOBALS['sql_auto_increments']);
1133 $GLOBALS['sql_query'] = '';
1136 $_table = new Table($target_table, $target_db);
1137 // Copy the data unless this is a VIEW
1138 if (($what == 'data' ||
$what == 'dataonly')
1139 && ! $_table->isView()
1141 $sql_set_mode = "SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO'";
1142 $GLOBALS['dbi']->query($sql_set_mode);
1143 $GLOBALS['sql_query'] .= "\n\n" . $sql_set_mode . ';';
1145 $_old_table = new Table($source_table, $source_db);
1146 $nonGeneratedCols = $_old_table->getNonGeneratedColumns(true);
1147 if (count($nonGeneratedCols) > 0) {
1148 $sql_insert_data = 'INSERT INTO ' . $target . '('
1149 . implode(', ', $nonGeneratedCols)
1150 . ') SELECT ' . implode(', ', $nonGeneratedCols)
1151 . ' FROM ' . $source;
1153 $GLOBALS['dbi']->query($sql_insert_data);
1154 $GLOBALS['sql_query'] .= "\n\n" . $sql_insert_data . ';';
1158 PMA_getRelationsParam();
1160 // Drops old table if the user has requested to move it
1163 // This could avoid some problems with replicated databases, when
1164 // moving table from replicated one to not replicated one
1165 $GLOBALS['dbi']->selectDb($source_db);
1167 $_source_table = new Table($source_table, $source_db);
1168 if ($_source_table->isView()) {
1169 $sql_drop_query = 'DROP VIEW';
1171 $sql_drop_query = 'DROP TABLE';
1173 $sql_drop_query .= ' ' . $source;
1174 $GLOBALS['dbi']->query($sql_drop_query);
1176 // Renable table in configuration storage
1177 PMA_REL_renameTable(
1178 $source_db, $target_db,
1179 $source_table, $target_table
1182 $GLOBALS['sql_query'] .= "\n\n" . $sql_drop_query . ';';
1188 // Create new entries as duplicates from old PMA DBs
1189 if ($what == 'dataonly' ||
isset($maintain_relations)) {
1193 if ($GLOBALS['cfgRelation']['commwork']) {
1194 // Get all comments and MIME-Types for current table
1195 $comments_copy_rs = PMA_queryAsControlUser(
1196 'SELECT column_name, comment'
1197 . ($GLOBALS['cfgRelation']['mimework']
1198 ?
', mimetype, transformation, transformation_options'
1201 . Util
::backquote($GLOBALS['cfgRelation']['db'])
1203 . Util
::backquote($GLOBALS['cfgRelation']['column_info'])
1206 . $GLOBALS['dbi']->escapeString($source_db) . '\''
1208 . ' table_name = \''
1209 . $GLOBALS['dbi']->escapeString($source_table) . '\''
1212 // Write every comment as new copied entry. [MIME]
1213 while ($comments_copy_row
1214 = $GLOBALS['dbi']->fetchAssoc($comments_copy_rs)) {
1215 $new_comment_query = 'REPLACE INTO '
1216 . Util
::backquote($GLOBALS['cfgRelation']['db'])
1217 . '.' . Util
::backquote(
1218 $GLOBALS['cfgRelation']['column_info']
1220 . ' (db_name, table_name, column_name, comment'
1221 . ($GLOBALS['cfgRelation']['mimework']
1222 ?
', mimetype, transformation, transformation_options'
1224 . ') ' . ' VALUES(' . '\'' . $GLOBALS['dbi']->escapeString($target_db)
1225 . '\',\'' . $GLOBALS['dbi']->escapeString($target_table) . '\',\''
1226 . $GLOBALS['dbi']->escapeString($comments_copy_row['column_name'])
1227 . '\',\'' . $GLOBALS['dbi']->escapeString($target_table) . '\',\''
1228 . $GLOBALS['dbi']->escapeString($comments_copy_row['comment'])
1230 . ($GLOBALS['cfgRelation']['mimework']
1231 ?
',\'' . $GLOBALS['dbi']->escapeString(
1232 $comments_copy_row['mimetype']
1234 . '\',' . '\'' . $GLOBALS['dbi']->escapeString(
1235 $comments_copy_row['transformation']
1237 . '\',' . '\'' . $GLOBALS['dbi']->escapeString(
1238 $comments_copy_row['transformation_options']
1243 PMA_queryAsControlUser($new_comment_query);
1245 $GLOBALS['dbi']->freeResult($comments_copy_rs);
1246 unset($comments_copy_rs);
1249 // duplicating the bookmarks must not be done here, but
1252 $get_fields = array('display_field');
1253 $where_fields = array(
1254 'db_name' => $source_db,
1255 'table_name' => $source_table
1257 $new_fields = array(
1258 'db_name' => $target_db,
1259 'table_name' => $target_table
1261 Table
::duplicateInfo(
1270 * @todo revise this code when we support cross-db relations
1272 $get_fields = array(
1277 $where_fields = array(
1278 'master_db' => $source_db,
1279 'master_table' => $source_table
1281 $new_fields = array(
1282 'master_db' => $target_db,
1283 'foreign_db' => $target_db,
1284 'master_table' => $target_table
1286 Table
::duplicateInfo(
1294 $get_fields = array(
1299 $where_fields = array(
1300 'foreign_db' => $source_db,
1301 'foreign_table' => $source_table
1303 $new_fields = array(
1304 'master_db' => $target_db,
1305 'foreign_db' => $target_db,
1306 'foreign_table' => $target_table
1308 Table
::duplicateInfo(
1317 * @todo Can't get duplicating PDFs the right way. The
1318 * page numbers always get screwed up independently from
1319 * duplication because the numbers do not seem to be stored on a
1320 * per-database basis. Would the author of pdf support please
1321 * have a look at it?
1323 $get_fields = array('page_descr');
1324 $where_fields = array('db_name' => $source_db);
1325 $new_fields = array('db_name' => $target_db);
1326 $last_id = Table::duplicateInfo(
1334 if (isset($last_id) && $last_id >= 0) {
1335 $get_fields = array('x', 'y');
1336 $where_fields = array(
1337 'db_name' => $source_db,
1338 'table_name' => $source_table
1340 $new_fields = array(
1341 'db_name' => $target_db,
1342 'table_name' => $target_table,
1343 'pdf_page_number' => $last_id
1345 Table::duplicateInfo(
1359 * checks if given name is a valid table name,
1360 * currently if not empty, trailing spaces, '.', '/' and '\'
1362 * @param string $table_name name to check
1363 * @param boolean $is_backquoted whether this name is used inside backquotes or not
1365 * @todo add check for valid chars in filename on current system/os
1366 * @see https://dev.mysql.com/doc/refman/5.0/en/legal-names.html
1368 * @return boolean whether the string is valid or not
1370 static function isValidName($table_name, $is_backquoted = false)
1372 if ($table_name !== rtrim($table_name)) {
1373 // trailing spaces not allowed even in backquotes
1377 if (strlen($table_name) === 0) {
1382 if (! $is_backquoted && $table_name !== trim($table_name)) {
1383 // spaces at the start or in between only allowed inside backquotes
1387 if (! $is_backquoted && preg_match('/^[a-zA-Z0-9_$]+$/', $table_name)) {
1388 // only allow the above regex in unquoted identifiers
1389 // see : https://dev.mysql.com/doc/refman/5.7/en/identifiers.html
1391 } else if ($is_backquoted) {
1392 // If backquoted, all characters should be allowed (except w/ trailing spaces)
1396 // If not backquoted and doesn't follow the above regex
1403 * @param string $new_name new table name
1404 * @param string $new_db new database name
1406 * @return bool success
1408 public function rename($new_name, $new_db = null)
1410 if ($GLOBALS['dbi']->getLowerCaseNames() === '1') {
1411 $new_name = strtolower($new_name);
1414 if (null !== $new_db && $new_db !== $this->getDbName()) {
1415 // Ensure the target is valid
1416 if (! $GLOBALS['dblist']->databases
->exists($new_db)) {
1417 $this->errors
[] = __('Invalid database:') . ' ' . $new_db;
1421 $new_db = $this->getDbName();
1424 $new_table = new Table($new_name, $new_db);
1426 if ($this->getFullName() === $new_table->getFullName()) {
1430 // Allow whitespaces (not trailing) in $new_name,
1431 // since we are using $backquoted in getting the fullName of table
1432 // below to be used in the query
1433 if (! Table
::isValidName($new_name, true)) {
1434 $this->errors
[] = __('Invalid table name:') . ' '
1435 . $new_table->getFullName();
1439 // If the table is moved to a different database drop its triggers first
1440 $triggers = $this->_dbi
->getTriggers(
1441 $this->getDbName(), $this->getName(), ''
1443 $handle_triggers = $this->getDbName() != $new_db && $triggers;
1444 if ($handle_triggers) {
1445 foreach ($triggers as $trigger) {
1446 $sql = 'DROP TRIGGER IF EXISTS '
1447 . Util
::backquote($this->getDbName())
1448 . '.' . Util
::backquote($trigger['name']) . ';';
1449 $this->_dbi
->query($sql);
1454 * tested also for a view, in MySQL 5.0.92, 5.1.55 and 5.5.13
1456 $GLOBALS['sql_query'] = '
1457 RENAME TABLE ' . $this->getFullName(true) . '
1458 TO ' . $new_table->getFullName(true) . ';';
1459 // I don't think a specific error message for views is necessary
1460 if (! $this->_dbi
->query($GLOBALS['sql_query'])) {
1461 // Restore triggers in the old database
1462 if ($handle_triggers) {
1463 $this->_dbi
->selectDb($this->getDbName());
1464 foreach ($triggers as $trigger) {
1465 $this->_dbi
->query($trigger['create']);
1468 $this->errors
[] = sprintf(
1469 __('Failed to rename table %1$s to %2$s!'),
1470 $this->getFullName(),
1471 $new_table->getFullName()
1476 $old_name = $this->getName();
1477 $old_db = $this->getDbName();
1478 $this->_name
= $new_name;
1479 $this->_db_name
= $new_db;
1481 // Renable table in configuration storage
1482 PMA_REL_renameTable(
1484 $old_name, $new_name
1487 $this->messages
[] = sprintf(
1488 __('Table %1$s has been renamed to %2$s.'),
1489 htmlspecialchars($old_name),
1490 htmlspecialchars($new_name)
1496 * Get all unique columns
1498 * returns an array with all columns with unique content, in fact these are
1499 * all columns being single indexed in PRIMARY or UNIQUE
1502 * - PRIMARY(id) // id
1503 * - UNIQUE(name) // name
1504 * - PRIMARY(fk_id1, fk_id2) // NONE
1505 * - UNIQUE(x,y) // NONE
1507 * @param bool $backquoted whether to quote name with backticks ``
1508 * @param bool $fullName whether to include full name of the table as a prefix
1512 public function getUniqueColumns($backquoted = true, $fullName = true)
1514 $sql = $this->_dbi
->getTableIndexesSql(
1519 $uniques = $this->_dbi
->fetchResult(
1521 array('Key_name', null),
1526 foreach ($uniques as $index) {
1527 if (count($index) > 1) {
1531 $possible_column = $this->getFullName($backquoted) . '.';
1533 $possible_column = '';
1536 $possible_column .= Util
::backquote($index[0]);
1538 $possible_column .= $index[0];
1540 // a column might have a primary and an unique index on it
1541 if (! in_array($possible_column, $return)) {
1542 $return[] = $possible_column;
1550 * Formats lists of columns
1552 * returns an array with all columns that make use of an index
1554 * e.g. index(col1, col2) would return col1, col2
1556 * @param array $indexed column data
1557 * @param bool $backquoted whether to quote name with backticks ``
1558 * @param bool $fullName whether to include full name of the table as a prefix
1562 private function _formatColumns($indexed, $backquoted, $fullName)
1565 foreach ($indexed as $column) {
1566 $return[] = ($fullName ?
$this->getFullName($backquoted) . '.' : '')
1567 . ($backquoted ? Util
::backquote($column) : $column);
1574 * Get all indexed columns
1576 * returns an array with all columns that make use of an index
1578 * e.g. index(col1, col2) would return col1, col2
1580 * @param bool $backquoted whether to quote name with backticks ``
1581 * @param bool $fullName whether to include full name of the table as a prefix
1585 public function getIndexedColumns($backquoted = true, $fullName = true)
1587 $sql = $this->_dbi
->getTableIndexesSql(
1592 $indexed = $this->_dbi
->fetchResult($sql, 'Column_name', 'Column_name');
1594 return $this->_formatColumns($indexed, $backquoted, $fullName);
1600 * returns an array with all columns
1602 * @param bool $backquoted whether to quote name with backticks ``
1603 * @param bool $fullName whether to include full name of the table as a prefix
1607 public function getColumns($backquoted = true, $fullName = true)
1609 $sql = 'SHOW COLUMNS FROM ' . $this->getFullName(true);
1610 $indexed = $this->_dbi
->fetchResult($sql, 'Field', 'Field');
1612 return $this->_formatColumns($indexed, $backquoted, $fullName);
1616 * Get meta info for fields in table
1620 public function getColumnsMeta()
1622 $move_columns_sql_query = sprintf(
1623 'SELECT * FROM %s.%s LIMIT 1',
1624 Util
::backquote($this->_db_name
),
1625 Util
::backquote($this->_name
)
1627 $move_columns_sql_result = $this->_dbi
->tryQuery($move_columns_sql_query);
1628 if ($move_columns_sql_result !== false) {
1629 return $this->_dbi
->getFieldsMeta($move_columns_sql_result);
1631 // unsure how to reproduce but it was seen on the reporting server
1637 * Get non-generated columns in table
1639 * @param bool $backquoted whether to quote name with backticks ``
1643 public function getNonGeneratedColumns($backquoted = true)
1645 $columns_meta_query = 'SHOW COLUMNS FROM ' . $this->getFullName(true);
1648 $columns_meta_query_result = $this->_dbi
->fetchResult(
1652 if ($columns_meta_query_result
1653 && $columns_meta_query_result !== false
1655 foreach ($columns_meta_query_result as $column) {
1656 $value = $column['Field'];
1657 if ($backquoted === true) {
1658 $value = Util
::backquote($value);
1661 if (strpos($column['Extra'], 'GENERATED') === false) {
1662 array_push($ret, $value);
1671 * Return UI preferences for this table from phpMyAdmin database.
1675 protected function getUiPrefsFromDb()
1677 $cfgRelation = PMA_getRelationsParam();
1678 $pma_table = Util
::backquote($cfgRelation['db']) . "."
1679 . Util
::backquote($cfgRelation['table_uiprefs']);
1681 // Read from phpMyAdmin database
1682 $sql_query = " SELECT `prefs` FROM " . $pma_table
1683 . " WHERE `username` = '" . $GLOBALS['dbi']->escapeString($GLOBALS['cfg']['Server']['user']) . "'"
1684 . " AND `db_name` = '" . $GLOBALS['dbi']->escapeString($this->_db_name
) . "'"
1685 . " AND `table_name` = '" . $GLOBALS['dbi']->escapeString($this->_name
) . "'";
1687 $row = $this->_dbi
->fetchArray(PMA_queryAsControlUser($sql_query));
1688 if (isset($row[0])) {
1689 return json_decode($row[0], true);
1696 * Save this table's UI preferences into phpMyAdmin database.
1698 * @return true|Message
1700 protected function saveUiPrefsToDb()
1702 $cfgRelation = PMA_getRelationsParam();
1703 $pma_table = Util
::backquote($cfgRelation['db']) . "."
1704 . Util
::backquote($cfgRelation['table_uiprefs']);
1706 $secureDbName = $GLOBALS['dbi']->escapeString($this->_db_name
);
1708 $username = $GLOBALS['cfg']['Server']['user'];
1709 $sql_query = " REPLACE INTO " . $pma_table
1710 . " (username, db_name, table_name, prefs) VALUES ('"
1711 . $GLOBALS['dbi']->escapeString($username) . "', '" . $secureDbName
1712 . "', '" . $GLOBALS['dbi']->escapeString($this->_name
) . "', '"
1713 . $GLOBALS['dbi']->escapeString(json_encode($this->uiprefs
)) . "')";
1715 $success = $this->_dbi
->tryQuery($sql_query, $GLOBALS['controllink']);
1718 $message = Message
::error(
1719 __('Could not save table UI preferences!')
1721 $message->addMessage(
1723 $this->_dbi
->getError($GLOBALS['controllink'])
1730 // Remove some old rows in table_uiprefs if it exceeds the configured
1732 $sql_query = 'SELECT COUNT(*) FROM ' . $pma_table;
1733 $rows_count = $this->_dbi
->fetchValue($sql_query);
1734 $max_rows = $GLOBALS['cfg']['Server']['MaxTableUiprefs'];
1735 if ($rows_count > $max_rows) {
1736 $num_rows_to_delete = $rows_count - $max_rows;
1738 = ' DELETE FROM ' . $pma_table .
1739 ' ORDER BY last_update ASC' .
1740 ' LIMIT ' . $num_rows_to_delete;
1741 $success = $this->_dbi
->tryQuery(
1742 $sql_query, $GLOBALS['controllink']
1746 $message = Message
::error(
1749 'Failed to cleanup table UI preferences (see ' .
1750 '$cfg[\'Servers\'][$i][\'MaxTableUiprefs\'] %s)'
1752 Util
::showDocu('config', 'cfg_Servers_MaxTableUiprefs')
1755 $message->addMessage(
1757 $this->_dbi
->getError($GLOBALS['controllink'])
1769 * Loads the UI preferences for this table.
1770 * If pmadb and table_uiprefs is set, it will load the UI preferences from
1771 * phpMyAdmin database.
1775 protected function loadUiPrefs()
1777 $cfgRelation = PMA_getRelationsParam();
1778 $server_id = $GLOBALS['server'];
1780 // set session variable if it's still undefined
1781 if (!isset($_SESSION['tmpval']['table_uiprefs'][$server_id][$this->_db_name
][$this->_name
])) {
1782 // check whether we can get from pmadb
1783 $_SESSION['tmpval']['table_uiprefs'][$server_id][$this->_db_name
]
1784 [$this->_name
] = $cfgRelation['uiprefswork']
1785 ?
$this->getUiPrefsFromDb()
1788 $this->uiprefs
=& $_SESSION['tmpval']['table_uiprefs'][$server_id]
1789 [$this->_db_name
][$this->_name
];
1793 * Get a property from UI preferences.
1794 * Return false if the property is not found.
1795 * Available property:
1796 * - PROP_SORTED_COLUMN
1797 * - PROP_COLUMN_ORDER
1798 * - PROP_COLUMN_VISIB
1800 * @param string $property property
1804 public function getUiProp($property)
1806 if (! isset($this->uiprefs
)) {
1807 $this->loadUiPrefs();
1810 // do checking based on property
1811 if ($property == self
::PROP_SORTED_COLUMN
) {
1812 if (!isset($this->uiprefs
[$property])) {
1816 if (!isset($_REQUEST['discard_remembered_sort'])) {
1817 // check if the column name exists in this table
1818 $tmp = explode(' ', $this->uiprefs
[$property]);
1820 //remove backquoting from colname
1821 $colname = str_replace('`', '', $colname);
1822 //get the available column name without backquoting
1823 $avail_columns = $this->getColumns(false);
1825 foreach ($avail_columns as $each_col) {
1826 // check if $each_col ends with $colname
1830 mb_strlen($each_col) - mb_strlen($colname)
1833 return $this->uiprefs
[$property];
1837 // remove the property, since it no longer exists in database
1838 $this->removeUiProp(self
::PROP_SORTED_COLUMN
);
1842 if ($property == self
::PROP_COLUMN_ORDER
1843 ||
$property == self
::PROP_COLUMN_VISIB
1845 if ($this->isView() ||
!isset($this->uiprefs
[$property])) {
1849 // check if the table has not been modified
1850 if ($this->getStatusInfo('Create_time') == $this->uiprefs
['CREATE_TIME']
1852 return array_map('intval', $this->uiprefs
[$property]);
1855 // remove the property, since the table has been modified
1856 $this->removeUiProp(self
::PROP_COLUMN_ORDER
);
1860 // default behaviour for other property:
1861 return isset($this->uiprefs
[$property]) ?
$this->uiprefs
[$property] : false;
1865 * Set a property from UI preferences.
1866 * If pmadb and table_uiprefs is set, it will save the UI preferences to
1867 * phpMyAdmin database.
1868 * Available property:
1869 * - PROP_SORTED_COLUMN
1870 * - PROP_COLUMN_ORDER
1871 * - PROP_COLUMN_VISIB
1873 * @param string $property Property
1874 * @param mixed $value Value for the property
1875 * @param string $table_create_time Needed for PROP_COLUMN_ORDER
1876 * and PROP_COLUMN_VISIB
1878 * @return boolean|Message
1880 public function setUiProp($property, $value, $table_create_time = null)
1882 if (! isset($this->uiprefs
)) {
1883 $this->loadUiPrefs();
1885 // we want to save the create time if the property is PROP_COLUMN_ORDER
1886 if (! $this->isView()
1887 && ($property == self
::PROP_COLUMN_ORDER
1888 ||
$property == self
::PROP_COLUMN_VISIB
)
1890 $curr_create_time = $this->getStatusInfo('CREATE_TIME');
1891 if (isset($table_create_time)
1892 && $table_create_time == $curr_create_time
1894 $this->uiprefs
['CREATE_TIME'] = $curr_create_time;
1896 // there is no $table_create_time, or
1897 // supplied $table_create_time is older than current create time,
1899 return Message
::error(
1902 'Cannot save UI property "%s". The changes made will ' .
1903 'not be persistent after you refresh this page. ' .
1904 'Please check if the table structure has been changed.'
1912 $this->uiprefs
[$property] = $value;
1914 // check if pmadb is set
1915 $cfgRelation = PMA_getRelationsParam();
1916 if ($cfgRelation['uiprefswork']) {
1917 return $this->saveUiprefsToDb();
1923 * Remove a property from UI preferences.
1925 * @param string $property the property
1927 * @return true|Message
1929 public function removeUiProp($property)
1931 if (! isset($this->uiprefs
)) {
1932 $this->loadUiPrefs();
1934 if (isset($this->uiprefs
[$property])) {
1935 unset($this->uiprefs
[$property]);
1937 // check if pmadb is set
1938 $cfgRelation = PMA_getRelationsParam();
1939 if ($cfgRelation['uiprefswork']) {
1940 return $this->saveUiprefsToDb();
1947 * Get all column names which are MySQL reserved words
1952 public function getReservedColumnNames()
1954 $columns = $this->getColumns(false);
1956 foreach ($columns as $column) {
1957 $temp = explode('.', $column);
1958 $column_name = $temp[2];
1959 if (Context
::isKeyword($column_name, true)) {
1960 $return[] = $column_name;
1967 * Function to get the name and type of the columns of a table
1971 public function getNameAndTypeOfTheColumns()
1974 foreach ($this->_dbi
->getColumnsFull(
1975 $this->_db_name
, $this->_name
1977 if (preg_match('@^(set|enum)\((.+)\)$@i', $row['Type'], $tmp)) {
1978 $tmp[2] = mb_substr(
1979 preg_replace('@([^,])\'\'@', '\\1\\\'', ',' . $tmp[2]), 1
1981 $columns[$row['Field']] = $tmp[1] . '('
1982 . str_replace(',', ', ', $tmp[2]) . ')';
1984 $columns[$row['Field']] = $row['Type'];
1991 * Get index with index name
1993 * @param string $index Index name
1997 public function getIndex($index)
1999 return Index
::singleton($this->_db_name
, $this->_name
, $index);
2003 * Function to get the sql query for index creation or edit
2005 * @param Index $index current index
2006 * @param bool &$error whether error occurred or not
2010 public function getSqlQueryForIndexCreateOrEdit($index, &$error)
2012 // $sql_query is the one displayed in the query box
2013 $sql_query = sprintf(
2014 'ALTER TABLE %s.%s',
2015 Util
::backquote($this->_db_name
),
2016 Util
::backquote($this->_name
)
2019 // Drops the old index
2020 if (! empty($_REQUEST['old_index'])) {
2021 if ($_REQUEST['old_index'] == 'PRIMARY') {
2022 $sql_query .= ' DROP PRIMARY KEY,';
2024 $sql_query .= sprintf(
2026 Util
::backquote($_REQUEST['old_index'])
2031 // Builds the new one
2032 switch ($index->getChoice()) {
2034 if ($index->getName() == '') {
2035 $index->setName('PRIMARY');
2036 } elseif ($index->getName() != 'PRIMARY') {
2037 $error = Message
::error(
2038 __('The name of the primary key must be "PRIMARY"!')
2041 $sql_query .= ' ADD PRIMARY KEY';
2047 if ($index->getName() == 'PRIMARY') {
2048 $error = Message
::error(
2049 __('Can\'t rename index to PRIMARY!')
2052 $sql_query .= sprintf(
2056 if ($index->getName()) {
2057 $sql_query .= Util
::backquote($index->getName());
2062 $index_fields = array();
2063 foreach ($index->getColumns() as $key => $column) {
2064 $index_fields[$key] = Util
::backquote($column->getName());
2065 if ($column->getSubPart()) {
2066 $index_fields[$key] .= '(' . $column->getSubPart() . ')';
2070 if (empty($index_fields)) {
2071 $error = Message
::error(__('No index parts defined!'));
2073 $sql_query .= ' (' . implode(', ', $index_fields) . ')';
2076 $keyBlockSizes = $index->getKeyBlockSize();
2077 if (! empty($keyBlockSizes)) {
2078 $sql_query .= sprintf(
2079 ' KEY_BLOCK_SIZE = ',
2080 $GLOBALS['dbi']->escapeString($keyBlockSizes)
2084 // specifying index type is allowed only for primary, unique and index only
2085 // TokuDB is using Fractal Tree, Using Type is not useless
2086 // Ref: https://mariadb.com/kb/en/mariadb/storage-engine-index-types/
2087 $type = $index->getType();
2088 if ($index->getChoice() != 'SPATIAL'
2089 && $index->getChoice() != 'FULLTEXT'
2090 && in_array($type, Index
::getIndexTypes())
2091 && ! $this->isEngine(array('TOKUDB'))
2093 $sql_query .= ' USING ' . $type;
2096 $parser = $index->getParser();
2097 if ($index->getChoice() == 'FULLTEXT' && ! empty($parser)) {
2098 $sql_query .= ' WITH PARSER ' . $GLOBALS['dbi']->escapeString($parser);
2101 $comment = $index->getComment();
2102 if (! empty($comment)) {
2103 $sql_query .= sprintf(
2105 $GLOBALS['dbi']->escapeString($comment)
2115 * Function to handle update for display field
2117 * @param string $disp current display field
2118 * @param string $display_field display field
2119 * @param array $cfgRelation configuration relation
2121 * @return boolean True on update succeed or False on failure
2123 public function updateDisplayField($disp, $display_field, $cfgRelation)
2127 if ($display_field == '') {
2128 $upd_query = 'DELETE FROM '
2129 . Util
::backquote($GLOBALS['cfgRelation']['db'])
2130 . '.' . Util
::backquote($cfgRelation['table_info'])
2131 . ' WHERE db_name = \''
2132 . $GLOBALS['dbi']->escapeString($this->_db_name
) . '\''
2133 . ' AND table_name = \''
2134 . $GLOBALS['dbi']->escapeString($this->_name
) . '\'';
2135 } elseif ($disp != $display_field) {
2136 $upd_query = 'UPDATE '
2137 . Util
::backquote($GLOBALS['cfgRelation']['db'])
2138 . '.' . Util
::backquote($cfgRelation['table_info'])
2139 . ' SET display_field = \''
2140 . $GLOBALS['dbi']->escapeString($display_field) . '\''
2141 . ' WHERE db_name = \''
2142 . $GLOBALS['dbi']->escapeString($this->_db_name
) . '\''
2143 . ' AND table_name = \''
2144 . $GLOBALS['dbi']->escapeString($this->_name
) . '\'';
2146 } elseif ($display_field != '') {
2147 $upd_query = 'INSERT INTO '
2148 . Util
::backquote($GLOBALS['cfgRelation']['db'])
2149 . '.' . Util
::backquote($cfgRelation['table_info'])
2150 . '(db_name, table_name, display_field) VALUES('
2151 . '\'' . $GLOBALS['dbi']->escapeString($this->_db_name
) . '\','
2152 . '\'' . $GLOBALS['dbi']->escapeString($this->_name
) . '\','
2153 . '\'' . $GLOBALS['dbi']->escapeString($display_field) . '\')';
2159 $GLOBALS['controllink'],
2169 * Function to get update query for updating internal relations
2171 * @param array $multi_edit_columns_name multi edit column names
2172 * @param array $destination_db destination tables
2173 * @param array $destination_table destination tables
2174 * @param array $destination_column destination columns
2175 * @param array $cfgRelation configuration relation
2176 * @param array|null $existrel db, table, column
2180 public function updateInternalRelations($multi_edit_columns_name,
2181 $destination_db, $destination_table, $destination_column,
2182 $cfgRelation, $existrel
2185 foreach ($destination_db as $master_field_md5 => $foreign_db) {
2187 // Map the fieldname's md5 back to its real name
2188 $master_field = $multi_edit_columns_name[$master_field_md5];
2189 $foreign_table = $destination_table[$master_field_md5];
2190 $foreign_field = $destination_column[$master_field_md5];
2191 if (! empty($foreign_db)
2192 && ! empty($foreign_table)
2193 && ! empty($foreign_field)
2195 if (! isset($existrel[$master_field])) {
2196 $upd_query = 'INSERT INTO '
2197 . Util
::backquote($GLOBALS['cfgRelation']['db'])
2198 . '.' . Util
::backquote($cfgRelation['relation'])
2199 . '(master_db, master_table, master_field, foreign_db,'
2200 . ' foreign_table, foreign_field)'
2202 . '\'' . $GLOBALS['dbi']->escapeString($this->_db_name
) . '\', '
2203 . '\'' . $GLOBALS['dbi']->escapeString($this->_name
) . '\', '
2204 . '\'' . $GLOBALS['dbi']->escapeString($master_field) . '\', '
2205 . '\'' . $GLOBALS['dbi']->escapeString($foreign_db) . '\', '
2206 . '\'' . $GLOBALS['dbi']->escapeString($foreign_table) . '\','
2207 . '\'' . $GLOBALS['dbi']->escapeString($foreign_field) . '\')';
2209 } elseif ($existrel[$master_field]['foreign_db'] != $foreign_db
2210 ||
$existrel[$master_field]['foreign_table'] != $foreign_table
2211 ||
$existrel[$master_field]['foreign_field'] != $foreign_field
2213 $upd_query = 'UPDATE '
2214 . Util
::backquote($GLOBALS['cfgRelation']['db'])
2215 . '.' . Util
::backquote($cfgRelation['relation'])
2216 . ' SET foreign_db = \''
2217 . $GLOBALS['dbi']->escapeString($foreign_db) . '\', '
2218 . ' foreign_table = \''
2219 . $GLOBALS['dbi']->escapeString($foreign_table) . '\', '
2220 . ' foreign_field = \''
2221 . $GLOBALS['dbi']->escapeString($foreign_field) . '\' '
2222 . ' WHERE master_db = \''
2223 . $GLOBALS['dbi']->escapeString($this->_db_name
) . '\''
2224 . ' AND master_table = \''
2225 . $GLOBALS['dbi']->escapeString($this->_name
) . '\''
2226 . ' AND master_field = \''
2227 . $GLOBALS['dbi']->escapeString($master_field) . '\'';
2228 } // end if... else....
2229 } elseif (isset($existrel[$master_field])) {
2230 $upd_query = 'DELETE FROM '
2231 . Util
::backquote($GLOBALS['cfgRelation']['db'])
2232 . '.' . Util
::backquote($cfgRelation['relation'])
2233 . ' WHERE master_db = \''
2234 . $GLOBALS['dbi']->escapeString($this->_db_name
) . '\''
2235 . ' AND master_table = \''
2236 . $GLOBALS['dbi']->escapeString($this->_name
) . '\''
2237 . ' AND master_field = \''
2238 . $GLOBALS['dbi']->escapeString($master_field) . '\'';
2239 } // end if... else....
2241 if (isset($upd_query)) {
2244 $GLOBALS['controllink'],
2255 * Function to handle foreign key updates
2257 * @param array $destination_foreign_db destination foreign database
2258 * @param array $multi_edit_columns_name multi edit column names
2259 * @param array $destination_foreign_table destination foreign table
2260 * @param array $destination_foreign_column destination foreign column
2261 * @param array $options_array options array
2262 * @param string $table current table
2263 * @param array $existrel_foreign db, table, column
2267 public function updateForeignKeys($destination_foreign_db,
2268 $multi_edit_columns_name, $destination_foreign_table,
2269 $destination_foreign_column, $options_array, $table, $existrel_foreign
2272 $preview_sql_data = '';
2273 $display_query = '';
2274 $seen_error = false;
2276 foreach ($destination_foreign_db as $master_field_md5 => $foreign_db) {
2280 // Map the fieldname's md5 back to its real name
2281 $master_field = $multi_edit_columns_name[$master_field_md5];
2283 $foreign_table = $destination_foreign_table[$master_field_md5];
2284 $foreign_field = $destination_foreign_column[$master_field_md5];
2286 if (isset($existrel_foreign[$master_field_md5]['ref_db_name'])) {
2287 $ref_db_name = $existrel_foreign[$master_field_md5]['ref_db_name'];
2289 $ref_db_name = $GLOBALS['db'];
2292 $empty_fields = false;
2293 foreach ($master_field as $key => $one_field) {
2294 if ((! empty($one_field) && empty($foreign_field[$key]))
2295 ||
(empty($one_field) && ! empty($foreign_field[$key]))
2297 $empty_fields = true;
2300 if (empty($one_field) && empty($foreign_field[$key])) {
2301 unset($master_field[$key]);
2302 unset($foreign_field[$key]);
2306 if (! empty($foreign_db)
2307 && ! empty($foreign_table)
2310 if (isset($existrel_foreign[$master_field_md5])) {
2312 = $existrel_foreign[$master_field_md5]['constraint'];
2313 $on_delete = !empty(
2314 $existrel_foreign[$master_field_md5]['on_delete']
2316 ?
$existrel_foreign[$master_field_md5]['on_delete']
2318 $on_update = ! empty(
2319 $existrel_foreign[$master_field_md5]['on_update']
2321 ?
$existrel_foreign[$master_field_md5]['on_update']
2324 if ($ref_db_name != $foreign_db
2325 ||
$existrel_foreign[$master_field_md5]['ref_table_name'] != $foreign_table
2326 ||
$existrel_foreign[$master_field_md5]['ref_index_list'] != $foreign_field
2327 ||
$existrel_foreign[$master_field_md5]['index_list'] != $master_field
2328 ||
$_REQUEST['constraint_name'][$master_field_md5] != $constraint_name
2329 ||
($_REQUEST['on_delete'][$master_field_md5] != $on_delete)
2330 ||
($_REQUEST['on_update'][$master_field_md5] != $on_update)
2332 // another foreign key is already defined for this field
2333 // or an option has been changed for ON DELETE or ON UPDATE
2336 } // end if... else....
2338 // no key defined for this field(s)
2341 } elseif (isset($existrel_foreign[$master_field_md5])) {
2343 } // end if... else....
2345 $tmp_error_drop = false;
2347 $drop_query = 'ALTER TABLE ' . Util
::backquote($table)
2348 . ' DROP FOREIGN KEY '
2350 $existrel_foreign[$master_field_md5]['constraint']
2354 if (! isset($_REQUEST['preview_sql'])) {
2355 $display_query .= $drop_query . "\n";
2356 $this->_dbi
->tryQuery($drop_query);
2357 $tmp_error_drop = $this->_dbi
->getError();
2359 if (! empty($tmp_error_drop)) {
2361 $html_output .= Util
::mysqlDie(
2362 $tmp_error_drop, $drop_query, false, '', false
2367 $preview_sql_data .= $drop_query . "\n";
2370 $tmp_error_create = false;
2375 $create_query = $this->_getSQLToCreateForeignKey(
2376 $table, $master_field, $foreign_db, $foreign_table, $foreign_field,
2377 $_REQUEST['constraint_name'][$master_field_md5],
2378 $options_array[$_REQUEST['on_delete'][$master_field_md5]],
2379 $options_array[$_REQUEST['on_update'][$master_field_md5]]
2382 if (! isset($_REQUEST['preview_sql'])) {
2383 $display_query .= $create_query . "\n";
2384 $this->_dbi
->tryQuery($create_query);
2385 $tmp_error_create = $this->_dbi
->getError();
2386 if (! empty($tmp_error_create)) {
2389 if (substr($tmp_error_create, 1, 4) == '1005') {
2390 $message = Message
::error(
2392 'Error creating foreign key on %1$s (check data ' .
2396 $message->addParam(implode(', ', $master_field));
2397 $html_output .= $message->getDisplay();
2399 $html_output .= Util
::mysqlDie(
2400 $tmp_error_create, $create_query, false, '', false
2403 $html_output .= Util
::showMySQLDocu(
2404 'InnoDB_foreign_key_constraints'
2408 $preview_sql_data .= $create_query . "\n";
2411 // this is an alteration and the old constraint has been dropped
2412 // without creation of a new one
2413 if ($drop && $create && empty($tmp_error_drop)
2414 && ! empty($tmp_error_create)
2416 // a rollback may be better here
2417 $sql_query_recreate = '# Restoring the dropped constraint...' . "\n";
2418 $sql_query_recreate .= $this->_getSQLToCreateForeignKey(
2421 $existrel_foreign[$master_field_md5]['ref_db_name'],
2422 $existrel_foreign[$master_field_md5]['ref_table_name'],
2423 $existrel_foreign[$master_field_md5]['ref_index_list'],
2424 $existrel_foreign[$master_field_md5]['constraint'],
2425 $options_array[$existrel_foreign[$master_field_md5]['on_delete']],
2426 $options_array[$existrel_foreign[$master_field_md5]['on_update']]
2428 if (! isset($_REQUEST['preview_sql'])) {
2429 $display_query .= $sql_query_recreate . "\n";
2430 $this->_dbi
->tryQuery($sql_query_recreate);
2432 $preview_sql_data .= $sql_query_recreate;
2446 * Returns the SQL query for foreign key constraint creation
2448 * @param string $table table name
2449 * @param array $field field names
2450 * @param string $foreignDb foreign database name
2451 * @param string $foreignTable foreign table name
2452 * @param array $foreignField foreign field names
2453 * @param string $name name of the constraint
2454 * @param string $onDelete on delete action
2455 * @param string $onUpdate on update action
2457 * @return string SQL query for foreign key constraint creation
2459 private function _getSQLToCreateForeignKey(
2469 $sql_query = 'ALTER TABLE ' . Util
::backquote($table) . ' ADD ';
2470 // if user entered a constraint name
2471 if (! empty($name)) {
2472 $sql_query .= ' CONSTRAINT ' . Util
::backquote($name);
2475 foreach ($field as $key => $one_field) {
2476 $field[$key] = Util
::backquote($one_field);
2478 foreach ($foreignField as $key => $one_field) {
2479 $foreignField[$key] = Util
::backquote($one_field);
2481 $sql_query .= ' FOREIGN KEY (' . implode(', ', $field) . ') REFERENCES '
2482 . ($this->_db_name
!= $foreignDb
2483 ? Util
::backquote($foreignDb) . '.' : '')
2484 . Util
::backquote($foreignTable)
2485 . '(' . implode(', ', $foreignField) . ')';
2487 if (! empty($onDelete)) {
2488 $sql_query .= ' ON DELETE ' . $onDelete;
2490 if (! empty($onUpdate)) {
2491 $sql_query .= ' ON UPDATE ' . $onUpdate;
2499 * Returns the generation expression for virtual columns
2501 * @param string $column name of the column
2503 * @return array|boolean associative array of column name and their expressions
2504 * or false on failure
2506 public function getColumnGenerationExpression($column = null)
2508 $serverType = Util
::getServerType();
2509 if ($serverType == 'MySQL'
2510 && PMA_MYSQL_INT_VERSION
> 50705
2511 && ! $GLOBALS['cfg']['Server']['DisableIS']
2515 `COLUMN_NAME` AS `Field`,
2516 `GENERATION_EXPRESSION` AS `Expression`
2518 `information_schema`.`COLUMNS`
2520 `TABLE_SCHEMA` = '" . $GLOBALS['dbi']->escapeString($this->_db_name
) . "'
2521 AND `TABLE_NAME` = '" . $GLOBALS['dbi']->escapeString($this->_name
) . "'";
2522 if ($column != null) {
2523 $sql .= " AND `COLUMN_NAME` = '" . $GLOBALS['dbi']->escapeString($column)
2526 $columns = $this->_dbi
->fetchResult($sql, 'Field', 'Expression');
2530 $createTable = $this->showCreate();
2531 if (!$createTable) {
2535 $parser = new Parser($createTable);
2537 * @var \PhpMyAdmin\SqlParser\Statements\CreateStatement $stmt
2539 $stmt = $parser->statements
[0];
2540 $fields = \PhpMyAdmin\SqlParser\Utils\Table
::getFields($stmt);
2541 if ($column != null) {
2542 $expression = isset($fields[$column]['expr']) ?
2543 substr($fields[$column]['expr'], 1, -1) : '';
2544 return array($column => $expression);
2548 foreach ($fields as $field => $options) {
2549 if (isset($options['expr'])) {
2550 $ret[$field] = substr($options['expr'], 1, -1);
2557 * Returns the CREATE statement for this table
2561 public function showCreate()
2563 return $this->_dbi
->fetchValue(
2564 'SHOW CREATE TABLE ' . Util
::backquote($this->_db_name
) . '.'
2565 . Util
::backquote($this->_name
),
2571 * Returns the real row count for a table
2575 public function getRealRowCountTable()
2577 // SQL query to get row count for a table.
2578 $result = $this->_dbi
->fetchSingleRow(
2580 'SELECT COUNT(*) AS %s FROM %s.%s',
2581 Util
::backquote('row_count'),
2582 Util
::backquote($this->_db_name
),
2583 Util
::backquote($this->_name
)
2586 return $result['row_count'];
2590 * Get columns with indexes
2592 * @param int $types types bitmask
2594 * @return array an array of columns
2596 public function getColumnsWithIndex($types)
2598 $columns_with_index = array();
2600 Index
::getFromTableByChoice(
2606 $columns = $index->getColumns();
2607 foreach ($columns as $column_name => $dummy) {
2608 $columns_with_index[] = $column_name;
2611 return $columns_with_index;