Numerous report improvments

2 files changed, 24 insertions, 10 deletions

diff --git a/sources/wiki/post.sql b/sources/wiki/post.sql
index 28e5673..eaf9ec0 100644
--- a/sources/wiki/post.sql
+++ b/sources/wiki/post.sql
@@ -13,14 +13,26 @@ UPDATE wikipages SET status='e' WHERE type='page' AND has_templ='true' AND parse
 
 CREATE INDEX wikipages_key_value_idx ON wikipages(key, value);
 
-INSERT INTO wikipages_keys (key, langs) SELECT key, group_concat(lang || ' ' || status) FROM wikipages WHERE value IS NULL GROUP BY key;
-INSERT INTO wikipages_tags (key, value, langs) SELECT key, value, group_concat(lang || ' ' || status) FROM wikipages WHERE value IS NOT NULL GROUP BY key, value;
+INSERT INTO wikipages_keys (key,        langs, lang_count) SELECT key,        group_concat(lang || ' ' || status), count(*) FROM wikipages WHERE value IS     NULL GROUP BY key;
+INSERT INTO wikipages_tags (key, value, langs, lang_count) SELECT key, value, group_concat(lang || ' ' || status), count(*) FROM wikipages WHERE value IS NOT NULL GROUP BY key, value;
 
 INSERT INTO wiki_languages (language, count_pages) SELECT lang, count(*) FROM wikipages GROUP BY lang;
 
-INSERT INTO stats (key, value) SELECT 'wikipages_keys',      count(*) FROM wikipages_keys;
-INSERT INTO stats (key, value) SELECT 'wikipages_tags',      count(*) FROM wikipages_tags;
-INSERT INTO stats (key, value) SELECT 'wikipages_languages', count(*) FROM wiki_languages;
+INSERT INTO stats (key, value) SELECT 'wiki_keys_described',                  count(*) FROM wikipages_keys;
+INSERT INTO stats (key, value) SELECT 'wiki_pages_for_keys',                  count(*) FROM wikipages WHERE value IS     NULL;
+INSERT INTO stats (key, value) SELECT 'wiki_pages_for_keys_redirect',         count(*) FROM wikipages WHERE value IS     NULL AND status='r';
+INSERT INTO stats (key, value) SELECT 'wiki_pages_for_keys_without_template', count(*) FROM wikipages WHERE value IS     NULL AND status='p';
+INSERT INTO stats (key, value) SELECT 'wiki_pages_for_keys_with_template',    count(*) FROM wikipages WHERE value IS     NULL AND status='t';
+INSERT INTO stats (key, value) SELECT 'wiki_pages_for_keys_with_error',       count(*) FROM wikipages WHERE value IS     NULL AND status='e';
+
+INSERT INTO stats (key, value) SELECT 'wiki_tags_described',                  count(*) FROM wikipages_tags;
+INSERT INTO stats (key, value) SELECT 'wiki_pages_for_tags',                  count(*) FROM wikipages WHERE value IS NOT NULL;
+INSERT INTO stats (key, value) SELECT 'wiki_pages_for_tags_redirect',         count(*) FROM wikipages WHERE value IS NOT NULL AND status='r';
+INSERT INTO stats (key, value) SELECT 'wiki_pages_for_tags_without_template', count(*) FROM wikipages WHERE value IS NOT NULL AND status='p';
+INSERT INTO stats (key, value) SELECT 'wiki_pages_for_tags_with_template',    count(*) FROM wikipages WHERE value IS NOT NULL AND status='t';
+INSERT INTO stats (key, value) SELECT 'wiki_pages_for_tags_with_error',       count(*) FROM wikipages WHERE value IS NOT NULL AND status='e';
+
+INSERT INTO stats (key, value) SELECT 'wiki_languages', count(*) FROM wiki_languages;
 
 ANALYZE;
 
diff --git a/sources/wiki/pre.sql b/sources/wiki/pre.sql
index 04071eb..1f51754 100644
--- a/sources/wiki/pre.sql
+++ b/sources/wiki/pre.sql
@@ -53,16 +53,18 @@ CREATE TABLE wikipages (
 DROP TABLE IF EXISTS wikipages_keys;
 
 CREATE TABLE wikipages_keys (
-    key   TEXT,
-    langs TEXT
+    key        TEXT,
+    langs      TEXT,
+    lang_count INTEGER
 );
 
 DROP TABLE IF EXISTS wikipages_tags;
 
 CREATE TABLE wikipages_tags (
-    key   TEXT,
-    value TEXT,
-    langs TEXT
+    key        TEXT,
+    value      TEXT,
+    langs      TEXT,
+    lang_count INTEGER
 );
 
 DROP TABLE IF EXISTS wiki_languages;