From af5872b7d432f78befcb7e9f87187a5b82b588bf Mon Sep 17 00:00:00 2001
From: Jean-Philippe Lang <jp_lang@yahoo.fr>
Date: Sun, 8 Feb 2015 15:13:57 +0000
Subject: [PATCH] Support for accent insensitive search with PostgreSQL
 (#18801).

If the unaccent extension is installed, the search engine will use it in order to do accent insensitive search.

git-svn-id: http://svn.redmine.org/redmine/trunk@13989 e93f8b46-1217-0410-a6f0-8f06a7374b81
---
 .../lib/acts_as_searchable.rb                 |  9 ++--
 lib/redmine/database.rb                       | 53 +++++++++++++++++++
 test/test_helper.rb                           |  4 ++
 test/unit/search_test.rb                      | 19 +++++++
 4 files changed, 82 insertions(+), 3 deletions(-)
 create mode 100644 lib/redmine/database.rb

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 e490c8fa7..96d1c2588 100644
--- a/lib/plugins/acts_as_searchable/lib/acts_as_searchable.rb
+++ b/lib/plugins/acts_as_searchable/lib/acts_as_searchable.rb
@@ -159,9 +159,12 @@ module Redmine
           private :search_tokens_condition
 
           def search_token_match_statement(column, value='?')
-            case connection.adapter_name
-            when /postgresql/i
-              "#{column} ILIKE #{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
diff --git a/lib/redmine/database.rb b/lib/redmine/database.rb
new file mode 100644
index 000000000..290c78ea4
--- /dev/null
+++ b/lib/redmine/database.rb
@@ -0,0 +1,53 @@
+# Redmine - project management software
+# Copyright (C) 2006-2015  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
+  # Helper module to get information about the Redmine database
+  module Database
+    class << self
+      # Returns true if the database is PostgreSQL
+      def postgresql?
+        (ActiveRecord::Base.connection.adapter_name =~ /postgresql/i).present?
+      end
+
+      # Returns the PostgreSQL version or nil if another DBMS is used
+      def postgresql_version
+        postgresql? ? ActiveRecord::Base.connection.send(:postgresql_version) : nil
+      end
+
+      # Returns true if the database is a PostgreSQL >=9.0 database with the unaccent extension installed
+      def postgresql_unaccent?
+        if postgresql?
+          return @postgresql_unaccent unless @postgresql_unaccent.nil?
+          begin
+            sql = "SELECT name FROM pg_available_extensions WHERE installed_version IS NOT NULL and name = 'unaccent'"
+            @postgresql_unaccent = postgresql_version >= 90000 && ActiveRecord::Base.connection.select_value(sql).present?
+          rescue
+            false
+          end
+        else
+          false
+        end
+      end
+
+      # Resets database information
+      def reset
+        @postgresql_unaccent = nil
+      end
+    end
+  end
+end
diff --git a/test/test_helper.rb b/test/test_helper.rb
index 1762de868..63159ad9a 100644
--- a/test/test_helper.rb
+++ b/test/test_helper.rb
@@ -169,6 +169,10 @@ class ActiveSupport::TestCase
     ActiveRecord::Base.connection.adapter_name =~ /mysql/i
   end
 
+  def postgresql?
+    ActiveRecord::Base.connection.adapter_name =~ /postgresql/i
+  end
+
   def assert_save(object)
     saved = object.save
     message = "#{object.class} could not be saved"
diff --git a/test/unit/search_test.rb b/test/unit/search_test.rb
index 083a8f355..bd9477025 100644
--- a/test/unit/search_test.rb
+++ b/test/unit/search_test.rb
@@ -171,6 +171,25 @@ class SearchTest < ActiveSupport::TestCase
     end
   end
 
+  def test_search_should_be_case_and_accent_insensitive_with_postgresql_and_noaccent_extension
+    if postgresql?
+      skip unless Redmine::Database.postgresql_version >= 90000
+      # Extension will be rollbacked with the test transaction
+      ActiveRecord::Base.connection.execute("CREATE EXTENSION IF NOT EXISTS unaccent")
+      Redmine::Database.reset
+      assert Redmine::Database.postgresql_unaccent?
+
+      issue1 = Issue.generate!(:subject => "OO")
+      issue2 = Issue.generate!(:subject => "oo")
+  
+      r = Issue.search_results('ÖÖ')
+      assert_include issue1, r
+      assert_include issue2, r
+    end
+  ensure
+    Redmine::Database.reset
+  end
+
   private
 
   def remove_permission(role, permission)
-- 
2.39.5