summaryrefslogtreecommitdiff
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
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.
-rw-r--r--lib/Multi/API.pm13
-rw-r--r--lib/VNDB/DB/VN.pm6
-rw-r--r--lib/VNDB/Util/BrowseHTML.pm2
-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
7 files changed, 26 insertions, 14 deletions
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 3dfaa2c9..52ac683a 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -160,6 +160,8 @@ sub filtertosql {
$v = "%$v%";
} elsif(${$o{process}} eq 'lang') {
return cerr $c, filter => 'Invalid language code', %e if !grep $v eq $_, @{$VNDB::S{languages}};
+ } elsif(${$o{process}} eq 'plat') {
+ return cerr $c, filter => 'Invalid platform code', %e if !grep $v eq $_, @{$VNDB::S{platforms}};
}
}
@@ -514,7 +516,7 @@ sub get_vn {
for (grep !/^(basic|details|anime|relations|tags|stats)$/, @{$get->{info}});
my $select = 'v.id, v.latest';
- $select .= ', vr.title, vr.original, v.c_released, v.c_languages::text[], v.c_olang::text[], v.c_platforms' if grep /basic/, @{$get->{info}};
+ $select .= ', vr.title, vr.original, v.c_released, v.c_languages::text[], v.c_olang::text[], v.c_platforms::text[]' if grep /basic/, @{$get->{info}};
$select .= ', vr.image, vr.img_nsfw, vr.alias AS aliases, vr.length, vr.desc AS description, vr.l_wp, vr.l_encubed, vr.l_renai' if grep /details/, @{$get->{info}};
$select .= ', v.c_popularity, v.c_rating, v.c_votecount' if grep /stats/, @{$get->{info}};
@@ -537,10 +539,9 @@ sub get_vn {
[ undef, 'v.c_released :op: 0', {qw|= = != <>|} ],
[ str => 'v.c_released :op: :value:', {qw|= = != <> > > < < <= <= >= >=|}, process => \&parsedate ],
], [ 'platforms',
- [ undef, "v.c_platforms :op: ''", {qw|= = != <>|} ],
- [ str => 'v.c_platforms :op: :value:', {'=' => 'LIKE', '!=' => 'NOT LIKE'}, process => \'like' ],
- [ stra => '(:value:)', {'=', 1}, join => ' OR ', serialize => 'v.c_platforms LIKE :value:', process => \'like' ],
- [ stra => '(:value:)', {'!=',1}, join => ' AND ', serialize => 'v.c_platforms NOT LIKE :value:', process => \'like' ],
+ [ undef, "v.c_platforms :op: '{}'", {qw|= = != <>|} ],
+ [ str => ':op: (v.c_platforms && ARRAY[:value:]::platform[])', {'=' => '', '!=' => 'NOT'}, process => \'plat' ],
+ [ stra => ':op: (v.c_platforms && ARRAY[:value:]::platform[])', {'=' => '', '!=' => 'NOT'}, join => ',', process => \'plat' ],
], [ 'languages',
[ undef, "v.c_languages :op: '{}'", {qw|= = != <>|} ],
[ str => ':op: (v.c_languages && ARRAY[:value:]::language[])', {'=' => '', '!=' => 'NOT'}, process => \'lang' ],
@@ -578,7 +579,7 @@ sub get_vn_res {
$_->{id}*=1;
if(grep /basic/, @{$get->{info}}) {
$_->{original} ||= undef;
- $_->{platforms} = [ split /\//, delete $_->{c_platforms} ];
+ $_->{platforms} = delete $_->{c_platforms};
$_->{languages} = delete $_->{c_languages};
$_->{orig_lang} = delete $_->{c_olang};
$_->{released} = formatdate delete $_->{c_released};
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index 83054b0a..cd844a27 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -46,7 +46,7 @@ sub dbVNGet {
$o{olang} ? (
'v.c_olang && ARRAY[!l]::language[]' => [ ref $o{olang} ? $o{olang} : [$o{olang}] ]) : (),
$o{plat} ? (
- '('.join(' OR ', map "v.c_platforms ILIKE '%%$_%%'", ref $o{plat} ? @{$o{plat}} : $o{plat}).')' => 1 ) : (),
+ 'v.c_platforms && ARRAY[!l]::platform[]' => [ ref $o{plat} ? $o{plat} : [$o{plat}] ]) : (),
defined $o{hasani} ? (
'!sEXISTS(SELECT 1 FROM vn_anime va WHERE va.vid = vr.id)' => [ $o{hasani} ? '' : 'NOT ' ]) : (),
defined $o{hasshot} ? (
@@ -100,8 +100,8 @@ sub dbVNGet {
);
my $tag_ids = $o{tag_inc} && join ',', ref $o{tag_inc} ? @{$o{tag_inc}} : $o{tag_inc};
- my @select = ( # see https://rt.cpan.org/Ticket/Display.html?id=54224 for the cast on c_languages
- qw|v.id v.locked v.hidden v.c_released v.c_languages::text[] v.c_platforms vr.title vr.original v.rgraph|, 'vr.id AS cid',
+ my @select = ( # see https://rt.cpan.org/Ticket/Display.html?id=54224 for the cast on c_languages and c_platforms
+ qw|v.id v.locked v.hidden v.c_released v.c_languages::text[] v.c_platforms::text[] vr.title vr.original v.rgraph|, 'vr.id AS cid',
$o{what} =~ /extended/ ? (
qw|vr.alias vr.image vr.img_nsfw vr.length vr.desc vr.l_wp vr.l_encubed vr.l_renai vr.l_vnn| ) : (),
$o{what} =~ /changes/ ? (
diff --git a/lib/VNDB/Util/BrowseHTML.pm b/lib/VNDB/Util/BrowseHTML.pm
index 94049cdc..40cc3270 100644
--- a/lib/VNDB/Util/BrowseHTML.pm
+++ b/lib/VNDB/Util/BrowseHTML.pm
@@ -199,7 +199,7 @@ sub htmlBrowseVN {
td class => 'tc8', defined($l->{wstat}) ? mt "_wish_$l->{wstat}" : '' if $f->{wish};
td class => 'tc2';
$_ ne 'oth' && cssicon $_, mt "_plat_$_"
- for (sort split /\//, $l->{c_platforms});
+ for (sort @{$l->{c_platforms}});
end;
td class => 'tc3';
cssicon "lang $_", mt "_lang_$_"
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 '{}';