Generic Ruby solution for SQLite3 "LIKE" or PostgreSQL "ILIKE"?

I am using SQLite3 for development and PostgreSQL for deployment. However, I am facing the following problem:

My simple search using SQLite3:

def self.search(search)
    if search
      find(:all, :conditions => ["style LIKE ? OR construction LIKE ?", "%#{search}%", "%#{search}%"])
    else
      find(:all)
    end
end

However, it doesn't work for PostgreSQL, and I need to replace the LIKE for ILIKE to solve the problem:

def self.search(search)
    if search
      find(:all, :conditions => ["style ILIKE ? OR construction ILIKE ?", "%#{search}%", "%#{search}%"])
    else
      find(:all)
    end
end

Is there a "Ruby way" to do these searches across any database?

EDIT - based on your answers I don't believe I will find a generic Ruby solution for that.

I have followed the Ruby on Rails Tutorial: Learn Rails by Example - by Michael Hartl, where the final Gemfile shows both databases... well, disappointing...

Solution 1:

The root of the problem lies in here:

I am using SQLite3 for development and PostgreSQL for deployment.

That's a bad idea™. You will keep running into incompatibilities - or worse: not realize some until damage is done.
Use the same RDBMS (PostgreSQL) for development and production and save yourself the pointless trouble.

While you are stuck with your unfortunate setup, there is a simple fix:

lower(style) LIKE lower(?)

Works on both platforms alike.

• You can drop the right-hand lower(), if you provide a lower-case search-pattern.

• In standard SQLite lower(X) only folds ASCII letters. For more, I quote the chapter Core Functions in the SQLite manual:

  The lower(X) function returns a copy of string X with all ASCII characters converted to lower case. The default built-in lower() function works for ASCII characters only. To do case conversions on non-ASCII characters, load the ICU extension.

  Emphasis mine.

• PostgreSQL lower(X) works with UTF-8 out of the box.

As a welcome side effect, you can speed up that query in PostgreSQL with an index on the expression lower(style), which will be faster than using ILIKE and a basic index on style.

Also, since PostgreSQL 9.1 you can use a GIN or GIST index with the pg_trgm extension to speed up any LIKE and ILIKE query - trigrams are case-insensitive. Detailed instructions and links in this related answer:

• Similar UTF-8 strings for autocomplete field

Solution 2:

I think Arel is the best way to solve this problem. It is used by Rails for active record and is database independent. Your code will work the same in sqlite3 or postgres which seems to fit your situation. Using the matches method will automatically switch to an ilike in a postgres environment. example:

users=User.arel_table
User.where(users[:style].matches("%#{search}%").or(users[:construction].matches("%#{search}%")))

You can get more information from the github: https://github.com/rails/arel/