From 0d81a08e3cf52e820752f3011d5182b0eb7b5580 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Roland=20H=C3=A4der?= Date: Tue, 21 Jun 2022 12:09:51 +0200 Subject: [PATCH] WIP: Properly some fixes? Also why is DROP VIEW IF EXISTS is being killed? --- database.sql | 17 ----------------- src/Database/DBA.php | 10 +++++----- src/Database/DBStructure.php | 14 +++++++------- src/Database/Database.php | 21 ++++++++++----------- src/Database/View.php | 4 ++-- 5 files changed, 24 insertions(+), 42 deletions(-) diff --git a/database.sql b/database.sql index 10b18d4cae..62546556c8 100644 --- a/database.sql +++ b/database.sql @@ -1673,7 +1673,6 @@ CREATE TABLE IF NOT EXISTS `worker-ipc` ( -- -- VIEW application-view -- -DROP VIEW IF EXISTS `application-view`; CREATE VIEW `application-view` AS SELECT `application`.`id` AS `id`, `application-token`.`uid` AS `uid`, @@ -1696,7 +1695,6 @@ CREATE VIEW `application-view` AS SELECT -- -- VIEW post-user-view -- -DROP VIEW IF EXISTS `post-user-view`; CREATE VIEW `post-user-view` AS SELECT `post-user`.`id` AS `id`, `post-user`.`id` AS `post-user-id`, @@ -1870,7 +1868,6 @@ CREATE VIEW `post-user-view` AS SELECT -- -- VIEW post-thread-user-view -- -DROP VIEW IF EXISTS `post-thread-user-view`; CREATE VIEW `post-thread-user-view` AS SELECT `post-user`.`id` AS `id`, `post-user`.`id` AS `post-user-id`, @@ -2043,7 +2040,6 @@ CREATE VIEW `post-thread-user-view` AS SELECT -- -- VIEW post-view -- -DROP VIEW IF EXISTS `post-view`; CREATE VIEW `post-view` AS SELECT `item-uri`.`uri` AS `uri`, `post`.`uri-id` AS `uri-id`, @@ -2179,7 +2175,6 @@ CREATE VIEW `post-view` AS SELECT -- -- VIEW post-thread-view -- -DROP VIEW IF EXISTS `post-thread-view`; CREATE VIEW `post-thread-view` AS SELECT `item-uri`.`uri` AS `uri`, `post-thread`.`uri-id` AS `uri-id`, @@ -2315,7 +2310,6 @@ CREATE VIEW `post-thread-view` AS SELECT -- -- VIEW category-view -- -DROP VIEW IF EXISTS `category-view`; CREATE VIEW `category-view` AS SELECT `post-category`.`uri-id` AS `uri-id`, `post-category`.`uid` AS `uid`, @@ -2329,7 +2323,6 @@ CREATE VIEW `category-view` AS SELECT -- -- VIEW collection-view -- -DROP VIEW IF EXISTS `collection-view`; CREATE VIEW `collection-view` AS SELECT `post-collection`.`uri-id` AS `uri-id`, `post-collection`.`type` AS `type`, @@ -2350,7 +2343,6 @@ CREATE VIEW `collection-view` AS SELECT -- -- VIEW tag-view -- -DROP VIEW IF EXISTS `tag-view`; CREATE VIEW `tag-view` AS SELECT `post-tag`.`uri-id` AS `uri-id`, `post-tag`.`type` AS `type`, @@ -2366,7 +2358,6 @@ CREATE VIEW `tag-view` AS SELECT -- -- VIEW network-item-view -- -DROP VIEW IF EXISTS `network-item-view`; CREATE VIEW `network-item-view` AS SELECT `post-user`.`uri-id` AS `uri-id`, `parent-post`.`id` AS `parent`, @@ -2397,7 +2388,6 @@ CREATE VIEW `network-item-view` AS SELECT -- -- VIEW network-thread-view -- -DROP VIEW IF EXISTS `network-thread-view`; CREATE VIEW `network-thread-view` AS SELECT `post-thread-user`.`uri-id` AS `uri-id`, `parent-post`.`id` AS `parent`, @@ -2426,7 +2416,6 @@ CREATE VIEW `network-thread-view` AS SELECT -- -- VIEW owner-view -- -DROP VIEW IF EXISTS `owner-view`; CREATE VIEW `owner-view` AS SELECT `contact`.`id` AS `id`, `contact`.`uid` AS `uid`, @@ -2554,7 +2543,6 @@ CREATE VIEW `owner-view` AS SELECT -- -- VIEW account-view -- -DROP VIEW IF EXISTS `account-view`; CREATE VIEW `account-view` AS SELECT `contact`.`id` AS `id`, `contact`.`url` AS `url`, @@ -2640,7 +2628,6 @@ CREATE VIEW `account-view` AS SELECT -- -- VIEW account-user-view -- -DROP VIEW IF EXISTS `account-user-view`; CREATE VIEW `account-user-view` AS SELECT `ucontact`.`id` AS `id`, `contact`.`id` AS `pid`, @@ -2744,7 +2731,6 @@ CREATE VIEW `account-user-view` AS SELECT -- -- VIEW pending-view -- -DROP VIEW IF EXISTS `pending-view`; CREATE VIEW `pending-view` AS SELECT `register`.`id` AS `id`, `register`.`hash` AS `hash`, @@ -2766,7 +2752,6 @@ CREATE VIEW `pending-view` AS SELECT -- -- VIEW tag-search-view -- -DROP VIEW IF EXISTS `tag-search-view`; CREATE VIEW `tag-search-view` AS SELECT `post-tag`.`uri-id` AS `uri-id`, `post-user`.`uid` AS `uid`, @@ -2788,7 +2773,6 @@ CREATE VIEW `tag-search-view` AS SELECT -- -- VIEW workerqueue-view -- -DROP VIEW IF EXISTS `workerqueue-view`; CREATE VIEW `workerqueue-view` AS SELECT `process`.`pid` AS `pid`, `workerqueue`.`priority` AS `priority` @@ -2799,7 +2783,6 @@ CREATE VIEW `workerqueue-view` AS SELECT -- -- VIEW profile_field-view -- -DROP VIEW IF EXISTS `profile_field-view`; CREATE VIEW `profile_field-view` AS SELECT `profile_field`.`id` AS `id`, `profile_field`.`uid` AS `uid`, diff --git a/src/Database/DBA.php b/src/Database/DBA.php index cb50159687..38590eb563 100644 --- a/src/Database/DBA.php +++ b/src/Database/DBA.php @@ -447,16 +447,16 @@ class DBA /** * Select rows from a table and fills an array with the data * - * @param string|array $table Table name or array [schema => table] - * @param array $fields Array of selected fields, empty for all - * @param array $condition Array of fields for condition - * @param array $params Array of several parameters + * @param string $table Table name or array [schema => table] + * @param array $fields Array of selected fields, empty for all + * @param array $condition Array of fields for condition + * @param array $params Array of several parameters * * @return array Data array * @throws \Exception * @see self::select */ - public static function selectToArray($table, array $fields = [], array $condition = [], array $params = []) + public static function selectToArray(string $table, array $fields = [], array $condition = [], array $params = []) { return DI::dba()->selectToArray($table, $fields, $condition, $params); } diff --git a/src/Database/DBStructure.php b/src/Database/DBStructure.php index 8f68cc2b0a..0756ad099f 100644 --- a/src/Database/DBStructure.php +++ b/src/Database/DBStructure.php @@ -84,7 +84,7 @@ class DBStructure 'deliverq', 'dsprphotoq', 'ffinder', 'sign', 'spam', 'term', 'user-item', 'thread', 'item', 'challenge', 'auth_codes', 'tokens', 'clients', 'profile_check', 'host']; - $tables = DBA::selectToArray(['INFORMATION_SCHEMA' => 'TABLES'], ['TABLE_NAME'], + $tables = DBA::selectToArray('INFORMATION_SCHEMA.TABLES', ['TABLE_NAME'], ['TABLE_SCHEMA' => DBA::databaseName(), 'TABLE_TYPE' => 'BASE TABLE']); if (empty($tables)) { @@ -119,13 +119,13 @@ class DBStructure public static function convertToInnoDB() { $tables = DBA::selectToArray( - ['information_schema' => 'tables'], + 'information_schema.tables', ['table_name'], ['engine' => 'MyISAM', 'table_schema' => DBA::databaseName()] ); $tables = array_merge($tables, DBA::selectToArray( - ['information_schema' => 'tables'], + 'information_schema.tables', ['table_name'], ['engine' => 'InnoDB', 'ROW_FORMAT' => ['COMPACT', 'REDUNDANT'], 'table_schema' => DBA::databaseName()] )); @@ -851,18 +851,18 @@ class DBStructure // This query doesn't seem to be executable as a prepared statement $indexes = DBA::toArray(DBA::p("SHOW INDEX FROM " . DBA::quoteIdentifier($table))); - $fields = DBA::selectToArray(['INFORMATION_SCHEMA' => 'COLUMNS'], + $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]); - $foreign_keys = DBA::selectToArray(['INFORMATION_SCHEMA' => 'KEY_COLUMN_USAGE'], + $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 = DBA::selectFirst(['INFORMATION_SCHEMA' => 'TABLES'], + $table_status = DBA::selectFirst('INFORMATION_SCHEMA.TABLES', ['ENGINE', 'TABLE_COLLATION', 'TABLE_COMMENT'], ["`TABLE_SCHEMA` = ? AND `TABLE_NAME` = ?", DBA::databaseName(), $table]); @@ -1064,7 +1064,7 @@ class DBStructure * @return boolean Does the table exist? * @throws Exception */ - public static function existsColumn($table, $columns = []) + public static function existsColumn(string $table, array $columns = []): bool { if (empty($table)) { return false; diff --git a/src/Database/Database.php b/src/Database/Database.php index fcc6f63379..fbdde000c1 100644 --- a/src/Database/Database.php +++ b/src/Database/Database.php @@ -1366,16 +1366,16 @@ class Database /** * Retrieve a single record from a table and returns it in an associative array * - * @param string|array $table - * @param array $fields - * @param array $condition - * @param array $params + * @param string $table Table name or array [schema => table] + * @param array $fields Array of selected fields, empty for all + * @param array $condition Array of fields for condition + * @param array $params Array of several parameters * * @return bool|array * @throws \Exception * @see $this->select */ - public function selectFirst($table, array $fields = [], array $condition = [], array $params = []) + public function selectFirst(string $table, array $fields = [], array $condition = [], array $params = []) { $params['limit'] = 1; $result = $this->select($table, $fields, $condition, $params); @@ -1392,16 +1392,15 @@ class Database /** * Select rows from a table and fills an array with the data * - * @param string|array $table Table name or array [schema => table] - * @param array $fields Array of selected fields, empty for all - * @param array $condition Array of fields for condition - * @param array $params Array of several parameters - * + * @param string $table Table name or array [schema => table] + * @param array $fields Array of selected fields, empty for all + * @param array $condition Array of fields for condition + * @param array $params Array of several parameters * @return array Data array * @throws \Exception * @see self::select */ - public function selectToArray($table, array $fields = [], array $condition = [], array $params = []) + public function selectToArray(string $table, array $fields = [], array $condition = [], array $params = []) { return $this->toArray($this->select($table, $fields, $condition, $params)); } diff --git a/src/Database/View.php b/src/Database/View.php index c8941167e0..18db875ea8 100644 --- a/src/Database/View.php +++ b/src/Database/View.php @@ -183,7 +183,7 @@ class View */ private static function isView(string $view): bool { - $status = DBA::selectFirst(['INFORMATION_SCHEMA' => 'TABLES'], ['TABLE_TYPE'], + $status = DBA::selectFirst('INFORMATION_SCHEMA.TABLES', ['TABLE_TYPE'], ['TABLE_SCHEMA' => DBA::databaseName(), 'TABLE_NAME' => $view]); if (empty($status['TABLE_TYPE'])) { @@ -201,7 +201,7 @@ class View */ private static function isTable(string $table): bool { - $status = DBA::selectFirst(['INFORMATION_SCHEMA' => 'TABLES'], ['TABLE_TYPE'], + $status = DBA::selectFirst('INFORMATION_SCHEMA.TABLES', ['TABLE_TYPE'], ['TABLE_SCHEMA' => DBA::databaseName(), 'TABLE_NAME' => $table]); if (empty($status['TABLE_TYPE'])) { -- 2.39.5