From 1c3769e0bac96979cc1aba0a80081b69a8470528 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 4 Jul 2009 13:53:36 +0200 Subject: Implemented support for multilingual releases The 'language' column in releases_rev has been replaced with a releases_lang table. As this is quite a big change, there may still be bugs floating around somewhere. --- lib/VNDB/DB/Misc.pm | 6 +++--- lib/VNDB/DB/Releases.pm | 26 ++++++++++++++++++++------ lib/VNDB/DB/ULists.pm | 21 ++++++++++++++++++--- lib/VNDB/Handler/Releases.pm | 21 ++++++++++++--------- lib/VNDB/Handler/ULists.pm | 2 +- lib/VNDB/Handler/VNPage.pm | 18 +++++++----------- lib/VNDB/Util/FormHTML.pm | 11 ++++++----- 7 files changed, 67 insertions(+), 38 deletions(-) (limited to 'lib') diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm index c13a38d2..1c21675e 100644 --- a/lib/VNDB/DB/Misc.pm +++ b/lib/VNDB/DB/Misc.pm @@ -163,10 +163,10 @@ sub dbItemMod { sub dbLanguages { my $self = shift; return [ - map $_->{language}, @{$self->dbAll(q| - SELECT DISTINCT rr.language + map $_->{lang}, @{$self->dbAll(q| + SELECT DISTINCT rl.lang FROM releases r - JOIN releases_rev rr ON rr.id = r.latest + JOIN releases_lang rl ON rl.rid = r.latest WHERE r.hidden = FALSE| )} ]; diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm index bf325266..bf40d8c2 100644 --- a/lib/VNDB/DB/Releases.pm +++ b/lib/VNDB/DB/Releases.pm @@ -33,7 +33,7 @@ sub dbReleaseGet { $o{date} ? ( '(rr.released >= ? AND rr.released <= ?)' => [ $o{date}[0], $o{date}[1] ] ) : (), $o{languages} ? ( - 'rr.language IN(!l)', => [ $o{languages} ] ) : (), + 'rr.id IN(SELECT irl.rid FROM releases_lang irl JOIN releases ir ON ir.latest = irl.rid WHERE irl.lang IN(!l))', => [ $o{languages} ] ) : (), $o{platforms} ? ( #'EXISTS(SELECT 1 FROM releases_platforms rp WHERE rp.rid = rr.id AND rp.platform IN(!l))' => [ $o{platforms} ] ) : (), 'rr.id IN(SELECT irp.rid FROM releases_platforms irp JOIN releases ir ON ir.latest = irp.rid WHERE irp.platform IN(!l))' => [ $o{platforms} ] ) : (), @@ -70,7 +70,7 @@ sub dbReleaseGet { ); my @select = ( - qw|r.id rr.title rr.original rr.language rr.website rr.released rr.minage rr.type rr.patch|, + qw|r.id rr.title rr.original rr.website rr.released rr.minage rr.type rr.patch|, 'rr.id AS cid', $o{what} =~ /extended/ ? qw|rr.notes rr.catalog rr.gtin rr.resolution rr.voiced rr.freeware rr.doujin rr.ani_story rr.ani_ero r.hidden r.locked| : (), $o{what} =~ /changes/ ? qw|c.added c.requester c.comments r.latest u.username c.rev| : (), @@ -85,15 +85,23 @@ sub dbReleaseGet { join(', ', @select), join(' ', @join), \@where, $o{order} ); - if(@$r && $o{what} =~ /(vn|producers|platforms|media)/) { + if(@$r) { my %r = map { $r->[$_]{producers} = []; $r->[$_]{platforms} = []; $r->[$_]{media} = []; $r->[$_]{vn} = []; + $r->[$_]{languages} = []; ($r->[$_]{cid}, $_) } 0..$#$r; + push(@{$r->[$r{$_->{rid}}]{languages}}, $_->{lang}) for (@{$self->dbAll(q| + SELECT rid, lang + FROM releases_lang + WHERE rid IN(!l)|, + [ keys %r ] + )}); + if($o{what} =~ /vn/) { push(@{$r->[$r{$_->{rid}}]{vn}}, $_) for (@{$self->dbAll(q| SELECT rv.rid, vr.vid, vr.title, vr.original @@ -162,17 +170,23 @@ sub dbReleaseAdd { # helper function, inserts a producer revision -# Arguments: global revision, item id, { columns in releases_rev + vn + producers + media + platforms } +# Arguments: global revision, item id, { columns in releases_rev + languages + vn + producers + media + platforms } sub insert_rev { my($self, $cid, $rid, $o) = @_; $self->dbExec(q| - INSERT INTO releases_rev (id, rid, title, original, gtin, catalog, language, website, released, + INSERT INTO releases_rev (id, rid, title, original, gtin, catalog, website, released, notes, minage, type, patch, resolution, voiced, freeware, doujin, ani_story, ani_ero) VALUES (!l)|, - [ $cid, $rid, @$o{qw| title original gtin catalog language website released + [ $cid, $rid, @$o{qw| title original gtin catalog website released notes minage type patch resolution voiced freeware doujin ani_story ani_ero|} ]); + $self->dbExec(q| + INSERT INTO releases_lang (rid, lang) + VALUES (?, ?)|, + $cid, $_ + ) for (@{$o->{languages}}); + $self->dbExec(q| INSERT INTO releases_producers (rid, pid) VALUES (?, ?)|, diff --git a/lib/VNDB/DB/ULists.pm b/lib/VNDB/DB/ULists.pm index 829ee537..4fca60f7 100644 --- a/lib/VNDB/DB/ULists.pm +++ b/lib/VNDB/DB/ULists.pm @@ -77,8 +77,8 @@ sub dbVNListList { $_->{vid}, $_->{rels} } @$r; - push @{$vns{$_->{vid}}}, $_ for (@{$self->dbAll(q| - SELECT rv.vid, rr.rid, rr.title, rr.original, rr.released, rr.type, rr.language, rr.minage, rl.rstat, rl.vstat + my $rel = $self->dbAll(q| + SELECT rv.vid, rr.rid, r.latest, rr.title, rr.original, rr.released, rr.type, rr.minage, rl.rstat, rl.vstat FROM rlists rl JOIN releases r ON rl.rid = r.id JOIN releases_rev rr ON rr.id = r.latest @@ -87,7 +87,22 @@ sub dbVNListList { AND rv.vid IN(!l) ORDER BY rr.released ASC|, $o{uid}, [ keys %vns ] - )}); + ); + + if(@$rel) { + my %rel = map { + $_->{languages}=[]; + $_->{latest}, $_->{languages} + } @$rel; + + push(@{$rel{$_->{rid}}}, $_->{lang}) for (@{$self->dbAll(q| + SELECT rid, lang + FROM releases_lang + WHERE rid IN(!l)|, + [ keys %rel ] + )}); + push @{$vns{$_->{vid}}}, $_ for @$rel; + } } return wantarray ? ($r, $np) : $r; diff --git a/lib/VNDB/Handler/Releases.pm b/lib/VNDB/Handler/Releases.pm index 6850d20c..a5ac1054 100644 --- a/lib/VNDB/Handler/Releases.pm +++ b/lib/VNDB/Handler/Releases.pm @@ -47,7 +47,7 @@ sub page { [ original => 'Original title', diff => 1 ], [ gtin => 'JAN/UPC/EAN', serialize => sub { $_[0]||'[none]' } ], [ catalog => 'Catalog number', serialize => sub { $_[0]||'[none]' } ], - [ language => 'Language', serialize => sub { $self->{languages}{$_[0]} } ], + [ languages => 'Language', join => ', ', split => sub { map $self->{languages}{$_}, @{$_[0]} } ], [ website => 'Website', ], [ released => 'Release date', htmlize => sub { datestr $_[0] } ], [ minage => 'Age rating', serialize => sub { $self->{age_ratings}{$_[0]}[0] } ], @@ -127,8 +127,11 @@ sub _infotable { Tr ++$i % 2 ? (class => 'odd') : (); td 'Language'; td; - cssicon "lang $r->{language}", $self->{languages}{$r->{language}}; - txt ' '.$self->{languages}{$r->{language}}; + for (@{$r->{languages}}) { + cssicon "lang $_", $self->{languages}{$_}; + txt ' '.$self->{languages}{$_}; + br if $_ ne $r->{languages}[$#{$r->{languages}}]; + } end; end; @@ -281,7 +284,7 @@ sub edit { my $vn = $rid ? $r->{vn} : [{ vid => $vid, title => $v->{title} }]; my %b4 = !$rid ? () : ( - (map { $_ => $r->{$_} } qw|type title original gtin catalog language website released + (map { $_ => $r->{$_} } qw|type title original gtin catalog languages website released notes minage platforms patch resolution voiced freeware doujin ani_story ani_ero|), media => join(',', sort map "$_->{medium} $_->{qty}", @{$r->{media}}), producers => join('|||', map "$_->{id},$_->{name}", sort { $a->{id} <=> $b->{id} } @{$r->{producers}}), @@ -300,7 +303,7 @@ sub edit { { name => 'gtin', required => 0, default => '0', func => [ \>intype, 'Not a valid JAN/UPC/EAN code' ] }, { name => 'catalog', required => 0, default => '', maxlength => 50 }, - { name => 'language', enum => [ keys %{$self->{languages}} ] }, + { name => 'languages', multi => 1, enum => [ keys %{$self->{languages}} ] }, { name => 'website', required => 0, default => '', template => 'url' }, { name => 'released', required => 0, default => 0, template => 'int' }, { name => 'minage' , required => 0, default => -1, enum => [ keys %{$self->{age_ratings}} ] }, @@ -332,7 +335,7 @@ sub edit { !grep !/^(platforms|producers|vn)$/ && $frm->{$_} ne $b4{$_}, keys %b4; my %opts = ( - (map { $_ => $frm->{$_} } qw| type title original gtin catalog language website released + (map { $_ => $frm->{$_} } qw| type title original gtin catalog languages website released notes minage platforms resolution editsum patch voiced freeware doujin ani_story ani_ero|), vn => $new_vn, producers => $producers, @@ -351,7 +354,7 @@ sub edit { } !defined $frm->{$_} && ($frm->{$_} = $b4{$_}) for keys %b4; - $frm->{language} = 'ja' if !$rid && !defined $frm->{lang}; + $frm->{languages} = ['ja'] if !$rid && !defined $frm->{languages}; $frm->{editsum} = sprintf 'Reverted to revision r%d.%d', $rid, $rev if $rev && !defined $frm->{editsum}; $self->htmlHeader(js => 'forms', title => $rid ? 'Edit '.$r->{title} : 'Add release to '.$v->{title}, noindex => 1); @@ -376,7 +379,7 @@ sub _form { [ input => short => 'title', name => 'Title (romaji)', width => 300 ], [ input => short => 'original', name => 'Original title', width => 300 ], [ static => content => 'The original title of this release, leave blank if it already is in the Latin alphabet.' ], - [ select => short => 'language', name => 'Language', + [ select => short => 'languages', name => 'Language(s)', multi => 1, options => [ map [ $_, "$_ ($self->{languages}{$_})" ], sort keys %{$self->{languages}} ] ], [ input => short => 'gtin', name => 'JAN/UPC/EAN' ], [ input => short => 'catalog', name => 'Catalog number' ], @@ -532,7 +535,7 @@ sub browse { td class => 'tc2', $l->{minage} > -1 ? $self->{age_ratings}{$l->{minage}}[0] : ''; td class => 'tc3'; $_ ne 'oth' && cssicon $_, $self->{platforms}{$_} for (@{$l->{platforms}}); - cssicon "lang $l->{language}", $self->{languages}{$l->{language}}; + cssicon "lang $_", $self->{languages}{$_} for (@{$l->{languages}}); cssicon lc(substr($self->{release_types}[$l->{type}],0,3)), $self->{release_types}[$l->{type}]; end; td class => 'tc4'; diff --git a/lib/VNDB/Handler/ULists.pm b/lib/VNDB/Handler/ULists.pm index 566a881c..3d101d8f 100644 --- a/lib/VNDB/Handler/ULists.pm +++ b/lib/VNDB/Handler/ULists.pm @@ -315,7 +315,7 @@ sub _vnlist_browse { lit datestr $_->{released}; end; td class => 'tc2'; - cssicon "lang $_->{language}", $self->{languages}{$_->{language}}; + cssicon "lang $_", $self->{languages}{$_} for @{$_->{languages}}; cssicon substr(lc $self->{release_types}[$_->{type}], 0, 3), $self->{release_types}[$_->{type}].' release'; end; td class => 'tc3'; diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm index 21c0e13d..8a3087f2 100644 --- a/lib/VNDB/Handler/VNPage.pm +++ b/lib/VNDB/Handler/VNPage.pm @@ -232,16 +232,14 @@ sub _producers { my($self, $i, $r) = @_; return if !grep @{$_->{producers}}, @$r; - my @lang; - for my $l (@$r) { - push @lang, $l->{language} if !grep $l->{language} eq $_, @lang; - } + my %lang; + my @lang = grep !$lang{$_}++, map @{$_->{languages}}, @$r; Tr ++$$i % 2 ? (class => 'odd') : (); td 'Producers'; td; for my $l (@lang) { - my %p = map { $_->{id} => $_ } map @{$_->{producers}}, grep $_->{language} eq $l, @$r; + my %p = map { $_->{id} => $_ } map @{$_->{producers}}, grep grep($_ eq $l, @{$_->{languages}}), @$r; my @p = values %p; next if !@p; cssicon "lang $l", $self->{languages}{$l}; @@ -411,10 +409,8 @@ sub _releases { } } - my @lang; - for my $l (@$r) { - push @lang, $l->{language} if !grep $l->{language} eq $_, @lang; - } + my %lang; + my @lang = grep !$lang{$_}++, map @{$_->{languages}}, @$r; table; for my $l (@lang) { @@ -424,7 +420,7 @@ sub _releases { txt $self->{languages}{$l}; end; end; - for my $rel (grep $l eq $_->{language}, @$r) { + for my $rel (grep grep($_ eq $l, @{$_->{languages}}), @$r) { Tr; td class => 'tc1'; lit datestr $rel->{released}; end; td class => 'tc2', $rel->{minage} < 0 ? '' : $self->{age_ratings}{$rel->{minage}}[0]; @@ -486,7 +482,7 @@ sub _screenshots { next if !@scr; Tr class => 'rel'; td colspan => 5; - cssicon 'lang '.$rel->{language}, $self->{languages}{$rel->{language}}; + cssicon "lang $_", $self->{languages}{$_} for (@{$rel->{languages}}); txt $rel->{title}; end; end; diff --git a/lib/VNDB/Util/FormHTML.pm b/lib/VNDB/Util/FormHTML.pm index 111fa5a6..e841fa65 100644 --- a/lib/VNDB/Util/FormHTML.pm +++ b/lib/VNDB/Util/FormHTML.pm @@ -187,14 +187,15 @@ sub htmlFormPart { } if(/select/) { my $l=''; - Select name => $o{short}, id => $o{short}, $o{width} ? (style => "width: $o{width}px") : (); - for (@{$o{options}}) { - if($_->[2] && $l ne $_->[2]) { + Select name => $o{short}, id => $o{short}, $o{width} ? (style => "width: $o{width}px") : (), $o{multi} ? (multiple => 'multiple', size => $o{size}||5) : (); + for my $p (@{$o{options}}) { + if($p->[2] && $l ne $p->[2]) { end if $l; - $l = $_->[2]; + $l = $p->[2]; optgroup label => $l; } - option value => $_->[0], defined $frm->{$o{short}} && $frm->{$o{short}} eq $_->[0] ? (selected => 'selected') : (), $_->[1]; + my $sel = defined $frm->{$o{short}} && ($frm->{$o{short}} eq $p->[0] || ref($frm->{$o{short}}) eq 'ARRAY' && grep $_ eq $p->[0], @{$frm->{$o{short}}}); + option value => $p->[0], $sel ? (selected => 'selected') : (), $p->[1]; } end if $l; end; -- cgit v1.2.3