libraries/normalization.lib.php

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