diff options
author | Yorhel <git@yorhel.nl> | 2011-01-02 14:17:57 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2011-01-02 14:17:57 +0100 |
commit | b4e3c35620916852a6028ab5f6644382553408f9 (patch) | |
tree | 303248a812f6a7dc9c25bec0d8af5836c87fec3f /util/updates | |
parent | 632df9599de8dbb25707b0bf8caea075c55cfa3f (diff) | |
parent | 98f4725013b6d7a65e1fd07f7f02785b12e8a9bd (diff) |
Merge branch 'beta'2.16
Conflicts:
ChangeLog
lib/VNDB/Handler/ULists.pm
Diffstat (limited to 'util/updates')
-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; + |