From 960a39f5937a50b4d999a3fc5e7465412eb377a0 Mon Sep 17 00:00:00 2001 From: Jochen Topf Date: Thu, 21 Aug 2014 14:38:23 +0200 Subject: Add new 'projects' source. Any kind of project using OSM tags can create a json-formatted taginfo project file and after its URL is added to the taginfo config, taginfo will integrate this data into its database. --- sources/projects/import.rb | 69 +++++++++++++++++++++++++++++++ sources/projects/parse.rb | 85 +++++++++++++++++++++++++++++++++++++++ sources/projects/post.sql | 15 +++++++ sources/projects/pre.sql | 46 +++++++++++++++++++++ sources/projects/project_list.txt | 4 ++ sources/projects/update.sh | 48 ++++++++++++++++++++++ 6 files changed, 267 insertions(+) create mode 100755 sources/projects/import.rb create mode 100755 sources/projects/parse.rb create mode 100644 sources/projects/post.sql create mode 100644 sources/projects/pre.sql create mode 100644 sources/projects/project_list.txt create mode 100755 sources/projects/update.sh (limited to 'sources') diff --git a/sources/projects/import.rb b/sources/projects/import.rb new file mode 100755 index 0000000..820bf02 --- /dev/null +++ b/sources/projects/import.rb @@ -0,0 +1,69 @@ +#!/usr/bin/ruby +#------------------------------------------------------------------------------ +# +# Taginfo source: Projects +# +# import.rb +# +#------------------------------------------------------------------------------ +# +# Copyright (C) 2014 Jochen Topf +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 2 of the License, or +# (at your option) any later version. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# with this program; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. +# +#------------------------------------------------------------------------------ + +require 'net/http' +require 'sqlite3' +require 'time' + +#------------------------------------------------------------------------------ + +dir = ARGV[0] || '.' +db = SQLite3::Database.new(dir + '/taginfo-projects.db') + +project_list = ARGV[1] || 'project_list.txt' + +#------------------------------------------------------------------------------ + +projects = [] +open(project_list) do |file| + file.each do |line| + projects << line.chomp.split(' ') + end +end + +projects.each do |id, url| + puts " #{id} #{url}" + uri = URI(url) + Net::HTTP.start(uri.host, uri.port) do |http| + request = Net::HTTP::Get.new(uri) + response = http.request(request) + last_modified = Time.parse(response['Last-Modified']).utc.iso8601 + db.execute("INSERT INTO projects (id, json_url, last_modified, fetch_date, fetch_status, fetch_json, fetch_result, data_updated) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", + id, + url, + last_modified, + Time.now.utc.iso8601, + response.code, + response.body, + (response.code == '200' ? 'OK' : 'FETCH ERROR'), + last_modified + ); + end +end + + +#-- THE END ------------------------------------------------------------------- diff --git a/sources/projects/parse.rb b/sources/projects/parse.rb new file mode 100755 index 0000000..c509fae --- /dev/null +++ b/sources/projects/parse.rb @@ -0,0 +1,85 @@ +#!/usr/bin/ruby +#------------------------------------------------------------------------------ +# +# Taginfo source: Projects +# +# parse.rb +# +#------------------------------------------------------------------------------ +# +# Copyright (C) 2014 Jochen Topf +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 2 of the License, or +# (at your option) any later version. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# with this program; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. +# +#------------------------------------------------------------------------------ + +require 'pp' +require 'json' +require 'sqlite3' + +#------------------------------------------------------------------------------ + +dir = ARGV[0] || '.' +db = SQLite3::Database.new(dir + '/taginfo-projects.db') + +#------------------------------------------------------------------------------ + +projects = db.execute("SELECT id, fetch_json FROM projects WHERE fetch_result='OK' ORDER BY id") + +projects.each do |id, json| + puts " #{id}..." + begin + data = JSON.parse(json, { :symbolize_names => true, :create_additions => false }) + db.transaction do |db| + db.execute("UPDATE projects SET data_format=?, data_url=? WHERE id=?", data[:data_format], data[:data_url], id) + + if data[:data_updated] + db.execute("UPDATE projects SET data_updated=? WHERE id=?", data[:data_updated], id) + end + + if data[:project] + p = data[:project] + db.execute("UPDATE projects SET name=?, description=?, project_url=?, doc_url=?, icon_url=?, contact_name=?, contact_email=? WHERE id=?", + p[:name], + p[:description], + p[:project_url], + p[:doc_url], + p[:icon_url], + p[:contact_name], + p[:contact_email], + id + ) + end + + if data[:tags] + data[:tags].each do |d| + db.execute("INSERT INTO project_tags (project_id, key, value, description, doc_url, icon_url) VALUES (?, ?, ?, ?, ?, ?)", + id, + d[:key], + d[:value], + d[:description], + d[:doc_url], + d[:icon_url] + ); + end + end + end + rescue JSON::ParserError + db.execute("UPDATE projects SET fetch_result='PARSE_ERROR' WHERE id=?", id) + end +end + + +#-- THE END ------------------------------------------------------------------- diff --git a/sources/projects/post.sql b/sources/projects/post.sql new file mode 100644 index 0000000..b8d210f --- /dev/null +++ b/sources/projects/post.sql @@ -0,0 +1,15 @@ +-- +-- Taginfo source: Projects +-- +-- post.sql +-- + +.bail ON + +CREATE INDEX project_keys_idx ON project_tags (key) WHERE value IS NULL; +CREATE INDEX project_tags_idx ON project_tags (key, value) WHERE value IS NOT NULL; + +ANALYZE; + +UPDATE source SET update_end=datetime('now'); + diff --git a/sources/projects/pre.sql b/sources/projects/pre.sql new file mode 100644 index 0000000..da4d66f --- /dev/null +++ b/sources/projects/pre.sql @@ -0,0 +1,46 @@ +-- +-- Taginfo source: Projects +-- +-- pre.sql +-- + +.bail ON + +INSERT INTO source (id, name, update_start, data_until) SELECT 'projects', 'Projects', datetime('now'), datetime('now'); + +DROP TABLE IF EXISTS projects; + +CREATE TABLE projects ( + id TEXT NOT NULL PRIMARY KEY, + json_url TEXT NOT NULL, + last_modified DATE, + fetch_date DATE, + fetch_status TEXT, -- HTTP status code + fetch_json TEXT, -- HTTP body + fetch_result TEXT, -- 'OK', 'FETCH ERROR', 'PARSE ERROR' + data_format INTEGER, + data_updated DATE, + data_url TEXT, + icon BLOB, + name TEXT, + description TEXT, + project_url TEXT, + doc_url TEXT, + icon_url TEXT, + contact_name TEXT, + contact_email TEXT, + keywords TEXT +); + +DROP TABLE IF EXISTS project_tags; + +CREATE TABLE project_tags ( + project_id TEXT NOT NULL, + key TEXT NOT NULL, + value TEXT, + description TEXT, + doc_url TEXT, + icon_url TEXT, + keywords TEXT +); + diff --git a/sources/projects/project_list.txt b/sources/projects/project_list.txt new file mode 100644 index 0000000..479724b --- /dev/null +++ b/sources/projects/project_list.txt @@ -0,0 +1,4 @@ +id_editor https://raw.githubusercontent.com/joto/taginfo/master/projects/id_editor.json +osmcoastline https://raw.githubusercontent.com/joto/osmcoastline/master/taginfo-project.json +osrm https://raw.githubusercontent.com/joto/taginfo/master/projects/osrm.json +waymarkedtrails http://mapstatic.waymarkedtrails.org/taginfo.json diff --git a/sources/projects/update.sh b/sources/projects/update.sh new file mode 100755 index 0000000..6c05ac9 --- /dev/null +++ b/sources/projects/update.sh @@ -0,0 +1,48 @@ +#!/bin/sh +# +# Taginfo source: Projects +# +# update.sh DIR +# + +set -e + +DIR=$1 +PROJECT_LIST=project_list.txt + +DATECMD='date +%Y-%m-%dT%H:%M:%S' + +if [ "x" = "x$DIR" ]; then + echo "Usage: update.sh DIR" + exit 1 +fi + +echo "`$DATECMD` Start projects..." + +EXEC_RUBY="$TAGINFO_RUBY" +if [ "x$EXEC_RUBY" = "x" ]; then + EXEC_RUBY=ruby +fi +echo "Running with ruby set as '${EXEC_RUBY}'" + +DATABASE=$DIR/taginfo-projects.db + +rm -f $DATABASE + +echo "`$DATECMD` Running init.sql..." +sqlite3 $DATABASE <../init.sql + +echo "`$DATECMD` Running pre.sql..." +sqlite3 $DATABASE