QGP3snmw

1
2DROP TABLE if exists community CASCADE;
3DROP TABLE if exists users CASCADE;
4DROP TABLE if exists article CASCADE;
5DROP TABLE if exists comment CASCADE;
6DROP TABLE if exists tag CASCADE;
7DROP TABLE if exists saved CASCADE;
8DROP TABLE if exists tag_saved CASCADE;
9DROP TABLE if exists message CASCADE;
10DROP TABLE if exists notification CASCADE;
11DROP TABLE if exists member_private CASCADE;
12DROP TABLE if exists member_status CASCADE;
13DROP TABLE if exists admin CASCADE;
14DROP TABLE if exists follow CASCADE;
15DROP TABLE if exists private CASCADE;
16DROP TABLE if exists votes CASCADE;
17
18
19CREATE TABLE community (
20
21  id SERIAL,
22  name text UNIQUE NOT NULL,
23  wiki text,
24  image text NOT NULL DEFAULT 'public/community_images/gray1.jpeg',
25
26  CONSTRAINT community_pk PRIMARY KEY (id)
27);
28
29
30
31CREATE TABLE users (
32
33  id SERIAL,
34  name text NOT NULL,
35
36  password VARCHAR NOT NULL,
37
38  email text UNIQUE NOT NULL,
39  username text UNIQUE NOT NULL,
40  date_joined TIMESTAMP WITH TIME zone DEFAULT now(),
41
42  description text,
43  is_deleted INTEGER DEFAULT 0,
44
45  photo text DEFAULT '0',
46
47  CONSTRAINT user_pk PRIMARY KEY (id)
48);
49
50CREATE INDEX user_name ON users USING hash (username);
51
52
53CREATE TABLE article (
54
55  id SERIAL,
56  community_id INTEGER NOT NULL,
57  user_id INTEGER NOT NULL,
58  title text NOT NULL,
59  content text NOT NULL,
60  posted_date TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
61
62  image text NOT NULL DEFAULT 'public/article_images/gray2.jpeg',
63
64  CONSTRAINT article_pk PRIMARY KEY (id),
65  CONSTRAINT id_community_fk FOREIGN KEY (community_id) REFERENCES community(id) ON UPDATE CASCADE,
66  CONSTRAINT id_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE
67);
68
69
70CREATE TABLE votes(
71
72  id SERIAL,
73  article_id INTEGER NOT NULL,
74  user_id INTEGER NOT NULL,
75  type INTEGER NOT NULL,
76
77  CONSTRAINT votes_pk PRIMARY KEY (id),
78  CONSTRAINT id_article_fk FOREIGN KEY (article_id) REFERENCES article(id) ON UPDATE CASCADE,
79  CONSTRAINT id_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE
80
81);
82
83
84
85
86
87CREATE TABLE comment (
88
89  id SERIAL,
90  article_id INTEGER NOT NULL,
91  user_id INTEGER NOT NULL,
92  content text NOT NULL,
93  posted_date TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
94
95  id_comment INTEGER,   --parent comment
96
97  CONSTRAINT comment_pk PRIMARY KEY (id),
98  CONSTRAINT id_article_fk FOREIGN KEY (article_id) REFERENCES article(id) ON UPDATE CASCADE,
99  CONSTRAINT id_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE,
100  CONSTRAINT id_comment_fk FOREIGN KEY (id_comment) REFERENCES comment(id) ON UPDATE CASCADE
101);
102
103
104
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 users(id) ON UPDATE CASCADE,
111  CONSTRAINT id_article_fk FOREIGN KEY (id_article) REFERENCES article(id) ON UPDATE CASCADE
112);
113
114
115
116CREATE TABLE tag (
117
118  id SERIAL,
119  name text NOT NULL,
120
121  CONSTRAINT tag_pk PRIMARY KEY (id)
122);
123
124
125
126CREATE TABLE tag_saved (
127
128  id_tag INTEGER,
129  id_saved_user INTEGER,
130  id_saved_article INTEGER,
131
132  CONSTRAINT tag_saved_pk PRIMARY KEY (id_tag, id_saved_user, id_saved_article),
133  CONSTRAINT id_tag_fk FOREIGN KEY (id_tag) REFERENCES tag(id) ON UPDATE CASCADE,
134  CONSTRAINT id_saved_fk FOREIGN KEY (id_saved_user, id_saved_article) REFERENCES saved(id_user, id_article) ON UPDATE CASCADE
135);
136
137
138
139CREATE TABLE message (
140
141  id SERIAL,
142  id_user_to INTEGER NOT NULL,
143  id_user_from INTEGER NOT NULL,
144  content text NOT NULL,
145
146  CONSTRAINT message_pk PRIMARY KEY (id),
147  CONSTRAINT id_user_to_fk FOREIGN KEY (id_user_to) REFERENCES users(id) ON UPDATE CASCADE,
148  CONSTRAINT id_user_from_fk FOREIGN KEY (id_user_from) REFERENCES users(id) ON UPDATE CASCADE
149);
150
151
152CREATE TABLE notification (
153
154  id SERIAL,
155  content text,
156  id_user INTEGER NOT NULL,
157  created_at TIMESTAMP WITH TIME zone,
158
159  CONSTRAINT notification_pk PRIMARY KEY (id),
160  CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES users(id) ON UPDATE CASCADE
161);
162
163
164CREATE TABLE follow (
165
166  id_user_follower INTEGER,
167  id_user_followed INTEGER,
168
169  CONSTRAINT follow_pk PRIMARY KEY (id_user_follower, id_user_followed),
170  CONSTRAINT id_follower_fk FOREIGN KEY (id_user_follower) REFERENCES users(id) ON UPDATE CASCADE,
171  CONSTRAINT id_followed_fk FOREIGN KEY (id_user_followed) REFERENCES users(id) ON UPDATE CASCADE
172);
173
174
175
176CREATE TABLE admin (
177
178  id_user INTEGER,
179  CONSTRAINT admin_pk PRIMARY KEY (id_user),
180  CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES users(id) ON UPDATE CASCADE
181);
182
183
184CREATE TABLE private (
185
186  community_id INTEGER,
187
188  CONSTRAINT private_community_pk PRIMARY KEY (community_id),
189  CONSTRAINT id_community_fk FOREIGN KEY (community_id) REFERENCES community(id) ON UPDATE CASCADE
190);
191
192
193CREATE TABLE member_private (
194
195  user_id INTEGER,
196  private_id INTEGER,
197  is_approved INTEGER NOT NULL DEFAULT 0,
198
199  CONSTRAINT member_private_pk PRIMARY KEY (user_id, private_id),
200  CONSTRAINT id_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE,
201  CONSTRAINT id_private_fk FOREIGN KEY (private_id) REFERENCES private(community_id) ON UPDATE CASCADE
202);
203
204
205
206CREATE TABLE member_status (
207
208  user_id INTEGER,
209  community_id INTEGER,
210  member_state text NOT NULL ,
211
212  CONSTRAINT status_ck CHECK ((member_state = ANY (ARRAY['banned'::text, 'subscriptor'::text, 'moderator'::text ]))),
213
214  CONSTRAINT member_status_pk PRIMARY KEY (user_id, community_id),
215  CONSTRAINT id_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE,
216  CONSTRAINT id_community_fk FOREIGN KEY (community_id) REFERENCES community(id) ON UPDATE CASCADE
217);
218
219
220INSERT INTO "users" (name,email,username,password,date_joined,description,is_deleted) VALUES ('Gonçalo Moreno', 'cenas1@fe.up.pt.com', 'sid','$2y$10$HfzIhGCCaxqyaIdGgjARSuOKAcm1Uy82YfLuNaajn6JrjLWy9Sj/W','2016-08-20 19:03:34', 'Cool kid', 0);
221INSERT INTO "users" (name,email,username,password,date_joined,description,is_deleted) VALUES ('Joao Almeida', 'cenas2@gmail.com', 'oco','$2y$10$HfzIhGCCaxqyaIdGgjARSuOKAcm1Uy82YfLuNaajn6JrjLWy9Sj/W','2012-08-20 18:03:34', 'Cooler kid', 0);
222INSERT INTO "users" (name,email,username,password,date_joined,description,is_deleted) VALUES ('Bruno Dias', 'cenas3@hotmail.com', 'badboy','$2y$10$HfzIhGCCaxqyaIdGgjARSuOKAcm1Uy82YfLuNaajn6JrjLWy9Sj/W','2016-08-20 19:03:34', 'the coolest kid', 0);
223INSERT INTO "users" (name,email,username,password,date_joined,description,is_deleted) VALUES ('Claudia Rodrigues', 'claudia@hotmail.com', 'ClaudiaR','$2y$10$HfzIhGCCaxqyaIdGgjARSuOKAcm1Uy82YfLuNaajn6JrjLWy9Sj/W','2016-08-13 19:03:24', 'MIEIC student', 0);
224INSERT INTO "users" (name,email,username,password,date_joined,description,is_deleted) VALUES ('Bruno Ribeiro', 'bruno@hotmail.com', 'brunor','$2y$10$HfzIhGCCaxqyaIdGgjARSuOKAcm1Uy82YfLuNaajn6JrjLWy9Sj/W','2016-09-20 19:03:44', 'Unemployed', 0);
225
226
227INSERT INTO "community" (name,wiki,image) VALUES ('Green News', 'How do trees access the internet? They log on.', 'public/community_images/greenews.jpg');
228INSERT INTO "community" (name,wiki, image) VALUES ('Water Information','Water you doing out so late tonight?', 'public/community_images/water.jpg');
229INSERT INTO "community" (name,wiki,image) VALUES ('Food News' ,'There’s a cereal killer on the loose.','public/community_images/foodnews.jpg');
230INSERT INTO "community" (name,wiki) VALUES ('Sports Reports' ,'Since Ive quit soccer, Ive lost my goal in life');
231INSERT INTO "community" (name,wiki) VALUES ('Political News' ,'On a scale of North Korea to America, how free are you tonight?');
232
233INSERT INTO "article" (community_id,user_id,title,content,posted_date,image) VALUES (1,1,'Earth is old!','Researchers calculate the age of the Earth by dating both the oldest rocks on the planet and meteorites that have been discovered on Earth (meteorites and Earth formed at the same time, when the solar system was forming). Their findings? Earth is about 4.54 billion years old.','2016-03-23 10:27:34','public/article_images/green1.jpg');
234INSERT INTO "article" (community_id,user_id,title,content,posted_date,image) VALUES (1,2,'The hottest spot is in Libya','The fiery award for Earth’s hottest spot goes to El Azizia, Libya, where temperature records from weather stations reveal it hit 136 degrees Fahrenheit (57.8 degrees Celsius) on Sept. 13, 1922, according to NASA Earth Observatory. There have likely been hotter locations beyond the network of weather stations. ','2018-01-01 16:10:34','public/article_images/green2.jpg'  );
235INSERT INTO "article" (community_id,user_id,title,content,posted_date,image) VALUES (1,3,'The coldest place is in Antarctica','It may come as no surprise that the coldest place on Earth can be found in Antarctica, but the chill factor is somewhat unbelievable. Winter temperatures there can drop below minus 100 degrees F (minus 73 degrees C).The lowest temperature ever recorded on Earth came from Russias Vostok Station, where records show the air plunged to a bone-chilling minus 128.6 degrees', 'public/article_images/green3.jpg' );
236INSERT INTO "article" (community_id,user_id,title,content,posted_date,image) VALUES (1,1,'Rare white koala born in Queensland Zoo','Visitors to an Australian zoo are being treated to a rare sight since the birth of a number of koala joeys, one of which is adorned with white fur. Australia Zoo in Queensland said the koala joey does not have albinism, where there is an absence of pigment in the skin, fur and eyes, but rather her white fur is caused by a recessive gene, thought to be inherited from the koala’s mother named Tia, who has had other pale-coloured joeys in the past.','2017-09-13 12:12:24', 'public/article_images/green4.jpg' );
237INSERT INTO "article" (community_id,user_id,title,content,posted_date,image) VALUES (1,1,'You can lower your blood pressure just by petting your pup!','Recent research on the human-animal bond has proved there is genuine chemistry between dogs and their owners. Daily interactions with your favorite furry companion have a measurably positive effect on your biochemistry, thanks to a hormone called oxytocin.','2010-01-21 10:27:34','public/article_images/green5.jpg'  );
238
239INSERT INTO "votes" (article_id,user_id,type) VALUES (1,1,1);
240INSERT INTO "votes" (article_id,user_id,type) VALUES (1,2,-1);
241INSERT INTO "votes" (article_id,user_id,type) VALUES (1,3,1);
242INSERT INTO "votes" (article_id,user_id,type) VALUES (1,4,1);
243INSERT INTO "votes" (article_id,user_id,type) VALUES (2,5,-1);
244INSERT INTO "votes" (article_id,user_id,type) VALUES (2,1,1);
245INSERT INTO "votes" (article_id,user_id,type) VALUES (2,2,1);
246INSERT INTO "votes" (article_id,user_id,type) VALUES (2,3,1);
247INSERT INTO "votes" (article_id,user_id,type) VALUES (3,4,1);
248INSERT INTO "votes" (article_id,user_id,type) VALUES (4,1,1);
249INSERT INTO "votes" (article_id,user_id,type) VALUES (4,2,1);
250INSERT INTO "votes" (article_id,user_id,type) VALUES (4,3,1);
251INSERT INTO "votes" (article_id,user_id,type) VALUES (4,4,1);
252INSERT INTO "votes" (article_id,user_id,type) VALUES (5,5,1);
253INSERT INTO "votes" (article_id,user_id,type) VALUES (6,1,1);
254INSERT INTO "votes" (article_id,user_id,type) VALUES (7,2,1);
255INSERT INTO "votes" (article_id,user_id,type) VALUES (7,3,1);
256INSERT INTO "votes" (article_id,user_id,type) VALUES (7,4,1);
257INSERT INTO "votes" (article_id,user_id,type) VALUES (8,5,1);
258INSERT INTO "votes" (article_id,user_id,type) VALUES (9,1,1);
259INSERT INTO "votes" (article_id,user_id,type) VALUES (9,2,1);
260INSERT INTO "votes" (article_id,user_id,type) VALUES (10,3,1);
261INSERT INTO "votes" (article_id,user_id,type) VALUES (10,4,1);
262INSERT INTO "votes" (article_id,user_id,type) VALUES (11,5,1);
263INSERT INTO "votes" (article_id,user_id,type) VALUES (12,1,1);
264
265
266INSERT INTO "article" (community_id,user_id,title,content,posted_date,image) VALUES (2,5,'World Water Day 2018 – Nature for Water','World Water Day, on 22 March every year, is about focusing attention on the importance of water. This year’s theme, ‘Nature for Water’, explores nature-based solutions to the water challenges we face in the 21st century.','2018-03-22 10:10:10', 'public/article_images/water1.jpg');
267INSERT INTO "article" (community_id,user_id,title,content,posted_date,image) VALUES (2,1,'The 28th UN-Water Meeting','The 28th UN-Water Meeting was convened at the headquarters of the International Fund for Agricultural Development (IFAD) in early February.','2013-02-12 18:18:10', 'public/article_images/water2.jpg'  );
268INSERT INTO "article" (community_id,user_id,title,content,posted_date,image) VALUES (2,2,'What is Water?','It is a transparent liquid that has no color, smell or taste. It is the basis of all living things. It is made up of two scientific elements hydrogen and oxygen; its chemical formula is H2O','2018-03-22 10:10:10', 'public/article_images/water3.jpg' );
269INSERT INTO "article" (community_id,user_id,title,content,posted_date,image) VALUES (2,2,'What is water pollution?','It is the contamination of water at source; seas, oceans, rivers, lake, reservoirs and wells. Water pollution is considered to be the biggest cause of deaths and diseases worldwide.','2017-09-22 10:10:10', 'public/article_images/water4.jpg' );
270
271INSERT INTO "article" (community_id,user_id,title,content,posted_date,image) VALUES (3,5,'Bird’s saliva is actually an expensive delicacy','Forget caviar and expensive truffles, bird’s saliva is the food of the well-heeled, at least in China anyway. Bird’s nest soup, is an expensive delicacy made from rare bird’s nests created from the saliva of small swiftlets. The nests, which have been used in Chinese cooking for over four centuries, are dissolved in water to make a soup which is believed to have exquisite flavour and be of benefit to health. These bird nests are considered to be one of the most expensive animal food products consumed by humans.','2018-04-22 18:10:10', 'public/article_images/food1.jpg' );
272INSERT INTO "article" (community_id,user_id,title,content,posted_date,image) VALUES (3,2,'Your food is allowed to contain trace of insects','You could be forgiven for thinking that when you buy your food that it will be bug-free and that the law would ensure that it stays that way. Well, unfortunately not. It seems that some of these elements are allowable (and often unavoidable) in small quantities.','2017-09-22 10:10:10','public/article_images/food2.jpg'  );
273INSERT INTO "article" (community_id,user_id,title,content,posted_date,image) VALUES (3,3,'Chewing coffee beans can help eliminate bad breath','Been overdoing it on the garlic and needing to rid yourself of the smell on your breath? Those of you who choose to chew on some gum or even some extra strong mints are doing it wrong as the answer apparently lies in the coffee bean. Yes, chewing on the roasted coffee beans can go some way to ridding you of your garlic or onion breath. ','2018-02-22 10:10:10','public/article_images/food3.jpg'  );