aboutsummaryrefslogtreecommitdiffstats
path: root/core/db
diff options
context:
space:
mode:
authorThomas Lange <code@nerdmind.de>2021-07-01 15:56:42 +0200
committerThomas Lange <code@nerdmind.de>2021-07-01 15:56:42 +0200
commit5824bc0991a90f033ef288925a68df19164470b0 (patch)
treebb2e1dc48af533fde81f8e5f556a3e5d0fb304ea /core/db
parentcd87a869b1d52fd65f53875527abf3205a4098a0 (diff)
downloadblog-5824bc0991a90f033ef288925a68df19164470b0.tar.gz
blog-5824bc0991a90f033ef288925a68df19164470b0.tar.xz
blog-5824bc0991a90f033ef288925a68df19164470b0.zip
Update database schema: Make id columns unsigned
This commit updates the database schema and adds a new migration to modify the signed integer columns to make them unsigned.
Diffstat (limited to 'core/db')
-rw-r--r--core/db/database.sql12
-rw-r--r--core/db/migrations/5.sql13
2 files changed, 19 insertions, 6 deletions
diff --git a/core/db/database.sql b/core/db/database.sql
index d1eb8e7..244a38b 100644
--- a/core/db/database.sql
+++ b/core/db/database.sql
@@ -3,16 +3,16 @@
-- =============================================================================
CREATE TABLE `migration` (`schema_version` smallint(4) NOT NULL)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-INSERT INTO `migration` (`schema_version`) VALUES (4);
+INSERT INTO `migration` (`schema_version`) VALUES (5);
-- =============================================================================
-- Table structure for page entities
-- =============================================================================
CREATE TABLE `page` (
- `id` smallint(6) NOT NULL AUTO_INCREMENT,
+ `id` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`time_insert` datetime NOT NULL,
`time_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- `user` tinyint(4) NOT NULL,
+ `user` tinyint(3) UNSIGNED NOT NULL,
`slug` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`name` varchar(100) NOT NULL,
`body` text NOT NULL,
@@ -28,10 +28,10 @@ CREATE TABLE `page` (
-- Table structure for post entities
-- =============================================================================
CREATE TABLE `post` (
- `id` smallint(6) NOT NULL AUTO_INCREMENT,
+ `id` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`time_insert` datetime NOT NULL,
`time_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- `user` tinyint(4) NOT NULL,
+ `user` tinyint(3) UNSIGNED NOT NULL,
`slug` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`name` varchar(100) NOT NULL,
`body` text NOT NULL,
@@ -47,7 +47,7 @@ CREATE TABLE `post` (
-- Table structure for user entities
-- =============================================================================
CREATE TABLE `user` (
- `id` tinyint(4) NOT NULL AUTO_INCREMENT,
+ `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,
`slug` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
diff --git a/core/db/migrations/5.sql b/core/db/migrations/5.sql
new file mode 100644
index 0000000..8e8ecd7
--- /dev/null
+++ b/core/db/migrations/5.sql
@@ -0,0 +1,13 @@
+ALTER TABLE `page` DROP FOREIGN KEY `page_user`;
+ALTER TABLE `post` DROP FOREIGN KEY `post_user`;
+
+ALTER TABLE `page` MODIFY `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
+ MODIFY `user` TINYINT UNSIGNED NOT NULL;
+ALTER TABLE `post` MODIFY `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
+ MODIFY `user` TINYINT UNSIGNED NOT NULL;
+ALTER TABLE `user` MODIFY `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT;
+
+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;