summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2014-10-13 10:24:01 +0200
committerYorhel <git@yorhel.nl>2014-10-13 10:24:01 +0200
commita1b4da1d3ae9e6ed9326df41f9831be81f6b839a (patch)
treef9a6aff31799cb4eaa5ddc133553b9cdf789bfbe /util
parent26506d2bd7543aea1a4f49634be8b9bf110ad57c (diff)
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.
Diffstat (limited to 'util')
-rw-r--r--util/sql/all.sql1
-rw-r--r--util/sql/func.sql5
-rw-r--r--util/sql/schema.sql4
-rw-r--r--util/updates/update_2.23.sql9
4 files changed, 15 insertions, 4 deletions
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 '{}';