diff options
Diffstat (limited to 'sources')
-rwxr-xr-x | sources/db/osmium_tagstats | bin | 135169 -> 128430 bytes | |||
-rw-r--r-- | sources/db/post.sql | 2 | ||||
-rw-r--r-- | sources/db/pre.sql | 19 | ||||
-rwxr-xr-x | sources/db/update.sh | 17 | ||||
-rw-r--r-- | sources/init.sql | 29 | ||||
-rw-r--r-- | sources/josm/post.sql | 2 | ||||
-rw-r--r-- | sources/josm/pre.sql | 19 | ||||
-rwxr-xr-x | sources/josm/update.sh | 17 | ||||
-rw-r--r-- | sources/master/languages.sql | 12 | ||||
-rw-r--r-- | sources/master/master.sql | 44 | ||||
-rwxr-xr-x | sources/master/update.sh | 6 | ||||
-rw-r--r-- | sources/merkaartor/post.sql | 4 | ||||
-rw-r--r-- | sources/merkaartor/pre.sql | 20 | ||||
-rwxr-xr-x | sources/merkaartor/update.sh | 17 | ||||
-rw-r--r-- | sources/potlatch/post.sql | 2 | ||||
-rw-r--r-- | sources/potlatch/pre.sql | 19 | ||||
-rwxr-xr-x | sources/potlatch/update.sh | 17 | ||||
-rwxr-xr-x | sources/update_all.sh | 13 | ||||
-rw-r--r-- | sources/wiki/post.sql | 2 | ||||
-rw-r--r-- | sources/wiki/pre.sql | 19 | ||||
-rwxr-xr-x | sources/wiki/update.sh | 17 |
21 files changed, 154 insertions, 143 deletions
diff --git a/sources/db/osmium_tagstats b/sources/db/osmium_tagstats Binary files differindex 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." |