From b5670d4f86d71a8bc1e558c50cfd421d991dae26 Mon Sep 17 00:00:00 2001 From: Jochen Topf Date: Sat, 17 May 2014 10:10:34 +0200 Subject: Refactoring some sql code. --- web/lib/api/v4/key.rb | 4 ++-- web/lib/api/v4/keys.rb | 13 ++++++++++--- web/lib/api/v4/tag.rb | 7 +++---- web/lib/sql.rb | 4 ++++ web/lib/ui/comparison.rb | 10 +++++----- web/lib/ui/keys.rb | 2 +- web/lib/ui/search.rb | 2 +- web/lib/ui/tags.rb | 8 ++++---- 8 files changed, 30 insertions(+), 20 deletions(-) diff --git a/web/lib/api/v4/key.rb b/web/lib/api/v4/key.rb index 1cbb817..66fae48 100644 --- a/web/lib/api/v4/key.rb +++ b/web/lib/api/v4/key.rb @@ -83,7 +83,7 @@ class Taginfo < Sinatra::Base condition('key = ?', key). get_first_value() || @db.select('SELECT png FROM db.key_distributions'). - condition('key IS NULL'). + is_null('key'). get_first_value() end @@ -101,7 +101,7 @@ class Taginfo < Sinatra::Base condition('key = ?', key). get_first_value() || @db.select('SELECT png FROM db.key_distributions'). - condition('key IS NULL'). + is_null('key'). get_first_value() end diff --git a/web/lib/api/v4/keys.rb b/web/lib/api/v4/keys.rb index 01739f7..6ea2ba8 100644 --- a/web/lib/api/v4/keys.rb +++ b/web/lib/api/v4/keys.rb @@ -76,9 +76,12 @@ class Taginfo < Sinatra::Base row['wikipages'] = Array.new end + key_list = res.map do |row| + "'" + SQLite3::Database.quote(row['key']) + "'" + end + wikipages = @db.select('SELECT key, lang, title, type FROM wiki.wikipages'). - condition("value IS NULL"). - condition("key IN (#{ res.map{ |row| "'" + SQLite3::Database.quote(row['key']) + "'" }.join(',') })"). + condition("key IN (#{ key_list.join(',') }) AND value IS NULL"). order_by([:key, :lang], 'ASC'). execute() @@ -96,8 +99,12 @@ class Taginfo < Sinatra::Base row['prevalent_values'] = Array.new end + key_list = res.map do |row| + "'" + SQLite3::Database.quote(row['key']) + "'" + end + prevvalues = @db.select('SELECT key, value, count, fraction FROM db.prevalent_values'). - condition("key IN (#{ res.map{ |row| "'" + SQLite3::Database.quote(row['key']) + "'" }.join(',') })"). + condition("key IN (#{ key_list.join(',') })"). order_by([:count], 'DESC'). execute() diff --git a/web/lib/api/v4/tag.rb b/web/lib/api/v4/tag.rb index 778fd4a..d05f73f 100644 --- a/web/lib/api/v4/tag.rb +++ b/web/lib/api/v4/tag.rb @@ -100,7 +100,7 @@ class Taginfo < Sinatra::Base condition('value = ?', value). get_first_value() || @db.select('SELECT png FROM db.key_distributions'). - condition('key IS NULL'). + is_null('key'). get_first_value() end @@ -120,7 +120,7 @@ class Taginfo < Sinatra::Base condition('value = ?', value). get_first_value() || @db.select('SELECT png FROM db.key_distributions'). - condition('key IS NULL'). + is_null('key'). get_first_value() end @@ -190,8 +190,7 @@ class Taginfo < Sinatra::Base end @db.select('SELECT * FROM db.tags'). - condition('key = ?', key). - condition('value = ?', value). + condition('key = ? AND value = ?', key, value). execute() do |row| ['all', 'nodes', 'ways', 'relations'].each_with_index do |type, n| out[n] = { diff --git a/web/lib/sql.rb b/web/lib/sql.rb index 0e6aa17..032573e 100644 --- a/web/lib/sql.rb +++ b/web/lib/sql.rb @@ -116,6 +116,10 @@ module SQL self end + def is_null(attribute) + condition("#{attribute} IS NULL") + end + def condition_if(expression, *param) if param.first.to_s != '' condition(expression, *param) diff --git a/web/lib/ui/comparison.rb b/web/lib/ui/comparison.rb index ad876c4..936071e 100644 --- a/web/lib/ui/comparison.rb +++ b/web/lib/ui/comparison.rb @@ -46,10 +46,10 @@ class Taginfo < Sinatra::Base data[:has_map] = data[:count_all] > 0 else - data[:count_all] = @db.select("SELECT count_all FROM db.tags").condition('key = ?', key).condition('value = ?', value).get_first_i - data[:count_nodes] = @db.select("SELECT count_nodes FROM db.tags").condition('key = ?', key).condition('value = ?', value).get_first_i - data[:count_ways] = @db.select("SELECT count_ways FROM db.tags").condition('key = ?', key).condition('value = ?', value).get_first_i - data[:count_relations] = @db.select("SELECT count_relations FROM db.tags").condition('key = ?', key).condition('value = ?', value).get_i + data[:count_all] = @db.select("SELECT count_all FROM db.tags").condition('key=? AND value=?', key, value).get_first_i + data[:count_nodes] = @db.select("SELECT count_nodes FROM db.tags").condition('key=? AND value=?', key, value).get_first_i + data[:count_ways] = @db.select("SELECT count_ways FROM db.tags").condition('key=? AND value=?', key, value).get_first_i + data[:count_relations] = @db.select("SELECT count_relations FROM db.tags").condition('key=? AND value=?', key, value).get_first_i desc = h(@db.select("SELECT description FROM wiki.wikipages WHERE lang=? AND key=? AND value=?", r18n.locale.code, key, value).get_first_value()) desc = h(@db.select("SELECT description FROM wiki.wikipages WHERE lang='en' AND key=? AND value=?", key, value).get_first_value()) if desc == '' @@ -59,7 +59,7 @@ class Taginfo < Sinatra::Base data[:wiki_pages] = @db.select("SELECT DISTINCT lang FROM wiki.wikipages WHERE key=? AND value=? ORDER BY lang", key, value).execute().map{ |row| row['lang'] } - data[:has_map] = (@db.count('tag_distributions').condition('key = ?', key).condition('value = ?', value).get_first_i > 0) + data[:has_map] = (@db.count('tag_distributions').condition('key=? AND value=?', key, value).get_first_i > 0) end end diff --git a/web/lib/ui/keys.rb b/web/lib/ui/keys.rb index 162792e..6f3e0d0 100644 --- a/web/lib/ui/keys.rb +++ b/web/lib/ui/keys.rb @@ -46,7 +46,7 @@ class Taginfo < Sinatra::Base end @josm_count = @db.count('josm_style_rules').condition('k = ?', @key).get_first_i - @wiki_count = @db.count('wiki.wikipages').condition('value IS NULL').condition('key=?', @key).get_first_i + @wiki_count = @db.count('wiki.wikipages').condition('key=? AND value IS NULL', @key).get_first_i @user_count = @db.select('SELECT users_all FROM db.keys').condition('key=?', @key).get_first_i @img_width = TaginfoConfig.get('geodistribution.width') * TaginfoConfig.get('geodistribution.scale_image') diff --git a/web/lib/ui/search.rb b/web/lib/ui/search.rb index a5c5b7e..7be38b9 100644 --- a/web/lib/ui/search.rb +++ b/web/lib/ui/search.rb @@ -60,7 +60,7 @@ END_XML else res = sel. condition_if("key LIKE ? || '%'", query). - condition("value IS NULL"). + is_null('value'). execute(). map{ |row| row['key'] } end diff --git a/web/lib/ui/tags.rb b/web/lib/ui/tags.rb index 342be55..6fcc995 100644 --- a/web/lib/ui/tags.rb +++ b/web/lib/ui/tags.rb @@ -30,10 +30,10 @@ class Taginfo < Sinatra::Base @sel[@filter_type] = ' selected="selected"' @filter_xapi = { 'all' => '*', nil => '*', 'nodes' => 'node', 'ways' => 'way', 'relations' => 'relation' }[@filter_type]; - @josm_count = @db.count('josm_style_rules').condition('k = ?', @key).condition('v = ?', @value).get_first_i - @wiki_count = @db.count('wiki.wikipages').condition('key=?', @key).condition('value=?', @value).get_first_i + @josm_count = @db.count('josm_style_rules').condition('k=? AND v=?', @key, @value).get_first_i + @wiki_count = @db.count('wiki.wikipages').condition('key=? AND value=?', @key, @value).get_first_i if @wiki_count == 0 - @wiki_count_key = @db.count('wiki.wikipages').condition('key=?', @key).condition('value IS NULL').get_first_i + @wiki_count_key = @db.count('wiki.wikipages').condition('key=? AND value IS NULL', @key).get_first_i end @count_all = @db.select("SELECT count_#{@filter_type} FROM db.tags").condition('key = ? AND value = ?', @key, @value).get_first_i @@ -55,7 +55,7 @@ class Taginfo < Sinatra::Base @has_rtype_link = true end - @has_map = @db.count('tag_distributions').condition('key = ?', @key).condition('value = ?', @value).get_first_i > 0 + @has_map = @db.count('tag_distributions').condition('key=? AND value=?', @key, @value).get_first_i > 0 @img_width = TaginfoConfig.get('geodistribution.width') * TaginfoConfig.get('geodistribution.scale_image') @img_height = TaginfoConfig.get('geodistribution.height') * TaginfoConfig.get('geodistribution.scale_image') -- cgit v1.2.3