DatabaseCreation: ddl.sql

File ddl.sql, 9.8 KB (added by 231136, 15 hours 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),
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_admin_id INTEGER NULL,
109 genre VARCHAR(255) NULL,
110
111 FOREIGN KEY (owner_artist_id) REFERENCES Artists(id),
112 FOREIGN KEY (published_by_artist_id) REFERENCES Artists(id),
113 FOREIGN KEY (published_by_label_admin_id) REFERENCES Label_Admins(id),
114
115 CONSTRAINT visibility_constraint CHECK ( visibility in ('PUBLIC','PRIVATE','SHARED')),
116
117 CHECK (
118 (published_by_artist_id IS NOT NULL)::int +
119 (published_by_label_admin_id IS NOT NULL)::int = 1
120 ),
121 CHECK (
122 published_by_artist_id IS NULL OR published_by_artist_id = owner_artist_id
123 )
124);
125
126
127CREATE TABLE Follows (
128 id BIGSERIAL PRIMARY KEY,
129 follower_user_id BIGINT NOT NULL,
130 followed_user_id BIGINT NOT NULL,
131 followed_at TIMESTAMP DEFAULT now(),
132
133 FOREIGN KEY (follower_user_id) REFERENCES Users(id) ON DELETE CASCADE,
134 FOREIGN KEY (followed_user_id) REFERENCES Users(id) ON DELETE CASCADE,
135
136 CONSTRAINT no_self_follow CHECK ( follower_user_id<>followed_user_id ),
137
138 UNIQUE (follower_user_id, followed_user_id)
139);
140
141CREATE TABLE Artist_Labels (
142 id BIGSERIAL PRIMARY KEY,
143 artist_id BIGINT NOT NULL,
144 label_id BIGINT NOT NULL,
145 active BOOLEAN NOT NULL,
146 start_date DATE NOT NULL,
147 end_date DATE NULL,
148
149 FOREIGN KEY (artist_id) REFERENCES Artists(id),
150 FOREIGN KEY (label_id) REFERENCES Labels(id)
151);
152
153CREATE TABLE Playlists (
154 id BIGSERIAL PRIMARY KEY,
155 visibility VARCHAR(255) NOT NULL,
156 creator_user_id BIGINT NOT NULL,
157 created_at TIMESTAMP NOT NULL DEFAULT now(),
158 playlist_name VARCHAR(255) NOT NULL,
159 description VARCHAR(255) NULL,
160
161 FOREIGN KEY (creator_user_id) REFERENCES Users(id),
162 CONSTRAINT visibility_constraint CHECK ( visibility in ('PUBLIC','PRIVATE','SHARED'))
163);
164
165CREATE TABLE Saved_Playlists (
166 id BIGSERIAL PRIMARY KEY,
167 playlist_id BIGINT NOT NULL,
168 saved_by BIGINT NOT NULL,
169 saved_at TIMESTAMP NOT NULL DEFAULT now(),
170
171 FOREIGN KEY (playlist_id) REFERENCES Playlists(id),
172 FOREIGN KEY (saved_by) REFERENCES Users(id),
173
174 UNIQUE (playlist_id, saved_by)
175);
176
177
178CREATE TABLE Albums (
179 id BIGSERIAL PRIMARY KEY,
180 title VARCHAR(255),
181 visibility VARCHAR(255) NOT NULL,
182 owner_artist_id BIGINT NOT NULL,
183 published_by_artist_id INTEGER NULL,
184 published_by_label_admin_id INTEGER NULL,
185
186 FOREIGN KEY (owner_artist_id) REFERENCES Artists(id),
187 FOREIGN KEY (published_by_artist_id) REFERENCES Artists(id),
188 FOREIGN KEY (published_by_label_admin_id) REFERENCES Label_Admins(id),
189
190 CONSTRAINT visibility_constraint CHECK ( visibility in ('PUBLIC','PRIVATE','SHARED')),
191 CHECK (
192 (published_by_artist_id IS NOT NULL)::int +
193 (published_by_label_admin_id IS NOT NULL)::int = 1
194 ),
195 CHECK (
196 published_by_artist_id IS NULL OR published_by_artist_id = owner_artist_id
197 )
198
199 -- TODO: treba da se dodade trigger za da se proveri deka dokolku pesnata e objavena od label, artist owner-ot e momentalno so toj label
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),
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),
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 UNIQUE,
234 FOREIGN KEY (song_id) REFERENCES Songs(id) ON DELETE CASCADE
235);
236
237CREATE TABLE Song_Relationships (
238 id BIGSERIAL PRIMARY KEY,
239 source_song_id BIGINT NOT NULL,
240 target_song_id BIGINT NOT NULL,
241 relationship_type VARCHAR(255) NOT NULL, -- remix, cover
242 FOREIGN KEY (source_song_id) REFERENCES Songs(id),
243 FOREIGN KEY (target_song_id) REFERENCES Songs(id)
244);
245
246CREATE TABLE Song_Contributors (
247 id BIGSERIAL PRIMARY KEY,
248 song_id BIGINT NOT NULL,
249 artist_id BIGINT NOT NULL,
250 role VARCHAR(255) NOT NULL,
251 credit_order INTEGER NOT NULL,
252
253 FOREIGN KEY (song_id) REFERENCES Songs(id) ON DELETE CASCADE,
254 FOREIGN KEY (artist_id) REFERENCES Artists(id),
255
256 CONSTRAINT credit_order_positive CHECK ( credit_order > 0 )
257);
258
259
260CREATE TABLE Playback_Sessions (
261 id BIGSERIAL PRIMARY KEY,
262 user_id BIGINT NOT NULL,
263 song_id BIGINT NOT NULL,
264 started_at TIMESTAMP NOT NULL,
265 listened_ms INTEGER NOT NULL,
266 last_position_ms INTEGER NOT NULL,
267
268 FOREIGN KEY (user_id) REFERENCES Users(id),
269 FOREIGN KEY (song_id) REFERENCES Songs(id)
270 -- todo: triger sto ke zapisuva vo song streams koga listened_ms ke nadmine odreden threshold
271);
272
273
274CREATE TABLE Song_Streams (
275 id BIGSERIAL PRIMARY KEY,
276 -- site 3 se zemeni od Playback_Sessions, verojatno od trigger
277 playback_session_id BIGINT NOT NULL,
278 song_id BIGINT NOT NULL,
279 streamed_at TIMESTAMP NOT NULL,
280
281 FOREIGN KEY (playback_session_id) REFERENCES Playback_Sessions(id) ON DELETE CASCADE,
282 FOREIGN KEY (song_id) REFERENCES Songs(id)
283);
284
285
286CREATE TABLE Reviews (
287 id SERIAL PRIMARY KEY,
288 user_id BIGINT NOT NULL,
289 song_id BIGINT NOT NULL,
290 reviewed_at TIMESTAMP NOT NULL DEFAULT now(),
291 grade INTEGER NOT NULL,
292 comment VARCHAR(500) NULL,
293
294 FOREIGN KEY (user_id) REFERENCES Users(id) ON DELETE CASCADE,
295 FOREIGN KEY (song_id) REFERENCES Songs(id) ON DELETE CASCADE,
296 CONSTRAINT grade_valid CHECK ( grade BETWEEN 1 and 5)
297);
298
299CREATE TABLE Resource_Shares (
300 id BIGSERIAL PRIMARY KEY,
301
302 song_id BIGINT REFERENCES songs(id),
303 album_id BIGINT REFERENCES albums(id),
304 playlist_id BIGINT REFERENCES playlists(id),
305
306 user_id BIGINT REFERENCES users(id),
307 role_id BIGINT REFERENCES roles(id),
308
309 permission_id BIGINT REFERENCES permissions(id),
310
311 resource_type TEXT GENERATED ALWAYS AS (
312 CASE
313 WHEN song_id IS NOT NULL THEN 'SONG'
314 WHEN album_id IS NOT NULL THEN 'ALBUM'
315 WHEN playlist_id IS NOT NULL THEN 'PLAYLIST'
316 END
317 ) STORED,
318
319 subject_type TEXT GENERATED ALWAYS AS (
320 CASE
321 WHEN user_id IS NOT NULL THEN 'USER'
322 WHEN role_id IS NOT NULL THEN 'ROLE'
323 END
324 ) STORED,
325
326 CHECK (
327 (song_id IS NOT NULL)::int +
328 (album_id IS NOT NULL)::int +
329 (playlist_id IS NOT NULL)::int = 1
330 ),
331
332 CHECK (
333 (user_id IS NOT NULL)::int +
334 (role_id IS NOT NULL)::int = 1
335 )
336);