xR9dKbnA

1DROP TABLE IF EXISTS "user" CASCADE;
2DROP TABLE IF EXISTS administrator CASCADE;
3DROP TABLE IF EXISTS activity CASCADE;
4DROP TABLE IF EXISTS friend_request CASCADE;
5DROP TABLE IF EXISTS creation CASCADE;
6DROP TABLE IF EXISTS rate CASCADE;
7DROP TABLE IF EXISTS content CASCADE;
8DROP TABLE IF EXISTS post CASCADE;
9DROP TABLE IF EXISTS "comment" CASCADE;
10DROP TABLE IF EXISTS category CASCADE;
11DROP TABLE IF EXISTS notifications CASCADE;
12DROP TABLE IF EXISTS friendship CASCADE;
13DROP TABLE IF EXISTS content_rates CASCADE;
14DROP TABLE IF EXISTS favorite_posts CASCADE;
15DROP TABLE IF EXISTS post_category CASCADE;
16
17CREATE TABLE "user" (
18	id SERIAL PRIMARY KEY,
19    username text NOT NULL,
20    name text NOT NULL,
21    password text NOT NULL,
22	email text NOT NULL,
23	birthdate TIMESTAMP WITH TIME zone,
24	bio text,
25	image text,
26	id_admin_ban INTEGER DEFAULT NULL,
27	CONSTRAINT user_username_key UNIQUE (username),
28	CONSTRAINT user_email_key UNIQUE (email)
29);
30
31CREATE TABLE administrator (
32    id_user INTEGER PRIMARY KEY,
33	CONSTRAINT administrator_id_user_fkey FOREIGN KEY (id_user) REFERENCES "user"(id)
34);
35
36CREATE TABLE activity (
37    id SERIAL PRIMARY KEY,
38	id_user INTEGER NOT NULL,
39	"date" TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
40	CONSTRAINT activity_id_user_fkey FOREIGN KEY (id_user) REFERENCES "user"(id)
41);
42
43CREATE TABLE friend_request (
44    id_activity INTEGER PRIMARY KEY,
45	sender_id INTEGER NOT NULL,
46	receiver_id INTEGER NOT NULL,
47	accept_date TIMESTAMP WITH TIME zone DEFAULT NULL,
48	accepted  BOOLEAN DEFAULT false,
49	rejected  BOOLEAN DEFAULT false,
50	CONSTRAINT friend_request_id_activity_fkey FOREIGN KEY (id_activity) REFERENCES activity(id),
51	CONSTRAINT friend_request_sender_id_fkey FOREIGN KEY (sender_id) REFERENCES "user"(id),
52	CONSTRAINT friend_request_receiver_id_fkey FOREIGN KEY (receiver_id) REFERENCES "user"(id)
53);
54
55CREATE TABLE creation (
56    id_activity INTEGER PRIMARY KEY,
57	id_content INTEGER NOT NULL,
58	CONSTRAINT creation_id_activity_fkey FOREIGN KEY (id_activity) REFERENCES activity(id)
59);
60
61CREATE TABLE rate (
62    id_activity INTEGER PRIMARY KEY,
63	value INTEGER NOT NULL CHECK (((value = 1) OR (value = -1))),
64	id_content INTEGER NOT NULL,
65	CONSTRAINT rate_id_activity_fkey FOREIGN KEY (id_activity) REFERENCES activity(id)
66);
67
68CREATE TABLE content (
69	id SERIAL PRIMARY KEY,
70	body text NOT NULL,
71	creation_date TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
72	last_edition_date TIMESTAMP WITH TIME zone DEFAULT NULL,
73	edited BOOLEAN DEFAULT FALSE,
74	rating INTEGER DEFAULT 0,
75	id_creator INTEGER NOT NULL,
76	CONSTRAINT content_id_creator_fkey FOREIGN KEY (id_creator) REFERENCES "user"(id)
77);
78
79CREATE TABLE post (
80    id_content INTEGER PRIMARY KEY,
81	title text NOT NULL,
82	image text,
83	CONSTRAINT post_id_content_fkey FOREIGN KEY (id_content) REFERENCES content(id)
84);
85
86CREATE TABLE "comment" (
87    id_content INTEGER PRIMARY KEY,
88	id_commented_post  INTEGER NOT NULL,
89	CONSTRAINT comment_id_content_fkey FOREIGN KEY (id_content) REFERENCES content(id),
90	CONSTRAINT comment_id_commented_post_fkey FOREIGN KEY (id_commented_post) REFERENCES post(id_content)
91);
92
93CREATE TABLE category (
94	id SERIAL PRIMARY KEY,
95	name text NOT NULL,
96	CONSTRAINT category_name_key UNIQUE (name)
97);
98
99CREATE TABLE notifications (
100	id_user INTEGER NOT NULL,
101	id_activity INTEGER NOT NULL,
102	PRIMARY KEY(id_user, id_activity),
103	CONSTRAINT notifications_id_user_fkey FOREIGN KEY (id_user) REFERENCES "user"(id),
104	CONSTRAINT notifications_id_activity_fkey FOREIGN KEY (id_activity) REFERENCES activity(id)
105);
106
107CREATE TABLE friendship (
108	id_user1 INTEGER NOT NULL,
109	id_user2 INTEGER NOT NULL,
110	PRIMARY KEY(id_user1, id_user2),
111	CONSTRAINT friendship_id_user1_fkey FOREIGN KEY (id_user1) REFERENCES "user"(id),
112	CONSTRAINT friendship_id_user2_fkey FOREIGN KEY (id_user2) REFERENCES "user"(id)
113);
114
115CREATE TABLE content_rates (
116	id_content INTEGER NOT NULL,
117	id_user INTEGER NOT NULL,
118	PRIMARY KEY(id_content, id_user),
119	CONSTRAINT content_rates_id_content_fkey FOREIGN KEY (id_content) REFERENCES content(id),
120	CONSTRAINT content_rates_id_user_fkey FOREIGN KEY (id_user) REFERENCES "user"(id)
121);
122
123CREATE TABLE favorite_posts (
124	id_post INTEGER NOT NULL, 
125	id_user INTEGER NOT NULL,
126	PRIMARY KEY(id_post, id_user),
127	CONSTRAINT favorite_posts_id_post_fkey FOREIGN KEY (id_post) REFERENCES post(id_content),
128	CONSTRAINT favorite_posts_id_user_fkey FOREIGN KEY (id_user) REFERENCES "user"(id)
129);
130
131CREATE TABLE post_category (
132	id_post INTEGER NOT NULL,
133	id_category INTEGER NOT NULL,
134	PRIMARY KEY(id_post, id_category),
135	CONSTRAINT post_category_id_post_fkey FOREIGN KEY (id_post) REFERENCES post(id_content),
136	CONSTRAINT post_category_id_category_fkey FOREIGN KEY (id_category) REFERENCES category(id)
137);
138
139-- Circular dependencies
140ALTER TABLE "user" ADD CONSTRAINT user_id_admin_ban_fkey FOREIGN KEY (id_admin_ban) REFERENCES administrator(id_user);
141ALTER TABLE creation ADD CONSTRAINT creation_id_content_fkey FOREIGN KEY (id_content) REFERENCES content(id);
142ALTER TABLE rate ADD CONSTRAINT rate_id_content_fkey FOREIGN KEY (id_content) REFERENCES content(id);