libraries/classes/Controllers/Table/StructureController.php

   1 <?php
   2
   3 declare(strict_types=1);
   4
   5 namespace PhpMyAdmin\Controllers\Table;
   6
   7 use PhpMyAdmin\Charsets;
   8 use PhpMyAdmin\CheckUserPrivileges;
   9 use PhpMyAdmin\Config\PageSettings;
  10 use PhpMyAdmin\Controllers\SqlController;
  11 use PhpMyAdmin\Core;
  12 use PhpMyAdmin\CreateAddField;
  13 use PhpMyAdmin\Database\CentralColumns;
  14 use PhpMyAdmin\DatabaseInterface;
  15 use PhpMyAdmin\DbTableExists;
  16 use PhpMyAdmin\Engines\Innodb;
  17 use PhpMyAdmin\Html\Generator;
  18 use PhpMyAdmin\Index;
  19 use PhpMyAdmin\Message;
  20 use PhpMyAdmin\Operations;
  21 use PhpMyAdmin\ParseAnalyze;
  22 use PhpMyAdmin\Partition;
  23 use PhpMyAdmin\Query\Utilities;
  24 use PhpMyAdmin\Relation;
  25 use PhpMyAdmin\RelationCleanup;
  26 use PhpMyAdmin\Response;
  27 use PhpMyAdmin\Sql;
  28 use PhpMyAdmin\SqlParser\Context;
  29 use PhpMyAdmin\SqlParser\Parser;
  30 use PhpMyAdmin\SqlParser\Statements\CreateStatement;
  31 use PhpMyAdmin\StorageEngine;
  32 use PhpMyAdmin\Table;
  33 use PhpMyAdmin\Table\ColumnsDefinition;
  34 use PhpMyAdmin\TablePartitionDefinition;
  35 use PhpMyAdmin\Template;
  36 use PhpMyAdmin\Tracker;
  37 use PhpMyAdmin\Transformations;
  38 use PhpMyAdmin\Url;
  39 use PhpMyAdmin\Util;
  40 use stdClass;
  41 use function array_keys;
  42 use function array_splice;
  43 use function count;
  44 use function implode;
  45 use function in_array;
  46 use function is_array;
  47 use function is_string;
  48 use function mb_strpos;
  49 use function mb_strtoupper;
  50 use function sprintf;
  51 use function str_replace;
  52 use function strlen;
  53 use function strpos;
  54 use function strrpos;
  55 use function substr;
  56 use function trim;
  57
  58 /**
  59  * Displays table structure infos like columns, indexes, size, rows
  60  * and allows manipulation of indexes and columns.
  61  */
  62 class StructureController extends AbstractController
  63 {
  64     /** @var Table  The table object */
  65     protected $tableObj;
  66
  67     /** @var CreateAddField */
  68     private $createAddField;
  69
  70     /** @var Relation */
  71     private $relation;
  72
  73     /** @var Transformations */
  74     private $transformations;
  75
  76     /** @var RelationCleanup */
  77     private $relationCleanup;
  78
  79     /** @var DatabaseInterface */
  80     private $dbi;
  81
  82     /**
  83      * @param Response          $response
  84      * @param string            $db       Database name
  85      * @param string            $table    Table name
  86      * @param DatabaseInterface $dbi
  87      */
  88     public function __construct(
  89         $response,
  90         Template $template,
  91         $db,
  92         $table,
  93         Relation $relation,
  94         Transformations $transformations,
  95         CreateAddField $createAddField,
  96         RelationCleanup $relationCleanup,
  97         $dbi
  98     ) {
  99         parent::__construct($response, $template, $db, $table);
 100         $this->createAddField = $createAddField;
 101         $this->relation = $relation;
 102         $this->transformations = $transformations;
 103         $this->relationCleanup = $relationCleanup;
 104         $this->dbi = $dbi;
 105
 106         $this->tableObj = $this->dbi->getTable($this->db, $this->table);
 107     }
 108
 109     public function index(): void
 110     {
 111         global $reread_info, $showtable, $db, $table, $cfg, $err_url;
 112         global $tbl_is_view, $tbl_storage_engine, $tbl_collation, $table_info_num_rows;
 113
 114         $this->dbi->selectDb($this->db);
 115         $reread_info = $this->tableObj->getStatusInfo(null, true);
 116         $showtable = $this->tableObj->getStatusInfo(
 117             null,
 118             (isset($reread_info) && $reread_info)
 119         );
 120
 121         if ($this->tableObj->isView()) {
 122             $tbl_is_view = true;
 123             $tbl_storage_engine = __('View');
 124         } else {
 125             $tbl_is_view = false;
 126             $tbl_storage_engine = $this->tableObj->getStorageEngine();
 127         }
 128
 129         $tbl_collation = $this->tableObj->getCollation();
 130         $table_info_num_rows = $this->tableObj->getNumRows();
 131
 132         $pageSettings = new PageSettings('TableStructure');
 133         $this->response->addHTML($pageSettings->getErrorHTML());
 134         $this->response->addHTML($pageSettings->getHTML());
 135
 136         $checkUserPrivileges = new CheckUserPrivileges($this->dbi);
 137         $checkUserPrivileges->getPrivileges();
 138
 139         $this->addScriptFiles(['table/structure.js', 'indexes.js']);
 140
 141         $cfgRelation = $this->relation->getRelationsParam();
 142
 143         Util::checkParameters(['db', 'table']);
 144
 145         $isSystemSchema = Utilities::isSystemSchema($db);
 146         $url_params = ['db' => $db, 'table' => $table];
 147         $err_url = Util::getScriptNameForOption($cfg['DefaultTabTable'], 'table');
 148         $err_url .= Url::getCommon($url_params, '&');
 149
 150         DbTableExists::check();
 151
 152         $primary = Index::getPrimary($this->table, $this->db);
 153         $columns_with_index = $this->dbi
 154             ->getTable($this->db, $this->table)
 155             ->getColumnsWithIndex(
 156                 Index::UNIQUE | Index::INDEX | Index::SPATIAL
 157                 | Index::FULLTEXT
 158             );
 159         $columns_with_unique_index = $this->dbi
 160             ->getTable($this->db, $this->table)
 161             ->getColumnsWithIndex(Index::UNIQUE);
 162
 163         $fields = (array) $this->dbi->getColumns(
 164             $this->db,
 165             $this->table,
 166             null,
 167             true
 168         );
 169
 170         $this->response->addHTML($this->displayStructure(
 171             $cfgRelation,
 172             $columns_with_unique_index,
 173             $primary,
 174             $fields,
 175             $columns_with_index,
 176             $isSystemSchema
 177         ));
 178     }
 179
 180     public function save(): void
 181     {
 182         $regenerate = $this->updateColumns();
 183         if (! $regenerate) {
 184             // continue to show the table's structure
 185             unset($_POST['selected']);
 186         }
 187
 188         $this->index();
 189     }
 190
 191     public function addKey(): void
 192     {
 193         global $containerBuilder, $reload;
 194
 195         /** @var SqlController $controller */
 196         $controller = $containerBuilder->get(SqlController::class);
 197         $controller->index();
 198
 199         $reload = true;
 200
 201         $this->index();
 202     }
 203
 204     public function browse(): void
 205     {
 206         global $PMA_Theme;
 207
 208         if (empty($_POST['selected_fld'])) {
 209             $this->response->setRequestStatus(false);
 210             $this->response->addJSON('message', __('No column selected.'));
 211
 212             return;
 213         }
 214
 215         $this->displayTableBrowseForSelectedColumns(
 216             $GLOBALS['goto'],
 217             $PMA_Theme->getImgPath()
 218         );
 219     }
 220
 221     public function change(): void
 222     {
 223         if (isset($_GET['change_column'])) {
 224             $this->displayHtmlForColumnChange(null);
 225
 226             return;
 227         }
 228
 229         $selected = $_POST['selected_fld'] ?? [];
 230
 231         if (empty($selected)) {
 232             $this->response->setRequestStatus(false);
 233             $this->response->addJSON('message', __('No column selected.'));
 234
 235             return;
 236         }
 237
 238         $this->displayHtmlForColumnChange($selected);
 239     }
 240
 241     public function addToCentralColumns(): void
 242     {
 243         global $sql_query, $message;
 244
 245         $selected = $_POST['selected_fld'] ?? [];
 246
 247         if (empty($selected)) {
 248             $this->response->setRequestStatus(false);
 249             $this->response->addJSON('message', __('No column selected.'));
 250
 251             return;
 252         }
 253
 254         $centralColumns = new CentralColumns($this->dbi);
 255         $centralColsError = $centralColumns->syncUniqueColumns(
 256             $selected,
 257             false
 258         );
 259
 260         if ($centralColsError instanceof Message) {
 261             $message = $centralColsError;
 262         }
 263
 264         if (empty($message)) {
 265             $message = Message::success();
 266         }
 267         $this->response->addHTML(
 268             Generator::getMessage($message, $sql_query)
 269         );
 270
 271         $this->index();
 272     }
 273
 274     public function removeFromCentralColumns(): void
 275     {
 276         global $sql_query, $db, $message;
 277
 278         $selected = $_POST['selected_fld'] ?? [];
 279
 280         if (empty($selected)) {
 281             $this->response->setRequestStatus(false);
 282             $this->response->addJSON('message', __('No column selected.'));
 283
 284             return;
 285         }
 286
 287         $centralColumns = new CentralColumns($this->dbi);
 288         $centralColsError = $centralColumns->deleteColumnsFromList(
 289             $db,
 290             $selected,
 291             false
 292         );
 293
 294         if ($centralColsError instanceof Message) {
 295             $message = $centralColsError;
 296         }
 297
 298         if (empty($message)) {
 299             $message = Message::success();
 300         }
 301         $this->response->addHTML(
 302             Generator::getMessage($message, $sql_query)
 303         );
 304
 305         $this->index();
 306     }
 307
 308     public function fulltext(): void
 309     {
 310         global $sql_query, $db, $table, $message;
 311
 312         $selected = $_POST['selected_fld'] ?? [];
 313
 314         if (empty($selected)) {
 315             $this->response->setRequestStatus(false);
 316             $this->response->addJSON('message', __('No column selected.'));
 317
 318             return;
 319         }
 320
 321         $i = 1;
 322         $selectedCount = count($selected);
 323         $sql_query = 'ALTER TABLE ' . Util::backquote($table) . ' ADD FULLTEXT(';
 324
 325         foreach ($selected as $field) {
 326             $sql_query .= Util::backquote($field);
 327             $sql_query .= $i++ === $selectedCount ? ');' : ', ';
 328         }
 329
 330         $this->dbi->selectDb($db);
 331         $result = $this->dbi->tryQuery($sql_query);
 332
 333         if (! $result) {
 334             $message = Message::error((string) $this->dbi->getError());
 335         }
 336
 337         if (empty($message)) {
 338             $message = Message::success();
 339         }
 340         $this->response->addHTML(
 341             Generator::getMessage($message, $sql_query)
 342         );
 343
 344         $this->index();
 345     }
 346
 347     public function spatial(): void
 348     {
 349         global $sql_query, $db, $table, $message;
 350
 351         $selected = $_POST['selected_fld'] ?? [];
 352
 353         if (empty($selected)) {
 354             $this->response->setRequestStatus(false);
 355             $this->response->addJSON('message', __('No column selected.'));
 356
 357             return;
 358         }
 359
 360         $i = 1;
 361         $selectedCount = count($selected);
 362         $sql_query = 'ALTER TABLE ' . Util::backquote($table) . ' ADD SPATIAL(';
 363
 364         foreach ($selected as $field) {
 365             $sql_query .= Util::backquote($field);
 366             $sql_query .= $i++ === $selectedCount ? ');' : ', ';
 367         }
 368
 369         $this->dbi->selectDb($db);
 370         $result = $this->dbi->tryQuery($sql_query);
 371
 372         if (! $result) {
 373             $message = Message::error((string) $this->dbi->getError());
 374         }
 375
 376         if (empty($message)) {
 377             $message = Message::success();
 378         }
 379         $this->response->addHTML(
 380             Generator::getMessage($message, $sql_query)
 381         );
 382
 383         $this->index();
 384     }
 385
 386     public function unique(): void
 387     {
 388         global $sql_query, $db, $table, $message;
 389
 390         $selected = $_POST['selected_fld'] ?? [];
 391
 392         if (empty($selected)) {
 393             $this->response->setRequestStatus(false);
 394             $this->response->addJSON('message', __('No column selected.'));
 395
 396             return;
 397         }
 398
 399         $i = 1;
 400         $selectedCount = count($selected);
 401         $sql_query = 'ALTER TABLE ' . Util::backquote($table) . ' ADD UNIQUE(';
 402
 403         foreach ($selected as $field) {
 404             $sql_query .= Util::backquote($field);
 405             $sql_query .= $i++ === $selectedCount ? ');' : ', ';
 406         }
 407
 408         $this->dbi->selectDb($db);
 409         $result = $this->dbi->tryQuery($sql_query);
 410
 411         if (! $result) {
 412             $message = Message::error((string) $this->dbi->getError());
 413         }
 414
 415         if (empty($message)) {
 416             $message = Message::success();
 417         }
 418         $this->response->addHTML(
 419             Generator::getMessage($message, $sql_query)
 420         );
 421
 422         $this->index();
 423     }
 424
 425     public function addIndex(): void
 426     {
 427         global $sql_query, $db, $table, $message;
 428
 429         $selected = $_POST['selected_fld'] ?? [];
 430
 431         if (empty($selected)) {
 432             $this->response->setRequestStatus(false);
 433             $this->response->addJSON('message', __('No column selected.'));
 434
 435             return;
 436         }
 437
 438         $i = 1;
 439         $selectedCount = count($selected);
 440         $sql_query = 'ALTER TABLE ' . Util::backquote($table) . ' ADD INDEX(';
 441
 442         foreach ($selected as $field) {
 443             $sql_query .= Util::backquote($field);
 444             $sql_query .= $i++ === $selectedCount ? ');' : ', ';
 445         }
 446
 447         $this->dbi->selectDb($db);
 448         $result = $this->dbi->tryQuery($sql_query);
 449
 450         if (! $result) {
 451             $message = Message::error((string) $this->dbi->getError());
 452         }
 453
 454         if (empty($message)) {
 455             $message = Message::success();
 456         }
 457         $this->response->addHTML(
 458             Generator::getMessage($message, $sql_query)
 459         );
 460
 461         $this->index();
 462     }
 463
 464     public function primary(): void
 465     {
 466         global $db, $table, $message, $sql_query, $url_params, $err_url, $cfg;
 467
 468         $selected = $_POST['selected'] ?? [];
 469         $selected_fld = $_POST['selected_fld'] ?? [];
 470
 471         if (empty($selected) && empty($selected_fld)) {
 472             $this->response->setRequestStatus(false);
 473             $this->response->addJSON('message', __('No column selected.'));
 474
 475             return;
 476         }
 477
 478         $primary = $this->getKeyForTablePrimary();
 479         if (empty($primary) && ! empty($selected_fld)) {
 480             // no primary key, so we can safely create new
 481             $mult_btn = __('Yes');
 482             $selected = $selected_fld;
 483         }
 484
 485         $mult_btn = $_POST['mult_btn'] ?? $mult_btn ?? '';
 486
 487         if (! empty($selected_fld) && ! empty($primary)) {
 488             Util::checkParameters(['db', 'table']);
 489
 490             $url_params = ['db' => $db, 'table' => $table];
 491             $err_url = Util::getScriptNameForOption($cfg['DefaultTabTable'], 'table');
 492             $err_url .= Url::getCommon($url_params, '&');
 493
 494             DbTableExists::check();
 495
 496             $this->render('table/structure/primary', [
 497                 'db' => $db,
 498                 'table' => $table,
 499                 'selected' => $selected_fld,
 500             ]);
 501
 502             return;
 503         }
 504
 505         if ($mult_btn === __('Yes')) {
 506             $sql_query = 'ALTER TABLE ' . Util::backquote($table);
 507             if (! empty($primary)) {
 508                 $sql_query .= ' DROP PRIMARY KEY,';
 509             }
 510             $sql_query .= ' ADD PRIMARY KEY(';
 511
 512             $i = 1;
 513             $selectedCount = count($selected);
 514             foreach ($selected as $field) {
 515                 $sql_query .= Util::backquote($field);
 516                 $sql_query .= $i++ === $selectedCount ? ');' : ', ';
 517             }
 518
 519             $this->dbi->selectDb($db);
 520             $result = $this->dbi->tryQuery($sql_query);
 521
 522             if (! $result) {
 523                 $message = Message::error((string) $this->dbi->getError());
 524             }
 525         }
 526
 527         if (empty($message)) {
 528             $message = Message::success();
 529         }
 530         $this->response->addHTML(
 531             Generator::getMessage($message, $sql_query)
 532         );
 533
 534         $this->index();
 535     }
 536
 537     public function drop(): void
 538     {
 539         global $db, $table, $message, $sql_query;
 540
 541         $selected = $_POST['selected'] ?? [];
 542
 543         if (empty($selected)) {
 544             $this->response->setRequestStatus(false);
 545             $this->response->addJSON('message', __('No column selected.'));
 546
 547             return;
 548         }
 549
 550         $sql_query = '';
 551
 552         if (($_POST['mult_btn'] ?? '') === __('Yes')) {
 553             $i = 1;
 554             $selectedCount = count($selected);
 555             $sql_query = 'ALTER TABLE ' . Util::backquote($table);
 556
 557             foreach ($selected as $field) {
 558                 $this->relationCleanup->column($db, $table, $field);
 559                 $sql_query .= ' DROP ' . Util::backquote($field);
 560                 $sql_query .= $i++ === $selectedCount ? ';' : ',';
 561             }
 562
 563             $this->dbi->selectDb($db);
 564             $result = $this->dbi->tryQuery($sql_query);
 565
 566             if (! $result) {
 567                 $message = Message::error((string) $this->dbi->getError());
 568             }
 569         }
 570
 571         if (empty($message)) {
 572             $message = Message::success();
 573         }
 574         $this->response->addHTML(
 575             Generator::getMessage($message, $sql_query)
 576         );
 577
 578         $this->index();
 579     }
 580
 581     public function dropConfirm(): void
 582     {
 583         global $db, $table, $url_params, $err_url, $cfg;
 584
 585         $selected = $_POST['selected_fld'] ?? null;
 586
 587         if (empty($selected)) {
 588             $this->response->setRequestStatus(false);
 589             $this->response->addJSON('message', __('No column selected.'));
 590
 591             return;
 592         }
 593
 594         Util::checkParameters(['db', 'table']);
 595
 596         $url_params = ['db' => $db, 'table' => $table];
 597         $err_url = Util::getScriptNameForOption($cfg['DefaultTabTable'], 'table');
 598         $err_url .= Url::getCommon($url_params, '&');
 599
 600         DbTableExists::check();
 601
 602         $this->render('table/structure/drop_confirm', [
 603             'db' => $db,
 604             'table' => $table,
 605             'fields' => $selected,
 606         ]);
 607     }
 608
 609     /**
 610      * Moves columns in the table's structure based on $_REQUEST
 611      */
 612     public function moveColumns(): void
 613     {
 614         if (! isset($_POST['move_columns'])
 615             || ! is_array($_POST['move_columns'])
 616             || ! $this->response->isAjax()
 617         ) {
 618             return;
 619         }
 620
 621         $this->dbi->selectDb($this->db);
 622
 623         /**
 624          * load the definitions for all columns
 625          */
 626         $columns = $this->dbi->getColumnsFull($this->db, $this->table);
 627         $column_names = array_keys($columns);
 628         $changes = [];
 629
 630         // @see https://mariadb.com/kb/en/library/changes-improvements-in-mariadb-102/#information-schema
 631         $usesLiteralNull = $this->dbi->isMariaDB() && $this->dbi->getVersion() >= 100200;
 632         $defaultNullValue = $usesLiteralNull ? 'NULL' : null;
 633         // move columns from first to last
 634         for ($i = 0, $l = count($_POST['move_columns']); $i < $l; $i++) {
 635             $column = $_POST['move_columns'][$i];
 636             // is this column already correctly placed?
 637             if ($column_names[$i] == $column) {
 638                 continue;
 639             }
 640
 641             // it is not, let's move it to index $i
 642             $data = $columns[$column];
 643             $extracted_columnspec = Util::extractColumnSpec($data['Type']);
 644             if (isset($data['Extra'])
 645                 && $data['Extra'] === 'on update CURRENT_TIMESTAMP'
 646             ) {
 647                 $extracted_columnspec['attribute'] = $data['Extra'];
 648                 unset($data['Extra']);
 649             }
 650             $timeType = $data['Type'] === 'timestamp' || $data['Type'] === 'datetime';
 651             $timeDefault = $data['Default'] === 'CURRENT_TIMESTAMP' || $data['Default'] === 'current_timestamp()';
 652             $current_timestamp = $timeType && $timeDefault;
 653
 654             // @see https://mariadb.com/kb/en/library/information-schema-columns-table/#examples
 655             if ($data['Null'] === 'YES' && in_array($data['Default'], [$defaultNullValue, null])) {
 656                 $default_type = 'NULL';
 657             } elseif ($current_timestamp) {
 658                 $default_type = 'CURRENT_TIMESTAMP';
 659             } elseif ($data['Default'] === null) {
 660                 $default_type = 'NONE';
 661             } else {
 662                 $default_type = 'USER_DEFINED';
 663             }
 664
 665             $virtual = [
 666                 'VIRTUAL',
 667                 'PERSISTENT',
 668                 'VIRTUAL GENERATED',
 669                 'STORED GENERATED',
 670             ];
 671             $data['Virtuality'] = '';
 672             $data['Expression'] = '';
 673             if (isset($data['Extra']) && in_array($data['Extra'], $virtual)) {
 674                 $data['Virtuality'] = str_replace(' GENERATED', '', $data['Extra']);
 675                 $expressions = $this->tableObj->getColumnGenerationExpression($column);
 676                 $data['Expression'] = is_array($expressions) ? $expressions[$column] : null;
 677             }
 678
 679             $changes[] = 'CHANGE ' . Table::generateAlter(
 680                 $column,
 681                 $column,
 682                 mb_strtoupper($extracted_columnspec['type']),
 683                 $extracted_columnspec['spec_in_brackets'],
 684                 $extracted_columnspec['attribute'],
 685                 $data['Collation'] ?? '',
 686                 $data['Null'] === 'YES' ? 'YES' : 'NO',
 687                 $default_type,
 688                 $current_timestamp ? '' : $data['Default'],
 689                 isset($data['Extra']) && $data['Extra'] !== '' ? $data['Extra']
 690                 : false,
 691                 isset($data['COLUMN_COMMENT']) && $data['COLUMN_COMMENT'] !== ''
 692                 ? $data['COLUMN_COMMENT'] : false,
 693                 $data['Virtuality'],
 694                 $data['Expression'],
 695                 $i === 0 ? '-first' : $column_names[$i - 1]
 696             );
 697             // update current column_names array, first delete old position
 698             for ($j = 0, $ll = count($column_names); $j < $ll; $j++) {
 699                 if ($column_names[$j] != $column) {
 700                     continue;
 701                 }
 702
 703                 unset($column_names[$j]);
 704             }
 705             // insert moved column
 706             array_splice($column_names, $i, 0, $column);
 707         }
 708         if (empty($changes) && ! isset($_REQUEST['preview_sql'])) { // should never happen
 709             $this->response->setRequestStatus(false);
 710
 711             return;
 712         }
 713         // query for moving the columns
 714         $sql_query = sprintf(
 715             'ALTER TABLE %s %s',
 716             Util::backquote($this->table),
 717             implode(', ', $changes)
 718         );
 719
 720         if (isset($_REQUEST['preview_sql'])) { // preview sql
 721             $this->response->addJSON(
 722                 'sql_data',
 723                 $this->template->render('preview_sql', ['query_data' => $sql_query])
 724             );
 725         } else { // move column
 726             $this->dbi->tryQuery($sql_query);
 727             $tmp_error = $this->dbi->getError();
 728             if (is_string($tmp_error)) {
 729                 $this->response->setRequestStatus(false);
 730                 $this->response->addJSON('message', Message::error($tmp_error));
 731             } else {
 732                 $message = Message::success(
 733                     __('The columns have been moved successfully.')
 734                 );
 735                 $this->response->addJSON('message', $message);
 736                 $this->response->addJSON('columns', $column_names);
 737             }
 738         }
 739     }
 740
 741     /**
 742      * Displays HTML for changing one or more columns
 743      *
 744      * @param array|null $selected the selected columns
 745      */
 746     protected function displayHtmlForColumnChange($selected): void
 747     {
 748         global $action, $num_fields;
 749
 750         if (empty($selected)) {
 751             $selected[] = $_REQUEST['field'];
 752             $selected_cnt = 1;
 753         } else { // from a multiple submit
 754             $selected_cnt = count($selected);
 755         }
 756
 757         /**
 758          * @todo optimize in case of multiple fields to modify
 759          */
 760         $fields_meta = [];
 761         for ($i = 0; $i < $selected_cnt; $i++) {
 762             $value = $this->dbi->getColumns(
 763                 $this->db,
 764                 $this->table,
 765                 $selected[$i],
 766                 true
 767             );
 768             if (count($value) === 0) {
 769                 $message = Message::error(
 770                     __('Failed to get description of column %s!')
 771                 );
 772                 $message->addParam($selected[$i]);
 773                 $this->response->addHTML($message);
 774             } else {
 775                 $fields_meta[] = $value;
 776             }
 777         }
 778         $num_fields = count($fields_meta);
 779
 780         $action = Url::getFromRoute('/table/structure/save');
 781
 782         /**
 783          * Form for changing properties.
 784          */
 785         $checkUserPrivileges = new CheckUserPrivileges($this->dbi);
 786         $checkUserPrivileges->getPrivileges();
 787
 788         $this->addScriptFiles(['vendor/jquery/jquery.uitablefilter.js', 'indexes.js']);
 789
 790         $templateData = ColumnsDefinition::displayForm(
 791             $this->transformations,
 792             $this->relation,
 793             $this->dbi,
 794             $action,
 795             $num_fields,
 796             null,
 797             $selected,
 798             $fields_meta
 799         );
 800
 801         $this->render('columns_definitions/column_definitions_form', $templateData);
 802     }
 803
 804     public function partitioning(): void
 805     {
 806         if (isset($_POST['save_partitioning'])) {
 807             $this->dbi->selectDb($this->db);
 808             $this->updatePartitioning();
 809             $this->index();
 810
 811             return;
 812         }
 813
 814         $pageSettings = new PageSettings('TableStructure');
 815         $this->response->addHTML($pageSettings->getErrorHTML());
 816         $this->response->addHTML($pageSettings->getHTML());
 817
 818         $this->addScriptFiles(['table/structure.js', 'indexes.js']);
 819
 820         $partitionDetails = null;
 821         if (! isset($_POST['partition_by'])) {
 822             $partitionDetails = $this->extractPartitionDetails();
 823         }
 824
 825         $storageEngines = StorageEngine::getArray();
 826
 827         $partitionDetails = TablePartitionDefinition::getDetails($partitionDetails);
 828         $this->render('table/structure/partition_definition_form', [
 829             'db' => $this->db,
 830             'table' => $this->table,
 831             'partition_details' => $partitionDetails,
 832             'storage_engines' => $storageEngines,
 833         ]);
 834     }
 835
 836     /**
 837      * Extracts partition details from CREATE TABLE statement
 838      *
 839      * @return array[]|null array of partition details
 840      */
 841     private function extractPartitionDetails(): ?array
 842     {
 843         $createTable = (new Table($this->table, $this->db))->showCreate();
 844         if (! $createTable) {
 845             return null;
 846         }
 847
 848         $parser = new Parser($createTable);
 849         /**
 850          * @var CreateStatement $stmt
 851          */
 852         $stmt = $parser->statements[0];
 853
 854         $partitionDetails = [];
 855
 856         $partitionDetails['partition_by'] = '';
 857         $partitionDetails['partition_expr'] = '';
 858         $partitionDetails['partition_count'] = '';
 859
 860         if (! empty($stmt->partitionBy)) {
 861             $openPos = strpos($stmt->partitionBy, '(');
 862             $closePos = strrpos($stmt->partitionBy, ')');
 863
 864             $partitionDetails['partition_by']
 865                 = trim(substr($stmt->partitionBy, 0, $openPos));
 866             $partitionDetails['partition_expr']
 867                 = trim(substr($stmt->partitionBy, $openPos + 1, $closePos - ($openPos + 1)));
 868             if (isset($stmt->partitionsNum)) {
 869                 $count = $stmt->partitionsNum;
 870             } else {
 871                 $count = count($stmt->partitions);
 872             }
 873             $partitionDetails['partition_count'] = $count;
 874         }
 875
 876         $partitionDetails['subpartition_by'] = '';
 877         $partitionDetails['subpartition_expr'] = '';
 878         $partitionDetails['subpartition_count'] = '';
 879
 880         if (! empty($stmt->subpartitionBy)) {
 881             $openPos = strpos($stmt->subpartitionBy, '(');
 882             $closePos = strrpos($stmt->subpartitionBy, ')');
 883
 884             $partitionDetails['subpartition_by']
 885                 = trim(substr($stmt->subpartitionBy, 0, $openPos));
 886             $partitionDetails['subpartition_expr']
 887                 = trim(substr($stmt->subpartitionBy, $openPos + 1, $closePos - ($openPos + 1)));
 888             if (isset($stmt->subpartitionsNum)) {
 889                 $count = $stmt->subpartitionsNum;
 890             } else {
 891                 $count = count($stmt->partitions[0]->subpartitions);
 892             }
 893             $partitionDetails['subpartition_count'] = $count;
 894         }
 895
 896         // Only LIST and RANGE type parameters allow subpartitioning
 897         $partitionDetails['can_have_subpartitions']
 898             = $partitionDetails['partition_count'] > 1
 899                 && ($partitionDetails['partition_by'] === 'RANGE'
 900                 || $partitionDetails['partition_by'] === 'RANGE COLUMNS'
 901                 || $partitionDetails['partition_by'] === 'LIST'
 902                 || $partitionDetails['partition_by'] === 'LIST COLUMNS');
 903
 904         // Values are specified only for LIST and RANGE type partitions
 905         $partitionDetails['value_enabled'] = isset($partitionDetails['partition_by'])
 906             && ($partitionDetails['partition_by'] === 'RANGE'
 907             || $partitionDetails['partition_by'] === 'RANGE COLUMNS'
 908             || $partitionDetails['partition_by'] === 'LIST'
 909             || $partitionDetails['partition_by'] === 'LIST COLUMNS');
 910
 911         $partitionDetails['partitions'] = [];
 912
 913         for ($i = 0, $iMax = (int) $partitionDetails['partition_count']; $i < $iMax; $i++) {
 914             if (! isset($stmt->partitions[$i])) {
 915                 $partitionDetails['partitions'][$i] = [
 916                     'name' => 'p' . $i,
 917                     'value_type' => '',
 918                     'value' => '',
 919                     'engine' => '',
 920                     'comment' => '',
 921                     'data_directory' => '',
 922                     'index_directory' => '',
 923                     'max_rows' => '',
 924                     'min_rows' => '',
 925                     'tablespace' => '',
 926                     'node_group' => '',
 927                 ];
 928             } else {
 929                 $p = $stmt->partitions[$i];
 930                 $type = $p->type;
 931                 $expr = trim((string) $p->expr, '()');
 932                 if ($expr === 'MAXVALUE') {
 933                     $type .= ' MAXVALUE';
 934                     $expr = '';
 935                 }
 936                 $partitionDetails['partitions'][$i] = [
 937                     'name' => $p->name,
 938                     'value_type' => $type,
 939                     'value' => $expr,
 940                     'engine' => $p->options->has('ENGINE', true),
 941                     'comment' => trim((string) $p->options->has('COMMENT', true), "'"),
 942                     'data_directory' => trim((string) $p->options->has('DATA DIRECTORY', true), "'"),
 943                     'index_directory' => trim((string) $p->options->has('INDEX_DIRECTORY', true), "'"),
 944                     'max_rows' => $p->options->has('MAX_ROWS', true),
 945                     'min_rows' => $p->options->has('MIN_ROWS', true),
 946                     'tablespace' => $p->options->has('TABLESPACE', true),
 947                     'node_group' => $p->options->has('NODEGROUP', true),
 948                 ];
 949             }
 950
 951             $partition =& $partitionDetails['partitions'][$i];
 952             $partition['prefix'] = 'partitions[' . $i . ']';
 953
 954             if ($partitionDetails['subpartition_count'] <= 1) {
 955                 continue;
 956             }
 957
 958             $partition['subpartition_count'] = $partitionDetails['subpartition_count'];
 959             $partition['subpartitions'] = [];
 960
 961             for ($j = 0, $jMax = (int) $partitionDetails['subpartition_count']; $j < $jMax; $j++) {
 962                 if (! isset($stmt->partitions[$i]->subpartitions[$j])) {
 963                     $partition['subpartitions'][$j] = [
 964                         'name' => $partition['name'] . '_s' . $j,
 965                         'engine' => '',
 966                         'comment' => '',
 967                         'data_directory' => '',
 968                         'index_directory' => '',
 969                         'max_rows' => '',
 970                         'min_rows' => '',
 971                         'tablespace' => '',
 972                         'node_group' => '',
 973                     ];
 974                 } else {
 975                     $sp = $stmt->partitions[$i]->subpartitions[$j];
 976                     $partition['subpartitions'][$j] = [
 977                         'name' => $sp->name,
 978                         'engine' => $sp->options->has('ENGINE', true),
 979                         'comment' => trim((string) $sp->options->has('COMMENT', true), "'"),
 980                         'data_directory' => trim((string) $sp->options->has('DATA DIRECTORY', true), "'"),
 981                         'index_directory' => trim((string) $sp->options->has('INDEX_DIRECTORY', true), "'"),
 982                         'max_rows' => $sp->options->has('MAX_ROWS', true),
 983                         'min_rows' => $sp->options->has('MIN_ROWS', true),
 984                         'tablespace' => $sp->options->has('TABLESPACE', true),
 985                         'node_group' => $sp->options->has('NODEGROUP', true),
 986                     ];
 987                 }
 988
 989                 $subpartition =& $partition['subpartitions'][$j];
 990                 $subpartition['prefix'] = 'partitions[' . $i . ']'
 991                     . '[subpartitions][' . $j . ']';
 992             }
 993         }
 994
 995         return $partitionDetails;
 996     }
 997
 998     private function updatePartitioning(): void
 999     {
1000         $sql_query = 'ALTER TABLE ' . Util::backquote($this->table) . ' '
1001             . $this->createAddField->getPartitionsDefinition();
1002
1003         // Execute alter query
1004         $result = $this->dbi->tryQuery($sql_query);
1005
1006         if ($result === false) {
1007             $this->response->setRequestStatus(false);
1008             $this->response->addJSON(
1009                 'message',
1010                 Message::rawError(
1011                     __('Query error') . ':<br>' . $this->dbi->getError()
1012                 )
1013             );
1014
1015             return;
1016         }
1017
1018         $message = Message::success(
1019             __('Table %1$s has been altered successfully.')
1020         );
1021         $message->addParam($this->table);
1022         $this->response->addHTML(
1023             Generator::getMessage($message, $sql_query, 'success')
1024         );
1025     }
1026
1027     /**
1028      * Function to display table browse for selected columns
1029      *
1030      * @param string $goto           goto page url
1031      * @param string $themeImagePath URI of the pma theme image
1032      *
1033      * @return void
1034      */
1035     protected function displayTableBrowseForSelectedColumns($goto, $themeImagePath)
1036     {
1037         $GLOBALS['active_page'] = Url::getFromRoute('/sql');
1038         $fields = [];
1039         foreach ($_POST['selected_fld'] as $sval) {
1040             $fields[] = Util::backquote($sval);
1041         }
1042         $sql_query = sprintf(
1043             'SELECT %s FROM %s.%s',
1044             implode(', ', $fields),
1045             Util::backquote($this->db),
1046             Util::backquote($this->table)
1047         );
1048
1049         // Parse and analyze the query
1050         $db = &$this->db;
1051         [
1052             $analyzed_sql_results,
1053             $db,
1054         ] = ParseAnalyze::sqlQuery($sql_query, $db);
1055
1056         $sql = new Sql(
1057             $this->dbi,
1058             $this->relation,
1059             $this->relationCleanup,
1060             new Operations($this->dbi, $this->relation),
1061             $this->transformations,
1062             $this->template
1063         );
1064
1065         $this->response->addHTML(
1066             $sql->executeQueryAndGetQueryResponse(
1067                 $analyzed_sql_results ?? '',
1068                 false, // is_gotofile
1069                 $this->db, // db
1070                 $this->table, // table
1071                 null, // find_real_end
1072                 null, // sql_query_for_bookmark
1073                 null, // extra_data
1074                 null, // message_to_show
1075                 null, // sql_data
1076                 $goto, // goto
1077                 $themeImagePath,
1078                 null, // disp_query
1079                 null, // disp_message
1080                 $sql_query, // sql_query
1081                 null // complete_query
1082             )
1083         );
1084     }
1085
1086     /**
1087      * Update the table's structure based on $_REQUEST
1088      *
1089      * @return bool true if error occurred
1090      */
1091     protected function updateColumns()
1092     {
1093         $err_url = Url::getFromRoute('/table/structure', [
1094             'db' => $this->db,
1095             'table' => $this->table,
1096         ]);
1097         $regenerate = false;
1098         $field_cnt = count($_POST['field_name'] ?? []);
1099         $changes = [];
1100         $adjust_privileges = [];
1101         $columns_with_index = $this->dbi
1102             ->getTable($this->db, $this->table)
1103             ->getColumnsWithIndex(
1104                 Index::PRIMARY | Index::UNIQUE
1105             );
1106         for ($i = 0; $i < $field_cnt; $i++) {
1107             if (! $this->columnNeedsAlterTable($i)) {
1108                 continue;
1109             }
1110
1111             $changes[] = 'CHANGE ' . Table::generateAlter(
1112                 Util::getValueByKey($_POST, "field_orig.${i}", ''),
1113                 $_POST['field_name'][$i],
1114                 $_POST['field_type'][$i],
1115                 $_POST['field_length'][$i],
1116                 $_POST['field_attribute'][$i],
1117                 Util::getValueByKey($_POST, "field_collation.${i}", ''),
1118                 Util::getValueByKey($_POST, "field_null.${i}", 'NO'),
1119                 $_POST['field_default_type'][$i],
1120                 $_POST['field_default_value'][$i],
1121                 Util::getValueByKey($_POST, "field_extra.${i}", false),
1122                 Util::getValueByKey($_POST, "field_comments.${i}", ''),
1123                 Util::getValueByKey($_POST, "field_virtuality.${i}", ''),
1124                 Util::getValueByKey($_POST, "field_expression.${i}", ''),
1125                 Util::getValueByKey($_POST, "field_move_to.${i}", ''),
1126                 $columns_with_index
1127             );
1128
1129             // find the remembered sort expression
1130             $sorted_col = $this->tableObj->getUiProp(
1131                 Table::PROP_SORTED_COLUMN
1132             );
1133             // if the old column name is part of the remembered sort expression
1134             if (mb_strpos(
1135                 (string) $sorted_col,
1136                 Util::backquote($_POST['field_orig'][$i])
1137             ) !== false) {
1138                 // delete the whole remembered sort expression
1139                 $this->tableObj->removeUiProp(Table::PROP_SORTED_COLUMN);
1140             }
1141
1142             if (! isset($_POST['field_adjust_privileges'][$i])
1143                 || empty($_POST['field_adjust_privileges'][$i])
1144                 || $_POST['field_orig'][$i] == $_POST['field_name'][$i]
1145             ) {
1146                 continue;
1147             }
1148
1149             $adjust_privileges[$_POST['field_orig'][$i]]
1150                 = $_POST['field_name'][$i];
1151         }
1152
1153         if (count($changes) > 0 || isset($_POST['preview_sql'])) {
1154             // Builds the primary keys statements and updates the table
1155             $key_query = '';
1156             /**
1157              * this is a little bit more complex
1158              *
1159              * @todo if someone selects A_I when altering a column we need to check:
1160              *  - no other column with A_I
1161              *  - the column has an index, if not create one
1162              */
1163
1164             // To allow replication, we first select the db to use
1165             // and then run queries on this db.
1166             if (! $this->dbi->selectDb($this->db)) {
1167                 Generator::mysqlDie(
1168                     $this->dbi->getError(),
1169                     'USE ' . Util::backquote($this->db) . ';',
1170                     false,
1171                     $err_url
1172                 );
1173             }
1174
1175             $sql_query = 'ALTER TABLE ' . Util::backquote($this->table) . ' ';
1176             $sql_query .= implode(', ', $changes) . $key_query;
1177             if (isset($_POST['online_transaction'])) {
1178                 $sql_query .= ', ALGORITHM=INPLACE, LOCK=NONE';
1179             }
1180             $sql_query .= ';';
1181
1182             // If there is a request for SQL previewing.
1183             if (isset($_POST['preview_sql'])) {
1184                 Core::previewSQL(count($changes) > 0 ? $sql_query : '');
1185
1186                 exit;
1187             }
1188
1189             $columns_with_index = $this->dbi
1190                 ->getTable($this->db, $this->table)
1191                 ->getColumnsWithIndex(
1192                     Index::PRIMARY | Index::UNIQUE | Index::INDEX
1193                     | Index::SPATIAL | Index::FULLTEXT
1194                 );
1195
1196             $changedToBlob = [];
1197             // While changing the Column Collation
1198             // First change to BLOB
1199             for ($i = 0; $i < $field_cnt; $i++) {
1200                 if (isset($_POST['field_collation'][$i], $_POST['field_collation_orig'][$i])
1201                     && $_POST['field_collation'][$i] !== $_POST['field_collation_orig'][$i]
1202                     && ! in_array($_POST['field_orig'][$i], $columns_with_index)
1203                 ) {
1204                     $secondary_query = 'ALTER TABLE ' . Util::backquote(
1205                         $this->table
1206                     )
1207                     . ' CHANGE ' . Util::backquote(
1208                         $_POST['field_orig'][$i]
1209                     )
1210                     . ' ' . Util::backquote($_POST['field_orig'][$i])
1211                     . ' BLOB';
1212
1213                     if (isset($_POST['field_virtuality'][$i], $_POST['field_expression'][$i])) {
1214                         if ($_POST['field_virtuality'][$i]) {
1215                             $secondary_query .= ' AS (' . $_POST['field_expression'][$i] . ') '
1216                                 . $_POST['field_virtuality'][$i];
1217                         }
1218                     }
1219
1220                     $secondary_query .= ';';
1221
1222                     $this->dbi->query($secondary_query);
1223                     $changedToBlob[$i] = true;
1224                 } else {
1225                     $changedToBlob[$i] = false;
1226                 }
1227             }
1228
1229             // Then make the requested changes
1230             $result = $this->dbi->tryQuery($sql_query);
1231
1232             if ($result !== false) {
1233                 $changed_privileges = $this->adjustColumnPrivileges(
1234                     $adjust_privileges
1235                 );
1236
1237                 if ($changed_privileges) {
1238                     $message = Message::success(
1239                         __(
1240                             'Table %1$s has been altered successfully. Privileges ' .
1241                             'have been adjusted.'
1242                         )
1243                     );
1244                 } else {
1245                     $message = Message::success(
1246                         __('Table %1$s has been altered successfully.')
1247                     );
1248                 }
1249                 $message->addParam($this->table);
1250
1251                 $this->response->addHTML(
1252                     Generator::getMessage($message, $sql_query, 'success')
1253                 );
1254             } else {
1255                 // An error happened while inserting/updating a table definition
1256
1257                 // Save the Original Error
1258                 $orig_error = $this->dbi->getError();
1259                 $changes_revert = [];
1260
1261                 // Change back to Original Collation and data type
1262                 for ($i = 0; $i < $field_cnt; $i++) {
1263                     if (! $changedToBlob[$i]) {
1264                         continue;
1265                     }
1266
1267                     $changes_revert[] = 'CHANGE ' . Table::generateAlter(
1268                         Util::getValueByKey($_POST, "field_orig.${i}", ''),
1269                         $_POST['field_name'][$i],
1270                         $_POST['field_type_orig'][$i],
1271                         $_POST['field_length_orig'][$i],
1272                         $_POST['field_attribute_orig'][$i],
1273                         Util::getValueByKey($_POST, "field_collation_orig.${i}", ''),
1274                         Util::getValueByKey($_POST, "field_null_orig.${i}", 'NO'),
1275                         $_POST['field_default_type_orig'][$i],
1276                         $_POST['field_default_value_orig'][$i],
1277                         Util::getValueByKey($_POST, "field_extra_orig.${i}", false),
1278                         Util::getValueByKey($_POST, "field_comments_orig.${i}", ''),
1279                         Util::getValueByKey($_POST, "field_virtuality_orig.${i}", ''),
1280                         Util::getValueByKey($_POST, "field_expression_orig.${i}", ''),
1281                         Util::getValueByKey($_POST, "field_move_to_orig.${i}", '')
1282                     );
1283                 }
1284
1285                 $revert_query = 'ALTER TABLE ' . Util::backquote($this->table)
1286                     . ' ';
1287                 $revert_query .= implode(', ', $changes_revert) . '';
1288                 $revert_query .= ';';
1289
1290                 // Column reverted back to original
1291                 $this->dbi->query($revert_query);
1292
1293                 $this->response->setRequestStatus(false);
1294                 $this->response->addJSON(
1295                     'message',
1296                     Message::rawError(
1297                         __('Query error') . ':<br>' . $orig_error
1298                     )
1299                 );
1300                 $regenerate = true;
1301             }
1302         }
1303
1304         // update field names in relation
1305         if (isset($_POST['field_orig']) && is_array($_POST['field_orig'])) {
1306             foreach ($_POST['field_orig'] as $fieldindex => $fieldcontent) {
1307                 if ($_POST['field_name'][$fieldindex] == $fieldcontent) {
1308                     continue;
1309                 }
1310
1311                 $this->relation->renameField(
1312                     $this->db,
1313                     $this->table,
1314                     $fieldcontent,
1315                     $_POST['field_name'][$fieldindex]
1316                 );
1317             }
1318         }
1319
1320         // update mime types
1321         if (isset($_POST['field_mimetype'])
1322             && is_array($_POST['field_mimetype'])
1323             && $GLOBALS['cfg']['BrowseMIME']
1324         ) {
1325             foreach ($_POST['field_mimetype'] as $fieldindex => $mimetype) {
1326                 if (! isset($_POST['field_name'][$fieldindex])
1327                     || strlen($_POST['field_name'][$fieldindex]) <= 0
1328                 ) {
1329                     continue;
1330                 }
1331
1332                 $this->transformations->setMime(
1333                     $this->db,
1334                     $this->table,
1335                     $_POST['field_name'][$fieldindex],
1336                     $mimetype,
1337                     $_POST['field_transformation'][$fieldindex],
1338                     $_POST['field_transformation_options'][$fieldindex],
1339                     $_POST['field_input_transformation'][$fieldindex],
1340                     $_POST['field_input_transformation_options'][$fieldindex]
1341                 );
1342             }
1343         }
1344
1345         return $regenerate;
1346     }
1347
1348     /**
1349      * Adjusts the Privileges for all the columns whose names have changed
1350      *
1351      * @param array $adjust_privileges assoc array of old col names mapped to new
1352      *                                 cols
1353      *
1354      * @return bool boolean whether at least one column privileges
1355      * adjusted
1356      */
1357     protected function adjustColumnPrivileges(array $adjust_privileges)
1358     {
1359         $changed = false;
1360
1361         if (Util::getValueByKey($GLOBALS, 'col_priv', false)
1362             && Util::getValueByKey($GLOBALS, 'is_reload_priv', false)
1363         ) {
1364             $this->dbi->selectDb('mysql');
1365
1366             // For Column specific privileges
1367             foreach ($adjust_privileges as $oldCol => $newCol) {
1368                 $this->dbi->query(
1369                     sprintf(
1370                         'UPDATE %s SET Column_name = "%s"
1371                         WHERE Db = "%s"
1372                         AND Table_name = "%s"
1373                         AND Column_name = "%s";',
1374                         Util::backquote('columns_priv'),
1375                         $newCol,
1376                         $this->db,
1377                         $this->table,
1378                         $oldCol
1379                     )
1380                 );
1381
1382                 // i.e. if atleast one column privileges adjusted
1383                 $changed = true;
1384             }
1385
1386             if ($changed) {
1387                 // Finally FLUSH the new privileges
1388                 $this->dbi->query('FLUSH PRIVILEGES;');
1389             }
1390         }
1391
1392         return $changed;
1393     }
1394
1395     /**
1396      * Verifies if some elements of a column have changed
1397      *
1398      * @param int $i column index in the request
1399      *
1400      * @return bool true if we need to generate ALTER TABLE
1401      */
1402     protected function columnNeedsAlterTable($i)
1403     {
1404         // these two fields are checkboxes so might not be part of the
1405         // request; therefore we define them to avoid notices below
1406         if (! isset($_POST['field_null'][$i])) {
1407             $_POST['field_null'][$i] = 'NO';
1408         }
1409         if (! isset($_POST['field_extra'][$i])) {
1410             $_POST['field_extra'][$i] = '';
1411         }
1412
1413         // field_name does not follow the convention (corresponds to field_orig)
1414         if ($_POST['field_name'][$i] != $_POST['field_orig'][$i]) {
1415             return true;
1416         }
1417
1418         $fields = [
1419             'field_attribute',
1420             'field_collation',
1421             'field_comments',
1422             'field_default_value',
1423             'field_default_type',
1424             'field_extra',
1425             'field_length',
1426             'field_null',
1427             'field_type',
1428         ];
1429         foreach ($fields as $field) {
1430             if ($_POST[$field][$i] != $_POST[$field . '_orig'][$i]) {
1431                 return true;
1432             }
1433         }
1434
1435         return ! empty($_POST['field_move_to'][$i]);
1436     }
1437
1438     /**
1439      * Displays the table structure ('show table' works correct since 3.23.03)
1440      *
1441      * @param array       $cfgRelation               current relation parameters
1442      * @param array       $columns_with_unique_index Columns with unique index
1443      * @param Index|false $primary_index             primary index or false if
1444      *                                               no one exists
1445      * @param array       $fields                    Fields
1446      * @param array       $columns_with_index        Columns with index
1447      *
1448      * @return string
1449      */
1450     protected function displayStructure(
1451         array $cfgRelation,
1452         array $columns_with_unique_index,
1453         $primary_index,
1454         array $fields,
1455         array $columns_with_index,
1456         bool $isSystemSchema
1457     ) {
1458         global $route, $tbl_is_view, $tbl_storage_engine, $PMA_Theme;
1459
1460         // prepare comments
1461         $comments_map = [];
1462         $mime_map = [];
1463
1464         if ($GLOBALS['cfg']['ShowPropertyComments']) {
1465             $comments_map = $this->relation->getComments($this->db, $this->table);
1466             if ($cfgRelation['mimework'] && $GLOBALS['cfg']['BrowseMIME']) {
1467                 $mime_map = $this->transformations->getMime($this->db, $this->table, true);
1468             }
1469         }
1470         $centralColumns = new CentralColumns($this->dbi);
1471         $central_list = $centralColumns->getFromTable(
1472             $this->db,
1473             $this->table
1474         );
1475
1476         /**
1477          * Displays Space usage and row statistics
1478          */
1479         // BEGIN - Calc Table Space
1480         // Get valid statistics whatever is the table type
1481         if ($GLOBALS['cfg']['ShowStats']) {
1482             //get table stats in HTML format
1483             $tablestats = $this->getTableStats($isSystemSchema);
1484             //returning the response in JSON format to be used by Ajax
1485             $this->response->addJSON('tableStat', $tablestats);
1486         }
1487         // END - Calc Table Space
1488
1489         $hideStructureActions = false;
1490         if ($GLOBALS['cfg']['HideStructureActions'] === true) {
1491             $hideStructureActions = true;
1492         }
1493
1494         // logic removed from Template
1495         $rownum = 0;
1496         $columns_list = [];
1497         $attributes = [];
1498         $displayed_fields = [];
1499         $row_comments = [];
1500         $extracted_columnspecs = [];
1501         $collations = [];
1502         foreach ($fields as &$field) {
1503             ++$rownum;
1504             $columns_list[] = $field['Field'];
1505
1506             $extracted_columnspecs[$rownum] = Util::extractColumnSpec($field['Type']);
1507             $attributes[$rownum] = $extracted_columnspecs[$rownum]['attribute'];
1508             if (strpos($field['Extra'], 'on update CURRENT_TIMESTAMP') !== false) {
1509                 $attributes[$rownum] = 'on update CURRENT_TIMESTAMP';
1510             }
1511
1512             $displayed_fields[$rownum] = new stdClass();
1513             $displayed_fields[$rownum]->text = $field['Field'];
1514             $displayed_fields[$rownum]->icon = '';
1515             $row_comments[$rownum] = '';
1516
1517             if (isset($comments_map[$field['Field']])) {
1518                 $displayed_fields[$rownum]->comment = $comments_map[$field['Field']];
1519                 $row_comments[$rownum] = $comments_map[$field['Field']];
1520             }
1521
1522             if ($primary_index && $primary_index->hasColumn($field['Field'])) {
1523                 $displayed_fields[$rownum]->icon .=
1524                     Generator::getImage('b_primary', __('Primary'));
1525             }
1526
1527             if (in_array($field['Field'], $columns_with_index)) {
1528                 $displayed_fields[$rownum]->icon .=
1529                     Generator::getImage('bd_primary', __('Index'));
1530             }
1531
1532             $collation = Charsets::findCollationByName(
1533                 $this->dbi,
1534                 $GLOBALS['cfg']['Server']['DisableIS'],
1535                 $field['Collation'] ?? ''
1536             );
1537             if ($collation === null) {
1538                 continue;
1539             }
1540
1541             $collations[$collation->getName()] = [
1542                 'name' => $collation->getName(),
1543                 'description' => $collation->getDescription(),
1544             ];
1545         }
1546
1547         $engine = $this->tableObj->getStorageEngine();
1548
1549         return $this->template->render('table/structure/display_structure', [
1550             'collations' => $collations,
1551             'is_foreign_key_supported' => Util::isForeignKeySupported($engine),
1552             'indexes' => Index::getFromTable($this->table, $this->db),
1553             'indexes_duplicates' => Index::findDuplicates($this->table, $this->db),
1554             'cfg_relation' => $this->relation->getRelationsParam(),
1555             'hide_structure_actions' => $hideStructureActions,
1556             'db' => $this->db,
1557             'table' => $this->table,
1558             'db_is_system_schema' => $isSystemSchema,
1559             'tbl_is_view' => $tbl_is_view,
1560             'mime_map' => $mime_map,
1561             'tbl_storage_engine' => $tbl_storage_engine,
1562             'primary' => $primary_index,
1563             'columns_with_unique_index' => $columns_with_unique_index,
1564             'columns_list' => $columns_list,
1565             'table_stats' => $tablestats ?? null,
1566             'fields' => $fields,
1567             'extracted_columnspecs' => $extracted_columnspecs,
1568             'columns_with_index' => $columns_with_index,
1569             'central_list' => $central_list,
1570             'comments_map' => $comments_map,
1571             'browse_mime' => $GLOBALS['cfg']['BrowseMIME'],
1572             'show_column_comments' => $GLOBALS['cfg']['ShowColumnComments'],
1573             'show_stats' => $GLOBALS['cfg']['ShowStats'],
1574             'relation_commwork' => $GLOBALS['cfgRelation']['commwork'],
1575             'relation_mimework' => $GLOBALS['cfgRelation']['mimework'],
1576             'central_columns_work' => $GLOBALS['cfgRelation']['centralcolumnswork'],
1577             'mysql_int_version' => $this->dbi->getVersion(),
1578             'is_mariadb' => $this->dbi->isMariaDB(),
1579             'theme_image_path' => $PMA_Theme->getImgPath(),
1580             'text_dir' => $GLOBALS['text_dir'],
1581             'is_active' => Tracker::isActive(),
1582             'have_partitioning' => Partition::havePartitioning(),
1583             'partitions' => Partition::getPartitions($this->db, $this->table),
1584             'partition_names' => Partition::getPartitionNames($this->db, $this->table),
1585             'default_sliders_state' => $GLOBALS['cfg']['InitialSlidersState'],
1586             'attributes' => $attributes,
1587             'displayed_fields' => $displayed_fields,
1588             'row_comments' => $row_comments,
1589             'route' => $route,
1590         ]);
1591     }
1592
1593     /**
1594      * Get HTML snippet for display table statistics
1595      *
1596      * @return string
1597      */
1598     protected function getTableStats(bool $isSystemSchema)
1599     {
1600         global $showtable, $tbl_is_view;
1601         global $tbl_storage_engine, $table_info_num_rows, $tbl_collation;
1602
1603         if (empty($showtable)) {
1604             $showtable = $this->dbi->getTable(
1605                 $this->db,
1606                 $this->table
1607             )->getStatusInfo(null, true);
1608         }
1609
1610         if (is_string($showtable)) {
1611             $showtable = [];
1612         }
1613
1614         if (empty($showtable['Data_length'])) {
1615             $showtable['Data_length'] = 0;
1616         }
1617         if (empty($showtable['Index_length'])) {
1618             $showtable['Index_length'] = 0;
1619         }
1620
1621         $is_innodb = (isset($showtable['Type'])
1622             && $showtable['Type'] === 'InnoDB');
1623
1624         $mergetable = $this->tableObj->isMerge();
1625
1626         // this is to display for example 261.2 MiB instead of 268k KiB
1627         $max_digits = 3;
1628         $decimals = 1;
1629         [$data_size, $data_unit] = Util::formatByteDown(
1630             $showtable['Data_length'],
1631             $max_digits,
1632             $decimals
1633         );
1634         if ($mergetable === false) {
1635             [$index_size, $index_unit] = Util::formatByteDown(
1636                 $showtable['Index_length'],
1637                 $max_digits,
1638                 $decimals
1639             );
1640         }
1641         if (isset($showtable['Data_free'])) {
1642             [$free_size, $free_unit] = Util::formatByteDown(
1643                 $showtable['Data_free'],
1644                 $max_digits,
1645                 $decimals
1646             );
1647             [$effect_size, $effect_unit] = Util::formatByteDown(
1648                 $showtable['Data_length']
1649                 + $showtable['Index_length']
1650                 - $showtable['Data_free'],
1651                 $max_digits,
1652                 $decimals
1653             );
1654         } else {
1655             [$effect_size, $effect_unit] = Util::formatByteDown(
1656                 $showtable['Data_length']
1657                 + $showtable['Index_length'],
1658                 $max_digits,
1659                 $decimals
1660             );
1661         }
1662         [$tot_size, $tot_unit] = Util::formatByteDown(
1663             $showtable['Data_length'] + $showtable['Index_length'],
1664             $max_digits,
1665             $decimals
1666         );
1667         if ($table_info_num_rows > 0) {
1668             [$avg_size, $avg_unit] = Util::formatByteDown(
1669                 ($showtable['Data_length']
1670                 + $showtable['Index_length'])
1671                 / $showtable['Rows'],
1672                 6,
1673                 1
1674             );
1675         } else {
1676             $avg_size = $avg_unit = '';
1677         }
1678         /** @var Innodb $innodbEnginePlugin */
1679         $innodbEnginePlugin = StorageEngine::getEngine('Innodb');
1680         $innodb_file_per_table = $innodbEnginePlugin->supportsFilePerTable();
1681
1682         $engine = $this->dbi->getTable($this->db, $this->table)->getStorageEngine();
1683
1684         $tableCollation = [];
1685         $collation = Charsets::findCollationByName(
1686             $this->dbi,
1687             $GLOBALS['cfg']['Server']['DisableIS'],
1688             $tbl_collation
1689         );
1690         if ($collation !== null) {
1691             $tableCollation = [
1692                 'name' => $collation->getName(),
1693                 'description' => $collation->getDescription(),
1694             ];
1695         }
1696
1697         return $this->template->render('table/structure/display_table_stats', [
1698             'db' => $GLOBALS['db'],
1699             'table' => $GLOBALS['table'],
1700             'is_foreign_key_supported' => Util::isForeignKeySupported($engine),
1701             'cfg_relation' => $this->relation->getRelationsParam(),
1702             'showtable' => $showtable,
1703             'table_info_num_rows' => $table_info_num_rows,
1704             'tbl_is_view' => $tbl_is_view,
1705             'db_is_system_schema' => $isSystemSchema,
1706             'tbl_storage_engine' => $tbl_storage_engine,
1707             'table_collation' => $tableCollation,
1708             'is_innodb' => $is_innodb,
1709             'mergetable' => $mergetable,
1710             'avg_size' => $avg_size ?? null,
1711             'avg_unit' => $avg_unit ?? null,
1712             'data_size' => $data_size,
1713             'data_unit' => $data_unit,
1714             'index_size' => $index_size ?? null,
1715             'index_unit' => $index_unit ?? null,
1716             'innodb_file_per_table' => $innodb_file_per_table,
1717             'free_size' => $free_size ?? null,
1718             'free_unit' => $free_unit ?? null,
1719             'effect_size' => $effect_size,
1720             'effect_unit' => $effect_unit,
1721             'tot_size' => $tot_size,
1722             'tot_unit' => $tot_unit,
1723         ]);
1724     }
1725
1726     /**
1727      * Gets table primary key
1728      *
1729      * @return string
1730      */
1731     protected function getKeyForTablePrimary()
1732     {
1733         $this->dbi->selectDb($this->db);
1734         $result = $this->dbi->query(
1735             'SHOW KEYS FROM ' . Util::backquote($this->table) . ';'
1736         );
1737         $primary = '';
1738         while ($row = $this->dbi->fetchAssoc($result)) {
1739             // Backups the list of primary keys
1740             if (! is_array($row) || $row['Key_name'] !== 'PRIMARY') {
1741                 continue;
1742             }
1743
1744             $primary .= $row['Column_name'] . ', ';
1745         }
1746         $this->dbi->freeResult($result);
1747
1748         return $primary;
1749     }
1750
1751     /**
1752      * Handles MySQL reserved words columns check.
1753      */
1754     public function reservedWordCheck(): void
1755     {
1756         if ($GLOBALS['cfg']['ReservedWordDisableWarning'] !== false) {
1757             $this->response->setRequestStatus(false);
1758
1759             return;
1760         }
1761
1762         $columns_names = $_POST['field_name'];
1763         $reserved_keywords_names = [];
1764         foreach ($columns_names as $column) {
1765             if (! Context::isKeyword(trim($column), true)) {
1766                 continue;
1767             }
1768
1769             $reserved_keywords_names[] = trim($column);
1770         }
1771         if (Context::isKeyword(trim($this->table), true)) {
1772             $reserved_keywords_names[] = trim($this->table);
1773         }
1774         if (count($reserved_keywords_names) === 0) {
1775             $this->response->setRequestStatus(false);
1776         }
1777         $this->response->addJSON(
1778             'message',
1779             sprintf(
1780                 _ngettext(
1781                     'The name \'%s\' is a MySQL reserved keyword.',
1782                     'The names \'%s\' are MySQL reserved keywords.',
1783                     count($reserved_keywords_names)
1784                 ),
1785                 implode(',', $reserved_keywords_names)
1786             )
1787         );
1788     }
1789 }