This repository has been archived by the owner on Oct 2, 2023. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 0
/
init.sql
81 lines (75 loc) · 2.42 KB
/
init.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
DROP TABLE IF EXISTS `comments`;
DROP TABLE IF EXISTS `edits`;
DROP TABLE IF EXISTS `stories`;
DROP TABLE IF EXISTS `competitions`;
DROP TABLE IF EXISTS `rules`;
DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
joinedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
email VARCHAR(50) NOT NULL,
password CHAR(60) NOT NULL,
active BOOLEAN DEFAULT TRUE,
score INT DEFAULT 0,
UNIQUE KEY(username),
UNIQUE KEY(email)
);
CREATE TABLE IF NOT EXISTS rules (
id INT PRIMARY KEY AUTO_INCREMENT,
ownerId INT DEFAULT 1, -- The administrator / me
name VARCHAR(50) NOT NULL,
description VARCHAR(2000) NOT NULL,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
isPublic BOOLEAN DEFAULT TRUE,
data JSON,
FOREIGN KEY (ownerId) REFERENCES users(id)
);
CREATE TABLE IF NOT EXISTS competitions (
id INT PRIMARY KEY AUTO_INCREMENT,
creatorId INT NOT NULL,
name VARCHAR(50) NOT NULL,
description VARCHAR(2000) NOT NULL,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
finishAt DATETIME,
winnerId INT,
rules JSON,
FOREIGN KEY (creatorId) REFERENCES users(id),
FOREIGN KEY (winnerId) REFERENCES users(id)
);
CREATE TABLE IF NOT EXISTS stories (
id INT PRIMARY KEY AUTO_INCREMENT,
ownerId INT NOT NULL,
competitionId INT,
name VARCHAR(50) NOT NULL,
description VARCHAR(2000),
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
lastEditedAt DATETIME,
currentEditorId INT,
semaphoreTakenAt DATETIME,
isPublic BOOLEAN DEFAULT TRUE,
isCommentsDisabled BOOLEAN DEFAULT FALSE,
content TEXT,
rules JSON,
FOREIGN KEY (ownerId) REFERENCES users(id),
FOREIGN KEY (currentEditorId) REFERENCES users(id),
FOREIGN KEY (competitionId) REFERENCES competitions(id)
);
CREATE TABLE IF NOT EXISTS edits (
id INT PRIMARY KEY AUTO_INCREMENT,
editorId INT NOT NULL,
storyId INT NOT NULL,
edit TEXT,
editedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (editorId) REFERENCES users(id),
FOREIGN KEY (storyId) REFERENCES stories(id)
);
CREATE TABLE IF NOT EXISTS comments (
id INT PRIMARY KEY AUTO_INCREMENT,
authorId INT NOT NULL,
storyId INT NOT NULL,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
content VARCHAR(2000) NOT NULL,
FOREIGN KEY (authorId) REFERENCES users(id),
FOREIGN KEY (storyId) REFERENCES stories(id)
);