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

Question

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...

Answer 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