summaryrefslogtreecommitdiff
path: root/lib/Multi/API.pm
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2020-04-05 15:18:05 +0200
committerYorhel <git@yorhel.nl>2020-04-05 15:18:27 +0200
commit6d767e48b63c30fd572cff1fdf73bb2e60574848 (patch)
treeb330f7f37b42293fad2579fb7788e2be5ca7ecb6 /lib/Multi/API.pm
parenta53e57953a9f4a9912c70901b3841fd30a80bd1d (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.pm6
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;