diff options
| author | Yorhel <git@yorhel.nl> | 2014-10-13 10:24:01 +0200 |
|---|---|---|
| committer | Yorhel <git@yorhel.nl> | 2014-10-13 10:24:01 +0200 |
| commit | a1b4da1d3ae9e6ed9326df41f9831be81f6b839a (patch) | |
| tree | f9a6aff31799cb4eaa5ddc133553b9cdf789bfbe /util/sql | |
| parent | 26506d2bd7543aea1a4f49634be8b9bf110ad57c (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/sql')
| -rw-r--r-- | util/sql/all.sql | 1 | ||||
| -rw-r--r-- | util/sql/func.sql | 5 | ||||
| -rw-r--r-- | util/sql/schema.sql | 4 |
3 files changed, 6 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, |