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 /lib/Multi/API.pm | |
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 'lib/Multi/API.pm')
-rw-r--r-- | lib/Multi/API.pm | 6 |
1 files changed, 3 insertions, 3 deletions
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm index 1ece822b..22152aa0 100644 --- a/lib/Multi/API.pm +++ b/lib/Multi/API.pm @@ -417,7 +417,7 @@ my %GET_VN = ( }, }, details => { - select => '(v.image).id as image, v.img_nsfw, v.alias AS aliases, v.length, v.desc AS description, v.l_wp, v.l_encubed, v.l_renai, l_wikidata', + select => 'vndbid_num(v.image) as image, v.img_nsfw, v.alias AS aliases, v.length, v.desc AS description, v.l_wp, v.l_encubed, v.l_renai, l_wikidata', proc => sub { $_[0]{aliases} ||= undef; $_[0]{length} *= 1; @@ -490,7 +490,7 @@ my %GET_VN = ( ]], }, screens => { - fetch => [[ 'id', 'SELECT vs.id AS vid, (vs.scr).id AS image, vs.rid, vs.nsfw, s.width, s.height + fetch => [[ 'id', 'SELECT vs.id AS vid, vndbid_num(vs.scr) AS image, vs.rid, vs.nsfw, s.width, s.height FROM vn_screenshots vs JOIN images s ON s.id = vs.scr WHERE vs.id IN(%s)', sub { my($r, $n) = @_; for my $i (@$r) { @@ -817,7 +817,7 @@ my %GET_CHARACTER = ( }, }, details => { - select => 'c.alias AS aliases, (c.image).id as image, c."desc" AS description', + select => 'c.alias AS aliases, vndbid_num(c.image) as image, c."desc" AS description', proc => sub { $_[0]{aliases} ||= undef; $_[0]{image} = $_[0]{image} ? sprintf '%s/ch/%02d/%d.jpg', config->{url_static}, $_[0]{image}%100, $_[0]{image} : undef; |