From f0ea19767d502ec7b5afff7c66c2681292175a3b Mon Sep 17 00:00:00 2001 From: Thomas Lange Date: Thu, 1 Jul 2021 19:45:12 +0200 Subject: Update database schema for coming category system This commit updates the database schema and adds a new migration for the upcoming category system. Please note that you need to have at least the following MySQL/MariaDB versions to use the category system later: MariaDB: 10.2.2 MySQL: 8.0 --- core/db/migrations/6.sql | 24 ++++++++++++++++++++++++ 1 file changed, 24 insertions(+) create mode 100644 core/db/migrations/6.sql (limited to 'core/db/migrations') diff --git a/core/db/migrations/6.sql b/core/db/migrations/6.sql new file mode 100644 index 0000000..0808895 --- /dev/null +++ b/core/db/migrations/6.sql @@ -0,0 +1,24 @@ +CREATE TABLE `category` ( + `id` tinyint(3) UNSIGNED NOT NULL AUTO_INCREMENT, + `time_insert` datetime NOT NULL, + `time_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `parent` tinyint(3) UNSIGNED DEFAULT NULL, + `slug` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, + `name` varchar(100) NOT NULL, + `body` text NOT NULL, + `argv` varchar(250) DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `time_insert` (`time_insert`), + UNIQUE KEY `slug` (`slug`), + KEY `category_parent` (`parent`), + FULLTEXT KEY `search` (`name`, `body`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +ALTER TABLE `post` ADD `category` tinyint(3) UNSIGNED DEFAULT NULL AFTER `user`, + ADD KEY `post_category` (`category`); + +ALTER TABLE `category` ADD CONSTRAINT `category_parent` FOREIGN KEY (`parent`) + REFERENCES `category` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; + +ALTER TABLE `post` ADD CONSTRAINT `post_category` FOREIGN KEY (`category`) + REFERENCES `category` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- cgit v1.2.3