· 8 years ago · Apr 03, 2018, 09:44 PM
1
2DROP TABLE IF EXISTS community;
3CREATE TABLE community (
4
5 id INTEGER,
6 name text,
7 wiki text,
8 image text NOT NULL,
9
10 CONSTRAINT community_pk PRIMARY KEY (id)
11);
12
13insert into community VALUES (0, 'choro', 'fodase', 'wefwe.png');
14insert into community VALUES (1, 'muito choro','riplbaw', 'werfwe.png');
15insert into community VALUES (2, 'lbaw' ,'rumoaopento', 'wefwe.png');
16insert into community VALUES (3, 'record' ,'futebol e cenas de futebol', 'record.png');
17insert into community VALUES (4, 'jn' ,'Ultimas Noticias', 'jn.png');
18insert into community VALUES (5, 'saude' ,'Noticias de Saude', 'saude.png');
19insert into community VALUES (6, 'noticias privadas' ,'Noticias secretas e isso', 'privado.png');
20
21DROP TABLE IF EXISTS "USER";
22CREATE TABLE "USER" (
23
24 id INTEGER,
25 name text NOT NULL,
26 email text UNIQUE NOT NULL,
27 username text UNIQUE NOT NULL,
28 date_joined TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
29
30 description text,
31 is_deleted INTEGER NOT NULL DEFAULT 0,
32
33 CONSTRAINT user_pk PRIMARY KEY (id)
34);
35
36CREATE INDEX user_name ON "USER" USING hash (username);
37
38insert into "USER" values (0, 'sid', 'rip@lbaw.com', 'sid', '2016-08-20 19:03:34', 'gg', 0);
39insert into "USER" values (1, 'oco', 'rip@sdis.com', 'oco', '2011-02-16 19:03:34', 'gg', 0);
40insert into "USER" values (2, 'cc', 'rip@ppin.com', 'cc', '2018-01-02 19:03:34', 'gg', 0);
41
42insert into "USER" values (3, 'claudia', 'cla@lbaw.com', 'cla', '2016-08-20 19:03:34', 'gg', 0);
43insert into "USER" values (4, 'bruno', 'bruno@sdis.com', 'br', '2011-02-16 19:03:34', 'gg', 0);
44insert into "USER" values (5, 'mm', 'mm@ppin.com', 'memesius', '2018-01-02 19:03:34', 'gg', 0);
45
46DROP TABLE IF EXISTS article;
47CREATE TABLE article (
48
49 id INTEGER,
50 id_community INTEGER NOT NULL,
51 id_user INTEGER NOT NULL,
52 title text NOT NULL,
53 content text NOT NULL,
54 upvotes INTEGER NOT NULL DEFAULT 0 CONSTRAINT upvotes CHECK (upvotes >= 0),
55 downvotes INTEGER NOT NULL DEFAULT 0 CONSTRAINT downvotes CHECK (downvotes >= 0),
56 posted_date TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
57
58
59 CONSTRAINT article_pk PRIMARY KEY (id),
60 CONSTRAINT id_community_fk FOREIGN KEY (id_community) REFERENCES community(id) ON UPDATE CASCADE,
61 CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES "USER"(id) ON UPDATE CASCADE
62);
63
64insert into article values(0, 0, 0, 'ripsdis', 'ta tudo a arder', 10000, 1, '2018-03-09 19:03:34');
65insert into article values(1, 0, 1, 'riplbaw', 'ta tudo a arder2', 130000, 1, '2018-03-03 19:03:34');
66insert into article values(2, 0, 1, 'ripcomp', 'o teste foi fodido', 130000, 1, '2018-01-02 19:03:34');
67insert into article values(3, 1, 2, 'lavar roupa suja com sql', 'gg', 13320000, 1, '2018-10-11 19:03:34');
68insert into article values(4, 0, 0, 'preciso de mais dados', 'ta tudo a arder ainda mais', 100, 19, '2018-03-09 19:03:34');
69insert into article values(5, 0, 1, 'quero dormir', 'zzzzzzz', 130, 1003, '2018-03-03 19:03:34');
70insert into article values(6, 0, 1, 'sono muito sono', 'dowefwefwef', 10000, 41, '2018-01-02 19:03:34');
71insert into article values(7, 1, 2, 'lavar roupa suja com postgresql', 'melhor sql', 13320000, 24241, '2018-10-11 19:03:34');
72
73
74
75DROP TABLE IF EXISTS comment;
76CREATE TABLE comment (
77
78 id INTEGER,
79 id_article INTEGER NOT NULL,
80 id_user INTEGER NOT NULL,
81 content text NOT NULL,
82 upvotes INTEGER NOT NULL DEFAULT 0 CONSTRAINT upvotes CHECK (upvotes > -1),
83 downvotes INTEGER NOT NULL DEFAULT 0 CONSTRAINT downvotes CHECK (downvotes > -1),
84 posted_date TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
85
86 id_comment INTEGER, --parent comment
87
88 CONSTRAINT comment_pk PRIMARY KEY (id),
89 CONSTRAINT id_article_fk FOREIGN KEY (id_article) REFERENCES article(id) ON UPDATE CASCADE,
90 CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES "USER"(id) ON UPDATE CASCADE,
91 CONSTRAINT id_comment_fk FOREIGN KEY (id_comment) REFERENCES comment(id) ON UPDATE CASCADE
92);
93
94insert into comment values(0, 0, 0, 'menos choro', 10000, 1, '2018-03-09 19:03:34');
95insert into comment values(4, 0, 0, 'CALATE', 10000, 0, '2018-02-09 19:03:34', 0);
96insert into comment values(1, 0, 0, ' choro', 10000, 1, '2018-03-09 19:03:34');
97insert into comment values(2, 1, 0, 'mais choro', 10000, 1, '2018-03-09 19:03:34');
98insert into comment values(3, 2, 0, 'pq lbaw', 10000, 1, '2018-03-09 19:03:34');
99insert into comment values(5, 0, 0, ' sql muito bom', 10000, 11, '2018-03-09 19:03:34', 1);
100insert into comment values(6, 1, 0, 'wedwedwed', 104000, 41, '2018-03-09 19:03:34', 2);
101insert into comment values(7, 2, 0, 'pq postgresql', 10000, 61, '2018-03-09 19:03:34');
102
103
104DROP TABLE IF EXISTS saved;
105CREATE TABLE saved (
106 id_user INTEGER,
107 id_article INTEGER,
108
109 CONSTRAINT saved_pk PRIMARY KEY (id_user, id_article),
110 CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES "USER"(id) ON UPDATE CASCADE,
111 CONSTRAINT id_article_fk FOREIGN KEY (id_article) REFERENCES article(id) ON UPDATE CASCADE
112);
113
114insert into saved values( 0,0);
115insert into saved values( 0,1);
116insert into saved values( 1,0);
117insert into saved values( 1,2);
118
119
120 DROP TABLE IF EXISTS tag;
121CREATE TABLE tag (
122
123 id INTEGER,
124 name text NOT NULL,
125
126 CONSTRAINT tag_pk PRIMARY KEY (id)
127);
128
129insert into tag values (0, 'interessante');
130insert into tag values (1, 'mau');
131insert into tag values (2, 'bom');
132
133DROP TABLE IF EXISTS tag_saved;
134CREATE TABLE tag_saved (
135 id_tag INTEGER,
136 id_saved_user INTEGER,
137 id_saved_article INTEGER,
138
139 CONSTRAINT tag_saved_pk PRIMARY KEY (id_tag, id_saved_user, id_saved_article),
140 CONSTRAINT id_tag_fk FOREIGN KEY (id_tag) REFERENCES tag(id) ON UPDATE CASCADE,
141 CONSTRAINT id_saved_fk FOREIGN KEY (id_saved_user, id_saved_article) REFERENCES saved(id_user, id_article) ON UPDATE CASCADE
142);
143
144insert into tag_saved values(0, 0,0);
145insert into tag_saved values(1, 0,1);
146insert into tag_saved values(2, 1,0);
147
148DROP TABLE IF EXISTS message;
149CREATE TABLE message (
150
151 id INTEGER,
152 id_user_to INTEGER NOT NULL,
153 id_user_from INTEGER NOT NULL,
154 content text NOT NULL,
155
156 CONSTRAINT message_pk PRIMARY KEY (id),
157 CONSTRAINT id_user_to_fk FOREIGN KEY (id_user_to) REFERENCES "USER"(id) ON UPDATE CASCADE,
158 CONSTRAINT id_user_from_fk FOREIGN KEY (id_user_from) REFERENCES "USER"(id) ON UPDATE CASCADE
159);
160
161insert into message values (0, 0, 1, 'es gay');
162insert into message values (1, 1, 0, 'rip lbaw');
163insert into message values (2, 0, 1, '........');
164insert into message values (3, 1, 2, '.wdewde.......');
165
166
167DROP TABLE IF EXISTS notification;
168CREATE TABLE notification (
169
170 id INTEGER,
171 content text,
172 id_user INTEGER NOT NULL,
173
174 CONSTRAINT notification_pk PRIMARY KEY (id),
175 CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES "USER"(id) ON UPDATE CASCADE
176);
177
178 insert into notification values (0, 'es gay', 0);
179 insert into notification values (1, '......', 0);
180 insert into notification values (2, 'ta tudo a arder', 1);
181 insert into notification values (3, 'ganhaste 10000000 euros', 2);
182
183
184DROP TABLE IF EXISTS follow;
185CREATE TABLE follow (
186
187 id_user_follower INTEGER,
188 id_user_followed INTEGER,
189
190 CONSTRAINT follow_pk PRIMARY KEY (id_user_follower, id_user_followed),
191 CONSTRAINT id_follower_fk FOREIGN KEY (id_user_follower) REFERENCES "USER"(id) ON UPDATE CASCADE,
192 CONSTRAINT id_followed_fk FOREIGN KEY (id_user_followed) REFERENCES "USER"(id) ON UPDATE CASCADE
193);
194
195insert into follow values(0,1);
196insert into follow values(1,2);
197insert into follow values(0,2);
198
199DROP TABLE IF EXISTS admin;
200CREATE TABLE admin (
201
202 id_user INTEGER,
203 CONSTRAINT admin_pk PRIMARY KEY (id_user),
204 CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES "USER"(id) ON UPDATE CASCADE
205);
206
207insert into admin values(0);
208
209DROP TABLE IF EXISTS private;
210CREATE TABLE private (
211
212 id_community INTEGER,
213
214 CONSTRAINT private_community_pk PRIMARY KEY (id_community),
215 CONSTRAINT id_community_fk FOREIGN KEY (id_community) REFERENCES community(id) ON UPDATE CASCADE
216);
217
218insert into private values (6);
219
220DROP TABLE IF EXISTS member_private;
221CREATE TABLE member_private (
222
223 id_user INTEGER,
224 id_private INTEGER,
225 is_approved INTEGER NOT NULL DEFAULT 0,
226
227 CONSTRAINT member_private_pk PRIMARY KEY (id_user, id_private),
228 CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES "USER"(id) ON UPDATE CASCADE,
229 CONSTRAINT id_private_fk FOREIGN KEY (id_private) REFERENCES private(id_community) ON UPDATE CASCADE
230);
231
232insert into member_private values (0, 6);
233
234DROP TABLE IF EXISTS member_status;
235CREATE TABLE member_status (
236
237 id_user INTEGER,
238 id_community INTEGER,
239 member_state text NOT NULL ,
240
241 CONSTRAINT status_ck CHECK ((member_state = ANY (ARRAY['banned'::text, 'subscriptor'::text, 'moderator'::text ]))),
242
243 CONSTRAINT member_status_pk PRIMARY KEY (id_user, id_community),
244 CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES "USER"(id) ON UPDATE CASCADE,
245 CONSTRAINT id_community_fk FOREIGN KEY (id_community) REFERENCES community(id) ON UPDATE CASCADE
246);
247insert into member_status values(0,0,'moderator');
248insert into member_status values(1,0,'subscriptor');
249insert into member_status values(0,1,'banned');