Rails Lighthouse ticket #2503

2503-add_case_sensitive_db.diff

From e518a6ca32b506a76d68614312f803bcb55296da Mon Sep 17 00:00:00 2001
From: Phil Ross <[email protected]>
Date: Sun, 31 May 2009 15:13:10 +0100
Subject: [PATCH] Added a :case_sensitive => :db option to validates_uniqueness_of, which uses the database's case-sensitivity mode for comparisons.

---
 .../lib/active_record/validations/uniqueness.rb    |   11 +++++++++--
 .../validations/uniqueness_validation_test.rb      |   18 ++++++++++++++++++
 2 files changed, 27 insertions(+), 2 deletions(-)

diff --git a/activerecord/lib/active_record/validations/uniqueness.rb b/activerecord/lib/active_record/validations/uniqueness.rb
index edec4e9..7ddab26 100644
--- a/activerecord/lib/active_record/validations/uniqueness.rb
+++ b/activerecord/lib/active_record/validations/uniqueness.rb
@@ -21,7 +21,9 @@ module ActiveRecord
       # Configuration options:
       # * <tt>:message</tt> - Specifies a custom error message (default is: "has already been taken").
       # * <tt>:scope</tt> - One or more columns by which to limit the scope of the uniqueness constraint.
-      # * <tt>:case_sensitive</tt> - Looks for an exact match. Ignored by non-text columns (+true+ by default).
+      # * <tt>:case_sensitive</tt> - Set to true to look for an exact match or false to look for a
+      #   case insensitive match. Can also be set to :db to use the databases case-sensitivity mode for the column.
+      #   Ignored by non-text columns (+true+ by default).
       # * <tt>:allow_nil</tt> - If set to true, skips this validation if the attribute is +nil+ (default is +false+).
       # * <tt>:allow_blank</tt> - If set to true, skips this validation if the attribute is blank (default is +false+).
       # * <tt>:if</tt> - Specifies a method, proc or string to call to determine if the validation should
@@ -118,7 +120,12 @@ module ActiveRecord
           if value.nil?
             comparison_operator = "IS ?"
           elsif column.text?
-            comparison_operator = "#{connection.case_sensitive_equality_operator} ?"
+            if configuration[:case_sensitive] == :db
+              comparison_operator = "= ?"
+            else
+              comparison_operator = "#{connection.case_sensitive_equality_operator} ?"
+            end
+            
             value = column.limit ? value.to_s[0, column.limit] : value.to_s
           else
             comparison_operator = "= ?"
diff --git a/activerecord/test/cases/validations/uniqueness_validation_test.rb b/activerecord/test/cases/validations/uniqueness_validation_test.rb
index 961db51..7b8720e 100644
--- a/activerecord/test/cases/validations/uniqueness_validation_test.rb
+++ b/activerecord/test/cases/validations/uniqueness_validation_test.rb
@@ -170,6 +170,24 @@ class UniquenessValidationTest < ActiveRecord::TestCase
     end
   end
 
+  def test_validate_db_case_sensitive_uniqueness
+    Topic.validates_uniqueness_of(:title, :case_sensitive => :db, :allow_nil => true)
+
+    t = Topic.new(:title => "I'm unique!")
+    assert t.save, "Should save t"
+
+    db_case_insensitive = Topic.find_all_by_id_and_title(t.id, "I'm UNIQUE!").any?
+    
+    t2 = Topic.new(:title => db_case_insensitive ? "I'm UNIQUE!" : "I'm unique!")
+    assert !t2.valid?, "Shouldn't be valid"
+    assert !t2.save, "Shouldn't save t2 as unique"
+    assert t2.errors[:title].any?, "Should be errors for title"
+    assert_equal ["has already been taken"], t2.errors[:title]
+    
+    t3 = Topic.new(:title => db_case_insensitive ? "I'm truly UNIQUE!" : "I'm UNIQUE!")
+    assert t3.save, "Should save t3"
+  end
+  
   def test_validate_case_sensitive_uniqueness
     Topic.validates_uniqueness_of(:title, :case_sensitive => true, :allow_nil => true)
 
-- 
1.5.6.5

LH2503-add_db_to_validates_uniqueness_of_2_3_stable.diff

From 7ff1f58a75bb8c627690876ea3e04651906837f8 Mon Sep 17 00:00:00 2001
From: Elad Meidar <[email protected]>
Date: Thu, 13 Aug 2009 17:09:37 -0400
Subject: [PATCH] LH2503 - a working patch to 2-3-stable, add :case_sensitive => :db to validates_uniqueness_of

---
 activerecord/lib/active_record/validations.rb |   10 ++++++++--
 activerecord/test/cases/validations_test.rb   |   18 ++++++++++++++++++
 2 files changed, 26 insertions(+), 2 deletions(-)

