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.

1 files changed, 11 insertions, 0 deletions

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();