SQL: Add C-based "vndbid" type and use it for image IDs

I had already rambled on the current composite type solution in
583ae868dfd3c882a8d2dd40b5d5ed099170c1c2 and I had already explored a
few alternatives. This was the one alternative I hadn't yet explored
because I wasn't sure the operational complexity was going to be worth
it, but after seeing how bad PostgreSQL was at optimizing queries with
composite types, I figured I might as well just go with this approach.

It improves performance of some queries by a *lot* (especially the image
selection query) and it's pretty elegant and convenient to work with.
Only downside is the complexity of compiling, installing and maintaining
a vndbid.so library for PostgreSQL.

6 files changed, 75 insertions, 10 deletions

diff --git a/util/dbdump.pl b/util/dbdump.pl
index 9946ad9e..51503b44 100755
--- a/util/dbdump.pl
+++ b/util/dbdump.pl
@@ -168,10 +168,15 @@ sub export_import_script {
     -- Uncomment to import the schema and data into a separate namespace:
     --CREATE SCHEMA vndb;
     --SET search_path TO vndb;
+
+    -- 'vndbid' is a custom base type used in the VNDB codebase, but it's safe to treat
+    -- it as just text. If you want to use the proper type, load sql/vndbid.sql from
+    -- the VNDB source code into your database and comment out the following line.
+    -- (or ignore the error message about 'vndbid' already existing)
+    CREATE DOMAIN vndbid AS text;
     _
 
     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);
 
@@ -179,7 +184,7 @@ sub export_import_script {
         my $schema = $schema->{$table->{name}};
         print $F "\n";
         print $F "CREATE TABLE \"$table->{name}\" (\n";
-        print $F join ",\n", map "  $_->{decl}" =~ s/" serial/" integer/ir, grep $_->{pub}, @{$schema->{cols}};
+        print $F join ",\n", map "  $_->{decl}" =~ s/" serial/" integer/ir =~ s/ +(?:check|constraint) +.*//ir, grep $_->{pub}, @{$schema->{cols}};
         print $F ",\n  PRIMARY KEY(".join(', ', map "\"$_\"", @{$schema->{primary}}).")" if $schema->{primary};
         print $F "\n);\n";
     }
