Edit Question - KnowledgeHub

Title Body

Assume Rails 3 with MySQL DB with Case Insensitive collation What's the story: Rails allows you to validate an attribute of a Model with the "uniqueness" validator. BUT the default comparison is CASE SENSITIVE according to Rails documentation. Which means that on validation it executes SQL like the following: SELECT 1 FROM `Users` WHERE (`Users`.`email` = BINARY 'FOO@email.com') LIMIT 1 This works completely wrong for me who has a DB with CI Collation. It will consider the 'FOO@email.com' valid, even if there is another user with 'foo@email.com' already in Users table. In other words, this means, that if the user of the application tries to create a new User with email 'FOO@email.com' this would have been completely VALID (by default) for Rails and INSERT will be sent to db. If you do not happen to have unique index on e-mail then you are boomed - row will be inserted without problem. If you happen to have a unique index, then exception will be thrown. Ok. Rails says: since your DB has case insensitive collation, carry out a case insensitive uniqueness validation. How is this done? It tells that you can override the default uniqueness comparison sensitivity by setting ":case_sensitive => false" on the particular attribute validator. On validation it creates the following SQL: SELECT 1 FROM `Users` WHERE (LOWER(`Users`.`email`) = LOWER('FOO@email.com') LIMIT 1 which is a DISASTER on a database table Users that you have designed to have a unique index on the email field, because it DOES NOT USE the index, does full table scan. I now see that the LOWER functions in SQL are inserted by the UniquenessValidator of ActiveRecord (file uniqueness.rb , module ActiveRecord , module Validations class UniquenessValidator ). Here is the piece of code that does this:

Tags (comma-separated) Cancel