summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--data/tpl/vnpage2
-rw-r--r--data/tpl/vnpage_scr10
-rw-r--r--lib/Multi/Image.pm84
-rw-r--r--lib/VNDB/Util/DB.pm23
-rw-r--r--lib/VNDB/VN.pm25
-rw-r--r--static/files/def.js47
-rw-r--r--static/files/dyna.js49
-rw-r--r--static/files/style.css1
-rw-r--r--util/dump.sql14
-rw-r--r--util/updates/update_1.21.sql12
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("<br />\n", map { $VNDB::VREL->[$_->{relation}].': '._hchar($_->{title}) } sort { $a->{id} <=> $b->{id} } @{$_[0]}) } ],
[ image => 'Image', sub { $_[0] > 0 ? sprintf '<img src="%s/cv/%02d/%d.jpg" />', $p{st}, $_[0]%100, $_[0] : $_[0] < 0 ? '[processing]' : 'No image'; } ],
- [ screenshots => 'Screenshots', sub { join "<br />\n", map sprintf('<a href="%s/sf/%02d/%d.jpg">%2$d</a> (%s)',$p{st},$$_[0]%100,$$_[0],$$_[1]?'NSFW':'Safe'), @{$_[0]} } ],
+ [ screenshots => 'Screenshots', sub { join "<br />\n", map sprintf('<a href="%s/sf/%02d/%d.jpg">%2$d</a> (%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}};
]]-
<div id="screenshots">
[[ for(@{$d{vn}{screenshots}}) { ]]
- <a href="[[= sprintf '%s/sf/%02d/%d.jpg', $p{st}, $$_[0]%100, $$_[0] ]]"[[= $$_[1] ? ' class="scr_nsfw"'.(!$p{AuthNsfw}?' style="display: none"':'') : '' ]]
- ><img src="[[= sprintf '%s/st/%02d/%d.jpg', $p{st}, $$_[0]%100, $$_[0] ]]"
- /><b>[[= $$_[1] ? 'x' : '&nbsp;' ]]</b></a>
+ <a href="[[= sprintf '%s/sf/%02d/%d.jpg', $p{st}, $$_{id}%100, $$_{id} ]]"
+ [[= $$_{nsfw} ? ' class="scr_nsfw"'.(!$p{AuthNsfw}?' style="display: none"':'') : '' ]]-
+ rel="[[= $$_{width}.'x'.$$_{height} ]]"><img src="[[= sprintf '%s/st/%02d/%d.jpg', $p{st}, $$_{id}%100, $$_{id} ]]"
+ /><b>[[= $$_{nsfw} ? 'x' : '&nbsp;' ]]</b></a>
[[ } ]]-
</div>
[[ if($nsfw) { ]]-
@@ -25,7 +26,6 @@
<a href="#" id="scrprev">&lt;- previous</a>
<a href="#" id="scrnext">next -&gt;</a>
</div>
-<img id="preload" onload="scrPosition()" />
[[ } else { ]]-
<p>
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 ? '<a href="javascript:scrNsfwHid()">Show</a> / hide' : 'Show / <a href="javascript:scrNsfwHid()">hide</a>')
+' 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 = '<img src="'+u+'" onclick="scrClose()" style="width: '+w+'px; height: '+h+'px" />';
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 = '<img src="'+x('preload').src+'" onclick="scrClose()" style="width: '+w+'px; height: '+h+'px" />';
+ 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<l.length;i++)
if(l[i].length > 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 };
// <tbody> because IE can't operate on <table>
x('scrfrm').innerHTML = '<table><tbody id="scrTbl"></tbody></table>';
@@ -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+'</b>'
+'<input type="checkbox" name="scrNSFW'+i+'" id="scrNSFW'+i+'"'+(scrL[i].nsfw?' checked="checked"':'')+' style="float: left" onclick="scrSer()" /> '
+'<label for="scrNSFW'+i+'" class="checkbox">&nbsp;This screenshot is NSFW.</label>'
- +'<input type="button" value="remove" onclick="scrDel('+i+')" style="float: right; width: auto; height: auto" />';
+ +'<input type="button" value="remove" onclick="scrDel('+i+')" style="float: right; width: auto; height: auto" />'
+ +'<br /><br />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<scrL.length;i++)
+ if(scrL[i] && scrL[i].load == 2)
+ ids+=(ids?';':'')+'id='+scrL[i].id;
+ if(!ids)
return;
- scrL[i].timer = setTimeout(function() {
- if(!scrL[i].load)
+ ajax('/xml/screenshots.xml?'+ids+';r='+(Math.floor(Math.random()*999)+1), function () {
+ if(!hr || hr.readyState != 4 || !hr.responseText)
return;
- x('scrTr'+i).getElementsByTagName('td')[0].innerHTML =
- '<img src="'+scrURL(scrL[i].id, 't')+'?'+(Math.floor(Math.random()*999)+1)+'" onload="scrImageSuccess('+i+')"'
- +' onerror="scrImageFail('+i+')" style="visibility: hidden; width: 0px; height: 0px;" id="scrImage'+i+'" />';
- setTimeout('scrImageFail('+i+')', 7000);
- }, 2000);
-}
-
-function scrImageSuccess(i) {
- scrL[i].load = 0;
- x('scrImage'+i).style.cssText = 'margin: 0; padding: 0; border: 0;';
- scrGenerateTR(i);
- scrSer();
+ if(hr.status != 200)
+ return alert('Whoops, error! :(');
+ var l = hr.responseXML.getElementsByTagName('image');
+ for(var s=0;s<l.length;s++) {
+ for(i=0;i<scrL.length;i++)
+ if(scrL[i] && scrL[i].id == l[s].getAttribute('id') && l[s].getAttribute('status') > 0) {
+ scrL[i].load = 0;
+ scrL[i].width = l[s].getAttribute('width');
+ scrL[i].height = l[s].getAttribute('height');
+ x('scrTr'+i).getElementsByTagName('td')[0].innerHTML =
+ '<img src="'+scrURL(scrL[i].id, 't')+'" style="margin: 0; padding: 0; border: 0" />';
+ scrGenerateTR(i);
+ scrSer();
+ }
+ }
+ });
}
function scrDel(i) {
diff --git a/static/files/style.css b/static/files/style.css
index 65e02102..1da16c17 100644
--- a/static/files/style.css
+++ b/static/files/style.css
@@ -651,7 +651,6 @@ div#scrView {
#scrclose { float: right; padding-left: 10px; }
#scrnext { padding-left: 5px; }
#scrprev { padding-right: 5px; }
-#preload { visibility: hidden; position: absolute; left: -9000px; top: -9000px }
diff --git a/util/dump.sql b/util/dump.sql
index f2db8bc2..a96365ac 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -123,6 +123,14 @@ CREATE TABLE rlists (
PRIMARY KEY(uid, rid)
);
+-- screenshots
+CREATE TABLE screenshots (
+ id SERIAL NOT NULL PRIMARY KEY,
+ status smallint NOT NULL DEFAULT 0,
+ width smallint NOT NULL DEFAULT 0,
+ height smallint NOT NULL DEFAULT 0
+);
+
-- threads
CREATE TABLE threads (
id SERIAL NOT NULL PRIMARY KEY,
@@ -286,7 +294,8 @@ ALTER TABLE vn_relations ADD FOREIGN KEY (vid1) REFERENCES vn_rev
ALTER TABLE vn_relations ADD FOREIGN KEY (vid2) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn_rev ADD FOREIGN KEY (id) REFERENCES changes (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn_rev ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE vn_screenshots ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn_screenshots ADD FOREIGN KEY (vid) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn_screenshots ADD FOREIGN KEY (scr) REFERENCES screenshots (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vnlists ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vnlists ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE votes ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED;
@@ -388,10 +397,9 @@ $$ LANGUAGE plpgsql;
---------------------------------
--- Sequences used for cover, relation graph and screenshot ID numbers
+-- Sequences used for ID generation of items not in the DB
CREATE SEQUENCE covers_seq;
CREATE SEQUENCE relgraph_seq;
-CREATE SEQUENCE screenshots_seq;
INSERT INTO users (id, username, mail, rank)
VALUES (0, 'deleted', 'del@vndb.org', 0);
diff --git a/util/updates/update_1.21.sql b/util/updates/update_1.21.sql
index c8457498..176f9056 100644
--- a/util/updates/update_1.21.sql
+++ b/util/updates/update_1.21.sql
@@ -1,12 +1,16 @@
-- screenshots
---CREATE SEQUENCE screenshots_seq;
+CREATE TABLE screenshots (
+ id SERIAL NOT NULL PRIMARY KEY,
+ status smallint NOT NULL DEFAULT 0, -- 0:unprocessed, 1:processed, <0:error (unimplemented)
+ width smallint NOT NULL DEFAULT 0,
+ height smallint NOT NULL DEFAULT 0
+) WITHOUT OIDS;
CREATE TABLE vn_screenshots (
- vid integer NOT NULL DEFAULT 0 REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED,
- scr integer NOT NULL DEFAULT 0,
+ vid integer NOT NULL DEFAULT 0 REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED,
+ scr integer NOT NULL DEFAULT 0 REFERENCES screenshots (id) DEFERRABLE INITIALLY DEFERRED,
nsfw smallint NOT NULL DEFAULT 0,
PRIMARY KEY(vid, scr)
) WITHOUT OIDS;
-