@@ -250,9 +255,9 @@ sub export_img {
 
     my %scr;
     my %dir = (ch => {}, cv => {}, sf => \%scr, st => \%scr);
-    $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}");
+    $dir{sf}{$_->[0]} = 1 for $db->selectall_array("SELECT vndbid_num(scr) FROM vn_screenshots WHERE $tables{vn_screenshots}{where}");
+    $dir{cv}{$_->[0]} = 1 for $db->selectall_array("SELECT vndbid_num(image) FROM vn WHERE image IS NOT NULL AND $tables{vn}{where}");
+    $dir{ch}{$_->[0]} = 1 for $db->selectall_array("SELECT vndbid_num(image) 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 f1529839..9ee1959f 100755
--- a/util/devdump.pl
+++ b/util/devdump.pl
@@ -134,7 +134,7 @@ sub copy_entry {
     # Image metadata
     my $image_ids = join ',', map "'$_'", @$images;
     copy images => "SELECT * FROM images WHERE id IN($image_ids)";
-    copy image_votes => "SELECT * FROM image_votes WHERE id IN($image_ids)", { uid => 'user' };
+    copy image_votes => "SELECT DISTINCT ON (id,uid%10) * FROM image_votes WHERE id IN($image_ids)", { uid => 'user' };
 
     # Threads (announcements)
     my $threads = join ',', @{ $db->selectcol_arrayref("SELECT tid FROM threads_boards b WHERE b.type = 'an'") };
@@ -181,6 +181,7 @@ sub copy_entry {
     print "SELECT update_stats_cache_full();\n";
     print "SELECT update_vnvotestats();\n";
     print "SELECT update_users_ulist_stats(NULL);\n";
+    print "SELECT update_images_cache(NULL);\n";
     print "UPDATE users u SET c_tags = (SELECT COUNT(*) FROM tags_vn v WHERE v.uid = u.id);\n";
     print "UPDATE users u SET c_changes = (SELECT COUNT(*) FROM changes c WHERE c.requester = u.id);\n";
 
@@ -196,7 +197,7 @@ sub copy_entry {
 
 # Now figure out which images we need, and throw everything in a tarball
 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;
+my @imgpaths = sort map { my($t,$id) = /([a-z]+)([0-9]+)/; (img($t, $id), $t eq 'sf' ? img('st', $id) : ()) } @$images;
 
 system("tar -czf devdump.tar.gz dump.sql ".join ' ', @imgpaths);
 unlink 'dump.sql';
diff --git a/util/docker-init.sh b/util/docker-init.sh
index e1cf1b68..42ff99a9 100755
--- a/util/docker-init.sh
+++ b/util/docker-init.sh
@@ -37,6 +37,12 @@ mkdevuser() {
 }
 
 
+# Should run as root
+installvndbid() {
+    make -C /var/www/sql/c install || exit
+}
+
+
 # Should run as devuser
 pg_start() {
     if [ ! -d /var/www/data/docker-pg/12 ]; then
@@ -67,6 +73,7 @@ pg_start() {
 
     make sql/editfunc.sql
     psql postgres -f sql/superuser_init.sql
+    psql -U devuser vndb -f sql/vndbid.sql
     echo "ALTER ROLE vndb       LOGIN" | psql postgres
     echo "ALTER ROLE vndb_site  LOGIN" | psql postgres
     echo "ALTER ROLE vndb_multi LOGIN" | psql postgres
@@ -102,6 +109,7 @@ devshell() {
 case "$1" in
     '')
         mkdevuser
+        installvndbid
         su devuser -c '/var/www/util/docker-init.sh pg_start'
         exec su devuser -c '/var/www/util/docker-init.sh devshell'
         ;;
diff --git a/util/unusedimages.pl b/util/unusedimages.pl
index f1a23377..3b81c7ae 100755
--- a/util/unusedimages.pl
+++ b/util/unusedimages.pl
@@ -36,7 +36,7 @@ sub cleandb {
       ) x
     )
   });
-  print "# Deleted unreferenced screenshots: $cnt\n";
+  print "# Deleted unreferenced images: $cnt\n";
 }
 
 sub addtxt {
@@ -59,7 +59,7 @@ sub addtxtsql {
 }
 
 sub addimagessql {
-  my $st = $db->prepare('SELECT (id).itype, (id).id FROM images');
+  my $st = $db->prepare('SELECT vndbid_type(id), vndbid_num(id) FROM images');
   $st->execute();
   $count = 0;
   while((my $num = $st->fetch())) {
diff --git a/util/updates/2020-04-05-vndbid-for-images.sql b/util/updates/2020-04-05-vndbid-for-images.sql
new file mode 100644
index 00000000..df85313b
--- /dev/null
+++ b/util/updates/2020-04-05-vndbid-for-images.sql
@@ -0,0 +1,51 @@
+-- Make sure to import sql/vndbid.sql before running this script.
+
+ALTER TABLE chars                    DROP CONSTRAINT chars_image_fkey;
+ALTER TABLE chars_hist               DROP CONSTRAINT chars_hist_image_fkey;
+ALTER TABLE image_votes              DROP CONSTRAINT image_votes_id_fkey;
+ALTER TABLE vn                       DROP CONSTRAINT vn_image_fkey;
+ALTER TABLE vn_hist                  DROP CONSTRAINT vn_hist_image_fkey;
+ALTER TABLE vn_screenshots           DROP CONSTRAINT vn_screenshots_scr_fkey;
+ALTER TABLE vn_screenshots_hist      DROP CONSTRAINT vn_screenshots_hist_scr_fkey;
+
+ALTER TABLE chars                    DROP CONSTRAINT chars_image_check;
+ALTER TABLE chars_hist               DROP CONSTRAINT chars_hist_image_check;
+ALTER TABLE images                   DROP CONSTRAINT images_id_check;
+ALTER TABLE vn                       DROP CONSTRAINT vn_image_check;
+ALTER TABLE vn_hist                  DROP CONSTRAINT vn_hist_image_check;
+ALTER TABLE vn_screenshots           DROP CONSTRAINT vn_screenshots_scr_check;
+ALTER TABLE vn_screenshots_hist      DROP CONSTRAINT vn_screenshots_hist_scr_check;
+
+ALTER TABLE chars                    ALTER COLUMN image TYPE vndbid USING vndbid((image).itype::text, (image).id);
+ALTER TABLE chars_hist               ALTER COLUMN image TYPE vndbid USING vndbid((image).itype::text, (image).id);
+ALTER TABLE images                   ALTER COLUMN id    TYPE vndbid USING vndbid((id).itype::text, (id).id);
+ALTER TABLE image_votes              ALTER COLUMN id    TYPE vndbid USING vndbid((id).itype::text, (id).id);
+ALTER TABLE vn                       ALTER COLUMN image TYPE vndbid USING vndbid((image).itype::text, (image).id);
+ALTER TABLE vn_hist                  ALTER COLUMN image TYPE vndbid USING vndbid((image).itype::text, (image).id);
+ALTER TABLE vn_screenshots           ALTER COLUMN scr   TYPE vndbid USING vndbid((scr).itype::text, (scr).id);
+ALTER TABLE vn_screenshots_hist      ALTER COLUMN scr   TYPE vndbid USING vndbid((scr).itype::text, (scr).id);
+
+ALTER TABLE chars                    ADD CONSTRAINT chars_image_check             CHECK(vndbid_type(image) = 'ch');
+ALTER TABLE chars_hist               ADD CONSTRAINT chars_hist_image_check        CHECK(vndbid_type(image) = 'ch');
+ALTER TABLE images                   ADD CONSTRAINT images_id_check               CHECK(vndbid_type(id) IN('ch', 'cv', 'sf'));
+ALTER TABLE vn                       ADD CONSTRAINT vn_image_check                CHECK(vndbid_type(image) = 'cv');
+ALTER TABLE vn_hist                  ADD CONSTRAINT vn_hist_image_check           CHECK(vndbid_type(image) = 'cv');
+ALTER TABLE vn_screenshots           ADD CONSTRAINT vn_screenshots_scr_check      CHECK(vndbid_type(scr) = 'sf');
+ALTER TABLE vn_screenshots_hist      ADD CONSTRAINT vn_screenshots_hist_scr_check CHECK(vndbid_type(scr) = 'sf');
+
+ALTER TABLE chars                    ADD CONSTRAINT chars_image_fkey                   FOREIGN KEY (image)     REFERENCES images        (id);
+ALTER TABLE chars_hist               ADD CONSTRAINT chars_hist_image_fkey              FOREIGN KEY (image)     REFERENCES images        (id);
+ALTER TABLE image_votes              ADD CONSTRAINT image_votes_id_fkey                FOREIGN KEY (id)        REFERENCES images        (id) ON DELETE CASCADE;
+ALTER TABLE vn                       ADD CONSTRAINT vn_image_fkey                      FOREIGN KEY (image)     REFERENCES images        (id);
+ALTER TABLE vn_hist                  ADD CONSTRAINT vn_hist_image_fkey                 FOREIGN KEY (image)     REFERENCES images        (id);
+ALTER TABLE vn_screenshots           ADD CONSTRAINT vn_screenshots_scr_fkey            FOREIGN KEY (scr)       REFERENCES images        (id);
+ALTER TABLE vn_screenshots_hist      ADD CONSTRAINT vn_screenshots_hist_scr_fkey       FOREIGN KEY (scr)       REFERENCES images        (id);
+
+DROP FUNCTION update_images_cache(image_id);
+
+\i sql/func.sql
+
+DROP TYPE image_id;
+DROP TYPE image_type;
+
+ANALYZE images, image_votes;
diff --git a/util/vndb.pl b/util/vndb.pl
index b7aedb27..d926a04e 100755
--- a/util/vndb.pl
+++ b/util/vndb.pl
@@ -39,7 +39,7 @@ tuwf->{elmgen} = $ARGV[0] && $ARGV[0] eq 'elmgen';
 
 
 sub _path {
-    my($t, $id) = $_[1] =~ /\(([a-z]+),([0-9]+)\)/;
+    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;
 }