diff options
| -rw-r--r-- | ChangeLog | 3 | ||||
| -rw-r--r-- | data/global.pl | 2 | ||||
| -rw-r--r-- | data/lang.txt | 6 | ||||
| -rw-r--r-- | data/style.css | 8 | ||||
| -rw-r--r-- | lib/VNDB/Handler/Releases.pm | 6 | ||||
| -rw-r--r-- | util/dump.sql | 9 | ||||
| -rw-r--r-- | util/updates/update_2.8.sql | 62 |
7 files changed, 81 insertions, 15 deletions
@@ -6,6 +6,9 @@ git - ? - Converted to ENUM data type: - vn_relations.relation - anime.type + - changes.type + - releases_rev.type + - releases_media.medium - New language: Hungarian - Complete rewrite of the Javascript code: - Intended to be less error prone, more maintainable, and easier to make diff --git a/data/global.pl b/data/global.pl index 9f176081..b1d4b031 100644 --- a/data/global.pl +++ b/data/global.pl @@ -66,7 +66,7 @@ our %S = (%S, 17 => [ '17+', 'CERO D' ], 18 => [ '18+', 'CERO Z' ], }, - release_types => [0..2], + release_types => [qw|complete partial trial|], platforms => [qw|win dos lin mac dvd gba msx nds nes p98 psp ps1 ps2 ps3 drc sat sfc wii xb3 oth|], media => { #DB qty? diff --git a/data/lang.txt b/data/lang.txt index dd5a59ad..d89f97d8 100644 --- a/data/lang.txt +++ b/data/lang.txt @@ -433,15 +433,15 @@ ru : Любительская группа # release types -:_rtype_0 +:_rtype_complete en : Complete ru : Полный -:_rtype_1 +:_rtype_partial en : Partial ru : Частичный -:_rtype_2 +:_rtype_trial en : Trial ru : Триальный diff --git a/data/style.css b/data/style.css index 483ef6d1..9aaceb59 100644 --- a/data/style.css +++ b/data/style.css @@ -1131,7 +1131,7 @@ div#iv_view { height: 11px; opacity: 0.5; } -.icons.rt0, .icons.rt1, .icons.rt2 { width: 11px; } +.icons.rtcomplete, .icons.rtpartial, .icons.rttrial { width: 11px; } acronym.icons, acronym.uicons { cursor: default; } a .icons { cursor: pointer } .icons.oth { background: none; } @@ -1153,9 +1153,9 @@ a .icons { cursor: pointer } .icons.xb3 { background-position: -16px -84px; } .icons.sat { background-position: -16px -98px; } -.icons.rt0 { background-position: -32px 0px; } -.icons.rt1 { background-position: -32px -14px; } -.icons.rt2 { background-position: -32px -28px; } +.icons.rtcomplete { background-position: -32px 0px; } +.icons.rtpartial { background-position: -32px -14px; } +.icons.rttrial { background-position: -32px -28px; } .icons.ext { background-position: -32px -42px; } .icons.msx { background-position: -32px -56px; } .icons.nes { background-position: -32px -70px; } diff --git a/lib/VNDB/Handler/Releases.pm b/lib/VNDB/Handler/Releases.pm index 62776fd2..3cc5d1bb 100644 --- a/lib/VNDB/Handler/Releases.pm +++ b/lib/VNDB/Handler/Releases.pm @@ -490,7 +490,7 @@ sub browse { { name => 'ln', required => 0, multi => 1, default => '', enum => $self->{languages} }, { name => 'pl', required => 0, multi => 1, default => '', enum => $self->{platforms} }, { name => 'me', required => 0, multi => 1, default => '', enum => [ keys %{$self->{media}} ] }, - { name => 'tp', required => 0, default => -1, enum => [ -1, @{$self->{release_types}} ] }, + { name => 'tp', required => 0, default => '', enum => [ '', @{$self->{release_types}} ] }, { name => 'pa', required => 0, default => 0, enum => [ 0..2 ] }, { name => 'fw', required => 0, default => 0, enum => [ 0..2 ] }, { name => 'do', required => 0, default => 0, enum => [ 0..2 ] }, @@ -509,7 +509,7 @@ sub browse { $f->{ln}[0] ? (languages => $f->{ln}) : (), $f->{me}[0] ? (media => $f->{me}) : (), $f->{re}[0] ? (resolutions => $f->{re} ) : (), - $f->{tp} >= 0 ? (type => $f->{tp}) : (), + $f->{tp} ? (type => $f->{tp}) : (), $f->{ma_a} || $f->{ma_m} ? (minage => [$f->{ma_m}, $f->{ma_a}]) : (), $f->{pa} ? (patch => $f->{pa}) : (), $f->{fw} ? (freeware => $f->{fw}) : (), @@ -620,7 +620,7 @@ sub _filters { end; end; $self->htmlFormPart($f, [ select => short => 'tp', name => mt('_rbrowse_type'), - options => [ [-1, mt '_rbrowse_all'], map [ $_, mt "_rtype_$_" ], @{$self->{release_types}} ]]); + options => [ ['', mt '_rbrowse_all'], map [ $_, mt "_rtype_$_" ], @{$self->{release_types}} ]]); $self->htmlFormPart($f, [ select => short => 'pa', name => mt('_rbrowse_patch'), options => [ [0, mt '_rbrowse_all' ], [1, mt '_rbrowse_patchonly'], [2, mt '_rbrowse_patchnone']]]); $self->htmlFormPart($f, [ select => short => 'fw', name => mt('_rbrowse_freeware'), diff --git a/util/dump.sql b/util/dump.sql index 8a8d2c61..145c09e8 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -8,6 +8,7 @@ CREATE LANGUAGE plpgsql; CREATE TYPE anime_type AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv'); CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p'); CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc'); +CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial'); CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig'); @@ -116,7 +117,7 @@ CREATE TABLE releases_rev ( rid integer NOT NULL DEFAULT 0, title varchar(250) NOT NULL DEFAULT '', original varchar(250) NOT NULL DEFAULT '', - type smallint NOT NULL DEFAULT 0, + type release_type NOT NULL DEFAULT 'complete', website varchar(250) NOT NULL DEFAULT '', released integer NOT NULL, notes text NOT NULL DEFAULT '', @@ -448,7 +449,7 @@ BEGIN JOIN releases r1 ON rr1.id = r1.latest JOIN releases_vn rv1 ON rr1.id = rv1.rid WHERE rv1.vid = vn.id - AND rr1.type <> 2 + AND rr1.type <> ''trial'' AND r1.hidden = FALSE AND rr1.released <> 0 GROUP BY rv1.vid @@ -460,7 +461,7 @@ BEGIN JOIN releases r2 ON rr2.id = r2.latest JOIN releases_vn rv2 ON rr2.id = rv2.rid WHERE rv2.vid = vn.id - AND rr2.type <> 2 + AND rr2.type <> ''trial'' AND rr2.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer AND r2.hidden = FALSE GROUP BY rl2.lang @@ -473,7 +474,7 @@ BEGIN JOIN releases r3 ON rp3.rid = r3.latest JOIN releases_vn rv3 ON rp3.rid = rv3.rid WHERE rv3.vid = vn.id - AND rr3.type <> 2 + AND rr3.type <> ''trial'' AND rr3.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer AND r3.hidden = FALSE GROUP BY rp3.platform diff --git a/util/updates/update_2.8.sql b/util/updates/update_2.8.sql index ff8fe9dd..c8bcda38 100644 --- a/util/updates/update_2.8.sql +++ b/util/updates/update_2.8.sql @@ -75,3 +75,65 @@ ALTER TABLE changes ALTER COLUMN type TYPE dbentry_type USING ELSE NULL -- not allowed to happen, otherwise FIX YOUR DATABASE! END; + +-- releases_rev.type stored as enum +CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial'); +ALTER TABLE releases_rev ALTER COLUMN type DROP DEFAULT; +ALTER TABLE releases_rev ALTER COLUMN type TYPE release_type USING + CASE + WHEN type = 0 THEN 'complete'::release_type + WHEN type = 1 THEN 'partial' + WHEN type = 2 THEN 'trial' + ELSE NULL + END; +ALTER TABLE releases_rev ALTER COLUMN type SET DEFAULT 'complete'; + +CREATE OR REPLACE FUNCTION update_vncache(id integer) RETURNS void AS $$ +DECLARE + w text := ''; +BEGIN + IF id > 0 THEN + w := ' WHERE id = '||id; + END IF; + EXECUTE 'UPDATE vn SET + c_released = COALESCE((SELECT + MIN(rr1.released) + FROM releases_rev rr1 + JOIN releases r1 ON rr1.id = r1.latest + JOIN releases_vn rv1 ON rr1.id = rv1.rid + WHERE rv1.vid = vn.id + AND rr1.type <> ''trial'' + AND r1.hidden = FALSE + AND rr1.released <> 0 + GROUP BY rv1.vid + ), 0), + c_languages = COALESCE(ARRAY_TO_STRING(ARRAY( + SELECT rl2.lang + FROM releases_rev rr2 + JOIN releases_lang rl2 ON rl2.rid = rr2.id + JOIN releases r2 ON rr2.id = r2.latest + JOIN releases_vn rv2 ON rr2.id = rv2.rid + WHERE rv2.vid = vn.id + AND rr2.type <> ''trial'' + AND rr2.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer + AND r2.hidden = FALSE + GROUP BY rl2.lang + ORDER BY rl2.lang + ), ''/''), ''''), + c_platforms = COALESCE(ARRAY_TO_STRING(ARRAY( + SELECT rp3.platform + FROM releases_platforms rp3 + JOIN releases_rev rr3 ON rp3.rid = rr3.id + JOIN releases r3 ON rp3.rid = r3.latest + JOIN releases_vn rv3 ON rp3.rid = rv3.rid + WHERE rv3.vid = vn.id + AND rr3.type <> ''trial'' + AND rr3.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer + AND r3.hidden = FALSE + GROUP BY rp3.platform + ORDER BY rp3.platform + ), ''/''), '''') + '||w; +END; +$$ LANGUAGE plpgsql; + |