Query result widget files and tests

qgis · Jul 6, 2021 · cc709a0 · cc709a0
1 parent 079264e
commit cc709a0
Show file tree

Hide file tree

Showing 4 changed files with 418 additions and 5 deletions.
diff --git a/python/gui/auto_generated/qgsqueryresultwidget.sip.in b/python/gui/auto_generated/qgsqueryresultwidget.sip.in
@@ -34,19 +34,19 @@ fetching can be interrupted by pressing the "Stop" push button.
 %End
   public:
 
-    QgsQueryResultWidget( QWidget *parent = 0, QgsAbstractDatabaseProviderConnection* connection /Transfer/ = 0 );
+    QgsQueryResultWidget( QWidget *parent = 0, QgsAbstractDatabaseProviderConnection *connection /Transfer/ = 0 );
 %Docstring
 Creates a QgsQueryResultWidget with the given ``connection``, ownership is transferred to the widget.
 %End
 
     virtual ~QgsQueryResultWidget();
 
-    void setConnection(QgsAbstractDatabaseProviderConnection* connection);
+    void setConnection( QgsAbstractDatabaseProviderConnection *connection );
 %Docstring
 Set the connection to ``connection``, ownership is transferred to the widget.
 %End
 
-    void setQuery( const QString& sql );
+    void setQuery( const QString &sql );
 %Docstring
 Convenience method to set the SQL editor test to ``sql``.
 %End
@@ -63,12 +63,12 @@ Executes the query
 Updates buttons status
 %End
 
-    void showError( const QString &title, const QString &message);
+    void showError( const QString &title, const QString &message );
 %Docstring
 Hides the result table and shows the error ``title`` and ``message`` in the message bar.
 %End
 
-    void tokensReady(const QStringList &tokens );
+    void tokensReady( const QStringList &tokens );
 %Docstring
 Triggered when the threaded API fetcher has new ``tokens`` to add.
 %End

