diff options
author | Yorhel <git@yorhel.nl> | 2020-09-12 15:21:13 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2020-09-12 15:21:16 +0200 |
commit | 722bb447e6218d522562007a4c8dcc4eb31f6582 (patch) | |
tree | b87b8dc999f9865fb970e60fb9ffb6a60fb2d3db /lib/VNDB/DB/VN.pm | |
parent | f263c9616fa9790dc7c20bbbb021078d8d44a1fd (diff) |
v2rw: Add new filter querying framework + rewrite homepage
The new filter framework hasn't been extensively tested yet, but seems
to mostly work.
No real changes to the homepage, but I did manage to make a few queries
a little bit faster. The most significant source of slowness is the
releases listing, but I can't optimize that without adding indices.
Diffstat (limited to 'lib/VNDB/DB/VN.pm')
-rw-r--r-- | lib/VNDB/DB/VN.pm | 43 |
1 files changed, 1 insertions, 42 deletions
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index 734ae69b..668b7fec 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -9,7 +9,7 @@ use POSIX 'strftime'; use Exporter 'import'; use VNDB::Func 'normalize_query', 'gtintype'; -our @EXPORT = qw|dbVNGet dbVNGetRev dbScreenshotRandom|; +our @EXPORT = qw|dbVNGet dbVNGetRev|; # Options: id, char, search, gtin, length, lang, olang, plat, tag_inc, tag_exc, tagspoil, @@ -254,45 +254,4 @@ sub _enrich { } - -# Fetch random VN + screenshots -# if any arguments are given, it will return one random screenshot for each VN -sub dbScreenshotRandom { - my($self, @vids) = @_; - if(!@vids) { - my $where = q{c_weight > 0 and vndbid_type(id) = 'sf' and c_sexual_avg < 0.4 and c_violence_avg < 0.4}; - state $stats ||= $self->dbRow("SELECT count(*) as total, count(*) filter(where $where) as subset from images"); - my $sample = 100*List::Util::min(1, (1000 / $stats->{subset}) * ($stats->{total} / $stats->{subset})); - return $self->dbAll(q{ - SELECT vndbid_num(i.id) AS scr, i.width, i.height, v.id AS vid, v.title - FROM ( - SELECT id, width, height - FROM images TABLESAMPLE SYSTEM (?) - WHERE c_weight > 0 and vndbid_type(id) = 'sf' and c_sexual_avg < 0.4 and c_violence_avg < 0.4 - ORDER BY random() - LIMIT 4 - ) i(id) - JOIN vn_screenshots vs ON vs.scr = i.id - JOIN vn v ON v.id = vs.id - WHERE NOT v.hidden - ORDER BY random() - LIMIT 4 - }, $sample); - } - - # this query is faster than it looks - return $self->dbAll(join(' UNION ALL ', map - q|SELECT vndbid_num(vs.scr) AS scr, vs.width, vs.height, v.id AS vid, v.title, RANDOM() AS position - FROM ( - SELECT vs2.id, vs2.scr, s.width, s.height - FROM vn_screenshots vs2 - JOIN images s ON s.id = vs2.scr - WHERE vs2.id = ? AND s.c_sexual_avg < 0.4 AND s.c_violence_avg < 0.4 - ORDER BY RANDOM() LIMIT 1 - ) vs - JOIN vn v ON v.id = vs.id - |, @vids).' ORDER BY position', @vids); -} - - 1; |