pyspatialite needs a new maintainer

[linux...@gmail.com]

For quite some time Lokkju Brennr has not been able to spend time maintaining pyspatialite.

In the discussion about Python 3 support (required for QGIS 3.x) it became clear that pyspatialite needs a new maintainer who is able to continue development. [0]

Is there anyone in the SpatiaLite community who is willing to adopt pyspatialite and continue its development & maintenance?

[0] https://github.com/lokkju/pyspatialite/issues/27

Kind Regards,

Bas

[a.fu...@lqt.it]

Hi Bas,

there is any real reason suggesting that pyspatialite is any
longer useful and required ?

few objective facts:
- pyspatialite simply was a clone derived from pysqlite, that
just added of its own two or three lines of C code intended
to initialize and register spatialite as an extension to
sqlite.
- pyspatialite was supported by an awful build script strictly
depending from a specific version of spatialite, that nowadays
is cold dead by many years (the build script contained an
hardwired URL directly pointing to a very specific tarball:
changing this URL was unsupported and required some clumsy
patch to be manually applied.
- the "capital sin": pyspatialite statically linked a private
copy of spatialite instead of dynamically linking the
system-wide spatialite's shared libraries.
this odd design choice caused many severe troubles in past
years to QGIS, that could inadvertently trigger internal
version conflicts when complex operations were based both
on C++ core modules (using the system-wide shared libraries)
and on Python plugins (using the private internal static code).

the most astonishing fact: pyspatialite is completely useless.

since many years SQLite has the intrinsic capability to dynamically
load any possible extension, obviously including SpatiaLite.
You are just required to execute this "special" SQL command
immediately after establishing a new DB connection:

SELECT load_extension('mod_spatialite');

note: dynamically loading 'mod_spatialite' at run time will
have the further positive effect to automatically ensure
that the system-wide shared libraries will be loaded, thus
making impossible any version conflict between C++ modules
and Python plugins.

the following code snippet exemplifies how to correctly
load and initialize SpatiaLite on Python just depending
on the standard sqlite3 connector and completely avoiding
to call pyspatialite (tested on Debian 8)
---------------------
import sqlite3
conn = sqlite3.connect(':memory:)
conn.enable_load_extension(True)
conn.execute("SELECT load_extension('mod_spatialite')")

# just a stupid test to check if SpatiaLite really works
for row in conn.execute("SELECT ST_AsText(MakePoint(11.5, 42.5,
4326))"):
print row
---------------------

bye Sandro

[Loki]

Thanks Sandro; this has all been discussed in the pysptialite repository issues and here before, and is the reason I don’t maintain it any longer.

Almost all distros now support module loading, making pyspatialite completely unnecessary. If one doesn’t, that should be fixed at this point, not patched over like I did. It was a useful kludge, but caused lots of issues.

Loki

> --
> You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
> To post to this group, send email to spatiali...@googlegroups.com.
> Visit this group at https://groups.google.com/group/spatialite-users.
> For more options, visit https://groups.google.com/d/optout.

[linux...@gmail.com]

On Friday, September 29, 2017 at 6:55:31 PM UTC+2, sandro furieri wrote:

there is any real reason suggesting that pyspatialite is any
longer useful and required ?

The qgis package depends on it, and the pyspatialite package has been patched to link the system provided libspatialite.

I see that from QGIS 2.18 on, utils.py provides a spatialite_connect() function that falls back to sqlite3 with load_extension() when pyspatialite is not available.

So for what the qgis package is concerned we can drop the pyspatialite package when its switched from 2.14 to 2.18.

pyspatialite has users besides QGIS, but they will have to adopt the same procedure as well when its removed.

Kind Regards,

Bas

[Pierre Girardeau]

Hi Sandro, that is all very clear, though I could not make it work on a Windows 10 64bit. What you suggest returns a "module not found" error. Also tried with SQLalchemy, and here the error is weirder: OperationalError: (sqlite3.OperationalError) not authorized [SQL: "SELECT load_extension('mod_spatialite')"]

P.

[a.fu...@lqt.it]

On Mon, 2 Oct 2017 01:41:48 -0700 (PDT), Pierre Girardeau wrote:
> Hi Sandro, that is all very clear, though I could not make it work on
> a Windows 10 64bit. What you suggest returns a "module not found"
> error.
>

Hi Pierre,

SQLite has the intrinsic capability to automatically locate any
dynamic extension module referenced by its short name (as e.g.
"mod_spatialite") _ONLY IF_ the module fully respects the
standard system rules for loading binary/executable objects.

unhappily the standard searching rules on Windows are really
clumsy, and are fully documented here [1]

[1]
https://msdn.microsoft.com/en-us/library/windows/desktop/ms682586(v=vs.85).aspx

so you are getting a "module not found" error simply because
you've not installed "mod_spatialite.dll" into some standard
folder automatically covered by the platform rules.

how to solve such an issue:
1) (inelegant brute force workaround) explicitly pass to
"load_extension" a full path, as e.g.
SELECT load_extension('C:/my_extensions/mod_spatialite');
2) (more sophisticated approach) alter the PATH system variable
so to include the directory where you've installed "mod_spatialite".
basically, from the command shell:
SET PATH=%PATH%;c:\my_extensions