diff --git a/activerecord/lib/active_record/validations.rb b/activerecord/lib/active_record/validations.rb
index 7a7d0ab..df8af9c 100644
--- a/activerecord/lib/active_record/validations.rb
+++ b/activerecord/lib/active_record/validations.rb
@@ -628,7 +628,9 @@ module ActiveRecord
       # Configuration options:
       # * <tt>:message</tt> - Specifies a custom error message (default is: "has already been taken").
       # * <tt>:scope</tt> - One or more columns by which to limit the scope of the uniqueness constraint.
-      # * <tt>:case_sensitive</tt> - Looks for an exact match. Ignored by non-text columns (+true+ by default).
+      # * <tt>:case_sensitive</tt> - Set to true to look for an exact match or false to look for a
+      #   case insensitive match. Can also be set to :db to use the databases case-sensitivity mode for the column.
+      #   Ignored by non-text columns (+true+ by default).
       # * <tt>:allow_nil</tt> - If set to true, skips this validation if the attribute is +nil+ (default is +false+).
       # * <tt>:allow_blank</tt> - If set to true, skips this validation if the attribute is blank (default is +false+).
       # * <tt>:if</tt> - Specifies a method, proc or string to call to determine if the validation should
@@ -725,7 +727,11 @@ module ActiveRecord
           if value.nil?
             comparison_operator = "IS ?"
           elsif column.text?
-            comparison_operator = "#{connection.case_sensitive_equality_operator} ?"
+            if configuration[:case_sensitive] == :db
+              comparison_operator = "= ?"
+            else
+              comparison_operator = "#{connection.case_sensitive_equality_operator} ?"
+            end
             value = column.limit ? value.to_s.mb_chars[0, column.limit] : value.to_s
           else
             comparison_operator = "= ?"
diff --git a/activerecord/test/cases/validations_test.rb b/activerecord/test/cases/validations_test.rb
index 4c023c3..a7bb249 100644
--- a/activerecord/test/cases/validations_test.rb
+++ b/activerecord/test/cases/validations_test.rb
@@ -496,6 +496,24 @@ class ValidationsTest < ActiveRecord::TestCase
     assert_not_equal "has already been taken", t3.errors.on(:title)
   end
 
+  def test_validate_db_case_sensitive_uniqueness
+    Topic.validates_uniqueness_of(:title, :case_sensitive => :db, :allow_nil => true)
+  
+    t = Topic.new(:title => "I'm unique!")
+    assert t.save, "Should save t"
+  
+    db_case_insensitive = Topic.find_all_by_id_and_title(t.id, "I'm UNIQUE!").any?
+    
+    t2 = Topic.new(:title => db_case_insensitive ? "I'm UNIQUE!" : "I'm unique!")
+    assert !t2.valid?, "Shouldn't be valid"
+    assert !t2.save, "Shouldn't save t2 as unique"
+    assert t2.errors[:title].any?, "Should be errors for title"
+    assert_equal "has already been taken", t2.errors[:title]
+      
+    t3 = Topic.new(:title => db_case_insensitive ? "I'm truly UNIQUE!" : "I'm UNIQUE!")
+    assert t3.save, "Should save t3"
+  end
+  
   def test_validate_case_sensitive_uniqueness_with_attribute_passed_as_integer
     Topic.validates_uniqueness_of(:title, :case_sensitve => true)
     t = Topic.create!('title' => 101)
-- 
1.6.0.2

mysql-case-insensitive-uniqueness.diff

From dd6bd6ed422f306945e1a528052c9e5d0861adb7 Mon Sep 17 00:00:00 2001
From: Grant Hollingworth <[email protected]>
Date: Thu, 2 Sep 2010 17:07:48 -0400
Subject: [PATCH] use bare "=" instead of lower() for case insensitive comparison in mysql

---
 .../abstract/database_statements.rb                |    4 ++++
 .../connection_adapters/mysql_adapter.rb           |    4 ++++
 .../lib/active_record/validations/uniqueness.rb    |    2 +-
 3 files changed, 9 insertions(+), 1 deletions(-)

diff --git a/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb b/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb
index 01e53b4..b3a118d 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb
@@ -272,6 +272,10 @@ module ActiveRecord
         "="
       end
 
+      def case_insensitive_equality_comparison(sql_attribute)
+        "LOWER(#{sql_attribute}) = LOWER(?)"
+      end
+
       def limited_update_conditions(where_sql, quoted_table_name, quoted_primary_key)
         "WHERE #{quoted_primary_key} IN (SELECT #{quoted_primary_key} FROM #{quoted_table_name} #{where_sql})"
       end
diff --git a/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb
index 47acf0b..fe2a54b 100644
--- a/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb
@@ -649,6 +649,10 @@ module ActiveRecord
         "= BINARY"
       end
 
+      def case_insensitive_equality_comparison(sql_attribute)
+        "#{sql_attribute} = ?"
+      end
+
       def limited_update_conditions(where_sql, quoted_table_name, quoted_primary_key)
         where_sql
       end
