jro_apps/redmine Commit - r13385:30175bf85e4d

Restore accent insensitive search with mysql (#18537)....

Jean-Philippe Lang -

r13385:30175bf85e4d

parent child

Context file:

r13385:30175bf85e4d

Collapse all files

lib/plugins/acts_as_searchable/lib/acts_as_searchable.rb 0 -2

             # Redmine - project management software
             # Copyright (C) 2006-2014  Jean-Philippe Lang
             #
             # This program is free software; you can redistribute it and/or
             # modify it under the terms of the GNU General Public License
             # as published by the Free Software Foundation; either version 2
             # of the License, or (at your option) any later version.
             #
             # This program is distributed in the hope that it will be useful,
             # but WITHOUT ANY WARRANTY; without even the implied warranty of
             # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
             # GNU General Public License for more details.
             #
             # You should have received a copy of the GNU General Public License
             # along with this program; if not, write to the Free Software
             # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
             module Redmine
               module Acts
                 module Searchable
                   def self.included(base)
                     base.extend ClassMethods
                   end
                   module ClassMethods
                     # Adds the search methods to the class.
                     #
                     # Options:
                     # * :columns - a column or an array of columns to search
                     # * :project_key - project foreign key (default to project_id)
                     # * :date_column - name of the datetime column used to sort results (default to :created_on)
                     # * :permission - permission required to search the model
                     # * :scope - scope used to search results
                     # * :preload - associations to preload when loading results for display
                     def acts_as_searchable(options = {})
                       return if self.included_modules.include?(Redmine::Acts::Searchable::InstanceMethods)
                       options.assert_valid_keys(:columns, :project_key, :date_column, :permission, :scope, :preload)
                       cattr_accessor :searchable_options
                       self.searchable_options = options
                       if searchable_options[:columns].nil?
                         raise 'No searchable column defined.'
                       elsif !searchable_options[:columns].is_a?(Array)
                         searchable_options[:columns] = [] << searchable_options[:columns]
                       end
                       searchable_options[:project_key] ||= "#{table_name}.project_id"
                       searchable_options[:date_column] ||= :created_on
                       # Should we search custom fields on this model ?
                       searchable_options[:search_custom_fields] = !reflect_on_association(:custom_values).nil?
                       send :include, Redmine::Acts::Searchable::InstanceMethods
                     end
                   end
                   module InstanceMethods
                     def self.included(base)
                       base.extend ClassMethods
                     end
                     module ClassMethods
                       # Searches the model for the given tokens and user visibility.
                       # The projects argument can be either nil (will search all projects), a project or an array of projects.
                       # Returns an array that contains the rank and id of all results.
                       # In current implementation, the rank is the record timestamp.
                       #
                       # Valid options:
                       # * :titles_only - searches tokens in the first searchable column only
                       # * :all_words - searches results that match all token
                       # * :limit - maximum number of results to return
                       #
                       # Example:
                       #   Issue.search_result_ranks_and_ids("foo")
                       #   # => [[Tue, 26 Jun 2007 22:16:00 UTC +00:00, 69], [Mon, 08 Oct 2007 14:31:00 UTC +00:00, 123]]
                       def search_result_ranks_and_ids(tokens, user=User.current, projects=nil, options={})
                         if projects.is_a?(Array) && projects.empty?
                           # no results
                           return []
                         end
                         tokens = [] << tokens unless tokens.is_a?(Array)
                         projects = [] << projects if projects.is_a?(Project)
                         columns = searchable_options[:columns]
                         columns = columns[0..0] if options[:titles_only]
                         token_clauses = columns.collect {|column| "(#{search_token_match_statement(column)})"}
                         if !options[:titles_only] && searchable_options[:search_custom_fields]
                           searchable_custom_fields = CustomField.where(:type => "#{self.name}CustomField", :searchable => true)
                           fields_by_visibility = searchable_custom_fields.group_by {|field|
                             field.visibility_by_project_condition(searchable_options[:project_key], user, "cfs.custom_field_id")
                           }
                           # only 1 subquery for all custom fields with the same visibility statement
                           fields_by_visibility.each do |visibility, fields|
                             ids = fields.map(&:id).join(',')
                             sql = "#{table_name}.id IN (SELECT cfs.customized_id FROM #{CustomValue.table_name} cfs" +
                               " WHERE cfs.customized_type='#{self.name}' AND cfs.customized_id=#{table_name}.id" +
                               " AND cfs.custom_field_id IN (#{ids})" +
                               " AND #{search_token_match_statement('cfs.value')}" +
                               " AND #{visibility})"
                             token_clauses << sql
                           end
                         end
                         sql = (['(' + token_clauses.join(' OR ') + ')'] * tokens.size).join(options[:all_words] ? ' AND ' : ' OR ')
                         tokens_conditions = [sql, * (tokens.collect {|w| "%#{w}%"} * token_clauses.size).sort]
                         search_scope(user, projects).
                           reorder(searchable_options[:date_column] => :desc, :id => :desc).
                           where(tokens_conditions).
                           limit(options[:limit]).
                           uniq.
                           pluck(searchable_options[:date_column], :id)
                       end
                       def search_token_match_statement(column, value='?')
                         case connection.adapter_name
                         when /postgresql/i
                           "#{column} ILIKE #{value}"
-                        when /mysql/i
-                          "LOWER(#{column}) COLLATE utf8_bin LIKE LOWER(#{value})"
                         else
                           "#{column} LIKE #{value}"
                         end
                       end
                       private :search_token_match_statement
                       # Returns the search scope for user and projects
                       def search_scope(user, projects)
                         scope = (searchable_options[:scope] || self)
                         if scope.is_a? Proc
                           scope = scope.call
                         end
                         if respond_to?(:visible) && !searchable_options.has_key?(:permission)
                           scope = scope.visible(user)
                         else
                           permission = searchable_options[:permission] || :view_project
                           scope = scope.where(Project.allowed_to_condition(user, permission))
                         end
                         if projects
                           scope = scope.where("#{searchable_options[:project_key]} IN (?)", projects.map(&:id))
                         end
                         scope
                       end
                       private :search_scope
                       # Returns search results of given ids
                       def search_results_from_ids(ids)
                         where(:id => ids).preload(searchable_options[:preload]).to_a
                       end
                       # Returns search results with same arguments as search_result_ranks_and_ids
                       def search_results(*args)
                         ranks_and_ids = search_result_ranks_and_ids(*args)
                         search_results_from_ids(ranks_and_ids.map(&:last))
                       end
                     end
                   end
                 end
               end
             end

test/test_helper.rb +4 0

             # Redmine - project management software
             # Copyright (C) 2006-2014  Jean-Philippe Lang
             #
             # This program is free software; you can redistribute it and/or
             # modify it under the terms of the GNU General Public License
             # as published by the Free Software Foundation; either version 2
             # of the License, or (at your option) any later version.
             #
             # This program is distributed in the hope that it will be useful,
             # but WITHOUT ANY WARRANTY; without even the implied warranty of
             # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
             # GNU General Public License for more details.
             #
             # You should have received a copy of the GNU General Public License
             # along with this program; if not, write to the Free Software
             # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
             if ENV["COVERAGE"]
               require 'simplecov'
               require File.expand_path(File.dirname(__FILE__) + "/coverage/html_formatter")
               SimpleCov.formatter = Redmine::Coverage::HtmlFormatter
               SimpleCov.start 'rails'
             end
             ENV["RAILS_ENV"] = "test"
             require File.expand_path(File.dirname(__FILE__) + "/../config/environment")
             require 'rails/test_help'
             require Rails.root.join('test', 'mocks', 'open_id_authentication_mock.rb').to_s
             require File.expand_path(File.dirname(__FILE__) + '/object_helpers')
             include ObjectHelpers
             require 'awesome_nested_set/version'
             require 'net/ldap'
             class ActionView::TestCase
               helper :application
               include ApplicationHelper
             end
             class ActiveSupport::TestCase
               include ActionDispatch::TestProcess
               self.use_transactional_fixtures = true
               self.use_instantiated_fixtures  = false
               #ESCAPED_CANT  = 'can&#x27;t'
               #ESCAPED_UCANT = 'Can&#x27;t'
               # Rails 4.0.2
               ESCAPED_CANT  = 'can&#39;t'
               ESCAPED_UCANT = 'Can&#39;t'
               def uploaded_test_file(name, mime)
                 fixture_file_upload("files/#{name}", mime, true)
               end
               # Mock out a file
               def self.mock_file
                 file = 'a_file.png'
                 file.stubs(:size).returns(32)
                 file.stubs(:original_filename).returns('a_file.png')
                 file.stubs(:content_type).returns('image/png')
                 file.stubs(:read).returns(false)
                 file
               end
               def mock_file
                 self.class.mock_file
               end
               def mock_file_with_options(options={})
                 file = ''
                 file.stubs(:size).returns(32)
                 original_filename = options[:original_filename] || nil
                 file.stubs(:original_filename).returns(original_filename)
                 content_type = options[:content_type] || nil
                 file.stubs(:content_type).returns(content_type)
                 file.stubs(:read).returns(false)
                 file
               end
               # Use a temporary directory for attachment related tests
               def set_tmp_attachments_directory
                 Dir.mkdir "#{Rails.root}/tmp/test" unless File.directory?("#{Rails.root}/tmp/test")
                 unless File.directory?("#{Rails.root}/tmp/test/attachments")
                   Dir.mkdir "#{Rails.root}/tmp/test/attachments"
                 end
                 Attachment.storage_path = "#{Rails.root}/tmp/test/attachments"
               end
               def set_fixtures_attachments_directory
                 Attachment.storage_path = "#{Rails.root}/test/fixtures/files"
               end
               def with_settings(options, &block)
                 saved_settings = options.keys.inject({}) do |h, k|
                   h[k] = case Setting[k]
                     when Symbol, false, true, nil
                       Setting[k]
                     else
                       Setting[k].dup
                     end
                   h
                 end
                 options.each {|k, v| Setting[k] = v}
                 yield
               ensure
                 saved_settings.each {|k, v| Setting[k] = v} if saved_settings
               end
               # Yields the block with user as the current user
               def with_current_user(user, &block)
                 saved_user = User.current
                 User.current = user
                 yield
               ensure
                 User.current = saved_user
               end
               def with_locale(locale, &block)
                 saved_localed = ::I18n.locale
                 ::I18n.locale = locale
                 yield
               ensure
                 ::I18n.locale = saved_localed
               end
               def self.ldap_configured?
                 @test_ldap = Net::LDAP.new(:host => '127.0.0.1', :port => 389)
                 return @test_ldap.bind
               rescue Exception => e
                 # LDAP is not listening
                 return nil
               end
               def self.convert_installed?
                 Redmine::Thumbnail.convert_available?
               end
               # Returns the path to the test +vendor+ repository
               def self.repository_path(vendor)
                 path = Rails.root.join("tmp/test/#{vendor.downcase}_repository").to_s
                 # Unlike ruby, JRuby returns Rails.root with backslashes under Windows
                 path.tr("\\", "/")
               end
               # Returns the url of the subversion test repository
               def self.subversion_repository_url
                 path = repository_path('subversion')
                 path = '/' + path unless path.starts_with?('/')
                 "file://#{path}"
               end
               # Returns true if the +vendor+ test repository is configured
               def self.repository_configured?(vendor)
                 File.directory?(repository_path(vendor))
               end
               def repository_path_hash(arr)
                 hs = {}
                 hs[:path]  = arr.join("/")
                 hs[:param] = arr.join("/")
                 hs
               end
               def sqlite?
                 ActiveRecord::Base.connection.adapter_name =~ /sqlite/i
               end
+              def mysql?
+                ActiveRecord::Base.connection.adapter_name =~ /mysql/i
+              end
               def assert_save(object)
                 saved = object.save
                 message = "#{object.class} could not be saved"
                 errors = object.errors.full_messages.map {|m| "- #{m}"}
                 message << ":\n#{errors.join("\n")}" if errors.any?
                 assert_equal true, saved, message
               end
               def assert_select_error(arg)
                 assert_select '#errorExplanation', :text => arg
               end
               def assert_include(expected, s, message=nil)
                 assert s.include?(expected), (message || "\"#{expected}\" not found in \"#{s}\"")
               end
               def assert_not_include(expected, s, message=nil)
                 assert !s.include?(expected), (message || "\"#{expected}\" found in \"#{s}\"")
               end
               def assert_select_in(text, *args, &block)
                 d = HTML::Document.new(CGI::unescapeHTML(String.new(text))).root
                 assert_select(d, *args, &block)
               end
               def assert_mail_body_match(expected, mail, message=nil)
                 if expected.is_a?(String)
                   assert_include expected, mail_body(mail), message
                 else
                   assert_match expected, mail_body(mail), message
                 end
               end
               def assert_mail_body_no_match(expected, mail, message=nil)
                 if expected.is_a?(String)
                   assert_not_include expected, mail_body(mail), message
                 else
                   assert_no_match expected, mail_body(mail), message
                 end
               end
               def mail_body(mail)
                 mail.parts.first.body.encoded
               end
               # awesome_nested_set new node lft and rgt value changed this refactor revision.
               #   https://github.com/collectiveidea/awesome_nested_set/commit/199fca9bb938e40200cd90714dc69247ef017c61
               # The reason of behavior change is that "self.class.base_class.unscoped" was added to this line.
               #   https://github.com/collectiveidea/awesome_nested_set/commit/199fca9bb9#diff-f61b59a5e6319024e211b0ffdd0e4ef1R273
               # It seems correct behavior because of this line comment.
               #   https://github.com/collectiveidea/awesome_nested_set/blame/199fca9bb9/lib/awesome_nested_set/model.rb#L278
               def new_issue_lft
                 # ::AwesomeNestedSet::VERSION > "2.1.6" ? Issue.maximum(:rgt) + 1 : 1
                 Issue.maximum(:rgt) + 1
               end
             end
             module Redmine
               class RoutingTest < ActionDispatch::IntegrationTest
                 def should_route(arg)
                   arg = arg.dup
                   request = arg.keys.detect {|key| key.is_a?(String)}
                   raise ArgumentError unless request
                   options = arg.slice!(request)
                   raise ArgumentError unless request =~ /\A(GET|POST|PUT|PATCH|DELETE)\s+(.+)\z/
                   method, path = $1.downcase.to_sym, $2
                   raise ArgumentError unless arg.values.first =~ /\A(.+)#(.+)\z/
                   controller, action = $1, $2
                   assert_routing(
                     {:method => method, :path => path},
                     options.merge(:controller => controller, :action => action)
                   )
                 end
               end
               class IntegrationTest < ActionDispatch::IntegrationTest
                 def log_user(login, password)
                   User.anonymous
                   get "/login"
                   assert_equal nil, session[:user_id]
                   assert_response :success
                   assert_template "account/login"
                   post "/login", :username => login, :password => password
                   assert_equal login, User.find(session[:user_id]).login
                 end
                 def credentials(user, password=nil)
                   {'HTTP_AUTHORIZATION' => ActionController::HttpAuthentication::Basic.encode_credentials(user, password || user)}
                 end
               end
               module ApiTest
                 API_FORMATS = %w(json xml).freeze
                 # Base class for API tests
                 class Base < Redmine::IntegrationTest
                   def setup
                     Setting.rest_api_enabled = '1'
                   end
                   def teardown
                     Setting.rest_api_enabled = '0'
                   end
                 end
                 class Routing < Redmine::RoutingTest
                   def should_route(arg)
                     arg = arg.dup
                     request = arg.keys.detect {|key| key.is_a?(String)}
                     raise ArgumentError unless request
                     options = arg.slice!(request)
                     API_FORMATS.each do |format|
                       format_request = request.sub /$/, ".#{format}"
                       super options.merge(format_request => arg[request], :format => format)
                     end
                   end
                 end
               end
             end

test/unit/search_test.rb +20 -11

             # encoding: utf-8
             #
             # Redmine - project management software
             # Copyright (C) 2006-2014  Jean-Philippe Lang
             #
             # This program is free software; you can redistribute it and/or
             # modify it under the terms of the GNU General Public License
             # as published by the Free Software Foundation; either version 2
             # of the License, or (at your option) any later version.
             #
             # This program is distributed in the hope that it will be useful,
             # but WITHOUT ANY WARRANTY; without even the implied warranty of
             # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
             # GNU General Public License for more details.
             #
             # You should have received a copy of the GNU General Public License
             # along with this program; if not, write to the Free Software
             # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
             require File.expand_path('../../test_helper', __FILE__)
             class SearchTest < ActiveSupport::TestCase
               fixtures :users,
                        :members,
                        :member_roles,
                        :projects,
                        :projects_trackers,
                        :roles,
                        :enabled_modules,
                        :issues,
                        :trackers,
                        :issue_statuses,
                        :enumerations,
                        :journals,
                        :journal_details,
                        :repositories,
                        :changesets
               def setup
                 @project = Project.find(1)
                 @issue_keyword = '%unable to print recipes%'
                 @issue = Issue.find(1)
                 @changeset_keyword = '%very first commit%'
                 @changeset = Changeset.find(100)
               end
               def test_search_by_anonymous
                 User.current = nil
                 r = Issue.search_results(@issue_keyword)
                 assert r.include?(@issue)
                 r = Changeset.search_results(@changeset_keyword)
                 assert r.include?(@changeset)
                 # Removes the :view_changesets permission from Anonymous role
                 remove_permission Role.anonymous, :view_changesets
                 User.current = nil
                 r = Issue.search_results(@issue_keyword)
                 assert r.include?(@issue)
                 r = Changeset.search_results(@changeset_keyword)
                 assert !r.include?(@changeset)
                 # Make the project private
                 @project.update_attribute :is_public, false
                 r = Issue.search_results(@issue_keyword)
                 assert !r.include?(@issue)
                 r = Changeset.search_results(@changeset_keyword)
                 assert !r.include?(@changeset)
               end
               def test_search_by_user
                 User.current = User.find_by_login('rhill')
                 assert User.current.memberships.empty?
                 r = Issue.search_results(@issue_keyword)
                 assert r.include?(@issue)
                 r = Changeset.search_results(@changeset_keyword)
                 assert r.include?(@changeset)
                 # Removes the :view_changesets permission from Non member role
                 remove_permission Role.non_member, :view_changesets
                 User.current = User.find_by_login('rhill')
                 r = Issue.search_results(@issue_keyword)
                 assert r.include?(@issue)
                 r = Changeset.search_results(@changeset_keyword)
                 assert !r.include?(@changeset)
                 # Make the project private
                 @project.update_attribute :is_public, false
                 r = Issue.search_results(@issue_keyword)
                 assert !r.include?(@issue)
                 r = Changeset.search_results(@changeset_keyword)
                 assert !r.include?(@changeset)
               end
               def test_search_by_allowed_member
                 User.current = User.find_by_login('jsmith')
                 assert User.current.projects.include?(@project)
                 r = Issue.search_results(@issue_keyword)
                 assert r.include?(@issue)
                 r = Changeset.search_results(@changeset_keyword)
                 assert r.include?(@changeset)
                 # Make the project private
                 @project.update_attribute :is_public, false
                 r = Issue.search_results(@issue_keyword)
                 assert r.include?(@issue)
                 r = Changeset.search_results(@changeset_keyword)
                 assert r.include?(@changeset)
               end
               def test_search_by_unallowed_member
                 # Removes the :view_changesets permission from user's and non member role
                 remove_permission Role.find(1), :view_changesets
                 remove_permission Role.non_member, :view_changesets
                 User.current = User.find_by_login('jsmith')
                 assert User.current.projects.include?(@project)
                 r = Issue.search_results(@issue_keyword)
                 assert r.include?(@issue)
                 r = Changeset.search_results(@changeset_keyword)
                 assert !r.include?(@changeset)
                 # Make the project private
                 @project.update_attribute :is_public, false
                 r = Issue.search_results(@issue_keyword)
                 assert r.include?(@issue)
                 r = Changeset.search_results(@changeset_keyword)
                 assert !r.include?(@changeset)
               end
               def test_search_issue_with_multiple_hits_in_journals
                 issue = Issue.find(1)
                 assert_equal 2, issue.journals.where("notes LIKE '%notes%'").count
                 r = Issue.search_results('%notes%')
                 assert_equal 1, r.size
                 assert_equal issue, r.first
               end
               def test_search_should_be_case_insensitive
                 issue = Issue.generate!(:subject => "AzerTY")
                 r = Issue.search_results('AZERty')
                 assert_include issue, r
               end
-              def test_search_should_not_use_ruby_downcase
+              def test_search_should_be_case_insensitive_with_accented_characters
-                skip "SQLite does not support case insensitive match for non-ASCII characters" if sqlite?
+                unless sqlite?
-                issue1 = Issue.generate!(:subject => "Special chars: ÖÖ")
+                  issue1 = Issue.generate!(:subject => "Special chars: ÖÖ")
-                issue2 = Issue.generate!(:subject => "Special chars: Öö")
+                  issue2 = Issue.generate!(:subject => "Special chars: Öö")
-                Issue.generate!(:subject => "Special chars: oo")
-                Issue.generate!(:subject => "Special chars: OO")
+                  r = Issue.search_results('ÖÖ')
+                  assert_include issue1, r
-                r = Issue.search_results('ÖÖ')
+                  assert_include issue2, r
-                assert_include issue1, r
+                end
-                assert_include issue2, r
+              end
-                assert_equal 2, r.size
+              def test_search_should_be_case_and_accent_insensitive_with_mysql
+                if mysql?
+                  issue1 = Issue.generate!(:subject => "OO")
+                  issue2 = Issue.generate!(:subject => "oo")
+                  r = Issue.search_results('ÖÖ')
+                  assert_include issue1, r
+                  assert_include issue2, r
+                end
               end
               private
               def remove_permission(role, permission)
                 role.permissions = role.permissions - [ permission ]
                 role.save
               end
             end

General Comments 0

Write
Preview

You need to be logged in to leave comments. Login now

No TODOs yet

	Site-wide shortcuts
/	Use quick search box
g h	Goto home page
g g	Goto my private gists page
g G	Goto my public gists page
g 0-9	Goto bookmarked items from 0-9
n r	New repository page
n g	New gist page

	Repositories
g s	Goto summary page
g c	Goto changelog page
g f	Goto files page
g F	Goto files page with file search activated
g p	Goto pull requests page
g o	Goto repository settings
g O	Goto repository access permissions settings
t s	Toggle sidebar on some pages