Case insensitive like (ilike) in Datamapper with Postgresql

Question

We are using Datamapper in a Sinatra application and would like to use case insensitive like that works on both Sqlite (locally in development) and Postgresql (on Heroku in production).

We have statements like

TreeItem.all(:name.like =>"%#{term}%",:unique => true,:limit => 20)

If termis "BERL" we get the suggestion "BERLIN" from both the Sqlite and Postgresql backends. However if termis "Berl" we only get that result from Sqlite and not Postgresql.

I guess this has to do with the fact that both dm-postgres-adapter and dm-sqlite-adapter outputting a LIKE in the resulting SQL query. Since Postgresql has a case sensitive LIKE we get this (for us unwanted) behavior.

Is there a way to get case insensitive like in Datamapper without resorting to use a raw SQL query to the adapter or patching the adapter to use ILIKEinstead of LIKE?

I could of course use something in between, such as:

TreeItem.all(:conditions => ["name LIKE ?","%#{term}%"],:unique => true,:limit => 20)

but then we would be tied to the use of Postgresql within our own code and not just as a configuration for the adapter.

Answer 1

By writing my own data object adapter that overrides the like_operator method I managed to get Postgres' case insensitive ILIKE.

require 'do_postgres'
require 'dm-do-adapter'

module DataMapper
  module Adapters

    class PostgresAdapter < DataObjectsAdapter

      module SQL #:nodoc:
        private

        # @api private
        def supports_returning?
          true
        end

        def like_operator(operand)
          'ILIKE'
        end
      end

      include SQL

    end

    const_added(:PostgresAdapter)

  end
end

Eventually I however decided to port the application in question to use a document database.

Answer 2

For other people who happen to use datamapper wanting support for ilike as well as 'similar to' in PostgreSQL: https://gist.github.com/Speljohan/5124955

Just drop that in your project, and then to use it, see these examples:

Model.all(:column.ilike => '%foo%')
Model.all(:column.similar => '(%foo%)|(%bar%)')