X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=src%2FDatabase%2FDBStructure.php;h=8f599a67d72a3e15b7886531181ec285987e8e76;hb=a7f777ee433f8cb5b141f0d413561881d2533c22;hp=dc13bd6566d94228cab5d63205dde2461c2533c1;hpb=51bc5279a07397e28b35fbeefb27ea6041f9b6cd;p=friendica.git diff --git a/src/Database/DBStructure.php b/src/Database/DBStructure.php index dc13bd6566..8f599a67d7 100644 --- a/src/Database/DBStructure.php +++ b/src/Database/DBStructure.php @@ -27,8 +27,6 @@ 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. */ @@ -162,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"])) { @@ -178,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"]; } @@ -295,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)) { @@ -387,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. @@ -441,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 == "") { @@ -454,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 == "") { @@ -466,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 == "") { @@ -596,7 +638,7 @@ class DBStructure } } - View::create($verbose, $action); + View::create(false, $action); if ($action && !$install) { DI::config()->set('system', 'maintenance', 0); @@ -614,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(sprintf("SHOW INDEX FROM `%s`", $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) { @@ -650,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'] = 1; } - 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) @@ -703,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. *