0001_base.sql (4021B)
1 create table logins 2 ( 3 id int not null auto_increment primary key, 4 email varchar(255) not null unique, 5 password_hash varchar(255) not null, 6 created_at datetime not null default current_timestamp, 7 updated_at datetime null on update current_timestamp 8 ); 9 10 create table signup_requests 11 ( 12 id int not null auto_increment primary key, 13 email varchar(255) not null, 14 requested_ip varchar(255) not null, 15 requested_user_agent varchar(255) not null, 16 created_at datetime not null default current_timestamp, 17 confirmed_at datetime null 18 ); 19 20 create table user_profiles 21 ( 22 login_id int not null primary key references logins (id) 23 on delete cascade on update cascade, 24 nickname varchar(255) not null, 25 bio text null, 26 updated_at datetime null on update current_timestamp 27 ); 28 29 create table user_acl_groups 30 ( 31 login_id int not null references logins (id) 32 on delete cascade on update cascade, 33 acl_group varchar(255) not null, 34 35 primary key (login_id, acl_group), 36 index (login_id), 37 index (acl_group) 38 ); 39 40 create table wikis 41 ( 42 id int not null auto_increment primary key, 43 owner_id int not null references logins (id) 44 on delete restrict on update cascade, 45 slug varchar(48) not null unique check ( slug <> '' ), 46 title varchar(255) not null, 47 description text null, 48 acl_data json null, 49 created_at datetime not null default current_timestamp, 50 updated_at datetime null on update current_timestamp 51 ); 52 53 create table wiki_texts 54 ( 55 id int not null auto_increment primary key, 56 content mediumtext not null check ( content <> '' ), 57 encoding varchar(255) not null default 'utf-8' 58 ); 59 60 create table wiki_pages 61 ( 62 id int not null auto_increment primary key, 63 wiki_id int not null references wikis (id) 64 on delete cascade on update cascade, 65 path varchar(255) not null check ( path <> '' ), 66 text_id int not null references wiki_texts (id) 67 on delete restrict on update cascade, 68 acl_data json null, 69 created_at datetime not null default current_timestamp, 70 updated_at datetime null on update current_timestamp, 71 72 unique (wiki_id, path) 73 ); 74 75 create table wiki_changes 76 ( 77 id int not null auto_increment primary key, 78 page_id int not null references wiki_pages (id) 79 on delete cascade on update cascade, 80 author_id int null references logins (id) 81 on delete set null on update cascade, 82 author_ip varchar(255) null, 83 text_id int not null references wiki_texts (id) 84 on delete restrict on update cascade, 85 created_at datetime not null default current_timestamp 86 ); 87 88 create table threads 89 ( 90 id int not null auto_increment primary key, 91 author_id int not null references logins (id) 92 on delete set null on update cascade, 93 title varchar(255) not null, 94 created_at datetime not null default current_timestamp 95 ); 96 97 create table thread_comments 98 ( 99 id int not null auto_increment primary key, 100 thread_id int not null references threads (id) 101 on delete cascade on update cascade, 102 author_id int not null references logins (id) 103 on delete set null on update cascade, 104 content text not null check ( content <> '' ), 105 created_at datetime not null default current_timestamp, 106 updated_at datetime null on update current_timestamp 107 ); 108 109 create table wiki_talks 110 ( 111 page_id int not null references wiki_pages (id) 112 on delete cascade on update cascade, 113 thread_id int not null references threads (id) 114 on delete cascade on update cascade, 115 116 primary key (page_id, thread_id) 117 ); 118