From 2cf1c1848571f11e753dec784c7323e51b00801d Mon Sep 17 00:00:00 2001 From: yorhel Date: Thu, 14 Aug 2008 21:11:52 +0000 Subject: Added screenshots table to store the dimensions of all images, moved to AJAX for determining when a thumbnail has been generated after uploading a screenshot, and got rid of the javascript image preloader git-svn-id: svn://vndb.org/vndb@84 1fe2e327-d9db-4752-bcf7-ef0cb4a1748b --- data/tpl/vnpage | 2 +- data/tpl/vnpage_scr | 10 +++--- lib/Multi/Image.pm | 84 ++++++++++++++++++++++++++------------------ lib/VNDB/Util/DB.pm | 23 ++++++++---- lib/VNDB/VN.pm | 25 ++++++++++--- static/files/def.js | 47 ++++++++++--------------- static/files/dyna.js | 49 +++++++++++++++----------- static/files/style.css | 1 - util/dump.sql | 14 ++++++-- util/updates/update_1.21.sql | 12 ++++--- 10 files changed, 159 insertions(+), 108 deletions(-) diff --git a/data/tpl/vnpage b/data/tpl/vnpage index d0651992..e77b5dc0 100644 --- a/data/tpl/vnpage +++ b/data/tpl/vnpage @@ -35,7 +35,7 @@ [ categories => 'Categories', sub { join(' ', map { my $l=$VNDB::CAT->{substr($_->[0],0,1)}[1]{substr($_->[0],1,2)}; $l?$l.'('.$_->[1].')':() } sort { $a->[0] cmp $b->[0] } @{$_[0]}) || 'No categories selected' }, 1 ], [ relations => 'Relations', sub { join("
\n", map { $VNDB::VREL->[$_->{relation}].': '._hchar($_->{title}) } sort { $a->{id} <=> $b->{id} } @{$_[0]}) } ], [ image => 'Image', sub { $_[0] > 0 ? sprintf '', $p{st}, $_[0]%100, $_[0] : $_[0] < 0 ? '[processing]' : 'No image'; } ], - [ screenshots => 'Screenshots', sub { join "
\n", map sprintf('%2$d (%s)',$p{st},$$_[0]%100,$$_[0],$$_[1]?'NSFW':'Safe'), @{$_[0]} } ], + [ screenshots => 'Screenshots', sub { join "
\n", map sprintf('%2$d (%s)',$p{st},$$_{id}%100,$$_{id},$$_{nsfw}?'NSFW':'Safe'), @{$_[0]} } ], [ img_nsfw => 'NSFW', sub { $_[0] ? 'Not safe' : 'Safe' } ] ) ]] [[ } ]]- diff --git a/data/tpl/vnpage_scr b/data/tpl/vnpage_scr index b12bbb14..687e80db 100644 --- a/data/tpl/vnpage_scr +++ b/data/tpl/vnpage_scr @@ -3,14 +3,15 @@ [[ if(@{$d{vn}{screenshots}}) { my $tot = @{$d{vn}{screenshots}}; - my $nsfw = grep $$_[1], @{$d{vn}{screenshots}}; + my $nsfw = grep $$_{nsfw}, @{$d{vn}{screenshots}}; ]]-
[[ for(@{$d{vn}{screenshots}}) { ]] - + [[ } ]]-
[[ if($nsfw) { ]]- @@ -25,7 +26,6 @@ <- previous next -> - [[ } else { ]]-

diff --git a/lib/Multi/Image.pm b/lib/Multi/Image.pm index 14ffc94b..4e4e6a48 100644 --- a/lib/Multi/Image.pm +++ b/lib/Multi/Image.pm @@ -19,7 +19,7 @@ sub spawn { package_states => [ $p => [qw| _start cmd_coverimage cv_process cv_update cv_finish - cmd_screenshot scr_process scr_clean scr_finish + cmd_screenshot scr_process scr_update scr_clean scr_finish |], ], heap => { @@ -34,10 +34,7 @@ sub _start { $_[KERNEL]->alias_set('image'); $_[KERNEL]->sig(shutdown => 'shutdown'); $_[KERNEL]->call(core => register => qr/^coverimage(?: ([0-9]+)|)$/, 'cmd_coverimage'); - $_[KERNEL]->call(core => register => qr/^screenshot ([0-9]+|all|clean)$/, 'cmd_screenshot'); - - # daily check for unprocessed cover images - $_[KERNEL]->post(core => addcron => '0 0 * * *', 'coverimage'); + $_[KERNEL]->call(core => register => qr/^screenshot(?: ([0-9]+|all|clean))?$/, 'cmd_screenshot'); } @@ -96,10 +93,10 @@ sub cv_process { # id sub cv_update { # id - if($Multi::SQL->do('UPDATE vn_rev SET image = ? WHERE image = ?', undef, $_[ARG0], -1*$_[ARG0])) { + if($Multi::SQL->do('UPDATE vn_rev SET image = ? WHERE image = ?', undef, $_[ARG0], -1*$_[ARG0]) > 0) { $_[KERNEL]->yield(cv_finish => $_[ARG0]); - } elsif(!$_[ARG0]) { - $_[KERNEL]->delay(cv_update => 5 => $_[ARG0]); + } elsif(!$_[ARG1]) { + $_[KERNEL]->delay(cv_update => 3 => $_[ARG0], 1); } else { $_[KERNEL]->call(core => log => 1, 'Image %d not present in the database!', $_[ARG0]); $_[KERNEL]->yield(cv_finish => $_[ARG0]); @@ -125,13 +122,25 @@ sub cmd_screenshot { my($cmd, $id) = @_[ARG0, ARG1]; $_[HEAP]{curcmd} = $_[ARG0]; $_[HEAP]{id} = $_[ARG1]; + + if(!$id) { + my $q = $Multi::SQL->prepare('SELECT id FROM screenshots WHERE status = 0'); + $q->execute(); + $_[HEAP]{todo} = [ map { $_->[0]} @{$q->fetchall_arrayref([])} ]; + if(!@{$_[HEAP]{todo}}) { + $_[KERNEL]->call(core => log => 2, 'No screenshots to process'); + $_[KERNEL]->yield('scr_finish'); + return; + } - if($id eq 'clean') { + } elsif($id eq 'clean') { return $_[KERNEL]->yield('scr_clean'); + } elsif($id eq 'all') { my $q = $Multi::SQL->prepare('SELECT DISTINCT scr FROM vn_screenshots'); $q->execute(); $_[HEAP]{todo} = [ map $_->[0], @{$q->fetchall_arrayref([])} ]; + } else { $_[HEAP]{todo} = [ $_[ARG1] ]; } @@ -180,39 +189,44 @@ sub scr_process { # id $_[KERNEL]->call(core => log => 2, 'Processed screenshot #%d in %.2fs: %.1fkB -> %.1fkB (%dx%d), thumb: %.1fkB (%dx%d)', $_[ARG0], time-$start, $os/1024, (-s $sf)/1024, $ow, $oh, (-s $st)/1024, $w, $h); - $_[KERNEL]->yield(scr_finish => $_[ARG0]); + $_[KERNEL]->yield(scr_update => $_[ARG0], $ow, $oh); +} + + +sub scr_update { # id, width, height + if($Multi::SQL->do('UPDATE screenshots SET status = 1, width = ?, height = ? WHERE id = ?', undef, $_[ARG1], $_[ARG2], $_[ARG0]) > 0) { + $_[KERNEL]->yield(scr_finish => $_[ARG0]); + } elsif(!$_[ARG3]) { + $_[KERNEL]->delay(scr_update => 3 => @_[ARG0..$#_], 1); + } else { + $_[KERNEL]->call(core => log => 1, 'Screenshot %d not present in the database!', $_[ARG0]); + $_[KERNEL]->yield(scr_finish => $_[ARG0]); + } } sub scr_clean { - # not very efficient... - my $q = $Multi::SQL->prepare('SELECT DISTINCT scr FROM vn_screenshots'); + my $sql = ' FROM screenshots s WHERE NOT EXISTS(SELECT 1 FROM vn_screenshots vs WHERE vs.scr = s.id)'; + my $q = $Multi::SQL->prepare('SELECT s.id'.$sql); $q->execute(); - my @exists = map $_->[0], @{$q->fetchall_arrayref([])}; - - # not very efficient either... - my @files = map /\/([0-9]+)\.jpg$/?$1:(), glob "$VNDB::VNDBopts{sfpath}/*/*.jpg"; - - my($files, $thumbs, $bytes) = (0,0,0); - for my $id (@files) { - if(!grep $_==$id, @exists) { - my $f = sprintf '%s/%02d/%d.jpg', $VNDB::VNDBopts{stpath}, $id%100, $id; - my $t = sprintf '%s/%02d/%d.jpg', $VNDB::VNDBopts{stpath}, $id%100, $id; - $bytes += -s $f; - $files++; - unlink $f; - if(-f $t) { - $bytes += -s $t; - $thumbs++; - unlink $t; - } - $_[KERNEL]->call(core => log => 3, 'Removing screenshot #%d', $id); - } + + my($bytes, $items, $id) = (0, 0, 0); + while(($id) = $q->fetchrow_array) { + my $f = sprintf '%s/%02d/%d.jpg', $VNDB::VNDBopts{stpath}, $id%100, $id; + my $t = sprintf '%s/%02d/%d.jpg', $VNDB::VNDBopts{stpath}, $id%100, $id; + $bytes += -s $f; + $bytes += -s $t; + $items++; + unlink $f; + unlink $t; + $_[KERNEL]->call(core => log => 3, 'Removing screenshot #%d', $id); } - $_[KERNEL]->call(core => log => 2, 'Removed %d + %d unused files, total of %.2fMB freed.', - $files, $thumbs, $bytes/1024/1024) if $files; - $_[KERNEL]->call(core => log => 2, 'No unused screenshots found') if !$files; + $Multi::SQL->do('DELETE'.$sql); + + $_[KERNEL]->call(core => log => 2, 'Removed %d unused screenshots, total of %.2fMB freed.', + $items, $bytes/1024/1024) if $items; + $_[KERNEL]->call(core => log => 2, 'No unused screenshots found') if !$items; $_[KERNEL]->yield('scr_finish'); } diff --git a/lib/VNDB/Util/DB.pm b/lib/VNDB/Util/DB.pm index a2f3efe3..683caec0 100644 --- a/lib/VNDB/Util/DB.pm +++ b/lib/VNDB/Util/DB.pm @@ -12,7 +12,7 @@ $VERSION = $VNDB::VERSION; @EXPORT = qw| DBInit DBCheck DBCommit DBRollBack DBExit - DBLanguageCount DBCategoryCount DBTableCount DBGetHist DBLockItem DBIncId + DBLanguageCount DBCategoryCount DBTableCount DBGetHist DBLockItem DBIncId DBAddScreenshot DBGetScreenshot DBGetUser DBAddUser DBUpdateUser DBDelUser DBGetVotes DBVoteStats DBAddVote DBDelVote DBGetVNList DBDelVNList @@ -245,6 +245,16 @@ sub DBIncId { # sequence (this is a rather low-level function... aww heck...) } +sub DBAddScreenshot { # just returns an ID + return $_[0]->DBRow(q|INSERT INTO screenshots (status) VALUES(0) RETURNING id|)->{id}; +} + + +sub DBGetScreenshot { # ids + return $_[0]->DBAll(q|SELECT * FROM screenshots WHERE id IN(!l)|, $_[1]); +} + + #-----------------------------------------------------------------------------# # A U T H / U S E R S T U F F # @@ -811,11 +821,12 @@ sub DBGetVN { # %options->{ id rev char search order results page what cati cate } if($o{what} =~ /screenshots/) { - push(@{$r->[$r{$_->{vid}}]{screenshots}}, [ $_->{scr}, $_->{nsfw} ]) for (@{$s->DBAll(q| - SELECT vid, scr, nsfw - FROM vn_screenshots - WHERE vid IN(!l) - ORDER BY scr|, + push(@{$r->[$r{$_->{vid}}]{screenshots}}, $_) && delete $_->{vid} for (@{$s->DBAll(q| + SELECT vs.vid, s.id, vs.nsfw, s.width, s.height + FROM vn_screenshots vs + JOIN screenshots s ON vs.scr = s.id + WHERE vs.vid IN(!l) + ORDER BY vs.scr|, [ keys %r ] )}); } diff --git a/lib/VNDB/VN.pm b/lib/VNDB/VN.pm index a6e5e735..e52fc2fc 100644 --- a/lib/VNDB/VN.pm +++ b/lib/VNDB/VN.pm @@ -84,7 +84,7 @@ sub VNEdit { relations => join('|||', map { $_->{relation}.','.$_->{id}.','.$_->{title} } @{$v->{relations}}), categories => join(',', map { $_->[0].$_->[1] } sort { $a->[0] cmp $b->[0] } @{$v->{categories}}), anime => join(' ', sort { $a <=> $b } map $_->{id}, @{$v->{anime}}), - screenshots => join(' ', map "$$_[0],$$_[1]", @{$v->{screenshots}}), + screenshots => join(' ', map "$$_{id},$$_{nsfw}", @{$v->{screenshots}}), ) : (); my $frm = {}; @@ -323,10 +323,25 @@ sub VNXML { sub VNScrXML { my $self = shift; - - return $self->ResNotFound if $self->ReqMethod ne 'POST'; return $self->ResDenied if !$self->AuthCan('edit'); + # check the status of recently uploaded screenshots + if($self->ReqMethod ne 'POST') { + my $ids = $self->FormCheck( + { name => 'id', required => 1, template => 'int', multi => 1 } + ); + return $self->ResNotFound if $ids->{_err}; + my $r = $self->DBGetScreenshot($ids->{id}); + return $self->ResNotFound if !@$r; + my $x = $self->ResStartXML; + $x->startTag('images'); + $x->emptyTag('image', id => $_->{id}, status => $_->{status}, width => $_->{width}, height => $_->{height}) + for (@$r); + $x->endTag('images'); + return; + } + + # upload new screenshot my $i = $self->FormCheck( { name => 'itemnumber', required => 1, template => 'int' } ); @@ -349,11 +364,11 @@ sub VNScrXML { if($id) { unlink $tmp; } else { - $id = $self->DBIncId('screenshots_seq'); + $id = $self->DBAddScreenshot; my $new = sprintf '%s/%02d/%d.jpg', $self->{sfpath}, $id%100, $id; rename $tmp, $new or die $!; chmod 0666, $new; - $self->RunCmd(sprintf 'screenshot %d', $id); + $self->RunCmd('screenshot'); } my $x = $self->ResStartXML; diff --git a/static/files/def.js b/static/files/def.js index 7e7e46d9..56b8d237 100644 --- a/static/files/def.js +++ b/static/files/def.js @@ -240,10 +240,11 @@ function scrNsfwHid() { +(scrNsfwEnabled ? 'Show / hide' : 'Show / hide') +' nsfw.'; } -var scrInt; function scrView() { var u=this.href; + var r=this.rel; var ol=x('screenshots').getElementsByTagName('a'); + d = x('scrView'); var l=[]; // remove NSFW @@ -256,51 +257,41 @@ function scrView() { if(l[i].href == u) { x('scrnext').style.visibility = l[i+1] ? 'visible' : 'hidden'; x('scrnext').href = l[i+1] ? l[i+1].href : '#'; + x('scrnext').rel = l[i+1] ? l[i+1].rel : ''; x('scrprev').style.visibility = l[i-1] ? 'visible' : 'hidden'; x('scrprev').href = l[i-1] ? l[i-1].href : '#'; + x('scrprev').rel = l[i-1] ? l[i-1].rel : ''; } - // show image - x('preload').src = u; - x('scrimg').innerHTML = 'Loading...'; - x('scrView').style.display = 'block'; - scrPosition(1); - return false; -} -function scrPosition(act) { - d = x('scrView'); - m = x('preload'); - // why don't all browsers use the same DOM... + // calculate dimensions + var w = Math.floor(r.split('x')[0]); + var h = Math.floor(r.split('x')[1]); var ww = typeof(window.innerWidth) == 'number' ? window.innerWidth : document.documentElement.clientWidth; var wh = typeof(window.innerHeight) == 'number' ? window.innerHeight : document.documentElement.clientHeight; var st = typeof(window.pageYOffset) == 'number' ? window.pageYOffset : document.body && document.body.scrollTop ? document.body.scrollTop : document.documentElement.scrollTop; - var h=20;var w=200; - if(act != 1) { - w = m.offsetWidth; - h = m.offsetHeight; - if(w+100 > ww || h+100 > wh) { - if(w/h > ww/wh) { // width++ - h *= (ww-100)/w; - w = ww-100; - } else { // height++ - w *= (wh-100)/h; - h = wh-100; - } + if(w+100 > ww || h+100 > wh) { + if(w/h > ww/wh) { // width++ + h *= (ww-100)/w; + w = ww-100; + } else { // height++ + w *= (wh-100)/h; + h = wh-100; } } var dw = w; var dh = h+20; dw = dw < 200 ? 200 : dw; + + // update document + d.style.display = 'block'; + x('scrimg').innerHTML = ''; d.style.width = dw+'px'; d.style.height = dh+'px'; d.style.left = ((ww - dw) / 2 - 10)+'px'; d.style.top = ((wh - dh) / 2 + st)+'px'; - if(act != 1) - x('scrimg').innerHTML = ''; + return false; } function scrClose() { - clearInterval(scrInt); - scrInt = null; x('scrView').style.display = 'none'; x('scrView').style.top = '-5000px'; x('scrimg').innerHTML = ''; diff --git a/static/files/dyna.js b/static/files/dyna.js index 639acfa9..e9d1e917 100644 --- a/static/files/dyna.js +++ b/static/files/dyna.js @@ -599,7 +599,7 @@ function scrLoad() { var l=x('screenshots').value.split(' '); for(var i=0;i 2) - scrL[i] = { load: 0, id: l[i].split(',')[0], nsfw: l[i].split(',')[1]>0?1:0 }; + scrL[i] = { load: 2, id: l[i].split(',')[0], nsfw: l[i].split(',')[1]>0?1:0 }; // because IE can't operate on x('scrfrm').innerHTML = '
'; @@ -608,6 +608,8 @@ function scrLoad() { scrGenerateTR(i); setTimeout(scrSetSubmit, 1000); + setInterval(scrCheckStatus, 1000); + scrCheckStatus(); } // give an error when submitting the form while still uploading an image @@ -657,7 +659,8 @@ function scrGenerateTR(i) { r += 'Screenshot #'+scrL[i].id+'' +' ' +'' - +''; + +'' + +'

Full size: '+scrL[i].width+'x'+scrL[i].height+'px'; if(scrL[i].obj) { x('scrTr'+i).getElementsByTagName('td')[1].innerHTML = r; @@ -729,26 +732,32 @@ function scrUploadComplete(i) { } } -function scrImageFail(i) { - if(scrL[i].timer) - clearTimeout(scrL[i].timer); - if(!scrL[i].load) +function scrCheckStatus() { + var ids=''; + for(var i=0;i