Add a history provider for executed SQL commands

This history provider stores all executed database SQL commands in the local history db (ie commands executed through the browser panel, "Execute SQL" context menu action) The connection details, runtime and affected rows are stored
qgis · Apr 26, 2023 · 6ddae04 · 6ddae04
1 parent c1045a2
commit 6ddae04
Show file tree

Hide file tree

Showing 8 changed files with 299 additions and 2 deletions.
diff --git a/src/app/browser/qgsinbuiltdataitemproviders.cpp b/src/app/browser/qgsinbuiltdataitemproviders.cpp
@@ -61,6 +61,8 @@
 #include "qgsrelationshipsitem.h"
 #include "qgsprovidersqlquerybuilder.h"
 #include "qgsdbrelationshipwidget.h"
+#include "qgsdbqueryhistoryprovider.h"
+#include "qgshistoryproviderregistry.h"
 
 #include <QFileInfo>
 #include <QMenu>
@@ -1642,6 +1644,14 @@ QWidget *QgsFieldItemGuiProvider::createParamWidget( QgsDataItem *item, QgsDataI
   return nullptr;
 }
 
+QgsDatabaseItemGuiProvider::QgsDatabaseItemGuiProvider()
+{
+  if ( QgsDatabaseQueryHistoryProvider *historyProvider = qobject_cast< QgsDatabaseQueryHistoryProvider * >( QgsGui::historyProviderRegistry()->providerById( QStringLiteral( "dbquery" ) ) ) )
+  {
+    connect( historyProvider, &QgsDatabaseQueryHistoryProvider::openSqlDialog, this, &QgsDatabaseItemGuiProvider::openSqlDialogGeneric );
+  }
+}
+
 QString QgsDatabaseItemGuiProvider::name()
 {
   return QStringLiteral( "database" );

diff --git a/src/app/browser/qgsinbuiltdataitemproviders.h b/src/app/browser/qgsinbuiltdataitemproviders.h
@@ -282,7 +282,7 @@ class QgsDatabaseItemGuiProvider : public QObject, public QgsDataItemGuiProvider
 
   public:
 
-    QgsDatabaseItemGuiProvider() = default;
+    QgsDatabaseItemGuiProvider();
 
     QString name() override;
 

diff --git a/src/gui/CMakeLists.txt b/src/gui/CMakeLists.txt
@@ -507,6 +507,7 @@ set(QGIS_GUI_SRCS
   qgsdataitemguiproviderregistry.cpp
   qgsdatumtransformdialog.cpp
   qgsdatasourceselectdialog.cpp
+  qgsdbqueryhistoryprovider.cpp
   qgsdbrelationshipwidget.cpp
   qgsdetaileditemdata.cpp
   qgsdetaileditemdelegate.cpp
@@ -775,6 +776,7 @@ set(QGIS_GUI_HDRS
   qgsdataitemguiproviderregistry.h
   qgsdatasourcemanagerdialog.h
   qgsdatasourceselectdialog.h
+  qgsdbqueryhistoryprovider.h
   qgsdbrelationshipwidget.h
   qgsnewdatabasetablenamewidget.h
   qgsdatumtransformdialog.h

diff --git a/src/gui/history/qgshistoryproviderregistry.cpp b/src/gui/history/qgshistoryproviderregistry.cpp
@@ -22,6 +22,7 @@
 #include "qgsxmlutils.h"
 #include "qgsprocessinghistoryprovider.h"
 #include "qgshistoryentry.h"
+#include "qgsdbqueryhistoryprovider.h"
 
 #include <QFile>
 #include <sqlite3.h>
@@ -59,6 +60,7 @@ QgsHistoryProviderRegistry::~QgsHistoryProviderRegistry()
 void QgsHistoryProviderRegistry::addDefaultProviders()
 {
   addProvider( new QgsProcessingHistoryProvider() );
+  addProvider( new QgsDatabaseQueryHistoryProvider() );
 }
 
 bool QgsHistoryProviderRegistry::addProvider( QgsAbstractHistoryProvider *provider )

diff --git a/src/gui/qgsdbqueryhistoryprovider.cpp b/src/gui/qgsdbqueryhistoryprovider.cpp
@@ -0,0 +1,203 @@
+/***************************************************************************
+                            qgsdbqueryhistoryprovider.cpp
+                            --------------------------
+    begin                : April 2023
+    copyright            : (C) 2023 by Nyall Dawson
+    email                : nyall dot dawson at gmail dot com
+ ***************************************************************************/
+/***************************************************************************
+ *                                                                         *
+ *   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 "qgsdbqueryhistoryprovider.h"
+#include "qgscodeeditorsql.h"
+#include "qgshistoryentry.h"
+#include "qgsprovidermetadata.h"
+#include "qgsproviderregistry.h"
+
+#include <QIcon>
+
+class QgsDatabaseQueryValueNode;
+
+///@cond PRIVATE
+class DatabaseQueryHistoryNode : public QgsHistoryEntryGroup
+{
+  public:
+
+    DatabaseQueryHistoryNode( const QgsHistoryEntry &entry, QgsDatabaseQueryHistoryProvider *provider )
+      : QgsHistoryEntryGroup()
+      , mEntry( entry )
+      , mProvider( provider )
+    {
+    }
+
+  protected:
+
+    QgsHistoryEntry mEntry;
+    QgsDatabaseQueryValueNode *mConnectionNode = nullptr;
+    QgsDatabaseQueryValueNode *mRowsNode = nullptr;
+    QgsDatabaseQueryValueNode *mTimeNode = nullptr;
+    QgsDatabaseQueryHistoryProvider *mProvider = nullptr;
+
+};
+
+class DatabaseQueryValueNode : public DatabaseQueryHistoryNode
+{
+  public:
+
+    DatabaseQueryValueNode( const QgsHistoryEntry &entry, QgsDatabaseQueryHistoryProvider *provider, const QString &value )
+      : DatabaseQueryHistoryNode( entry, provider )
+      , mValue( value )
+    {}
+
+    QVariant data( int role = Qt::DisplayRole ) const override
+    {
+      switch ( role )
+      {
+        case Qt::DisplayRole:
+        case Qt::ToolTipRole:
+          return mValue;
+
+        default:
+          return QVariant();
+      }
+    }
+
+    QString html( const QgsHistoryWidgetContext & ) const override
+    {
+      return mValue;
+    }
+
+  private:
+
+    QString mValue;
+
+};
+
+class DatabaseQueryRootNode : public DatabaseQueryHistoryNode
+{
+  public:
+
+    DatabaseQueryRootNode( const QgsHistoryEntry &entry, QgsDatabaseQueryHistoryProvider *provider )
+      : DatabaseQueryHistoryNode( entry, provider )
+    {
+      setEntry( entry );
+
+      mProviderKey = mEntry.entry.value( QStringLiteral( "provider" ) ).toString();
+    }
+
+    QVariant data( int role = Qt::DisplayRole ) const override
+    {
+      switch ( role )
+      {
+        case Qt::DisplayRole:
+        case Qt::ToolTipRole:
+          return mEntry.entry.value( QStringLiteral( "query" ) );
+
+        case Qt::DecorationRole:
+        {
+          if ( !mProviderIcon.isNull() )
+            return mProviderIcon;
+
+          if ( QgsProviderMetadata *md = QgsProviderRegistry::instance()->providerMetadata( mProviderKey ) )
+          {
+            mProviderIcon = md->icon();
+          }
+          return mProviderIcon;
+        }
+
+        default:
+          break;
+      }
+      return QVariant();
+    }
+
+    void setEntry( const QgsHistoryEntry &entry )
+    {
+      if ( !mConnectionNode )
+      {
+        mConnectionNode = new DatabaseQueryValueNode( mEntry, mProvider, QStringLiteral( "Connection: %1" ).arg( entry.entry.value( QStringLiteral( "connection" ) ).toString() ) );
+        addChild( mConnectionNode );
+      }
+      if ( entry.entry.contains( QStringLiteral( "rows" ) ) )
+      {
+        if ( !mRowsNode )
+        {
+          mRowsNode = new DatabaseQueryValueNode( mEntry, mProvider, QStringLiteral( "Row count: %1" ).arg( entry.entry.value( QStringLiteral( "rows" ) ).toString() ) );
+          addChild( mRowsNode );
+        }
+      }
+      if ( entry.entry.contains( QStringLiteral( "time" ) ) )
+      {
+        if ( !mTimeNode )
+        {
+          mTimeNode = new DatabaseQueryValueNode( mEntry, mProvider, QStringLiteral( "Execution time: %1 ms" ).arg( entry.entry.value( QStringLiteral( "time" ) ).toString() ) );
+          addChild( mTimeNode );
+        }
+      }
+    }
+
+    QWidget *createWidget( const QgsHistoryWidgetContext & ) override
+    {
+      QgsCodeEditorSQL *editor = new QgsCodeEditorSQL();
+      editor->setText( mEntry.entry.value( QStringLiteral( "query" ) ).toString() );
+      editor->setReadOnly( true );
+      editor->setCaretLineVisible( false );
+      editor->setLineNumbersVisible( false );
+      editor->setFoldingVisible( false );
+      editor->setEdgeMode( QsciScintilla::EdgeNone );
+      editor->setWrapMode( QsciScintilla::WrapMode::WrapWord );
+      return editor;
+    }
+
+    void doubleClicked( const QgsHistoryWidgetContext & ) override
+    {
+      mProvider->emitOpenSqlDialog( mEntry.entry.value( QStringLiteral( "connection" ) ).toString(),
+                                    mEntry.entry.value( QStringLiteral( "provider" ) ).toString(),
+                                    mEntry.entry.value( QStringLiteral( "query" ) ).toString() );
+    }
+
+  private:
+
+    QString mProviderKey;
+    mutable QIcon mProviderIcon;
+    DatabaseQueryValueNode *mConnectionNode = nullptr;
+    DatabaseQueryValueNode *mRowsNode = nullptr;
+    DatabaseQueryValueNode *mTimeNode = nullptr;
+
+};
+
+///@endcond
+
+
+QgsDatabaseQueryHistoryProvider::QgsDatabaseQueryHistoryProvider()
+{
+}
+
+QString QgsDatabaseQueryHistoryProvider::id() const
+{
+  return QStringLiteral( "dbquery" );
+}
+
+QgsHistoryEntryNode *QgsDatabaseQueryHistoryProvider::createNodeForEntry( const QgsHistoryEntry &entry, const QgsHistoryWidgetContext & )
+{
+  return new DatabaseQueryRootNode( entry, this );
+}
+
+void QgsDatabaseQueryHistoryProvider::updateNodeForEntry( QgsHistoryEntryNode *node, const QgsHistoryEntry &entry, const QgsHistoryWidgetContext & )
+{
+  if ( DatabaseQueryRootNode *dbNode = dynamic_cast< DatabaseQueryRootNode * >( node ) )
+  {
+    dbNode->setEntry( entry );
+  }
+}
+
+void QgsDatabaseQueryHistoryProvider::emitOpenSqlDialog( const QString &connectionUri, const QString &provider, const QString &sql )
+{
+  emit openSqlDialog( connectionUri, provider, sql );
+}
diff --git a/src/gui/qgsdbqueryhistoryprovider.h b/src/gui/qgsdbqueryhistoryprovider.h
@@ -0,0 +1,59 @@
+/***************************************************************************
+                            qgsdbqueryhistoryprovider.h
+                            --------------------------
+    begin                : April 2023
+    copyright            : (C) 2023 by Nyall Dawson
+    email                : nyall dot dawson at gmail dot com
+ ***************************************************************************/
+/***************************************************************************
+ *                                                                         *
+ *   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 QGSDBQUERYHISTORYPROVIDER_H
+#define QGSDBQUERYHISTORYPROVIDER_H
+
+#include "qgis_gui.h"
+#include "qgis_sip.h"
+
+#include "qgshistoryprovider.h"
+#include "qgshistoryentrynode.h"
+
+#define SIP_SKIP
+
+/**
+ * History provider for operations database queries.
+ *
+ * \ingroup gui
+ *
+ * \note Not available in Python bindings
+ * \since QGIS 3.32
+ */
+class GUI_EXPORT QgsDatabaseQueryHistoryProvider : public QgsAbstractHistoryProvider
+{
+    Q_OBJECT
+
+  public:
+
+    QgsDatabaseQueryHistoryProvider();
+
+    QString id() const override;
+
+    QgsHistoryEntryNode *createNodeForEntry( const QgsHistoryEntry &entry, const QgsHistoryWidgetContext &context ) override SIP_FACTORY;
+    void updateNodeForEntry( QgsHistoryEntryNode *node, const QgsHistoryEntry &entry, const QgsHistoryWidgetContext &context ) override;
+
+    void emitOpenSqlDialog( const QString &connectionUri, const QString &provider, const QString &sql );
+
+  signals:
+
+    void openSqlDialog( const QString &connectionUri, const QString &provider, const QString &sql );
+
+};
+
+#endif //QGSDBQUERYHISTORYPROVIDER_H
+
+
+
diff --git a/src/gui/qgsqueryresultwidget.cpp b/src/gui/qgsqueryresultwidget.cpp
@@ -21,6 +21,9 @@
 #include "qgsquerybuilder.h"
 #include "qgsvectorlayer.h"
 #include "qgsapplication.h"
+#include "qgsgui.h"
+#include "qgshistoryproviderregistry.h"
+#include "qgshistoryentry.h"
 
 #include <QClipboard>
 #include <QShortcut>
@@ -165,11 +168,20 @@ void QgsQueryResultWidget::executeQuery()
   mFirstRowFetched = false;
 
   cancelRunningQuery();
-
   if ( mConnection )
   {
     const QString sql { mSqlEditor->text( ) };
 
+    bool ok = false;
+    mCurrentHistoryEntryId = QgsGui::historyProviderRegistry()->addEntry( QStringLiteral( "dbquery" ),
+                             QVariantMap
+    {
+      { QStringLiteral( "query" ), sql },
+      { QStringLiteral( "provider" ), mConnection->providerKey() },
+      { QStringLiteral( "connection" ), mConnection->uri() },
+    },
+    ok );
+
     mWasCanceled = false;
     mFeedback = std::make_unique<QgsFeedback>();
     mStopButton->setEnabled( true );
@@ -390,6 +402,14 @@ void QgsQueryResultWidget::startFetching()
 
       connect( mModel.get(), &QgsQueryResultModel::fetchingComplete, mStopButton, [ = ]
       {
+        bool ok = false;
+        const QgsHistoryEntry currentHistoryEntry = QgsGui::historyProviderRegistry()->entry( mCurrentHistoryEntryId, ok );
+        QVariantMap entryDetails = currentHistoryEntry.entry;
+        entryDetails.insert( QStringLiteral( "rows" ), mActualRowCount );
+        entryDetails.insert( QStringLiteral( "time" ), mQueryResultWatcher.result().queryExecutionTime() );
+
+        QgsGui::historyProviderRegistry()->updateEntry( mCurrentHistoryEntryId,
+            entryDetails );
         mProgressBar->hide();
         mStopButton->setEnabled( false );
       } );

diff --git a/src/gui/qgsqueryresultwidget.h b/src/gui/qgsqueryresultwidget.h
@@ -226,6 +226,7 @@ class GUI_EXPORT QgsQueryResultWidget: public QWidget, private Ui::QgsQueryResul
     long long mActualRowCount = -1;
     long long mFetchedRowsBatchCount = 0;
     QueryWidgetMode mQueryWidgetMode = QueryWidgetMode::SqlQueryMode;
+    long long mCurrentHistoryEntryId = -1;
 
     /**
      * Updates SQL layer columns.