On Wed, 24 Mar 2021 08:49:40 +1000, Pedro Camargo wrote:
> The setup usually goes as I describe in a blog post a while back
> (Spatialite and Python in 2020 – Xl-Optim), and it was working
> flawlessly until now. However, my KNN triggers are only working
> inside
> QGIS, while crashing Python and the sqlite browser silently (when I
> managed to get into the debugger it seemed to point to the sqlite3
> DLL)
>
Hi Pedro,
KNN is affected by what seems to be a flaw in the SQLite architecture.
just a little technical insight: the loadable module mod_spatialite
will usually receive a copy of "mirror APIs" directly pointing to
the corresponding APIs internally provvided by the calling library
libsqlite3.
KNN is fully based on sqlite3_rtree_query_callback() [1], a very
advanced API allowing to directly inspect the R*Tree, but unhappily
this one is one of the very few SQLite's API not reflected by the
"mirror APIs" supplied to loadable modules.
So, in order to support KNN in mod_spatialite we are necessarily
forced to explicitly link libsqlite3.
[1]
https://sqlite.org/rtree.html#customquery
My personal interpretation: until the binary code of mod_spatialite
points to the same identical copy of sqlite3.dll anything runs smoothly
(as it seems confirmed in your all QGIS environment).
but when the "mirror APIs" and the direct link to
sqlite3_rtree_query_callback()
point to two different DLLs (possibly of different versions, or
built by different compilers may be using different C runtimes)
some spectacular crash is almost certainly ensured.
(as it happens in your standalone Python environment not based
on QGIS itself)
short conclusion: mixing mod_spatialite.dll with a sqlite3.dll
someway different from the original one used at build time will
very probably be a cause of severe instability.
said in different words: when you absolutely need to use
mod_spatialite in different Python environments you should
always be very carefull in ensuring to load a copy of
mod_spatialite specifically built for that environment
and surely referencing the same sqlite3.dll loaded by
Python.
bye Sandro