diff options
Diffstat (limited to 'core/db')
| -rw-r--r-- | core/db/database.sql | 12 | ||||
| -rw-r--r-- | core/db/migrations/5.sql | 13 |
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; |