X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=src%2FDatabase%2FDBStructure.php;h=bd54ecf8c562fe1c0bb9521ddd1284f645580f03;hb=c5be1092d0538d02d0e0da88bec89e6232501728;hp=7d52e60caec13d53a0b0b811754932c76ca121ed;hpb=be1b398caf635058782d5aecbed3a0decc1aeba4;p=friendica.git diff --git a/src/Database/DBStructure.php b/src/Database/DBStructure.php index 7d52e60cae..bd54ecf8c5 100644 --- a/src/Database/DBStructure.php +++ b/src/Database/DBStructure.php @@ -1,22 +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 'include/dba.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 @@ -35,21 +46,30 @@ 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"; + $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'])); + foreach ($tables AS $table) { + $sql = "ALTER TABLE " . DBA::quoteIdentifier($table['table_name']) . " ENGINE=InnoDB ROW_FORMAT=DYNAMIC;"; echo $sql . "\n"; $result = DBA::e($sql); @@ -60,7 +80,7 @@ class DBStructure } /** - * @brief Print out database error messages + * Print out database error messages * * @param string $message Message to be added to the error message * @@ -68,15 +88,15 @@ class DBStructure */ private static function printUpdateError($message) { - echo L10n::t("\nError %d occurred during database update:\n%s\n", + echo DI::l10n()->t("\nError %d occurred during database update:\n%s\n", DBA::errorNo(), DBA::errorMessage()); - return L10n::t('Errors encountered performing database changes: ') . $message . EOL; + return DI::l10n()->t('Errors encountered performing database changes: ') . $message . EOL; } - public static function printStructure() + public static function printStructure($basePath) { - $database = self::definition(false); + $database = self::definition($basePath, false); echo "-- ------------------------------------------\n"; echo "-- " . FRIENDICA_PLATFORM . " " . FRIENDICA_VERSION . " (" . FRIENDICA_CODENAME, ")\n"; @@ -90,32 +110,34 @@ class DBStructure echo "\n"; } + + View::printStructure($basePath); } /** - * Loads the database structure definition from the config/dbstructure.config.php file. + * Loads the database structure definition from the static/dbstructure.config.php file. * On first pass, defines DB_UPDATE_VERSION constant. * - * @see config/dbstructure.config.php + * @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($with_addons_structure = true) + public static function definition($basePath, $with_addons_structure = true) { if (!self::$definition) { - $a = \Friendica\BaseObject::getApp(); - $filename = $a->getBasePath() . '/config/dbstructure.config.php'; + $filename = $basePath . '/static/dbstructure.config.php'; if (!is_readable($filename)) { - throw new Exception('Missing database structure config file config/dbstructure.config.php'); + throw new Exception('Missing database structure config file static/dbstructure.config.php'); } $definition = require $filename; if (!$definition) { - throw new Exception('Corrupted database structure config file config/dbstructure.config.php'); + throw new Exception('Corrupted database structure config file static/dbstructure.config.php'); } self::$definition = $definition; @@ -138,11 +160,16 @@ class DBStructure $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 (!empty($structure["indexes"])) { @@ -154,6 +181,10 @@ class DBStructure } } + foreach ($foreign_keys AS $fieldname => $parameters) { + $sql_rows[] = self::foreignCommand($name, $fieldname, $parameters); + } + if (isset($structure["engine"])) { $engine = " ENGINE=" . $structure["engine"]; } @@ -247,19 +278,20 @@ class DBStructure /** * 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 = ''; @@ -270,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)) { @@ -284,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 @@ -362,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. @@ -416,9 +449,43 @@ class DBStructure } } - if (isset($database[$name]["table_status"]["Comment"])) { - $structurecomment = defaults($structure, "comment", ""); - if ($database[$name]["table_status"]["Comment"] != $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; + } else { + $sql3 .= ", " . $sql2; + } + } + } + + 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 == "") { @@ -429,8 +496,8 @@ class DBStructure } } - if (isset($database[$name]["table_status"]["Engine"]) && isset($structure['engine'])) { - if ($database[$name]["table_status"]["Engine"] != $structure['engine']) { + 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 == "") { @@ -441,8 +508,8 @@ class DBStructure } } - if (isset($database[$name]["table_status"]["Collation"])) { - if ($database[$name]["table_status"]["Collation"] != 'utf8mb4_general_ci') { + 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 == "") { @@ -461,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'])) { @@ -520,7 +587,7 @@ class DBStructure 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 @@ -571,14 +638,16 @@ 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); } } @@ -587,22 +656,36 @@ class DBStructure private static function tableStructure($table) { - $structures = q("DESCRIBE `%s`", $table); - - $full_columns = q("SHOW FULL COLUMNS FROM `%s`", $table); + // This query doesn't seem to be executable as a prepared statement + $indexes = DBA::toArray(DBA::p("SHOW INDEX FROM " . DBA::quoteIdentifier($table))); - $indexes = q("SHOW INDEX FROM `%s`", $table); + $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]); - $table_status = q("SHOW TABLE STATUS WHERE `name` = '%s'", $table); + $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 (DBA::isResult($table_status)) { - $table_status = $table_status[0]; - } else { - $table_status = []; - } + $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 (DBA::isResult($indexes)) { foreach ($indexes AS $index) { @@ -623,39 +706,39 @@ class DBStructure $indexdata[$index["Key_name"]][] = $column; } } - if (DBA::isResult($structures)) { - foreach ($structures AS $field) { - // Replace the default size values so that we don't have to define them + + $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["Type"] = str_replace($search, $replace, $field["Type"]); + $field['COLUMN_TYPE'] = str_replace($search, $replace, $field['COLUMN_TYPE']); + + $fielddata[$field['COLUMN_NAME']]['type'] = $field['COLUMN_TYPE']; - $fielddata[$field["Field"]]["type"] = $field["Type"]; - if ($field["Null"] == "NO") { - $fielddata[$field["Field"]]["not null"] = true; + if ($field['IS_NULLABLE'] == 'NO') { + $fielddata[$field['COLUMN_NAME']]['not null'] = true; } - if (isset($field["Default"])) { - $fielddata[$field["Field"]]["default"] = $field["Default"]; + if (isset($field['COLUMN_DEFAULT'])) { + $fielddata[$field['COLUMN_NAME']]['default'] = $field['COLUMN_DEFAULT']; } - if ($field["Extra"] != "") { - $fielddata[$field["Field"]]["extra"] = $field["Extra"]; + if (!empty($field['EXTRA'])) { + $fielddata[$field['COLUMN_NAME']]['extra'] = $field['EXTRA']; } - if ($field["Key"] == "PRI") { - $fielddata[$field["Field"]]["primary"] = true; + if ($field['COLUMN_KEY'] == 'PRI') { + $fielddata[$field['COLUMN_NAME']]['primary'] = 1; } - } - } - if (DBA::isResult($full_columns)) { - foreach ($full_columns AS $column) { - $fielddata[$column["Field"]]["Collation"] = $column["Collation"]; - $fielddata[$column["Field"]]["comment"] = $column["Comment"]; + + $fielddata[$field['COLUMN_NAME']]['Collation'] = $field['COLLATION_NAME']; + $fielddata[$field['COLUMN_NAME']]['comment'] = $field['COLUMN_COMMENT']; } } - return ["fields" => $fielddata, "indexes" => $indexdata, "table_status" => $table_status]; + return ["fields" => $fielddata, "indexes" => $indexdata, + "foreign_keys" => $foreigndata, "table_status" => $table_status]; } private static function dropIndex($indexname) @@ -676,6 +759,48 @@ class DBStructure 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 foreignCommand(string $tablename, string $fieldname, array $parameters) { + $foreign_table = array_keys($parameters['foreign'])[0]; + $foreign_field = array_values($parameters['foreign'])[0]; + + $constraint = self::getConstraintName($tablename, $fieldname, $parameters); + + $sql = "CONSTRAINT `" . $constraint . "` FOREIGN KEY (`" . $fieldname . "`)" . + " REFERENCES `" . $foreign_table . "` (`" . $foreign_field . "`)"; + + if (!empty($parameters['foreign']['on update'])) { + $sql .= " ON UPDATE " . strtoupper($parameters['foreign']['on update']); + } else { + $sql .= " ON UPDATE RESTRICT"; + } + + if (!empty($parameters['foreign']['on delete'])) { + $sql .= " ON DELETE " . strtoupper($parameters['foreign']['on delete']); + } else { + $sql .= " ON DELETE CASCADE"; + } + + 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); + } + /** * Constructs a GROUP BY clause from a UNIQUE index definition. * @@ -713,8 +838,8 @@ class DBStructure * @todo You cannot rename a primary key if "auto increment" is set * * @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 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? @@ -816,7 +941,7 @@ class DBStructure /** * Check if a table exists * - * @param string $table Table name + * @param string|array $table Table name * * @return boolean Does the table exist? * @throws Exception @@ -827,20 +952,28 @@ class DBStructure return false; } - $table = DBA::escape($table); - - $sql = "SHOW TABLES LIKE '" . $table . "';"; - - $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 = (DBA::numRows($stmt) > 0); + $condition = ['table_schema' => DBA::databaseName(), 'table_name' => $table]; } - DBA::close($stmt); + $result = DBA::exists(['information_schema' => 'tables'], $condition); - return $retval; + return $result; + } + + /** + * Returns the columns of a table + * + * @param string $table Table name + * + * @return array An array of the table columns + * @throws Exception + */ + public static function getColumns($table) + { + $stmtColumns = DBA::p("SHOW COLUMNS FROM `" . $table . "`"); + return DBA::toArray($stmtColumns); } }