Move to thread the first phase of the query (before the iterator)

qgis · Jul 6, 2021 · 9fc81ff · 9fc81ff
1 parent b988ca8
commit 9fc81ff
Show file tree

Hide file tree

Showing 7 changed files with 119 additions and 40 deletions.
diff --git a/python/gui/auto_generated/qgsqueryresultwidget.sip.in b/python/gui/auto_generated/qgsqueryresultwidget.sip.in
@@ -11,7 +11,6 @@
 
 
 
-
 class QgsQueryResultWidget: QWidget
 {
 %Docstring

diff --git a/src/app/browser/qgsinbuiltdataitemproviders.cpp b/src/app/browser/qgsinbuiltdataitemproviders.cpp
@@ -51,6 +51,7 @@
 #include "qgsprojectitem.h"
 #include "qgsfieldsitem.h"
 #include "qgsconnectionsitem.h"
+#include "qgsqueryresultwidget.h"
 
 #include <QFileInfo>
 #include <QMenu>
@@ -1059,6 +1060,35 @@ void QgsDatabaseItemGuiProvider::populateContextMenu( QgsDataItem *item, QMenu *
         } );
         menu->addAction( newTableAction );
       }
+
+      if ( conn && conn->capabilities().testFlag( QgsAbstractDatabaseProviderConnection::Capability::ExecuteSql ) )
+      {
+
+        QAction *sqlAction = new QAction( QObject::tr( "Run SQL command…" ), menu );
+
+        QObject::connect( sqlAction, &QAction::triggered, collectionItem, [ collectionItem ]
+        {
+          std::unique_ptr<QgsAbstractDatabaseProviderConnection> conn2( collectionItem->databaseConnection() );
+          // This should never happen but let's play safe
+          if ( ! conn2 )
+          {
+            QgsMessageLog::logMessage( tr( "Connection to the database (%1) was lost." ).arg( collectionItem->name() ) );
+            return;
+          }
+          QgsDialog dialog;
+          QgsQueryResultWidget *widget { new QgsQueryResultWidget( &dialog, conn2.release() ) };
+          widget->layout()->setMargin( 0 );
+          dialog.layout()->addWidget( widget );
+          connect( widget, &QgsQueryResultWidget::createSqlVectorLayer, widget, [collectionItem]( const QString &, const QString &, const QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions & options )
+          {
+            std::unique_ptr<QgsAbstractDatabaseProviderConnection> conn3( collectionItem->databaseConnection() );
+            conn3->createSqlVectorLayer( options );
+          } );
+          dialog.exec();
+        } );
+        menu->addAction( sqlAction );
+
+      }
     }
   }
 }

diff --git a/src/core/providers/qgsabstractdatabaseproviderconnection.cpp b/src/core/providers/qgsabstractdatabaseproviderconnection.cpp
@@ -288,6 +288,7 @@ QList<QgsAbstractDatabaseProviderConnection::TableProperty::GeometryColumnType>
   return mGeometryColumnTypes;
 }
 
