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?

I find it better to use the same DB adapter for dev/test/prod. However, check this out (ignore the accepted answer). — ndnenkov
– ndnenkov, Commented Oct 13, 2016 at 14:49
@sadiksha Me too don't have much knowledge on the SQLite part. So if it works with MySQL and not SQLite, n the error clearly says that SQLite doesn't have implemented that regexp function. So better use the same Db for all envs as ndn suggested. — millisami
– millisami, Commented Oct 14, 2016 at 3:22
Thanks you and @ndn, for this case I used the query for handling arrays. I will definitely consider your suggestion. — Sadiksha Gautam
– Sadiksha Gautam, Commented Oct 17, 2016 at 14:47
@SadikshaGautam it would be best if you post an answer with your current solution and accept it for future users to find. — ndnenkov
– ndnenkov, Commented Oct 17, 2016 at 14:53

Community · Accepted Answer · 2017-05-23 12:00:43Z

2

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

edited May 23, 2017 at 12:00

CommunityBot

11 silver badge

answered Oct 18, 2016 at 10:45

Bibek Shrestha

35.4k7 gold badges35 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related