AaT3D2bT

1DROP TABLE if exists community CASCADE;
2DROP TABLE if exists users CASCADE;
3DROP TABLE if exists article CASCADE;
4DROP TABLE if exists comment CASCADE;
5DROP TABLE if exists tag CASCADE;
6DROP TABLE if exists saved CASCADE;
7DROP TABLE if exists tag_saved CASCADE;
8DROP TABLE if exists message CASCADE;
9DROP TABLE if exists notification CASCADE;
10DROP TABLE if exists member_private CASCADE;
11DROP TABLE if exists member_status CASCADE;
12DROP TABLE if exists admin CASCADE;
13DROP TABLE if exists follow CASCADE;
14DROP TABLE if exists private CASCADE;
15DROP TABLE if exists votes CASCADE;
16
17
18CREATE TABLE community (
19
20  id SERIAL,
21  name text UNIQUE NOT NULL,
22  wiki text,
23  image text NOT NULL DEFAULT 'public/community_images/gray1.jpeg',
24
25  CONSTRAINT community_pk PRIMARY KEY (id)
26);
27
28
29
30CREATE TABLE users (
31
32  id SERIAL,
33  name text NOT NULL,
34
35  password VARCHAR NOT NULL,
36
37  email text UNIQUE NOT NULL,
38  username text UNIQUE NOT NULL,
39  date_joined TIMESTAMP WITH TIME zone DEFAULT now(),
40
41  description text,
42  is_deleted INTEGER DEFAULT 0,
43
44  photo text DEFAULT '0',
45
46  CONSTRAINT user_pk PRIMARY KEY (id)
47);
48
49CREATE INDEX user_name ON users USING hash (username);
50
51
52CREATE TABLE article (
53
54  id SERIAL,
55  community_id INTEGER NOT NULL,
56  user_id INTEGER NOT NULL,
57  title text NOT NULL,
58  content text NOT NULL,
59  posted_date TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
60
61  image text NOT NULL DEFAULT 'public/article_images/gray2.jpeg',
62
63  CONSTRAINT article_pk PRIMARY KEY (id),
64  CONSTRAINT id_community_fk FOREIGN KEY (community_id) REFERENCES community(id) ON UPDATE CASCADE,
65  CONSTRAINT id_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE
66);
67
68
69CREATE TABLE votes(
70
71  id SERIAL,
72  article_id INTEGER NOT NULL,
73  user_id INTEGER NOT NULL,
74  type INTEGER NOT NULL,
75
76  CONSTRAINT votes_pk PRIMARY KEY (id),
77  CONSTRAINT id_article_fk FOREIGN KEY (article_id) REFERENCES article(id) ON UPDATE CASCADE,
78  CONSTRAINT id_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE
79
80);
81
82
83
84
85
86CREATE TABLE comment (
87
88  id SERIAL,
89  article_id INTEGER NOT NULL,
90  user_id INTEGER NOT NULL,
91  content text NOT NULL,
92  posted_date TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
93
94  id_comment INTEGER,   --parent comment
95
96  CONSTRAINT comment_pk PRIMARY KEY (id),
97  CONSTRAINT id_article_fk FOREIGN KEY (article_id) REFERENCES article(id) ON UPDATE CASCADE,
98  CONSTRAINT id_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE,
99  CONSTRAINT id_comment_fk FOREIGN KEY (id_comment) REFERENCES comment(id) ON UPDATE CASCADE
100);
101
102
103
104CREATE TABLE saved (
105  id_user INTEGER,
106  id_article INTEGER,
107
108  CONSTRAINT saved_pk PRIMARY KEY (id_user, id_article),
109  CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES users(id) ON UPDATE CASCADE,
110  CONSTRAINT id_article_fk FOREIGN KEY (id_article) REFERENCES article(id) ON UPDATE CASCADE
111);
112
113
114
115CREATE TABLE tag (
116
117  id SERIAL,
118  name text NOT NULL,
119
120  CONSTRAINT tag_pk PRIMARY KEY (id)
121);
122
123
124
125CREATE TABLE tag_saved (
126
127  id_tag INTEGER,
128  id_saved_user INTEGER,
129  id_saved_article INTEGER,
130
131  CONSTRAINT tag_saved_pk PRIMARY KEY (id_tag, id_saved_user, id_saved_article),
132  CONSTRAINT id_tag_fk FOREIGN KEY (id_tag) REFERENCES tag(id) ON UPDATE CASCADE,
133  CONSTRAINT id_saved_fk FOREIGN KEY (id_saved_user, id_saved_article) REFERENCES saved(id_user, id_article) ON UPDATE CASCADE
134);
135
136
137
138CREATE TABLE message (
139
140  id SERIAL,
141  id_user_to INTEGER NOT NULL,
142  id_user_from INTEGER NOT NULL,
143  content text NOT NULL,
144
145  CONSTRAINT message_pk PRIMARY KEY (id),
146  CONSTRAINT id_user_to_fk FOREIGN KEY (id_user_to) REFERENCES users(id) ON UPDATE CASCADE,
147  CONSTRAINT id_user_from_fk FOREIGN KEY (id_user_from) REFERENCES users(id) ON UPDATE CASCADE
148);
149
150
151CREATE TABLE notification (
152
153  id SERIAL,
154  content text,
155  id_user INTEGER NOT NULL,
156  created_at TIMESTAMP WITH TIME zone,
157
158  CONSTRAINT notification_pk PRIMARY KEY (id),
159  CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES users(id) ON UPDATE CASCADE
160);
161
162
163CREATE TABLE follow (
164
165  id_user_follower INTEGER,
166  id_user_followed INTEGER,
167
168  CONSTRAINT follow_pk PRIMARY KEY (id_user_follower, id_user_followed),
169  CONSTRAINT id_follower_fk FOREIGN KEY (id_user_follower) REFERENCES users(id) ON UPDATE CASCADE,
170  CONSTRAINT id_followed_fk FOREIGN KEY (id_user_followed) REFERENCES users(id) ON UPDATE CASCADE
171);
172
173
174
175CREATE TABLE admin (
176
177  id_user INTEGER,
178  CONSTRAINT admin_pk PRIMARY KEY (id_user),
179  CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES users(id) ON UPDATE CASCADE
180);
181
182
183CREATE TABLE private (
184
185  community_id INTEGER,
186
187  CONSTRAINT private_community_pk PRIMARY KEY (community_id),
188  CONSTRAINT id_community_fk FOREIGN KEY (community_id) REFERENCES community(id) ON UPDATE CASCADE
189);
190
191
192CREATE TABLE member_private (
193
194  user_id INTEGER,
195  private_id INTEGER,
196  is_approved INTEGER NOT NULL DEFAULT 0,
197
198  CONSTRAINT member_private_pk PRIMARY KEY (user_id, private_id),
199  CONSTRAINT id_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE,
200  CONSTRAINT id_private_fk FOREIGN KEY (private_id) REFERENCES private(community_id) ON UPDATE CASCADE
201);
202
203
204
205CREATE TABLE member_status (
206
207  user_id INTEGER,
208  community_id INTEGER,
209  member_state text NOT NULL ,
210
211  CONSTRAINT status_ck CHECK ((member_state = ANY (ARRAY['banned'::text, 'subscriptor'::text, 'moderator'::text ]))),
212
213  CONSTRAINT member_status_pk PRIMARY KEY (user_id, community_id),
214  CONSTRAINT id_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE,
215  CONSTRAINT id_community_fk FOREIGN KEY (community_id) REFERENCES community(id) ON UPDATE CASCADE
216);
217
218
219INSERT 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);
220INSERT 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);
221INSERT 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);
222INSERT 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);
223INSERT 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);
224
225
226INSERT INTO "community" (name,wiki,image) VALUES ('Green News', 'How do trees access the internet? They log on.', 'public/community_images/greenews.jpg');
227INSERT INTO "community" (name,wiki, image) VALUES ('Water Information','Water you doing out so late tonight?', 'public/community_images/water.jpg');
228INSERT INTO "community" (name,wiki,image) VALUES ('Food News' ,'There’s a cereal killer on the loose.','public/community_images/foodnews.jpg');
229INSERT INTO "community" (name,wiki,image) VALUES ('Sports Reports' ,'Since Ive quit soccer, Ive lost my goal in life','public/community_images/sportsnews.jpg');
230INSERT INTO "community" (name,wiki,image) VALUES ('Political News' ,'On a scale of North Korea to America, how free are you tonight?','public/community_images/politics.jpg');
231
232INSERT 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');
233INSERT 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'  );
234INSERT 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).' , '2018-03-23 02:37:24', 'public/article_images/green3.jpg');
235INSERT 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' );
236INSERT 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.jpeg'  );
237
238
239INSERT 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 years 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');
240INSERT 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');
241INSERT 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' );
242INSERT 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' );
243
244INSERT 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' );
245INSERT 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'  );
246INSERT 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'  );
247
248INSERT INTO "article" (community_id,user_id,title,content,posted_date,image) VALUES (4,4,'Real Madrid não tem espaço para mais taças da champions','nsólito. A zona do Museu do Real Madrid reservada à Liga dos Campeões ainda não recebeu a taça conquistada no sábado, diante do Liverpool, porque... não há espaço para mais troféus. A história parece tirada de um livro de piadas, mas é bem real. Tudo por causa da cadência incrível de conquistas dos merengues, com três Champions de forma consecutiva, que apanhou os responsáveis do clube desprevenidos. Uma história peculiar que começou em 2014, quando em Lisboa conquistaram a mítica Décima. Nesse momento, e para assinalar um feito já de si histórico, os blancos decidiram criar uma vitrina especial para expor as dez taças. O problema veio depois. A de 2015/16 não gerou grandes complicações e foi colocada num espaço livre depois de uma reordenação. A de 2016/17 já obrigou a um esforço adicional, mas lá se encaixou num plano minimamente estético. Só que dali para a frente... o espaço acabou. E ninguém pensou nisso! ','2018-05-28 12:10:10', 'public/article_images/sports1.jpg' );
249INSERT INTO "article" (community_id,user_id,title,content,posted_date,image) VALUES (4,4,'HISTÓRICO! PORTUGAL É CAMPEÃO DA EUROPA','De ‘patinho feio’ a campeão da Europa. Assim é a história de Portugal e de Éder neste Euro2016. As críticas que o jogador e a seleção ouviram acabaram por ser caladas perante 60 mil franceses em pleno Stade de France, com um triunfo sofrido por 1-0, após prolongamento, num jogo onde nem pode praticamente jogar com o seu capitão, Cristiano Ronaldo, que saiu lesionado ainda na primeira parte.
250
251O espírito de sobrevivência e a união entre os jogadores guiou Portugal até este feito, depois de um empate a zero no tempo regulamentar. A França dominou o jogo durante mais tempo, mas a Seleção Nacional demonstrou enorme resistência e coesão para suster o período de maior assédio dos gauleses.','2016-07-10 22:10:10','public/article_images/sport2.jpg'  );
252INSERT INTO "article" (community_id,user_id,title,content,posted_date,image) VALUES (4,1,'Portugal Campeão Europeu de futsal','Em Ljubljana, na Eslovénia, Ricardinho adiantou os portugueses logo nos primeiros minutos da final, mas Espanha, com dois golos, virou o resultado a seu favor no segundo tempo.
253
254Mesmo no limite, a cerca de um minuto do final da partida, Bruno Coelho igualou a contenda, forçando o prolongamento.
255
256Nesta fase, com ambas as equipas a mostrarem respeito mútuo, o sonho português tremeu com a saída por lesão do melhor jogador do Mundo, Ricardinho, mas um livre direto marcado por Bruno Coelho, a menos de um minuto do fim, deu o primeiro título europeu a Portugal. ','2018-02-10 09:10:10','public/article_images/sport3.jpg'  );
257INSERT INTO "article" (community_id,user_id,title,content,posted_date,image) VALUES (4,2,'João Sousa é o primeiro português a conquistar o Estoril Open','Tenista português venceu o americano Frances Tiafoe, por um duplo 6-4, este domingo, na final o open português, no Clube de Ténis do Estoril.
258
259E à quarta tentativa eis que João Sousa conquistou o Estoril Open 2018. O tenista português bateu Frances Tiafoe na final, por duplo 6-4, em mais um jogo empolgante do português.
260
261Pela primeira vez um português ergueu o troféu em casa, com o vimaranense de 29 anos a superar o feito de Fred Gil em 2010, que chegou à final do antigo Estoril Open e perdeu. Sousa junta assim o Estoril Open aos outros dois títulos ATP da carreira: Kuala Lumpur, Malásia, em 2013, e em Valência (2016).','2018-05-06 18:10:10', 'public/article_images/sports4.jpg' );
262
263INSERT INTO "votes" (article_id,user_id,type) VALUES (1,1,1);
264INSERT INTO "votes" (article_id,user_id,type) VALUES (1,2,-1);
265INSERT INTO "votes" (article_id,user_id,type) VALUES (1,3,1);
266INSERT INTO "votes" (article_id,user_id,type) VALUES (1,4,1);
267INSERT INTO "votes" (article_id,user_id,type) VALUES (2,5,-1);
268INSERT INTO "votes" (article_id,user_id,type) VALUES (2,1,1);
269INSERT INTO "votes" (article_id,user_id,type) VALUES (2,2,1);
270INSERT INTO "votes" (article_id,user_id,type) VALUES (2,3,1);
271INSERT INTO "votes" (article_id,user_id,type) VALUES (3,4,1);
272INSERT INTO "votes" (article_id,user_id,type) VALUES (4,1,1);
273INSERT INTO "votes" (article_id,user_id,type) VALUES (4,2,1);
274INSERT INTO "votes" (article_id,user_id,type) VALUES (4,3,1);
275INSERT INTO "votes" (article_id,user_id,type) VALUES (4,4,1);
276INSERT INTO "votes" (article_id,user_id,type) VALUES (5,5,1);
277INSERT INTO "votes" (article_id,user_id,type) VALUES (7,2,1);
278INSERT INTO "votes" (article_id,user_id,type) VALUES (7,3,1);
279INSERT INTO "votes" (article_id,user_id,type) VALUES (7,4,1);
280INSERT INTO "votes" (article_id,user_id,type) VALUES (8,5,1);
281INSERT INTO "votes" (article_id,user_id,type) VALUES (9,1,1);
282INSERT INTO "votes" (article_id,user_id,type) VALUES (9,2,1);
283INSERT INTO "votes" (article_id,user_id,type) VALUES (10,3,1);
284INSERT INTO "votes" (article_id,user_id,type) VALUES (10,4,1);
285INSERT INTO "votes" (article_id,user_id,type) VALUES (11,5,1);
286INSERT INTO "votes" (article_id,user_id,type) VALUES (12,1,1);