From 5bf66662a9bdd62c5bccab15e607cd95cfb8fcab Mon Sep 17 00:00:00 2001 From: Charles Cabergs Date: Mon, 27 Jul 2020 10:05:23 +0200 Subject: Removed wordpress and phpmyadmin, my server doesn't handle it well and it brings shame on my familly --- .../phpmyadmin/libraries/classes/Normalization.php | 1105 -------------------- 1 file changed, 1105 deletions(-) delete mode 100644 srcs/phpmyadmin/libraries/classes/Normalization.php (limited to 'srcs/phpmyadmin/libraries/classes/Normalization.php') diff --git a/srcs/phpmyadmin/libraries/classes/Normalization.php b/srcs/phpmyadmin/libraries/classes/Normalization.php deleted file mode 100644 index 6c3d73b..0000000 --- a/srcs/phpmyadmin/libraries/classes/Normalization.php +++ /dev/null @@ -1,1105 +0,0 @@ -dbi = $dbi; - $this->relation = $relation; - $this->transformations = $transformations; - $this->template = $template; - } - - /** - * build the html for columns of $colTypeCategory category - * in form of given $listType in a table - * - * @param string $db current database - * @param string $table current table - * @param string $colTypeCategory supported all|Numeric|String|Spatial - * |Date and time using the _pgettext() format - * @param string $listType type of list to build, supported dropdown|checkbox - * - * @return string HTML for list of columns in form of given list types - */ - public function getHtmlForColumnsList( - $db, - $table, - $colTypeCategory = 'all', - $listType = 'dropdown' - ) { - $columnTypeList = []; - if ($colTypeCategory != 'all') { - $types = $this->dbi->types->getColumns(); - $columnTypeList = $types[$colTypeCategory]; - } - $this->dbi->selectDb($db); - $columns = $this->dbi->getColumns( - $db, - $table, - null, - true - ); - $type = ""; - $selectColHtml = ""; - foreach ($columns as $column => $def) { - if (isset($def['Type'])) { - $extractedColumnSpec = Util::extractColumnSpec($def['Type']); - $type = $extractedColumnSpec['type']; - } - if (empty($columnTypeList) - || in_array(mb_strtoupper($type), $columnTypeList) - ) { - if ($listType == 'checkbox') { - $selectColHtml .= '' - . htmlspecialchars($column) . ' [ ' - . htmlspecialchars($def['Type']) . ' ]
'; - } else { - $selectColHtml .= ''; - } - } - } - return $selectColHtml; - } - - /** - * get the html of the form to add the new column to given table - * - * @param integer $numFields number of columns to add - * @param string $db current database - * @param string $table current table - * @param array $columnMeta array containing default values for the fields - * - * @return string HTML - */ - public function getHtmlForCreateNewColumn( - $numFields, - $db, - $table, - array $columnMeta = [] - ) { - $cfgRelation = $this->relation->getRelationsParam(); - $contentCells = []; - $availableMime = []; - $mimeMap = []; - if ($cfgRelation['mimework'] && $GLOBALS['cfg']['BrowseMIME']) { - $mimeMap = $this->transformations->getMime($db, $table); - $availableMimeTypes = $this->transformations->getAvailableMimeTypes(); - if ($availableMimeTypes !== null) { - $availableMime = $availableMimeTypes; - } - } - $commentsMap = $this->relation->getComments($db, $table); - for ($columnNumber = 0; $columnNumber < $numFields; $columnNumber++) { - $contentCells[$columnNumber] = [ - 'column_number' => $columnNumber, - 'column_meta' => $columnMeta, - 'type_upper' => '', - 'length_values_input_size' => 8, - 'length' => '', - 'extracted_columnspec' => [], - 'submit_attribute' => null, - 'comments_map' => $commentsMap, - 'fields_meta' => null, - 'is_backup' => true, - 'move_columns' => [], - 'cfg_relation' => $cfgRelation, - 'available_mime' => $availableMime, - 'mime_map' => $mimeMap, - ]; - } - - $charsets = Charsets::getCharsets($this->dbi, $GLOBALS['cfg']['Server']['DisableIS']); - $collations = Charsets::getCollations($this->dbi, $GLOBALS['cfg']['Server']['DisableIS']); - $charsetsList = []; - /** @var Charset $charset */ - foreach ($charsets as $charset) { - $collationsList = []; - /** @var Collation $collation */ - foreach ($collations[$charset->getName()] as $collation) { - $collationsList[] = [ - 'name' => $collation->getName(), - 'description' => $collation->getDescription(), - ]; - } - $charsetsList[] = [ - 'name' => $charset->getName(), - 'description' => $charset->getDescription(), - 'collations' => $collationsList, - ]; - } - - return $this->template->render('columns_definitions/table_fields_definitions', [ - 'is_backup' => true, - 'fields_meta' => null, - 'mimework' => $cfgRelation['mimework'], - 'content_cells' => $contentCells, - 'change_column' => $_POST['change_column'], - 'is_virtual_columns_supported' => Util::isVirtualColumnsSupported(), - 'browse_mime' => $GLOBALS['cfg']['BrowseMIME'], - 'server_type' => Util::getServerType(), - 'max_rows' => intval($GLOBALS['cfg']['MaxRows']), - 'char_editing' => $GLOBALS['cfg']['CharEditing'], - 'attribute_types' => $this->dbi->types->getAttributes(), - 'privs_available' => $GLOBALS['col_priv'] && $GLOBALS['is_reload_priv'], - 'max_length' => $this->dbi->getVersion() >= 50503 ? 1024 : 255, - 'charsets' => $charsetsList, - ]); - } - - /** - * build the html for step 1.1 of normalization - * - * @param string $db current database - * @param string $table current table - * @param string $normalizedTo up to which step normalization will go, - * possible values 1nf|2nf|3nf - * - * @return string HTML for step 1.1 - */ - public function getHtmlFor1NFStep1($db, $table, $normalizedTo) - { - $step = 1; - $stepTxt = __('Make all columns atomic'); - $html = "

