diff options
author | Yorhel <git@yorhel.nl> | 2020-04-05 15:18:05 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2020-04-05 15:18:27 +0200 |
commit | 6d767e48b63c30fd572cff1fdf73bb2e60574848 (patch) | |
tree | b330f7f37b42293fad2579fb7788e2be5ca7ecb6 /util | |
parent | a53e57953a9f4a9912c70901b3841fd30a80bd1d (diff) |
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.
Diffstat (limited to 'util')
-rwxr-xr-x | util/dbdump.pl | 15 | ||||
-rwxr-xr-x | util/devdump.pl | 5 | ||||
-rwxr-xr-x | util/docker-init.sh | 8 | ||||
-rwxr-xr-x | util/unusedimages.pl | 4 | ||||
-rw-r--r-- | util/updates/2020-04-05-vndbid-for-images.sql | 51 | ||||
-rwxr-xr-x | util/vndb.pl | 2 |
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; } |