libraries/Table.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Holds the Table class
   5  *
   6  * @package PhpMyAdmin
   7  */
   8 namespace PMA\libraries;
   9
  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;
  16
  17 /**
  18  * Handles everything related to tables
  19  *
  20  * @todo make use of Message and Error
  21  * @package PhpMyAdmin
  22  */
  23 class Table
  24 {
  25     /**
  26      * UI preferences properties
  27      */
  28     const PROP_SORTED_COLUMN = 'sorted_col';
  29     const PROP_COLUMN_ORDER = 'col_order';
  30     const PROP_COLUMN_VISIB = 'col_visib';
  31
  32     /**
  33      * @var string  engine (innodb, myisam, bdb, ...)
  34      */
  35     var $engine = '';
  36
  37     /**
  38      * @var string  type (view, base table, system view)
  39      */
  40     var $type = '';
  41
  42     /**
  43      * @var array UI preferences
  44      */
  45     var $uiprefs;
  46
  47     /**
  48      * @var array errors occurred
  49      */
  50     var $errors = array();
  51
  52     /**
  53      * @var array messages
  54      */
  55     var $messages = array();
  56
  57     /**
  58      * @var string  table name
  59      */
  60     protected $_name = '';
  61
  62     /**
  63      * @var string  database name
  64      */
  65     protected $_db_name = '';
  66
  67     /**
  68      * @var DatabaseInterface
  69      */
  70     protected $_dbi;
  71
  72     /**
  73      * Constructor
  74      *
  75      * @param string            $table_name table name
  76      * @param string            $db_name    database name
  77      * @param DatabaseInterface $dbi        database interface for the table
  78      */
  79     public function __construct($table_name, $db_name, DatabaseInterface $dbi = null)
  80     {
  81         if (empty($dbi)) {
  82             $dbi = $GLOBALS['dbi'];
  83         }
  84         $this->_dbi = $dbi;
  85         $this->_name = $table_name;
  86         $this->_db_name = $db_name;
  87     }
  88
  89     /**
  90      * returns table name
  91      *
  92      * @see Table::getName()
  93      * @return string  table name
  94      */
  95     public function __toString()
  96     {
  97         return $this->getName();
  98     }
  99
 100     /**
 101      * return the last error
 102      *
 103      * @return string the last error
 104      */
 105     public function getLastError()
 106     {
 107         return end($this->errors);
 108     }
 109
 110     /**
 111      * return the last message
 112      *
 113      * @return string the last message
 114      */
 115     public function getLastMessage()
 116     {
 117         return end($this->messages);
 118     }
 119
 120     /**
 121      * returns table name
 122      *
 123      * @param boolean $backquoted whether to quote name with backticks ``
 124      *
 125      * @return string  table name
 126      */
 127     public function getName($backquoted = false)
 128     {
 129         if ($backquoted) {
 130             return Util::backquote($this->_name);
 131         }
 132         return $this->_name;
 133     }
 134
 135     /**
 136      * returns database name for this table
 137      *
 138      * @param boolean $backquoted whether to quote name with backticks ``
 139      *
 140      * @return string  database name for this table
 141      */
 142     public function getDbName($backquoted = false)
 143     {
 144         if ($backquoted) {
 145             return Util::backquote($this->_db_name);
 146         }
 147         return $this->_db_name;
 148     }
 149
 150     /**
 151      * returns full name for table, including database name
 152      *
 153      * @param boolean $backquoted whether to quote name with backticks ``
 154      *
 155      * @return string
 156      */
 157     public function getFullName($backquoted = false)
 158     {
 159         return $this->getDbName($backquoted) . '.'
 160         . $this->getName($backquoted);
 161     }
 162
 163
 164     /**
 165      * Checks the storage engine used to create table
 166      *
 167      * @param array or string $engine Checks the table engine against an
 168      * array of engine strings or a single string, should be uppercase
 169      *
 170      * @return bool True, if $engine matches the storage engine for the table,
 171      * False otherwise.
 172      */
 173     public function isEngine($engine)
 174     {
 175         $tbl_storage_engine = strtoupper(
 176             $this->getStatusInfo('ENGINE', null, true)
 177         );
 178
 179         if (is_array($engine)){
 180             foreach($engine as $e){
 181                 if($e == $tbl_storage_engine){
 182                     return true;
 183                 }
 184             }
 185             return false;
 186         }else{
 187             return $tbl_storage_engine == $engine;
 188         }
 189     }
 190
 191     /**
 192      * returns whether the table is actually a view
 193      *
 194      * @return boolean whether the given is a view
 195      */
 196     public function isView()
 197     {
 198         $db = $this->_db_name;
 199         $table = $this->_name;
 200         if (empty($db) || empty($table)) {
 201             return false;
 202         }
 203
 204         // use cached data or load information with SHOW command
 205         if ($this->_dbi->getCachedTableContent(array($db, $table)) != null
 206             || $GLOBALS['cfg']['Server']['DisableIS']
 207         ) {
 208             $type = $this->getStatusInfo('TABLE_TYPE');
 209             return $type == 'VIEW' || $type == 'SYSTEM VIEW';
 210         }
 211
 212         // information_schema tables are 'SYSTEM VIEW's
 213         if ($db == 'information_schema') {
 214             return true;
 215         }
 216
 217         // query information_schema
 218         $result = $this->_dbi->fetchResult(
 219             "SELECT TABLE_NAME
 220             FROM information_schema.VIEWS
 221             WHERE TABLE_SCHEMA = '" . $GLOBALS['dbi']->escapeString($db) . "'
 222                 AND TABLE_NAME = '" . $GLOBALS['dbi']->escapeString($table) . "'"
 223         );
 224         return $result ? true : false;
 225     }
 226
 227     /**
 228      * Returns whether the table is actually an updatable view
 229      *
 230      * @return boolean whether the given is an updatable view
 231      */
 232     public function isUpdatableView()
 233     {
 234         if (empty($this->_db_name) || empty($this->_name)) {
 235             return false;
 236         }
 237
 238         $result = $this->_dbi->fetchResult(
 239             "SELECT TABLE_NAME
 240             FROM information_schema.VIEWS
 241             WHERE TABLE_SCHEMA = '" . $GLOBALS['dbi']->escapeString($this->_db_name) . "'
 242                 AND TABLE_NAME = '" . $GLOBALS['dbi']->escapeString($this->_name) . "'
 243                 AND IS_UPDATABLE = 'YES'"
 244         );
 245         return $result ? true : false;
 246     }
 247
 248     /**
 249      * Checks if this is a merge table
 250      *
 251      * If the ENGINE of the table is MERGE or MRG_MYISAM (alias),
 252      * this is a merge table.
 253      *
 254      * @return boolean  true if it is a merge table
 255      */
 256     public function isMerge()
 257     {
 258         return $this->isEngine(array('MERGE', 'MRG_MYISAM'));
 259     }
 260
 261     /**
 262      * Returns full table status info, or specific if $info provided
 263      * this info is collected from information_schema
 264      *
 265      * @param string  $info          specific information to be fetched
 266      * @param boolean $force_read    read new rather than serving from cache
 267      * @param boolean $disable_error if true, disables error message
 268      *
 269      * @todo DatabaseInterface::getTablesFull needs to be merged
 270      * somehow into this class or at least better documented
 271      *
 272      * @return mixed
 273      */
 274     public function getStatusInfo(
 275         $info = null,
 276         $force_read = false,
 277         $disable_error = false
 278     ) {
 279         $db = $this->_db_name;
 280         $table = $this->_name;
 281
 282         if (! empty($_SESSION['is_multi_query'])) {
 283             $disable_error = true;
 284         }
 285
 286         // sometimes there is only one entry (ExactRows) so
 287         // we have to get the table's details
 288         if ($this->_dbi->getCachedTableContent(array($db, $table)) == null
 289             || $force_read
 290             || count($this->_dbi->getCachedTableContent(array($db, $table))) == 1
 291         ) {
 292             $this->_dbi->getTablesFull($db, $table);
 293         }
 294
 295         if ($this->_dbi->getCachedTableContent(array($db, $table)) == null) {
 296             // happens when we enter the table creation dialog
 297             // or when we really did not get any status info, for example
 298             // when $table == 'TABLE_NAMES' after the user tried SHOW TABLES
 299             return '';
 300         }
 301
 302         if (null === $info) {
 303             return $this->_dbi->getCachedTableContent(array($db, $table));
 304         }
 305
 306         // array_key_exists allows for null values
 307         if (!array_key_exists(
 308             $info, $this->_dbi->getCachedTableContent(array($db, $table))
 309         )
 310         ) {
 311             if (! $disable_error) {
 312                 trigger_error(
 313                     __('Unknown table status:') . ' ' . $info,
 314                     E_USER_WARNING
 315                 );
 316             }
 317             return false;
 318         }
 319
 320         return $this->_dbi->getCachedTableContent(array($db, $table, $info));
 321     }
 322
 323     /**
 324      * generates column specification for ALTER or CREATE TABLE syntax
 325      *
 326      * @param string      $name          name
 327      * @param string      $type          type ('INT', 'VARCHAR', 'BIT', ...)
 328      * @param string      $length        length ('2', '5,2', '', ...)
 329      * @param string      $attribute     attribute
 330      * @param string      $collation     collation
 331      * @param bool|string $null          with 'NULL' or 'NOT NULL'
 332      * @param string      $default_type  whether default is CURRENT_TIMESTAMP,
 333      *                                   NULL, NONE, USER_DEFINED
 334      * @param string      $default_value default value for USER_DEFINED
 335      *                                   default type
 336      * @param string      $extra         'AUTO_INCREMENT'
 337      * @param string      $comment       field comment
 338      * @param string      $virtuality    virtuality of the column
 339      * @param string      $expression    expression for the virtual column
 340      * @param string      $move_to       new position for column
 341      *
 342      * @todo    move into class PMA_Column
 343      * @todo on the interface, some js to clear the default value when the
 344      * default current_timestamp is checked
 345      *
 346      * @return string  field specification
 347      */
 348     static function generateFieldSpec($name, $type, $length = '',
 349         $attribute = '', $collation = '', $null = false,
 350         $default_type = 'USER_DEFINED', $default_value = '',  $extra = '',
 351         $comment = '', $virtuality = '', $expression = '', $move_to = ''
 352     ) {
 353         $is_timestamp = mb_strpos(
 354             mb_strtoupper($type),
 355             'TIMESTAMP'
 356         ) !== false;
 357
 358         $query = Util::backquote($name) . ' ' . $type;
 359
 360         // allow the possibility of a length for TIME, DATETIME and TIMESTAMP
 361         // (will work on MySQL >= 5.6.4)
 362         //
 363         // MySQL permits a non-standard syntax for FLOAT and DOUBLE,
 364         // see https://dev.mysql.com/doc/refman/5.5/en/floating-point-types.html
 365         //
 366         $pattern = '@^(DATE|TINYBLOB|TINYTEXT|BLOB|TEXT|'
 367             . 'MEDIUMBLOB|MEDIUMTEXT|LONGBLOB|LONGTEXT|SERIAL|BOOLEAN|UUID)$@i';
 368         if (strlen($length) !== 0 && ! preg_match($pattern, $type)) {
 369             // Note: The variable $length here can contain several other things
 370             // besides length - ENUM/SET value or length of DECIMAL (eg. 12,3)
 371             // so we can't just convert it to integer
 372             $query .= '(' . $length . ')';
 373         }
 374         if ($attribute != '') {
 375             $query .= ' ' . $attribute;
 376
 377             if ($is_timestamp
 378                 && preg_match('/TIMESTAMP/i', $attribute)
 379                 && strlen($length) !== 0
 380                 && $length !== 0
 381             ) {
 382                 $query .= '(' . $length . ')';
 383             }
 384         }
 385
 386         if ($virtuality) {
 387             $query .= ' AS (' . $expression . ') ' . $virtuality;
 388         } else {
 389
 390             $matches = preg_match(
 391                 '@^(TINYTEXT|TEXT|MEDIUMTEXT|LONGTEXT|VARCHAR|CHAR|ENUM|SET)$@i',
 392                 $type
 393             );
 394             if (! empty($collation) && $collation != 'NULL' && $matches) {
 395                 $query .= Util::getCharsetQueryPart($collation, true);
 396             }
 397
 398             if ($null !== false) {
 399                 if ($null == 'NULL') {
 400                     $query .= ' NULL';
 401                 } else {
 402                     $query .= ' NOT NULL';
 403                 }
 404             }
 405
 406             switch ($default_type) {
 407             case 'USER_DEFINED' :
 408                 if ($is_timestamp && $default_value === '0') {
 409                     // a TIMESTAMP does not accept DEFAULT '0'
 410                     // but DEFAULT 0 works
 411                     $query .= ' DEFAULT 0';
 412                 } elseif ($type == 'BIT') {
 413                     $query .= ' DEFAULT b\''
 414                         . preg_replace('/[^01]/', '0', $default_value)
 415                         . '\'';
 416                 } elseif ($type == 'BOOLEAN') {
 417                     if (preg_match('/^1|T|TRUE|YES$/i', $default_value)) {
 418                         $query .= ' DEFAULT TRUE';
 419                     } elseif (preg_match('/^0|F|FALSE|NO$/i', $default_value)) {
 420                         $query .= ' DEFAULT FALSE';
 421                     } else {
 422                         // Invalid BOOLEAN value
 423                         $query .= ' DEFAULT \''
 424                             . $GLOBALS['dbi']->escapeString($default_value) . '\'';
 425                     }
 426                 } elseif ($type == 'BINARY' || $type == 'VARBINARY') {
 427                     $query .= ' DEFAULT 0x' . $default_value;
 428                 } else {
 429                     $query .= ' DEFAULT \''
 430                         . $GLOBALS['dbi']->escapeString($default_value) . '\'';
 431                 }
 432                 break;
 433             /** @noinspection PhpMissingBreakStatementInspection */
 434             case 'NULL' :
 435                 // If user uncheck null checkbox and not change default value null,
 436                 // default value will be ignored.
 437                 if ($null !== false && $null !== 'NULL') {
 438                     break;
 439                 }
 440                 // else fall-through intended, no break here
 441             case 'CURRENT_TIMESTAMP' :
 442                 $query .= ' DEFAULT ' . $default_type;
 443
 444                 if (strlen($length) !== 0
 445                     && $length !== 0
 446                     && $is_timestamp
 447                     && $default_type !== 'NULL' // Not to be added in case of NULL
 448                 ) {
 449                     $query .= '(' . $length . ')';
 450                 }
 451                 break;
 452             case 'NONE' :
 453             default :
 454                 break;
 455             }
 456
 457             if (!empty($extra)) {
 458                 $query .= ' ' . $extra;
 459             }
 460         }
 461         if (!empty($comment)) {
 462             $query .= " COMMENT '" . $GLOBALS['dbi']->escapeString($comment) . "'";
 463         }
 464
 465         // move column
 466         if ($move_to == '-first') { // dash can't appear as part of column name
 467             $query .= ' FIRST';
 468         } elseif ($move_to != '') {
 469             $query .= ' AFTER ' . Util::backquote($move_to);
 470         }
 471         return $query;
 472     } // end function
 473
 474     /**
 475      * Checks if the number of records in a table is at least equal to
 476      * $min_records
 477      *
 478      * @param int $min_records Number of records to check for in a table
 479      *
 480      * @return bool True, if at least $min_records exist, False otherwise.
 481      */
 482     public function checkIfMinRecordsExist($min_records = 0)
 483     {
 484         $check_query = 'SELECT ';
 485         $fieldsToSelect = '';
 486
 487         $uniqueFields = $this->getUniqueColumns(true, false);
 488         if (count($uniqueFields) > 0) {
 489             $fieldsToSelect = implode(', ', $uniqueFields);
 490         } else {
 491             $indexedCols = $this->getIndexedColumns(true, false);
 492             if (count($indexedCols) > 0) {
 493                 $fieldsToSelect = implode(', ', $indexedCols);
 494             } else {
 495                 $fieldsToSelect = '*';
 496             }
 497         }
 498
 499         $check_query .= $fieldsToSelect
 500             . ' FROM ' . $this->getFullName(true)
 501             . ' LIMIT ' . $min_records;
 502
 503         $res = $GLOBALS['dbi']->tryQuery(
 504             $check_query
 505         );
 506
 507         if ($res !== false) {
 508             $num_records = $GLOBALS['dbi']->numRows($res);
 509             if ($num_records >= $min_records) {
 510                 return true;
 511             }
 512         }
 513
 514         return false;
 515     }
 516
 517     /**
 518      * Counts and returns (or displays) the number of records in a table
 519      *
 520      * @param bool $force_exact whether to force an exact count
 521      *
 522      * @return mixed the number of records if "retain" param is true,
 523      *               otherwise true
 524      */
 525     public function countRecords($force_exact = false)
 526     {
 527         $is_view = $this->isView();
 528         $db = $this->_db_name;
 529         $table = $this->_name;
 530
 531         if ($this->_dbi->getCachedTableContent(array($db, $table, 'ExactRows')) != null) {
 532             $row_count = $this->_dbi->getCachedTableContent(
 533                 array($db, $table, 'ExactRows')
 534             );
 535             return $row_count;
 536         }
 537         $row_count = false;
 538
 539         if (! $force_exact) {
 540             if (($this->_dbi->getCachedTableContent(array($db, $table, 'Rows')) == null)
 541                 && !$is_view
 542             ) {
 543                 $tmp_tables = $this->_dbi->getTablesFull($db, $table);
 544                 if (isset($tmp_tables[$table])) {
 545                     $this->_dbi->cacheTableContent(
 546                         array($db, $table),
 547                         $tmp_tables[$table]
 548                     );
 549                 }
 550             }
 551             if ($this->_dbi->getCachedTableContent(array($db, $table, 'Rows')) != null) {
 552                 $row_count = $this->_dbi->getCachedTableContent(
 553                     array($db, $table, 'Rows')
 554                 );
 555             } else {
 556                 $row_count = false;
 557             }
 558         }
 559         // for a VIEW, $row_count is always false at this point
 560         if (false !== $row_count
 561             && $row_count >= $GLOBALS['cfg']['MaxExactCount']
 562         ) {
 563             return $row_count;
 564         }
 565
 566         if (! $is_view) {
 567             $row_count = $this->_dbi->fetchValue(
 568                 'SELECT COUNT(*) FROM ' . Util::backquote($db) . '.'
 569                 . Util::backquote($table)
 570             );
 571         } else {
 572             // For complex views, even trying to get a partial record
 573             // count could bring down a server, so we offer an
 574             // alternative: setting MaxExactCountViews to 0 will bypass
 575             // completely the record counting for views
 576
 577             if ($GLOBALS['cfg']['MaxExactCountViews'] == 0) {
 578                 $row_count = false;
 579             } else {
 580                 // Counting all rows of a VIEW could be too long,
 581                 // so use a LIMIT clause.
 582                 // Use try_query because it can fail (when a VIEW is
 583                 // based on a table that no longer exists)
 584                 $result = $this->_dbi->tryQuery(
 585                     'SELECT 1 FROM ' . Util::backquote($db) . '.'
 586                     . Util::backquote($table) . ' LIMIT '
 587                     . $GLOBALS['cfg']['MaxExactCountViews'],
 588                     null,
 589                     DatabaseInterface::QUERY_STORE
 590                 );
 591                 if (!$this->_dbi->getError()) {
 592                     $row_count = $this->_dbi->numRows($result);
 593                     $this->_dbi->freeResult($result);
 594                 }
 595             }
 596         }
 597         if ($row_count) {
 598             $this->_dbi->cacheTableContent(array($db, $table, 'ExactRows'), $row_count);
 599         }
 600
 601         return $row_count;
 602     } // end of the 'Table::countRecords()' function
 603
 604     /**
 605      * Generates column specification for ALTER syntax
 606      *
 607      * @param string      $oldcol        old column name
 608      * @param string      $newcol        new column name
 609      * @param string      $type          type ('INT', 'VARCHAR', 'BIT', ...)
 610      * @param string      $length        length ('2', '5,2', '', ...)
 611      * @param string      $attribute     attribute
 612      * @param string      $collation     collation
 613      * @param bool|string $null          with 'NULL' or 'NOT NULL'
 614      * @param string      $default_type  whether default is CURRENT_TIMESTAMP,
 615      *                                   NULL, NONE, USER_DEFINED
 616      * @param string      $default_value default value for USER_DEFINED default
 617      *                                   type
 618      * @param string      $extra         'AUTO_INCREMENT'
 619      * @param string      $comment       field comment
 620      * @param string      $virtuality    virtuality of the column
 621      * @param string      $expression    expression for the virtual column
 622      * @param string      $move_to       new position for column
 623      *
 624      * @see Table::generateFieldSpec()
 625      *
 626      * @return string  field specification
 627      */
 628     static public function generateAlter($oldcol, $newcol, $type, $length,
 629         $attribute, $collation, $null, $default_type, $default_value,
 630         $extra, $comment, $virtuality, $expression, $move_to
 631     ) {
 632         return Util::backquote($oldcol) . ' '
 633         . Table::generateFieldSpec(
 634             $newcol, $type, $length, $attribute,
 635             $collation, $null, $default_type, $default_value, $extra,
 636             $comment, $virtuality, $expression, $move_to
 637         );
 638     } // end function
 639
 640     /**
 641      * Inserts existing entries in a PMA_* table by reading a value from an old
 642      * entry
 643      *
 644      * @param string $work         The array index, which Relation feature to
 645      *                             check ('relwork', 'commwork', ...)
 646      * @param string $pma_table    The array index, which PMA-table to update
 647      *                             ('bookmark', 'relation', ...)
 648      * @param array  $get_fields   Which fields will be SELECT'ed from the old entry
 649      * @param array  $where_fields Which fields will be used for the WHERE query
 650      *                             (array('FIELDNAME' => 'FIELDVALUE'))
 651      * @param array  $new_fields   Which fields will be used as new VALUES.
 652      *                             These are the important keys which differ
 653      *                             from the old entry
 654      *                             (array('FIELDNAME' => 'NEW FIELDVALUE'))
 655      *
 656      * @global relation variable
 657      *
 658      * @return int|boolean
 659      */
 660     static public function duplicateInfo($work, $pma_table, $get_fields,
 661         $where_fields, $new_fields
 662     ) {
 663         $last_id = -1;
 664
 665         if (!isset($GLOBALS['cfgRelation']) || !$GLOBALS['cfgRelation'][$work]) {
 666             return true;
 667         }
 668
 669         $select_parts = array();
 670         $row_fields = array();
 671         foreach ($get_fields as $get_field) {
 672             $select_parts[] = Util::backquote($get_field);
 673             $row_fields[$get_field] = 'cc';
 674         }
 675
 676         $where_parts = array();
 677         foreach ($where_fields as $_where => $_value) {
 678             $where_parts[] = Util::backquote($_where) . ' = \''
 679                 . $GLOBALS['dbi']->escapeString($_value) . '\'';
 680         }
 681
 682         $new_parts = array();
 683         $new_value_parts = array();
 684         foreach ($new_fields as $_where => $_value) {
 685             $new_parts[] = Util::backquote($_where);
 686             $new_value_parts[] = $GLOBALS['dbi']->escapeString($_value);
 687         }
 688
 689         $table_copy_query = '
 690             SELECT ' . implode(', ', $select_parts) . '
 691               FROM ' . Util::backquote($GLOBALS['cfgRelation']['db']) . '.'
 692               . Util::backquote($GLOBALS['cfgRelation'][$pma_table]) . '
 693              WHERE ' . implode(' AND ', $where_parts);
 694
 695         // must use DatabaseInterface::QUERY_STORE here, since we execute
 696         // another query inside the loop
 697         $table_copy_rs = PMA_queryAsControlUser(
 698             $table_copy_query, true, DatabaseInterface::QUERY_STORE
 699         );
 700
 701         while ($table_copy_row = @$GLOBALS['dbi']->fetchAssoc($table_copy_rs)) {
 702             $value_parts = array();
 703             foreach ($table_copy_row as $_key => $_val) {
 704                 if (isset($row_fields[$_key]) && $row_fields[$_key] == 'cc') {
 705                     $value_parts[] = $GLOBALS['dbi']->escapeString($_val);
 706                 }
 707             }
 708
 709             $new_table_query = 'INSERT IGNORE INTO '
 710                 . Util::backquote($GLOBALS['cfgRelation']['db'])
 711                 . '.' . Util::backquote($GLOBALS['cfgRelation'][$pma_table])
 712                 . ' (' . implode(', ', $select_parts) . ', '
 713                 . implode(', ', $new_parts) . ') VALUES (\''
 714                 . implode('\', \'', $value_parts) . '\', \''
 715                 . implode('\', \'', $new_value_parts) . '\')';
 716
 717             PMA_queryAsControlUser($new_table_query);
 718             $last_id = $GLOBALS['dbi']->insertId();
 719         } // end while
 720
 721         $GLOBALS['dbi']->freeResult($table_copy_rs);
 722
 723         return $last_id;
 724     } // end of 'Table::duplicateInfo()' function
 725
 726     /**
 727      * Copies or renames table
 728      *
 729      * @param string $source_db    source database
 730      * @param string $source_table source table
 731      * @param string $target_db    target database
 732      * @param string $target_table target table
 733      * @param string $what         what to be moved or copied (data, dataonly)
 734      * @param bool   $move         whether to move
 735      * @param string $mode         mode
 736      *
 737      * @return bool true if success, false otherwise
 738      */
 739     static public function moveCopy($source_db, $source_table, $target_db,
 740         $target_table, $what, $move, $mode
 741     ) {
 742
 743         global $err_url;
 744
 745         // Try moving the tables directly, using native `RENAME` statement.
 746         if ($move && $what == 'data') {
 747             $tbl = new Table($source_table, $source_db);
 748             if ($tbl->rename($target_table, $target_db)) {
 749                 $GLOBALS['message'] = $tbl->getLastMessage();
 750                 return true;
 751             }
 752         }
 753
 754         // Setting required export settings.
 755         $GLOBALS['sql_backquotes'] = 1;
 756         $GLOBALS['asfile']         = 1;
 757
 758         // Ensuring the target database is valid.
 759         if (! $GLOBALS['dblist']->databases->exists($source_db, $target_db)) {
 760             if (! $GLOBALS['dblist']->databases->exists($source_db)) {
 761                 $GLOBALS['message'] = Message::rawError(
 762                     sprintf(
 763                         __('Source database `%s` was not found!'),
 764                         htmlspecialchars($source_db)
 765                     )
 766                 );
 767             }
 768             if (! $GLOBALS['dblist']->databases->exists($target_db)) {
 769                 $GLOBALS['message'] = Message::rawError(
 770                     sprintf(
 771                         __('Target database `%s` was not found!'),
 772                         htmlspecialchars($target_db)
 773                     )
 774                 );
 775             }
 776             return false;
 777         }
 778
 779         /**
 780          * The full name of source table, quoted.
 781          * @var string $source
 782          */
 783         $source = Util::backquote($source_db)
 784             . '.' . Util::backquote($source_table);
 785
 786         // If the target database is not specified, the operation is taking
 787         // place in the same database.
 788         if (! isset($target_db) || strlen($target_db) === 0) {
 789             $target_db = $source_db;
 790         }
 791
 792         // Selecting the database could avoid some problems with replicated
 793         // databases, when moving table from replicated one to not replicated one.
 794         $GLOBALS['dbi']->selectDb($target_db);
 795
 796         /**
 797          * The full name of target table, quoted.
 798          * @var string $target
 799          */
 800         $target = Util::backquote($target_db)
 801             . '.' . Util::backquote($target_table);
 802
 803         // No table is created when this is a data-only operation.
 804         if ($what != 'dataonly') {
 805
 806             include_once "libraries/plugin_interface.lib.php";
 807
 808             /**
 809              * Instance used for exporting the current structure of the table.
 810              *
 811              * @var \PMA\libraries\plugins\export\ExportSql
 812              */
 813             $export_sql_plugin = PMA_getPlugin(
 814                 "export",
 815                 "sql",
 816                 'libraries/plugins/export/',
 817                 array(
 818                     'export_type' => 'table',
 819                     'single_table' => false,
 820                 )
 821             );
 822
 823             $no_constraints_comments = true;
 824             $GLOBALS['sql_constraints_query'] = '';
 825             // set the value of global sql_auto_increment variable
 826             if (isset($_POST['sql_auto_increment'])) {
 827                 $GLOBALS['sql_auto_increment'] = $_POST['sql_auto_increment'];
 828             }
 829
 830             /**
 831              * The old structure of the table..
 832              * @var string $sql_structure
 833              */
 834             $sql_structure = $export_sql_plugin->getTableDef(
 835                 $source_db, $source_table, "\n", $err_url, false, false
 836             );
 837
 838             unset($no_constraints_comments);
 839
 840             // -----------------------------------------------------------------
 841             // Phase 0: Preparing structures used.
 842
 843             /**
 844              * The destination where the table is moved or copied to.
 845              * @var Expression
 846              */
 847             $destination = new Expression(
 848                 $target_db, $target_table, ''
 849             );
 850
 851             // Find server's SQL mode so the builder can generate correct
 852             // queries.
 853             // One of the options that alters the behaviour is `ANSI_QUOTES`.
 854             Context::setMode(
 855                 $GLOBALS['dbi']->fetchValue("SELECT @@sql_mode")
 856             );
 857
 858             // -----------------------------------------------------------------
 859             // Phase 1: Dropping existent element of the same name (if exists
 860             // and required).
 861
 862             if (isset($_REQUEST['drop_if_exists'])
 863                 && $_REQUEST['drop_if_exists'] == 'true'
 864             ) {
 865
 866                 /**
 867                  * Drop statement used for building the query.
 868                  * @var DropStatement $statement
 869                  */
 870                 $statement = new DropStatement();
 871
 872                 $tbl = new Table($target_db, $target_table);
 873
 874                 $statement->options = new OptionsArray(
 875                     array(
 876                         $tbl->isView() ? 'VIEW' : 'TABLE',
 877                         'IF EXISTS',
 878                     )
 879                 );
 880
 881                 $statement->fields = array($destination);
 882
 883                 // Building the query.
 884                 $drop_query = $statement->build() . ';';
 885
 886                 // Executing it.
 887                 $GLOBALS['dbi']->query($drop_query);
 888                 $GLOBALS['sql_query'] .= "\n" . $drop_query;
 889
 890                 // If an existing table gets deleted, maintain any entries for
 891                 // the PMA_* tables.
 892                 $maintain_relations = true;
 893             }
 894
 895             // -----------------------------------------------------------------
 896             // Phase 2: Generating the new query of this structure.
 897
 898             /**
 899              * The parser responsible for parsing the old queries.
 900              * @var Parser $parser
 901              */
 902             $parser = new Parser($sql_structure);
 903
 904             if (!empty($parser->statements[0])) {
 905
 906                 /**
 907                  * The CREATE statement of this structure.
 908                  * @var \PhpMyAdmin\SqlParser\Statements\CreateStatement $statement
 909                  */
 910                 $statement = $parser->statements[0];
 911
 912                 // Changing the destination.
 913                 $statement->name = $destination;
 914
 915                 // Building back the query.
 916                 $sql_structure = $statement->build() . ';';
 917
 918                 // Executing it.
 919                 $GLOBALS['dbi']->query($sql_structure);
 920                 $GLOBALS['sql_query'] .= "\n" . $sql_structure;
 921             }
 922
 923             // -----------------------------------------------------------------
 924             // Phase 3: Adding constraints.
 925             // All constraint names are removed because they must be unique.
 926
 927             if (($move || isset($GLOBALS['add_constraints']))
 928                 && !empty($GLOBALS['sql_constraints_query'])
 929             ) {
 930
 931                 $parser = new Parser($GLOBALS['sql_constraints_query']);
 932
 933                 /**
 934                  * The ALTER statement that generates the constraints.
 935                  * @var \PhpMyAdmin\SqlParser\Statements\AlterStatement $statement
 936                  */
 937                 $statement = $parser->statements[0];
 938
 939                 // Changing the altered table to the destination.
 940                 $statement->table = $destination;
 941
 942                 // Removing the name of the constraints.
 943                 foreach ($statement->altered as $idx => $altered) {
 944                     // All constraint names are removed because they must be unique.
 945                     if ($altered->options->has('CONSTRAINT')) {
 946                         $altered->field = null;
 947                     }
 948                 }
 949
 950                 // Building back the query.
 951                 $GLOBALS['sql_constraints_query'] = $statement->build() . ';';
 952
 953                 // Executing it.
 954                 if ($mode == 'one_table') {
 955                     $GLOBALS['dbi']->query($GLOBALS['sql_constraints_query']);
 956                 }
 957                 $GLOBALS['sql_query'] .= "\n" . $GLOBALS['sql_constraints_query'];
 958                 if ($mode == 'one_table') {
 959                     unset($GLOBALS['sql_constraints_query']);
 960                 }
 961             }
 962
 963             // -----------------------------------------------------------------
 964             // Phase 4: Adding indexes.
 965             // View phase 3.
 966
 967             if (!empty($GLOBALS['sql_indexes'])) {
 968
 969                 $parser = new Parser($GLOBALS['sql_indexes']);
 970
 971                 $GLOBALS['sql_indexes'] = '';
 972                 /**
 973                  * The ALTER statement that generates the indexes.
 974                  * @var \PhpMyAdmin\SqlParser\Statements\AlterStatement $statement
 975                  */
 976                 foreach ($parser->statements as $statement) {
 977
 978                     // Changing the altered table to the destination.
 979                     $statement->table = $destination;
 980
 981                     // Removing the name of the constraints.
 982                     foreach ($statement->altered as $idx => $altered) {
 983                         // All constraint names are removed because they must be unique.
 984                         if ($altered->options->has('CONSTRAINT')) {
 985                             $altered->field = null;
 986                         }
 987                     }
 988
 989                     // Building back the query.
 990                     $sql_index = $statement->build() . ';';
 991
 992                     // Executing it.
 993                     if ($mode == 'one_table' || $mode == 'db_copy') {
 994                         $GLOBALS['dbi']->query($sql_index);
 995                     }
 996
 997                     $GLOBALS['sql_indexes'] .= $sql_index;
 998                 }
 999
1000                 $GLOBALS['sql_query'] .= "\n" . $GLOBALS['sql_indexes'];
1001                 if ($mode == 'one_table' || $mode == 'db_copy') {
1002                     unset($GLOBALS['sql_indexes']);
1003                 }
1004             }
1005
1006             // -----------------------------------------------------------------
1007             // Phase 5: Adding AUTO_INCREMENT.
1008
1009             if (! empty($GLOBALS['sql_auto_increments'])) {
1010                 if ($mode == 'one_table' || $mode == 'db_copy') {
1011
1012                     $parser =  new Parser($GLOBALS['sql_auto_increments']);
1013
1014                     /**
1015                      * The ALTER statement that alters the AUTO_INCREMENT value.
1016                      * @var \PhpMyAdmin\SqlParser\Statements\AlterStatement $statement
1017                      */
1018                     $statement = $parser->statements[0];
1019
1020                     // Changing the altered table to the destination.
1021                     $statement->table = $destination;
1022
1023                     // Building back the query.
1024                     $GLOBALS['sql_auto_increments'] = $statement->build() . ';';
1025
1026                     // Executing it.
1027                     $GLOBALS['dbi']->query($GLOBALS['sql_auto_increments']);
1028                     $GLOBALS['sql_query'] .= "\n" . $GLOBALS['sql_auto_increments'];
1029                     unset($GLOBALS['sql_auto_increments']);
1030                 }
1031             }
1032         } else {
1033             $GLOBALS['sql_query'] = '';
1034         }
1035
1036         $_table = new Table($target_table, $target_db);
1037         // Copy the data unless this is a VIEW
1038         if (($what == 'data' || $what == 'dataonly')
1039             && ! $_table->isView()
1040         ) {
1041             $sql_set_mode = "SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO'";
1042             $GLOBALS['dbi']->query($sql_set_mode);
1043             $GLOBALS['sql_query'] .= "\n\n" . $sql_set_mode . ';';
1044
1045             $_old_table = new Table($source_table, $source_db);
1046             $nonGeneratedCols = $_old_table->getNonGeneratedColumns(true);
1047             if (count($nonGeneratedCols) > 0) {
1048                 $sql_insert_data = 'INSERT INTO ' . $target . '('
1049                     . implode(', ', $nonGeneratedCols)
1050                     . ') SELECT ' . implode(', ', $nonGeneratedCols)
1051                     . ' FROM ' . $source;
1052
1053                 $GLOBALS['dbi']->query($sql_insert_data);
1054                 $GLOBALS['sql_query'] .= "\n\n" . $sql_insert_data . ';';
1055             }
1056         }
1057
1058         PMA_getRelationsParam();
1059
1060         // Drops old table if the user has requested to move it
1061         if ($move) {
1062
1063             // This could avoid some problems with replicated databases, when
1064             // moving table from replicated one to not replicated one
1065             $GLOBALS['dbi']->selectDb($source_db);
1066
1067             $_source_table = new Table($source_table, $source_db);
1068             if ($_source_table->isView()) {
1069                 $sql_drop_query = 'DROP VIEW';
1070             } else {
1071                 $sql_drop_query = 'DROP TABLE';
1072             }
1073             $sql_drop_query .= ' ' . $source;
1074             $GLOBALS['dbi']->query($sql_drop_query);
1075
1076             // Renable table in configuration storage
1077             PMA_REL_renameTable(
1078                 $source_db, $target_db,
1079                 $source_table, $target_table
1080             );
1081
1082             $GLOBALS['sql_query'] .= "\n\n" . $sql_drop_query . ';';
1083             // end if ($move)
1084             return true;
1085         }
1086
1087         // we are copying
1088         // Create new entries as duplicates from old PMA DBs
1089         if ($what == 'dataonly' || isset($maintain_relations)) {
1090             return true;
1091         }
1092
1093         if ($GLOBALS['cfgRelation']['commwork']) {
1094             // Get all comments and MIME-Types for current table
1095             $comments_copy_rs = PMA_queryAsControlUser(
1096                 'SELECT column_name, comment'
1097                 . ($GLOBALS['cfgRelation']['mimework']
1098                 ? ', mimetype, transformation, transformation_options'
1099                 : '')
1100                 . ' FROM '
1101                 . Util::backquote($GLOBALS['cfgRelation']['db'])
1102                 . '.'
1103                 . Util::backquote($GLOBALS['cfgRelation']['column_info'])
1104                 . ' WHERE '
1105                 . ' db_name = \''
1106                 . $GLOBALS['dbi']->escapeString($source_db) . '\''
1107                 . ' AND '
1108                 . ' table_name = \''
1109                 . $GLOBALS['dbi']->escapeString($source_table) . '\''
1110             );
1111
1112             // Write every comment as new copied entry. [MIME]
1113             while ($comments_copy_row
1114                 = $GLOBALS['dbi']->fetchAssoc($comments_copy_rs)) {
1115                 $new_comment_query = 'REPLACE INTO '
1116                     . Util::backquote($GLOBALS['cfgRelation']['db'])
1117                     . '.' . Util::backquote(
1118                         $GLOBALS['cfgRelation']['column_info']
1119                     )
1120                     . ' (db_name, table_name, column_name, comment'
1121                     . ($GLOBALS['cfgRelation']['mimework']
1122                         ? ', mimetype, transformation, transformation_options'
1123                         : '')
1124                     . ') ' . ' VALUES(' . '\'' . $GLOBALS['dbi']->escapeString($target_db)
1125                     . '\',\'' . $GLOBALS['dbi']->escapeString($target_table) . '\',\''
1126                     . $GLOBALS['dbi']->escapeString($comments_copy_row['column_name'])
1127                     . '\',\'' . $GLOBALS['dbi']->escapeString($target_table) . '\',\''
1128                     . $GLOBALS['dbi']->escapeString($comments_copy_row['comment'])
1129                     . '\''
1130                     . ($GLOBALS['cfgRelation']['mimework']
1131                         ? ',\'' . $GLOBALS['dbi']->escapeString(
1132                             $comments_copy_row['mimetype']
1133                         )
1134                         . '\',' . '\'' . $GLOBALS['dbi']->escapeString(
1135                             $comments_copy_row['transformation']
1136                         )
1137                         . '\',' . '\'' . $GLOBALS['dbi']->escapeString(
1138                             $comments_copy_row['transformation_options']
1139                         )
1140                         . '\''
1141                         : '')
1142                     . ')';
1143                 PMA_queryAsControlUser($new_comment_query);
1144             } // end while
1145             $GLOBALS['dbi']->freeResult($comments_copy_rs);
1146             unset($comments_copy_rs);
1147         }
1148
1149         // duplicating the bookmarks must not be done here, but
1150         // just once per db
1151
1152         $get_fields = array('display_field');
1153         $where_fields = array(
1154             'db_name' => $source_db,
1155             'table_name' => $source_table
1156         );
1157         $new_fields = array(
1158             'db_name' => $target_db,
1159             'table_name' => $target_table
1160         );
1161         Table::duplicateInfo(
1162             'displaywork',
1163             'table_info',
1164             $get_fields,
1165             $where_fields,
1166             $new_fields
1167         );
1168
1169         /**
1170          * @todo revise this code when we support cross-db relations
1171          */
1172         $get_fields = array(
1173             'master_field',
1174             'foreign_table',
1175             'foreign_field'
1176         );
1177         $where_fields = array(
1178             'master_db' => $source_db,
1179             'master_table' => $source_table
1180         );
1181         $new_fields = array(
1182             'master_db' => $target_db,
1183             'foreign_db' => $target_db,
1184             'master_table' => $target_table
1185         );
1186         Table::duplicateInfo(
1187             'relwork',
1188             'relation',
1189             $get_fields,
1190             $where_fields,
1191             $new_fields
1192         );
1193
1194         $get_fields = array(
1195             'foreign_field',
1196             'master_table',
1197             'master_field'
1198         );
1199         $where_fields = array(
1200             'foreign_db' => $source_db,
1201             'foreign_table' => $source_table
1202         );
1203         $new_fields = array(
1204             'master_db' => $target_db,
1205             'foreign_db' => $target_db,
1206             'foreign_table' => $target_table
1207         );
1208         Table::duplicateInfo(
1209             'relwork',
1210             'relation',
1211             $get_fields,
1212             $where_fields,
1213             $new_fields
1214         );
1215
1216         /**
1217          * @todo Can't get duplicating PDFs the right way. The
1218          * page numbers always get screwed up independently from
1219          * duplication because the numbers do not seem to be stored on a
1220          * per-database basis. Would the author of pdf support please
1221          * have a look at it?
1222          *
1223         $get_fields = array('page_descr');
1224         $where_fields = array('db_name' => $source_db);
1225         $new_fields = array('db_name' => $target_db);
1226         $last_id = Table::duplicateInfo(
1227             'pdfwork',
1228             'pdf_pages',
1229             $get_fields,
1230             $where_fields,
1231             $new_fields
1232         );
1233
1234         if (isset($last_id) && $last_id >= 0) {
1235             $get_fields = array('x', 'y');
1236             $where_fields = array(
1237                 'db_name' => $source_db,
1238                 'table_name' => $source_table
1239             );
1240             $new_fields = array(
1241                 'db_name' => $target_db,
1242                 'table_name' => $target_table,
1243                 'pdf_page_number' => $last_id
1244             );
1245             Table::duplicateInfo(
1246                 'pdfwork',
1247                 'table_coords',
1248                 $get_fields,
1249                 $where_fields,
1250                 $new_fields
1251             );
1252         }
1253          */
1254
1255         return true;
1256     }
1257
1258     /**
1259      * checks if given name is a valid table name,
1260      * currently if not empty, trailing spaces, '.', '/' and '\'
1261      *
1262      * @param string  $table_name    name to check
1263      * @param boolean $is_backquoted whether this name is used inside backquotes or not
1264      *
1265      * @todo add check for valid chars in filename on current system/os
1266      * @see  https://dev.mysql.com/doc/refman/5.0/en/legal-names.html
1267      *
1268      * @return boolean whether the string is valid or not
1269      */
1270     static function isValidName($table_name, $is_backquoted = false)
1271     {
1272         if ($table_name !== rtrim($table_name)) {
1273             // trailing spaces not allowed even in backquotes
1274             return false;
1275         }
1276
1277         if (strlen($table_name) === 0) {
1278             // zero length
1279             return false;
1280         }
1281
1282         if (! $is_backquoted && $table_name !== trim($table_name)) {
1283             // spaces at the start or in between only allowed inside backquotes
1284             return false;
1285         }
1286
1287         if (! $is_backquoted && preg_match('/^[a-zA-Z0-9_$]+$/', $table_name)) {
1288             // only allow the above regex in unquoted identifiers
1289             // see : https://dev.mysql.com/doc/refman/5.7/en/identifiers.html
1290             return true;
1291         } else if ($is_backquoted) {
1292             // If backquoted, all characters should be allowed (except w/ trailing spaces)
1293             return true;
1294         }
1295
1296         // If not backquoted and doesn't follow the above regex
1297         return false;
1298     }
1299
1300     /**
1301      * renames table
1302      *
1303      * @param string $new_name new table name
1304      * @param string $new_db   new database name
1305      *
1306      * @return bool success
1307      */
1308     public function rename($new_name, $new_db = null)
1309     {
1310         if ($GLOBALS['dbi']->getLowerCaseNames() === '1') {
1311             $new_name = strtolower($new_name);
1312         }
1313
1314         if (null !== $new_db && $new_db !== $this->getDbName()) {
1315             // Ensure the target is valid
1316             if (! $GLOBALS['dblist']->databases->exists($new_db)) {
1317                 $this->errors[] = __('Invalid database:') . ' ' . $new_db;
1318                 return false;
1319             }
1320         } else {
1321             $new_db = $this->getDbName();
1322         }
1323
1324         $new_table = new Table($new_name, $new_db);
1325
1326         if ($this->getFullName() === $new_table->getFullName()) {
1327             return true;
1328         }
1329
1330         // Allow whitespaces (not trailing) in $new_name,
1331         // since we are using $backquoted in getting the fullName of table
1332         // below to be used in the query
1333         if (! Table::isValidName($new_name, true)) {
1334             $this->errors[] = __('Invalid table name:') . ' '
1335                 . $new_table->getFullName();
1336             return false;
1337         }
1338
1339         // If the table is moved to a different database drop its triggers first
1340         $triggers = $this->_dbi->getTriggers(
1341             $this->getDbName(), $this->getName(), ''
1342         );
1343         $handle_triggers = $this->getDbName() != $new_db && $triggers;
1344         if ($handle_triggers) {
1345             foreach ($triggers as $trigger) {
1346                 $sql = 'DROP TRIGGER IF EXISTS '
1347                     . Util::backquote($this->getDbName())
1348                     . '.' . Util::backquote($trigger['name']) . ';';
1349                 $this->_dbi->query($sql);
1350             }
1351         }
1352
1353         /*
1354          * tested also for a view, in MySQL 5.0.92, 5.1.55 and 5.5.13
1355          */
1356         $GLOBALS['sql_query'] = '
1357             RENAME TABLE ' . $this->getFullName(true) . '
1358                   TO ' . $new_table->getFullName(true) . ';';
1359         // I don't think a specific error message for views is necessary
1360         if (! $this->_dbi->query($GLOBALS['sql_query'])) {
1361             // Restore triggers in the old database
1362             if ($handle_triggers) {
1363                 $this->_dbi->selectDb($this->getDbName());
1364                 foreach ($triggers as $trigger) {
1365                     $this->_dbi->query($trigger['create']);
1366                 }
1367             }
1368             $this->errors[] = sprintf(
1369                 __('Failed to rename table %1$s to %2$s!'),
1370                 $this->getFullName(),
1371                 $new_table->getFullName()
1372             );
1373             return false;
1374         }
1375
1376         $old_name = $this->getName();
1377         $old_db = $this->getDbName();
1378         $this->_name = $new_name;
1379         $this->_db_name = $new_db;
1380
1381         // Renable table in configuration storage
1382         PMA_REL_renameTable(
1383             $old_db, $new_db,
1384             $old_name, $new_name
1385         );
1386
1387         $this->messages[] = sprintf(
1388             __('Table %1$s has been renamed to %2$s.'),
1389             htmlspecialchars($old_name),
1390             htmlspecialchars($new_name)
1391         );
1392         return true;
1393     }
1394
1395     /**
1396      * Get all unique columns
1397      *
1398      * returns an array with all columns with unique content, in fact these are
1399      * all columns being single indexed in PRIMARY or UNIQUE
1400      *
1401      * e.g.
1402      *  - PRIMARY(id) // id
1403      *  - UNIQUE(name) // name
1404      *  - PRIMARY(fk_id1, fk_id2) // NONE
1405      *  - UNIQUE(x,y) // NONE
1406      *
1407      * @param bool $backquoted whether to quote name with backticks ``
1408      * @param bool $fullName   whether to include full name of the table as a prefix
1409      *
1410      * @return array
1411      */
1412     public function getUniqueColumns($backquoted = true, $fullName = true)
1413     {
1414         $sql = $this->_dbi->getTableIndexesSql(
1415             $this->getDbName(),
1416             $this->getName(),
1417             'Non_unique = 0'
1418         );
1419         $uniques = $this->_dbi->fetchResult(
1420             $sql,
1421             array('Key_name', null),
1422             'Column_name'
1423         );
1424
1425         $return = array();
1426         foreach ($uniques as $index) {
1427             if (count($index) > 1) {
1428                 continue;
1429             }
1430             if ($fullName) {
1431                 $possible_column = $this->getFullName($backquoted) . '.';
1432             } else {
1433                 $possible_column = '';
1434             }
1435             if ($backquoted) {
1436                 $possible_column .= Util::backquote($index[0]);
1437             } else {
1438                 $possible_column .= $index[0];
1439             }
1440             // a column might have a primary and an unique index on it
1441             if (! in_array($possible_column, $return)) {
1442                 $return[] = $possible_column;
1443             }
1444         }
1445
1446         return $return;
1447     }
1448
1449     /**
1450      * Formats lists of columns
1451      *
1452      * returns an array with all columns that make use of an index
1453      *
1454      * e.g. index(col1, col2) would return col1, col2
1455      *
1456      * @param array $indexed    column data
1457      * @param bool  $backquoted whether to quote name with backticks ``
1458      * @param bool  $fullName   whether to include full name of the table as a prefix
1459      *
1460      * @return array
1461      */
1462     private function _formatColumns($indexed, $backquoted, $fullName)
1463     {
1464         $return = array();
1465         foreach ($indexed as $column) {
1466             $return[] = ($fullName ? $this->getFullName($backquoted) . '.' : '')
1467                 . ($backquoted ? Util::backquote($column) : $column);
1468         }
1469
1470         return $return;
1471     }
1472
1473     /**
1474      * Get all indexed columns
1475      *
1476      * returns an array with all columns that make use of an index
1477      *
1478      * e.g. index(col1, col2) would return col1, col2
1479      *
1480      * @param bool $backquoted whether to quote name with backticks ``
1481      * @param bool $fullName   whether to include full name of the table as a prefix
1482      *
1483      * @return array
1484      */
1485     public function getIndexedColumns($backquoted = true, $fullName = true)
1486     {
1487         $sql = $this->_dbi->getTableIndexesSql(
1488             $this->getDbName(),
1489             $this->getName(),
1490             ''
1491         );
1492         $indexed = $this->_dbi->fetchResult($sql, 'Column_name', 'Column_name');
1493
1494         return $this->_formatColumns($indexed, $backquoted, $fullName);
1495     }
1496
1497     /**
1498      * Get all columns
1499      *
1500      * returns an array with all columns
1501      *
1502      * @param bool $backquoted whether to quote name with backticks ``
1503      * @param bool $fullName   whether to include full name of the table as a prefix
1504      *
1505      * @return array
1506      */
1507     public function getColumns($backquoted = true, $fullName = true)
1508     {
1509         $sql = 'SHOW COLUMNS FROM ' . $this->getFullName(true);
1510         $indexed = $this->_dbi->fetchResult($sql, 'Field', 'Field');
1511
1512         return $this->_formatColumns($indexed, $backquoted, $fullName);
1513     }
1514
1515     /**
1516      * Get meta info for fields in table
1517      *
1518      * @return mixed
1519      */
1520     public function getColumnsMeta()
1521     {
1522         $move_columns_sql_query = sprintf(
1523             'SELECT * FROM %s.%s LIMIT 1',
1524             Util::backquote($this->_db_name),
1525             Util::backquote($this->_name)
1526         );
1527         $move_columns_sql_result = $this->_dbi->tryQuery($move_columns_sql_query);
1528         if ($move_columns_sql_result !== false) {
1529             return $this->_dbi->getFieldsMeta($move_columns_sql_result);
1530         } else {
1531             // unsure how to reproduce but it was seen on the reporting server
1532             return array();
1533         }
1534     }
1535
1536     /**
1537      * Get non-generated columns in table
1538      *
1539      * @param bool $backquoted whether to quote name with backticks ``
1540      *
1541      * @return array
1542      */
1543     public function getNonGeneratedColumns($backquoted = true)
1544     {
1545         $columns_meta_query = 'SHOW COLUMNS FROM ' . $this->getFullName(true);
1546         $ret = array();
1547
1548         $columns_meta_query_result = $this->_dbi->fetchResult(
1549             $columns_meta_query
1550         );
1551
1552         if ($columns_meta_query_result
1553             && $columns_meta_query_result !== false
1554         ) {
1555             foreach ($columns_meta_query_result as $column) {
1556                 $value = $column['Field'];
1557                 if ($backquoted === true) {
1558                     $value = Util::backquote($value);
1559                 }
1560
1561                 if (strpos($column['Extra'], 'GENERATED') === false && strpos($column['Extra'], 'VIRTUAL') === false) {
1562                     array_push($ret, $value);
1563                 }
1564             }
1565         }
1566
1567         return $ret;
1568     }
1569
1570     /**
1571      * Return UI preferences for this table from phpMyAdmin database.
1572      *
1573      * @return array
1574      */
1575     protected function getUiPrefsFromDb()
1576     {
1577         $cfgRelation = PMA_getRelationsParam();
1578         $pma_table = Util::backquote($cfgRelation['db']) . "."
1579             . Util::backquote($cfgRelation['table_uiprefs']);
1580
1581         // Read from phpMyAdmin database
1582         $sql_query = " SELECT `prefs` FROM " . $pma_table
1583             . " WHERE `username` = '" . $GLOBALS['dbi']->escapeString($GLOBALS['cfg']['Server']['user']) . "'"
1584             . " AND `db_name` = '" . $GLOBALS['dbi']->escapeString($this->_db_name) . "'"
1585             . " AND `table_name` = '" . $GLOBALS['dbi']->escapeString($this->_name) . "'";
1586
1587         $row = $this->_dbi->fetchArray(PMA_queryAsControlUser($sql_query));
1588         if (isset($row[0])) {
1589             return json_decode($row[0], true);
1590         } else {
1591             return array();
1592         }
1593     }
1594
1595     /**
1596      * Save this table's UI preferences into phpMyAdmin database.
1597      *
1598      * @return true|Message
1599      */
1600     protected function saveUiPrefsToDb()
1601     {
1602         $cfgRelation = PMA_getRelationsParam();
1603         $pma_table = Util::backquote($cfgRelation['db']) . "."
1604             . Util::backquote($cfgRelation['table_uiprefs']);
1605
1606         $secureDbName = $GLOBALS['dbi']->escapeString($this->_db_name);
1607
1608         $username = $GLOBALS['cfg']['Server']['user'];
1609         $sql_query = " REPLACE INTO " . $pma_table
1610             . " (username, db_name, table_name, prefs) VALUES ('"
1611             . $GLOBALS['dbi']->escapeString($username) . "', '" . $secureDbName
1612             . "', '" . $GLOBALS['dbi']->escapeString($this->_name) . "', '"
1613             . $GLOBALS['dbi']->escapeString(json_encode($this->uiprefs)) . "')";
1614
1615         $success = $this->_dbi->tryQuery($sql_query, $GLOBALS['controllink']);
1616
1617         if (!$success) {
1618             $message = Message::error(
1619                 __('Could not save table UI preferences!')
1620             );
1621             $message->addMessage(
1622                 Message::rawError(
1623                     $this->_dbi->getError($GLOBALS['controllink'])
1624                 ),
1625                 '<br /><br />'
1626             );
1627             return $message;
1628         }
1629
1630         // Remove some old rows in table_uiprefs if it exceeds the configured
1631         // maximum rows
1632         $sql_query = 'SELECT COUNT(*) FROM ' . $pma_table;
1633         $rows_count = $this->_dbi->fetchValue($sql_query);
1634         $max_rows = $GLOBALS['cfg']['Server']['MaxTableUiprefs'];
1635         if ($rows_count > $max_rows) {
1636             $num_rows_to_delete = $rows_count - $max_rows;
1637             $sql_query
1638                 = ' DELETE FROM ' . $pma_table .
1639                 ' ORDER BY last_update ASC' .
1640                 ' LIMIT ' . $num_rows_to_delete;
1641             $success = $this->_dbi->tryQuery(
1642                 $sql_query, $GLOBALS['controllink']
1643             );
1644
1645             if (!$success) {
1646                 $message = Message::error(
1647                     sprintf(
1648                         __(
1649                             'Failed to cleanup table UI preferences (see ' .
1650                             '$cfg[\'Servers\'][$i][\'MaxTableUiprefs\'] %s)'
1651                         ),
1652                         Util::showDocu('config', 'cfg_Servers_MaxTableUiprefs')
1653                     )
1654                 );
1655                 $message->addMessage(
1656                     Message::rawError(
1657                         $this->_dbi->getError($GLOBALS['controllink'])
1658                     ),
1659                     '<br /><br />'
1660                 );
1661                 return $message;
1662             }
1663         }
1664
1665         return true;
1666     }
1667
1668     /**
1669      * Loads the UI preferences for this table.
1670      * If pmadb and table_uiprefs is set, it will load the UI preferences from
1671      * phpMyAdmin database.
1672      *
1673      * @return void
1674      */
1675     protected function loadUiPrefs()
1676     {
1677         $cfgRelation = PMA_getRelationsParam();
1678         $server_id = $GLOBALS['server'];
1679
1680         // set session variable if it's still undefined
1681         if (!isset($_SESSION['tmpval']['table_uiprefs'][$server_id][$this->_db_name][$this->_name])) {
1682             // check whether we can get from pmadb
1683             $_SESSION['tmpval']['table_uiprefs'][$server_id][$this->_db_name]
1684             [$this->_name] = $cfgRelation['uiprefswork']
1685                 ?  $this->getUiPrefsFromDb()
1686                 : array();
1687         }
1688         $this->uiprefs =& $_SESSION['tmpval']['table_uiprefs'][$server_id]
1689         [$this->_db_name][$this->_name];
1690     }
1691
1692     /**
1693      * Get a property from UI preferences.
1694      * Return false if the property is not found.
1695      * Available property:
1696      * - PROP_SORTED_COLUMN
1697      * - PROP_COLUMN_ORDER
1698      * - PROP_COLUMN_VISIB
1699      *
1700      * @param string $property property
1701      *
1702      * @return mixed
1703      */
1704     public function getUiProp($property)
1705     {
1706         if (! isset($this->uiprefs)) {
1707             $this->loadUiPrefs();
1708         }
1709
1710         // do checking based on property
1711         if ($property == self::PROP_SORTED_COLUMN) {
1712             if (!isset($this->uiprefs[$property])) {
1713                 return false;
1714             }
1715
1716             if (!isset($_REQUEST['discard_remembered_sort'])) {
1717                 // check if the column name exists in this table
1718                 $tmp = explode(' ', $this->uiprefs[$property]);
1719                 $colname = $tmp[0];
1720                 //remove backquoting from colname
1721                 $colname = str_replace('`', '', $colname);
1722                 //get the available column name without backquoting
1723                 $avail_columns = $this->getColumns(false);
1724
1725                 foreach ($avail_columns as $each_col) {
1726                     // check if $each_col ends with $colname
1727                     if (substr_compare(
1728                         $each_col,
1729                         $colname,
1730                         mb_strlen($each_col) - mb_strlen($colname)
1731                     ) === 0
1732                     ) {
1733                         return $this->uiprefs[$property];
1734                     }
1735                 }
1736             }
1737             // remove the property, since it no longer exists in database
1738             $this->removeUiProp($property);
1739             return false;
1740         }
1741
1742         if ($property == self::PROP_COLUMN_ORDER
1743             || $property == self::PROP_COLUMN_VISIB
1744         ) {
1745             if ($this->isView() || !isset($this->uiprefs[$property])) {
1746                 return false;
1747             }
1748
1749             // check if the table has not been modified
1750             if ($this->getStatusInfo('Create_time') == $this->uiprefs['CREATE_TIME']
1751             ) {
1752                 return array_map('intval', $this->uiprefs[$property]);
1753             }
1754
1755             // remove the property, since the table has been modified
1756             $this->removeUiProp($property);
1757             return false;
1758         }
1759
1760         // default behaviour for other property:
1761         return isset($this->uiprefs[$property]) ? $this->uiprefs[$property] : false;
1762     }
1763
1764     /**
1765      * Set a property from UI preferences.
1766      * If pmadb and table_uiprefs is set, it will save the UI preferences to
1767      * phpMyAdmin database.
1768      * Available property:
1769      * - PROP_SORTED_COLUMN
1770      * - PROP_COLUMN_ORDER
1771      * - PROP_COLUMN_VISIB
1772      *
1773      * @param string $property          Property
1774      * @param mixed  $value             Value for the property
1775      * @param string $table_create_time Needed for PROP_COLUMN_ORDER
1776      *                                  and PROP_COLUMN_VISIB
1777      *
1778      * @return boolean|Message
1779      */
1780     public function setUiProp($property, $value, $table_create_time = null)
1781     {
1782         if (! isset($this->uiprefs)) {
1783             $this->loadUiPrefs();
1784         }
1785         // we want to save the create time if the property is PROP_COLUMN_ORDER
1786         if (! $this->isView()
1787             && ($property == self::PROP_COLUMN_ORDER
1788             || $property == self::PROP_COLUMN_VISIB)
1789         ) {
1790             $curr_create_time = $this->getStatusInfo('CREATE_TIME');
1791             if (isset($table_create_time)
1792                 && $table_create_time == $curr_create_time
1793             ) {
1794                 $this->uiprefs['CREATE_TIME'] = $curr_create_time;
1795             } else {
1796                 // there is no $table_create_time, or
1797                 // supplied $table_create_time is older than current create time,
1798                 // so don't save
1799                 return Message::error(
1800                     sprintf(
1801                         __(
1802                             'Cannot save UI property "%s". The changes made will ' .
1803                             'not be persistent after you refresh this page. ' .
1804                             'Please check if the table structure has been changed.'
1805                         ),
1806                         $property
1807                     )
1808                 );
1809             }
1810         }
1811         // save the value
1812         $this->uiprefs[$property] = $value;
1813
1814         // check if pmadb is set
1815         $cfgRelation = PMA_getRelationsParam();
1816         if ($cfgRelation['uiprefswork']) {
1817             return $this->saveUiprefsToDb();
1818         }
1819         return true;
1820     }
1821
1822     /**
1823      * Remove a property from UI preferences.
1824      *
1825      * @param string $property the property
1826      *
1827      * @return true|Message
1828      */
1829     public function removeUiProp($property)
1830     {
1831         if (! isset($this->uiprefs)) {
1832             $this->loadUiPrefs();
1833         }
1834         if (isset($this->uiprefs[$property])) {
1835             unset($this->uiprefs[$property]);
1836
1837             // check if pmadb is set
1838             $cfgRelation = PMA_getRelationsParam();
1839             if ($cfgRelation['uiprefswork']) {
1840                 return $this->saveUiprefsToDb();
1841             }
1842         }
1843         return true;
1844     }
1845
1846     /**
1847      * Get all column names which are MySQL reserved words
1848      *
1849      * @return array
1850      * @access public
1851      */
1852     public function getReservedColumnNames()
1853     {
1854         $columns = $this->getColumns(false);
1855         $return = array();
1856         foreach ($columns as $column) {
1857             $temp = explode('.', $column);
1858             $column_name = $temp[2];
1859             if (Context::isKeyword($column_name, true)) {
1860                 $return[] = $column_name;
1861             }
1862         }
1863         return $return;
1864     }
1865
1866     /**
1867      * Function to get the name and type of the columns of a table
1868      *
1869      * @return array
1870      */
1871     public function getNameAndTypeOfTheColumns()
1872     {
1873         $columns = array();
1874         foreach ($this->_dbi->getColumnsFull(
1875             $this->_db_name, $this->_name
1876         ) as $row) {
1877             if (preg_match('@^(set|enum)\((.+)\)$@i', $row['Type'], $tmp)) {
1878                 $tmp[2] = mb_substr(
1879                     preg_replace('@([^,])\'\'@', '\\1\\\'', ',' . $tmp[2]), 1
1880                 );
1881                 $columns[$row['Field']] = $tmp[1] . '('
1882                     . str_replace(',', ', ', $tmp[2]) . ')';
1883             } else {
1884                 $columns[$row['Field']] = $row['Type'];
1885             }
1886         }
1887         return $columns;
1888     }
1889
1890     /**
1891      * Get index with index name
1892      *
1893      * @param string $index Index name
1894      *
1895      * @return Index
1896      */
1897     public function getIndex($index)
1898     {
1899         return Index::singleton($this->_db_name, $this->_name, $index);
1900     }
1901
1902     /**
1903      * Function to get the sql query for index creation or edit
1904      *
1905      * @param Index $index  current index
1906      * @param bool  &$error whether error occurred or not
1907      *
1908      * @return string
1909      */
1910     public function getSqlQueryForIndexCreateOrEdit($index, &$error)
1911     {
1912         // $sql_query is the one displayed in the query box
1913         $sql_query = sprintf(
1914             'ALTER TABLE %s.%s',
1915             Util::backquote($this->_db_name),
1916             Util::backquote($this->_name)
1917         );
1918
1919         // Drops the old index
1920         if (! empty($_REQUEST['old_index'])) {
1921             if ($_REQUEST['old_index'] == 'PRIMARY') {
1922                 $sql_query .= ' DROP PRIMARY KEY,';
1923             } else {
1924                 $sql_query .= sprintf(
1925                     ' DROP INDEX %s,',
1926                     Util::backquote($_REQUEST['old_index'])
1927                 );
1928             }
1929         } // end if
1930
1931         // Builds the new one
1932         switch ($index->getChoice()) {
1933         case 'PRIMARY':
1934             if ($index->getName() == '') {
1935                 $index->setName('PRIMARY');
1936             } elseif ($index->getName() != 'PRIMARY') {
1937                 $error = Message::error(
1938                     __('The name of the primary key must be "PRIMARY"!')
1939                 );
1940             }
1941             $sql_query .= ' ADD PRIMARY KEY';
1942             break;
1943         case 'FULLTEXT':
1944         case 'UNIQUE':
1945         case 'INDEX':
1946         case 'SPATIAL':
1947             if ($index->getName() == 'PRIMARY') {
1948                 $error = Message::error(
1949                     __('Can\'t rename index to PRIMARY!')
1950                 );
1951             }
1952             $sql_query .= sprintf(
1953                 ' ADD %s ',
1954                 $index->getChoice()
1955             );
1956             if ($index->getName()) {
1957                 $sql_query .= Util::backquote($index->getName());
1958             }
1959             break;
1960         } // end switch
1961
1962         $index_fields = array();
1963         foreach ($index->getColumns() as $key => $column) {
1964             $index_fields[$key] = Util::backquote($column->getName());
1965             if ($column->getSubPart()) {
1966                 $index_fields[$key] .= '(' . $column->getSubPart() . ')';
1967             }
1968         } // end while
1969
1970         if (empty($index_fields)) {
1971             $error = Message::error(__('No index parts defined!'));
1972         } else {
1973             $sql_query .= ' (' . implode(', ', $index_fields) . ')';
1974         }
1975
1976         $keyBlockSizes = $index->getKeyBlockSize();
1977         if (! empty($keyBlockSizes)) {
1978             $sql_query .= sprintf(
1979                 ' KEY_BLOCK_SIZE = ',
1980                 $GLOBALS['dbi']->escapeString($keyBlockSizes)
1981             );
1982         }
1983
1984         // specifying index type is allowed only for primary, unique and index only
1985         // TokuDB is using Fractal Tree, Using Type is not useless
1986         // Ref: https://mariadb.com/kb/en/mariadb/storage-engine-index-types/
1987         $type = $index->getType();
1988         if ($index->getChoice() != 'SPATIAL'
1989             && $index->getChoice() != 'FULLTEXT'
1990             && in_array($type, Index::getIndexTypes())
1991             && ! $this->isEngine(array('TOKUDB'))
1992         ) {
1993             $sql_query .= ' USING ' . $type;
1994         }
1995
1996         $parser = $index->getParser();
1997         if ($index->getChoice() == 'FULLTEXT' && ! empty($parser)) {
1998             $sql_query .= ' WITH PARSER ' . $GLOBALS['dbi']->escapeString($parser);
1999         }
2000
2001         $comment = $index->getComment();
2002         if (! empty($comment)) {
2003             $sql_query .= sprintf(
2004                 " COMMENT '%s'",
2005                 $GLOBALS['dbi']->escapeString($comment)
2006             );
2007         }
2008
2009         $sql_query .= ';';
2010
2011         return $sql_query;
2012     }
2013
2014     /**
2015      * Function to handle update for display field
2016      *
2017      * @param string $display_field display field
2018      * @param array  $cfgRelation   configuration relation
2019      *
2020      * @return boolean True on update succeed or False on failure
2021      */
2022     public function updateDisplayField($display_field, $cfgRelation)
2023     {
2024         $upd_query = false;
2025         if ($display_field == '') {
2026             $upd_query = 'DELETE FROM '
2027                 . Util::backquote($GLOBALS['cfgRelation']['db'])
2028                 . '.' . Util::backquote($cfgRelation['table_info'])
2029                 . ' WHERE db_name  = \''
2030                 . $GLOBALS['dbi']->escapeString($this->_db_name) . '\''
2031                 . ' AND table_name = \''
2032                 . $GLOBALS['dbi']->escapeString($this->_name) . '\'';
2033         } else {
2034             $upd_query = 'REPLACE INTO '
2035                 . Util::backquote($GLOBALS['cfgRelation']['db'])
2036                 . '.' . Util::backquote($cfgRelation['table_info'])
2037                 . '(db_name, table_name, display_field) VALUES('
2038                 . '\'' . $GLOBALS['dbi']->escapeString($this->_db_name) . '\','
2039                 . '\'' . $GLOBALS['dbi']->escapeString($this->_name) . '\','
2040                 . '\'' . $GLOBALS['dbi']->escapeString($display_field) . '\')';
2041         }
2042
2043         if ($upd_query) {
2044             $this->_dbi->query(
2045                 $upd_query,
2046                 $GLOBALS['controllink'],
2047                 0,
2048                 false
2049             );
2050             return true;
2051         }
2052         return false;
2053     }
2054
2055     /**
2056      * Function to get update query for updating internal relations
2057      *
2058      * @param array      $multi_edit_columns_name multi edit column names
2059      * @param array      $destination_db          destination tables
2060      * @param array      $destination_table       destination tables
2061      * @param array      $destination_column      destination columns
2062      * @param array      $cfgRelation             configuration relation
2063      * @param array|null $existrel                db, table, column
2064      *
2065      * @return boolean
2066      */
2067     public function updateInternalRelations($multi_edit_columns_name,
2068         $destination_db, $destination_table, $destination_column,
2069         $cfgRelation, $existrel
2070     ) {
2071         $updated = false;
2072         foreach ($destination_db as $master_field_md5 => $foreign_db) {
2073             $upd_query = null;
2074             // Map the fieldname's md5 back to its real name
2075             $master_field = $multi_edit_columns_name[$master_field_md5];
2076             $foreign_table = $destination_table[$master_field_md5];
2077             $foreign_field = $destination_column[$master_field_md5];
2078             if (! empty($foreign_db)
2079                 && ! empty($foreign_table)
2080                 && ! empty($foreign_field)
2081             ) {
2082                 if (! isset($existrel[$master_field])) {
2083                     $upd_query  = 'INSERT INTO '
2084                         . Util::backquote($GLOBALS['cfgRelation']['db'])
2085                         . '.' . Util::backquote($cfgRelation['relation'])
2086                         . '(master_db, master_table, master_field, foreign_db,'
2087                         . ' foreign_table, foreign_field)'
2088                         . ' values('
2089                         . '\'' . $GLOBALS['dbi']->escapeString($this->_db_name) . '\', '
2090                         . '\'' . $GLOBALS['dbi']->escapeString($this->_name) . '\', '
2091                         . '\'' . $GLOBALS['dbi']->escapeString($master_field) . '\', '
2092                         . '\'' . $GLOBALS['dbi']->escapeString($foreign_db) . '\', '
2093                         . '\'' . $GLOBALS['dbi']->escapeString($foreign_table) . '\','
2094                         . '\'' . $GLOBALS['dbi']->escapeString($foreign_field) . '\')';
2095
2096                 } elseif ($existrel[$master_field]['foreign_db'] != $foreign_db
2097                     || $existrel[$master_field]['foreign_table'] != $foreign_table
2098                     || $existrel[$master_field]['foreign_field'] != $foreign_field
2099                 ) {
2100                     $upd_query  = 'UPDATE '
2101                         . Util::backquote($GLOBALS['cfgRelation']['db'])
2102                         . '.' . Util::backquote($cfgRelation['relation'])
2103                         . ' SET foreign_db       = \''
2104                         . $GLOBALS['dbi']->escapeString($foreign_db) . '\', '
2105                         . ' foreign_table    = \''
2106                         . $GLOBALS['dbi']->escapeString($foreign_table) . '\', '
2107                         . ' foreign_field    = \''
2108                         . $GLOBALS['dbi']->escapeString($foreign_field) . '\' '
2109                         . ' WHERE master_db  = \''
2110                         . $GLOBALS['dbi']->escapeString($this->_db_name) . '\''
2111                         . ' AND master_table = \''
2112                         . $GLOBALS['dbi']->escapeString($this->_name) . '\''
2113                         . ' AND master_field = \''
2114                         . $GLOBALS['dbi']->escapeString($master_field) . '\'';
2115                 } // end if... else....
2116             } elseif (isset($existrel[$master_field])) {
2117                 $upd_query = 'DELETE FROM '
2118                     . Util::backquote($GLOBALS['cfgRelation']['db'])
2119                     . '.' . Util::backquote($cfgRelation['relation'])
2120                     . ' WHERE master_db  = \''
2121                     . $GLOBALS['dbi']->escapeString($this->_db_name) . '\''
2122                     . ' AND master_table = \''
2123                     . $GLOBALS['dbi']->escapeString($this->_name) . '\''
2124                     . ' AND master_field = \''
2125                     . $GLOBALS['dbi']->escapeString($master_field) . '\'';
2126             } // end if... else....
2127
2128             if (isset($upd_query)) {
2129                 $this->_dbi->query(
2130                     $upd_query,
2131                     $GLOBALS['controllink'],
2132                     0,
2133                     false
2134                 );
2135                 $updated = true;
2136             }
2137         }
2138         return $updated;
2139     }
2140
2141     /**
2142      * Function to handle foreign key updates
2143      *
2144      * @param array  $destination_foreign_db     destination foreign database
2145      * @param array  $multi_edit_columns_name    multi edit column names
2146      * @param array  $destination_foreign_table  destination foreign table
2147      * @param array  $destination_foreign_column destination foreign column
2148      * @param array  $options_array              options array
2149      * @param string $table                      current table
2150      * @param array  $existrel_foreign           db, table, column
2151      *
2152      * @return array
2153      */
2154     public function updateForeignKeys($destination_foreign_db,
2155         $multi_edit_columns_name, $destination_foreign_table,
2156         $destination_foreign_column, $options_array, $table, $existrel_foreign
2157     ) {
2158         $html_output = '';
2159         $preview_sql_data = '';
2160         $display_query = '';
2161         $seen_error = false;
2162
2163         foreach ($destination_foreign_db as $master_field_md5 => $foreign_db) {
2164             $create = false;
2165             $drop = false;
2166
2167             // Map the fieldname's md5 back to its real name
2168             $master_field = $multi_edit_columns_name[$master_field_md5];
2169
2170             $foreign_table = $destination_foreign_table[$master_field_md5];
2171             $foreign_field = $destination_foreign_column[$master_field_md5];
2172
2173             if (isset($existrel_foreign[$master_field_md5]['ref_db_name'])) {
2174                 $ref_db_name = $existrel_foreign[$master_field_md5]['ref_db_name'];
2175             } else {
2176                 $ref_db_name = $GLOBALS['db'];
2177             }
2178
2179             $empty_fields = false;
2180             foreach ($master_field as $key => $one_field) {
2181                 if ((! empty($one_field) && empty($foreign_field[$key]))
2182                     || (empty($one_field) && ! empty($foreign_field[$key]))
2183                 ) {
2184                     $empty_fields = true;
2185                 }
2186
2187                 if (empty($one_field) && empty($foreign_field[$key])) {
2188                     unset($master_field[$key]);
2189                     unset($foreign_field[$key]);
2190                 }
2191             }
2192
2193             if (! empty($foreign_db)
2194                 && ! empty($foreign_table)
2195                 && ! $empty_fields
2196             ) {
2197                 if (isset($existrel_foreign[$master_field_md5])) {
2198                     $constraint_name
2199                         = $existrel_foreign[$master_field_md5]['constraint'];
2200                     $on_delete = !empty(
2201                         $existrel_foreign[$master_field_md5]['on_delete']
2202                     )
2203                         ? $existrel_foreign[$master_field_md5]['on_delete']
2204                         : 'RESTRICT';
2205                     $on_update = ! empty(
2206                         $existrel_foreign[$master_field_md5]['on_update']
2207                     )
2208                         ? $existrel_foreign[$master_field_md5]['on_update']
2209                         : 'RESTRICT';
2210
2211                     if ($ref_db_name != $foreign_db
2212                         || $existrel_foreign[$master_field_md5]['ref_table_name'] != $foreign_table
2213                         || $existrel_foreign[$master_field_md5]['ref_index_list'] != $foreign_field
2214                         || $existrel_foreign[$master_field_md5]['index_list'] != $master_field
2215                         || $_REQUEST['constraint_name'][$master_field_md5] != $constraint_name
2216                         || ($_REQUEST['on_delete'][$master_field_md5] != $on_delete)
2217                         || ($_REQUEST['on_update'][$master_field_md5] != $on_update)
2218                     ) {
2219                         // another foreign key is already defined for this field
2220                         // or an option has been changed for ON DELETE or ON UPDATE
2221                         $drop = true;
2222                         $create = true;
2223                     } // end if... else....
2224                 } else {
2225                     // no key defined for this field(s)
2226                     $create = true;
2227                 }
2228             } elseif (isset($existrel_foreign[$master_field_md5])) {
2229                 $drop = true;
2230             } // end if... else....
2231
2232             $tmp_error_drop = false;
2233             if ($drop) {
2234                 $drop_query = 'ALTER TABLE ' . Util::backquote($table)
2235                     . ' DROP FOREIGN KEY '
2236                     . Util::backquote(
2237                         $existrel_foreign[$master_field_md5]['constraint']
2238                     )
2239                     . ';';
2240
2241                 if (! isset($_REQUEST['preview_sql'])) {
2242                     $display_query .= $drop_query . "\n";
2243                     $this->_dbi->tryQuery($drop_query);
2244                     $tmp_error_drop = $this->_dbi->getError();
2245
2246                     if (! empty($tmp_error_drop)) {
2247                         $seen_error = true;
2248                         $html_output .= Util::mysqlDie(
2249                             $tmp_error_drop, $drop_query, false, '', false
2250                         );
2251                         continue;
2252                     }
2253                 } else {
2254                     $preview_sql_data .= $drop_query . "\n";
2255                 }
2256             }
2257             $tmp_error_create = false;
2258             if (!$create) {
2259                 continue;
2260             }
2261
2262             $create_query = $this->_getSQLToCreateForeignKey(
2263                 $table, $master_field, $foreign_db, $foreign_table, $foreign_field,
2264                 $_REQUEST['constraint_name'][$master_field_md5],
2265                 $options_array[$_REQUEST['on_delete'][$master_field_md5]],
2266                 $options_array[$_REQUEST['on_update'][$master_field_md5]]
2267             );
2268
2269             if (! isset($_REQUEST['preview_sql'])) {
2270                 $display_query .= $create_query . "\n";
2271                 $this->_dbi->tryQuery($create_query);
2272                 $tmp_error_create = $this->_dbi->getError();
2273                 if (! empty($tmp_error_create)) {
2274                     $seen_error = true;
2275
2276                     if (substr($tmp_error_create, 1, 4) == '1005') {
2277                         $message = Message::error(
2278                             __(
2279                                 'Error creating foreign key on %1$s (check data ' .
2280                                 'types)'
2281                             )
2282                         );
2283                         $message->addParam(implode(', ', $master_field));
2284                         $html_output .= $message->getDisplay();
2285                     } else {
2286                         $html_output .= Util::mysqlDie(
2287                             $tmp_error_create, $create_query, false, '', false
2288                         );
2289                     }
2290                     $html_output .= Util::showMySQLDocu(
2291                         'InnoDB_foreign_key_constraints'
2292                     ) . "\n";
2293                 }
2294             } else {
2295                 $preview_sql_data .= $create_query . "\n";
2296             }
2297
2298             // this is an alteration and the old constraint has been dropped
2299             // without creation of a new one
2300             if ($drop && $create && empty($tmp_error_drop)
2301                 && ! empty($tmp_error_create)
2302             ) {
2303                 // a rollback may be better here
2304                 $sql_query_recreate = '# Restoring the dropped constraint...' . "\n";
2305                 $sql_query_recreate .= $this->_getSQLToCreateForeignKey(
2306                     $table,
2307                     $master_field,
2308                     $existrel_foreign[$master_field_md5]['ref_db_name'],
2309                     $existrel_foreign[$master_field_md5]['ref_table_name'],
2310                     $existrel_foreign[$master_field_md5]['ref_index_list'],
2311                     $existrel_foreign[$master_field_md5]['constraint'],
2312                     $options_array[$existrel_foreign[$master_field_md5]['on_delete']],
2313                     $options_array[$existrel_foreign[$master_field_md5]['on_update']]
2314                 );
2315                 if (! isset($_REQUEST['preview_sql'])) {
2316                     $display_query .= $sql_query_recreate . "\n";
2317                     $this->_dbi->tryQuery($sql_query_recreate);
2318                 } else {
2319                     $preview_sql_data .= $sql_query_recreate;
2320                 }
2321             }
2322         } // end foreach
2323
2324         return array(
2325             $html_output,
2326             $preview_sql_data,
2327             $display_query,
2328             $seen_error
2329         );
2330     }
2331
2332     /**
2333      * Returns the SQL query for foreign key constraint creation
2334      *
2335      * @param string $table        table name
2336      * @param array  $field        field names
2337      * @param string $foreignDb    foreign database name
2338      * @param string $foreignTable foreign table name
2339      * @param array  $foreignField foreign field names
2340      * @param string $name         name of the constraint
2341      * @param string $onDelete     on delete action
2342      * @param string $onUpdate     on update action
2343      *
2344      * @return string SQL query for foreign key constraint creation
2345      */
2346     private function _getSQLToCreateForeignKey(
2347         $table,
2348         $field,
2349         $foreignDb,
2350         $foreignTable,
2351         $foreignField,
2352         $name = null,
2353         $onDelete = null,
2354         $onUpdate = null
2355     ) {
2356         $sql_query  = 'ALTER TABLE ' . Util::backquote($table) . ' ADD ';
2357         // if user entered a constraint name
2358         if (! empty($name)) {
2359             $sql_query .= ' CONSTRAINT ' . Util::backquote($name);
2360         }
2361
2362         foreach ($field as $key => $one_field) {
2363             $field[$key] = Util::backquote($one_field);
2364         }
2365         foreach ($foreignField as $key => $one_field) {
2366             $foreignField[$key] = Util::backquote($one_field);
2367         }
2368         $sql_query .= ' FOREIGN KEY (' . implode(', ', $field) . ') REFERENCES '
2369             . ($this->_db_name != $foreignDb
2370                 ? Util::backquote($foreignDb) . '.' : '')
2371             . Util::backquote($foreignTable)
2372             . '(' . implode(', ', $foreignField) . ')';
2373
2374         if (! empty($onDelete)) {
2375             $sql_query .= ' ON DELETE ' . $onDelete;
2376         }
2377         if (! empty($onUpdate)) {
2378             $sql_query .= ' ON UPDATE ' . $onUpdate;
2379         }
2380         $sql_query .= ';';
2381
2382         return $sql_query;
2383     }
2384
2385     /**
2386      * Returns the generation expression for virtual columns
2387      *
2388      * @param string $column name of the column
2389      *
2390      * @return array|boolean associative array of column name and their expressions
2391      *                       or false on failure
2392      */
2393     public function getColumnGenerationExpression($column = null)
2394     {
2395         $serverType = Util::getServerType();
2396         if ($serverType == 'MySQL'
2397             && PMA_MYSQL_INT_VERSION > 50705
2398             && ! $GLOBALS['cfg']['Server']['DisableIS']
2399         ) {
2400             $sql
2401                 = "SELECT
2402                 `COLUMN_NAME` AS `Field`,
2403                 `GENERATION_EXPRESSION` AS `Expression`
2404                 FROM
2405                 `information_schema`.`COLUMNS`
2406                 WHERE
2407                 `TABLE_SCHEMA` = '" . $GLOBALS['dbi']->escapeString($this->_db_name) . "'
2408                 AND `TABLE_NAME` = '" . $GLOBALS['dbi']->escapeString($this->_name) . "'";
2409             if ($column != null) {
2410                 $sql .= " AND  `COLUMN_NAME` = '" . $GLOBALS['dbi']->escapeString($column)
2411                     . "'";
2412             }
2413             $columns = $this->_dbi->fetchResult($sql, 'Field', 'Expression');
2414             return $columns;
2415         }
2416
2417         $createTable = $this->showCreate();
2418         if (!$createTable) {
2419             return false;
2420         }
2421
2422         $parser = new Parser($createTable);
2423         /**
2424          * @var \PhpMyAdmin\SqlParser\Statements\CreateStatement $stmt
2425         */
2426         $stmt = $parser->statements[0];
2427         $fields = \PhpMyAdmin\SqlParser\Utils\Table::getFields($stmt);
2428         if ($column != null) {
2429             $expression = isset($fields[$column]['expr']) ?
2430                 substr($fields[$column]['expr'], 1, -1) : '';
2431             return array($column => $expression);
2432         }
2433
2434         $ret = array();
2435         foreach ($fields as $field => $options) {
2436             if (isset($options['expr'])) {
2437                 $ret[$field] = substr($options['expr'], 1, -1);
2438             }
2439         }
2440         return $ret;
2441     }
2442
2443     /**
2444      * Returns the CREATE statement for this table
2445      *
2446      * @return mixed
2447      */
2448     public function showCreate()
2449     {
2450         return $this->_dbi->fetchValue(
2451             'SHOW CREATE TABLE ' . Util::backquote($this->_db_name) . '.'
2452             . Util::backquote($this->_name),
2453             0, 1
2454         );
2455     }
2456
2457     /**
2458      * Returns the real row count for a table
2459      *
2460      * @return number
2461      */
2462     public function getRealRowCountTable()
2463     {
2464         // SQL query to get row count for a table.
2465         $result = $this->_dbi->fetchSingleRow(
2466             sprintf(
2467                 'SELECT COUNT(*) AS %s FROM %s.%s',
2468                 Util::backquote('row_count'),
2469                 Util::backquote($this->_db_name),
2470                 Util::backquote($this->_name)
2471             )
2472         );
2473         return $result['row_count'];
2474     }
2475
2476     /**
2477      * Get columns with indexes
2478      *
2479      * @param int $types types bitmask
2480      *
2481      * @return array an array of columns
2482      */
2483     public function getColumnsWithIndex($types)
2484     {
2485         $columns_with_index = array();
2486         foreach (
2487             Index::getFromTableByChoice(
2488                 $this->_name,
2489                 $this->_db_name,
2490                 $types
2491             ) as $index
2492         ) {
2493             $columns = $index->getColumns();
2494             foreach ($columns as $column_name => $dummy) {
2495                 $columns_with_index[] = $column_name;
2496             }
2497         }
2498         return $columns_with_index;
2499     }
2500 }