summaryrefslogtreecommitdiff
path: root/util/updates/wip-reviews.sql
blob: 677eb7b63a82f96e0c33f5c1a370c643842cc03a (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
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;


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