" - . __('First step of normalization (1NF)') . "

"; - $html .= "
" . - "
" . - "" . __('Step 1.') . $step . " " . $stepTxt . "" . - "

" . __( - 'Do you have any column which can be split into more than' - . ' one column? ' - . 'For example: address can be split into street, city, country and zip.' - ) - . "
( " - . __( - 'Show me the central list of columns that are not already in this table' - ) . " )

" - . "

" . __( - 'Select a column which can be split into more ' - . 'than one (on select of \'no such column\', it\'ll move to next step).' - ) - . "

" - . "
" - . "" - . "" . __('split into ') - . "" - . "
" - . "
" - . "
" - . "
" - . "
"; - return $html; - } - - /** - * build the html contents of various html elements in step 1.2 - * - * @param string $db current database - * @param string $table current table - * - * @return string[] HTML contents for step 1.2 - */ - public function getHtmlContentsFor1NFStep2($db, $table) - { - $step = 2; - $stepTxt = __('Have a primary key'); - $primary = Index::getPrimary($table, $db); - $hasPrimaryKey = "0"; - $legendText = __('Step 1.') . $step . " " . $stepTxt; - $extra = ''; - if ($primary) { - $headText = __("Primary key already exists."); - $subText = __("Taking you to next step…"); - $hasPrimaryKey = "1"; - } else { - $headText = __( - "There is no primary key; please add one.
" - . "Hint: A primary key is a column " - . "(or combination of columns) that uniquely identify all rows." - ); - $subText = '' - . Util::getIcon( - 'b_index_add', - __( - 'Add a primary key on existing column(s)' - ) - ) - . ''; - $extra = __( - "If it's not possible to make existing " - . "column combinations as primary key" - ) . "
" - . '' - . __('+ Add a new primary key column') . ''; - } - return [ - 'legendText' => $legendText, - 'headText' => $headText, - 'subText' => $subText, - 'hasPrimaryKey' => $hasPrimaryKey, - 'extra' => $extra, - ]; - } - - /** - * build the html contents of various html elements in step 1.4 - * - * @param string $db current database - * @param string $table current table - * - * @return string[] HTML contents for step 1.4 - */ - public function getHtmlContentsFor1NFStep4($db, $table) - { - $step = 4; - $stepTxt = __('Remove redundant columns'); - $legendText = __('Step 1.') . $step . " " . $stepTxt; - $headText = __( - "Do you have a group of columns which on combining gives an existing" - . " column? For example, if you have first_name, last_name and" - . " full_name then combining first_name and last_name gives full_name" - . " which is redundant." - ); - $subText = __( - "Check the columns which are redundant and click on remove. " - . "If no redundant column, click on 'No redundant column'" - ); - $extra = $this->getHtmlForColumnsList($db, $table, 'all', "checkbox") . "
" - . '' - . ''; - return [ - 'legendText' => $legendText, - 'headText' => $headText, - 'subText' => $subText, - 'extra' => $extra, - ]; - } - - /** - * build the html contents of various html elements in step 1.3 - * - * @param string $db current database - * @param string $table current table - * - * @return string[] HTML contents for step 1.3 - */ - public function getHtmlContentsFor1NFStep3($db, $table) - { - $step = 3; - $stepTxt = __('Move repeating groups'); - $legendText = __('Step 1.') . $step . " " . $stepTxt; - $headText = __( - "Do you have a group of two or more columns that are closely " - . "related and are all repeating the same attribute? For example, " - . "a table that holds data on books might have columns such as book_id, " - . "author1, author2, author3 and so on which form a " - . "repeating group. In this case a new table (book_id, author) should " - . "be created." - ); - $subText = __( - "Check the columns which form a repeating group. " - . "If no such group, click on 'No repeating group'" - ); - $extra = $this->getHtmlForColumnsList($db, $table, 'all', "checkbox") . "
" - . '' - . ''; - $primary = Index::getPrimary($table, $db); - $primarycols = $primary->getColumns(); - $pk = []; - foreach ($primarycols as $col) { - $pk[] = $col->getName(); - } - return [ - 'legendText' => $legendText, - 'headText' => $headText, - 'subText' => $subText, - 'extra' => $extra, - 'primary_key' => json_encode($pk), - ]; - } - - /** - * build html contents for 2NF step 2.1 - * - * @param string $db current database - * @param string $table current table - * - * @return string[] HTML contents for 2NF step 2.1 - */ - public function getHtmlFor2NFstep1($db, $table) - { - $legendText = __('Step 2.') . "1 " . __('Find partial dependencies'); - $primary = Index::getPrimary($table, $db); - $primarycols = $primary->getColumns(); - $pk = []; - $subText = ''; - $selectPkForm = ""; - $extra = ""; - foreach ($primarycols as $col) { - $pk[] = $col->getName(); - $selectPkForm .= '' - . htmlspecialchars($col->getName()); - } - $key = implode(', ', $pk); - if (count($primarycols) > 1) { - $this->dbi->selectDb($db); - $columns = (array) $this->dbi->getColumnNames( - $db, - $table - ); - if (count($pk) == count($columns)) { - $headText = sprintf( - __( - 'No partial dependencies possible as ' - . 'no non-primary column exists since primary key ( %1$s ) ' - . 'is composed of all the columns in the table.' - ), - htmlspecialchars($key) - ) . '
'; - $extra = '

