From f0ea19767d502ec7b5afff7c66c2681292175a3b Mon Sep 17 00:00:00 2001
From: Thomas Lange <code@nerdmind.de>
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/database.sql     | 27 ++++++++++++++++++++++++++-
 core/db/migrations/6.sql | 24 ++++++++++++++++++++++++
 2 files changed, 50 insertions(+), 1 deletion(-)
 create mode 100644 core/db/migrations/6.sql

(limited to 'core/db')

diff --git a/core/db/database.sql b/core/db/database.sql
index 244a38b..172fb3a 100644
--- a/core/db/database.sql
+++ b/core/db/database.sql
@@ -3,7 +3,26 @@
 -- =============================================================================
 CREATE TABLE `migration` (`schema_version` smallint(4) NOT NULL)
 	ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-INSERT INTO `migration` (`schema_version`) VALUES (5);
+INSERT INTO `migration` (`schema_version`) VALUES (6);
+
+-- =============================================================================
+-- Table structure for category entities
+-- =============================================================================
+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;
 
 -- =============================================================================
 -- Table structure for page entities
@@ -32,6 +51,7 @@ CREATE TABLE `post` (
 	`time_insert` datetime NOT NULL,
 	`time_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 	`user` tinyint(3) UNSIGNED NOT NULL,
+	`category` 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,
@@ -40,6 +60,7 @@ CREATE TABLE `post` (
 	UNIQUE KEY `time_insert` (`time_insert`),
 	UNIQUE KEY `slug` (`slug`),
 	KEY `post_user` (`user`),
+	KEY `post_category` (`category`),
 	FULLTEXT KEY `search` (`name`, `body`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
@@ -66,10 +87,14 @@ CREATE TABLE `user` (
 -- =============================================================================
 -- Add foreign keys for entity relationships
 -- =============================================================================
+ALTER TABLE `category` ADD CONSTRAINT `category_parent` FOREIGN KEY (`parent`)
+	REFERENCES `category` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
 ALTER TABLE `page` ADD CONSTRAINT `page_user` FOREIGN KEY (`user`)
 	REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
 ALTER TABLE `post` ADD CONSTRAINT `post_user` FOREIGN KEY (`user`)
 	REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
+ALTER TABLE `post` ADD CONSTRAINT `post_category` FOREIGN KEY (`category`)
+	REFERENCES `category` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
 
 -- =============================================================================
 -- Insert some demo data
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