| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- Stars + the cached repos.star_count column + the trigger that |
| 4 | -- maintains it. |
| 5 | -- |
| 6 | -- The S11 status block claimed `repos.star_count` was already in |
| 7 | -- place; it was not. We add the column here so this sprint stands |
| 8 | -- alone (noted in the S26 status block). |
| 9 | -- |
| 10 | -- Deletion semantics: a star row is the user's choice. When the user |
| 11 | -- is hard-deleted (or the repo is hard-deleted) the row goes with |
| 12 | -- them via ON DELETE CASCADE; the AFTER DELETE trigger keeps |
| 13 | -- star_count consistent. |
| 14 | |
| 15 | -- +goose Up |
| 16 | ALTER TABLE repos |
| 17 | ADD COLUMN star_count bigint NOT NULL DEFAULT 0, |
| 18 | ADD COLUMN watcher_count bigint NOT NULL DEFAULT 0; |
| 19 | |
| 20 | CREATE TABLE stars ( |
| 21 | user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| 22 | repo_id bigint NOT NULL REFERENCES repos(id) ON DELETE CASCADE, |
| 23 | starred_at timestamptz NOT NULL DEFAULT now(), |
| 24 | PRIMARY KEY (user_id, repo_id) |
| 25 | ); |
| 26 | |
| 27 | -- Stars-of-a-user, recency-sorted: drives `/{user}?tab=stars`. |
| 28 | CREATE INDEX stars_user_starred_at_idx |
| 29 | ON stars (user_id, starred_at DESC); |
| 30 | |
| 31 | -- Stargazers-of-a-repo, recency-sorted: drives `/{owner}/{repo}/stargazers`. |
| 32 | CREATE INDEX stars_repo_starred_at_idx |
| 33 | ON stars (repo_id, starred_at DESC); |
| 34 | |
| 35 | -- +goose StatementBegin |
| 36 | CREATE OR REPLACE FUNCTION tg_stars_count_inc() RETURNS trigger |
| 37 | LANGUAGE plpgsql AS $$ |
| 38 | BEGIN |
| 39 | UPDATE repos SET star_count = star_count + 1 WHERE id = NEW.repo_id; |
| 40 | RETURN NEW; |
| 41 | END; |
| 42 | $$; |
| 43 | -- +goose StatementEnd |
| 44 | |
| 45 | -- +goose StatementBegin |
| 46 | CREATE OR REPLACE FUNCTION tg_stars_count_dec() RETURNS trigger |
| 47 | LANGUAGE plpgsql AS $$ |
| 48 | BEGIN |
| 49 | UPDATE repos SET star_count = GREATEST(star_count - 1, 0) |
| 50 | WHERE id = OLD.repo_id; |
| 51 | RETURN OLD; |
| 52 | END; |
| 53 | $$; |
| 54 | -- +goose StatementEnd |
| 55 | |
| 56 | CREATE TRIGGER stars_count_inc AFTER INSERT ON stars |
| 57 | FOR EACH ROW EXECUTE FUNCTION tg_stars_count_inc(); |
| 58 | |
| 59 | CREATE TRIGGER stars_count_dec AFTER DELETE ON stars |
| 60 | FOR EACH ROW EXECUTE FUNCTION tg_stars_count_dec(); |
| 61 | |
| 62 | -- +goose Down |
| 63 | DROP TRIGGER IF EXISTS stars_count_dec ON stars; |
| 64 | DROP TRIGGER IF EXISTS stars_count_inc ON stars; |
| 65 | DROP FUNCTION IF EXISTS tg_stars_count_dec(); |
| 66 | DROP FUNCTION IF EXISTS tg_stars_count_inc(); |
| 67 | DROP TABLE IF EXISTS stars; |
| 68 | ALTER TABLE repos |
| 69 | DROP COLUMN IF EXISTS watcher_count, |
| 70 | DROP COLUMN IF EXISTS star_count; |
| 71 |