dh_demo

commit 55e06556f300007f879d710f220c7675418fa97a
parent 32d63ebf51b905da30b6925dc0c7de0322f25c86
Author: Yongbin Kim <iam@yongbin.kim>
Date:   Wed, 25 Jan 2023 11:23:00 +0900

feat: 테이블 정리

- ACL 테이블 삭제 및 유저 ACL 그룹 테이블 추가
- wiki_pages가 (wikis.id, path) 대신 id를 primary key로 사용하게 수정
- wiki_talks 테이블 추가

Signed-off-by: Yongbin Kim <iam@yongbin.kim>

Diffstat:
M
sql/0001_base.sql
 | 
48
+++++++++++++++++++++++++++++-------------------

1 file changed, 29 insertions(+), 19 deletions(-)
diff --git a/sql/0001_base.sql b/sql/0001_base.sql
@@ -26,6 +26,14 @@ create table user_profiles
     updated_at datetime     null on update current_timestamp
 );
 
+create table user_acl_groups
+(
+    login_id  int          not null references logins (id)
+        on delete cascade on update cascade,
+    acl_group varchar(255) not null,
+    primary key (login_id, acl_group)
+);
+
 create table wikis
 (
     id          int          not null auto_increment primary key,
@@ -41,6 +49,7 @@ create table wikis
 
 create table wiki_pages
 (
+    id         int          not null auto_increment primary key,
     wiki_id    int          not null references wikis (id)
         on delete cascade on update cascade,
     path       varchar(255) not null check ( path <> '' ),
@@ -50,36 +59,37 @@ create table wiki_pages
     created_at datetime     not null default current_timestamp,
     updated_at datetime     null on update current_timestamp,
 
-    primary key (wiki_id, path)
+    index (wiki_id, path)
 );
 
 create table wiki_changes
 (
-    id         int          not null auto_increment primary key,
-    wiki_id    int          not null references wikis (id)
+    id         int      not null auto_increment primary key,
+    page_id    int      not null references wiki_pages (id)
         on delete cascade on update cascade,
-    page_slug  varchar(255) not null,
-    author_id  int          not null references logins (id)
+    author_id  int      not null references logins (id)
         on delete set null on update cascade,
-    diff       text         not null,
-    created_at datetime     not null default current_timestamp,
-
-    foreign key (wiki_id, page_slug) references wiki_pages (wiki_id, path)
-        on delete cascade on update cascade
+    diff       text     not null,
+    created_at datetime not null default current_timestamp
 );
 
-create table acl_groups
+create table wiki_talks
 (
-    id   int          not null auto_increment primary key,
-    name varchar(255) not null unique
+    id         int          not null auto_increment primary key,
+    page_id    int          not null references wiki_pages (id)
+        on delete cascade on update cascade,
+    title      varchar(255) not null check ( title <> '' ),
+    created_at datetime     not null default current_timestamp
 );
 
-create table acl_group_members
+create table wiki_talk_comments
 (
-    group_id int not null references acl_groups (id)
+    id         int      not null auto_increment primary key,
+    talk_id    int      not null references wiki_talks (id)
         on delete cascade on update cascade,
-    login_id int not null references logins (id)
-        on delete cascade on update cascade,
-
-    primary key (group_id, login_id)
+    author_id  int      not null references logins (id)
+        on delete set null on update cascade,
+    content    text     not null check ( content <> '' ),
+    created_at datetime not null default current_timestamp,
+    updated_at datetime null on update current_timestamp
 );