Implement loadable extensions

[christian.boos]

I've just seen #179, and while not strictly necessary for supporting virtual tables, I think that support for http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions would be a must too.

The idea behind this request is to be able to use http://www.sqlite.org/cvstrac/wiki?p=FtsOne one day for Trac's advanced search

According to the FTS1 page, that will probably be called FTS2 at that time ;)

[christian.boos]

enhancement, of course (damn, we should really make this option more obvious to pick)

[christian.boos]

Hm, on second thought: is there really something to do at the pysqlite level in order to be able to use FTS1, or will:

SELECT load_extension('libfts1.dll');

be enough?

If this is indeed enough, then maybe it's not worth the trouble to implement support for loaded extensions in pysqlite...

[christian.boos]

Ok, some work is nevertheless needed, as right now we have:

>>> import pysqlite2.dbapi2 as sqlite
>>> cnx = sqlite.Connection(":memory:")
>>> cnx.execute("SELECT load_extension('fts1.dll');")
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
pysqlite2.dbapi2.OperationalError: not authorized

and with attachment:enable_load_extension-r296.patch:

>>> import pysqlite2.dbapi2 as sqlite
>>> cnx = sqlite.Connection(":memory:")
>>> cnx.enable_load_extension(True) # <-- added by the patch
>>> cnx.execute("SELECT load_extension('fts1.dll');")
<pysqlite2.dbapi2.Cursor object at 0x00A48F50>

I also checked that one can reset it to False afterwards:

>>> cnx.execute("SELECT load_extension('fts1.dll');")
<pysqlite2.dbapi2.Cursor object at 0x00A48F80>
>>> cnx.enable_load_extension(False)
>>> cnx.execute("SELECT load_extension('fts1.dll');")
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
pysqlite2.dbapi2.OperationalError: not authorized
>>>

[christian.boos]

Add access to the sqlite3_enable_load_extension(sqlite3 *db, int onoff) API function introduced in SQLite 3.3.8

[christian.boos]

Oh, I just realized that #179 was about creating Python backends for virtual tables.

For simply using virtual tables provided by other modules, all what is needed is the above patch (if the module is to be loaded dynamically; if the module is statically linked to SQLite, it should even work with a unmodified version of pysqlite).

>>> import pysqlite2.dbapi2 as sqlite
>>> cnx = sqlite.Connection(":memory:")
>>> cnx.enable_load_extension(True)
>>> cnx.execute("SELECT load_extension('fts1.dll');")
...
>>> cnx.execute("create virtual table recipe using FTS1(name, ingredients);")
...
>>> c = cnx.cursor()
>>> ins = "insert into recipe (name, ingredients) values (?,?);"
>>> data  = [
... ('broccoli stew', 'broccoli peppers cheese tomatoes'),
... ('pumpkin stew', 'pumpkin onions garlic celery'),
... ('broccoli pie', 'broccoli cheese onions flour'),
... ('pumpkin pie', 'pumpkin sugar flour butter') ]
>>> c.executemany(ins, data)
...
>>> list(cnx.execute("select rowid, name, ingredients from recipe where name match 'pie';"))
[(4, u'broccoli pie', u'broccoli cheese onions flour'), (5, u'pumpkin pie', u'pumpkin sugar flour butter')]

That's great ;)