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
 375         if ($virtuality) {
 376             $query .= ' AS (' . $expression . ') ' . $virtuality;
 377         } else {
 378             if ($attribute != '') {
 379                 $query .= ' ' . $attribute;
 380
 381                 if ($is_timestamp
 382                     && preg_match('/TIMESTAMP/i', $attribute)
 383                     && strlen($length) !== 0
 384                     && $length !== 0
 385                 ) {
 386                     $query .= '(' . $length . ')';
 387                 }
 388             }
 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                     . '\''
1128                     . ($GLOBALS['cfgRelation']['mimework']
1129                         ? ',\'' . $GLOBALS['dbi']->escapeString(
1130                             $comments_copy_row['comment']
1131                         )
1132                         . '\',' . '\'' . $GLOBALS['dbi']->escapeString(
1133                             $comments_copy_row['mimetype']
1134                         )
1135                         . '\',' . '\'' . $GLOBALS['dbi']->escapeString(
1136                             $comments_copy_row['transformation']
1137                         )
1138                         . '\',' . '\'' . $GLOBALS['dbi']->escapeString(
1139                             $comments_copy_row['transformation_options']
1140                         )
1141                         . '\''
1142                         : '')
1143                     . ')';
1144                 PMA_queryAsControlUser($new_comment_query);
1145             } // end while
1146             $GLOBALS['dbi']->freeResult($comments_copy_rs);
1147             unset($comments_copy_rs);
1148         }
1149
1150         // duplicating the bookmarks must not be done here, but
1151         // just once per db
1152
1153         $get_fields = array('display_field');
1154         $where_fields = array(
1155             'db_name' => $source_db,
1156             'table_name' => $source_table
1157         );
1158         $new_fields = array(
1159             'db_name' => $target_db,
1160             'table_name' => $target_table
1161         );
1162         Table::duplicateInfo(
1163             'displaywork',
1164             'table_info',
1165             $get_fields,
1166             $where_fields,
1167             $new_fields
1168         );
1169
1170         /**
1171          * @todo revise this code when we support cross-db relations
1172          */
1173         $get_fields = array(
1174             'master_field',
1175             'foreign_table',
1176             'foreign_field'
1177         );
1178         $where_fields = array(
1179             'master_db' => $source_db,
1180             'master_table' => $source_table
1181         );
1182         $new_fields = array(
1183             'master_db' => $target_db,
1184             'foreign_db' => $target_db,
1185             'master_table' => $target_table
1186         );
1187         Table::duplicateInfo(
1188             'relwork',
1189             'relation',
1190             $get_fields,
1191             $where_fields,
1192             $new_fields
1193         );
1194
1195         $get_fields = array(
1196             'foreign_field',
1197             'master_table',
1198             'master_field'
1199         );
1200         $where_fields = array(
1201             'foreign_db' => $source_db,
1202             'foreign_table' => $source_table
1203         );
1204         $new_fields = array(
1205             'master_db' => $target_db,
1206             'foreign_db' => $target_db,
1207             'foreign_table' => $target_table
1208         );
1209         Table::duplicateInfo(
1210             'relwork',
1211             'relation',
1212             $get_fields,
1213             $where_fields,
1214             $new_fields
1215         );
1216
1217         /**
1218          * @todo Can't get duplicating PDFs the right way. The
1219          * page numbers always get screwed up independently from
1220          * duplication because the numbers do not seem to be stored on a
1221          * per-database basis. Would the author of pdf support please
1222          * have a look at it?
1223          *
1224         $get_fields = array('page_descr');
1225         $where_fields = array('db_name' => $source_db);
1226         $new_fields = array('db_name' => $target_db);
1227         $last_id = Table::duplicateInfo(
1228             'pdfwork',
1229             'pdf_pages',
1230             $get_fields,
1231             $where_fields,
1232             $new_fields
1233         );
1234
1235         if (isset($last_id) && $last_id >= 0) {
1236             $get_fields = array('x', 'y');
1237             $where_fields = array(
1238                 'db_name' => $source_db,
1239                 'table_name' => $source_table
1240             );
1241             $new_fields = array(
1242                 'db_name' => $target_db,
1243                 'table_name' => $target_table,
1244                 'pdf_page_number' => $last_id
1245             );
1246             Table::duplicateInfo(
1247                 'pdfwork',
1248                 'table_coords',
1249                 $get_fields,
1250                 $where_fields,
1251                 $new_fields
1252             );
1253         }
1254          */
1255
1256         return true;
1257     }
1258
1259     /**
1260      * checks if given name is a valid table name,
1261      * currently if not empty, trailing spaces, '.', '/' and '\'
1262      *
1263      * @param string  $table_name    name to check
1264      * @param boolean $is_backquoted whether this name is used inside backquotes or not
1265      *
1266      * @todo add check for valid chars in filename on current system/os
1267      * @see  https://dev.mysql.com/doc/refman/5.0/en/legal-names.html
1268      *
1269      * @return boolean whether the string is valid or not
1270      */
1271     static function isValidName($table_name, $is_backquoted = false)
1272     {
1273         if ($table_name !== rtrim($table_name)) {
1274             // trailing spaces not allowed even in backquotes
1275             return false;
1276         }
1277
1278         if (strlen($table_name) === 0) {
1279             // zero length
1280             return false;
1281         }
1282
1283         if (! $is_backquoted && $table_name !== trim($table_name)) {
1284             // spaces at the start or in between only allowed inside backquotes
1285             return false;
1286         }
1287
1288         if (! $is_backquoted && preg_match('/^[a-zA-Z0-9_$]+$/', $table_name)) {
1289             // only allow the above regex in unquoted identifiers
1290             // see : https://dev.mysql.com/doc/refman/5.7/en/identifiers.html
1291             return true;
1292         } else if ($is_backquoted) {
1293             // If backquoted, all characters should be allowed (except w/ trailing spaces)
1294             return true;
1295         }
1296
1297         // If not backquoted and doesn't follow the above regex
1298         return false;
1299     }
1300
1301     /**
1302      * renames table
1303      *
1304      * @param string $new_name new table name
1305      * @param string $new_db   new database name
1306      *
1307      * @return bool success
1308      */
1309     public function rename($new_name, $new_db = null)
1310     {
1311         if ($GLOBALS['dbi']->getLowerCaseNames() === '1') {
1312             $new_name = strtolower($new_name);
1313         }
1314
1315         if (null !== $new_db && $new_db !== $this->getDbName()) {
1316             // Ensure the target is valid
1317             if (! $GLOBALS['dblist']->databases->exists($new_db)) {
1318                 $this->errors[] = __('Invalid database:') . ' ' . $new_db;
1319                 return false;
1320             }
1321         } else {
1322             $new_db = $this->getDbName();
1323         }
1324
1325         $new_table = new Table($new_name, $new_db);
1326
1327         if ($this->getFullName() === $new_table->getFullName()) {
1328             return true;
1329         }
1330
1331         // Allow whitespaces (not trailing) in $new_name,
1332         // since we are using $backquoted in getting the fullName of table
1333         // below to be used in the query
1334         if (! Table::isValidName($new_name, true)) {
1335             $this->errors[] = __('Invalid table name:') . ' '
1336                 . $new_table->getFullName();
1337             return false;
1338         }
1339
1340         // If the table is moved to a different database drop its triggers first
1341         $triggers = $this->_dbi->getTriggers(
1342             $this->getDbName(), $this->getName(), ''
1343         );
1344         $handle_triggers = $this->getDbName() != $new_db && $triggers;
1345         if ($handle_triggers) {
1346             foreach ($triggers as $trigger) {
1347                 $sql = 'DROP TRIGGER IF EXISTS '
1348                     . Util::backquote($this->getDbName())
1349                     . '.' . Util::backquote($trigger['name']) . ';';
1350                 $this->_dbi->query($sql);
1351             }
1352         }
1353
1354         /*
1355          * tested also for a view, in MySQL 5.0.92, 5.1.55 and 5.5.13
1356          */
1357         $GLOBALS['sql_query'] = '
1358             RENAME TABLE ' . $this->getFullName(true) . '
1359                   TO ' . $new_table->getFullName(true) . ';';
1360         // I don't think a specific error message for views is necessary
1361         if (! $this->_dbi->query($GLOBALS['sql_query'])) {
1362             // Restore triggers in the old database
1363             if ($handle_triggers) {
1364                 $this->_dbi->selectDb($this->getDbName());
1365                 foreach ($triggers as $trigger) {
1366                     $this->_dbi->query($trigger['create']);
1367                 }
1368             }
1369             $this->errors[] = sprintf(
1370                 __('Failed to rename table %1$s to %2$s!'),
1371                 $this->getFullName(),
1372                 $new_table->getFullName()
1373             );
1374             return false;
1375         }
1376
1377         $old_name = $this->getName();
1378         $old_db = $this->getDbName();
1379         $this->_name = $new_name;
1380         $this->_db_name = $new_db;
1381
1382         // Renable table in configuration storage
1383         PMA_REL_renameTable(
1384             $old_db, $new_db,
1385             $old_name, $new_name
1386         );
1387
1388         $this->messages[] = sprintf(
1389             __('Table %1$s has been renamed to %2$s.'),
1390             htmlspecialchars($old_name),
1391             htmlspecialchars($new_name)
1392         );
1393         return true;
1394     }
1395
1396     /**
1397      * Get all unique columns
1398      *
1399      * returns an array with all columns with unique content, in fact these are
1400      * all columns being single indexed in PRIMARY or UNIQUE
1401      *
1402      * e.g.
1403      *  - PRIMARY(id) // id
1404      *  - UNIQUE(name) // name
1405      *  - PRIMARY(fk_id1, fk_id2) // NONE
1406      *  - UNIQUE(x,y) // NONE
1407      *
1408      * @param bool $backquoted whether to quote name with backticks ``
1409      * @param bool $fullName   whether to include full name of the table as a prefix
1410      *
1411      * @return array
1412      */
1413     public function getUniqueColumns($backquoted = true, $fullName = true)
1414     {
1415         $sql = $this->_dbi->getTableIndexesSql(
1416             $this->getDbName(),
1417             $this->getName(),
1418             'Non_unique = 0'
1419         );
1420         $uniques = $this->_dbi->fetchResult(
1421             $sql,
1422             array('Key_name', null),
1423             'Column_name'
1424         );
1425
1426         $return = array();
1427         foreach ($uniques as $index) {
1428             if (count($index) > 1) {
1429                 continue;
1430             }
1431             if ($fullName) {
1432                 $possible_column = $this->getFullName($backquoted) . '.';
1433             } else {
1434                 $possible_column = '';
1435             }
1436             if ($backquoted) {
1437                 $possible_column .= Util::backquote($index[0]);
1438             } else {
1439                 $possible_column .= $index[0];
1440             }
1441             // a column might have a primary and an unique index on it
1442             if (! in_array($possible_column, $return)) {
1443                 $return[] = $possible_column;
1444             }
1445         }
1446
1447         return $return;
1448     }
1449
1450     /**
1451      * Formats lists of columns
1452      *
1453      * returns an array with all columns that make use of an index
1454      *
1455      * e.g. index(col1, col2) would return col1, col2
1456      *
1457      * @param array $indexed    column data
1458      * @param bool  $backquoted whether to quote name with backticks ``
1459      * @param bool  $fullName   whether to include full name of the table as a prefix
1460      *
1461      * @return array
1462      */
1463     private function _formatColumns($indexed, $backquoted, $fullName)
1464     {
1465         $return = array();
1466         foreach ($indexed as $column) {
1467             $return[] = ($fullName ? $this->getFullName($backquoted) . '.' : '')
1468                 . ($backquoted ? Util::backquote($column) : $column);
1469         }
1470
1471         return $return;
1472     }
1473
1474     /**
1475      * Get all indexed columns
1476      *
1477      * returns an array with all columns that make use of an index
1478      *
1479      * e.g. index(col1, col2) would return col1, col2
1480      *
1481      * @param bool $backquoted whether to quote name with backticks ``
1482      * @param bool $fullName   whether to include full name of the table as a prefix
1483      *
1484      * @return array
1485      */
1486     public function getIndexedColumns($backquoted = true, $fullName = true)
1487     {
1488         $sql = $this->_dbi->getTableIndexesSql(
1489             $this->getDbName(),
1490             $this->getName(),
1491             ''
1492         );
1493         $indexed = $this->_dbi->fetchResult($sql, 'Column_name', 'Column_name');
1494
1495         return $this->_formatColumns($indexed, $backquoted, $fullName);
1496     }
1497
1498     /**
1499      * Get all columns
1500      *
1501      * returns an array with all columns
1502      *
1503      * @param bool $backquoted whether to quote name with backticks ``
1504      * @param bool $fullName   whether to include full name of the table as a prefix
1505      *
1506      * @return array
1507      */
1508     public function getColumns($backquoted = true, $fullName = true)
1509     {
1510         $sql = 'SHOW COLUMNS FROM ' . $this->getFullName(true);
1511         $indexed = $this->_dbi->fetchResult($sql, 'Field', 'Field');
1512
1513         return $this->_formatColumns($indexed, $backquoted, $fullName);
1514     }
1515
1516     /**
1517      * Get meta info for fields in table
1518      *
1519      * @return mixed
1520      */
1521     public function getColumnsMeta()
1522     {
1523         $move_columns_sql_query = sprintf(
1524             'SELECT * FROM %s.%s LIMIT 1',
1525             Util::backquote($this->_db_name),
1526             Util::backquote($this->_name)
1527         );
1528         $move_columns_sql_result = $this->_dbi->tryQuery($move_columns_sql_query);
1529         if ($move_columns_sql_result !== false) {
1530             return $this->_dbi->getFieldsMeta($move_columns_sql_result);
1531         } else {
1532             // unsure how to reproduce but it was seen on the reporting server
1533             return array();
1534         }
1535     }
1536
1537     /**
1538      * Get non-generated columns in table
1539      *
1540      * @param bool $backquoted whether to quote name with backticks ``
1541      *
1542      * @return array
1543      */
1544     public function getNonGeneratedColumns($backquoted = true)
1545     {
1546         $columns_meta_query = 'SHOW COLUMNS FROM ' . $this->getFullName(true);
1547         $ret = array();
1548
1549         $columns_meta_query_result = $this->_dbi->fetchResult(
1550             $columns_meta_query
1551         );
1552
1553         if ($columns_meta_query_result
1554             && $columns_meta_query_result !== false
1555         ) {
1556             foreach ($columns_meta_query_result as $column) {
1557                 $value = $column['Field'];
1558                 if ($backquoted === true) {
1559                     $value = Util::backquote($value);
1560                 }
1561
1562                 if (strpos($column['Extra'], 'GENERATED') === false) {
1563                     array_push($ret, $value);
1564                 }
1565             }
1566         }
1567
1568         return $ret;
1569     }
1570
1571     /**
1572      * Return UI preferences for this table from phpMyAdmin database.
1573      *
1574      * @return array
1575      */
1576     protected function getUiPrefsFromDb()
1577     {
1578         $cfgRelation = PMA_getRelationsParam();
1579         $pma_table = Util::backquote($cfgRelation['db']) . "."
1580             . Util::backquote($cfgRelation['table_uiprefs']);
1581
1582         // Read from phpMyAdmin database
1583         $sql_query = " SELECT `prefs` FROM " . $pma_table
1584             . " WHERE `username` = '" . $GLOBALS['dbi']->escapeString($GLOBALS['cfg']['Server']['user']) . "'"
1585             . " AND `db_name` = '" . $GLOBALS['dbi']->escapeString($this->_db_name) . "'"
1586             . " AND `table_name` = '" . $GLOBALS['dbi']->escapeString($this->_name) . "'";
1587
1588         $row = $this->_dbi->fetchArray(PMA_queryAsControlUser($sql_query));
1589         if (isset($row[0])) {
1590             return json_decode($row[0], true);
1591         } else {
1592             return array();
1593         }
1594     }
1595
1596     /**
1597      * Save this table's UI preferences into phpMyAdmin database.
1598      *
1599      * @return true|Message
1600      */
1601     protected function saveUiPrefsToDb()
1602     {
1603         $cfgRelation = PMA_getRelationsParam();
1604         $pma_table = Util::backquote($cfgRelation['db']) . "."
1605             . Util::backquote($cfgRelation['table_uiprefs']);
1606
1607         $secureDbName = $GLOBALS['dbi']->escapeString($this->_db_name);
1608
1609         $username = $GLOBALS['cfg']['Server']['user'];
1610         $sql_query = " REPLACE INTO " . $pma_table
1611             . " (username, db_name, table_name, prefs) VALUES ('"
1612             . $GLOBALS['dbi']->escapeString($username) . "', '" . $secureDbName
1613             . "', '" . $GLOBALS['dbi']->escapeString($this->_name) . "', '"
1614             . $GLOBALS['dbi']->escapeString(json_encode($this->uiprefs)) . "')";
1615
1616         $success = $this->_dbi->tryQuery($sql_query, $GLOBALS['controllink']);
1617
1618         if (!$success) {
1619             $message = Message::error(
1620                 __('Could not save table UI preferences!')
1621             );
1622             $message->addMessage(
1623                 Message::rawError(
1624                     $this->_dbi->getError($GLOBALS['controllink'])
1625                 ),
1626                 '<br /><br />'
1627             );
1628             return $message;
1629         }
1630
1631         // Remove some old rows in table_uiprefs if it exceeds the configured
1632         // maximum rows
1633         $sql_query = 'SELECT COUNT(*) FROM ' . $pma_table;
1634         $rows_count = $this->_dbi->fetchValue($sql_query);
1635         $max_rows = $GLOBALS['cfg']['Server']['MaxTableUiprefs'];
1636         if ($rows_count > $max_rows) {
1637             $num_rows_to_delete = $rows_count - $max_rows;
1638             $sql_query
1639                 = ' DELETE FROM ' . $pma_table .
1640                 ' ORDER BY last_update ASC' .
1641                 ' LIMIT ' . $num_rows_to_delete;
1642             $success = $this->_dbi->tryQuery(
1643                 $sql_query, $GLOBALS['controllink']
1644             );
1645
1646             if (!$success) {
1647                 $message = Message::error(
1648                     sprintf(
1649                         __(
1650                             'Failed to cleanup table UI preferences (see ' .
1651                             '$cfg[\'Servers\'][$i][\'MaxTableUiprefs\'] %s)'
1652                         ),
1653                         Util::showDocu('config', 'cfg_Servers_MaxTableUiprefs')
1654                     )
1655                 );
1656                 $message->addMessage(
1657                     Message::rawError(
1658                         $this->_dbi->getError($GLOBALS['controllink'])
1659                     ),
1660                     '<br /><br />'
1661                 );
1662                 return $message;
1663             }
1664         }
1665
1666         return true;
1667     }
1668
1669     /**
1670      * Loads the UI preferences for this table.
1671      * If pmadb and table_uiprefs is set, it will load the UI preferences from
1672      * phpMyAdmin database.
1673      *
1674      * @return void
1675      */
1676     protected function loadUiPrefs()
1677     {
1678         $cfgRelation = PMA_getRelationsParam();
1679         $server_id = $GLOBALS['server'];
1680
1681         // set session variable if it's still undefined
1682         if (!isset($_SESSION['tmpval']['table_uiprefs'][$server_id][$this->_db_name][$this->_name])) {
1683             // check whether we can get from pmadb
1684             $_SESSION['tmpval']['table_uiprefs'][$server_id][$this->_db_name]
1685             [$this->_name] = $cfgRelation['uiprefswork']
1686                 ?  $this->getUiPrefsFromDb()
1687                 : array();
1688         }
1689         $this->uiprefs =& $_SESSION['tmpval']['table_uiprefs'][$server_id]
1690         [$this->_db_name][$this->_name];
1691     }
1692
1693     /**
1694      * Get a property from UI preferences.
1695      * Return false if the property is not found.
1696      * Available property:
1697      * - PROP_SORTED_COLUMN
1698      * - PROP_COLUMN_ORDER
1699      * - PROP_COLUMN_VISIB
1700      *
1701      * @param string $property property
1702      *
1703      * @return mixed
1704      */
1705     public function getUiProp($property)
1706     {
1707         if (! isset($this->uiprefs)) {
1708             $this->loadUiPrefs();
1709         }
1710
1711         // do checking based on property
1712         if ($property == self::PROP_SORTED_COLUMN) {
1713             if (!isset($this->uiprefs[$property])) {
1714                 return false;
1715             }
1716
1717             if (!isset($_REQUEST['discard_remembered_sort'])) {
1718                 // check if the column name exists in this table
1719                 $tmp = explode(' ', $this->uiprefs[$property]);
1720                 $colname = $tmp[0];
1721                 //remove backquoting from colname
1722                 $colname = str_replace('`', '', $colname);
1723                 //get the available column name without backquoting
1724                 $avail_columns = $this->getColumns(false);
1725
1726                 foreach ($avail_columns as $each_col) {
1727                     // check if $each_col ends with $colname
1728                     if (substr_compare(
1729                         $each_col,
1730                         $colname,
1731                         mb_strlen($each_col) - mb_strlen($colname)
1732                     ) === 0
1733                     ) {
1734                         return $this->uiprefs[$property];
1735                     }
1736                 }
1737             }
1738             // remove the property, since it no longer exists in database
1739             $this->removeUiProp(self::PROP_SORTED_COLUMN);
1740             return false;
1741         }
1742
1743         if ($property == self::PROP_COLUMN_ORDER
1744             || $property == self::PROP_COLUMN_VISIB
1745         ) {
1746             if ($this->isView() || !isset($this->uiprefs[$property])) {
1747                 return false;
1748             }
1749
1750             // check if the table has not been modified
1751             if ($this->getStatusInfo('Create_time') == $this->uiprefs['CREATE_TIME']
1752             ) {
1753                 return $this->uiprefs[$property];
1754             }
1755
1756             // remove the property, since the table has been modified
1757             $this->removeUiProp(self::PROP_COLUMN_ORDER);
1758             return false;
1759         }
1760
1761         // default behaviour for other property:
1762         return isset($this->uiprefs[$property]) ? $this->uiprefs[$property] : false;
1763     }
1764
1765     /**
1766      * Set a property from UI preferences.
1767      * If pmadb and table_uiprefs is set, it will save the UI preferences to
1768      * phpMyAdmin database.
1769      * Available property:
1770      * - PROP_SORTED_COLUMN
1771      * - PROP_COLUMN_ORDER
1772      * - PROP_COLUMN_VISIB
1773      *
1774      * @param string $property          Property
1775      * @param mixed  $value             Value for the property
1776      * @param string $table_create_time Needed for PROP_COLUMN_ORDER
1777      *                                  and PROP_COLUMN_VISIB
1778      *
1779      * @return boolean|Message
1780      */
1781     public function setUiProp($property, $value, $table_create_time = null)
1782     {
1783         if (! isset($this->uiprefs)) {
1784             $this->loadUiPrefs();
1785         }
1786         // we want to save the create time if the property is PROP_COLUMN_ORDER
1787         if (! $this->isView()
1788             && ($property == self::PROP_COLUMN_ORDER
1789             || $property == self::PROP_COLUMN_VISIB)
1790         ) {
1791             $curr_create_time = $this->getStatusInfo('CREATE_TIME');
1792             if (isset($table_create_time)
1793                 && $table_create_time == $curr_create_time
1794             ) {
1795                 $this->uiprefs['CREATE_TIME'] = $curr_create_time;
1796             } else {
1797                 // there is no $table_create_time, or
1798                 // supplied $table_create_time is older than current create time,
1799                 // so don't save
1800                 return Message::error(
1801                     sprintf(
1802                         __(
1803                             'Cannot save UI property "%s". The changes made will ' .
1804                             'not be persistent after you refresh this page. ' .
1805                             'Please check if the table structure has been changed.'
1806                         ),
1807                         $property
1808                     )
1809                 );
1810             }
1811         }
1812         // save the value
1813         $this->uiprefs[$property] = $value;
1814
1815         // check if pmadb is set
1816         $cfgRelation = PMA_getRelationsParam();
1817         if ($cfgRelation['uiprefswork']) {
1818             return $this->saveUiprefsToDb();
1819         }
1820         return true;
1821     }
1822
1823     /**
1824      * Remove a property from UI preferences.
1825      *
1826      * @param string $property the property
1827      *
1828      * @return true|Message
1829      */
1830     public function removeUiProp($property)
1831     {
1832         if (! isset($this->uiprefs)) {
1833             $this->loadUiPrefs();
1834         }
1835         if (isset($this->uiprefs[$property])) {
1836             unset($this->uiprefs[$property]);
1837
1838             // check if pmadb is set
1839             $cfgRelation = PMA_getRelationsParam();
1840             if ($cfgRelation['uiprefswork']) {
1841                 return $this->saveUiprefsToDb();
1842             }
1843         }
1844         return true;
1845     }
1846
1847     /**
1848      * Get all column names which are MySQL reserved words
1849      *
1850      * @return array
1851      * @access public
1852      */
1853     public function getReservedColumnNames()
1854     {
1855         $columns = $this->getColumns(false);
1856         $return = array();
1857         foreach ($columns as $column) {
1858             $temp = explode('.', $column);
1859             $column_name = $temp[2];
1860             if (Context::isKeyword($column_name, true)) {
1861                 $return[] = $column_name;
1862             }
1863         }
1864         return $return;
1865     }
1866
1867     /**
1868      * Function to get the name and type of the columns of a table
1869      *
1870      * @return array
1871      */
1872     public function getNameAndTypeOfTheColumns()
1873     {
1874         $columns = array();
1875         foreach ($this->_dbi->getColumnsFull(
1876             $this->_db_name, $this->_name
1877         ) as $row) {
1878             if (preg_match('@^(set|enum)\((.+)\)$@i', $row['Type'], $tmp)) {
1879                 $tmp[2] = mb_substr(
1880                     preg_replace('@([^,])\'\'@', '\\1\\\'', ',' . $tmp[2]), 1
1881                 );
1882                 $columns[$row['Field']] = $tmp[1] . '('
1883                     . str_replace(',', ', ', $tmp[2]) . ')';
1884             } else {
1885                 $columns[$row['Field']] = $row['Type'];
1886             }
1887         }
1888         return $columns;
1889     }
1890
1891     /**
1892      * Get index with index name
1893      *
1894      * @param string $index Index name
1895      *
1896      * @return Index
1897      */
1898     public function getIndex($index)
1899     {
1900         return Index::singleton($this->_db_name, $this->_name, $index);
1901     }
1902
1903     /**
1904      * Function to get the sql query for index creation or edit
1905      *
1906      * @param Index $index  current index
1907      * @param bool  &$error whether error occurred or not
1908      *
1909      * @return string
1910      */
1911     public function getSqlQueryForIndexCreateOrEdit($index, &$error)
1912     {
1913         // $sql_query is the one displayed in the query box
1914         $sql_query = sprintf(
1915             'ALTER TABLE %s.%s',
1916             Util::backquote($this->_db_name),
1917             Util::backquote($this->_name)
1918         );
1919
1920         // Drops the old index
1921         if (! empty($_REQUEST['old_index'])) {
1922             if ($_REQUEST['old_index'] == 'PRIMARY') {
1923                 $sql_query .= ' DROP PRIMARY KEY,';
1924             } else {
1925                 $sql_query .= sprintf(
1926                     ' DROP INDEX %s,',
1927                     Util::backquote($_REQUEST['old_index'])
1928                 );
1929             }
1930         } // end if
1931
1932         // Builds the new one
1933         switch ($index->getChoice()) {
1934         case 'PRIMARY':
1935             if ($index->getName() == '') {
1936                 $index->setName('PRIMARY');
1937             } elseif ($index->getName() != 'PRIMARY') {
1938                 $error = Message::error(
1939                     __('The name of the primary key must be "PRIMARY"!')
1940                 );
1941             }
1942             $sql_query .= ' ADD PRIMARY KEY';
1943             break;
1944         case 'FULLTEXT':
1945         case 'UNIQUE':
1946         case 'INDEX':
1947         case 'SPATIAL':
1948             if ($index->getName() == 'PRIMARY') {
1949                 $error = Message::error(
1950                     __('Can\'t rename index to PRIMARY!')
1951                 );
1952             }
1953             $sql_query .= sprintf(
1954                 ' ADD %s ',
1955                 $index->getChoice()
1956             );
1957             if ($index->getName()) {
1958                 $sql_query .= Util::backquote($index->getName());
1959             }
1960             break;
1961         } // end switch
1962
1963         $index_fields = array();
1964         foreach ($index->getColumns() as $key => $column) {
1965             $index_fields[$key] = Util::backquote($column->getName());
1966             if ($column->getSubPart()) {
1967                 $index_fields[$key] .= '(' . $column->getSubPart() . ')';
1968             }
1969         } // end while
1970
1971         if (empty($index_fields)) {
1972             $error = Message::error(__('No index parts defined!'));
1973         } else {
1974             $sql_query .= ' (' . implode(', ', $index_fields) . ')';
1975         }
1976
1977         $keyBlockSizes = $index->getKeyBlockSize();
1978         if (! empty($keyBlockSizes)) {
1979             $sql_query .= sprintf(
1980                 ' KEY_BLOCK_SIZE = ',
1981                 $GLOBALS['dbi']->escapeString($keyBlockSizes)
1982             );
1983         }
1984
1985         // specifying index type is allowed only for primary, unique and index only
1986         $type = $index->getType();
1987         if ($index->getChoice() != 'SPATIAL'
1988             && $index->getChoice() != 'FULLTEXT'
1989             && in_array($type, Index::getIndexTypes())
1990         ) {
1991             $sql_query .= ' USING ' . $type;
1992         }
1993
1994         $parser = $index->getParser();
1995         if ($index->getChoice() == 'FULLTEXT' && ! empty($parser)) {
1996             $sql_query .= ' WITH PARSER ' . $GLOBALS['dbi']->escapeString($parser);
1997         }
1998
1999         $comment = $index->getComment();
2000         if (! empty($comment)) {
2001             $sql_query .= sprintf(
2002                 " COMMENT '%s'",
2003                 $GLOBALS['dbi']->escapeString($comment)
2004             );
2005         }
2006
2007         $sql_query .= ';';
2008
2009         return $sql_query;
2010     }
2011
2012     /**
2013      * Function to handle update for display field
2014      *
2015      * @param string $disp          current display field
2016      * @param string $display_field display field
2017      * @param array  $cfgRelation   configuration relation
2018      *
2019      * @return boolean True on update succeed or False on failure
2020      */
2021     public function updateDisplayField($disp, $display_field, $cfgRelation)
2022     {
2023         $upd_query = false;
2024         if ($disp) {
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             } elseif ($disp != $display_field) {
2034                 $upd_query = 'UPDATE '
2035                     . Util::backquote($GLOBALS['cfgRelation']['db'])
2036                     . '.' . Util::backquote($cfgRelation['table_info'])
2037                     . ' SET display_field = \''
2038                     . $GLOBALS['dbi']->escapeString($display_field) . '\''
2039                     . ' WHERE db_name  = \''
2040                     . $GLOBALS['dbi']->escapeString($this->_db_name) . '\''
2041                     . ' AND table_name = \''
2042                     . $GLOBALS['dbi']->escapeString($this->_name) . '\'';
2043             }
2044         } elseif ($display_field != '') {
2045             $upd_query = 'INSERT INTO '
2046                 . Util::backquote($GLOBALS['cfgRelation']['db'])
2047                 . '.' . Util::backquote($cfgRelation['table_info'])
2048                 . '(db_name, table_name, display_field) VALUES('
2049                 . '\'' . $GLOBALS['dbi']->escapeString($this->_db_name) . '\','
2050                 . '\'' . $GLOBALS['dbi']->escapeString($this->_name) . '\','
2051                 . '\'' . $GLOBALS['dbi']->escapeString($display_field) . '\')';
2052         }
2053
2054         if ($upd_query) {
2055             $this->_dbi->query(
2056                 $upd_query,
2057                 $GLOBALS['controllink'],
2058                 0,
2059                 false
2060             );
2061             return true;
2062         }
2063         return false;
2064     }
2065
2066     /**
2067      * Function to get update query for updating internal relations
2068      *
2069      * @param array      $multi_edit_columns_name multi edit column names
2070      * @param array      $destination_db          destination tables
2071      * @param array      $destination_table       destination tables
2072      * @param array      $destination_column      destination columns
2073      * @param array      $cfgRelation             configuration relation
2074      * @param array|null $existrel                db, table, column
2075      *
2076      * @return boolean
2077      */
2078     public function updateInternalRelations($multi_edit_columns_name,
2079         $destination_db, $destination_table, $destination_column,
2080         $cfgRelation, $existrel
2081     ) {
2082         $updated = false;
2083         foreach ($destination_db as $master_field_md5 => $foreign_db) {
2084             $upd_query = null;
2085             // Map the fieldname's md5 back to its real name
2086             $master_field = $multi_edit_columns_name[$master_field_md5];
2087             $foreign_table = $destination_table[$master_field_md5];
2088             $foreign_field = $destination_column[$master_field_md5];
2089             if (! empty($foreign_db)
2090                 && ! empty($foreign_table)
2091                 && ! empty($foreign_field)
2092             ) {
2093                 if (! isset($existrel[$master_field])) {
2094                     $upd_query  = 'INSERT INTO '
2095                         . Util::backquote($GLOBALS['cfgRelation']['db'])
2096                         . '.' . Util::backquote($cfgRelation['relation'])
2097                         . '(master_db, master_table, master_field, foreign_db,'
2098                         . ' foreign_table, foreign_field)'
2099                         . ' values('
2100                         . '\'' . $GLOBALS['dbi']->escapeString($this->_db_name) . '\', '
2101                         . '\'' . $GLOBALS['dbi']->escapeString($this->_name) . '\', '
2102                         . '\'' . $GLOBALS['dbi']->escapeString($master_field) . '\', '
2103                         . '\'' . $GLOBALS['dbi']->escapeString($foreign_db) . '\', '
2104                         . '\'' . $GLOBALS['dbi']->escapeString($foreign_table) . '\','
2105                         . '\'' . $GLOBALS['dbi']->escapeString($foreign_field) . '\')';
2106
2107                 } elseif ($existrel[$master_field]['foreign_db'] != $foreign_db
2108                     || $existrel[$master_field]['foreign_table'] != $foreign_table
2109                     || $existrel[$master_field]['foreign_field'] != $foreign_field
2110                 ) {
2111                     $upd_query  = 'UPDATE '
2112                         . Util::backquote($GLOBALS['cfgRelation']['db'])
2113                         . '.' . Util::backquote($cfgRelation['relation'])
2114                         . ' SET foreign_db       = \''
2115                         . $GLOBALS['dbi']->escapeString($foreign_db) . '\', '
2116                         . ' foreign_table    = \''
2117                         . $GLOBALS['dbi']->escapeString($foreign_table) . '\', '
2118                         . ' foreign_field    = \''
2119                         . $GLOBALS['dbi']->escapeString($foreign_field) . '\' '
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             } elseif (isset($existrel[$master_field])) {
2128                 $upd_query = 'DELETE FROM '
2129                     . Util::backquote($GLOBALS['cfgRelation']['db'])
2130                     . '.' . Util::backquote($cfgRelation['relation'])
2131                     . ' WHERE master_db  = \''
2132                     . $GLOBALS['dbi']->escapeString($this->_db_name) . '\''
2133                     . ' AND master_table = \''
2134                     . $GLOBALS['dbi']->escapeString($this->_name) . '\''
2135                     . ' AND master_field = \''
2136                     . $GLOBALS['dbi']->escapeString($master_field) . '\'';
2137             } // end if... else....
2138
2139             if (isset($upd_query)) {
2140                 $this->_dbi->query(
2141                     $upd_query,
2142                     $GLOBALS['controllink'],
2143                     0,
2144                     false
2145                 );
2146                 $updated = true;
2147             }
2148         }
2149         return $updated;
2150     }
2151
2152     /**
2153      * Function to handle foreign key updates
2154      *
2155      * @param array  $destination_foreign_db     destination foreign database
2156      * @param array  $multi_edit_columns_name    multi edit column names
2157      * @param array  $destination_foreign_table  destination foreign table
2158      * @param array  $destination_foreign_column destination foreign column
2159      * @param array  $options_array              options array
2160      * @param string $table                      current table
2161      * @param array  $existrel_foreign           db, table, column
2162      *
2163      * @return array
2164      */
2165     public function updateForeignKeys($destination_foreign_db,
2166         $multi_edit_columns_name, $destination_foreign_table,
2167         $destination_foreign_column, $options_array, $table, $existrel_foreign
2168     ) {
2169         $html_output = '';
2170         $preview_sql_data = '';
2171         $display_query = '';
2172         $seen_error = false;
2173
2174         foreach ($destination_foreign_db as $master_field_md5 => $foreign_db) {
2175             $create = false;
2176             $drop = false;
2177
2178             // Map the fieldname's md5 back to its real name
2179             $master_field = $multi_edit_columns_name[$master_field_md5];
2180
2181             $foreign_table = $destination_foreign_table[$master_field_md5];
2182             $foreign_field = $destination_foreign_column[$master_field_md5];
2183
2184             if (isset($existrel_foreign[$master_field_md5]['ref_db_name'])) {
2185                 $ref_db_name = $existrel_foreign[$master_field_md5]['ref_db_name'];
2186             } else {
2187                 $ref_db_name = $GLOBALS['db'];
2188             }
2189
2190             $empty_fields = false;
2191             foreach ($master_field as $key => $one_field) {
2192                 if ((! empty($one_field) && empty($foreign_field[$key]))
2193                     || (empty($one_field) && ! empty($foreign_field[$key]))
2194                 ) {
2195                     $empty_fields = true;
2196                 }
2197
2198                 if (empty($one_field) && empty($foreign_field[$key])) {
2199                     unset($master_field[$key]);
2200                     unset($foreign_field[$key]);
2201                 }
2202             }
2203
2204             if (! empty($foreign_db)
2205                 && ! empty($foreign_table)
2206                 && ! $empty_fields
2207             ) {
2208                 if (isset($existrel_foreign[$master_field_md5])) {
2209                     $constraint_name
2210                         = $existrel_foreign[$master_field_md5]['constraint'];
2211                     $on_delete = !empty(
2212                         $existrel_foreign[$master_field_md5]['on_delete']
2213                     )
2214                         ? $existrel_foreign[$master_field_md5]['on_delete']
2215                         : 'RESTRICT';
2216                     $on_update = ! empty(
2217                         $existrel_foreign[$master_field_md5]['on_update']
2218                     )
2219                         ? $existrel_foreign[$master_field_md5]['on_update']
2220                         : 'RESTRICT';
2221
2222                     if ($ref_db_name != $foreign_db
2223                         || $existrel_foreign[$master_field_md5]['ref_table_name'] != $foreign_table
2224                         || $existrel_foreign[$master_field_md5]['ref_index_list'] != $foreign_field
2225                         || $existrel_foreign[$master_field_md5]['index_list'] != $master_field
2226                         || $_REQUEST['constraint_name'][$master_field_md5] != $constraint_name
2227                         || ($_REQUEST['on_delete'][$master_field_md5] != $on_delete)
2228                         || ($_REQUEST['on_update'][$master_field_md5] != $on_update)
2229                     ) {
2230                         // another foreign key is already defined for this field
2231                         // or an option has been changed for ON DELETE or ON UPDATE
2232                         $drop = true;
2233                         $create = true;
2234                     } // end if... else....
2235                 } else {
2236                     // no key defined for this field(s)
2237                     $create = true;
2238                 }
2239             } elseif (isset($existrel_foreign[$master_field_md5])) {
2240                 $drop = true;
2241             } // end if... else....
2242
2243             $tmp_error_drop = false;
2244             if ($drop) {
2245                 $drop_query = 'ALTER TABLE ' . Util::backquote($table)
2246                     . ' DROP FOREIGN KEY '
2247                     . Util::backquote(
2248                         $existrel_foreign[$master_field_md5]['constraint']
2249                     )
2250                     . ';';
2251
2252                 if (! isset($_REQUEST['preview_sql'])) {
2253                     $display_query .= $drop_query . "\n";
2254                     $this->_dbi->tryQuery($drop_query);
2255                     $tmp_error_drop = $this->_dbi->getError();
2256
2257                     if (! empty($tmp_error_drop)) {
2258                         $seen_error = true;
2259                         $html_output .= Util::mysqlDie(
2260                             $tmp_error_drop, $drop_query, false, '', false
2261                         );
2262                         continue;
2263                     }
2264                 } else {
2265                     $preview_sql_data .= $drop_query . "\n";
2266                 }
2267             }
2268             $tmp_error_create = false;
2269             if (!$create) {
2270                 continue;
2271             }
2272
2273             $create_query = $this->_getSQLToCreateForeignKey(
2274                 $table, $master_field, $foreign_db, $foreign_table, $foreign_field,
2275                 $_REQUEST['constraint_name'][$master_field_md5],
2276                 $options_array[$_REQUEST['on_delete'][$master_field_md5]],
2277                 $options_array[$_REQUEST['on_update'][$master_field_md5]]
2278             );
2279
2280             if (! isset($_REQUEST['preview_sql'])) {
2281                 $display_query .= $create_query . "\n";
2282                 $this->_dbi->tryQuery($create_query);
2283                 $tmp_error_create = $this->_dbi->getError();
2284                 if (! empty($tmp_error_create)) {
2285                     $seen_error = true;
2286
2287                     if (substr($tmp_error_create, 1, 4) == '1005') {
2288                         $message = Message::error(
2289                             __(
2290                                 'Error creating foreign key on %1$s (check data ' .
2291                                 'types)'
2292                             )
2293                         );
2294                         $message->addParam(implode(', ', $master_field));
2295                         $html_output .= $message->getDisplay();
2296                     } else {
2297                         $html_output .= Util::mysqlDie(
2298                             $tmp_error_create, $create_query, false, '', false
2299                         );
2300                     }
2301                     $html_output .= Util::showMySQLDocu(
2302                         'InnoDB_foreign_key_constraints'
2303                     ) . "\n";
2304                 }
2305             } else {
2306                 $preview_sql_data .= $create_query . "\n";
2307             }
2308
2309             // this is an alteration and the old constraint has been dropped
2310             // without creation of a new one
2311             if ($drop && $create && empty($tmp_error_drop)
2312                 && ! empty($tmp_error_create)
2313             ) {
2314                 // a rollback may be better here
2315                 $sql_query_recreate = '# Restoring the dropped constraint...' . "\n";
2316                 $sql_query_recreate .= $this->_getSQLToCreateForeignKey(
2317                     $table,
2318                     $master_field,
2319                     $existrel_foreign[$master_field_md5]['ref_db_name'],
2320                     $existrel_foreign[$master_field_md5]['ref_table_name'],
2321                     $existrel_foreign[$master_field_md5]['ref_index_list'],
2322                     $existrel_foreign[$master_field_md5]['constraint'],
2323                     $options_array[$existrel_foreign[$master_field_md5]['on_delete']],
2324                     $options_array[$existrel_foreign[$master_field_md5]['on_update']]
2325                 );
2326                 if (! isset($_REQUEST['preview_sql'])) {
2327                     $display_query .= $sql_query_recreate . "\n";
2328                     $this->_dbi->tryQuery($sql_query_recreate);
2329                 } else {
2330                     $preview_sql_data .= $sql_query_recreate;
2331                 }
2332             }
2333         } // end foreach
2334
2335         return array(
2336             $html_output,
2337             $preview_sql_data,
2338             $display_query,
2339             $seen_error
2340         );
2341     }
2342
2343     /**
2344      * Returns the SQL query for foreign key constraint creation
2345      *
2346      * @param string $table        table name
2347      * @param array  $field        field names
2348      * @param string $foreignDb    foreign database name
2349      * @param string $foreignTable foreign table name
2350      * @param array  $foreignField foreign field names
2351      * @param string $name         name of the constraint
2352      * @param string $onDelete     on delete action
2353      * @param string $onUpdate     on update action
2354      *
2355      * @return string SQL query for foreign key constraint creation
2356      */
2357     private function _getSQLToCreateForeignKey(
2358         $table,
2359         $field,
2360         $foreignDb,
2361         $foreignTable,
2362         $foreignField,
2363         $name = null,
2364         $onDelete = null,
2365         $onUpdate = null
2366     ) {
2367         $sql_query  = 'ALTER TABLE ' . Util::backquote($table) . ' ADD ';
2368         // if user entered a constraint name
2369         if (! empty($name)) {
2370             $sql_query .= ' CONSTRAINT ' . Util::backquote($name);
2371         }
2372
2373         foreach ($field as $key => $one_field) {
2374             $field[$key] = Util::backquote($one_field);
2375         }
2376         foreach ($foreignField as $key => $one_field) {
2377             $foreignField[$key] = Util::backquote($one_field);
2378         }
2379         $sql_query .= ' FOREIGN KEY (' . implode(', ', $field) . ') REFERENCES '
2380             . ($this->_db_name != $foreignDb
2381                 ? Util::backquote($foreignDb) . '.' : '')
2382             . Util::backquote($foreignTable)
2383             . '(' . implode(', ', $foreignField) . ')';
2384
2385         if (! empty($onDelete)) {
2386             $sql_query .= ' ON DELETE ' . $onDelete;
2387         }
2388         if (! empty($onUpdate)) {
2389             $sql_query .= ' ON UPDATE ' . $onUpdate;
2390         }
2391         $sql_query .= ';';
2392
2393         return $sql_query;
2394     }
2395
2396     /**
2397      * Returns the generation expression for virtual columns
2398      *
2399      * @param string $column name of the column
2400      *
2401      * @return array|boolean associative array of column name and their expressions
2402      *                       or false on failure
2403      */
2404     public function getColumnGenerationExpression($column = null)
2405     {
2406         $serverType = Util::getServerType();
2407         if ($serverType == 'MySQL'
2408             && PMA_MYSQL_INT_VERSION > 50705
2409             && ! $GLOBALS['cfg']['Server']['DisableIS']
2410         ) {
2411             $sql
2412                 = "SELECT
2413                 `COLUMN_NAME` AS `Field`,
2414                 `GENERATION_EXPRESSION` AS `Expression`
2415                 FROM
2416                 `information_schema`.`COLUMNS`
2417                 WHERE
2418                 `TABLE_SCHEMA` = '" . $GLOBALS['dbi']->escapeString($this->_db_name) . "'
2419                 AND `TABLE_NAME` = '" . $GLOBALS['dbi']->escapeString($this->_name) . "'";
2420             if ($column != null) {
2421                 $sql .= " AND  `COLUMN_NAME` = '" . $GLOBALS['dbi']->escapeString($column)
2422                     . "'";
2423             }
2424             $columns = $this->_dbi->fetchResult($sql, 'Field', 'Expression');
2425             return $columns;
2426         }
2427
2428         $createTable = $this->showCreate();
2429         if (!$createTable) {
2430             return false;
2431         }
2432
2433         $parser = new Parser($createTable);
2434         /**
2435          * @var \PhpMyAdmin\SqlParser\Statements\CreateStatement $stmt
2436         */
2437         $stmt = $parser->statements[0];
2438         $fields = \PhpMyAdmin\SqlParser\Utils\Table::getFields($stmt);
2439         if ($column != null) {
2440             $expression = isset($fields[$column]['expr']) ?
2441                 substr($fields[$column]['expr'], 1, -1) : '';
2442             return array($column => $expression);
2443         }
2444
2445         $ret = array();
2446         foreach ($fields as $field => $options) {
2447             if (isset($options['expr'])) {
2448                 $ret[$field] = substr($options['expr'], 1, -1);
2449             }
2450         }
2451         return $ret;
2452     }
2453
2454     /**
2455      * Returns the CREATE statement for this table
2456      *
2457      * @return mixed
2458      */
2459     public function showCreate()
2460     {
2461         return $this->_dbi->fetchValue(
2462             'SHOW CREATE TABLE ' . Util::backquote($this->_db_name) . '.'
2463             . Util::backquote($this->_name),
2464             0, 1
2465         );
2466     }
2467
2468     /**
2469      * Returns the real row count for a table
2470      *
2471      * @return number
2472      */
2473     public function getRealRowCountTable()
2474     {
2475         // SQL query to get row count for a table.
2476         $result = $this->_dbi->fetchSingleRow(
2477             sprintf(
2478                 'SELECT COUNT(*) AS %s FROM %s.%s',
2479                 Util::backquote('row_count'),
2480                 Util::backquote($this->_db_name),
2481                 Util::backquote($this->_name)
2482             )
2483         );
2484         return $result['row_count'];
2485     }
2486
2487     /**
2488      * Get columns with indexes
2489      *
2490      * @param int $types types bitmask
2491      *
2492      * @return array an array of columns
2493      */
2494     public function getColumnsWithIndex($types)
2495     {
2496         $columns_with_index = array();
2497         foreach (
2498             Index::getFromTableByChoice(
2499                 $this->_name,
2500                 $this->_db_name,
2501                 $types
2502             ) as $index
2503         ) {
2504             $columns = $index->getColumns();
2505             foreach ($columns as $column_name => $dummy) {
2506                 $columns_with_index[] = $column_name;
2507             }
2508         }
2509         return $columns_with_index;
2510     }
2511 }