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

Drop the right-hand lower() if you provide a lower-case search-pattern.

In standard SQLite lower(X) only folds ASCII letters. Quoting 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.

Bold 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 - trigram indexes are case-insensitive. Details:

• Similar UTF-8 strings for autocomplete field

Answer 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/

Answer 3

No, there is no "ruby way" to search a database -- Ruby on Rails (specifically ActiveRecord) contains helper methods for performing CRUD operations on RDBs supported by ActiveRecord but there is not much better of a way to search using LIKE then the examples you provided.

The section of the Rails doc related to this discussion would be ActiveRecord::FinderMethods.

As a side note, instead of doing find(:all) you can just do all.

The Rails docs use the same syntax as use for doing LIKE statements, i.e.:

Person.exists?(['name LIKE ?', "%#{query}%"])

Using the method above is perfectly safe.

The reason why statements like the one below are unsafe is because the where string is passed directly into your database query without any sanitization, which leaves your database open to exploitation (i.e. a simple apostrophe in params[:first_name] could mess up your entire query and leave your database vulnerable -- specifically SQL injection). In the example above, ActiveRecord can sanitize the parameters that you are passing into the query.

Client.where("first_name LIKE '%#{params[:first_name]}%'")

Answer 4

Unfortunately I don't think you're going to find a good solution to this. The only other syntax available to you, instead of :conditions, is to use a .where clause:

where(["style ILIKE ? OR construction ILIKE ?", "%#{search}%", "%#{search}%"])

Unfortunately, as you've probably realized, this alternate syntax will run into the exact same problem. This is just one of the annoyances you'll encounter in having a substantially different development environment from your production environment -- there are also other fairly substantial differences between SQLite and PostgresSQL. I would recommend just installing Postgres on your development machine and using that. It'll make developing a lot easier, and make your code a lot cleaner.

Answer 5

While it's not a good practice to use different databases in production and development, It still is a good case to use the squeel gem: https://github.com/ernie/squeel/

With it, you can write your queries with a DSL that is easy and arguably much cleaner and readable than raw sql, and the gem will handle it's translation to SQL specific to the RDBMS used.

Ryan Bates has a good video about it: http://railscasts.com/episodes/354-squeel

Answer 6

I was facing the same problem once, here is my solution:

I wrote a little lib which make it possible to use the function for each database:

class AdapterSpecific

  class << self
    def like_case_insensitive
      case ActiveRecord::Base.connection.adapter_name
      when 'PostgreSQL'
        'ILIKE'
      else
        'LIKE'
      end
    end    

    def random
      #something
    end
  end

To use it in your model:

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

Since written, I migrated the deployment database to Postgres as this is a much better setup for several reasons (see other answers).

You may also think to use full text search for Postgres which will make your text search much more efficient, see texticle for a basic implementation or pg_search if you need more customization.

Answer 7

Searching using LIKE can be painful on the database. Certainly it wont use an index which can be quite cost prohibitive.

A longer answer: I would recommend using Postgres in development (ditching sqlite3) and then having a Full-Text index on all your searchable fields style, construction via Postgres' tsvector type.

Full-text searches in Postgres when using an index are very fast.