Refactoring some sql code.

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')