src/Normalization.php

   1 <?php
   2
   3 declare(strict_types=1);
   4
   5 namespace PhpMyAdmin;
   6
   7 use PhpMyAdmin\ConfigStorage\Relation;
   8 use PhpMyAdmin\Html\Generator;
   9 use PhpMyAdmin\Query\Compatibility;
  10
  11 use function __;
  12 use function _pgettext;
  13 use function array_merge;
  14 use function array_pop;
  15 use function array_unique;
  16 use function count;
  17 use function explode;
  18 use function htmlspecialchars;
  19 use function implode;
  20 use function in_array;
  21 use function is_array;
  22 use function json_encode;
  23 use function mb_strtoupper;
  24 use function sort;
  25 use function sprintf;
  26 use function str_replace;
  27 use function trim;
  28
  29 /**
  30  * Set of functions used for normalization
  31  */
  32 class Normalization
  33 {
  34     private readonly Config $config;
  35
  36     public function __construct(
  37         private DatabaseInterface $dbi,
  38         private Relation $relation,
  39         private Transformations $transformations,
  40         public Template $template,
  41     ) {
  42         $this->config = Config::getInstance();
  43     }
  44
  45     /**
  46      * build the html for columns of $colTypeCategory category
  47      * in form of given $listType in a table
  48      *
  49      * @param string $db              current database
  50      * @param string $table           current table
  51      * @param string $colTypeCategory supported all|Numeric|String|Spatial
  52      *                                |Date and time using the _pgettext() format
  53      * @param string $listType        type of list to build, supported dropdown|checkbox
  54      *
  55      * @return string HTML for list of columns in form of given list types
  56      */
  57     public function getHtmlForColumnsList(
  58         string $db,
  59         string $table,
  60         string $colTypeCategory = 'all',
  61         string $listType = 'dropdown',
  62     ): string {
  63         $columnTypeList = [];
  64         if ($colTypeCategory !== 'all') {
  65             $types = $this->dbi->types->getColumns();
  66             $columnTypeList = $types[$colTypeCategory];
  67             if (! is_array($columnTypeList)) {
  68                 $columnTypeList = [];
  69             }
  70         }
  71
  72         $this->dbi->selectDb($db);
  73         $columns = $this->dbi->getColumns($db, $table, true);
  74         $selectColHtml = '';
  75         foreach ($columns as $def) {
  76             $column = $def->field;
  77             $extractedColumnSpec = Util::extractColumnSpec($def->type);
  78
  79             if (
  80                 $columnTypeList !== [] && ! in_array(mb_strtoupper(
  81                     $extractedColumnSpec['type'],
  82                 ), $columnTypeList, true)
  83             ) {
  84                 continue;
  85             }
  86
  87             if ($listType === 'checkbox') {
  88                 $selectColHtml .= '<input type="checkbox" value="'
  89                     . htmlspecialchars($column) . '">'
  90                     . htmlspecialchars($column) . ' [ '
  91                     . htmlspecialchars($def->type) . ' ]<br>';
  92             } else {
  93                 $selectColHtml .= '<option value="' . htmlspecialchars($column)
  94                 . '">' . htmlspecialchars($column)
  95                 . ' [ ' . htmlspecialchars($def->type) . ' ]'
  96                 . '</option>';
  97             }
  98         }
  99
 100         return $selectColHtml;
 101     }
 102
 103     /**
 104      * get the html of the form to add the new column to given table
 105      *
 106      * @param int     $numFields  number of columns to add
 107      * @param string  $db         current database
 108      * @param string  $table      current table
 109      * @param mixed[] $columnMeta array containing default values for the fields
 110      *
 111      * @return string HTML
 112      */
 113     public function getHtmlForCreateNewColumn(
 114         UserPrivileges $userPrivileges,
 115         int $numFields,
 116         string $db,
 117         string $table,
 118         array $columnMeta = [],
 119     ): string {
 120         $relationParameters = $this->relation->getRelationParameters();
 121         $contentCells = [];
 122         $availableMime = [];
 123         $mimeMap = [];
 124         if ($relationParameters->browserTransformationFeature !== null && $this->config->settings['BrowseMIME']) {
 125             $mimeMap = $this->transformations->getMime($db, $table);
 126             $availableMime = $this->transformations->getAvailableMimeTypes();
 127         }
 128
 129         $commentsMap = $this->relation->getComments($db, $table);
 130         /** @infection-ignore-all */
 131         for ($columnNumber = 0; $columnNumber < $numFields; $columnNumber++) {
 132             $contentCells[$columnNumber] = [
 133                 'column_number' => $columnNumber,
 134                 'column_meta' => $columnMeta,
 135                 'type_upper' => '',
 136                 'length_values_input_size' => 8,
 137                 'length' => '',
 138                 'extracted_columnspec' => [],
 139                 'submit_attribute' => null,
 140                 'comments_map' => $commentsMap,
 141                 'fields_meta' => null,
 142                 'is_backup' => true,
 143                 'move_columns' => [],
 144                 'available_mime' => $availableMime,
 145                 'mime_map' => $mimeMap,
 146             ];
 147         }
 148
 149         $charsets = Charsets::getCharsets($this->dbi, $this->config->selectedServer['DisableIS']);
 150         $collations = Charsets::getCollations($this->dbi, $this->config->selectedServer['DisableIS']);
 151         $charsetsList = [];
 152         foreach ($charsets as $charset) {
 153             $collationsList = [];
 154             foreach ($collations[$charset->getName()] as $collation) {
 155                 $collationsList[] = ['name' => $collation->getName(), 'description' => $collation->getDescription()];
 156             }
 157
 158             $charsetsList[] = [
 159                 'name' => $charset->getName(),
 160                 'description' => $charset->getDescription(),
 161                 'collations' => $collationsList,
 162             ];
 163         }
 164
 165         return $this->template->render('columns_definitions/table_fields_definitions', [
 166             'is_backup' => true,
 167             'fields_meta' => null,
 168             'relation_parameters' => $relationParameters,
 169             'content_cells' => $contentCells,
 170             'change_column' => $_POST['change_column'] ?? $_GET['change_column'] ?? null,
 171             'is_virtual_columns_supported' => Compatibility::isVirtualColumnsSupported($this->dbi->getVersion()),
 172             'browse_mime' => $this->config->settings['BrowseMIME'],
 173             'supports_stored_keyword' => Compatibility::supportsStoredKeywordForVirtualColumns(
 174                 $this->dbi->getVersion(),
 175             ),
 176             'server_version' => $this->dbi->getVersion(),
 177             'max_rows' => (int) $this->config->settings['MaxRows'],
 178             'char_editing' => $this->config->settings['CharEditing'],
 179             'attribute_types' => $this->dbi->types->getAttributes(),
 180             'privs_available' => $userPrivileges->column && $userPrivileges->isReload,
 181             'max_length' => $this->dbi->getVersion() >= 50503 ? 1024 : 255,
 182             'charsets' => $charsetsList,
 183         ]);
 184     }
 185
 186     /**
 187      * build the html for step 1.1 of normalization
 188      *
 189      * @param string $db           current database
 190      * @param string $table        current table
 191      * @param string $normalizedTo up to which step normalization will go,
 192      *                             possible values 1nf|2nf|3nf
 193      *
 194      * @return string HTML for step 1.1
 195      */
 196     public function getHtmlFor1NFStep1(string $db, string $table, string $normalizedTo): string
 197     {
 198         $step = 1;
 199         $stepTxt = __('Make all columns atomic');
 200         $html = '<h3>' . __('First step of normalization (1NF)') . '</h3>';
 201         $html .= '<div class="card" id="mainContent" data-normalizeto="' . $normalizedTo . '">'
 202             . '<div class="card-header">' . __('Step 1.') . $step . ' ' . $stepTxt . '</div>'
 203             . '<div class="card-body">'
 204             . '<h4>' . __(
 205                 'Do you have any column which can be split into more than one column?'
 206                 . ' For example: address can be split into street, city, country and zip.',
 207             )
 208             . "<br>(<a class='central_columns_dialog' data-maxrows='25' "
 209             . "data-pick=false href='#'> "
 210             . __('Show me the central list of columns that are not already in this table') . ' </a>)</h4>'
 211             . "<p class='cm-em'>" . __(
 212                 'Select a column which can be split into more '
 213                 . 'than one (on select of \'no such column\', it\'ll move to next step).',
 214             )
 215             . '</p>'
 216             . "<div id='extra'>"
 217             . "<select id='selectNonAtomicCol' name='makeAtomic'>"
 218             . '<option selected="selected" disabled="disabled">'
 219             . __('Select one…') . '</option>'
 220             . "<option value='no_such_col'>" . __('No such column') . '</option>'
 221             . $this->getHtmlForColumnsList(
 222                 $db,
 223                 $table,
 224                 _pgettext('string types', 'String'),
 225             )
 226             . '</select>'
 227             . '<span>' . __('split into ')
 228             . "</span><input id='numField' type='number' value='2'>"
 229             . '<input type="submit" class="btn btn-primary" id="splitGo" value="' . __('Go') . '"></div>'
 230             . "<div id='newCols'></div>"
 231             . '</div><div class="card-footer"></div>'
 232             . '</div>';
 233
 234         return $html;
 235     }
 236
 237     /**
 238      * build the html contents of various html elements in step 1.2
 239      *
 240      * @param string $db    current database
 241      * @param string $table current table
 242      *
 243      * @return array{legendText: string, headText: string, subText: string, hasPrimaryKey: string, extra: string}
 244      */
 245     public function getHtmlContentsFor1NFStep2(string $db, string $table): array
 246     {
 247         $step = 2;
 248         $stepTxt = __('Have a primary key');
 249         $primary = Index::getPrimary($this->dbi, $table, $db);
 250         $hasPrimaryKey = '0';
 251         $legendText = __('Step 1.') . $step . ' ' . $stepTxt;
 252         $extra = '';
 253         if ($primary !== null) {
 254             $headText = __('Primary key already exists.');
 255             $subText = __('Taking you to next step…');
 256             $hasPrimaryKey = '1';
 257         } else {
 258             $headText = __(
 259                 'There is no primary key; please add one.<br>'
 260                 . 'Hint: A primary key is a column '
 261                 . '(or combination of columns) that uniquely identify all rows.',
 262             );
 263             $subText = '<a href="#" id="createPrimaryKey">'
 264                 . Generator::getIcon(
 265                     'b_index_add',
 266                     __(
 267                         'Add a primary key on existing column(s)',
 268                     ),
 269                 )
 270                 . '</a>';
 271             $extra = __('If it\'s not possible to make existing column combinations as primary key') . '<br>'
 272                 . '<a href="#" id="addNewPrimary">'
 273                 . __('+ Add a new primary key column') . '</a>';
 274         }
 275
 276         return [
 277             'legendText' => $legendText,
 278             'headText' => $headText,
 279             'subText' => $subText,
 280             'hasPrimaryKey' => $hasPrimaryKey,
 281             'extra' => $extra,
 282         ];
 283     }
 284
 285     /**
 286      * build the html contents of various html elements in step 1.4
 287      *
 288      * @param string $db    current database
 289      * @param string $table current table
 290      *
 291      * @return array{legendText: string, headText: string, subText: string, extra: string} HTML contents for step 1.4
 292      */
 293     public function getHtmlContentsFor1NFStep4(string $db, string $table): array
 294     {
 295         $step = 4;
 296         $stepTxt = __('Remove redundant columns');
 297         $legendText = __('Step 1.') . $step . ' ' . $stepTxt;
 298         $headText = __(
 299             'Do you have a group of columns which on combining gives an existing'
 300             . ' column? For example, if you have first_name, last_name and'
 301             . ' full_name then combining first_name and last_name gives full_name'
 302             . ' which is redundant.',
 303         );
 304         $subText = __(
 305             'Check the columns which are redundant and click on remove. '
 306             . "If no redundant column, click on 'No redundant column'",
 307         );
 308         $extra = $this->getHtmlForColumnsList($db, $table, 'all', 'checkbox') . '<br>'
 309             . '<input class="btn btn-secondary" type="submit" id="removeRedundant" value="'
 310             . __('Remove selected') . '">'
 311             . '<input class="btn btn-secondary" type="submit" id="noRedundantColumn" value="'
 312             . __('No redundant column') . '">';
 313
 314         return ['legendText' => $legendText, 'headText' => $headText, 'subText' => $subText, 'extra' => $extra];
 315     }
 316
 317     /**
 318      * build the html contents of various html elements in step 1.3
 319      *
 320      * @param string $db    current database
 321      * @param string $table current table
 322      *
 323      * @return array{legendText: string, headText: string, subText: string, extra: string, primary_key: false|string}
 324      */
 325     public function getHtmlContentsFor1NFStep3(string $db, string $table): array
 326     {
 327         $step = 3;
 328         $stepTxt = __('Move repeating groups');
 329         $legendText = __('Step 1.') . $step . ' ' . $stepTxt;
 330         $headText = __(
 331             'Do you have a group of two or more columns that are closely '
 332             . 'related and are all repeating the same attribute? For example, '
 333             . 'a table that holds data on books might have columns such as book_id, '
 334             . 'author1, author2, author3 and so on which form a '
 335             . 'repeating group. In this case a new table (book_id, author) should '
 336             . 'be created.',
 337         );
 338         $subText = __(
 339             'Check the columns which form a repeating group. If no such group, click on \'No repeating group\'',
 340         );
 341         $extra = $this->getHtmlForColumnsList($db, $table, 'all', 'checkbox') . '<br>'
 342             . '<input class="btn btn-secondary" type="submit" id="moveRepeatingGroup" value="'
 343             . __('Done') . '">'
 344             . '<input class="btn btn-secondary" type="submit" value="' . __('No repeating group')
 345             . '" id="noRepeatingGroup">';
 346         $primary = Index::getPrimary($this->dbi, $table, $db);
 347         $primarycols = $primary === null ? [] : $primary->getColumns();
 348         $pk = [];
 349         foreach ($primarycols as $col) {
 350             $pk[] = $col->getName();
 351         }
 352
 353         return [
 354             'legendText' => $legendText,
 355             'headText' => $headText,
 356             'subText' => $subText,
 357             'extra' => $extra,
 358             'primary_key' => json_encode($pk),
 359         ];
 360     }
 361
 362     /**
 363      * build html contents for 2NF step 2.1
 364      *
 365      * @param string $db    current database
 366      * @param string $table current table
 367      *
 368      * @return array{legendText: string, headText: string, subText: string, extra: string, primary_key: string}
 369      */
 370     public function getHtmlFor2NFstep1(string $db, string $table): array
 371     {
 372         $legendText = __('Step 2.') . '1 ' . __('Find partial dependencies');
 373         $primary = Index::getPrimary($this->dbi, $table, $db);
 374         $primarycols = $primary === null ? [] : $primary->getColumns();
 375         $pk = [];
 376         $subText = '';
 377         $selectPkForm = '';
 378         $extra = '';
 379         foreach ($primarycols as $col) {
 380             $pk[] = $col->getName();
 381             $selectPkForm .= '<input type="checkbox" name="pd" value="'
 382                 . htmlspecialchars($col->getName()) . '">'
 383                 . htmlspecialchars($col->getName());
 384         }
 385
 386         $key = implode(', ', $pk);
 387         if (count($primarycols) > 1) {
 388             $this->dbi->selectDb($db);
 389             $columns = $this->dbi->getColumnNames($db, $table);
 390             if (count($pk) === count($columns)) {
 391                 $headText = sprintf(
 392                     __(
 393                         'No partial dependencies possible as '
 394                         . 'no non-primary column exists since primary key ( %1$s ) '
 395                         . 'is composed of all the columns in the table.',
 396                     ),
 397                     htmlspecialchars($key),
 398                 ) . '<br>';
 399                 $extra = '<h3>' . __('Table is already in second normal form.')
 400                     . '</h3>';
 401             } else {
 402                 $headText = sprintf(
 403                     __(
 404                         'The primary key ( %1$s ) consists of more than one column '
 405                         . 'so we need to find the partial dependencies.',
 406                     ),
 407                     htmlspecialchars($key),
 408                 ) . '<br>' . __('Please answer the following question(s) carefully to obtain a correct normalization.')
 409                     . '<br><a href="#" id="showPossiblePd">' . __(
 410                         '+ Show me the possible partial dependencies based on data in the table',
 411                     ) . '</a>';
 412                 $subText = __(
 413                     'For each column below, '
 414                     . 'please select the <b>minimal set</b> of columns among given set '
 415                     . 'whose values combined together are sufficient'
 416                     . ' to determine the value of the column.',
 417                 );
 418                 $cnt = 0;
 419                 foreach ($columns as $column) {
 420                     if (in_array($column, $pk, true)) {
 421                         continue;
 422                     }
 423
 424                     $cnt++;
 425                     $extra .= '<b>' . sprintf(
 426                         __('\'%1$s\' depends on:'),
 427                         htmlspecialchars($column),
 428                     ) . '</b><br>';
 429                     $extra .= '<form id="pk_' . $cnt . '" data-colname="'
 430                         . htmlspecialchars($column) . '" class="smallIndent">'
 431                         . $selectPkForm . '</form><br><br>';
 432                 }
 433             }
 434         } else {
 435             $headText = sprintf(
 436                 __(
 437                     'No partial dependencies possible as the primary key ( %1$s ) has just one column.',
 438                 ),
 439                 htmlspecialchars($key),
 440             ) . '<br>';
 441             $extra = '<h3>' . __('Table is already in second normal form.') . '</h3>';
 442         }
 443
 444         return [
 445             'legendText' => $legendText,
 446             'headText' => $headText,
 447             'subText' => $subText,
 448             'extra' => $extra,
 449             'primary_key' => $key,
 450         ];
 451     }
 452
 453     /**
 454      * build the html for showing the tables to have in order to put current table in 2NF
 455      *
 456      * @param mixed[] $partialDependencies array containing all the dependencies
 457      * @param string  $table               current table
 458      *
 459      * @return string HTML
 460      */
 461     public function getHtmlForNewTables2NF(array $partialDependencies, string $table): string
 462     {
 463         $html = '<p><b>' . sprintf(
 464             __(
 465                 'In order to put the '
 466                 . 'original table \'%1$s\' into Second normal form we need '
 467                 . 'to create the following tables:',
 468             ),
 469             htmlspecialchars($table),
 470         ) . '</b></p>';
 471         $tableName = $table;
 472         $i = 1;
 473         foreach ($partialDependencies as $key => $dependents) {
 474             $html .= '<p><input type="text" name="' . htmlspecialchars($key)
 475                 . '" value="' . htmlspecialchars($tableName) . '">'
 476                 . '( <u>' . htmlspecialchars($key) . '</u>'
 477                 . (count($dependents) > 0 ? ', ' : '')
 478                 . htmlspecialchars(implode(', ', $dependents)) . ' )';
 479             $i++;
 480             $tableName = 'table' . $i;
 481         }
 482
 483         return $html;
 484     }
 485
 486     /**
 487      * create/alter the tables needed for 2NF
 488      *
 489      * @param mixed[] $partialDependencies array containing all the partial dependencies
 490      * @param object  $tablesName          name of new tables
 491      * @param string  $table               current table
 492      * @param string  $db                  current database
 493      *
 494      * @return array{legendText: string, headText: string, queryError: bool, extra: Message}
 495      */
 496     public function createNewTablesFor2NF(
 497         array $partialDependencies,
 498         object $tablesName,
 499         string $table,
 500         string $db,
 501     ): array {
 502         $dropCols = false;
 503         $nonPKCols = [];
 504         $queries = [];
 505         $error = false;
 506         $headText = '<h3>' . sprintf(
 507             __('The second step of normalization is complete for table \'%1$s\'.'),
 508             htmlspecialchars($table),
 509         ) . '</h3>';
 510         if (count($partialDependencies) === 1) {
 511             return ['legendText' => __('End of step'), 'headText' => $headText, 'queryError' => false];
 512         }
 513
 514         $message = '';
 515         $this->dbi->selectDb($db);
 516         foreach ($partialDependencies as $key => $dependents) {
 517             if ($tablesName->$key != $table) {
 518                 $keys = explode(', ', $key);
 519                 $quotedKeys = [];
 520                 foreach ($keys as $eachKey) {
 521                     $quotedKeys[] = Util::backquote($eachKey);
 522                 }
 523
 524                 $backquotedKey = implode(', ', $quotedKeys);
 525
 526                 $quotedDependents = [];
 527                 foreach ($dependents as $dependent) {
 528                     $quotedDependents[] = Util::backquote($dependent);
 529                 }
 530
 531                 $queries[] = 'CREATE TABLE ' . Util::backquote($tablesName->$key)
 532                     . ' SELECT DISTINCT ' . $backquotedKey
 533                     . (count($dependents) > 0 ? ', ' : '')
 534                     . implode(',', $quotedDependents)
 535                     . ' FROM ' . Util::backquote($table) . ';';
 536                 $queries[] = 'ALTER TABLE ' . Util::backquote($tablesName->$key)
 537                     . ' ADD PRIMARY KEY(' . $backquotedKey . ');';
 538                 $nonPKCols = array_merge($nonPKCols, $dependents);
 539             } else {
 540                 $dropCols = true;
 541             }
 542         }
 543
 544         if ($dropCols) {
 545             $query = 'ALTER TABLE ' . Util::backquote($table);
 546             foreach ($nonPKCols as $col) {
 547                 $query .= ' DROP ' . Util::backquote($col) . ',';
 548             }
 549
 550             $query = trim($query, ', ');
 551             $query .= ';';
 552             $queries[] = $query;
 553         } else {
 554             $queries[] = 'DROP TABLE ' . Util::backquote($table);
 555         }
 556
 557         foreach ($queries as $query) {
 558             if (! $this->dbi->tryQuery($query)) {
 559                 $message = Message::error(__('Error in processing!'));
 560                 $message->addMessage(
 561                     Message::rawError($this->dbi->getError()),
 562                     '<br><br>',
 563                 );
 564                 $error = true;
 565                 break;
 566             }
 567         }
 568
 569         return [
 570             'legendText' => __('End of step'),
 571             'headText' => $headText,
 572             'queryError' => $error,
 573             'extra' => $message,
 574         ];
 575     }
 576
 577     /**
 578      * build the html for showing the new tables to have in order
 579      * to put given tables in 3NF
 580      *
 581      * @param object  $dependencies containing all the dependencies
 582      * @param mixed[] $tables       tables formed after 2NF and need to convert to 3NF
 583      * @param string  $db           current database
 584      *
 585      * @return array{html:string, newTables:mixed[], success:true} containing html and the list of new tables
 586      */
 587     public function getHtmlForNewTables3NF(object $dependencies, array $tables, string $db): array
 588     {
 589         $html = '';
 590         $i = 1;
 591         $newTables = [];
 592         foreach ($tables as $table => $arrDependson) {
 593             if (count(array_unique($arrDependson)) === 1) {
 594                 continue;
 595             }
 596
 597             $primary = Index::getPrimary($this->dbi, $table, $db);
 598             $primarycols = $primary === null ? [] : $primary->getColumns();
 599             $pk = [];
 600             foreach ($primarycols as $col) {
 601                 $pk[] = $col->getName();
 602             }
 603
 604             $html .= '<p><b>' . sprintf(
 605                 __(
 606                     'In order to put the '
 607                     . 'original table \'%1$s\' into Third normal form we need '
 608                     . 'to create the following tables:',
 609                 ),
 610                 htmlspecialchars($table),
 611             ) . '</b></p>';
 612             $tableName = $table;
 613             $columnList = [];
 614             foreach ($arrDependson as $key) {
 615                 $dependents = $dependencies->$key;
 616                 if ($key == $table) {
 617                     $key = implode(', ', $pk);
 618                 }
 619
 620                 $tmpTableCols = array_merge(explode(', ', $key), $dependents);
 621                 sort($tmpTableCols);
 622                 if (in_array($tmpTableCols, $columnList)) {
 623                     continue;
 624                 }
 625
 626                 $columnList[] = $tmpTableCols;
 627                 $html .= '<p><input type="text" name="'
 628                     . htmlspecialchars($tableName)
 629                     . '" value="' . htmlspecialchars($tableName) . '">'
 630                     . '( <u>' . htmlspecialchars($key) . '</u>'
 631                     . (count($dependents) > 0 ? ', ' : '')
 632                     . htmlspecialchars(implode(', ', $dependents)) . ' )';
 633                 $newTables[$table][$tableName] = ['pk' => $key, 'nonpk' => implode(', ', $dependents)];
 634                 $i++;
 635                 $tableName = 'table' . $i;
 636             }
 637         }
 638
 639         return ['html' => $html, 'newTables' => $newTables, 'success' => true];
 640     }
 641
 642     /**
 643      * create new tables or alter existing to get 3NF
 644      *
 645      * @param mixed[] $newTables list of new tables to be created
 646      * @param string  $db        current database
 647      *
 648      * @return array{legendText: string, headText: string, queryError: string|false, extra?: string}
 649      */
 650     public function createNewTablesFor3NF(array $newTables, string $db): array
 651     {
 652         $queries = [];
 653         $dropCols = false;
 654         $error = false;
 655         $headText = '<h3>' . __('The third step of normalization is complete.') . '</h3>';
 656         if ($newTables === []) {
 657             return ['legendText' => __('End of step'), 'headText' => $headText, 'queryError' => false];
 658         }
 659
 660         $message = '';
 661         $this->dbi->selectDb($db);
 662         foreach ($newTables as $originalTable => $tablesList) {
 663             foreach ($tablesList as $table => $cols) {
 664                 if ($table != $originalTable) {
 665                     $pkArray = explode(', ', $cols['pk']);
 666                     $quotedPkArray = [];
 667                     foreach ($pkArray as $pk) {
 668                         $quotedPkArray[] = Util::backquote($pk);
 669                     }
 670
 671                     $quotedPk = implode(', ', $quotedPkArray);
 672
 673                     $nonpkArray = explode(', ', $cols['nonpk']);
 674                     $quotedNonpkArray = [];
 675                     foreach ($nonpkArray as $nonpk) {
 676                         $quotedNonpkArray[] = Util::backquote($nonpk);
 677                     }
 678
 679                     $quotedNonpk = implode(', ', $quotedNonpkArray);
 680
 681                     $queries[] = 'CREATE TABLE ' . Util::backquote($table)
 682                         . ' SELECT DISTINCT ' . $quotedPk
 683                         . ', ' . $quotedNonpk
 684                         . ' FROM ' . Util::backquote($originalTable) . ';';
 685                     $queries[] = 'ALTER TABLE ' . Util::backquote($table)
 686                         . ' ADD PRIMARY KEY(' . $quotedPk . ');';
 687                 } else {
 688                     $dropCols = $cols;
 689                 }
 690             }
 691
 692             if ($dropCols) {
 693                 $columns = $this->dbi->getColumnNames($db, $originalTable);
 694                 $colPresent = array_merge(
 695                     explode(', ', $dropCols['pk']),
 696                     explode(', ', $dropCols['nonpk']),
 697                 );
 698                 $query = 'ALTER TABLE ' . Util::backquote($originalTable);
 699                 foreach ($columns as $col) {
 700                     if (in_array($col, $colPresent, true)) {
 701                         continue;
 702                     }
 703
 704                     $query .= ' DROP ' . Util::backquote($col) . ',';
 705                 }
 706
 707                 $query = trim($query, ', ');
 708                 $query .= ';';
 709                 $queries[] = $query;
 710             } else {
 711                 $queries[] = 'DROP TABLE ' . Util::backquote($originalTable);
 712             }
 713
 714             $dropCols = false;
 715         }
 716
 717         foreach ($queries as $query) {
 718             if (! $this->dbi->tryQuery($query)) {
 719                 $message = Message::error(__('Error in processing!'));
 720                 $message->addMessage(
 721                     Message::rawError($this->dbi->getError()),
 722                     '<br><br>',
 723                 );
 724                 $error = true;
 725                 break;
 726             }
 727         }
 728
 729         return [
 730             'legendText' => __('End of step'),
 731             'headText' => $headText,
 732             'queryError' => $error,
 733             'extra' => $message,
 734         ];
 735     }
 736
 737     /**
 738      * move the repeating group of columns to a new table
 739      *
 740      * @param string $repeatingColumns comma separated list of repeating group columns
 741      * @param string $primaryColumns   comma separated list of column in primary key
 742      *                                 of $table
 743      * @param string $newTable         name of the new table to be created
 744      * @param string $newColumn        name of the new column in the new table
 745      * @param string $table            current table
 746      * @param string $db               current database
 747      *
 748      * @return array{queryError: bool, message: Message}
 749      */
 750     public function moveRepeatingGroup(
 751         string $repeatingColumns,
 752         string $primaryColumns,
 753         string $newTable,
 754         string $newColumn,
 755         string $table,
 756         string $db,
 757     ): array {
 758         $repeatingColumnsArr = explode(', ', $repeatingColumns);
 759         $primaryColumnsArray = explode(',', $primaryColumns);
 760         $columns = [];
 761         foreach ($primaryColumnsArray as $column) {
 762             $columns[] = Util::backquote($column);
 763         }
 764
 765         $primaryColumns = implode(',', $columns);
 766         $query1 = 'CREATE TABLE ' . Util::backquote($newTable);
 767         $query2 = 'ALTER TABLE ' . Util::backquote($table);
 768         $message = Message::success(
 769             sprintf(
 770                 __('Selected repeating group has been moved to the table \'%s\''),
 771                 htmlspecialchars($table),
 772             ),
 773         );
 774         $first = true;
 775         $error = false;
 776         foreach ($repeatingColumnsArr as $repeatingColumn) {
 777             if (! $first) {
 778                 $query1 .= ' UNION ';
 779             }
 780
 781             $first = false;
 782             $quotedRepeatingColumn = Util::backquote($repeatingColumn);
 783             $query1 .= ' SELECT ' . $primaryColumns . ',' . $quotedRepeatingColumn
 784                 . ' as ' . Util::backquote($newColumn)
 785                 . ' FROM ' . Util::backquote($table);
 786             $query2 .= ' DROP ' . $quotedRepeatingColumn . ',';
 787         }
 788
 789         $query2 = trim($query2, ',');
 790         $queries = [$query1, $query2];
 791         $this->dbi->selectDb($db);
 792         foreach ($queries as $query) {
 793             if (! $this->dbi->tryQuery($query)) {
 794                 $message = Message::error(__('Error in processing!'));
 795                 $message->addMessage(
 796                     Message::rawError($this->dbi->getError()),
 797                     '<br><br>',
 798                 );
 799                 $error = true;
 800                 break;
 801             }
 802         }
 803
 804         return ['queryError' => $error, 'message' => $message];
 805     }
 806
 807     /**
 808      * build html for 3NF step 1 to find the transitive dependencies
 809      *
 810      * @param string  $db     current database
 811      * @param mixed[] $tables tables formed after 2NF and need to process for 3NF
 812      *
 813      * @return array{legendText: string, headText: string, subText: string, extra: string}
 814      */
 815     public function getHtmlFor3NFstep1(string $db, array $tables): array
 816     {
 817         $legendText = __('Step 3.') . '1 ' . __('Find transitive dependencies');
 818         $extra = '';
 819         $headText = __('Please answer the following question(s) carefully to obtain a correct normalization.');
 820         $subText = __(
 821             'For each column below, '
 822             . 'please select the <b>minimal set</b> of columns among given set '
 823             . 'whose values combined together are sufficient'
 824             . ' to determine the value of the column.<br>'
 825             . 'Note: A column may have no transitive dependency, '
 826             . 'in that case you don\'t have to select any.',
 827         );
 828         $cnt = 0;
 829         $this->dbi->selectDb($db);
 830         foreach ($tables as $table) {
 831             $primary = Index::getPrimary($this->dbi, $table, $db);
 832             $primarycols = $primary === null ? [] : $primary->getColumns();
 833             $selectTdForm = '';
 834             $pk = [];
 835             foreach ($primarycols as $col) {
 836                 $pk[] = $col->getName();
 837             }
 838
 839             $columns = $this->dbi->getColumnNames($db, $table);
 840             if (count($columns) - count($pk) <= 1) {
 841                 continue;
 842             }
 843
 844             foreach ($columns as $column) {
 845                 if (in_array($column, $pk, true)) {
 846                     continue;
 847                 }
 848
 849                 $selectTdForm .= '<input type="checkbox" name="pd" value="'
 850                 . htmlspecialchars($column) . '">'
 851                 . '<span>' . htmlspecialchars($column) . '</span>';
 852             }
 853
 854             foreach ($columns as $column) {
 855                 if (in_array($column, $pk, true)) {
 856                     continue;
 857                 }
 858
 859                 $cnt++;
 860                 $extra .= '<b>' . sprintf(
 861                     __('\'%1$s\' depends on:'),
 862                     htmlspecialchars($column),
 863                 )
 864                     . '</b><br>';
 865                 $extra .= '<form id="td_' . $cnt . '" data-colname="'
 866                     . htmlspecialchars($column) . '" data-tablename="'
 867                     . htmlspecialchars($table) . '" class="smallIndent">'
 868                     . $selectTdForm
 869                     . '</form><br><br>';
 870             }
 871         }
 872
 873         if ($extra === '') {
 874             $headText = __(
 875                 'No Transitive dependencies possible as the table doesn\'t have any non primary key columns',
 876             );
 877             $subText = '';
 878             $extra = '<h3>' . __('Table is already in Third normal form!') . '</h3>';
 879         }
 880
 881         return ['legendText' => $legendText, 'headText' => $headText, 'subText' => $subText, 'extra' => $extra];
 882     }
 883
 884     /**
 885      * find all the possible partial dependencies based on data in the table.
 886      *
 887      * @param string $table current table
 888      * @param string $db    current database
 889      *
 890      * @return string HTML containing the list of all the possible partial dependencies
 891      */
 892     public function findPartialDependencies(string $table, string $db): string
 893     {
 894         $dependencyList = [];
 895         $this->dbi->selectDb($db);
 896         $columnNames = $this->dbi->getColumnNames($db, $table);
 897         $columns = [];
 898         foreach ($columnNames as $column) {
 899             $columns[] = Util::backquote($column);
 900         }
 901
 902         $totalRowsRes = $this->dbi->fetchResult(
 903             'SELECT COUNT(*) FROM (SELECT * FROM '
 904             . Util::backquote($table) . ' LIMIT 500) as dt;',
 905         );
 906         $totalRows = $totalRowsRes[0];
 907         $primary = Index::getPrimary($this->dbi, $table, $db);
 908         $primarycols = $primary === null ? [] : $primary->getColumns();
 909         $pk = [];
 910         foreach ($primarycols as $col) {
 911             $pk[] = Util::backquote($col->getName());
 912         }
 913
 914         $partialKeys = $this->getAllCombinationPartialKeys($pk);
 915         $distinctValCount = $this->findDistinctValuesCount(
 916             array_unique(
 917                 array_merge($columns, $partialKeys),
 918             ),
 919             $table,
 920         );
 921         foreach ($columns as $column) {
 922             if (in_array($column, $pk, true)) {
 923                 continue;
 924             }
 925
 926             foreach ($partialKeys as $partialKey) {
 927                 if (
 928                     ! $partialKey
 929                     || ! $this->checkPartialDependency(
 930                         $partialKey,
 931                         $column,
 932                         $table,
 933                         $distinctValCount[$partialKey],
 934                         $distinctValCount[$column],
 935                         $totalRows,
 936                     )
 937                 ) {
 938                     continue;
 939                 }
 940
 941                 $dependencyList[$partialKey][] = $column;
 942             }
 943         }
 944
 945         $html = __('This list is based on a subset of the table\'s data and is not necessarily accurate. ')
 946             . '<div class="dependencies_box">';
 947         foreach ($dependencyList as $dependon => $colList) {
 948             $html .= '<span class="d-block">'
 949                 . '<input type="button" class="btn btn-secondary pickPd" value="' . __('Pick') . '">'
 950                 . '<span class="determinants">'
 951                 . htmlspecialchars(str_replace('`', '', (string) $dependon)) . '</span> -> '
 952                 . '<span class="dependents">'
 953                 . htmlspecialchars(str_replace('`', '', implode(', ', $colList)))
 954                 . '</span>'
 955                 . '</span>';
 956         }
 957
 958         if ($dependencyList === []) {
 959             $html .= '<p class="d-block m-1">'
 960                 . __('No partial dependencies found!') . '</p>';
 961         }
 962
 963         $html .= '</div>';
 964
 965         return $html;
 966     }
 967
 968     /**
 969      * check whether a particular column is dependent on given subset of primary key
 970      *
 971      * @param string $partialKey the partial key, subset of primary key,
 972      *                           each column in key supposed to be backquoted
 973      * @param string $column     backquoted column on whose dependency being checked
 974      * @param string $table      current table
 975      * @param int    $pkCnt      distinct value count for given partial key
 976      * @param int    $colCnt     distinct value count for given column
 977      * @param int    $totalRows  total distinct rows count of the table
 978      */
 979     private function checkPartialDependency(
 980         string $partialKey,
 981         string $column,
 982         string $table,
 983         int $pkCnt,
 984         int $colCnt,
 985         int $totalRows,
 986     ): bool {
 987         $query = 'SELECT '
 988             . 'COUNT(DISTINCT ' . $partialKey . ',' . $column . ') as pkColCnt '
 989             . 'FROM (SELECT * FROM ' . Util::backquote($table)
 990             . ' LIMIT 500) as dt;';
 991         $res = $this->dbi->fetchResult($query);
 992         $pkColCnt = $res[0];
 993         if ($pkCnt !== 0 && $pkCnt === $colCnt && $colCnt == $pkColCnt) {
 994             return true;
 995         }
 996
 997         return $totalRows !== 0 && $totalRows === $pkCnt;
 998     }
 999
1000     /**
1001      * function to get distinct values count of all the column in the array $columns
1002      *
1003      * @param string[] $columns array of backquoted columns whose distinct values
1004      *                       need to be counted.
1005      * @param string   $table   table to which these columns belong
1006      *
1007      * @return int[] associative array containing the count
1008      */
1009     private function findDistinctValuesCount(array $columns, string $table): array
1010     {
1011         $result = [];
1012         $query = 'SELECT ';
1013         foreach ($columns as $column) {
1014             if ($column === '') {
1015                 continue;
1016             }
1017
1018             //each column is already backquoted
1019             $query .= 'COUNT(DISTINCT ' . $column . ') as \''
1020                 . $column . '_cnt\', ';
1021         }
1022
1023         $query = trim($query, ', ');
1024         $query .= ' FROM (SELECT * FROM ' . Util::backquote($table)
1025             . ' LIMIT 500) as dt;';
1026         $res = $this->dbi->fetchResult($query);
1027         foreach ($columns as $column) {
1028             if ($column === '') {
1029                 continue;
1030             }
1031
1032             $result[$column] = (int) ($res[0][$column . '_cnt'] ?? null);
1033         }
1034
1035         return $result;
1036     }
1037
1038     /**
1039      * find all the possible partial keys
1040      *
1041      * @param mixed[] $primaryKey array containing all the column present in primary key
1042      *
1043      * @return string[] containing all the possible partial keys(subset of primary key)
1044      */
1045     private function getAllCombinationPartialKeys(array $primaryKey): array
1046     {
1047         $results = [''];
1048         foreach ($primaryKey as $element) {
1049             foreach ($results as $combination) {
1050                 $results[] = trim($element . ',' . $combination, ',');
1051             }
1052         }
1053
1054         array_pop($results); //remove key which consist of all primary key columns
1055
1056         return $results;
1057     }
1058 }