CopyPastor/schema.sql at develop · SOBotics/CopyPastor · 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
CREATE TABLE IF NOT EXISTS posts (
 post_id INTEGER PRIMARY KEY,
 url_one text NOT NULL,
 url_two text NOT NULL,
 title_one text NOT NULL,
 title_two text NOT NULL,
 body_one text NOT NULL,
 body_two text NOT NULL,
 date_one INTEGER NOT NULL,
 date_two INTEGER NOT NULL,
 username_one text NOT NULL,
 username_two text NOT NULL,
 user_url_one text NOT NULL,
 user_url_two text NOT NULL,
 score INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS feedback (
 feedback_id INTEGER PRIMARY KEY,
 post_id INTEGER,
 feedback_type text NOT NULL,
 username text NOT NULL,
 link text NOT NULL,
 FOREIGN KEY(post_id) REFERENCES posts(post_id)
);

CREATE TABLE IF NOT EXISTS auth (
 auth_string TEXT PRIMARY KEY,
 associated_user TEXT
);

CREATE TABLE IF NOT EXISTS reasons (
 reason_id INTEGER PRIMARY KEY,
 reason text NOT NULL
);

CREATE TABLE IF NOT EXISTS caught_for (
 post_id INTEGER NOT NULL,
 reason_id INTEGER NOT NULL,
 score INTEGER NOT NULL,
 PRIMARY KEY (post_id, reason_id),
 FOREIGN KEY(post_id) REFERENCES posts(post_id),
 FOREIGN KEY(reason_id) REFERENCES reasons(reason_id)
);