summaryrefslogtreecommitdiff
path: root/util/updates/wip-reviews.sql
diff options
context:
space:
mode:
Diffstat (limited to 'util/updates/wip-reviews.sql')
-rw-r--r--util/updates/wip-reviews.sql51
1 files changed, 51 insertions, 0 deletions
diff --git a/util/updates/wip-reviews.sql b/util/updates/wip-reviews.sql
new file mode 100644
index 00000000..fbf588d3
--- /dev/null
+++ b/util/updates/wip-reviews.sql
@@ -0,0 +1,51 @@
+-- WIP: The modifications in this file are not final and haven't been integrated in sql/ yet.
+
+CREATE SEQUENCE reviews_seq;
+
+CREATE TABLE reviews (
+ id vndbid PRIMARY KEY DEFAULT vndbid('w', nextval('reviews_seq')::int) CONSTRAINT reviews_id_check CHECK(vndbid_type(id) = 'w'),
+ vid int NOT NULL,
+ uid int NOT NULL,
+ rid int,
+ date timestamptz NOT NULL DEFAULT NOW(),
+ lastmod timestamptz,
+ summary text NOT NULL,
+ text text,
+ spoiler boolean NOT NULL
+);
+
+CREATE TABLE reviews_votes (
+ id vndbid NOT NULL,
+ uid int,
+ date timestamptz NOT NULL,
+ vote boolean NOT NULL -- true = upvote, false = downvote
+);
+
+CREATE UNIQUE INDEX reviews_vid_uid ON reviews (vid,uid);
+CREATE INDEX reviews_uid ON reviews (uid);
+
+ALTER TABLE reviews ADD CONSTRAINT reviews_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id) ON DELETE CASCADE;
+ALTER TABLE reviews ADD CONSTRAINT reviews_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT;
+ALTER TABLE reviews ADD CONSTRAINT reviews_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id) ON DELETE SET DEFAULT;
+ALTER TABLE reviews_votes ADD CONSTRAINT reviews_votes_id_fkey FOREIGN KEY (id) REFERENCES reviews (id) ON DELETE CASCADE;
+ALTER TABLE reviews_votes ADD CONSTRAINT reviews_votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+
+ALTER TABLE users ADD COLUMN perm_review boolean NOT NULL DEFAULT true;
+UPDATE users SET perm_review = false WHERE not perm_board;
+
+\i sql/perms.sql
+
+--c_votes int NOT NULL DEFAULT 0,
+--c_avg float
+--
+--CREATE OR REPLACE FUNCTION update_reviews_vote_cache() RETURNS trigger AS $$
+--BEGIN
+-- WITH stats(id,cnt,avg) AS (
+-- SELECT id, COUNT(*), AVG(vote::int) FROM reviews_votes WHERE id IN(OLD.id,NEW.id) GROUP BY id
+-- ) UPDATE reviews SET c_votes = cnt, c_avg = avg FROM stats WHERE reviews.id = stats.id;
+-- RETURN NULL;
+--END
+--$$ LANGUAGE plpgsql;
+--
+--CREATE TRIGGER reviews_votes_cache1 AFTER INSERT OR DELETE ON reviews_votes FOR EACH ROW EXECUTE PROCEDURE update_reviews_vote_cache();
+--CREATE TRIGGER reviews_votes_cache2 AFTER UPDATE ON reviews_votes FOR EACH ROW WHEN ((OLD.id, OLD.vote) IS DISTINCT FROM (NEW.id, NEW.vote)) EXECUTE PROCEDURE update_reviews_vote_cache();