' . __('Table is already in second normal form.') - . '

'; - } else { - $headText = sprintf( - __( - 'The primary key ( %1$s ) consists of more than one column ' - . 'so we need to find the partial dependencies.' - ), - htmlspecialchars($key) - ) . '
' . __( - 'Please answer the following question(s) ' - . 'carefully to obtain a correct normalization.' - ) - . '
' . __( - '+ Show me the possible partial dependencies ' - . 'based on data in the table' - ) . ''; - $subText = __( - 'For each column below, ' - . 'please select the minimal set of columns among given set ' - . 'whose values combined together are sufficient' - . ' to determine the value of the column.' - ); - $cnt = 0; - foreach ($columns as $column) { - if (! in_array($column, $pk)) { - $cnt++; - $extra .= "" . sprintf( - __('\'%1$s\' depends on:'), - htmlspecialchars($column) - ) . "
"; - $extra .= '
' - . $selectPkForm . '


'; - } - } - } - } else { - $headText = sprintf( - __( - 'No partial dependencies possible as the primary key' - . ' ( %1$s ) has just one column.' - ), - htmlspecialchars($key) - ) . '
'; - $extra = '

' . __('Table is already in second normal form.') . '

'; - } - return [ - 'legendText' => $legendText, - 'headText' => $headText, - 'subText' => $subText, - 'extra' => $extra, - 'primary_key' => $key, - ]; - } - - /** - * build the html for showing the tables to have in order to put current table in 2NF - * - * @param array $partialDependencies array containing all the dependencies - * @param string $table current table - * - * @return string HTML - */ - public function getHtmlForNewTables2NF(array $partialDependencies, $table) - { - $html = '

