SQL: Keep track of dimensions for all images in the DB

Ought to simplify the image flagging UI.

7 files changed, 42 insertions, 27 deletions

diff --git a/lib/VNDB/DB/Chars.pm b/lib/VNDB/DB/Chars.pm
index 047f39b5..7ee6d86b 100644
--- a/lib/VNDB/DB/Chars.pm
+++ b/lib/VNDB/DB/Chars.pm
@@ -5,7 +5,7 @@ use strict;
 use warnings;
 use Exporter 'import';
 
-our @EXPORT = qw|dbCharFilters dbCharGet dbCharGetRev dbCharRevisionInsert dbCharImageId|;
+our @EXPORT = qw|dbCharFilters dbCharGet dbCharGetRev dbCharRevisionInsert|;
 
 
 # Character filters shared by dbCharGet and dbVNGet
@@ -194,11 +194,5 @@ sub dbCharRevisionInsert {
 }
 
 
-# fetches an ID for a new image
-sub dbCharImageId {
-  return shift->dbRow(q|INSERT INTO images (id) VALUES (ROW('ch', nextval('charimg_seq'))::image_id) RETURNING (id).id|)->{id};
-}
-
-
 1;
 
diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm
index cd290d61..73e3e71f 100644
--- a/lib/VNDB/DB/Misc.pm
+++ b/lib/VNDB/DB/Misc.pm
@@ -6,7 +6,7 @@ use warnings;
 use Exporter 'import';
 
 our @EXPORT = qw|
-  dbStats dbItemEdit dbRevisionGet dbWikidata
+  dbStats dbItemEdit dbRevisionGet dbWikidata dbImageAdd
 |;
 
 
@@ -115,5 +115,13 @@ sub dbWikidata {
 }
 
 
+# insert a new image and return its ID
+sub dbImageAdd {
+  my($s, $type, $width, $height) = @_;
+  my $seq = {qw/sf screenshots_seq cv covers_seq ch charimg_seq/}->{$type}||die;
+  return $s->dbRow(q|INSERT INTO images (id, width, height) VALUES (ROW(?, nextval(?))::image_id, ?, ?) RETURNING (id).id|, $type, $seq, $width, $height)->{id};
+}
+
+
 1;
 
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index 3615d918..e8f3666e 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -8,7 +8,7 @@ use POSIX 'strftime';
 use Exporter 'import';
 use VNDB::Func 'normalize_query', 'gtintype';
 
-our @EXPORT = qw|dbVNGet dbVNGetRev dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreenshotGet dbScreenshotRandom|;
+our @EXPORT = qw|dbVNGet dbVNGetRev dbVNRevisionInsert dbScreenshotGet dbScreenshotRandom|;
 
 
 # Options: id, char, search, gtin, length, lang, olang, plat, tag_inc, tag_exc, tagspoil,
@@ -318,19 +318,6 @@ sub dbVNRevisionInsert {
 }
 
 
-# fetches an ID for a new image
-sub dbVNImageId {
-  return shift->dbRow(q|INSERT INTO images (id) VALUES (ROW('cv', nextval('covers_seq'))::image_id) RETURNING (id).id|)->{id};
-}
-
-
-# insert a new screenshot and return it's ID
-sub dbScreenshotAdd {
-  my($s, $width, $height) = @_;
-  return $s->dbRow(q|INSERT INTO images (id, width, height) VALUES (ROW('sf', nextval('screenshots_seq'))::image_id, ?, ?) RETURNING (id).id|, $width, $height)->{id};
-}
-
-
 # arrayref of screenshot IDs as argument
 sub dbScreenshotGet {
   return shift->dbAll(q|SELECT (id).id, width, height FROM images WHERE id IN(SELECT ROW('sf', n::integer)::image_id FROM unnest(ARRAY[!l]) x(n))|, shift);
diff --git a/lib/VNDB/Handler/Chars.pm b/lib/VNDB/Handler/Chars.pm
index a7a8d801..d1544981 100644
--- a/lib/VNDB/Handler/Chars.pm
+++ b/lib/VNDB/Handler/Chars.pm
@@ -426,7 +426,7 @@ sub _uploadimage {
   $im->Set(magick => 'JPEG', quality => 90);
 
   # Get ID and save
-  my $imgid = $self->dbCharImageId;
+  my $imgid = $self->dbImageAdd(ch => $nw, $nh);
   my $fn = imgpath(ch => $imgid);
   $im->Write($fn);
   chmod 0666, $fn;
diff --git a/lib/VNDB/Handler/VNEdit.pm b/lib/VNDB/Handler/VNEdit.pm
index 932a07f9..49e383a7 100644
--- a/lib/VNDB/Handler/VNEdit.pm
+++ b/lib/VNDB/Handler/VNEdit.pm
@@ -244,7 +244,7 @@ sub _uploadimage {
   $im->Set(quality => 90);
 
   # Get ID and save
-  my $imgid = $self->dbVNImageId;
+  my $imgid = $self->dbImageAdd(cv => $nw, $nh);
   my $fn = imgpath(cv => $imgid);
   $im->Write($fn);
   chmod 0666, $fn;
@@ -517,7 +517,7 @@ sub scrxml {
     $im->Set(quality => 90);
     ($ow, $oh) = ($im->Get('width'), $im->Get('height'));
 
-    $id = $self->dbScreenshotAdd($ow, $oh);
+    $id = $self->dbImageAdd(sf => $ow, $oh);
     my $fn = imgpath(sf => $id);
     $im->Write($fn);
     chmod 0666, $fn;
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 9f86b9be..89d8431e 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -207,8 +207,8 @@ CREATE TABLE docs_hist (
 -- images
 CREATE TABLE images (
   id       image_id NOT NULL PRIMARY KEY, -- [pub]
-  width    smallint, -- [pub] dimensions are only set for the 'sf' type (for now)
-  height   smallint -- [pub]
+  width    smallint NOT NULL, -- [pub]
+  height   smallint NOT NULL -- [pub]
 );
 
 -- login_throttle
diff --git a/util/updates/2020-03-12-image-sizes.pl b/util/updates/2020-03-12-image-sizes.pl
new file mode 100755
index 00000000..2855c581
--- /dev/null
+++ b/util/updates/2020-03-12-image-sizes.pl
@@ -0,0 +1,26 @@
+#!/usr/bin/perl
+
+use v5.26;
+use warnings;
+use DBI;
+use Image::Magick;
+
+my $db = DBI->connect('dbi:Pg:dbname=vndb', 'vndb', undef, { RaiseError => 1, AutoCommit => 0 });
+
+my $upd = $db->prepare('UPDATE images SET width = ?, height = ? WHERE id = ?::image_id');
+
+for my $id ($db->selectcol_arrayref('SELECT id FROM images WHERE width IS NULL')->@*) {
+    my($t,$n) = $id =~ /\(([a-z]+),([0-9]+)\)/;
+    my $f = sprintf 'static/%s/%02d/%d.jpg', $t, $n%100, $n;
+    my $im = Image::Magick->new;
+    my $e = $im->Read($f);
+    warn "$f: $e\n" if $e;
+    $upd->execute($im->Get('width'), $im->Get('height'), $id) if !$e;
+}
+
+# A few images have been permanently deleted, that's alright, not being used anyway.
+$db->do('UPDATE images SET width = 0, height = 0 WHERE width IS NULL');
+
+$db->do('ALTER TABLE images ALTER COLUMN width SET NOT NULL');
+$db->do('ALTER TABLE images ALTER COLUMN height SET NOT NULL');
+$db->commit;