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

Question

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?

Answer 1

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