From c0093acd514fb4828f2f347ce585ce9a045b0bd8 Mon Sep 17 00:00:00 2001 From: Banana Date: Thu, 14 Sep 2023 11:19:58 +0200 Subject: [PATCH] db charsets update --- ChangeLog | 3 ++- documentation/insipid-edit-me.sql | 26 +++++++++++++------------- documentation/update.txt | 15 +++++++++++++++ 3 files changed, 30 insertions(+), 14 deletions(-) diff --git a/ChangeLog b/ChangeLog index 91ff121..481c869 100644 --- a/ChangeLog +++ b/ChangeLog @@ -1,10 +1,11 @@ version 2.x - Dragon Chapel () + Added LOGFILE and QUERY_DEBUG const to config.php. See update.txt for more details + Updated DB table charsets. Needs manuall DB updates. See update.txt file. * Fixed URL handling at creation + Improved DB queries. + Add top 5 tag or category relation stats in the tag and category overview - + Update license to GNU GENERAL PUBLIC LICENSE Version 3, 29 June 2007 + + Updated license to GNU GENERAL PUBLIC LICENSE Version 3, 29 June 2007 + Updated error logging and try/catch for mysql + Updated from https://github.com/druidfi/mysqldump-php to https://github.com/ifsnop/mysqldump-php since develop resumed. Version mysqldump-php v2.11 diff --git a/documentation/insipid-edit-me.sql b/documentation/insipid-edit-me.sql index 889201a..577ef04 100644 --- a/documentation/insipid-edit-me.sql +++ b/documentation/insipid-edit-me.sql @@ -21,8 +21,8 @@ SET time_zone = "+00:00"; DROP TABLE IF EXISTS `#REPLACE_ME#_category`; CREATE TABLE `#REPLACE_ME#_category` ( `id` int NOT NULL, - `name` varchar(128) COLLATE utf8mb4_bin NOT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT; + `name` varchar(128) COLLATE utf8mb4_unicode_520_ci NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci ROW_FORMAT=COMPACT; -- -------------------------------------------------------- @@ -34,7 +34,7 @@ DROP TABLE IF EXISTS `#REPLACE_ME#_categoryrelation`; CREATE TABLE `#REPLACE_ME#_categoryrelation` ( `linkid` int NOT NULL, `categoryid` int NOT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci ROW_FORMAT=COMPACT; -- -------------------------------------------------------- @@ -67,16 +67,16 @@ CREATE TABLE `#REPLACE_ME#_combined` ( DROP TABLE IF EXISTS `#REPLACE_ME#_link`; CREATE TABLE `#REPLACE_ME#_link` ( `id` int NOT NULL, - `link` mediumtext COLLATE utf8mb4_bin NOT NULL, + `link` mediumtext COLLATE utf8mb4_unicode_520_ci NOT NULL, `created` datetime NOT NULL, `updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `status` int NOT NULL, - `description` varchar(255) COLLATE utf8mb4_bin NOT NULL, - `title` varchar(255) COLLATE utf8mb4_bin NOT NULL, - `image` varchar(255) COLLATE utf8mb4_bin NOT NULL, - `hash` char(32) COLLATE utf8mb4_bin NOT NULL, - `search` text COLLATE utf8mb4_bin NOT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT; + `description` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL, + `title` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL, + `image` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL, + `hash` char(32) COLLATE utf8mb4_unicode_520_ci NOT NULL, + `search` text COLLATE utf8mb4_unicode_520_ci NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci ROW_FORMAT=COMPACT; -- -------------------------------------------------------- @@ -87,8 +87,8 @@ CREATE TABLE `#REPLACE_ME#_link` ( DROP TABLE IF EXISTS `#REPLACE_ME#_tag`; CREATE TABLE `#REPLACE_ME#_tag` ( `id` int NOT NULL, - `name` varchar(64) COLLATE utf8mb4_bin NOT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT; + `name` varchar(64) COLLATE utf8mb4_unicode_520_ci NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci ROW_FORMAT=COMPACT; -- -------------------------------------------------------- @@ -100,7 +100,7 @@ DROP TABLE IF EXISTS `#REPLACE_ME#_tagrelation`; CREATE TABLE `#REPLACE_ME#_tagrelation` ( `linkid` int NOT NULL, `tagid` int NOT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci ROW_FORMAT=COMPACT; -- -------------------------------------------------------- diff --git a/documentation/update.txt b/documentation/update.txt index f01058f..d3e500e 100644 --- a/documentation/update.txt +++ b/documentation/update.txt @@ -7,6 +7,7 @@ the version your are updating from is done. + Add log file path constant to config file. See config.default.php for LOGFILE constant. Add it to your local config.php + Update your tables with the following SQL statements. Replace #REPLACE_ME# with your current table prefix. + ALTER TABLE `#REPLACE_ME#_link` ADD INDEX (`created`); ALTER TABLE `#REPLACE_ME#_link` ADD INDEX (`status`); ALTER TABLE `#REPLACE_ME#_categoryrelation` ADD INDEX (`categoryid`); @@ -14,6 +15,20 @@ ALTER TABLE `#REPLACE_ME#_tagrelation` ADD UNIQUE `tagid` (`linkid`, `tagid`); ALTER TABLE `#REPLACE_ME#_tagrelation` ADD INDEX (`linkid`); ALTER TABLE `#REPLACE_ME#_category` ADD INDEX (`name`); +ALTER TABLE `#REPLACE_ME#_category` DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci; +ALTER TABLE `#REPLACE_ME#_categoryrelation` DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci; +ALTER TABLE `#REPLACE_ME#_link` DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci; +ALTER TABLE `#REPLACE_ME#_tag` DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci; +ALTER TABLE `#REPLACE_ME#_tagrelation` DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci; +ALTER TABLE `#REPLACE_ME#_tag` CHANGE `name` `name` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL; +ALTER TABLE `#REPLACE_ME#_link` CHANGE `link` `link` MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL; +ALTER TABLE `#REPLACE_ME#_link` CHANGE `description` `description` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL; +ALTER TABLE `#REPLACE_ME#_link` CHANGE `title` `title` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL; +ALTER TABLE `#REPLACE_ME#_link` CHANGE `image` `image` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL; +ALTER TABLE `#REPLACE_ME#_link` CHANGE `hash` `hash` CHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL; +ALTER TABLE `#REPLACE_ME#_link` CHANGE `search` `search` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL; +ALTER TABLE `#REPLACE_ME#_category` CHANGE `name` `name` VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL; + ## version 2.8.1 - Deathwind + Added debug setting into config. See config.default.php for DEBUG constant. Add it to your local config.php -- 2.39.5