python*: Allow loading SQLite extensions

[m3drano@…]

This patch adds the flag --enable-loadable-sqlite-extensions to the Python build, which allows to load extensions in SQLite databases natively. There is more information on the subject in the Python's documentation: ​https://docs.python.org/3.4/library/sqlite3.html#f1

This setting is enabled on other OSes as default too, such as Linux distributions (Debian, Arch) at least.

This patch applies to the port python34, but we can certainly extend it to other Python versions too if it is deemed appropriate. Other option would be to make this a port variant.

[ryandesign (Ryan Carsten Schmidt)]

If there is no downside to enabling this, it should be on by default, not a variant. If there is no reason why it cannot be done to other python versions too, that should be done also; unnecessary differences between versions of python ports should be avoided. Since adding this configure argument changes the files the port installs, the port's revision must be increased.

The web page you mentioned above says:

The sqlite3 module is not built with loadable extension support by default, because some platforms (notably Mac OS X) have SQLite libraries which are compiled without this feature. To get loadable extension support, you must pass –enable-loadable-sqlite-extensions to configure

Perhaps they are referring to Apple's SQLite libraries in OS X, and this does not apply to the our SQLite libraries in MacPorts? Did you verify that adding this configure argument and rebuilding the port had the desired effect?

[petrrr]

Cc Me!

[m3drano@…]

I agree it should be on by default. I have tested this with the SQLite version provided by MacPorts (3.8.11.2) and the system default in El Capitan (3.8.10.2). It works perfectly with the port, both with the sqlite3 tool and the python library. The default sqlite3 install has extension loading disabled (both on the sqlite3 tool and the Python 3 version provided).

$ /usr/bin/sqlite3 
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load liblibsqlitefunctions.so;
Error: unknown command or invalid arguments:  "load". Enter ".help" for help

$ sqlite3 
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load liblibsqlitefunctions.so

$ /usr/bin/python
Python 2.7.10 (default, Aug 22 2015, 20:33:39) 
[GCC 4.2.1 Compatible Apple LLVM 7.0.0 (clang-700.0.59.1)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> conn = sqlite3.connect('test.db')
>>> conn.enable_load_extensions(True)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
AttributeError: 'sqlite3.Connection' object has no attribute 'enable_load_extensions'
>>> 

$ python
Python 3.4.3 (default, Oct 16 2015, 19:24:20) 
[GCC 4.2.1 Compatible Apple LLVM 7.0.0 (clang-700.0.72)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> conn = sqlite3.connect('tmp.db')
>>> conn.enable_load_extension(True)
>>> conn.load_extension('liblibsqlitefunctions.so')
>>>

[m3drano@…]

Do we have any update regarding this change proposal? I think we should enable it on all Python versions.

[sonkm3 (sonkm3)]

Cc Me!

[daniel@…]

Cc Me!

[daniel@…]

I'm currently using the following super-trivial variant with the python 3.5 port in order to use loadable modules, so far without any issues, on OS X 10.11.6:

variant sqliteext description {Enable loadable sqlite extensions} {
    configure.args-append   --enable-loadable-sqlite-extensions
}

No other modifications were necessary. When using python3.5 from /opt/local, conn.enable_load_extensions(True) works and sqlite extensions can be loaded.

Patching portfiles locally is a major PITA, it would be great if this could be enabled by default in all python 3.x ports, or at least be an available variant in all python 3.x ports.

[larryv (Lawrence Velázquez)]

Cc Me!

[dmarteau (David Marteau)]

Please, is there currently a roadmap for this bug ?

Since pyspatialite is now totally obsolete (run only with spatialite 3), the only way to use spatialite with python is loading the extension explicitely. Do not been able to load sqlite extension in python on OSX is really painful.

Thx

[larryv (Lawrence Velázquez)]

Sorry for the lack of action here. I think we should enable this on all Python ports and will do so this week, if Jyrki doesn’t object.

[jyrkiwahlstedt]

Replying to larryv@…:

Sorry for the lack of action here. I think we should enable this on all Python ports and will do so this week, if Jyrki doesn’t object.

Go ahead, please!

[dmarteau (David Marteau)]

Cc Me!

[m3drano@…]

ping?

[larryv (Lawrence Velázquez)]

Sorry again for the delay. Enabled for all relevant Python ports in r153800. Thanks for your patience.

[m3drano@…]

Thanks a lot for the change!