Changes between Version 3 and Version 4 of AdvancedDatabaseDevelopment

Timestamp:

02/11/26 21:39:17 (2 weeks ago)

Author:

231136

Comment:

--

AdvancedDatabaseDevelopment

@@ -297 +297 @@
 }}}
 
+=== Тригер што испраќа нотификација до создавачот на настан кога нов артист ќе сака да се пријави
+
+Прво ги креираме потребните табели:
+{{{
+CREATE TABLE events_requests(
+    artist_id BIGINT REFERENCES artists(user_id),
+    event_id BIGINT REFERENCES events(event_id),
+    creator_id BIGINT REFERENCES users(user_id),
+    message TEXT,
+    CONSTRAINT pk_request_for_events PRIMARY KEY (artist_id, event_id, creator_id)
+);
+
+CREATE TABLE event_requests_notifications(
+    notification_id BIGSERIAL PRIMARY KEY,
+
+    artist_id BIGINT,
+    event_id BIGINT,
+    creator_id BIGINT,
+
+    message TEXT,
+    status TEXT DEFAULT 'UNRESOLVED',
+    creator_has_read BOOLEAN DEFAULT FALSE,
+    created_at TIMESTAMP DEFAULT now(),
+
+    FOREIGN KEY (artist_id, event_id, creator_id)
+        REFERENCES events_requests(artist_id, event_id, creator_id)
+        ON DELETE CASCADE
+);
+}}}
+
+Правиме тригер кој при секое ново барање ќе испрати нотификација до создавачот на настанот:
+{{{
+CREATE OR REPLACE FUNCTION notify_event_creator()
+    RETURNS TRIGGER
+AS $$
+BEGIN
+    INSERT INTO event_requests_notifications (artist_id, event_id, creator_id, message)
+    VALUES (NEW.artist_id, NEW.event_id, NEW.creator_id, NEW.message);
+    RETURN NEW;
+END;
+$$
+LANGUAGE plpgsql;
+
+CREATE OR REPLACE TRIGGER update_event_requests_notifications
+    AFTER INSERT ON events_requests
+    FOR EACH ROW
+EXECUTE FUNCTION notify_event_creator();
+}}}
+
+Правиме тригер кој доколку барањето е прифатено додава нов запис во `performs_at` табелата.
+{{{
+CREATE OR REPLACE FUNCTION resolve_notification()
+    RETURNS TRIGGER
+AS $$
+BEGIN
+    IF NEW.status = 'ACCEPTED'
+        THEN
+        INSERT INTO performs_at (event_id, artist_id) VALUES (OLD.event_id, OLD.artist_id);
+    ELSIF NEW.status != 'REJECTED' THEN RAISE EXCEPTION 'Invalid status: %, Status must be "ACCEPTED" or "REJECTED"',
+        NEW.status;
+    END IF;
+    DELETE FROM events_requests WHERE
+        artist_id = OLD.artist_id AND
+        event_id = OLD.event_id AND
+        creator_id = OLD.creator_id;
+    RETURN NEW;
+END;
+$$
+LANGUAGE plpgsql;
+
+CREATE OR REPLACE TRIGGER event_requests_notifications_resolve
+    AFTER UPDATE ON event_requests_notifications
+    FOR EACH ROW
+EXECUTE FUNCTION resolve_notification();
+}}}
+
 == Погледи (Views)
+
+
+=== Поглед за 10 најслушани песни
+
+{{{
+create materialized view top_songs_by_listens as
+SELECT s.id, me.title, me.genre, u.full_name, u.username, me.cover, s.album_id
+FROM songs s
+JOIN listens l on l.song_id = s.id
+JOIN musical_entities me on s.id = me.id
+JOIN users u on u.user_id = me.released_by
+GROUP BY
+    s.id, me.title, me.genre, u.full_name, u.username, me.cover, s.album_id
+ORDER BY count(*) desc
+LIMIT 10;
+}}}
+
+=== Детален поглед за секоја песна
+{{{
+CREATE OR REPLACE VIEW song_details_view AS
+SELECT
+    s.id AS song_id,
+    me.title AS song_title,
+    me.genre AS song_genre,
+    me.cover AS song_cover,
+    s.link AS song_link,
+    alb_me.id AS album_id,
+    alb_me.title AS album_title,
+    u.user_id AS artist_user_id,
+    u.full_name AS artist_name,
+    u.username AS artist_username
+FROM project.songs s
+         JOIN project.musical_entities me ON s.id = me.id
+         JOIN project.albums alb ON s.album_id = alb.id
+         JOIN project.musical_entities alb_me ON alb.id = alb_me.id
+         JOIN project.artists art ON me.released_by = art.user_id
+         JOIN project.non_admin_users nau ON art.user_id = nau.user_id
+         JOIN users u ON nau.user_id = u.user_id;
+}}}
+
+=== Детален поглед за секоја плејлиста
+{{{
+CREATE OR REPLACE VIEW project.playlist_summary_view AS
+SELECT
+    p.playlist_id AS playlist_id,
+    p.name AS playlist_name,
+    p.cover AS playlist_cover,
+    p.created_by AS creator_id,
+    COUNT(ps.song_id) AS song_count
+FROM project.playlists p
+LEFT JOIN project.playlist_songs ps ON p.playlist_id = ps.playlist_id
+GROUP BY p.playlist_id, p.name, p.cover, p.created_by;
+}}}