diff options
| -rw-r--r-- | app/models/query.rb | 10 | ||||
| -rw-r--r-- | lib/plugins/acts_as_searchable/lib/acts_as_searchable.rb | 10 | ||||
| -rw-r--r-- | lib/redmine/database.rb | 15 | ||||
| -rw-r--r-- | test/unit/query_test.rb | 27 |
4 files changed, 39 insertions, 23 deletions
diff --git a/app/models/query.rb b/app/models/query.rb index b77f76c2e..a232258e0 100644 --- a/app/models/query.rb +++ b/app/models/query.rb @@ -779,9 +779,9 @@ class Query < ActiveRecord::Base date = Date.today sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year, is_custom_filter) when "~" - sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{self.class.connection.quote_string(value.first.to_s.downcase)}%'" + sql = sql_contains("#{db_table}.#{db_field}", value.first) when "!~" - sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{self.class.connection.quote_string(value.first.to_s.downcase)}%'" + sql = sql_contains("#{db_table}.#{db_field}", value.first, false) else raise "Unknown query operator #{operator}" end @@ -789,6 +789,12 @@ class Query < ActiveRecord::Base return sql end + # Returns a SQL LIKE statement with wildcards + def sql_contains(db_field, value, match=true) + value = "'%#{self.class.connection.quote_string(value.to_s)}%'" + Redmine::Database.like(db_field, value, :match => match) + end + # Adds a filter for the given custom field def add_custom_field_filter(field, assoc=nil) options = field.format.query_filter_options(field, self) diff --git a/lib/plugins/acts_as_searchable/lib/acts_as_searchable.rb b/lib/plugins/acts_as_searchable/lib/acts_as_searchable.rb index 96d1c2588..06d5db7de 100644 --- a/lib/plugins/acts_as_searchable/lib/acts_as_searchable.rb +++ b/lib/plugins/acts_as_searchable/lib/acts_as_searchable.rb @@ -159,15 +159,7 @@ module Redmine private :search_tokens_condition def search_token_match_statement(column, value='?') - if Redmine::Database.postgresql? - if Redmine::Database.postgresql_unaccent? - "unaccent(#{column}) ILIKE unaccent(#{value})" - else - "#{column} ILIKE #{value}" - end - else - "#{column} LIKE #{value}" - end + Redmine::Database.like(column, value) end private :search_token_match_statement diff --git a/lib/redmine/database.rb b/lib/redmine/database.rb index 290c78ea4..d8b4b7ccb 100644 --- a/lib/redmine/database.rb +++ b/lib/redmine/database.rb @@ -44,6 +44,21 @@ module Redmine end end + # Returns a SQL statement for case/accent (if possible) insensitive match + def like(left, right, options={}) + neg = (options[:match] == false ? 'NOT ' : '') + + if postgresql? + if postgresql_unaccent? + "unaccent(#{left}) #{neg}ILIKE unaccent(#{right})" + else + "#{left} #{neg}ILIKE #{right}" + end + else + "#{left} #{neg}LIKE #{right}" + end + end + # Resets database information def reset @postgresql_unaccent = nil diff --git a/test/unit/query_test.rb b/test/unit/query_test.rb index 1907cd9b3..aa45b5beb 100644 --- a/test/unit/query_test.rb +++ b/test/unit/query_test.rb @@ -592,12 +592,22 @@ class QueryTest < ActiveSupport::TestCase end def test_operator_contains - query = IssueQuery.new(:project => Project.find(1), :name => '_') - query.add_filter('subject', '~', ['uNable']) - assert query.statement.include?("LOWER(#{Issue.table_name}.subject) LIKE '%unable%'") + issue = Issue.generate!(:subject => 'AbCdEfG') + + query = IssueQuery.new(:name => '_') + query.add_filter('subject', '~', ['cdeF']) + result = find_issues_with_query(query) + assert_include issue, result + result.each {|issue| assert issue.subject.downcase.include?('cdef') } + end + + def test_operator_does_not_contain + issue = Issue.generate!(:subject => 'AbCdEfG') + + query = IssueQuery.new(:name => '_') + query.add_filter('subject', '!~', ['cdeF']) result = find_issues_with_query(query) - assert result.empty? - result.each {|issue| assert issue.subject.downcase.include?('unable') } + assert_not_include issue, result end def test_range_for_this_week_with_week_starting_on_monday @@ -625,13 +635,6 @@ class QueryTest < ActiveSupport::TestCase query.statement end - def test_operator_does_not_contains - query = IssueQuery.new(:project => Project.find(1), :name => '_') - query.add_filter('subject', '!~', ['uNable']) - assert query.statement.include?("LOWER(#{Issue.table_name}.subject) NOT LIKE '%unable%'") - find_issues_with_query(query) - end - def test_filter_assigned_to_me user = User.find(2) group = Group.find(10) |