From a910fd8864f5f1e9c35e9065abad24353793f313 Mon Sep 17 00:00:00 2001 From: Philipp Date: Tue, 12 Jul 2022 23:21:16 +0200 Subject: [PATCH] Split DBStructure & View to avoid DB-calls and dependencies for basic operations - new "Definition" classes vor DB and Views - new "Writer" classes to create SQL definitions for DB and Views - DBStructure & View are responsible to execute DB-querys --- src/Console/AutomaticInstallation.php | 2 +- src/Console/DatabaseStructure.php | 30 +- src/Core/Installer.php | 6 +- src/DI.php | 18 +- src/Database/DBStructure.php | 474 ++------------------ src/Database/Database.php | 25 +- src/Database/Definition/DbaDefinition.php | 125 ++++++ src/Database/Definition/ViewDefinition.php | 91 ++++ src/Database/View.php | 132 +----- src/Model/APContact.php | 2 +- src/Model/Attach.php | 3 +- src/Model/Contact/User.php | 3 +- src/Model/GServer.php | 2 +- src/Model/Photo.php | 2 +- src/Model/Post.php | 13 +- src/Model/Post/Content.php | 5 +- src/Model/Post/History.php | 6 +- src/Model/Post/Question.php | 3 +- src/Model/Post/QuestionOption.php | 3 +- src/Model/Post/Thread.php | 5 +- src/Model/Post/ThreadUser.php | 5 +- src/Model/Post/User.php | 5 +- src/Model/Post/UserNotification.php | 4 +- src/Module/Install.php | 2 +- src/Module/Settings/UserExport.php | 4 +- src/Util/Writer/DbaDefinitionSqlWriter.php | 301 +++++++++++++ src/Util/Writer/DocWriter.php | 145 ++++++ src/Util/Writer/ViewDefinitionSqlWriter.php | 66 +++ src/Worker/ExpirePosts.php | 6 +- static/dependencies.config.php | 18 + 30 files changed, 898 insertions(+), 608 deletions(-) create mode 100644 src/Database/Definition/DbaDefinition.php create mode 100644 src/Database/Definition/ViewDefinition.php create mode 100644 src/Util/Writer/DbaDefinitionSqlWriter.php create mode 100644 src/Util/Writer/DocWriter.php create mode 100644 src/Util/Writer/ViewDefinitionSqlWriter.php diff --git a/src/Console/AutomaticInstallation.php b/src/Console/AutomaticInstallation.php index e3e9e22cb3..ca25d71a6f 100644 --- a/src/Console/AutomaticInstallation.php +++ b/src/Console/AutomaticInstallation.php @@ -225,7 +225,7 @@ HELP; $installer->resetChecks(); - if (!$installer->installDatabase($basePathConf)) { + if (!$installer->installDatabase()) { $errorMessage = $this->extractErrors($installer->getChecks()); throw new RuntimeException($errorMessage); } diff --git a/src/Console/DatabaseStructure.php b/src/Console/DatabaseStructure.php index 652abfd97d..b7eafaafdb 100644 --- a/src/Console/DatabaseStructure.php +++ b/src/Console/DatabaseStructure.php @@ -25,6 +25,12 @@ use Friendica\Core\Config\ValueObject\Cache; use Friendica\Core\Update; use Friendica\Database\Database; use Friendica\Database\DBStructure; +use Friendica\Database\Definition\DbaDefinition; +use Friendica\Database\Definition\ViewDefinition; +use Friendica\Util\BasePath; +use Friendica\Util\Writer\DbaDefinitionSqlWriter; +use Friendica\Util\Writer\DocWriter; +use Friendica\Util\Writer\ViewDefinitionSqlWriter; use RuntimeException; /** @@ -42,6 +48,18 @@ class DatabaseStructure extends \Asika\SimpleConsole\Console * @var Cache */ private $configCache; + /** + * @var DbaDefinition + */ + private $dbaDefinition; + /** + * @var ViewDefinition + */ + private $viewDefinition; + /** + * @var string + */ + private $basePath; protected function getHelp() { @@ -71,12 +89,15 @@ HELP; return $help; } - public function __construct(Database $dba, Cache $configCache, $argv = null) + public function __construct(Database $dba, DbaDefinition $dbaDefinition, ViewDefinition $viewDefinition, BasePath $basePath, Cache $configCache, $argv = null) { parent::__construct($argv); $this->dba = $dba; + $this->dbaDefinition = $dbaDefinition; + $this->viewDefinition = $viewDefinition; $this->configCache = $configCache; + $this->basePath = $basePath->getPath(); } protected function doExecute() @@ -120,10 +141,9 @@ HELP; $output = ob_get_clean(); break; case "dumpsql": - DBStructure::writeStructure(); - ob_start(); - DBStructure::printStructure($basePath); - $output = ob_get_clean(); + DocWriter::writeDbDefinition($this->dbaDefinition, $this->basePath); + $output = DbaDefinitionSqlWriter::create($this->dbaDefinition); + $output .= ViewDefinitionSqlWriter::create($this->viewDefinition); break; case "toinnodb": ob_start(); diff --git a/src/Core/Installer.php b/src/Core/Installer.php index 7af94c2e1e..8ed6c9afa4 100644 --- a/src/Core/Installer.php +++ b/src/Core/Installer.php @@ -189,14 +189,12 @@ class Installer /*** * Installs the DB-Scheme for Friendica * - * @param string $basePath The base path of this application - * * @return bool true if the installation was successful, otherwise false * @throws Exception */ - public function installDatabase($basePath) + public function installDatabase() { - $result = DBStructure::install($basePath); + $result = DBStructure::install(); if ($result) { $txt = DI::l10n()->t('You may need to import the file "database.sql" manually using phpmyadmin or mysql.') . EOL; diff --git a/src/DI.php b/src/DI.php index e34df7b669..e8fa90eb9b 100644 --- a/src/DI.php +++ b/src/DI.php @@ -65,11 +65,27 @@ abstract class DI /** * @return Database\Database */ - public static function dba() + public static function dba(): Database\Database { return self::$dice->create(Database\Database::class); } + /** + * @return \Friendica\Database\Definition\DbaDefinition + */ + public static function dbaDefinition(): Database\Definition\DbaDefinition + { + return self::$dice->create(Database\Definition\DbaDefinition::class); + } + + /** + * @return \Friendica\Database\Definition\ViewDefinition + */ + public static function viewDefinition(): Database\Definition\ViewDefinition + { + return self::$dice->create(Database\Definition\ViewDefinition::class); + } + // // "App" namespace instances // diff --git a/src/Database/DBStructure.php b/src/Database/DBStructure.php index 0ef99bb10a..b4f8ed1329 100644 --- a/src/Database/DBStructure.php +++ b/src/Database/DBStructure.php @@ -22,13 +22,12 @@ namespace Friendica\Database; use Exception; -use Friendica\Core\Hook; use Friendica\Core\Logger; -use Friendica\Core\Renderer; use Friendica\DI; use Friendica\Model\Item; use Friendica\Model\User; use Friendica\Util\DateTimeFormat; +use Friendica\Util\Writer\DbaDefinitionSqlWriter; /** * This class contains functions that doesn't need to know if pdo, mysqli or whatever is used. @@ -42,13 +41,6 @@ class DBStructure const RENAME_COLUMN = 0; const RENAME_PRIMARY_KEY = 1; - /** - * Database structure definition loaded from config/dbstructure.config.php - * - * @var array - */ - private static $definition = []; - /** * Set a database version to trigger update functions * @@ -160,349 +152,14 @@ class DBStructure return DI::l10n()->t('Errors encountered performing database changes: ') . $message . EOL; } - public static function writeStructure() - { - $tables = []; - foreach (self::definition('') as $name => $definition) { - $indexes = [[ - 'name' => 'Name', - 'fields' => 'Fields', - ], - [ - 'name' => '-', - 'fields' => '-', - ]]; - - $lengths = ['name' => 4, 'fields' => 6]; - foreach ($definition['indexes'] as $key => $value) { - $fieldlist = implode(', ', $value); - $indexes[] = ['name' => $key, 'fields' => $fieldlist]; - $lengths['name'] = max($lengths['name'], strlen($key)); - $lengths['fields'] = max($lengths['fields'], strlen($fieldlist)); - } - - array_walk_recursive($indexes, function(&$value, $key) use ($lengths) - { - $value = str_pad($value, $lengths[$key], $value === '-' ? '-' : ' '); - }); - - $foreign = []; - $fields = [[ - 'name' => 'Field', - 'comment' => 'Description', - 'type' => 'Type', - 'null' => 'Null', - 'primary' => 'Key', - 'default' => 'Default', - 'extra' => 'Extra', - ], - [ - 'name' => '-', - 'comment' => '-', - 'type' => '-', - 'null' => '-', - 'primary' => '-', - 'default' => '-', - 'extra' => '-', - ]]; - $lengths = [ - 'name' => 5, - 'comment' => 11, - 'type' => 4, - 'null' => 4, - 'primary' => 3, - 'default' => 7, - 'extra' => 5, - ]; - foreach ($definition['fields'] as $key => $value) { - $field = []; - $field['name'] = $key; - $field['comment'] = $value['comment'] ?? ''; - $field['type'] = $value['type']; - $field['null'] = ($value['not null'] ?? false) ? 'NO' : 'YES'; - $field['primary'] = ($value['primary'] ?? false) ? 'PRI' : ''; - $field['default'] = $value['default'] ?? 'NULL'; - $field['extra'] = $value['extra'] ?? ''; - - foreach ($field as $fieldName => $fieldvalue) { - $lengths[$fieldName] = max($lengths[$fieldName] ?? 0, strlen($fieldvalue)); - } - $fields[] = $field; - - if (!empty($value['foreign'])) { - $foreign[] = [ - 'field' => $key, - 'targettable' => array_keys($value['foreign'])[0], - 'targetfield' => array_values($value['foreign'])[0] - ]; - } - } - - array_walk_recursive($fields, function(&$value, $key) use ($lengths) - { - $value = str_pad($value, $lengths[$key], $value === '-' ? '-' : ' '); - }); - - $tables[] = ['name' => $name, 'comment' => $definition['comment']]; - $content = Renderer::replaceMacros(Renderer::getMarkupTemplate('structure.tpl'), [ - '$name' => $name, - '$comment' => $definition['comment'], - '$fields' => $fields, - '$indexes' => $indexes, - '$foreign' => $foreign, - ]); - $filename = DI::basePath() . '/doc/database/db_' . $name . '.md'; - file_put_contents($filename, $content); - } - asort($tables); - $content = Renderer::replaceMacros(Renderer::getMarkupTemplate('tables.tpl'), [ - '$tables' => $tables, - ]); - $filename = DI::basePath() . '/doc/database.md'; - file_put_contents($filename, $content); - } - - public static function printStructure(string $basePath) - { - $database = self::definition($basePath, false); - - 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); - - echo "\n"; - } - - View::printStructure($basePath); - } - - /** - * 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 string $basePath The base path of this application - * @param boolean $with_addons_structure Whether to tack on addons additional tables - * @return array - * @throws Exception - */ - public static function definition(string $basePath, bool $with_addons_structure = true): array - { - if (!self::$definition) { - if (empty($basePath)) { - $basePath = DI::app()->getBasePath(); - } - - $filename = $basePath . '/static/dbstructure.config.php'; - - if (!is_readable($filename)) { - throw new Exception('Missing database structure config file static/dbstructure.config.php at basePath=' . $basePath); - } - - $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 ($with_addons_structure) { - Hook::callAll('dbstructure_definition', $definition); - } - - return $definition; - } - - /** - * Get field data for the given table - * - * @param string $table Tavle to load field definitions for - * @param array $data data fields - * @return array fields for the given - */ - public static function getFieldsForTable(string $table, array $data = []): array - { - $definition = DBStructure::definition('', false); - if (empty($definition[$table])) { - return []; - } - - $fieldNames = array_keys($definition[$table]['fields']); - - $fields = []; - - // Assign all field that are present in the table - foreach ($fieldNames as $field) { - if (isset($data[$field])) { - // Limit the length of varchar, varbinary, char and binrary fields - if (is_string($data[$field]) && preg_match("/char\((\d*)\)/", $definition[$table]['fields'][$field]['type'], $result)) { - $data[$field] = mb_substr($data[$field], 0, $result[1]); - } elseif (is_string($data[$field]) && preg_match("/binary\((\d*)\)/", $definition[$table]['fields'][$field]['type'], $result)) { - $data[$field] = substr($data[$field], 0, $result[1]); - } - $fields[$field] = $data[$field]; - } - } - - return $fields; - } - - /** - * Creates given table with structure - * - * @param string $name Name of table - * @param array $structure Structure of table - * @param boolean $verbose Output SQL statements - * @param boolean $action Whether to run the SQL commands - * @return Whether the SQL command ran successful - */ - private static function createTable(string $name, array $structure, bool $verbose, bool $action): bool - { - $r = 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 (!empty($structure['indexes'])) { - foreach ($structure['indexes'] as $indexName => $fieldNames) { - $sql_index = self::createIndex($indexName, $fieldNames, ''); - if (!is_null($sql_index)) { - $sql_rows[] = $sql_index; - } - } - } - - 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; - } - - /** - * Returns SQL statement for field - * - * @param array $parameters Parameters for SQL statement - * @param boolean $create Whether to include PRIMARY KEY statement (unused) - * @return string SQL statement part - */ - private static function FieldCommand(array $parameters, bool $create = true): string - { - $fieldstruct = $parameters['type']; - - if (isset($parameters['Collation'])) { - $fieldstruct .= ' COLLATE ' . $parameters['Collation']; - } - - 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; - } - - private static function createIndex(string $indexName, array $fieldNames, string $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) . "`"; - } - } - - if ($indexName == 'PRIMARY') { - return sprintf("%s PRIMARY KEY(%s)", $method, $names); - } - - - return sprintf("%s INDEX `%s` (%s)", $method, DBA::escape($indexName), $names); - } - /** * Perform a database structure dryrun (means: just simulating) * * @throws Exception */ - public static function dryRun() + public static function dryRun(): string { - self::update(DI::app()->getBasePath(), true, false); + return self::update(true, false); } /** @@ -520,7 +177,7 @@ class DBStructure DI::config()->set('system', 'maintenance', 1); } - $status = self::update(DI::app()->getBasePath(), $verbose, true); + $status = self::update($verbose, true); if ($enable_maintenance_mode) { DI::config()->set('system', 'maintenance', 0); @@ -533,20 +190,17 @@ class DBStructure /** * Updates DB structure from the installation and returns eventual errors messages * - * @param string $basePath The base path of this application - * * @return string Empty string if the update is successful, error messages otherwise * @throws Exception */ - public static function install(string $basePath): string + public static function install(): string { - return self::update($basePath, false, true, true); + return self::update(false, true, true); } /** * Updates DB structure and returns eventual errors messages * - * @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? @@ -555,7 +209,7 @@ class DBStructure * @return string Empty string if the update is successful, error messages otherwise * @throws Exception */ - private static function update(string $basePath, bool $verbose, bool $action, bool $install = false, array $tables = null, array $definition = null): string + private static function update(bool $verbose, bool $action, bool $install = false, array $tables = null, array $definition = null): string { $in_maintenance_mode = DI::config()->get('system', 'maintenance'); @@ -593,7 +247,7 @@ class DBStructure // Get the definition if (is_null($definition)) { - $definition = self::definition($basePath); + $definition = DI::dbaDefinition()->getAll(); } // MySQL >= 5.7.4 doesn't support the IGNORE keyword in ALTER TABLE statements @@ -607,11 +261,17 @@ class DBStructure // Compare it foreach ($definition as $name => $structure) { $is_new_table = false; - $sql3 = ""; + $sql3 = ""; if (!isset($database[$name])) { - $r = self::createTable($name, $structure, $verbose, $action); - if (!DBA::isResult($r)) { - $errors .= self::printUpdateError($name); + $sql = DbaDefinitionSqlWriter::createTable($name, $structure, $verbose, $action); + if ($verbose) { + echo $sql; + } + if ($action) { + $r = DBA::e($sql); + if (!DBA::isResult($r)) { + $errors .= self::printUpdateError($name); + } } $is_new_table = true; } else { @@ -628,7 +288,7 @@ class DBStructure $new_index_definition = "__NOT_SET__"; } if ($current_index_definition != $new_index_definition && substr($indexName, 0, 6) != 'local_') { - $sql2 = self::dropIndex($indexName); + $sql2 = DbaDefinitionSqlWriter::dropIndex($indexName); if ($sql3 == "") { $sql3 = "ALTER" . $ignore . " TABLE `" . $name . "` " . $sql2; } else { @@ -639,7 +299,7 @@ class DBStructure // 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 = DbaDefinitionSqlWriter::addTableField($fieldName, $parameters); if ($sql3 == "") { $sql3 = "ALTER" . $ignore . " TABLE `" . $name . "` " . $sql2; } else { @@ -665,9 +325,9 @@ class DBStructure } $current_field_definition = DBA::cleanQuery(implode(",", $field_definition)); - $new_field_definition = DBA::cleanQuery(implode(",", $parameters)); + $new_field_definition = DBA::cleanQuery(implode(",", $parameters)); if ($current_field_definition != $new_field_definition) { - $sql2 = self::modifyTableField($fieldName, $parameters); + $sql2 = DbaDefinitionSqlWriter::modifyTableField($fieldName, $parameters); if ($sql3 == "") { $sql3 = "ALTER" . $ignore . " TABLE `" . $name . "` " . $sql2; } else { @@ -692,7 +352,7 @@ class DBStructure } $new_index_definition = implode(",", $fieldNames); if ($current_index_definition != $new_index_definition) { - $sql2 = self::createIndex($indexName, $fieldNames); + $sql2 = DbaDefinitionSqlWriter::createIndex($indexName, $fieldNames); if ($sql2 != "") { if ($sql3 == "") { @@ -718,7 +378,7 @@ class DBStructure unset($existing_foreign_keys[$constraint]); if (empty($database[$name]['foreign_keys'][$constraint])) { - $sql2 = self::addForeignKey($name, $fieldName, $parameters); + $sql2 = DbaDefinitionSqlWriter::addForeignKey($fieldName, $parameters); if ($sql3 == "") { $sql3 = "ALTER" . $ignore . " TABLE `" . $name . "` " . $sql2; @@ -729,7 +389,7 @@ class DBStructure } foreach ($existing_foreign_keys as $param) { - $sql2 = self::dropForeignKey($param['CONSTRAINT_NAME']); + $sql2 = DbaDefinitionSqlWriter::dropForeignKey($param['CONSTRAINT_NAME']); if ($sql3 == "") { $sql3 = "ALTER" . $ignore . " TABLE `" . $name . "` " . $sql2; @@ -793,7 +453,7 @@ class DBStructure } if ($field_definition['Collation'] != $parameters['Collation']) { - $sql2 = self::modifyTableField($fieldName, $parameters); + $sql2 = DbaDefinitionSqlWriter::modifyTableField($fieldName, $parameters); if (($sql3 == "") || (substr($sql3, -2, 2) == "; ")) { $sql3 .= "ALTER" . $ignore . " TABLE `" . $name . "` " . $sql2; } else { @@ -853,28 +513,28 @@ class DBStructure $fields = DBA::selectToArray('INFORMATION_SCHEMA.COLUMNS', ['COLUMN_NAME', 'COLUMN_TYPE', 'IS_NULLABLE', 'COLUMN_DEFAULT', 'EXTRA', - 'COLUMN_KEY', 'COLLATION_NAME', 'COLUMN_COMMENT'], + 'COLUMN_KEY', 'COLLATION_NAME', 'COLUMN_COMMENT'], ["`TABLE_SCHEMA` = ? AND `TABLE_NAME` = ?", - DBA::databaseName(), $table]); + DBA::databaseName(), $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]); + DBA::databaseName(), $table]); $table_status = DBA::selectFirst('INFORMATION_SCHEMA.TABLES', ['ENGINE', 'TABLE_COLLATION', 'TABLE_COMMENT'], ["`TABLE_SCHEMA` = ? AND `TABLE_NAME` = ?", - DBA::databaseName(), $table]); + DBA::databaseName(), $table]); - $fielddata = []; - $indexdata = []; + $fielddata = []; + $indexdata = []; $foreigndata = []; if (DBA::isResult($foreign_keys)) { foreach ($foreign_keys as $foreign_key) { - $parameters = ['foreign' => [$foreign_key['REFERENCED_TABLE_NAME'] => $foreign_key['REFERENCED_COLUMN_NAME']]]; - $constraint = self::getConstraintName($table, $foreign_key['COLUMN_NAME'], $parameters); + $parameters = ['foreign' => [$foreign_key['REFERENCED_TABLE_NAME'] => $foreign_key['REFERENCED_COLUMN_NAME']]]; + $constraint = self::getConstraintName($table, $foreign_key['COLUMN_NAME'], $parameters); $foreigndata[$constraint] = $foreign_key; } } @@ -902,8 +562,8 @@ class DBStructure $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']; + $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']; @@ -925,33 +585,18 @@ class DBStructure } $fielddata[$field['COLUMN_NAME']]['Collation'] = $field['COLLATION_NAME']; - $fielddata[$field['COLUMN_NAME']]['comment'] = $field['COLUMN_COMMENT']; + $fielddata[$field['COLUMN_NAME']]['comment'] = $field['COLUMN_COMMENT']; } } return [ - 'fields' => $fielddata, - 'indexes' => $indexdata, + 'fields' => $fielddata, + 'indexes' => $indexdata, 'foreign_keys' => $foreigndata, 'table_status' => $table_status ]; } - private static function dropIndex(string $indexName): string - { - return sprintf("DROP INDEX `%s`", DBA::escape($indexName)); - } - - private static function addTableField(string $fieldName, array $parameters): string - { - return sprintf("ADD `%s` %s", DBA::escape($fieldName), self::FieldCommand($parameters)); - } - - private static function modifyTableField(string $fieldName, array $parameters): string - { - return sprintf("MODIFY `%s` %s", DBA::escape($fieldName), self::FieldCommand($parameters, false)); - } - private static function getConstraintName(string $tableName, string $fieldName, array $parameters): string { $foreign_table = array_keys($parameters['foreign'])[0]; @@ -960,37 +605,6 @@ class DBStructure 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]; - - $sql = "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): string - { - return sprintf("ADD %s", self::foreignCommand($tableName, $fieldName, $parameters)); - } - - private static function dropForeignKey(string $constraint): string - { - return sprintf("DROP FOREIGN KEY `%s`", $constraint); - } - /** * Renames columns or the primary key of a table * @@ -1108,7 +722,7 @@ class DBStructure { return DBA::exists('INFORMATION_SCHEMA.KEY_COLUMN_USAGE', ["`TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? AND `COLUMN_NAME` = ? AND `REFERENCED_TABLE_SCHEMA` IS NOT NULL", - DBA::databaseName(), $table, $field]); + DBA::databaseName(), $table, $field]); } /** @@ -1181,8 +795,8 @@ class DBStructure if (self::existsTable('user') && !DBA::exists('user', ['uid' => 0])) { $user = [ - 'verified' => true, - 'page-flags' => User::PAGE_FLAGS_SOAPBOX, + 'verified' => true, + 'page-flags' => User::PAGE_FLAGS_SOAPBOX, 'account-type' => User::ACCOUNT_TYPE_RELAY, ]; DBA::insert('user', $user); @@ -1258,7 +872,7 @@ class DBStructure $permission = ''; } $fields = ['id' => $set['psid'], 'uid' => $set['uid'], 'allow_cid' => $permission, - 'allow_gid' => '', 'deny_cid' => '', 'deny_gid' => '']; + 'allow_gid' => '', 'deny_cid' => '', 'deny_gid' => '']; DBA::insert('permissionset', $fields); } DBA::close($sets); @@ -1288,7 +902,7 @@ class DBStructure $isUpdate = false; $processes = DBA::select('information_schema.processlist', ['info'], [ - 'db' => DBA::databaseName(), + 'db' => DBA::databaseName(), 'command' => ['Query', 'Execute'] ]); diff --git a/src/Database/Database.php b/src/Database/Database.php index 6e5e147da9..3276e90e52 100644 --- a/src/Database/Database.php +++ b/src/Database/Database.php @@ -23,6 +23,8 @@ namespace Friendica\Database; use Friendica\Core\Config\ValueObject\Cache; use Friendica\Core\System; +use Friendica\Database\Definition\DbaDefinition; +use Friendica\Database\Definition\ViewDefinition; use Friendica\Network\HTTPException\ServiceUnavailableException; use Friendica\Util\DateTimeFormat; use Friendica\Util\Profiler; @@ -73,20 +75,21 @@ class Database protected $in_retrial = false; protected $testmode = false; private $relation = []; + /** @var DbaDefinition */ + protected $dbaDefinition; + /** @var ViewDefinition */ + protected $viewDefinition; - public function __construct(Cache $configCache, Profiler $profiler, LoggerInterface $logger) + public function __construct(Cache $configCache, Profiler $profiler, DbaDefinition $dbaDefinition, ViewDefinition $viewDefinition, LoggerInterface $logger) { // We are storing these values for being able to perform a reconnect - $this->configCache = $configCache; - $this->profiler = $profiler; - $this->logger = $logger; + $this->configCache = $configCache; + $this->profiler = $profiler; + $this->logger = $logger; + $this->dbaDefinition = $dbaDefinition; + $this->viewDefinition = $viewDefinition; $this->connect(); - - if ($this->isConnected()) { - // Loads DB_UPDATE_VERSION constant - DBStructure::definition($configCache->get('system', 'basepath'), false); - } } /** @@ -1609,10 +1612,10 @@ class Database $types = []; - $tables = DBStructure::definition('', false); + $tables = $this->dbaDefinition->getAll(); if (empty($tables[$table])) { // When a matching table wasn't found we check if it is a view - $views = View::definition('', false); + $views = $this->viewDefinition->getAll(); if (empty($views[$table])) { return $fields; } diff --git a/src/Database/Definition/DbaDefinition.php b/src/Database/Definition/DbaDefinition.php new file mode 100644 index 0000000000..9f42d176a7 --- /dev/null +++ b/src/Database/Definition/DbaDefinition.php @@ -0,0 +1,125 @@ +. + * + */ + +namespace Friendica\Database\Definition; + +use Exception; +use Friendica\Core\Hook; + +/** + * Stores the whole database definition + */ +class DbaDefinition +{ + /** @var string The relative path of the db structure config file */ + const DBSTRUCTURE_RELATIVE_PATH = '/static/dbstructure.config.php'; + + /** @var array The complete DB definition as an array */ + protected $definition; + + /** @var string */ + protected $configFile; + + /** + * @param string $basePath The basepath of the dbstructure file (loads relative path in case of null) + * + * @throws Exception in case the config file isn't available/readable + */ + public function __construct(string $basePath) + { + $this->configFile = $basePath . static::DBSTRUCTURE_RELATIVE_PATH; + + if (!is_readable($this->configFile)) { + throw new Exception('Missing database structure config file static/dbstructure.config.php at basePath=' . $basePath); + } + } + + /** + * @return array Returns the whole Definition as an array + */ + public function getAll(): array + { + return $this->definition; + } + + /** + * Get field data for the given table + * + * @param string $table Tavle to load field definitions for + * @param array $data data fields + * @return array fields for the given + */ + public function getFieldsForTable(string $table, array $data = []): array + { + $definition = $this->definition; + if (empty($definition[$table])) { + return []; + } + + $fieldNames = array_keys($definition[$table]['fields']); + + $fields = []; + + // Assign all field that are present in the table + foreach ($fieldNames as $field) { + if (isset($data[$field])) { + // Limit the length of varchar, varbinary, char and binrary fields + if (is_string($data[$field]) && preg_match("/char\((\d*)\)/", $definition[$table]['fields'][$field]['type'], $result)) { + $data[$field] = mb_substr($data[$field], 0, $result[1]); + } elseif (is_string($data[$field]) && preg_match("/binary\((\d*)\)/", $definition[$table]['fields'][$field]['type'], $result)) { + $data[$field] = substr($data[$field], 0, $result[1]); + } + $fields[$field] = $data[$field]; + } + } + + return $fields; + } + + /** + * Loads the database structure definition from the static/dbstructure.config.php file. + * On first pass, defines DB_UPDATE_VERSION constant. + * + * @param bool $withAddonStructure Whether to tack on addons additional tables + * + * @throws Exception in case the definition cannot be found + * + * @see static/dbstructure.config.php + * + * @return self The current instance + */ + public function load(bool $withAddonStructure = false): self + { + $definition = require $this->configFile; + + if (!$definition) { + throw new Exception('Corrupted database structure config file static/dbstructure.config.php'); + } + + if ($withAddonStructure) { + Hook::callAll('dbstructure_definition', $definition); + } + + $this->definition = $definition; + + return $this; + } +} diff --git a/src/Database/Definition/ViewDefinition.php b/src/Database/Definition/ViewDefinition.php new file mode 100644 index 0000000000..1971f0cce2 --- /dev/null +++ b/src/Database/Definition/ViewDefinition.php @@ -0,0 +1,91 @@ +. + * + */ + +namespace Friendica\Database\Definition; + +use Exception; +use Friendica\Core\Hook; + +/** + * Stores the whole View definitions + */ +class ViewDefinition +{ + /** @var string the relative path to the database view config file */ + const DBSTRUCTURE_RELATIVE_PATH = '/static/dbview.config.php'; + + /** @var array The complete view definition as an array */ + protected $definition; + + /** @var string */ + protected $configFile; + + /** + * @param string $basePath The basepath of the dbview file (loads relative path in case of null) + * + * @throws Exception in case the config file isn't available/readable + */ + public function __construct(string $basePath) + { + $this->configFile = $basePath . static::DBSTRUCTURE_RELATIVE_PATH; + + if (!is_readable($this->configFile)) { + throw new Exception('Missing database structure config file static/dbview.config.php at basePath=' . $basePath); + } + } + + /** + * @return array Returns the whole Definition as an array + */ + public function getAll(): array + { + return $this->definition; + } + + /** + * Loads the database structure definition from the static/dbview.config.php file. + * On first pass, defines DB_UPDATE_VERSION constant. + * + * @param bool $withAddonStructure Whether to tack on addons additional tables + * + * @throws Exception in case the definition cannot be found + * + * @see static/dbview.config.php + * + * @return self The current instance + */ + public function load(bool $withAddonStructure = false): self + { + $definition = require $this->configFile; + + if (!$definition) { + throw new Exception('Corrupted database structure config file static/dbstructure.config.php'); + } + + if ($withAddonStructure) { + Hook::callAll('dbview_definition', $definition); + } + + $this->definition = $definition; + + return $this; + } +} diff --git a/src/Database/View.php b/src/Database/View.php index 18db875ea8..d64e544cc0 100644 --- a/src/Database/View.php +++ b/src/Database/View.php @@ -21,60 +21,11 @@ namespace Friendica\Database; -use Exception; -use Friendica\Core\Hook; use Friendica\DI; +use Friendica\Util\Writer\ViewDefinitionSqlWriter; class View { - /** - * view definition loaded from static/dbview.config.php - * - * @var array - */ - private static $definition = []; - - /** - * Loads the database structure definition from the static/dbview.config.php file. - * On first pass, defines DB_UPDATE_VERSION constant. - * - * @see static/dbview.config.php - * @param string $basePath The base path of this application - * @param boolean $with_addons_structure Whether to tack on addons additional tables - * @return array - * @throws Exception - */ - public static function definition(string $basePath = '', bool $with_addons_structure = true): array - { - if (!self::$definition) { - if (empty($basePath)) { - $basePath = DI::app()->getBasePath(); - } - - $filename = $basePath . '/static/dbview.config.php'; - - if (!is_readable($filename)) { - throw new Exception('Missing database view config file static/dbview.config.php'); - } - - $definition = require $filename; - - if (!$definition) { - throw new Exception('Corrupted database view config file static/dbview.config.php'); - } - - self::$definition = $definition; - } else { - $definition = self::$definition; - } - - if ($with_addons_structure) { - Hook::callAll('dbview_definition', $definition); - } - - return $definition; - } - /** * Creates a view * @@ -85,94 +36,29 @@ class View public static function create(bool $verbose, bool $action) { // Delete previously used views that aren't used anymore - foreach(['post-view', 'post-thread-view'] as $view) { + foreach (['post-view', 'post-thread-view'] as $view) { if (self::isView($view)) { $sql = sprintf("DROP VIEW IF EXISTS `%s`", DBA::escape($view)); if (!empty($sql) && $verbose) { echo $sql . ";\n"; } - + if (!empty($sql) && $action) { DBA::e($sql); } } } - $definition = self::definition(); + $definition = DI::viewDefinition()->getAll(); foreach ($definition as $name => $structure) { - self::createView($name, $structure, $verbose, $action); - } - } - - /** - * Prints view structure - * - * @param string $basePath Base path - * @return void - */ - public static function printStructure(string $basePath) - { - $database = self::definition($basePath, false); - - foreach ($database as $name => $structure) { - echo "--\n"; - echo "-- VIEW $name\n"; - echo "--\n"; - self::createView($name, $structure, true, false); - - echo "\n"; - } - } - - /** - * Creates view - * - * @param string $name Name of view - * @param array $structure Structure of view - * @param bool $verbose Whether to show SQL statements - * @param bool $action Whether to execute SQL statements - * @return bool Whether execution went fine - */ - private static function createView(string $name, array $structure, bool $verbose, bool $action): bool - { - $r = true; - - $sql_rows = []; - foreach ($structure['fields'] as $fieldname => $origin) { - if (is_string($origin)) { - $sql_rows[] = $origin . " AS `" . DBA::escape($fieldname) . "`"; - } elseif (is_array($origin) && (sizeof($origin) == 2)) { - $sql_rows[] = "`" . DBA::escape($origin[0]) . "`.`" . DBA::escape($origin[1]) . "` AS `" . DBA::escape($fieldname) . "`"; + if (self::isView($name)) { + DBA::e(sprintf("DROP VIEW IF EXISTS `%s`", DBA::escape($name))); + } elseif (self::isTable($name)) { + DBA::e(sprintf("DROP TABLE IF EXISTS `%s`", DBA::escape($name))); } + DBA::e(ViewDefinitionSqlWriter::createView($name, $structure)); } - - if (self::isView($name)) { - $sql = sprintf("DROP VIEW IF EXISTS `%s`", DBA::escape($name)); - } elseif (self::isTable($name)) { - $sql = sprintf("DROP TABLE IF EXISTS `%s`", DBA::escape($name)); - } - - if (!empty($sql) && $verbose) { - echo $sql . ";\n"; - } - - if (!empty($sql) && $action) { - DBA::e($sql); - } - - $sql = sprintf("CREATE VIEW `%s` AS SELECT \n\t", DBA::escape($name)) . - implode(",\n\t", $sql_rows) . "\n\t" . $structure['query']; - - if ($verbose) { - echo $sql . ";\n"; - } - - if ($action) { - $r = DBA::e($sql); - } - - return $r; } /** diff --git a/src/Model/APContact.php b/src/Model/APContact.php index 9269ee7903..bc51f37aa0 100644 --- a/src/Model/APContact.php +++ b/src/Model/APContact.php @@ -466,7 +466,7 @@ class APContact } // Limit the length on incoming fields - $apcontact = DBStructure::getFieldsForTable('apcontact', $apcontact); + $apcontact = DI::dbaDefinition()->getFieldsForTable('apcontact', $apcontact); if (DBA::exists('apcontact', ['url' => $apcontact['url']])) { DBA::update('apcontact', $apcontact, ['url' => $apcontact['url']]); diff --git a/src/Model/Attach.php b/src/Model/Attach.php index b2cc229c3d..55ab81a8ae 100644 --- a/src/Model/Attach.php +++ b/src/Model/Attach.php @@ -23,7 +23,6 @@ namespace Friendica\Model; use Friendica\Core\System; use Friendica\Database\DBA; -use Friendica\Database\DBStructure; use Friendica\DI; use Friendica\Core\Storage\Exception\InvalidClassStorageException; use Friendica\Core\Storage\Exception\ReferenceStorageException; @@ -46,7 +45,7 @@ class Attach */ private static function getFields(): array { - $allfields = DBStructure::definition(DI::app()->getBasePath(), false); + $allfields = DI::dbaDefinition()->getAll(); $fields = array_keys($allfields['attach']['fields']); array_splice($fields, array_search('data', $fields), 1); return $fields; diff --git a/src/Model/Contact/User.php b/src/Model/Contact/User.php index c656d1f003..bc500d5230 100644 --- a/src/Model/Contact/User.php +++ b/src/Model/Contact/User.php @@ -28,6 +28,7 @@ use Friendica\Core\System; use Friendica\Database\Database; use Friendica\Database\DBA; use Friendica\Database\DBStructure; +use Friendica\DI; use Friendica\Model\Contact; use Friendica\Model\ItemURI; use PDOException; @@ -129,7 +130,7 @@ class User $fields['rel'] = Contact::SELF; } - return DBStructure::getFieldsForTable('user-contact', $fields); + return DI::dbaDefinition()->getFieldsForTable('user-contact', $fields); } /** diff --git a/src/Model/GServer.php b/src/Model/GServer.php index aa7a2bd647..e6f81a53f1 100644 --- a/src/Model/GServer.php +++ b/src/Model/GServer.php @@ -2075,7 +2075,7 @@ class GServer */ public static function update(array $fields, array $condition): bool { - $fields = DBStructure::getFieldsForTable('gserver', $fields); + $fields = DI::dbaDefinition()->getFieldsForTable('gserver', $fields); return DBA::update('gserver', $fields, $condition); } diff --git a/src/Model/Photo.php b/src/Model/Photo.php index 9be1efef3a..096915523c 100644 --- a/src/Model/Photo.php +++ b/src/Model/Photo.php @@ -250,7 +250,7 @@ class Photo */ private static function getFields(): array { - $allfields = DBStructure::definition(DI::app()->getBasePath(), false); + $allfields = DI::dbaDefinition()->getAll(); $fields = array_keys($allfields['photo']['fields']); array_splice($fields, array_search('data', $fields), 1); return $fields; diff --git a/src/Model/Post.php b/src/Model/Post.php index 56898e3b29..a83a0920a9 100644 --- a/src/Model/Post.php +++ b/src/Model/Post.php @@ -27,6 +27,7 @@ use Friendica\Core\System; use Friendica\Database\Database; use Friendica\Database\DBA; use Friendica\Database\DBStructure; +use Friendica\DI; use Friendica\Protocol\Activity; class Post @@ -45,7 +46,7 @@ class Post throw new BadMethodCallException('Empty URI_id'); } - $fields = DBStructure::getFieldsForTable('post', $data); + $fields = DI::dbaDefinition()->getFieldsForTable('post', $data); // Additionally assign the key fields $fields['uri-id'] = $uri_id; @@ -524,7 +525,7 @@ class Post // To ensure the data integrity we do it in an transaction DBA::transaction(); - $update_fields = DBStructure::getFieldsForTable('post-user', $fields); + $update_fields = DI::dbaDefinition()->getFieldsForTable('post-user', $fields); if (!empty($update_fields)) { $affected_count = 0; $posts = DBA::select('post-user-view', ['post-user-id'], $condition); @@ -541,7 +542,7 @@ class Post $affected = $affected_count; } - $update_fields = DBStructure::getFieldsForTable('post-content', $fields); + $update_fields = DI::dbaDefinition()->getFieldsForTable('post-content', $fields); if (!empty($update_fields)) { $affected_count = 0; $posts = DBA::select('post-user-view', ['uri-id'], $condition, ['group_by' => ['uri-id']]); @@ -558,7 +559,7 @@ class Post $affected = max($affected, $affected_count); } - $update_fields = DBStructure::getFieldsForTable('post', $fields); + $update_fields = DI::dbaDefinition()->getFieldsForTable('post', $fields); if (!empty($update_fields)) { $affected_count = 0; $posts = DBA::select('post-user-view', ['uri-id'], $condition, ['group_by' => ['uri-id']]); @@ -592,7 +593,7 @@ class Post $affected = max($affected, $affected_count); } - $update_fields = DBStructure::getFieldsForTable('post-thread', $fields); + $update_fields = DI::dbaDefinition()->getFieldsForTable('post-thread', $fields); if (!empty($update_fields)) { $affected_count = 0; $posts = DBA::select('post-user-view', ['uri-id'], $thread_condition, ['group_by' => ['uri-id']]); @@ -609,7 +610,7 @@ class Post $affected = max($affected, $affected_count); } - $update_fields = DBStructure::getFieldsForTable('post-thread-user', $fields); + $update_fields = DI::dbaDefinition()->getFieldsForTable('post-thread-user', $fields); if (!empty($update_fields)) { $affected_count = 0; $posts = DBA::select('post-user-view', ['post-user-id'], $thread_condition); diff --git a/src/Model/Post/Content.php b/src/Model/Post/Content.php index 3a5c32810c..e7723f1d16 100644 --- a/src/Model/Post/Content.php +++ b/src/Model/Post/Content.php @@ -26,6 +26,7 @@ use Friendica\Core\Protocol; use Friendica\Database\Database; use Friendica\Database\DBA; use Friendica\Database\DBStructure; +use Friendica\DI; use Friendica\Model\Post; class Content @@ -44,7 +45,7 @@ class Content throw new BadMethodCallException('Empty URI_id'); } - $fields = DBStructure::getFieldsForTable('post-content', $data); + $fields = DI::dbaDefinition()->getFieldsForTable('post-content', $data); // Additionally assign the key fields $fields['uri-id'] = $uri_id; @@ -67,7 +68,7 @@ class Content throw new BadMethodCallException('Empty URI_id'); } - $fields = DBStructure::getFieldsForTable('post-content', $data); + $fields = DI::dbaDefinition()->getFieldsForTable('post-content', $data); // Remove the key fields unset($fields['uri-id']); diff --git a/src/Model/Post/History.php b/src/Model/Post/History.php index 24ee7c1e7d..29e0cf8c13 100644 --- a/src/Model/Post/History.php +++ b/src/Model/Post/History.php @@ -24,7 +24,7 @@ namespace Friendica\Model\Post; use Friendica\Core\Logger; use Friendica\Database\DBA; use Friendica\Database\Database; -use Friendica\Database\DBStructure; +use Friendica\DI; use Friendica\Model\Post; class History @@ -37,7 +37,7 @@ class History */ public static function add(int $uri_id, array $item) { - $allfields = DBStructure::definition('', false); + $allfields = DI::dbaDefinition()->getAll(); $fields = array_keys($allfields['post-history']['fields']); $post = Post::selectFirstPost($fields, ['uri-id' => $uri_id]); @@ -52,7 +52,7 @@ class History } $update = false; - $changed = DBStructure::getFieldsForTable('post-history', $item); + $changed = DI::dbaDefinition()->getFieldsForTable('post-history', $item); unset($changed['uri-id']); unset($changed['edited']); foreach ($changed as $field => $content) { diff --git a/src/Model/Post/Question.php b/src/Model/Post/Question.php index db0d755b7f..d73c54a582 100644 --- a/src/Model/Post/Question.php +++ b/src/Model/Post/Question.php @@ -24,6 +24,7 @@ namespace Friendica\Model\Post; use BadMethodCallException; use Friendica\Database\DBA; use Friendica\Database\DBStructure; +use Friendica\DI; class Question { @@ -42,7 +43,7 @@ class Question throw new BadMethodCallException('Empty URI_id'); } - $fields = DBStructure::getFieldsForTable('post-question', $data); + $fields = DI::dbaDefinition()->getFieldsForTable('post-question', $data); // Remove the key fields unset($fields['uri-id']); diff --git a/src/Model/Post/QuestionOption.php b/src/Model/Post/QuestionOption.php index 641c8f2ccf..03ac0633c7 100644 --- a/src/Model/Post/QuestionOption.php +++ b/src/Model/Post/QuestionOption.php @@ -24,6 +24,7 @@ namespace Friendica\Model\Post; use BadMethodCallException; use Friendica\Database\DBA; use Friendica\Database\DBStructure; +use Friendica\DI; class QuestionOption { @@ -43,7 +44,7 @@ class QuestionOption throw new BadMethodCallException('Empty URI_id'); } - $fields = DBStructure::getFieldsForTable('post-question-option', $data); + $fields = DI::dbaDefinition()->getFieldsForTable('post-question-option', $data); // Remove the key fields unset($fields['uri-id']); diff --git a/src/Model/Post/Thread.php b/src/Model/Post/Thread.php index ab7900ad82..c432c6d7f6 100644 --- a/src/Model/Post/Thread.php +++ b/src/Model/Post/Thread.php @@ -25,6 +25,7 @@ use \BadMethodCallException; use Friendica\Database\Database; use Friendica\Database\DBA; use Friendica\Database\DBStructure; +use Friendica\DI; class Thread { @@ -42,7 +43,7 @@ class Thread throw new BadMethodCallException('Empty URI_id'); } - $fields = DBStructure::getFieldsForTable('post-thread', $data); + $fields = DI::dbaDefinition()->getFieldsForTable('post-thread', $data); // Additionally assign the key fields $fields['uri-id'] = $uri_id; @@ -65,7 +66,7 @@ class Thread throw new BadMethodCallException('Empty URI_id'); } - $fields = DBStructure::getFieldsForTable('post-thread', $data); + $fields = DI::dbaDefinition()->getFieldsForTable('post-thread', $data); // Remove the key fields unset($fields['uri-id']); diff --git a/src/Model/Post/ThreadUser.php b/src/Model/Post/ThreadUser.php index 0de26abc58..2e850de05d 100644 --- a/src/Model/Post/ThreadUser.php +++ b/src/Model/Post/ThreadUser.php @@ -25,6 +25,7 @@ use \BadMethodCallException; use Friendica\Database\Database; use Friendica\Database\DBA; use Friendica\Database\DBStructure; +use Friendica\DI; class ThreadUser { @@ -43,7 +44,7 @@ class ThreadUser throw new BadMethodCallException('Empty URI_id'); } - $fields = DBStructure::getFieldsForTable('post-thread-user', $data); + $fields = DI::dbaDefinition()->getFieldsForTable('post-thread-user', $data); // Additionally assign the key fields $fields['uri-id'] = $uri_id; @@ -68,7 +69,7 @@ class ThreadUser throw new BadMethodCallException('Empty URI_id'); } - $fields = DBStructure::getFieldsForTable('post-thread-user', $data); + $fields = DI::dbaDefinition()->getFieldsForTable('post-thread-user', $data); // Remove the key fields unset($fields['uri-id']); diff --git a/src/Model/Post/User.php b/src/Model/Post/User.php index 0da3062a0b..efbcaf635b 100644 --- a/src/Model/Post/User.php +++ b/src/Model/Post/User.php @@ -25,6 +25,7 @@ use Friendica\Database\DBA; use \BadMethodCallException; use Friendica\Database\Database; use Friendica\Database\DBStructure; +use Friendica\DI; class User { @@ -47,7 +48,7 @@ class User return false; } - $fields = DBStructure::getFieldsForTable('post-user', $data); + $fields = DI::dbaDefinition()->getFieldsForTable('post-user', $data); // Additionally assign the key fields $fields['uri-id'] = $uri_id; @@ -81,7 +82,7 @@ class User throw new BadMethodCallException('Empty URI_id'); } - $fields = DBStructure::getFieldsForTable('post-user', $data); + $fields = DI::dbaDefinition()->getFieldsForTable('post-user', $data); // Remove the key fields unset($fields['uri-id']); diff --git a/src/Model/Post/UserNotification.php b/src/Model/Post/UserNotification.php index 005dd7084a..127857db4b 100644 --- a/src/Model/Post/UserNotification.php +++ b/src/Model/Post/UserNotification.php @@ -67,7 +67,7 @@ class UserNotification throw new BadMethodCallException('Empty URI_id'); } - $fields = DBStructure::getFieldsForTable('post-user-notification', $data); + $fields = DI::dbaDefinition()->getFieldsForTable('post-user-notification', $data); $fields['uri-id'] = $uri_id; $fields['uid'] = $uid; @@ -91,7 +91,7 @@ class UserNotification throw new BadMethodCallException('Empty URI_id'); } - $fields = DBStructure::getFieldsForTable('post-user-notification', $data); + $fields = DI::dbaDefinition()->getFieldsForTable('post-user-notification', $data); // Remove the key fields unset($fields['uri-id']); diff --git a/src/Module/Install.php b/src/Module/Install.php index 59d6581056..843cee9f18 100644 --- a/src/Module/Install.php +++ b/src/Module/Install.php @@ -170,7 +170,7 @@ class Install extends BaseModule return; } - $this->installer->installDatabase($configCache->get('system', 'basepath')); + $this->installer->installDatabase(); // install allowed themes to register theme hooks // this is same as "Reload active theme" in /admin/themes diff --git a/src/Module/Settings/UserExport.php b/src/Module/Settings/UserExport.php index 2d29f4d647..4dfbdb99eb 100644 --- a/src/Module/Settings/UserExport.php +++ b/src/Module/Settings/UserExport.php @@ -124,7 +124,7 @@ class UserExport extends BaseSettings */ private static function exportMultiRow(string $query) { - $dbStructure = DBStructure::definition(DI::app()->getBasePath(), false); + $dbStructure = DI::dbaDefinition()->getAll(); preg_match("/\s+from\s+`?([a-z\d_]+)`?/i", $query, $match); $table = $match[1]; @@ -156,7 +156,7 @@ class UserExport extends BaseSettings */ private static function exportRow(string $query) { - $dbStructure = DBStructure::definition(DI::app()->getBasePath(), false); + $dbStructure = DI::dbaDefinition()->getAll(); preg_match("/\s+from\s+`?([a-z\d_]+)`?/i", $query, $match); $table = $match[1]; diff --git a/src/Util/Writer/DbaDefinitionSqlWriter.php b/src/Util/Writer/DbaDefinitionSqlWriter.php new file mode 100644 index 0000000000..556957a85d --- /dev/null +++ b/src/Util/Writer/DbaDefinitionSqlWriter.php @@ -0,0 +1,301 @@ +. + * + */ + +namespace Friendica\Util\Writer; + +use Exception; +use Friendica\Database\Definition\DbaDefinition; + +/** + * SQL writer utility for the database definition + */ +class DbaDefinitionSqlWriter +{ + /** + * Creates a complete SQL definition bases on a give DBA Definition class + * + * @param DbaDefinition $definition The DBA definition class + * + * @return string The SQL definition as a string + * + * @throws Exception in case of parameter failures + */ + public static function create(DbaDefinition $definition): string + { + $sqlString = "-- ------------------------------------------\n"; + $sqlString .= "-- " . FRIENDICA_PLATFORM . " " . FRIENDICA_VERSION . " (" . FRIENDICA_CODENAME . ")\n"; + $sqlString .= "-- DB_UPDATE_VERSION " . DB_UPDATE_VERSION . "\n"; + $sqlString .= "-- ------------------------------------------\n\n\n"; + + foreach ($definition->getAll() as $tableName => $tableStructure) { + $sqlString .= "--\n"; + $sqlString .= "-- TABLE $tableName\n"; + $sqlString .= "--\n"; + $sqlString .= static::createTable($tableName, $tableStructure); + } + + return $sqlString; + } + + /** + * Creates the SQL definition of one table + * + * @param string $tableName The table name + * @param array $tableStructure The table structure + * + * @return string The SQL definition + * + * @throws Exception in cases of structure failures + */ + public static function createTable(string $tableName, array $tableStructure): string + { + $engine = ''; + $comment = ''; + $sql_rows = []; + $primary_keys = []; + $foreign_keys = []; + + foreach ($tableStructure['fields'] as $fieldName => $field) { + $sql_rows[] = '`' . static::escape($fieldName) . '` ' . self::FieldCommand($field); + if (!empty($field['primary'])) { + $primary_keys[] = $fieldName; + } + if (!empty($field['foreign'])) { + $foreign_keys[$fieldName] = $field; + } + } + + if (!empty($tableStructure['indexes'])) { + foreach ($tableStructure['indexes'] as $indexName => $fieldNames) { + $sql_index = self::createIndex($indexName, $fieldNames, ''); + if (!is_null($sql_index)) { + $sql_rows[] = $sql_index; + } + } + } + + foreach ($foreign_keys as $fieldName => $parameters) { + $sql_rows[] = self::foreignCommand($fieldName, $parameters); + } + + if (isset($tableStructure['engine'])) { + $engine = ' ENGINE=' . $tableStructure['engine']; + } + + if (isset($tableStructure['comment'])) { + $comment = " COMMENT='" . static::escape($tableStructure['comment']) . "'"; + } + + $sql = implode(",\n\t", $sql_rows); + + $sql = sprintf("CREATE TABLE IF NOT EXISTS `%s` (\n\t", static::escape($tableName)) . $sql . + "\n)" . $engine . " DEFAULT COLLATE utf8mb4_general_ci" . $comment; + return $sql . ";\n\n"; + } + + /** + * Standard escaping for SQL definitions + * + * @param string $sqlString the SQL string to escape + * + * @return string escaped SQL string + */ + public static function escape(string $sqlString): string + { + return str_replace("'", "\\'", $sqlString); + } + + /** + * Creates the SQL definition to add a foreign key + * + * @param string $keyName The foreign key name + * @param array $parameters The given parameters of the foreign key + * + * @return string The SQL definition + */ + public static function addForeignKey(string $keyName, array $parameters): string + { + return sprintf("ADD %s", static::foreignCommand($keyName, $parameters)); + } + + /** + * Creates the SQL definition to drop a foreign key + * + * @param string $keyName The foreign key name + * + * @return string The SQL definition + */ + public static function dropForeignKey(string $keyName): string + { + return sprintf("DROP FOREIGN KEY `%s`", $keyName); + } + + /** + * Creates the SQL definition to drop an index + * + * @param string $indexName The index name + * + * @return string The SQL definition + */ + public static function dropIndex(string $indexName): string + { + return sprintf("DROP INDEX `%s`", static::escape($indexName)); + } + + /** + * Creates the SQL definition to add a table field + * + * @param string $fieldName The table field name + * @param array $parameters The parameters of the table field + * + * @return string The SQL definition + */ + public static function addTableField(string $fieldName, array $parameters): string + { + return sprintf("ADD `%s` %s", static::escape($fieldName), static::FieldCommand($parameters)); + } + + /** + * Creates the SQL definition to modify a table field + * + * @param string $fieldName The table field name + * @param array $parameters The paramters to modify + * + * @return string The SQL definition + */ + public static function modifyTableField(string $fieldName, array $parameters): string + { + return sprintf("MODIFY `%s` %s", static::escape($fieldName), self::FieldCommand($parameters, false)); + } + + /** + * Returns SQL statement for field + * + * @param array $parameters Parameters for SQL statement + * @param boolean $create Whether to include PRIMARY KEY statement (unused) + * @return string SQL statement part + */ + public static function FieldCommand(array $parameters, bool $create = true): string + { + $fieldstruct = $parameters['type']; + + if (isset($parameters['Collation'])) { + $fieldstruct .= ' COLLATE ' . $parameters['Collation']; + } + + 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 '" . static::escape($parameters['comment']) . "'"; + } + + /*if (($parameters['primary'] != '') && $create) + $fieldstruct .= ' PRIMARY KEY';*/ + + return $fieldstruct; + } + + /** + * Creates the SQL definition to create an index + * + * @param string $indexName The index name + * @param array $fieldNames The field names of this index + * @param string $method The method to create the index (default is ADD) + * + * @return string The SQL definition + * @throws Exception in cases the paramter contains invalid content + */ + public static function createIndex(string $indexName, array $fieldNames, string $method = 'ADD'): string + { + $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 .= "`" . static::escape($matches[1]) . "`(" . intval($matches[2]) . ")"; + } else { + $names .= "`" . static::escape($fieldName) . "`"; + } + } + + if ($indexName == 'PRIMARY') { + return sprintf("%s PRIMARY KEY(%s)", $method, $names); + } + + + return sprintf("%s INDEX `%s` (%s)", $method, static::escape($indexName), $names); + } + + /** + * Creates the SQL definition for foreign keys + * + * @param string $foreignKeyName The foreign key name + * @param array $parameters The parameters of the foreign key + * + * @return string The SQL definition + */ + public static function foreignCommand(string $foreignKeyName, array $parameters): string + { + $foreign_table = array_keys($parameters['foreign'])[0]; + $foreign_field = array_values($parameters['foreign'])[0]; + + $sql = "FOREIGN KEY (`" . $foreignKeyName . "`) 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; + } +} diff --git a/src/Util/Writer/DocWriter.php b/src/Util/Writer/DocWriter.php new file mode 100644 index 0000000000..a63f6a6493 --- /dev/null +++ b/src/Util/Writer/DocWriter.php @@ -0,0 +1,145 @@ +. + * + */ + +namespace Friendica\Util\Writer; + +use Friendica\Core\Renderer; +use Friendica\Database\Definition\DbaDefinition; +use Friendica\Network\HTTPException\ServiceUnavailableException; + +/** + * Utility class to write content into the '/doc' directory + */ +class DocWriter +{ + /** + * Creates all database definitions as Markdown fields and create the mkdoc config file. + * + * @param DbaDefinition $definition The Database definition class + * @param string $basePath The basepath of Friendica + * + * @return void + * @throws ServiceUnavailableException in really unexpected cases! + */ + public static function writeDbDefinition(DbaDefinition $definition, string $basePath) + { + $tables = []; + foreach ($definition->getAll() as $name => $definition) { + $indexes = [ + [ + 'name' => 'Name', + 'fields' => 'Fields', + ], + [ + 'name' => '-', + 'fields' => '-', + ] + ]; + + $lengths = ['name' => 4, 'fields' => 6]; + foreach ($definition['indexes'] as $key => $value) { + $fieldlist = implode(', ', $value); + $indexes[] = ['name' => $key, 'fields' => $fieldlist]; + $lengths['name'] = max($lengths['name'], strlen($key)); + $lengths['fields'] = max($lengths['fields'], strlen($fieldlist)); + } + + array_walk_recursive($indexes, function (&$value, $key) use ($lengths) { + $value = str_pad($value, $lengths[$key], $value === '-' ? '-' : ' '); + }); + + $foreign = []; + $fields = [ + [ + 'name' => 'Field', + 'comment' => 'Description', + 'type' => 'Type', + 'null' => 'Null', + 'primary' => 'Key', + 'default' => 'Default', + 'extra' => 'Extra', + ], + [ + 'name' => '-', + 'comment' => '-', + 'type' => '-', + 'null' => '-', + 'primary' => '-', + 'default' => '-', + 'extra' => '-', + ] + ]; + $lengths = [ + 'name' => 5, + 'comment' => 11, + 'type' => 4, + 'null' => 4, + 'primary' => 3, + 'default' => 7, + 'extra' => 5, + ]; + foreach ($definition['fields'] as $key => $value) { + $field = []; + $field['name'] = $key; + $field['comment'] = $value['comment'] ?? ''; + $field['type'] = $value['type']; + $field['null'] = ($value['not null'] ?? false) ? 'NO' : 'YES'; + $field['primary'] = ($value['primary'] ?? false) ? 'PRI' : ''; + $field['default'] = $value['default'] ?? 'NULL'; + $field['extra'] = $value['extra'] ?? ''; + + foreach ($field as $fieldName => $fieldvalue) { + $lengths[$fieldName] = max($lengths[$fieldName] ?? 0, strlen($fieldvalue)); + } + $fields[] = $field; + + if (!empty($value['foreign'])) { + $foreign[] = [ + 'field' => $key, + 'targettable' => array_keys($value['foreign'])[0], + 'targetfield' => array_values($value['foreign'])[0] + ]; + } + } + + array_walk_recursive($fields, function (&$value, $key) use ($lengths) { + $value = str_pad($value, $lengths[$key], $value === '-' ? '-' : ' '); + }); + + $tables[] = ['name' => $name, 'comment' => $definition['comment']]; + $content = Renderer::replaceMacros(Renderer::getMarkupTemplate('structure.tpl'), [ + '$name' => $name, + '$comment' => $definition['comment'], + '$fields' => $fields, + '$indexes' => $indexes, + '$foreign' => $foreign, + ]); + $filename = $basePath . '/doc/database/db_' . $name . '.md'; + file_put_contents($filename, $content); + } + asort($tables); + $content = Renderer::replaceMacros(Renderer::getMarkupTemplate('tables.tpl'), [ + '$tables' => $tables, + ]); + $filename = $basePath . '/doc/database.md'; + file_put_contents($filename, $content); + } +} diff --git a/src/Util/Writer/ViewDefinitionSqlWriter.php b/src/Util/Writer/ViewDefinitionSqlWriter.php new file mode 100644 index 0000000000..5e6715b6f4 --- /dev/null +++ b/src/Util/Writer/ViewDefinitionSqlWriter.php @@ -0,0 +1,66 @@ +. + * + */ + +namespace Friendica\Util\Writer; + +use Friendica\Database\Definition\ViewDefinition; + +class ViewDefinitionSqlWriter +{ + public static function create(ViewDefinition $definition): string + { + $sqlString = ''; + + foreach ($definition->getAll() as $viewName => $viewStructure) { + $sqlString .= "--\n"; + $sqlString .= "-- VIEW $viewName\n"; + $sqlString .= "--\n"; + $sqlString .= static::dropView($viewName); + $sqlString .= static::createView($viewName, $viewStructure); + } + + return $sqlString; + } + + public static function dropView(string $viewName): string + { + return sprintf("DROP VIEW IF EXISTS `%s`", static::escape($viewName)) . ";\n"; + } + + public static function createView(string $viewName, array $viewStructure): string + { + $sql_rows = []; + foreach ($viewStructure['fields'] as $fieldname => $origin) { + if (is_string($origin)) { + $sql_rows[] = $origin . " AS `" . static::escape($fieldname) . "`"; + } elseif (is_array($origin) && (sizeof($origin) == 2)) { + $sql_rows[] = "`" . static::escape($origin[0]) . "`.`" . static::escape($origin[1]) . "` AS `" . static::escape($fieldname) . "`"; + } + } + return sprintf("CREATE VIEW `%s` AS SELECT \n\t", static::escape($viewName)) . + implode(",\n\t", $sql_rows) . "\n\t" . $viewStructure['query'] . ";\n\n"; + } + + public static function escape(string $sqlString): string + { + return str_replace("'", "\\'", $sqlString); + } +} diff --git a/src/Worker/ExpirePosts.php b/src/Worker/ExpirePosts.php index 52d792ea62..704e8bd944 100644 --- a/src/Worker/ExpirePosts.php +++ b/src/Worker/ExpirePosts.php @@ -122,7 +122,7 @@ class ExpirePosts $rows = 0; $userposts = DBA::select('post-user', [], ["`uri-id` not in (select `uri-id` from `post`)"]); while ($fields = DBA::fetch($userposts)) { - $post_fields = DBStructure::getFieldsForTable('post', $fields); + $post_fields = DI::dbaDefinition()->getFieldsForTable('post', $fields); DBA::insert('post', $post_fields, Database::INSERT_IGNORE); $rows++; } @@ -136,7 +136,7 @@ class ExpirePosts $rows = 0; $userposts = DBA::select('post-user', [], ["`gravity` = ? AND `uri-id` not in (select `uri-id` from `post-thread`)", GRAVITY_PARENT]); while ($fields = DBA::fetch($userposts)) { - $post_fields = DBStructure::getFieldsForTable('post-thread', $fields); + $post_fields = DI::dbaDefinition()->getFieldsForTable('post-thread', $fields); $post_fields['commented'] = $post_fields['changed'] = $post_fields['created']; DBA::insert('post-thread', $post_fields, Database::INSERT_IGNORE); $rows++; @@ -151,7 +151,7 @@ class ExpirePosts $rows = 0; $userposts = DBA::select('post-user', [], ["`gravity` = ? AND `id` not in (select `post-user-id` from `post-thread-user`)", GRAVITY_PARENT]); while ($fields = DBA::fetch($userposts)) { - $post_fields = DBStructure::getFieldsForTable('post-thread-user', $fields); + $post_fields = DI::dbaDefinition()->getFieldsForTable('post-thread-user', $fields); $post_fields['commented'] = $post_fields['changed'] = $post_fields['created']; DBA::insert('post-thread-user', $post_fields, Database::INSERT_IGNORE); $rows++; diff --git a/static/dependencies.config.php b/static/dependencies.config.php index 0fc3e0adc4..5aba529db2 100644 --- a/static/dependencies.config.php +++ b/static/dependencies.config.php @@ -43,6 +43,8 @@ use Friendica\Core\Lock; use Friendica\Core\Session\Capability\IHandleSessions; use Friendica\Core\Storage\Repository\StorageManager; use Friendica\Database\Database; +use Friendica\Database\Definition\DbaDefinition; +use Friendica\Database\Definition\ViewDefinition; use Friendica\Factory; use Friendica\Core\Storage\Capability\ICanWriteToStorage; use Friendica\Model\User\Cookie; @@ -106,6 +108,22 @@ return [ ['create', [], Dice::CHAIN_CALL], ] ], + DbaDefinition::class => [ + 'constructParams' => [ + [Dice::INSTANCE => '$basepath'], + ], + 'call' => [ + ['load', [false], Dice::CHAIN_CALL], + ], + ], + ViewDefinition::class => [ + 'constructParams' => [ + [Dice::INSTANCE => '$basepath'], + ], + 'call' => [ + ['load', [false], Dice::CHAIN_CALL], + ], + ], Database::class => [ 'constructParams' => [ [Dice::INSTANCE => \Psr\Log\NullLogger::class], -- 2.39.5