VN listing: Add table options + developer and raw average columns

With additional VN cache columns for the new developers and average
table columns. The developers cache is also used by the AdvSearch to
potentially speed up some queries (and slow down others).

I also changed the popularity and rating caches to smallint. Doesn't
save anything with the current padding, but there's not much point in
using a floating point type when the values get rounded anyway.

12 files changed, 271 insertions, 115 deletions

diff --git a/css/v2.css b/css/v2.css
index f851127c..613f6a39 100644
--- a/css/v2.css
+++ b/css/v2.css
@@ -628,13 +628,15 @@ div#vntags                    { margin: 0 30px 0 30px; border-top: 1px solid $bo
 
 /****** VN browse ********/
 
-.vnbrowse .tc_s       { padding-left: 30px; width: 70px  }
-.vnbrowse .tc2        { text-align: right; padding: 0; }
-.vnbrowse .tc3        { padding: 0; }
-.vnbrowse .tc5        { text-align: right; padding-right: 10px }
-.vnbrowse .tc6        { width: 80px }
-.vnbrowse .tc7        { text-align: right; width: 8px; white-space: nowrap }
-.vnbrowse .tc7 abbr   { display: inline-block; width: 20px; }
+.vnbrowse .tc_score   { padding-left: 30px; width: 70px  }
+.vnbrowse .tc_title   { padding-left: 30px }
+.vnbrowse .tc_score + td { padding-left: 0 }
+.vnbrowse .tc_ulist       { text-align: right; width: 8px; white-space: nowrap }
+.vnbrowse .tc_ulist abbr  { display: inline-block; width: 20px; }
+.vnbrowse .tc_plat        { text-align: right; padding: 0; }
+.vnbrowse .tc_lang        { padding: 0; }
+.vnbrowse .tc_pop         { text-align: right; padding-right: 10px }
+.vnbrowse .tc_rating, .vnbrowse .tc_average { width: 80px; white-space: nowrap }
 
 
 
diff --git a/elm/TableOpts.elm b/elm/TableOpts.elm
index 7dcfec2a..6ecf0aa9 100644
--- a/elm/TableOpts.elm
+++ b/elm/TableOpts.elm
@@ -50,6 +50,8 @@ type Msg
   = Open Bool
   | View Int Bool
   | Results Int Bool
+  | Sort Int Bool Bool
+  | Cols Int Bool
   | Save
   | Saved GApi.Response
 
@@ -60,6 +62,8 @@ update msg model =
     Open b      -> ({ model | saved = False, dd = DD.toggle model.dd b }, Cmd.none)
     View n _    -> ({ model | saved = False, view = n }, Cmd.none)
     Results n _ -> ({ model | saved = False, results = n }, Cmd.none)
