DatabaseCreation: ddl.4.sql

File ddl.4.sql, 9.9 KB (added by 231136, 6 days ago)
Line 
1DROP TABLE IF EXISTS Reviews CASCADE;
2DROP TABLE IF EXISTS Song_Streams CASCADE;
3DROP TABLE IF EXISTS Playback_Sessions CASCADE;
4DROP TABLE IF EXISTS Song_Contributors CASCADE;
5DROP TABLE IF EXISTS Song_Relationships CASCADE;
6DROP TABLE IF EXISTS Song_Contents CASCADE;
7DROP TABLE IF EXISTS Playlist_Tracks CASCADE;
8DROP TABLE IF EXISTS Album_Tracks CASCADE;
9DROP TABLE IF EXISTS Songs CASCADE;
10DROP TABLE IF EXISTS Albums CASCADE;
11DROP TABLE IF EXISTS Saved_Playlists CASCADE;
12DROP TABLE IF EXISTS Playlists CASCADE;
13DROP TABLE IF EXISTS Artist_Labels CASCADE;
14DROP TABLE IF EXISTS Artists CASCADE;
15DROP TABLE IF EXISTS Label_Admins CASCADE;
16DROP TABLE IF EXISTS Resource_Shares CASCADE;
17DROP TABLE IF EXISTS Role_Permissions CASCADE;
18DROP TABLE IF EXISTS User_Roles CASCADE;
19DROP TABLE IF EXISTS Follows CASCADE;
20DROP TABLE IF EXISTS Labels CASCADE;
21DROP TABLE IF EXISTS Permissions CASCADE;
22DROP TABLE IF EXISTS Roles CASCADE;
23DROP TABLE IF EXISTS Users CASCADE;
24
25
26CREATE TABLE Users (
27 id BIGSERIAL PRIMARY KEY,
28 username VARCHAR(64) NOT NULL UNIQUE,
29 email VARCHAR(64) NOT NULL UNIQUE,
30 password VARCHAR(64) NOT NULL,
31 full_name VARCHAR(64) NOT NULL,
32 last_login TIMESTAMP NULL,
33 created_at TIMESTAMP NOT NULL DEFAULT now(),
34 CONSTRAINT is_valid_email CHECK (
35 email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
36 )
37);
38
39CREATE TABLE Labels (
40 id BIGSERIAL PRIMARY KEY,
41 name VARCHAR(255) NOT NULL
42);
43
44CREATE TABLE Label_Admins (
45 id BIGSERIAL PRIMARY KEY,
46 label_id BIGINT NOT NULL,
47 user_id BIGINT NOT NULL,
48
49 FOREIGN KEY (label_id) REFERENCES Labels(id),
50 FOREIGN KEY (user_id) REFERENCES Users(id),
51
52 CONSTRAINT unique_user_admin UNIQUE (user_id)
53);
54
55CREATE TABLE Artists (
56 id BIGSERIAL PRIMARY KEY,
57 user_id BIGINT NOT NULL UNIQUE,
58 display_name VARCHAR(255) NOT NULL,
59
60 FOREIGN KEY (user_id) REFERENCES Users(id)
61);
62
63
64CREATE TABLE Roles (
65 id BIGSERIAL PRIMARY KEY,
66 role_name VARCHAR(255) NOT NULL UNIQUE
67);
68
69CREATE TABLE User_Roles (
70 id BIGSERIAL PRIMARY KEY,
71 user_id BIGINT NOT NULL,
72 role_id BIGINT NOT NULL,
73
74 FOREIGN KEY (user_id) REFERENCES Users(id) ON DELETE CASCADE,
75 FOREIGN KEY (role_id) REFERENCES Roles(id)
76);
77
78
79
80CREATE TABLE Permissions (
81 id BIGSERIAL PRIMARY KEY,
82 action VARCHAR(255) NOT NULL,
83 resource_type VARCHAR(255) NOT NULL,
84 scope VARCHAR(32) NOT NULL,
85
86 CONSTRAINT scope_constraint CHECK ( scope in ('ANY','PUBLIC','SHARED','OWN')),
87 CONSTRAINT resource_type_constraint CHECK ( resource_type in ('SONG','ALBUM','PLAYLIST')),
88 CONSTRAINT action_constraint CHECK ( action in ('CREATE','EDIT','PLAY', 'VIEW','DELETE','SHARE','ADD_SONG','REMOVE_SONG'))
89);
90
91CREATE TABLE Role_Permissions (
92 id BIGSERIAL PRIMARY KEY,
93 permission_id BIGINT NOT NULL,
94 role_id BIGINT NOT NULL,
95
96 FOREIGN KEY (permission_id) REFERENCES Permissions(id),
97 FOREIGN KEY (role_id) REFERENCES Roles(id),
98
99 UNIQUE (role_id,permission_id)
100);
101
102CREATE TABLE Songs (
103 id BIGSERIAL PRIMARY KEY,
104 title VARCHAR(255) NOT NULL,
105 visibility VARCHAR(64) NOT NULL,
106 owner_artist_id BIGINT NOT NULL,
107 published_by_artist_id INTEGER NULL,
108 published_by_label_id INTEGER NULL,
109 genre VARCHAR(255) NULL,
110 deleted_at TIMESTAMP NULL
111
112 FOREIGN KEY (owner_artist_id) REFERENCES Artists(id),
113 FOREIGN KEY (published_by_artist_id) REFERENCES Artists(id),
114 FOREIGN KEY (published_by_label_id) REFERENCES Labels(id),
115
116 CONSTRAINT visibility_constraint CHECK ( visibility in ('PUBLIC','PRIVATE','SHARED')),
117
118 CHECK (
119 (published_by_artist_id IS NOT NULL)::int +
120 (published_by_label_id IS NOT NULL)::int = 1
121 ),
122 CHECK (
123 published_by_artist_id IS NULL OR published_by_artist_id = owner_artist_id
124 )
125);
126
127
128CREATE TABLE Follows (
129 id BIGSERIAL PRIMARY KEY,
130 follower_user_id BIGINT NOT NULL,
131 followed_user_id BIGINT NOT NULL,
132 followed_at TIMESTAMP DEFAULT now(),
133
134 FOREIGN KEY (follower_user_id) REFERENCES Users(id) ON DELETE CASCADE,
135 FOREIGN KEY (followed_user_id) REFERENCES Users(id) ON DELETE CASCADE,
136
137 CONSTRAINT no_self_follow CHECK ( follower_user_id<>followed_user_id ),
138
139 UNIQUE (follower_user_id, followed_user_id)
140);
141
142CREATE TABLE Artist_Labels (
143 id BIGSERIAL PRIMARY KEY,
144 artist_id BIGINT NOT NULL,
145 label_id BIGINT NOT NULL,
146 active BOOLEAN NULL,
147 start_date DATE NULL,
148 end_date DATE NULL,
149
150 FOREIGN KEY (artist_id) REFERENCES Artists(id) ON DELETE CASCADE,
151 FOREIGN KEY (label_id) REFERENCES Labels(id) ON DELETE CASCADE
152
153);
154
155CREATE TABLE Playlists (
156 id BIGSERIAL PRIMARY KEY,
157 visibility VARCHAR(255) NOT NULL,
158 creator_user_id BIGINT NOT NULL,
159 created_at TIMESTAMP NOT NULL DEFAULT now(),
160 playlist_name VARCHAR(255) NOT NULL,
161 description VARCHAR(255) NULL,
162
163 FOREIGN KEY (creator_user_id) REFERENCES Users(id),
164 CONSTRAINT visibility_constraint CHECK ( visibility in ('PUBLIC','PRIVATE','SHARED'))
165);
166
167CREATE TABLE Saved_Playlists (
168 id BIGSERIAL PRIMARY KEY,
169 playlist_id BIGINT NOT NULL,
170 saved_by BIGINT NOT NULL,
171 saved_at TIMESTAMP NOT NULL DEFAULT now(),
172
173 FOREIGN KEY (playlist_id) REFERENCES Playlists(id) ON DELETE CASCADE,
174 FOREIGN KEY (saved_by) REFERENCES Users(id) ON DELETE CASCADE,
175
176 UNIQUE (playlist_id, saved_by)
177);
178
179
180CREATE TABLE Albums (
181 id BIGSERIAL PRIMARY KEY,
182 title VARCHAR(255),
183 visibility VARCHAR(255) NOT NULL,
184 owner_artist_id BIGINT NOT NULL,
185 published_by_artist_id INTEGER NULL,
186 published_by_label_id INTEGER NULL,
187
188 FOREIGN KEY (owner_artist_id) REFERENCES Artists(id),
189 FOREIGN KEY (published_by_artist_id) REFERENCES Artists(id),
190 FOREIGN KEY (published_by_label_id) REFERENCES Labels(id),
191
192 CONSTRAINT visibility_constraint CHECK ( visibility in ('PUBLIC','PRIVATE','SHARED')),
193 CHECK (
194 (published_by_artist_id IS NOT NULL)::int +
195 (published_by_label_id IS NOT NULL)::int = 1
196 ),
197 CHECK (
198 published_by_artist_id IS NULL OR published_by_artist_id = owner_artist_id
199 )
200);
201
202
203CREATE TABLE Album_Tracks (
204 id BIGSERIAL PRIMARY KEY,
205 album_id BIGINT NOT NULL,
206 song_id BIGINT NOT NULL,
207 track_number INTEGER NOT NULL,
208
209 FOREIGN KEY (album_id) REFERENCES Albums(id) ON DELETE CASCADE,
210 FOREIGN KEY (song_id) REFERENCES Songs(id) ON DELETE CASCADE,
211
212 UNIQUE (album_id, song_id),
213 UNIQUE (album_id, track_number),
214
215 CONSTRAINT track_number_positive CHECK ( track_number > 0 )
216);
217
218CREATE TABLE Playlist_Tracks (
219 id BIGSERIAL PRIMARY KEY,
220 song_id BIGINT NOT NULL,
221 playlist_id BIGINT NOT NULL,
222 added_at TIMESTAMP NOT NULL DEFAULT now(),
223
224 FOREIGN KEY (song_id) REFERENCES Songs(id) ON DELETE CASCADE,
225 FOREIGN KEY (playlist_id) REFERENCES Playlists(id) ON DELETE CASCADE,
226
227 UNIQUE (playlist_id, song_id)
228);
229
230CREATE TABLE Song_Contents (
231 id BIGSERIAL PRIMARY KEY,
232 song_id BIGINT NOT NULL,
233 content BYTEA NOT NULL,
234 FOREIGN KEY (song_id) REFERENCES Songs(id) ON DELETE CASCADE
235);
236
237-- pravime unique index na hash-ot bidejki fajlovite go nadminuvaat limitot na btree index (~8KB)
238CREATE UNIQUE INDEX song_contents_content_md5_key ON Song_Contents (md5(content));
239
240CREATE TABLE Song_Relationships (
241 id BIGSERIAL PRIMARY KEY,
242 source_song_id BIGINT NOT NULL,
243 target_song_id BIGINT NOT NULL,
244 relationship_type VARCHAR(255) NOT NULL, -- remix, cover
245 FOREIGN KEY (source_song_id) REFERENCES Songs(id),
246 FOREIGN KEY (target_song_id) REFERENCES Songs(id)
247);
248
249CREATE TABLE Song_Contributors (
250 id BIGSERIAL PRIMARY KEY,
251 song_id BIGINT NOT NULL,
252 artist_id BIGINT NOT NULL,
253 role VARCHAR(255) NOT NULL,
254 credit_order INTEGER NOT NULL,
255
256 FOREIGN KEY (song_id) REFERENCES Songs(id) ON DELETE CASCADE,
257 FOREIGN KEY (artist_id) REFERENCES Artists(id),
258
259 CONSTRAINT credit_order_positive CHECK ( credit_order > 0 )
260);
261
262
263CREATE TABLE Playback_Sessions (
264 id BIGSERIAL PRIMARY KEY,
265 user_id BIGINT NOT NULL,
266 song_id BIGINT NOT NULL,
267 started_at TIMESTAMP NOT NULL,
268 listened_ms INTEGER NOT NULL,
269 last_position_ms INTEGER NOT NULL,
270
271 FOREIGN KEY (user_id) REFERENCES Users(id),
272 FOREIGN KEY (song_id) REFERENCES Songs(id)
273);
274
275
276CREATE TABLE Song_Streams (
277 id BIGSERIAL PRIMARY KEY,
278 playback_session_id BIGINT NOT NULL,
279 song_id BIGINT NOT NULL,
280 streamed_at TIMESTAMP NOT NULL,
281 user_id BIGINT NOT NULL,
282
283 FOREIGN KEY (playback_session_id) REFERENCES Playback_Sessions(id) ON DELETE CASCADE,
284 FOREIGN KEY (song_id) REFERENCES Songs(id)
285);
286
287
288CREATE TABLE Reviews (
289 id SERIAL PRIMARY KEY,
290 user_id BIGINT NOT NULL,
291 song_id BIGINT NOT NULL,
292 reviewed_at TIMESTAMP NOT NULL DEFAULT now(),
293 grade INTEGER NOT NULL,
294 comment VARCHAR(500) NULL,
295
296 FOREIGN KEY (user_id) REFERENCES Users(id) ON DELETE CASCADE,
297 FOREIGN KEY (song_id) REFERENCES Songs(id) ON DELETE CASCADE,
298 CONSTRAINT grade_valid CHECK ( grade BETWEEN 1 and 5)
299);
300
301CREATE TABLE Resource_Shares (
302 id BIGSERIAL PRIMARY KEY,
303
304 song_id BIGINT REFERENCES songs(id) ON DELETE CASCADE,
305 album_id BIGINT REFERENCES albums(id) ON DELETE CASCADE,
306 playlist_id BIGINT REFERENCES playlists(id) ON DELETE CASCADE,
307
308 user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
309 role_id BIGINT REFERENCES roles(id) ON DELETE CASCADE,
310
311 permission_id BIGINT REFERENCES permissions(id) ON DELETE CASCADE,
312
313 resource_type TEXT GENERATED ALWAYS AS (
314 CASE
315 WHEN song_id IS NOT NULL THEN 'SONG'
316 WHEN album_id IS NOT NULL THEN 'ALBUM'
317 WHEN playlist_id IS NOT NULL THEN 'PLAYLIST'
318 END
319 ) STORED,
320
321 subject_type TEXT GENERATED ALWAYS AS (
322 CASE
323 WHEN user_id IS NOT NULL THEN 'USER'
324 WHEN role_id IS NOT NULL THEN 'ROLE'
325 END
326 ) STORED,
327
328 CHECK (
329 (song_id IS NOT NULL)::int +
330 (album_id IS NOT NULL)::int +
331 (playlist_id IS NOT NULL)::int = 1
332 ),
333
334 CHECK (
335 (user_id IS NOT NULL)::int +
336 (role_id IS NOT NULL)::int = 1
337 )
338);