SQL: Converted releases_rev.type to an ENUM data type

2 files changed, 67 insertions, 4 deletions

diff --git a/util/dump.sql b/util/dump.sql
index 8a8d2c61..145c09e8 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -8,6 +8,7 @@ CREATE LANGUAGE plpgsql;
 CREATE TYPE anime_type   AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv');
 CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p');
 CREATE TYPE medium       AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
+CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial');
 CREATE TYPE vn_relation  AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
 
 
@@ -116,7 +117,7 @@ CREATE TABLE releases_rev (
   rid integer NOT NULL DEFAULT 0,
   title varchar(250) NOT NULL DEFAULT '',
   original varchar(250) NOT NULL DEFAULT '',
-  type smallint NOT NULL DEFAULT 0,
+  type release_type NOT NULL DEFAULT 'complete',
   website varchar(250) NOT NULL DEFAULT '',
   released integer NOT NULL,
   notes text NOT NULL DEFAULT '',
@@ -448,7 +449,7 @@ BEGIN
       JOIN releases r1 ON rr1.id = r1.latest
       JOIN releases_vn rv1 ON rr1.id = rv1.rid
       WHERE rv1.vid = vn.id
-      AND rr1.type <> 2
+      AND rr1.type <> ''trial''
       AND r1.hidden = FALSE
       AND rr1.released <> 0
       GROUP BY rv1.vid
@@ -460,7 +461,7 @@ BEGIN
       JOIN releases r2 ON rr2.id = r2.latest
       JOIN releases_vn rv2 ON rr2.id = rv2.rid
       WHERE rv2.vid = vn.id
-      AND rr2.type <> 2
+      AND rr2.type <> ''trial''
       AND rr2.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
       AND r2.hidden = FALSE
       GROUP BY rl2.lang
@@ -473,7 +474,7 @@ BEGIN
       JOIN releases r3 ON rp3.rid = r3.latest
       JOIN releases_vn rv3 ON rp3.rid = rv3.rid
       WHERE rv3.vid = vn.id
-      AND rr3.type <> 2
+      AND rr3.type <> ''trial''
       AND rr3.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
       AND r3.hidden = FALSE
       GROUP BY rp3.platform
diff --git a/util/updates/update_2.8.sql b/util/updates/update_2.8.sql
index ff8fe9dd..c8bcda38 100644
--- a/util/updates/update_2.8.sql
+++ b/util/updates/update_2.8.sql
@@ -75,3 +75,65 @@ ALTER TABLE changes ALTER COLUMN type TYPE dbentry_type USING
     ELSE NULL -- not allowed to happen, otherwise FIX YOUR DATABASE!
   END;
 
+
+-- releases_rev.type stored as enum
+CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial');
+ALTER TABLE releases_rev ALTER COLUMN type DROP DEFAULT;
+ALTER TABLE releases_rev ALTER COLUMN type TYPE release_type USING
+  CASE
+    WHEN type = 0 THEN 'complete'::release_type
+    WHEN type = 1 THEN 'partial'
+    WHEN type = 2 THEN 'trial'
+    ELSE NULL
+  END;
+ALTER TABLE releases_rev ALTER COLUMN type SET DEFAULT 'complete';
+
+CREATE OR REPLACE FUNCTION update_vncache(id integer) RETURNS void AS $$
+DECLARE
+  w text := '';
+BEGIN
+  IF id > 0 THEN
+    w := ' WHERE id = '||id;
+  END IF;
+  EXECUTE 'UPDATE vn SET
+    c_released = COALESCE((SELECT
+      MIN(rr1.released)
+      FROM releases_rev rr1
+      JOIN releases r1 ON rr1.id = r1.latest
+      JOIN releases_vn rv1 ON rr1.id = rv1.rid
+      WHERE rv1.vid = vn.id
+      AND rr1.type <> ''trial''
+      AND r1.hidden = FALSE
+      AND rr1.released <> 0
+      GROUP BY rv1.vid
+    ), 0),
+    c_languages = COALESCE(ARRAY_TO_STRING(ARRAY(
+      SELECT rl2.lang
+      FROM releases_rev rr2
+      JOIN releases_lang rl2 ON rl2.rid = rr2.id
+      JOIN releases r2 ON rr2.id = r2.latest
+      JOIN releases_vn rv2 ON rr2.id = rv2.rid
+      WHERE rv2.vid = vn.id
+      AND rr2.type <> ''trial''
+      AND rr2.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
+      AND r2.hidden = FALSE
+      GROUP BY rl2.lang
+      ORDER BY rl2.lang
+    ), ''/''), ''''),
+    c_platforms = COALESCE(ARRAY_TO_STRING(ARRAY(
+      SELECT rp3.platform
+      FROM releases_platforms rp3
+      JOIN releases_rev rr3 ON rp3.rid = rr3.id
+      JOIN releases r3 ON rp3.rid = r3.latest
+      JOIN releases_vn rv3 ON rp3.rid = rv3.rid
+      WHERE rv3.vid = vn.id
+      AND rr3.type <> ''trial''
+      AND rr3.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
+      AND r3.hidden = FALSE
+      GROUP BY rp3.platform
+      ORDER BY rp3.platform
+    ), ''/''), '''')
+  '||w;
+END;
+$$ LANGUAGE plpgsql;
+