+    Sort n b _  -> ({ model | saved = False, sort = n, asc = b }, Cmd.none)
+    Cols n b    -> ({ model | cols = if b then B.or model.cols (B.shiftLeftBy n 1) else B.and model.cols (B.xor (B.complement 0) (B.shiftLeftBy n 1)) }, Cmd.none)
     Save        -> ( { model | saved = False, state = Api.Loading }
                    , GTO.send { save = Maybe.withDefault "" model.opts.save
                               , value = if encInt model == model.opts.default then Nothing else Just (encInt model)
@@ -90,11 +94,25 @@ view model = div []
   , DD.view model.dd Api.Normal
       (text "display options")
       (\_ -> [ table [ style "min-width" "300px" ]
-        [ tr [] [ td [] [ text "Format" ], td [] -- TODO: Icons, or some sort of preview?
-          [ linkRadio (model.view == 0) (View 0) [ text "Rows"  ], text " / "
-          , linkRadio (model.view == 1) (View 1) [ text "Cards" ], text " / "
-          , linkRadio (model.view == 2) (View 2) [ text "Grid"  ]
-          ] ]
+
+        -- TODO: Format icons, or some sort of preview?
+        [ if List.isEmpty model.opts.views then text "" else
+          tr [] [ td [] [ text "Format" ], td [] <| List.intersperse (text " / ") <| List.map (\o ->
+              linkRadio (model.view == o) (View o) [ text (if o == 0 then "Rows" else if o == 1 then "Cards" else "Grid") ]
+            ) model.opts.views ]
+
+        , if List.isEmpty model.opts.sorts then text "" else
+          tr [] [ td [] [ text "Order by" ], td [] <| List.intersperse (br [] []) <| List.map (\o ->
+              linkRadio (model.sort == o.id) (Sort o.id (if model.sort == o.id then not model.asc else True))
+              [ text o.name
+              , text <| if model.sort /= o.id then "" else if model.asc then " ▴" else " ▾" ]
+            ) model.opts.sorts ]
+
+        , if List.isEmpty model.opts.vis then text "" else
+          tr [] [ td [] [ text "Visible", br [] [], text "columns" ], td [] <| List.intersperse (br [] []) <| List.map (\o ->
+              linkRadio (B.and model.cols (B.shiftLeftBy o.id 1) > 0) (Cols o.id) [ text o.name ]
+            ) model.opts.vis ]
+
         , tr [] [ td [] [ text "Results" ], td []
           [ linkRadio (model.results == 1) (Results 1) [ text "10"  ], text " / "
           , linkRadio (model.results == 2) (Results 2) [ text "25"  ], text " / "
@@ -102,6 +120,7 @@ view model = div []
           , linkRadio (model.results == 3) (Results 3) [ text "100" ], text " / "
           , linkRadio (model.results == 4) (Results 4) [ text "200" ]
           ] ]
+
         , tr [] [ td [] [], td []
           [ input [ type_ "submit", class "submit", value "Update" ] []
           , case (model.opts.save, model.saved) of
diff --git a/lib/VNWeb/AdvSearch.pm b/lib/VNWeb/AdvSearch.pm
index e46b8e53..b1c58062 100644
--- a/lib/VNWeb/AdvSearch.pm
+++ b/lib/VNWeb/AdvSearch.pm
@@ -311,10 +311,11 @@ my @TYPE; # stack of query types, $TYPE[0] is the top-level query, $TYPE[$#TYPE]
 f v =>  2 => 'lang',      { enum => \%LANGUAGE }, '=' => sub { sql 'v.c_languages && ARRAY', \$_, '::language[]' };
 f v =>  3 => 'olang',     { enum => \%LANGUAGE }, '=' => sub { sql 'v.olang =', \$_ };
 f v =>  4 => 'platform',  { enum => \%PLATFORM }, '=' => sub { sql 'v.c_platforms && ARRAY', \$_, '::platform[]' };
+f v =>  6 => 'developer-id',{ vndbid => 'p' }, '=' => sub { sql 'v.c_developers && ARRAY', \$_, '::vndbid[]' };
 f v =>  5 => 'length',    { uint => 1, enum => \%VN_LENGTH }, '=' => sub { sql 'v.length =', \$_ };
 f v =>  7 => 'released',  { fuzzyrdate => 1 }, sql => sub { sql 'v.c_released', $_[0], \($_ == 1 ? strftime('%Y%m%d', gmtime) : $_) };
 f v =>  9 => 'popularity',{ uint => 1, range => [ 0,  100] }, sql => sub { sql 'v.c_popularity', $_[0], \($_/100) };
-f v => 10 => 'rating',    { uint => 1, range => [10,  100] }, sql => sub { sql 'v.c_rating <> 0 AND v.c_rating', $_[0], \$_ };
+f v => 10 => 'rating',    { uint => 1, range => [10,  100] }, sql => sub { sql 'v.c_rating', $_[0], \$_ };
 f v => 11 => 'vote-count',{ uint => 1, range => [ 0,1<<30] }, sql => sub { sql 'v.c_votecount', $_[0], \$_ };
 f v => 61 => 'has-description', { uint => 1, range => [1,1] }, '=' => sub { 'v."desc" <> \'\'' };
 f v => 62 => 'has-anime',       { uint => 1, range => [1,1] }, '=' => sub { 'EXISTS(SELECT 1 FROM vn_anime va WHERE va.id = v.id)' };
@@ -322,13 +323,6 @@ f v => 63 => 'has-screenshot',  { uint => 1, range => [1,1] }, '=' => sub { 'EXI
 f v => 64 => 'has-review',      { uint => 1, range => [1,1] }, '=' => sub { 'EXISTS(SELECT 1 FROM reviews r WHERE r.vid = v.id AND NOT r.c_flagged)' };
 f v => 65 => 'on-list',         { uint => 1, range => [1,1] }, '=' => sub { auth ? sql 'v.id IN(SELECT vid FROM ulist_vns WHERE uid =', \auth->uid, ')' : '1=0' };
 
-f v =>  6 => 'developer-id',{ vndbid => 'p' },
-    sql_list => sub {
-        my($neg, $all, $val) = @_;
-        sql 'v.id', $neg ? 'NOT' : '', 'IN(SELECT rv.vid FROM releases r JOIN releases_vn rv ON rv.id = r.id JOIN releases_producers rp ON rp.id = r.id
-                               WHERE NOT r.hidden AND rp.developer AND rp.pid IN', $val, $all && @$val > 1 ? ('GROUP BY rv.vid HAVING COUNT(rp.pid) =', \scalar @$val) : (), ')';
-    };
-
 f v =>  8 => 'tag',      { type => 'any', func => \&_validate_tag },
     compact => sub { my $id = ($_->[0] =~ s/^g//r)*1; $_->[1] == 0 && $_->[2] == 0 ? $id : [ $id, int($_->[2]*5)*3 + $_->[1] ] },
     sql_list => \&_sql_where_tag;
diff --git a/lib/VNWeb/HTML.pm b/lib/VNWeb/HTML.pm
index 8ec6ea85..58f0cf22 100644
--- a/lib/VNWeb/HTML.pm
+++ b/lib/VNWeb/HTML.pm
@@ -806,14 +806,20 @@ sub paginate_ {
 
 
 # Generate sort buttons for a table header. This function assumes that sorting
-# options are given as query parameters: 's' for the $column_name to sort on
-# and 'o' for order ('a'sc/'d'esc).
+# options are given either as a TableOpts parameter in 's' or as two query
+# parameters: 's' for the $column_name to sort on and 'o' for order ('a'/'d').
 # Options: $column_title, $column_name, $opt, $url
 # Where $url is a function that is given ('p', undef, 's', $column_name, 'o', $order) and returns a URL.
 sub sortable_ {
     my($name, $opt, $url) = @_;
-    $opt->{s} eq $name && $opt->{o} eq 'a' ? txt_ ' ▴' : a_ href => $url->(p => undef, s => $name, o => 'a'), ' ▴';
-    $opt->{s} eq $name && $opt->{o} eq 'd' ? txt_  '▾' : a_ href => $url->(p => undef, s => $name, o => 'd'),  '▾';
+    if(ref $opt->{s}) {
+        my $o = $opt->{s}->sorted($name);
+        $o eq 'a' ? txt_ ' ▴' : a_ href => $url->(p => undef, s => $opt->{s}->sort_param($name, 'a')), ' ▴';
+        $o eq 'd' ? txt_  '▾' : a_ href => $url->(p => undef, s => $opt->{s}->sort_param($name, 'd')),  '▾';
+    } else {
+        $opt->{s} eq $name && $opt->{o} eq 'a' ? txt_ ' ▴' : a_ href => $url->(p => undef, s => $name, o => 'a'), ' ▴';
+        $opt->{s} eq $name && $opt->{o} eq 'd' ? txt_  '▾' : a_ href => $url->(p => undef, s => $name, o => 'd'),  '▾';
+    }
 }
 
 
diff --git a/lib/VNWeb/TT/TagPage.pm b/lib/VNWeb/TT/TagPage.pm
index e7b63f31..9be519ff 100644
--- a/lib/VNWeb/TT/TagPage.pm
+++ b/lib/VNWeb/TT/TagPage.pm
@@ -65,14 +65,16 @@ sub infobox_ {
 }
 
 
+my $TABLEOPTS = VNWeb::VN::List::TABLEOPTS(1);
+
+
 sub vns_ {
     my($t) = @_;
 
     my $opt = tuwf->validate(get =>
         p => { upage => 1 },
         f => { advsearch_err => 'v' },
-        s => { onerror => 'tagscore', enum => [qw/tagscore title rel pop rating/] },
-        o => { onerror => 'd', enum => ['a','d'] },
+        s => { tableopts => $TABLEOPTS },
         m => { onerror => [auth->pref('spoilers')||0], type => 'array', scalar => 1, minlength => 1, values => { enum => [0..2] } },
         fil => { required => 0 },
     )->data;
@@ -99,44 +101,36 @@ sub vns_ {
     my($count, $list);
     db_maytimeout {
         $count = tuwf->dbVali('SELECT count(*) FROM vn v JOIN tags_vn_inherit tvi ON tvi.vid = v.id WHERE', $where);
-        $list = $count ? tuwf->dbPagei({results => 50, page => $opt->{p}}, '
-            SELECT tvi.rating AS tagscore, v.id, v.title, v.original, v.c_released, v.c_popularity, v.c_votecount, v.c_rating
+        $list = $count ? tuwf->dbPagei({results => $opt->{s}->results(), page => $opt->{p}}, '
+            SELECT tvi.rating AS tagscore, v.id, v.title, v.original, v.c_released, v.c_popularity, v.c_votecount, v.c_rating, v.c_average
                  , v.c_platforms::text[] AS platforms, v.c_languages::text[] AS lang
               FROM vn v
               JOIN tags_vn_inherit tvi ON tvi.vid = v.id
              WHERE', $where, '
-             ORDER BY', sprintf {
-                 tagscore => 'tvi.rating %s, v.title',
-                 title    => 'v.title %s',
-                 rel      => 'v.c_released %s, v.title',
-                 pop      => 'v.c_popularity %s NULLS LAST, v.title',
-                 rating   => 'v.c_rating %s NULLS LAST, v.title'
-             }->{$opt->{s}}, $opt->{o} eq 'a' ? 'ASC' : 'DESC'
+             ORDER BY', $opt->{s}->sql_order(),
         ) : [];
     } || (($count, $list) = (undef, []));
 
-    VNWeb::VN::List::enrich_userlist $list;
+    VNWeb::VN::List::enrich_listing $opt, $list;
     $time = time - $time;
 
-    div_ class => 'mainbox', sub {
-        p_ class => 'mainopts', sub {
-            a_ href => "/g/links?t=$t->{id}", 'Recently tagged';
-        };
-        h1_ 'Visual novels';
-        form_ action => "/$t->{id}", method => 'get', sub {
+    form_ action => "/$t->{id}", method => 'get', sub {
+        div_ class => 'mainbox', sub {
+            p_ class => 'mainopts', sub {
+                a_ href => "/g/links?t=$t->{id}", 'Recently tagged';
+            };
+            h1_ 'Visual novels';
             p_ class => 'browseopts', sub {
                 button_ type => 'submit', name => 'm', value => 0, $opt->{m} == 0 ? (class => 'optselected') : (), 'Hide spoilers';
                 button_ type => 'submit', name => 'm', value => 1, $opt->{m} == 1 ? (class => 'optselected') : (), 'Show minor spoilers';
                 button_ type => 'submit', name => 'm', value => 2, $opt->{m} == 2 ? (class => 'optselected') : (), 'Spoil me!';
             };
-            input_ type => 'hidden', name => 'o', value => $opt->{o};
-            input_ type => 'hidden', name => 's', value => $opt->{s};
             input_ type => 'hidden', name => 'm', value => $opt->{m};
             $opt->{f}->elm_;
             advsearch_msg_ $count, $time;
         };
+        VNWeb::VN::List::listing_ $opt, $list, $count, 1 if $count;
     };
-    VNWeb::VN::List::listing_ $opt, $list, $count, 1 if $count;
 }
 
 
diff --git a/lib/VNWeb/TableOpts.pm b/lib/VNWeb/TableOpts.pm
index 6d6384fb..432bf548 100644
--- a/lib/VNWeb/TableOpts.pm
+++ b/lib/VNWeb/TableOpts.pm
@@ -43,11 +43,11 @@ package VNWeb::TableOpts;
 #
 #   my $opts = tuwf->validate(get => s => { tableopts => $config })->data;
 #
-#   my $sql = sql('.... ORDER BY', $opts->sql_order);  (TODO)
+#   my $sql = sql('.... ORDER BY', $opts->sql_order);
 #
 #   $opts->view;     # Current view, 'rows', 'cards' or 'grid'
 #   $opts->results;  # How many results to display
-#   $opts->vis('popularity'); # is the column visible? (TODO)
+#   $opts->vis('popularity'); # is the column visible?
 #
 #
 #
@@ -89,9 +89,8 @@ my %results = map +($results[$_], $_), 0..$#results;
 # Turn config options into something more efficient to work with
 sub tableopts {
     my %o = (
-        sort_ids  => [], # identifier => column name
-        vis_ids   => [], # identifier => column name
-        col_order => [], # column names in the order listed in the config
+        sort_ids  => [], # identifier => column config hash
+        col_order => [], # column config hashes in the order listed in the config
         columns   => {}, # column name => config hash
         views     => [], # supported views, as numbers
         default   => 0,  # default settings, integer form
@@ -107,9 +106,9 @@ sub tableopts {
             next;
         }
         $o{columns}{$k} = $v;
-        push $o{col_order}->@*, $k;
-        $o{sort_ids}[$v->{sort_id}] = $k if defined $v->{sort_id};
-        $o{vis_ids}[$v->{vis_id}] = $k if defined $v->{vis_id};
+        $v->{id} = $k;
+        push $o{col_order}->@*, $v;
+        $o{sort_ids}[$v->{sort_id}] = $v if defined $v->{sort_id};
         $o{default} |= ($v->{sort_id} << 6) | ({qw|asc 0 desc 32|}->{$v->{sort_default}}//croak("unknown sort_default: $v->{sort_default}")) if $v->{sort_default};
         $o{default} |= 1 << ($v->{vis_id} + 12) if $v->{vis_default};
     }
@@ -125,10 +124,18 @@ TUWF::set('custom_validations')->{tableopts} = sub {
         my $d = $t->{pref} && auth ? tuwf->dbVali('SELECT', $t->{pref}, 'FROM users WHERE id =', \auth->uid) : undef;
         bless([$d // $t->{default},$t], __PACKAGE__)
     }, func => sub {
-        # TODO: compatibility with the old ?s=<colname> sort parameter
-        my $v = _dec($_[0]) // return 0;
+        my $obj = bless [undef, $t], __PACKAGE__;
+        my $col = [grep $_->{compat} && $_->{compat} eq $_[0], values $t->{columns}->%*]->[0];
+        if($col && defined $col->{sort_id}) {
+            $obj->[0] = $t->{default};
+            $obj->set_sort_col_id($col->{sort_id});
+            my $ord = tuwf->reqGet('o');
+            $obj->set_order($ord && $ord eq 'd' ? 1 : 0);
+        } else {
+            $obj->[0] = _dec($_[0]) // return 0;
+        }
+        $_[0] = $obj;
         # We could do strict validation on the individual fields, but the methods below can handle incorrect data.
-        $_[0] = bless [$v, $t], __PACKAGE__;
         1;
     } }
 };
@@ -145,17 +152,55 @@ sub grid  { shift->view eq 'grid'  }
 
 sub results { $results[($_[0][0] >> 2) & 7] || $results[0] }
 
+sub order { $_[0][0] & 32 }
+sub set_order { if($_[1]) { $_[0][0] |= 32 } else { $_[0][0] &= ~32 } }
+
+sub sort_col_id { ($_[0][0] >> 6) & 63 }
+sub set_sort_col_id { $_[0][0] = ($_[0][0] & (~1 - 0b111111000000)) | ($_[1] << 6) }
+
+# Given the key of a column, returns whether it is currently sorted on ('' / 'a' / 'd')
+sub sorted {
+    my($self, $key) = @_;
+    $self->[1]{columns}{$key}{sort_id} != $self->sort_col_id ? '' : $self->order ? 'd' : 'a';
+}
+
+# Given the key of a column and the desired order ('a'/'d'), returns a new object with that sorting applied.
+sub sort_param {
+    my($self, $key, $o) = @_;
+    my $n = bless [@$self], __PACKAGE__;
+    $n->set_order($o eq 'a' ? 0 : 1);
+    $n->set_sort_col_id($self->[1]{columns}{$key}{sort_id});
+    $n
+}
+
+# Returns an SQL expression suitable for use in an ORDER BY clause.
+sub sql_order {
+    my($self) = @_;
+    my($v,$o) = $self->@*;
+    my $col = $o->{sort_ids}[ $self->sort_col_id ] || $o->{sort_ids}[ sort_col_id([$o->{default}]) ];
+    die "No column to sort on" if !$col;
+    my $order = $self->order ? 'DESC' : 'ASC';
+    my $opposite_order = $self->order ? 'ASC' : 'DESC';
+    my $sql = $col->{sort_sql};
+    $sql =~ /[?!]o/ ? ($sql =~ s/\?o/$order/rg =~ s/!o/$opposite_order/rg) : "$sql $order";
+}
+
+
+# Returns whether the given column key is visible.
+sub vis { $_[0][0] & (1 << (12+$_[0][1]{columns}{$_[1]}{vis_id})) }
+
 
 my $FORM_OUT = form_compile any => {
     save    => { required => 0 },
     views   => { type => 'array', values => { uint => 1 } },
     default => { uint => 1 },
     value   => { uint => 1 },
-    # TODO: Sorting & column visibility
+    sorts   => { aoh => { id => { uint => 1 }, name => {} } },
+    vis     => { aoh => { id => { uint => 1 }, name => {} } },
 };
 
 elm_api TableOptsSave => $FORM_OUT, {
-    save => { enum => ['tableopts_c'] },
+    save => { enum => ['tableopts_c', 'tableopts_v', 'tableopts_vt'] },
     value => { required => 0, uint => 1 }
 }, sub {
     my($f) = @_;
@@ -172,6 +217,8 @@ sub elm_ {
         views   => $o->{views},
         default => $o->{default},
         value   => $v,
+        sorts   => [ map +{ id => $_->{sort_id}, name => $_->{name} }, grep defined $_->{sort_id}, values $o->{col_order}->@* ],
+        vis     => [ map +{ id => $_->{vis_id}, name => $_->{name} }, grep defined $_->{vis_id}, values $o->{col_order}->@* ],
     }, sub {
         TUWF::XML::input_ type => 'hidden', name => 's', value => $self->query_encode if defined $self->query_encode
     };
diff --git a/lib/VNWeb/ULists/List.pm b/lib/VNWeb/ULists/List.pm
index ae443955..fc0bbf9e 100644
--- a/lib/VNWeb/ULists/List.pm
+++ b/lib/VNWeb/ULists/List.pm
@@ -117,7 +117,7 @@ sub vn_ {
         } if in vote => $opt->{c};
 
         td_ class => 'tc_rating', sub {
-            txt_ sprintf '%.2f', ($v->{c_rating}||0)/10;
+            txt_ sprintf '%.2f', ($v->{c_rating}||0)/100;
             b_ class => 'grayedout', sprintf ' (%d)', $v->{c_votecount};
         } if in rating => $opt->{c};
 
diff --git a/lib/VNWeb/VN/List.pm b/lib/VNWeb/VN/List.pm
index 422e8b1b..aab698b7 100644
--- a/lib/VNWeb/VN/List.pm
+++ b/lib/VNWeb/VN/List.pm
@@ -5,6 +5,64 @@ use VNWeb::AdvSearch;
 use VNWeb::Filters;
 use VNWeb::TT::Lib 'tagscore_';
 
+# Returns the tableopts config for this VN list (0) or the VN listing on tags (1).
+sub TABLEOPTS {
+    my($tags) = @_;
+    tableopts _pref => $tags ? 'tableopts_vt' : 'tableopts_v',
+        $tags ? (tagscore => {
+            name => 'Tag score',
+            compat => 'tagscore',
+            sort_id => 0,
+            sort_sql => 'tvi.rating ?o, v.title',
+            sort_default => 'desc'
+        }) : (),
+        title => {
+            name => 'Title',
+            compat => 'title',
+            sort_id => 1,
+            sort_sql => 'v.title',
+            sort_default => $tags ? undef : 'asc',
+        },
+        released => {
+            name => 'Release date',
+            compat => 'rel',
+            sort_id => 2,
+            sort_sql => 'v.c_released ?o, v.title',
+        },
+        developer => {
+            name => 'Developer',
+            vis_id => 2,
+        },
+        popularity => {
+            name => 'Popularity score',
+            compat => 'pop',
+            sort_id => 3,
+            sort_sql => 'v.c_popularity ?o NULLS LAST, v.title',
+            vis_id => 0,
+            vis_default => 1,
+        },
+        rating => {
+            name => 'Bayesian rating',
+            compat => 'rating',
+            sort_id => 4,
+            sort_sql => 'v.c_rating ?o NULLS LAST, v.title',
+            vis_id => 1,
+            vis_default => 1,
+        },
+        average => {
+            name => 'Vote average',
+            sort_id => 5,
+            sort_sql => 'v.c_average ?o NULLS LAST, v.title',
+            vis_id => 3,
+        },
+        votes => {
+            name => 'Number of votes',
+            sort_id => 6,
+            sort_sql => 'v.c_votecount ?o, v.title',
+        }
+}
+
+my $TABLEOPTS = TABLEOPTS 0;
 
 # Also used by VNWeb::TT::TagPage
 sub listing_ {
@@ -12,46 +70,63 @@ sub listing_ {
 
     my sub url { '?'.query_encode %$opt, @_ }
 
-    paginate_ \&url, $opt->{p}, [$count, 50], 't';
+    paginate_ \&url, $opt->{p}, [$count, $opt->{s}->results], 't', sub { $opt->{s}->elm_ };
     div_ class => 'mainbox browse vnbrowse', sub {
         table_ class => 'stripe', sub {
             thead_ sub { tr_ sub {
-                td_ class => 'tc_s',sub { txt_ 'Score'; sortable_ 'tagscore', $opt, \&url } if $tagscore;
-                td_ class => $tagscore ? 'tc_t' : 'tc1', sub { txt_ 'Title'; sortable_ 'title', $opt, \&url };
-                td_ class => 'tc7', '';
-                td_ class => 'tc2', '';
-                td_ class => 'tc3', '';
-                td_ class => 'tc4', sub { txt_ 'Released';   sortable_ 'rel',    $opt, \&url };
-                td_ class => 'tc5', sub { txt_ 'Popularity'; sortable_ 'pop',    $opt, \&url };
-                td_ class => 'tc6', sub { txt_ 'Rating';     sortable_ 'rating', $opt, \&url };
+                td_ class => 'tc_score', sub { txt_ 'Score'; sortable_ 'tagscore', $opt, \&url } if $tagscore;
+                td_ class => 'tc_title', sub { txt_ 'Title'; sortable_ 'title', $opt, \&url };
+                td_ class => 'tc_dev',   'Developer' if $opt->{s}->vis('developer');
+                td_ class => 'tc_ulist', '';
+                td_ class => 'tc_plat',  '';
+                td_ class => 'tc_lang',  '';
+                td_ class => 'tc_rel',   sub { txt_ 'Released';   sortable_ 'released',   $opt, \&url };
+                td_ class => 'tc_pop',   sub { txt_ 'Popularity'; sortable_ 'popularity', $opt, \&url } if $opt->{s}->vis('popularity');
+                td_ class => 'tc_rating',sub { txt_ 'Rating';     sortable_ 'rating',     $opt, \&url } if $opt->{s}->vis('rating');
+                td_ class => 'tc_average',sub{ txt_ 'Average';    sortable_ 'average',    $opt, \&url } if $opt->{s}->vis('average');
             } };
             tr_ sub {
-                td_ class => 'tc_s',sub { tagscore_ $_->{tagscore} } if $tagscore;
-                td_ class => $tagscore ? 'tc_t' : 'tc1', sub { a_ href => "/$_->{id}", title => $_->{original}||$_->{title}, $_->{title} };
-                td_ class => 'tc7', sub {
+                td_ class => 'tc_score', sub { tagscore_ $_->{tagscore} } if $tagscore;
+                td_ class => 'tc_title', sub { a_ href => "/$_->{id}", title => $_->{original}||$_->{title}, $_->{title} };
+                td_ class => 'tc_dev',   sub {
+                    join_ ' & ', sub {
+                        a_ href => "/$_->{id}", title => $_->{original}||$_->{name}, $_->{name};
+                    }, sort { $a->{name} cmp $b->{name} || $a->{id} <=> $b->{id} } $_->{developers}->@*;
+                } if $opt->{s}->vis('developer');
+                td_ class => 'tc_ulist', sub {
                     b_ class => $_->{userlist_obtained} == $_->{userlist_all} ? 'done' : 'todo', sprintf '%d/%d', $_->{userlist_obtained}, $_->{userlist_all} if $_->{userlist_all};
                     abbr_ title => join(', ', $_->{vnlist_labels}->@*), scalar $_->{vnlist_labels}->@* if $_->{vnlist_labels} && $_->{vnlist_labels}->@*;
                     abbr_ title => 'No labels', ' ' if $_->{vnlist_labels} && !$_->{vnlist_labels}->@*;
                 };
-                td_ class => 'tc2', sub { join_ '', sub { platform_ $_ if $_ ne 'unk' }, sort $_->{platforms}->@* };
-                td_ class => 'tc3', sub { join_ '', sub { abbr_ class => "icons lang $_", title => $LANGUAGE{$_}, '' }, reverse sort $_->{lang}->@* };
-                td_ class => 'tc4', sub { rdate_ $_->{c_released} };
-                td_ class => 'tc5', sprintf '%.2f', ($_->{c_popularity}||0)*100;
-                td_ class => 'tc6', sub {
-                    txt_ sprintf '%.2f', ($_->{c_rating}||0)/10;
+                td_ class => 'tc_plat',  sub { join_ '', sub { platform_ $_ if $_ ne 'unk' }, sort $_->{platforms}->@* };
+                td_ class => 'tc_lang',  sub { join_ '', sub { abbr_ class => "icons lang $_", title => $LANGUAGE{$_}, '' }, reverse sort $_->{lang}->@* };
+                td_ class => 'tc_rel',   sub { rdate_ $_->{c_released} };
+                td_ class => 'tc_pop',   sprintf '%.2f', ($_->{c_popularity}||0)/100 if $opt->{s}->vis('popularity');
+                td_ class => 'tc_rating',sub {
+                    txt_ sprintf '%.2f', ($_->{c_rating}||0)/100;
                     b_ class => 'grayedout', sprintf ' (%d)', $_->{c_votecount};
-                };
+                } if $opt->{s}->vis('rating');
+                td_ class => 'tc_average',sub {
+                    txt_ sprintf '%.2f', ($_->{c_average}||0)/100;
+                    b_ class => 'grayedout', sprintf ' (%d)', $_->{c_votecount} if !$opt->{s}->vis('rating');
+                } if $opt->{s}->vis('average');
             } for @$list;
         }
     };
-    paginate_ \&url, $opt->{p}, [$count, 50], 'b';
+    paginate_ \&url, $opt->{p}, [$count, $opt->{s}->results], 'b';
 }
 
 
-# Enrich the userlist fields needed for listing_()
+# Enrich some extra fields fields needed for listing_()
 # Also used by VNWeb::TT::TagPage
-sub enrich_userlist {
-    return if !auth;
+sub enrich_listing {
+    my $opt = shift;
+
+    enrich developers => id => vid => sub {
+        'SELECT v.id AS vid, p.id, p.name, p.original
+           FROM vn v, unnest(v.c_developers) vp(id), producers p
+          WHERE p.id = vp.id AND v.id IN', $_[0], 'ORDER BY p.name, p.id'
+    }, @_ if $opt->{s}->vis('developer');
 
     enrich_merge id => sub { sql '
         SELECT irv.vid AS id
@@ -61,7 +136,7 @@ sub enrich_userlist {
          JOIN releases_vn irv ON irv.id = irl.rid
         WHERE irl.uid =', \auth->uid, 'AND irv.vid IN', $_, '
         GROUP BY irv.vid
-    ' }, @_;
+    ' }, @_ if auth;
 
     enrich_flatten vnlist_labels => id => vid => sub { sql '
         SELECT uvl.vid, ul.label
@@ -69,7 +144,7 @@ sub enrich_userlist {
           JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl
          WHERE uvl.uid =', \auth->uid, 'AND uvl.vid IN', $_[0], '
          ORDER BY CASE WHEN ul.id < 10 THEN ul.id ELSE 10 END, ul.label'
-    }, @_;
+    }, @_ if auth;
 }
 
 
@@ -79,8 +154,7 @@ TUWF::get qr{/v(?:/(?<char>all|[a-z0]))?}, sub {
         sq=> { onerror => undef },
         p => { upage => 1 },
         f => { advsearch_err => 'v' },
-        s => { onerror => 'title', enum => [qw/title rel pop rating/] },
-        o => { onerror => 'a', enum => ['a','d'] },
+        s => { tableopts => $TABLEOPTS },
         ch=> { onerror => [], type => 'array', scalar => 1, values => { onerror => undef, enum => ['0', 'a'..'z'] } },
         fil  => { required => 0 },
         rfil => { required => 0 },
@@ -121,41 +195,35 @@ TUWF::get qr{/v(?:/(?<char>all|[a-z0]))?}, sub {
     my($count, $list);
     db_maytimeout {
         $count = tuwf->dbVali('SELECT count(*) FROM vn v WHERE', $where);
-        $list = $count ? tuwf->dbPagei({results => 50, page => $opt->{p}}, '
-            SELECT v.id, v.title, v.original, v.c_released, v.c_popularity, v.c_votecount, v.c_rating, v.c_platforms::text[] AS platforms, v.c_languages::text[] AS lang
+        $list = $count ? tuwf->dbPagei({results => $opt->{s}->results(), page => $opt->{p}}, '
+            SELECT v.id, v.title, v.original, v.c_released, v.c_popularity, v.c_votecount, v.c_rating, v.c_average
+                 , v.c_platforms::text[] AS platforms, v.c_languages::text[] AS lang
               FROM vn v
              WHERE', $where, '
-             ORDER BY', sprintf {
-                 title  => 'v.title %s',
-                 rel    => 'v.c_released %s, v.title',
-                 pop    => 'v.c_popularity %s NULLS LAST, v.title',
-                 rating => 'v.c_rating %s NULLS LAST, v.title'
-             }->{$opt->{s}}, $opt->{o} eq 'a' ? 'ASC' : 'DESC'
+             ORDER BY', $opt->{s}->sql_order(),
         ) : [];
     } || (($count, $list) = (undef, []));
 
     return tuwf->resRedirect("/$list->[0]{id}") if $count && $count == 1 && $opt->{q} && !defined $opt->{ch};
 
-    enrich_userlist $list;
+    enrich_listing($opt, $list);
     $time = time - $time;
 
     framework_ title => 'Browse visual novels', sub {
-        div_ class => 'mainbox', sub {
-            h1_ 'Browse visual novels';
-            form_ action => '/v', method => 'get', sub {
+        form_ action => '/v', method => 'get', sub {
+            div_ class => 'mainbox', sub {
+                h1_ 'Browse visual novels';
                 searchbox_ v => $opt->{q}//'';
                 p_ class => 'browseopts', sub {
                     button_ type => 'submit', name => 'ch', value => ($_//''), ($_//'') eq ($opt->{ch}//'') ? (class => 'optselected') : (), !defined $_ ? 'ALL' : $_ ? uc $_ : '#'
                         for (undef, 'a'..'z', 0);
                 };
-                input_ type => 'hidden', name => 'o', value => $opt->{o};
-                input_ type => 'hidden', name => 's', value => $opt->{s};
                 input_ type => 'hidden', name => 'ch', value => $opt->{ch}//'';
                 $opt->{f}->elm_;
                 advsearch_msg_ $count, $time;
             };
+            listing_ $opt, $list, $count if $count;
         };
-        listing_ $opt, $list, $count if $count;
     };
 };
 
diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm
index 68239338..32e4a567 100644
--- a/lib/VNWeb/VN/Page.pm
+++ b/lib/VNWeb/VN/Page.pm
@@ -671,8 +671,8 @@ sub stats_ {
         clearfloat_;
         div_ sub {
             h3_ 'Ranking';
-            p_ sprintf 'Popularity: ranked #%d with a score of %.2f', $rank->{c_pop_rank}, $rank->{c_popularity}*100 if defined $rank->{c_popularity};
-            p_ sprintf 'Bayesian rating: ranked #%d with a rating of %.2f', $rank->{c_rat_rank}, $rank->{c_rating}/10;
+            p_ sprintf 'Popularity: ranked #%d with a score of %.2f', $rank->{c_pop_rank}, $rank->{c_popularity}/100 if defined $rank->{c_popularity};
+            p_ sprintf 'Bayesian rating: ranked #%d with a rating of %.2f', $rank->{c_rat_rank}, $rank->{c_rating}/100;
         } if $v->{c_votecount};
     }
 
diff --git a/sql/func.sql b/sql/func.sql
index 3facbcdf..8ee5ca2b 100644
--- a/sql/func.sql
+++ b/sql/func.sql
@@ -84,12 +84,23 @@ CREATE OR REPLACE FUNCTION update_vncache(vndbid) RETURNS void AS $$
         AND r.hidden = FALSE
       GROUP BY rp.platform
       ORDER BY rp.platform
+    ),
+    c_developers = ARRAY(
+      SELECT rp.pid
+        FROM releases_producers rp
+        JOIN releases r ON rp.id = r.id
+        JOIN releases_vn rv ON rv.id = r.id
+       WHERE rv.vid = $1
+         AND r.official AND rp.developer
+         AND r.hidden = FALSE
+      GROUP BY rp.pid
+      ORDER BY rp.pid
     )
   WHERE id = $1;
 $$ LANGUAGE sql;
 
 
--- Update vn.c_popularity, c_rating, c_votecount, c_pop_rank and c_rat_rank
+-- Update vn.c_popularity, c_rating, c_votecount, c_pop_rank, c_rat_rank and c_average
 CREATE OR REPLACE FUNCTION update_vnvotestats() RETURNS void AS $$
   WITH votes(vid, uid, vote) AS ( -- List of all non-ignored VN votes
     SELECT vid, uid, vote FROM ulist_vns WHERE vote IS NOT NULL AND uid NOT IN(SELECT id FROM users WHERE ign_votes)
@@ -97,8 +108,8 @@ CREATE OR REPLACE FUNCTION update_vnvotestats() RETURNS void AS $$
     SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes
   ), avgavg(avgavg) AS ( -- Average vote average
     SELECT AVG(a)::real FROM (SELECT AVG(vote) FROM votes GROUP BY vid) x(a)
-  ), ratings(vid, count, rating) AS ( -- Ratings and vote counts
-    SELECT vid, COALESCE(COUNT(uid), 0),
+  ), ratings(vid, count, average, rating) AS ( -- Ratings and vote counts
+    SELECT vid, COALESCE(COUNT(uid), 0), (AVG(vote)*10)::smallint,
            COALESCE(
               ((SELECT avgcount FROM avgcount) * (SELECT avgavg FROM avgavg) + SUM(vote)::real) /
               ((SELECT avgcount FROM avgcount) + COUNT(uid)::real),
@@ -111,18 +122,18 @@ CREATE OR REPLACE FUNCTION update_vnvotestats() RETURNS void AS $$
         SELECT uid, vid, ((rank() OVER (PARTITION BY uid ORDER BY vote))::real - 1) ^ 0.36788 FROM votes
       ) x(uid, vid, rank)
      GROUP BY vid
-  ), stats(vid, rating, count, popularity, pop_rank, rat_rank) AS ( -- Combined stats
-    SELECT v.id, COALESCE(round(r.rating::numeric, 1), 0)::real, COALESCE(r.count, 0)
-         , round((p.win/(SELECT MAX(win) FROM popularities))::numeric, 4)::real
+  ), stats(vid, rating, count, average, popularity, pop_rank, rat_rank) AS ( -- Combined stats
+    SELECT v.id, (r.rating*10)::smallint, COALESCE(r.count, 0), r.average
+         , (p.win/(SELECT MAX(win) FROM popularities)*10000)::smallint
          , CASE WHEN p.win    IS NULL THEN NULL ELSE rank() OVER(ORDER BY hidden, p.win    DESC NULLS LAST) END
          , CASE WHEN r.rating IS NULL THEN NULL ELSE rank() OVER(ORDER BY hidden, r.rating DESC NULLS LAST) END
       FROM vn v
       LEFT JOIN ratings r ON r.vid = v.id
       LEFT JOIN popularities p ON p.vid = v.id AND p.win > 0
   )
-  UPDATE vn SET c_rating = rating, c_votecount = count, c_popularity = popularity, c_pop_rank = pop_rank, c_rat_rank = rat_rank
+  UPDATE vn SET c_rating = rating, c_votecount = count, c_popularity = popularity, c_pop_rank = pop_rank, c_rat_rank = rat_rank, c_average = average
     FROM stats
-   WHERE id = vid AND (c_rating, c_votecount, c_popularity, c_pop_rank, c_rat_rank) IS DISTINCT FROM (rating, count, popularity, pop_rank, rat_rank);
+   WHERE id = vid AND (c_rating, c_votecount, c_popularity, c_pop_rank, c_rat_rank, c_average) IS DISTINCT FROM (rating, count, popularity, pop_rank, rat_rank, average);
 $$ LANGUAGE SQL;
 
 
diff --git a/sql/schema.sql b/sql/schema.sql
index 6220a29f..eef5203c 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -984,7 +984,9 @@ CREATE TABLE users (
   ulist_votes         jsonb,
   ulist_vnlist        jsonb,
   ulist_wish          jsonb,
-  vnlang              jsonb -- '$lang(-mtl)?' => true/false, which languages to expand/collapse on VN pages
+  vnlang              jsonb, -- '$lang(-mtl)?' => true/false, which languages to expand/collapse on VN pages
+  tableopts_v         integer,
+  tableopts_vt        integer -- VN listing on tag pages
 );
 
 -- Additional fields for the 'users' table, but with some protected columns.
@@ -1012,9 +1014,9 @@ CREATE TABLE vn ( -- dbentry_type=v
   image         vndbid CONSTRAINT vn_image_check CHECK(vndbid_type(image) = 'cv'), -- [pub]
   l_wikidata    integer, -- [pub]
   c_votecount   integer NOT NULL DEFAULT 0, -- [pub]
-  c_popularity  real, -- [pub]
+  c_popularity  smallint, -- [pub], ratio between 0 and 10000
   c_pop_rank    integer,
-  c_rating      real, -- [pub]
+  c_rating      smallint, -- [pub], decimal vote*100, i.e. 100 - 1000
   c_rat_rank    integer,
   c_released    integer NOT NULL DEFAULT 0,
   length        smallint NOT NULL DEFAULT 0, -- [pub]
@@ -1030,7 +1032,9 @@ CREATE TABLE vn ( -- dbentry_type=v
   "desc"        text NOT NULL DEFAULT '', -- [pub]
   c_search      text,
   c_languages   language[] NOT NULL DEFAULT '{}',
-  c_platforms   platform[] NOT NULL DEFAULT '{}'
+  c_platforms   platform[] NOT NULL DEFAULT '{}',
+  c_developers  vndbid[] NOT NULL DEFAULT '{}',
+  c_average     smallint -- [pub], decimal vote*100, i.e. 100 - 1000
 );
 
 -- vn_hist
diff --git a/util/updates/2021-06-04-vn-developers-and-average-cache.sql b/util/updates/2021-06-04-vn-developers-and-average-cache.sql
new file mode 100644
index 00000000..4fc6a510
--- /dev/null
+++ b/util/updates/2021-06-04-vn-developers-and-average-cache.sql
@@ -0,0 +1,11 @@
+ALTER TABLE users ADD COLUMN tableopts_v         integer;
+ALTER TABLE users ADD COLUMN tableopts_vt        integer;
+
+ALTER TABLE vn ADD COLUMN c_developers  vndbid[] NOT NULL DEFAULT '{}';
+ALTER TABLE vn ADD COLUMN c_average     smallint;
+ALTER TABLE vn ALTER COLUMN c_popularity TYPE smallint USING c_popularity*10000;
+ALTER TABLE vn ALTER COLUMN c_rating     TYPE smallint USING c_rating*10;
+\i sql/func.sql
+\timing
+SELECT count(*) FROM (SELECT update_vncache(id) FROM vn) x;
+SELECT update_vnvotestats();