Ahhhhh. Ok. Yes that's good. Thanks a lot. Will be waiting for the 3.2. Thanks again -- Panayotis
On Oct 17, 11:32 pm, John Mileham <[email protected]> wrote: > I suppose I should have said "by default." Apologies for the flame bait. > > On a more useful note, master appears to have already changed this behavior > for 3.2: > > https://github.com/rails/rails/commit/c90e5ce779dbf9bd0ee53b68aee9fde... > > There's no "let the DB decide my case sensitivity" option, but you at least > get consistent behavior across databases, and you get to use indexes if you > make the right decision when defining your field's collation up front. > > -john > > On Mon, Oct 17, 2011 at 2:04 PM, Panayotis Matsinopoulos < > > > > > > > > [email protected]> wrote: > > Let us not compare RDBMS in this thread, because it is irrelevant. > > Almost all the databases allow you to define the COLLATION at creation > > time. > > (except ORACLE which has core problem to support this, as far as I > > know. I may be wrong though). > > PostgreSQL does, MySQL does, MS SQL Server does...to name a few. > > > So it is not that X RDBMS does this and Y RDBMS does the other. > > Hence, your statement "MySQL is the only database ....that does...." > > is not > > correct. If somebody sets the COLLATION at database creation to be CI > > (Case Insensitive) > > then `select .....where ....'Bla' = 'bla';` will return true. If the > > creator of the database > > sets the COLLATION to be CS (Case Sensitive), then even MySQL will > > return false. > > > ALL modern database management systems do that. And they have VERY > > GOOD reasons > > for doing that (which is out of the scope of this thread). > > > What I am trying to say here is that Rails/ActiveRecord SHOULD leave > > this responsibility > > (uniqueness validation with case or not sensitivity) to the db level, > > or AT LEAST let the user of > > ActiveRecord decide whether to use this ActiveRecord:uniqueness > > validation feature or not. > > Currently Rails OBLIGES the developer to USE IT (when > > using :uniqueness validator), > > by either setting this value to TRUE (the default value > > of :case_sensitive option) or FALSE. > > There should be another one extra value to this option, something like > > "IGNORE" or "DB" or something > > that will pass this responsibility to the database level (or db > > adapter level). > > > In summary, > > > *) I find it a serious design flaw that this is missing. > > *) Why the same feature (:case_sensitive => true/false) is not present > > on finders? BTW, It is correct that it does > > not. > > > All the other technical details to overcome this shortcoming are just > > workarounds, the better of > > which, according to my opinion is just to not use it and write your > > own :uniqueness validator for > > string column types. > > > -- Panayotis > > > On Oct 17, 6:56 pm, John Mileham <[email protected]> wrote: > > > MySQL is the only database I'm aware of for which `select 'Bla' = 'bla';` > > > returns true. So while it looks odd from a MySQL user's perspective to > > > perform LOWER on both sides, it's the correct way to do case insensitive > > > matching on a SQL92 database. Also, on databases like PostgreSQL and > > Oracle > > > at least, you can use functional indexes (e.g. `CREATE INDEX > > > users_lower_email_idx ON users (LOWER(email))`) to make these lookups > > fast. > > > > For your case, you should probably just not use the `:case_insensitive` > > > flag, and know that MySQL is going to compare insensitively anyway. Even > > > though aesthetically I'd like to see the Rails MySQL adapter behave > > > consistently with the other databases (be case sensitive by default), I > > > think it might be quite a rabbithole if Rails tried to force MySQL to do > > > string comparison case sensitively because of MySQL's lack of support for > > > functional indexes. From a quick browse, there appears to be no way to > > > perform indexed string equality lookups on the same column both case > > > sensitively and insensitively -- you'd have to create a pair of columns, > > one > > > with case sensitive collation, the other without, in order to efficiently > > > look up the content both ways. I'm not a MySQL expert though. > > > > -john > > > -- > > You received this message because you are subscribed to the Google Groups > > "Ruby on Rails: Core" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]. > > For more options, visit this group at > >http://groups.google.com/group/rubyonrails-core?hl=en. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
