summaryrefslogtreecommitdiff
path: root/lib/VNWeb/Misc/HomePage.pm
diff options
context:
space:
mode:
Diffstat (limited to 'lib/VNWeb/Misc/HomePage.pm')
-rw-r--r--lib/VNWeb/Misc/HomePage.pm108
1 files changed, 63 insertions, 45 deletions
diff --git a/lib/VNWeb/Misc/HomePage.pm b/lib/VNWeb/Misc/HomePage.pm
index 64238e1c..86254fcd 100644
--- a/lib/VNWeb/Misc/HomePage.pm
+++ b/lib/VNWeb/Misc/HomePage.pm
@@ -5,18 +5,19 @@ use VNWeb::AdvSearch;
use VNWeb::Discussions::Lib 'enrich_boards';
-sub screens_ {
- state $where ||= sql 'i.c_weight > 0 and vndbid_type(i.id) =', \'sf', 'and i.c_sexual_avg <', \0.4, 'and i.c_violence_avg <', \0.4;
+sub screens {
+ state $where ||= sql 'i.c_weight > 0 and vndbid_type(i.id) =', \'sf', 'and i.c_sexual_avg <', \40, 'and i.c_violence_avg <', \40;
state $stats ||= tuwf->dbRowi('SELECT count(*) as total, count(*) filter(where', $where, ') as subset from images i');
- state $sample ||= 100*min 1, (200 / $stats->{subset}) * ($stats->{total} / $stats->{subset});
+ state $sample ||= 100*min 1, (200 / (1+$stats->{subset})) * ($stats->{total} / (1+$stats->{subset}));
my $filt = advsearch_default 'v';
+ my $start = time;
my $lst = $filt->{query} ? tuwf->dbAlli(
# Assumption: If we randomly select 30 matching VNs, there'll be at least 4 VNs with qualified screenshots
# (As of Sep 2020, over half of the VNs in the database have screenshots, so that assumption usually works)
'SELECT * FROM (
SELECT DISTINCT ON (v.id) i.id, i.width, i,height, v.id AS vid, v.title
- FROM (SELECT id, title FROM vn v WHERE NOT v.hidden AND ', $filt->sql_where(), ' ORDER BY random() LIMIT', \30, ') v
+ FROM (SELECT id, title FROM', vnt, 'v WHERE NOT v.hidden AND ', $filt->sql_where(), ' ORDER BY random() LIMIT', \30, ') v
JOIN vn_screenshots vs ON v.id = vs.id
JOIN images i ON i.id = vs.scr
WHERE ', $where, '
@@ -26,18 +27,12 @@ sub screens_ {
SELECT i.id, i.width, i.height, v.id AS vid, v.title
FROM (SELECT id, width, height FROM images i TABLESAMPLE SYSTEM (', \$sample, ') WHERE', $where, ' ORDER BY random() LIMIT', \4, ') i(id)
JOIN vn_screenshots vs ON vs.scr = i.id
- JOIN vn v ON v.id = vs.id
+ JOIN', vnt, 'v ON v.id = vs.id
WHERE NOT v.hidden
ORDER BY random()
LIMIT', \4
);
-
- p_ class => 'screenshots', sub {
- a_ href => "/$_->{vid}", title => $_->{title}, sub {
- my($w, $h) = imgsize $_->{width}, $_->{height}, config->{scr_size}->@*;
- img_ src => imgurl($_->{id}, 1), alt => $_->{title}, width => $w, height => $h;
- } for @$lst;
- }
+ ($lst, $filt->{query} && time - $start > 0.3)
}
@@ -46,14 +41,14 @@ sub recent_changes_ {
h1_ sub {
a_ href => '/hist', 'Recent Changes'; txt_ ' ';
a_ href => '/feeds/changes.atom', sub {
- img_ src => config->{url_static}.'/f/rss.svg', title => 'Atom feed', width => 14, height => 14;
+ abbr_ class => 'icon-rss', title => 'Atom feed', '';
}
};
ul_ sub {
li_ sub {
span_ sub {
txt_ "$1:" if $_->{itemid} =~ /^(.)/;
- a_ href => "/$_->{itemid}.$_->{rev}", title => $_->{original}||$_->{title}, $_->{title};
+ a_ href => "/$_->{itemid}.$_->{rev}", tattr $_;
};
span_ sub {
lit_ " by ";
@@ -70,7 +65,7 @@ sub recent_db_posts_ {
FROM threads t
JOIN threads_boards tb ON tb.tid = t.id AND tb.type = \'an\'
JOIN threads_posts tp ON tp.tid = t.id AND tp.num = 1
- WHERE NOT t.hidden AND NOT t.private AND tp.date >', sql_fromtime(time-2*30*24*3600), '
+ WHERE NOT t.hidden AND NOT t.private AND tp.date >', sql_fromtime(time-30*24*3600), '
ORDER BY tb.tid DESC
LIMIT 1+1'
);
@@ -87,7 +82,7 @@ sub recent_db_posts_ {
enrich_boards undef, $lst;
p_ class => 'mainopts', sub {
a_ href => '/t/an', 'Announcements';
- b_ class => 'grayedout', '&';
+ small_ '&';
a_ href => '/t/db', 'VNDB';
};
h1_ sub {
@@ -98,7 +93,7 @@ sub recent_db_posts_ {
a_ href => "/$_->{id}", $_->{title};
} for @$an;
li_ sub {
- my $boards = join ', ', map $BOARD_TYPE{$_->{btype}}{txt}.($_->{iid}?' > '.$_->{title}:''), $_->{boards}->@*;
+ my $boards = join ', ', map $BOARD_TYPE{$_->{btype}}{txt}.($_->{iid}?' > '.$_->{title}[1]:''), $_->{boards}->@*;
span_ sub {
txt_ fmtage($_->{date}).' ';
a_ href => "/$_->{id}.$_->{num}#last", title => "Posted in $boards", $_->{title};
@@ -115,7 +110,7 @@ sub recent_db_posts_ {
sub recent_vn_posts_ {
my $lst = tuwf->dbAlli('
WITH tposts (id,title,num,date,uid) AS (
- SELECT t.id, t.title, tp.num, tp.date, tp.uid
+ SELECT t.id, ARRAY[NULL, t.title], tp.num, tp.date, tp.uid
FROM threads t
JOIN threads_posts tp ON tp.tid = t.id AND tp.num = t.c_lastnum
WHERE NOT EXISTS(SELECT 1 FROM threads_boards tb WHERE tb.tid = t.id AND tb.type IN(\'an\',\'db\',\'u\'))
@@ -125,9 +120,9 @@ sub recent_vn_posts_ {
SELECT w.id, v.title, wp.num, wp.date, wp.uid
FROM reviews w
JOIN reviews_posts wp ON wp.id = w.id AND wp.num = w.c_lastnum
- JOIN vn v ON v.id = w.vid
+ JOIN', vnt, 'v ON v.id = w.vid
LEFT JOIN users u ON wp.uid = u.id
- WHERE NOT w.c_flagged AND NOT wp.hidden
+ WHERE NOT w.c_flagged AND wp.hidden IS NULL
ORDER BY wp.date DESC LIMIT 10
) SELECT x.id, x.num, x.title,', sql_totime('x.date'), 'AS date, ', sql_user(), '
FROM (SELECT * FROM tposts UNION ALL SELECT * FROM wposts) x
@@ -138,7 +133,7 @@ sub recent_vn_posts_ {
enrich_boards undef, $lst;
p_ class => 'mainopts', sub {
a_ href => '/t/all', 'Forums';
- b_ class => 'grayedout', '&';
+ small_ '&';
a_ href => '/w?o=d&s=lastpost', 'Reviews';
};
h1_ sub {
@@ -147,9 +142,9 @@ sub recent_vn_posts_ {
ul_ sub {
li_ sub {
span_ sub {
- my $boards = join ', ', map $BOARD_TYPE{$_->{btype}}{txt}.($_->{iid}?' > '.$_->{title}:''), $_->{boards}->@*;
+ my $boards = join ', ', map $BOARD_TYPE{$_->{btype}}{txt}.($_->{iid}?' > '.$_->{title}[1]:''), $_->{boards}->@*;
txt_ fmtage($_->{date}).' ';
- a_ href => "/$_->{id}.$_->{num}#last", title => $boards ? "Posted in $boards" : 'Review', $_->{title};
+ a_ href => "/$_->{id}.$_->{num}#last", title => $boards ? "Posted in $boards" : 'Review', tlang(@{$_->{title}}[0,1]), $_->{title}[1];
};
span_ sub {
lit_ ' by ';
@@ -161,7 +156,7 @@ sub recent_vn_posts_ {
-sub releases_ {
+sub releases {
my($released) = @_;
my $filt = advsearch_default 'r';
@@ -169,20 +164,28 @@ sub releases_ {
# Drop any top-level date filters
$filt->{query} = [ grep !(ref $_ eq 'ARRAY' && $_->[0] eq 'released'), $filt->{query}->@* ] if $filt->{query};
delete $filt->{query} if $filt->{query} && ($filt->{query}[0] eq 'released' || $filt->{query}->@* < 2);
+ my $has_saved = !!$filt->{query};
# Add the release date as filter, we need to construct a filter for the header link anyway
$filt->{query} = [ 'and', [ released => $released ? '<=' : '>', 1 ], $filt->{query} || () ];
- # XXX This query is kinda slow, an index on releases.released would probably help.
+ my $start = time;
my $lst = tuwf->dbAlli('
- SELECT id, title, original, released
- FROM releases r
+ SELECT id, title, released
+ FROM', releasest, 'r
WHERE NOT hidden AND ', $filt->sql_where(), '
- AND NOT EXISTS(SELECT 1 FROM releases_lang rl WHERE rl.id = r.id AND rl.mtl)
+ AND NOT EXISTS(SELECT 1 FROM releases_titles rt WHERE rt.id = r.id AND rt.mtl)
ORDER BY released', $released ? 'DESC' : '', ', id LIMIT 10'
);
+ my $end = time;
enrich_flatten plat => id => id => 'SELECT id, platform FROM releases_platforms WHERE id IN', $lst;
- enrich_flatten lang => id => id => 'SELECT id, lang FROM releases_lang WHERE id IN', $lst;
+ enrich_flatten lang => id => id => 'SELECT id, lang FROM releases_titles WHERE id IN', $lst;
+ ($lst, $filt, $has_saved && $end-$start > 0.3)
+}
+
+
+sub releases_ {
+ my($lst, $filt, $released) = @_;
h1_ sub {
a_ href => '/r?f='.$filt->query_encode().';o=a;s=released', 'Upcoming Releases' if !$released;
@@ -194,9 +197,9 @@ sub releases_ {
rdate_ $_->{released};
txt_ ' ';
platform_ $_ for $_->{plat}->@*;
- abbr_ class => "icons lang $_", title => $LANGUAGE{$_}, '' for $_->{lang}->@*;
+ abbr_ class => "icon-lang-$_", title => $LANGUAGE{$_}{txt}, '' for $_->{lang}->@*;
txt_ ' ';
- a_ href => "/$_->{id}", title => $_->{original}||$_->{title}, $_->{title};
+ a_ href => "/$_->{id}", tattr $_;
}
} for @$lst;
};
@@ -207,7 +210,7 @@ sub reviews_ {
my $lst = tuwf->dbAlli('
SELECT w.id, v.title, w.isfull, ', sql_user(), ',', sql_totime('w.date'), 'AS date
FROM reviews w
- JOIN vn v ON v.id = w.vid
+ JOIN', vnt, 'v ON v.id = w.vid
LEFT JOIN users u ON u.id = w.uid
WHERE NOT w.c_flagged
ORDER BY w.id DESC LIMIT 10'
@@ -219,8 +222,8 @@ sub reviews_ {
li_ sub {
span_ sub {
txt_ fmtage($_->{date}).' ';
- b_ class => 'grayedout', $_->{isfull} ? ' Full ' : ' Mini ';
- a_ href => "/$_->{id}", title => $_->{title}, $_->{title};
+ small_ $_->{isfull} ? ' Full ' : ' Mini ';
+ a_ href => "/$_->{id}", tattr $_;
};
span_ sub {
lit_ 'by ';
@@ -238,8 +241,13 @@ TUWF::get qr{/}, sub {
'description' => 'VNDB.org strives to be a comprehensive database for information about visual novels.',
);
+ my($screens, $slowscreens) = screens;
+ my($rel0, $filt0, $slowrel0) = releases 0;
+ my($rel1, $filt1, $slowrel1) = releases 1;
+ my $slowrel = $slowrel0 || $slowrel1;
+
framework_ title => $meta{title}, feeds => 1, og => \%meta, index => 1, sub {
- div_ class => 'mainbox', sub {
+ article_ sub {
h1_ $meta{title};
p_ class => 'description', sub {
txt_ $meta{description};
@@ -250,17 +258,27 @@ TUWF::get qr{/}, sub {
largest, most accurate and most up-to-date visual novel database on the web.
};
};
- screens_;
- };
- div_ class => 'homepage threelayout', sub {
- div_ \&recent_changes_;
- div_ \&recent_db_posts_;
- div_ \&recent_vn_posts_;
+ p_ class => 'screenshots', sub {
+ a_ href => "/$_->{vid}", title => $_->{title}[1], sub {
+ my($w, $h) = imgsize $_->{width}, $_->{height}, config->{scr_size}->@*;
+ img_ src => imgurl($_->{id}, 't'), alt => $_->{title}[1], width => $w, height => $h;
+ } for @$screens;
+ };
+ p_ class => 'center standout', sub {
+ txt_ 'If VNDB appears to load a little slow for you, try clearing or adjusting your ';
+ a_ href => '/v', 'saved visual novel filters' if $slowscreens;
+ txt_ ' or ' if $slowscreens && $slowrel;
+ a_ href => '/r', 'saved release filters' if $slowrel;
+ txt_ '.';
+ } if $slowscreens || $slowrel;
};
- div_ class => 'homepage threelayout', sub {
- div_ sub { reviews_ };
- div_ sub { releases_ 0 };
- div_ sub { releases_ 1 };
+ div_ class => 'homepage', sub {
+ article_ \&recent_changes_;
+ article_ \&recent_db_posts_;
+ article_ \&recent_vn_posts_;
+ article_ sub { reviews_ };
+ article_ sub { releases_ $rel0, $filt0, 0 };
+ article_ sub { releases_ $rel1, $filt1, 1 };
};
};
};
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-19 00:23:05 +0000