2

Does Active Record provide a way to generate SQL that forces a text search to be case-sensitive?

Ruby-on-Rails generators instructed to create a string-type column produce a simple VARCHAR(255) field, in a mysql database. It turns out that queries on such columns are case insensitive by default.

Thus, an Active Record search such as:

Secret.where(token: 'abcde')

will match records with tokens abcde, ABcdE, etc.

Without changing the underlying database column (e.g. specifying a utf8_bin collation) searches can be made case sensitive by explicitly tweaking the where clause:

Secret.where('binary token = ?', 'abcde')

However, this is database-specific, and I am wondering if Active Record has an idiom to accomplish the same for any database. Just as an example, something resembling the where.not construct:

Secret.where.binary(token: 'abcde')

Wouldn't this be a common enough need?

Giuseppe
  • 4,776
  • 4
  • 36
  • 35
  • 2
    `Secret.where('binary token LIKE ? ', '%abcde%')` how about this? – Aman Sep 18 '18 at 07:27
  • @Gabbar, this is similar to my own version, in that you are actually writing database-specific SQL. My question is about whether an Active Record abstraction exists. – Giuseppe Sep 18 '18 at 07:30
  • I'm not getting you exactly , but so far as i understand Active Record also contact to database in return of sql query to ORM object. – Aman Sep 18 '18 at 07:35
  • Active Record searches are NOT case-insensitive by default. Moreover, AR is not about "case sensitivity", it is about SQL constructing and mapping result to the objects. I guess, either you DB settings or `token` column type is the reason why SQL query, generated by `Secret.where(token: 'abcde')`, makes case-insensitive search. – Pavel Mikhailyuk Sep 18 '18 at 08:32
  • 1
    Duplicate of: https://stackoverflow.com/questions/2220423/case-insensitive-search-in-rails-model ? – Austio Sep 18 '18 at 08:56
  • @PavelMikhailyuk, you are right, the question needs editing. But first, at least when the RoR-generated mysql column is a `varchar(255)`, part of an `InnoDB`, `utf8mb4` table, a search like `Secret.where(token: 'abcde')` is indeed case-insensitive. Agree? It is at least conceivable that AR might provide a way to construct (db-specific) SQL that forces the query to be case-sensitive. Am I still missing something? – Giuseppe Sep 19 '18 at 08:11
  • @Austio, my question is related, but I think quite distinct from the one you refer to. – Giuseppe Sep 19 '18 at 08:21
  • @PavelMikhailyuk, please see my extensive edits. Thanks! – Giuseppe Sep 19 '18 at 08:53
  • `binary` is MySQL-specific, other DBs use different syntax for type casting. And "other DBs" use case-sensitive `collation` by default. So, probably, Rails team does not consider it as "common enough need". Moreover, I haven't find even `COLLATE` usage in ActiveRecord sources(except for schema definition), despite `COLLATE` is much more _common_ across DB, than `binary`. – Pavel Mikhailyuk Sep 20 '18 at 19:02
  • @PavelMikhailyuk, I gather from your comments that the brief answer to my question is NO. If so, would you go ahead and post a response? I'd like to see this question resolved. – Giuseppe Sep 26 '18 at 11:18

1 Answers1

1

In short: there is NO ActiveRecord idiom for case-sensitive search.

For case-insensitive search you can try to use this. It still works, but source code was changed a bit. So, use it on your own risk.

In general, case sensitivity is subset of the Collation idiom. And different DBMS use very different default collations for string(text) data types, including default case sensitivity. Detailed description for MySQL.

There is a sql operator COLLATE which is very common across DBMS(but seems still is not in SQL Standard). But ActiveRecord sources show it only in schema creation code. Neither ActiveRecord, nor Arel gems do not use COLLATE in where search(sad).

Note: Please, don't miss the database tag(mysql etc) in a Question. There are many Questions and Answers on SO without the tags(or with sql one), which are completely irrelevant for the most of DBMSs except author's one.

Pavel Mikhailyuk
  • 2,497
  • 5
  • 15
  • I had initially avoided the `mysql` tag on purpose because the essence of my question was NOT db-specific. I was seeking a functionality that could have been provided by AR, such that one would not have to worry about the specific database syntax. Anyhow, tag added now. – Giuseppe Sep 26 '18 at 14:05
  • I left the note not for you only: I've really spent "a tons" of time on SO, understanding what DBMS Q/A was related to. Hint: DBMS(especially postgresql :) ) tag also triggers some good SO members to answer even they don't know Rails. – Pavel Mikhailyuk Sep 26 '18 at 18:59