Merge pull request #35488 from elpaso/spatialite-transactions

Spatialite provider transactions
qgis · Apr 1, 2020 · cac2d28 · cac2d28
2 parents f31af07 + c52d79e
commit cac2d28
Show file tree

Hide file tree

Showing 11 changed files with 528 additions and 132 deletions.
diff --git a/src/core/qgsconnectionpool.h b/src/core/qgsconnectionpool.h
@@ -285,8 +285,6 @@ class QgsConnectionPool
      * If \a timeout is a negative value the calling thread will be blocked
      * until a connection becomes available. This is the default behavior.
      *
-     *
-     *
      * \returns initialized connection or NULLPTR if unsuccessful
      */
     T acquireConnection( const QString &connInfo, int timeout = -1, bool requestMayBeNested = false )

diff --git a/src/core/qgsspatialiteutils.cpp b/src/core/qgsspatialiteutils.cpp
@@ -22,6 +22,22 @@
 #include <sqlite3.h>
 #include <spatialite.h>
 
+// Define this variable to print all spatialite SQL statements
+#ifdef SPATIALITE_PRINT_ALL_SQL
+// Debugging code
+#include <QDebug>
+#include <QThread>
+static int trace_callback( unsigned, void *ctx, void *p, void * )
+{
+  sqlite3_stmt *stmt = ( sqlite3_stmt * )p;
+  char *sql = sqlite3_expanded_sql( stmt );
+  qDebug() << "SPATIALITE" << QThread::currentThreadId() << ( sqlite3 * ) ctx << sql;
+  sqlite3_free( sql );
+  return 0;
+}
+#endif
+
+
 int spatialite_database_unique_ptr::open( const QString &path )
 {
   auto &deleter = get_deleter();
@@ -54,6 +70,16 @@ int spatialite_database_unique_ptr::open_v2( const QString &path, int flags, con
   if ( result == SQLITE_OK )
     spatialite_init_ex( database, deleter.mSpatialiteContext, 0 );
 
+#ifdef SPATIALITE_PRINT_ALL_SQL
+  // Log all queries
+  sqlite3_trace_v2(
+    database,
+    SQLITE_TRACE_STMT,
+    trace_callback,
+    database
+  );
+#endif
+
   return result;
 }
 

diff --git a/src/providers/spatialite/CMakeLists.txt b/src/providers/spatialite/CMakeLists.txt
@@ -13,6 +13,7 @@ SET(SPATIALITE_SRCS
   qgsspatialitefeatureiterator.cpp
   qgsspatialitetablemodel.cpp
   qgsspatialiteproviderconnection.cpp
+  qgsspatialitetransaction.cpp
 )
 
 IF (WITH_GUI)

diff --git a/src/providers/spatialite/qgsspatialiteconnection.h b/src/providers/spatialite/qgsspatialiteconnection.h
@@ -158,7 +158,13 @@ class QgsSqliteHandle
     {
       mIsValid = false;
     }
+
+    /**
+     * Returns a possibly cached SQLite DB object from \a path, if \a shared is FALSE
+     * the DB will not be searched in the cache and a new READ ONLY connection will be returned.
+     */
     static QgsSqliteHandle *openDb( const QString &dbPath, bool shared = true );
+
     static bool checkMetadata( sqlite3 *handle );
     static void closeDb( QgsSqliteHandle *&handle );
 

diff --git a/src/providers/spatialite/qgsspatialitefeatureiterator.cpp b/src/providers/spatialite/qgsspatialitefeatureiterator.cpp
@@ -29,7 +29,16 @@
 QgsSpatiaLiteFeatureIterator::QgsSpatiaLiteFeatureIterator( QgsSpatiaLiteFeatureSource *source, bool ownSource, const QgsFeatureRequest &request )
   : QgsAbstractFeatureIteratorFromSource<QgsSpatiaLiteFeatureSource>( source, ownSource, request )
 {
-  mHandle = QgsSpatiaLiteConnPool::instance()->acquireConnection( mSource->mSqlitePath, request.timeout(), request.requestMayBeNested() );
+
+  mSqliteHandle = source->transactionHandle();
+  if ( ! mSqliteHandle )
+  {
+    mHandle = QgsSpatiaLiteConnPool::instance()->acquireConnection( mSource->mSqlitePath, request.timeout(), request.requestMayBeNested() );
+    if ( mHandle )
+    {
+      mSqliteHandle = mHandle->handle();
+    }
+  }
 
   mFetchGeometry = !mSource->mGeometryColumn.isNull() && !( mRequest.flags() & QgsFeatureRequest::NoGeometry );
   mHasPrimaryKey = !mSource->mPrimaryKey.isEmpty();
@@ -290,9 +299,10 @@ bool QgsSpatiaLiteFeatureIterator::close()
 
   iteratorClosed();
 
-  if ( !mHandle )
+  mClosed = true;
+
+  if ( !mSqliteHandle )
   {
-    mClosed = true;
     return false;
   }
 
@@ -302,19 +312,24 @@ bool QgsSpatiaLiteFeatureIterator::close()
     sqliteStatement = nullptr;
   }
 
-  QgsSpatiaLiteConnPool::instance()->releaseConnection( mHandle );
-  mHandle = nullptr;
+  if ( mHandle )
+  {
+    QgsSpatiaLiteConnPool::instance()->releaseConnection( mHandle );
+    mHandle = nullptr;
+  }
 
+  mSqliteHandle = nullptr;
   mClosed = true;
   return true;
 }
 
