libraries/normalization.lib.php

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