From a1b4da1d3ae9e6ed9326df41f9831be81f6b839a Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Mon, 13 Oct 2014 10:24:01 +0200
Subject: SQL: Use enum to represent platforms

I believe I didn't do this conversion earlier (back when I converted the
language types) because PostgreSQL didn't support dynamically adding new
values to an existing enum back then, and modifying an enum was a huge
pain. Recent versions do support this, so there's no reason to keep it
as a string.

...I just felt like adding some churn to the code base.
---
 util/sql/all.sql             | 1 +
 util/sql/func.sql            | 5 +++--
 util/sql/schema.sql          | 4 ++--
 util/updates/update_2.23.sql | 9 +++++++++
 4 files changed, 15 insertions(+), 4 deletions(-)

(limited to 'util')

diff --git a/util/sql/all.sql b/util/sql/all.sql
index 976bf217..9f2dfcc0 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -13,6 +13,7 @@ CREATE TYPE language          AS ENUM ('ar', 'cs', 'da', 'de', 'en', 'es', 'fi',
 CREATE TYPE medium            AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
 CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel', 'listdel', 'dbedit', 'announce');
 CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 'c', 't');
+CREATE TYPE platform          AS ENUM ('win', 'dos', 'lin', 'mac', 'ios', 'and', 'dvd', 'bdp', 'gba', 'gbc', 'msx', 'nds', 'nes', 'p88', 'p98', 'pcf', 'psp', 'ps1', 'ps2', 'ps3', 'ps4', 'psv', 'drc', 'sat', 'sfc', 'wii', 'n3d', 'xb1', 'xb3', 'xbo', 'web', 'oth');
 CREATE TYPE prefs_key         AS ENUM ('l10n', 'skin', 'customcss', 'filter_vn', 'filter_release', 'show_nsfw', 'hide_list', 'notify_nodbedit', 'notify_announce', 'vn_list_own', 'vn_list_wish');
 CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori');
 CREATE TYPE release_type      AS ENUM ('complete', 'partial', 'trial');
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 3923ffb5..4bcc8d99 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -54,7 +54,7 @@ CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$
       GROUP BY rl2.lang
       ORDER BY rl2.lang
     ),
-    c_platforms = COALESCE(ARRAY_TO_STRING(ARRAY(
+    c_platforms = ARRAY(
       SELECT rp3.platform
       FROM releases_platforms rp3
       JOIN releases_rev rr3 ON rp3.rid = rr3.id
@@ -65,7 +65,8 @@ CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$
       AND rr3.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer
       AND r3.hidden = FALSE
       GROUP BY rp3.platform
-      ORDER BY rp3.platform), '/'), '')
+      ORDER BY rp3.platform
+    )
   WHERE id = $1;
 $$ LANGUAGE sql;
 
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 0c9f0404..046eccf6 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -170,7 +170,7 @@ CREATE TABLE releases_media (
 -- releases_platforms
 CREATE TABLE releases_platforms (
   rid integer NOT NULL DEFAULT 0,
-  platform character(3) NOT NULL DEFAULT 0,
+  platform platform NOT NULL,
   PRIMARY KEY(rid, platform)
 );
 
@@ -391,7 +391,7 @@ CREATE TABLE vn (
   rgraph integer,
   c_released integer NOT NULL DEFAULT 0,
   c_languages language[] NOT NULL DEFAULT '{}',
-  c_platforms varchar NOT NULL DEFAULT '',
+  c_platforms platform[] NOT NULL DEFAULT '{}',
   c_popularity real,
   c_rating real,
   c_votecount integer NOT NULL DEFAULT 0,
diff --git a/util/updates/update_2.23.sql b/util/updates/update_2.23.sql
index e3b32a11..f93b1480 100644
--- a/util/updates/update_2.23.sql
+++ b/util/updates/update_2.23.sql
@@ -83,3 +83,12 @@ CREATE TABLE login_throttle (
 -- timeout is a timestamp...
 ALTER TABLE login_throttle ALTER COLUMN timeout TYPE timestamptz USING to_timestamp(timeout);
 
+
+-- platform from varchar to enum
+CREATE TYPE platform AS ENUM ('win', 'dos', 'lin', 'mac', 'ios', 'and', 'dvd', 'bdp', 'gba', 'gbc', 'msx', 'nds', 'nes', 'p88', 'p98', 'pcf', 'psp', 'ps1', 'ps2', 'ps3', 'ps4', 'psv', 'drc', 'sat', 'sfc', 'wii', 'n3d', 'xb1', 'xb3', 'xbo', 'web', 'oth');
+ALTER TABLE releases_platforms ALTER COLUMN platform DROP DEFAULT;
+ALTER TABLE releases_platforms ALTER COLUMN platform TYPE platform USING platform::platform;
+
+ALTER TABLE vn ALTER COLUMN c_platforms DROP DEFAULT;
+ALTER TABLE vn ALTER COLUMN c_platforms TYPE platform[] USING string_to_array(c_platforms, '/')::platform[];
+ALTER TABLE vn ALTER COLUMN c_platforms SET DEFAULT '{}';
-- 
cgit v1.2.3