Begin of Potlatch support

12 files changed, 244 insertions, 22 deletions

diff --git a/sources/db/pre.sql b/sources/db/pre.sql
index 1bb00ea..9404aaa 100644
--- a/sources/db/pre.sql
+++ b/sources/db/pre.sql
@@ -29,19 +29,22 @@ DROP TABLE IF EXISTS keys;
 
 CREATE TABLE keys (
   key              VARCHAR,
-  count_all        INTEGER,
-  count_nodes      INTEGER,
-  count_ways       INTEGER,
-  count_relations  INTEGER,
-  values_all       INTEGER,
-  values_nodes     INTEGER,
-  values_ways      INTEGER,
-  values_relations INTEGER,
-  users_all        INTEGER,
-  users_nodes      INTEGER,
-  users_ways       INTEGER,
-  users_relations  INTEGER,
-  grids            INTEGER,
+  count_all        INTEGER DEFAULT 0,
+  count_nodes      INTEGER DEFAULT 0,
+  count_ways       INTEGER DEFAULT 0,
+  count_relations  INTEGER DEFAULT 0,
+  values_all       INTEGER DEFAULT 0,
+  values_nodes     INTEGER DEFAULT 0,
+  values_ways      INTEGER DEFAULT 0,
+  values_relations INTEGER DEFAULT 0,
+  users_all        INTEGER DEFAULT 0,
+  users_nodes      INTEGER DEFAULT 0,
+  users_ways       INTEGER DEFAULT 0,
+  users_relations  INTEGER DEFAULT 0,
+  grids            INTEGER DEFAULT 0,
+  in_wiki          INTEGER DEFAULT 0,
+  in_josm          INTEGER DEFAULT 0,
+  in_potlatch      INTEGER DEFAULT 0,
   prevalent_values TEXT
 );
 
@@ -57,10 +60,13 @@ DROP TABLE IF EXISTS tags;
 CREATE TABLE tags (
   key              VARCHAR,
   value            VARCHAR,
-  count_all        INTEGER,
-  count_nodes      INTEGER,
-  count_ways       INTEGER,
-  count_relations  INTEGER 
+  count_all        INTEGER DEFAULT 0,
+  count_nodes      INTEGER DEFAULT 0,
+  count_ways       INTEGER DEFAULT 0,
+  count_relations  INTEGER DEFAULT 0,
+  in_wiki          INTEGER DEFAULT 0,
+  in_josm          INTEGER DEFAULT 0,
+  in_potlatch      INTEGER DEFAULT 0
 );
 
 DROP TABLE IF EXISTS keypairs;
diff --git a/sources/master/master.sql b/sources/master/master.sql
index c446ae7..d6d28af 100644
--- a/sources/master/master.sql
+++ b/sources/master/master.sql
@@ -35,6 +35,17 @@ INSERT INTO master_stats SELECT * FROM db.stats
                    UNION SELECT * FROM josm.stats
                    UNION SELECT * FROM wiki.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
