Multiple changes

* Improved and cleaned up source import scripts
* Improved some reports and report overview
* New osmium version that uses a better hash function
* Some experimental magic to include Javascipt in an easier way (tested in keys.erb)

21 files changed, 154 insertions, 143 deletions

diff --git a/sources/db/osmium_tagstats b/sources/db/osmium_tagstats
index 618ee6d..ab5e21e 100755
--- a/sources/db/osmium_tagstats
+++ b/sources/db/osmium_tagstats
diff --git a/sources/db/post.sql b/sources/db/post.sql
index e182a28..e824c48 100644
--- a/sources/db/post.sql
+++ b/sources/db/post.sql
@@ -61,5 +61,5 @@ UPDATE keys SET prevalent_values=(
 
 ANALYZE;
 
-UPDATE meta SET update_end=datetime('now');
+UPDATE source SET update_end=datetime('now');
 
diff --git a/sources/db/pre.sql b/sources/db/pre.sql
index 998b20e..38ec051 100644
--- a/sources/db/pre.sql
+++ b/sources/db/pre.sql
@@ -6,24 +6,7 @@
 
 .bail ON
 
-DROP TABLE IF EXISTS meta;
-
-CREATE TABLE meta (
-    source_id    TEXT,
-    source_name  TEXT,
-    update_start TEXT,
-    update_end   TEXT,
-    data_until   TEXT
-);
-
-INSERT INTO meta (source_id, source_name, update_start) SELECT 'db', 'Database', datetime('now');
-
-DROP TABLE IF EXISTS stats;
-
-CREATE TABLE stats (
-    key   TEXT,
-    value INT64
-);
+INSERT INTO source (id, name, update_start) SELECT 'db', 'Database', datetime('now');
 
 DROP TABLE IF EXISTS keys;
 
diff --git a/sources/db/update.sh b/sources/db/update.sh
index 4ab2856..56a7652 100755
--- a/sources/db/update.sh
+++ b/sources/db/update.sh
@@ -9,32 +9,37 @@ set -e
 
 DIR=$1
 
+DATECMD='date +%Y-%m-%dT%H:%M:%S'
+
 if [ "x" = "x$DIR" ]; then
     echo "Usage: update.sh DIR"
     exit 1
 fi
 
-echo -n "Start db: "; date
+echo "`$DATECMD` Start db..."
 
 DATABASE=$DIR/taginfo-db.db
 
 rm -f $DATABASE
 rm -f $DIR/count.db
 
-echo "Running pre.sql..."
+echo "`$DATECMD` Running init.sql..."
+sqlite3 $DATABASE <../init.sql
+
+echo "`$DATECMD` Running pre.sql..."
 sqlite3 $DATABASE <pre.sql
 
-echo -n "Running count... "; date
+echo "`$DATECMD` Running count... "
 HERE=`pwd`
 cd $DIR
 bzcat $DIR/planet.osm.bz2 | $HERE/osmium_tagstats -
 cd $HERE
 
-echo -n "Running update_characters... "; date
+echo "`$DATECMD` Running update_characters... "
 ./update_characters.pl $DIR
 
-echo -n "Running post.sql... "; date
+echo "`$DATECMD` Running post.sql... "
 perl -pe "s|__DIR__|$DIR|" post.sql | sqlite3 $DATABASE
 
-echo -n "Done db: "; date
+echo "`$DATECMD` Done db."
 
diff --git a/sources/init.sql b/sources/init.sql
new file mode 100644
index 0000000..e188915
--- /dev/null
+++ b/sources/init.sql
@@ -0,0 +1,29 @@
+--
+--  init.sql
+--
+--  Initialize source database
+--
+
+.bail ON
+
+--
+-- Contains metadata about this source.
+--
+DROP TABLE IF EXISTS source;
+CREATE TABLE source (
+    id           TEXT,
+    name         TEXT,
+    update_start TEXT,
+    update_end   TEXT,
+    data_until   TEXT
+);
+
+--
+-- Contains general statistical data for this source.
+--
+DROP TABLE IF EXISTS stats;
+CREATE TABLE stats (
+    key   TEXT,
+    value INT64
+);
+
diff --git a/sources/josm/post.sql b/sources/josm/post.sql
index 352b026..8dd6972 100644
--- a/sources/josm/post.sql
+++ b/sources/josm/post.sql
@@ -18,5 +18,5 @@ INSERT INTO stats (key, value) SELECT 'josm_tags_in_style_rules', count(distinct
 
 ANALYZE;
 
-UPDATE meta SET update_end=datetime('now');
+UPDATE source SET update_end=datetime('now');
 
diff --git a/sources/josm/pre.sql b/sources/josm/pre.sql
index 42a4213..5c2975d 100644
--- a/sources/josm/pre.sql
+++ b/sources/josm/pre.sql
@@ -6,24 +6,7 @@
 
 .bail ON
 
-DROP TABLE IF EXISTS meta;
-
-CREATE TABLE meta (
-    source_id    TEXT,
-    source_name  TEXT,
-    update_start TEXT,
-    update_end   TEXT,
-    data_until   TEXT
-);
-
-INSERT INTO meta (source_id, source_name, update_start, data_until) SELECT 'josm', 'JOSM', datetime('now'), datetime('now');
-
-DROP TABLE IF EXISTS stats;
-
-CREATE TABLE stats (
-    key   TEXT,
-    value INT64
-);
+INSERT INTO source (id, name, update_start, data_until) SELECT 'josm', 'JOSM', datetime('now'), datetime('now');
 
 --
 --  josm_styles
diff --git a/sources/josm/update.sh b/sources/josm/update.sh
index 6eca722..abfb7e9 100755
--- a/sources/josm/update.sh
+++ b/sources/josm/update.sh
@@ -9,12 +9,14 @@ set -e
 
 DIR=$1
 
+DATECMD='date +%Y-%m-%dT%H:%M:%S'
+
 if [ "x" = "x$DIR" ]; then
     echo "Usage: update.sh DIR"
     exit 1
 fi
 
-echo -n "Start josm: "; date
+echo "`$DATECMD` Start josm..."
 
 DATABASE=$DIR/taginfo-josm.db
 ELEMSTYLES=$DIR/elemstyles.xml
@@ -22,17 +24,20 @@ ELEMSTYLES=$DIR/elemstyles.xml
 rm -f $DATABASE
 rm -f $ELEMSTYLES
 
-echo "Getting styles..."
+echo "`$DATECMD` Getting styles..."
 wget -O $ELEMSTYLES http://josm.openstreetmap.de/svn/trunk/styles/standard/elemstyles.xml
 
-echo "Running pre.sql..."
+echo "`$DATECMD` Running init.sql..."
+sqlite3 $DATABASE <../init.sql
+
+echo "`$DATECMD` Running pre.sql..."
 sqlite3 $DATABASE <pre.sql
 
-echo "Running import..."
+echo "`$DATECMD` Running import..."
 ./import_josm.rb $DIR
 
-echo "Running post.sql..."
+echo "`$DATECMD` Running post.sql..."
 sqlite3 $DATABASE <post.sql
 
-echo -n "Done josm: "; date
+echo "`$DATECMD` Done josm."
 
diff --git a/sources/master/languages.sql b/sources/master/languages.sql
index 968e689..eb2b6d9 100644
--- a/sources/master/languages.sql
+++ b/sources/master/languages.sql
@@ -6,15 +6,17 @@
 
 .bail ON
 
+--
+--  Contains all the languages Taginfo knows about.
+--
 DROP TABLE IF EXISTS languages;
-
 CREATE TABLE languages (
     code           VARCHAR,
-    iso639_1       VARCHAR,
+    iso639_1       VARCHAR, -- official ISO 639-1 code (if available)
     english_name   VARCHAR,
     native_name    VARCHAR,
-    wiki_key_pages INTEGER,
-    wiki_tag_pages INTEGER
+    wiki_key_pages INTEGER, -- count of wiki pages with the title "code:Key:*" (or "Key:*" for code='en')
+    wiki_tag_pages INTEGER  -- count of wiki pages with the title "code:Tag:*" (or "Tag:*" for code='en')
 );
 
 INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('ar',      'ar', 'Arabic', 'العربية');
@@ -48,5 +50,3 @@ INSERT INTO languages (code, iso639_1, english_name, native_name) VALUES ('zh',
 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 fc0ccef..c559426 100644
--- a/sources/master/master.sql
+++ b/sources/master/master.sql
@@ -1,8 +1,10 @@
+-- ============================================================================
 --
 --  Taginfo
 --
 --  master.sql
 --
+-- ============================================================================
 
 .bail ON
 
@@ -12,8 +14,29 @@ ATTACH DATABASE '__DIR__/josm/taginfo-josm.db'             AS josm;
 ATTACH DATABASE '__DIR__/potlatch/taginfo-potlatch.db'     AS potlatch; 
 ATTACH DATABASE '__DIR__/merkaartor/taginfo-merkaartor.db' AS merkaartor; 
 
-DROP TABLE IF EXISTS master_meta;
+-- ============================================================================
+
+--
+--  Collects information about all the sources.
+--
+DROP TABLE IF EXISTS sources;
+CREATE TABLE sources (
+    no           INTEGER,
+    visible      INTEGER,
+    id           TEXT,
+    name         TEXT,
+    update_start TEXT,
+    update_end   TEXT,
+    data_until   TEXT
+);
+
+INSERT INTO sources SELECT 1, 1, * FROM db.source
+              UNION SELECT 2, 1, * FROM wiki.source
+              UNION SELECT 3, 1, * FROM josm.source
+              UNION SELECT 4, 0, * FROM potlatch.source
+              UNION SELECT 5, 0, * FROM merkaartor.source;
 
+DROP TABLE IF EXISTS master_meta;
 CREATE TABLE master_meta (
     source_id    TEXT,
     source_name  TEXT,
@@ -22,14 +45,14 @@ CREATE TABLE master_meta (
     data_until   TEXT
 );
 
-INSERT INTO master_meta SELECT * FROM db.meta
-                  UNION SELECT * FROM wiki.meta
-                  UNION SELECT * FROM josm.meta;
+-- for backwards compatibility
+INSERT INTO master_meta SELECT * FROM db.source
+                  UNION SELECT * FROM wiki.source
+                  UNION SELECT * FROM josm.source;
 -- XXX                 UNION SELECT * FROM potlatch.meta
 -- XXX                 UNION SELECT * FROM merkaartor.meta;
 
 DROP TABLE IF EXISTS master_stats;
-
 CREATE TABLE master_stats (
     key   TEXT,
     value INT64
@@ -41,6 +64,8 @@ INSERT INTO master_stats SELECT * FROM db.stats
                    UNION SELECT * FROM potlatch.stats
                    UNION SELECT * FROM merkaartor.stats;
 
+-- ============================================================================
+
 INSERT INTO db.keys (key) SELECT DISTINCT key FROM wiki.wikipages        WHERE key NOT IN (SELECT key FROM db.keys);
 INSERT INTO db.keys (key) SELECT DISTINCT k   FROM josm.josm_style_rules WHERE k   NOT IN (SELECT key FROM db.keys);
 -- potlatch XXX
@@ -55,9 +80,9 @@ UPDATE db.keys SET in_merkaartor=1 WHERE key IN (SELECT key FROM merkaartor.keys
 -- too slow, so we drop it for now
 -- INSERT INTO db.tags (key, value) SELECT DISTINCT key, value FROM wiki.wikipages WHERE key || '=XX=' || value NOT IN (SELECT key || '=XX=' || value FROM db.tags);
 
+-- ============================================================================
 
 DROP TABLE IF EXISTS popular_keys;
-
 CREATE TABLE popular_keys (
     key           VARCHAR,
     count         INTEGER,
@@ -87,8 +112,9 @@ UPDATE popular_keys SET in_wiki=1    WHERE key IN (SELECT distinct key FROM wiki
 UPDATE popular_keys SET in_wiki_en=1 WHERE key IN (SELECT distinct key FROM wiki.wikipages WHERE lang='en');
 UPDATE popular_keys SET in_josm=1    WHERE key IN (SELECT distinct k   FROM josm.josm_style_rules);
 
-DROP TABLE IF EXISTS popular_metadata;
+-- ============================================================================
 
+DROP TABLE IF EXISTS popular_metadata;
 CREATE TABLE popular_metadata (
     keys        INTEGER,
     count_min   INTEGER,
@@ -111,9 +137,13 @@ 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 b8d63b2..f1598d9 100755
--- a/sources/master/update.sh
+++ b/sources/master/update.sh
@@ -9,12 +9,14 @@ set -e
 
 DIR=$1
 
+DATECMD='date +%Y-%m-%dT%H:%M:%S'
+
 if [ "x" = "x$DIR" ]; then
     echo "Usage: update.sh DIR"
     exit 1
 fi
 
-echo -n "Start master: "; date
+echo "`$DATECMD` Start master..."
 
 DATABASE=$DIR/taginfo-master.db
 
@@ -23,5 +25,5 @@ rm -f $DATABASE
 sqlite3 $DATABASE <languages.sql
 perl -pe "s|__DIR__|$DIR|" master.sql | sqlite3 $DATABASE
 
-echo -n "Done master: "; date
+echo "`$DATECMD` Done master."
 
diff --git a/sources/merkaartor/post.sql b/sources/merkaartor/post.sql
index 9e7fea9..06bcef0 100644
--- a/sources/merkaartor/post.sql
+++ b/sources/merkaartor/post.sql
@@ -6,3 +6,7 @@
 
 .bail ON
 
+ANALYZE;
+
+UPDATE source SET update_end=datetime('now');
+
diff --git a/sources/merkaartor/pre.sql b/sources/merkaartor/pre.sql
index 60ba309..76d8012 100644
--- a/sources/merkaartor/pre.sql
+++ b/sources/merkaartor/pre.sql
@@ -6,25 +6,7 @@
 
 .bail ON
 
-DROP TABLE IF EXISTS meta;
-
-CREATE TABLE meta (
-    source_id    TEXT,
-    source_name  TEXT,
-    update_start TEXT,
-    update_end   TEXT,
-    data_until   TEXT
-);
-
-INSERT INTO meta (source_id, source_name, update_start, data_until) SELECT 'potlatch', 'Potlatch', datetime('now'), datetime('now');
-
-DROP TABLE IF EXISTS stats;
-
-CREATE TABLE stats (
-    key   TEXT,
-    value INT64
-);
-
+INSERT INTO source (id, name, update_start, data_until) SELECT 'merkaartor', 'Merkaartor', datetime('now'), datetime('now');
 
 --
 --  templates
diff --git a/sources/merkaartor/update.sh b/sources/merkaartor/update.sh
index ae97d45..a112b27 100755
--- a/sources/merkaartor/update.sh
+++ b/sources/merkaartor/update.sh
@@ -9,18 +9,20 @@ set -e
 
 DIR=$1
 
+DATECMD='date +%Y-%m-%dT%H:%M:%S'
+
 if [ "x" = "x$DIR" ]; then
     echo "Usage: update.sh DIR"
     exit 1
 fi
 
-echo -n "Start merkaartor: "; date
+echo "`$DATECMD` Start merkaartor..."
 
 DATABASE=$DIR/taginfo-merkaartor.db
 
 rm -f $DATABASE
 
-echo "Getting resources..."
+echo "`$DATECMD` Updating resources..."
 if [ -d $DIR/git-source ]; then
     cd $DIR/git-source
     git pull
@@ -29,14 +31,17 @@ else
     git clone http://git.gitorious.org/merkaartor/main.git $DIR/git-source
 fi
 
-echo "Running pre.sql..."
+echo "`$DATECMD` Running init.sql..."
+sqlite3 $DATABASE <../init.sql
+
+echo "`$DATECMD` Running pre.sql..."
 sqlite3 $DATABASE <pre.sql
 
-echo "Running import..."
+echo "`$DATECMD` Running import..."
 ./import_merkaartor.rb $DIR
 
-echo "Running post.sql..."
+echo "`$DATECMD` Running post.sql..."
 sqlite3 $DATABASE <post.sql
 
-echo -n "Done merkaartor: "; date
+echo "`$DATECMD` Done merkaartor."
 
diff --git a/sources/potlatch/post.sql b/sources/potlatch/post.sql
index 3446d08..1254b89 100644
--- a/sources/potlatch/post.sql
+++ b/sources/potlatch/post.sql
@@ -11,3 +11,5 @@ UPDATE features SET category_name = (SELECT name FROM categories WHERE id=catego
 
 ANALYZE;
 
+UPDATE source SET update_end=datetime('now');
+
diff --git a/sources/potlatch/pre.sql b/sources/potlatch/pre.sql
index 9f79445..35d8c9d 100644
--- a/sources/potlatch/pre.sql
+++ b/sources/potlatch/pre.sql
@@ -6,24 +6,7 @@
 
 .bail ON
 
-DROP TABLE IF EXISTS meta;
-
-CREATE TABLE meta (
-    source_id    TEXT,
-    source_name  TEXT,
-    update_start TEXT,
-    update_end   TEXT,
-    data_until   TEXT
-);
-
-INSERT INTO meta (source_id, source_name, update_start, data_until) SELECT 'potlatch', 'Potlatch', datetime('now'), datetime('now');
-
-DROP TABLE IF EXISTS stats;
-
-CREATE TABLE stats (
-    key   TEXT,
-    value INT64
-);
+INSERT INTO source (id, name, update_start, data_until) SELECT 'potlatch', 'Potlatch', datetime('now'), datetime('now');
 
 --
 --  categories
diff --git a/sources/potlatch/update.sh b/sources/potlatch/update.sh
index 8ba9956..748ff4a 100755
--- a/sources/potlatch/update.sh
+++ b/sources/potlatch/update.sh
@@ -9,32 +9,37 @@ set -e
 
 DIR=$1
 
+DATECMD='date +%Y-%m-%dT%H:%M:%S'
+
 if [ "x" = "x$DIR" ]; then
     echo "Usage: update.sh DIR"
     exit 1
 fi
 
-echo -n "Start potlatch: "; date
+echo "`$DATECMD` Start potlatch..."
 
 DATABASE=$DIR/taginfo-potlatch.db
 
 rm -f $DATABASE
 
-echo "Getting resources..."
+echo "`$DATECMD` Updating resources..."
 if [ -d $DIR/resources ]; then
     svn update $DIR/resources
 else
     svn checkout http://svn.openstreetmap.org/applications/editors/potlatch2/resources $DIR/resources
 fi
 
-echo "Running pre.sql..."
+echo "`$DATECMD` Running init.sql..."
+sqlite3 $DATABASE <../init.sql
+
+echo "`$DATECMD` Running pre.sql..."
 sqlite3 $DATABASE <pre.sql
 
-echo "Running import..."
+echo "`$DATECMD` Running import..."
 ./import_potlatch.rb $DIR
 
-echo "Running post.sql..."
+echo "`$DATECMD` Running post.sql..."
 sqlite3 $DATABASE <post.sql
 
-echo -n "Done potlatch: "; date
+echo "`$DATECMD` Done potlatch."
 
diff --git a/sources/update_all.sh b/sources/update_all.sh
index f7e4459..39e58ce 100755
--- a/sources/update_all.sh
+++ b/sources/update_all.sh
@@ -9,6 +9,8 @@ SOURCES="josm potlatch merkaartor wiki db"
 
 set -e
 
+DATECMD='date +%Y-%m-%dT%H:%M:%S'
+
 DIR=$1
 
 if [ "x" = "x$DIR" ]; then
@@ -16,9 +18,11 @@ if [ "x" = "x$DIR" ]; then
     exit 1
 fi
 
-exec >$DIR/update_all.log 2>&1
+LOGFILE=`date +%Y%m%dT%H%M`
+mkdir -p $DIR/log
+exec >$DIR/log/$LOGFILE.log 2>&1
 
-echo -n "Start: "; date
+echo "`$DATECMD` Start update_all..."
 
 mkdir -p $DIR/download
 
@@ -40,7 +44,7 @@ cd ..
 
 for source in $SOURCES; do
     echo "====================================="
-    echo "Running bzip2..."
+    echo "Running bzip2 on $source..."
     bzip2 -9 -c $DIR/$source/taginfo-$source.db >$DIR/download/taginfo-$source.db.bz2
     echo "Done."
 done
@@ -49,5 +53,6 @@ echo "Running bzip2..."
 bzip2 -9 -c $DIR/taginfo-master.db >$DIR/download/taginfo-master.db.bz2
 echo "Done."
 
-echo -n "Done: "; date
+echo "====================================="
+echo "`$DATECMD` Done update_all."
 
diff --git a/sources/wiki/post.sql b/sources/wiki/post.sql
index eaf9ec0..773a04d 100644
--- a/sources/wiki/post.sql
+++ b/sources/wiki/post.sql
@@ -36,5 +36,5 @@ INSERT INTO stats (key, value) SELECT 'wiki_languages', count(*) FROM wiki_langu
 
 ANALYZE;
 
-UPDATE meta SET update_end=datetime('now');
+UPDATE source SET update_end=datetime('now');
 
diff --git a/sources/wiki/pre.sql b/sources/wiki/pre.sql
index 0e8200e..56e9538 100644
--- a/sources/wiki/pre.sql
+++ b/sources/wiki/pre.sql
@@ -6,24 +6,7 @@
 
 .bail ON
 
-DROP TABLE IF EXISTS meta;
-
-CREATE TABLE meta (
-    source_id    TEXT,
-    source_name  TEXT,
-    update_start TEXT,
-    update_end   TEXT,
-    data_until   TEXT
-);
-
-INSERT INTO meta (source_id, source_name, update_start, data_until) SELECT 'wiki', 'Wiki', datetime('now'), datetime('now');
-
-DROP TABLE IF EXISTS stats;
-
-CREATE TABLE stats (
-    key   TEXT,
-    value INT64
-);
+INSERT INTO source (id, name, update_start, data_until) SELECT 'wiki', 'Wiki', datetime('now'), datetime('now');
 
 DROP TABLE IF EXISTS wikipages;
 
diff --git a/sources/wiki/update.sh b/sources/wiki/update.sh
index b6f63a6..7d7dc6b 100755
--- a/sources/wiki/update.sh
+++ b/sources/wiki/update.sh
@@ -9,12 +9,14 @@ set -e
 
 DIR=$1
 
+DATECMD='date +%Y-%m-%dT%H:%M:%S'
+
 if [ "x" = "x$DIR" ]; then
     echo "Usage: update.sh DIR"
     exit 1
 fi
 
-echo -n "Start wiki: "; date
+echo "`$DATECMD` Start wiki..."
 
 DATABASE=$DIR/taginfo-wiki.db
 LOGFILE=$DIR/get_wiki_data.log
@@ -24,17 +26,20 @@ rm -f $DIR/tagpages.list
 rm -f $LOGFILE
 rm -f $DATABASE
 
-echo "Running pre.sql..."
+echo "`$DATECMD` Running init.sql..."
+sqlite3 $DATABASE <../init.sql
+
+echo "`$DATECMD` Running pre.sql..."
 sqlite3 $DATABASE <pre.sql
 
-echo "Getting page list..."
+echo "`$DATECMD` Getting page list..."
 ./get_page_list.rb $DIR
 
-echo "Getting wiki data..."
+echo "`$DATECMD` Getting wiki data..."
 ./get_wiki_data.rb $DIR >$LOGFILE
 
-echo "Running post.sql..."
+echo "`$DATECMD` Running post.sql..."
 sqlite3 $DATABASE <post.sql
 
-echo -n "Done wiki: "; date
+echo "`$DATECMD` Done wiki."