Numerous report improvments

5 files changed, 65 insertions, 45 deletions

diff --git a/sources/master/languages.sql b/sources/master/languages.sql
index a04db33..968e689 100644
--- a/sources/master/languages.sql
+++ b/sources/master/languages.sql
@@ -9,42 +9,44 @@
 DROP TABLE IF EXISTS languages;
 
 CREATE TABLE languages (
-    code         VARCHAR,
-    english_name VARCHAR,
-    native_name  VARCHAR
+    code           VARCHAR,
+    iso639_1       VARCHAR,
+    english_name   VARCHAR,
+    native_name    VARCHAR,
+    wiki_key_pages INTEGER,
+    wiki_tag_pages INTEGER
 );
 
-INSERT INTO languages VALUES ('ar', 'Arabic', 'العربية');
-INSERT INTO languages VALUES ('bg', 'Bulgarian', 'български език');
-INSERT INTO languages VALUES ('cz', 'Czech', 'česky');
-INSERT INTO languages VALUES ('da', 'Danish', 'dansk');
-INSERT INTO languages VALUES ('de', 'German', 'Deutsch');
-INSERT INTO languages VALUES ('en', 'English', 'English');
-INSERT INTO languages VALUES ('es', 'Spanish', 'español');
-INSERT INTO languages VALUES ('et', 'Estonian', 'eesti');
-INSERT INTO languages VALUES ('fi', 'Finish', 'suomi');
-INSERT INTO languages VALUES ('fr', 'French', 'français');
-INSERT INTO languages VALUES ('he', 'Hebrew', 'עברית');
-INSERT INTO languages VALUES ('hr', 'Croatian', 'hrvatski');
-INSERT INTO languages VALUES ('hu', 'Hungarian', 'Magyar');
-INSERT INTO languages VALUES ('it', 'Italian', 'Italiano');
-INSERT INTO languages VALUES ('ja', 'Japanese', '日本語');
-INSERT INTO languages VALUES ('nl', 'Dutch', 'Nederlands');
-INSERT INTO languages VALUES ('no', 'Norwegian', 'Norsk');
-INSERT INTO languages VALUES ('pl', 'Polish', 'polski');
-INSERT INTO languages VALUES ('pt', 'Portuguese', 'Português');
-INSERT INTO languages VALUES ('pt-br', 'Brazilian Portuguese', 'Português do Brasil');
-INSERT INTO languages VALUES ('ro', 'Romanian', 'română');
-INSERT INTO languages VALUES ('ro-md', 'Moldovan', 'română (Moldova)');
-INSERT INTO languages VALUES ('ru', 'Russian', 'русский язык');
--- INSERT INTO languages VALUES ('sh', '', ''); -- not in ISO 639-1
-INSERT INTO languages VALUES ('sq', 'Albanian', 'Shqip');
-INSERT INTO languages VALUES ('sv', 'Swedish', 'svenska');
-INSERT INTO languages VALUES ('tr', 'Turkish', 'Türkçe');
-INSERT INTO languages VALUES ('uk', 'Ukrainian', 'українська');
-INSERT INTO languages VALUES ('zh', 'Chinese', '中文');
-INSERT INTO languages VALUES ('zh-hans', 'Chinese', '中文'); -- hans?
--- INSERT INTO languages VALUES ('', '', '');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('ar',      'ar', 'Arabic', 'العربية');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('bg',      'bg', 'Bulgarian', 'български език');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('cz',      'cz', 'Czech', 'česky');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('da',      'da', 'Danish', 'dansk');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('de',      'de', 'German', 'Deutsch');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('en',      'en', 'English', 'English');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('es',      'es', 'Spanish', 'español');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('et',      'et', 'Estonian', 'eesti');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('fi',      'fi', 'Finish', 'suomi');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('fr',      'fr', 'French', 'français');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('he',      'he', 'Hebrew', 'עברית');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('hr',      'hr', 'Croatian', 'hrvatski');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('hu',      'hu', 'Hungarian', 'Magyar');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('it',      'it', 'Italian', 'Italiano');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('ja',      'ja', 'Japanese', '日本語');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('nl',      'nl', 'Dutch', 'Nederlands');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('no',      'no', 'Norwegian', 'Norsk');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('pl',      'pl', 'Polish', 'polski');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('pt',      'pt', 'Portuguese', 'Português');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('pt-br',   NULL, 'Brazilian Portuguese', 'Português do Brasil');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('ro',      'ro', 'Romanian', 'română');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('ro-md',   NULL, 'Moldovan', 'română (Moldova)');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('ru',      'ru', 'Russian', 'русский язык');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('sq',      'sq', 'Albanian', 'Shqip');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('sv',      'sv', 'Swedish', 'svenska');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('tr',      'tr', 'Turkish', 'Türkçe');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('uk',      'uk', 'Ukrainian', 'українська');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('zh',      'zh', 'Chinese', '中文');
+INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('zh-hans', NULL, 'Chinese', '中文');
+-- INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('', '', '', '');
 
 ANALYZE;
 
diff --git a/sources/master/master.sql b/sources/master/master.sql
index 331a464..fc0ccef 100644
--- a/sources/master/master.sql
+++ b/sources/master/master.sql
@@ -111,5 +111,9 @@ UPDATE popular_keys SET scale_name  = 0 WHERE key LIKE '%:%';
 
 UPDATE popular_keys SET scale1 = 10 * scale_count + 8 * scale_users + 2 * scale_wiki + 1 * scale_josm + 2 * scale_name;
 
+INSERT INTO languages (code) SELECT distinct(lang) FROM wiki.wikipages WHERE lang NOT IN (SELECT code FROM languages);
+UPDATE languages SET wiki_key_pages=(SELECT count(distinct key) FROM wiki.wikipages WHERE lang=code AND value IS NULL);
+UPDATE languages SET wiki_tag_pages=(SELECT count(distinct key) FROM wiki.wikipages WHERE lang=code AND value IS NOT NULL);
+
 ANALYZE;
 
diff --git a/sources/master/update.sh b/sources/master/update.sh
index 9b7040e..b8d63b2 100755
--- a/sources/master/update.sh
+++ b/sources/master/update.sh
@@ -20,8 +20,8 @@ DATABASE=$DIR/taginfo-master.db
 
 rm -f $DATABASE
 
-perl -pe "s|__DIR__|$DIR|" master.sql | sqlite3 $DATABASE
 sqlite3 $DATABASE <languages.sql
+perl -pe "s|__DIR__|$DIR|" master.sql | sqlite3 $DATABASE
 
 echo -n "Done master: "; date
 
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;