+UPDATE db.keys SET in_wiki=1 WHERE key IN (SELECT key FROM wiki.wikipages);
+UPDATE db.keys SET in_josm=1 WHERE key IN (SELECT k FROM josm.josm_style_rules);
+-- potlatch XXX
+
+-- 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 (
@@ -44,6 +55,7 @@ CREATE TABLE popular_keys (
     wikipages   INTEGER DEFAULT 0,
     in_wiki     INTEGER DEFAULT 0,
     in_josm     INTEGER DEFAULT 0,
+    in_potlatch INTEGER DEFAULT 0,
     scale_count REAL,
     scale_users REAL,
     scale_wiki  REAL,
diff --git a/sources/potlatch/import_potlatch.rb b/sources/potlatch/import_potlatch.rb
new file mode 100755
index 0000000..b939cc6
--- /dev/null
+++ b/sources/potlatch/import_potlatch.rb
@@ -0,0 +1,74 @@
+#!/usr/bin/ruby
+#------------------------------------------------------------------------------
+#
+#  Taginfo source: Potlatch
+#
+#  import_potlatch.rb
+#
+#------------------------------------------------------------------------------
+#
+#  Copyright (C) 2010  Jochen Topf <jochen@remote.org>
+#
+#  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 'sqlite3'
+require 'rexml/document'
+
+dir = ARGV[0] || '.'
+
+db = SQLite3::Database.new(dir + '/taginfo-potlatch.db')
+
+db.execute('BEGIN TRANSACTION');
+
+file = File.new(dir + '/resources/map_features.xml')
+doc = REXML::Document.new(file)
+
+doc.elements.each('/mapFeatures/category') do |category_element|
+    db.execute('INSERT INTO categories (id, name) VALUES (?, ?)', category_element.attributes['id'], category_element.attributes['name'])
+end
+
+doc.elements.each('/mapFeatures/feature') do |feature_element|
+    feature_name = feature_element.attributes['name']
+
+    on = { :point => 0, :line => 0, :area => 0, :relation => 0 }
+
+    fields = Hash.new
+    feature_element.elements.each do |element|
+        case element.name
+            when 'tag'
+                value = element.attributes['v'] == '*' ? nil : element.attributes['v']
+                db.execute('INSERT INTO tags (key, value, feature_name) VALUES (?, ?, ?)', element.attributes['k'], value, feature_name)
+            when /^(point|line|area|relation)$/
+                on[$1.to_sym] = 1
+            when /^(category|help)$/
+                fields[element.name] = element.text.strip
+        end
+    end
+
+    if on[:point] + on[:line] + on[:area] + on[:relation] == 0
+        on = { :point => 1, :line => 1, :area => 1, :relation => 1 }
+    end
+
+    db.execute('INSERT INTO features (name, category_id, help, on_point, on_line, on_area, on_relation) VALUES (?, ?, ?, ?, ?, ?, ?)',
+        feature_name, fields['category'], fields['help'], on[:point], on[:line], on[:area], on[:relation])
+end
+
+db.execute('COMMIT');
+
+
+#-- THE END -------------------------------------------------------------------
diff --git a/sources/potlatch/post.sql b/sources/potlatch/post.sql
new file mode 100644
index 0000000..3446d08
--- /dev/null
+++ b/sources/potlatch/post.sql
@@ -0,0 +1,13 @@
+--
+--  Taginfo source: Potlatch
+--
+--  post.sql
+--
+
+.bail ON
+
+-- pull over category name from categories table so we don't need joins later
+UPDATE features SET category_name = (SELECT name FROM categories WHERE id=category_id);
+
+ANALYZE;
+
diff --git a/sources/potlatch/pre.sql b/sources/potlatch/pre.sql
new file mode 100644
index 0000000..7f3a40b
--- /dev/null
+++ b/sources/potlatch/pre.sql
@@ -0,0 +1,68 @@
+--
+--  Taginfo source: Potlatch
+--
+--  pre.sql
+--
+
+.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
+);
+
+--
+--  categories
+--
+
+DROP TABLE IF EXISTS categories;
+
+CREATE TABLE categories (
+    id   TEXT,
+    name TEXT
+);
+
+
+--
+--  features
+--
+
+DROP TABLE IF EXISTS features;
+
+CREATE TABLE features (
+    name          TEXT,
+    category_id   TEXT REFERENCES categories (id),
+    category_name TEXT,
+    help          TEXT,
+    on_point      INTEGER,
+    on_line       INTEGER,
+    on_area       INTEGER,
+    on_relation   INTEGER
+);
+
+--
+--  tags
+--
+
+DROP TABLE IF EXISTS tags;
+
+CREATE TABLE tags (
+    key          TEXT,
+    value        TEXT,
+    feature_name REFERENCES features (name)
+);
+
diff --git a/sources/potlatch/update.sh b/sources/potlatch/update.sh
new file mode 100755
index 0000000..004f1db
--- /dev/null
+++ b/sources/potlatch/update.sh
@@ -0,0 +1,40 @@
+#!/bin/sh
+#
+#  Taginfo source: Potlatch
+#
+#  update.sh DIR
+#
+
+set -e
+
+DIR=$1
+
+if [ "x" = "x$DIR" ]; then
+    echo "Usage: update.sh DIR"
+    exit 1
+fi
+
+echo -n "Start potlatch: "; date
+
+DATABASE=$DIR/taginfo-potlatch.db
+
+rm -f $DATABASE
+
+echo "Getting 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..."
+sqlite3 $DATABASE <pre.sql
+
+echo "Running import..."
+./import_potlatch.rb $DIR
+
+echo "Running post.sql..."
+sqlite3 $DATABASE <post.sql
+
+echo -n "Done potlatch: "; date
+
diff --git a/web/lib/api/db.rb b/web/lib/api/db.rb
index 26bc86a..cc75952 100644
--- a/web/lib/api/db.rb
+++ b/web/lib/api/db.rb
@@ -8,7 +8,7 @@ class Taginfo < Sinatra::Base
         
         res = @db.select('SELECT * FROM db.keys').
             condition_if("key LIKE '%' || ? || '%'", params[:query]).
-            order_by([:key, :count_all, :count_nodes, :count_ways, :count_relations, :values_all, :users_all], params[:sortname], params[:sortorder]).
+            order_by([:key, :count_all, :count_nodes, :count_ways, :count_relations, :values_all, :users_all, :in_wiki, :in_josm, :in_potlatch], params[:sortname], params[:sortorder]).
             paging(params[:rp], params[:page]).
             execute()
 
@@ -28,6 +28,9 @@ class Taginfo < Sinatra::Base
                 :count_relations_fraction => row['count_relations'].to_f / @stats['relations'],
                 :values_all               => row['values_all'].to_i,
                 :users_all                => row['users_all'].to_i,
+                :in_wiki                  => row['in_wiki']     == '1' ? true : false,
+                :in_josm                  => row['in_josm']     == '1' ? true : false,
+                :in_potlatch              => row['in_potlatch'] == '1' ? true : false,
                 :prevalent_values         => (row['prevalent_values'] || '').split('|').map{ |pv| pv }
             } }
         }.to_json
