Changes between Initial Version and Version 1 of AdvancedDatabaseDevelopment

Timestamp:

02/10/26 20:36:01 (3 weeks ago)

Author:

231136

Comment:

--

AdvancedDatabaseDevelopment

@@ +1 @@
+= Напреден развој на базата
+
+== Процедури
+
+=== Најпопуларна песна, албум и артист од изминатиот ден
+
+{{{
+CREATE OR REPLACE PROCEDURE yesterdays_most_popular()
+    language plpgsql
+as
+$$
+DECLARE
+    yesterday TIMESTAMP;
+BEGIN
+    yesterday := CURRENT_DATE - 1;
+
+    IF EXISTS (
+        SELECT 1
+        FROM listens
+        WHERE timestamp BETWEEN yesterday AND CURRENT_DATE
+    ) THEN
+        INSERT INTO daily_top_songs (day, song_id, play_count)
+        SELECT CURRENT_DATE - 1, s.id, COUNT(*)
+        FROM songs s
+        JOIN listens l ON l.song_id = s.id
+        WHERE l.timestamp BETWEEN yesterday AND NOW()
+        GROUP BY s.id
+        ORDER BY COUNT(*) DESC
+        LIMIT 1;
+
+        INSERT INTO daily_top_albums(day, album_id, play_count)
+        SELECT CURRENT_DATE - 1, a.id, COUNT(*)
+        FROM albums a
+        JOIN songs s on s.album_id = a.id
+        JOIN listens l on l.song_id = s.id
+        WHERE l.timestamp BETWEEN yesterday AND NOW()
+        GROUP BY a.id
+        ORDER BY COUNT(*) DESC
+        LIMIT 1;
+
+        INSERT INTO daily_top_artists(day, artist_id, play_count)
+        SELECT CURRENT_DATE - 1, a.user_id, COUNT(*)
+        FROM artists a
+        JOIN musical_entities me on me.released_by = a.user_id
+        JOIN listens l on l.song_id = me.id
+        WHERE l.timestamp BETWEEN yesterday AND NOW()
+        GROUP BY a.user_id
+        ORDER BY COUNT(*) DESC
+        LIMIT 1;
+
+    ELSE
+        INSERT INTO daily_top_songs (day, song_id, play_count)
+        VALUES (CURRENT_DATE - 1, NULL, 0);
+
+        INSERT INTO daily_top_albums (day, album_id, play_count)
+        VALUES (CURRENT_DATE - 1, NULL, 0);
+
+        INSERT INTO daily_top_artists (day, artist_id, play_count)
+        VALUES (CURRENT_DATE - 1, NULL, 0);
+    END IF;
+END;
+$$;
+}}}
+
+=== Известување на артисти кои треба да настапат на настан во наредните 7 дена
+
+Прво ја додаваме табелата каде ќе ги чуваме нотификациите:
+{{{
+CREATE TABLE event_notifications(
+    event_id BIGINT REFERENCES events(event_id),
+    artist_id BIGINT REFERENCES artists(user_id),
+    artist_has_read BOOLEAN,
+    message TEXT,
+
+    CONSTRAINT pk_event_notifications PRIMARY KEY (event_id, artist_id)
+);
+}}}
+
+Потоа, процедурата:
+
+{{{
+CREATE OR REPLACE PROCEDURE notify_event_performers()
+    language plpgsql
+AS
+$$
+DECLARE
+    to_notify RECORD;
+    next_seven_days DATE;
+BEGIN
+    next_seven_days := CURRENT_DATE + 7;
+
+    FOR to_notify IN SELECT
+                         e.event_id,
+                         a.user_id,
+                         e.name,
+                         e.venue,
+                         e.location,
+                         e.DATE - current_date AS days_until
+    FROM events e
+    JOIN performs_at pa ON pa.event_id=e.event_id
+    JOIN artists a ON pa.artist_id=a.user_id
+    WHERE e.DATE BETWEEN current_date AND next_seven_days AND
+    NOT EXISTS(
+        SELECT 1
+        FROM event_notifications en
+        WHERE en.event_id=e.event_id AND en.artist_id=pa.artist_id
+    )
+
+    LOOP
+        INSERT INTO event_notifications (event_id, artist_id, artist_has_read, message)
+        VALUES(
+               to_notify.event_id,
+               to_notify.user_id,
+               FALSE,
+               format(
+                  'You have an event at %s, %s IN %s days.',
+                   to_notify.venue,
+                   to_notify.location,
+                   to_notify.days_until
+               )
+            );
+    END LOOP;
+
+END;
+$$;
+}}}
+
+=== Известување артистите доколку нивна песна достигне одреден број на слушања
+
+Прво ги додаваме потребните табели:
+{{{
+CREATE TABLE song_play_counts (
+    song_id     BIGINT PRIMARY KEY REFERENCES songs(id),
+    play_count  BIGINT NOT NULL DEFAULT 0
+);
+
+CREATE TABLE song_play_milestones (
+    song_id  BIGINT PRIMARY KEY REFERENCES songs(id),
+    last_milestone BIGINT NOT NULL DEFAULT 0
+);
+
+CREATE TABLE milestone_notifications(
+    user_id BIGINT REFERENCES users(user_id),
+    song_id BIGINT REFERENCES songs(id),
+    milestone BIGINT ,
+    message TEXT,
+    artist_has_read BOOLEAN,
+
+    CONSTRAINT pk_milestone_notifications PRIMARY KEY (user_id,song_id,milestone)
+);
+
+CREATE TABLE last_processed_listen(
+    last_timestamp TIMESTAMP
+);
+}}}
+
+Иницијално поставуваме `last_timestamp` на `-infinity` за при првиот внес сите слушања да се земат предвид.
+{{{
+INSERT INTO last_processed_listen (last_timestamp)
+VALUES ('-infinity');
+}}}
+
+
+Потребна ни е процедура што периодично ќе ги земе сите нови слушања и соодветно ќе ги зголеми бројачите во `song_play_counts`.
+{{{
+
+CREATE OR REPLACE PROCEDURE update_song_play_counts()
+    language plpgsql
+AS
+$$
+DECLARE
+
+BEGIN
+    WITH new_listens AS (
+        SELECT l.song_id, COUNT(*) AS cnt
+        FROM listens l
+        WHERE l.TIMESTAMP > (
+            SELECT last_timestamp FROM last_processed_listen
+        )
+        GROUP BY l.song_id
+    )
+    INSERT INTO song_play_counts (song_id, play_count)
+    SELECT song_id, cnt
+    FROM new_listens
+    ON CONFLICT (song_id)
+        DO UPDATE
+        SET play_count = song_play_counts.play_count + EXCLUDED.play_count;
+
+    UPDATE last_processed_listen
+    SET last_timestamp = NOW();
+END;
+$$;
+}}}
+
+
+Потоа, процедура што ги наоѓа песните кои постигнале одреден успех - во нашиот случај, бидејќи базата е со малку податоци, сметаме дека 10 слушања се доволно голем успех за да го известиме артистот за истите. Откога песната ќе добие 10 слушања и артистот ќе биде известен, понатаму ќе биде известуван на 20, 30, 40 итн.
+
+{{{
+CREATE OR REPLACE PROCEDURE notify_artists_on_reached_milestone()
+    language plpgsql
+AS
+$$
+DECLARE
+
+BEGIN
+
+    INSERT INTO milestone_notifications (user_id, song_id, milestone, message, artist_has_read)
+    SELECT
+        u.user_id,
+        s.id,
+        spm.last_milestone+10,
+        format(
+            'Milestone reached! Song %s reached %s listens!',
+             me.title,
+             spm.last_milestone + 10
+        ),
+        FALSE
+    FROM song_play_counts spc
+    LEFT JOIN song_play_milestones spm ON spc.song_id = spm.song_id
+    LEFT JOIN songs s ON spc.song_id = s.id
+    LEFT JOIN musical_entities me ON s.id = me.id
+    LEFT JOIN users u ON u.user_id=me.released_by
+    WHERE spc.play_count >= coalesce(spm.last_milestone,0)+10
+    ON CONFLICT DO NOTHING;
+
+    INSERT INTO song_play_milestones (song_id, last_milestone)
+    SELECT
+        song_id,
+        MAX(milestone)
+    FROM milestone_notifications
+    GROUP BY song_id
+    ON CONFLICT (song_id)
+        DO UPDATE SET last_milestone = EXCLUDED.last_milestone;
+
+END;
+$$;
+}}}
+
+== Тригери
+
+=== Тригер што проверува дали дали албум или песна се слушани од корисник пред да бидат оценети.
+
+Не сакаме во базата да имаме оценки за песни или албуми коишто не биле слушани, но не може тоа да го спречиме со едноставен constraint, па воведуваме тригер при нови записи во базата.
+
+{{{
+CREATE OR REPLACE FUNCTION check_has_listened()
+    RETURNS TRIGGER
+AS $$
+BEGIN
+    IF EXISTS(
+        SELECT 1 FROM listens l
+        WHERE l.song_id = NEW.musical_entity_id
+          AND l.listener_id = NEW.listener_id
+    )
+        OR EXISTS(
+            SELECT 1 FROM albums a
+            JOIN songs s ON s.album_id = a.id
+            JOIN listens l ON s.id = l.song_id
+            WHERE a.id = NEW.musical_entity_id AND l.listener_id = NEW.listener_id
+        )
+    THEN RETURN NEW;
+    ELSE RAISE EXCEPTION 'listener_id=% has NOT listened to musical_entity_id=%',
+        NEW.listener_id,
+        NEW.musical_entity_id;
+    END IF;
+END;
+$$
+    LANGUAGE plpgsql;
+
+CREATE OR REPLACE TRIGGER has_listened_before_review
+    BEFORE INSERT ON reviews
+    FOR EACH ROW
+EXECUTE FUNCTION check_has_listened();
+}}}