X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;ds=sidebyside;f=src%2FDatabase%2FDBStructure.php;h=8f599a67d72a3e15b7886531181ec285987e8e76;hb=a7f777ee433f8cb5b141f0d413561881d2533c22;hp=92666edb89b78c42cfa3bb80bf48e701cb99ef91;hpb=924869ab5b75fd496014a1b7ca1240a6df939fc6;p=friendica.git diff --git a/src/Database/DBStructure.php b/src/Database/DBStructure.php index 92666edb89..8f599a67d7 100644 --- a/src/Database/DBStructure.php +++ b/src/Database/DBStructure.php @@ -1,24 +1,33 @@ . + * */ + namespace Friendica\Database; use Exception; -use Friendica\Core\Config; use Friendica\Core\Hook; -use Friendica\Core\L10n; use Friendica\Core\Logger; +use Friendica\DI; use Friendica\Util\DateTimeFormat; -require_once 'boot.php'; -require_once 'include/dba.php'; -require_once 'include/enotify.php'; -require_once 'include/text.php'; - /** - * @brief This class contain functions for the database management - * * This class contains functions that doesn't need to know if pdo, mysqli or whatever is used. */ class DBStructure @@ -27,6 +36,9 @@ class DBStructure const UPDATE_SUCCESSFUL = 1; // Database check was successful const UPDATE_FAILED = 2; // Database check failed + const RENAME_COLUMN = 0; + const RENAME_PRIMARY_KEY = 1; + /** * Database structure definition loaded from config/dbstructure.config.php * @@ -34,21 +46,31 @@ class DBStructure */ private static $definition = []; - /* - * Converts all tables from MyISAM to InnoDB + /** + * Converts all tables from MyISAM/InnoDB Antelope to InnoDB Barracuda */ - public static function convertToInnoDB() { - $r = q("SELECT `TABLE_NAME` FROM `information_schema`.`tables` WHERE `engine` = 'MyISAM' AND `table_schema` = '%s'", - DBA::escape(DBA::databaseName())); - - if (!DBA::isResult($r)) { - echo L10n::t('There are no tables on MyISAM.')."\n"; + public static function convertToInnoDB() + { + $tables = DBA::selectToArray( + ['information_schema' => 'tables'], + ['table_name'], + ['engine' => 'MyISAM', 'table_schema' => DBA::databaseName()] + ); + + $tables = array_merge($tables, DBA::selectToArray( + ['information_schema' => 'tables'], + ['table_name'], + ['engine' => 'InnoDB', 'ROW_FORMAT' => ['COMPACT', 'REDUNDANT'], 'table_schema' => DBA::databaseName()] + )); + + if (!DBA::isResult($tables)) { + echo DI::l10n()->t('There are no tables on MyISAM or InnoDB with the Antelope file format.') . "\n"; return; } - foreach ($r AS $table) { - $sql = sprintf("ALTER TABLE `%s` engine=InnoDB;", DBA::escape($table['TABLE_NAME'])); - echo $sql."\n"; + foreach ($tables AS $table) { + $sql = "ALTER TABLE " . DBA::quoteIdentifier($table['table_name']) . " ENGINE=InnoDB ROW_FORMAT=DYNAMIC;"; + echo $sql . "\n"; $result = DBA::e($sql); if (!DBA::isResult($result)) { @@ -57,119 +79,219 @@ class DBStructure } } - private static function tableStructure($table) { - $structures = q("DESCRIBE `%s`", $table); + /** + * Print out database error messages + * + * @param string $message Message to be added to the error message + * + * @return string Error message + */ + private static function printUpdateError($message) + { + echo DI::l10n()->t("\nError %d occurred during database update:\n%s\n", + DBA::errorNo(), DBA::errorMessage()); - $full_columns = q("SHOW FULL COLUMNS FROM `%s`", $table); + return DI::l10n()->t('Errors encountered performing database changes: ') . $message . EOL; + } - $indexes = q("SHOW INDEX FROM `%s`", $table); + public static function printStructure($basePath) + { + $database = self::definition($basePath, false); - $table_status = q("SHOW TABLE STATUS WHERE `name` = '%s'", $table); + echo "-- ------------------------------------------\n"; + echo "-- " . FRIENDICA_PLATFORM . " " . FRIENDICA_VERSION . " (" . FRIENDICA_CODENAME, ")\n"; + echo "-- DB_UPDATE_VERSION " . DB_UPDATE_VERSION . "\n"; + echo "-- ------------------------------------------\n\n\n"; + foreach ($database AS $name => $structure) { + echo "--\n"; + echo "-- TABLE $name\n"; + echo "--\n"; + self::createTable($name, $structure, true, false); - if (DBA::isResult($table_status)) { - $table_status = $table_status[0]; - } else { - $table_status = []; + echo "\n"; } - $fielddata = []; - $indexdata = []; + View::printStructure($basePath); + } - if (DBA::isResult($indexes)) { - foreach ($indexes AS $index) { - if ($index['Key_name'] != 'PRIMARY' && $index['Non_unique'] == '0' && !isset($indexdata[$index["Key_name"]])) { - $indexdata[$index["Key_name"]] = ['UNIQUE']; - } + /** + * Loads the database structure definition from the static/dbstructure.config.php file. + * On first pass, defines DB_UPDATE_VERSION constant. + * + * @see static/dbstructure.config.php + * @param boolean $with_addons_structure Whether to tack on addons additional tables + * @param string $basePath The base path of this application + * @return array + * @throws Exception + */ + public static function definition($basePath, $with_addons_structure = true) + { + if (!self::$definition) { - $column = $index["Column_name"]; + $filename = $basePath . '/static/dbstructure.config.php'; - if ($index["Sub_part"] != "") { - $column .= "(".$index["Sub_part"].")"; - } + if (!is_readable($filename)) { + throw new Exception('Missing database structure config file static/dbstructure.config.php'); + } - $indexdata[$index["Key_name"]][] = $column; + $definition = require $filename; + + if (!$definition) { + throw new Exception('Corrupted database structure config file static/dbstructure.config.php'); } + + self::$definition = $definition; + } else { + $definition = self::$definition; } - if (DBA::isResult($structures)) { - foreach ($structures AS $field) { - // Replace the default size values so that we don't have to define them - $search = ['tinyint(1)', 'tinyint(3) unsigned', 'tinyint(4)', 'smallint(5) unsigned', 'smallint(6)', 'mediumint(8) unsigned', 'mediumint(9)', 'bigint(20)', 'int(10) unsigned', 'int(11)']; - $replace = ['boolean', 'tinyint unsigned', 'tinyint', 'smallint unsigned', 'smallint', 'mediumint unsigned', 'mediumint', 'bigint', 'int unsigned', 'int']; - $field["Type"] = str_replace($search, $replace, $field["Type"]); - $fielddata[$field["Field"]]["type"] = $field["Type"]; - if ($field["Null"] == "NO") { - $fielddata[$field["Field"]]["not null"] = true; - } + if ($with_addons_structure) { + Hook::callAll('dbstructure_definition', $definition); + } - if (isset($field["Default"])) { - $fielddata[$field["Field"]]["default"] = $field["Default"]; - } + return $definition; + } - if ($field["Extra"] != "") { - $fielddata[$field["Field"]]["extra"] = $field["Extra"]; - } + private static function createTable($name, $structure, $verbose, $action) + { + $r = true; - if ($field["Key"] == "PRI") { - $fielddata[$field["Field"]]["primary"] = true; - } + $engine = ""; + $comment = ""; + $sql_rows = []; + $primary_keys = []; + $foreign_keys = []; + + foreach ($structure["fields"] AS $fieldname => $field) { + $sql_rows[] = "`" . DBA::escape($fieldname) . "` " . self::FieldCommand($field); + if (!empty($field['primary'])) { + $primary_keys[] = $fieldname; + } + if (!empty($field['foreign'])) { + $foreign_keys[$fieldname] = $field; } } - if (DBA::isResult($full_columns)) { - foreach ($full_columns AS $column) { - $fielddata[$column["Field"]]["Collation"] = $column["Collation"]; - $fielddata[$column["Field"]]["comment"] = $column["Comment"]; + + if (!empty($structure["indexes"])) { + foreach ($structure["indexes"] AS $indexname => $fieldnames) { + $sql_index = self::createIndex($indexname, $fieldnames, ""); + if (!is_null($sql_index)) { + $sql_rows[] = $sql_index; + } } } - return ["fields" => $fielddata, "indexes" => $indexdata, "table_status" => $table_status]; + foreach ($foreign_keys AS $fieldname => $parameters) { + $sql_rows[] = self::foreignCommand($name, $fieldname, $parameters); + } + + if (isset($structure["engine"])) { + $engine = " ENGINE=" . $structure["engine"]; + } + + if (isset($structure["comment"])) { + $comment = " COMMENT='" . DBA::escape($structure["comment"]) . "'"; + } + + $sql = implode(",\n\t", $sql_rows); + + $sql = sprintf("CREATE TABLE IF NOT EXISTS `%s` (\n\t", DBA::escape($name)) . $sql . + "\n)" . $engine . " DEFAULT COLLATE utf8mb4_general_ci" . $comment; + if ($verbose) { + echo $sql . ";\n"; + } + + if ($action) { + $r = DBA::e($sql); + } + + return $r; } - public static function printStructure() { - $database = self::definition(false); + private static function FieldCommand($parameters, $create = true) + { + $fieldstruct = $parameters["type"]; - echo "-- ------------------------------------------\n"; - echo "-- ".FRIENDICA_PLATFORM." ".FRIENDICA_VERSION." (".FRIENDICA_CODENAME,")\n"; - echo "-- DB_UPDATE_VERSION ".DB_UPDATE_VERSION."\n"; - echo "-- ------------------------------------------\n\n\n"; - foreach ($database AS $name => $structure) { - echo "--\n"; - echo "-- TABLE $name\n"; - echo "--\n"; - self::createTable($name, $structure, true, false); + if (isset($parameters["Collation"])) { + $fieldstruct .= " COLLATE " . $parameters["Collation"]; + } - echo "\n"; + if (isset($parameters["not null"])) { + $fieldstruct .= " NOT NULL"; } + + if (isset($parameters["default"])) { + if (strpos(strtolower($parameters["type"]), "int") !== false) { + $fieldstruct .= " DEFAULT " . $parameters["default"]; + } else { + $fieldstruct .= " DEFAULT '" . $parameters["default"] . "'"; + } + } + if (isset($parameters["extra"])) { + $fieldstruct .= " " . $parameters["extra"]; + } + + if (isset($parameters["comment"])) { + $fieldstruct .= " COMMENT '" . DBA::escape($parameters["comment"]) . "'"; + } + + /*if (($parameters["primary"] != "") && $create) + $fieldstruct .= " PRIMARY KEY";*/ + + return ($fieldstruct); } - /** - * @brief Print out database error messages - * - * @param string $message Message to be added to the error message - * - * @return string Error message - */ - private static function printUpdateError($message) { - echo L10n::t("\nError %d occurred during database update:\n%s\n", - DBA::errorNo(), DBA::errorMessage()); + private static function createIndex($indexname, $fieldnames, $method = "ADD") + { + $method = strtoupper(trim($method)); + if ($method != "" && $method != "ADD") { + throw new Exception("Invalid parameter 'method' in self::createIndex(): '$method'"); + } + + if (in_array($fieldnames[0], ["UNIQUE", "FULLTEXT"])) { + $index_type = array_shift($fieldnames); + $method .= " " . $index_type; + } + + $names = ""; + foreach ($fieldnames AS $fieldname) { + if ($names != "") { + $names .= ","; + } + + if (preg_match('|(.+)\((\d+)\)|', $fieldname, $matches)) { + $names .= "`" . DBA::escape($matches[1]) . "`(" . intval($matches[2]) . ")"; + } else { + $names .= "`" . DBA::escape($fieldname) . "`"; + } + } - return L10n::t('Errors encountered performing database changes: ').$message.EOL; + if ($indexname == "PRIMARY") { + return sprintf("%s PRIMARY KEY(%s)", $method, $names); + } + + + $sql = sprintf("%s INDEX `%s` (%s)", $method, DBA::escape($indexname), $names); + return ($sql); } /** * Updates DB structure and returns eventual errors messages * - * @param bool $verbose - * @param bool $action Whether to actually apply the update - * @param bool $install Is this the initial update during the installation? - * @param array $tables An array of the database tables - * @param array $definition An array of the definition tables + * @param string $basePath The base path of this application + * @param bool $verbose + * @param bool $action Whether to actually apply the update + * @param bool $install Is this the initial update during the installation? + * @param array $tables An array of the database tables + * @param array $definition An array of the definition tables * @return string Empty string if the update is successful, error messages otherwise + * @throws Exception */ - public static function update($verbose, $action, $install = false, array $tables = null, array $definition = null) { + public static function update($basePath, $verbose, $action, $install = false, array $tables = null, array $definition = null) + { if ($action && !$install) { - Config::set('system', 'maintenance', 1); - Config::set('system', 'maintenance_reason', L10n::t('%s: Database update', DateTimeFormat::utcNow().' '.date('e'))); + DI::config()->set('system', 'maintenance', 1); + DI::config()->set('system', 'maintenance_reason', DI::l10n()->t('%s: Database update', DateTimeFormat::utcNow() . ' ' . date('e'))); } $errors = ''; @@ -180,7 +302,7 @@ class DBStructure $database = []; if (is_null($tables)) { - $tables = q("SHOW TABLES"); + $tables = DBA::toArray(DBA::p("SHOW TABLES")); } if (DBA::isResult($tables)) { @@ -194,7 +316,7 @@ class DBStructure // Get the definition if (is_null($definition)) { - $definition = self::definition(); + $definition = self::definition($basePath); } // MySQL >= 5.7.4 doesn't support the IGNORE keyword in ALTER TABLE statements @@ -221,16 +343,16 @@ class DBStructure } else { foreach ($structure["indexes"] AS $indexname => $fieldnames) { if (isset($database[$name]["indexes"][$indexname])) { - $current_index_definition = implode(",",$database[$name]["indexes"][$indexname]); + $current_index_definition = implode(",", $database[$name]["indexes"][$indexname]); } else { $current_index_definition = "__NOT_SET__"; } - $new_index_definition = implode(",",$fieldnames); + $new_index_definition = implode(",", $fieldnames); if ($current_index_definition != $new_index_definition) { if ($fieldnames[0] == "UNIQUE") { $is_unique = true; if ($ignore == "") { - $temp_name = "temp-".$name; + $temp_name = "temp-" . $name; } } } @@ -242,29 +364,29 @@ class DBStructure * and index name doesn't start with "local_" */ foreach ($database[$name]["indexes"] as $indexname => $fieldnames) { - $current_index_definition = implode(",",$fieldnames); + $current_index_definition = implode(",", $fieldnames); if (isset($structure["indexes"][$indexname])) { - $new_index_definition = implode(",",$structure["indexes"][$indexname]); + $new_index_definition = implode(",", $structure["indexes"][$indexname]); } else { $new_index_definition = "__NOT_SET__"; } if ($current_index_definition != $new_index_definition && substr($indexname, 0, 6) != 'local_') { - $sql2=self::dropIndex($indexname); + $sql2 = self::dropIndex($indexname); if ($sql3 == "") { - $sql3 = "ALTER".$ignore." TABLE `".$temp_name."` ".$sql2; + $sql3 = "ALTER" . $ignore . " TABLE `" . $temp_name . "` " . $sql2; } else { - $sql3 .= ", ".$sql2; + $sql3 .= ", " . $sql2; } } } // Compare the field structure field by field foreach ($structure["fields"] AS $fieldname => $parameters) { if (!isset($database[$name]["fields"][$fieldname])) { - $sql2=self::addTableField($fieldname, $parameters); + $sql2 = self::addTableField($fieldname, $parameters); if ($sql3 == "") { - $sql3 = "ALTER" . $ignore . " TABLE `".$temp_name."` ".$sql2; + $sql3 = "ALTER" . $ignore . " TABLE `" . $temp_name . "` " . $sql2; } else { - $sql3 .= ", ".$sql2; + $sql3 .= ", " . $sql2; } } else { // Compare the field definition @@ -272,6 +394,7 @@ class DBStructure // Remove the relation data that is used for the referential integrity unset($parameters['relation']); + unset($parameters['foreign']); // We change the collation after the indexes had been changed. // This is done to avoid index length problems. @@ -289,9 +412,9 @@ class DBStructure if ($current_field_definition != $new_field_definition) { $sql2 = self::modifyTableField($fieldname, $parameters); if ($sql3 == "") { - $sql3 = "ALTER" . $ignore . " TABLE `".$temp_name."` ".$sql2; + $sql3 = "ALTER" . $ignore . " TABLE `" . $temp_name . "` " . $sql2; } else { - $sql3 .= ", ".$sql2; + $sql3 .= ", " . $sql2; } } } @@ -306,61 +429,93 @@ class DBStructure if (!$is_new_table) { foreach ($structure["indexes"] AS $indexname => $fieldnames) { if (isset($database[$name]["indexes"][$indexname])) { - $current_index_definition = implode(",",$database[$name]["indexes"][$indexname]); + $current_index_definition = implode(",", $database[$name]["indexes"][$indexname]); } else { $current_index_definition = "__NOT_SET__"; } - $new_index_definition = implode(",",$fieldnames); + $new_index_definition = implode(",", $fieldnames); if ($current_index_definition != $new_index_definition) { $sql2 = self::createIndex($indexname, $fieldnames); // Fetch the "group by" fields for unique indexes - if ($fieldnames[0] == "UNIQUE") { - $group_by = self::groupBy($indexname, $fieldnames); - } + $group_by = self::groupBy($fieldnames); if ($sql2 != "") { if ($sql3 == "") { - $sql3 = "ALTER" . $ignore . " TABLE `".$temp_name."` ".$sql2; + $sql3 = "ALTER" . $ignore . " TABLE `" . $temp_name . "` " . $sql2; } else { - $sql3 .= ", ".$sql2; + $sql3 .= ", " . $sql2; } } } } - if (isset($database[$name]["table_status"]["Comment"])) { - $structurecomment = defaults($structure, "comment", ""); - if ($database[$name]["table_status"]["Comment"] != $structurecomment) { - $sql2 = "COMMENT = '".DBA::escape($structurecomment)."'"; + $existing_foreign_keys = $database[$name]['foreign_keys']; + + // Foreign keys + // Compare the field structure field by field + foreach ($structure["fields"] AS $fieldname => $parameters) { + if (empty($parameters['foreign'])) { + continue; + } + + $constraint = self::getConstraintName($name, $fieldname, $parameters); + + unset($existing_foreign_keys[$constraint]); + + if (empty($database[$name]['foreign_keys'][$constraint])) { + $sql2 = self::addForeignKey($name, $fieldname, $parameters); if ($sql3 == "") { - $sql3 = "ALTER" . $ignore . " TABLE `".$temp_name."` ".$sql2; + $sql3 = "ALTER" . $ignore . " TABLE `" . $temp_name . "` " . $sql2; } else { - $sql3 .= ", ".$sql2; + $sql3 .= ", " . $sql2; } } } - if (isset($database[$name]["table_status"]["Engine"]) && isset($structure['engine'])) { - if ($database[$name]["table_status"]["Engine"] != $structure['engine']) { - $sql2 = "ENGINE = '".DBA::escape($structure['engine'])."'"; + foreach ($existing_foreign_keys as $constraint => $param) { + $sql2 = self::dropForeignKey($constraint); + + if ($sql3 == "") { + $sql3 = "ALTER" . $ignore . " TABLE `" . $temp_name . "` " . $sql2; + } else { + $sql3 .= ", " . $sql2; + } + } + + if (isset($database[$name]["table_status"]["TABLE_COMMENT"])) { + $structurecomment = $structure["comment"] ?? ''; + if ($database[$name]["table_status"]["TABLE_COMMENT"] != $structurecomment) { + $sql2 = "COMMENT = '" . DBA::escape($structurecomment) . "'"; if ($sql3 == "") { - $sql3 = "ALTER" . $ignore . " TABLE `".$temp_name."` ".$sql2; + $sql3 = "ALTER" . $ignore . " TABLE `" . $temp_name . "` " . $sql2; } else { - $sql3 .= ", ".$sql2; + $sql3 .= ", " . $sql2; } } } - if (isset($database[$name]["table_status"]["Collation"])) { - if ($database[$name]["table_status"]["Collation"] != 'utf8mb4_general_ci') { + if (isset($database[$name]["table_status"]["ENGINE"]) && isset($structure['engine'])) { + if ($database[$name]["table_status"]["ENGINE"] != $structure['engine']) { + $sql2 = "ENGINE = '" . DBA::escape($structure['engine']) . "'"; + + if ($sql3 == "") { + $sql3 = "ALTER" . $ignore . " TABLE `" . $temp_name . "` " . $sql2; + } else { + $sql3 .= ", " . $sql2; + } + } + } + + if (isset($database[$name]["table_status"]["TABLE_COLLATION"])) { + if ($database[$name]["table_status"]["TABLE_COLLATION"] != 'utf8mb4_general_ci') { $sql2 = "DEFAULT COLLATE utf8mb4_general_ci"; if ($sql3 == "") { - $sql3 = "ALTER" . $ignore . " TABLE `".$temp_name."` ".$sql2; + $sql3 = "ALTER" . $ignore . " TABLE `" . $temp_name . "` " . $sql2; } else { - $sql3 .= ", ".$sql2; + $sql3 .= ", " . $sql2; } } } @@ -373,7 +528,7 @@ class DBStructure // Compare the field structure field by field foreach ($structure["fields"] AS $fieldname => $parameters) { // Compare the field definition - $field_definition = defaults($database[$name]["fields"], $fieldname, ['Collation' => '']); + $field_definition = ($database[$name]["fields"][$fieldname] ?? '') ?: ['Collation' => '']; // Define the default collation if not given if (!isset($parameters['Collation']) && !empty($field_definition['Collation'])) { @@ -385,9 +540,9 @@ class DBStructure if ($field_definition['Collation'] != $parameters['Collation']) { $sql2 = self::modifyTableField($fieldname, $parameters); if (($sql3 == "") || (substr($sql3, -2, 2) == "; ")) { - $sql3 .= "ALTER" . $ignore . " TABLE `".$temp_name."` ".$sql2; + $sql3 .= "ALTER" . $ignore . " TABLE `" . $temp_name . "` " . $sql2; } else { - $sql3 .= ", ".$sql2; + $sql3 .= ", " . $sql2; } } } @@ -412,27 +567,27 @@ class DBStructure if ($ignore != "") { echo "SET session old_alter_table=1;\n"; } else { - echo "DROP TABLE IF EXISTS `".$temp_name."`;\n"; - echo "CREATE TABLE `".$temp_name."` LIKE `".$name."`;\n"; + echo "DROP TABLE IF EXISTS `" . $temp_name . "`;\n"; + echo "CREATE TABLE `" . $temp_name . "` LIKE `" . $name . "`;\n"; } } - echo $sql3."\n"; + echo $sql3 . "\n"; if ($is_unique && ($temp_name != $name)) { if ($ignore != "") { echo "SET session old_alter_table=0;\n"; } else { - echo "INSERT INTO `".$temp_name."` SELECT ".DBA::anyValueFallback($field_list)." FROM `".$name."`".$group_by.";\n"; - echo "DROP TABLE `".$name."`;\n"; - echo "RENAME TABLE `".$temp_name."` TO `".$name."`;\n"; + echo "INSERT INTO `" . $temp_name . "` SELECT " . DBA::anyValueFallback($field_list) . " FROM `" . $name . "`" . $group_by . ";\n"; + echo "DROP TABLE `" . $name . "`;\n"; + echo "RENAME TABLE `" . $temp_name . "` TO `" . $name . "`;\n"; } } } if ($action) { if (!$install) { - Config::set('system', 'maintenance_reason', L10n::t('%s: updating %s table.', DateTimeFormat::utcNow().' '.date('e'), $name)); + DI::config()->set('system', 'maintenance_reason', DI::l10n()->t('%s: updating %s table.', DateTimeFormat::utcNow() . ' ' . date('e'), $name)); } // Ensure index conversion to unique removes duplicates @@ -440,13 +595,13 @@ class DBStructure if ($ignore != "") { DBA::e("SET session old_alter_table=1;"); } else { - $r = DBA::e("DROP TABLE IF EXISTS `".$temp_name."`;"); + $r = DBA::e("DROP TABLE IF EXISTS `" . $temp_name . "`;"); if (!DBA::isResult($r)) { $errors .= self::printUpdateError($sql3); return $errors; } - $r = DBA::e("CREATE TABLE `".$temp_name."` LIKE `".$name."`;"); + $r = DBA::e("CREATE TABLE `" . $temp_name . "` LIKE `" . $name . "`;"); if (!DBA::isResult($r)) { $errors .= self::printUpdateError($sql3); return $errors; @@ -462,17 +617,17 @@ class DBStructure if ($ignore != "") { DBA::e("SET session old_alter_table=0;"); } else { - $r = DBA::e("INSERT INTO `".$temp_name."` SELECT ".$field_list." FROM `".$name."`".$group_by.";"); + $r = DBA::e("INSERT INTO `" . $temp_name . "` SELECT " . $field_list . " FROM `" . $name . "`" . $group_by . ";"); if (!DBA::isResult($r)) { $errors .= self::printUpdateError($sql3); return $errors; } - $r = DBA::e("DROP TABLE `".$name."`;"); + $r = DBA::e("DROP TABLE `" . $name . "`;"); if (!DBA::isResult($r)) { $errors .= self::printUpdateError($sql3); return $errors; } - $r = DBA::e("RENAME TABLE `".$temp_name."` TO `".$name."`;"); + $r = DBA::e("RENAME TABLE `" . $temp_name . "` TO `" . $name . "`;"); if (!DBA::isResult($r)) { $errors .= self::printUpdateError($sql3); return $errors; @@ -483,147 +638,177 @@ class DBStructure } } + View::create(false, $action); + if ($action && !$install) { - Config::set('system', 'maintenance', 0); - Config::set('system', 'maintenance_reason', ''); + DI::config()->set('system', 'maintenance', 0); + DI::config()->set('system', 'maintenance_reason', ''); if ($errors) { - Config::set('system', 'dbupdate', self::UPDATE_FAILED); + DI::config()->set('system', 'dbupdate', self::UPDATE_FAILED); } else { - Config::set('system', 'dbupdate', self::UPDATE_SUCCESSFUL); + DI::config()->set('system', 'dbupdate', self::UPDATE_SUCCESSFUL); } } return $errors; } - private static function FieldCommand($parameters, $create = true) { - $fieldstruct = $parameters["type"]; + private static function tableStructure($table) + { + // This query doesn't seem to be executable as a prepared statement + $indexes = DBA::toArray(DBA::p(sprintf("SHOW INDEX FROM `%s`", $table))); - if (isset($parameters["Collation"])) { - $fieldstruct .= " COLLATE ".$parameters["Collation"]; - } + $fields = DBA::selectToArray(['INFORMATION_SCHEMA' => 'COLUMNS'], + ['COLUMN_NAME', 'COLUMN_TYPE', 'IS_NULLABLE', 'COLUMN_DEFAULT', 'EXTRA', + 'COLUMN_KEY', 'COLLATION_NAME', 'COLUMN_COMMENT'], + ["`TABLE_SCHEMA` = ? AND `TABLE_NAME` = ?", + DBA::databaseName(), $table]); - if (isset($parameters["not null"])) { - $fieldstruct .= " NOT NULL"; - } + $foreign_keys = DBA::selectToArray(['INFORMATION_SCHEMA' => 'KEY_COLUMN_USAGE'], + ['COLUMN_NAME', 'CONSTRAINT_NAME', 'REFERENCED_TABLE_NAME', 'REFERENCED_COLUMN_NAME'], + ["`TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? AND `REFERENCED_TABLE_SCHEMA` IS NOT NULL", + DBA::databaseName(), $table]); - if (isset($parameters["default"])) { - if (strpos(strtolower($parameters["type"]),"int")!==false) { - $fieldstruct .= " DEFAULT ".$parameters["default"]; - } else { - $fieldstruct .= " DEFAULT '".$parameters["default"]."'"; + $table_status = DBA::selectFirst(['INFORMATION_SCHEMA' => 'TABLES'], + ['ENGINE', 'TABLE_COLLATION', 'TABLE_COMMENT'], + ["`TABLE_SCHEMA` = ? AND `TABLE_NAME` = ?", + DBA::databaseName(), $table]); + + $fielddata = []; + $indexdata = []; + $foreigndata = []; + + if (DBA::isResult($foreign_keys)) { + foreach ($foreign_keys as $foreign_key) { + $constraint = $foreign_key['CONSTRAINT_NAME']; + unset($foreign_key['CONSTRAINT_NAME']); + $foreigndata[$constraint] = $foreign_key; } } - if (isset($parameters["extra"])) { - $fieldstruct .= " ".$parameters["extra"]; - } - if (isset($parameters["comment"])) { - $fieldstruct .= " COMMENT '".DBA::escape($parameters["comment"])."'"; - } + if (DBA::isResult($indexes)) { + foreach ($indexes AS $index) { + if ($index["Key_name"] != "PRIMARY" && $index["Non_unique"] == "0" && !isset($indexdata[$index["Key_name"]])) { + $indexdata[$index["Key_name"]] = ["UNIQUE"]; + } - /*if (($parameters["primary"] != "") && $create) - $fieldstruct .= " PRIMARY KEY";*/ + if ($index["Index_type"] == "FULLTEXT" && !isset($indexdata[$index["Key_name"]])) { + $indexdata[$index["Key_name"]] = ["FULLTEXT"]; + } - return($fieldstruct); - } + $column = $index["Column_name"]; - private static function createTable($name, $structure, $verbose, $action) { - $r = true; + if ($index["Sub_part"] != "") { + $column .= "(" . $index["Sub_part"] . ")"; + } - $engine = ""; - $comment = ""; - $sql_rows = []; - $primary_keys = []; - foreach ($structure["fields"] AS $fieldname => $field) { - $sql_rows[] = "`".DBA::escape($fieldname)."` ".self::FieldCommand($field); - if (!empty($field['primary'])) { - $primary_keys[] = $fieldname; + $indexdata[$index["Key_name"]][] = $column; } } - if (!empty($structure["indexes"])) { - foreach ($structure["indexes"] AS $indexname => $fieldnames) { - $sql_index = self::createIndex($indexname, $fieldnames, ""); - if (!is_null($sql_index)) { - $sql_rows[] = $sql_index; + $fielddata = []; + if (DBA::isResult($fields)) { + foreach ($fields AS $field) { + $search = ['tinyint(1)', 'tinyint(3) unsigned', 'tinyint(4)', 'smallint(5) unsigned', 'smallint(6)', 'mediumint(8) unsigned', 'mediumint(9)', 'bigint(20)', 'int(10) unsigned', 'int(11)']; + $replace = ['boolean', 'tinyint unsigned', 'tinyint', 'smallint unsigned', 'smallint', 'mediumint unsigned', 'mediumint', 'bigint', 'int unsigned', 'int']; + $field['COLUMN_TYPE'] = str_replace($search, $replace, $field['COLUMN_TYPE']); + + $fielddata[$field['COLUMN_NAME']]['type'] = $field['COLUMN_TYPE']; + + if ($field['IS_NULLABLE'] == 'NO') { + $fielddata[$field['COLUMN_NAME']]['not null'] = 1; } - } - } - if (isset($structure["engine"])) { - $engine = " ENGINE=" . $structure["engine"]; - } + if (isset($field['COLUMN_DEFAULT'])) { + $fielddata[$field['COLUMN_NAME']]['default'] = $field['COLUMN_DEFAULT']; + } - if (isset($structure["comment"])) { - $comment = " COMMENT='" . DBA::escape($structure["comment"]) . "'"; - } + if (!empty($field['EXTRA'])) { + $fielddata[$field['COLUMN_NAME']]['extra'] = $field['EXTRA']; + } - $sql = implode(",\n\t", $sql_rows); + if ($field['COLUMN_KEY'] == 'PRI') { + $fielddata[$field['COLUMN_NAME']]['primary'] = 1; + } - $sql = sprintf("CREATE TABLE IF NOT EXISTS `%s` (\n\t", DBA::escape($name)).$sql. - "\n)" . $engine . " DEFAULT COLLATE utf8mb4_general_ci" . $comment; - if ($verbose) { - echo $sql.";\n"; + $fielddata[$field['COLUMN_NAME']]['Collation'] = $field['COLLATION_NAME']; + $fielddata[$field['COLUMN_NAME']]['comment'] = $field['COLUMN_COMMENT']; + } } - if ($action) { - $r = DBA::e($sql); - } + return ["fields" => $fielddata, "indexes" => $indexdata, + "foreign_keys" => $foreigndata, "table_status" => $table_status]; + } - return $r; + private static function dropIndex($indexname) + { + $sql = sprintf("DROP INDEX `%s`", DBA::escape($indexname)); + return ($sql); } - private static function addTableField($fieldname, $parameters) { + private static function addTableField($fieldname, $parameters) + { $sql = sprintf("ADD `%s` %s", DBA::escape($fieldname), self::FieldCommand($parameters)); - return($sql); + return ($sql); } - private static function modifyTableField($fieldname, $parameters) { + private static function modifyTableField($fieldname, $parameters) + { $sql = sprintf("MODIFY `%s` %s", DBA::escape($fieldname), self::FieldCommand($parameters, false)); - return($sql); + return ($sql); } - private static function dropIndex($indexname) { - $sql = sprintf("DROP INDEX `%s`", DBA::escape($indexname)); - return($sql); + private static function getConstraintName(string $tablename, string $fieldname, array $parameters) + { + $foreign_table = array_keys($parameters['foreign'])[0]; + $foreign_field = array_values($parameters['foreign'])[0]; + + return $tablename . "-" . $fieldname. "-" . $foreign_table. "-" . $foreign_field; } - private static function createIndex($indexname, $fieldnames, $method = "ADD") { - $method = strtoupper(trim($method)); - if ($method!="" && $method!="ADD") { - throw new Exception("Invalid parameter 'method' in self::createIndex(): '$method'"); - } + private static function foreignCommand(string $tablename, string $fieldname, array $parameters) { + $foreign_table = array_keys($parameters['foreign'])[0]; + $foreign_field = array_values($parameters['foreign'])[0]; - if ($fieldnames[0] == "UNIQUE") { - array_shift($fieldnames); - $method .= ' UNIQUE'; - } + $constraint = self::getConstraintName($tablename, $fieldname, $parameters); - $names = ""; - foreach ($fieldnames AS $fieldname) { - if ($names != "") { - $names .= ","; - } + $sql = "CONSTRAINT `" . $constraint . "` FOREIGN KEY (`" . $fieldname . "`)" . + " REFERENCES `" . $foreign_table . "` (`" . $foreign_field . "`)"; - if (preg_match('|(.+)\((\d+)\)|', $fieldname, $matches)) { - $names .= "`".DBA::escape($matches[1])."`(".intval($matches[2]).")"; - } else { - $names .= "`".DBA::escape($fieldname)."`"; - } + if (!empty($parameters['foreign']['on update'])) { + $sql .= " ON UPDATE " . strtoupper($parameters['foreign']['on update']); + } else { + $sql .= " ON UPDATE RESTRICT"; } - if ($indexname == "PRIMARY") { - return sprintf("%s PRIMARY KEY(%s)", $method, $names); + if (!empty($parameters['foreign']['on delete'])) { + $sql .= " ON DELETE " . strtoupper($parameters['foreign']['on delete']); + } else { + $sql .= " ON DELETE CASCADE"; } + return $sql; + } - $sql = sprintf("%s INDEX `%s` (%s)", $method, DBA::escape($indexname), $names); - return($sql); + private static function addForeignKey(string $tablename, string $fieldname, array $parameters) + { + return sprintf("ADD %s", self::foreignCommand($tablename, $fieldname, $parameters)); + } + + private static function dropForeignKey(string $constraint) + { + return sprintf("DROP FOREIGN KEY `%s`", $constraint); } - private static function groupBy($indexname, $fieldnames) { + /** + * Constructs a GROUP BY clause from a UNIQUE index definition. + * + * @param array $fieldnames + * @return string + */ + private static function groupBy(array $fieldnames) + { if ($fieldnames[0] != "UNIQUE") { return ""; } @@ -637,9 +822,9 @@ class DBStructure } if (preg_match('|(.+)\((\d+)\)|', $fieldname, $matches)) { - $names .= "`".DBA::escape($matches[1])."`"; + $names .= "`" . DBA::escape($matches[1]) . "`"; } else { - $names .= "`".DBA::escape($fieldname)."`"; + $names .= "`" . DBA::escape($fieldname) . "`"; } } @@ -648,28 +833,62 @@ class DBStructure } /** - * Check if a table exists + * Renames columns or the primary key of a table * - * @param string $table Table name + * @todo You cannot rename a primary key if "auto increment" is set * - * @return boolean Does the table exist? + * @param string $table Table name + * @param array $columns Columns Syntax for Rename: [ $old1 => [ $new1, $type1 ], $old2 => [ $new2, $type2 ], ... ] + * Syntax for Primary Key: [ $col1, $col2, ...] + * @param int $type The type of renaming (Default is Column) + * + * @return boolean Was the renaming successful? + * @throws Exception */ - public static function existsTable($table) + public static function rename($table, $columns, $type = self::RENAME_COLUMN) { - if (empty($table)) { + if (empty($table) || empty($columns)) { + return false; + } + + if (!is_array($columns)) { return false; } $table = DBA::escape($table); - $sql = "SHOW TABLES LIKE '" . $table . "';"; + $sql = "ALTER TABLE `" . $table . "`"; + switch ($type) { + case self::RENAME_COLUMN: + if (!self::existsColumn($table, array_keys($columns))) { + return false; + } + $sql .= implode(',', array_map( + function ($to, $from) { + return " CHANGE `" . $from . "` `" . $to[0] . "` " . $to[1]; + }, + $columns, + array_keys($columns) + )); + break; + case self::RENAME_PRIMARY_KEY: + if (!self::existsColumn($table, $columns)) { + return false; + } + $sql .= " DROP PRIMARY KEY, ADD PRIMARY KEY(`" . implode('`, `', $columns) . "`)"; + break; + default: + return false; + } + + $sql .= ";"; $stmt = DBA::p($sql); if (is_bool($stmt)) { $retval = $stmt; } else { - $retval = (DBA::numRows($stmt) > 0); + $retval = true; } DBA::close($stmt); @@ -678,14 +897,16 @@ class DBStructure } /** - * Check if the columns of the table exists + * Check if the columns of the table exists * * @param string $table Table name * @param array $columns Columns to check ( Syntax: [ $col1, $col2, .. ] ) * * @return boolean Does the table exist? + * @throws Exception */ - public static function existsColumn($table, $columns = []) { + public static function existsColumn($table, $columns = []) + { if (empty($table)) { return false; } @@ -717,106 +938,42 @@ class DBStructure return true; } - const RENAME_COLUMN = 0; - const RENAME_PRIMARY_KEY = 1; - /** - * Renames columns or the primary key of a table - * @todo You cannot rename a primary key if "auto increment" is set + * Check if a table exists * - * @param string $table Table name - * @param array $columns Columns Syntax for Rename: [ $old1 => [ $new1, $type1 ], $old2 => [ $new2, $type2 ], ... ] ) - * Syntax for Primary Key: [ $col1, $col2, ...] ) - * @param int $type The type of renaming (Default is Column) - * - * @return boolean Was the renaming successful? + * @param string|array $table Table name * + * @return boolean Does the table exist? + * @throws Exception */ - public static function rename($table, $columns, $type = self::RENAME_COLUMN) { - if (empty($table) || empty($columns)) { - return false; - } - - if (!is_array($columns)) { + public static function existsTable($table) + { + if (empty($table)) { return false; } - $table = DBA::escape($table); - - $sql = "ALTER TABLE `" . $table . "`"; - switch ($type) { - case self::RENAME_COLUMN: - if (!self::existsColumn($table, array_keys($columns))) { - return false; - } - $sql .= implode(',', array_map( - function ($to, $from) { - return " CHANGE `" . $from . "` `" . $to[0] . "` " . $to[1]; - }, - $columns, - array_keys($columns) - )); - break; - case self::RENAME_PRIMARY_KEY: - if (!self::existsColumn($table, $columns)) { - return false; - } - $sql .= " DROP PRIMARY KEY, ADD PRIMARY KEY(`" . implode('`, `', $columns) . "`)"; - break; - default: - return false; - } - - $sql .= ";"; - - $stmt = DBA::p($sql); - - if (is_bool($stmt)) { - $retval = $stmt; + if (is_array($table)) { + $condition = ['table_schema' => key($table), 'table_name' => current($table)]; } else { - $retval = true; + $condition = ['table_schema' => DBA::databaseName(), 'table_name' => $table]; } - DBA::close($stmt); + $result = DBA::exists(['information_schema' => 'tables'], $condition); - return $retval; + return $result; } /** - * Loads the database structure definition from the config/dbstructure.config.php file. - * On first pass, defines DB_UPDATE_VERSION constant. + * Returns the columns of a table * - * @see config/dbstructure.config.php - * @param boolean $with_addons_structure Whether to tack on addons additional tables - * @return array + * @param string $table Table name + * + * @return array An array of the table columns * @throws Exception */ - public static function definition($with_addons_structure = true) + public static function getColumns($table) { - if (!self::$definition) { - $a = \Friendica\BaseObject::getApp(); - - $filename = $a->getBasePath() . '/config/dbstructure.config.php'; - - if (!is_readable($filename)) { - throw new Exception('Missing database structure config file config/dbstructure.config.php'); - } - - $definition = require $filename; - - if (!$definition) { - throw new Exception('Corrupted database structure config file config/dbstructure.config.php'); - } - - self::$definition = $definition; - } else { - $definition = self::$definition; - } - - if ($with_addons_structure) { - Hook::callAll('dbstructure_definition', $definition); - } - - return $definition; + $stmtColumns = DBA::p("SHOW COLUMNS FROM `" . $table . "`"); + return DBA::toArray($stmtColumns); } }