From 50cfb305796d1d115c408feefbe39fddf7615166 Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
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 <git@yorhel.nl>
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 <git@yorhel.nl>
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 <git@yorhel.nl>
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 <git@yorhel.nl>
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 <git@yorhel.nl>
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 <git@yorhel.nl>
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 <git@yorhel.nl>
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 <git@yorhel.nl>
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 <git@yorhel.nl>
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

