From 50cfb305796d1d115c408feefbe39fddf7615166 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Fri, 17 Dec 2010 14:39:41 +0100 Subject: Don't allow NULL for rr.minage and use -1 for unknown This can be seen as a partial revert of 0a4f97f0186d6941a4cab2e3bd05201f1fed1441. I used to think using NULL for special values is more "correct" in database terms. But in the end I guess I should be aiming for whatever solution is easier. Both are "correct" in a sense anyway. --- util/updates/update_2.16.sql | 6 ++++++ 1 file changed, 6 insertions(+) create mode 100644 util/updates/update_2.16.sql (limited to 'util/updates/update_2.16.sql') diff --git a/util/updates/update_2.16.sql b/util/updates/update_2.16.sql new file mode 100644 index 00000000..8ffbd6ae --- /dev/null +++ b/util/updates/update_2.16.sql @@ -0,0 +1,6 @@ + +-- 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; + -- cgit v1.2.3 From a129097a782ced2f2c3622f239809a937ecdb7d4 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 18 Dec 2010 19:28:08 +0100 Subject: RFC-01: Implemented (most) of the SQL part - Created vnlists table - Converted rlists.vstat into that table - Added triggers to make sure that there is always a corresponding row in vnlists for every row in rlists. - Added a check on vnlists for the 'listdel' notify --- util/updates/update_2.16.sql | 38 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 38 insertions(+) (limited to 'util/updates/update_2.16.sql') diff --git a/util/updates/update_2.16.sql b/util/updates/update_2.16.sql index 8ffbd6ae..580be925 100644 --- a/util/updates/update_2.16.sql +++ b/util/updates/update_2.16.sql @@ -4,3 +4,41 @@ 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(), + PRIMARY KEY(uid, vid) +); + + +-- load new function(s) +\i util/sql/func.sql + + +-- convert from rlists.vstat +INSERT INTO vnlists 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(); + -- cgit v1.2.3 From 234864cf99fd0eb30cdc561e29dda14a1536cacc Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 19 Dec 2010 19:07:27 +0100 Subject: RFC-01: Code/DB cleanup and renamed some stuff for consistency --- util/updates/update_2.16.sql | 4 ++++ 1 file changed, 4 insertions(+) (limited to 'util/updates/update_2.16.sql') diff --git a/util/updates/update_2.16.sql b/util/updates/update_2.16.sql index 580be925..07050734 100644 --- a/util/updates/update_2.16.sql +++ b/util/updates/update_2.16.sql @@ -42,3 +42,7 @@ INSERT INTO vnlists SELECT 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; + -- cgit v1.2.3 From 19e353c480091ec607324ee09a8821dd3d895975 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Tue, 21 Dec 2010 10:37:54 +0100 Subject: Added notes field to the user VN list The interface to set this could be more dynamic, since it'll be a lot of work to set different notes for each VN. But oh well, let's first see how many people will use this feature. --- util/updates/update_2.16.sql | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'util/updates/update_2.16.sql') diff --git a/util/updates/update_2.16.sql b/util/updates/update_2.16.sql index 07050734..9ff96b32 100644 --- a/util/updates/update_2.16.sql +++ b/util/updates/update_2.16.sql @@ -15,6 +15,7 @@ CREATE TABLE vnlists ( 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) ); @@ -24,7 +25,7 @@ CREATE TABLE vnlists ( -- convert from rlists.vstat -INSERT INTO vnlists SELECT +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 -- cgit v1.2.3 From e0131b6ab501eaeda1003ac5131f803a36192d46 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Thu, 23 Dec 2010 11:48:09 +0100 Subject: Added users_prefs table and removed users.(skin|customcss) Will convert the other preferences later. --- util/updates/update_2.16.sql | 20 ++++++++++++++++++++ 1 file changed, 20 insertions(+) (limited to 'util/updates/update_2.16.sql') diff --git a/util/updates/update_2.16.sql b/util/updates/update_2.16.sql index 9ff96b32..86654c72 100644 --- a/util/updates/update_2.16.sql +++ b/util/updates/update_2.16.sql @@ -47,3 +47,23 @@ CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER INSERT ON rlists DEFERRABLE 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; + -- cgit v1.2.3 From 951568c87b1a1ad6fcb73928608f76b3470fd817 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Thu, 23 Dec 2010 12:05:57 +0100 Subject: Converted the show_nsfw preference to use the users_prefs table --- util/updates/update_2.16.sql | 3 +++ 1 file changed, 3 insertions(+) (limited to 'util/updates/update_2.16.sql') diff --git a/util/updates/update_2.16.sql b/util/updates/update_2.16.sql index 86654c72..a0678fd3 100644 --- a/util/updates/update_2.16.sql +++ b/util/updates/update_2.16.sql @@ -63,7 +63,10 @@ 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 <> ''; + UNION + SELECT id, 'show_nsfw'::prefs_key, '1' FROM users WHERE show_nsfw; ALTER TABLE users DROP COLUMN skin; ALTER TABLE users DROP COLUMN customcss; +ALTER TABLE users DROP COLUMN show_nsfw; -- cgit v1.2.3 From 6ff1efe0d07e24e9fb2db199c308c6cbed51e578 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Thu, 23 Dec 2010 12:51:35 +0100 Subject: Converted the show_list pref. to users_prefs and inverted the value In the users_prefs table, the default value should evaluate to 'false' in Perl, so show_list had to be inverted to hide_list. --- util/updates/update_2.16.sql | 3 +++ 1 file changed, 3 insertions(+) (limited to 'util/updates/update_2.16.sql') diff --git a/util/updates/update_2.16.sql b/util/updates/update_2.16.sql index a0678fd3..b401a7a4 100644 --- a/util/updates/update_2.16.sql +++ b/util/updates/update_2.16.sql @@ -65,8 +65,11 @@ INSERT INTO users_prefs (uid, key, value) SELECT id, 'customcss', customcss FROM users WHERE customcss <> ''; UNION SELECT id, 'show_nsfw'::prefs_key, '1' FROM users WHERE show_nsfw; + UNION + SELECT id, 'hide_list'::prefs_key, '1' FROM users WHERE NOT show_list; 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; -- cgit v1.2.3 From 3eb574e4e26162aa754372fff806e4c6de8f4754 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Thu, 23 Dec 2010 14:25:37 +0100 Subject: Converted the notify_announce and notify_dbedit preferences And renamed notify_dbedit to notify_nodbedit, since the default is to provide a notify on a database edit. Also fixed a few bugs along the way. --- util/updates/update_2.16.sql | 24 ++++++++++++++++-------- 1 file changed, 16 insertions(+), 8 deletions(-) (limited to 'util/updates/update_2.16.sql') diff --git a/util/updates/update_2.16.sql b/util/updates/update_2.16.sql index b401a7a4..c1b3358b 100644 --- a/util/updates/update_2.16.sql +++ b/util/updates/update_2.16.sql @@ -50,7 +50,7 @@ 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 TYPE prefs_key AS ENUM ('l10n', 'skin', 'customcss', '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, @@ -61,15 +61,23 @@ CREATE TABLE users_prefs ( -- 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 <> ''; - UNION - SELECT id, 'show_nsfw'::prefs_key, '1' FROM users WHERE show_nsfw; - UNION - SELECT id, 'hide_list'::prefs_key, '1' FROM users WHERE NOT show_list; - + 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; + -- cgit v1.2.3 From 5ac3d668fb56961aa43f824feb7dc3407b8eb33e Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 1 Jan 2011 15:05:34 +0100 Subject: More infrastructural changes to accomodate for the permanent filters - Added a 'prefs' option to htmlFooter() to add preference data for use by Javascript. - Added an /xml/prefs.xml URL for setting preferences from JS. - Added 'filter_*' keys to the prefs_key ENUM - Load filters by default on VN and Release browser --- util/updates/update_2.16.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'util/updates/update_2.16.sql') diff --git a/util/updates/update_2.16.sql b/util/updates/update_2.16.sql index c1b3358b..12f28121 100644 --- a/util/updates/update_2.16.sql +++ b/util/updates/update_2.16.sql @@ -50,7 +50,7 @@ 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_nodbedit', 'notify_announce'); +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, -- cgit v1.2.3 From 33a8b46e8a402fe0a6cc80786b0bdab9fb5d513e Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 2 Jan 2011 10:14:14 +0100 Subject: Bugfix: allow a VN to be available for more than 7 platforms This constraint was caused by the character length limit on vn.c_platforms. Trying to add or edit a release in such a way that a platform would be added to c_platforms would result in a 500. --- util/updates/update_2.16.sql | 3 +++ 1 file changed, 3 insertions(+) (limited to 'util/updates/update_2.16.sql') diff --git a/util/updates/update_2.16.sql b/util/updates/update_2.16.sql index 12f28121..2d354f03 100644 --- a/util/updates/update_2.16.sql +++ b/util/updates/update_2.16.sql @@ -81,3 +81,6 @@ 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; + -- cgit v1.2.3