Use sqlite3 to directly run some SQL queries in QgsSpatiaLiteProvider…

…Connection, where we don't require result rows Because GDAL runs some consistency checks on spatialite databases on opening, this breaks the multi-step SQL commands which are run as part of renameVectorTable. The first SQL renames the table, then when the second tries to run gdal rejects the database because the geometry_columns definition is corrupt. Instead, add a method to directly run sql commands using sqlite3 instead of GDAL, and use this to run any SQL where we don't require result rows. Fixes Spatialite connection test
qgis · Mar 16, 2020 · 684a237 · 684a237
1 parent 99955e4
commit 684a237
Show file tree

Hide file tree

Showing 3 changed files with 28 additions and 5 deletions.
diff --git a/src/providers/spatialite/qgsspatialiteproviderconnection.cpp b/src/providers/spatialite/qgsspatialiteproviderconnection.cpp
@@ -161,17 +161,17 @@ void QgsSpatiaLiteProviderConnection::renameVectorTable( const QString &schema,
   QString sql( QStringLiteral( "ALTER TABLE %1 RENAME TO %2" )
                .arg( QgsSqliteUtils::quotedIdentifier( name ),
                      QgsSqliteUtils::quotedIdentifier( newName ) ) );
-  executeSqlPrivate( sql );
+  executeSqlDirect( sql );
   sql = QStringLiteral( "UPDATE geometry_columns SET f_table_name = lower(%2) WHERE lower(f_table_name) = lower(%1)" )
         .arg( QgsSqliteUtils::quotedString( name ),
               QgsSqliteUtils::quotedString( newName ) );
-  executeSqlPrivate( sql );
+  executeSqlDirect( sql );
   sql = QStringLiteral( "UPDATE layer_styles SET f_table_name = lower(%2) WHERE f_table_name = lower(%1)" )
         .arg( QgsSqliteUtils::quotedString( name ),
               QgsSqliteUtils::quotedString( newName ) );
   try
   {
-    executeSqlPrivate( sql );
+    executeSqlDirect( sql );
   }
   catch ( QgsProviderConnectionException &ex )
   {
@@ -193,7 +193,7 @@ void QgsSpatiaLiteProviderConnection::vacuum( const QString &schema, const QStri
   {
     QgsMessageLog::logMessage( QStringLiteral( "Schema is not supported by Spatialite, ignoring" ), QStringLiteral( "OGR" ), Qgis::Info );
   }
-  executeSqlPrivate( QStringLiteral( "VACUUM" ) );
+  executeSqlDirect( QStringLiteral( "VACUUM" ) );
 }
 
 
@@ -391,6 +391,24 @@ QList<QVariantList> QgsSpatiaLiteProviderConnection::executeSqlPrivate( const QS
   return results;
 }
 
+bool QgsSpatiaLiteProviderConnection::executeSqlDirect( const QString &sql ) const
+{
+  sqlite3_database_unique_ptr database;
+  int result = database.open( pathFromUri() );
+  if ( result != SQLITE_OK )
+  {
+    throw QgsProviderConnectionException( QObject::tr( "Error executing SQL %1: %2" ).arg( sql ).arg( database.errorMessage() ) );
+  }
+
+  QString errorMessage;
+  result = database.exec( sql, errorMessage );
+  if ( result != SQLITE_OK )
+  {
+    throw QgsProviderConnectionException( QObject::tr( "Error executing SQL %1: %2" ).arg( sql ).arg( errorMessage ) );
+  }
+  return true;
+}
+
 QString QgsSpatiaLiteProviderConnection::pathFromUri() const
 {
   const QgsDataSourceUri dsUri( uri() );

diff --git a/src/providers/spatialite/qgsspatialiteproviderconnection.h b/src/providers/spatialite/qgsspatialiteproviderconnection.h
@@ -50,6 +50,9 @@ class QgsSpatiaLiteProviderConnection : public QgsAbstractDatabaseProviderConnec
     //! Use GDAL to execute SQL
     QList<QVariantList> executeSqlPrivate( const QString &sql ) const;
 
+    //! Executes SQL directly using sqlite3 -- avoids the extra consistency checks which GDAL requires when opening a spatialite database
+    bool executeSqlDirect( const QString &sql ) const;
+
     //! extract the path from the DS URI (which is in "PG" form: 'dbname=\'/path_to.sqlite\' table="table_name" (geom_col_name)')
     QString pathFromUri() const;
 

diff --git a/tests/src/python/test_qgsproviderconnection_spatialite.py b/tests/src/python/test_qgsproviderconnection_spatialite.py
@@ -15,6 +15,7 @@
 
 import os
 import shutil
+import tempfile
 from test_qgsproviderconnection_base import TestPyQgsProviderConnectionBase
 from qgis.core import (
     QgsWkbTypes,
@@ -43,8 +44,9 @@ class TestPyQgsProviderConnectionSpatialite(unittest.TestCase, TestPyQgsProvider
     def setUpClass(cls):
         """Run before all tests"""
         TestPyQgsProviderConnectionBase.setUpClass()
+        cls.basetestpath = tempfile.mkdtemp()
         spatialite_original_path = '{}/qgis_server/test_project_wms_grouped_layers.sqlite'.format(TEST_DATA_DIR)
-        cls.spatialite_path = '{}/qgis_server/test_project_wms_grouped_layers_test.sqlite'.format(TEST_DATA_DIR)
+        cls.spatialite_path = os.path.join(cls.basetestpath, 'test.sqlite')
         shutil.copy(spatialite_original_path, cls.spatialite_path)
         cls.uri = "dbname=\'%s\'" % cls.spatialite_path
         vl = QgsVectorLayer('{} table=\'cdb_lines\''.format(cls.uri), 'test', 'spatialite')