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