SQL: Use global "images" table for metadata of all images in the DB

In preparation for an image flagging feature. This replaces the
'screenshots' table.

I played around with various implementations of a primary key for the
images table; A single integer encoding both the type and id is most
efficient, but kind of ugly to work with. Two separate columns are also
ugly and force the creation of a separate 'image_type' column on all
referencing tables. The current composite type isn't all that much
better, but at least it makes it easy to keep treating the image id as a
single entity. Old VNDB::* code still treats it as an integer with
different namespaces, new VNWeb::* code treats it as a proper composite
type.

8 files changed, 138 insertions, 48 deletions

diff --git a/util/dbdump.pl b/util/dbdump.pl
index 8f0cc5f8..f2f5d4ca 100755
--- a/util/dbdump.pl
+++ b/util/dbdump.pl
@@ -56,6 +56,9 @@ my %tables = (
                                 .' AND (rid IS NULL OR rid IN(SELECT id FROM releases WHERE NOT hidden))'
                            , order => 'id, vid, rid' },
     docs                => { where => 'NOT hidden' },
+    images              => { where => 'id IN(SELECT scr FROM vn_screenshots vs JOIN vn v ON v.id = vs.id WHERE NOT v.hidden'
+                                    .' UNION SELECT image FROM chars WHERE image IS NOT NULL AND NOT hidden'
+                                    .' UNION SELECT image from vn WHERE image IS NOT NULL AND NOT hidden)' },
     producers           => { where => 'NOT hidden' },
     producers_relations => { where => 'id IN(SELECT id FROM producers WHERE NOT hidden)' },
     releases            => { where => 'NOT hidden' },
@@ -70,7 +73,6 @@ my %tables = (
                                                     .' JOIN ulist_vns_labels uvl ON uvl.vid = rv.vid'
                                                     .' JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl'
                                                    .' WHERE r.id = rlists.rid AND uvl.uid = rlists.uid AND NOT r.hidden AND NOT v.hidden AND NOT ul.private)' },
-    screenshots         => { where => 'id IN(SELECT scr FROM vn_screenshots vs JOIN vn v ON v.id = vs.id WHERE NOT v.hidden)' },
     staff               => { where => 'NOT hidden' },
     staff_alias         => { where => 'id IN(SELECT id FROM staff WHERE NOT hidden)' },
     tags                => { where => 'state = 2' },
