Numerous report improvments

3 files changed, 41 insertions, 35 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