How to Turn on Regexp in SQLite3 and Rails 3.1

How to turn on REGEXP in SQLite3 and Rails 3.1?

I ran into the same issue. I took the code used in the resolution, ported it to work with Rails 3+ and made a gem for easier use. I hope this helps.

https://github.com/sei-mi/sqlite3_ar_regexp

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

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

How do I get sqlite3 to do regexp in Tcl

It's actually pretty simple to enable regular expression processing. All you have to do (assuming that db is your connection handle) is use the function method like this:

db function regexp -deterministic {regexp --}

This tells SQLite to create the function, that it is deterministic (as regular expression matching most certainly is) and that it should work by passing the arguments to regexp -- (the -- stops REs that begin with - from causing problems).

See for yourself from this session log:

% package require sqlite3
3.8.10.2
% sqlite3 db :memory:
% db eval {select 1 where 'abc' regexp 'b'}
no such function: regexp
% db function regexp -deterministic {regexp --}
% db eval {select 1 where 'abc' regexp 'b'}
1

How to search an independent word in a mySQL search?

You need to use the REGEXP facility along with "word boundary" expressions. [[:<:]] and [[:>:]]. For example

SELECT * FROM sentences WHERE sentence REGEXP '[[:<:]]fox[[:>:]]'

This will handle cases where 'fox' is preceded or followed by a comma, start/end of string, or other non-word character. A word character is defined as [_A-Za-z0-9]. Here's a link to the doc: http://dev.mysql.com/doc/refman/5.1/en/regexp.html

How to sort text in sqlite3 with specified locale?

SQLite supports integration with ICU. According to the Readme file,
sqlite/ext/icu/README.txt
the sqlite/ext/icu/ directory contains source code for the SQLite "ICU" extension, an
integration of the "International Components for Unicode" library with SQLite.

1. Features

    1.1  SQL Scalars upper() and lower()
    1.2  Unicode Aware LIKE Operator
    1.3  ICU Collation Sequences
    1.4  SQL REGEXP Operator

How to read the thumb_image column in the WhatsApp Messengers SQLite3 database?

Ok figured out how to do this (This a quick and dirty method).

Download and Install Java SDK (set Path and classpath variables accordingly)

Dump the WhatsApp database:

sqlite3 msgstore.db
.output dump.sql
.dump
.exit

Find the thumb_image column in the dump.sql file you want to read/modify. eg:
```
X'ACED0......
```
Write the above data to binary format using the command below (note: use the full data you get from your data.sql file in place of ACED000...:
```
echo 'ACED000...' | xxd -r -p > thumb.ser
```

Make the following java files using:

MediaData.java:

package com.whatsapp;

import java.io.File;
import java.io.Serializable;

public class MediaData implements Serializable {
static final long serialVersionUID = -3211751283609594L;

boolean autodownloadRetryEnabled;
int faceX;
int faceY;
long fileSize;
long progress;
boolean transcoded;
boolean transferred;
long trimFrom;
long trimTo;
File file;

public boolean get1() {
    return autodownloadRetryEnabled;
}

public int get2() {
    return faceX;
}

public int get3() {
    return faceY;
}

public long get4() {
    return fileSize;
}

public long get5() {
    return progress;
}

public boolean get6() {
    return transcoded;
}

public boolean get7() {
    return transferred;
}

public long get8() {
    return trimFrom;
}

public long get9() {
    return trimTo;
}

public File get10() {
    return file;
}
}

readdata.java:

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;

import com.whatsapp.MediaData;

public class readdata {

    public static void main(String[] args) {

        try {
            //Create Mediadata object
            MediaData md;

            //Read bianry data
            ObjectInputStream oin = new ObjectInputStream(new FileInputStream("thumb.ser"));

            //Set Mediadata object "md" to data ready from binary file using Mediadata cast
            md = (MediaData) oin.readObject();

            //Print Existing Values
            System.out.println(md.get1());
            System.out.println(md.get2());
            System.out.println(md.get3());
            System.out.println(md.get4());
            System.out.println(md.get5());
            System.out.println(md.get6());
            System.out.println(md.get7());
            System.out.println(md.get8());
            System.out.println(md.get9());
            System.out.println(md.get10());

            //Data can be modified and then written out to serialized file again:          

            //Write data to new binary file
            ObjectOutputStream oout = new ObjectOutputStream(new FileOutputStream("thumb.mod.ser"));

            oout.writeObject(md);
            oout.flush();
            oout.close();

        }

        catch (Exception exc)
        {
            exc.printStackTrace();
        }
    }    
}

Now compile java code and execute:

$ javac -d . MediaData.java && javac readdata.java && java readdata

true
422
128
58716
100
false
true
0
0
\storage\emulated\0\WhatsApp\Media\WhatsApp Images\IMG-20151111-WA0026.jpg

That should print the information in the thumb.ser file.

Note:
jdeserialize was used to determine/reverse engineer the class definition used in Mediadata.java

References:

https://jdeserialize.googlecode.com/files/jdeserialize-1.2.jar
https://code.google.com/p/jdeserialize/
https://community.oracle.com/thread/2113555
https://github.com/jberkel/whassup/blob/master/library/src/main/java/com/github/jberkel/whassup/model/Media.java
https://github.com/jberkel/whassup/blob/master/library/src/main/java/com/whatsapp/MediaData.java

Heroku and ruby on rails: Deploy works, but running app fails do to usual sqlite3 error

Notice how your production db settings uses *default and also references sqlite3? That is what is causing the error. The *default part loads in everything under the default settings which is this part

default: &default
  adapter: sqlite3
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

So you need to change the settings under production and remove that stuff.

production:
  adapter: postgresql

Make sure to push to git and the deploy to heroku so the changes take effect.

If you have migrations that need to run then you will also need to run heroku run rake db:migrate from the console to set up the DB tables, it will look for this connection even on a "hello world" page so the DB needs to be set up for the app to work (as far as I know).

How to Turn on Regexp in SQLite3 and Rails 3.1