From 4b43d8a30fbee3e225eaca25cdfc0deea61ec74d Mon Sep 17 00:00:00 2001 From: Banana Date: Wed, 30 Dec 2020 17:06:21 +0100 Subject: [PATCH] collation cleanup --- README | 4 +- documentation/setup/bibliotheca.sql | 82 +++++++++++------------ webclient/api.php | 2 +- webclient/index.php | 2 +- webclient/lib/managecollections.class.php | 8 +-- 5 files changed, 49 insertions(+), 49 deletions(-) diff --git a/README b/README index 366a65d..c09a32c 100644 --- a/README +++ b/README @@ -8,10 +8,10 @@ https://www.bananas-playground.net/projekt/bibliotheca/ * A Webserver (tested on Apache 2.4) * PHP 7+ -* MySQL 8+ +* MySQL 8+ / MariaDB 10.2 ## Technical information -This is build and tested on a linux based OS. Windows support not yet. +This is build and tested on a linux based OS. Windows support not yet, but it could work. ## third party https://github.com/FabianBeiner/PHP-IMDB-Grabber with some modifications diff --git a/documentation/setup/bibliotheca.sql b/documentation/setup/bibliotheca.sql index a1ef00c..23b61aa 100644 --- a/documentation/setup/bibliotheca.sql +++ b/documentation/setup/bibliotheca.sql @@ -27,16 +27,16 @@ SET time_zone = "+00:00"; DROP TABLE IF EXISTS `#REPLACEME#_collection`; CREATE TABLE `bib_collection` ( `id` int NOT NULL, - `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, - `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, - `defaultSearchField` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, + `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `defaultSearchField` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `modificationuser` int DEFAULT NULL, `owner` int NOT NULL, `group` int NOT NULL, - `rights` char(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; + `rights` char(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- @@ -47,16 +47,16 @@ CREATE TABLE `bib_collection` ( DROP TABLE IF EXISTS `#REPLACEME#_group`; CREATE TABLE `#REPLACEME#_group` ( `id` int NOT NULL, - `name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, - `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, + `name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `protected` tinyint(1) NOT NULL DEFAULT '0', `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `modificationuser` int DEFAULT NULL, `owner` int NOT NULL, `group` int NOT NULL, - `rights` char(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; + `rights` char(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `#REPLACEME#_group` @@ -76,15 +76,15 @@ INSERT INTO `#REPLACEME#_group` (`id`, `name`, `description`, `protected`, `crea DROP TABLE IF EXISTS `#REPLACEME#_menu`; CREATE TABLE `#REPLACEME#_menu` ( `id` int UNSIGNED NOT NULL, - `text` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, - `action` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, - `icon` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, + `text` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `action` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `icon` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `owner` int NOT NULL DEFAULT '0', `group` int NOT NULL DEFAULT '0', - `rights` char(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, + `rights` char(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `position` int NOT NULL DEFAULT '0', - `category` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; + `category` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `#REPLACEME#_menu` @@ -114,20 +114,20 @@ INSERT INTO `#REPLACEME#_menu` (`id`, `text`, `action`, `icon`, `owner`, `group` DROP TABLE IF EXISTS `#REPLACEME#_sys_fields`; CREATE TABLE `#REPLACEME#_sys_fields` ( `id` int NOT NULL, - `identifier` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, - `displayname` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, - `type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, - `searchtype` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, - `createstring` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, - `value` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci, - `apiinfo` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, + `identifier` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `displayname` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `searchtype` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `createstring` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `value` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + `apiinfo` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `modificationuser` int DEFAULT NULL, `owner` int NOT NULL, `group` int NOT NULL, - `rights` char(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; + `rights` char(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `#REPLACEME#_sys_fields` @@ -167,14 +167,14 @@ INSERT INTO `#REPLACEME#_sys_fields` (`id`, `identifier`, `displayname`, `type`, DROP TABLE IF EXISTS `#REPLACEME#_tool`; CREATE TABLE `#REPLACEME#_tool` ( `id` int NOT NULL, - `name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, - `description` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, - `action` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, + `name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `description` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `action` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `target` varchar(16) NOT NULL, `owner` int NOT NULL, `group` int NOT NULL, - `rights` char(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; + `rights` char(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `#REPLACEME#_tool` @@ -193,7 +193,7 @@ DROP TABLE IF EXISTS `#REPLACEME#_tool2collection`; CREATE TABLE `#REPLACEME#_tool2collection` ( `fk_tool_id` int NOT NULL, `fk_collection_id` int NOT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- @@ -204,11 +204,11 @@ CREATE TABLE `#REPLACEME#_tool2collection` ( DROP TABLE IF EXISTS `#REPLACEME#_user`; CREATE TABLE `#REPLACEME#_user` ( `id` int NOT NULL, - `login` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, - `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, - `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, + `login` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `active` int NOT NULL DEFAULT '1', - `apiToken` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, + `apiToken` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `apiTokenValidDate` timestamp NULL DEFAULT NULL, `baseGroupId` int NOT NULL DEFAULT '0', `protected` tinyint(1) NOT NULL DEFAULT '0', @@ -217,8 +217,8 @@ CREATE TABLE `#REPLACEME#_user` ( `modificationuser` int DEFAULT NULL, `owner` int NOT NULL, `group` int NOT NULL, - `rights` char(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; + `rights` char(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `#REPLACEME#_user` @@ -238,7 +238,7 @@ DROP TABLE IF EXISTS `#REPLACEME#_user2group`; CREATE TABLE `#REPLACEME#_user2group` ( `fk_user_id` int NOT NULL DEFAULT '0', `fk_group_id` int NOT NULL DEFAULT '0' -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `#REPLACEME#_user2group` @@ -258,10 +258,10 @@ DROP TABLE IF EXISTS `#REPLACEME#_userSession`; CREATE TABLE `#REPLACEME#_userSession` ( `fk_user_id` int NOT NULL, `loginTime` datetime NOT NULL, - `area` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '', - `token` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, - `salt` char(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; + `area` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', + `token` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `salt` char(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `#REPLACEME#_userSession` diff --git a/webclient/api.php b/webclient/api.php index 7edfd5c..d9659b5 100644 --- a/webclient/api.php +++ b/webclient/api.php @@ -41,7 +41,7 @@ $driver = new mysqli_driver(); $driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT; if ($DB->connect_errno) exit('Can not connect to MySQL Server'); $DB->set_charset("utf8mb4"); -$DB->query("SET collation_connection = 'utf8mb4_0900_ai_ci'"); +$DB->query("SET collation_connection = 'utf8mb4_unicode_ci'"); # user Object $Doomguy = new Doomguy($DB); diff --git a/webclient/index.php b/webclient/index.php index ff65231..52230f5 100644 --- a/webclient/index.php +++ b/webclient/index.php @@ -53,7 +53,7 @@ $driver = new mysqli_driver(); $driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT; if ($DB->connect_errno) exit('Can not connect to MySQL Server'); $DB->set_charset("utf8mb4"); -$DB->query("SET collation_connection = 'utf8mb4_0900_ai_ci'"); +$DB->query("SET collation_connection = 'utf8mb4_unicode_ci'"); # user Object $Doomguy = new Doomguy($DB); diff --git a/webclient/lib/managecollections.class.php b/webclient/lib/managecollections.class.php index e37266e..94952dd 100644 --- a/webclient/lib/managecollections.class.php +++ b/webclient/lib/managecollections.class.php @@ -219,9 +219,9 @@ class ManageCollections { $queryEntry2lookup = "CREATE TABLE `".DB_PREFIX."_collection_entry2lookup_".$newId."` ( `fk_field` int NOT NULL, `fk_entry` int NOT NULL, - `value` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, + `value` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, FULLTEXT KEY `value` (`value`) - ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci"; + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"; $this->_DB->query($queryEntry2lookup); $queryCollectionFields = "CREATE TABLE `".DB_PREFIX."_collection_fields_".$newId."` ( @@ -229,7 +229,7 @@ class ManageCollections { `sort` int NOT NULL, UNIQUE KEY `fk_field_id` (`fk_field_id`), KEY `sort` (`sort`) - ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci"; + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"; $this->_DB->query($queryCollectionFields); $queryCollectionEntry = "CREATE TABLE `".DB_PREFIX."_collection_entry_".$newId."` ( @@ -241,7 +241,7 @@ class ManageCollections { `group` int NOT NULL, `rights` char(9) COLLATE utf8mb4_bin NOT NULL, PRIMARY KEY (`id`) - ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci"; + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"; $this->_DB->query($queryCollectionEntry); $this->_updateToolRelation($newId,$data['tool']); -- 2.39.5