X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=src%2FDatabase%2FDBStructure.php;h=bd54ecf8c562fe1c0bb9521ddd1284f645580f03;hb=c5be1092d0538d02d0e0da88bec89e6232501728;hp=d70f7f4ec6b75c7dcba90b556eda3798d82f7469;hpb=174129af946b912f842cffc474b7ee5119f76247;p=friendica.git diff --git a/src/Database/DBStructure.php b/src/Database/DBStructure.php index d70f7f4ec6..bd54ecf8c5 100644 --- a/src/Database/DBStructure.php +++ b/src/Database/DBStructure.php @@ -1,19 +1,32 @@ . + * */ namespace Friendica\Database; use Exception; -use Friendica\Core\Config; use Friendica\Core\Hook; use Friendica\Core\Logger; use Friendica\DI; use Friendica\Util\DateTimeFormat; -require_once __DIR__ . '/../../include/dba.php'; - /** * This class contains functions that doesn't need to know if pdo, mysqli or whatever is used. */ @@ -34,7 +47,7 @@ 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() { @@ -44,13 +57,19 @@ class DBStructure ['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.') . "\n"; + echo DI::l10n()->t('There are no tables on MyISAM or InnoDB with the Antelope file format.') . "\n"; return; } foreach ($tables AS $table) { - $sql = "ALTER TABLE " . DBA::quoteIdentifier($table['table_name']) . " engine=InnoDB;"; + $sql = "ALTER TABLE " . DBA::quoteIdentifier($table['table_name']) . " ENGINE=InnoDB ROW_FORMAT=DYNAMIC;"; echo $sql . "\n"; $result = DBA::e($sql); @@ -91,10 +110,12 @@ 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 static/dbstructure.config.php @@ -139,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"])) { @@ -155,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"]; } @@ -260,8 +290,8 @@ class DBStructure 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', DI::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 = ''; @@ -272,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)) { @@ -364,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. @@ -418,9 +449,43 @@ class DBStructure } } - if (isset($database[$name]["table_status"]["Comment"])) { + $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"]["Comment"] != $structurecomment) { + if ($database[$name]["table_status"]["TABLE_COMMENT"] != $structurecomment) { $sql2 = "COMMENT = '" . DBA::escape($structurecomment) . "'"; if ($sql3 == "") { @@ -431,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 == "") { @@ -443,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 == "") { @@ -522,7 +587,7 @@ class DBStructure if ($action) { if (!$install) { - Config::set('system', 'maintenance_reason', DI::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 @@ -573,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); } } @@ -589,22 +656,36 @@ class DBStructure private static function tableStructure($table) { - $structures = q("DESCRIBE `%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))); - $full_columns = q("SHOW FULL COLUMNS 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]); - $indexes = q("SHOW INDEX FROM `%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]); - $table_status = q("SHOW TABLE STATUS WHERE `name` = '%s'", $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) { @@ -625,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["Field"]]["type"] = $field["Type"]; - if ($field["Null"] == "NO") { - $fielddata[$field["Field"]]["not null"] = true; + $fielddata[$field['COLUMN_NAME']]['type'] = $field['COLUMN_TYPE']; + + 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) @@ -678,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. *