FtyqFmRT

1DROP TABLE IF EXISTS annex CASCADE;
2DROP TABLE IF EXISTS clinical_case CASCADE;
3DROP TABLE IF EXISTS comment CASCADE;
4DROP TABLE IF EXISTS doctor CASCADE;
5DROP TABLE IF EXISTS external_account CASCADE;
6DROP TABLE IF EXISTS follow_request CASCADE;
7DROP TABLE IF EXISTS friendship_request CASCADE;
8DROP TABLE IF EXISTS hospital CASCADE;
9DROP TABLE IF EXISTS likes_clinical_case CASCADE;
10DROP TABLE IF EXISTS likes_comment CASCADE;
11DROP TABLE IF EXISTS notifications CASCADE;
12DROP TABLE IF EXISTS private_message CASCADE;
13DROP TABLE IF EXISTS report CASCADE;
14DROP TABLE IF EXISTS specialty CASCADE;
15DROP TABLE IF EXISTS tag CASCADE;
16DROP TABLE IF EXISTS tag_clinical_case CASCADE;
17DROP TABLE IF EXISTS user_account CASCADE;
18
19
20CREATE DOMAIN priority_case AS TEXT
21CHECK(
22   VALUE ~ 'High'
23OR VALUE ~ 'Medium'
24OR VALUE ~ 'Low'
25);
26
27CREATE DOMAIN type_file AS TEXT
28CHECK(
29   VALUE ~ 'Imagem'
30OR VALUE ~ 'Anexo'
31);
32
33CREATE DOMAIN type_acc AS TEXT
34CHECK(
35   VALUE ~ 'Facebook'
36OR VALUE ~ 'Linkedin'
37OR VALUE ~ 'Google'
38);
39
40    -- Tables
41    CREATE TABLE doctor( id SERIAL,
42    name text NOT NULL,
43    cedule_nr INTEGER NOT NULL,
44    birthdate date,
45    age INTEGER,
46    email text NOT NULL,
47    phone_nr INTEGER NOT NULL,
48    path_photo text NOT NULL,
49    about text,
50    id_specialty INTEGER NOT NULL,
51    id_hospital INTEGER NOT NULL,
52    remember_token VARCHAR
53        );
54
55    CREATE TABLE user_account(
56    id_doctor INTEGER NOT NULL,
57    password text NOT NULL,
58    verified boolean NOT NULL,
59    verification_code text
60        );
61
62    CREATE TABLE external_account(
63    id_doctor INTEGER NOT NULL,
64    id_external_site INTEGER NOT NULL,
65    ACC_TYPE type_acc NOT NULL
66        );
67
68    CREATE TABLE hospital (
69    id SERIAL,
70    name text NOT NULL
71        );
72
73    CREATE TABLE friendship_request(
74    id SERIAL,
75    "date" TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
76    accepted boolean NOT NULL,
77    id_doctor_sends INTEGER NOT NULL,
78    id_doctor_gets INTEGER NOT NULL
79        );
80
81    CREATE TABLE comment (
82    id SERIAL,
83    "date" TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
84    n_likes INTEGER DEFAULT 0 ,
85    n_reports INTEGER CHECK (n_reports >=0) DEFAULT 0,
86    content text NOT NULL,
87    id_doctor INTEGER NOT NULL,
88    id_clinical_case INTEGER NOT NULL
89        );
90
91    CREATE TABLE clinical_case (
92    id SERIAL,
93    "date" TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
94    title text NOT NULL,
95    description text NOT NULL,
96    state boolean NOT NULL,
97    n_comments INTEGER CHECK (n_comments >= 0) DEFAULT 0,
98    n_likes INTEGER DEFAULT 0,
99    n_reports INTEGER CHECK (n_reports >= 0) DEFAULT 0,
100    priority priority_case NOT NULL,
101    visibility boolean NOT NULL,
102    id_doctor INTEGER NOT NULL
103        );
104
105    CREATE TABLE specialty (
106    id SERIAL,
107    name text NOT NULL
108        );
109
110    CREATE TABLE annex (
111    id SERIAL,
112    path text NOT NULL,
113    "date" TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
114    id_clinical_case INTEGER,
115    id_comment INTEGER,
116    id_message INTEGER,
117    typefile type_file NOT NULL
118    );
119
120    CREATE TABLE tag (
121    id SERIAL,
122    name text NOT NULL
123        );
124
125    CREATE TABLE private_message (
126    id SERIAL,
127    content text NOT NULL,
128    "date" TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
129    "read" boolean DEFAULT false,
130    id_doctor_sends INTEGER NOT NULL,
131    id_doctor_gets INTEGER NOT NULL
132        );
133
134    CREATE TABLE report (
135    id SERIAL,
136    "date" TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
137    content text NOT NULL,
138    id_clinical_case INTEGER,
139    id_comment INTEGER
140        );
141
142    CREATE TABLE follow_request (
143    id SERIAL NOT NULL,
144    "date" TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
145    accepted boolean NOT NULL,
146    id_doctor INTEGER NOT NULL,
147    id_clinical_case INTEGER NOT NULL
148        );
149
150    CREATE TABLE notifications (
151    id SERIAL,
152    "date" TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
153    read boolean DEFAULT false,
154    id_doctor INTEGER NOT NULL,
155    id_friend_request INTEGER,
156    id_follow_request INTEGER,
157    id_comment INTEGER
158        );
159
160    CREATE TABLE tag_clinical_case (
161    id_tag INTEGER,
162    id_clinical_case INTEGER
163        );
164
165    CREATE TABLE likes_comments (
166    id_comment INTEGER,
167    id_doctor INTEGER,
168    has_like boolean NOT NULL
169    );
170
171    CREATE TABLE likes_clinical_cases (
172    id_clinical_case INTEGER,
173    id_doctor INTEGER
174        );
175
176    -- Primary keys
177-- Primary Keys and Uniques
178
179ALTER TABLE ONLY doctor
180    ADD CONSTRAINT doctor_pkey PRIMARY KEY (id);
181
182ALTER TABLE ONLY doctor
183    ADD CONSTRAINT doctor_email_key UNIQUE (email);
184
185ALTER TABLE ONLY doctor
186    ADD CONSTRAINT doctor_phonenr_key UNIQUE (phone_nr);
187
188ALTER TABLE ONLY doctor
189    ADD CONSTRAINT doctor_cedulenr_key UNIQUE (cedule_nr);
190
191ALTER TABLE ONLY doctor
192    ADD CONSTRAINT doctor_photopath_key UNIQUE (path_photo);
193
194ALTER TABLE ONLY user_account
195    ADD CONSTRAINT useraccount_pkey PRIMARY KEY (id_doctor);
196
197ALTER TABLE ONLY external_account
198    ADD CONSTRAINT externalaccount_pkey PRIMARY KEY (id_doctor);
199
200ALTER TABLE ONLY external_account
201    ADD CONSTRAINT externalaccount_idexternalsite_key UNIQUE (id_external_site);
202
203ALTER TABLE ONLY hospital
204    ADD CONSTRAINT hospital_pkey PRIMARY KEY (id);
205
206ALTER TABLE ONLY friendship_request
207    ADD CONSTRAINT friendship_request_pkey PRIMARY KEY (id);
208
209ALTER TABLE ONLY comment
210    ADD CONSTRAINT comment_pkey PRIMARY KEY (id);
211
212ALTER TABLE ONLY clinical_case
213    ADD CONSTRAINT clinical_case_pkey PRIMARY KEY (id);
214
215ALTER TABLE ONLY specialty
216    ADD CONSTRAINT specialty_pkey PRIMARY KEY (id);
217
218ALTER TABLE ONLY annex
219    ADD CONSTRAINT annex_pkey PRIMARY KEY (id);
220
221ALTER TABLE ONLY tag
222    ADD CONSTRAINT tag_pkey PRIMARY KEY (id);
223
224ALTER TABLE ONLY private_message
225    ADD CONSTRAINT private_message_pkey PRIMARY KEY (id);
226
227ALTER TABLE ONLY report
228    ADD CONSTRAINT report_pkey PRIMARY KEY (id);
229
230ALTER TABLE ONLY follow_request
231    ADD CONSTRAINT follow_request_pkey PRIMARY KEY (id);
232
233ALTER TABLE ONLY notifications
234    ADD CONSTRAINT notifications_pkey PRIMARY KEY (id);
235
236ALTER TABLE ONLY tag_clinical_case
237    ADD CONSTRAINT tag_clinical_case_pkey PRIMARY KEY (id_tag, id_clinical_case);
238
239ALTER TABLE ONLY likes_comments
240    ADD CONSTRAINT likes_comments_pkey PRIMARY KEY (id_comment, id_doctor);
241
242ALTER TABLE ONLY likes_clinical_cases
243    ADD CONSTRAINT likes_clinical_cases_pkey PRIMARY KEY (id_clinical_case,id_doctor);
244
245-- Other constraints
246
247-- Only one of the keys in friend request, follow request and comment are non null
248ALTER TABLE notifications
249ADD CONSTRAINT one_notnull_notifications CHECK (
250  (CASE WHEN id_friend_request IS NULL THEN 0 ELSE 1 END) +
251  (CASE WHEN id_follow_request IS NULL THEN 0 ELSE 1 END) +
252  (CASE WHEN id_comment IS NULL THEN 0 ELSE 1 END) = 1);
253
254-- Same for annex
255ALTER TABLE annex
256ADD CONSTRAINT one_notnull_annex CHECK (
257  (CASE WHEN id_comment IS NULL THEN 0 ELSE 1 END) +
258  (CASE WHEN id_clinical_case IS NULL THEN 0 ELSE 1 END) +
259  (CASE WHEN id_message IS NULL THEN 0 ELSE 1 END) = 1);
260
261
262-- Same for reports
263ALTER TABLE report
264ADD CONSTRAINT one_notnull_report CHECK (
265  (CASE WHEN id_comment IS NULL THEN 0 ELSE 1 END) +
266  (CASE WHEN id_clinical_case IS NULL THEN 0 ELSE 1 END) = 1);
267
268  -- Id_receive and id_send mustn't be equal, in friend request
269
270ALTER TABLE friendship_request
271  ADD CONSTRAINT friend_send_cannot_be_equal_to_receive_CHK
272    CHECK (id_doctor_gets <> id_doctor_sends) ;
273
274
275    -- Foreign keys
276
277-- Doctor
278ALTER TABLE ONLY doctor
279    ADD CONSTRAINT doctor_id_specialty_fkey FOREIGN KEY (id_specialty) REFERENCES specialty(id) ON UPDATE CASCADE;
280
281ALTER TABLE ONLY doctor
282    ADD CONSTRAINT doctor_id_hospital_fkey FOREIGN KEY (id_hospital) REFERENCES hospital(id) ON UPDATE CASCADE;
283
284-- User account
285ALTER TABLE ONLY user_account
286    ADD CONSTRAINT useraccount_id_doctor_fkey FOREIGN KEY (id_doctor) REFERENCES doctor(id) ON UPDATE CASCADE;
287
288ALTER TABLE ONLY external_account
289    ADD CONSTRAINT externalaccount_id_doctor_fkey FOREIGN KEY (id_doctor) REFERENCES doctor(id) ON UPDATE CASCADE;
290
291-- Friendship request
292ALTER TABLE ONLY friendship_request
293    ADD CONSTRAINT friendship_request_id_doctor_sends_fkey FOREIGN KEY (id_doctor_sends) REFERENCES doctor(id) ON UPDATE CASCADE;
294
295ALTER TABLE ONLY friendship_request
296    ADD CONSTRAINT friendship_request_id_doctor_getss_fkey FOREIGN KEY (id_doctor_gets) REFERENCES doctor(id) ON UPDATE CASCADE;
297
298-- Comment
299ALTER TABLE ONLY comment
300    ADD CONSTRAINT comment_id_doctor_fkey FOREIGN KEY (id_doctor) REFERENCES doctor(id) ON UPDATE CASCADE;
301
302 ALTER TABLE ONLY comment
303    ADD CONSTRAINT comment_id_clinical_case_fkey FOREIGN KEY (id_clinical_case) REFERENCES clinical_case(id) ON UPDATE CASCADE;
304
305-- Clinical case
306 ALTER TABLE ONLY clinical_case
307    ADD CONSTRAINT clinical_case_id_doctor_fkey FOREIGN KEY (id_doctor) REFERENCES doctor(id) ON UPDATE CASCADE;
308
309-- Annex
310 ALTER TABLE ONLY annex
311    ADD CONSTRAINT annex_id_clinical_case_fkey FOREIGN KEY (id_clinical_case) REFERENCES clinical_case(id) ON UPDATE CASCADE;
312
313 ALTER TABLE ONLY annex
314    ADD CONSTRAINT annex_id_comment_fkey FOREIGN KEY (id_comment) REFERENCES comment(id) ON UPDATE CASCADE;
315
316 ALTER TABLE ONLY annex
317    ADD CONSTRAINT annex_id_message_fkey FOREIGN KEY (id_message) REFERENCES private_message(id) ON UPDATE CASCADE;
318
319-- Private message
320ALTER TABLE ONLY private_message
321   ADD CONSTRAINT private_message_id_doctor_sends_fkey FOREIGN KEY (id_doctor_sends) REFERENCES doctor(id) ON UPDATE CASCADE;
322
323ALTER TABLE ONLY private_message
324   ADD CONSTRAINT private_message_id__doctor_gets_fkey FOREIGN KEY (id_doctor_gets) REFERENCES doctor(id) ON UPDATE CASCADE;
325
326-- Report
327ALTER TABLE ONLY report
328   ADD CONSTRAINT report_id_clinical_case_fkey FOREIGN KEY (id_clinical_case) REFERENCES clinical_case(id) ON UPDATE CASCADE;
329
330ALTER TABLE ONLY report
331   ADD CONSTRAINT report_id_comment_fkey FOREIGN KEY (id_comment) REFERENCES comment(id) ON UPDATE CASCADE;
332
333-- Follow clinical case request
334ALTER TABLE ONLY follow_request
335   ADD CONSTRAINT follow_request_id_doctor_fkey FOREIGN KEY (id_doctor) REFERENCES doctor(id) ON UPDATE CASCADE;
336
337ALTER TABLE ONLY follow_request
338   ADD CONSTRAINT follow_request_id_clinical_case_fkey FOREIGN KEY (id_clinical_case) REFERENCES clinical_case(id) ON UPDATE CASCADE;
339
340-- Notifications
341ALTER TABLE ONLY notifications
342   ADD CONSTRAINT notifications_id_doctor_fkey FOREIGN KEY (id_doctor) REFERENCES doctor(id) ON UPDATE CASCADE;
343
344ALTER TABLE ONLY notifications
345   ADD CONSTRAINT notifications_id_followrequest_fkey FOREIGN KEY (id_follow_request) REFERENCES follow_request(id) ON UPDATE CASCADE;
346
347ALTER TABLE ONLY notifications
348   ADD CONSTRAINT notifications_id_friendrequest_fkey FOREIGN KEY (id_friend_request) REFERENCES friendship_request(id) ON UPDATE CASCADE;
349
350ALTER TABLE ONLY notifications
351   ADD CONSTRAINT notifications_id_comment_fkey FOREIGN KEY (id_comment) REFERENCES comment(id) ON UPDATE CASCADE;
352
353-- Tag clinical case
354ALTER TABLE ONLY tag_clinical_case
355   ADD CONSTRAINT tag_clinical_case_id_tag_fkey FOREIGN KEY (id_tag) REFERENCES tag(id) ON UPDATE CASCADE;
356
357ALTER TABLE ONLY tag_clinical_case
358   ADD CONSTRAINT tag_clinical_case_id_clinical_case_fkey FOREIGN KEY (id_clinical_case) REFERENCES clinical_case(id) ON UPDATE CASCADE ON DELETE CASCADE;
359
360-- Likes comments
361ALTER TABLE ONLY likes_comments
362   ADD CONSTRAINT likes_comments_id_comment_fkey FOREIGN KEY (id_comment) REFERENCES comment(id) ON UPDATE CASCADE ON DELETE CASCADE;
363
364ALTER TABLE ONLY likes_comments
365   ADD CONSTRAINT likes_comments_id_doctor_fkey FOREIGN KEY (id_doctor) REFERENCES doctor(id) ON UPDATE CASCADE ON DELETE CASCADE;
366
367-- Likes clinical cases
368ALTER TABLE ONLY likes_clinical_cases
369   ADD CONSTRAINT likes_clinical_cases_id_clinical_case FOREIGN KEY (id_clinical_case) REFERENCES clinical_case(id) ON UPDATE CASCADE;
370
371ALTER TABLE ONLY likes_clinical_cases
372   ADD CONSTRAINT likes_clinical_cases_id_doctor FOREIGN KEY (id_doctor) REFERENCES doctor(id) ON UPDATE CASCADE;
373
374 -- Trigger 01
375CREATE FUNCTION follow_case() RETURNS TRIGGER AS
376$BODY$
377BEGIN
378    IF EXISTS (SELECT *
379    FROM clinical_case JOIN follow_request ON clinical_case.id=follow_request.id_clinical_case
380    WHERE NEW.id_clinical_case=id_clinical_case AND NEW.id_doctor=follow_request.id_doctor AND accepted=true )
381THEN
382    RAISE EXCEPTION 'A user cannot follow the same clinical case twice.';
383END IF;
384  RETURN NEW;
385END
386$BODY$
387LANGUAGE plpgsql;
388
389CREATE TRIGGER follow_case
390  BEFORE INSERT ON follow_request
391  FOR EACH ROW
392    EXECUTE PROCEDURE follow_case();
393
394-- INSERT INTO "follow_request" (accepted, id_doctor, id_clinical_case) VALUES (TRUE, 5, 1)
395
396-- TRIGGER 02
397CREATE FUNCTION check_friendship_pair() RETURNS TRIGGER AS
398$BODY$
399BEGIN
400  IF EXISTS (SELECT * FROM friendship_request WHERE NEW.id_doctor_sends=id_doctor_sends AND NEW.id_doctor_gets=id_doctor_gets AND accepted=true
401UNION
402SELECT * FROM friendship_request WHERE NEW.id_doctor_gets=id_doctor_sends AND NEW.id_doctor_sends=id_doctor_gets AND accepted=true
403 ) THEN
404    RAISE EXCEPTION 'A user cannot be friend of someone twice';
405  END IF;
406  RETURN NEW;
407END
408$BODY$
409LANGUAGE plpgsql;
410
411CREATE TRIGGER check_friendship_pair
412  BEFORE INSERT ON friendship_request
413  FOR EACH ROW
414    EXECUTE PROCEDURE check_friendship_pair();
415-- INSERT INTO "friendship_request" (accepted, id_doctor_sends, id_doctor_gets) VALUES (TRUE, 15, 11)
416
417-- Trigger 03
418CREATE FUNCTION if_friend_follow_request() RETURNS TRIGGER AS
419$BODY$
420BEGIN
421  IF EXISTS (SELECT * FROM friendship_request WHERE id_doctor_sends=NEW.id_doctor AND id_doctor_gets=(SELECT id_doctor FROM clinical_case WHERE id=NEW.id_clinical_case) AND accepted=true
422UNION
423SELECT * FROM friendship_request WHERE id_doctor_gets=NEW.id_doctor AND id_doctor_sends=(SELECT id_doctor FROM clinical_case WHERE id=NEW.id_clinical_case) AND accepted=true
424 ) THEN
425    UPDATE follow_request SET accepted=TRUE;
426INSERT INTO notifications(read, id_doctor, id_follow_request) VALUES (FALSE, NEW.id_doctor, NEW.id);
427  END IF;
428  RETURN NEW;
429END
430$BODY$
431LANGUAGE plpgsql;
432
433CREATE TRIGGER if_friend_follow_request
434  AFTER INSERT ON follow_request
435  FOR EACH ROW
436    EXECUTE PROCEDURE if_friend_follow_request();
437
438-- INSERT INTO "follow_request" (accepted, id_doctor, id_clinical_case) VALUES (FALSE, 5, 1)
439
440-- Trigger 04
441CREATE FUNCTION check_friendship_request() RETURNS TRIGGER AS
442$BODY$
443BEGIN
444  IF EXISTS (SELECT * FROM friendship_request WHERE NEW.id_doctor_sends=id_doctor_sends AND NEW.id_doctor_gets=id_doctor_gets AND accepted=false
445UNION
446SELECT * FROM friendship_request WHERE NEW.id_doctor_gets=id_doctor_sends AND NEW.id_doctor_sends=id_doctor_gets AND accepted=false
447 ) THEN
448    UPDATE friendship_request
449    SET date=now()
450     WHERE ((NEW.id_doctor_sends=id_doctor_sends AND NEW.id_doctor_gets=id_doctor_gets) OR (NEW.id_doctor_gets=id_doctor_sends AND NEW.id_doctor_sends=id_doctor_gets));
451  END IF;
452  RETURN NULL;
453
454END
455$BODY$
456LANGUAGE plpgsql;
457
458CREATE TRIGGER check_friendship_request
459  BEFORE INSERT ON friendship_request
460  FOR EACH ROW
461    EXECUTE PROCEDURE check_friendship_request();
462
463-- INSERT INTO "friendship_request" (accepted, id_doctor_sends, id_doctor_gets) VALUES (FALSE, 1, 2)
464
465--Trigger 05
466CREATE FUNCTION check_follow_request() RETURNS TRIGGER AS
467$BODY$
468BEGIN
469   IF EXISTS (SELECT * FROM clinical_case JOIN follow_request ON clinical_case.id=follow_request.id_clinical_case WHERE NEW.id_clinical_case=id_clinical_case AND  NEW.id_doctor=follow_request.id_doctor AND accepted=false )
470   THEN
471   UPDATE follow_request
472    SET date=now()
473    WHERE id_clinical_case=NEW.id_clinical_case AND follow_request.id_doctor=NEW.id_doctor;
474
475UPDATE notifications SET date=now() WHERE (id_doctor=(SELECT id_doctor FROM clinical_case where id=NEW.id_clinical_case) AND id_follow_request=(SELECT id FROM follow_request WHERE id_doctor=NEW.id_doctor AND id_clinical_case=NEW.id_clinical_case));
476
477  END IF;
478  RETURN NULL;
479END
480$BODY$
481LANGUAGE plpgsql;
482
483CREATE TRIGGER check_follow_request
484  BEFORE INSERT ON follow_request
485  FOR EACH ROW
486    EXECUTE PROCEDURE check_follow_request();
487
488-- to test: INSERT INTO "follow_request" (accepted, id_doctor, id_clinical_case) VALUES (FALSE, 6, 3)
489-- notification needs to have id_doctor=41 and id_follow_request=15
490
491-- Trigger 06
492CREATE FUNCTION check_likes_clinical_cases() RETURNS TRIGGER AS
493$BODY$
494BEGIN
495IF (NEW.id_doctor=(SELECT id_doctor FROM clinical_case WHERE id=NEW.id_clinical_case)) THEN
496   RAISE EXCEPTION 'A user cannot like his own clinical case.';
497 END IF;
498 RETURN NEW;
499END
500$BODY$
501LANGUAGE plpgsql;
502CREATE TRIGGER check_likes
503 BEFORE INSERT OR UPDATE ON likes_clinical_cases
504 FOR EACH ROW
505   EXECUTE PROCEDURE check_likes_clinical_cases();
506
507-- INSERT INTO likes_clinical_cases (id_clinical_case, id_doctor) VALUES (1, 18)
508
509-- Trigger 07
510CREATE FUNCTION check_likes_comments() RETURNS TRIGGER AS
511$BODY$
512BEGIN
513IF (NEW.id_doctor=(SELECT id_doctor FROM comment WHERE id=NEW.id_comment)) THEN
514   RAISE EXCEPTION 'A user cannot like his own comment.';
515 END IF;
516 RETURN NEW;
517END
518$BODY$
519LANGUAGE plpgsql;
520CREATE TRIGGER check_likes_comments
521 BEFORE INSERT OR UPDATE ON likes_comments
522 FOR EACH ROW
523   EXECUTE PROCEDURE check_likes_comments();
524
525-- Trigger 08
526CREATE FUNCTION follow_user_own_case() RETURNS TRIGGER AS
527$BODY$
528BEGIN
529IF (NEW.id_doctor=(SELECT id_doctor FROM clinical_case WHERE id=NEW.id_clinical_case))
530THEN
531    RAISE EXCEPTION 'A user cannot like his own clinical case.';
532  END IF;
533  RETURN NEW;
534END
535$BODY$
536LANGUAGE plpgsql;
537
538CREATE TRIGGER follow_user_own_case
539  BEFORE INSERT ON likes_clinical_cases
540  FOR EACH ROW
541    EXECUTE PROCEDURE follow_user_own_case();
542
543-- Indexes
544CREATE INDEX id_doct ON clinical_case USING hash (id_doctor);
545CREATE INDEX id_doc_sends ON private_message USING hash (id_doctor_sends);
546CREATE INDEX id_doc_gets ON private_message USING hash (id_doctor_gets);
547CREATE INDEX id_clinical_comments ON comment USING hash (id_clinical_case);
548CREATE INDEX id_doc_notified ON notifications USING btree (id_doctor);
549CREATE INDEX id_cc_attachment ON annex USING hash (id_clinical_case);
550
551-- INSERT INTO likes_clinical_cases (id_clinical_case, id_doctor) VALUES (1, 18)
552
553-- Populating the database
554
555INSERT INTO specialty (name) VALUES ('Pediatrics'),('Psychiatry'),('Anesthesiology'),('Internal Medicine'),('Neurology'),('Obstetrics&Gynecology'),('Surgery-General'),('Urology'),('Otolaryngology'),('Orthopaedic Surgery'),('Ophtalmology'),('Other');
556
557INSERT INTO "hospital" (name) VALUES ('Hospital de S.João'),('Hospital St.António'),('Hospital St. Maria '),('Hospital de Gaia'),('Hospital de Jesus');
558
559INSERT INTO "tag" (name) VALUES ('Pediatrics'),('Psychiatry'),('Anesthesiology'),('Internal Medicine'),('Neurology'),('Obstetrics&Gynecology'),('Surgery-General'),('Urology'),('Otolaryngology'),('Orthopaedic Surgery'),('Ophtalmology'),('Other');