I'm not sure what Rob meant by "use Ruby constructs" - but if my guess is correct then I disagree, this issue has nothing to do with Ruby. The validates uniqueness of uses a DB select to see if the value in question already exists in the DB. The problem is in the query ActiveRecord itself is using.
For reasons that I can't work out, the vuo processing in AR always uses a case-sensitive comparison operator (which it gets from the adapter) when creating the SQL to search for conflicting records. In MySQL the case_sensitive_equality_operator uses the BINARY keyword which basically ignores the table collation and compares the raw character codes instead (hence making the comparison case sensitive, but also accent sensitive). Now vuo gets around the case issue by lower-casing both arguments, but the accent sensitivity is still there because of the BINARY keyword in the comparison. This means that there's a very real potential for vuo to see two values as different but for them to be considered the same (base on the table collation) for all other queries and for constraint checking. The index itself correctly barfs on the collation-specific constraint violation. Definitely this is a bug in vuo in AR - it should not ignore the table collation. On Nov 18, 2009, at 11:44 PM, Rob Kaufman wrote: > Hi Jose, > The problem is that Rails validations use Ruby constructs to test > the values... they don't use or really know that much about the > MySQL settings. There are two ways around this issue. The first > way to use a custom validation, that is probably the right thing to > do. The second would be to override the accessor to strip out any > accent codes on assignment. Basically you'd setup your own custom > name= method. > > Good Luck, > Rob > > On Tue, Nov 3, 2009 at 20:42, jaambros <[email protected]> wrote: > > Hi all, > In the aacg_courses model I have: > validates_uniqueness_of :name, :case_sensitive=>:false > in the migration I have an index with :unique options > add_index :aacg_courses, > [:name, :language_id ],:name=>"aacg_courses_idx" , :unique => true > > when trying to save a record with a name that differs to one in the > DB by only an accent (or ñ) I get an error: > Mysql::Error: Duplicate entry 'Geografia-8-core-1' for key > 'aacg_courses_idx': INSERT INTO aacg_courses (name, > acad_area_cycle_grade_id, type_course, language_id) VALUES > ('Geografia', 8, 'core', 1) > > The DB had a record with name Geografía (note the accent on the i) and > I was trying to save one without the accent; call them G1 and G2 > It seems that validate_uniqueness_of finds G1 and G2 are diff but > MySQL finds they are the same and issues a uniquess violation. > MySQL's behavior is correct in my view as I'm using UTF8/collation > (which ignores accents) throughout in my app. > I have UTF8 in all the right places in Rails I think: > $KCODE = "UTF-8" in environment.rb, and > def configure_charsets > headers["Content-Type"] = "text/html; charset=utf-8" > ActiveRecord::Base.connection.execute 'SET NAMES UTF8' > end > in app/controllers/application_controller > > It seems that rails uniqueness is not mached to that of the DB in this > case. > Is there an elegant way of getting this right? [short of writing a > custom validation] > > Thanks in advance, Jose > > --~--~---------~--~----~------------~-------~--~----~ > SD Ruby mailing list > [email protected] > http://groups.google.com/group/sdruby > -~----------~----~----~----~------~----~------~--~--- > > > > > -- > Rob Kaufman > http://notch8.com > gtalk/jabber/email: [email protected] > p: 858-412-7749 > f: 866-477-1620 > > > -- > SD Ruby mailing list > [email protected] > http://groups.google.com/group/sdruby -- SD Ruby mailing list [email protected] http://groups.google.com/group/sdruby
