summaryrefslogtreecommitdiff
path: root/util/updates/update_2.16.sql
blob: 86654c722bc6bd455e36e80d33a693e727f803f7 (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

-- remove the NOT NULL from rr.minage and use -1 when unknown
UPDATE releases_rev SET minage = -1 WHERE minage IS NULL;
ALTER TABLE releases_rev ALTER COLUMN minage SET DEFAULT -1;
ALTER TABLE releases_rev ALTER COLUMN minage DROP NOT NULL;


-- speed up get-releases-by-vn queries
CREATE INDEX releases_vn_vid ON releases_vn (vid);


-- add vnlists table
CREATE TABLE vnlists (
  uid integer NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  vid integer NOT NULL REFERENCES vn (id),
  status smallint NOT NULL DEFAULT 0,
  added TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  notes varchar NOT NULL DEFAULT '',
  PRIMARY KEY(uid, vid)
);


-- load new function(s)
\i util/sql/func.sql


-- convert from rlists.vstat
INSERT INTO vnlists (uid, vid, status, added) SELECT
    i.uid, i.vid, COALESCE(MIN(CASE WHEN rl.vstat = 0 THEN NULL ELSE rl.vstat END), 0), MIN(rl.added)
  FROM (
    SELECT DISTINCT rl.uid, rv.vid
      FROM rlists rl
      JOIN releases r ON r.id = rl.rid
      JOIN releases_vn rv ON rv.rid = r.latest
  ) AS i(uid,vid)
  JOIN rlists rl ON rl.uid = i.uid
  JOIN releases r ON r.id = rl.rid
  JOIN releases_vn rv ON rv.rid = r.latest AND rv.vid = i.vid
  GROUP BY i.uid, i.vid;


-- add constraints triggers
CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER DELETE ON vnlists DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER INSERT ON rlists  DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();

-- remove rlists.vstat and rename rlists.rstat
ALTER TABLE rlists DROP COLUMN vstat;
ALTER TABLE rlists RENAME COLUMN rstat TO status;



-- add users_prefs table
CREATE TYPE prefs_key AS ENUM ('l10n', 'skin', 'customcss', 'show_nsfw', 'hide_list', 'notify_dbedit', 'notify_announce');
CREATE TABLE users_prefs (
  uid integer NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  key prefs_key NOT NULL,
  value varchar NOT NULL,
  PRIMARY KEY(uid, key)
);

-- convert from users.* to users_prefs
INSERT INTO users_prefs (uid, key, value)
    SELECT id, 'skin'::prefs_key, skin FROM users WHERE skin <> ''
  UNION
    SELECT id, 'customcss', customcss FROM users WHERE customcss <> '';

ALTER TABLE users DROP COLUMN skin;
ALTER TABLE users DROP COLUMN customcss;