P2: schema_creation.2.sql

File schema_creation.2.sql, 9.0 KB (added by 211099, 3 weeks ago)
Line 
1-- Delete tables if they exist
2DROP TABLE IF EXISTS NEED_APPROVAL CASCADE;
3DROP TABLE IF EXISTS NOTIFY CASCADE;
4DROP TABLE IF EXISTS ROLES CASCADE;
5DROP TABLE IF EXISTS PERMISSION_LEVEL CASCADE;
6DROP TABLE IF EXISTS SUGGESTION_TYPE CASCADE;
7DROP TABLE IF EXISTS CONTENT_TYPE CASCADE;
8DROP TABLE IF EXISTS READING_LIST_ITEMS CASCADE;
9DROP TABLE IF EXISTS HAS_GENRE CASCADE;
10DROP TABLE IF EXISTS COLLABORATION CASCADE;
11DROP TABLE IF EXISTS COMMENT CASCADE;
12DROP TABLE IF EXISTS LIKES CASCADE;
13DROP TABLE IF EXISTS AI_SUGGESTION CASCADE;
14DROP TABLE IF EXISTS NOTIFICATION CASCADE;
15DROP TABLE IF EXISTS READING_LIST CASCADE;
16DROP TABLE IF EXISTS CHAPTER CASCADE;
17DROP TABLE IF EXISTS STATUS CASCADE;
18DROP TABLE IF EXISTS STORY CASCADE;
19DROP TABLE IF EXISTS WRITER CASCADE;
20DROP TABLE IF EXISTS REGULAR_USER CASCADE;
21DROP TABLE IF EXISTS ADMINS CASCADE;
22DROP TABLE IF EXISTS GENRE CASCADE;
23DROP TABLE IF EXISTS USERS CASCADE;
24
25-- Tables
26
27CREATE TABLE USERS(
28 user_id SERIAL PRIMARY KEY,
29 username VARCHAR(255) NOT NULL UNIQUE,
30 email VARCHAR(255) NOT NULL UNIQUE,
31 name VARCHAR(100) NOT NULL,
32 surname VARCHAR(100) NOT NULL,
33 password VARCHAR(255) NOT NULL,
34 created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
35 updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
36 CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
37);
38
39CREATE TABLE ADMINS(
40 user_id INTEGER PRIMARY KEY,
41 FOREIGN KEY (user_id) REFERENCES USERS(user_id)
42 ON DELETE CASCADE
43 ON UPDATE CASCADE
44);
45
46CREATE TABLE REGULAR_USER(
47 user_id INTEGER PRIMARY KEY,
48 FOREIGN KEY (user_id) REFERENCES USERS(user_id)
49 ON DELETE CASCADE
50 ON UPDATE CASCADE
51);
52
53CREATE TABLE WRITER(
54 user_id INTEGER PRIMARY KEY,
55 FOREIGN KEY (user_id) REFERENCES USERS(user_id)
56 ON DELETE CASCADE
57 ON UPDATE CASCADE
58);
59
60CREATE TABLE STORY(
61 story_id SERIAL PRIMARY KEY,
62 mature_content BOOLEAN NOT NULL,
63 short_description VARCHAR(500) NOT NULL,
64 image VARCHAR(2048),
65 content TEXT NOT NULL,
66 user_id INTEGER NOT NULL,
67 created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
68 updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
69 FOREIGN KEY (user_id) REFERENCES WRITER(user_id)
70 ON DELETE CASCADE
71 ON UPDATE CASCADE
72);
73
74CREATE TABLE STATUS(
75 story_id INTEGER,
76 status VARCHAR(50) NOT NULL,
77 CONSTRAINT status_pk PRIMARY KEY(story_id, status),
78 CONSTRAINT status_values CHECK (status IN ('draft', 'published', 'archived')),
79 FOREIGN KEY (story_id) REFERENCES STORY(story_id)
80 ON DELETE CASCADE
81 ON UPDATE CASCADE
82);
83
84CREATE TABLE CHAPTER(
85 chapter_id SERIAL PRIMARY KEY,
86 chapter_number INTEGER NOT NULL,
87 chapter_name VARCHAR(100) NOT NULL,
88 title VARCHAR(200) NOT NULL,
89 content TEXT NOT NULL,
90 word_count INTEGER CHECK (word_count >= 0),
91 rating DECIMAL(3,2) CHECK (rating >= 0 AND rating <= 5),
92 published_at TIMESTAMPTZ NOT NULL,
93 view_count INTEGER DEFAULT 0 CHECK (view_count >= 0),
94 story_id INTEGER NOT NULL,
95 created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
96 updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
97 CONSTRAINT unique_chapter_number UNIQUE(story_id, chapter_number),
98 FOREIGN KEY (story_id) REFERENCES STORY(story_id)
99 ON DELETE CASCADE
100 ON UPDATE CASCADE
101);
102
103CREATE TABLE GENRE(
104 genre_id SERIAL PRIMARY KEY,
105 name VARCHAR(100) NOT NULL UNIQUE
106);
107
108CREATE TABLE READING_LIST(
109 list_id SERIAL PRIMARY KEY,
110 name VARCHAR(100) NOT NULL,
111 content TEXT,
112 is_public BOOLEAN NOT NULL,
113 user_id INTEGER NOT NULL,
114 created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
115 updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
116 FOREIGN KEY (user_id) REFERENCES USERS(user_id)
117 ON DELETE CASCADE
118 ON UPDATE CASCADE
119);
120
121CREATE TABLE NOTIFICATION(
122 notification_id SERIAL PRIMARY KEY,
123 content TEXT NOT NULL,
124 is_read BOOLEAN DEFAULT FALSE,
125 recipient_user_id INTEGER,
126 type VARCHAR(50),
127 link VARCHAR(500),
128 created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
129 FOREIGN KEY (recipient_user_id) REFERENCES USERS(user_id)
130 ON DELETE SET NULL
131 ON UPDATE CASCADE
132);
133
134CREATE TABLE CONTENT_TYPE(
135 notification_id INTEGER,
136 content_type VARCHAR(50) NOT NULL,
137 CONSTRAINT content_type_pk PRIMARY KEY(notification_id, content_type),
138 FOREIGN KEY (notification_id) REFERENCES NOTIFICATION(notification_id)
139 ON DELETE CASCADE
140 ON UPDATE CASCADE
141);
142
143CREATE TABLE AI_SUGGESTION(
144 suggestion_id SERIAL PRIMARY KEY,
145 original_text TEXT NOT NULL,
146 suggested_text TEXT NOT NULL,
147 accepted BOOLEAN NOT NULL DEFAULT FALSE,
148 created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
149 applied_at TIMESTAMPTZ,
150 story_id INTEGER NOT NULL,
151 FOREIGN KEY (story_id) REFERENCES STORY(story_id)
152 ON DELETE CASCADE
153 ON UPDATE CASCADE
154);
155
156CREATE TABLE SUGGESTION_TYPE(
157 suggestion_id INTEGER,
158 suggestion_type VARCHAR(50) NOT NULL,
159 CONSTRAINT suggestion_type_pk PRIMARY KEY(suggestion_id, suggestion_type),
160 FOREIGN KEY (suggestion_id) REFERENCES AI_SUGGESTION(suggestion_id)
161 ON DELETE CASCADE
162 ON UPDATE CASCADE
163);
164
165CREATE TABLE LIKES(
166 user_id INTEGER,
167 story_id INTEGER,
168 created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
169 CONSTRAINT like_pk PRIMARY KEY(user_id, story_id),
170 FOREIGN KEY (user_id) REFERENCES USERS(user_id)
171 ON DELETE CASCADE
172 ON UPDATE CASCADE,
173 FOREIGN KEY (story_id) REFERENCES STORY(story_id)
174 ON DELETE CASCADE
175 ON UPDATE CASCADE
176);
177
178CREATE TABLE COMMENT(
179 comment_id SERIAL PRIMARY KEY,
180 content TEXT NOT NULL,
181 user_id INTEGER NOT NULL,
182 story_id INTEGER NOT NULL,
183 created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
184 updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
185 FOREIGN KEY (user_id) REFERENCES USERS(user_id)
186 ON DELETE CASCADE
187 ON UPDATE CASCADE,
188 FOREIGN KEY (story_id) REFERENCES STORY(story_id)
189 ON DELETE CASCADE
190 ON UPDATE CASCADE
191);
192CREATE TABLE COLLABORATION(
193 user_id INTEGER,
194 story_id INTEGER,
195 created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
196 CONSTRAINT collaboration_pk PRIMARY KEY(user_id, story_id),
197 FOREIGN KEY (user_id) REFERENCES USERS(user_id)
198 ON DELETE CASCADE
199 ON UPDATE CASCADE,
200 FOREIGN KEY (story_id) REFERENCES STORY(story_id)
201 ON DELETE CASCADE
202 ON UPDATE CASCADE
203);
204
205CREATE TABLE ROLES(
206 user_id INTEGER,
207 story_id INTEGER,
208 roles VARCHAR(50) NOT NULL,
209 CONSTRAINT role_pk PRIMARY KEY(user_id, story_id, roles),
210 FOREIGN KEY (user_id, story_id) REFERENCES COLLABORATION(user_id, story_id)
211 ON DELETE CASCADE
212 ON UPDATE CASCADE
213);
214
215CREATE TABLE PERMISSION_LEVEL(
216 user_id INTEGER,
217 story_id INTEGER,
218 permission_level INTEGER NOT NULL,
219 CONSTRAINT permission_level_pk PRIMARY KEY(user_id, story_id, permission_level),
220 FOREIGN KEY (user_id, story_id) REFERENCES COLLABORATION(user_id, story_id)
221 ON DELETE CASCADE
222 ON UPDATE CASCADE
223);
224
225CREATE TABLE HAS_GENRE(
226 story_id INTEGER,
227 genre_id INTEGER,
228 CONSTRAINT has_genre_pk PRIMARY KEY(story_id, genre_id),
229 FOREIGN KEY (story_id) REFERENCES STORY(story_id)
230 ON DELETE CASCADE
231 ON UPDATE CASCADE,
232 FOREIGN KEY (genre_id) REFERENCES GENRE(genre_id)
233 ON DELETE CASCADE
234 ON UPDATE CASCADE
235);
236
237CREATE TABLE READING_LIST_ITEMS(
238 list_id INTEGER,
239 story_id INTEGER,
240 added_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
241 CONSTRAINT reading_list_items_pk PRIMARY KEY(list_id, story_id),
242 FOREIGN KEY (list_id) REFERENCES READING_LIST(list_id)
243 ON DELETE CASCADE
244 ON UPDATE CASCADE,
245 FOREIGN KEY (story_id) REFERENCES STORY(story_id)
246 ON DELETE CASCADE
247 ON UPDATE CASCADE
248);
249
250CREATE TABLE NOTIFY(
251 user_id INTEGER,
252 story_id INTEGER,
253 notification_id INTEGER,
254 CONSTRAINT notify_pk PRIMARY KEY(user_id, story_id, notification_id),
255 FOREIGN KEY (user_id) REFERENCES USERS(user_id)
256 ON DELETE CASCADE
257 ON UPDATE CASCADE,
258 FOREIGN KEY (story_id) REFERENCES STORY(story_id)
259 ON DELETE CASCADE
260 ON UPDATE CASCADE,
261 FOREIGN KEY (notification_id) REFERENCES NOTIFICATION(notification_id)
262 ON DELETE CASCADE
263 ON UPDATE CASCADE
264);
265
266CREATE TABLE NEED_APPROVAL(
267 suggestion_id INTEGER,
268 story_id INTEGER,
269 chapter_id INTEGER,
270 CONSTRAINT need_approval_pk PRIMARY KEY(suggestion_id, story_id, chapter_id),
271 FOREIGN KEY (suggestion_id) REFERENCES AI_SUGGESTION(suggestion_id)
272 ON DELETE CASCADE
273 ON UPDATE CASCADE,
274 FOREIGN KEY (story_id) REFERENCES STORY(story_id)
275 ON DELETE CASCADE
276 ON UPDATE CASCADE,
277 FOREIGN KEY (chapter_id) REFERENCES CHAPTER(chapter_id)
278 ON DELETE CASCADE
279 ON UPDATE CASCADE
280);