Sunday, March 15, 2009

Implementing REGEXP in Sqlite3

I am implementing a rails application that required searching using a regular expression. I am using MySQL for the production database, but SQLite3 for the test database. Now SQLite3 supports the REGEXP SQL operator, however it is left to the user to implement the function. I found a post by Rolando Abarca where he attempted to implement regexp in Ruby. I adapted that and also created a rails initializer (config/initializers/sqlite3_regexp.rb):



require 'active_record/connection_adapters/sqlite3_adapter'

class ActiveRecord::ConnectionAdapters::SQLite3Adapter
def initialize(db, logger)
super
db.create_function('regexp', 2, :numeric) do |func, pattern, expression|
regexp = Regexp.new(pattern.to_s, Regexp::IGNORECASE)
if expression.to_s.match(regexp)
func.result = 1
else
func.result = 0
end
end
end
end


This allowed me to implement a named_scope to search for a set of comma (or semicolon) separated names:



class Subject < ActiveRecord::Base
named_scope :with_names, lambda {|names|
names = names.strip.split(/ *[,;] */).join('|')
if !names.blank?
{:conditions => ["name regexp ?", names]}
else
{}
end
}
end


Which could then be called like:



subjects = Subject.with_names("math, english")

See this post on my site Veit Consulting.