How do I get sqlite3 to do regexp in Tcl

Question

I'd like to include regexp in my Tcl project but when I run my code I get this error:

no such function: REGEXP

my code looks like this:

return [brain eval "select * from bad WHERE input REGEXP '^(.){0}_'"]

I can test this exact code out in the database (I'm using BD browser for SQLite to browse the database) and it works correctly:

select * from uniq WHERE input REGEXP '^(.){1}0'

20 Rows returned from: select * from uniq WHERE input REGEXP '^(.){1}0' (took 18ms)

So REGEXP will work in the browser, but not in my Tcl Script. Here's what I found so far on this issue:

1. someone else had the same problem in ruby: How to turn on REGEXP in SQLite3 and Rails 3.1?
2. Somone had the same problem in iOS and had to cretae_sqlite_function "No such function: REGEXP" in sqlite3 on iOS
3. how to write a function in sqlite: https://www.sqlite.org/c3ref/create_function.html
4. How to write a function for sqlite in Tcl: https://www.sqlite.org/tclsqlite.html
5. An example of the function I may have to write in ruby: https://github.com/sei-mi/sqlite3_ar_regexp/blob/master/lib/sqlite3_ar_regexp/extension.rb
6. REGEXP user function is not defined by default: http://www.sqlite.org/lang_expr.html#regexp
7. A PHP example of the REGEXP user-defined Function: How do I use regex in a SQLite query?

So I've come to the conclusion that I have to write some kind of function myself to get this to work, but I don't know what that function has to look like. Is it simply passing on the regular expression I make to sqlite3? or is it converting the regular expression to something else then passing it on?

Would the function look something like this?

file mkdir db
sqlite3 db ./brain/brain.sqlite -create true

db eval { create_function('regexp', 2) do |func, pattern, expression|
            func.result = expression.to_s.match(
            Regexp.new(pattern.to_s, Regexp::IGNORECASE)) ? 1 : 0
         end
         }  

Thanks for any help or advice you can offer me!

Answer 1

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