' . sprintf( - __( - 'In order to put the ' - . 'original table \'%1$s\' into Second normal form we need ' - . 'to create the following tables:' - ), - htmlspecialchars($table) - ) . '

'; - $tableName = $table; - $i = 1; - foreach ($partialDependencies as $key => $dependents) { - $html .= '

' - . '( ' . htmlspecialchars($key) . '' - . (count($dependents) > 0 ? ', ' : '') - . htmlspecialchars(implode(', ', $dependents)) . ' )'; - $i++; - $tableName = 'table' . $i; - } - return $html; - } - - /** - * create/alter the tables needed for 2NF - * - * @param array $partialDependencies array containing all the partial dependencies - * @param object $tablesName name of new tables - * @param string $table current table - * @param string $db current database - * - * @return array - */ - public function createNewTablesFor2NF(array $partialDependencies, $tablesName, $table, $db) - { - $dropCols = false; - $nonPKCols = []; - $queries = []; - $error = false; - $headText = '

' . sprintf( - __('The second step of normalization is complete for table \'%1$s\'.'), - htmlspecialchars($table) - ) . '

'; - if (count((array) $partialDependencies) === 1) { - return [ - 'legendText' => __('End of step'), - 'headText' => $headText, - 'queryError' => $error, - ]; - } - $message = ''; - $this->dbi->selectDb($db); - foreach ($partialDependencies as $key => $dependents) { - if ($tablesName->$key != $table) { - $backquotedKey = implode(', ', Util::backquote(explode(', ', $key))); - $queries[] = 'CREATE TABLE ' . Util::backquote($tablesName->$key) - . ' SELECT DISTINCT ' . $backquotedKey - . (count($dependents) > 0 ? ', ' : '') - . implode(',', Util::backquote($dependents)) - . ' FROM ' . Util::backquote($table) . ';'; - $queries[] = 'ALTER TABLE ' . Util::backquote($tablesName->$key) - . ' ADD PRIMARY KEY(' . $backquotedKey . ');'; - $nonPKCols = array_merge($nonPKCols, $dependents); - } else { - $dropCols = true; - } - } - - if ($dropCols) { - $query = 'ALTER TABLE ' . Util::backquote($table); - foreach ($nonPKCols as $col) { - $query .= ' DROP ' . Util::backquote($col) . ','; - } - $query = trim($query, ', '); - $query .= ';'; - $queries[] = $query; - } else { - $queries[] = 'DROP TABLE ' . Util::backquote($table); - } - foreach ($queries as $query) { - if (! $this->dbi->tryQuery($query)) { - $message = Message::error(__('Error in processing!')); - $message->addMessage( - Message::rawError( - $this->dbi->getError() - ), - '

' - ); - $error = true; - break; - } - } - return [ - 'legendText' => __('End of step'), - 'headText' => $headText, - 'queryError' => $error, - 'extra' => $message, - ]; - } - - /** - * build the html for showing the new tables to have in order - * to put given tables in 3NF - * - * @param object $dependencies containing all the dependencies - * @param array $tables tables formed after 2NF and need to convert to 3NF - * @param string $db current database - * - * @return array containing html and the list of new tables - */ - public function getHtmlForNewTables3NF($dependencies, array $tables, $db) - { - $html = ""; - $i = 1; - $newTables = []; - foreach ($tables as $table => $arrDependson) { - if (count(array_unique($arrDependson)) === 1) { - continue; - } - $primary = Index::getPrimary($table, $db); - $primarycols = $primary->getColumns(); - $pk = []; - foreach ($primarycols as $col) { - $pk[] = $col->getName(); - } - $html .= '

' . sprintf( - __( - 'In order to put the ' - . 'original table \'%1$s\' into Third normal form we need ' - . 'to create the following tables:' - ), - htmlspecialchars($table) - ) . '