diff --git a/web/public/img/sources/potlatch.16.png b/web/public/img/sources/potlatch.16.png
new file mode 100644
index 0000000..951d965
--- /dev/null
+++ b/web/public/img/sources/potlatch.16.png
diff --git a/web/public/img/sources/potlatch.24.png b/web/public/img/sources/potlatch.24.png
new file mode 100644
index 0000000..c6c22fd
--- /dev/null
+++ b/web/public/img/sources/potlatch.24.png
diff --git a/web/public/img/sources/potlatch.32.png b/web/public/img/sources/potlatch.32.png
new file mode 100644
index 0000000..186a74b
--- /dev/null
+++ b/web/public/img/sources/potlatch.32.png
diff --git a/web/public/img/sources/potlatch.64.png b/web/public/img/sources/potlatch.64.png
new file mode 100644
index 0000000..a637041
--- /dev/null
+++ b/web/public/img/sources/potlatch.64.png
diff --git a/web/views/keys.erb b/web/views/keys.erb
index 6b21d83..5eaaaf1 100644
--- a/web/views/keys.erb
+++ b/web/views/keys.erb
@@ -10,14 +10,17 @@
             method: 'GET',
             dataType: 'json',
             colModel: [
-                { display: 'Key',               name: 'key',      width: 200, sortable: true, align: 'left' },
+                { display: 'Key',               name: 'key',      width: 180, sortable: true, align: 'left' },
                 { display: '<span title="Number of objects with this key"><img src="/img/types/all.16.png" alt=""/> Total</span>',           name: 'count_all',        width: 250, sortable: true, align: 'center' },
                 { display: '<span title="Number of nodes with this key"><img src="/img/types/node.16.png" alt=""/> Nodes</span>',            name: 'count_nodes',      width: 250, sortable: true, align: 'center' },
                 { display: '<span title="Number of ways with this key"><img src="/img/types/way.16.png" alt=""/> Ways</span>',               name: 'count_ways',       width: 250, sortable: true, align: 'center' },
                 { display: '<span title="Number of relations with this key"><img src="/img/types/relation.16.png" alt=""/> Relation</span>', name: 'count_relations',  width: 250, sortable: true, align: 'center' },
-                { display: 'Users',             name: 'users_all', width: 44, sortable: true, align: 'right' },
-                { display: '<span title="Number of different values for this key">Values</span>',  name: 'values_all', width: 70, sortable: true, align: 'right' },
-                { display: 'Prevalent Values',  name: 'prevalent_values', width: 500, sortable: true, align: 'left' }
+                { display: 'Users', name: 'users_all', width: 44, sortable: true, align: 'right' },
+                { display: '<img src="/img/sources/wiki.16.png" alt="Wiki" title="Wiki"/>', name: 'in_wiki', width: 20, sortable: true, align: 'center' },
+                { display: '<img src="/img/sources/josm.16.png" alt="JOSM" title="JOSM"/>', name: 'in_josm', width: 20, sortable: true, align: 'center' },
+             //   { display: '<img src="/img/sources/potlatch.16.png" alt="Potlatch 2" title="Potlatch 2"/>', name: 'in_potlatch', width: 20, sortable: true, align: 'center' },
+                { display: '<span title="Number of different values for this key">Values</span>', name: 'values_all', width: 70, sortable: true, align: 'right' },
+                { display: 'Prevalent Values', name: 'prevalent_values', width: 500, sortable: true, align: 'left' }
             ],
             searchitems: [
                 { display: 'Key', name: 'key' }
@@ -39,6 +42,9 @@
                         print_value_with_percent(row.count_ways,      row.count_ways_fraction),
                         print_value_with_percent(row.count_relations, row.count_relations_fraction),
                         print_with_ts(row.users_all),
+                        row.in_wiki     ? '&#x2714;' : '-',
+                        row.in_josm     ? '&#x2714;' : '-',
+                 //       row.in_potlatch ? '&#x2714;' : '-',
                         print_with_ts(row.values_all),
                         print_tag_list(row.key, row.prevalent_values)
                     ] };