SQL: vndbid data type conversion for most DB entries

I had wanted to split this up into multiple commits and roll out in
stages, but couldn't really find a natural way to do so. There are
several places that take a generic identifier and expect it to work the
same for all entries they support, so changing one entry at a time
wasn't going to be any easier. Only the tags & traits haven't been
updated yet, I'll convert those later.

While this is a major change and affects a lot of code, the individual
changes are all pretty simple. I'm surprised how much code did not have
to be updated at all. No doubt I've missed a few places, though, so this
commit will almost certainly break something.

6 files changed, 310 insertions, 55 deletions

diff --git a/util/dbdump.pl b/util/dbdump.pl
index 3a088804..1695d911 100755
--- a/util/dbdump.pl
+++ b/util/dbdump.pl
@@ -201,7 +201,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 =~ s/ +(?:check|constraint) +.*//ir, grep $_->{pub}, @{$schema->{cols}};
+        print $F join ",\n", map "  $_->{decl}" =~ s/" serial/" integer/ir =~ s/ +(?:check|constraint|default) +.*//ir, grep $_->{pub}, @{$schema->{cols}};
         print $F ",\n  PRIMARY KEY(".join(', ', map "\"$_\"", @{$schema->{primary}}).")" if $schema->{primary};
         print $F "\n);\n";
     }
