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

Процедури, Jobs

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

Прво ги креираме потребните табели:

CREATE TABLE daily_top_albums (
    day DATE,
    album_id BIGINT NULL,
    play_count BIGINT
);

CREATE TABLE daily_top_artists (
    day DATE,
    artist_id BIGINT NULL,
    play_count BIGINT
);

CREATE TABLE daily_top_songs (
    day DATE,
    song_id BIGINT NULL,
    play_count BIGINT
);

Потоа, процедурата:

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

Тригер што испраќа нотификација до создавачот на настан кога нов артист ќе сака да се пријави

Прво ги креираме потребните табели:

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;