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