Напреден развој на базата

Процедури

Најпопуларна песна, албум и артист од изминатиот ден

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();