+
 ////
 
 
 bool QgsSpatiaLiteFeatureIterator::prepareStatement( const QString &whereClause, long limit, const QString &orderBy )
 {
-  if ( !mHandle )
+  if ( !mSqliteHandle )
     return false;
 
   try
@@ -359,10 +374,10 @@ bool QgsSpatiaLiteFeatureIterator::prepareStatement( const QString &whereClause,
 
     QgsDebugMsgLevel( sql, 4 );
 
-    if ( sqlite3_prepare_v2( mHandle->handle(), sql.toUtf8().constData(), -1, &sqliteStatement, nullptr ) != SQLITE_OK )
+    if ( sqlite3_prepare_v2( mSqliteHandle, sql.toUtf8().constData(), -1, &sqliteStatement, nullptr ) != SQLITE_OK )
     {
       // some error occurred
-      QgsMessageLog::logMessage( QObject::tr( "SQLite error: %2\nSQL: %1" ).arg( sql, sqlite3_errmsg( mHandle->handle() ) ), QObject::tr( "SpatiaLite" ) );
+      QgsMessageLog::logMessage( QObject::tr( "SQLite error: %2\nSQL: %1" ).arg( sql, sqlite3_errmsg( mSqliteHandle ) ), QObject::tr( "SpatiaLite" ) );
       return false;
     }
   }
@@ -489,7 +504,7 @@ bool QgsSpatiaLiteFeatureIterator::getFeature( sqlite3_stmt *stmt, QgsFeature &f
   if ( ret != SQLITE_ROW )
   {
     // some unexpected error occurred
-    QgsMessageLog::logMessage( QObject::tr( "SQLite error getting feature: %1" ).arg( QString::fromUtf8( sqlite3_errmsg( mHandle->handle() ) ) ), QObject::tr( "SpatiaLite" ) );
+    QgsMessageLog::logMessage( QObject::tr( "SQLite error getting feature: %1" ).arg( QString::fromUtf8( sqlite3_errmsg( mSqliteHandle ) ) ), QObject::tr( "SpatiaLite" ) );
     return false;
   }
 
@@ -642,10 +657,16 @@ QgsSpatiaLiteFeatureSource::QgsSpatiaLiteFeatureSource( const QgsSpatiaLiteProvi
   , mSpatialIndexMbrCache( p->mSpatialIndexMbrCache )
   , mSqlitePath( p->mSqlitePath )
   , mCrs( p->crs() )
+  , mTransactionHandle( p->transaction() ? p->sqliteHandle() : nullptr )
 {
 }
 
 QgsFeatureIterator QgsSpatiaLiteFeatureSource::getFeatures( const QgsFeatureRequest &request )
 {
   return QgsFeatureIterator( new QgsSpatiaLiteFeatureIterator( this, false, request ) );
 }
+
+sqlite3 *QgsSpatiaLiteFeatureSource::transactionHandle()
+{
+  return mTransactionHandle;
+}
diff --git a/src/providers/spatialite/qgsspatialitefeatureiterator.h b/src/providers/spatialite/qgsspatialitefeatureiterator.h
@@ -34,6 +34,8 @@ class QgsSpatiaLiteFeatureSource final: public QgsAbstractFeatureSource
 
     QgsFeatureIterator getFeatures( const QgsFeatureRequest &request ) override;
 
+    sqlite3 *transactionHandle();
+
   private:
     QString mGeometryColumn;
     QString mSubsetString;
@@ -49,6 +51,7 @@ class QgsSpatiaLiteFeatureSource final: public QgsAbstractFeatureSource
     bool mSpatialIndexMbrCache;
     QString mSqlitePath;
     QgsCoordinateReferenceSystem mCrs;
+    sqlite3 *mTransactionHandle = nullptr;
 
     friend class QgsSpatiaLiteFeatureIterator;
     friend class QgsSpatiaLiteExpressionCompiler;
@@ -81,8 +84,10 @@ class QgsSpatiaLiteFeatureIterator final: public QgsAbstractFeatureIteratorFromS
     QVariant getFeatureAttribute( sqlite3_stmt *stmt, int ic, QVariant::Type type, QVariant::Type subType );
     void getFeatureGeometry( sqlite3_stmt *stmt, int ic, QgsFeature &feature );
 
-    //! wrapper of the SQLite database connection
+    //! QGIS wrapper of the SQLite database connection
     QgsSqliteHandle *mHandle = nullptr;
+    //! The low level connection
+    sqlite3 *mSqliteHandle = nullptr;
 
     /**
       * SQLite statement handle