kbrock / activerecord-hash_options Goto Github PK

In many cases, the operations in sql work the same as in ruby.
Numbers comparison is very predictable, and for the most part regular expression matching is predictable as well.

Unfortunately string comparison is another matter (e.g. "cat" > "bat"). The one that looks the easiest is potentially the most complicated. Some of these aspects may come into play with regular expressions, but locally those only use equality, granted in a fuzzy way.

Case sensitivity is complicated. The actual concept may be simple or complicated, I'm undecided on that one, but determining if case sensitivity is relevant is another matter. Platforms have different defaults based upon their locale and other configuration variables. And even on a case sensitive platform, a query can be case insensitive.

I'm still a little confused whether it is locale or collation that affects the case sensitivity in string comparison. It is nice that mysql ships with a standard set of locales which makes debugging that much easier. Postgres not shipping with a locale and relying upon the operating system makes that a confusing mess.

Don't want to get into per column collation, let alone per query collation. Or the non-string data types that are focused on case sensitivity. This could really take a simple concept and make it worse than a database implementation.

It may be as simple as requiring that the ruby code run with a LC_LOCALE that is the same as the database and we use the c comparison methods that respect the locale. Not sure how that would affect readability, but I have a feeling that chasing this last 20% will make this very complicated.

Let's focus on case sensitivity first.

• Introduce a configuration parameter that allows us to say that the system should be case insensitive by default.
• Introduce an environment variable that allows us to toggle this on or off. I'm guessing the default behavior is case sensitive, but that is negotiable.
• modify the regular expression code to respect that default. there is one block of code with regular expressions in particular that hints where this would come into play.
• Modify ruby comparison to respect string comparison. May need to tweak regular expressions that come across. Not sure if a case insensitive regex needs to be converted to a case sensitive regular expression. Not sure if that is possible in a database

From here we can expand this to handle the edge cases around nuances with the database. Still not sure if those are locale based or are just different implementation based.