diff options
-rw-r--r-- | lib/ChangeLog | 1 | ||||
-rw-r--r-- | lib/VNDB/Util/DB.pm | 592 | ||||
-rw-r--r-- | lib/VNDB/Util/Tools.pm | 1 |
3 files changed, 282 insertions, 312 deletions
diff --git a/lib/ChangeLog b/lib/ChangeLog index 3d15c225..7b97bb63 100644 --- a/lib/ChangeLog +++ b/lib/ChangeLog @@ -13,6 +13,7 @@ TODO: - Fixed display of wrong ID in the screenshot diff - Fixed bug with the infinite thumbnail generation message when the server doesn't respond within one second. + - Rewrote VNDB::Util::DB::sqlprint to use server-side prepared statements 1.21 - 2008-08-16 (r90) - Added !vn and !uptime commands to Multi::IRC diff --git a/lib/VNDB/Util/DB.pm b/lib/VNDB/Util/DB.pm index d25a1e5b..85a31520 100644 --- a/lib/VNDB/Util/DB.pm +++ b/lib/VNDB/Util/DB.pm @@ -5,7 +5,6 @@ use strict; use warnings; use DBI; use Exporter 'import'; -use Storable 'nfreeze', 'thaw'; use vars ('$VERSION', '@EXPORT'); $VERSION = $VNDB::VERSION; @@ -22,7 +21,7 @@ $VERSION = $VNDB::VERSION; DBGetRelease DBAddRelease DBEditRelease DBHideRelease DBGetProducer DBGetProducerVN DBAddProducer DBEditProducer DBHideProducer DBGetThreads DBGetPosts DBAddPost DBEditPost DBEditThread DBAddThread - DBExec DBRow DBAll DBLastId + DBExec DBRow DBAll |; @@ -139,10 +138,10 @@ sub DBLanguageCount { sub DBTableCount { # table (users, producers, vn, releases, votes) return $_[0]->DBRow(q| SELECT COUNT(*) as cnt - FROM %s - %s|, + FROM !s + !W|, $_[1], - $_[1] =~ /producers|vn|releases/ ? 'WHERE hidden = FALSE' : '', + $_[1] =~ /producers|vn|releases/ ? { 'hidden = ?' => 0 } : {}, )->{cnt} - ($_[1] eq 'users' ? 1 : 0); } @@ -160,28 +159,28 @@ sub DBGetHist { # %options->{ type, id, cid, caused, next, page, results, ip, ed my %where = ( $o{cid} ? ( - 'c.id IN(!l)' => $o{cid} ) : (), + 'c.id IN(!l)' => [$o{cid}] ) : (), $o{type} eq 'u' ? ( - 'c.requester = %d' => $o{id} ) : (), + 'c.requester = ?' => $o{id} ) : (), - $o{type} eq 'v' && !$o{releases} ? ( 'c.type = 0' => 1, - $o{id} ? ( 'vr.vid = %d' => $o{id} ) : () ) : (), + $o{type} eq 'v' && !$o{releases} ? ( 'c.type = ?' => 0, + $o{id} ? ( 'vr.vid = ?' => $o{id} ) : () ) : (), $o{type} eq 'v' && $o{releases} ? ( - '((c.type = 0 AND vr.vid = %d) OR (c.type = 1 AND rv.vid = %1$d))' => $o{id} ) : (), + '((c.type = ? AND vr.vid = ?) OR (c.type = ? AND rv.vid = ?))' => [0,$o{id},1,$o{id}] ) : (), - $o{type} eq 'r' ? ( 'c.type = 1' => 1, - $o{id} ? ( 'rr.rid = %d' => $o{id} ) : () ) : (), - $o{type} eq 'p' ? ( 'c.type = 2' => 1, - $o{id} ? ( 'pr.pid = %d' => $o{id} ) : () ) : (), + $o{type} eq 'r' ? ( 'c.type = ?' => 1, + $o{id} ? ( 'rr.rid = ?' => $o{id} ) : () ) : (), + $o{type} eq 'p' ? ( 'c.type = ?' => 2, + $o{id} ? ( 'pr.pid = ?' => $o{id} ) : () ) : (), $o{caused} ? ( - 'c.causedby = %d' => $o{caused} ) : (), + 'c.causedby = ?' => $o{caused} ) : (), $o{ip} ? ( - 'c.ip = !s' => $o{ip} ) : (), + 'c.ip = ?' => $o{ip} ) : (), defined $o{edits} && !$o{edits} ? ( - 'c.rev = 1' => 1 ) : (), + 'c.rev = ?' => 1 ) : (), $o{edits} ? ( - 'c.rev > 1' => 1 ) : (), + 'c.rev > ?' => 1 ) : (), # get rid of 'hidden' items !$o{showhid} ? ( @@ -191,8 +190,6 @@ sub DBGetHist { # %options->{ type, id, cid, caused, next, page, results, ip, ed ) : (), ); - my $where = keys %where ? 'WHERE !W' : ''; - my $select = 'c.id, c.type, c.added, c.requester, c.comments, c.rev, c.causedby'; $select .= ', u.username' if $o{what} =~ /user/; $select .= ', COALESCE(vr.vid, rr.rid, pr.pid) AS iid' if $o{what} =~ /iid/; @@ -216,10 +213,10 @@ sub DBGetHist { # %options->{ type, id, cid, caused, next, page, results, ip, ed SELECT $select FROM changes c $join - $where - ORDER BY c.id %s - LIMIT %d OFFSET %d|, - $where ? \%where : (), + !W + ORDER BY c.id !s + LIMIT ? OFFSET ?|, + \%where, $o{next} ? 'ASC' : 'DESC', $o{results}+(wantarray?1:0), $o{results}*($o{page}-1) ); @@ -233,15 +230,15 @@ sub DBGetHist { # %options->{ type, id, cid, caused, next, page, results, ip, ed sub DBLockItem { # table, id, locked my($s, $tbl, $id, $l) = @_; $s->DBExec(q| - UPDATE %s - SET locked = !b - WHERE id = %d|, + UPDATE !s + SET locked = ? + WHERE id = ?|, $tbl, $l, $id); } sub DBIncId { # sequence (this is a rather low-level function... aww heck...) - return $_[0]->DBRow(q|SELECT nextval(!s) AS ni|, $_[1])->{ni}; + return $_[0]->DBRow(q|SELECT nextval(?) AS ni|, $_[1])->{ni}; } @@ -272,38 +269,38 @@ sub DBGetUser { # %options->{ username mail passwd order firstchar uid results p ); my %where = ( + 'id > 0' => 1, $o{username} ? ( - 'username = !s' => $o{username} ) : (), + 'username = ?' => $o{username} ) : (), $o{mail} ? ( - 'mail = !s' => $o{mail} ) : (), + 'mail = ?' => $o{mail} ) : (), $o{passwd} ? ( - 'passwd = decode(!s, \'hex\')' => $o{passwd} ) : (), + 'passwd = decode(?, \'hex\')' => $o{passwd} ) : (), $o{firstchar} ? ( - 'SUBSTRING(username from 1 for 1) = !s' => $o{firstchar} ) : (), + 'SUBSTRING(username from 1 for 1) = ?' => $o{firstchar} ) : (), !$o{firstchar} && defined $o{firstchar} ? ( 'ASCII(username) < 97 OR ASCII(username) > 122' => 1 ) : (), $o{uid} ? ( - 'id = %d' => $o{uid} ) : (), + 'id = ?' => $o{uid} ) : (), !$o{uid} && !$o{username} ? ( 'id > 0' => 1 ) : (), ); - my $where = keys %where ? 'AND !W' : ''; - my $r = $s->DBAll(qq| + my $r = $s->DBAll(q| SELECT * FROM users u - WHERE id > 0 $where - ORDER BY %s - LIMIT %d OFFSET %d|, - $where ? \%where : (), + !W + ORDER BY !s + LIMIT ? OFFSET ?|, + \%where, $o{order}, $o{results}+(wantarray?1:0), $o{results}*($o{page}-1) ); + # XXX: easy to cache, good performance win if($o{what} =~ /list/ && $#$r >= 0) { my %r = map { $r->[$_]{votes} = 0; - $r->[$_]{vnlist} = 0; $r->[$_]{changes} = 0; ($r->[$_]{id}, $_) } 0..$#$r; @@ -336,7 +333,7 @@ sub DBAddUser { # username, passwd, mail, rank return $_[0]->DBExec(q| INSERT INTO users (username, passwd, mail, rank, registered) - VALUES (!s, decode(!s, 'hex'), !s, %d, %d)|, + VALUES (?, decode(?, 'hex'), ?, ?, ?)|, lc($_[1]), $_[2], $_[3], $_[4], time ); } @@ -348,18 +345,16 @@ sub DBUpdateUser { # uid, %options->{ columns in users table } my %opt = @_; my %h; - defined $opt{$_} && ($h{$_.' = !s'} = $opt{$_}) - for (qw| username mail |); - defined $opt{$_} && ($h{$_.' = %d'} = $opt{$_}) - for (qw| rank flags |); - $h{'passwd = decode(!s, \'hex\')'} = $opt{passwd} + defined $opt{$_} && ($h{$_.' = ?'} = $opt{$_}) + for (qw| username mail rank flags |); + $h{'passwd = decode(?, \'hex\')'} = $opt{passwd} if defined $opt{passwd}; return 0 if scalar keys %h <= 0; return $s->DBExec(q| UPDATE users - SET !H - WHERE id = %d|, + !H + WHERE id = ?|, \%h, $user); } @@ -367,13 +362,13 @@ sub DBUpdateUser { # uid, %options->{ columns in users table } sub DBDelUser { # uid my($s, $id) = @_; $s->DBExec($_, $id) for ( - q|DELETE FROM vnlists WHERE uid = %d|, - q|DELETE FROM rlists WHERE uid = %d|, - q|DELETE FROM wlists WHERE uid = %d|, - q|DELETE FROM votes WHERE uid = %d|, - q|UPDATE changes SET requester = 0 WHERE requester = %d|, - q|UPDATE threads_posts SET uid = 0 WHERE uid = %d|, - q|DELETE FROM users WHERE id = %d| + q|DELETE FROM vnlists WHERE uid = ?|, + q|DELETE FROM rlists WHERE uid = ?|, + q|DELETE FROM wlists WHERE uid = ?|, + q|DELETE FROM votes WHERE uid = ?|, + q|UPDATE changes SET requester = 0 WHERE requester = ?|, + q|UPDATE threads_posts SET uid = 0 WHERE uid = ?|, + q|DELETE FROM users WHERE id = ?| ); } @@ -394,22 +389,21 @@ sub DBGetVotes { # %options->{ uid vid hide order results page } $o{page} ||= 1; my %where = ( - $o{uid} ? ( 'n.uid = %d' => $o{uid} ) : (), - $o{vid} ? ( 'n.vid = %d' => $o{vid} ) : (), - $o{hide} ? ( 'u.flags & %d = %1$d' => $VNDB::UFLAGS->{list} ) : (), + $o{uid} ? ( 'n.uid = ?' => $o{uid} ) : (), + $o{vid} ? ( 'n.vid = ?' => $o{vid} ) : (), + $o{hide} ? ( 'u.flags & ? = ?' => [ $VNDB::UFLAGS->{list}, $VNDB::UFLAGS->{list} ] ) : (), ); - my $where = scalar keys %where ? 'WHERE !W' : ''; - my $r = $s->DBAll(qq| + my $r = $s->DBAll(q| SELECT n.vid, vr.title, n.vote, n.date, n.uid, u.username FROM votes n JOIN vn v ON v.id = n.vid JOIN vn_rev vr ON vr.id = v.latest JOIN users u ON u.id = n.uid - $where - ORDER BY %s - LIMIT %d OFFSET %d|, - $where ? \%where : (), + !W + ORDER BY !s + LIMIT ? OFFSET ?|, + \%where, $o{order}, $o{results}+(wantarray?1:0), $o{results}*($o{page}-1) ); @@ -422,13 +416,13 @@ sub DBGetVotes { # %options->{ uid vid hide order results page } sub DBVoteStats { # uid|vid => id my($s, $col, $id) = @_; - my $r = [ qw| 0 0 0 0 0 0 0 0 0 0 | ], - my $where = $col ? 'WHERE '.$col.' = '.$id : ''; - $r->[$_->{vote}-1] = $_->{votes} for (@{$s->DBAll(qq| + my $r = [ qw| 0 0 0 0 0 0 0 0 0 0 | ]; + $r->[$_->{vote}-1] = $_->{votes} for (@{$s->DBAll(q| SELECT vote, COUNT(vote) as votes FROM votes - $where + !W GROUP BY vote|, + $col ? { '!s = ?' => [ $col, $id ] } : {}, )}); return $r; } @@ -437,26 +431,27 @@ sub DBVoteStats { # uid|vid => id sub DBAddVote { # vid, uid, vote $_[0]->DBExec(q| UPDATE votes - SET vote = %d - WHERE vid = %d - AND uid = %d|, + SET vote = ? + WHERE vid = ? + AND uid = ?|, $_[3], $_[1], $_[2] ) || $_[0]->DBExec(q| INSERT INTO votes (vid, uid, vote, date) - VALUES (%d, %d, %d, %d)|, - $_[1], $_[2], $_[3], time + VALUES (!l)|, + [ @_[1..3], time ] ); - # XXX: performance improvement: let a cron job handle this } sub DBDelVote { # uid, vid # uid = 0 to delete all - my $uid = $_[1] ? 'uid = '.$_[1].' AND' : ''; $_[0]->DBExec(q| DELETE FROM votes - WHERE %s vid = %d|, - $uid, $_[2]); + !W|, + { 'vid = ?' => $_[2], + $_[1] ? ('uid = ?' => $_[1]) : () + } + ); } @@ -476,12 +471,12 @@ sub DBGetVNList { # %options->{ uid vid hide order results page status } my %where = ( $o{uid} ? ( - 'l.uid = %d' => $o{uid} ) : (), + 'l.uid = ?' => $o{uid} ) : (), $o{vid} ? ( - 'l.vid = %d' => $o{vid} ) : (), + 'l.vid = ?' => $o{vid} ) : (), defined $o{status} ? ( - 'l.status = %d' => $o{status} ) : (), - $o{hide} ? ( 'u.flags & %d = %1$d' => $VNDB::UFLAGS->{list} ) : (), + 'l.status = ?' => $o{status} ) : (), + $o{hide} ? ( 'u.flags & ? = ?' => [ $VNDB::UFLAGS->{list}, $VNDB::UFLAGS->{list} ] ) : (), ); return wantarray ? ([], 0) : [] if !keys %where; @@ -492,9 +487,9 @@ sub DBGetVNList { # %options->{ uid vid hide order results page status } JOIN vn v ON l.vid = v.id JOIN vn_rev vr ON vr.id = v.latest JOIN users u ON l.uid = u.id - WHERE !W - ORDER BY %s - LIMIT %d OFFSET %d|, + !W + ORDER BY !s + LIMIT ? OFFSET ?|, \%where, $o{order}, $o{results}+(wantarray?1:0), $o{results}*($o{page}-1) @@ -508,11 +503,12 @@ sub DBGetVNList { # %options->{ uid vid hide order results page status } sub DBDelVNList { # uid, @vid # uid = 0 to delete all my($s, $uid, @vid) = @_; - $uid = $uid ? 'uid = '.$uid.' AND ' : ''; $s->DBExec(q| DELETE FROM vnlists - WHERE %s vid IN(!l)|, - $uid, \@vid + !W|, + { 'vid IN (!l)' => [\@vid], + $uid ? ('uid = ?' => $uid) : () + } ); } @@ -534,8 +530,8 @@ sub DBGetWishList { # %options->{ uid vid what order page results } $o{what} ||= ''; my %where = ( - 'wl.uid = %d' => $o{uid}, - $o{vid} ? ( 'wl.vid = %d' => $o{vid} ) : (), + 'wl.uid = ?' => $o{uid}, + $o{vid} ? ( 'wl.vid = ?' => $o{vid} ) : (), ); my $select = 'wl.vid, wl.wstat, wl.added'; @@ -550,9 +546,9 @@ sub DBGetWishList { # %options->{ uid vid what order page results } SELECT $select FROM wlists wl @join - WHERE !W - ORDER BY %s - LIMIT %d OFFSET %d|, + !W + ORDER BY !s + LIMIT ? OFFSET ?|, \%where, $o{order}, $o{results}+(wantarray?1:0), $o{results}*($o{page}-1) @@ -566,18 +562,18 @@ sub DBGetWishList { # %options->{ uid vid what order page results } sub DBEditWishList { # %options->{ uid vid wstat } my($s, %o) = @_; - $s->DBExec(q|UPDATE wlists SET wstat = %d WHERE uid = %d AND vid IN(!l)|, + $s->DBExec(q|UPDATE wlists SET wstat = ? WHERE uid = ? AND vid IN(!l)|, $o{wstat}, $o{uid}, ref($o{vid}) eq 'ARRAY' ? $o{vid} : [ $o{vid} ]) || $s->DBExec(q|INSERT INTO wlists (uid, vid, wstat) - VALUES(%d, %d, %d)|, - @o{qw| uid vid wstat |}); + VALUES(!l)|, + [@o{qw| uid vid wstat |}]); } sub DBDelWishList { # uid, vids my($s, $uid, $vid) = @_; - $s->DBExec(q|DELETE FROM wlists WHERE uid = %d AND vid IN(!l)|, $uid, $vid); + $s->DBExec(q|DELETE FROM wlists WHERE uid = ? AND vid IN(!l)|, $uid, $vid); } @@ -594,15 +590,15 @@ sub DBGetRList { # %options->{ uid rids } my($s, %o) = @_; my %where = ( - 'uid = %d' => $o{uid}, + 'uid = ?' => $o{uid}, $o{rids} ? ( - 'rid IN(!l)' => $o{rids} ) : (), + 'rid IN(!l)' => [$o{rids}] ) : (), ); return $s->DBAll(q| SELECT uid, rid, rstat, vstat FROM rlists - WHERE !W|, + !W|, \%where); } @@ -621,26 +617,26 @@ sub DBGetRLists { # %options->{ uid order char rstat vstat voted page results } FROM rlists irl JOIN releases ir ON ir.id = irl.rid JOIN releases_vn irv ON irv.rid = ir.latest - WHERE !W + !W )| if !$o{voted}; $where = '('.$where.') AND LOWER(SUBSTR(vr.title, 1, 1)) = \''.$o{char}.'\'' if $o{char}; $where = '('.$where.') AND (ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' if defined $o{char} && !$o{char}; # WHERE clause for the rlists subquery my %where = ( - 'uid = %d' => $o{uid}, - defined $o{rstat} ? ( 'rstat = %d' => $o{rstat} ) : (), - defined $o{vstat} ? ( 'vstat = %d' => $o{vstat} ) : (), + 'uid = ?' => $o{uid}, + defined $o{rstat} ? ( 'rstat = ?' => $o{rstat} ) : (), + defined $o{vstat} ? ( 'vstat = ?' => $o{vstat} ) : (), ); my $r = $s->DBAll(qq| SELECT vr.vid, vr.title, v.c_released, v.c_languages, v.c_platforms, COALESCE(vo.vote, 0) AS vote FROM vn v JOIN vn_rev vr ON vr.id = v.latest - %s JOIN votes vo ON vo.vid = v.id AND vo.uid = %d + !s JOIN votes vo ON vo.vid = v.id AND vo.uid = ? WHERE $where - ORDER BY %s - LIMIT %d OFFSET %d|, + ORDER BY !s + LIMIT ? OFFSET ?|, $o{voted} ? '' : 'LEFT', $o{uid}, # JOIN if we only want votes, LEFT JOIN if we also want rlist items $o{voted} ? () : \%where, $o{order}, @@ -656,7 +652,7 @@ sub DBGetRLists { # %options->{ uid order char rstat vstat voted page results } 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 - WHERE rl.uid = %d + WHERE rl.uid = ? AND rv.vid IN(!l) ORDER BY rr.released ASC|, $o{uid}, [ keys %vns ] @@ -674,24 +670,24 @@ sub DBEditRList { # %options->{ uid rid rstat vstat } # rid can only be a arrayref with UPDATE my($s, %o) = @_; my %s = ( - defined $o{rstat} ? ( 'rstat = %d', $o{rstat} ) : (), - defined $o{vstat} ? ( 'vstat = %d', $o{vstat} ) : (), + defined $o{rstat} ? ( 'rstat = ?', $o{rstat} ) : (), + defined $o{vstat} ? ( 'vstat = ?', $o{vstat} ) : (), ); $o{rstat}||=0; $o{vstat}||=0; - $s->DBExec(q|UPDATE rlists SET !H WHERE uid = %d AND rid IN(!l)|, + $s->DBExec(q|UPDATE rlists !H WHERE uid = ? AND rid IN(!l)|, \%s, $o{uid}, ref($o{rid}) eq 'ARRAY' ? $o{rid} : [ $o{rid} ]) || $s->DBExec(q|INSERT INTO rlists (uid, rid, rstat, vstat) - VALUES(%d, %d, %d, %d)|, - @o{qw| uid rid rstat vstat |}); + VALUES(!l)|, + [@o{qw| uid rid rstat vstat |}]); } sub DBDelRList { # uid, \@rids my($s, $uid, $rid) = @_; - $s->DBExec(q|DELETE FROM rlists WHERE uid = %d AND rid IN(!l)|, $uid, ref($rid) eq 'ARRAY' ? $rid : [ $rid ]); + $s->DBExec(q|DELETE FROM rlists WHERE uid = ? AND rid IN(!l)|, $uid, ref($rid) eq 'ARRAY' ? $rid : [ $rid ]); } @@ -714,30 +710,31 @@ sub DBGetVN { # %options->{ id rev char search order results page what cati cate my %where = ( !$o{id} && !$o{rev} ? ( # don't fetch hidden items unless we ask for an ID - 'v.hidden = FALSE' => 1 ) : (), + 'v.hidden = ?' => 0 ) : (), $o{id} && !ref($o{id}) ? ( - 'v.id = %d' => $o{id} ) : (), + 'v.id = ?' => $o{id} ) : (), $o{id} && ref($o{id}) ? ( - 'v.id IN(!l)' => $o{id} ) : (), + 'v.id IN(!l)' => [$o{id}] ) : (), $o{rev} ? ( - 'c.rev = %d' => $o{rev} ) : (), + 'c.rev = ?' => $o{rev} ) : (), $o{char} ? ( - 'LOWER(SUBSTR(vr.title, 1, 1)) = !s' => $o{char} ) : (), + 'LOWER(SUBSTR(vr.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 ) : (), $o{cati} && @{$o{cati}} ? ( q| v.id IN(SELECT iv.id FROM vn_categories ivc JOIN vn iv ON iv.latest = ivc.vid - WHERE cat IN(!L) + WHERE cat IN(!l) GROUP BY iv.id - HAVING COUNT(cat) = |.($#{$o{cati}}+1).')' => $o{cati} ) : (), + HAVING COUNT(cat) = ?)| => [ $o{cati}, $#{$o{cati}}+1 ] ) : (), $o{cate} && @{$o{cate}} ? ( q| v.id NOT IN(SELECT iv.id FROM vn_categories ivc JOIN vn iv ON iv.latest = ivc.vid - WHERE cat IN(!L) - GROUP BY iv.id)| => $o{cate} ) : (), + WHERE cat IN(!l) + GROUP BY iv.id)| => [ $o{cate} ] ) : (), + # this needs some proper handling... $o{lang} && @{$o{lang}} ? ( '('.join(' OR ', map "v.c_languages ILIKE '%%$_%%'", @{$o{lang}}).')' => 1 ) : (), $o{platform} && @{$o{platform}} ? ( @@ -745,13 +742,17 @@ sub DBGetVN { # %options->{ id rev char search order results page what cati cate ); if($o{search}) { - my %w; + my @w; for (split /[ -,]/, $o{search}) { s/%//g; next if length($_) < 2; - my $gt = VNDB::GTINType($_) ? ' OR irr.gtin = '.$_ : ''; - $w{ sprintf '(ivr.title ILIKE %s OR ivr.alias ILIKE %1$s OR irr.title ILIKE %1$s OR irr.original ILIKE %1$s'.$gt.')', - qs('%%'.$_.'%%') } = 1; + if(VNDB::GTINType($_)) { + push @w, 'irr.gtin = ?', $_; + } else { + $_ = "%$_%"; + push @w, '(ivr.title ILIKE ? OR ivr.alias ILIKE ? OR irr.title ILIKE ? OR irr.original ILIKE ?)', + [ $_, $_, $_, $_ ]; + } } $where{ q| v.id IN(SELECT iv.id @@ -760,12 +761,10 @@ sub DBGetVN { # %options->{ id rev char search order results page what cati cate LEFT JOIN releases_vn irv ON irv.vid = iv.id LEFT JOIN releases_rev irr ON irr.id = irv.rid LEFT JOIN releases ir ON ir.latest = irr.id - WHERE !W - GROUP BY iv.id)| } = \%w if keys %w; + !W + GROUP BY iv.id)| } = [ \@w ] if @w; } - my $where = scalar keys %where ? 'WHERE !W' : ''; - my @join = ( $o{rev} ? 'JOIN vn v ON v.id = vr.vid' : @@ -786,10 +785,10 @@ sub DBGetVN { # %options->{ id rev char search order results page what cati cate SELECT $sel FROM vn_rev vr @join - $where - ORDER BY %s - LIMIT %d OFFSET %d|, - $where ? \%where : (), + !W + ORDER BY !s + LIMIT ? OFFSET ?|, + \%where, $o{order}, $o{results}+(wantarray?1:0), $o{results}*($o{page}-1) ); @@ -857,19 +856,19 @@ sub DBGetVN { # %options->{ id rev char search order results page what cati cate } -sub DBAddVN { # %options->{ columns in vn_rev + comm + relations + categories + anime + screenshots } +sub DBAddVN { # %options->{ comm + _insert_vn_rev } my($s, %o) = @_; my $id = $s->DBRow(q| INSERT INTO changes (type, requester, ip, comments) - VALUES (%d, %d, !s, !s) + VALUES (!l) RETURNING id|, - 0, $s->AuthInfo->{id}, $s->ReqIP, $o{comm} + [ 0, $s->AuthInfo->{id}, $s->ReqIP, $o{comm} ] )->{id}; my $vid = $s->DBRow(q| INSERT INTO vn (latest) - VALUES (%d) + VALUES (?) RETURNING id|, $id )->{id}; @@ -879,59 +878,59 @@ sub DBAddVN { # %options->{ columns in vn_rev + comm + relations + categories + } -sub DBEditVN { # id, %options->( columns in vn_rev + comm + relations + categories + anime + screenshots + uid + causedby } +sub DBEditVN { # id, %options->( comm + _insert_vn_rev + uid + causedby } my($s, $vid, %o) = @_; my $c = $s->DBRow(q| INSERT INTO changes (type, requester, ip, comments, rev, causedby) - VALUES (%d, %d, !s, !s, ( + VALUES (?, ?, ?, ?, ( SELECT c.rev+1 FROM changes c JOIN vn_rev vr ON vr.id = c.id - WHERE vr.vid = %d + WHERE vr.vid = ? ORDER BY c.id DESC LIMIT 1 - ), %d) + ), ?) RETURNING id, rev|, 0, $o{uid}||$s->AuthInfo->{id}, $s->ReqIP, $o{comm}, $vid, $o{causedby}||0); _insert_vn_rev($s, $c->{id}, $vid, \%o); - $s->DBExec(q|UPDATE vn SET latest = %d WHERE id = %d|, $c->{id}, $vid); + $s->DBExec(q|UPDATE vn SET latest = ? WHERE id = ?|, $c->{id}, $vid); return ($c->{rev}, $c->{id}); # local revision, global revision } -sub _insert_vn_rev { +sub _insert_vn_rev { # columns in vn_rev + categories + screenshots + relations my($s, $cid, $vid, $o) = @_; $s->DBExec(q| INSERT INTO vn_rev (id, vid, title, "desc", alias, image, img_nsfw, length, l_wp, l_encubed, l_renai, l_vnn) - VALUES (%d, %d, !s, !s, !s, %d, !b, %d, !s, !s, !s, %d)|, - $cid, $vid, @$o{qw|title desc alias image img_nsfw length l_wp l_encubed l_renai l_vnn|}); + VALUES (!l)|, + [ $cid, $vid, @$o{qw|title desc alias image img_nsfw length l_wp l_encubed l_renai l_vnn|} ]); $s->DBExec(q| INSERT INTO vn_categories (vid, cat, lvl) - VALUES (%d, !s, %d)|, + VALUES (?, ?, ?)|, $cid, $_->[0], $_->[1] ) for (@{$o->{categories}}); $s->DBExec(q| INSERT INTO vn_screenshots (vid, scr, nsfw) - VALUES (%d, %d, !b)|, - $cid, $_->[0], $_->[1] + VALUES (?, ?, ?)|, + $cid, $_->[0], $_->[1]?1:0 ) for (@{$o->{screenshots}}); $s->DBExec(q| INSERT INTO vn_relations (vid1, vid2, relation) - VALUES (%d, %d, %d)|, + VALUES (?, ?, ?)|, $cid, $_->[1], $_->[0] ) for (@{$o->{relations}}); if(@{$o->{anime}}) { $s->DBExec(q| INSERT INTO vn_anime (vid, aid) - VALUES (%d, %d)|, + VALUES (?, ?)|, $cid, $_ ) for (@{$o->{anime}}); @@ -940,7 +939,7 @@ sub _insert_vn_rev { SELECT id FROM anime WHERE id IN(!l)|, $o->{anime}); $s->DBExec(q| - INSERT INTO anime (id) VALUES (%d)|, $_ + INSERT INTO anime (id) VALUES (?)|, $_ ) for (grep { my $ia = $_; !(scalar grep $ia == $_->{id}, @$a) @@ -953,23 +952,15 @@ sub DBHideVN { # id, hidden my($s, $id, $h) = @_; $s->DBExec(q| UPDATE vn - SET hidden = !b - WHERE id = %d|, - $h, $id); - -# $s->DBExec(q| -# DELETE FROM vn_relations -# WHERE vid2 = %d -# OR vid1 IN(SELECT id FROM vn_rev WHERE vid = %d)|, -# $id, $id); -# $s->DBDelVNList(0, $id); -# $s->DBDelVote(0, $id); + SET hidden = ? + WHERE id = ?|, + $h?1:0, $id); } sub DBVNCache { # @vids my($s,@vn) = @_; - $s->DBExec('SELECT update_vncache(%d)', $_) for (@vn); + $s->DBExec('SELECT update_vncache(?)', $_) for (@vn); } @@ -990,18 +981,17 @@ sub DBGetRelease { # %options->{ id vid results page rev } $o{order} ||= 'rr.released ASC'; my %where = ( !$o{id} && !$o{rev} ? ( - 'r.hidden = FALSE' => 1 ) : (), + 'r.hidden = ?' => 0 ) : (), $o{id} ? ( - 'r.id = %d' => $o{id} ) : (), + 'r.id = ?' => $o{id} ) : (), $o{rev} ? ( - 'c.rev = %d' => $o{rev} ) : (), + 'c.rev = ?' => $o{rev} ) : (), $o{vid} ? ( - 'rv.vid = %d' => $o{vid} ) : (), + 'rv.vid = ?' => $o{vid} ) : (), defined $o{unreleased} ? ( - q|rr.released %s TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer| => $o{unreleased} ? '>' : '<=' ) : (), + q|rr.released !s TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer| => $o{unreleased} ? '>' : '<=' ) : (), ); - my $where = scalar keys %where ? 'WHERE !W' : ''; my @join; push @join, $o{rev} ? 'JOIN releases r ON r.id = rr.rid' : 'JOIN releases r ON rr.id = r.latest'; push @join, 'JOIN changes c ON c.id = rr.id' if $o{what} =~ /changes/ || $o{rev}; @@ -1015,10 +1005,10 @@ sub DBGetRelease { # %options->{ id vid results page rev } SELECT $select FROM releases_rev rr @join - $where - ORDER BY %s - LIMIT %d OFFSET %d|, - $where ? \%where : (), + !W + ORDER BY !s + LIMIT ? OFFSET ?|, + \%where, $o{order}, $o{results}+(wantarray?1:0), $o{results}*($o{page}-1) ); @@ -1079,19 +1069,19 @@ sub DBGetRelease { # %options->{ id vid results page rev } } -sub DBAddRelease { # options -> { columns in releases_rev table + comm + vn + producers + media + platforms } +sub DBAddRelease { # options -> { comm + _insert_release_rev } my($s, %o) = @_; my $id = $s->DBRow(q| INSERT INTO changes (type, requester, ip, comments) - VALUES (%d, %d, !s, !s) + VALUES (!l) RETURNING id|, - 1, $s->AuthInfo->{id}, $s->ReqIP, $o{comm} + [ 1, $s->AuthInfo->{id}, $s->ReqIP, $o{comm} ] )->{id}; my $rid = $s->DBRow(q| INSERT INTO releases (latest) - VALUES (%d) + VALUES (?) RETURNING id|, $id)->{id}; _insert_release_rev($s, $id, $rid, \%o); @@ -1099,16 +1089,16 @@ sub DBAddRelease { # options -> { columns in releases_rev table + comm + vn + pr } -sub DBEditRelease { # id, %opts->{ columns in releases_rev table + comm + vn + producers + media + platforms } +sub DBEditRelease { # id, %opts->{ comm + _insert_release_rev } my($s, $rid, %o) = @_; my $c = $s->DBRow(q| INSERT INTO changes (type, requester, ip, comments, rev) - VALUES (%d, %d, !s, !s, ( + VALUES (?, ?, ?, ?, ( SELECT c.rev+1 FROM changes c JOIN releases_rev rr ON rr.id = c.id - WHERE rr.rid = %d + WHERE rr.rid = ? ORDER BY c.id DESC LIMIT 1 )) @@ -1117,41 +1107,40 @@ sub DBEditRelease { # id, %opts->{ columns in releases_rev table + comm + vn + p _insert_release_rev($s, $c->{id}, $rid, \%o); - $s->DBExec(q|UPDATE releases SET latest = %d WHERE id = %d|, $c->{id}, $rid); + $s->DBExec(q|UPDATE releases SET latest = ? WHERE id = ?|, $c->{id}, $rid); return ($c->{rev}, $c->{id}); # local revision, global revision } -sub _insert_release_rev { +sub _insert_release_rev { # %option->{ columns in releases_rev + producers + platforms + vn + media } my($s, $cid, $rid, $o) = @_; - # most GTIN numbers can't be represented in a 32bit integer, so make sure Perl doesn't interpret it as one (%s, not %d) $s->DBExec(q| INSERT INTO releases_rev (id, rid, title, original, gtin, language, website, released, notes, minage, type) - VALUES (%d, %d, !s, !s, %s, !s, !s, %d, !s, %d, %d)|, - $cid, $rid, @$o{qw| title original gtin language website released notes minage type|}); + VALUES (!l)|, + [ $cid, $rid, @$o{qw| title original gtin language website released notes minage type|} ]); $s->DBExec(q| INSERT INTO releases_producers (rid, pid) - VALUES (%d, %d)|, + VALUES (?, ?)|, $cid, $_ ) for (@{$o->{producers}}); $s->DBExec(q| INSERT INTO releases_platforms (rid, platform) - VALUES (%d, !s)|, + VALUES (?, ?)|, $cid, $_ ) for (@{$o->{platforms}}); $s->DBExec(q| INSERT INTO releases_vn (rid, vid) - VALUES (%d, %d)|, + VALUES (?, ?)|, $cid, $_ ) for (@{$o->{vn}}); $s->DBExec(q| INSERT INTO releases_media (rid, medium, qty) - VALUES (%d, !s, %d)|, + VALUES (?, ?, ?)|, $cid, $_->[0], $_->[1] ) for (@{$o->{media}}); } @@ -1161,9 +1150,9 @@ sub DBHideRelease { # id, hidden my($s, $id, $h) = @_; $s->DBExec(q| UPDATE releases - SET hidden = !b - WHERE id = %d|, - $h, $id); + SET hidden = ? + WHERE id = ?|, + $h?1:0, $id); } @@ -1182,21 +1171,19 @@ sub DBGetProducer { # %options->{ id search char results page rev } $o{what} ||= ''; my %where = ( !$o{id} && !$o{rev} ? ( - 'p.hidden = FALSE' => 1 ) : (), + 'p.hidden = ?' => 0 ) : (), $o{id} ? ( - 'p.id = %d' => $o{id} ) : (), + 'p.id = ?' => $o{id} ) : (), $o{search} ? ( - sprintf('(pr.name ILIKE %s OR pr.original ILIKE %1$s)', qs('%%'.$o{search}.'%%')), 1 - ) : (), + '(pr.name ILIKE ? OR pr.original ILIKE ?)', [ '%%'.$o{search}.'%%', '%%'.$o{search}.'%%' ] ) : (), $o{char} ? ( - 'LOWER(SUBSTR(pr.name, 1, 1)) = !s' => $o{char} ) : (), + 'LOWER(SUBSTR(pr.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 = %d' => $o{rev} ) : (), + 'c.rev = ?' => $o{rev} ) : (), ); - my $where = scalar keys %where ? 'WHERE !W' : ''; 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}; @@ -1209,10 +1196,10 @@ sub DBGetProducer { # %options->{ id search char results page rev } SELECT $select FROM producers_rev pr @join - $where + !W ORDER BY pr.name ASC - LIMIT %d OFFSET %d|, - $where ? \%where : (), + LIMIT ? OFFSET ?|, + \%where, $o{results}+(wantarray?1:0), $o{results}*($o{page}-1) ); @@ -1223,7 +1210,6 @@ sub DBGetProducer { # %options->{ id search char results page rev } } -# XXX: This query is killing me! sub DBGetProducerVN { # pid return $_[0]->DBAll(q| SELECT v.id, MAX(vr.title) AS title, MIN(rr.released) AS date @@ -1233,27 +1219,27 @@ sub DBGetProducerVN { # pid JOIN releases_vn rv ON rv.rid = rr.id JOIN vn v ON v.id = rv.vid JOIN vn_rev vr ON vr.id = v.latest - WHERE vp.pid = %d - AND v.hidden = FALSE + WHERE vp.pid = ? + AND v.hidden = ? GROUP BY v.id ORDER BY date|, - $_[1]); + $_[1], 0); } -sub DBAddProducer { # %opts->{ columns in producers_rev + comm } +sub DBAddProducer { # %opts->{ comm + _insert_producer_rev } my($s, %o) = @_; my $id = $s->DBRow(q| INSERT INTO changes (type, requester, ip, comments) - VALUES (%d, %d, !s, !s) + VALUES (!l) RETURNING id|, - 2, $s->AuthInfo->{id}, $s->ReqIP, $o{comm} + [ 2, $s->AuthInfo->{id}, $s->ReqIP, $o{comm} ] )->{id}; my $pid = $s->DBRow(q| INSERT INTO producers (latest) - VALUES (%d) + VALUES (?) RETURNING id|, $id )->{id}; @@ -1263,16 +1249,16 @@ sub DBAddProducer { # %opts->{ columns in producers_rev + comm } } -sub DBEditProducer { # id, %opts->{ columns in producers_rev + comm } +sub DBEditProducer { # id, %opts->{ comm + _insert_producer_rev } my($s, $pid, %o) = @_; my $c = $s->DBRow(q| INSERT INTO changes (type, requester, ip, comments, rev) - VALUES (%d, %d, !s, !s, ( + VALUES (?, ?, ?, ?, ( SELECT c.rev+1 FROM changes c JOIN producers_rev pr ON pr.id = c.id - WHERE pr.pid = %d + WHERE pr.pid = ? ORDER BY c.id DESC LIMIT 1 )) @@ -1281,17 +1267,17 @@ sub DBEditProducer { # id, %opts->{ columns in producers_rev + comm } _insert_producer_rev($s, $c->{id}, $pid, \%o); - $s->DBExec(q|UPDATE producers SET latest = %d WHERE id = %d|, $c->{id}, $pid); + $s->DBExec(q|UPDATE producers SET latest = ? WHERE id = ?|, $c->{id}, $pid); return ($c->{rev}, $c->{id}); # local revision, global revision } -sub _insert_producer_rev { +sub _insert_producer_rev { # %opts->{ columns in produces_rev } my($s, $cid, $pid, $o) = @_; $s->DBExec(q| INSERT INTO producers_rev (id, pid, name, original, website, type, lang, "desc") - VALUES (%d, %d, !s, !s, !s, !s, !s, !s)|, - $cid, $pid, @$o{qw| name original website type lang desc|}); + VALUES (!l)|, + [ $cid, $pid, @$o{qw| name original website type lang desc|} ]); } @@ -1299,9 +1285,9 @@ sub DBHideProducer { # id, hidden my($s, $id, $h) = @_; $s->DBExec(q| UPDATE producers - SET hidden = !b - WHERE id = %d|, - $h, $id); + SET hidden = ? + WHERE id = ?|, + $h?1:0, $id); } @@ -1323,15 +1309,14 @@ sub DBGetThreads { # %options->{ id type iid results page what } my %where = ( $o{id} ? ( - 't.id = %d' => $o{id} ) : (), + 't.id = ?' => $o{id} ) : (), !$o{id} ? ( - 't.hidden = FALSE' => 1 ) : (), + 't.hidden = ?' => 0 ) : (), $o{type} && !$o{iid} ? ( - 't.id IN(SELECT tid FROM threads_tags WHERE type = !s)' => $o{type} ) : (), + 't.id IN(SELECT tid FROM threads_tags WHERE type = ?)' => $o{type} ) : (), $o{type} && $o{iid} ? ( - 'tt.type = !s' => $o{type}, 'tt.iid = %d' => $o{iid} ) : (), + 'tt.type = ?' => $o{type}, 'tt.iid = ?' => $o{iid} ) : (), ); - my $where = scalar keys %where ? 'WHERE !W' : ''; my $select = 't.id, t.title, t.count, t.locked, t.hidden'; $select .= ', tp.uid, tp.date, u.username' if $o{what} =~ /firstpost/; @@ -1348,10 +1333,10 @@ sub DBGetThreads { # %options->{ id type iid results page what } SELECT $select FROM threads t @join - $where - ORDER BY %s - LIMIT %d OFFSET %d|, - $where ? \%where : (), + !W + ORDER BY !s + LIMIT ? OFFSET ?|, + \%where, $o{order}, $o{results}+(wantarray?1:0), $o{results}*($o{page}-1) ); @@ -1399,18 +1384,18 @@ sub DBGetPosts { # %options->{ tid num page results } $o{page} ||= 1; my %where = ( - 'tp.tid = %d' => $o{tid}, + 'tp.tid = ?' => $o{tid}, $o{num} ? ( - 'tp.num = %d' => $o{num} ) : (), + 'tp.num = ?' => $o{num} ) : (), ); my $r = $s->DBAll(q| SELECT tp.num, tp.date, tp.edited, tp.msg, tp.hidden, tp.uid, u.username FROM threads_posts tp JOIN users u ON u.id = tp.uid - WHERE !W + !W ORDER BY tp.num ASC - LIMIT %d OFFSET %d|, + LIMIT ? OFFSET ?|, \%where, $o{results}, $o{results}*($o{page}-1) ); @@ -1422,18 +1407,18 @@ sub DBGetPosts { # %options->{ tid num page results } sub DBAddPost { # %options->{ tid uid msg num } my($s, %o) = @_; - $o{num} ||= $s->DBRow('SELECT num FROM threads_posts WHERE tid = %d ORDER BY num DESC LIMIT 1', $o{tid})->{num}+1; + $o{num} ||= $s->DBRow('SELECT num FROM threads_posts WHERE tid = ? ORDER BY num DESC LIMIT 1', $o{tid})->{num}+1; $o{uid} ||= $s->AuthInfo->{id}; $s->DBExec(q| INSERT INTO threads_posts (tid, num, uid, msg) - VALUES(%d, %d, %d, !s)|, + VALUES(?, ?, ?, ?)|, @o{qw| tid num uid msg |} ); $s->DBExec(q| UPDATE threads SET count = count+1 - WHERE id = %d|, + WHERE id = ?|, $o{tid}); return $o{num}; @@ -1444,16 +1429,16 @@ sub DBEditPost { # %options->{ tid num msg hidden } my($s, %o) = @_; my %set = ( - 'msg = !s' => $o{msg}, - 'edited = %d' => time, - 'hidden = !b' => $o{hidden}, + 'msg = ?' => $o{msg}, + 'edited = ?' => time, + 'hidden = ?' => $o{hidden}?1:0, ); $s->DBExec(q| UPDATE threads_posts - SET !H - WHERE tid = %d - AND num = %d|, + !H + WHERE tid = ? + AND num = ?|, \%set, $o{tid}, $o{num} ); } @@ -1463,22 +1448,22 @@ sub DBEditThread { # %options->{ id title locked hidden tags } my($s, %o) = @_; my %set = ( - 'title = !s' => $o{title}, - 'locked = !b' => $o{locked}, - 'hidden = !b' => $o{hidden}, + 'title = ?' => $o{title}, + 'locked = ?' => $o{locked}?1:0, + 'hidden = ?' => $o{hidden}?1:0, ); $s->DBExec(q| UPDATE threads - SET !H - WHERE id = %d|, + !H + WHERE id = ?|, \%set, $o{id}); if($o{tags}) { - $s->DBExec('DELETE FROM threads_tags WHERE tid = %d', $o{id}); + $s->DBExec('DELETE FROM threads_tags WHERE tid = ?', $o{id}); $s->DBExec(q| INSERT INTO threads_tags (tid, type, iid) - VALUES (%d, !s, %d)|, + VALUES (?, ?, ?)|, $o{id}, $_->[0], $_->[1]||0 ) for (@{$o{tags}}); } @@ -1490,14 +1475,14 @@ sub DBAddThread { # %options->{ title hidden locked tags } my $id = $s->DBRow(q| INSERT INTO threads (title, hidden, locked) - VALUES (!s, !b, !b) + VALUES (?, ?, ?) RETURNING id|, $o{title}, $o{hidden}, $o{locked} )->{id}; $s->DBExec(q| INSERT INTO threads_tags (tid, type, iid) - VALUES (%d, !s, %d)|, + VALUES (?, ?, ?)|, $id, $_->[0], $_->[1] ) for (@{$o{tags}}); @@ -1517,12 +1502,6 @@ sub DBExec { return sqlhelper(shift, 0, @_); } sub DBRow { return sqlhelper(shift, 1, @_); } sub DBAll { return sqlhelper(shift, 2, @_); } - -sub DBLastId { # table - return $_[0]->{_DB}->{sql}->last_insert_id(undef, undef, $_[1], undef); -} - - sub sqlhelper { # type, query, @list my $self = shift; my $type = shift; @@ -1533,17 +1512,17 @@ sub sqlhelper { # type, query, @list $sqlq =~ s/\r?\n/ /g; $sqlq =~ s/ +/ /g; - $sqlq = sqlprint($sqlq, @_) if exists $_[0]; - #warn "$sqlq\n"; + my(@q) = @_ ? sqlprint(0, $sqlq, @_) : ($sqlq); + #warn join(', ', map "'$_'", @q)."\n"; - my $q = $s->prepare($sqlq); - $q->execute(); + my $q = $s->prepare($q[0]); + $q->execute($#q ? @q[1..$#q] : ()); my $r = $type == 1 ? $q->fetchrow_hashref : $type == 2 ? $q->fetchall_arrayref({}) : $q->rows; $q->finish(); - push(@{$self->{_DB}->{Queries}}, [ $sqlq, Time::HiRes::tv_interval($start) ]) if $self->{debug}; + push(@{$self->{_DB}->{Queries}}, [ $q[0], Time::HiRes::tv_interval($start), @q[1..$#q] ]) if $self->{debug}; $r = 0 if $type == 0 && !$r; $r = {} if $type == 1 && (!$r || ref($r) ne 'HASH'); @@ -1553,60 +1532,49 @@ sub sqlhelper { # type, query, @list } -# Added features: -# !s SQL-quote -# !b boolean (anything perl considers true = 'TRUE', otherwise 'FALSE') -# !l listify -# !L SQL-quote-and-listify -# !H list of SET-items: key = format, value = replacement -# !W same as !H, but for WHERE clauses -sub sqlprint { - my $i = -1; +# sqlprint: +# ? normal placeholder +# !l list of placeholders, expects arrayref +# !H list of SET-items, expects hashref or arrayref: format => (bind_value || \@bind_values) +# !W same as !H, but for WHERE clauses (AND'ed together) +# !s the classic sprintf %s, use with care +# This isn't sprintf, so all other things won't work, +# Only the ? placeholder is supported, so no dollar sign numbers or named placeholders +# Indeed, this also means you can't use PgSQL operators containing a question mark + +sub sqlprint { # start, query, bind values. Returns new query + bind values my @arg; - my $sq = my $s = shift; - while($sq =~ s/([%!])(.)//) { - $i++; - my $t = $1; my $d = $2; - if($t eq '%') { - if($d eq '%') { - $i--; next + my $q=''; + my $s = shift; + for my $p (split /(\?|![lHWs])/, shift) { + next if !defined $p; + if($p eq '?') { + push @arg, shift; + $q .= '$'.(@arg+$s); + } elsif($p eq '!s') { + $q .= shift; + } elsif($p eq '!l') { + my $l = shift; + $q .= join ', ', map '$'.(@arg+$s+$_+1), 0..$#$l; + push @arg, @$l; + } elsif($p eq '!H' || $p eq '!W') { + my $h=shift; + my @h=ref $h eq 'HASH' ? %$h : @$h; + my @r; + while(my($k,$v) = (shift(@h), shift(@h))) { + last if !defined $k; + my($n,@l) = sqlprint($#arg+1, $k, ref $v eq 'ARRAY' ? @$v : $v); + push @r, $n; + push @arg, @l; } - $arg[$i] = $_[$i]; - next; - } - if($d !~ /[sblLHW]/) { - $i--; next + $q .= ($p eq '!W' ? 'WHERE ' : 'SET ').join $p eq '!W' ? ' AND ' : ', ', @r + if @r; + } else { + $q .= $p; } - $arg[$i] = qs($_[$i]) if $d eq 's'; - $arg[$i] = $_[$i] ? 'TRUE' : 'FALSE' if $d eq 'b'; - $arg[$i] = join(',', @{$_[$i]}) if $d eq 'l'; - $arg[$i] = join(',', (qs(@{$_[$i]}))) if $d eq 'L'; - if($d eq 'H' || $d eq 'W') { - my @i; - defined $_[$i]{$_} && push(@i, sqlprint($_, $_[$i]{$_})) for keys %{$_[$i]}; - $arg[$i] = join($d eq 'H' ? ', ' : ' AND ', @i); - } - } - $s =~ s/![sblLHW]/%s/g; - $s =~ s/!!/!/g; - return sprintf($s, @arg); -} - - -sub qs { # ISO SQL2-quoting, with some PgSQL-specific stuff - my @r = @_; - # NOTE: we use E''-style strings because backslash escaping in the normal ''-style - # depends on the standard_conforming_strings configuration option of PgSQL, - # while E'' will always behave the same regardless of the server configuration. - for (@r) { - (!defined $_ or $_ eq '_NULL_') && next; - s/'/''/g; - s/\\/\\\\/g; - $_ = "E'$_'"; } - return wantarray ? @r : $r[0]; + return($q, @arg); } - 1; diff --git a/lib/VNDB/Util/Tools.pm b/lib/VNDB/Util/Tools.pm index 8fe918d0..ee295a67 100644 --- a/lib/VNDB/Util/Tools.pm +++ b/lib/VNDB/Util/Tools.pm @@ -145,6 +145,7 @@ sub AddDefaultStuff { for (@{$self->{_DB}->{Queries}}) { $_->[0] =~ s/^\s//g; $sqls .= sprintf("[%6.2fms] %s\n", $_->[1]*1000, $_->[0] || '[undef]'); + $sqls .= " ".join(', ', map "'$_'", @{$_}[2..$#$_])."\n" if exists $$_[2]; } $self->ResAddTpl(devshit => $sqls); } |