· 4 years ago · May 19, 2021, 04:06 AM
1create schema music;
2
3create table music.users
4(
5 user_id integer primary key,
6 user_nm varchar(50) not null,
7 birth_dt date
8);
9
10create table music.validate_user_data
11(
12 user_id integer,
13 card_nm varchar(16),
14 mail_nm varchar(50) not null,
15 subscr_type varchar(50) not null,
16 valid_from_dttm timestamp default now(),
17 valid_to_dttm timestamp default now() + interval '1 year',
18
19 foreign key (user_id) references music.users (user_id) on delete cascade,
20 constraint subscr_version primary key (user_id, valid_from_dttm)
21);
22
23create table music.tracks (
24 track_id integer primary key,
25 track_nm varchar(100) not null,
26 premiere_dt date,
27 duration_sec interval not null,
28 genre varchar(100)
29);
30
31create table music.singers
32(
33 singer_id integer primary key,
34 singer_nm varchar(50) not null
35);
36
37create table music.playlists
38(
39 playlist_id integer primary key,
40 user_id integer not null,
41 playlist_nm varchar(100) default 'новый плейлист',
42 playlist_desc varchar(1000),
43
44 foreign key (user_id) references music.users (user_id) on delete cascade
45);
46
47create table music.singers_on_track
48(
49 track_id integer not null,
50 singer_id integer not null,
51
52 constraint pk_singer_track primary key (singer_id, track_id),
53 foreign key (singer_id) references music.singers (singer_id) on delete set null,
54 foreign key (track_id) references music.tracks (track_id) on delete cascade
55);
56
57create table music.track_in_playlist
58(
59 playlist_id integer not null,
60 track_id integer not null,
61
62 constraint pk_playlist primary key (playlist_id, track_id),
63 foreign key (playlist_id) references music.playlists (playlist_id) on delete cascade,
64 foreign key (track_id) references music.tracks (track_id) on delete cascade
65);
66
67create table music.devices
68(
69 device_id integer primary key,
70 user_id integer,
71 device_nm varchar[50],
72
73 foreign key (user_id) references music.users (user_id) on delete cascade
74);
75
76create table music.track_listening (
77 listening_id integer primary key,
78 track_id integer not null,
79 device_id integer not null,
80 played_from_dttm timestamp not null,
81 played_to_dttm timestamp not null,
82 position_sec interval,
83
84 foreign key (track_id) references music.tracks(track_id) on delete cascade,
85 foreign key (device_id) references music.devices(device_id) on delete cascade
86);
87
88-- при добавлении новой версии подписки пользователя устанавливает valid_to для старой и вставляет новую
89create or replace function music.update_validate_data_func() returns trigger as
90$$
91begin
92 if (new.valid_from_dttm is null) then
93 new.valid_from_dttm = now();
94 end if;
95
96 update music.validate_user_data
97 set valid_to_dttm = new.valid_from_dttm
98 where user_id = new.user_id
99 and valid_to_dttm > new.valid_from_dttm;
100
101 return new;
102
103end;
104$$ language plpgsql;
105
106
107create trigger update_valida_data
108 before insert
109 on music.validate_user_data
110 for each row
111execute procedure music.update_validate_data_func();
112
113-- при добавлении нового трека played_to_dttm старых записей меняется
114create or replace function music.update_track_listening_func() returns trigger as
115$$
116begin
117
118 if (new.position_sec) then
119 new.position_sec = interval '00:00:00';
120 end if;
121
122 update music.track_listening
123 set played_to_dttm = new.played_from_dttm
124 where device_id = new.device_id
125 and played_to_dttm > new.played_from_dttm;
126
127 return new;
128
129
130end;
131
132$$ language plpgsql;
133
134create trigger update_track_listening
135 before insert
136 on music.track_listening
137 for each row
138
139execute procedure music.update_track_listening_func();
140
141-- добавляет пользователю с id user_to_add_id плейлист состоящий из песен исполнителя singer_to_add_id
142create or replace procedure music.create_playlist_from_singer(singer_to_add_id int, user_to_add_id int)
143as
144$$
145declare
146 current_track_id int;
147 new_playlist_id int;
148 singer_name text;
149 singer_cnt int := 0;
150 user_cnt int := 0;
151begin
152 execute 'select singer_nm from music.singers where singer_id = $1'
153 into singer_name using singer_to_add_id;
154
155 execute 'select coalesce(count(user_id), 0) from music.users where user_id = $1' into user_cnt using user_to_add_id;
156
157 if user_cnt = 0 then
158 raise exception 'user does not exists';
159 end if;
160
161 execute 'select coalesce(count(singer_id), 0) from music.singers where singer_id = $1' into singer_cnt
162 using singer_to_add_id;
163
164 if singer_cnt = 0 then
165 raise exception 'singer does not exists';
166 end if;
167
168 execute 'select coalesce(max(playlist_id), 0) from music.playlists' into new_playlist_id;
169 new_playlist_id := new_playlist_id + 1;
170 insert into music.playlists values (new_playlist_id, user_to_add_id, 'All of ' || singer_name,
171 'Плейлист содержит все песни исполнителя ' || singer_name);
172 for current_track_id in select track_id from music.singers_on_track where singer_id = singer_to_add_id
173 loop
174 insert into music.track_in_playlist values(new_playlist_id, current_track_id);
175 end loop;
176end;
177$$
178 language plpgsql;
179
180-- позволяет добавлять группы исполнителей (feat) для одного трека
181-- если хотя бы одного исполнителя нет в БД singers, то кидает исключение
182create or replace procedure music.add_track(track_name text, date timestamp, duracion_sec interval, track_genre varchar(100),
183 artists_id int array)
184as
185 $$
186 declare
187 new_track_id int;
188 current_singer_id int;
189 singers_cnt int := 0;
190 index int := 1;
191 len integer := array_length(artists_id, 1);
192 begin
193 for index in 1..len by 1
194 loop
195 current_singer_id := artists_id[index];
196 execute 'select coalesce(count(singer_id), 0) from music.singers where singer_id = $1' into singers_cnt
197 using current_singer_id;
198 if singers_cnt = 0 then
199 raise exception 'singer with id = % does not exists', current_singer_id;
200 end if;
201 end loop;
202
203 execute 'select coalesce(max(track_id), 0) from music.tracks' into new_track_id;
204 new_track_id := new_track_id + 1;
205
206 insert into music.tracks values(new_track_id, track_name, date, duracion_sec, track_genre);
207
208 for index in 1..len by 1
209 loop
210 current_singer_id := artists_id[index];
211 insert into music.singers_on_track values (new_track_id, current_singer_id);
212 end loop;
213
214 end;
215 $$
216 language plpgsql;
217
218-- ВСТАВКА
219set datestyle = 'DMY';
220
221insert into music.users
222values (1, 'Ростов Николай Ильич', '17.03.1765'),
223 (2, 'Болконский Андрей Николаевич,', '25.07.1788'),
224 (3, 'Безухов Кирилл Владимирович', '11.11.1711'),
225 (4, 'Друбецкая Анна Михайловна', '14.06.1756'),
226 (5, 'Денисов Василий Дмитриевич.', '07.09.1783'),
227 (6, 'Ростов Николай Ильич', '17.03.1765'),
228 (7, 'Ростова Вера Ильинична', '19.01.1768'),
229 (8, 'Болконская Марья Николаевна ', '21.04.1770'),
230 (9, 'Уваров Фёдор Петрович', '22.11.1745'),
231 (10, 'Сперанский Михаил Михайлович', '12.01.1772');
232
233insert into music.tracks
234values (1, 'Rum & Coca-Cola', '01.01.1790', interval'198 sec', 'Jazz'),
235 (2, 'Sentimental Journey', '02.01.1791', interval'241 sec', 'Punk'),
236 (3, 'Till The End of Time', '05.04.1792', interval'207 sec', 'Polka'),
237 (4, 'My Dreams Are Getting Better All the Time', '21.01.1790', interval'176 sec', 'Rock music'),
238 (5, 'On the Atchison, Topeka & the Santa Fe', '11.07.1797', interval'222 sec', 'Rock music'),
239 (6, 'It’s Been a Long, Long Time', '11.11.1799', interval '245 sec', 'Popular music'),
240 (7, 'I Can’t Begin to Tell You', '11.09.1792', interval '194 sec', 'Popular music'),
241 (8, 'Ac-cent-tchu-ate the Positive', '01.01.1792', interval '227 sec', 'Popular music'),
242 (9, 'Chickery Chick', '01.01.1791', interval '172 sec', 'Jazz'),
243 (10, 'There! I’ve Said it Again', '01.01.1786', interval '266 sec', 'Jazz'),
244 (11, 'Candy', '01.01.1780', interval '258 sec', 'Jazz'),
245 (12, 'I’m Beginning to See The Light', '01.01.1790', interval '272 sec', 'Jazz'),
246 (13, 'On the Atchison, Topeka & the Santa Fe', '01.01.1779', interval '256 sec', 'Reggae'),
247 (14, 'I’m Beginning to See The Light', '01.01.1795', interval '246 sec', 'Reggae'),
248 (15, 'It’s Been a Long, Long Time', '01.01.1792', interval '175 sec', 'Pop'),
249 (16, 'Nancy, With The Laughing Face', '01.01.1793', interval '184 sec', 'Classical'),
250 (17, 'Caledonia', '01.01.1792', interval '270 sec', 'Classical'),
251 (18, 'I’m Beginning to See The Light', '01.01.1790', interval '140 sec', 'Classical'),
252 (19, 'Ac-cent-tchu-ate the Positive', '01.01.1795', interval '250 sec', 'Classical'),
253 (20, 'Dream', '01.01.1794', interval '262 sec', 'Easy Listening');
254
255insert into music.singers
256values (1, 'The Andrews Sisters'),
257 (2, 'Les Brown'),
258 (3, 'Perry Como'),
259 (4, 'Les Brown'),
260 (5, 'Johnny Mercer'),
261 (6, 'Harry James'),
262 (7, 'Bing Crosby'),
263 (8, 'Johnny Mercer'),
264 (9, 'Sammy Kaye'),
265 (10, 'Vaughn Monroe'),
266 (11, 'Johnny Mercer'), -- Johnny Mercer & Joe Stafford
267 (12, 'Duke Ellington'),
268 (13, 'Judy Garland'),
269 (14, 'Ella Fitzgerald'), --'Ella Fitzgerald & The Ink Spots'
270 (15, 'Les Paul'),
271 (16, 'Frank Sinatra'),
272 (17, 'Louis Jordan'),
273 (18, 'Harry James'),
274 (19, 'Bing Crosby'), --'Bing Crosby & The Andrews Sisters'
275 (20, 'The Pied Pipers'),
276 (21, 'Joe Stafford'),
277 (22, 'The Ink Spots');
278
279insert into music.singers_on_track
280values (1, 1),
281 (2, 2),
282 (3, 3),
283 (4, 4),
284 (5, 5),
285 (6, 6),
286 (7, 7),
287 (8, 8),
288 (9, 9),
289 (10, 10),
290 (11, 11),
291 (11, 21),
292 (12, 12),
293 (13, 13),
294 (14, 14),
295 (14, 22),
296 (15, 15),
297 (16, 16),
298 (17, 17),
299 (18, 18),
300 (19, 19),
301 (19, 1),
302 (20, 20);
303
304insert into music.playlists
305values (1, 1, 'My', 'Мой плейлист, я его люблю'),
306 (2, 1, 'Тренировки', 'Плейлист для тренировок'),
307 (3, 3, 'Веселая', 'Для хорошего настроения'),
308 (4, 4, 'Драйв', 'Подборка на случай нехватки адреналина)'),
309 (5, 4, 'Спокойная', 'Если адреналина слишком много'),
310 (6, 8, 'Новый год!', 'Закройте глаза. Снег кружится летает, летает...');
311
312call music.create_playlist_from_singer(2, 4);
313-- ЗАПРОСЫ
314
315-- 1) Прослушивания трека по дням
316
317select t.track_nm as track_name,
318 tl.played_from_dttm,
319 count(*) over (partition by t.track_id order by tl.played_from_dttm) as amount_play
320from music.track_listening as tl
321 inner join music.tracks as t
322 on tl.track_id = t.track_id;
323
324
325-- 2) Топ прослушиваемых артистов
326
327select count(*),
328 t.track_nm
329from music.singers_on_track as sot
330 inner join music.tracks as t
331 on sot.track_id = t.track_id
332 inner join music.track_listening as tl
333 on tl.track_id = t.track_id
334group by t.track_nm
335order by count(*) asc;
336
337
338-- 3) Список пользователей, плэйлистов и колличества их прослушивания
339
340select u.user_nm,
341 p.playlist_nm,
342 count(*) over (partition by p.playlist_id) as amount_on_playlist
343from music.playlists as p
344 inner join music.track_in_playlist as sip
345 on p.playlist_id = sip.playlist_id
346 inner join music.users as u
347 on u.user_id = p.user_id
348 inner join music.tracks as t
349 on t.track_id = sip.track_id
350 inner join music.track_listening as tl
351 on tl.track_id = t.track_id;
352
353
354-- 4) Колличество различных исполнителей для юзера
355
356
357select u.user_nm,
358 count(s.singer_id) amount_different_singers
359from music.users as u
360 inner join music.playlists as p
361 on u.user_id = p.user_id
362 inner join music.track_in_playlist as sip
363 on sip.playlist_id = p.playlist_id
364 inner join music.tracks as t
365 on t.track_id = sip.track_id
366 inner join music.singers_on_track as sot
367 on sot.track_id = t.track_id
368 inner join music.singers as s
369 on s.singer_id = sot.singer_id
370group by u.user_nm;
371
372
373-- VIEW VIEW VIEW
374
375create schema music_view;
376
377-- VIEW сокрытие
378
379-- user
380
381create view music_view.user_view as
382select u.user_id,
383 regexp_replace(u.user_nm, '[[:alnum:]]', '*', 'g') as user_name,
384 u.birth_dt
385from music.users as u;
386
387-- singer
388create view music_view.singer_view as
389select s.singer_id,
390 regexp_replace(s.singer_nm, '[[:alnum:]]', '*', 'g') as singer_name
391from music.singers as s;
392
393-- validate_user_data
394create view music_view.data_view as
395select regexp_replace(data.card_nm, '[[:digit:]]', '*', 'g') as card_number,
396 regexp_replace(split_part(data.mail_nm, '@', 1), '[[:alpha:]]', '*', 'g') || '@' ||
397 split_part(data.mail_nm, '@', 2) as email,
398 data.subscr_type
399from music.validate_user_data as data;
400
401
402-- tracks
403create view music_view.tracks_view as
404select t.track_nm,
405 t.premiere_dt,
406 t.genre
407from music.tracks as t;
408
409-- playlist
410create view music_view.playlist_view as
411select p.playlist_nm,
412 p.playlist_desc
413from music.playlists as p;
414
415-- devices
416create view music_view.devices_view as
417select d.device_nm as device_name
418from music.devices as d;
419
420-- VIEW (ADVANCED)
421
422-- 1) user and his songs with playlist
423create view music_view.users_and_songs as
424select us.user_nm,
425 p.playlist_nm,
426 t.track_nm
427from music.users as us
428 inner join music.playlists as p
429 on p.user_id = us.user_id
430 inner join music.track_in_playlist as sip
431 on sip.playlist_id = p.playlist_id
432 inner join music.tracks as t
433 on t.track_id = sip.track_id;
434
435-- 2) tracks_listen and device
436create view music_view.songs_lsiten_with_singer as
437select d.device_nm,
438 tl.played_from_dttm,
439 tl.played_to_dttm,
440 t.track_nm,
441 s.singer_id
442from music.tracks as t
443 inner join music.track_listening as tl
444 on t.track_id = tl.track_id
445 inner join music.devices as d
446 on d.device_id = tl.device_id
447 inner join music.singers_on_track as sot
448 on sot.track_id = t.track_id
449 inner join music.singers as s
450 on s.singer_id = sot.singer_id;
451
452-- 3) User его колличество прослушиваний и последнее
453create view music_view.users_plays as
454select u.user_nm as user_name,
455 count(*) as amount_songs,
456 max(tl.played_to_dttm) as last_play
457from music.users as u
458 inner join music.devices as d
459 on u.user_id = d.user_id
460 inner join music.track_listening as tl
461 on tl.device_id = d.device_id
462group by u.user_nm
463order by amount_songs asc;
464
465call music.create_playlist_from_singer(6, 1);