diff options
Diffstat (limited to 'web')
| -rw-r--r-- | web/lib/api/db.rb | 39 | ||||
| -rw-r--r-- | web/lib/api/josm.rb | 52 | ||||
| -rw-r--r-- | web/lib/api/reports.rb | 19 | ||||
| -rw-r--r-- | web/lib/api/wiki.rb | 4 | ||||
| -rw-r--r-- | web/lib/sql.rb | 87 | ||||
| -rw-r--r-- | web/lib/ui/reports.rb | 2 | ||||
| -rw-r--r-- | web/lib/ui/search.rb | 2 | ||||
| -rw-r--r-- | web/rakefile.rb | 14 | ||||
| -rwxr-xr-x | web/taginfo.rb | 6 | ||||
| -rw-r--r-- | web/test/test_sql.rb | 103 | ||||
| -rw-r--r-- | web/views/reports/index.erb | 1 | ||||
| -rw-r--r-- | web/views/reports/key_lengths.erb | 111 |
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(' ') - @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 ? '✔' : '-', + row.in_josm ? '✔' : '-', + // row.in_potlatch ? '✔' : '-', + // row.in_merkaartor ? '✔' : '-', + print_with_ts(row.values_all), + print_tag_list(row.key, row.prevalent_values) + ] }; + }); + return data; + } + }); + }); +</script> |