@@ -331,7 +331,7 @@ sub export_votes {
 
     open my $F, '>:gzip:utf8', $dest;
     $db->do(q{COPY (
-        SELECT uv.vid||' '||uv.uid||' '||uv.vote||' '||to_char(uv.vote_date, 'YYYY-MM-DD')
+        SELECT vndbid_num(uv.vid)||' '||vndbid_num(uv.uid)||' '||uv.vote||' '||to_char(uv.vote_date, 'YYYY-MM-DD')
           FROM ulist_vns uv
           JOIN users u ON u.id = uv.uid
           JOIN vn v ON v.id = uv.vid
diff --git a/util/devdump.pl b/util/devdump.pl
index fe126e57..58844dba 100755
--- a/util/devdump.pl
+++ b/util/devdump.pl
@@ -17,28 +17,29 @@ use lib $ROOT.'/lib';
 
 my $db = DBI->connect('dbi:Pg:dbname=vndb', 'vndb', undef, { RaiseError => 1 });
 
+sub ids { join ',', map "'$_'", @_ }
 
 
 # Figure out which DB entries to export
 
-my @vids = (3, 17, 97, 183, 264, 266, 384, 407, 1910, 2932, 5922, 6438, 9837);
-my $vids = join ',', @vids;
+my @vids = (qw/v3 v17 v97 v183 v264 v266 v384 v407 v1910 v2932 v5922 v6438 v9837/);
+my $vids = ids @vids;
 my $staff = $db->selectcol_arrayref(
     "SELECT c2.itemid FROM vn_staff_hist  v JOIN changes c ON c.id = v.chid JOIN staff_alias_hist a ON a.aid = v.aid JOIN changes c2 ON c2.id = a.chid WHERE c.itemid IN($vids) "
    ."UNION "
    ."SELECT c2.itemid FROM vn_seiyuu_hist v JOIN changes c ON c.id = v.chid JOIN staff_alias_hist a ON a.aid = v.aid JOIN changes c2 ON c2.id = a.chid WHERE c.itemid IN($vids)"
 );
 my $releases = $db->selectcol_arrayref("SELECT DISTINCT c.itemid FROM releases_vn_hist v JOIN changes c ON c.id = v.chid WHERE v.vid IN($vids)");
-my $producers = $db->selectcol_arrayref("SELECT pid FROM releases_producers_hist p JOIN changes c ON c.id = p.chid WHERE c.type = 'r' AND c.itemid IN(".join(',',@$releases).")");
+my $producers = $db->selectcol_arrayref("SELECT pid FROM releases_producers_hist p JOIN changes c ON c.id = p.chid WHERE c.itemid IN(".ids(@$releases).")");
 my $characters = $db->selectcol_arrayref(
     "SELECT DISTINCT c.itemid FROM chars_vns_hist e JOIN changes c ON c.id = e.chid WHERE e.vid IN($vids) "
    ."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)
+         SELECT image FROM chars_hist          ch JOIN changes c ON c.id = ch.chid WHERE c.itemid IN(}.ids(@$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.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.itemid IN($vids)
 });
 
 
@@ -47,6 +48,7 @@ my $images = $db->selectcol_arrayref(q{
 sub copy {
     my($dest, $sql, $specials) = @_;
 
+    warn $dest;
     $sql ||= "SELECT * FROM $dest";
     $specials ||= {};
 
@@ -61,7 +63,7 @@ sub copy {
     $sql = "SELECT " . join(',', map {
         my $s = $specials->{$_} || '';
         if($s eq 'user') {
-            qq{CASE WHEN "$_" % 10 = 0 THEN NULL ELSE "$_" % 10 END AS "$_"}
+            qq{CASE WHEN vndbid_num("$_") % 10 = 0 THEN NULL ELSE vndbid('u', vndbid_num("$_") % 10) END AS "$_"}
         } else {
             qq{"$_"}
         }
@@ -77,14 +79,14 @@ sub copy {
 
 # Helper function to copy a full DB entry with history and all (doesn't handle references)
 sub copy_entry {
-    my($type, $tables, $ids) = @_;
-    $ids = join ',', @$ids;
-    copy changes => "SELECT * FROM changes WHERE type = '$type' AND itemid IN($ids)", {requester => 'user', ip => 'del'};
+    my($tables, $ids) = @_;
+    $ids = ids @$ids;
+    copy changes => "SELECT * FROM changes WHERE itemid IN($ids)", {requester => 'user', ip => 'del'};
     for(@$tables) {
         my $add = '';
         $add = " AND vid IN($vids)" if /^releases_vn/ || /^vn_relations/ || /^chars_vns/;
         copy $_          => "SELECT *   FROM $_ WHERE id IN($ids) $add";
-        copy "${_}_hist" => "SELECT x.* FROM ${_}_hist x JOIN changes c ON c.id = x.chid WHERE c.type = '$type' AND c.itemid IN($ids) $add";
+        copy "${_}_hist" => "SELECT x.* FROM ${_}_hist x JOIN changes c ON c.id = x.chid WHERE c.itemid IN($ids) $add";
     }
 }
 
@@ -109,15 +111,15 @@ sub copy_entry {
     # A few pre-defined users
     # This password is 'hunter2' with the default salt
     my $pass = '000100000801ec4185fed438752d6b3b968e2b2cd045f70005cb7e10cafdbb694a82246bd34a065b6e977e0c3dcc';
-    printf "INSERT INTO users (id, username, mail, perm_usermod, passwd, email_confirmed) VALUES (%d, '%s', '%s', %s, decode('%s', 'hex'), true);\n", @$_, $pass for(
-        [ 2, 'admin', 'admin@vndb.org', 'true' ],
-        [ 3, 'user1', 'user1@vndb.org', 'false'],
-        [ 4, 'user2', 'user2@vndb.org', 'false'],
-        [ 5, 'user3', 'user3@vndb.org', 'false'],
-        [ 6, 'user4', 'user4@vndb.org', 'false'],
-        [ 7, 'user5', 'user5@vndb.org', 'false'],
-        [ 8, 'user6', 'user6@vndb.org', 'false'],
-        [ 9, 'user7', 'user7@vndb.org', 'false'],
+    printf "INSERT INTO users (id, username, mail, perm_usermod, passwd, email_confirmed) VALUES ('%s', '%s', '%s', %s, decode('%s', 'hex'), true);\n", @$_, $pass for(
+        [ 'u2', 'admin', 'admin@vndb.org', 'true' ],
+        [ 'u3', 'user1', 'user1@vndb.org', 'false'],
+        [ 'u4', 'user2', 'user2@vndb.org', 'false'],
+        [ 'u5', 'user3', 'user3@vndb.org', 'false'],
+        [ 'u6', 'user4', 'user4@vndb.org', 'false'],
+        [ 'u7', 'user5', 'user5@vndb.org', 'false'],
+        [ 'u8', 'user6', 'user6@vndb.org', 'false'],
+        [ 'u9', 'user7', 'user7@vndb.org', 'false'],
     );
     print "SELECT ulist_labels_create(id) FROM users;\n";
 
@@ -132,9 +134,9 @@ sub copy_entry {
     copy 'wikidata';
 
     # Image metadata
-    my $image_ids = join ',', map "'$_'", @$images;
+    my $image_ids = ids @$images;
     copy images => "SELECT * FROM images WHERE id IN($image_ids)";
-    copy image_votes => "SELECT DISTINCT ON (id,uid%10) * FROM image_votes WHERE id IN($image_ids)", { uid => 'user' };
+    copy image_votes => "SELECT DISTINCT ON (id,vndbid('u', vndbid_num(uid)%10+10)) * FROM image_votes WHERE id IN($image_ids)", { uid => 'user' };
 
     # Threads (announcements)
     my $threads = join ',', map "'$_'", @{ $db->selectcol_arrayref("SELECT tid FROM threads_boards b WHERE b.type = 'an'") };
@@ -143,31 +145,31 @@ sub copy_entry {
     copy threads_posts  => "SELECT * FROM threads_posts WHERE tid IN($threads)", { uid => 'user' };
 
     # Doc pages
-    copy_entry d => ['docs'], $db->selectcol_arrayref('SELECT id FROM docs');
+    copy_entry ['docs'], $db->selectcol_arrayref('SELECT id FROM docs');
 
     # Staff
-    copy_entry s => [qw/staff staff_alias/], $staff;
+    copy_entry [qw/staff staff_alias/], $staff;
 
     # Producers (TODO: Relations)
-    copy_entry p => [qw/producers/], $producers;
+    copy_entry [qw/producers/], $producers;
 
     # Characters
-    copy_entry c => [qw/chars chars_traits chars_vns/], $characters;
+    copy_entry [qw/chars chars_traits chars_vns/], $characters;
 
     # Visual novels
-    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_entry v     => [qw/vn vn_anime vn_seiyuu vn_staff vn_relations vn_screenshots/], \@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.itemid IN($vids)";
+    copy_entry [qw/vn vn_anime vn_seiyuu vn_staff vn_relations vn_screenshots/], \@vids;
 
     # VN-related niceties
-    copy tags_vn     => "SELECT DISTINCT ON (tag,vid,uid%10) * FROM tags_vn WHERE vid IN($vids)", {uid => 'user'};
+    copy tags_vn     => "SELECT DISTINCT ON (tag,vid,vndbid_num(uid)%10) * FROM tags_vn WHERE vid IN($vids)", {uid => 'user'};
     copy quotes      => "SELECT * FROM quotes WHERE vid IN($vids)";
-    my $votes = "SELECT vid, uid%8+2 AS uid, (percentile_cont((uid%8+1)::float/9) WITHIN GROUP (ORDER BY vote))::smallint AS vote, MIN(vote_date) AS vote_date"
-               ."  FROM ulist_vns WHERE vid IN($vids) AND vote IS NOT NULL GROUP BY vid, uid%8";
+    my $votes = "SELECT vid, vndbid('u', vndbid_num(uid)%8+2) AS uid, (percentile_cont((vndbid_num(uid)%8+1)::float/9) WITHIN GROUP (ORDER BY vote))::smallint AS vote, MIN(vote_date) AS vote_date"
+               ."  FROM ulist_vns WHERE vid IN($vids) AND vote IS NOT NULL GROUP BY vid, vndbid_num(uid)%8";
     copy ulist_vns   => $votes, {uid => 'user'};
     copy ulist_vns_labels => "SELECT vid, uid, 7 AS lbl FROM ($votes) x", {uid => 'user'};
 
     # Releases
-    copy_entry r => [qw/releases releases_lang releases_media releases_platforms releases_producers releases_vn/], $releases;
+    copy_entry [qw/releases releases_lang releases_media releases_platforms releases_producers releases_vn/], $releases;
 
     print "\\i sql/tableattrs.sql\n";
     print "\\i sql/triggers.sql\n";
diff --git a/util/revision-integrity.pl b/util/revision-integrity.pl
index 3c8a552d..8c542f92 100755
--- a/util/revision-integrity.pl
+++ b/util/revision-integrity.pl
@@ -34,6 +34,6 @@ for my $table (sort { $a->{name} cmp $b->{name} } values %$schema) {
          EXCEPT
          SELECT '$main', c.itemid, $histlock $cols
            FROM $table->{name} e
-           JOIN changes c ON c.type = '$schema->{$type}{dbentry_type}' AND e.chid = c.id
-          WHERE NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev > c.rev);\n\n"
+           JOIN changes c ON e.chid = c.id
+          WHERE NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.itemid = c.itemid AND c2.rev > c.rev);\n\n"
 }
diff --git a/util/saved-queries.pl b/util/saved-queries.pl
index 5c7b70ee..f93d4643 100755
--- a/util/saved-queries.pl
+++ b/util/saved-queries.pl
@@ -25,7 +25,7 @@ for my $r (tuwf->dbAlli('SELECT uid, qtype, name, query FROM saved_queries')->@*
     my $q = eval { tuwf->compile({advsearch => $r->{qtype}})->validate($r->{query})->data };
     if(!$q) {
         $err++;
-        warn "Invalid query: u$r->{uid}, $r->{qtype}, \"$r->{name}\": $r->{query}\n";
+        warn "Invalid query: $r->{uid}, $r->{qtype}, \"$r->{name}\": $r->{query}\n";
         next;
     }
 
@@ -33,7 +33,7 @@ for my $r (tuwf->dbAlli('SELECT uid, qtype, name, query FROM saved_queries')->@*
     if($r->{qtype} eq 'v' && !$r->{name} && $q->{query}[0] eq 'and') {
         my @lengths = grep ref $_ && $_->[0] eq 'length', $q->{query}->@*;
         $q->{query} = [ grep(!ref $_ || $_->[0] ne 'length', $q->{query}->@*), [ 'or', @lengths ] ] if @lengths > 1;
-        warn "Converted 'AND length' to 'OR length' for u$r->{uid}\n" if @lengths > 1;
+        warn "Converted 'AND length' to 'OR length' for $r->{uid}\n" if @lengths > 1;
     }
 
     # "Unlabeled && !Unlabeled" used to mean "on my list" and was what the old filter conversions used.
@@ -42,17 +42,17 @@ for my $r (tuwf->dbAlli('SELECT uid, qtype, name, query FROM saved_queries')->@*
         my sub isonlist {
             my $q = $_;
             ref $q && $q->[0] eq 'or' && @$q == 3
-                && $q->[1][0] eq 'label' && $q->[1][1] eq  '=' && ref $q->[1][2] && $q->[1][2][0] eq "u$r->{uid}" && $q->[1][2][1] eq 0
-                && $q->[2][0] eq 'label' && $q->[2][1] eq '!=' && ref $q->[2][2] && $q->[2][2][0] eq "u$r->{uid}" && $q->[2][2][1] eq 0
+                && $q->[1][0] eq 'label' && $q->[1][1] eq  '=' && ref $q->[1][2] && $q->[1][2][0] eq $r->{uid} && $q->[1][2][1] eq 0
+                && $q->[2][0] eq 'label' && $q->[2][1] eq '!=' && ref $q->[2][2] && $q->[2][2][0] eq $r->{uid} && $q->[2][2][1] eq 0
         }
         my $e=0;
         $q->{query} = [ map isonlist($_) ? do { $e=1; [ 'on-list', '=', 1 ] } : $_, $q->{query}->@* ];
-        warn "Converted Unlabaled hack to on-list for u$r->{uid}\n" if $e;
+        warn "Converted Unlabaled hack to on-list for $r->{uid}\n" if $e;
     }
 
     my $qs = $q->query_encode;
     if(!$qs) {
-        warn "Empty query: u$r->{uid}, $r->{qtype}, \"$r->{name}\": $r->{query}\n";
+        warn "Empty query: $r->{uid}, $r->{qtype}, \"$r->{name}\": $r->{query}\n";
         next;
     }
     if($qs ne $r->{query}) {
diff --git a/util/sqleditfunc.pl b/util/sqleditfunc.pl
index 2af28b6a..67aef6cf 100755
--- a/util/sqleditfunc.pl
+++ b/util/sqleditfunc.pl
@@ -37,15 +37,15 @@ sub gensql {
     $_, join ', ', @{$ts{$_}}), sort keys %ts;
 
   $replace{copyfromtemp} = join "\n", map sprintf(
-    "  DELETE FROM %1\$s WHERE id = r.itemid;\n".
-    "  INSERT INTO %1\$s (id, %2\$s) SELECT r.itemid, %2\$s FROM edit_%1\$s;\n".
-    "  INSERT INTO %1\$s_hist (chid, %2\$s) SELECT r.chid, %2\$s FROM edit_%1\$s;",
+    "  DELETE FROM %1\$s WHERE id = nitemid;\n".
+    "  INSERT INTO %1\$s (id, %2\$s) SELECT nitemid, %2\$s FROM edit_%1\$s;\n".
+    "  INSERT INTO %1\$s_hist (chid, %2\$s) SELECT nchid, %2\$s FROM edit_%1\$s;",
     $_, join ', ', @{$ts{$_}}), grep $_ ne $item, sort keys %ts;
 
   $replace{copymainfromtemp} = sprintf
-    "  INSERT INTO %1\$s_hist (chid, %2\$s) SELECT r.chid, %2\$s FROM edit_%1\$s;\n".
+    "  INSERT INTO %1\$s_hist (chid, %2\$s) SELECT nchid, %2\$s FROM edit_%1\$s;\n".
     "  UPDATE %1\$s SET locked = (SELECT ilock FROM edit_revision), hidden = (SELECT ihid FROM edit_revision),\n".
-    "    %3\$s FROM edit_%1\$s x WHERE id = r.itemid;",
+    "    %3\$s FROM edit_%1\$s x WHERE id = nitemid;",
     $item, join(', ', @{$ts{$item}}), join(', ', map "$_ = x.$_", @{$ts{$item}});
 
   $template =~ s/{([a-z]+)}/$replace{$1}/gr;
@@ -59,7 +59,7 @@ print $F gensql $_ for sort grep $schema->{$_}{dbentry_type}, keys %$schema;
 
 __DATA__
 
-CREATE OR REPLACE FUNCTION edit_{itemtype}_init(xid integer, xrev integer) RETURNS void AS $$
+CREATE OR REPLACE FUNCTION edit_{itemtype}_init(xid vndbid, xrev integer) RETURNS void AS $$
 DECLARE
   xchid integer;
 BEGIN
@@ -70,7 +70,7 @@ BEGIN
     TRUNCATE {temptablenames};
   END;
   -- Create edit_revision table and get relevant change ID.
-  SELECT edit_revtable('{itemtype}', xid, xrev) INTO xchid;
+  SELECT edit_revtable(xid, xrev) INTO xchid;
   -- new entry, load defaults
   IF xchid IS NULL THEN
     INSERT INTO edit_{item} DEFAULT VALUES;
@@ -82,18 +82,26 @@ END;
 $$ LANGUAGE plpgsql;
 
 
-CREATE OR REPLACE FUNCTION edit_{itemtype}_commit() RETURNS edit_rettype AS $$
-DECLARE
-  r edit_rettype;
+CREATE OR REPLACE FUNCTION edit_{itemtype}_commit(out nchid integer, out nitemid vndbid, out nrev integer) AS $$
 BEGIN
   IF (SELECT COUNT(*) FROM edit_{item}) <> 1 THEN
     RAISE 'edit_{item} must have exactly one row!';
   END IF;
-  SELECT INTO r * FROM edit_commit();
+  SELECT itemid INTO nitemid FROM edit_revision;
+  -- figure out revision number
+  SELECT MAX(rev)+1 INTO nrev FROM changes WHERE itemid = nitemid;
+  SELECT COALESCE(nrev, 1) INTO nrev;
+  -- insert DB item
+  IF nitemid IS NULL THEN
+    INSERT INTO {item} DEFAULT VALUES RETURNING id INTO nitemid;
+  END IF;
+  -- insert change
+  INSERT INTO changes (itemid, rev, requester, ip, comments, ihid, ilock)
+    SELECT nitemid, nrev, requester, ip, comments, ihid, ilock FROM edit_revision RETURNING id INTO nchid;
+  -- insert data
 {copyfromtemp}
 {copymainfromtemp}
-  PERFORM edit_committed('{itemtype}', r);
-  RETURN r;
+  PERFORM edit_committed(nchid, nitemid, nrev);
 END;
 $$ LANGUAGE plpgsql;
 
diff --git a/util/updates/2021-03-01-entries-to-vndbid.sql b/util/updates/2021-03-01-entries-to-vndbid.sql
new file mode 100644
index 00000000..29669bea
--- /dev/null
+++ b/util/updates/2021-03-01-entries-to-vndbid.sql
@@ -0,0 +1,245 @@
+-- Public dump breakage:
+--   SELECT .. FROM vn WHERE id = 10;
+--   SELECT .. FROM vn WHERE id IN(1,2,3);
+--   SELECT 'https://vndb.org/v'||id FROM vn;
+
+BEGIN;
+
+ALTER TABLE changes                  DROP CONSTRAINT changes_requester_fkey;
+ALTER TABLE chars                    DROP CONSTRAINT chars_main_fkey;
+ALTER TABLE chars_hist               DROP CONSTRAINT chars_hist_main_fkey;
+ALTER TABLE chars_traits             DROP CONSTRAINT chars_traits_id_fkey;
+ALTER TABLE chars_vns                DROP CONSTRAINT chars_vns_id_fkey;
+ALTER TABLE chars_vns                DROP CONSTRAINT chars_vns_rid_fkey;
+ALTER TABLE chars_vns                DROP CONSTRAINT chars_vns_vid_fkey;
+ALTER TABLE chars_vns_hist           DROP CONSTRAINT chars_vns_hist_rid_fkey;
+ALTER TABLE chars_vns_hist           DROP CONSTRAINT chars_vns_hist_vid_fkey;
+ALTER TABLE image_votes              DROP CONSTRAINT image_votes_uid_fkey;
+ALTER TABLE notification_subs        DROP CONSTRAINT notification_subs_uid_fkey;
+ALTER TABLE notifications            DROP CONSTRAINT notifications_uid_fkey;
+ALTER TABLE producers_relations      DROP CONSTRAINT producers_relations_pid_fkey;
+ALTER TABLE producers_relations_hist DROP CONSTRAINT producers_relations_hist_pid_fkey;
+ALTER TABLE quotes                   DROP CONSTRAINT quotes_vid_fkey;
+ALTER TABLE releases_lang            DROP CONSTRAINT releases_lang_id_fkey;
+ALTER TABLE releases_media           DROP CONSTRAINT releases_media_id_fkey;
+ALTER TABLE releases_platforms       DROP CONSTRAINT releases_platforms_id_fkey;
+ALTER TABLE releases_producers       DROP CONSTRAINT releases_producers_id_fkey;
+ALTER TABLE releases_producers       DROP CONSTRAINT releases_producers_pid_fkey;
+ALTER TABLE releases_producers_hist  DROP CONSTRAINT releases_producers_hist_pid_fkey;
+ALTER TABLE releases_vn              DROP CONSTRAINT releases_vn_id_fkey;
+ALTER TABLE releases_vn              DROP CONSTRAINT releases_vn_vid_fkey;
+ALTER TABLE releases_vn_hist         DROP CONSTRAINT releases_vn_hist_vid_fkey;
+ALTER TABLE reviews                  DROP CONSTRAINT reviews_rid_fkey;
+ALTER TABLE reviews                  DROP CONSTRAINT reviews_uid_fkey;
+ALTER TABLE reviews                  DROP CONSTRAINT reviews_vid_fkey;
+ALTER TABLE reviews_posts            DROP CONSTRAINT reviews_posts_uid_fkey;
+ALTER TABLE reviews_votes            DROP CONSTRAINT reviews_votes_uid_fkey;
+ALTER TABLE rlists                   DROP CONSTRAINT rlists_rid_fkey;
+ALTER TABLE rlists                   DROP CONSTRAINT rlists_uid_fkey;
+ALTER TABLE saved_queries            DROP CONSTRAINT saved_queries_uid_fkey;
+ALTER TABLE sessions                 DROP CONSTRAINT sessions_uid_fkey;
+ALTER TABLE staff_alias              DROP CONSTRAINT staff_alias_id_fkey;
+ALTER TABLE tags                     DROP CONSTRAINT tags_addedby_fkey;
+ALTER TABLE tags_vn                  DROP CONSTRAINT tags_vn_uid_fkey;
+ALTER TABLE tags_vn                  DROP CONSTRAINT tags_vn_vid_fkey;
+ALTER TABLE threads_poll_votes       DROP CONSTRAINT threads_poll_votes_uid_fkey;
+ALTER TABLE threads_posts            DROP CONSTRAINT threads_posts_uid_fkey;
+ALTER TABLE traits                   DROP CONSTRAINT traits_addedby_fkey;
+ALTER TABLE ulist_labels             DROP CONSTRAINT ulist_labels_uid_fkey;
+ALTER TABLE ulist_vns                DROP CONSTRAINT ulist_vns_uid_fkey;
+ALTER TABLE ulist_vns                DROP CONSTRAINT ulist_vns_vid_fkey;
+ALTER TABLE ulist_vns_labels         DROP CONSTRAINT ulist_vns_labels_uid_fkey;
+ALTER TABLE ulist_vns_labels         DROP CONSTRAINT ulist_vns_labels_uid_lbl_fkey;
+ALTER TABLE ulist_vns_labels         DROP CONSTRAINT ulist_vns_labels_uid_vid_fkey;
+ALTER TABLE ulist_vns_labels         DROP CONSTRAINT ulist_vns_labels_vid_fkey;
+ALTER TABLE vn_anime                 DROP CONSTRAINT vn_anime_id_fkey;
+ALTER TABLE vn_relations             DROP CONSTRAINT vn_relations_id_fkey;
+ALTER TABLE vn_relations             DROP CONSTRAINT vn_relations_vid_fkey;
+ALTER TABLE vn_relations_hist        DROP CONSTRAINT vn_relations_vid_fkey;
+ALTER TABLE vn_screenshots           DROP CONSTRAINT vn_screenshots_id_fkey;
+ALTER TABLE vn_screenshots           DROP CONSTRAINT vn_screenshots_rid_fkey;
+ALTER TABLE vn_screenshots_hist      DROP CONSTRAINT vn_screenshots_hist_rid_fkey;
+ALTER TABLE vn_seiyuu                DROP CONSTRAINT vn_seiyuu_cid_fkey;
+ALTER TABLE vn_seiyuu                DROP CONSTRAINT vn_seiyuu_id_fkey;
+ALTER TABLE vn_seiyuu_hist           DROP CONSTRAINT vn_seiyuu_hist_cid_fkey;
+ALTER TABLE vn_staff                 DROP CONSTRAINT vn_staff_id_fkey;
+
+DROP INDEX chars_vns_pkey;
+DROP INDEX chars_vns_hist_pkey;
+
+ALTER TABLE rlists ALTER COLUMN uid DROP DEFAULT;
+ALTER TABLE rlists ALTER COLUMN rid DROP DEFAULT;
+
+
+DROP INDEX changes_itemrev;
+ALTER TABLE changes ALTER COLUMN itemid TYPE vndbid USING vndbid(type::text, itemid);
+ALTER TABLE changes DROP COLUMN type;
+
+ALTER TABLE threads_boards DROP CONSTRAINT threads_boards_pkey;
+ALTER TABLE threads_boards ALTER COLUMN iid DROP DEFAULT;
+ALTER TABLE threads_boards ALTER COLUMN iid DROP NOT NULL;
+ALTER TABLE threads_boards ALTER COLUMN iid TYPE vndbid USING CASE WHEN iid = 0 THEN NULL ELSE vndbid(type::text, iid) END;
+
+ALTER TABLE audit_log ALTER COLUMN by_uid TYPE vndbid USING vndbid('u', by_uid);
+ALTER TABLE audit_log ALTER COLUMN affected_uid TYPE vndbid USING vndbid('u', affected_uid);
+ALTER TABLE reports ALTER COLUMN uid TYPE vndbid USING vndbid('u', uid);
+
+
+ALTER TABLE chars ALTER COLUMN id DROP DEFAULT;
+ALTER TABLE chars ALTER COLUMN id TYPE vndbid USING vndbid('c', id);
+ALTER TABLE chars ALTER COLUMN id SET DEFAULT vndbid('c', nextval('chars_id_seq')::int);
+ALTER TABLE chars ADD CONSTRAINT chars_id_check CHECK(vndbid_type(id) = 'c');
+
+ALTER TABLE chars        ALTER COLUMN main TYPE vndbid USING vndbid('c', main);
+ALTER TABLE chars_hist   ALTER COLUMN main TYPE vndbid USING vndbid('c', main);
+ALTER TABLE chars_traits ALTER COLUMN id TYPE vndbid USING vndbid('c', id);
+ALTER TABLE chars_vns    ALTER COLUMN id TYPE vndbid USING vndbid('c', id);
+ALTER TABLE traits_chars   ALTER COLUMN cid TYPE vndbid USING vndbid('c', cid);
+ALTER TABLE vn_seiyuu      ALTER COLUMN cid TYPE vndbid USING vndbid('c', cid);
+ALTER TABLE vn_seiyuu_hist ALTER COLUMN cid TYPE vndbid USING vndbid('c', cid);
+
+
+ALTER TABLE docs ALTER COLUMN id DROP DEFAULT;
+ALTER TABLE docs ALTER COLUMN id TYPE vndbid USING vndbid('d', id);
+ALTER TABLE docs ALTER COLUMN id SET DEFAULT vndbid('d', nextval('docs_id_seq')::int);
+ALTER TABLE docs ADD CONSTRAINT docs_id_check CHECK(vndbid_type(id) = 'd');
+
+
+ALTER TABLE producers ALTER COLUMN id DROP DEFAULT;
+ALTER TABLE producers ALTER COLUMN id TYPE vndbid USING vndbid('p', id);
+ALTER TABLE producers ALTER COLUMN id SET DEFAULT vndbid('p', nextval('producers_id_seq')::int);
+ALTER TABLE producers ADD CONSTRAINT producers_id_check CHECK(vndbid_type(id) = 'p');
+
+ALTER TABLE producers_relations      ALTER COLUMN id  TYPE vndbid USING vndbid('p', id);
+ALTER TABLE producers_relations      ALTER COLUMN pid TYPE vndbid USING vndbid('p', pid);
+ALTER TABLE producers_relations_hist ALTER COLUMN pid TYPE vndbid USING vndbid('p', pid);
+ALTER TABLE releases_producers       ALTER COLUMN pid TYPE vndbid USING vndbid('p', pid);
+ALTER TABLE releases_producers_hist  ALTER COLUMN pid TYPE vndbid USING vndbid('p', pid);
+
+
+ALTER TABLE releases ALTER COLUMN id DROP DEFAULT;
+ALTER TABLE releases ALTER COLUMN id TYPE vndbid USING vndbid('r', id);
+ALTER TABLE releases ALTER COLUMN id SET DEFAULT vndbid('r', nextval('releases_id_seq')::int);
+ALTER TABLE releases ADD CONSTRAINT releases_id_check CHECK(vndbid_type(id) = 'r');
+
+ALTER TABLE chars_vns                ALTER COLUMN rid TYPE vndbid USING vndbid('r', rid);
+ALTER TABLE chars_vns_hist           ALTER COLUMN rid TYPE vndbid USING vndbid('r', rid);
+ALTER TABLE releases_lang            ALTER COLUMN id  TYPE vndbid USING vndbid('r', id);
+ALTER TABLE releases_media           ALTER COLUMN id  TYPE vndbid USING vndbid('r', id);
+ALTER TABLE releases_platforms       ALTER COLUMN id  TYPE vndbid USING vndbid('r', id);
+ALTER TABLE releases_producers       ALTER COLUMN id  TYPE vndbid USING vndbid('r', id);
+ALTER TABLE releases_vn              ALTER COLUMN id  TYPE vndbid USING vndbid('r', id);
+ALTER TABLE reviews                  ALTER COLUMN rid TYPE vndbid USING vndbid('r', rid);
+ALTER TABLE rlists                   ALTER COLUMN rid TYPE vndbid USING vndbid('r', rid);
+ALTER TABLE vn_screenshots           ALTER COLUMN rid TYPE vndbid USING vndbid('r', rid);
+ALTER TABLE vn_screenshots_hist      ALTER COLUMN rid TYPE vndbid USING vndbid('r', rid);
+
+
+ALTER TABLE staff ALTER COLUMN id DROP DEFAULT;
+ALTER TABLE staff ALTER COLUMN id TYPE vndbid USING vndbid('s', id);
+ALTER TABLE staff ALTER COLUMN id SET DEFAULT vndbid('s', nextval('staff_id_seq')::int);
+ALTER TABLE staff ADD CONSTRAINT staff_id_check CHECK(vndbid_type(id) = 's');
+
+ALTER TABLE staff_alias ALTER COLUMN id TYPE vndbid USING vndbid('s', id);
+
+
+ALTER TABLE vn ALTER COLUMN id DROP DEFAULT;
+ALTER TABLE vn ALTER COLUMN id TYPE vndbid USING vndbid('v', id);
+ALTER TABLE vn ALTER COLUMN id SET DEFAULT vndbid('v', nextval('vn_id_seq')::int);
+ALTER TABLE vn ADD CONSTRAINT vn_id_check CHECK(vndbid_type(id) = 'v');
+
+ALTER TABLE chars_vns          ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid);
+ALTER TABLE chars_vns_hist     ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid);
+ALTER TABLE quotes             ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid);
+ALTER TABLE releases_vn        ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid);
+ALTER TABLE releases_vn_hist   ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid);
+ALTER TABLE reviews            ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid);
+ALTER TABLE tags_vn            ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid);
+ALTER TABLE tags_vn_inherit    ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid);
+ALTER TABLE ulist_vns          ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid);
+ALTER TABLE ulist_vns_labels   ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid);
+ALTER TABLE vn_anime           ALTER COLUMN id  TYPE vndbid USING vndbid('v', id);
+ALTER TABLE vn_relations       ALTER COLUMN id  TYPE vndbid USING vndbid('v', id);
+ALTER TABLE vn_relations       ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid);
+ALTER TABLE vn_relations_hist  ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid);
+ALTER TABLE vn_screenshots     ALTER COLUMN id  TYPE vndbid USING vndbid('v', id);
+ALTER TABLE vn_seiyuu          ALTER COLUMN id  TYPE vndbid USING vndbid('v', id);
+ALTER TABLE vn_staff           ALTER COLUMN id  TYPE vndbid USING vndbid('v', id);
+
+
+ALTER TABLE users ALTER COLUMN id DROP DEFAULT;
+ALTER TABLE users ALTER COLUMN id TYPE vndbid USING vndbid('u', id);
+ALTER TABLE users ALTER COLUMN id SET DEFAULT vndbid('u', nextval('users_id_seq')::int);
+ALTER TABLE users ADD CONSTRAINT users_id_check CHECK(vndbid_type(id) = 'u');
+
+ALTER TABLE changes                  ALTER COLUMN requester TYPE vndbid USING vndbid('u', requester);
+ALTER TABLE image_votes              ALTER COLUMN uid       TYPE vndbid USING vndbid('u', uid);
+ALTER TABLE notification_subs        ALTER COLUMN uid       TYPE vndbid USING vndbid('u', uid);
+ALTER TABLE notifications            ALTER COLUMN uid       TYPE vndbid USING vndbid('u', uid);
+ALTER TABLE reviews                  ALTER COLUMN uid       TYPE vndbid USING vndbid('u', uid);
+ALTER TABLE reviews_posts            ALTER COLUMN uid       TYPE vndbid USING vndbid('u', uid);
+ALTER TABLE reviews_votes            ALTER COLUMN uid       TYPE vndbid USING vndbid('u', uid);
+ALTER TABLE rlists                   ALTER COLUMN uid       TYPE vndbid USING vndbid('u', uid);
+ALTER TABLE saved_queries            ALTER COLUMN uid       TYPE vndbid USING vndbid('u', uid);
+ALTER TABLE sessions                 ALTER COLUMN uid       TYPE vndbid USING vndbid('u', uid);
+ALTER TABLE tags                     ALTER COLUMN addedby   TYPE vndbid USING vndbid('u', addedby);
+ALTER TABLE tags_vn                  ALTER COLUMN uid       TYPE vndbid USING vndbid('u', uid);
+ALTER TABLE threads_poll_votes       ALTER COLUMN uid       TYPE vndbid USING vndbid('u', uid);
+ALTER TABLE threads_posts            ALTER COLUMN uid       TYPE vndbid USING vndbid('u', uid);
+ALTER TABLE traits                   ALTER COLUMN addedby   TYPE vndbid USING vndbid('u', addedby);
+ALTER TABLE ulist_labels             ALTER COLUMN uid       TYPE vndbid USING vndbid('u', uid);
+ALTER TABLE ulist_vns                ALTER COLUMN uid       TYPE vndbid USING vndbid('u', uid);
+ALTER TABLE ulist_vns_labels         ALTER COLUMN uid       TYPE vndbid USING vndbid('u', uid);
+
+ALTER TABLE images ALTER COLUMN c_uids DROP DEFAULT;
+ALTER TABLE images ALTER COLUMN c_uids TYPE vndbid[] USING '{}';
+ALTER TABLE images ALTER COLUMN c_uids SET DEFAULT '{}';
+
+DROP FUNCTION edit_revtable(dbentry_type, integer, integer);
+DROP FUNCTION edit_commit();
+DROP FUNCTION edit_committed(dbentry_type, edit_rettype);
+DROP FUNCTION edit_c_init(integer, integer);
+DROP FUNCTION edit_d_init(integer, integer);
+DROP FUNCTION edit_p_init(integer, integer);
+DROP FUNCTION edit_r_init(integer, integer);
+DROP FUNCTION edit_s_init(integer, integer);
+DROP FUNCTION edit_v_init(integer, integer);
+DROP FUNCTION edit_c_commit();
+DROP FUNCTION edit_d_commit();
+DROP FUNCTION edit_p_commit();
+DROP FUNCTION edit_r_commit();
+DROP FUNCTION edit_s_commit();
+DROP FUNCTION edit_v_commit();
+
+DROP FUNCTION update_vncache(integer);
+DROP FUNCTION tag_vn_calc(integer);
+DROP FUNCTION traits_chars_calc(integer);
+DROP FUNCTION ulist_labels_create(integer);
+DROP FUNCTION item_info(id vndbid, num int);
+DROP FUNCTION notify(iid vndbid, num integer, uid integer);
+DROP FUNCTION update_users_ulist_stats(integer);
+DROP FUNCTION user_getscryptargs(integer);
+DROP FUNCTION user_login(integer, bytea, bytea);
+DROP FUNCTION user_logout(integer, bytea);
+DROP FUNCTION user_isvalidsession(integer, bytea, session_type);
+DROP FUNCTION user_emailtoid(text);
+DROP FUNCTION user_resetpass(text, bytea);
+DROP FUNCTION user_setpass(integer, bytea, bytea);
+DROP FUNCTION user_isauth(integer, integer, bytea);
+DROP FUNCTION user_getmail(integer, integer, bytea);
+DROP FUNCTION user_setmail_token(integer, bytea, bytea, text);
+DROP FUNCTION user_setmail_confirm(integer, bytea);
+DROP FUNCTION user_setperm_usermod(integer, integer, bytea, boolean);
+DROP FUNCTION user_admin_setpass(integer, integer, bytea, bytea);
+DROP FUNCTION user_admin_setmail(integer, integer, bytea, text);
+\i sql/func.sql
+\i sql/editfunc.sql
+DROP TYPE edit_rettype;
+
+COMMIT;
+
+-- Need to do this analyze to ensure adding the foreign key constraints will use proper query plans.
+ANALYZE;
+\i sql/tableattrs.sql
+\i sql/perms.sql
+SELECT update_images_cache(NULL);