P2: schema_creation.3.sql

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