QJTUHcP9

1-- Here goes the SQL code - CREATE TABLES, INDEXES, TRIGGERS, UDFs.
2 
3-- Tables
4
5DROP TABLE IF EXISTS community;
6CREATE TABLE community (
7
8  id INTEGER,
9  name text,
10  wiki text,
11  image text NOT NULL,
12
13  CONSTRAINT community_pk PRIMARY KEY (id)
14);
15
16DROP TABLE IF EXISTS "USER";
17CREATE TABLE "USER" (
18
19  id INTEGER,
20  name text NOT NULL,
21  email text UNIQUE NOT NULL,
22  username text UNIQUE NOT NULL,
23  date_joined TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
24
25  description text,
26  is_deleted INTEGER NOT NULL DEFAULT 0,
27
28  CONSTRAINT user_pk PRIMARY KEY (id)
29);
30
31CREATE INDEX user_name ON "USER" USING hash (username);
32
33
34DROP TABLE IF EXISTS article;
35CREATE TABLE article (
36
37  id INTEGER,
38  id_community INTEGER NOT NULL,
39  id_user INTEGER NOT NULL,
40  title text NOT NULL,
41  content text NOT NULL,
42  upvotes INTEGER NOT NULL DEFAULT 0 CONSTRAINT upvotes CHECK (upvotes >= 0),
43  downvotes INTEGER NOT NULL DEFAULT 0 CONSTRAINT downvotes CHECK (downvotes >= 0),
44  posted_date TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
45
46
47  CONSTRAINT article_pk PRIMARY KEY (id),
48  CONSTRAINT id_community_fk FOREIGN KEY (id_community) REFERENCES community(id) ON UPDATE CASCADE,
49  CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES "USER"(id) ON UPDATE CASCADE
50);
51
52DROP TABLE IF EXISTS comment;
53CREATE TABLE comment (
54
55  id INTEGER,
56  id_article INTEGER NOT NULL,
57  id_user INTEGER NOT NULL,
58  content text NOT NULL,
59  upvotes INTEGER NOT NULL DEFAULT 0 CONSTRAINT upvotes CHECK (upvotes > -1),
60  downvotes INTEGER NOT NULL DEFAULT 0 CONSTRAINT downvotes CHECK (downvotes > -1),
61  posted_date TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
62
63  id_comment INTEGER,   --parent comment
64
65  CONSTRAINT comment_pk PRIMARY KEY (id),
66  CONSTRAINT id_article_fk FOREIGN KEY (id_article) REFERENCES article(id) ON UPDATE CASCADE,
67  CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES "USER"(id) ON UPDATE CASCADE,
68  CONSTRAINT id_comment_fk FOREIGN KEY (id_comment) REFERENCES comment(id) ON UPDATE CASCADE
69);
70
71
72DROP TABLE IF EXISTS saved;
73CREATE TABLE saved (
74  id_user INTEGER,
75  id_article INTEGER,
76
77  CONSTRAINT saved_pk PRIMARY KEY (id_user, id_article),
78  CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES "USER"(id) ON UPDATE CASCADE,
79  CONSTRAINT id_article_fk FOREIGN KEY (id_article) REFERENCES article(id) ON UPDATE CASCADE
80);
81
82insert into saved values( 0,0);
83insert into saved values( 0,1);
84insert into saved values( 1,0);
85insert into saved values( 1,2);
86
87
88 DROP TABLE IF EXISTS tag;
89CREATE TABLE tag (
90
91  id INTEGER,
92  name text NOT NULL,
93
94  CONSTRAINT tag_pk PRIMARY KEY (id)
95);
96
97
98DROP TABLE IF EXISTS tag_saved;
99CREATE TABLE tag_saved (
100  id_tag INTEGER,
101  id_saved_user INTEGER,
102  id_saved_article INTEGER,
103
104  CONSTRAINT tag_saved_pk PRIMARY KEY (id_tag, id_saved_user, id_saved_article),
105  CONSTRAINT id_tag_fk FOREIGN KEY (id_tag) REFERENCES tag(id) ON UPDATE CASCADE,
106  CONSTRAINT id_saved_fk FOREIGN KEY (id_saved_user, id_saved_article) REFERENCES saved(id_user, id_article) ON UPDATE CASCADE
107);
108
109
110DROP TABLE IF EXISTS message;
111CREATE TABLE message (
112
113  id INTEGER,
114  id_user_to INTEGER NOT NULL,
115  id_user_from INTEGER NOT NULL,
116  content text NOT NULL,
117
118  CONSTRAINT message_pk PRIMARY KEY (id),
119  CONSTRAINT id_user_to_fk FOREIGN KEY (id_user_to) REFERENCES "USER"(id) ON UPDATE CASCADE,
120  CONSTRAINT id_user_from_fk FOREIGN KEY (id_user_from) REFERENCES "USER"(id) ON UPDATE CASCADE
121);
122
123DROP TABLE IF EXISTS notification;
124CREATE TABLE notification (
125
126  id INTEGER,
127  content text,
128  id_user INTEGER NOT NULL,
129
130  CONSTRAINT notification_pk PRIMARY KEY (id),
131  CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES "USER"(id) ON UPDATE CASCADE
132);
133
134DROP TABLE IF EXISTS follow;
135CREATE TABLE follow (
136
137  id_user_follower INTEGER,
138  id_user_followed INTEGER,
139
140  CONSTRAINT follow_pk PRIMARY KEY (id_user_follower, id_user_followed),
141  CONSTRAINT id_follower_fk FOREIGN KEY (id_user_follower) REFERENCES "USER"(id) ON UPDATE CASCADE,
142  CONSTRAINT id_followed_fk FOREIGN KEY (id_user_followed) REFERENCES "USER"(id) ON UPDATE CASCADE
143);
144
145insert into follow values(0,1);
146insert into follow values(1,2);
147insert into follow values(0,2);
148
149DROP TABLE IF EXISTS admin;
150CREATE TABLE admin (
151
152  id_user INTEGER,
153  CONSTRAINT admin_pk PRIMARY KEY (id_user),
154  CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES "USER"(id) ON UPDATE CASCADE
155);
156
157insert into admin values(0);
158
159DROP TABLE IF EXISTS private;
160CREATE TABLE private (
161
162  id_community INTEGER,
163
164  CONSTRAINT private_community_pk PRIMARY KEY (id_community),
165  CONSTRAINT id_community_fk FOREIGN KEY (id_community) REFERENCES community(id) ON UPDATE CASCADE
166);
167
168insert into private values (6);
169
170DROP TABLE IF EXISTS member_private;
171CREATE TABLE member_private (
172
173  id_user INTEGER,
174  id_private INTEGER,
175  is_approved INTEGER NOT NULL DEFAULT 0,
176
177  CONSTRAINT member_private_pk PRIMARY KEY (id_user, id_private),
178  CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES "USER"(id) ON UPDATE CASCADE,
179  CONSTRAINT id_private_fk FOREIGN KEY (id_private) REFERENCES private(id_community) ON UPDATE CASCADE
180);
181
182
183DROP TABLE IF EXISTS member_status;
184CREATE TABLE member_status (
185
186  id_user INTEGER,
187  id_community INTEGER,
188  member_state text NOT NULL ,
189
190  CONSTRAINT status_ck CHECK ((member_state = ANY (ARRAY['banned'::text, 'subscriptor'::text, 'moderator'::text ]))),
191
192  CONSTRAINT member_status_pk PRIMARY KEY (id_user, id_community),
193  CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES "USER"(id) ON UPDATE CASCADE,
194  CONSTRAINT id_community_fk FOREIGN KEY (id_community) REFERENCES community(id) ON UPDATE CASCADE
195);