CodeBlog/schema.sql at main · ArttuKnowit/CodeBlog · GitHub

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
DROPTABLE IF EXISTS posts;

DROPTABLE IF EXISTS users;

CREATETABLEusers (
 id INTEGERPRIMARY KEY AUTOINCREMENT,
 username TEXTNOT NULL UNIQUE,
 password TEXTNOT NULL,
 intro TEXT DEFAULT ''
);

CREATETABLEposts (
 id INTEGERPRIMARY KEY AUTOINCREMENT,
 created TIMESTAMPNOT NULL DEFAULT CURRENT_TIMESTAMP,
 title TEXTNOT NULL,
 content TEXTNOT NULL,
 user_id INTEGER,
FOREIGN KEY (user_id) REFERENCES users (id)
);

CREATETABLEcomments (
 id INTEGERPRIMARY KEY AUTOINCREMENT,
 post_id INTEGERNOT NULL,
 user_id INTEGER,
 content TEXTNOT NULL,
 created TIMESTAMPNOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (post_id) REFERENCES posts (id),
FOREIGN KEY (user_id) REFERENCES users (id)
);

CREATETABLEfavorites (
 user_id INTEGERNOT NULL,
 post_id INTEGERNOT NULL,
PRIMARY KEY (user_id, post_id),
FOREIGN KEY (user_id) REFERENCES users (id),
FOREIGN KEY (post_id) REFERENCES posts (id)
);

-- Private messages between users
CREATETABLEmessages (
 id INTEGERPRIMARY KEY AUTOINCREMENT,
 sender_id INTEGERNOT NULL,
 recipient_id INTEGERNOT NULL,
 content TEXTNOT NULL,
 created TIMESTAMPNOT NULL DEFAULT CURRENT_TIMESTAMP,
 is_read INTEGERNOT NULL DEFAULT 0,
FOREIGN KEY (sender_id) REFERENCES users (id),
FOREIGN KEY (recipient_id) REFERENCES users (id)
);