summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-08-15 16:17:13 +0200
committerYorhel <git@yorhel.nl>2019-08-15 16:17:16 +0200
commit5a173c85c30acd3c5d80f9a0b6dd250efb61ad81 (patch)
tree359fad2a80bdfad1e7358872cc29bea57dac5208
parent30cdc48d9ddbfe5223ebcfe5cdff3cdab372d894 (diff)
Minor util/sql/ reorganization + Make devdump more robust
I never really liked the hack that devdump.pl had to use to temporarily disable triggers and references. This new importer first imports all schema-related things, then the data, then the functions and table attributes - like an actual database dump. This restructuring should also make it (slightly) easier to import the "near-complete" database dump, but that's still going to involve a fair amount of scripting. This also fixes #22 - the script now asks whether to import a 'dump.sql' if it exists.
-rw-r--r--lib/Multi/Maintenance.pm17
-rw-r--r--lib/VNDBSchema.pm4
-rwxr-xr-xutil/dbdump.pl2
-rwxr-xr-xutil/devdump.pl49
-rwxr-xr-xutil/docker-init.sh28
-rw-r--r--util/sql/all.sql56
-rw-r--r--util/sql/func.sql16
-rw-r--r--util/sql/schema.sql29
-rw-r--r--util/sql/tableattrs.sql71
-rw-r--r--util/sql/triggers.sql61
10 files changed, 158 insertions, 175 deletions
diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm
index 738909a9..3739af1f 100644
--- a/lib/Multi/Maintenance.pm
+++ b/lib/Multi/Maintenance.pm
@@ -127,20 +127,9 @@ my %monthlies = (
# This only takes about 3 seconds to complete
vncache_full => 'SELECT update_vncache(id) FROM vn',
- # These shouldn't really be necessary, the triggers in PgSQL should keep
- # these up-to-date nicely. But these all take less a second to complete,
- # anyway.
- stats_users => q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM users)-1 WHERE section = 'users'|,
- stats_vn => q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM vn WHERE hidden = FALSE) WHERE section = 'vn'|,
- stats_rel => q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM releases WHERE hidden = FALSE) WHERE section = 'releases'|,
- stats_prod => q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM producers WHERE hidden = FALSE) WHERE section = 'producers'|,
- stats_chars => q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM chars WHERE hidden = FALSE) WHERE section = 'chars'|,
- stats_chars => q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM staff WHERE hidden = FALSE) WHERE section = 'staff'|,
- stats_tags => q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM tags WHERE state = 2) WHERE section = 'tags'|,
- stats_trait => q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM traits WHERE state = 2) WHERE section = 'traits'|,
- stats_thread=> q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM threads WHERE hidden = FALSE) WHERE section = 'threads'|,
- stats_posts => q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM threads_posts WHERE hidden = FALSE
- AND EXISTS(SELECT 1 FROM threads WHERE threads.id = tid AND threads.hidden = FALSE)) WHERE section = 'threads_posts'|,
+ # This shouldn't really be necessary, the triggers in PgSQL should keep
+ # these up-to-date nicely. But it takes less than a second, anyway.
+ stats_cache => 'SELECT update_stats_cache_full()',
);
diff --git a/lib/VNDBSchema.pm b/lib/VNDBSchema.pm
index c095550d..94f495b2 100644
--- a/lib/VNDBSchema.pm
+++ b/lib/VNDBSchema.pm
@@ -32,6 +32,8 @@ sub schema {
while(<$F>) {
chomp;
next if /^\s*--/ || /^\s*$/;
+ next if /^\s*CREATE\s+TYPE/;
+ next if /^\s*CREATE\s+SEQUENCE/;
if(/^\s*CREATE\s+TABLE\s+([^ ]+)/) {
die "Unexpected 'CREATE TABLE $1'\n" if $table;
@@ -72,7 +74,7 @@ sub schema {
}
-# Parses types from all.sql and returns a hashref with the following structure:
+# Parses types from schema.sql and returns a hashref with the following structure:
# {
# anime_type => {
# decl => 'CREATE TYPE ..;'
diff --git a/util/dbdump.pl b/util/dbdump.pl
index bbff0cf5..acb5907a 100755
--- a/util/dbdump.pl
+++ b/util/dbdump.pl
@@ -100,7 +100,7 @@ my %tables = (
my @tables = map +{ name => $_, %{$tables{$_}} }, sort keys %tables;
my $schema = VNDBSchema::schema("$ROOT/util/sql/schema.sql");
-my $types = VNDBSchema::types("$ROOT/util/sql/all.sql");
+my $types = VNDBSchema::types("$ROOT/util/sql/schema.sql");
my $references = VNDBSchema::references("$ROOT/util/sql/tableattrs.sql");
my $db = DBI->connect('dbi:Pg:dbname=vndb', 'vndb', undef, { RaiseError => 1 });
diff --git a/util/devdump.pl b/util/devdump.pl
index e001ede2..48e43cf7 100755
--- a/util/devdump.pl
+++ b/util/devdump.pl
@@ -83,20 +83,10 @@ sub copy_entry {
open my $OUT, '>:utf8', 'dump.sql';
select $OUT;
- # Header
- my @tables = grep !/^multi_/, sort @{ $db->selectcol_arrayref(
- "SELECT oid::regclass::text FROM pg_class WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace"
- ) };
- print "\\set ON_ERROR_STOP 1\n";
- print "BEGIN;\n";
- printf "TRUNCATE TABLE %s CASCADE;\n", join ',', @tables;
- print "SET CONSTRAINTS ALL DEFERRED;\n";
- printf "ALTER TABLE %s DISABLE TRIGGER USER;\n", $_ for @tables;
-
- # Copy over some required defaults
- open my $F, '<', 'util/sql/data.sql';
- print while <$F>;
- close $F;
+ print "-- This file replaces 'util/sql/all.sql'.\n";
+ print "\\set ON_ERROR_STOP 1\n";
+ print "\\i util/sql/schema.sql\n";
+ print "\\i util/sql/data.sql\n";
# Copy over all sequence values
my @seq = sort @{ $db->selectcol_arrayref(
@@ -161,32 +151,23 @@ sub copy_entry {
# Releases
copy_entry r => [qw/releases releases_lang releases_media releases_platforms releases_producers releases_vn/], $releases;
- # Caches
+ print "\\i util/sql/func.sql\n";
+ print "\\i util/sql/editfunc.sql\n";
+ print "\\i util/sql/tableattrs.sql\n";
+
+ # Update some caches
print "SELECT tag_vn_calc();\n";
print "SELECT traits_chars_calc();\n";
print "SELECT update_vncache(id) FROM vn;\n";
+ print "SELECT update_stats_cache_full();\n";
+ print "SELECT update_vnpopularity();\n";
print "UPDATE users u SET c_votes = (SELECT COUNT(*) FROM votes v WHERE v.uid = u.id);\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";
- # These were copied from Multi::Maintenance
- print "UPDATE stats_cache SET count = (SELECT COUNT(*) FROM users)-1 WHERE section = 'users';\n";
- print "UPDATE stats_cache SET count = (SELECT COUNT(*) FROM vn WHERE hidden = FALSE) WHERE section = 'vn';\n";
- print "UPDATE stats_cache SET count = (SELECT COUNT(*) FROM releases WHERE hidden = FALSE) WHERE section = 'releases';\n";
- print "UPDATE stats_cache SET count = (SELECT COUNT(*) FROM producers WHERE hidden = FALSE) WHERE section = 'producers';\n";
- print "UPDATE stats_cache SET count = (SELECT COUNT(*) FROM chars WHERE hidden = FALSE) WHERE section = 'chars';\n";
- print "UPDATE stats_cache SET count = (SELECT COUNT(*) FROM staff WHERE hidden = FALSE) WHERE section = 'staff';\n";
- print "UPDATE stats_cache SET count = (SELECT COUNT(*) FROM tags WHERE state = 2) WHERE section = 'tags';\n";
- print "UPDATE stats_cache SET count = (SELECT COUNT(*) FROM traits WHERE state = 2) WHERE section = 'traits';\n";
- print "UPDATE stats_cache SET count = (SELECT COUNT(*) FROM threads WHERE hidden = FALSE) WHERE section = 'threads';\n";
- print "UPDATE stats_cache SET count = (SELECT COUNT(*) FROM threads_posts WHERE hidden = FALSE
- AND EXISTS(SELECT 1 FROM threads WHERE threads.id = tid AND threads.hidden = FALSE)) WHERE section = 'threads_posts';\n";
- # TODO: Something with the 'stats_cache' table. The vn.c_* stats are also inconsistent
-
- # Footer
- # Apparently we can't do an ALTER TABLE while the (deferred) foreign key checks
- # haven't been executed, so do a commit first.
- print "COMMIT;\n";
- printf "ALTER TABLE %s ENABLE TRIGGER USER;\n", $_ for @tables;
+ # TODO: The vn.c_rating and vn.c_votecount stats are still inconsistent
+
+ print "\\set ON_ERROR_STOP 0\n";
+ print "\\i util/sql/perms.sql\n";
select STDOUT;
close $OUT;
diff --git a/util/docker-init.sh b/util/docker-init.sh
index 0ac0aca1..c78c5e76 100755
--- a/util/docker-init.sh
+++ b/util/docker-init.sh
@@ -48,28 +48,34 @@ pg_start() {
return
fi
- psql postgres -f util/sql/superuser_init.sql
- echo "ALTER ROLE vndb LOGIN" | psql postgres
- echo "ALTER ROLE vndb_site LOGIN" | psql postgres
- echo "ALTER ROLE vndb_multi LOGIN" | psql postgres
-
- make util/sql/editfunc.sql
- psql -U vndb -f util/sql/all.sql
-
+ echo "============================================================="
echo
- echo "You now have a functional, but empty, database."
+ echo "Database has not been initialized yet, doing that now."
echo "If you want to have some data to play around with,"
echo "I can download and install a development database for you."
echo "For information, see https://vndb.org/d8#3"
echo "(Warning: This will also write images to static/)"
echo
- echo "Enter n to keep an empty database, y to download the dev database."
+ echo "Enter n to setup an empty database, y to download the dev database."
+ [ -f dump.sql ] && echo " Or e to import the existing dump.sql."
read -p "Choice: " opt
- if [[ $opt =~ ^[Yy] ]]
+
+ make util/sql/editfunc.sql
+ psql postgres -f util/sql/superuser_init.sql
+ echo "ALTER ROLE vndb LOGIN" | psql postgres
+ echo "ALTER ROLE vndb_site LOGIN" | psql postgres
+ echo "ALTER ROLE vndb_multi LOGIN" | psql postgres
+
+ if [[ $opt =~ ^[Ee] ]]
+ then
+ psql -U vndb -f dump.sql
+ elif [[ $opt =~ ^[Yy] ]]
then
curl -L https://dl.vndb.org/dump/vndb-dev-latest.tar.gz | tar -xzf-
psql -U vndb -f dump.sql
rm dump.sql
+ else
+ psql -U vndb -f util/sql/all.sql
fi
touch data/docker-pg/vndb-init-done
diff --git a/util/sql/all.sql b/util/sql/all.sql
index 0e79379f..8bf3ae63 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -1,60 +1,10 @@
-- NOTE: Make sure you're cd'ed in the vndb root directory before running this script
-
--- data types
-
-CREATE TYPE anime_type AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv');
-CREATE TYPE blood_type AS ENUM ('unknown', 'a', 'b', 'ab', 'o');
-CREATE TYPE board_type AS ENUM ('an', 'db', 'ge', 'v', 'p', 'u');
-CREATE TYPE char_role AS ENUM ('main', 'primary', 'side', 'appears');
-CREATE TYPE credit_type AS ENUM ('scenario', 'chardesign', 'art', 'music', 'songs', 'director', 'staff');
-CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p', 'c', 's', 'd');
-CREATE TYPE edit_rettype AS (itemid integer, chid integer, rev integer);
-CREATE TYPE gender AS ENUM ('unknown', 'm', 'f', 'b');
-CREATE TYPE language AS ENUM ('ar', 'bg', 'ca', 'cs', 'da', 'de', 'el', 'en', 'eo', 'es', 'fi', 'fr', 'he', 'hr', 'hu', 'id', 'it', 'ja', 'ko', 'nl', 'no', 'pl', 'pt-pt', 'pt-br', 'ro', 'ru', 'sk', 'sv', 'ta', 'th', 'tr', 'uk', 'vi', 'zh');
-CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
-CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel', 'listdel', 'dbedit', 'announce');
-CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 'c', 't', 's', 'd');
-CREATE TYPE platform AS ENUM ('win', 'dos', 'lin', 'mac', 'ios', 'and', 'dvd', 'bdp', 'fmt', 'gba', 'gbc', 'msx', 'nds', 'nes', 'p88', 'p98', 'pce', 'pcf', 'psp', 'ps1', 'ps2', 'ps3', 'ps4', 'psv', 'drc', 'sat', 'sfc', 'swi', 'wii', 'wiu', 'n3d', 'x68', 'xb1', 'xb3', 'xbo', 'web', 'oth');
-CREATE TYPE prefs_key AS ENUM ('l10n', 'skin', 'customcss', 'filter_vn', 'filter_release', 'show_nsfw', 'hide_list', 'notify_nodbedit', 'notify_announce', 'vn_list_own', 'vn_list_wish', 'tags_all', 'tags_cat', 'spoilers', 'traits_sexual');
-CREATE TYPE producer_type AS ENUM ('co', 'in', 'ng');
-CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori');
-CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial');
-CREATE TYPE tag_category AS ENUM('cont', 'ero', 'tech');
-CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
-CREATE TYPE resolution AS ENUM ('unknown', 'nonstandard', '640x480', '800x600', '1024x768', '1280x960', '1600x1200', '640x400', '960x600', '960x640', '1024x576', '1024x600', '1024x640', '1280x720', '1280x800', '1366x768', '1600x900', '1920x1080');
-
-
--- schema
-
+\set ON_ERROR_STOP 1
\i util/sql/schema.sql
-
-
--- functions
-
+\i util/sql/data.sql
\i util/sql/func.sql
-
--- auto-generated editing functions
-
\i util/sql/editfunc.sql
-
--- constraints & indices
-
\i util/sql/tableattrs.sql
-
--- triggers
-
-\i util/sql/triggers.sql
-
--- Sequences used for ID generation of items not in the DB
-CREATE SEQUENCE covers_seq;
-CREATE SEQUENCE charimg_seq;
-
--- permissions
-
+\set ON_ERROR_STOP 0
\i util/sql/perms.sql
-
-
--- Rows that are assumed to be available
-
-\i util/sql/data.sql
diff --git a/util/sql/func.sql b/util/sql/func.sql
index af01e0fe..8d81c440 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -206,6 +206,22 @@ END;
$$ LANGUAGE plpgsql;
+-- Fully recalculate all rows in stats_cache
+CREATE OR REPLACE FUNCTION update_stats_cache_full() RETURNS void AS $$
+BEGIN
+ UPDATE stats_cache SET count = (SELECT COUNT(*) FROM users)-1 WHERE section = 'users';
+ UPDATE stats_cache SET count = (SELECT COUNT(*) FROM vn WHERE hidden = FALSE) WHERE section = 'vn';
+ UPDATE stats_cache SET count = (SELECT COUNT(*) FROM releases WHERE hidden = FALSE) WHERE section = 'releases';
+ UPDATE stats_cache SET count = (SELECT COUNT(*) FROM producers WHERE hidden = FALSE) WHERE section = 'producers';
+ UPDATE stats_cache SET count = (SELECT COUNT(*) FROM chars WHERE hidden = FALSE) WHERE section = 'chars';
+ UPDATE stats_cache SET count = (SELECT COUNT(*) FROM staff WHERE hidden = FALSE) WHERE section = 'staff';
+ UPDATE stats_cache SET count = (SELECT COUNT(*) FROM tags WHERE state = 2) WHERE section = 'tags';
+ UPDATE stats_cache SET count = (SELECT COUNT(*) FROM traits WHERE state = 2) WHERE section = 'traits';
+ UPDATE stats_cache SET count = (SELECT COUNT(*) FROM threads WHERE hidden = FALSE) WHERE section = 'threads';
+ UPDATE stats_cache SET count = (SELECT COUNT(*) FROM threads_posts WHERE hidden = FALSE
+ AND EXISTS(SELECT 1 FROM threads WHERE threads.id = tid AND threads.hidden = FALSE)) WHERE section = 'threads_posts';
+END;
+$$ LANGUAGE plpgsql;
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 7786e9b5..5eee0517 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -44,6 +44,35 @@
-- full SQL query parser.
+-- data types
+
+CREATE TYPE anime_type AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv');
+CREATE TYPE blood_type AS ENUM ('unknown', 'a', 'b', 'ab', 'o');
+CREATE TYPE board_type AS ENUM ('an', 'db', 'ge', 'v', 'p', 'u');
+CREATE TYPE char_role AS ENUM ('main', 'primary', 'side', 'appears');
+CREATE TYPE credit_type AS ENUM ('scenario', 'chardesign', 'art', 'music', 'songs', 'director', 'staff');
+CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p', 'c', 's', 'd');
+CREATE TYPE edit_rettype AS (itemid integer, chid integer, rev integer);
+CREATE TYPE gender AS ENUM ('unknown', 'm', 'f', 'b');
+CREATE TYPE language AS ENUM ('ar', 'bg', 'ca', 'cs', 'da', 'de', 'el', 'en', 'eo', 'es', 'fi', 'fr', 'he', 'hr', 'hu', 'id', 'it', 'ja', 'ko', 'nl', 'no', 'pl', 'pt-pt', 'pt-br', 'ro', 'ru', 'sk', 'sv', 'ta', 'th', 'tr', 'uk', 'vi', 'zh');
+CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
+CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel', 'listdel', 'dbedit', 'announce');
+CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 'c', 't', 's', 'd');
+CREATE TYPE platform AS ENUM ('win', 'dos', 'lin', 'mac', 'ios', 'and', 'dvd', 'bdp', 'fmt', 'gba', 'gbc', 'msx', 'nds', 'nes', 'p88', 'p98', 'pce', 'pcf', 'psp', 'ps1', 'ps2', 'ps3', 'ps4', 'psv', 'drc', 'sat', 'sfc', 'swi', 'wii', 'wiu', 'n3d', 'x68', 'xb1', 'xb3', 'xbo', 'web', 'oth');
+CREATE TYPE prefs_key AS ENUM ('l10n', 'skin', 'customcss', 'filter_vn', 'filter_release', 'show_nsfw', 'hide_list', 'notify_nodbedit', 'notify_announce', 'vn_list_own', 'vn_list_wish', 'tags_all', 'tags_cat', 'spoilers', 'traits_sexual');
+CREATE TYPE producer_type AS ENUM ('co', 'in', 'ng');
+CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori');
+CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial');
+CREATE TYPE tag_category AS ENUM('cont', 'ero', 'tech');
+CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
+CREATE TYPE resolution AS ENUM ('unknown', 'nonstandard', '640x480', '800x600', '1024x768', '1280x960', '1600x1200', '640x400', '960x600', '960x640', '1024x576', '1024x600', '1024x640', '1280x720', '1280x800', '1366x768', '1600x900', '1920x1080');
+
+-- Sequences used for ID generation of items not in the DB
+CREATE SEQUENCE covers_seq;
+CREATE SEQUENCE charimg_seq;
+
+
+
-- affiliate_links
CREATE TABLE affiliate_links (
id SERIAL PRIMARY KEY,
diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql
index 1e5e5f6f..0a5a82c7 100644
--- a/util/sql/tableattrs.sql
+++ b/util/sql/tableattrs.sql
@@ -1,3 +1,5 @@
+-- Constraints
+
ALTER TABLE affiliate_links ADD CONSTRAINT affiliate_links_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
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);
@@ -101,6 +103,9 @@ ALTER TABLE wlists ADD CONSTRAINT wlists_uid_fkey
ALTER TABLE wlists ADD CONSTRAINT wlists_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+
+-- Indices
+
CREATE INDEX affiliate_links_rid ON affiliate_links (rid) WHERE NOT hidden;
CREATE INDEX chars_main ON chars (main) WHERE main IS NOT NULL AND NOT hidden; -- Only used on /c+
CREATE INDEX chars_vns_vid ON chars_vns (vid);
@@ -124,3 +129,69 @@ CREATE INDEX votes_uid ON votes (uid);
CREATE UNIQUE INDEX changes_itemrev ON changes (type, itemid, rev);
CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (id, vid, COALESCE(rid, 0));
CREATE UNIQUE INDEX chars_vns_hist_pkey ON chars_vns_hist (chid, vid, COALESCE(rid, 0));
+
+
+
+-- Triggers
+
+CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
+CREATE TRIGGER users_votes_update AFTER INSERT OR DELETE ON votes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
+CREATE TRIGGER users_tags_update AFTER INSERT OR DELETE ON tags_vn FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
+
+CREATE TRIGGER stats_cache_new AFTER INSERT ON vn FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new AFTER INSERT ON producers FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new AFTER INSERT ON releases FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit AFTER UPDATE ON releases FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new AFTER INSERT ON chars FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit AFTER UPDATE ON chars FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new AFTER INSERT ON staff FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit AFTER UPDATE ON staff FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new AFTER INSERT ON tags FOR EACH ROW WHEN (NEW.state = 2) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit AFTER UPDATE ON tags FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new AFTER INSERT ON traits FOR EACH ROW WHEN (NEW.state = 2) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit AFTER UPDATE ON traits FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new AFTER INSERT ON threads FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit AFTER UPDATE ON threads FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit AFTER UPDATE ON threads_posts FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache AFTER INSERT OR DELETE ON users FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
+
+CREATE TRIGGER vn_anime_aid_new BEFORE INSERT ON vn_anime FOR EACH ROW EXECUTE PROCEDURE vn_anime_aid();
+CREATE TRIGGER vn_anime_aid_edit BEFORE UPDATE ON vn_anime FOR EACH ROW WHEN (OLD.aid IS DISTINCT FROM NEW.aid) EXECUTE PROCEDURE vn_anime_aid();
+
+CREATE TRIGGER anime_fetch_notify AFTER INSERT OR UPDATE ON anime FOR EACH ROW WHEN (NEW.lastfetch IS NULL) EXECUTE PROCEDURE anime_fetch_notify();
+
+CREATE TRIGGER producers_wikidata_new BEFORE INSERT ON producers FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert();
+CREATE TRIGGER producers_wikidata_edit BEFORE UPDATE ON producers FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert();
+CREATE TRIGGER producers_hist_wikidata_new BEFORE INSERT ON producers_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert();
+CREATE TRIGGER producers_hist_wikidata_edit BEFORE UPDATE ON producers_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert();
+CREATE TRIGGER staff_wikidata_new BEFORE INSERT ON staff FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert();
+CREATE TRIGGER staff_wikidata_edit BEFORE UPDATE ON staff FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert();
+CREATE TRIGGER staff_hist_wikidata_new BEFORE INSERT ON staff_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert();
+CREATE TRIGGER staff_hist_wikidata_edit BEFORE UPDATE ON staff_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert();
+CREATE TRIGGER vn_wikidata_new BEFORE INSERT ON vn FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert();
+CREATE TRIGGER vn_wikidata_edit BEFORE UPDATE ON vn FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert();
+CREATE TRIGGER vn_hist_wikidata_new BEFORE INSERT ON vn_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert();
+CREATE TRIGGER vn_hist_wikidata_edit BEFORE UPDATE ON vn_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert();
+
+CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW
+ WHEN ((OLD.rgraph IS NOT NULL AND NEW.rgraph IS NULL)
+ OR (NEW.rgraph IS NOT NULL AND (OLD.c_released IS DISTINCT FROM NEW.c_released OR OLD.c_languages IS DISTINCT FROM NEW.c_languages))
+ ) EXECUTE PROCEDURE vn_relgraph_notify();
+
+CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.rgraph IS NOT NULL AND NEW.rgraph IS NULL) EXECUTE PROCEDURE producer_relgraph_notify();
+
+CREATE TRIGGER insert_notify AFTER INSERT ON changes FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
+CREATE TRIGGER insert_notify AFTER INSERT ON threads_posts FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
+CREATE TRIGGER insert_notify AFTER INSERT ON tags FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
+CREATE TRIGGER insert_notify AFTER INSERT ON traits FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
+
+CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm();
+CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce();
+
+CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.c_search IS NOT NULL AND NEW.c_search IS NULL) EXECUTE PROCEDURE vn_vnsearch_notify();
+
+CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER DELETE ON vnlists DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
+CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER INSERT ON rlists DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
diff --git a/util/sql/triggers.sql b/util/sql/triggers.sql
deleted file mode 100644
index 67a49991..00000000
--- a/util/sql/triggers.sql
+++ /dev/null
@@ -1,61 +0,0 @@
-CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
-CREATE TRIGGER users_votes_update AFTER INSERT OR DELETE ON votes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
-CREATE TRIGGER users_tags_update AFTER INSERT OR DELETE ON tags_vn FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
-
-CREATE TRIGGER stats_cache_new AFTER INSERT ON vn FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_edit AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_new AFTER INSERT ON producers FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_edit AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_new AFTER INSERT ON releases FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_edit AFTER UPDATE ON releases FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_new AFTER INSERT ON chars FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_edit AFTER UPDATE ON chars FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_new AFTER INSERT ON staff FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_edit AFTER UPDATE ON staff FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_new AFTER INSERT ON tags FOR EACH ROW WHEN (NEW.state = 2) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_edit AFTER UPDATE ON tags FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_new AFTER INSERT ON traits FOR EACH ROW WHEN (NEW.state = 2) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_edit AFTER UPDATE ON traits FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_new AFTER INSERT ON threads FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_edit AFTER UPDATE ON threads FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_new AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_edit AFTER UPDATE ON threads_posts FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache AFTER INSERT OR DELETE ON users FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
-
-CREATE TRIGGER vn_anime_aid_new BEFORE INSERT ON vn_anime FOR EACH ROW EXECUTE PROCEDURE vn_anime_aid();
-CREATE TRIGGER vn_anime_aid_edit BEFORE UPDATE ON vn_anime FOR EACH ROW WHEN (OLD.aid IS DISTINCT FROM NEW.aid) EXECUTE PROCEDURE vn_anime_aid();
-
-CREATE TRIGGER anime_fetch_notify AFTER INSERT OR UPDATE ON anime FOR EACH ROW WHEN (NEW.lastfetch IS NULL) EXECUTE PROCEDURE anime_fetch_notify();
-
-CREATE TRIGGER producers_wikidata_new BEFORE INSERT ON producers FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER producers_wikidata_edit BEFORE UPDATE ON producers FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER producers_hist_wikidata_new BEFORE INSERT ON producers_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER producers_hist_wikidata_edit BEFORE UPDATE ON producers_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER staff_wikidata_new BEFORE INSERT ON staff FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER staff_wikidata_edit BEFORE UPDATE ON staff FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER staff_hist_wikidata_new BEFORE INSERT ON staff_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER staff_hist_wikidata_edit BEFORE UPDATE ON staff_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER vn_wikidata_new BEFORE INSERT ON vn FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER vn_wikidata_edit BEFORE UPDATE ON vn FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER vn_hist_wikidata_new BEFORE INSERT ON vn_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER vn_hist_wikidata_edit BEFORE UPDATE ON vn_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert();
-
-CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW
- WHEN ((OLD.rgraph IS NOT NULL AND NEW.rgraph IS NULL)
- OR (NEW.rgraph IS NOT NULL AND (OLD.c_released IS DISTINCT FROM NEW.c_released OR OLD.c_languages IS DISTINCT FROM NEW.c_languages))
- ) EXECUTE PROCEDURE vn_relgraph_notify();
-
-CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.rgraph IS NOT NULL AND NEW.rgraph IS NULL) EXECUTE PROCEDURE producer_relgraph_notify();
-
-CREATE TRIGGER insert_notify AFTER INSERT ON changes FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
-CREATE TRIGGER insert_notify AFTER INSERT ON threads_posts FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
-CREATE TRIGGER insert_notify AFTER INSERT ON tags FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
-CREATE TRIGGER insert_notify AFTER INSERT ON traits FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
-
-CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm();
-CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce();
-
-CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.c_search IS NOT NULL AND NEW.c_search IS NULL) EXECUTE PROCEDURE vn_vnsearch_notify();
-
-CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER DELETE ON vnlists DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
-CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER INSERT ON rlists DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();