[SOLVED] SQLite3::SQLException: no such function: REGEXP rails rspec

SQLite3::SQLException: no such function: REGEXP rails rspec

In my project, I have different time slices. These time slices have names such as "00-04", "04-08", "08-12", "12-16", "16-20" and "20-24" and more. I wanted to get all the time slices objects with aforementioned names. So, I programmed following:

time_slice_names = ["00-04", "04-08", "08-12", "12-16", "16-20", "20-24"]
time_slices = TimeSlice.where('name REGEXP ?', time_slice_names.join("|"))

time_slices gives me correct objects in my development mode. However, when I run the test, I have the errors as:

 ActiveRecord::StatementInvalid:
 SQLite3::SQLException: no such function: REGEXP: SELECT "time_slices".* FROM "time_slices"  WHERE (name REGEXP '00-04|04-08|08-12|12-16|16-20|20-24')

My database.yml is as follows:

  development:
    adapter: mysql2
    database: development 
    encoding: utf8
    pool: 5
    timeout: 5000

  test:
    adapter: sqlite3
    database: db/test.sqlite3
    pool: 5
    timeout: 5000

I am aware that my test environment uses sqlite3 whereas my development environment uses mysql2. Is there anyway where I can perform the above mentioned query on both mysql2 and sqlite3?

Solution

So according to different answers in How to turn on REGEXP in SQLite3 and Rails 3.1?, regexp() is not by default defined in Sqlite3. You have to install a native extension which adds this functionality to Sqlite3.

However, your query can be done without using Regular expressions.

time_slice_names = ["00-04", "04-08", "08-12", "12-16", "16-20", "20-24"]
time_slices = TimeSlice.where(name: time_slice_names)

The internal database adapter (mysql or sqlite3) should understand this and convert it to supporting where field in (x, y, z) query.

More information available ActiveRecord where field = ? array of possible values