summaryrefslogtreecommitdiff
path: root/util/updates/2020-08-17-reviews.sql
diff options
context:
space:
mode:
Diffstat (limited to 'util/updates/2020-08-17-reviews.sql')
-rw-r--r--util/updates/2020-08-17-reviews.sql71
1 files changed, 71 insertions, 0 deletions
diff --git a/util/updates/2020-08-17-reviews.sql b/util/updates/2020-08-17-reviews.sql
new file mode 100644
index 00000000..87ded565
--- /dev/null
+++ b/util/updates/2020-08-17-reviews.sql
@@ -0,0 +1,71 @@
+ALTER TABLE reports ADD COLUMN objectnum integer;
+UPDATE reports SET objectnum = regexp_replace(object, '^.+\.([0-9]+)$', '\1')::integer WHERE object LIKE '%.%';
+ALTER TABLE reports ALTER COLUMN object TYPE vndbid USING regexp_replace(object, '\.[0-9]+$','')::vndbid;
+ALTER TABLE reports DROP COLUMN rtype;
+DROP TYPE report_type;
+
+
+
+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,
+ rid int,
+ date timestamptz NOT NULL DEFAULT NOW(),
+ lastmod timestamptz,
+ summary text NOT NULL,
+ text text,
+ spoiler boolean NOT NULL
+);
+
+CREATE TABLE reviews_posts (
+ id vndbid NOT NULL,
+ num smallint NOT NULL,
+ uid integer,
+ date timestamptz NOT NULL DEFAULT NOW(),
+ edited timestamptz,
+ hidden boolean NOT NULL DEFAULT FALSE,
+ msg text NOT NULL DEFAULT '',
+ PRIMARY KEY(id, num)
+);
+
+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);
+CREATE UNIQUE INDEX reviews_votes_id_uid ON reviews_votes (id,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_posts ADD CONSTRAINT reviews_posts_id_fkey FOREIGN KEY (id) REFERENCES reviews (id) ON DELETE CASCADE;
+ALTER TABLE reviews_posts ADD CONSTRAINT reviews_posts_uid_fkey FOREIGN KEY (uid) REFERENCES users (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 false;
+UPDATE users SET perm_review = false WHERE not perm_dbmod;
+
+\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();