diff options
author | Yorhel <git@yorhel.nl> | 2019-08-15 16:17:13 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2019-08-15 16:17:16 +0200 |
commit | 5a173c85c30acd3c5d80f9a0b6dd250efb61ad81 (patch) | |
tree | 359fad2a80bdfad1e7358872cc29bea57dac5208 /util/devdump.pl | |
parent | 30cdc48d9ddbfe5223ebcfe5cdff3cdab372d894 (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.
Diffstat (limited to 'util/devdump.pl')
-rwxr-xr-x | util/devdump.pl | 49 |
1 files changed, 15 insertions, 34 deletions
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; |