From 6bd0b0cd1f3892253d881f71533940f0cf07c13d Mon Sep 17 00:00:00 2001 From: Yorhel Date: Tue, 2 Oct 2018 12:54:41 +0200 Subject: DB: Convert resolution into an enum Been wanting to do this for a long time - using an integer index into an array that changes once in a while is way too fragile. Doubly so when said indices are also used in filters and URLs that can't be updated every time a new resolution is added. --- data/global.pl | 38 +++++++++++++++++++------------------- lib/Multi/API.pm | 2 +- lib/VNDB/Handler/Releases.pm | 15 +++++++-------- lib/VNDB/Handler/VNPage.pm | 14 +++++--------- lib/VNDB/Util/Misc.pm | 16 ++++++++++++++-- util/jsgen.pl | 4 ++-- util/sql/all.sql | 1 + util/sql/schema.sql | 4 ++-- util/updates/update_20181002.sql | 32 ++++++++++++++++++++++++++++++++ 9 files changed, 83 insertions(+), 43 deletions(-) create mode 100644 util/updates/update_20181002.sql diff --git a/data/global.pl b/data/global.pl index 7624be77..18e128ad 100644 --- a/data/global.pl +++ b/data/global.pl @@ -197,25 +197,25 @@ our %S; in => [ 0, 'Internet download', '', 'download' ], otc => [ 0, 'Other', '', 'cartridge'], ), - resolutions => [ - [ 'Unknown / console / handheld', '' ], # hardcoded to 0 in many places - [ 'Non-standard', '' ], # hardcoded to 1 in VNPage.pm - [ '640x480', '4:3' ], - [ '800x600', '4:3' ], - [ '1024x768', '4:3' ], - [ '1280x960', '4:3' ], - [ '1600x1200', '4:3' ], - [ '640x400', 'widescreen' ], - [ '960x600', 'widescreen' ], - [ '1024x576', 'widescreen' ], - [ '1024x600', 'widescreen' ], - [ '1024x640', 'widescreen' ], - [ '1280x720', 'widescreen' ], - [ '1280x800', 'widescreen' ], - [ '1366x768', 'widescreen' ], - [ '1600x900', 'widescreen' ], - [ '1920x1080', 'widescreen' ], - ], + resolutions => ordhash( + unknown => [ 'Unknown / console / handheld', '' ], # hardcoded in many places + nonstandard => [ 'Non-standard', '' ], # hardcoded in VNPage.pm + '640x480' => [ '640x480', '4:3' ], + '800x600' => [ '800x600', '4:3' ], + '1024x768' => [ '1024x768', '4:3' ], + '1280x960' => [ '1280x960', '4:3' ], + '1600x1200' => [ '1600x1200', '4:3' ], + '640x400' => [ '640x400', 'widescreen' ], + '960x600' => [ '960x600', 'widescreen' ], + '1024x576' => [ '1024x576', 'widescreen' ], + '1024x600' => [ '1024x600', 'widescreen' ], + '1024x640' => [ '1024x640', 'widescreen' ], + '1280x720' => [ '1280x720', 'widescreen' ], + '1280x800' => [ '1280x800', 'widescreen' ], + '1366x768' => [ '1366x768', 'widescreen' ], + '1600x900' => [ '1600x900', 'widescreen' ], + '1920x1080' => [ '1920x1080', 'widescreen' ], + ), tag_categories => ordhash( cont => 'Content', ero => 'Sexual content', diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm index c959539a..585c3d2c 100644 --- a/lib/Multi/API.pm +++ b/lib/Multi/API.pm @@ -602,7 +602,7 @@ my %GET_RELEASE = ( $_[0]{minage} = $_[0]{minage} < 0 ? undef : $_[0]{minage}*1; $_[0]{gtin} ||= undef; $_[0]{catalog} ||= undef; - $_[0]{resolution} = $_[0]{resolution} ? $VNDB::S{resolutions}[ $_[0]{resolution} ][0] : undef; + $_[0]{resolution} = $_[0]{resolution} eq 'unknown' ? undef : $VNDB::S{resolutions}{ $_[0]{resolution} }[0]; $_[0]{voiced} = $_[0]{voiced} ? $_[0]{voiced}*1 : undef; $_[0]{animation} = [ $_[0]{ani_story} ? $_[0]{ani_story}*1 : undef, diff --git a/lib/VNDB/Handler/Releases.pm b/lib/VNDB/Handler/Releases.pm index d69d20af..ad32e78f 100644 --- a/lib/VNDB/Handler/Releases.pm +++ b/lib/VNDB/Handler/Releases.pm @@ -62,7 +62,7 @@ sub page { [ notes => 'Notes', diff => qr/[ ,\n\.]/ ], [ platforms => 'Platforms', join => ', ', split => sub { map $self->{platforms}{$_}, @{$_[0]} } ], [ media => 'Media', join => ', ', split => sub { map fmtmedia($_->{medium}, $_->{qty}), @{$_[0]} } ], - [ resolution => 'Resolution', serialize => sub { $self->{resolutions}[$_[0]][0]; } ], + [ resolution => 'Resolution', serialize => sub { $self->{resolutions}{$_[0]}[0]; } ], [ voiced => 'Voiced', serialize => sub { $self->{voiced}[$_[0]] } ], [ ani_story => 'Story animation', serialize => sub { $self->{animated}[$_[0]] } ], [ ani_ero => 'Ero animation', serialize => sub { $self->{animated}[$_[0]] } ], @@ -164,10 +164,10 @@ sub _infotable { end; } - if($r->{resolution}) { + if($r->{resolution} ne 'unknown') { Tr; td 'Resolution'; - td $self->{resolutions}[$r->{resolution}][0]; + td $self->{resolutions}{$r->{resolution}}[0]; end; } @@ -324,7 +324,7 @@ sub edit { { post => 'notes', required => 0, default => '', maxlength => 10240 }, { post => 'platforms', required => 0, default => '', multi => 1, enum => [ keys %{$self->{platforms}} ] }, { post => 'media', required => 0, default => '' }, - { post => 'resolution',required => 0, default => 0, enum => [ 0..$#{$self->{resolutions}} ] }, + { post => 'resolution',required => 0, default => 0, enum => [ keys %{$self->{resolutions}} ] }, { post => 'voiced', required => 0, default => 0, enum => [ 0..$#{$self->{voiced}} ] }, { post => 'ani_story', required => 0, default => 0, enum => [ 0..$#{$self->{animated}} ] }, { post => 'ani_ero', required => 0, default => 0, enum => [ 0..$#{$self->{animated}} ] }, @@ -347,7 +347,8 @@ sub edit { $frm->{$_} = $frm->{$_} ? 1 : 0 for (qw|patch freeware doujin uncensored ihid ilock|); # reset some fields when the patch flag is set - $frm->{doujin} = $frm->{resolution} = $frm->{voiced} = $frm->{ani_story} = $frm->{ani_ero} = 0 if $frm->{patch}; + $frm->{doujin} = $frm->{voiced} = $frm->{ani_story} = $frm->{ani_ero} = 0 if $frm->{patch}; + $frm->{resolution} = 'unknown' if $frm->{patch}; $frm->{uncensored} = 0 if $frm->{minage} != 18; my $same = $rid && @@ -422,7 +423,7 @@ sub _form { rel_format => [ 'Format', [ select => short => 'resolution', name => 'Resolution', options => [ - map [ $_, @{$self->{resolutions}[$_]} ], 0..$#{$self->{resolutions}} ] ], + map [ $_, @{$self->{resolutions}{$_}} ], keys %{$self->{resolutions}} ] ], [ select => short => 'voiced', name => 'Voiced', options => [ map [ $_, $self->{voiced}[$_] ], 0..$#{$self->{voiced}} ] ], [ select => short => 'ani_story', name => 'Story animation', options => [ @@ -611,7 +612,6 @@ sub _fil_compat { { get => 'ma_a', required => 0, default => 0, enum => $self->{age_ratings} }, { get => 'mi', required => 0, default => 0, template => 'uint' }, { get => 'ma', required => 0, default => 99999999, template => 'uint' }, - { get => 're', required => 0, multi => 1, default => 0, enum => [ 1..$#{$self->{resolutions}} ] }, ); return () if $f->{_err}; $c{minage} = [ grep $_ >= 0 && ($f->{ma_m} ? $f->{ma_a} >= $_ : $f->{ma_a} <= $_), @{$self->{age_ratings}} ] if $f->{ma_a} || $f->{ma_m}; @@ -620,7 +620,6 @@ sub _fil_compat { $c{plat} = $f->{pl} if $f->{pl}[0]; $c{lang} = $f->{ln} if $f->{ln}[0]; $c{med} = $f->{me} if $f->{me}[0]; - $c{resolution} = $f->{re} if $f->{re}[0]; $c{type} = $f->{tp} if $f->{tp}; $c{patch} = $f->{pa} == 2 ? 0 : 1 if $f->{pa}; $c{freeware} = $f->{fw} == 2 ? 0 : 1 if $f->{fw}; diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm index 565d73ce..0bf9ec4d 100644 --- a/lib/VNDB/Handler/VNPage.pm +++ b/lib/VNDB/Handler/VNPage.pm @@ -122,13 +122,9 @@ my @rel_cols = ( na_for_patch => 1, default => 1, what => 'extended', - has_data => sub { !!$_[0]{resolution} }, + has_data => sub { $_[0]{resolution} ne 'unknown' }, draw => sub { - if($_[0]{resolution}) { - txt $TUWF::OBJ->{resolutions}[$_[0]{resolution}][0]; - } else { - txt 'Unknown'; - } + txt $_[0]{resolution} eq 'unknown' ? 'Unknown' : $TUWF::OBJ->{resolutions}{$_[0]{resolution}}[0]; }, }, { # Voiced id => 'voi', @@ -869,11 +865,11 @@ sub _release_icons { # Resolution column my $resolution = $rel->{resolution}; - if($resolution) { - my $resolution_type = $resolution == 1 ? 'custom' : $self->{resolutions}[$resolution][1] eq 'widescreen' ? '16-9' : '4-3'; + if($resolution ne 'unknown') { + my $resolution_type = $resolution eq 'nonstandard' ? 'custom' : $self->{resolutions}{$resolution}[1] eq 'widescreen' ? '16-9' : '4-3'; # Ugly workaround: PC-98 has non-square pixels, thus not widescreen $resolution_type = '4-3' if $resolution_type eq '16-9' && grep $_ eq 'p98', @{$rel->{platforms}}; - _release_icon "res$resolution_type", $self->{resolutions}[$resolution][0], "resolution_$resolution_type"; + _release_icon "res$resolution_type", $self->{resolutions}{$resolution}[0], "resolution_$resolution_type"; } # Media column diff --git a/lib/VNDB/Util/Misc.pm b/lib/VNDB/Util/Misc.pm index 660818a2..ee7e8875 100644 --- a/lib/VNDB/Util/Misc.pm +++ b/lib/VNDB/Util/Misc.pm @@ -40,8 +40,9 @@ sub filFetchDB { my $filters = fil_parse $overwrite // $pref, @{$filfields{$type}}; # compatibility - $self->authPref($prefname => fil_serialize $filters) - if $type eq 'vn' && _fil_vn_compat($self, $filters) && !defined $overwrite; + my $compat = ($type eq 'vn' && _fil_vn_compat($self, $filters)) + || ($type eq 'release' && _fil_release_compat($self, $filters)); + $self->authPref($prefname => fil_serialize $filters) if $compat && !defined $overwrite; # write the definite filter string in $overwrite $_[2] = fil_serialize({map +( @@ -100,6 +101,17 @@ sub _fil_vn_compat { } +sub _fil_release_compat { + my($self, $fil) = @_; + if($fil->{resolution} && $fil->{resolution} =~ /^[0-9]+$/) { + $fil->{resolution} = (keys %{$self->{resolutions}})[$fil->{resolution}] || 'unknown'; + return 1; + } + return 0; +} + + + sub bbSubstLinks { my ($self, $msg) = @_; diff --git a/util/jsgen.pl b/util/jsgen.pl index 8317f86f..828d090f 100755 --- a/util/jsgen.pl +++ b/util/jsgen.pl @@ -18,8 +18,8 @@ sub resolutions { my $cat = ''; my @r; my $push = \@r; - for my $i (0..$#{$S{resolutions}}) { - my $r = $S{resolutions}[$i]; + for my $i (keys %{$S{resolutions}}) { + my $r = $S{resolutions}{$i}; if($cat ne $r->[1]) { push @r, [$r->[1]]; $cat = $r->[1]; diff --git a/util/sql/all.sql b/util/sql/all.sql index 12f16b11..b96c1020 100644 --- a/util/sql/all.sql +++ b/util/sql/all.sql @@ -22,6 +22,7 @@ CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial'); CREATE TYPE tag_category AS ENUM('cont', 'ero', 'tech'); CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig'); +CREATE TYPE resolution AS ENUM ('unknown', 'nonstandard', '640x480', '800x600', '1024x768', '1280x960', '1600x1200', '640x400', '960x600', '1024x576', '1024x600', '1024x640', '1280x720', '1280x800', '1366x768', '1600x900', '1920x1080'); -- schema diff --git a/util/sql/schema.sql b/util/sql/schema.sql index 6c2d785b..9b031ae6 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -262,7 +262,7 @@ CREATE TABLE releases ( -- dbentry_type=r patch boolean NOT NULL DEFAULT FALSE, freeware boolean NOT NULL DEFAULT FALSE, doujin boolean NOT NULL DEFAULT FALSE, - resolution smallint NOT NULL DEFAULT 0, + resolution resolution NOT NULL DEFAULT 'unknown', voiced smallint NOT NULL DEFAULT 0, ani_story smallint NOT NULL DEFAULT 0, ani_ero smallint NOT NULL DEFAULT 0, @@ -284,7 +284,7 @@ CREATE TABLE releases_hist ( patch boolean NOT NULL DEFAULT FALSE, freeware boolean NOT NULL DEFAULT FALSE, doujin boolean NOT NULL DEFAULT FALSE, - resolution smallint NOT NULL DEFAULT 0, + resolution resolution NOT NULL DEFAULT 'unknown', voiced smallint NOT NULL DEFAULT 0, ani_story smallint NOT NULL DEFAULT 0, ani_ero smallint NOT NULL DEFAULT 0, diff --git a/util/updates/update_20181002.sql b/util/updates/update_20181002.sql new file mode 100644 index 00000000..d98ed764 --- /dev/null +++ b/util/updates/update_20181002.sql @@ -0,0 +1,32 @@ +CREATE TYPE resolution AS ENUM ('unknown', 'nonstandard', '640x480', '800x600', '1024x768', '1280x960', '1600x1200', '640x400', '960x600', '1024x576', '1024x600', '1024x640', '1280x720', '1280x800', '1366x768', '1600x900', '1920x1080'); + +CREATE OR REPLACE FUNCTION conv_resolution(integer) RETURNS resolution AS $$ +SELECT CASE + WHEN $1 = 0 THEN 'unknown'::resolution + WHEN $1 = 1 THEN 'nonstandard' + WHEN $1 = 2 THEN '640x480' + WHEN $1 = 3 THEN '800x600' + WHEN $1 = 4 THEN '1024x768' + WHEN $1 = 5 THEN '1280x960' + WHEN $1 = 6 THEN '1600x1200' + WHEN $1 = 7 THEN '640x400' + WHEN $1 = 8 THEN '960x600' + WHEN $1 = 9 THEN '1024x576' + WHEN $1 = 10 THEN '1024x600' + WHEN $1 = 11 THEN '1024x640' + WHEN $1 = 12 THEN '1280x720' + WHEN $1 = 13 THEN '1280x800' + WHEN $1 = 14 THEN '1366x768' + WHEN $1 = 15 THEN '1600x900' + WHEN $1 = 16 THEN '1920x1080' +END $$ LANGUAGE SQL; + +ALTER TABLE releases ALTER COLUMN resolution DROP DEFAULT; +ALTER TABLE releases ALTER COLUMN resolution TYPE resolution USING conv_resolution(resolution); +ALTER TABLE releases ALTER COLUMN resolution SET DEFAULT 'unknown'; + +ALTER TABLE releases_hist ALTER COLUMN resolution DROP DEFAULT; +ALTER TABLE releases_hist ALTER COLUMN resolution TYPE resolution USING conv_resolution(resolution); +ALTER TABLE releases_hist ALTER COLUMN resolution SET DEFAULT 'unknown'; + +DROP FUNCTION conv_resolution(int); -- cgit v1.2.3