diff options
Diffstat (limited to 'util/updates/update_2.16.sql')
-rw-r--r-- | util/updates/update_2.16.sql | 86 |
1 files changed, 86 insertions, 0 deletions
diff --git a/util/updates/update_2.16.sql b/util/updates/update_2.16.sql new file mode 100644 index 00000000..2d354f03 --- /dev/null +++ b/util/updates/update_2.16.sql @@ -0,0 +1,86 @@ + +-- 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', 'filter_vn', 'filter_release', 'show_nsfw', 'hide_list', 'notify_nodbedit', '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 ALL + SELECT id, 'customcss', customcss FROM users WHERE customcss <> '' + UNION ALL + SELECT id, 'show_nsfw', '1' FROM users WHERE show_nsfw + UNION ALL + SELECT id, 'hide_list', '1' FROM users WHERE NOT show_list + UNION ALL + SELECT id, 'notify_nodbedit', '1' FROM users WHERE NOT notify_dbedit + UNION ALL + SELECT id, 'notify_announce', '1' FROM users WHERE notify_announce; + +-- remove unused columns from the user table +ALTER TABLE users DROP COLUMN skin; +ALTER TABLE users DROP COLUMN customcss; +ALTER TABLE users DROP COLUMN show_nsfw; +ALTER TABLE users DROP COLUMN show_list; +ALTER TABLE users DROP COLUMN notify_dbedit; +ALTER TABLE users DROP COLUMN notify_announce; + + +-- remove size constraint on vn.c_platforms +ALTER TABLE vn ALTER COLUMN c_platforms TYPE varchar; + |