diff --git a/activerecord/lib/active_record/validations/uniqueness.rb b/activerecord/lib/active_record/validations/uniqueness.rb
index 853808e..778db20 100644
--- a/activerecord/lib/active_record/validations/uniqueness.rb
+++ b/activerecord/lib/active_record/validations/uniqueness.rb
@@ -72,7 +72,7 @@ module ActiveRecord
         if value.nil? || (options[:case_sensitive] || !column.text?)
           sql = "#{sql_attribute} #{operator}"
         else
-          sql = "LOWER(#{sql_attribute}) = LOWER(?)"
+          sql = klass.connection.case_insensitive_equality_comparison(sql_attribute)
         end
 
         [sql, [value]]
-- 
1.7.3.2

validates_uniqueness_with_db_sensitive.diff

From deca62c0389e1c01bd3e94d370eaeee354e75a59 Mon Sep 17 00:00:00 2001
From: Hugo Peixoto <theorem@Nayru.(none)>
Date: Sun, 9 Aug 2009 00:31:19 +0100
Subject: [PATCH] Added a :case_sensitive => :db option to validates_uniqueness_of, which uses the database's case-sensitivity mode for comparisons.
 Ported the patch submitted by Phil Ross <[email protected]>

---
 activerecord/lib/active_record/validations.rb |   11 +++++++++--
 activerecord/test/cases/validations_test.rb   |   18 ++++++++++++++++++
 2 files changed, 27 insertions(+), 2 deletions(-)

diff --git a/activerecord/lib/active_record/validations.rb b/activerecord/lib/active_record/validations.rb
index d2d12b8..17eb597 100644
--- a/activerecord/lib/active_record/validations.rb
+++ b/activerecord/lib/active_record/validations.rb
@@ -628,7 +628,9 @@ module ActiveRecord
       # Configuration options:
       # * <tt>:message</tt> - Specifies a custom error message (default is: "has already been taken").
       # * <tt>:scope</tt> - One or more columns by which to limit the scope of the uniqueness constraint.
-      # * <tt>:case_sensitive</tt> - Looks for an exact match. Ignored by non-text columns (+true+ by default).
+      # * <tt>:case_sensitive</tt> - Set to true to look for an exact match or false to look for a
+      #   case insensitive match. Can also be set to :db to use the databases case-sensitivity mode for the column.
+      #   Ignored by non-text columns (+true+ by default).
       # * <tt>:allow_nil</tt> - If set to true, skips this validation if the attribute is +nil+ (default is +false+).
       # * <tt>:allow_blank</tt> - If set to true, skips this validation if the attribute is blank (default is +false+).
       # * <tt>:if</tt> - Specifies a method, proc or string to call to determine if the validation should
@@ -725,7 +727,12 @@ module ActiveRecord
           if value.nil?
             comparison_operator = "IS ?"
           elsif column.text?
-            comparison_operator = "#{connection.case_sensitive_equality_operator} ?"
+            if configuration[:case_sensitive] == :db
+              comparison_operator = "= ?"
+            else
+              comparison_operator = "#{connection.case_sensitive_equality_operator} ?"
+            end
+            
             value = column.limit ? value.to_s[0, column.limit] : value.to_s
           else
             comparison_operator = "= ?"
diff --git a/activerecord/test/cases/validations_test.rb b/activerecord/test/cases/validations_test.rb
index c20f5ae..e819b6a 100644
--- a/activerecord/test/cases/validations_test.rb
+++ b/activerecord/test/cases/validations_test.rb
@@ -496,6 +496,24 @@ class ValidationsTest < ActiveRecord::TestCase
     assert_not_equal "has already been taken", t3.errors.on(:title)
   end
 
+  def test_validate_db_case_sensitive_uniqueness
+    Topic.validates_uniqueness_of(:title, :case_sensitive => :db, :allow_nil => true)
+
+    t = Topic.new(:title => "I'm unique!")
+    assert t.save, "Should save t"
+
+    db_case_insensitive = Topic.find_all_by_id_and_title(t.id, "I'm UNIQUE!").any?
+    
+    t2 = Topic.new(:title => db_case_insensitive ? "I'm UNIQUE!" : "I'm unique!")
+    assert !t2.valid?, "Shouldn't be valid"
+    assert !t2.save, "Shouldn't save t2 as unique"
+    assert t2.errors[:title].any?, "Should be errors for title"
+    assert_equal "has already been taken", t2.errors[:title]
+    
+    t3 = Topic.new(:title => db_case_insensitive ? "I'm truly UNIQUE!" : "I'm UNIQUE!")
+    assert t3.save, "Should save t3"
+  end
+
   def test_validate_case_sensitive_uniqueness_with_attribute_passed_as_integer
     Topic.validates_uniqueness_of(:title, :case_sensitve => true)
     t = Topic.create!('title' => 101)
-- 
1.6.3.3