Loading sqlite extension

[han-holl]

Hi,
I am trying to load a sqlite3 extension (sqlean regexp), but did not succeed at all.
Desperately, I even tried the folowing (hoping that sequels 'require sqlite3' would be a noop), but alas.

#!/bin/ruby
require 'sqlite3'
def sqlt
db = SQLite3::Database.open 'abn.db'
db.enable_load_extension(true)
db.load_extension('./sqlite/regexp.so')
pp db.execute("select regexp_like('abcdef', 'b.d')")
end
sqlt
require "sequel"
DB = Sequel.sqlite('/data/abn/abn.db')
Items = DB[:main]
sqlt
pp Items.select{regexp_like(:naam, '^BEL')}.all

Result:
[[1]]
[[1]]
/usr/local/share/gems/gems/sqlite3-2.6.0-x86_64-linux-gnu/lib/sqlite3/statement.rb:36:in `prepare': SQLite3::SQLException: no such function: regexp_like: (Sequel::DatabaseError)

I could find no documentation or anyone trying to do the same.

Cheers,

Han Holl

[jeremyevans]

You appear to be loading an extension into a different SQLite 3 database object than the one Sequel uses. Try:

DB = Sequel.sqlite('/data/abn/abn.db',
  after_connect: proc do|db|
    db.enable_load_extension(true)
    db.load_extension('./sqlite/regexp.so')
  end
) 

[han-holl]

Well, thank you.
I guess I should have phrased my question: how do I get at the underlying database?
From the description of after_connect:
"A callable object called after each new connection is made, with the connection object (and server argument if the callable accepts 2 arguments), useful for customizations that you want to apply to all connections (nil by default)."
I would never have guessed that that was what I was after. Not without an example like you gave here.

Thank you again, and thank you for sequel.

Han Holl

[jeremyevans]

By "underlying database", if you mean the SQLite3::Database object, that is the "new connection" referred to in the after_connect documentation.

Outside of an after_connect call, the connection object is also yielded by Database#synchronize. However, due to Sequel's use of a connection pool, the only safe place to modify connection-specific state such as loading an SQLite extension is inside an after_connect callback (which is called before the connection is added to the pool).