aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorThomas Lange <code@nerdmind.de>2021-07-01 19:45:12 +0200
committerThomas Lange <code@nerdmind.de>2021-07-01 19:45:12 +0200
commitf0ea19767d502ec7b5afff7c66c2681292175a3b (patch)
treeaa296a2ca7e904a0041a81a18e952fbfae484a1d
parenta0fddb8470f33d7c66ff79cdbf9956290d72db25 (diff)
downloadblog-f0ea19767d502ec7b5afff7c66c2681292175a3b.tar.gz
blog-f0ea19767d502ec7b5afff7c66c2681292175a3b.tar.xz
blog-f0ea19767d502ec7b5afff7c66c2681292175a3b.zip
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
-rw-r--r--core/db/database.sql27
-rw-r--r--core/db/migrations/6.sql24
-rw-r--r--core/namespace/Migrator.php2
3 files changed, 51 insertions, 2 deletions
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;
diff --git a/core/namespace/Migrator.php b/core/namespace/Migrator.php
index 6eec9a5..8f31f7c 100644
--- a/core/namespace/Migrator.php
+++ b/core/namespace/Migrator.php
@@ -5,7 +5,7 @@ class Migrator {
private $directory;
private $migrations = [];
- const CURRENT_SCHEMA_VERSION = 5;
+ const CURRENT_SCHEMA_VERSION = 6;
#===============================================================================
# Fetch on-disk schema version from migration table