MinGW build:Spatialite and GeoPackage not fully supported ('sqlite3.Connection' object has no attribute 'enable_load_extension')

[agiudiceandrea]

Describe the bug

Using the MinGW build (https://github.com/qgis/QGIS/pull/41527)

• Spatialite

a Spatialite connection made through the Browser panel is not available in the DB Manager core plugin. The connection is listed, but the following error is displayed trying to open the connection:

An error has occurred while executing Python code: 

AttributeError: 'sqlite3.Connection' object has no attribute 'enable_load_extension' 
Traceback (most recent call last):
  File "F:/QGIS/QGIS-Portable/share/qgis/python/plugins\db_manager\db_model.py", line 456, in rowCount
    self._refreshIndex(parent, True)
  File "F:/QGIS/QGIS-Portable/share/qgis/python/plugins\db_manager\db_model.py", line 505, in _refreshIndex
    if item.populate():
  File "F:/QGIS/QGIS-Portable/share/qgis/python/plugins\db_manager\db_model.py", line 182, in populate
    if not connection.connect():
  File "F:/QGIS/QGIS-Portable/share/qgis/python/plugins\db_manager\db_plugins\spatialite\plugin.py", line 78, in connect
    return self.connectToUri(uri)
  File "F:/QGIS/QGIS-Portable/share/qgis/python/plugins\db_manager\db_plugins\plugin.py", line 142, in connectToUri
    self.db = self.databasesFactory(self, uri)
  File "F:/QGIS/QGIS-Portable/share/qgis/python/plugins\db_manager\db_plugins\spatialite\plugin.py", line 64, in databasesFactory
    return SLDatabase(connection, uri)
  File "F:/QGIS/QGIS-Portable/share/qgis/python/plugins\db_manager\db_plugins\spatialite\plugin.py", line 107, in __init__
    Database.__init__(self, connection, uri)
  File "F:/QGIS/QGIS-Portable/share/qgis/python/plugins\db_manager\db_plugins\plugin.py", line 267, in __init__
    self.connector = self.connectorsFactory(uri)
  File "F:/QGIS/QGIS-Portable/share/qgis/python/plugins\db_manager\db_plugins\spatialite\plugin.py", line 110, in connectorsFactory
    return SpatiaLiteDBConnector(uri)
  File "F:/QGIS/QGIS-Portable/share/qgis/python/plugins\db_manager\db_plugins\spatialite\connector.py", line 51, in __init__
    self.connection = spatialite_connect(self._connectionInfo())
  File "F:\QGIS\QGIS-Portable\lib\python3.9\site-packages\qgis\utils.py", line 664, in spatialite_connect
    con.enable_load_extension(True)
AttributeError: 'sqlite3.Connection' object has no attribute 'enable_load_extension'

Python version: 3.9.1 (default, Feb 15 2021, 00:00:00) [GCC 10.2.1 20200723 (Fedora MinGW 10.2.1-6.fc34)] 
QGIS version: 3.19.0-Master Master, 7b37f32b 

Moreover it seem that Spatialite doesn't have all the topological and geodesic functions working, cause the rttopo library is missing:

md = QgsProviderRegistry.instance().providerMetadata('spatialite')
db=md.createConnection(QgsDataSourceUri('dbname=\'C:/TMP/test.sqlite\' table=\'test\'(geometry) sql=').uri(),{})
db.executeSql('select spatialite_version();')
[['5.0.0']]
db.executeSql('select rttopo_version();')
[[NULL]]

and geodesic function likeST_Area(geometry, true) returns Error executing SQL select st_area(geometry, true);): wrong number of arguments to function st_area(), while ST_Area(geometry) works but returns a non geodesic area value; topological function like ST_MakeValid(geometry) returns Error executing SQL select makeValid(geometry);: GEOS support not enabled.

• GeoPackage

a GeoPackage connection made through the Browser panel is not available in the DB Manager core plugin. The connection is listed, but the following error is displayed trying to open the connection:

"C:/TEST/1vlayer.gpkg" not recognized as GPKG (SQLite reported instead.)

moreover in the Browser panel all the internal tables are listed like it was a normal SQLite container and the tables with geometry are not recognised.

QGIS and OS versions

QGIS version: 3.19.0-Master Master, 7b37f32b

Windows 7 SP 1 64 bit

Additional context

[elpaso]

CC @manisandro any clue?

[manisandro]

AttributeError: 'sqlite3.Connection' object has no attribute 'enable_load_extension'

This should be fixed by https://koji.fedoraproject.org/koji/taskinfo?taskID=62765719

Moreover it seem that Spatialite doesn't have all the topological and geodesic functions working, cause the rttopo library is missing:

I'll need to package mingw-librttopo

[agiudiceandrea]

This should be fixed by https://koji.fedoraproject.org/koji/taskinfo?taskID=62765719

@manisandro I see that mingw-python3-3.9.2-2.fc35 was updated with "- Pass --enable-loadable-sqlite-extensions" https://koji.fedoraproject.org/koji/buildinfo?buildID=1715848. Anyway the latest MinGW build still suffers from the same issue: AttributeError: 'sqlite3.Connection' object has no attribute 'enable_load_extension' See also that the reported Python version is Python version: 3.9.1 (default, Feb 15 2021, 00:00:00) [GCC 10.2.1 20200723 (Fedora MinGW 10.2.1-6.fc34)]

[manisandro]

The docker container of the CI build is cached and still uses an older version of mingw-python3. Not sure how one triggers a rebuild of the container. @elpaso?

Btw review request for mingw-librttopo is here [1] in case there is any packager reading this.

[1] https://bugzilla.redhat.com/show_bug.cgi?id=1936125

[elpaso]