> Also tried with SQLalchemy, and here the error is weirder:
> OperationalError: (sqlite3.OperationalError) not authorized [SQL:
> "SELECT load_extension('mod_spatialite')"]
>

it's not at all weird; the Python/SQLite3 connector requires
an explicit authorization so to effectively enable dynamic
loading. if you notice well, you'll find this line of code
on my snippet just before attempting to load the extension:

conn.enable_load_extension(True)

bye Sandro

[Pierre Girardeau]

Thanks, Sandro. I've tried the "hard" way: specifying the full path to the module, but python tells me it cannot find it anyway :( For now, I've found a workaround working with a different DB type. I will try and sort this out later when I have more time.

Anyway, thanks very much for helping!

P.

[a.fu...@lqt.it]

On Mon, 2 Oct 2017 03:05:21 -0700 (PDT), Pierre Girardeau wrote:
> Thanks, Sandro. I've tried the "hard" way: specifying the full path
> to
> the module, but python tells me it cannot find it anyway :( For now,
> I've found a workaround working with a different DB type. I will try
> and sort this out later when I have more time.
>

Pierre,

few (may be) useful hints:
- remember: you can never mix 32 and 64 bit executables.
if you are using e.g. Python 32 bit you'll then necessarily
use a 32 bit "mod_spatialite"
- "mod_spatialite" depends on several other DLLs (geos, proj
etc; a failure to locate and load anyone of them will
automatically cause a failure in loading "mod_spatialite"
itself. always copy _ALL_ depending DLLs into the same
folder where "mod_spatialite" is.
- testing first "load_module" by using the "sqlite3.exe"
front-end may usually get a by far richer diagnostic.
- using the nice "dependency walker" tool may help as well:
http://www.dependencywalker.com/

bye Sandro

[Jukka Rahkonen]

On Monday, October 2, 2017 at 1:19:12 PM UTC+3, sandro furieri wrote:

- "mod_spatialite" depends on several other DLLs (geos, proj
   etc; a failure to locate and load anyone of them will
   automatically cause a failure in loading "mod_spatialite"
   itself. always copy _ALL_ depending DLLs into the same
   folder where "mod_spatialite" is.
- 

bye Sandro

I agree that it is safest to keep all the dll's and mod_spatialite in the same directory, but at least with Java on Windows it can still happen that mod_spatialite is found but dll's not. By moving  the whole bunch into all possible candidate directories and testing after each change has worked for me.

-Jukka Rahkonen-

[Volker Fröhlich]

Am 2017-09-29 um 23:37 schrieb linux...@gmail.com:

> The qgis package depends on it, and the pyspatialite package has been
> patched to link the system provided libspatialite.
>
> I see that from QGIS 2.18 on, utils.py provides a spatialite_connect()
> function that falls back to sqlite3 with load_extension() when
> pyspatialite is not available.

I can't seem to spot that in 2.18.12. The only occurrence of
load_extension that I find is in
python/ext-libs/pyspatialite/src/connection.c, which is part of the
bundled pyspatialite.

python/plugins/db_manager/db_plugins/spatialite/connector.py is
importing from pyspatialite.

Greetings,

Volker

[linux...@gmail.com]

On Thursday, October 5, 2017 at 9:33:32 PM UTC+2, Volker Fröhlich wrote:

Am 2017-09-29 um 23:37 schrieb linux...@gmail.com:

> The qgis package depends on it, and the pyspatialite package has been
> patched to link the system provided libspatialite.
>
> I see that from QGIS 2.18 on, utils.py provides a spatialite_connect()
> function that falls back to sqlite3 with load_extension() when
> pyspatialite is not available.

I can't seem to spot that in 2.18.12. The only occurrence of
load_extension that I find is in
python/ext-libs/pyspatialite/src/connection.c, which is part of the
bundled pyspatialite.

Apparently I was looking on the wrong branch, it's in master for QGIS 3.x:

 https://github.com/qgis/QGIS/blob/master/python/utils.py#L588

Kind Regards,

Bas