@@ -169,6 +171,7 @@ sub export_import_script {
     _
 
     print $F "\n\n";
+    print $F "$types->{image_type}{decl}\n";
     my %types = map +($_->{type}, 1), grep $_->{pub}, map @{$schema->{$_->{name}}{cols}}, @tables;
     print $F "$types->{$_}{decl}\n" for (sort grep $types->{$_}, keys %types);
 
@@ -247,9 +250,9 @@ sub export_img {
 
     my %scr;
     my %dir = (ch => {}, cv => {}, sf => \%scr, st => \%scr);
-    $dir{sf}{$_->[0]} = 1 for $db->selectall_array("SELECT id FROM screenshots WHERE $tables{screenshots}{where} ORDER BY id");
-    $dir{cv}{$_->[0]} = 1 for $db->selectall_array("SELECT image FROM vn WHERE image <> 0 AND $tables{vn}{where} ORDER BY image");
-    $dir{ch}{$_->[0]} = 1 for $db->selectall_array("SELECT image FROM chars WHERE image <> 0 AND $tables{chars}{where} ORDER BY image");
+    $dir{sf}{$_->[0]} = 1 for $db->selectall_array("SELECT (scr).id FROM vn_screenshots WHERE $tables{vn_screenshots}{where}");
+    $dir{cv}{$_->[0]} = 1 for $db->selectall_array("SELECT (image).id FROM vn WHERE image IS NOT NULL AND $tables{vn}{where}");
+    $dir{ch}{$_->[0]} = 1 for $db->selectall_array("SELECT (image).id FROM chars WHERE image IS NOT NULL AND $tables{chars}{where}");
     $db->rollback;
     undef $db;
 
diff --git a/util/devdump.pl b/util/devdump.pl
index 1134e7b7..5fb4a916 100755
--- a/util/devdump.pl
+++ b/util/devdump.pl
@@ -8,6 +8,12 @@ use warnings;
 use autodie;
 use DBI;
 use DBD::Pg;
+use Cwd 'abs_path';
+
+my $ROOT;
+BEGIN { ($ROOT = abs_path $0) =~ s{/util/devdump\.pl$}{}; }
+
+use lib $ROOT.'/lib';
 
 my $db = DBI->connect('dbi:Pg:dbname=vndb', 'vndb', undef, { RaiseError => 1 });
 
@@ -29,7 +35,11 @@ my $characters = $db->selectcol_arrayref(
    ."UNION "
    ."SELECT DISTINCT h.main FROM chars_vns_hist e JOIN changes c ON c.id = e.chid JOIN chars_hist h ON h.chid = e.chid WHERE e.vid IN($vids) AND h.main IS NOT NULL"
 );
-
+my $images = $db->selectcol_arrayref(q{
+         SELECT image FROM chars_hist          ch JOIN changes c ON c.id = ch.chid WHERE c.type = 'c' AND c.itemid IN(}.join(',',@$characters).qq{) AND ch.image IS NOT NULL
+   UNION SELECT image FROM vn_hist             vh JOIN changes c ON c.id = vh.chid WHERE c.type = 'v' AND c.itemid IN($vids) AND vh.image IS NOT NULL
+   UNION SELECT scr   FROM vn_screenshots_hist vs JOIN changes c ON c.id = vs.chid WHERE c.type = 'v' AND c.itemid IN($vids)
+});
 
 
 # Helper function to copy a table or SQL statement. Can do modifications on a
@@ -121,6 +131,9 @@ sub copy_entry {
     # Wikidata (TODO: This could be a lot more selective)
     copy 'wikidata';
 
+    # Image metadata
+    copy images => 'SELECT * FROM images WHERE id IN('.join(',',map "'$_'", @$images).')';
+
     # Threads (announcements)
     my $threads = join ',', @{ $db->selectcol_arrayref("SELECT tid FROM threads_boards b WHERE b.type = 'an'") };
     copy threads        => "SELECT * FROM threads WHERE id IN($threads)";
@@ -141,7 +154,6 @@ sub copy_entry {
     copy_entry c => [qw/chars chars_traits chars_vns/], $characters;
 
     # Visual novels
-    copy screenshots => "SELECT DISTINCT s.* FROM screenshots s JOIN vn_screenshots_hist v ON v.scr = s.id JOIN changes c ON c.id = v.chid WHERE c.type = 'v' AND c.itemid IN($vids)";
     copy anime       => "SELECT DISTINCT a.* FROM anime a JOIN vn_anime_hist v ON v.aid = a.id JOIN changes c ON c.id = v.chid WHERE c.type = 'v' AND c.itemid IN($vids)";
     copy relgraphs   => "SELECT DISTINCT ON (r.id) r.* FROM relgraphs r JOIN vn v ON v.rgraph = r.id WHERE v.id IN($vids)", {};
     copy_entry v     => [qw/vn vn_anime vn_seiyuu vn_staff vn_relations vn_screenshots/], \@vids;
@@ -181,11 +193,8 @@ sub copy_entry {
 
 
 # Now figure out which images we need, and throw everything in a tarball
-sub imgs { map sprintf('static/%s/%02d/%d.jpg', $_[0], $_%100, $_), @{$_[1]} }
-
-my $ch = $db->selectcol_arrayref("SELECT DISTINCT e.image FROM chars_hist          e JOIN changes c ON c.id = e.chid WHERE c.type = 'c' AND e.image <> 0 AND c.itemid IN(".join(',', @$characters).")");
-my $cv = $db->selectcol_arrayref("SELECT DISTINCT e.image FROM vn_hist             e JOIN changes c ON c.id = e.chid WHERE c.type = 'v' AND e.image <> 0 AND c.itemid IN($vids)");
-my $sf = $db->selectcol_arrayref("SELECT DISTINCT e.scr   FROM vn_screenshots_hist e JOIN changes c ON c.id = e.chid WHERE c.type = 'v' AND c.itemid IN($vids)");
+sub img { sprintf 'static/%s/%02d/%d.jpg', $_[0], $_[1]%100, $_[1] }
+my @imgpaths = sort map { my($t,$id) = /\((.+),(.+)\)/; (img($t, $id), $t eq 'sf' ? img('st', $id) : ()) } @$images;
 
-system("tar -czf devdump.tar.gz dump.sql ".join ' ', imgs(ch => $ch), imgs(cv => $cv), imgs(sf => $sf), imgs(st => $sf));
+system("tar -czf devdump.tar.gz dump.sql ".join ' ', @imgpaths);
 unlink 'dump.sql';
diff --git a/util/sql/perms.sql b/util/sql/perms.sql
index b4833a60..7410a688 100644
--- a/util/sql/perms.sql
+++ b/util/sql/perms.sql
@@ -3,7 +3,7 @@
 DROP OWNED BY vndb_site;
 GRANT CONNECT, TEMP ON DATABASE :DBNAME TO vndb_site;
 GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO vndb_site;
-GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO vndb_site;
+GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO vndb_site;
 
 GRANT SELECT, INSERT                 ON anime                    TO vndb_site;
 GRANT SELECT, INSERT                 ON changes                  TO vndb_site;
@@ -15,6 +15,7 @@ GRANT SELECT, INSERT,         DELETE ON chars_vns                TO vndb_site;
 GRANT SELECT, INSERT                 ON chars_vns_hist           TO vndb_site;
 GRANT SELECT, INSERT, UPDATE         ON docs                     TO vndb_site;
 GRANT SELECT, INSERT                 ON docs_hist                TO vndb_site;
+GRANT SELECT, INSERT, UPDATE         ON images                   TO vndb_site;
 GRANT SELECT, INSERT, UPDATE, DELETE ON login_throttle           TO vndb_site;
 GRANT SELECT, INSERT, UPDATE, DELETE ON notifications            TO vndb_site;
 GRANT SELECT, INSERT, UPDATE         ON producers                TO vndb_site;
@@ -36,7 +37,6 @@ GRANT SELECT, INSERT,         DELETE ON releases_vn              TO vndb_site;
 GRANT SELECT, INSERT                 ON releases_vn_hist         TO vndb_site;
 GRANT SELECT                         ON relgraphs                TO vndb_site;
 GRANT SELECT, INSERT, UPDATE, DELETE ON rlists                   TO vndb_site;
-GRANT SELECT, INSERT, UPDATE         ON screenshots              TO vndb_site;
 -- No access to the 'sessions' table, managed by the user_* functions.
 GRANT SELECT                         ON shop_denpa               TO vndb_site;
 GRANT SELECT                         ON shop_dlsite              TO vndb_site;
@@ -94,7 +94,7 @@ GRANT SELECT, INSERT                 ON wikidata                 TO vndb_site;
 DROP OWNED BY vndb_multi;
 GRANT CONNECT, TEMP ON DATABASE :DBNAME TO vndb_multi;
 GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO vndb_multi;
-GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO vndb_multi;
+GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO vndb_multi;
 
 GRANT SELECT,         UPDATE         ON anime                    TO vndb_multi;
 GRANT SELECT                         ON changes                  TO vndb_multi;
@@ -104,6 +104,7 @@ GRANT SELECT                         ON chars_traits             TO vndb_multi;
 GRANT SELECT                         ON chars_vns                TO vndb_multi;
 GRANT SELECT                         ON docs                     TO vndb_multi;
 GRANT SELECT                         ON docs_hist                TO vndb_multi;
+GRANT SELECT                         ON images                   TO vndb_multi;
 GRANT SELECT, INSERT, UPDATE, DELETE ON login_throttle           TO vndb_multi;
 GRANT SELECT, INSERT, UPDATE, DELETE ON notifications            TO vndb_multi;
 GRANT SELECT,         UPDATE         ON producers                TO vndb_multi;
@@ -119,7 +120,6 @@ GRANT SELECT                         ON releases_producers       TO vndb_multi;
 GRANT SELECT                         ON releases_vn              TO vndb_multi;
 GRANT SELECT, INSERT, UPDATE, DELETE ON relgraphs                TO vndb_multi;
 GRANT SELECT, INSERT, UPDATE, DELETE ON rlists                   TO vndb_multi;
-GRANT SELECT                         ON screenshots              TO vndb_multi;
 GRANT SELECT (expires)               ON sessions                 TO vndb_multi;
 GRANT                         DELETE ON sessions                 TO vndb_multi;
 GRANT SELECT, INSERT, UPDATE, DELETE ON shop_denpa               TO vndb_multi;
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 28048f5e..9f86b9be 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -68,9 +68,13 @@ CREATE TYPE vn_relation       AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'sid
 CREATE TYPE resolution        AS ENUM ('unknown', 'nonstandard', '640x480', '800x600', '1024x768', '1280x960', '1600x1200', '640x400', '960x600', '960x640', '1024x576', '1024x600', '1024x640', '1280x720', '1280x800', '1366x768', '1600x900', '1920x1080');
 CREATE TYPE session_type      AS ENUM ('web', 'pass', 'mail');
 
+CREATE TYPE image_type        AS ENUM ('ch', 'cv', 'sf');
+CREATE TYPE image_id          AS (itype image_type, id int);
+
 -- Sequences used for ID generation of items not in the DB
 CREATE SEQUENCE covers_seq;
 CREATE SEQUENCE charimg_seq;
+CREATE SEQUENCE screenshots_seq;
 
 
 
@@ -108,7 +112,7 @@ CREATE TABLE chars ( -- dbentry_type=c
   name       varchar(250) NOT NULL DEFAULT '', -- [pub]
   original   varchar(250) NOT NULL DEFAULT '', -- [pub]
   alias      varchar(500) NOT NULL DEFAULT '', -- [pub]
-  image      integer  NOT NULL DEFAULT 0, -- [pub]
+  image      image_id CONSTRAINT chars_image_check CHECK((image).itype = 'ch'), -- [pub]
   "desc"     text     NOT NULL DEFAULT '', -- [pub]
   gender     gender NOT NULL DEFAULT 'unknown', -- [pub]
   s_bust     smallint NOT NULL DEFAULT 0, -- [pub]
@@ -131,7 +135,7 @@ CREATE TABLE chars_hist (
   name       varchar(250) NOT NULL DEFAULT '',
   original   varchar(250) NOT NULL DEFAULT '',
   alias      varchar(500) NOT NULL DEFAULT '',
-  image      integer  NOT NULL DEFAULT 0,
+  image      image_id CONSTRAINT chars_hist_image_check CHECK((image).itype = 'ch'),
   "desc"     text     NOT NULL DEFAULT '',
   gender     gender NOT NULL DEFAULT 'unknown',
   s_bust     smallint NOT NULL DEFAULT 0,
@@ -200,6 +204,13 @@ CREATE TABLE docs_hist (
   html       text -- cache
 );
 
+-- 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]
+);
+
 -- login_throttle
 CREATE TABLE login_throttle (
   ip inet NOT NULL PRIMARY KEY,
@@ -449,13 +460,6 @@ CREATE TABLE rlists (
   PRIMARY KEY(uid, rid)
 );
 
--- screenshots
-CREATE TABLE screenshots (
-  id SERIAL NOT NULL PRIMARY KEY, -- [pub]
-  width smallint NOT NULL DEFAULT 0, -- [pub]
-  height smallint NOT NULL DEFAULT 0 -- [pub]
-);
-
 -- sessions
 CREATE TABLE sessions (
   uid     integer NOT NULL,
@@ -800,7 +804,7 @@ CREATE TABLE vn ( -- dbentry_type=v
   alias      varchar(500) NOT NULL DEFAULT '', -- [pub]
   length     smallint NOT NULL DEFAULT 0, -- [pub]
   img_nsfw   boolean NOT NULL DEFAULT FALSE, -- [pub]
-  image      integer NOT NULL DEFAULT 0, -- [pub]
+  image      image_id CONSTRAINT vn_image_check CHECK((image).itype = 'cv'), -- [pub]
   "desc"     text NOT NULL DEFAULT '', -- [pub]
   l_wp       varchar(150) NOT NULL DEFAULT '', -- [pub] (deprecated)
   l_encubed  varchar(100) NOT NULL DEFAULT '', -- [pub] (deprecated)
@@ -825,7 +829,7 @@ CREATE TABLE vn_hist (
   alias      varchar(500) NOT NULL DEFAULT '',
   length     smallint NOT NULL DEFAULT 0,
   img_nsfw   boolean NOT NULL DEFAULT FALSE,
-  image      integer NOT NULL DEFAULT 0,
+  image      image_id CONSTRAINT vn_hist_image_check CHECK((image).itype = 'cv'),
   "desc"     text NOT NULL DEFAULT '',
   l_wp       varchar(150) NOT NULL DEFAULT '',
   l_encubed  varchar(100) NOT NULL DEFAULT '',
@@ -868,7 +872,7 @@ CREATE TABLE vn_relations_hist (
 -- vn_screenshots
 CREATE TABLE vn_screenshots (
   id         integer NOT NULL, -- [pub]
-  scr        integer NOT NULL, -- [pub] screenshots.id
+  scr        image_id NOT NULL CONSTRAINT vn_screenshots_scr_check CHECK((scr).itype = 'sf'), -- [pub] images.id
   rid        integer,          -- [pub] releases.id (only NULL for old revisions, nowadays not allowed anymore)
   nsfw       boolean NOT NULL DEFAULT FALSE, -- [pub]
   PRIMARY KEY(id, scr)
@@ -877,7 +881,7 @@ CREATE TABLE vn_screenshots (
 -- vn_screenshots_hist
 CREATE TABLE vn_screenshots_hist (
   chid       integer NOT NULL,
-  scr        integer NOT NULL,
+  scr        image_id NOT NULL CONSTRAINT vn_screenshots_hist_scr_check CHECK((scr).itype = 'sf'),
   rid        integer,
   nsfw       boolean NOT NULL DEFAULT FALSE,
   PRIMARY KEY(chid, scr)
diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql
index 5c894a85..191c78e6 100644
--- a/util/sql/tableattrs.sql
+++ b/util/sql/tableattrs.sql
@@ -2,8 +2,10 @@
 
 ALTER TABLE changes                  ADD CONSTRAINT changes_requester_fkey             FOREIGN KEY (requester) REFERENCES users         (id) ON DELETE SET DEFAULT;
 ALTER TABLE chars                    ADD CONSTRAINT chars_main_fkey                    FOREIGN KEY (main)      REFERENCES chars         (id);
+ALTER TABLE chars                    ADD CONSTRAINT chars_image_fkey                   FOREIGN KEY (image)     REFERENCES images        (id);
 ALTER TABLE chars_hist               ADD CONSTRAINT chars_hist_chid_fkey               FOREIGN KEY (chid)      REFERENCES changes       (id) ON DELETE CASCADE;
 ALTER TABLE chars_hist               ADD CONSTRAINT chars_hist_main_fkey               FOREIGN KEY (main)      REFERENCES chars         (id);
+ALTER TABLE chars_hist               ADD CONSTRAINT chars_hist_image_fkey              FOREIGN KEY (image)     REFERENCES images        (id);
 ALTER TABLE chars_traits             ADD CONSTRAINT chars_traits_id_fkey               FOREIGN KEY (id)        REFERENCES chars         (id);
 ALTER TABLE chars_traits             ADD CONSTRAINT chars_traits_tid_fkey              FOREIGN KEY (tid)       REFERENCES traits        (id);
 ALTER TABLE chars_traits_hist        ADD CONSTRAINT chars_traits_hist_chid_fkey        FOREIGN KEY (chid)      REFERENCES changes       (id) ON DELETE CASCADE;
@@ -75,8 +77,10 @@ ALTER TABLE ulist_vns_labels         ADD CONSTRAINT ulist_vns_labels_vid_fkey
 ALTER TABLE ulist_vns_labels         ADD CONSTRAINT ulist_vns_labels_uid_lbl_fkey      FOREIGN KEY (uid,lbl)   REFERENCES ulist_labels  (uid,id) ON DELETE CASCADE;
 ALTER TABLE ulist_vns_labels         ADD CONSTRAINT ulist_vns_labels_uid_vid_fkey      FOREIGN KEY (uid,vid)   REFERENCES ulist_vns     (uid,vid) ON DELETE CASCADE;
 ALTER TABLE vn                       ADD CONSTRAINT vn_rgraph_fkey                     FOREIGN KEY (rgraph)    REFERENCES relgraphs     (id);
+ALTER TABLE vn                       ADD CONSTRAINT vn_image_fkey                      FOREIGN KEY (image)     REFERENCES images        (id);
 ALTER TABLE vn                       ADD CONSTRAINT vn_l_wikidata_fkey                 FOREIGN KEY (l_wikidata)REFERENCES wikidata      (id);
 ALTER TABLE vn_hist                  ADD CONSTRAINT vn_hist_chid_fkey                  FOREIGN KEY (chid)      REFERENCES changes       (id) ON DELETE CASCADE;
+ALTER TABLE vn_hist                  ADD CONSTRAINT vn_hist_image_fkey                 FOREIGN KEY (image)     REFERENCES images        (id);
 ALTER TABLE vn_hist                  ADD CONSTRAINT vn_hist_l_wikidata_fkey            FOREIGN KEY (l_wikidata)REFERENCES wikidata      (id);
 ALTER TABLE vn_anime                 ADD CONSTRAINT vn_anime_id_fkey                   FOREIGN KEY (id)        REFERENCES vn            (id);
 ALTER TABLE vn_anime                 ADD CONSTRAINT vn_anime_aid_fkey                  FOREIGN KEY (aid)       REFERENCES anime         (id);
@@ -87,10 +91,10 @@ ALTER TABLE vn_relations             ADD CONSTRAINT vn_relations_vid_fkey
 ALTER TABLE vn_relations_hist        ADD CONSTRAINT vn_relations_chid_fkey             FOREIGN KEY (chid)      REFERENCES changes       (id) ON DELETE CASCADE;
 ALTER TABLE vn_relations_hist        ADD CONSTRAINT vn_relations_vid_fkey              FOREIGN KEY (vid)       REFERENCES vn            (id);
 ALTER TABLE vn_screenshots           ADD CONSTRAINT vn_screenshots_id_fkey             FOREIGN KEY (id)        REFERENCES vn            (id);
-ALTER TABLE vn_screenshots           ADD CONSTRAINT vn_screenshots_scr_fkey            FOREIGN KEY (scr)       REFERENCES screenshots   (id);
+ALTER TABLE vn_screenshots           ADD CONSTRAINT vn_screenshots_scr_fkey            FOREIGN KEY (scr)       REFERENCES images        (id);
 ALTER TABLE vn_screenshots           ADD CONSTRAINT vn_screenshots_rid_fkey            FOREIGN KEY (rid)       REFERENCES releases      (id) DEFERRABLE;
 ALTER TABLE vn_screenshots_hist      ADD CONSTRAINT vn_screenshots_hist_chid_fkey      FOREIGN KEY (chid)      REFERENCES changes       (id) ON DELETE CASCADE;
-ALTER TABLE vn_screenshots_hist      ADD CONSTRAINT vn_screenshots_hist_scr_fkey       FOREIGN KEY (scr)       REFERENCES screenshots   (id);
+ALTER TABLE vn_screenshots_hist      ADD CONSTRAINT vn_screenshots_hist_scr_fkey       FOREIGN KEY (scr)       REFERENCES images        (id);
 ALTER TABLE vn_screenshots_hist      ADD CONSTRAINT vn_screenshots_hist_rid_fkey       FOREIGN KEY (rid)       REFERENCES releases      (id) DEFERRABLE;
 ALTER TABLE vn_seiyuu                ADD CONSTRAINT vn_seiyuu_id_fkey                  FOREIGN KEY (id)        REFERENCES vn            (id);
 ALTER TABLE vn_seiyuu                ADD CONSTRAINT vn_seiyuu_aid_fkey                 FOREIGN KEY (aid)       REFERENCES staff_alias   (aid) DEFERRABLE INITIALLY DEFERRED;
diff --git a/util/unusedimages.pl b/util/unusedimages.pl
index b5eb3989..f1a23377 100755
--- a/util/unusedimages.pl
+++ b/util/unusedimages.pl
@@ -24,9 +24,17 @@ my %dir = (cv => \%cv, ch => \%ch, sf => \%scr, st => \%scr);
 
 sub cleandb {
   my $cnt = $db->do(q{
-    DELETE FROM screenshots s
-     WHERE NOT EXISTS(SELECT 1 FROM vn_screenshots_hist WHERE scr = s.id)
-       AND NOT EXISTS(SELECT 1 FROM vn_screenshots WHERE scr = s.id)
+    DELETE FROM images WHERE id IN(
+      SELECT id FROM images EXCEPT
+      SELECT * FROM (
+              SELECT scr   FROM vn_screenshots
+        UNION SELECT scr   FROM vn_screenshots_hist
+        UNION SELECT image FROM vn           WHERE image IS NOT NULL
+        UNION SELECT image FROM vn_hist      WHERE image IS NOT NULL
+        UNION SELECT image FROM chars        WHERE image IS NOT NULL
+        UNION SELECT image FROM chars_hist   WHERE image IS NOT NULL
+      ) x
+    )
   });
   print "# Deleted unreferenced screenshots: $cnt\n";
 }
@@ -50,17 +58,16 @@ sub addtxtsql {
   print "# References in $name... $count\n";
 }
 
-sub addnumsql {
-  my($name, $tbl, $query) = @_;
-  $count = 0;
-  my $st = $db->prepare($query);
+sub addimagessql {
+  my $st = $db->prepare('SELECT (id).itype, (id).id FROM images');
   $st->execute();
+  $count = 0;
   while((my $num = $st->fetch())) {
-    $tbl->{$num->[0]} = 1;
+    $dir{$num->[0]}{$num->[1]} = 1;
     $count++;
   }
-  print "# Items in $name... $count\n";
-}
+  print "# Items in `images'... $count\n";
+};
 
 sub findunused {
   my $size = 0;
@@ -100,7 +107,5 @@ addtxtsql 'Tag descriptions',       'SELECT description FROM tags';
 addtxtsql 'Trait descriptions',     'SELECT description FROM traits';
 addtxtsql 'Change summaries',       'SELECT comments FROM changes';
 addtxtsql 'Posts',                  'SELECT msg FROM threads_posts';
-addnumsql 'Screenshots', \%scr,     'SELECT id FROM screenshots';
-addnumsql 'VN images', \%cv,        'SELECT image FROM vn    UNION ALL SELECT image from vn_hist';
-addnumsql 'Character images', \%ch, 'SELECT image FROM chars UNION ALL SELECT image from chars_hist';
+addimagessql;
 findunused;
diff --git a/util/updates/2020-03-06-images-table.sql b/util/updates/2020-03-06-images-table.sql
new file mode 100644
index 00000000..f3db7975
--- /dev/null
+++ b/util/updates/2020-03-06-images-table.sql
@@ -0,0 +1,58 @@
+CREATE TYPE image_type AS ENUM ('ch', 'cv', 'sf');
+CREATE TYPE image_id AS (itype image_type, id int);
+
+CREATE TABLE images (
+  id       image_id NOT NULL PRIMARY KEY CHECK((id).id IS NOT NULL AND (id).itype IS NOT NULL),
+  width    smallint, -- dimensions are only set for the 'sf' type (for now)
+  height   smallint
+);
+
+BEGIN;
+SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+
+INSERT INTO images (id, width, height)
+   SELECT ROW('sf', id)::image_id, width, height FROM screenshots
+UNION ALL
+   SELECT ROW('cv', image)::image_id, null, null FROM vn_hist WHERE image <> 0 GROUP BY image
+UNION ALL
+   SELECT ROW('ch', image)::image_id, null, null FROM chars_hist WHERE image <> 0 GROUP BY image;
+
+
+ALTER TABLE vn      ALTER COLUMN image DROP NOT NULL;
+ALTER TABLE vn      ALTER COLUMN image DROP DEFAULT;
+ALTER TABLE vn      ALTER COLUMN image TYPE image_id USING CASE WHEN image = 0 THEN NULL ELSE ROW('cv', image)::image_id END;
+ALTER TABLE vn      ADD CONSTRAINT vn_image_fkey      FOREIGN KEY (image) REFERENCES images (id);
+ALTER TABLE vn      ADD CONSTRAINT vn_image_check CHECK((image).itype = 'cv');
+ALTER TABLE vn_hist ALTER COLUMN image DROP NOT NULL;
+ALTER TABLE vn_hist ALTER COLUMN image DROP DEFAULT;
+ALTER TABLE vn_hist ALTER COLUMN image TYPE image_id USING CASE WHEN image = 0 THEN NULL ELSE ROW('cv', image)::image_id END;
+ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_image_fkey      FOREIGN KEY (image) REFERENCES images (id);
+ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_image_check CHECK((image).itype = 'cv');
+
+ALTER TABLE vn_screenshots      DROP CONSTRAINT vn_screenshots_scr_fkey;
+ALTER TABLE vn_screenshots      ALTER COLUMN scr TYPE image_id USING CASE WHEN scr = 0 THEN NULL ELSE ROW('sf', scr)::image_id END;
+ALTER TABLE vn_screenshots      ADD CONSTRAINT vn_screenshots_scr_fkey      FOREIGN KEY (scr) REFERENCES images (id);
+ALTER TABLE vn_screenshots      ADD CONSTRAINT vn_screenshots_scr_check CHECK((scr).itype = 'sf');
+ALTER TABLE vn_screenshots_hist DROP CONSTRAINT vn_screenshots_hist_scr_fkey;
+ALTER TABLE vn_screenshots_hist ALTER COLUMN scr TYPE image_id USING CASE WHEN scr = 0 THEN NULL ELSE ROW('sf', scr)::image_id END;
+ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_scr_fkey      FOREIGN KEY (scr) REFERENCES images (id);
+ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_scr_check CHECK((scr).itype = 'sf');
+
+ALTER TABLE chars      ALTER COLUMN image DROP NOT NULL;
+ALTER TABLE chars      ALTER COLUMN image DROP DEFAULT;
+ALTER TABLE chars      ALTER COLUMN image TYPE image_id USING CASE WHEN image = 0 THEN NULL ELSE ROW('ch', image)::image_id END;
+ALTER TABLE chars      ADD CONSTRAINT chars_image_fkey      FOREIGN KEY (image) REFERENCES images (id);
+ALTER TABLE chars      ADD CONSTRAINT chars_image_check CHECK((image).itype = 'ch');
+ALTER TABLE chars_hist ALTER COLUMN image DROP NOT NULL;
+ALTER TABLE chars_hist ALTER COLUMN image DROP DEFAULT;
+ALTER TABLE chars_hist ALTER COLUMN image TYPE image_id USING CASE WHEN image = 0 THEN NULL ELSE ROW('ch', image)::image_id END;
+ALTER TABLE chars_hist ADD CONSTRAINT chars_hist_image_fkey      FOREIGN KEY (image) REFERENCES images (id);
+ALTER TABLE chars_hist ADD CONSTRAINT chars_hist_image_check CHECK((image).itype = 'ch');
+
+COMMIT;
+
+CREATE SEQUENCE screenshots_seq;
+SELECT setval('screenshots_seq', nextval('screenshots_id_seq'));
+DROP TABLE screenshots;
+
+\i util/sql/perms.sql
diff --git a/util/vndb.pl b/util/vndb.pl
index 405b32a9..0741bfaf 100755
--- a/util/vndb.pl
+++ b/util/vndb.pl
@@ -12,6 +12,7 @@ BEGIN { ($ROOT = abs_path $0) =~ s{/util/vndb\.pl$}{}; }
 
 use lib $ROOT.'/lib';
 use SkinFile;
+use VNDB::Func ();
 use VNDB::Config;
 use VNWeb::Auth;
 use VNWeb::HTML ();
@@ -37,11 +38,17 @@ TUWF::set %{ config->{tuwf} };
 tuwf->{elmgen} = $ARGV[0] && $ARGV[0] eq 'elmgen';
 
 
-# tuwf->imgpath(cg => $image_id)
-sub TUWF::Object::imgpath { sprintf '%s/%s/%02d/%d.jpg', $ROOT, $_[1], $_[2]%100, $_[2] }
+sub _path {
+    my($t, $id) = $_[1] =~ /\(([a-z]+),([0-9]+)\)/;
+    $t = 'st' if $t eq 'sf' && $_[2];
+    sprintf '%s/%s/%02d/%d.jpg', $_[0], $t, $id%100, $id;
+}
+
+# tuwf->imgpath($image_id, $thumb)
+sub TUWF::Object::imgpath { _path $ROOT, $_[1], $_[2] }
 
-# tuwf->imgurl(cv => $image_id)
-sub TUWF::Object::imgurl { sprintf '%s/%s/%02d/%d.jpg', $_[0]->{url_static}, $_[1], $_[2]%100, $_[2] }
+# tuwf->imgurl($image_id, $thumb)
+sub TUWF::Object::imgurl { _path $_[0]{url_static}, $_[1], $_[2] }
 
 
 TUWF::hook before => sub {