diff --git a/src/gui/qgsqueryresultwidget.cpp b/src/gui/qgsqueryresultwidget.cpp
@@ -0,0 +1,216 @@
+/***************************************************************************
+  qgsqueryresultwidget.cpp - QgsQueryResultWidget
+
+ ---------------------
+ begin                : 14.1.2021
+ copyright            : (C) 2021 by ale
+ email                : [your-email-here]
+ ***************************************************************************
+ *                                                                         *
+ *   This program is free software; you can redistribute it and/or modify  *
+ *   it under the terms of the GNU General Public License as published by  *
+ *   the Free Software Foundation; either version 2 of the License, or     *
+ *   (at your option) any later version.                                   *
+ *                                                                         *
+ ***************************************************************************/
+#include "qgsqueryresultwidget.h"
+#include "qgsabstractdatabaseproviderconnection.h"
+#include "qgscodeeditorsql.h"
+#include "qgsmessagelog.h"
+#include <QPushButton>
+
+QgsQueryResultWidget::QgsQueryResultWidget( QWidget *parent, QgsAbstractDatabaseProviderConnection *connection )
+  : QWidget::QWidget( parent )
+{
+  setupUi( this );
+
+  connect( mExecuteButton, &QPushButton::pressed, this, &QgsQueryResultWidget::executeQuery );
+  connect( mCodeEditor, &QgsCodeEditorSQL::textChanged, this, [ = ] { updateButtons(); } );
+
+  mStatusLabel->hide();
+  setConnection( connection );
+}
+
+QgsQueryResultWidget::~QgsQueryResultWidget()
+{
+  if ( mApiFetcher )
+  {
+    mApiFetcher->stopFetching();
+    mWorkerThread.quit();
+    mWorkerThread.wait();
+  }
+}
+
+void QgsQueryResultWidget::executeQuery()
+{
+  if ( mConnection )
+  {
+    const auto sql = mCodeEditor->text( );
+    try
+    {
+      mWasCanceled = false;
+      mFeedback = qgis::make_unique<QgsFeedback>();
+      connect( mStopButton, &QPushButton::pressed, mFeedback.get(), &QgsFeedback::cancel );
+      mModel = qgis::make_unique<QgsQueryResultModel>( mConnection->execSql( sql, mFeedback.get() ) );
+      connect( mFeedback.get(), &QgsFeedback::canceled, mModel.get(), [ = ]
+      {
+        mModel->cancel();
+        mWasCanceled = true;
+      } );
+      mStatusLabel->show();
+      mQueryResultsTableView->show();
+      mStatusLabel->setText( tr( "Running⋯" ) );
+      connect( mModel.get(), &QgsQueryResultModel::rowsInserted, this, [ = ]( const QModelIndex &, int, int )
+      {
+        mStatusLabel->setText( tr( "Fetched rows: %1 %2" )
+                               .arg( mModel->rowCount( mModel->index( -1, -1 ) ) )
+                               .arg( mWasCanceled ? QStringLiteral( "(stopped)" ) : QString() ) );
+      } );
+      mQueryResultsTableView->setModel( mModel.get() );
+      mStopButton->setEnabled( true );
+      connect( mModel.get(), &QgsQueryResultModel::fetchingComplete, mStopButton, [ = ]
+      {
+        mStopButton->setEnabled( false );
+        if ( ! mWasCanceled )
+        {
+          mStatusLabel->setText( "Query executed successfully." );
+        }
+      } );
+    }
+    catch ( QgsProviderConnectionException &ex )
+    {
+      showError( tr( "SQL error" ), ex.what() );
+    }
+  }
+  else
+  {
+    showError( tr( "Connection error" ), tr( "Cannot execute query: connection to the database not set." ) );
+  }
+}
+
+void QgsQueryResultWidget::updateButtons()
+{
+  mExecuteButton->setEnabled( ! mCodeEditor->text().isEmpty() );
+}
+
+void QgsQueryResultWidget::showError( const QString &title, const QString &message )
+{
+  mStatusLabel->show();
+  mStatusLabel->setText( tr( "There was an error executing the query." ) );
+  mMessageBar->pushCritical( title, message );
+}
+
+void QgsQueryResultWidget::tokensReady( const QStringList &tokens )
+{
+  mCodeEditor->setFieldNames( mCodeEditor->fieldNames() + tokens );
+}
+
+void QgsQueryResultWidget::setConnection( QgsAbstractDatabaseProviderConnection *connection )
+{
+  mConnection.reset( connection );
+
+  if ( mApiFetcher )
+  {
+    mApiFetcher->stopFetching();
+    mWorkerThread.quit();
+    mWorkerThread.wait();
+    mApiFetcher->deleteLater();
+    mApiFetcher = nullptr;
+  }
+
+  if ( connection )
+  {
+    mCodeEditor->setFieldNames( QStringList( ) );
+    mApiFetcher = new QgsConnectionsApiFetcher( connection );
+    mApiFetcher->moveToThread( &mWorkerThread );
+    connect( &mWorkerThread, &QThread::started, mApiFetcher, &QgsConnectionsApiFetcher::fetchTokens );
+    connect( &mWorkerThread, &QThread::finished, mApiFetcher, [ = ]
+    {
+      mApiFetcher->deleteLater();
+      mApiFetcher = nullptr;
+    } );
+    connect( mApiFetcher, &QgsConnectionsApiFetcher::tokensReady, this, &QgsQueryResultWidget::tokensReady );
+    mWorkerThread.start();
+  }
+}
+
+void QgsQueryResultWidget::setQuery( const QString &sql )
+{
+  mCodeEditor->setText( sql );
+}
+
+///@cond private
+
+void QgsConnectionsApiFetcher::fetchTokens()
+{
+  if ( ! mStopFetching && mConnection )
+  {
+    QStringList schemas;
+    if ( mConnection->capabilities().testFlag( QgsAbstractDatabaseProviderConnection::Capability::Schemas ) )
+    {
+      try
+      {
+        schemas = mConnection->schemas();
+        emit tokensReady( schemas );
+      }
+      catch ( QgsProviderConnectionException &ex )
+      {
+        QgsMessageLog::logMessage( tr( "Error retrieving schemas: %1" ).arg( ex.what() ) );
+      }
+    }
+    else
+    {
+      schemas.push_back( QString() );  // Fake empty schema for DBs not supporting it
+    }
+
+    for ( const auto &schema : qgis::as_const( schemas ) )
+    {
+      if ( mStopFetching ) { return; }
+      QStringList tableNames;
+      try
+      {
+        const auto tables = mConnection->tables( schema );
+        for ( const auto &table : qgis::as_const( tables ) )
+        {
+          if ( mStopFetching ) { return; }
+          tableNames.push_back( table.tableName() );
+        }
+        emit tokensReady( tableNames );
+      }
+      catch ( QgsProviderConnectionException &ex )
+      {
+        QgsMessageLog::logMessage( tr( "Error retrieving tables: %1" ).arg( ex.what() ) );
+      }
+
+      // Get fields
+      for ( const auto &table : qgis::as_const( tableNames ) )
+      {
+        if ( mStopFetching ) { return; }
+        QStringList fieldNames;
+        try
+        {
+          const auto fields( mConnection->fields( schema, table ) );
+          if ( mStopFetching ) { return; }
+          for ( const auto &field : qgis::as_const( fields ) )
+          {
+            fieldNames.push_back( field.name() );
+            if ( mStopFetching ) { return; }
+          }
+          emit tokensReady( fieldNames );
+        }
+        catch ( QgsProviderConnectionException &ex )
+        {
+          QgsMessageLog::logMessage( tr( "Error retrieving fields for table %1: %2" ).arg( table, ex.what() ) );
+        }
+      }
+    }
+  }
+  emit fetchingFinished();
+}
+
+void QgsConnectionsApiFetcher::stopFetching()
+{
+  mStopFetching = 1;
+}
+
+///@endcond private
diff --git a/src/gui/qgsqueryresultwidget.h b/src/gui/qgsqueryresultwidget.h
@@ -0,0 +1,143 @@
+/***************************************************************************
+  qgsqueryresultwidget.h - QgsQueryResultWidget
+
+ ---------------------
+ begin                : 14.1.2021
+ copyright            : (C) 2021 by elpaso
+ email                : elpaso@itopen.it
+ ***************************************************************************
+ *                                                                         *
+ *   This program is free software; you can redistribute it and/or modify  *
+ *   it under the terms of the GNU General Public License as published by  *
+ *   the Free Software Foundation; either version 2 of the License, or     *
+ *   (at your option) any later version.                                   *
+ *                                                                         *
+ ***************************************************************************/
+#ifndef QGSQUERYRESULTWIDGET_H
+#define QGSQUERYRESULTWIDGET_H
+
+#include "qgis_gui.h"
+#include "qgis_sip.h"
+#include "ui_qgsqueryresultwidgetbase.h"
+#include "qgsabstractdatabaseproviderconnection.h"
+#include "qgsqueryresultmodel.h"
+#include "qgsfeedback.h"
+
+#include <QWidget>
+#include <QThread>
+
+
+///@cond private
+
+#ifndef SIP_RUN
+
+/**
+ * The QgsConnectionsApiFetcher class fetches tokens (table and field names) of a connection from a separate thread.
+ */
+class GUI_EXPORT QgsConnectionsApiFetcher: public QObject
+{
+    Q_OBJECT
+
+  public:
+
+    //! Constructs a result fetcher from \a queryResult
+    QgsConnectionsApiFetcher( const QgsAbstractDatabaseProviderConnection *conn )
+      : mConnection( conn )
+    {}
+
+    //! Start fetching
+    void fetchTokens();
+
+    //! Stop fetching
+    void stopFetching();
+
+  signals:
+
+    //!! Emitted when \a newTokens have been fetched
+    void tokensReady( const QStringList &newTokens );
+
+    //! Emitted when fetching of tokes has finished or has been interrupted.
+    void fetchingFinished();
+
+  private:
+
+    const QgsAbstractDatabaseProviderConnection *mConnection;
+    QAtomicInt mStopFetching = 0;
+
+};
+
+#endif
+
+///@endcond private
+
+/**
+ * The QgsQueryResultWidget class allow users to enter and run an SQL query on a
+ * DB connection (an instance of QgsAbstractDatabaseProviderConnection).
+ *
+ * Query results are displayed in a table view.
+ * Query execution and result
+ * fetching can be interrupted by pressing the "Stop" push button.
+ *
+ * \note the ownership of the connection is transferred to the widget.
+ *
+ * \since QGIS 3.20
+ */
+class GUI_EXPORT QgsQueryResultWidget: public QWidget, private Ui::QgsQueryResultWidgetBase
+{
+    Q_OBJECT
+
+  public:
+
+    /**
+     * Creates a QgsQueryResultWidget with the given \a connection, ownership is transferred to the widget.
+     */
+    QgsQueryResultWidget( QWidget *parent = nullptr, QgsAbstractDatabaseProviderConnection *connection SIP_TRANSFER = nullptr );
+
+    virtual ~QgsQueryResultWidget();
+
+    /**
+     * Set the connection to \a connection, ownership is transferred to the widget.
+     */
+    void setConnection( QgsAbstractDatabaseProviderConnection *connection );
+
+    /**
+     * Convenience method to set the SQL editor test to \a sql.
+     */
+    void setQuery( const QString &sql );
+
+  public slots:
+
+    /**
+     * Executes the query
+     */
+    void executeQuery();
+
+    /**
+     * Updates buttons status
+     */
+    void updateButtons();
+
+    /**
+     * Hides the result table and shows the error \a title and \a message in the message bar.
+     */
+    void showError( const QString &title, const QString &message );
+
+    /**
+     * Triggered when the threaded API fetcher has new \a tokens to add.
+     */
+    void tokensReady( const QStringList &tokens );
+
+  private:
+
+    std::unique_ptr<QgsAbstractDatabaseProviderConnection> mConnection;
+    std::unique_ptr<QgsQueryResultModel> mModel;
+    std::unique_ptr<QgsFeedback> mFeedback;
+    QgsConnectionsApiFetcher *mApiFetcher = nullptr;
+    QThread mWorkerThread;
+    bool mWasCanceled = false;
+
+    friend class TestQgsQueryResultWidget;
+
+};
+
+#endif // QGSQUERYRESULTWIDGET_H