'; - $tableName = $table; - $columnList = []; - foreach ($arrDependson as $key) { - $dependents = $dependencies->$key; - if ($key == $table) { - $key = implode(', ', $pk); - } - $tmpTableCols = array_merge(explode(', ', $key), $dependents); - sort($tmpTableCols); - if (! in_array($tmpTableCols, $columnList)) { - $columnList[] = $tmpTableCols; - $html .= '

' - . '( ' . htmlspecialchars($key) . '' - . (count($dependents) > 0 ? ', ' : '') - . htmlspecialchars(implode(', ', $dependents)) . ' )'; - $newTables[$table][$tableName] = [ - "pk" => $key, - "nonpk" => implode(', ', $dependents), - ]; - $i++; - $tableName = 'table' . $i; - } - } - } - return [ - 'html' => $html, - 'newTables' => $newTables, - 'success' => true, - ]; - } - - /** - * create new tables or alter existing to get 3NF - * - * @param array $newTables list of new tables to be created - * @param string $db current database - * - * @return array - */ - public function createNewTablesFor3NF(array $newTables, $db) - { - $queries = []; - $dropCols = false; - $error = false; - $headText = '

' . - __('The third step of normalization is complete.') - . '

'; - if (count((array) $newTables) === 0) { - return [ - 'legendText' => __('End of step'), - 'headText' => $headText, - 'queryError' => $error, - ]; - } - $message = ''; - $this->dbi->selectDb($db); - foreach ($newTables as $originalTable => $tablesList) { - foreach ($tablesList as $table => $cols) { - if ($table != $originalTable) { - $quotedPk = implode( - ', ', - Util::backquote(explode(', ', $cols->pk)) - ); - $quotedNonpk = implode( - ', ', - Util::backquote(explode(', ', $cols->nonpk)) - ); - $queries[] = 'CREATE TABLE ' . Util::backquote($table) - . ' SELECT DISTINCT ' . $quotedPk - . ', ' . $quotedNonpk - . ' FROM ' . Util::backquote($originalTable) . ';'; - $queries[] = 'ALTER TABLE ' . Util::backquote($table) - . ' ADD PRIMARY KEY(' . $quotedPk . ');'; - } else { - $dropCols = $cols; - } - } - if ($dropCols) { - $columns = (array) $this->dbi->getColumnNames( - $db, - $originalTable - ); - $colPresent = array_merge( - explode(', ', $dropCols->pk), - explode(', ', $dropCols->nonpk) - ); - $query = 'ALTER TABLE ' . Util::backquote($originalTable); - foreach ($columns as $col) { - if (! in_array($col, $colPresent)) { - $query .= ' DROP ' . Util::backquote($col) . ','; - } - } - $query = trim($query, ', '); - $query .= ';'; - $queries[] = $query; - } else { - $queries[] = 'DROP TABLE ' . Util::backquote($originalTable); - } - $dropCols = false; - } - foreach ($queries as $query) { - if (! $this->dbi->tryQuery($query)) { - $message = Message::error(__('Error in processing!')); - $message->addMessage( - Message::rawError( - $this->dbi->getError() - ), - '

