summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--ChangeLog1
-rw-r--r--lib/VNDB/DB/Misc.pm6
-rw-r--r--lib/VNDB/DB/Releases.pm26
-rw-r--r--lib/VNDB/DB/ULists.pm21
-rw-r--r--lib/VNDB/Handler/Releases.pm21
-rw-r--r--lib/VNDB/Handler/ULists.pm2
-rw-r--r--lib/VNDB/Handler/VNPage.pm18
-rw-r--r--lib/VNDB/Util/FormHTML.pm11
-rw-r--r--util/dump.sql16
-rw-r--r--util/updates/update_2.5.sql61
10 files changed, 141 insertions, 42 deletions
diff --git a/ChangeLog b/ChangeLog
index c2a52374..cc16fa39 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -2,6 +2,7 @@
- Hide NSFW images in diff viewer (unless NSFW warnings are disabled)
- Display related boards in recent posts tooltip op homepage
- Added search box on user list
+ - Proper support for multilingual releases
2.4 - 2009-06-07
- Release search + browser + filters
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,18 +170,24 @@ 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 (?, ?)|,
$cid, $_
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 => [ \&gtintype, '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;
diff --git a/util/dump.sql b/util/dump.sql
index 5d13e027..6fe4bc08 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -79,6 +79,13 @@ CREATE TABLE releases (
hidden boolean NOT NULL DEFAULT FALSE
);
+-- releases_lang
+CREATE TABLE releases_lang (
+ rid integer NOT NULL,
+ lang varchar NOT NULL,
+ PRIMARY KEY(rid, lang)
+);
+
-- releases_media
CREATE TABLE releases_media (
rid integer NOT NULL DEFAULT 0,
@@ -108,7 +115,6 @@ CREATE TABLE releases_rev (
title varchar(250) NOT NULL DEFAULT '',
original varchar(250) NOT NULL DEFAULT '',
type smallint NOT NULL DEFAULT 0,
- language varchar NOT NULL DEFAULT 'ja',
website varchar(250) NOT NULL DEFAULT '',
released integer NOT NULL,
notes text NOT NULL DEFAULT '',
@@ -358,6 +364,7 @@ ALTER TABLE producers_rev ADD FOREIGN KEY (id) REFERENCES changes
ALTER TABLE producers_rev ADD FOREIGN KEY (pid) REFERENCES producers (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE quotes ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases ADD FOREIGN KEY (latest) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_lang ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases_media ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases_platforms ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases_producers ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
@@ -453,16 +460,17 @@ BEGIN
GROUP BY rv1.vid
), 0),
c_languages = COALESCE(ARRAY_TO_STRING(ARRAY(
- SELECT language
+ 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 <> 2
AND rr2.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
AND r2.hidden = FALSE
- GROUP BY rr2.language
- ORDER BY rr2.language
+ GROUP BY rl2.lang
+ ORDER BY rl2.lang
), ''/''), ''''),
c_platforms = COALESCE(ARRAY_TO_STRING(ARRAY(
SELECT rp3.platform
diff --git a/util/updates/update_2.5.sql b/util/updates/update_2.5.sql
new file mode 100644
index 00000000..dab07608
--- /dev/null
+++ b/util/updates/update_2.5.sql
@@ -0,0 +1,61 @@
+
+
+-- multilingual releases
+
+CREATE TABLE releases_lang (
+ rid integer NOT NULL REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED,
+ lang varchar NOT NULL,
+ PRIMARY KEY(rid, lang)
+);
+INSERT INTO releases_lang (rid, lang) SELECT id, language FROM releases_rev;
+ALTER TABLE releases_rev DROP COLUMN language;
+
+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 <> 2
+ 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 <> 2
+ 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 <> 2
+ 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;
+