diff options
| author | morkt <morkt@users.noreply.github.com> | 2015-11-10 06:06:54 +0400 |
|---|---|---|
| committer | morkt <morkt@users.noreply.github.com> | 2015-11-10 06:06:54 +0400 |
| commit | d488967ebdde36bf2c959f1d4e1c55c622887bf8 (patch) | |
| tree | deed89549ba634260924c932021be0de1796d62e /lib | |
| parent | ddbf3ae0ae530954b0e105e99819c85cfe4de17c (diff) | |
| parent | 052b78e84b4c7379f107cfaa5f9cede09b8b1b7b (diff) | |
Merge branch 'master' into poll
Diffstat (limited to 'lib')
30 files changed, 820 insertions, 767 deletions
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm index d6034160..1ed62682 100644 --- a/lib/Multi/API.pm +++ b/lib/Multi/API.pm @@ -11,7 +11,6 @@ use Multi::Core; use AnyEvent::Socket; use AnyEvent::Handle; use POE::Filter::VNDBAPI 'encode_filters'; -use Digest::SHA 'sha256'; use Encode 'encode_utf8', 'decode_utf8'; use Crypt::ScryptKDF 'scrypt_raw';; use VNDBUtil 'normalize_query', 'norm_ip'; @@ -277,10 +276,7 @@ sub login_verify { my $uid = $res->value(0,0); my $accepted = 0; - if(length $passwd == 41) { # Old sha256 - my $salt = substr $passwd, 0, 9; - $accepted = sha256($VNDB::S{global_salt}.encode_utf8($arg->{password}).$salt) eq substr $passwd, 9; - } elsif(length $passwd == 46) { # New scrypt + if(length $passwd == 46) { # scrypt my($N, $r, $p, $salt, $hash) = unpack 'NCCa8a*', $passwd; $accepted = $hash eq scrypt_raw($arg->{password}, $VNDB::S{scrypt_salt} . $salt, $N, $r, $p, 32); } else { @@ -359,8 +355,8 @@ sub splitarray { # } # filters => filters args for get_filters() (TODO: Document) my %GET_VN = ( - sql => 'SELECT %s FROM vn v JOIN vn_rev vr ON v.latest = vr.id WHERE NOT v.hidden AND (%s) %s', - select => 'v.id, v.latest', + sql => 'SELECT %s FROM vn v WHERE NOT v.hidden AND (%s) %s', + select => 'v.id', proc => sub { delete $_[0]{latest}; $_[0]{id} *= 1 @@ -368,12 +364,12 @@ my %GET_VN = ( sortdef => 'id', sorts => { id => 'v.id %s', - title => 'vr.title %s', + title => 'v.title %s', released => 'v.c_released %s', }, flags => { basic => { - select => 'vr.title, vr.original, v.c_released, v.c_languages, v.c_olang, v.c_platforms', + select => 'v.title, v.original, v.c_released, v.c_languages, v.c_olang, v.c_platforms', proc => sub { $_[0]{original} ||= undef; $_[0]{platforms} = splitarray delete $_[0]{c_platforms}; @@ -383,7 +379,7 @@ my %GET_VN = ( }, }, details => { - 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', + select => 'v.image, v.img_nsfw, v.alias AS aliases, v.length, v.desc AS description, v.l_wp, v.l_encubed, v.l_renai', proc => sub { $_[0]{aliases} ||= undef; $_[0]{length} *= 1; @@ -407,12 +403,12 @@ my %GET_VN = ( }, }, anime => { - fetch => [[ 'latest', 'SELECT va.vid, a.id, a.year, a.ann_id, a.nfo_id, a.type, a.title_romaji, a.title_kanji - FROM anime a JOIN vn_anime va ON va.aid = a.id WHERE va.vid IN(%s)', + fetch => [[ 'id', 'SELECT va.id AS vid, a.id, a.year, a.ann_id, a.nfo_id, a.type, a.title_romaji, a.title_kanji + FROM anime a JOIN vn_anime va ON va.aid = a.id WHERE va.id IN(%s)', sub { my($r, $n) = @_; # link for my $i (@$r) { - $i->{anime} = [ grep $i->{latest} == $_->{vid}, @$n ]; + $i->{anime} = [ grep $i->{id} == $_->{vid}, @$n ]; } # cleanup for (@$n) { @@ -425,16 +421,16 @@ my %GET_VN = ( ]], }, relations => { - fetch => [[ 'latest', 'SELECT vl.vid1, v.id, vl.relation, vr.title, vr.original FROM vn_relations vl - JOIN vn v ON v.id = vl.vid2 JOIN vn_rev vr ON vr.id = v.latest WHERE vl.vid1 IN(%s) AND NOT v.hidden', + fetch => [[ 'id', 'SELECT vr.id AS vid, v.id, vr.relation, v.title, v.original FROM vn_relations vr + JOIN vn v ON v.id = vr.vid WHERE vr.id IN(%s)', sub { my($r, $n) = @_; for my $i (@$r) { - $i->{relations} = [ grep $i->{latest} == $_->{vid1}, @$n ]; + $i->{relations} = [ grep $i->{id} == $_->{vid}, @$n ]; } for (@$n) { $_->{id} *= 1; $_->{original} ||= undef; - delete $_->{vid1}; + delete $_->{vid}; } } ]], @@ -460,17 +456,17 @@ my %GET_VN = ( [ inta => 'v.id :op:(:value:)', {'=' => 'IN', '!= ' => 'NOT IN'}, range => [1,1e6], join => ',' ], ], title => [ - [ str => 'vr.title :op: :value:', {qw|= = != <>|} ], - [ str => 'vr.title ILIKE :value:', {'~',1}, process => \'like' ], + [ str => 'v.title :op: :value:', {qw|= = != <>|} ], + [ str => 'v.title ILIKE :value:', {'~',1}, process => \'like' ], ], original => [ - [ undef, "vr.original :op: ''", {qw|= = != <>|} ], - [ str => 'vr.original :op: :value:', {qw|= = != <>|} ], - [ str => 'vr.original ILIKE :value:', {'~',1}, process => \'like' ] + [ undef, "v.original :op: ''", {qw|= = != <>|} ], + [ str => 'v.original :op: :value:', {qw|= = != <>|} ], + [ str => 'v.original ILIKE :value:', {'~',1}, process => \'like' ] ], firstchar => [ - [ undef, '(:op: ((ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)))', {'=', '', '!=', 'NOT'} ], - [ str => 'LOWER(SUBSTR(vr.title, 1, 1)) :op: :value:' => {qw|= = != <>|}, process => sub { shift =~ /^([a-z])$/ ? $1 : \'Invalid character' } ], + [ undef, '(:op: ((ASCII(v.title) < 97 OR ASCII(v.title) > 122) AND (ASCII(v.title) < 65 OR ASCII(v.title) > 90)))', {'=', '', '!=', 'NOT'} ], + [ str => 'LOWER(SUBSTR(v.title, 1, 1)) :op: :value:' => {qw|= = != <>|}, process => sub { shift =~ /^([a-z])$/ ? $1 : \'Invalid character' } ], ], released => [ [ undef, 'v.c_released :op: 0', {qw|= = != <>|} ], @@ -498,13 +494,13 @@ my %GET_VN = ( ); my %GET_RELEASE = ( - sql => 'SELECT %s FROM releases r JOIN releases_rev rr ON rr.id = r.latest WHERE NOT hidden AND (%s) %s', - select => 'r.id, r.latest', + sql => 'SELECT %s FROM releases r WHERE NOT hidden AND (%s) %s', + select => 'r.id', sortdef => 'id', sorts => { id => 'r.id %s', - title => 'rr.title %s', - released => 'rr.released %s', + title => 'r.title %s', + released => 'r.released %s', }, proc => sub { delete $_[0]{latest}; @@ -512,7 +508,7 @@ my %GET_RELEASE = ( }, flags => { basic => { - select => 'rr.title, rr.original, rr.released, rr.type, rr.patch, rr.freeware, rr.doujin', + select => 'r.title, r.original, r.released, r.type, r.patch, r.freeware, r.doujin', proc => sub { $_[0]{original} ||= undef; $_[0]{released} = formatdate($_[0]{released}); @@ -520,16 +516,16 @@ my %GET_RELEASE = ( $_[0]{freeware} = $_[0]{freeware} =~ /^t/ ? TRUE : FALSE; $_[0]{doujin} = $_[0]{doujin} =~ /^t/ ? TRUE : FALSE; }, - fetch => [[ 'latest', 'SELECT rid, lang FROM releases_lang WHERE rid IN(%s)', + fetch => [[ 'id', 'SELECT id, lang FROM releases_lang WHERE id IN(%s)', sub { my($n, $r) = @_; for my $i (@$n) { - $i->{languages} = [ map $i->{latest} == $_->{rid} ? $_->{lang} : (), @$r ]; + $i->{languages} = [ map $i->{id} == $_->{id} ? $_->{lang} : (), @$r ]; } }, ]], }, details => { - select => 'rr.website, rr.notes, rr.minage, rr.gtin, rr.catalog', + select => 'r.website, r.notes, r.minage, r.gtin, r.catalog', proc => sub { $_[0]{website} ||= undef; $_[0]{notes} ||= undef; @@ -538,30 +534,30 @@ my %GET_RELEASE = ( $_[0]{catalog} ||= undef; }, fetch => [ - [ 'latest', 'SELECT rid, platform FROM releases_platforms WHERE rid IN(%s)', + [ 'id', 'SELECT id, platform FROM releases_platforms WHERE id IN(%s)', sub { my($n, $r) = @_; for my $i (@$n) { - $i->{platforms} = [ map $i->{latest} == $_->{rid} ? $_->{platform} : (), @$r ]; + $i->{platforms} = [ map $i->{id} == $_->{id} ? $_->{platform} : (), @$r ]; } } ], - [ 'latest', 'SELECT rid, medium, qty FROM releases_media WHERE rid IN(%s)', + [ 'id', 'SELECT id, medium, qty FROM releases_media WHERE id IN(%s)', sub { my($n, $r) = @_; for my $i (@$n) { - $i->{media} = [ grep $i->{latest} == $_->{rid}, @$r ]; + $i->{media} = [ grep $i->{id} == $_->{id}, @$r ]; } for (@$r) { - delete $_->{rid}; + delete $_->{id}; $_->{qty} = $VNDB::S{media}{$_->{medium}} ? $_->{qty}*1 : undef; } } ], ] }, vn => { - fetch => [[ 'latest', 'SELECT rv.rid, v.id, vr.title, vr.original FROM releases_vn rv JOIN vn v ON v.id = rv.vid - JOIN vn_rev vr ON vr.id = v.latest WHERE NOT v.hidden AND rv.rid IN(%s)', + fetch => [[ 'id', 'SELECT rv.id AS rid, v.id, v.title, v.original FROM releases_vn rv JOIN vn v ON v.id = rv.vid + WHERE NOT v.hidden AND rv.id IN(%s)', sub { my($n, $r) = @_; for my $i (@$n) { - $i->{vn} = [ grep $i->{latest} == $_->{rid}, @$r ]; + $i->{vn} = [ grep $i->{id} == $_->{rid}, @$r ]; } for (@$r) { $_->{id}*=1; @@ -572,11 +568,11 @@ my %GET_RELEASE = ( ]], }, producers => { - fetch => [[ 'latest', 'SELECT rp.rid, rp.developer, rp.publisher, p.id, pr.type, pr.name, pr.original FROM releases_producers rp - JOIN producers p ON p.id = rp.pid JOIN producers_rev pr ON pr.id = p.latest WHERE NOT p.hidden AND rp.rid IN(%s)', + fetch => [[ 'id', 'SELECT rp.id AS rid, rp.developer, rp.publisher, p.id, p.type, p.name, p.original FROM releases_producers rp + JOIN producers p ON p.id = rp.pid WHERE NOT p.hidden AND rp.id IN(%s)', sub { my($n, $r) = @_; for my $i (@$n) { - $i->{producers} = [ grep $i->{latest} == $_->{rid}, @$r ]; + $i->{producers} = [ grep $i->{id} == $_->{rid}, @$r ]; } for (@$r) { $_->{id}*=1; @@ -595,47 +591,47 @@ my %GET_RELEASE = ( [ inta => 'r.id :op:(:value:)', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',', range => [1,1e6] ], ], vn => [ - [ 'int' => 'rr.id IN(SELECT rv.rid FROM releases_vn rv WHERE rv.vid = :value:)', {'=',1}, range => [1,1e6] ], + [ 'int' => 'r.id IN(SELECT rv.id FROM releases_vn rv WHERE rv.vid = :value:)', {'=',1}, range => [1,1e6] ], ], producer => [ - [ 'int' => 'rr.id IN(SELECT rp.rid FROM releases_producers rp WHERE rp.pid = :value:)', {'=',1}, range => [1,1e6] ], + [ 'int' => 'r.id IN(SELECT rp.id FROM releases_producers rp WHERE rp.pid = :value:)', {'=',1}, range => [1,1e6] ], ], title => [ - [ str => 'rr.title :op: :value:', {qw|= = != <>|} ], - [ str => 'rr.title ILIKE :value:', {'~',1}, process => \'like' ], + [ str => 'r.title :op: :value:', {qw|= = != <>|} ], + [ str => 'r.title ILIKE :value:', {'~',1}, process => \'like' ], ], original => [ - [ undef, "rr.original :op: ''", {qw|= = != <>|} ], - [ str => 'rr.original :op: :value:', {qw|= = != <>|} ], - [ str => 'rr.original ILIKE :value:', {'~',1}, process => \'like' ] + [ undef, "r.original :op: ''", {qw|= = != <>|} ], + [ str => 'r.original :op: :value:', {qw|= = != <>|} ], + [ str => 'r.original ILIKE :value:', {'~',1}, process => \'like' ] ], released => [ - [ undef, 'rr.released :op: 0', {qw|= = != <>|} ], - [ str => 'rr.released :op: :value:', {qw|= = != <> > > < < <= <= >= >=|}, process => \&parsedate ], + [ undef, 'r.released :op: 0', {qw|= = != <>|} ], + [ str => 'r.released :op: :value:', {qw|= = != <> > > < < <= <= >= >=|}, process => \&parsedate ], ], - patch => [ [ bool => 'rr.patch = :value:', {'=',1} ] ], - freeware => [ [ bool => 'rr.freeware = :value:', {'=',1} ] ], - doujin => [ [ bool => 'rr.doujin = :value:', {'=',1} ] ], + patch => [ [ bool => 'r.patch = :value:', {'=',1} ] ], + freeware => [ [ bool => 'r.freeware = :value:', {'=',1} ] ], + doujin => [ [ bool => 'r.doujin = :value:', {'=',1} ] ], type => [ - [ str => 'rr.type :op: :value:', {qw|= = != <>|}, + [ str => 'r.type :op: :value:', {qw|= = != <>|}, process => sub { !grep($_ eq $_[0], @{$VNDB::S{release_types}}) ? \'No such release type' : $_[0] } ], ], gtin => [ - [ 'int' => 'rr.gtin :op: :value:', {qw|= = != <>|}, process => sub { length($_[0]) > 14 ? \'Too long GTIN code' : $_[0] } ], + [ 'int' => 'r.gtin :op: :value:', {qw|= = != <>|}, process => sub { length($_[0]) > 14 ? \'Too long GTIN code' : $_[0] } ], ], catalog => [ - [ str => 'rr.catalog :op: :value:', {qw|= = != <>|} ], + [ str => 'r.catalog :op: :value:', {qw|= = != <>|} ], ], languages => [ - [ str => 'rr.id :op:(SELECT rl.rid FROM releases_lang rl WHERE rl.lang = :value:)', {'=' => 'IN', '!=' => 'NOT IN'}, process => \'lang' ], - [ stra => 'rr.id :op:(SELECT rl.rid FROM releases_lang rl WHERE rl.lang IN(:value:))', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',', process => \'lang' ], + [ str => 'r.id :op:(SELECT rl.id FROM releases_lang rl WHERE rl.lang = :value:)', {'=' => 'IN', '!=' => 'NOT IN'}, process => \'lang' ], + [ stra => 'r.id :op:(SELECT rl.id FROM releases_lang rl WHERE rl.lang IN(:value:))', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',', process => \'lang' ], ], }, ); my %GET_PRODUCER = ( - sql => 'SELECT %s FROM producers p JOIN producers_rev pr ON p.latest = pr.id WHERE NOT p.hidden AND (%s) %s', - select => 'p.id, p.latest', + sql => 'SELECT %s FROM producers p WHERE NOT p.hidden AND (%s) %s', + select => 'p.id', proc => sub { delete $_[0]{latest}; $_[0]{id} *= 1 @@ -643,17 +639,17 @@ my %GET_PRODUCER = ( sortdef => 'id', sorts => { id => 'p.id %s', - name => 'pr.name %s', + name => 'p.name %s', }, flags => { basic => { - select => 'pr.type, pr.name, pr.original, pr.lang AS language', + select => 'p.type, p.name, p.original, p.lang AS language', proc => sub { $_[0]{original} ||= undef; }, }, details => { - select => 'pr.website, pr.l_wp, pr.desc AS description, pr.alias AS aliases', + select => 'p.website, p.l_wp, p.desc AS description, p.alias AS aliases', proc => sub { $_[0]{description} ||= undef; $_[0]{aliases} ||= undef; @@ -664,16 +660,16 @@ my %GET_PRODUCER = ( }, }, relations => { - fetch => [[ 'latest', 'SELECT pl.pid1, p.id, pl.relation, pr.name, pr.original FROM producers_relations pl - JOIN producers p ON p.id = pl.pid2 JOIN producers_rev pr ON pr.id = p.latest WHERE pl.pid1 IN(%s) AND NOT p.hidden', + fetch => [[ 'id', 'SELECT pl.id AS pid, p.id, pl.relation, p.name, p.original FROM producers_relations pl + JOIN producers p ON p.id = pl.pid WHERE pl.id IN(%s)', sub { my($n, $r) = @_; for my $i (@$n) { - $i->{relations} = [ grep $i->{latest} == $_->{pid1}, @$r ]; + $i->{relations} = [ grep $i->{id} == $_->{pid}, @$r ]; } for (@$r) { $_->{id}*=1; $_->{original} ||= undef; - delete $_->{pid1}; + delete $_->{pid}; } }, ]], @@ -685,31 +681,31 @@ my %GET_PRODUCER = ( [ inta => 'p.id :op:(:value:)', {'=' => 'IN', '!= ' => 'NOT IN'}, join => ',', range => [1,1e6] ], ], name => [ - [ str => 'pr.name :op: :value:', {qw|= = != <>|} ], - [ str => 'pr.name ILIKE :value:', {'~',1}, process => \'like' ], + [ str => 'p.name :op: :value:', {qw|= = != <>|} ], + [ str => 'p.name ILIKE :value:', {'~',1}, process => \'like' ], ], original => [ - [ undef, "pr.original :op: ''", {qw|= = != <>|} ], - [ str => 'pr.original :op: :value:', {qw|= = != <>|} ], - [ str => 'pr.original ILIKE :value:', {'~',1}, process => \'like' ] + [ undef, "p.original :op: ''", {qw|= = != <>|} ], + [ str => 'p.original :op: :value:', {qw|= = != <>|} ], + [ str => 'p.original ILIKE :value:', {'~',1}, process => \'like' ] ], type => [ - [ str => 'pr.type :op: :value:', {qw|= = != <>|}, + [ str => 'p.type :op: :value:', {qw|= = != <>|}, process => sub { !grep($_ eq $_[0], @{$VNDB::S{producer_types}}) ? \'No such producer type' : $_[0] } ], ], language => [ - [ str => 'pr.lang :op: :value:', {qw|= = != <>|}, process => \'lang' ], - [ stra => 'pr.lang :op:(:value:)', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',', process => \'lang' ], + [ str => 'p.lang :op: :value:', {qw|= = != <>|}, process => \'lang' ], + [ stra => 'p.lang :op:(:value:)', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',', process => \'lang' ], ], search => [ - [ str => '(pr.name ILIKE :value: OR pr.original ILIKE :value: OR pr.alias ILIKE :value:)', {'~',1}, process => \'like' ], + [ str => '(p.name ILIKE :value: OR p.original ILIKE :value: OR p.alias ILIKE :value:)', {'~',1}, process => \'like' ], ], }, ); my %GET_CHARACTER = ( - sql => 'SELECT %s FROM chars c JOIN chars_rev cr ON c.latest = cr.id WHERE NOT c.hidden AND (%s) %s', - select => 'c.id, c.latest', + sql => 'SELECT %s FROM chars c WHERE NOT c.hidden AND (%s) %s', + select => 'c.id', proc => sub { delete $_[0]{latest}; $_[0]{id} *= 1 @@ -717,11 +713,11 @@ my %GET_CHARACTER = ( sortdef => 'id', sorts => { id => 'c.id %s', - name => 'cr.name %s', + name => 'c.name %s', }, flags => { basic => { - select => 'cr.name, cr.original, cr.gender, cr.bloodt, cr.b_day, cr.b_month', + select => 'c.name, c.original, c.gender, c.bloodt, c.b_day, c.b_month', proc => sub { $_[0]{original} ||= undef; $_[0]{gender} = undef if $_[0]{gender} eq 'unknown'; @@ -730,7 +726,7 @@ my %GET_CHARACTER = ( }, }, details => { - select => 'cr.alias AS aliases, cr.image, cr."desc" AS description', + select => 'c.alias AS aliases, c.image, c."desc" AS description', proc => sub { $_[0]{aliases} ||= undef; $_[0]{image} = $_[0]{image} ? sprintf '%s/ch/%02d/%d.jpg', $VNDB::S{url_static}, $_[0]{image}%100, $_[0]{image} : undef; @@ -738,16 +734,16 @@ my %GET_CHARACTER = ( }, }, meas => { - select => 'cr.s_bust AS bust, cr.s_waist AS waist, cr.s_hip AS hip, cr.height, cr.weight', + select => 'c.s_bust AS bust, c.s_waist AS waist, c.s_hip AS hip, c.height, c.weight', proc => sub { $_[0]{$_} = $_[0]{$_} ? $_[0]{$_}*1 : undef for(qw|bust waist hip height weight|); }, }, traits => { - fetch => [[ 'latest', 'SELECT cid, tid, spoil FROM chars_traits WHERE cid IN(%s)', + fetch => [[ 'id', 'SELECT id, tid, spoil FROM chars_traits WHERE id IN(%s)', sub { my($n, $r) = @_; for my $i (@$n) { - $i->{traits} = [ map [ $_->{tid}*1, $_->{spoil}*1 ], grep $i->{latest} == $_->{cid}, @$r ]; + $i->{traits} = [ map [ $_->{tid}*1, $_->{spoil}*1 ], grep $i->{id} == $_->{id}, @$r ]; } }, ]], @@ -759,19 +755,19 @@ my %GET_CHARACTER = ( [ inta => 'c.id :op:(:value:)', {'=' => 'IN', '!= ' => 'NOT IN'}, range => [1,1e6], join => ',' ], ], name => [ - [ str => 'cr.name :op: :value:', {qw|= = != <>|} ], - [ str => 'cr.name ILIKE :value:', {'~',1}, process => \'like' ], + [ str => 'c.name :op: :value:', {qw|= = != <>|} ], + [ str => 'c.name ILIKE :value:', {'~',1}, process => \'like' ], ], original => [ - [ undef, "cr.original :op: ''", {qw|= = != <>|} ], - [ str => 'cr.original :op: :value:', {qw|= = != <>|} ], - [ str => 'cr.original ILIKE :value:', {'~',1}, process => \'like' ] + [ undef, "c.original :op: ''", {qw|= = != <>|} ], + [ str => 'c.original :op: :value:', {qw|= = != <>|} ], + [ str => 'c.original ILIKE :value:', {'~',1}, process => \'like' ] ], search => [ - [ str => '(cr.name ILIKE :value: OR cr.original ILIKE :value: OR cr.alias ILIKE :value:)', {'~',1}, process => \'like' ], + [ str => '(c.name ILIKE :value: OR c.original ILIKE :value: OR c.alias ILIKE :value:)', {'~',1}, process => \'like' ], ], vn => [ - [ 'int' => 'cr.id IN(SELECT cv.cid FROM chars_vns cv WHERE cv.vid = :value:)', {'=',1}, range => [1,1e6] ], + [ 'int' => 'c.id IN(SELECT cv.id FROM chars_vns cv WHERE cv.vid = :value:)', {'=',1}, range => [1,1e6] ], ], }, ); diff --git a/lib/Multi/Anime.pm b/lib/Multi/Anime.pm index a17eead6..89f74a9e 100644 --- a/lib/Multi/Anime.pm +++ b/lib/Multi/Anime.pm @@ -98,10 +98,10 @@ sub unload { sub check_anime { - return if $C{aid}; + return if $C{aid} || $C{tw}; pg_cmd 'SELECT id FROM anime WHERE lastfetch IS NULL OR lastfetch < NOW() - $1::interval ORDER BY lastfetch DESC NULLS FIRST LIMIT 1', [ $O{cachetime} ], sub { my $res = shift; - return if pg_expect $res, 1 or $C{aid} or !$res->rows; + return if pg_expect $res, 1 or $C{aid} or $C{tw} or !$res->rows; $C{aid} = $res->value(0,0); nextcmd(); }; diff --git a/lib/Multi/Feed.pm b/lib/Multi/Feed.pm index d2161aec..2c4144db 100644 --- a/lib/Multi/Feed.pm +++ b/lib/Multi/Feed.pm @@ -44,16 +44,16 @@ sub generate { # changes pg_cmd q{ - SELECT '/'||c.type||COALESCE(vr.vid, rr.rid, pr.pid, cr.cid, sr.sid)||'.'||c.rev AS id, - COALESCE(vr.title, rr.title, pr.name, cr.name, sa.name) AS title, extract('epoch' from c.added) AS updated, + SELECT '/'||c.type||COALESCE(v.id, r.id, p.id, c.id, s.id)||'.'||c.rev AS id, + COALESCE(v.title, r.title, p.name, ca.name, sa.name) AS title, extract('epoch' from c.added) AS updated, u.username, u.id AS uid, c.comments AS summary FROM changes c - LEFT JOIN vn_rev vr ON c.type = 'v' AND c.id = vr.id - LEFT JOIN releases_rev rr ON c.type = 'r' AND c.id = rr.id - LEFT JOIN producers_rev pr ON c.type = 'p' AND c.id = pr.id - LEFT JOIN chars_rev cr ON c.type = 'c' AND c.id = cr.id - LEFT JOIN staff_rev sr ON c.type = 's' AND c.id = sr.id - LEFT JOIN staff_alias sa ON sa.rid = sr.id AND sa.id = sr.aid + LEFT JOIN vn v ON c.type = 'v' AND c.itemid = v.id + LEFT JOIN releases r ON c.type = 'r' AND c.itemid = r.id + LEFT JOIN producers p ON c.type = 'p' AND c.itemid = p.id + LEFT JOIN chars ca ON c.type = 'c' AND c.itemid = ca.id + LEFT JOIN staff s ON c.type = 's' AND c.itemid = s.id + LEFT JOIN staff_alias sa ON sa.id = s.id AND sa.aid = s.aid JOIN users u ON u.id = c.requester WHERE c.requester <> 1 ORDER BY c.id DESC diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm index b583e090..6c6159e3 100644 --- a/lib/Multi/IRC.pm +++ b/lib/Multi/IRC.pm @@ -18,12 +18,10 @@ use Encode 'decode_utf8', 'encode_utf8'; # long subquery used in several places my $GETBOARDS = q{array_to_string(array( - SELECT tb.type||COALESCE(':'||COALESCE(u.username, vr.title, pr.name), '') + SELECT tb.type||COALESCE(':'||COALESCE(u.username, v.title, p.name), '') FROM threads_boards tb LEFT JOIN vn v ON tb.type = 'v' AND v.id = tb.iid - LEFT JOIN vn_rev vr ON vr.id = v.latest LEFT JOIN producers p ON tb.type = 'p' AND p.id = tb.iid - LEFT JOIN producers_rev pr ON pr.id = p.latest LEFT JOIN users u ON tb.type = 'u' AND u.id = tb.iid WHERE tb.tid = t.id ORDER BY tb.type, tb.iid @@ -279,24 +277,24 @@ sub handleid { # plain vn/user/producer/thread/tag/trait/release pg_cmd 'SELECT $1::text AS type, $2::integer AS id, '.( - $t eq 'v' ? 'vr.title FROM vn_rev vr JOIN vn v ON v.latest = vr.id WHERE v.id = $2' : + $t eq 'v' ? 'v.title FROM vn v WHERE v.id = $2' : $t eq 'u' ? 'u.username AS title FROM users u WHERE u.id = $2' : - $t eq 'p' ? 'pr.name AS title FROM producers_rev pr JOIN producers p ON p.latest = pr.id WHERE p.id = $2' : - $t eq 'c' ? 'cr.name AS title FROM chars_rev cr JOIN chars c ON c.latest = cr.id WHERE c.id = $2' : - $t eq 's' ? 'sa.name AS title FROM staff_rev sr JOIN staff s ON s.latest = sr.id JOIN staff_alias sa ON sa.id = sr.aid AND sa.rid = s.latest WHERE s.id = $2' : + $t eq 'p' ? 'p.name AS title FROM producers p WHERE p.id = $2' : + $t eq 'c' ? 'c.name AS title FROM chars c WHERE c.id = $2' : + $t eq 's' ? 'sa.name AS title FROM staff s JOIN staff_alias sa ON sa.aid = s.aid AND sa.id = s.id WHERE s.id = $2' : $t eq 't' ? 'title, '.$GETBOARDS.' FROM threads t WHERE id = $2' : $t eq 'g' ? 'name AS title FROM tags WHERE id = $2' : $t eq 'i' ? 'name AS title FROM traits WHERE id = $2' : - 'rr.title FROM releases_rev rr JOIN releases r ON r.latest = rr.id WHERE r.id = $2'), + 'r.title FROM releases r WHERE r.id = $2'), [ $t, $id ], $c if !$rev && $t =~ /[vprtugics]/; # edit/insert of vn/release/producer or discussion board post pg_cmd 'SELECT $1::text AS type, $2::integer AS id, $3::integer AS rev, '.( - $t eq 'v' ? 'vr.title, u.username, c.comments FROM changes c JOIN vn_rev vr ON c.id = vr.id JOIN users u ON u.id = c.requester WHERE vr.vid = $2 AND c.rev = $3' : - $t eq 'r' ? 'rr.title, u.username, c.comments FROM changes c JOIN releases_rev rr ON c.id = rr.id JOIN users u ON u.id = c.requester WHERE rr.rid = $2 AND c.rev = $3' : - $t eq 'p' ? 'pr.name AS title, u.username, c.comments FROM changes c JOIN producers_rev pr ON c.id = pr.id JOIN users u ON u.id = c.requester WHERE pr.pid = $2 AND c.rev = $3' : - $t eq 'c' ? 'cr.name AS title, u.username, h.comments FROM changes h JOIN chars_rev cr ON h.id = cr.id JOIN users u ON u.id = h.requester WHERE cr.cid = $2 AND h.rev = $3' : - $t eq 's' ? 'sa.name AS title, u.username, c.comments FROM changes c JOIN staff_rev sr ON c.id = sr.id JOIN users u ON u.id = c.requester JOIN staff_alias sa ON sa.id = sr.aid AND sa.rid = sr.id WHERE sr.sid = $2 AND c.rev = $3' : + $t eq 'v' ? 'vh.title, u.username, c.comments FROM changes c JOIN vn_hist vh ON c.id = vh.chid JOIN users u ON u.id = c.requester WHERE c.type = \'v\' AND c.itemid = $2 AND c.rev = $3' : + $t eq 'r' ? 'rh.title, u.username, c.comments FROM changes c JOIN releases_hist rh ON c.id = rh.chid JOIN users u ON u.id = c.requester WHERE c.type = \'r\' AND c.itemid = $2 AND c.rev = $3' : + $t eq 'p' ? 'ph.name AS title, u.username, c.comments FROM changes c JOIN producers_hist ph ON c.id = ph.chid JOIN users u ON u.id = c.requester WHERE c.type = \'p\' AND c.itemid = $2 AND c.rev = $3' : + $t eq 'c' ? 'ch.name AS title, u.username, c.comments FROM changes c JOIN chars_hist ch ON c.id = ch.chid JOIN users u ON u.id = c.requester WHERE c.type = \'c\' AND c.itemid = $2 AND c.rev = $3' : + $t eq 's' ? 'sah.name AS title, u.username, c.comments FROM changes c JOIN staff_hist sh ON c.id = sh.chid JOIN users u ON u.id = c.requester JOIN staff_alias_hist sah ON sah.chid = c.id AND sah.aid = sh.aid WHERE c.type = \'s\' AND c.itemid = $2 AND c.rev = $3' : 't.title, u.username, '.$GETBOARDS.' FROM threads t JOIN threads_posts tp ON tp.tid = t.id JOIN users u ON u.id = tp.uid WHERE t.id = $2 AND tp.num = $3'), [ $t, $id, $rev], $c if $rev && $t =~ /[vprtcs]/; @@ -341,15 +339,15 @@ sub notify { my $q = { rev => q{ - SELECT c.type, c.rev, c.comments, c.id AS lastid, - COALESCE(vr.vid, rr.rid, pr.pid, cr.cid, sr.sid) AS id, COALESCE(vr.title, rr.title, pr.name, cr.name, sa.name) AS title, u.username + SELECT c.type, c.rev, c.comments, c.id AS lastid, c.itemid AS id, + COALESCE(vh.title, rh.title, ph.name, ch.name, sah.name) AS title, u.username FROM changes c - LEFT JOIN vn_rev vr ON c.type = 'v' AND c.id = vr.id - LEFT JOIN releases_rev rr ON c.type = 'r' AND c.id = rr.id - LEFT JOIN producers_rev pr ON c.type = 'p' AND c.id = pr.id - LEFT JOIN chars_rev cr ON c.type = 'c' AND c.id = cr.id - LEFT JOIN staff_rev sr ON c.type = 's' AND c.id = sr.id - LEFT JOIN staff_alias sa ON c.type = 's' AND sa.id = sr.aid AND sa.rid = c.id + LEFT JOIN vn_hist vh ON c.type = 'v' AND c.id = vh.chid + LEFT JOIN releases_hist rh ON c.type = 'r' AND c.id = rh.chid + LEFT JOIN producers_hist ph ON c.type = 'p' AND c.id = ph.chid + LEFT JOIN chars_hist ch ON c.type = 'c' AND c.id = ch.chid + LEFT JOIN staff_hist sh ON c.type = 's' AND c.id = sh.chid + LEFT JOIN staff_alias_hist sah ON c.type = 's' AND sah.aid = sh.aid AND sah.chid = c.id JOIN users u ON u.id = c.requester WHERE c.id > $1 AND c.requester <> 1 ORDER BY c.id}, @@ -409,13 +407,12 @@ vn => [ 0, 0, sub { return $irc->send_msg(PRIVMSG => $chan, "Couldn't do anything with that search query, you might want to add quotes or use longer words.") if !@q; - my $w = join ' AND ', map "v.c_search LIKE \$$_", 1..@q; + my $w = join ' AND ', map "c_search LIKE \$$_", 1..@q; pg_cmd qq{ - SELECT 'v'::text AS type, v.id, vr.title - FROM vn v - JOIN vn_rev vr ON vr.id = v.latest - WHERE NOT v.hidden AND $w - ORDER BY vr.title + SELECT 'v'::text AS type, id, title + FROM vn + WHERE NOT hidden AND $w + ORDER BY title LIMIT 6 }, [ map "%$_%", @q ], sub { my $res = shift; @@ -431,11 +428,10 @@ p => [ 0, 0, sub { my($nick, $chan, $q) = @_; return $irc->send_msg(PRIVMSG => $chan, 'You forgot the search query, dummy~~!') if !$q; pg_cmd q{ - SELECT 'p'::text AS type, p.id, pr.name AS title + SELECT 'p'::text AS type, id, name AS title FROM producers p - JOIN producers_rev pr ON pr.id = p.latest - WHERE p.hidden = FALSE AND (pr.name ILIKE $1 OR pr.original ILIKE $1 OR pr.alias ILIKE $1) - ORDER BY pr.name + WHERE hidden = FALSE AND (name ILIKE $1 OR original ILIKE $1 OR alias ILIKE $1) + ORDER BY name LIMIT 6 }, [ "%$q%" ], sub { my $res = shift; @@ -449,18 +445,17 @@ p => [ 0, 0, sub { scr => [ 0, 0, sub { my($nick, $chan, $q) = @_; - return $irc->send_msg(PRIVMSG => $chan. + return $irc->send_msg(PRIVMSG => $chan, q|Sorry, I failed to comprehend which screenshot you'd like me to lookup for you,| .q| please understand that Yorhel was not willing to supply me with mind reading capabilities.|) - if $q !~ /([0-9]+)\.jpg/; + if !$q || $q !~ /([0-9]+)\.jpg/; $q = $1; pg_cmd q{ - SELECT 'v'::text AS type, v.id, vr.title - FROM vn v - JOIN vn_rev vr ON vr.id = v.latest - JOIN vn_rev vr2 ON vr2.vid = v.id - JOIN vn_screenshots vs ON vs.vid = vr2.id - WHERE vs.scr = $1 LIMIT 1 + SELECT 'v'::text AS type, v.id, v.title + FROM changes c + JOIN vn_screenshots_hist vsh ON vsh.chid = c.id + JOIN vn v ON v.id = c.itemid + WHERE vsh.scr = $1 LIMIT 1 }, [ $q ], sub { my $res = shift; return if pg_expect $res, 1; diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm index 23e17fff..dfa708d6 100644 --- a/lib/Multi/Maintenance.pm +++ b/lib/Multi/Maintenance.pm @@ -53,17 +53,15 @@ sub log_res { my %dailies = ( - # takes about 500ms to 5s to complete, depending on how many releases have - # been released within the past 5 days + # takes about 50ms to 500ms to complete, depending on how many releases have been released within the past 5 days vncache_inc => q| SELECT update_vncache(id) FROM ( SELECT DISTINCT rv.vid FROM releases r - JOIN releases_rev rr ON rr.id = r.latest - JOIN releases_vn rv ON rv.rid = r.latest - WHERE rr.released > TO_CHAR(NOW() - '5 days'::interval, 'YYYYMMDD')::integer - AND rr.released <= TO_CHAR(NOW(), 'YYYYMMDD')::integer + JOIN releases_vn rv ON rv.id = r.id + WHERE r.released > TO_CHAR(NOW() - '5 days'::interval, 'YYYYMMDD')::integer + AND r.released <= TO_CHAR(NOW(), 'YYYYMMDD')::integer ) AS r(id)|, # takes about 9 seconds max, still OK @@ -75,7 +73,7 @@ my %dailies = ( # takes about 30 seconds vnpopularity => 'SELECT update_vnpopularity()', - # takes about 25 seconds, can be performed in ranges as well when necessary + # takes about 1 second, can be performed in ranges as well when necessary vnrating => q| UPDATE vn SET c_rating = (SELECT ( @@ -126,9 +124,7 @@ sub daily { my %monthlies = ( - # This takes about 4 to 5 minutes to complete, and should only be necessary - # in the event that the daily vncache_inc cron hasn't been running for 5 - # subsequent days. + # This only takes about 3 seconds to complete vncache_full => 'SELECT update_vncache(id) FROM vn', # These shouldn't really be necessary, the triggers in PgSQL should keep @@ -202,18 +198,9 @@ sub vnsearch_check { return if pg_expect $res, 1 or !$res->rows; my $id = $res->value(0,0); - pg_cmd q|SELECT vr.title, vr.original, vr.alias - FROM vn v - JOIN vn_rev vr ON vr.id = v.latest - WHERE v.id = $1 - UNION - SELECT rr.title, rr.original, NULL - FROM releases r - JOIN releases_rev rr ON rr.id = r.latest - JOIN releases_vn rv ON rv.rid = r.latest - WHERE rv.vid = $1 - AND NOT r.hidden - |, [ $id ], sub { vnsearch_update($id, @_) }; + pg_cmd q|SELECT title, original, alias FROM vn WHERE id = $1 + UNION SELECT r.title, r.original, NULL FROM releases r JOIN releases_vn rv ON rv.id = r.id WHERE rv.vid = $1 AND NOT r.hidden|, + [ $id ], sub { vnsearch_update($id, @_) }; }; } diff --git a/lib/Multi/RG.pm b/lib/Multi/RG.pm index 30101c53..eade569a 100644 --- a/lib/Multi/RG.pm +++ b/lib/Multi/RG.pm @@ -40,9 +40,9 @@ sub check_rg { AE::log debug => 'Checking for new graphs to create.'; pg_cmd q| - SELECT 'v', v.id FROM vn v JOIN vn_relations vr ON vr.vid1 = v.latest WHERE rgraph IS NULL AND hidden = FALSE + SELECT 'v', v.id FROM vn v JOIN vn_relations vr ON vr.id = v.id WHERE v.rgraph IS NULL AND v.hidden = FALSE UNION - SELECT 'p', p.id FROM producers p JOIN producers_relations pr ON pr.pid1 = p.latest WHERE rgraph IS NULL AND hidden = FALSE + SELECT 'p', p.id FROM producers p JOIN producers_relations pr ON pr.id = p.id WHERE p.rgraph IS NULL AND p.hidden = FALSE LIMIT 1|, undef, \&creategraph; } @@ -69,8 +69,8 @@ sub getrelid { my $id = shift; AE::log debug => "Fetching relations for $C{type}$id"; pg_cmd $C{type} eq 'v' - ? 'SELECT vid2, relation, official FROM vn v JOIN vn_relations vr ON vr.vid1 = v.latest WHERE v.id = $1' - : 'SELECT pid2, relation FROM producers p JOIN producers_relations pr ON pr.pid1 = p.latest WHERE p.id = $1', + ? 'SELECT vid, relation, official FROM vn_relations WHERE id = $1' + : 'SELECT pid, relation FROM producers_relations WHERE id = $1', [ $id ], sub { getrel($id, @_) }; } @@ -104,8 +104,8 @@ sub getrel { # id, res, time my $ids = join(', ', map '$'.$_, 1..@ids); AE::log debug => "Fetching node information for $C{type}:".join ', ', @ids; pg_cmd $C{type} eq 'v' - ? "SELECT v.id, vr.title, v.c_released AS date, array_to_string(v.c_languages, '/') AS lang FROM vn v JOIN vn_rev vr ON vr.id = v.latest WHERE v.id IN($ids) ORDER BY v.c_released" - : "SELECT p.id, pr.name, pr.lang, pr.type FROM producers p JOIN producers_rev pr ON pr.id = p.latest WHERE p.id IN($ids) ORDER BY pr.name", + ? "SELECT id, title, c_released AS date, array_to_string(c_languages, '/') AS lang FROM vn WHERE id IN($ids) ORDER BY c_released" + : "SELECT id, name, lang, type FROM producers WHERE id IN($ids) ORDER BY name", [ @ids ], \&builddot; } @@ -256,8 +256,6 @@ sub processgraph { # Before saving the SVG output, we'll modify it a little: # - Remove comments - # - Add svg: prefix to all tags - # - Remove xmlns declarations (this is set in the html) # - Remove <title> elements (unused) # - Remove id attributes (unused) # - Remove first <polygon> element (emulates the background color) @@ -274,15 +272,15 @@ sub processgraph { $attr{class} = 'border' if $attr{stroke} && $attr{stroke} eq '#111111'; $attr{class} = 'nodebg' if $attr{fill} && $attr{fill} eq '#222222'; - delete @attr{qw|stroke fill xmlns xmlns:xlink|}; + delete @attr{qw|stroke fill|}; delete $attr{id} if $attr{id} && $attr{id} !~ /^node_[vp]\d+$/; - $w->tag("svg:$el", %attr, $el eq 'path' || $el eq 'polygon' ? undef : ()); + $w->tag($el, %attr, $el eq 'path' || $el eq 'polygon' ? undef : ()); }, End => sub { my($expat, $el) = @_; return if $el eq 'title' || $expat->in_element('title'); return if $el eq 'polygon' && $expat->depth == 2; - $w->end("svg:$el") if $el ne 'path' && $el ne 'polygon'; + $w->end($el) if $el ne 'path' && $el ne 'polygon'; }, Char => sub { my($expat, $str) = @_; diff --git a/lib/VNDB/DB/Affiliates.pm b/lib/VNDB/DB/Affiliates.pm index 51f8c2dc..94dfd198 100644 --- a/lib/VNDB/DB/Affiliates.pm +++ b/lib/VNDB/DB/Affiliates.pm @@ -23,12 +23,12 @@ sub dbAffiliateGet { defined($o{hidden}) ? ('!s af.hidden' => $o{hidden} ? '' : 'NOT') : (), ); - my $join = $o{what} ? 'JOIN releases r ON r.id = af.rid JOIN releases_rev rr ON rr.id = r.latest' : ''; - my $select = $o{what} ? ', rr.title' : ''; + my $join = $o{what} ? 'JOIN releases r ON r.id = af.rid' : ''; + my $select = $o{what} ? ', r.title' : ''; my $order = sprintf { id => 'af.id %s', - rel => 'rr.title %s', + rel => 'r.title %s', prio => 'af.priority %s', url => 'af.url %s', lastfetch => 'af.lastfetch %s', diff --git a/lib/VNDB/DB/Chars.pm b/lib/VNDB/DB/Chars.pm index 3db5f869..db9ae93b 100644 --- a/lib/VNDB/DB/Chars.pm +++ b/lib/VNDB/DB/Chars.pm @@ -5,10 +5,10 @@ use strict; use warnings; use Exporter 'import'; -our @EXPORT = qw|dbCharGet dbCharRevisionInsert dbCharImageId|; +our @EXPORT = qw|dbCharGet dbCharGetRev dbCharRevisionInsert dbCharImageId|; -# options: id rev instance tagspoil trait_inc trait_exc char what results page gender bloodt +# options: id instance tagspoil trait_inc trait_exc char what results page gender bloodt # bust_min bust_max waist_min waist_max hip_min hip_max height_min height_max weight_min weight_max role # what: extended traits vns changes sub dbCharGet { @@ -24,33 +24,31 @@ sub dbCharGet { $o{search} =~ s/%//g if $o{search}; my %where = ( - !$o{id} && !$o{rev} ? ( 'c.hidden = FALSE' => 1 ) : (), - $o{id} ? ( - 'c.id IN(!l)' => [ ref $o{id} ? $o{id} : [$o{id}] ] ) : (), - $o{rev} ? ( 'h.rev = ?' => $o{rev} ) : (), + !$o{id} ? ( 'c.hidden = FALSE' => 1 ) : (), + $o{id} ? ( 'c.id IN(!l)' => [ ref $o{id} ? $o{id} : [$o{id}] ] ) : (), $o{notid} ? ( 'c.id <> ?' => $o{notid} ) : (), - $o{instance} ? ( 'cr.main = ?' => $o{instance} ) : (), - $o{vid} ? ( 'cr.id IN(SELECT cid FROM chars_vns WHERE vid = ?)' => $o{vid} ) : (), - defined $o{gender} ? ( 'cr.gender IN(!l)' => [ ref $o{gender} ? $o{gender} : [$o{gender}] ]) : (), - defined $o{bloodt} ? ( 'cr.bloodt IN(!l)' => [ ref $o{bloodt} ? $o{bloodt} : [$o{bloodt}] ]) : (), - defined $o{bust_min} ? ( 'cr.s_bust >= ?' => $o{bust_min} ) : (), - defined $o{bust_max} ? ( 'cr.s_bust <= ? AND cr.s_bust > 0' => $o{bust_max} ) : (), - defined $o{waist_min} ? ( 'cr.s_waist >= ?' => $o{waist_min} ) : (), - defined $o{waist_max} ? ( 'cr.s_waist <= ? AND cr.s_waist > 0' => $o{waist_max} ) : (), - defined $o{hip_min} ? ( 'cr.s_hip >= ?' => $o{hip_min} ) : (), - defined $o{hip_max} ? ( 'cr.s_hip <= ? AND cr.s_hip > 0' => $o{hip_max} ) : (), - defined $o{height_min} ? ( 'cr.height >= ?' => $o{height_min} ) : (), - defined $o{height_max} ? ( 'cr.height <= ? AND cr.height > 0' => $o{height_max} ) : (), - defined $o{weight_min} ? ( 'cr.weight >= ?' => $o{weight_min} ) : (), - defined $o{weight_max} ? ( 'cr.weight <= ? AND cr.weight > 0' => $o{weight_max} ) : (), + $o{instance} ? ( 'c.main = ?' => $o{instance} ) : (), + $o{vid} ? ( 'c.id IN(SELECT id FROM chars_vns WHERE vid = ?)' => $o{vid} ) : (), + defined $o{gender} ? ( 'c.gender IN(!l)' => [ ref $o{gender} ? $o{gender} : [$o{gender}] ]) : (), + defined $o{bloodt} ? ( 'c.bloodt IN(!l)' => [ ref $o{bloodt} ? $o{bloodt} : [$o{bloodt}] ]) : (), + defined $o{bust_min} ? ( 'c.s_bust >= ?' => $o{bust_min} ) : (), + defined $o{bust_max} ? ( 'c.s_bust <= ? AND c.s_bust > 0' => $o{bust_max} ) : (), + defined $o{waist_min} ? ( 'c.s_waist >= ?' => $o{waist_min} ) : (), + defined $o{waist_max} ? ( 'c.s_waist <= ? AND c.s_waist > 0' => $o{waist_max} ) : (), + defined $o{hip_min} ? ( 'c.s_hip >= ?' => $o{hip_min} ) : (), + defined $o{hip_max} ? ( 'c.s_hip <= ? AND c.s_hip > 0' => $o{hip_max} ) : (), + defined $o{height_min} ? ( 'c.height >= ?' => $o{height_min} ) : (), + defined $o{height_max} ? ( 'c.height <= ? AND c.height > 0' => $o{height_max} ) : (), + defined $o{weight_min} ? ( 'c.weight >= ?' => $o{weight_min} ) : (), + defined $o{weight_max} ? ( 'c.weight <= ? AND c.weight > 0' => $o{weight_max} ) : (), $o{search} ? ( - '(cr.name ILIKE ? OR cr.original ILIKE ? OR cr.alias ILIKE ?)', [ map '%'.$o{search}.'%', 1..3 ] ) : (), + '(c.name ILIKE ? OR c.original ILIKE ? OR c.alias ILIKE ?)', [ map '%'.$o{search}.'%', 1..3 ] ) : (), $o{char} ? ( - 'LOWER(SUBSTR(cr.name, 1, 1)) = ?' => $o{char} ) : (), + 'LOWER(SUBSTR(c.name, 1, 1)) = ?' => $o{char} ) : (), defined $o{char} && !$o{char} ? ( - '(ASCII(cr.name) < 97 OR ASCII(cr.name) > 122) AND (ASCII(cr.name) < 65 OR ASCII(cr.name) > 90)' => 1 ) : (), + '(ASCII(c.name) < 97 OR ASCII(c.name) > 122) AND (ASCII(c.name) < 65 OR ASCII(c.name) > 90)' => 1 ) : (), $o{role} ? ( - 'EXISTS(SELECT 1 FROM chars_vns cvi WHERE cvi.cid = cr.id AND cvi.role IN(!l))', + 'EXISTS(SELECT 1 FROM chars_vns cvi WHERE cvi.id = c.id AND cvi.role IN(!l))', [ ref $o{role} ? $o{role} : [$o{role}] ] ) : (), $o{trait_inc} ? ( 'c.id IN(SELECT cid FROM traits_chars WHERE tid IN(!l) AND spoil <= ? GROUP BY cid HAVING COUNT(tid) = ?)', @@ -59,75 +57,103 @@ sub dbCharGet { 'c.id NOT IN(SELECT cid FROM traits_chars WHERE tid IN(!l))' => [ ref $o{trait_exc} ? $o{trait_exc} : [$o{trait_exc}] ] ) : (), ); - my @select = (qw|c.id cr.name cr.original cr.gender|, 'cr.id AS cid'); - push @select, qw|c.hidden c.locked cr.alias cr.desc cr.image cr.b_month cr.b_day cr.s_bust cr.s_waist cr.s_hip cr.height cr.weight cr.bloodt cr.main cr.main_spoil| if $o{what} =~ /extended/; - push @select, qw|h.requester h.comments c.latest u.username h.rev h.ihid h.ilock|, "extract('epoch' from h.added) as added" if $o{what} =~ /changes/; - - my @join; - push @join, $o{rev} ? 'JOIN chars c ON c.id = cr.cid' : 'JOIN chars c ON cr.id = c.latest'; - push @join, 'JOIN changes h ON h.id = cr.id' if $o{what} =~ /changes/ || $o{rev}; - push @join, 'JOIN users u ON u.id = h.requester' if $o{what} =~ /changes/; + my @select = (qw|c.id c.name c.original c.gender|); + push @select, qw|c.hidden c.locked c.alias c.desc c.image c.b_month c.b_day c.s_bust c.s_waist c.s_hip c.height c.weight c.bloodt c.main c.main_spoil| if $o{what} =~ /extended/; my($r, $np) = $self->dbPage(\%o, q| SELECT !s - FROM chars_rev cr - !s + FROM chars c !W - ORDER BY cr.name|, - join(', ', @select), join(' ', @join), \%where + ORDER BY c.name|, + join(', ', @select), \%where ); - if(@$r && $o{what} =~ /vns|traits|seiyuu/) { + return _enrich($self, $r, $np, 0, $o{what}, $o{vid}); +} + + +sub dbCharGetRev { + my $self = shift; + my %o = (what => '', @_); + + $o{rev} ||= $self->dbRow('SELECT MAX(rev) AS rev FROM changes WHERE type = \'c\' AND itemid = ?', $o{id})->{rev}; + + my $select = 'c.itemid AS id, ch.name, ch.original, ch.gender'; + $select .= ', extract(\'epoch\' from c.added) as added, c.requester, c.comments, u.username, c.rev, c.ihid, c.ilock'; + $select .= ', c.id AS cid, NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev = c.rev+1) AS lastrev'; + $select .= ', ch.alias, ch.desc, ch.image, ch.b_month, ch.b_day, ch.s_bust, ch.s_waist, ch.s_hip, ch.height, ch.weight, ch.bloodt, ch.main, ch.main_spoil, co.hidden, co.locked' if $o{what} =~ /extended/; + + my $r = $self->dbAll(q| + SELECT !s + FROM changes c + JOIN chars co ON co.id = c.itemid + JOIN chars_hist ch ON ch.chid = c.id + JOIN users u ON u.id = c.requester + WHERE c.type = 'c' AND c.itemid = ? AND c.rev = ?|, + $select, $o{id}, $o{rev} + ); + + return _enrich($self, $r, 0, 1, $o{what}); +} + + +sub _enrich { + my($self, $r, $np, $rev, $what, $vid) = @_; + + if(@$r && $what =~ /vns|traits/) { + my($col, $hist, $colname) = $rev ? ('cid', '_hist', 'chid') : ('id', '', 'id'); my %r = map { $_->{traits} = []; $_->{vns} = []; - $_->{seiyuu} = []; - ($_->{cid}, $_) + ($_->{$col}, $_) } @$r; - if($o{what} =~ /traits/) { - push @{$r{ delete $_->{cid} }{traits}}, $_ for (@{$self->dbAll(q| - SELECT ct.cid, ct.tid, ct.spoil, t.name, t.sexual, t."group", tg.name AS groupname - FROM chars_traits ct + if($what =~ /traits/) { + push @{$r{ delete $_->{xid} }{traits}}, $_ for (@{$self->dbAll(qq| + SELECT ct.$colname AS xid, ct.tid, ct.spoil, t.name, t.sexual, t."group", tg.name AS groupname + FROM chars_traits$hist ct JOIN traits t ON t.id = ct.tid - LEFT JOIN traits tg ON tg.id = t."group" - WHERE cid IN(!l) + JOIN traits tg ON tg.id = t."group" + WHERE ct.$colname IN(!l) ORDER BY tg."order", t.name|, [ keys %r ] )}); } - if($o{what} =~ /vns(?:\((\d+)\))?/) { - push @{$r{ delete $_->{cid} }{vns}}, $_ for (@{$self->dbAll(q| - SELECT cv.cid, cv.vid, cv.rid, cv.spoil, cv.role, vr.title AS vntitle, rr.title AS rtitle - FROM chars_vns cv + if($what =~ /vns(?:\((\d+)\))?/) { + push @{$r{ delete $_->{xid} }{vns}}, $_ for (@{$self->dbAll(" + SELECT cv.$colname AS xid, cv.vid, cv.rid, cv.spoil, cv.role, v.title AS vntitle, r.title AS rtitle + FROM chars_vns$hist cv JOIN vn v ON cv.vid = v.id - JOIN vn_rev vr ON vr.id = v.latest LEFT JOIN releases r ON cv.rid = r.id - LEFT JOIN releases_rev rr ON rr.id = r.latest !W - ORDER BY v.c_released|, - { 'cv.cid IN(!l)' => [[keys %r]], $1 ? ('cv.vid = ?', $1) : () } + ORDER BY v.c_released", + { "cv.$colname IN(!l)" => [[keys %r]], $1 ? ('cv.vid = ?', $1) : () } )}); } + } - if($o{what} =~ /seiyuu/) { - push @{$r{ delete $_->{cid} }{seiyuu}}, $_ for (@{$self->dbAll(q| - SELECT cr.id AS cid, s.id AS sid, sa.name, sa.original, vs.note, v.id AS vid, vr.title AS vntitle - FROM vn_seiyuu vs - JOIN chars_rev cr ON cr.cid = vs.cid - JOIN staff_alias sa ON sa.id = vs.aid - JOIN staff s ON sa.rid = s.latest - JOIN vn_rev vr ON vr.id = vs.vid - JOIN vn v ON v.latest = vs.vid - !W - ORDER BY v.c_released, sa.name|, { - 's.hidden = FALSE' => 1, - 'cr.id IN(!l)' => [[ keys %r ]], - $o{vid} ? ('v.id = ?' => $o{vid}) : (), - } - )}); - } + # Depends on the VN revision rather than char revision + if(@$r && $what =~ /seiyuu/) { + my %r = map { + $_->{seiyuu} = []; + ($_->{id}, $_) + } @$r; + + push @{$r{ delete $_->{cid} }{seiyuu}}, $_ for (@{$self->dbAll(q| + SELECT vs.cid, s.id AS sid, sa.name, sa.original, vs.note, v.id AS vid, v.title AS vntitle + FROM vn_seiyuu vs + JOIN staff_alias sa ON sa.aid = vs.aid + JOIN staff s ON s.id = sa.id + JOIN vn v ON v.id = vs.id + !W + ORDER BY v.c_released, sa.name|, { + 's.hidden = FALSE' => 1, + 'vs.cid IN(!l)' => [[ keys %r ]], + $vid ? ('v.id = ?' => $vid) : (), + } + )}); } + return wantarray ? ($r, $np) : $r; } @@ -139,15 +165,15 @@ sub dbCharRevisionInsert { my %set = map exists($o->{$_}) ? (qq|"$_" = ?|, $o->{$_}) : (), qw|name original alias desc image b_month b_day s_bust s_waist s_hip height weight bloodt gender main main_spoil|; - $self->dbExec('UPDATE edit_char !H', \%set) if keys %set; + $self->dbExec('UPDATE edit_chars !H', \%set) if keys %set; if($o->{traits}) { - $self->dbExec('DELETE FROM edit_char_traits'); - $self->dbExec('INSERT INTO edit_char_traits (tid, spoil) VALUES (?,?)', $_->[0],$_->[1]) for (@{$o->{traits}}); + $self->dbExec('DELETE FROM edit_chars_traits'); + $self->dbExec('INSERT INTO edit_chars_traits (tid, spoil) VALUES (?,?)', $_->[0],$_->[1]) for (@{$o->{traits}}); } if($o->{vns}) { - $self->dbExec('DELETE FROM edit_char_vns'); - $self->dbExec('INSERT INTO edit_char_vns (vid, rid, spoil, role) VALUES(!l)', $_) for (@{$o->{vns}}); + $self->dbExec('DELETE FROM edit_chars_vns'); + $self->dbExec('INSERT INTO edit_chars_vns (vid, rid, spoil, role) VALUES(!l)', $_) for (@{$o->{vns}}); } } diff --git a/lib/VNDB/DB/Discussions.pm b/lib/VNDB/DB/Discussions.pm index 2af85c4e..c380feeb 100644 --- a/lib/VNDB/DB/Discussions.pm +++ b/lib/VNDB/DB/Discussions.pm @@ -23,11 +23,11 @@ sub dbThreadGet { !$o{id} ? ( 't.hidden = FALSE' => 0 ) : (), $o{type} && !$o{iid} ? ( - 't.id IN(SELECT tid FROM threads_boards WHERE type IN(!l))' => [ ref $o{type} ? $o{type} : [ $o{type} ] ] ) : (), + 'EXISTS(SELECT 1 FROM threads_boards WHERE tid = t.id AND type IN(!l))' => [ ref $o{type} ? $o{type} : [ $o{type} ] ] ) : (), $o{type} && $o{iid} ? ( 'tb.type = ?' => $o{type}, 'tb.iid = ?' => $o{iid} ) : (), $o{notusers} ? ( - 't.id NOT IN(SELECT tid FROM threads_boards WHERE type = \'u\')' => 1) : (), + 'NOT EXISTS(SELECT 1 FROM threads_boards WHERE type = \'u\' AND tid = t.id)' => 1) : (), ); if($o{search}) { @@ -39,16 +39,11 @@ sub dbThreadGet { my @select = ( qw|t.id t.title t.count t.locked t.hidden|, - $o{what} =~ /firstpost/ ? ('tpf.uid AS fuid', q|EXTRACT('epoch' from tpf.date) AS fdate|, 'uf.username AS fusername') : (), $o{what} =~ /lastpost/ ? ('tpl.uid AS luid', q|EXTRACT('epoch' from tpl.date) AS ldate|, 'ul.username AS lusername') : (), 'p.id AS poll', ); my @join = ( - $o{what} =~ /firstpost/ ? ( - 'JOIN threads_posts tpf ON tpf.tid = t.id AND tpf.num = 1', - 'JOIN users uf ON uf.id = tpf.uid' - ) : (), $o{what} =~ /lastpost/ ? ( 'JOIN threads_posts tpl ON tpl.tid = t.id AND tpl.num = t.count', 'JOIN users ul ON ul.id = tpl.uid' @@ -86,14 +81,23 @@ sub dbThreadGet { [ keys %r ] )}); } + + if($o{what} =~ /firstpost/) { + do { my $x = $r->[$r{$_->{tid}}]; $x->{fuid} = $_->{uid}; $x->{fdate} = $_->{date}; $x->{fusername} = $_->{username} } for (@{$self->dbAll(q| + SELECT tpf.tid, tpf.uid, EXTRACT('epoch' from tpf.date) AS date, uf.username + FROM threads_posts tpf + JOIN users uf ON tpf.uid = uf.id + WHERE tpf.num = 1 AND tpf.tid IN(!l)|, + [ keys %r ] + )}); + } + if($o{what} =~ /boardtitles/) { push(@{$r->[$r{$_->{tid}}]{boards}}, $_) for (@{$self->dbAll(q| - SELECT tb.tid, tb.type, tb.iid, COALESCE(u.username, vr.title, pr.name) AS title, COALESCE(u.username, vr.original, pr.original) AS original + SELECT tb.tid, tb.type, tb.iid, COALESCE(u.username, v.title, p.name) AS title, COALESCE(u.username, v.original, p.original) AS original FROM threads_boards tb LEFT JOIN vn v ON tb.type = 'v' AND v.id = tb.iid - LEFT JOIN vn_rev vr ON vr.id = v.latest LEFT JOIN producers p ON tb.type = 'p' AND p.id = tb.iid - LEFT JOIN producers_rev pr ON pr.id = p.latest LEFT JOIN users u ON tb.type = 'u' AND u.id = tb.iid WHERE tb.tid IN(!l)|, [ keys %r ] @@ -189,9 +193,9 @@ sub dbPostGet { $o{hide} && $o{what} =~ /thread/ ? ( 't.hidden = FALSE' => 1 ) : (), $o{search} ? ( - q{to_tsvector('english', strip_bb_tags(msg)) @@ to_tsquery(?)} => $o{search}) : (), + 'bb_tsvector(msg) @@ to_tsquery(?)' => $o{search}) : (), $o{type} ? ( - 'tp.tid = ANY(ARRAY(SELECT tid FROM threads_boards WHERE type IN(!l)))' => [ ref $o{type} ? $o{type} : [ $o{type} ] ] ) : (), + 'tp.tid IN(SELECT tid FROM threads_boards WHERE type IN(!l))' => [ ref $o{type} ? $o{type} : [ $o{type} ] ] ) : (), ); my @select = ( diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm index 8927e1ec..d6389376 100644 --- a/lib/VNDB/DB/Misc.pm +++ b/lib/VNDB/DB/Misc.pm @@ -21,14 +21,13 @@ sub dbStats { # Inserts a new revision into the database -# Arguments: type [vrp], revision id, %options->{ editsum uid ihid ilock + db[item]RevisionInsert } -# revision id = changes.id of the revision this edit is based on, undef to create a new DB item -# Returns: { iid, cid, rev } +# Arguments: type [vrp], itemid, rev, %options->{ editsum uid ihid ilock + db[item]RevisionInsert } +# rev = changes.rev of the revision this edit is based on, undef to create a new DB item +# Returns: { itemid, chid, rev } sub dbItemEdit { - my($self, $type, $oid, %o) = @_; + my($self, $type, $itemid, $rev, %o) = @_; - my $fun = {qw|v vn r release p producer c char s staff|}->{$type}; - $self->dbExec('SELECT edit_!s_init(?)', $fun, $oid); + $self->dbExec('SELECT edit_!s_init(?, ?)', $type, $itemid, $rev); $self->dbExec('UPDATE edit_revision !H', { 'requester = ?' => $o{uid}||$self->authInfo->{id}, 'ip = ?' => $self->reqIP, @@ -43,14 +42,11 @@ sub dbItemEdit { $self->dbCharRevisionInsert( \%o) if $type eq 'c'; $self->dbStaffRevisionInsert( \%o) if $type eq 's'; - return $self->dbRow('SELECT * FROM edit_!s_commit()', $fun); + return $self->dbRow('SELECT * FROM edit_!s_commit()', $type); } -# Options: type, iid, uid, auto, hidden, edit, page, results, what, releases -# what: item user -# Not very fast in each situation. Can be further optimized by: putting indexes -# on *_rev.?id, or by caching iid, ititle and ihidden in the changes table. +# Options: type, itemid, uid, auto, hidden, edit, page, results, releases sub dbRevisionGet { my($self, %o) = @_; $o{results} ||= 10; @@ -58,68 +54,57 @@ sub dbRevisionGet { $o{auto} ||= 0; # 0:show, -1:only, 1:hide $o{hidden} ||= 0; $o{edit} ||= 0; # 0:both, -1:new, 1:edits - $o{what} ||= ''; - $o{releases} = 0 if !$o{type} || $o{type} ne 'v' || !$o{iid}; - - my %tables = qw|v vn r releases p producers c chars s staff|; - # what types should we join? - my @types = ( - !$o{type} ? qw(v r p c s) : - ref($o{type}) ? @{$o{type}} : - $o{type} ne 'v' ? $o{type} : - $o{releases} ? ('v', 'r') : 'v' - ); + $o{releases} = 0 if !$o{type} || $o{type} ne 'v' || !$o{itemid}; my %where = ( $o{releases} ? ( - q{((h.type = 'v' AND vr.vid = ?) OR (h.type = 'r' AND h.id = ANY(ARRAY(SELECT rv.rid FROM releases_vn rv WHERE rv.vid = ?))))} => [$o{iid}, $o{iid}], + # This selects all changes of releases that are currently linked to the VN, not release revisions that are linked to the VN. + # The latter seems more useful, but is also a lot more expensive. + q{((c.type = 'v' AND c.itemid = ?) OR (c.type = 'r' AND c.itemid = ANY(ARRAY(SELECT rv.id FROM releases_vn rv WHERE rv.vid = ?))))} => [$o{itemid}, $o{itemid}], ) : ( $o{type} ? ( - 'h.type IN(!l)' => [ ref($o{type})?$o{type}:[$o{type}] ] ) : (), - $o{iid} ? ( - '!sr.!sid = ?' => [ $o{type}, $o{type}, $o{iid} ] ) : (), + 'c.type IN(!l)' => [ ref($o{type})?$o{type}:[$o{type}] ] ) : (), + $o{itemid} ? ( + 'c.itemid = ?' => [ $o{itemid} ] ) : (), ), $o{uid} ? ( - 'h.requester = ?' => $o{uid} ) : (), + 'c.requester = ?' => $o{uid} ) : (), $o{auto} ? ( - 'h.requester !s 1' => $o{auto} < 0 ? '=' : '<>' ) : (), + 'c.requester !s 1' => $o{auto} < 0 ? '=' : '<>' ) : (), $o{hidden} ? ( - '('.join(' OR ', map sprintf('%s.hidden IS NOT NULL AND %s %1$s.hidden', $_, $o{hidden} == 1 ? 'NOT' : ''), @types).')' => 1 ) : (), + '!s EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.ihid AND'. + ' c2.rev = (SELECT MAX(c3.rev) FROM changes c3 WHERE c3.type = c.type AND c3.itemid = c.itemid))' => $o{hidden} == 1 ? 'NOT' : '') : (), $o{edit} ? ( - 'h.rev !s 1' => $o{edit} < 0 ? '=' : '>' ) : (), - ); - - my @join = ( - $o{iid} || $o{what} =~ /item/ || $o{hidden} || $o{releases} ? ( - map sprintf(q|LEFT JOIN %s_rev %sr ON h.type = '%2$s' AND h.id = %2$sr.id|, $tables{$_}, $_), @types - ) : (), - $o{hidden} ? ( - map sprintf(q|LEFT JOIN %s %s ON h.type = '%2$s' AND %2$sr.%2$sid = %2$s.id|, $tables{$_}, $_), @types - ) : (), - $o{what} =~ /user/ ? 'JOIN users u ON h.requester = u.id' : (), - ); - push @join, 'LEFT JOIN staff_alias sa ON sa.rid = sr.id AND sa.id = sr.aid' if grep /s/, @types; - - my %tcolumns = qw(v vr.title r rr.title p pr.name c cr.name s sa.name); - my @select = ( - qw|h.id h.type h.requester h.comments h.rev|, - q|extract('epoch' from h.added) as added|, - $o{what} =~ /user/ ? 'u.username' : (), - $o{what} =~ /item/ ? ( - 'COALESCE('.join(', ', map "${_}r.${_}id", @types).') AS iid', - 'COALESCE('.join(', ', map $tcolumns{$_}, @types).') AS ititle', - 'COALESCE('.join(', ', map /s/ ? 'sa.original' : "${_}r.original", @types).') AS ioriginal', - ) : (), + 'c.rev !s 1' => $o{edit} < 0 ? '=' : '>' ) : (), ); my($r, $np) = $self->dbPage(\%o, q| - SELECT !s - FROM changes h - !s + SELECT c.id, c.type, c.itemid, c.requester, c.comments, c.rev, extract('epoch' from c.added) as added, u.username + FROM changes c + JOIN users u ON c.requester = u.id !W - ORDER BY h.id DESC|, - join(', ', @select), join(' ', @join), \%where + ORDER BY c.id DESC|, \%where ); + + # I couldn't find a way to fetch the titles the main query above without slowing it down considerably, so let's just do it this way. + if(@$r) { + my %r = map +($_->{id}, $_), @$r; + my $w = join ' OR ', ('(type = ? AND id = ?)') x @$r; + my @w = map +($_->{type}, $_->{id}), @$r; + + $r{ $_->{id} }{ititle} = $_->{title}, $r{ $_->{id} }{ioriginal} = $_->{original} for(@{$self->dbAll(" + SELECT id, title, original FROM ( + SELECT 'v'::dbentry_type, chid, title, original FROM vn_hist + UNION ALL SELECT 'r'::dbentry_type, chid, title, original FROM releases_hist + UNION ALL SELECT 'p'::dbentry_type, chid, name, original FROM producers_hist + UNION ALL SELECT 'c'::dbentry_type, chid, name, original FROM chars_hist + UNION ALL SELECT 's'::dbentry_type, sh.chid, name, original FROM staff_hist sh JOIN staff_alias_hist sah ON sah.chid = sh.chid AND sah.aid = sh.aid + ) x(type, id, title, original) + WHERE $w + ", @w + )}); + } + return wantarray ? ($r, $np) : $r; } diff --git a/lib/VNDB/DB/Producers.pm b/lib/VNDB/DB/Producers.pm index 3ff70ae1..3b6f2a32 100644 --- a/lib/VNDB/DB/Producers.pm +++ b/lib/VNDB/DB/Producers.pm @@ -4,12 +4,13 @@ package VNDB::DB::Producers; use strict; use warnings; use Exporter 'import'; +use Encode 'decode_utf8'; -our @EXPORT = qw|dbProducerGet dbProducerRevisionInsert|; +our @EXPORT = qw|dbProducerGet dbProducerGetRev dbProducerRevisionInsert|; -# options: results, page, id, search, char, rev -# what: extended changes relations relgraph +# options: results, page, id, search, char +# what: extended relations relgraph sub dbProducerGet { my $self = shift; my %o = ( @@ -22,52 +23,80 @@ sub dbProducerGet { $o{search} =~ s/%//g if $o{search}; my %where = ( - !$o{id} && !$o{rev} ? ( + !$o{id} ? ( 'p.hidden = FALSE' => 1 ) : (), $o{id} ? ( 'p.id IN(!l)' => [ ref $o{id} ? $o{id} : [$o{id}] ] ) : (), $o{search} ? ( - '(pr.name ILIKE ? OR pr.original ILIKE ? OR pr.alias ILIKE ?)', [ map '%'.$o{search}.'%', 1..3 ] ) : (), + '(p.name ILIKE ? OR p.original ILIKE ? OR p.alias ILIKE ?)', [ map '%'.$o{search}.'%', 1..3 ] ) : (), $o{char} ? ( - 'LOWER(SUBSTR(pr.name, 1, 1)) = ?' => $o{char} ) : (), + 'LOWER(SUBSTR(p.name, 1, 1)) = ?' => $o{char} ) : (), defined $o{char} && !$o{char} ? ( - '(ASCII(pr.name) < 97 OR ASCII(pr.name) > 122) AND (ASCII(pr.name) < 65 OR ASCII(pr.name) > 90)' => 1 ) : (), - $o{rev} ? ( - 'c.rev = ?' => $o{rev} ) : (), + '(ASCII(p.name) < 97 OR ASCII(p.name) > 122) AND (ASCII(p.name) < 65 OR ASCII(p.name) > 90)' => 1 ) : (), ); - my @join; - push @join, $o{rev} ? 'JOIN producers p ON p.id = pr.pid' : 'JOIN producers p ON pr.id = p.latest'; - push @join, 'JOIN changes c ON c.id = pr.id' if $o{what} =~ /changes/ || $o{rev}; - push @join, 'JOIN users u ON u.id = c.requester' if $o{what} =~ /changes/; - push @join, 'JOIN relgraphs pg ON pg.id = p.rgraph' if $o{what} =~ /relgraph/; + my $join = $o{what} =~ /relgraph/ ? 'JOIN relgraphs pg ON pg.id = p.rgraph' : ''; - my $select = 'p.id, pr.type, pr.name, pr.original, pr.lang, pr.id AS cid, p.rgraph'; - $select .= ', pr.desc, pr.alias, pr.website, pr.l_wp, p.hidden, p.locked' if $o{what} =~ /extended/; - $select .= q|, extract('epoch' from c.added) as added, c.requester, c.comments, p.latest, pr.id AS cid, u.username, c.rev, c.ihid, c.ilock| if $o{what} =~ /changes/; + my $select = 'p.id, p.type, p.name, p.original, p.lang, p.rgraph'; + $select .= ', p.desc, p.alias, p.website, p.l_wp, p.hidden, p.locked' if $o{what} =~ /extended/; $select .= ', pg.svg' if $o{what} =~ /relgraph/; my($r, $np) = $self->dbPage(\%o, q| SELECT !s - FROM producers_rev pr + FROM producers p !s !W - ORDER BY pr.name ASC|, - $select, join(' ', @join), \%where, + ORDER BY p.name ASC|, + $select, $join, \%where, ); - if(@$r && $o{what} =~ /relations/) { + $_->{svg} && ($_->{svg} = decode_utf8($_->{svg})) for (@$r); + return _enrich($self, $r, $np, 0, $o{what}); +} + + +# options: id, rev, what +# what: extended relations +sub dbProducerGetRev { + my $self = shift; + my %o = (what => '', @_); + + $o{rev} ||= $self->dbRow('SELECT MAX(rev) AS rev FROM changes WHERE type = \'p\' AND itemid = ?', $o{id})->{rev}; + + my $select = 'c.itemid AS id, p.type, p.name, p.original, p.lang, po.rgraph'; + $select .= ', extract(\'epoch\' from c.added) as added, c.requester, c.comments, u.username, c.rev, c.ihid, c.ilock'; + $select .= ', c.id AS cid, NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev = c.rev+1) AS lastrev'; + $select .= ', p.desc, p.alias, p.website, p.l_wp, po.hidden, po.locked' if $o{what} =~ /extended/; + + my $r = $self->dbAll(q| + SELECT !s + FROM changes c + JOIN producers po ON po.id = c.itemid + JOIN producers_hist p ON p.chid = c.id + JOIN users u ON u.id = c.requester + WHERE c.type = 'p' AND c.itemid = ? AND c.rev = ?|, + $select, $o{id}, $o{rev} + ); + + return _enrich($self, $r, 0, 1, $o{what}); +} + + +sub _enrich { + my($self, $r, $np, $rev, $what) = @_; + + if(@$r && $what =~ /relations/) { + my($col, $hist, $colname) = $rev ? ('cid', '_hist', 'chid') : ('id', '', 'id'); my %r = map { $r->[$_]{relations} = []; - ($r->[$_]{cid}, $_) + ($r->[$_]{$col}, $_) } 0..$#$r; - push @{$r->[$r{$_->{pid1}}]{relations}}, $_ for(@{$self->dbAll(q| - SELECT rel.pid1, rel.pid2 AS id, rel.relation, pr.name, pr.original - FROM producers_relations rel - JOIN producers p ON rel.pid2 = p.id - JOIN producers_rev pr ON p.latest = pr.id - WHERE rel.pid1 IN(!l)|, + push @{$r->[$r{$_->{xid}}]{relations}}, $_ for(@{$self->dbAll(qq| + SELECT rel.$colname AS xid, rel.pid AS id, rel.relation, p.name, p.original + FROM producers_relations$hist rel + JOIN producers p ON rel.pid = p.id + WHERE rel.$colname IN(!l)|, [ keys %r ] )}); } @@ -83,13 +112,13 @@ sub dbProducerRevisionInsert { my %set = map exists($o->{$_}) ? (qq|"$_" = ?|, $o->{$_}) : (), qw|name original website l_wp type lang desc alias|; - $self->dbExec('UPDATE edit_producer !H', \%set) if keys %set; + $self->dbExec('UPDATE edit_producers !H', \%set) if keys %set; if($o->{relations}) { - $self->dbExec('DELETE FROM edit_producer_relations'); + $self->dbExec('DELETE FROM edit_producers_relations'); my $q = join ',', map '(?,?)', @{$o->{relations}}; my @q = map +($_->[1], $_->[0]), @{$o->{relations}}; - $self->dbExec("INSERT INTO edit_producer_relations (pid, relation) VALUES $q", @q) if @q; + $self->dbExec("INSERT INTO edit_producers_relations (pid, relation) VALUES $q", @q) if @q; } } diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm index 3c19cfb2..eafb84c0 100644 --- a/lib/VNDB/DB/Releases.pm +++ b/lib/VNDB/DB/Releases.pm @@ -7,12 +7,12 @@ use POSIX 'strftime'; use Exporter 'import'; use VNDB::Func 'gtintype'; -our @EXPORT = qw|dbReleaseGet dbReleaseRevisionInsert|; +our @EXPORT = qw|dbReleaseGet dbReleaseGetRev dbReleaseRevisionInsert|; -# Options: id vid pid rev released page results what med sort reverse date_before date_after +# Options: id vid pid released page results what med sort reverse date_before date_after # plat lang olang type minage search resolution freeware doujin voiced ani_story ani_ero -# What: extended changes vn producers platforms media affiliates +# What: extended vn producers platforms media affiliates # Sort: title released minage sub dbReleaseGet { my($self, %o) = @_; @@ -23,34 +23,33 @@ sub dbReleaseGet { $o{med} = [ $o{med} ] if $o{med} && !ref $o{med}; my @where = ( - !$o{id} && !$o{rev} ? ( 'r.hidden = FALSE' => 0 ) : (), + !$o{id} ? ( 'r.hidden = FALSE' => 0 ) : (), $o{id} ? ( 'r.id = ?' => $o{id} ) : (), - $o{rev} ? ( 'c.rev = ?' => $o{rev} ) : (), $o{vid} ? ( 'rv.vid IN(!l)' => [ ref $o{vid} ? $o{vid} : [$o{vid}] ] ) : (), $o{pid} ? ( 'rp.pid = ?' => $o{pid} ) : (), - defined $o{patch} ? ( 'rr.patch = ?' => $o{patch} == 1 ? 1 : 0) : (), - defined $o{freeware} ? ( 'rr.freeware = ?' => $o{freeware} == 1 ? 1 : 0) : (), - defined $o{doujin} ? ( 'rr.doujin = ?' => $o{doujin} == 1 ? 1 : 0) : (), - defined $o{type} ? ( 'rr.type = ?' => $o{type} ) : (), - defined $o{date_before} ? ( 'rr.released <= ?' => $o{date_before} ) : (), - defined $o{date_after} ? ( 'rr.released >= ?' => $o{date_after} ) : (), - defined $o{minage} ? ( 'rr.minage IN(!l)' => [ ref $o{minage} ? $o{minage} : [$o{minage}] ] ) : (), - defined $o{resolution} ? ( 'rr.resolution IN(!l)' => [ ref $o{resolution} ? $o{resolution} : [$o{resolution}] ] ) : (), - defined $o{voiced} ? ( 'rr.voiced IN(!l)' => [ ref $o{voiced} ? $o{voiced} : [$o{voiced}] ] ) : (), - defined $o{ani_story} ? ( 'rr.ani_story IN(!l)' => [ ref $o{ani_story} ? $o{ani_story} : [$o{ani_story}] ] ) : (), - defined $o{ani_ero} ? ( 'rr.ani_ero IN(!l)' => [ ref $o{ani_ero} ? $o{ani_ero} : [$o{ani_ero}] ] ) : (), - defined $o{released} ? ( 'rr.released !s ?' => [ $o{released} ? '<=' : '>', strftime('%Y%m%d', gmtime) ] ) : (), + defined $o{patch} ? ( 'r.patch = ?' => $o{patch} == 1 ? 1 : 0) : (), + defined $o{freeware} ? ( 'r.freeware = ?' => $o{freeware} == 1 ? 1 : 0) : (), + defined $o{doujin} ? ( 'r.doujin = ?' => $o{doujin} == 1 ? 1 : 0) : (), + defined $o{type} ? ( 'r.type = ?' => $o{type} ) : (), + defined $o{date_before} ? ( 'r.released <= ?' => $o{date_before} ) : (), + defined $o{date_after} ? ( 'r.released >= ?' => $o{date_after} ) : (), + defined $o{minage} ? ( 'r.minage IN(!l)' => [ ref $o{minage} ? $o{minage} : [$o{minage}] ] ) : (), + defined $o{resolution} ? ( 'r.resolution IN(!l)' => [ ref $o{resolution} ? $o{resolution} : [$o{resolution}] ] ) : (), + defined $o{voiced} ? ( 'r.voiced IN(!l)' => [ ref $o{voiced} ? $o{voiced} : [$o{voiced}] ] ) : (), + defined $o{ani_story} ? ( 'r.ani_story IN(!l)' => [ ref $o{ani_story} ? $o{ani_story} : [$o{ani_story}] ] ) : (), + defined $o{ani_ero} ? ( 'r.ani_ero IN(!l)' => [ ref $o{ani_ero} ? $o{ani_ero} : [$o{ani_ero}] ] ) : (), + defined $o{released} ? ( 'r.released !s ?' => [ $o{released} ? '<=' : '>', strftime('%Y%m%d', gmtime) ] ) : (), $o{lang} ? ( - 'rr.id IN(SELECT irl.rid FROM releases_lang irl JOIN releases ir ON ir.latest = irl.rid WHERE irl.lang IN(!l))' => [ ref $o{lang} ? $o{lang} : [ $o{lang} ] ] ) : (), + 'r.id IN(SELECT irl.id FROM releases_lang irl WHERE irl.lang IN(!l))' => [ ref $o{lang} ? $o{lang} : [ $o{lang} ] ] ) : (), $o{olang} ? ( - 'rr.id IN(SELECT irv.rid FROM releases_vn irv JOIN releases ir ON ir.latest = irv.rid JOIN vn v ON irv.vid = v.id WHERE v.c_olang && ARRAY[!l]::language[])' => [ ref $o{olang} ? $o{olang} : [ $o{olang} ] ] ) : (), + 'r.id IN(SELECT irv.id FROM releases_vn irv JOIN vn v ON irv.vid = v.id WHERE v.c_olang && ARRAY[!l]::language[])' => [ ref $o{olang} ? $o{olang} : [ $o{olang} ] ] ) : (), $o{plat} ? ('('.join(' OR ', - grep(/^unk$/, @{$o{plat}}) ? 'NOT EXISTS(SELECT 1 FROM releases_platforms irp WHERE irp.rid = r.latest)' : (), - grep(!/^unk$/, @{$o{plat}}) ? 'rr.id IN(SELECT irp.rid FROM releases_platforms irp JOIN releases ir ON ir.latest = irp.rid WHERE irp.platform IN(!l))' : (), + grep(/^unk$/, @{$o{plat}}) ? 'NOT EXISTS(SELECT 1 FROM releases_platforms irp WHERE irp.id = r.id)' : (), + grep(!/^unk$/, @{$o{plat}}) ? 'r.id IN(SELECT irp.id FROM releases_platforms irp WHERE irp.platform IN(!l))' : (), ).')', [ [ grep !/^unk$/, @{$o{plat}} ] ]) : (), $o{med} ? ('('.join(' OR ', - grep(/^unk$/, @{$o{med}}) ? 'NOT EXISTS(SELECT 1 FROM releases_media irm WHERE irm.rid = r.latest)' : (), - grep(!/^unk$/, @{$o{med}}) ? 'rr.id IN(SELECT irm.rid FROM releases_media irm JOIN releases ir ON ir.latest = irm.rid WHERE irm.medium IN(!l))' : () + grep(/^unk$/, @{$o{med}}) ? 'NOT EXISTS(SELECT 1 FROM releases_media irm WHERE irm.id = r.id)' : (), + grep(!/^unk$/, @{$o{med}}) ? 'r.id IN(SELECT irm.id FROM releases_media irm WHERE irm.medium IN(!l))' : () ).')', [ [ grep(!/^unk$/, @{$o{med}}) ] ]) : (), ); @@ -58,110 +57,135 @@ sub dbReleaseGet { for (split /[ -,._]/, $o{search}) { s/%//g; if(/^\d+$/ && gtintype($_)) { - push @where, 'rr.gtin = ?', $_; + push @where, 'r.gtin = ?', $_; } elsif(length($_) > 0) { $_ = "%$_%"; - push @where, '(rr.title ILIKE ? OR rr.original ILIKE ? OR rr.catalog = ?)', + push @where, '(r.title ILIKE ? OR r.original ILIKE ? OR r.catalog = ?)', [ $_, $_, $_ ]; } } } my @join = ( - $o{rev} ? 'JOIN releases r ON r.id = rr.rid' : 'JOIN releases r ON rr.id = r.latest', - $o{vid} ? 'JOIN releases_vn rv ON rv.rid = rr.id' : (), - $o{pid} ? 'JOIN releases_producers rp ON rp.rid = rr.id' : (), - $o{what} =~ /changes/ || $o{rev} ? ( - 'JOIN changes c ON c.id = rr.id', - 'JOIN users u ON u.id = c.requester' - ) : (), + $o{vid} ? 'JOIN releases_vn rv ON rv.id = r.id' : (), + $o{pid} ? 'JOIN releases_producers rp ON rp.id = r.id' : (), ); my @select = ( - 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.requester c.comments r.latest u.username c.rev c.ihid c.ilock|, q|extract('epoch' from c.added) as added|) : (), + qw|r.id r.title r.original r.website r.released r.minage r.type r.patch|, + $o{what} =~ /extended/ ? qw|r.notes r.catalog r.gtin r.resolution r.voiced r.freeware r.doujin r.ani_story r.ani_ero r.hidden r.locked| : (), $o{pid} ? ('rp.developer', 'rp.publisher') : (), ); my $order = sprintf { - title => 'rr.title %s, rr.released %1$s', - type => 'rr.patch %s, rr.type %1$s, rr.released %1$s, rr.title %1$s', - publication => 'rr.doujin %s, rr.freeware %1$s, rr.patch %1$s, rr.released %1$s, rr.title %1$s', - resolution => 'rr.resolution %s, rr.patch %2$s, rr.released %1$s, rr.title %1$s', - voiced => 'rr.voiced %s, rr.patch %2$s, rr.released %1$s, rr.title %1$s', - ani_ero => 'rr.ani_story %s, rr.ani_ero %1$s, rr.patch %2$s, rr.released %1$s, rr.title %1$s', - released => 'rr.released %s, r.id %1$s', - minage => 'rr.minage %s, rr.released %1$s, rr.title %1$s', - notes => 'rr.notes %s, rr.released %1$s, rr.title %1$s', + title => 'r.title %s, r.released %1$s', + type => 'r.patch %s, r.type %1$s, r.released %1$s, r.title %1$s', + publication => 'r.doujin %s, r.freeware %1$s, r.patch %1$s, r.released %1$s, r.title %1$s', + resolution => 'r.resolution %s, r.patch %2$s, r.released %1$s, r.title %1$s', + voiced => 'r.voiced %s, r.patch %2$s, r.released %1$s, r.title %1$s', + ani_ero => 'r.ani_story %s, r.ani_ero %1$s, r.patch %2$s, r.released %1$s, r.title %1$s', + released => 'r.released %s, r.id %1$s', + minage => 'r.minage %s, r.released %1$s, r.title %1$s', + notes => 'r.notes %s, r.released %1$s, r.title %1$s', }->{ $o{sort}||'released' }, $o{reverse} ? 'DESC' : 'ASC', !$o{reverse} ? 'DESC' : 'ASC'; my($r, $np) = $self->dbPage(\%o, q| SELECT !s - FROM releases_rev rr + FROM releases r !s !W ORDER BY !s|, join(', ', @select), join(' ', @join), \@where, $order ); + return _enrich($self, $r, $np, 0, $o{what}); +} + + +# options: id, rev, what +# what: extended vn producers platforms media affiliates +sub dbReleaseGetRev { + my $self = shift; + my %o = (what => '', @_); + + $o{rev} ||= $self->dbRow('SELECT MAX(rev) AS rev FROM changes WHERE type = \'r\' AND itemid = ?', $o{id})->{rev}; + + my $select = 'c.itemid AS id, r.title, r.original, r.website, r.released, r.minage, r.type, r.patch'; + $select .= ', r.notes, r.catalog, r.gtin, r.resolution, r.voiced, r.freeware, r.doujin, r.ani_story, r.ani_ero, ro.hidden, ro.locked' if $o{what} =~ /extended/; + $select .= ', extract(\'epoch\' from c.added) as added, c.requester, c.comments, u.username, c.rev, c.ihid, c.ilock'; + $select .= ', c.id AS cid, NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev = c.rev+1) AS lastrev'; + + my $r = $self->dbAll(q| + SELECT !s + FROM changes c + JOIN releases ro ON ro.id = c.itemid + JOIN releases_hist r ON r.chid = c.id + JOIN users u ON u.id = c.requester + WHERE c.type = 'r' AND c.itemid = ? AND c.rev = ?|, + $select, $o{id}, $o{rev} + ); + + return _enrich($self, $r, 0, 1, $o{what}); +} + + +sub _enrich { + my($self, $r, $np, $rev, $what) = @_; + if(@$r) { + my($col, $hist, $colname) = $rev ? ('cid', '_hist', 'chid') : ('id', '', 'id'); my %r = map { $r->[$_]{producers} = []; $r->[$_]{platforms} = []; $r->[$_]{media} = []; $r->[$_]{vn} = []; $r->[$_]{languages} = []; - ($r->[$_]{cid}, $_) + ($r->[$_]{$col}, $_) } 0..$#$r; - push(@{$r->[$r{$_->{rid}}]{languages}}, $_->{lang}) for (@{$self->dbAll(q| - SELECT rid, lang - FROM releases_lang - WHERE rid IN(!l)|, + push(@{$r->[$r{$_->{xid}}]{languages}}, $_->{lang}) for (@{$self->dbAll(" + SELECT $colname AS xid, lang + FROM releases_lang$hist + WHERE $colname 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 - FROM releases_vn rv + if($what =~ /vn/) { + push(@{$r->[$r{$_->{xid}}]{vn}}, $_) for (@{$self->dbAll(" + SELECT rv.$colname AS xid, v.id AS vid, v.title, v.original + FROM releases_vn$hist rv JOIN vn v ON v.id = rv.vid - JOIN vn_rev vr ON vr.id = v.latest - WHERE rv.rid IN(!l) - ORDER BY vr.title|, + WHERE rv.$colname IN(!l) + ORDER BY v.title", [ keys %r ] )}); } - if($o{what} =~ /producers/) { - push(@{$r->[$r{$_->{rid}}]{producers}}, $_) for (@{$self->dbAll(q| - SELECT rp.rid, rp.developer, rp.publisher, p.id, pr.name, pr.original, pr.type - FROM releases_producers rp + if($what =~ /producers/) { + push(@{$r->[$r{$_->{xid}}]{producers}}, $_) for (@{$self->dbAll(" + SELECT rp.$colname AS xid, rp.developer, rp.publisher, p.id, p.name, p.original, p.type + FROM releases_producers$hist rp JOIN producers p ON rp.pid = p.id - JOIN producers_rev pr ON pr.id = p.latest - WHERE rp.rid IN(!l) - ORDER BY pr.name|, + WHERE rp.$colname IN(!l) + ORDER BY p.name", [ keys %r ] )}); } - if($o{what} =~ /platforms/) { - push(@{$r->[$r{$_->{rid}}]{platforms}}, $_->{platform}) for (@{$self->dbAll(q| - SELECT rid, platform - FROM releases_platforms - WHERE rid IN(!l)|, + if($what =~ /platforms/) { + push(@{$r->[$r{$_->{xid}}]{platforms}}, $_->{platform}) for (@{$self->dbAll(" + SELECT $colname AS xid, platform + FROM releases_platforms$hist + WHERE $colname IN(!l)", [ keys %r ] )}); } - if($o{what} =~ /media/) { - push(@{$r->[$r{$_->{rid}}]{media}}, $_) for (@{$self->dbAll(q| - SELECT rid, medium, qty - FROM releases_media - WHERE rid IN(!l)|, + if($what =~ /media/) { + push(@{$r->[$r{$_->{xid}}]{media}}, $_) for (@{$self->dbAll(" + SELECT $colname AS xid, medium, qty + FROM releases_media$hist + WHERE $colname IN(!l)", [ keys %r ] )}); } @@ -179,38 +203,38 @@ sub dbReleaseRevisionInsert { my %set = map exists($o->{$_}) ? ("$_ = ?", $o->{$_}) : (), qw|title original gtin catalog website released notes minage type patch resolution voiced freeware doujin ani_story ani_ero|; - $self->dbExec('UPDATE edit_release !H', \%set) if keys %set; + $self->dbExec('UPDATE edit_releases !H', \%set) if keys %set; if($o->{languages}) { - $self->dbExec('DELETE FROM edit_release_lang'); + $self->dbExec('DELETE FROM edit_releases_lang'); my $q = join ',', map '(?)', @{$o->{languages}}; - $self->dbExec("INSERT INTO edit_release_lang (lang) VALUES $q", @{$o->{languages}}) if @{$o->{languages}}; + $self->dbExec("INSERT INTO edit_releases_lang (lang) VALUES $q", @{$o->{languages}}) if @{$o->{languages}}; } if($o->{producers}) { - $self->dbExec('DELETE FROM edit_release_producers'); + $self->dbExec('DELETE FROM edit_releases_producers'); my $q = join ',', map '(?,?,?)', @{$o->{producers}}; my @q = map +($_->[0], $_->[1]?1:0, $_->[2]?1:0), @{$o->{producers}}; - $self->dbExec("INSERT INTO edit_release_producers (pid, developer, publisher) VALUES $q", @q) if @q; + $self->dbExec("INSERT INTO edit_releases_producers (pid, developer, publisher) VALUES $q", @q) if @q; } if($o->{platforms}) { - $self->dbExec('DELETE FROM edit_release_platforms'); + $self->dbExec('DELETE FROM edit_releases_platforms'); my $q = join ',', map '(?)', @{$o->{platforms}}; - $self->dbExec("INSERT INTO edit_release_platforms (platform) VALUES $q", @{$o->{platforms}}) if @{$o->{platforms}}; + $self->dbExec("INSERT INTO edit_releases_platforms (platform) VALUES $q", @{$o->{platforms}}) if @{$o->{platforms}}; } if($o->{vn}) { - $self->dbExec('DELETE FROM edit_release_vn'); + $self->dbExec('DELETE FROM edit_releases_vn'); my $q = join ',', map '(?)', @{$o->{vn}}; - $self->dbExec("INSERT INTO edit_release_vn (vid) VALUES $q", @{$o->{vn}}) if @{$o->{vn}}; + $self->dbExec("INSERT INTO edit_releases_vn (vid) VALUES $q", @{$o->{vn}}) if @{$o->{vn}}; } if($o->{media}) { - $self->dbExec('DELETE FROM edit_release_media'); + $self->dbExec('DELETE FROM edit_releases_media'); my $q = join ',', map '(?,?)', @{$o->{media}}; my @q = map +($_->[0], $_->[1]), @{$o->{media}}; - $self->dbExec("INSERT INTO edit_release_media (medium, qty) VALUES $q", @q) if @q; + $self->dbExec("INSERT INTO edit_releases_media (medium, qty) VALUES $q", @q) if @q; } } diff --git a/lib/VNDB/DB/Staff.pm b/lib/VNDB/DB/Staff.pm index 4dfe0756..69f4d6b3 100644 --- a/lib/VNDB/DB/Staff.pm +++ b/lib/VNDB/DB/Staff.pm @@ -5,9 +5,9 @@ use strict; use warnings; use Exporter 'import'; -our @EXPORT = qw|dbStaffGet dbStaffRevisionInsert dbStaffAliasIds|; +our @EXPORT = qw|dbStaffGet dbStaffGetRev dbStaffRevisionInsert dbStaffAliasIds|; -# options: results, page, id, aid, search, exact, rev, truename, role, gender +# options: results, page, id, aid, search, exact, truename, role, gender # what: extended changes roles aliases sub dbStaffGet { my $self = shift; @@ -31,15 +31,15 @@ sub dbStaffGet { $o{search} =~ s/%//g if $o{search}; my %where = ( - !$o{id} && !$o{rev} ? ( 's.hidden = FALSE' => 1 ) : (), + !$o{id} ? ( 's.hidden = FALSE' => 1 ) : (), $o{id} ? ( ref $o{id} ? ('s.id IN(!l)' => [$o{id}]) : ('s.id = ?' => $o{id}) ) : (), - $o{aid} ? ( ref $o{aid} ? ('sa.id IN(!l)' => [$o{aid}]) : ('sa.id = ?' => $o{aid}) ) : (), - $o{truename} ? ( 'sr.aid = sa.id' => 1 ) : (), - defined $o{gender} ? ( 'sr.gender IN(!l)' => [ ref $o{gender} ? $o{gender} : [$o{gender}] ]) : (), + $o{aid} ? ( ref $o{aid} ? ('sa.aid IN(!l)' => [$o{aid}]) : ('sa.aid = ?' => $o{aid}) ) : (), + $o{id} || $o{truename} ? ( 's.aid = sa.aid' => 1 ) : (), + defined $o{gender} ? ( 's.gender IN(!l)' => [ ref $o{gender} ? $o{gender} : [$o{gender}] ]) : (), defined $o{role} ? ( '('.join(' OR ', - @roles ? ( 'EXISTS(SELECT 1 FROM vn_staff vs JOIN vn v ON v.latest = vs.vid WHERE vs.aid = sa.id AND vs.role IN(!l) AND NOT v.hidden)' ) : (), - $seiyuu ? ( 'EXISTS(SELECT 1 FROM vn_seiyuu vsy JOIN vn v ON v.latest = vsy.vid WHERE vsy.aid = sa.id AND NOT v.hidden)' ) : () + @roles ? ( 'EXISTS(SELECT 1 FROM vn_staff vs JOIN vn v ON v.id = vs.id WHERE vs.aid = sa.aid AND vs.role IN(!l) AND NOT v.hidden)' ) : (), + $seiyuu ? ( 'EXISTS(SELECT 1 FROM vn_seiyuu vsy JOIN vn v ON v.id = vsy.id WHERE vsy.aid = sa.aid AND NOT v.hidden)' ) : () ).')' => ( @roles ? [ \@roles ] : 1 ), ) : (), $o{exact} ? ( '(sa.name = ? OR sa.original = ?)' => [ ($o{exact}) x 2 ] ) : (), @@ -53,87 +53,114 @@ sub dbStaffGet { $o{char} ? ( 'LOWER(SUBSTR(sa.name, 1, 1)) = ?' => $o{char} ) : (), defined $o{char} && !$o{char} ? ( '(ASCII(sa.name) < 97 OR ASCII(sa.name) > 122) AND (ASCII(sa.name) < 65 OR ASCII(sa.name) > 90)' => 1 ) : (), - $o{rev} ? ( 'c.rev = ?' => $o{rev} ) : (), ); - my @join; - push @join, 'JOIN staff s ON '.($o{rev} ? 's.id = sr.sid' : 'sr.id = s.latest'); - push @join, 'JOIN staff_alias sa ON sa.rid = sr.id'.($o{id}?' AND sa.id = sr.aid':''); - push @join, 'JOIN changes c ON c.id = sr.id' if $o{what} =~ /changes/ || $o{rev}; - push @join, 'JOIN users u ON u.id = c.requester' if $o{what} =~ /changes/; - - my $select = 's.id, sa.id AS aid, sa.name, sa.original, sr.gender, sr.lang, sr.id AS cid'; - $select .= ', sr.desc, sr.l_wp, sr.l_site, sr.l_twitter, sr.l_anidb, s.hidden, s.locked' if $o{what} =~ /extended/; - $select .= q|, extract('epoch' from c.added) as added, c.requester, c.comments, s.latest, u.username, c.rev, c.ihid, c.ilock| if $o{what} =~ /changes/; - - my $order = 'ORDER BY sa.name'; + my $select = 's.id, sa.aid, sa.name, sa.original, s.gender, s.lang'; + $select .= ', s.desc, s.l_wp, s.l_site, s.l_twitter, s.l_anidb, s.hidden, s.locked' if $o{what} =~ /extended/; my($r, $np) = $self->dbPage(\%o, q| SELECT !s - FROM staff_rev sr - !s + FROM staff s + JOIN staff_alias sa ON sa.id = s.id !W - !s|, - $select, join(' ', @join), \%where, $order + ORDER BY sa.name|, + $select, \%where ); - if (@$r && $o{what} =~ /roles|aliases/) { + return _enrich($self, $r, $np, 0, $o{what}); +} + + +sub dbStaffGetRev { + my $self = shift; + my %o = (what => '', @_); + + $o{rev} ||= $self->dbRow('SELECT MAX(rev) AS rev FROM changes WHERE type = \'s\' AND itemid = ?', $o{id})->{rev}; + + my $select = 'c.itemid AS id, sa.aid, sa.name, sa.original, s.gender, s.lang'; + $select .= ', extract(\'epoch\' from c.added) as added, c.requester, c.comments, u.username, c.rev, c.ihid, c.ilock'; + $select .= ', c.id AS cid, NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev = c.rev+1) AS lastrev'; + $select .= ', s.desc, s.l_wp, s.l_site, s.l_twitter, s.l_anidb, so.hidden, so.locked' if $o{what} =~ /extended/; + + my $r = $self->dbAll(q| + SELECT !s + FROM changes c + JOIN staff so ON so.id = c.itemid + JOIN staff_hist s ON s.chid = c.id + JOIN staff_alias_hist sa ON sa.chid = c.id AND s.aid = sa.aid + JOIN users u ON u.id = c.requester + WHERE c.type = 's' AND c.itemid = ? AND c.rev = ?|, + $select, $o{id}, $o{rev} + ); + + return _enrich($self, $r, 0, 1, $o{what}); +} + + +sub _enrich { + my($self, $r, $np, $rev, $what) = @_; + + # Role info is linked to VN revisions, so is independent of the selected staff revision + if(@$r && $what =~ /roles/) { my %r = map { $_->{roles} = []; $_->{cast} = []; + ($_->{id}, $_); + } @$r; + + push @{$r{ delete $_->{id} }{roles}}, $_ for (@{$self->dbAll(q| + SELECT sa.id, sa.aid, v.id AS vid, sa.name, sa.original, v.c_released, v.title, v.original AS t_original, vs.role, vs.note + FROM vn_staff vs + JOIN vn v ON v.id = vs.id + JOIN staff_alias sa ON vs.aid = sa.aid + WHERE sa.id IN(!l) AND NOT v.hidden + ORDER BY v.c_released ASC, v.title ASC, vs.role ASC|, [ keys %r ] + )}); + push @{$r{ delete $_->{id} }{cast}}, $_ for (@{$self->dbAll(q| + SELECT sa.id, sa.aid, v.id AS vid, sa.name, sa.original, v.c_released, v.title, v.original AS t_original, c.id AS cid, c.name AS c_name, c.original AS c_original, vs.note + FROM vn_seiyuu vs + JOIN vn v ON v.id = vs.id + JOIN chars c ON c.id = vs.cid + JOIN staff_alias sa ON vs.aid = sa.aid + WHERE sa.id IN(!l) AND NOT v.hidden + ORDER BY v.c_released ASC, v.title ASC|, [ keys %r ] + )}); + } + + if(@$r && $what =~ /aliases/) { + my ($col, $hist, $colname) = $rev ? ('cid', '_hist', 'chid') : ('id', '', 'id'); + my %r = map { $_->{aliases} = []; - ($_->{cid}, $_); + ($_->{$col}, $_); } @$r; - if ($o{what} =~ /roles/) { - push @{$r{ delete $_->{rid} }{roles}}, $_ for (@{$self->dbAll(q| - SELECT sa.rid, vr.vid, sa.name, sa.original, v.c_released, vr.title, vr.original AS t_original, vs.role, vs.note - FROM vn_staff vs - JOIN vn_rev vr ON vr.id = vs.vid - JOIN vn v ON v.latest = vr.id - JOIN staff_alias sa ON vs.aid = sa.id - WHERE sa.rid IN(!l) AND NOT v.hidden - ORDER BY v.c_released ASC, vr.title ASC, vs.role ASC|, [ keys %r ] - )}); - push @{$r{ delete $_->{rid} }{cast}}, $_ for (@{$self->dbAll(q| - SELECT sa.rid, vr.vid, sa.name, sa.original, v.c_released, vr.title, vr.original AS t_original, cr.cid, cr.name AS c_name, cr.original AS c_original, vs.note - FROM vn_seiyuu vs - JOIN vn_rev vr ON vr.id = vs.vid - JOIN vn v ON v.latest = vr.id - JOIN chars_rev cr ON cr.cid = vs.cid - JOIN chars c ON c.latest = cr.id - JOIN staff_alias sa ON vs.aid = sa.id - WHERE sa.rid IN(!l) AND NOT v.hidden - ORDER BY v.c_released ASC, vr.title ASC|, [ keys %r ] - )}); - } - if ($o{what} =~ /aliases/) { - push @{$r{ delete $_->{rid} }{aliases}}, $_ for (@{$self->dbAll(q| - SELECT sa.id, sa.rid, sa.name, sa.original - FROM staff_alias sa - JOIN staff_rev sr ON sr.id = sa.rid - WHERE sr.id IN(!l) AND sr.aid <> sa.id - ORDER BY sa.name ASC|, [ keys %r ] - )}); - } + + push @{$r{ delete $_->{xid} }{aliases}}, $_ for (@{$self->dbAll(" + SELECT s.$colname AS xid, sa.aid, sa.name, sa.original + FROM staff_alias$hist sa + JOIN staff$hist s ON s.$colname = sa.$colname + WHERE s.$colname IN(!l) AND s.aid <> sa.aid + ORDER BY sa.name ASC", [ keys %r ] + )}); } return wantarray ? ($r, $np) : $r; } + # Updates the edit_* tables, used from dbItemEdit() # Arguments: { columns in staff_rev and staff_alias}, sub dbStaffRevisionInsert { my($self, $o) = @_; - $self->dbExec('DELETE FROM edit_staff_aliases'); + $self->dbExec('DELETE FROM edit_staff_alias'); if($o->{aid}) { $self->dbExec(q| - INSERT INTO edit_staff_aliases (id, name, original) VALUES (?, ?, ?)|, + INSERT INTO edit_staff_alias (aid, name, original) VALUES (?, ?, ?)|, $o->{aid}, $o->{name}, $o->{original}); } else { $o->{aid} = $self->dbRow(q| - INSERT INTO edit_staff_aliases (name, original) VALUES (?, ?) RETURNING id|, - $o->{name}, $o->{original})->{id}; + INSERT INTO edit_staff_alias (name, original) VALUES (?, ?) RETURNING aid|, + $o->{name}, $o->{original})->{aid}; } my %staff = map exists($o->{$_}) ? (qq|"$_" = ?|, $o->{$_}) : (), @@ -141,9 +168,9 @@ sub dbStaffRevisionInsert { $self->dbExec('UPDATE edit_staff !H', \%staff) if %staff; for my $a (@{$o->{aliases}}) { if($a->{aid}) { - $self->dbExec('INSERT INTO edit_staff_aliases (id, name, original) VALUES (!l)', [ @{$a}{qw|aid name orig|} ]); + $self->dbExec('INSERT INTO edit_staff_alias (aid, name, original) VALUES (!l)', [ @{$a}{qw|aid name orig|} ]); } else { - $self->dbExec('INSERT INTO edit_staff_aliases (name, original) VALUES (?, ?)', $a->{name}, $a->{orig}); + $self->dbExec('INSERT INTO edit_staff_alias (name, original) VALUES (?, ?)', $a->{name}, $a->{orig}); } } } @@ -153,10 +180,10 @@ sub dbStaffRevisionInsert { sub dbStaffAliasIds { my($self, $sid) = @_; return $self->dbAll(q| - SELECT DISTINCT sa.id - FROM staff_alias sa - JOIN staff_rev sr ON sr.id = sa.rid - WHERE sr.sid = ?|, $sid); + SELECT DISTINCT sa.aid + FROM changes c + JOIN staff_alias_hist sa ON sa.chid = c.id + WHERE c.type = 's' AND c.itemid = ?|, $sid); } 1; diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm index 37411a62..f3c901ab 100644 --- a/lib/VNDB/DB/Tags.pm +++ b/lib/VNDB/DB/Tags.pm @@ -184,11 +184,11 @@ sub dbTagLinks { my @select = ( qw|tv.tag tv.vid tv.uid tv.vote tv.spoiler tv.ignore|, "EXTRACT('epoch' from tv.date) AS date", - $o{what} =~ /details/ ? (qw|vr.title u.username t.name|) : (), + $o{what} =~ /details/ ? (qw|v.title u.username t.name|) : (), ); my @join = $o{what} =~ /details/ ? ( - 'JOIN vn v ON v.id = tv.vid JOIN vn_rev vr ON vr.id = v.latest', + 'JOIN vn v ON v.id = tv.vid', 'JOIN users u ON u.id = tv.uid', 'JOIN tags t ON t.id = tv.tag' ) : (); @@ -196,7 +196,7 @@ sub dbTagLinks { my $order = !$o{sort} ? '' : 'ORDER BY '.{ username => 'u.username', date => 'tv.date', - title => 'vr.title', + title => 'v.title', tag => 't.name', }->{$o{sort}}.($o{reverse} ? ' DESC' : ' ASC'); diff --git a/lib/VNDB/DB/ULists.pm b/lib/VNDB/DB/ULists.pm index 6831e579..23bde7cf 100644 --- a/lib/VNDB/DB/ULists.pm +++ b/lib/VNDB/DB/ULists.pm @@ -59,22 +59,21 @@ sub dbVNListList { 'vl.uid = ?' => $o{uid}, defined($o{voted}) ? ('vo.vote !s NULL' => $o{voted} ? 'IS NOT' : 'IS') : (), defined($o{status})? ('vl.status = ?' => $o{status}) : (), - $o{char} ? ('LOWER(SUBSTR(vr.title, 1, 1)) = ?' => $o{char} ) : (), + $o{char} ? ('LOWER(SUBSTR(v.title, 1, 1)) = ?' => $o{char} ) : (), defined $o{char} && !$o{char} ? ( - '(ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' => 1 ) : (), + '(ASCII(v.title) < 97 OR ASCII(v.title) > 122) AND (ASCII(v.title) < 65 OR ASCII(v.title) > 90)' => 1 ) : (), ); my $order = sprintf { - title => 'vr.title %s', - vote => 'vo.vote %s NULLS LAST, vr.title ASC', + title => 'v.title %s', + vote => 'vo.vote %s NULLS LAST, v.title ASC', }->{ $o{sort}||'title' }, $o{reverse} ? 'DESC' : 'ASC'; # execute query my($r, $np) = $self->dbPage(\%o, qq| - SELECT vr.vid, vr.title, vr.original, vl.status, vl.notes, COALESCE(vo.vote, 0) AS vote + SELECT vl.vid, v.title, v.original, vl.status, vl.notes, COALESCE(vo.vote, 0) AS vote FROM vnlists vl JOIN vn v ON v.id = vl.vid - JOIN vn_rev vr ON vr.id = v.latest LEFT JOIN votes vo ON vo.vid = vl.vid AND vo.uid = vl.uid !W ORDER BY !s|, @@ -89,27 +88,26 @@ sub dbVNListList { } @$r; my $rel = $self->dbAll(q| - SELECT rv.vid, rr.rid, r.latest, rr.title, rr.original, rr.released, rr.type, rl.status + SELECT rv.vid, rl.rid, r.title, r.original, r.released, r.type, rl.status FROM rlists rl JOIN releases r ON rl.rid = r.id - JOIN releases_rev rr ON rr.id = r.latest - JOIN releases_vn rv ON rv.rid = r.latest + JOIN releases_vn rv ON rv.id = r.id WHERE rl.uid = ? AND rv.vid IN(!l) - ORDER BY rr.released ASC|, + ORDER BY r.released ASC|, $o{uid}, [ keys %vns ] ); if(@$rel) { - my %rel = map { $_->{latest} => [] } @$rel; - push(@{$rel{$_->{rid}}}, $_->{lang}) for (@{$self->dbAll(q| - SELECT rid, lang + my %rel = map { $_->{rid} => [] } @$rel; + push(@{$rel{$_->{id}}}, $_->{lang}) for (@{$self->dbAll(q| + SELECT id, lang FROM releases_lang - WHERE rid IN(!l)|, + WHERE id IN(!l)|, [ keys %rel ] )}); for(@$rel) { - $_->{languages} = $rel{$_->{latest}}; + $_->{languages} = $rel{$_->{rid}}; push @{$vns{$_->{vid}}}, $_; } } @@ -189,9 +187,9 @@ sub dbVoteGet { $o{vid} ? ( 'n.vid = ?' => $o{vid} ) : (), $o{hide} ? ( 'NOT EXISTS(SELECT 1 FROM users_prefs WHERE uid = n.uid AND key = \'hide_list\')' => 1 ) : (), $o{hide_ign} ? ( '(NOT u.ign_votes OR u.id = ?)' => $self->authInfo->{id}||0 ) : (), - $o{vn_char} ? ( 'LOWER(SUBSTR(vr.title, 1, 1)) = ?' => $o{vn_char} ) : (), + $o{vn_char} ? ( 'LOWER(SUBSTR(v.title, 1, 1)) = ?' => $o{vn_char} ) : (), defined $o{vn_char} && !$o{vn_char} ? ( - '(ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' => 1 ) : (), + '(ASCII(v.title) < 97 OR ASCII(v.title) > 122) AND (ASCII(v.title) < 65 OR ASCII(v.title) > 90)' => 1 ) : (), $o{user_char} ? ( 'LOWER(SUBSTR(u.username, 1, 1)) = ?' => $o{user_char} ) : (), defined $o{user_char} && !$o{user_char} ? ( '(ASCII(u.username) < 97 OR ASCII(u.username) > 122) AND (ASCII(u.username) < 65 OR ASCII(u.username) > 90)' => 1 ) : (), @@ -200,13 +198,12 @@ sub dbVoteGet { my @select = ( qw|n.vid n.vote n.uid|, q|extract('epoch' from n.date) as date|, $o{what} =~ /user/ ? ('u.username') : (), - $o{what} =~ /vn/ ? (qw|vr.title vr.original|) : (), + $o{what} =~ /vn/ ? (qw|v.title v.original|) : (), ); my @join = ( $o{what} =~ /vn/ ? ( 'JOIN vn v ON v.id = n.vid', - 'JOIN vn_rev vr ON vr.id = v.latest' ) : (), $o{what} =~ /user/ || $o{hide} ? ( 'JOIN users u ON u.id = n.uid' @@ -216,8 +213,8 @@ sub dbVoteGet { my $order = sprintf { date => 'n.date %s', username => 'u.username %s', - title => 'vr.title %s', - vote => 'n.vote %s'.($o{what} =~ /vn/ ? ', vr.title ASC' : $o{what} =~ /user/ ? ', u.username ASC' : ''), + title => 'v.title %s', + vote => 'n.vote %s'.($o{what} =~ /vn/ ? ', v.title ASC' : $o{what} =~ /user/ ? ', u.username ASC' : ''), }->{$o{sort}}, $o{reverse} ? 'DESC' : 'ASC'; my($r, $np) = $self->dbPage(\%o, q| @@ -302,15 +299,14 @@ sub dbWishListGet { my $select = q|wl.vid, wl.wstat, extract('epoch' from wl.added) AS added|; my @join; if($o{what} =~ /vn/) { - $select .= ', vr.title, vr.original'; - push @join, 'JOIN vn v ON v.id = wl.vid', - 'JOIN vn_rev vr ON vr.id = v.latest'; + $select .= ', v.title, v.original'; + push @join, 'JOIN vn v ON v.id = wl.vid'; } my $order = sprintf { - title => 'vr.title %s', + title => 'v.title %s', added => 'wl.added %s', - wstat => 'wl.wstat %2$s, vr.title ASC', + wstat => 'wl.wstat %2$s, v.title ASC', }->{ $o{sort}||'added' }, $o{reverse} ? 'DESC' : 'ASC', $o{reverse} ? 'ASC' : 'DESC'; my($r, $np) = $self->dbPage(\%o, q| diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index 5d404b28..8f3bf913 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -7,12 +7,12 @@ use Exporter 'import'; use VNDB::Func 'gtintype', 'normalize_query'; use Encode 'decode_utf8'; -our @EXPORT = qw|dbVNGet dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreenshotGet dbScreenshotRandom dbVNHasChar dbVNHasStaff dbVNImportSeiyuu|; +our @EXPORT = qw|dbVNGet dbVNGetRev dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreenshotGet dbScreenshotRandom dbVNHasChar dbVNHasStaff dbVNImportSeiyuu|; -# Options: id, rev, char, search, length, lang, olang, plat, tag_inc, tag_exc, tagspoil, +# Options: id, char, search, length, lang, olang, plat, tag_inc, tag_exc, tagspoil, # hasani, hasshot, ul_notblack, ul_onwish, results, page, what, sort, reverse, inc_hidden -# What: extended anime credits relations screenshots relgraph rating ranking changes wishlist vnlist +# What: extended anime credits relations screenshots relgraph rating ranking wishlist vnlist # Note: wishlist and vnlist are ignored (no db search) unless a user is logged in # Sort: id rel pop rating title tagscore rand sub dbVNGet { @@ -33,14 +33,12 @@ sub dbVNGet { my @where = ( $o{id} ? ( 'v.id IN(!l)' => [ ref $o{id} ? $o{id} : [$o{id}] ] ) : (), - $o{rev} ? ( - 'c.rev = ?' => $o{rev} ) : (), $o{char} ? ( - 'LOWER(SUBSTR(vr.title, 1, 1)) = ?' => $o{char} ) : (), + 'LOWER(SUBSTR(v.title, 1, 1)) = ?' => $o{char} ) : (), defined $o{char} && !$o{char} ? ( - '(ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' => 1 ) : (), + '(ASCII(v.title) < 97 OR ASCII(v.title) > 122) AND (ASCII(v.title) < 65 OR ASCII(v.title) > 90)' => 1 ) : (), defined $o{length} ? ( - 'vr.length IN(!l)' => [ ref $o{length} ? $o{length} : [$o{length}] ]) : (), + 'v.length IN(!l)' => [ ref $o{length} ? $o{length} : [$o{length}] ]) : (), $o{lang} ? ( 'v.c_languages && ARRAY[!l]::language[]' => [ ref $o{lang} ? $o{lang} : [$o{lang}] ]) : (), $o{olang} ? ( @@ -48,9 +46,9 @@ sub dbVNGet { $o{plat} ? ( '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 ' ]) : (), + '!sEXISTS(SELECT 1 FROM vn_anime va WHERE va.id = v.id)' => [ $o{hasani} ? '' : 'NOT ' ]) : (), defined $o{hasshot} ? ( - '!sEXISTS(SELECT 1 FROM vn_screenshots vs WHERE vs.vid = vr.id)' => [ $o{hasshot} ? '' : 'NOT ' ]) : (), + '!sEXISTS(SELECT 1 FROM vn_screenshots vs WHERE vs.id = v.id)' => [ $o{hasshot} ? '' : 'NOT ' ]) : (), $o{tag_inc} ? ( 'v.id IN(SELECT vid FROM tags_vn_inherit WHERE tag IN(!l) AND spoiler <= ? GROUP BY vid HAVING COUNT(tag) = ?)', [ ref $o{tag_inc} ? $o{tag_inc} : [$o{tag_inc}], $o{tagspoil}, ref $o{tag_inc} ? $#{$o{tag_inc}}+1 : 1 ]) : (), @@ -66,24 +64,14 @@ sub dbVNGet { 'v.id !s IN(SELECT vid FROM votes WHERE uid = ?)' => [ $o{ul_voted} ? '' : 'NOT', $uid ] ) : (), $uid && defined $o{ul_onlist} ? ( 'v.id !s IN(SELECT vid FROM vnlists WHERE uid = ?)' => [ $o{ul_onlist} ? '' : 'NOT', $uid ] ) : (), - # don't fetch hidden items unless we ask for an ID - !$o{id} && !$o{rev} && !$o{inc_hidden} ? ( + !$o{id} && !$o{inc_hidden} ? ( 'v.hidden = FALSE' => 0 ) : (), - # optimize fetching random entries (only when there are no other filters present, otherwise this won't work well) + # optimize fetching random entries (only when there are no other filters present, otherwise this won't work well) $o{sort} eq 'rand' && $o{results} <= 10 && !grep(!/^(?:results|page|what|sort|tagspoil)$/, keys %o) ? ( - sprintf 'v.id IN(SELECT floor(random() * last_value)::integer - FROM generate_series(1,20), (SELECT last_value FROM vn_id_seq) s1 - LIMIT 20)' ) : (), + 'v.id IN(SELECT floor(random() * last_value)::integer FROM generate_series(1,20), (SELECT MAX(id) AS last_value FROM vn) s1 LIMIT 20)' ) : (), ); my @join = ( - $o{rev} ? - 'JOIN vn v ON v.id = vr.vid' : - 'JOIN vn v ON vr.id = v.latest', - $o{rev} || $o{what} =~ /changes/ ? - 'JOIN changes c ON c.id = vr.id' : (), - $o{what} =~ /changes/ ? - 'JOIN users u ON u.id = c.requester' : (), $o{what} =~ /relgraph/ ? 'JOIN relgraphs vg ON vg.id = v.rgraph' : (), $uid && $o{what} =~ /wishlist/ ? @@ -92,8 +80,7 @@ sub dbVNGet { SELECT irv.vid, COUNT(*) AS userlist_all, SUM(CASE WHEN irl.status = 2 THEN 1 ELSE 0 END) AS userlist_obtained FROM rlists irl - JOIN releases ir ON irl.rid = ir.id - JOIN releases_vn irv ON irv.rid = ir.latest + JOIN releases_vn irv ON irv.id = irl.rid WHERE irl.uid = $uid GROUP BY irv.vid ) AS vnlist ON vnlist.vid = v.id") : (), @@ -101,11 +88,9 @@ 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 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', + qw|v.id v.locked v.hidden v.c_released v.c_languages::text[] v.c_platforms::text[] v.title v.original v.rgraph|, $o{what} =~ /extended/ ? ( - qw|vr.alias vr.image vr.img_nsfw vr.length vr.desc vr.l_wp vr.l_encubed vr.l_renai| ) : (), - $o{what} =~ /changes/ ? ( - qw|c.requester c.comments v.latest u.username c.rev c.ihid c.ilock|, q|extract('epoch' from c.added) as added|) : (), + qw|v.alias v.image v.img_nsfw v.length v.desc v.l_wp v.l_encubed v.l_renai| ) : (), $o{what} =~ /relgraph/ ? 'vg.svg' : (), $o{what} =~ /rating/ ? (qw|v.c_popularity v.c_rating v.c_votecount|) : (), $o{what} =~ /ranking/ ? ( @@ -120,100 +105,129 @@ sub dbVNGet { ); my $order = sprintf { - id => 'id %s', - rel => 'c_released %s, title ASC', - pop => 'c_popularity %s NULLS LAST', - rating => 'c_rating %s NULLS LAST', - title => 'title %s', + id => 'v.id %s', + rel => 'v.c_released %s, v.title ASC', + pop => 'v.c_popularity %s NULLS LAST', + rating => 'v.c_rating %s NULLS LAST', + title => 'v.title %s', tagscore => 'tagscore %s', rand => 'RANDOM()', }->{$o{sort}}, $o{reverse} ? 'DESC' : 'ASC'; my($r, $np) = $self->dbPage(\%o, q| SELECT !s - FROM vn_rev vr + FROM vn v !s !W ORDER BY !s|, join(', ', @select), join(' ', @join), \@where, $order, ); - if($o{what} =~ /relgraph/) { - $_->{svg} = decode_utf8($_->{svg}) for @$r; - } + $_->{svg} && ($_->{svg} = decode_utf8($_->{svg})) for (@$r); + return _enrich($self, $r, $np, 0, $o{what}); +} + + +sub dbVNGetRev { + my $self = shift; + my %o = (what => '', @_); + + $o{rev} ||= $self->dbRow('SELECT MAX(rev) AS rev FROM changes WHERE type = \'v\' AND itemid = ?', $o{id})->{rev}; - if(@$r && $o{what} =~ /anime|relations|screenshots|credits/) { + # XXX: Too much duplication with code in dbVNGet() here. Can we combine some code here? + my $uid = $self->authInfo->{id}; + + my $select = 'c.itemid AS id, vo.c_released, vo.c_languages::text[], vo.c_platforms::text[], v.title, v.original, vo.rgraph'; + $select .= ', extract(\'epoch\' from c.added) as added, c.requester, c.comments, u.username, c.rev, c.ihid, c.ilock'; + $select .= ', c.id AS cid, NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev = c.rev+1) AS lastrev'; + $select .= ', v.alias, v.image, v.img_nsfw, v.length, v.desc, v.l_wp, v.l_encubed, v.l_renai, vo.hidden, vo.locked' if $o{what} =~ /extended/; + $select .= ', vo.c_popularity, vo.c_rating, vo.c_votecount' if $o{what} =~ /rating/; + $select .= ', (SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > COALESCE(vo.c_popularity, 0.0)) AS p_ranking' + .', (SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_rating > COALESCE(vo.c_rating, 0.0)) AS r_ranking' if $o{what} =~ /ranking/; + + my $r = $self->dbAll(q| + SELECT !s + FROM changes c + JOIN vn vo ON vo.id = c.itemid + JOIN vn_hist v ON v.chid = c.id + JOIN users u ON u.id = c.requester + WHERE c.type = 'v' AND c.itemid = ? AND c.rev = ?|, + $select, $o{id}, $o{rev} + ); + + return _enrich($self, $r, 0, 1, $o{what}); +} + + +sub _enrich { + my($self, $r, $np, $rev, $what) = @_; + + if(@$r && $what =~ /anime|relations|screenshots|credits/) { + my($col, $hist, $colname) = $rev ? ('cid', '_hist', 'chid') : ('id', '', 'id'); my %r = map { $r->[$_]{anime} = []; $r->[$_]{credits} = []; $r->[$_]{seiyuu} = []; $r->[$_]{relations} = []; $r->[$_]{screenshots} = []; - ($r->[$_]{cid}, $_) + ($r->[$_]{$col}, $_) } 0..$#$r; - if($o{what} =~ /credits/) { - push(@{$r->[$r{ delete $_->{vid} }]{credits}}, $_) for (@{$self->dbAll(q| - SELECT vs.vid, s.id, vs.aid, sa.name, sa.original, sr.gender, sr.lang, vs.role, vs.note - FROM vn_staff vs - JOIN staff_alias sa ON vs.aid = sa.id - JOIN staff_rev sr ON sr.id = sa.rid - JOIN staff s ON sr.id = s.latest - WHERE s.hidden = FALSE AND vs.vid IN(!l) - ORDER BY vs.role ASC, sa.name ASC|, + if($what =~ /credits/) { + push(@{$r->[$r{ delete $_->{xid} }]{credits}}, $_) for (@{$self->dbAll(" + SELECT vs.$colname AS xid, s.id, vs.aid, sa.name, sa.original, s.gender, s.lang, vs.role, vs.note + FROM vn_staff$hist vs + JOIN staff_alias sa ON vs.aid = sa.aid + JOIN staff s ON s.id = sa.id + WHERE s.hidden = FALSE AND vs.$colname IN(!l) + ORDER BY vs.role ASC, sa.name ASC", [ keys %r ] )}); - push(@{$r->[$r{ delete $_->{vid} }]{seiyuu}}, $_) for (@{$self->dbAll(q| - SELECT vs.vid, s.id, vs.aid, sa.name, sa.original, sr.gender, sr.lang, cr.cid, cr.name AS cname, vs.note, - (SELECT MAX(spoil) FROM chars_vns cv WHERE cv.vid = vr.vid AND cv.cid = cr.id) AS spoil - FROM vn_seiyuu vs - JOIN vn_rev vr ON vr.id = vs.vid - JOIN staff_alias sa ON vs.aid = sa.id - JOIN staff_rev sr ON sr.id = sa.rid - JOIN staff s ON s.id = sr.sid AND sr.id = s.latest + + # The seiyuu query needs the VN id to get the VN<->Char spoiler level. + # Obtaining this ID is different when using the hist table. + my($vid, $join) = $rev ? ('h.itemid', 'JOIN changes h ON h.id = vs.chid') : ('vs.id', ''); + push(@{$r->[$r{ delete $_->{xid} }]{seiyuu}}, $_) for (@{$self->dbAll(" + SELECT vs.$colname AS xid, s.id, vs.aid, sa.name, sa.original, s.gender, s.lang, c.id AS cid, c.name AS cname, vs.note, + (SELECT MAX(spoil) FROM chars_vns cv WHERE cv.vid = $vid AND cv.id = c.id) AS spoil + FROM vn_seiyuu$hist vs + JOIN staff_alias sa ON vs.aid = sa.aid + JOIN staff s ON s.id = sa.id JOIN chars c ON c.id = vs.cid - JOIN chars_rev cr ON cr.id = c.latest - WHERE s.hidden = FALSE AND vs.vid IN(!l) - AND cr.id IN(SELECT cv.cid FROM chars_vns cv WHERE cv.vid = vr.vid AND cv.cid = cr.id) - ORDER BY cr.name|, + $join + WHERE s.hidden = FALSE AND vs.$colname IN(!l) + ORDER BY c.name", [ keys %r ] )}); } - if($o{what} =~ /anime/) { - push(@{$r->[$r{$_->{vid}}]{anime}}, $_) && delete $_->{vid} for (@{$self->dbAll(q| - SELECT va.vid, a.id, a.year, a.ann_id, a.nfo_id, a.type, a.title_romaji, a.title_kanji, extract('epoch' from a.lastfetch) AS lastfetch - FROM vn_anime va + if($what =~ /anime/) { + push(@{$r->[$r{ delete $_->{xid} }]{anime}}, $_) for (@{$self->dbAll(" + SELECT va.$colname AS xid, a.id, a.year, a.ann_id, a.nfo_id, a.type, a.title_romaji, a.title_kanji, extract('epoch' from a.lastfetch) AS lastfetch + FROM vn_anime$hist va JOIN anime a ON va.aid = a.id - WHERE va.vid IN(!l)|, + WHERE va.$colname IN(!l)", [ keys %r ] )}); } - if($o{what} =~ /relations/) { - push(@{$r->[$r{$_->{vid1}}]{relations}}, { - relation => $_->{relation}, - official => $_->{official}, - id => $_->{vid2}, - title => $_->{title}, - original => $_->{original}, - }) for(@{$self->dbAll(q| - SELECT rel.vid1, rel.vid2, rel.relation, rel.official, vr.title, vr.original - FROM vn_relations rel - JOIN vn v ON rel.vid2 = v.id - JOIN vn_rev vr ON v.latest = vr.id - WHERE rel.vid1 IN(!l)|, + if($what =~ /relations/) { + push(@{$r->[$r{ delete $_->{xid} }]{relations}}, $_) for(@{$self->dbAll(" + SELECT rel.$colname AS xid, rel.vid AS id, rel.relation, rel.official, v.title, v.original + FROM vn_relations$hist rel + JOIN vn v ON rel.vid = v.id + WHERE rel.$colname IN(!l)", [ keys %r ] )}); } - if($o{what} =~ /screenshots/) { - push(@{$r->[$r{$_->{vid}}]{screenshots}}, $_) && delete $_->{vid} for (@{$self->dbAll(q| - SELECT vs.vid, s.id, vs.nsfw, vs.rid, s.width, s.height - FROM vn_screenshots vs + if($what =~ /screenshots/) { + push(@{$r->[$r{ delete $_->{xid} }]{screenshots}}, $_) for (@{$self->dbAll(" + SELECT vs.$colname AS xid, s.id, vs.nsfw, vs.rid, s.width, s.height + FROM vn_screenshots$hist vs JOIN screenshots s ON vs.scr = s.id - WHERE vs.vid IN(!l) - ORDER BY vs.scr|, + WHERE vs.$colname IN(!l) + ORDER BY vs.scr", [ keys %r ] )}); } @@ -296,41 +310,36 @@ sub dbScreenshotGet { sub dbScreenshotRandom { my($self, @vids) = @_; return $self->dbAll(q| - SELECT s.id AS scr, s.width, s.height, vr.vid, vr.title + SELECT s.id AS scr, s.width, s.height, v.id AS vid, v.title FROM screenshots s JOIN vn_screenshots vs ON vs.scr = s.id - JOIN vn_rev vr ON vr.id = vs.vid - JOIN vn v ON v.id = vr.vid AND v.latest = vs.vid + JOIN vn v ON v.id = vs.id WHERE NOT v.hidden AND NOT vs.nsfw AND s.id IN( SELECT floor(random() * last_value)::integer - FROM generate_series(1,20), (SELECT last_value FROM screenshots_id_seq) s1 + FROM generate_series(1,20), (SELECT MAX(id) AS last_value FROM screenshots) s1 LIMIT 20 ) LIMIT 4| ) if !@vids; # this query is faster than it looks return $self->dbAll(join(' UNION ALL ', map - q|SELECT s.id AS scr, s.width, s.height, vr.vid, vr.title, RANDOM() AS position - FROM vn v - JOIN vn_rev vr ON vr.id = v.latest - JOIN vn_screenshots vs ON vs.vid = v.latest + q|SELECT s.id AS scr, s.width, s.height, v.id AS vid, v.title, RANDOM() AS position + FROM ( + SELECT vs2.id, vs2.scr FROM vn_screenshots vs2 + WHERE vs2.id = ? AND NOT vs2.nsfw + ORDER BY RANDOM() LIMIT 1 + ) vs + JOIN vn v ON v.id = vs.id JOIN screenshots s ON s.id = vs.scr - WHERE v.id = ? AND s.id = ( - SELECT vs2.scr - FROM vn_screenshots vs2 - JOIN vn v2 ON v2.latest = vs2.vid - WHERE v2.id = v.id AND NOT vs2.nsfw - ORDER BY RANDOM() - LIMIT 1 - )|, @vids).' ORDER BY position', @vids); + |, @vids).' ORDER BY position', @vids); } sub dbVNHasChar { my($self, $vid) = @_; return $self->dbRow( - 'SELECT 1 AS exists FROM chars c JOIN chars_vns cv ON c.latest = cv.cid WHERE cv.vid = ? AND NOT c.hidden', $vid + 'SELECT 1 AS exists FROM chars c JOIN chars_vns cv ON c.id = cv.id WHERE cv.vid = ? AND NOT c.hidden', $vid )->{exists}; } @@ -338,7 +347,7 @@ sub dbVNHasChar { sub dbVNHasStaff { my($self, $vid) = @_; return $self->dbRow( - 'SELECT 1 AS exists FROM vn_staff vs FULL OUTER JOIN vn_seiyuu vsy ON vs.vid = vsy.vid JOIN vn v ON v.latest = vs.vid OR v.latest = vsy.vid WHERE v.id = ?', $vid + 'SELECT 1 AS exists FROM vn_staff WHERE id = ? UNION ALL SELECT 1 FROM vn_seiyuu WHERE id = ? LIMIT 1', $vid, $vid )->{exists}; } @@ -347,16 +356,12 @@ sub dbVNHasStaff { sub dbVNImportSeiyuu { my($self, $vid, $cids) = @_; return $self->dbAll(q| - SELECT DISTINCT ON(cr.cid) cr.cid, cr.name AS c_name, s.id AS sid, sa.id AS aid, sa.name + SELECT DISTINCT ON(c.id) c.id AS cid, c.name AS c_name, sa.id AS sid, sa.aid, sa.name FROM vn_seiyuu vs - JOIN vn v ON v.latest = vs.vid JOIN chars c ON c.id = vs.cid - JOIN chars_rev cr ON cr.id = c.latest - JOIN staff_alias sa ON sa.id = vs.aid - JOIN staff s ON sa.rid = s.latest - WHERE vs.cid IN(!l) AND v.id <> ?|, $cids, $vid); + JOIN staff_alias sa ON sa.aid = vs.aid + WHERE vs.cid IN(!l) AND vs.id <> ?|, $cids, $vid); } 1; - diff --git a/lib/VNDB/Func.pm b/lib/VNDB/Func.pm index b4848796..2644ea35 100644 --- a/lib/VNDB/Func.pm +++ b/lib/VNDB/Func.pm @@ -31,8 +31,8 @@ sub clearfloat { # Draws a CSS icon, arguments: class, title sub cssicon { - acronym class => "icons $_[0]", title => $_[1]; - lit ' '; + abbr class => "icons $_[0]", title => $_[1]; + lit ' '; end; } diff --git a/lib/VNDB/Handler/Chars.pm b/lib/VNDB/Handler/Chars.pm index 7171d506..98847b4c 100644 --- a/lib/VNDB/Handler/Chars.pm +++ b/lib/VNDB/Handler/Chars.pm @@ -20,9 +20,10 @@ TUWF::register( sub page { my($self, $id, $rev) = @_; - my $r = $self->dbCharGet( + my $method = $rev ? 'dbCharGetRev' : 'dbCharGet'; + my $r = $self->$method( id => $id, - what => 'extended traits vns seiyuu'.($rev ? ' changes' : ''), + what => 'extended traits vns seiyuu', $rev ? ( rev => $rev ) : () )->[0]; return $self->resNotFound if !$r->{id}; @@ -32,7 +33,7 @@ sub page { return if $self->htmlHiddenMessage('c', $r); if($rev) { - my $prev = $rev && $rev > 1 && $self->dbCharGet(id => $id, rev => $rev-1, what => 'changes extended traits vns')->[0]; + my $prev = $rev && $rev > 1 && $self->dbCharGetRev(id => $id, rev => $rev-1, what => 'extended traits vns')->[0]; $self->htmlRevision('c', $prev, $r, [ name => diff => 1 ], [ original => diff => 1 ], @@ -265,9 +266,9 @@ sub edit { $copy = $rev && $rev eq 'copy' || $copy && $copy eq 'copy'; $rev = undef if defined $rev && $rev !~ /^\d+$/; - my $r = $id && $self->dbCharGet(id => $id, what => 'changes extended vns traits', $rev ? (rev => $rev) : ())->[0]; + my $r = $id && $self->dbCharGetRev(id => $id, what => 'extended vns traits', $rev ? (rev => $rev) : ())->[0]; return $self->resNotFound if $id && !$r->{id}; - $rev = undef if !$r || $r->{cid} == $r->{latest}; + $rev = undef if !$r || $r->{lastrev}; return $self->htmlDenied if !$self->authCan('edit') || $id && (($r->{locked} || $r->{hidden}) && !$self->authCan('dbmod')); @@ -343,8 +344,8 @@ sub edit { $_->[1]||=undef for (@vns); $frm->{vns} = \@vns; - my $nrev = $self->dbItemEdit(c => !$copy && $id ? $r->{cid} : undef, %$frm); - return $self->resRedirect("/c$nrev->{iid}.$nrev->{rev}", 'post'); + my $nrev = $self->dbItemEdit(c => !$copy && $id ? ($r->{id}, $r->{rev}) : (undef, undef), %$frm); + return $self->resRedirect("/c$nrev->{itemid}.$nrev->{rev}", 'post'); } } diff --git a/lib/VNDB/Handler/Misc.pm b/lib/VNDB/Handler/Misc.pm index 8176d854..cb9a508a 100644 --- a/lib/VNDB/Handler/Misc.pm +++ b/lib/VNDB/Handler/Misc.pm @@ -70,12 +70,12 @@ sub homepage { a href => '/hist', mt '_home_recentchanges'; txt ' '; a href => '/feeds/changes.atom'; cssicon 'feed', mt '_atom_feed'; end; end; - my $changes = $self->dbRevisionGet(what => 'item user', results => 10, auto => 1); + my $changes = $self->dbRevisionGet(results => 10, auto => 1); ul; for (@$changes) { li; lit mt '_home_recentchanges_item', $_->{type}, - sprintf('<a href="%s" title="%s">%s</a>', "/$_->{type}$_->{iid}.$_->{rev}", + sprintf('<a href="%s" title="%s">%s</a>', "/$_->{type}$_->{itemid}.$_->{rev}", xml_escape($_->{ioriginal}||$_->{ititle}), xml_escape shorten $_->{ititle}, 33), $_; end; @@ -213,8 +213,7 @@ sub history { # get the edit history my($list, $np) = $self->dbRevisionGet( - what => 'item user', - $type && $type ne 'u' ? ( type => $type, iid => $id ) : (), + $type && $type ne 'u' ? ( type => $type, itemid => $id ) : (), $type eq 'u' ? ( uid => $id ) : (), $f->{t} ? ( type => $f->{t} eq 'a' ? [qw|v r p|] : $f->{t} ) : (), page => $f->{p}, diff --git a/lib/VNDB/Handler/Producers.pm b/lib/VNDB/Handler/Producers.pm index 6369d4d1..69000b27 100644 --- a/lib/VNDB/Handler/Producers.pm +++ b/lib/VNDB/Handler/Producers.pm @@ -44,9 +44,10 @@ sub rg { sub page { my($self, $pid, $rev) = @_; - my $p = $self->dbProducerGet( + my $method = $rev ? 'dbProducerGetRev' : 'dbProducerGet'; + my $p = $self->$method( id => $pid, - what => 'extended relations'.($rev ? ' changes' : ''), + what => 'extended relations', $rev ? ( rev => $rev ) : () )->[0]; return $self->resNotFound if !$p->{id}; @@ -56,7 +57,7 @@ sub page { return if $self->htmlHiddenMessage('p', $p); if($rev) { - my $prev = $rev && $rev > 1 && $self->dbProducerGet(id => $pid, rev => $rev-1, what => 'changes extended relations')->[0]; + my $prev = $rev && $rev > 1 && $self->dbProducerGetRev(id => $pid, rev => $rev-1, what => 'extended relations')->[0]; $self->htmlRevision('p', $prev, $p, [ type => serialize => sub { mt "_ptype_$_[0]" } ], [ name => diff => 1 ], @@ -200,9 +201,9 @@ sub _releases { sub edit { my($self, $pid, $rev) = @_; - my $p = $pid && $self->dbProducerGet(id => $pid, what => 'changes extended relations', $rev ? (rev => $rev) : ())->[0]; + my $p = $pid && $self->dbProducerGetRev(id => $pid, what => 'extended relations', rev => $rev)->[0]; return $self->resNotFound if $pid && !$p->{id}; - $rev = undef if !$p || $p->{cid} == $p->{latest}; + $rev = undef if !$p || $p->{lastrev}; return $self->htmlDenied if !$self->authCan('edit') || $pid && (($p->{locked} || $p->{hidden}) && !$self->authCan('dbmod')); @@ -246,16 +247,16 @@ sub edit { $frm->{relations} = $relations; $frm->{l_wp} = undef if !$frm->{l_wp}; - my $nrev = $self->dbItemEdit(p => $pid ? $p->{cid} : undef, %$frm); + my $nrev = $self->dbItemEdit(p => $pid||undef, $pid ? $p->{rev} : undef, %$frm); # update reverse relations if(!$pid && $#$relations >= 0 || $pid && $frm->{prodrelations} ne $b4{prodrelations}) { my %old = $pid ? (map { $_->{id} => $_->{relation} } @{$p->{relations}}) : (); my %new = map { $_->[1] => $_->[0] } @$relations; - _updreverse($self, \%old, \%new, $nrev->{iid}, $nrev->{rev}); + _updreverse($self, \%old, \%new, $nrev->{itemid}, $nrev->{rev}); } - return $self->resRedirect("/p$nrev->{iid}.$nrev->{rev}", 'post'); + return $self->resRedirect("/p$nrev->{itemid}.$nrev->{rev}", 'post'); } } @@ -329,10 +330,10 @@ sub _updreverse { # edit all related producers for my $i (keys %upd) { - my $r = $self->dbProducerGet(id => $i, what => 'relations')->[0]; + my $r = $self->dbProducerGetRev(id => $i, what => 'relations')->[0]; my @newrel = map $_->{id} != $pid ? [ $_->{relation}, $_->{id} ] : (), @{$r->{relations}}; push @newrel, [ $upd{$i}, $pid ] if $upd{$i}; - $self->dbItemEdit(p => $r->{cid}, + $self->dbItemEdit(p => $i, $r->{rev}, relations => \@newrel, editsum => "Reverse relation update caused by revision p$pid.$rev", uid => 1, diff --git a/lib/VNDB/Handler/Releases.pm b/lib/VNDB/Handler/Releases.pm index b06a62f3..30505562 100644 --- a/lib/VNDB/Handler/Releases.pm +++ b/lib/VNDB/Handler/Releases.pm @@ -20,9 +20,10 @@ TUWF::register( sub page { my($self, $rid, $rev) = @_; - my $r = $self->dbReleaseGet( + my $method = $rev ? 'dbReleaseGetRev' : 'dbReleaseGet'; + my $r = $self->$method( id => $rid, - what => 'vn extended producers platforms media'.($rev ? ' changes' : ''), + what => 'vn extended producers platforms media', $rev ? (rev => $rev) : (), )->[0]; return $self->resNotFound if !$r->{id}; @@ -32,7 +33,7 @@ sub page { return if $self->htmlHiddenMessage('r', $r); if($rev) { - my $prev = $rev && $rev > 1 && $self->dbReleaseGet( + my $prev = $rev && $rev > 1 && $self->dbReleaseGetRev( id => $rid, rev => $rev-1, what => 'vn extended producers platforms media changes' )->[0]; @@ -271,9 +272,9 @@ sub edit { $rid = 0; } - my $r = $rid && $self->dbReleaseGet(id => $rid, what => 'vn extended producers platforms media changes', $rev ? (rev => $rev) : ())->[0]; + my $r = $rid && $self->dbReleaseGetRev(id => $rid, what => 'vn extended producers platforms media', $rev ? (rev => $rev) : ())->[0]; return $self->resNotFound if $rid && !$r->{id}; - $rev = undef if !$r || $r->{cid} == $r->{latest}; + $rev = undef if !$r || $r->{lastrev}; my $v = $vid && $self->dbVNGet(id => $vid)->[0]; return $self->resNotFound if $vid && !$v->{id}; @@ -349,7 +350,7 @@ sub edit { } if(!$frm->{_err}) { - my $nrev = $self->dbItemEdit(r => !$copy && $rid ? $r->{cid} : undef, + my $nrev = $self->dbItemEdit(r => !$copy && $rid ? ($r->{id}, $r->{rev}) : (undef, undef), (map { $_ => $frm->{$_} } qw| type title original gtin catalog languages website released minage notes platforms resolution editsum patch voiced freeware doujin ani_story ani_ero ihid ilock|), vn => $new_vn, @@ -357,7 +358,7 @@ sub edit { media => $media, ); - return $self->resRedirect("/r$nrev->{iid}.$nrev->{rev}", 'post'); + return $self->resRedirect("/r$nrev->{itemid}.$nrev->{rev}", 'post'); } } @@ -388,8 +389,8 @@ sub _form { [ check => short => 'patch', name => mt('_redit_form_patch') ], [ check => short => 'freeware', name => mt('_redit_form_freeware') ], [ check => short => 'doujin', name => mt('_redit_form_doujin') ], - [ input => short => 'title', name => mt('_redit_form_title'), width => 300 ], - [ input => short => 'original', name => mt('_redit_form_original'), width => 300 ], + [ input => short => 'title', name => mt('_redit_form_title'), width => 450 ], + [ input => short => 'original', name => mt('_redit_form_original'), width => 450 ], [ static => content => mt '_redit_form_original_note' ], [ select => short => 'languages', name => mt('_redit_form_languages'), multi => 1, options => [ map [ $_, "$_ (".mt("_lang_$_").')' ], sort @{$self->{languages}} ] ], diff --git a/lib/VNDB/Handler/Staff.pm b/lib/VNDB/Handler/Staff.pm index 88e6d7c8..0f878ca6 100644 --- a/lib/VNDB/Handler/Staff.pm +++ b/lib/VNDB/Handler/Staff.pm @@ -19,9 +19,10 @@ TUWF::register( sub page { my($self, $id, $rev) = @_; - my $s = $self->dbStaffGet( + my $method = $rev ? 'dbStaffGetRev' : 'dbStaffGet'; + my $s = $self->$method( id => $id, - what => 'extended aliases roles'.($rev ? ' changes' : ''), + what => 'extended aliases roles', $rev ? ( rev => $rev ) : () )->[0]; return $self->resNotFound if !$s->{id}; @@ -31,7 +32,7 @@ sub page { return if $self->htmlHiddenMessage('s', $s); if($rev) { - my $prev = $rev && $rev > 1 && $self->dbStaffGet(id => $id, rev => $rev-1, what => 'changes extended aliases')->[0]; + my $prev = $rev && $rev > 1 && $self->dbStaffGetRev(id => $id, rev => $rev-1, what => 'extended aliases')->[0]; $self->htmlRevision('s', $prev, $s, [ name => diff => 1 ], [ original => diff => 1 ], @@ -179,18 +180,18 @@ sub _cast { sub edit { my($self, $sid, $rev) = @_; - my $s = $sid && $self->dbStaffGet(id => $sid, what => 'changes extended aliases', $rev ? (rev => $rev) : ())->[0]; + my $s = $sid && $self->dbStaffGetRev(id => $sid, what => 'extended aliases roles', $rev ? (rev => $rev) : ())->[0]; return $self->resNotFound if $sid && !$s->{id}; - $rev = undef if !$s || $s->{cid} == $s->{latest}; + $rev = undef if !$s || $s->{lastrev}; - return $self->htmlDenied if !$self->authCan('staffedit') + return $self->htmlDenied if !$self->authCan('edit') || $sid && (($s->{locked} || $s->{hidden}) && !$self->authCan('dbmod')); my %b4 = !$sid ? () : ( (map { $_ => $s->{$_} } qw|name original gender lang desc l_wp l_site l_twitter l_anidb ihid ilock|), primary => $s->{aid}, aliases => [ - map +{ aid => $_->{id}, name => $_->{name}, orig => $_->{original} }, + map +{ aid => $_->{aid}, name => $_->{name}, orig => $_->{original} }, sort { $a->{name} cmp $b->{name} || $a->{original} cmp $b->{original} } @{$s->{aliases}} ], ); @@ -220,20 +221,26 @@ sub edit { ); if(!$frm->{_err}) { - my %old_aliases = $sid ? ( map +($_->{id} => 1), @{$self->dbStaffAliasIds($sid)} ) : (); + my %old_aliases = $sid ? ( map +($_->{aid} => 1), @{$self->dbStaffAliasIds($sid)} ) : (); $frm->{primary} = 0 unless exists $old_aliases{$frm->{primary}}; # reset aid to zero for newly added aliases. $_->{aid} *= $old_aliases{$_->{aid}} ? 1 : 0 for(@{$frm->{aliases}}); + # Make sure no aliases that have been linked to a VN are removed. + my %new_aliases = map +($_, 1), grep $_, $frm->{primary}, map $_->{aid}, @{$frm->{aliases}}; + $frm->{_err} = [ 'usedalias' ] if grep !$new_aliases{$_->{aid}}, @{$s->{roles}}, @{$self->{cast}}; + } + + if(!$frm->{_err}) { $frm->{ihid} = $frm->{ihid} ?1:0; $frm->{ilock} = $frm->{ilock}?1:0; $frm->{aid} = $frm->{primary} if $sid; $frm->{desc} = $self->bbSubstLinks($frm->{desc}); return $self->resRedirect("/s$sid", 'post') if $sid && !form_compare(\%b4, $frm); - my $nrev = $self->dbItemEdit ('s' => $sid ? $s->{cid} : undef, %$frm); - return $self->resRedirect("/s$nrev->{iid}.$nrev->{rev}", 'post'); + my $nrev = $self->dbItemEdit(s => $sid ? ($s->{id}, $s->{rev}) : (undef, undef), %$frm); + return $self->resRedirect("/s$nrev->{itemid}.$nrev->{rev}", 'post'); } } @@ -384,4 +391,3 @@ sub staffxml { } 1; -__END__ diff --git a/lib/VNDB/Handler/Users.pm b/lib/VNDB/Handler/Users.pm index 491c573b..6b30db7b 100644 --- a/lib/VNDB/Handler/Users.pm +++ b/lib/VNDB/Handler/Users.pm @@ -33,6 +33,7 @@ sub userpage { return $self->resNotFound if !$u->{id}; my $votes = $u->{c_votes} && $self->dbVoteStats(uid => $uid); + my $list_visible = !$u->{hide_list} || ($self->authInfo->{id}||0) == $u->{id} || $self->authCan('usermod'); my $title = mt '_userpage_title', $u->{username}; $self->htmlHeader(title => $title, noindex => 1); @@ -70,7 +71,7 @@ sub userpage { Tr; td mt '_userpage_votes'; td; - if($u->{hide_list}) { + if(!$list_visible) { txt mt '_userpage_hidden'; } elsif($votes) { my($total, $count) = (0, 0); @@ -100,7 +101,7 @@ sub userpage { Tr; td mt '_userpage_list'; - td $u->{hide_list} ? mt('_userpage_hidden') : + td !$list_visible ? mt('_userpage_hidden') : mt('_userpage_list_item', $u->{releasecount}, $u->{vncount}); end; @@ -117,7 +118,7 @@ sub userpage { end 'table'; end 'div'; - if(!$u->{hide_list} && $votes) { + if($votes && $list_visible) { div class => 'mainbox'; h1 mt '_userpage_votestats'; $self->htmlVoteStats(u => $u, $votes); @@ -125,7 +126,7 @@ sub userpage { } if($u->{c_changes}) { - my $list = $self->dbRevisionGet(what => 'item user', uid => $uid, results => 5); + my $list = $self->dbRevisionGet(uid => $uid, results => 5); h1 class => 'boxtitle'; a href => "/u$uid/hist", mt '_userpage_changes'; end; diff --git a/lib/VNDB/Handler/VNEdit.pm b/lib/VNDB/Handler/VNEdit.pm index 8869d1a9..2d67a3df 100644 --- a/lib/VNDB/Handler/VNEdit.pm +++ b/lib/VNDB/Handler/VNEdit.pm @@ -64,8 +64,8 @@ sub addform { $self->htmlForm({ frm => $frm, action => '/v/add', continue => @$l ? 2 : 1 }, vn_add => [ mt('_vnedit_title_add'), - [ input => short => 'title', name => mt '_vnedit_frm_title' ], - [ input => short => 'original', name => mt '_vnedit_original' ], + [ input => short => 'title', name => mt('_vnedit_frm_title'), width => 450 ], + [ input => short => 'original', name => mt('_vnedit_original'), width => 450 ], [ static => content => mt '_vnedit_original_msg' ], [ textarea => short => 'alias', name => mt('_vnedit_alias'), rows => 4 ], [ static => content => mt '_vnedit_alias_msg' ], @@ -77,9 +77,9 @@ sub addform { sub edit { my($self, $vid, $rev, $nosubmit) = @_; - my $v = $vid && $self->dbVNGet(id => $vid, what => 'extended screenshots relations anime credits changes', $rev ? (rev => $rev) : ())->[0]; + my $v = $vid && $self->dbVNGetRev(id => $vid, what => 'extended screenshots relations anime credits changes', $rev ? (rev => $rev) : ())->[0]; return $self->resNotFound if $vid && !$v->{id}; - $rev = undef if !$vid || $v->{cid} == $v->{latest}; + $rev = undef if !$vid || $v->{lastrev}; return $self->htmlDenied if !$self->authCan('edit') || $vid && (($v->{locked} || $v->{hidden}) && !$self->authCan('dbmod')); @@ -176,7 +176,7 @@ sub edit { return $self->resRedirect("/v$vid", 'post') if $vid && !form_compare(\%b4, $frm); # perform the edit/add - my $nrev = $self->dbItemEdit(v => $vid ? $v->{cid} : undef, + my $nrev = $self->dbItemEdit(v => $vid ? ($v->{id}, $v->{rev}) : (undef, undef), (map { $_ => $frm->{$_} } qw|title original image alias desc length l_wp l_encubed l_renai editsum img_nsfw ihid ilock credits seiyuu screenshots|), anime => [ keys %$anime ], relations => $relations, @@ -186,10 +186,10 @@ sub edit { if(!$vid && $#$relations >= 0 || $vid && $frm->{vnrelations} ne $b4{vnrelations}) { my %old = $vid ? (map +($_->{id} => [ $_->{relation}, $_->{official} ]), @{$v->{relations}}) : (); my %new = map +($_->[1] => [ $_->[0], $_->[2] ]), @$relations; - _updreverse($self, \%old, \%new, $nrev->{iid}, $nrev->{rev}); + _updreverse($self, \%old, \%new, $nrev->{itemid}, $nrev->{rev}); } - return $self->resRedirect("/v$nrev->{iid}.$nrev->{rev}", 'post'); + return $self->resRedirect("/v$nrev->{itemid}.$nrev->{rev}", 'post'); } } @@ -250,8 +250,8 @@ sub _form { my $import = @$chars ? $self->dbVNImportSeiyuu($v->{id}, [ map $_->{id}, @$chars ]) : []; $self->htmlForm({ frm => $frm, action => $v ? "/v$v->{id}/edit" : '/v/new', editsum => 1, upload => 1 }, vn_geninfo => [ mt('_vnedit_geninfo'), - [ input => short => 'title', name => mt '_vnedit_frm_title' ], - [ input => short => 'original', name => mt '_vnedit_original' ], + [ input => short => 'title', name => mt('_vnedit_frm_title'), width => 450 ], + [ input => short => 'original', name => mt('_vnedit_original'), width => 450 ], [ static => content => mt '_vnedit_original_msg' ], [ textarea => short => 'alias', name => mt('_vnedit_alias'), rows => 4 ], [ static => content => mt '_vnedit_alias_msg' ], @@ -422,7 +422,7 @@ sub _form { # Update reverse relations and regenerate relation graph # Arguments: %old. %new, vid, rev -# %old,%new -> { vid2 => [ relation, official ], .. } +# %old,%new -> { vid => [ relation, official ], .. } # from the perspective of vid # rev is of the related edit sub _updreverse { @@ -441,10 +441,10 @@ sub _updreverse { # edit all related VNs for my $i (keys %upd) { - my $r = $self->dbVNGet(id => $i, what => 'relations')->[0]; + my $r = $self->dbVNGetRev(id => $i, what => 'relations')->[0]; my @newrel = map $_->{id} != $vid ? [ $_->{relation}, $_->{id}, $_->{official} ] : (), @{$r->{relations}}; push @newrel, [ $upd{$i}[0], $vid, $upd{$i}[1] ] if $upd{$i}; - $self->dbItemEdit(v => $r->{cid}, + $self->dbItemEdit(v => $r->{id}, $r->{rev}, relations => \@newrel, editsum => "Reverse relation update caused by revision v$vid.$rev", uid => 1, # Multi diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm index 9c85f922..02d14139 100644 --- a/lib/VNDB/Handler/VNPage.pm +++ b/lib/VNDB/Handler/VNPage.pm @@ -187,8 +187,7 @@ my @rel_cols = ( sub releases { my($self, $vid) = @_; - my $v = $self->dbVNGet( - id => $vid)->[0]; + my $v = $self->dbVNGet(id => $vid)->[0]; return $self->resNotFound if !$v->{id}; my $title = mt('_vnpage_rel_title', $v->{title}); @@ -334,9 +333,10 @@ sub page { my $staff = $rev && $rev eq 'staff'; $rev = undef if $char || $staff; - my $v = $self->dbVNGet( + my $method = $rev ? 'dbVNGetRev' : 'dbVNGet'; + my $v = $self->$method( id => $vid, - what => 'extended anime relations screenshots rating ranking'.($staff || $rev ? ' credits' : '').($rev ? ' changes' : ''), + what => 'extended anime relations screenshots rating ranking'.($staff || $rev ? ' credits' : ''), $rev ? (rev => $rev) : (), )->[0]; return $self->resNotFound if !$v->{id}; @@ -508,8 +508,8 @@ sub _revision { my($self, $v, $rev) = @_; return if !$rev; - my $prev = $rev && $rev > 1 && $self->dbVNGet( - id => $v->{id}, rev => $rev-1, what => 'extended anime relations screenshots credits changes' + my $prev = $rev && $rev > 1 && $self->dbVNGetRev( + id => $v->{id}, rev => $rev-1, what => 'extended anime relations screenshots credits' )->[0]; $self->htmlRevision('v', $prev, $v, @@ -554,7 +554,7 @@ sub _revision { return @r ? @r : (mt '_revision_empty'); }], [ screenshots => join => '<br />', split => sub { - my @r = map sprintf('[%s] <a href="%s" rel="iv:%dx%d">%d</a> (%s)', + my @r = map sprintf('[%s] <a href="%s" data-iv="%dx%d">%d</a> (%s)', $_->{rid} ? qq|<a href="/r$_->{rid}">r$_->{rid}</a>| : 'no release', imgurl(sf => $_->{id}), $_->{width}, $_->{height}, $_->{id}, mt($_->{nsfw} ? '_vndiff_nsfw_notsafe' : '_vndiff_nsfw_safe') @@ -668,7 +668,7 @@ sub _anime { } txt '] '; end; - acronym title => $_->{title_kanji}||$_->{title_romaji}, shorten $_->{title_romaji}, 50; + abbr title => $_->{title_kanji}||$_->{title_romaji}, shorten $_->{title_romaji}, 50; b ' ('.(defined $_->{type} ? mt("_animetype_$_->{type}").', ' : '').$_->{year}.')'; br; } @@ -748,7 +748,7 @@ sub _affiliate_links { || ($f->{default_version} && $f->{default_version}->($self, $link, $rel)) || $version; txt " at $f->{name}"; - acronym class => 'pricenote', title => + abbr class => 'pricenote', title => $link->{lastfetch} ? sprintf('Last updated: %s.', $en->age($link->{lastfetch})) : '', " for $link->{price}" if $link->{price}; txt ' ยป'; @@ -859,7 +859,7 @@ sub _screenshots { my($w, $h) = imgsize($_->{width}, $_->{height}, @{$self->{scr_size}}); a href => imgurl(sf => $_->{id}), class => sprintf('scrlnk%s%s', $_->{nsfw} ? ' nsfw':'', $_->{nsfw}&&!$self->authPref('show_nsfw')?' hidden':''), - rel => "iv:$_->{width}x$_->{height}:scr"; + 'data-iv' => "$_->{width}x$_->{height}:scr"; img src => imgurl(st => $_->{id}), width => $w, height => $h, alt => mt '_vnpage_scr_num', $_->{id}; end; diff --git a/lib/VNDB/Util/Auth.pm b/lib/VNDB/Util/Auth.pm index a1c6cf08..0c3b5b73 100644 --- a/lib/VNDB/Util/Auth.pm +++ b/lib/VNDB/Util/Auth.pm @@ -5,7 +5,7 @@ package VNDB::Util::Auth; use strict; use warnings; use Exporter 'import'; -use Digest::SHA qw|sha1 sha1_hex sha256|; +use Digest::SHA qw|sha1 sha1_hex|; use Crypt::URandom 'urandom'; use Crypt::ScryptKDF 'scrypt_raw'; use Encode 'encode_utf8'; @@ -112,16 +112,7 @@ sub _authCheck { my $d = $self->dbUserGet(username => $user, what => 'extended notifycount')->[0]; return 0 if !$d->{id}; - # Old-style hashes - if(length $d->{passwd} == 41) { - return 0 if _authPreparePassSha256($self, $pass, substr $d->{passwd}, 0, 9) ne $d->{passwd}; - $self->{_auth} = $d; - # Update database with new hash format, now that we have the plain text password - $self->dbUserEdit($d->{id}, passwd => $self->authPreparePass($pass)); - return 1; - } - - # New scrypt hashes + # scrypt format if(length $d->{passwd} == 46) { my($N, $r, $p, $salt) = unpack 'NCCa8', $d->{passwd}; return 0 if $self->authPreparePass($pass, $salt, $N, $r, $p) ne $d->{passwd}; @@ -144,16 +135,6 @@ sub authPreparePass { } -# Same as authPreparePass, but for the old sha256 hash. -# Arguments: pass, optionally salt -# Returns: encrypted password (as a binary string) -sub _authPreparePassSha256 { - my($self, $pass, $salt) = @_; - $salt ||= encode_utf8(randomascii(9)); - return $salt.sha256($self->{global_salt} . encode_utf8($pass) . $salt); -} - - # Generates a random token that can be used to reset the password. # Returns: token (hex string), token-encrypted (binary string) sub authPrepareReset { diff --git a/lib/VNDB/Util/BrowseHTML.pm b/lib/VNDB/Util/BrowseHTML.pm index b54ad5d9..d57c0546 100644 --- a/lib/VNDB/Util/BrowseHTML.pm +++ b/lib/VNDB/Util/BrowseHTML.pm @@ -137,11 +137,11 @@ sub htmlBrowseHist { ], row => sub { my($s, $n, $i) = @_; - my $revurl = "/$i->{type}$i->{iid}.$i->{rev}"; + my $revurl = "/$i->{type}$i->{itemid}.$i->{rev}"; Tr; td class => 'tc1_1'; - a href => $revurl, "$i->{type}$i->{iid}"; + a href => $revurl, "$i->{type}$i->{itemid}"; end; td class => 'tc1_2'; a href => $revurl, ".$i->{rev}"; diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm index 7b3fa673..b576e4ad 100644 --- a/lib/VNDB/Util/CommonHTML.pm +++ b/lib/VNDB/Util/CommonHTML.pm @@ -133,12 +133,12 @@ sub htmlHiddenMessage { my($self, $type, $obj) = @_; return 0 if !$obj->{hidden}; my $board = $type =~ /[cs]/ ? 'db' : $type eq 'r' ? 'v'.$obj->{vn}[0]{vid} : $type.$obj->{id}; - # fetch edit summary (not present in $obj because the changes aren't fetched) - my $editsum = $type eq 'v' ? $self->dbVNGet(id => $obj->{id}, what => 'changes')->[0]{comments} - : $type eq 'r' ? $self->dbReleaseGet(id => $obj->{id}, what => 'changes')->[0]{comments} - : $type eq 'c' ? $self->dbCharGet(id => $obj->{id}, what => 'changes')->[0]{comments} - : $type eq 's' ? $self->dbStaffGet(id => $obj->{id}, what => 'changes')->[0]{comments} - : $self->dbProducerGet(id => $obj->{id}, what => 'changes')->[0]{comments}; + # fetch edit summary (not present in $obj, requires the db*GetRev() methods) + my $editsum = $type eq 'v' ? $self->dbVNGetRev(id => $obj->{id})->[0]{comments} + : $type eq 'r' ? $self->dbReleaseGetRev(id => $obj->{id})->[0]{comments} + : $type eq 'c' ? $self->dbCharGetRev(id => $obj->{id})->[0]{comments} + : $type eq 's' ? $self->dbStaffGetRev(id => $obj->{id})->[0]{comments} + : $self->dbProducerGetRev(id => $obj->{id})->[0]{comments}; div class => 'mainbox'; h1 $obj->{title}||$obj->{name}; div class => 'warning'; @@ -183,7 +183,7 @@ sub htmlRevision { a class => 'prev', href => sprintf('/%s%d.%d', $type, $new->{id}, $new->{rev}-1), '<- '.mt '_revision_previous' if $new->{rev} > 1; a class => 'next', href => sprintf('/%s%d.%d', $type, $new->{id}, $new->{rev}+1), mt('_revision_next').' ->' - if $new->{cid} != $new->{latest}; + if !$new->{lastrev}; p class => 'center'; a href => "/$type$new->{id}", "$type$new->{id}"; end; @@ -204,12 +204,12 @@ sub htmlRevision { table class => 'stripe'; thead; Tr; - td; lit ' '; end; + td; lit ' '; end; td; revheader($self, $type, $old); end; td; revheader($self, $type, $new); end; end; Tr; - td; lit ' '; end; + td; lit ' '; end; td colspan => 2; b mt '_revision_edit_summary', $new->{rev}; br; br; @@ -310,7 +310,7 @@ sub htmlEditMessage { } end; end; - if($obj && $obj->{latest} != $obj->{cid}) { + if($obj && !$obj->{lastrev}) { div class => 'warning'; h2 mt '_editmsg_revert_title'; p mt '_editmsg_revert_msg', $num; @@ -329,11 +329,7 @@ sub htmlItemMessage { if($obj->{locked}) { p class => 'locked', mt '_itemmsg_locked'; - } elsif(!$self->authInfo->{id}) { - p class => 'locked'; - lit mt '_itemmsg_login', '/u/login'; - end; - } elsif(!$self->authCan('edit')) { + } elsif($self->authInfo->{id} && !$self->authCan('edit')) { p class => 'locked', mt '_itemmsg_denied'; } } @@ -439,6 +435,7 @@ sub htmlSearchBox { sub htmlRGHeader { my($self, $title, $type, $obj) = @_; + # This used to be a good test for inline SVG support, but I'm not sure it is nowadays. if(($self->reqHeader('Accept')||'') !~ /application\/xhtml\+xml/) { $self->htmlHeader(title => $title); $self->htmlMainTabs($type, $obj, 'rg'); @@ -452,8 +449,7 @@ sub htmlRGHeader { $self->htmlFooter; return 1; } - $self->resHeader('Content-Type' => 'application/xhtml+xml; charset=UTF-8'); - $self->htmlHeader(title => $title, svg => 1); + $self->htmlHeader(title => $title); $self->htmlMainTabs($type, $obj, 'rg'); return 0; } diff --git a/lib/VNDB/Util/FormHTML.pm b/lib/VNDB/Util/FormHTML.pm index 22eeb4c5..e1f3bdfc 100644 --- a/lib/VNDB/Util/FormHTML.pm +++ b/lib/VNDB/Util/FormHTML.pm @@ -99,7 +99,7 @@ sub htmlFormPart { if(/check/) { Tr class => 'newfield'; td class => 'label'; - lit ' '; + lit ' '; end; td class => 'field'; input type => 'checkbox', name => $o{short}, id => $o{short}, tabindex => 10, @@ -122,7 +122,7 @@ sub htmlFormPart { } elsif($o{label}) { txt $o{label}; } else { - lit ' '; + lit ' '; } end; } diff --git a/lib/VNDB/Util/LayoutHTML.pm b/lib/VNDB/Util/LayoutHTML.pm index e041960a..f2344fc5 100644 --- a/lib/VNDB/Util/LayoutHTML.pm +++ b/lib/VNDB/Util/LayoutHTML.pm @@ -17,11 +17,8 @@ sub htmlHeader { # %options->{ title, noindex, search, feeds, svg } $skin = $self->{skin_default} if !$self->{skins}{$skin} || !-d "$VNDB::ROOT/static/s/$skin"; # heading - html lang => $self->{l10n}->language_tag(), $o{svg} ? ( - doctype => 'xhtml-math-svg', - 'xmlns:svg' => 'http://www.w3.org/2000/svg', - 'xmlns:xlink' => 'http://www.w3.org/1999/xlink' - ) : (); + lit '<!DOCTYPE HTML>'; + tag 'html', lang => $self->{l10n}->language_tag(); head; title $o{title}; Link rel => 'shortcut icon', href => '/favicon.ico', type => 'image/x-icon'; @@ -79,7 +76,7 @@ sub _menu { form action => '/v/all', method => 'get', id => 'search'; fieldset; legend 'Search'; - input type => 'text', class => 'text', id => 'sq', name => 'sq', value => $o{search}||mt('_menu_emptysearch'); + input type => 'text', class => 'text', id => 'sq', name => 'sq', value => $o{search}||'', placeholder => mt('_menu_emptysearch'); input type => 'submit', class => 'submit', value => 'Search'; end; end; @@ -104,9 +101,7 @@ sub _menu { if($self->authCan('edit')) { a href => '/v/add', mt '_menu_addvn'; br; a href => '/p/new', mt '_menu_addproducer'; br; - if($self->authCan('staffedit')) { - a href => '/s/new', mt '_menu_addstaff'; br; - } + a href => '/s/new', mt '_menu_addstaff'; br; a href => '/c/new', mt '_menu_addcharacter'; br; } br; |