CC @3nids do you know how can we trigger a docker rebuild? Is there a GH tag for that?

[3nids]

@elpaso I just added the dispatch event to the workflow. You can now go here https://github.com/qgis/QGIS/actions/workflows/mingw64.yml and trigger it manually.

[elpaso]

@3nids thank you!

[elpaso]

@manisandro, Denis has just added the docker rebuild logic (any time the workflow is manually triggered), but apparently the QGIS build does not work with the new packages, did it work for you on your machine?

https://github.com/qgis/QGIS/runs/2054760822?check_suite_focus=true#step:7:86

[manisandro]

Hmm cannot reproduce this on my up-to-date rawhide, will need to investigate.

Btw mingw-librttopo is now built, after the next reposync it should become available.

[agiudiceandrea]

@manisandro should mingw64-librttopo be added to qgis3-build-deps-mingw.dockerfile?

[manisandro]

@agiudiceandrea No, it will get pulled in as a dependency with the updated libspatialite.

[agiudiceandrea]

@manisandro

Spatialite

It seems the Spatialite issues is partially fixed now (MinGW build QGIS version 3.19.0-Master QGIS code revision 56f7812c): a Spatialite connection made through the Browser panel is also available in the DB Manager core plugin and it is possible to "open" the connection in the DB Manager without errors and it is possible to successfully perform SQL queries using also geodesic function like ST_Area(geometry, true).

In the Python console

md = QgsProviderRegistry.instance().providerMetadata('spatialite')
db=md.createConnection(QgsDataSourceUri('dbname=\'C:/TMP/test.sqlite\' table=\'test\'(geometry) sql=').uri(),{})
db.executeSql('select sqlite_version(), spatialite_version(), rttopo_version();')

correctly returns

[['3.35.2', '5.0.1', '1.1.0']]

Anyway geodesic functions like st_area(geometry, true) always return NULL with db.executeSql('select st_area(geometry, true) from test;') in the Python console.

Geodesic functions don't work also in Virtual layers (this is a more general issue https://github.com/qgis/QGIS/issues/41890)

GeoPackage

The issue is still present with the same incorrect behaviour in both Browser and DB Manager and the same error ("C:/TMP/test.gpkg" not recognized as GPKG (SQLite reported instead.)) This could be due to something wrong with GDAL shipped by mingw64-gdal or mingw64-python3-gdal which doesn't support the GeoPackage format. In fact, in the MinGW build Python console:

from osgeo import gdal
gdal.__version__
'3.2.2'
driver_list = [gdal.GetDriver(i).ShortName for i in range(gdal.GetDriverCount())]
'GPKG' in driver_list
False
print(gdal.GetDriverByName('GPKG'))
None
gdal_ds = gdal.OpenEx('C:/TMP/test.gpkg')
gdal_ds.GetDriver().ShortName
'SQLite'

while using QGIS 3.18.1 (OSGeo4W testing) which is built against the same GDAL version (3.2.2), the same GeoPackage is correctly recognised as GPKG:

from osgeo import gdal
gdal.__version__
'3.2.2'
driver_list = [gdal.GetDriver(i).ShortName for i in range(gdal.GetDriverCount())]
'GPKG' in driver_list
True
print(gdal.GetDriverByName('GPKG'))
<osgeo.gdal.Driver; proxy of <Swig Object of type 'GDALDriverShadow *' at 0x000000000232EC90> >
gdal_ds = gdal.OpenEx('C:/TMP/test.gpkg')
gdal_ds.GetDriver().ShortName
'GPKG'

[agiudiceandrea]

After installing mingw64-gdal-tools in order to directly access gdal functionality, it seems to me the issue is more likely in mingw64-gdal. In fact, neither gdalinfo --formats nor ogrinfo --formats lists the GPKG driver among the available drivers:

F:\QGIS\QGIS-Portable\bin>ogrinfo --version
GDAL 3.2.2, released 2021/03/05

F:\QGIS\QGIS-Portable\bin>gdalinfo --formats | find /I "GPKG"

F:\QGIS\QGIS-Portable\bin>ogrinfo --formats | find /I "GPKG"

F:\QGIS\QGIS-Portable\bin>ogrinfo --formats | find /I "SQLite"
  SQLite -vector- (rw+v): SQLite / Spatialite

while, with the same gdal version shipped by OSGeo4W testing, both gdalinfo --formats and ogrinfo --formats list the GPKG driver:

F:\QGIS\SourceDir\qgis>ogrinfo --version
GDAL 3.2.2, released 2021/03/05

F:\QGIS\SourceDir\qgis>gdalinfo --formats | find /I "GPKG"
  GPKG -raster,vector- (rw+vs): GeoPackage
F:\QGIS\SourceDir\qgis>ogrinfo --formats | find /I "GPKG"
  GPKG -raster,vector- (rw+vs): GeoPackage
F:\QGIS\SourceDir\qgis>ogrinfo --formats | find /I "SQLite"
  SQLite -vector- (rw+v): SQLite / Spatialite

[roya0045]

@agiudiceandrea maybe it was compiled with the libspatialite and not libsqlite3?

[roya0045]

@agiudiceandrea Do you consider this as resolved with the latest changes?

[agiudiceandrea]

The mingw-gdal-3.2.2-2 will only fix the GeoPackage issue.

The Spatialite issue still partially occurs even with gdal-3.2.2-2:

Anyway geodesic functions like st_area(geometry, true) always return NULL with db.executeSql('select st_area(geometry, true) from test;') in the Python console.

Geodesic functions don't work also in Virtual layers (this is a more general issue #41890)

[roya0045]

And do you think those are entirely dependent on the gdal library or possibly on something else?

[agiudiceandrea]

@roya0045 I don't know.

[alexbruy]

Is this still valid?