+
 QgsFields QgsAbstractDatabaseProviderConnection::fields( const QString &schema, const QString &tableName ) const
 {
   QgsVectorLayer::LayerOptions options { false, true };

diff --git a/src/gui/qgsqueryresultwidget.cpp b/src/gui/qgsqueryresultwidget.cpp
@@ -22,6 +22,7 @@
 #include <QStandardItem>
 
 
+
 QgsQueryResultWidget::QgsQueryResultWidget( QWidget *parent, QgsAbstractDatabaseProviderConnection *connection )
   : QWidget::QWidget( parent )
 {
@@ -77,49 +78,41 @@ void QgsQueryResultWidget::executeQuery()
   mQueryResultsTableView->show();
   mSqlErrorText->hide();
   mFirstRowFetched = false;
+
+  // Wait for other threads
+  if ( mFeedback )
+  {
+    mFeedback->cancel();
+  }
+  mQueryResultWatcher.waitForFinished();
+
   if ( mConnection )
   {
     const auto sql = mSqlEditor->text( );
     try
     {
       mWasCanceled = false;
       mFeedback = qgis::make_unique<QgsFeedback>();
-      connect( mStopButton, &QPushButton::pressed, mFeedback.get(), &QgsFeedback::cancel );
+      mStopButton->setEnabled( true );
+      mStatusLabel->show();
+      mStatusLabel->setText( tr( "Running⋯" ) );
 
-      mModel = qgis::make_unique<QgsQueryResultModel>( mConnection->execSql( sql, mFeedback.get() ) );
-      connect( mFeedback.get(), &QgsFeedback::canceled, mModel.get(), [ = ]
+      connect( mStopButton, &QPushButton::pressed, mFeedback.get(), [ = ]
       {
-        mModel->cancel();
+        mStatusLabel->setText( tr( "Stopped" ) );
+        mFeedback->cancel();
         mWasCanceled = true;
       } );
 
-      mStatusLabel->show();
-      mStatusLabel->setText( tr( "Running⋯" ) );
+      // Create model when result is ready
+      connect( &mQueryResultWatcher, &QFutureWatcher<QgsAbstractDatabaseProviderConnection::QueryResult>::finished, this, &QgsQueryResultWidget::startFetching, Qt::ConnectionType::UniqueConnection );
 
-      connect( mModel.get(), &QgsQueryResultModel::rowsInserted, this, [ = ]( const QModelIndex &, int, int )
+      QFuture<QgsAbstractDatabaseProviderConnection::QueryResult> future = QtConcurrent::run( [ = ]() -> QgsAbstractDatabaseProviderConnection::QueryResult
       {
-        if ( ! mFirstRowFetched )
-        {
-          mFirstRowFetched = true;
-          updateButtons();
-          updateSqlLayerColumns( );
-        }
-        mStatusLabel->setText( tr( "Fetched rows: %1 %2" )
-                               .arg( mModel->rowCount( mModel->index( -1, -1 ) ) )
-                               .arg( mWasCanceled ? QStringLiteral( "(stopped)" ) : QString() ) );
+        return mConnection->execSql( sql, mFeedback.get() );
       } );
-      mQueryResultsTableView->setModel( mModel.get() );
-      mQueryResultsTableView->show();
+      mQueryResultWatcher.setFuture( future );
 
-      mStopButton->setEnabled( true );
-      connect( mModel.get(), &QgsQueryResultModel::fetchingComplete, mStopButton, [ = ]
-      {
-        mStopButton->setEnabled( false );
-        if ( ! mWasCanceled )
-        {
-          mStatusLabel->setText( "Query executed successfully." );
-        }
-      } );
     }
     catch ( QgsProviderConnectionException &ex )
     {
@@ -141,6 +134,9 @@ void QgsQueryResultWidget::updateButtons()
 
 void QgsQueryResultWidget::updateSqlLayerColumns( )
 {
+  // Precondition
+  Q_ASSERT( mModel );
+
   mFilterToolButton->setEnabled( true );
   mPkColumnsComboBox->clear();
   mGeometryColumnComboBox->clear();
@@ -162,6 +158,50 @@ void QgsQueryResultWidget::updateSqlLayerColumns( )
   }
 }
 
+void QgsQueryResultWidget::startFetching()
+{
+  if ( ! mWasCanceled )
+  {
+    QgsAbstractDatabaseProviderConnection::QueryResult result { mQueryResultWatcher.result() };
+    mModel = qgis::make_unique<QgsQueryResultModel>( result );
+    connect( mFeedback.get(), &QgsFeedback::canceled, mModel.get(), [ = ]
+    {
+      mModel->cancel();
+      mWasCanceled = true;
+    } );
+
+    connect( mModel.get(), &QgsQueryResultModel::rowsInserted, this, [ = ]( const QModelIndex &, int, int )
+    {
+      if ( ! mFirstRowFetched )
+      {
+        emit columnNamesReady();
+        mFirstRowFetched = true;
+        updateButtons();
+        updateSqlLayerColumns( );
+      }
+      mStatusLabel->setText( tr( "Fetched rows: %1 %2" )
+                             .arg( mModel->rowCount( mModel->index( -1, -1 ) ) )
+                             .arg( mWasCanceled ? tr( "(stopped)" ) : QString() ) );
+    } );
+    mQueryResultsTableView->setModel( mModel.get() );
+    mQueryResultsTableView->show();
+
+    connect( mModel.get(), &QgsQueryResultModel::fetchingComplete, mStopButton, [ = ]
+    {
+      mStopButton->setEnabled( false );
+      if ( ! mWasCanceled )
+      {
+        mStatusLabel->setText( "Query executed successfully." );
+      }
+    } );
+  }
+  else
+  {
+    mStatusLabel->setText( tr( "SQL command aborted" ) );
+  }
+
+}
+
 void QgsQueryResultWidget::showError( const QString &title, const QString &message )
 {
   mStatusLabel->show();

diff --git a/src/gui/qgsqueryresultwidget.h b/src/gui/qgsqueryresultwidget.h
@@ -25,7 +25,7 @@
 
 #include <QWidget>
 #include <QThread>
-
+#include <QtConcurrent>
 
 ///@cond private
 
@@ -157,6 +157,7 @@ class GUI_EXPORT QgsQueryResultWidget: public QWidget, private Ui::QgsQueryResul
     bool mWasCanceled = false;
     QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions mSqlVectorLayerOptions;
     bool mFirstRowFetched = false;
+    QFutureWatcher<QgsAbstractDatabaseProviderConnection::QueryResult> mQueryResultWatcher;
 
     /**
      * Updates buttons status
@@ -165,6 +166,10 @@ class GUI_EXPORT QgsQueryResultWidget: public QWidget, private Ui::QgsQueryResul
 
     void updateSqlLayerColumns();
 
+    /**
+     * Starts the model population after initial query run
+     */
+    void startFetching();
 
     friend class TestQgsQueryResultWidget;
 

diff --git a/src/providers/postgres/qgspostgresproviderconnection.cpp b/src/providers/postgres/qgspostgresproviderconnection.cpp
@@ -131,7 +131,6 @@ void QgsPostgresProviderConnection::createVectorTable( const QString &schema,
 
 QString QgsPostgresProviderConnection::tableUri( const QString &schema, const QString &name ) const
 {
-  const auto tableInfo { table( schema, name ) };
   QgsDataSourceUri dsUri( uri() );
   dsUri.setTable( name );
   dsUri.setSchema( schema );
@@ -379,7 +378,7 @@ QVariantList QgsPostgresProviderResultIterator::nextRowPrivate()
   // Get results
   QVariantList row;
 
-  if ( mRowIndex >= result->PQntuples() )
+  if ( !result || mRowIndex >= result->PQntuples() )
   {
     // Release the resources
     mConn.reset();
@@ -418,7 +417,7 @@ QVariantList QgsPostgresProviderResultIterator::nextRowPrivate()
 
 bool QgsPostgresProviderResultIterator::hasNextRowPrivate() const
 {
-  return mRowIndex < result->PQntuples();
+  return result && mRowIndex < result->PQntuples();
 }
 
 

diff --git a/tests/src/gui/testqgsqueryresultwidget.cpp b/tests/src/gui/testqgsqueryresultwidget.cpp
@@ -35,11 +35,10 @@ class TestQgsQueryResultWidget: public QObject
     void init(); // will be called before each testfunction is executed.
     void cleanup(); // will be called after every testfunction.
 
-  public slots:
-    void testWidgetCrash();
+  private slots:
     void testWidget();
+    void testWidgetCrash();
     void testWidgetInvalid();
-  private slots:
     void testCodeEditorApis();
 
   private:
@@ -120,12 +119,8 @@ void TestQgsQueryResultWidget::testWidget()
   // For interactive testing
   //d->exec();
   w->executeQuery();
-  QTimer::singleShot( 1, d.get(), [ = ]
-  {
-    QTest::mousePress( w->mStopButton, Qt::MouseButton::LeftButton );
-  } );
   bool exited = false;
-  connect( w->mApiFetcher, &QgsConnectionsApiFetcher::fetchingFinished, d.get(), [ & ] { exited = true; } );
+  connect( w, &QgsQueryResultWidget::columnNamesReady, d.get(), [ & ] { exited = true; } );
   while ( ! exited )
     QgsApplication::processEvents();
   const auto rowCount { w->mModel->rowCount( w->mModel->index( -1, -1 ) ) };
@@ -148,6 +143,16 @@ void TestQgsQueryResultWidget::testCodeEditorApis()
   QVERIFY( w->mSqlEditor->fieldNames().contains( QStringLiteral( "qgis_test" ) ) );
   QVERIFY( w->mSqlEditor->fieldNames().contains( QStringLiteral( "random_big_data" ) ) );
   QVERIFY( w->mSqlEditor->fieldNames().contains( QStringLiteral( "descr" ) ) );
+
+  // Test feedback interrupt
+  w = qgis::make_unique<QgsQueryResultWidget>( nullptr, makeConn() );
+  QTimer::singleShot( 0, w.get(), [ & ]
+  {
+    QTest::mousePress( w->mStopButton, Qt::MouseButton::LeftButton );
+  } );
+  connect( w->mApiFetcher, &QgsConnectionsApiFetcher::fetchingFinished, w.get(), [ & ] { exited = true; } );
+  while ( ! exited )
+    QgsApplication::processEvents();
 }