· 8 years ago · Apr 03, 2018, 10:04 PM
1DROP TABLE IF EXISTS community;
2CREATE TABLE community (
3
4 id INTEGER,
5 name text,
6 wiki text,
7 image text NOT NULL,
8
9 CONSTRAINT community_pk PRIMARY KEY (id)
10);
11
12DROP TABLE IF EXISTS "USER";
13CREATE TABLE "USER" (
14
15 id INTEGER,
16 name text NOT NULL,
17 email text UNIQUE NOT NULL,
18 username text UNIQUE NOT NULL,
19 date_joined TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
20
21 description text,
22 is_deleted INTEGER NOT NULL DEFAULT 0,
23
24 CONSTRAINT user_pk PRIMARY KEY (id)
25);
26
27CREATE INDEX user_name ON "USER" USING hash (username);
28
29DROP TABLE IF EXISTS article;
30CREATE TABLE article (
31
32 id INTEGER,
33 id_community INTEGER NOT NULL,
34 id_user INTEGER NOT NULL,
35 title text NOT NULL,
36 content text NOT NULL,
37 upvotes INTEGER NOT NULL DEFAULT 0 CONSTRAINT upvotes CHECK (upvotes >= 0),
38 downvotes INTEGER NOT NULL DEFAULT 0 CONSTRAINT downvotes CHECK (downvotes >= 0),
39 posted_date TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
40
41
42 CONSTRAINT article_pk PRIMARY KEY (id),
43 CONSTRAINT id_community_fk FOREIGN KEY (id_community) REFERENCES community(id) ON UPDATE CASCADE,
44 CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES "USER"(id) ON UPDATE CASCADE
45);
46
47DROP TABLE IF EXISTS comment;
48CREATE TABLE comment (
49
50 id INTEGER,
51 id_article INTEGER NOT NULL,
52 id_user INTEGER NOT NULL,
53 content text NOT NULL,
54 upvotes INTEGER NOT NULL DEFAULT 0 CONSTRAINT upvotes CHECK (upvotes > -1),
55 downvotes INTEGER NOT NULL DEFAULT 0 CONSTRAINT downvotes CHECK (downvotes > -1),
56 posted_date TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
57
58 id_comment INTEGER, --parent comment
59
60 CONSTRAINT comment_pk PRIMARY KEY (id),
61 CONSTRAINT id_article_fk FOREIGN KEY (id_article) REFERENCES article(id) ON UPDATE CASCADE,
62 CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES "USER"(id) ON UPDATE CASCADE,
63 CONSTRAINT id_comment_fk FOREIGN KEY (id_comment) REFERENCES comment(id) ON UPDATE CASCADE
64);
65
66DROP TABLE IF EXISTS saved;
67CREATE TABLE saved (
68 id_user INTEGER,
69 id_article INTEGER,
70
71 CONSTRAINT saved_pk PRIMARY KEY (id_user, id_article),
72 CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES "USER"(id) ON UPDATE CASCADE,
73 CONSTRAINT id_article_fk FOREIGN KEY (id_article) REFERENCES article(id) ON UPDATE CASCADE
74);
75
76 DROP TABLE IF EXISTS tag;
77CREATE TABLE tag (
78
79 id INTEGER,
80 name text NOT NULL,
81
82 CONSTRAINT tag_pk PRIMARY KEY (id)
83);
84
85DROP TABLE IF EXISTS tag_saved;
86CREATE TABLE tag_saved (
87 id_tag INTEGER,
88 id_saved_user INTEGER,
89 id_saved_article INTEGER,
90
91 CONSTRAINT tag_saved_pk PRIMARY KEY (id_tag, id_saved_user, id_saved_article),
92 CONSTRAINT id_tag_fk FOREIGN KEY (id_tag) REFERENCES tag(id) ON UPDATE CASCADE,
93 CONSTRAINT id_saved_fk FOREIGN KEY (id_saved_user, id_saved_article) REFERENCES saved(id_user, id_article) ON UPDATE CASCADE
94);
95
96DROP TABLE IF EXISTS message;
97CREATE TABLE message (
98
99 id INTEGER,
100 id_user_to INTEGER NOT NULL,
101 id_user_from INTEGER NOT NULL,
102 content text NOT NULL,
103
104 CONSTRAINT message_pk PRIMARY KEY (id),
105 CONSTRAINT id_user_to_fk FOREIGN KEY (id_user_to) REFERENCES "USER"(id) ON UPDATE CASCADE,
106 CONSTRAINT id_user_from_fk FOREIGN KEY (id_user_from) REFERENCES "USER"(id) ON UPDATE CASCADE
107);
108
109
110DROP TABLE IF EXISTS notification;
111CREATE TABLE notification (
112
113 id INTEGER,
114 content text,
115 id_user INTEGER NOT NULL,
116
117 CONSTRAINT notification_pk PRIMARY KEY (id),
118 CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES "USER"(id) ON UPDATE CASCADE
119);
120
121
122DROP TABLE IF EXISTS follow;
123CREATE TABLE follow (
124
125 id_user_follower INTEGER,
126 id_user_followed INTEGER,
127
128 CONSTRAINT follow_pk PRIMARY KEY (id_user_follower, id_user_followed),
129 CONSTRAINT id_follower_fk FOREIGN KEY (id_user_follower) REFERENCES "USER"(id) ON UPDATE CASCADE,
130 CONSTRAINT id_followed_fk FOREIGN KEY (id_user_followed) REFERENCES "USER"(id) ON UPDATE CASCADE
131);
132
133
134DROP TABLE IF EXISTS admin;
135CREATE TABLE admin (
136
137 id_user INTEGER,
138 CONSTRAINT admin_pk PRIMARY KEY (id_user),
139 CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES "USER"(id) ON UPDATE CASCADE
140);
141
142DROP TABLE IF EXISTS private;
143CREATE TABLE private (
144
145 id_community INTEGER,
146
147 CONSTRAINT private_community_pk PRIMARY KEY (id_community),
148 CONSTRAINT id_community_fk FOREIGN KEY (id_community) REFERENCES community(id) ON UPDATE CASCADE
149);
150
151
152DROP TABLE IF EXISTS member_private;
153CREATE TABLE member_private (
154
155 id_user INTEGER,
156 id_private INTEGER,
157 is_approved INTEGER NOT NULL DEFAULT 0,
158
159 CONSTRAINT member_private_pk PRIMARY KEY (id_user, id_private),
160 CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES "USER"(id) ON UPDATE CASCADE,
161 CONSTRAINT id_private_fk FOREIGN KEY (id_private) REFERENCES private(id_community) ON UPDATE CASCADE
162);
163
164
165DROP TABLE IF EXISTS member_status;
166CREATE TABLE member_status (
167
168 id_user INTEGER,
169 id_community INTEGER,
170 member_state text NOT NULL ,
171
172 CONSTRAINT status_ck CHECK ((member_state = ANY (ARRAY['banned'::text, 'subscriptor'::text, 'moderator'::text ]))),
173
174 CONSTRAINT member_status_pk PRIMARY KEY (id_user, id_community),
175 CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES "USER"(id) ON UPDATE CASCADE,
176 CONSTRAINT id_community_fk FOREIGN KEY (id_community) REFERENCES community(id) ON UPDATE CASCADE
177);
178
179-- Here goes the SQL code - INSERTS
180
181INSERT INTO "community" (id,name,wiki,image) VALUES (0, 'Green News', 'How do trees access the internet? They log on.', 'wefwe.png');
182INSERT INTO "community" (id,name,wiki,image) VALUES (1, 'Water Information','Water you doing out so late tonight?', 'werfwe.png');
183INSERT INTO "community" (id,name,wiki,image) VALUES (2, 'Food News' ,'There’s a cereal killer on the loose.', 'wefw2e.png');
184INSERT INTO "community" (id,name,wiki,image) VALUES (3, 'Sports Reports' ,'Since Ive quit soccer, Ive lost my goal in life', 'wefw23e.png');
185INSERT INTO "community" (id,name,wiki,image) VALUES (4, 'Political News' ,'On a scale of North Korea to America, how free are you tonight?', 'wefw24e.png');
186
187INSERT INTO "USER" (id,name,email,username,date_joined,description,is_deleted) VALUES (0, 'Gonçalo Moreno', 'cenas1@fe.up.pt.com', 'sid','2016-08-20 19:03:34', 'Cool kid', 0);
188INSERT INTO "USER" (id,name,email,username,date_joined,description,is_deleted) VALUES (1, 'Joao Almeida', 'cenas2@gmail.com', 'oco','2012-08-20 18:03:34', 'Cooler kid', 0);
189INSERT INTO "USER" (id,name,email,username,date_joined,description,is_deleted) VALUES (2, 'Bruno Dias', 'cenas3@hotmail.com', 'badboy','2016-08-20 19:03:34', 'the coolest kid', 1);
190INSERT INTO "USER" (id,name,email,username,date_joined,description,is_deleted) VALUES (3, 'Claudia Rodrigues', 'claudia@hotmail.com', 'ClaudiaR','2016-08-13 19:03:24', 'MIEIC student', 0);
191INSERT INTO "USER" (id,name,email,username,date_joined,description,is_deleted) VALUES (4, 'Bruno Ribeiro', 'bruno@hotmail.com', 'brunor','2016-09-20 19:03:44', 'Unemployed', 0);
192
193INSERT INTO "article" (id,id_community,id_user,title,content,upvotes,downvotes,posted_date) VALUES (0, 0, 0, 'Aenean egestas', 'Cras eget nisi dictum augue', 10000, 1,'2018-03-09 19:03:34');
194INSERT INTO "article" (id,id_community,id_user,title,content,upvotes,downvotes,posted_date) VALUES (1, 0, 1, 'Lasdkajsd asdj', 'Lasdi3wqn dabs qw dsa', 130000, 1,'2018-03-03 19:03:34');
195INSERT INTO "article" (id,id_community,id_user,title,content,upvotes,downvotes,posted_date) VALUES (2, 0, 1, 'Paos', 'Psod whd sh asasd ', 130000, 1,'2018-01-02 19:03:34');
196INSERT INTO "article" (id,id_community,id_user,title,content,upvotes,downvotes,posted_date) VALUES (3, 1, 2, 'Ois jsd qs dwqjd', 'Qausd as', 13320000, 1,'2018-10-11 19:03:34');
197
198INSERT INTO "comment" (id,id_article,id_user,content,upvotes,downvotes,posted_date,id_comment) VALUES (0, 0, 0, 'fasdjas', 10000, 1, '2018-03-09 19:03:34');
199INSERT INTO "comment" (id,id_article,id_user,content,upvotes,downvotes,posted_date,id_comment) VALUES (4, 0, 0, 'ASKDAKDAS', 10000, 0, '2018-02-09 19:03:34', 0);
200INSERT INTO "comment" (id,id_article,id_user,content,upvotes,downvotes,posted_date,id_comment) VALUES (1, 0, 0, 'fasmdas asfaafs asda', 10000, 1, '2018-03-09 19:03:34');
201INSERT INTO "comment" (id,id_article,id_user,content,upvotes,downvotes,posted_date,id_comment) VALUES (2, 1, 0, 'qwe3r falsdw w', 10000, 1, '2018-03-09 19:03:34');
202INSERT INTO "comment" (id,id_article,id_user,content,upvotes,downvotes,posted_date,id_comment) VALUES (3, 2, 0, 'pq ldasd qw', 10000, 1, '2018-03-09 19:03:34');
203
204INSERT INTO "saved" (id_user,id_article) VALUES (0,0);
205INSERT INTO "saved" (id_user,id_article) VALUES (0,1);
206INSERT INTO "saved" (id_user,id_article) VALUES (1,0);
207INSERT INTO "saved" (id_user,id_article) VALUES (1,2);
208
209INSERT INTO "tag" (id,name) VALUES (0, 'interessante');
210INSERT INTO "tag" (id,name) VALUES (1, 'mau');
211INSERT INTO "tag" (id,name) VALUES (2, 'bom');
212
213INSERT INTO "tag_saved" (id_tag,id_saved_user,id_saved_article) VALUES (0, 0,0);
214INSERT INTO "tag_saved" (id_tag,id_saved_user,id_saved_article) VALUES (1, 0,1);
215INSERT INTO "tag_saved" (id_tag,id_saved_user,id_saved_article) VALUES (2, 1,0);
216
217INSERT INTO "message" (id,id_user_to,id_user_from,content) VALUES (0, 0, 1, 'asjdasdasd asd');
218INSERT INTO "message" (id,id_user_to,id_user_from,content) VALUES (1, 1, 0, 'aaaaaaa');
219INSERT INTO "message" (id,id_user_to,id_user_from,content) VALUES (2, 0, 1, '........');
220INSERT INTO "message" (id,id_user_to,id_user_from,content) VALUES (3, 1, 2, '.wdewde.......');
221
222INSERT INTO "notification" (id,content,id_user) VALUES (0, 'AKAKA AKKA', 0);
223INSERT INTO "notification" (id,content,id_user) VALUES (1, '......', 0);
224INSERT INTO "notification" (id,content,id_user) VALUES (2, 'ta tusdo a arder', 1);
225INSERT INTO "notification" (id,content,id_user) VALUES (3, 'ganhaste 10000000 euros', 2);
226
227INSERT INTO "follow" (id_user_follower,id_user_followed) VALUES (0,1);
228INSERT INTO "follow" (id_user_follower,id_user_followed) VALUES (1,2);
229INSERT INTO "follow" (id_user_follower,id_user_followed) VALUES (0,2);
230
231INSERT INTO "admin" (id_user) VALUES (0);
232
233INSERT INTO "private" (id_community) VALUES (6);
234
235INSERT INTO "member_private" (id_user,id_private,is_approved) VALUES (0, 6, 0);
236
237INSERT INTO "member_status" (id_user,id_community,member_state) VALUES (0,0,'moderator');
238INSERT INTO "member_status" (id_user,id_community,member_state) VALUES (1,0,'subscriptor');
239INSERT INTO "member_status" (id_user,id_community,member_state) VALUES (0,1,'banned');