summaryrefslogtreecommitdiff
path: root/util/updates/update_2.16.sql
diff options
context:
space:
mode:
Diffstat (limited to 'util/updates/update_2.16.sql')
-rw-r--r--util/updates/update_2.16.sql86
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;
+