' - ); - $error = true; - break; - } - } - return [ - 'legendText' => __('End of step'), - 'headText' => $headText, - 'queryError' => $error, - 'extra' => $message, - ]; - } - - /** - * move the repeating group of columns to a new table - * - * @param string $repeatingColumns comma separated list of repeating group columns - * @param string $primaryColumns comma separated list of column in primary key - * of $table - * @param string $newTable name of the new table to be created - * @param string $newColumn name of the new column in the new table - * @param string $table current table - * @param string $db current database - * - * @return array - */ - public function moveRepeatingGroup( - $repeatingColumns, - $primaryColumns, - $newTable, - $newColumn, - $table, - $db - ) { - $repeatingColumnsArr = (array) Util::backquote( - explode(', ', $repeatingColumns) - ); - $primaryColumns = implode( - ',', - Util::backquote(explode(',', $primaryColumns)) - ); - $query1 = 'CREATE TABLE ' . Util::backquote($newTable); - $query2 = 'ALTER TABLE ' . Util::backquote($table); - $message = Message::success( - sprintf( - __('Selected repeating group has been moved to the table \'%s\''), - htmlspecialchars($table) - ) - ); - $first = true; - $error = false; - foreach ($repeatingColumnsArr as $repeatingColumn) { - if (! $first) { - $query1 .= ' UNION '; - } - $first = false; - $query1 .= ' SELECT ' . $primaryColumns . ',' . $repeatingColumn - . ' as ' . Util::backquote($newColumn) - . ' FROM ' . Util::backquote($table); - $query2 .= ' DROP ' . $repeatingColumn . ','; - } - $query2 = trim($query2, ','); - $queries = [ - $query1, - $query2, - ]; - $this->dbi->selectDb($db); - foreach ($queries as $query) { - if (! $this->dbi->tryQuery($query)) { - $message = Message::error(__('Error in processing!')); - $message->addMessage( - Message::rawError( - $this->dbi->getError() - ), - '

' - ); - $error = true; - break; - } - } - return [ - 'queryError' => $error, - 'message' => $message, - ]; - } - - /** - * build html for 3NF step 1 to find the transitive dependencies - * - * @param string $db current database - * @param array $tables tables formed after 2NF and need to process for 3NF - * - * @return string[] - */ - public function getHtmlFor3NFstep1($db, array $tables) - { - $legendText = __('Step 3.') . "1 " . __('Find transitive dependencies'); - $extra = ""; - $headText = __( - 'Please answer the following question(s) ' - . 'carefully to obtain a correct normalization.' - ); - $subText = __( - 'For each column below, ' - . 'please select the minimal set of columns among given set ' - . 'whose values combined together are sufficient' - . ' to determine the value of the column.
' - . 'Note: A column may have no transitive dependency, ' - . 'in that case you don\'t have to select any.' - ); - $cnt = 0; - foreach ($tables as $table) { - $primary = Index::getPrimary($table, $db); - $primarycols = $primary->getColumns(); - $selectTdForm = ""; - $pk = []; - foreach ($primarycols as $col) { - $pk[] = $col->getName(); - } - $this->dbi->selectDb($db); - $columns = (array) $this->dbi->getColumnNames( - $db, - $table - ); - if (count($columns) - count($pk) <= 1) { - continue; - } - foreach ($columns as $column) { - if (! in_array($column, $pk)) { - $selectTdForm .= '' - . '' . htmlspecialchars($column) . ''; - } - } - foreach ($columns as $column) { - if (! in_array($column, $pk)) { - $cnt++; - $extra .= "" . sprintf( - __('\'%1$s\' depends on:'), - htmlspecialchars($column) - ) - . "
"; - $extra .= '
' - . $selectTdForm - . '


'; - } - } - } - if ($extra == "") { - $headText = __( - "No Transitive dependencies possible as the table " - . "doesn't have any non primary key columns" - ); - $subText = ""; - $extra = "

" . __("Table is already in Third normal form!") . "

"; - } - return [ - 'legendText' => $legendText, - 'headText' => $headText, - 'subText' => $subText, - 'extra' => $extra, - ]; - } - - /** - * get html for options to normalize table - * - * @return string HTML - */ - public function getHtmlForNormalizeTable() - { - $htmlOutput = '
' - . Url::getHiddenInputs($GLOBALS['db'], $GLOBALS['table']) - . ''; - $htmlOutput .= '
'; - $htmlOutput .= '' - . __('Improve table structure (Normalization):') . ''; - $htmlOutput .= '

' . __('Select up to what step you want to normalize') - . '

'; - $choices = [ - '1nf' => __('First step of normalization (1NF)'), - '2nf' => __('Second step of normalization (1NF+2NF)'), - '3nf' => __('Third step of normalization (1NF+2NF+3NF)'), - ]; - - $htmlOutput .= Util::getRadioFields( - 'normalizeTo', - $choices, - '1nf', - true - ); - $htmlOutput .= '
' - . "" . __( - 'Hint: Please follow the procedure carefully in order ' - . 'to obtain correct normalization' - ) . "" - . '' - . '
' - . '
' - . ''; - - return $htmlOutput; - } - - /** - * find all the possible partial dependencies based on data in the table. - * - * @param string $table current table - * @param string $db current database - * - * @return string HTML containing the list of all the possible partial dependencies - */ - public function findPartialDependencies($table, $db) - { - $dependencyList = []; - $this->dbi->selectDb($db); - $columns = (array) $this->dbi->getColumnNames( - $db, - $table - ); - $columns = (array) Util::backquote($columns); - $totalRowsRes = $this->dbi->fetchResult( - 'SELECT COUNT(*) FROM (SELECT * FROM ' - . Util::backquote($table) . ' LIMIT 500) as dt;' - ); - $totalRows = $totalRowsRes[0]; - $primary = Index::getPrimary($table, $db); - $primarycols = $primary->getColumns(); - $pk = []; - foreach ($primarycols as $col) { - $pk[] = Util::backquote($col->getName()); - } - $partialKeys = $this->getAllCombinationPartialKeys($pk); - $distinctValCount = $this->findDistinctValuesCount( - array_unique( - array_merge($columns, $partialKeys) - ), - $table - ); - foreach ($columns as $column) { - if (! in_array($column, $pk)) { - foreach ($partialKeys as $partialKey) { - if ($partialKey - && $this->checkPartialDependency( - $partialKey, - $column, - $table, - $distinctValCount[$partialKey], - $distinctValCount[$column], - $totalRows - ) - ) { - $dependencyList[$partialKey][] = $column; - } - } - } - } - - $html = __( - 'This list is based on a subset of the table\'s data ' - . 'and is not necessarily accurate. ' - ) - . '
'; - foreach ($dependencyList as $dependon => $colList) { - $html .= '' - . '' - . '' - . htmlspecialchars(str_replace('`', '', $dependon)) . ' -> ' - . '' - . htmlspecialchars(str_replace('`', '', implode(', ', $colList))) - . '' - . ''; - } - if (empty($dependencyList)) { - $html .= '

' - . __('No partial dependencies found!') . '

'; - } - $html .= '
'; - return $html; - } - - /** - * check whether a particular column is dependent on given subset of primary key - * - * @param string $partialKey the partial key, subset of primary key, - * each column in key supposed to be backquoted - * @param string $column backquoted column on whose dependency being checked - * @param string $table current table - * @param integer $pkCnt distinct value count for given partial key - * @param integer $colCnt distinct value count for given column - * @param integer $totalRows total distinct rows count of the table - * - * @return boolean TRUE if $column is dependent on $partialKey, False otherwise - */ - private function checkPartialDependency( - $partialKey, - $column, - $table, - $pkCnt, - $colCnt, - $totalRows - ) { - $query = 'SELECT ' - . 'COUNT(DISTINCT ' . $partialKey . ',' . $column . ') as pkColCnt ' - . 'FROM (SELECT * FROM ' . Util::backquote($table) - . ' LIMIT 500) as dt;'; - $res = $this->dbi->fetchResult($query, null, null); - $pkColCnt = $res[0]; - if ($pkCnt && $pkCnt == $colCnt && $colCnt == $pkColCnt) { - return true; - } - if ($totalRows && $totalRows == $pkCnt) { - return true; - } - return false; - } - - /** - * function to get distinct values count of all the column in the array $columns - * - * @param array $columns array of backquoted columns whose distinct values - * need to be counted. - * @param string $table table to which these columns belong - * - * @return array associative array containing the count - */ - private function findDistinctValuesCount(array $columns, $table) - { - $result = []; - $query = 'SELECT '; - foreach ($columns as $column) { - if ($column) { //each column is already backquoted - $query .= 'COUNT(DISTINCT ' . $column . ') as \'' - . $column . '_cnt\', '; - } - } - $query = trim($query, ', '); - $query .= ' FROM (SELECT * FROM ' . Util::backquote($table) - . ' LIMIT 500) as dt;'; - $res = $this->dbi->fetchResult($query, null, null); - foreach ($columns as $column) { - if ($column) { - $result[$column] = $res[0][$column . '_cnt'] ?? null; - } - } - return $result; - } - - /** - * find all the possible partial keys - * - * @param array $primaryKey array containing all the column present in primary key - * - * @return array containing all the possible partial keys(subset of primary key) - */ - private function getAllCombinationPartialKeys(array $primaryKey) - { - $results = ['']; - foreach ($primaryKey as $element) { - foreach ($results as $combination) { - $results[] = trim($element . ',' . $combination, ','); - } - } - array_pop($results); //remove key which consist of all primary key columns - return $results; - } -} -- cgit