summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB/VN.pm
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2020-09-12 15:21:13 +0200
committerYorhel <git@yorhel.nl>2020-09-12 15:21:16 +0200
commit722bb447e6218d522562007a4c8dcc4eb31f6582 (patch)
treeb87b8dc999f9865fb970e60fb9ffb6a60fb2d3db /lib/VNDB/DB/VN.pm
parentf263c9616fa9790dc7c20bbbb021078d8d44a1fd (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.pm43
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;