summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--web/lib/api/db.rb39
-rw-r--r--web/lib/api/josm.rb52
-rw-r--r--web/lib/api/reports.rb19
-rw-r--r--web/lib/api/wiki.rb4
-rw-r--r--web/lib/sql.rb87
-rw-r--r--web/lib/ui/reports.rb2
-rw-r--r--web/lib/ui/search.rb2
-rw-r--r--web/rakefile.rb14
-rwxr-xr-xweb/taginfo.rb6
-rw-r--r--web/test/test_sql.rb103
-rw-r--r--web/views/reports/index.erb1
-rw-r--r--web/views/reports/key_lengths.erb111
12 files changed, 387 insertions, 53 deletions
diff --git a/web/lib/api/db.rb b/web/lib/api/db.rb
index a460b8b..56ca45f 100644
--- a/web/lib/api/db.rb
+++ b/web/lib/api/db.rb
@@ -8,7 +8,20 @@ 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, :in_wiki, :in_josm, :in_potlatch], params[:sortname], params[:sortorder]).
+ order_by(params[:sortname], params[:sortorder]) { |o|
+ o.key
+ o.count_all
+ o.count_nodes
+ o.count_ways
+ o.count_relations
+ o.values_all
+ o.users_all
+ o.in_wiki
+ o.in_josm
+ o.in_potlatch
+ o.length 'length(key)'
+ o.length :key
+ }.
paging(params[:rp], params[:page]).
execute()
@@ -94,7 +107,13 @@ class Taginfo < Sinatra::Base
condition("count_#{filter_type} > 0").
condition('key = ?', key).
condition_if("value LIKE '%' || ? || '%'", params[:query]).
- order_by([:value, :count_all, :count_nodes, :count_ways, :count_relations], params[:sortname], params[:sortorder]).
+ order_by(params[:sortname], params[:sortorder]){ |o|
+ o.value
+ o.count_all
+ o.count_nodes
+ o.count_ways
+ o.count_relations
+ }.
paging(params[:rp], params[:page]).
execute()
@@ -131,7 +150,11 @@ class Taginfo < Sinatra::Base
res = @db.select("SELECT p.key1 AS other_key, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.keypairs p, db.keys k WHERE p.key1=k.key AND p.key2=? AND p.count_#{filter_type} > 0
UNION SELECT p.key2 AS other_key, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.keypairs p, db.keys k WHERE p.key2=k.key AND p.key1=? AND p.count_#{filter_type} > 0", key, key).
- order_by([:together_count, :other_key, :from_fraction], params[:sortname], params[:sortorder]).
+ order_by(params[:sortname], params[:sortorder]){ |o|
+ o.together_count
+ o.other_key
+ o.from_fraction
+ }.
paging(params[:rp], params[:page]).
execute()
@@ -155,7 +178,15 @@ class Taginfo < Sinatra::Base
res = @db.select('SELECT * FROM popular_keys').
condition_if("key LIKE '%' || ? || '%'", params[:query]).
- order_by([:key, :scale_count, :scale_users, :scale_wiki, :scale_josm, :scale1, :scale2], params[:sortname], params[:sortorder]).
+ order_by(params[:sortname], params[:sortorder]){ |o|
+ o.key
+ o.scale_count
+ o.scale_user
+ o.scale_wiki
+ o.scale_josm
+ o.scale1
+ o.scale2
+ }.
paging(params[:rp], params[:page]).
execute()
diff --git a/web/lib/api/josm.rb b/web/lib/api/josm.rb
index 7e11514..b08d42b 100644
--- a/web/lib/api/josm.rb
+++ b/web/lib/api/josm.rb
@@ -23,17 +23,6 @@ class Taginfo < Sinatra::Base
}.to_json
end
- def sort_by_for_keys
- return case params[:sortname]
- when 'k'
- ['k', 'v', 'b']
- when 'v'
- ['v', 'b', 'k']
- else
- params[:sortname]
- end
- end
-
get '/api/2/josm/styles/:style' do
total = @db.count('josm_style_rules').
condition_if("k LIKE '%' || ? || '%' OR v LIKE '%' || ? || '%'", params[:query], params[:query]).
@@ -41,26 +30,23 @@ class Taginfo < Sinatra::Base
res = @db.select('SELECT * FROM josm_style_rules').
condition_if("k LIKE '%' || ? || '%' OR v LIKE '%' || ? || '%'", params[:query], params[:query]).
- order_by([:k, :v, :b, :scale_min, :scale_max], sort_by_for_keys, params[:sortorder]).
+ order_by(params[:sortname], params[:sortorder]){ |o|
+ o.k :k
+ o.k :v
+ o.k :b
+ o.v :v
+ o.v :b
+ o.v :k
+ o.b
+ o.scale_min
+ o.scale_max
+ }.
paging(params[:rp], params[:page]).
execute()
return get_josm_result(total, res);
end
- def sort_by_for_values
- return case params[:sortname]
- when 'v'
- ['v', 'b']
- when 'scale_min'
- ['scale_min', 'scale_max', 'v', 'b']
- when 'scale_max'
- ['scale_max', 'scale_min', 'v', 'b']
- else
- params[:sortname]
- end
- end
-
get '/api/2/josm/styles/:style/keys' do
style = params[:style] # XXX do something with this
key = params[:key]
@@ -73,7 +59,19 @@ class Taginfo < Sinatra::Base
res = @db.select('SELECT * FROM josm_style_rules').
condition('k = ?', key).
condition_if("v LIKE '%' || ? || '%'", params[:query]).
- order_by([:v, :b, :scale_min, :scale_max], sort_by_for_values, params[:sortorder]).
+ order_by(params[:sortname], params[:sortorder]){ |o|
+ o.v :v
+ o.v :b
+ o.b
+ o.scale_min :scale_min
+ o.scale_min :scale_max
+ o.scale_min :v
+ o.scale_min :b
+ o.scale_max :scale_max
+ o.scale_max :scale_min
+ o.scale_max :v
+ o.scale_max :b
+ }.
paging(params[:rp], params[:page]).
execute()
@@ -92,7 +90,7 @@ class Taginfo < Sinatra::Base
res = @db.select('SELECT * FROM josm_style_rules').
condition('k = ?', key).
condition('v = ?', value).
- order_by([:scale_min], 'scale_min', 'ASC').
+ order_by(:scale_min).
paging(params[:rp], params[:page]).
execute()
diff --git a/web/lib/api/reports.rb b/web/lib/api/reports.rb
index 798f96b..1f6153b 100644
--- a/web/lib/api/reports.rb
+++ b/web/lib/api/reports.rb
@@ -18,7 +18,12 @@ class Taginfo < Sinatra::Base
condition('count_all > ?', min_count).
condition("in_wiki#{english} = 0").
condition_if("key LIKE '%' || ? || '%'", params[:query]).
- order_by([:key, :count_all, :values_all, :users_all], params[:sortname], params[:sortorder]).
+ order_by(params[:sortname], params[:sortorder]){ |o|
+ o.key
+ o.count_all
+ o.values_all
+ o.users_all
+ }.
paging(params[:rp], params[:page]).
execute()
@@ -27,12 +32,12 @@ class Taginfo < Sinatra::Base
:rp => params[:rp].to_i,
:total => total,
:data => res.map{ |row| {
- :key => row['key'],
- :count_all => row['count_all'].to_i,
- :count_all_fraction => row['count_all'].to_f / @db.stats('objects'),
- :values_all => row['values_all'].to_i,
- :users_all => row['users_all'].to_i,
- :prevalent_values => (row['prevalent_values'] || '').split('|').map{ |pv| pv }
+ :key => row['key'],
+ :count_all => row['count_all'].to_i,
+ :count_all_fraction => row['count_all'].to_f / @db.stats('objects'),
+ :values_all => row['values_all'].to_i,
+ :users_all => row['users_all'].to_i,
+ :prevalent_values => (row['prevalent_values'] || '').split('|').map{ |pv| pv }
} }
}.to_json
end
diff --git a/web/lib/api/wiki.rb b/web/lib/api/wiki.rb
index cf8303f..831e7b0 100644
--- a/web/lib/api/wiki.rb
+++ b/web/lib/api/wiki.rb
@@ -35,7 +35,9 @@ class Taginfo < Sinatra::Base
res = @db.select('SELECT key, langs FROM wiki.wikipages_keys').
condition_if("key LIKE '%' || ? || '%'", params[:query]).
- order_by([:key], params[:sortname], params[:sortorder]).
+ order_by(params[:sortname], params[:sortorder]){ |o|
+ o.key
+ }.
paging(params[:rp], params[:page]).
execute()
diff --git a/web/lib/sql.rb b/web/lib/sql.rb
index 0ebc0d8..af03825 100644
--- a/web/lib/sql.rb
+++ b/web/lib/sql.rb
@@ -78,25 +78,37 @@ module SQL
self
end
- def order_by(allowed, values, direction='ASC')
- unless values.is_a?(Array)
+ def order_by(values, direction='ASC', &block)
+
+ if values.is_a?(Array)
+ values = values.compact
+ else
values = [values]
end
- values.compact.each do |value|
- unless allowed.include?(value.to_sym)
- raise ArgumentError, 'order by this attribute not allowed'
- end
- end
+
+ o = Order.new(values, &block)
+
if direction.nil?
direction = 'ASC'
else
+ direction = direction.to_s
if direction !~ /^(asc|desc)$/i
raise ArgumentError, 'direction must be ASC or DESC'
end
end
- unless values.compact.empty?
- @order_by = "ORDER BY " + values.map{ |value| "#{value} #{direction}" }.join(',')
+
+ values.each do |value|
+ unless o._allowed(value)
+ raise ArgumentError, 'order by this attribute not allowed'
+ end
+ end
+
+ unless values.empty?
+ @order_by = "ORDER BY " + values.map{ |value|
+ o[value.to_s].map{ |oel| oel.to_s(direction.upcase) }.join(',')
+ }.join(',')
end
+
self
end
@@ -156,4 +168,61 @@ module SQL
end
+ class OrderElement
+
+ @@DIRECTION = { 'ASC' => 'DESC', 'DESC' => 'ASC' }
+
+ def initialize(column, reverse)
+ @column = column
+ @reverse = reverse
+ end
+
+ def to_s(direction)
+ dir = @reverse ? @@DIRECTION[direction.upcase] : direction.upcase
+ "#{@column} #{dir}"
+ end
+
+ end
+
+ class Order
+
+ def initialize(values, &block)
+ @allowed = Hash.new
+ if block_given?
+ yield self
+ else
+ values.each do |value|
+ _add(value.to_s)
+ end
+ end
+ end
+
+ def _allowed(field)
+ @allowed.has_key?(field.to_s)
+ end
+
+ def [](field)
+ @allowed[field]
+ end
+
+ def _add(field, attribute=nil)
+ field = field.to_s
+ if field =~ /^(.*)!$/
+ field = $1
+ reverse = true
+ else
+ reverse = false
+ end
+ attribute = field if attribute.nil?
+
+ @allowed[field] ||= Array.new
+ @allowed[field] << OrderElement.new(attribute.to_s, reverse)
+ end
+
+ def method_missing(field, attribute=nil)
+ _add(field, attribute)
+ end
+
+ end
+
end
diff --git a/web/lib/ui/reports.rb b/web/lib/ui/reports.rb
index bebad52..983a2f6 100644
--- a/web/lib/ui/reports.rb
+++ b/web/lib/ui/reports.rb
@@ -9,7 +9,7 @@ class Taginfo < Sinatra::Base
#--------------------------------------------------------------------------
- ['Frequently Used Keys Without Wiki Page', 'Characters in Keys'].each do |title|
+ ['Frequently Used Keys Without Wiki Page', 'Characters in Keys', 'Key Lengths'].each do |title|
name = title.gsub(/ /, '_').downcase
get '/reports/' + name do
@title = title
diff --git a/web/lib/ui/search.rb b/web/lib/ui/search.rb
index a19aedc..0a93e2c 100644
--- a/web/lib/ui/search.rb
+++ b/web/lib/ui/search.rb
@@ -13,7 +13,7 @@ class Taginfo < Sinatra::Base
@substring_keys = @db.select('SELECT key FROM keys').
condition("key LIKE '%' || ? || '%' AND key != ?", params[:search], params[:search]).
- order_by([:key], :key, 'ASC').
+ order_by(:key).
execute().
map{ |row| row['key'] }
diff --git a/web/rakefile.rb b/web/rakefile.rb
new file mode 100644
index 0000000..7780fd0
--- /dev/null
+++ b/web/rakefile.rb
@@ -0,0 +1,14 @@
+require 'rake'
+require 'rake/testtask'
+#require 'rake/clean'
+
+$: << 'lib'
+
+task :default => :test
+
+desc "Run the tests"
+Rake::TestTask::new do |t|
+ t.test_files = FileList['test/test_*.rb']
+ t.verbose = true
+end
+
diff --git a/web/taginfo.rb b/web/taginfo.rb
index 762271f..6c3debf 100755
--- a/web/taginfo.rb
+++ b/web/taginfo.rb
@@ -142,7 +142,7 @@ class Taginfo < Sinatra::Base
@prevalent_values = @db.select("SELECT value, count_#{@filter_type} AS count FROM tags").
condition('key=?', @key).
condition('count > ?', @count_all_values * 0.02).
- order_by([:count], :count, 'DESC').
+ order_by(:count, 'DESC').
execute().map{ |row| [{ 'value' => row['value'], 'count' => row['count'].to_i }] }
# add "(other)" label for the rest of the values
@@ -159,9 +159,9 @@ class Taginfo < Sinatra::Base
'<img src="/img/types/' + (@merkaartor_selector =~ /Type is #{name}/ ? type.to_s : 'none') + '.16.png" alt="' + name + '" title="' + name + '"/>'
}.join('&nbsp;')
- @merkaartor_values = @db.select('SELECT value FROM merkaartor.tags').condition('key=?', @key).order_by([:value], :value, 'ASC').execute().map{ |row| row['value'] }
+ @merkaartor_values = @db.select('SELECT value FROM merkaartor.tags').condition('key=?', @key).order_by(:value).execute().map{ |row| row['value'] }
- @merkaartor_desc = @db.select('SELECT lang, description FROM key_descriptions').condition('key=?', @key).order_by([:lang], :lang, 'ASC').execute()
+ @merkaartor_desc = @db.select('SELECT lang, description FROM key_descriptions').condition('key=?', @key).order_by(:lang).execute()
erb :key
end
diff --git a/web/test/test_sql.rb b/web/test/test_sql.rb
new file mode 100644
index 0000000..4ff7ca0
--- /dev/null
+++ b/web/test/test_sql.rb
@@ -0,0 +1,103 @@
+$: << 'lib'
+require File.join(File.dirname(__FILE__), '..', 'lib', 'sql.rb')
+require 'test/unit'
+
+class TestSql < Test::Unit::TestCase
+
+ def setup
+ @select = SQL::Select.new('test', 'test')
+ end
+
+ def test_order_by_no_block
+ assert_kind_of SQL::Select, @select
+ @select.order_by(:foo)
+ assert_equal 'test ORDER BY foo ASC', @select.build_query
+ end
+
+ def test_order_by_no_block_desc
+ assert_kind_of SQL::Select, @select
+ @select.order_by(:foo, :desc)
+ assert_equal 'test ORDER BY foo DESC', @select.build_query
+ end
+
+ def test_order_by_simple
+ assert_kind_of SQL::Select, @select
+ @select.order_by(:foo) do |o|
+ o.foo
+ end
+ assert_equal 'test ORDER BY foo ASC', @select.build_query
+ end
+
+ def test_order_by_desc
+ @select.order_by(:foo, 'desc') do |o|
+ o.foo
+ end
+ assert_equal 'test ORDER BY foo DESC', @select.build_query
+ end
+
+ def test_order_by_unused
+ @select.order_by([:foo]) do |o|
+ o.foo
+ o.bar! 'baz'
+ end
+ assert_equal 'test ORDER BY foo ASC', @select.build_query
+ end
+
+ def test_order_by_reverse
+ @select.order_by(:bar) do |o|
+ o.foo
+ o.bar! 'baz'
+ end
+ assert_equal 'test ORDER BY baz DESC', @select.build_query
+ end
+
+ def test_order_by_array
+ @select.order_by([:foo, 'bar']) do |o|
+ o.bar! 'baz'
+ o.foo
+ end
+ assert_equal 'test ORDER BY foo ASC,baz DESC', @select.build_query
+ end
+
+ def test_order_by_array_reverse
+ @select.order_by([:bar, :foo], 'DESC') do |o|
+ o.foo
+ o.bar!
+ end
+ assert_equal 'test ORDER BY bar ASC,foo DESC', @select.build_query
+ end
+
+ def test_order_by_array_multiple
+ @select.order_by([:bar, :foo], :DESC) do |o|
+ o.foo 'f1'
+ o.foo! :f2
+ o.bar :baz
+ end
+ assert_equal 'test ORDER BY baz DESC,f1 DESC,f2 ASC', @select.build_query
+ end
+
+ def test_order_by_map
+ @select.order_by(:length) do |o|
+ o.length 'length(foo)'
+ o.length :foo
+ end
+ assert_equal 'test ORDER BY length(foo) ASC,foo ASC', @select.build_query
+ end
+
+ def test_order_by_asc_or_desc
+ assert_raise ArgumentError do
+ @select.order_by(:foo, 'blub') do |o|
+ o.foo
+ end
+ end
+ end
+
+ def test_order_by_no_def
+ assert_raise ArgumentError do
+ @select.order_by(:foo) do |o|
+ o.bar
+ end
+ end
+ end
+
+end
diff --git a/web/views/reports/index.erb b/web/views/reports/index.erb
index 401bdd6..4a18155 100644
--- a/web/views/reports/index.erb
+++ b/web/views/reports/index.erb
@@ -4,6 +4,7 @@
<ul>
<li><a href="/reports/characters_in_keys">Characters in keys</a></li>
+ <li><a href="/reports/key_lengths">Keys lengths</a></li>
</ul>
<h2>Wiki</h2>
diff --git a/web/views/reports/key_lengths.erb b/web/views/reports/key_lengths.erb
new file mode 100644
index 0000000..b4e6429
--- /dev/null
+++ b/web/views/reports/key_lengths.erb
@@ -0,0 +1,111 @@
+<h1><%= @title %></h1>
+
+<%
+ hist = Array.new
+ @db.execute('SELECT length(key) AS length, count(*) AS count FROM db.keys GROUP BY length(key) ORDER BY length(key)') do |row|
+ hist[row['length'].to_i] = row['count'].to_i
+ end
+ hist = hist.map{ |item| item.nil? ? 0 : item }
+%>
+
+<div id="canvas"></div>
+
+<table id="keys">
+</table>
+
+<script type="text/javascript+protovis">
+
+var data = <%= hist.to_json %>;
+
+var w=900, h=300;
+
+var vis = new pv.Panel()
+ .canvas('canvas')
+ .width(w)
+ .height(h)
+ .top(20)
+ .right(20)
+ .bottom(20)
+ .left(40);
+
+var x = pv.Scale.linear(0, <%= hist.size %>).range(0, w);
+var y = pv.Scale.linear(0, <%= hist.max %>).range(0, h);
+
+var bar = vis.add(pv.Bar)
+ .data(data)
+ .bottom(0)
+ .left(function() x(this.index)-3)
+ .height(y)
+ .title(function(d) '' + d + ' keys of length ' + this.index )
+ .width(6);
+
+vis.add(pv.Rule)
+ .data(y.ticks(10))
+ .bottom(y)
+ .left(-5)
+ .width(-5)
+ .anchor("left").add(pv.Label)
+ .textMargin(8)
+ .text(x.tickFormat);
+
+vis.add(pv.Rule)
+ .data(x.ticks(10))
+ .left(x)
+ .bottom(-2)
+ .height(-5)
+ .anchor("bottom").add(pv.Label)
+ .textMargin(8)
+ .text(x.tickFormat);
+
+vis.render();
+
+</script>
+<script type="text/javascript">
+ jQuery(document).ready(function() {
+ jQuery('#keys').flexigrid({
+ url: '/api/2/db/keys',
+ method: 'GET',
+ dataType: 'json',
+ colModel: [
+ { display: 'Length', name: 'length', width: 60, sortable: true, align: 'right' },
+ { 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: '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: '<img src="/img/sources/merkaartor.16.png" alt="Merkaartor" title="Merkaartor"/>', name: 'in_merkaartor', 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' }
+ ],
+ sortname: 'length',
+ sortorder: 'asc',
+ showToggleBtn: false,
+ usepager: true,
+ useRp: true,
+ rp: 15,
+ rpOptions: [10,15,20,25,50,100],
+ height: 420,
+ preProcess: function(data) {
+ data.rows = jQuery.map(data.data, function(row, i) {
+ return { 'cell': [
+ row.key.length,
+ link_to_key(row.key),
+ print_value_with_percent(row.count_all, row.count_all_fraction),
+ print_with_ts(row.users_all),
+ row.in_wiki ? '&#x2714;' : '-',
+ row.in_josm ? '&#x2714;' : '-',
+ // row.in_potlatch ? '&#x2714;' : '-',
+ // row.in_merkaartor ? '&#x2714;' : '-',
+ print_with_ts(row.values_all),
+ print_tag_list(row.key, row.prevalent_values)
+ ] };
+ });
+ return data;
+ }
+ });
+ });
+</script>
generated by cgit v1.2.3 (git 2.25.4) at 2024-06-13 21:42:00 +0000