SQL results widget: add query layers support (initial)

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

Hide file tree

Showing 16 changed files with 421 additions and 61 deletions.
diff --git a/python/core/auto_generated/providers/qgsabstractdatabaseproviderconnection.sip.in b/python/core/auto_generated/providers/qgsabstractdatabaseproviderconnection.sip.in
@@ -121,6 +121,14 @@ Returns the number of fetched rows.
 
     };
 
+    struct SqlVectorLayerOptions
+    {
+      QString sql; //! The SQL expression that defines the SQL (query) layer
+      QString filter; //! Additional subset string (provider-side filter), not all data providers support this feature: check support with SqlLayerFilters capability
+      QString layerName; //! Optional name for the new layer
+      QStringList primaryKeyColumns; //! List of primary key column names
+      QString geometryColumn; //! Name of the geometry column
+    };
 
     struct TableProperty
     {
@@ -294,6 +302,11 @@ This information is calculated from the geometry columns types.
 
     };
 
+    struct SpatialIndexOptions
+    {
+      QString geometryColumnName;
+    };
+
     enum Capability
     {
       CreateVectorTable,
@@ -317,6 +330,7 @@ This information is calculated from the geometry columns types.
       DeleteField,
       DeleteFieldCascade,
       AddField,
+      SqlLayerFilters,
     };
     typedef QFlags<QgsAbstractDatabaseProviderConnection::Capability> Capabilities;
 
@@ -486,7 +500,6 @@ Adds a field.
 .. versionadded:: 3.16
 %End
 
-
     virtual void renameSchema( const QString &name, const QString &newName ) const throw( QgsProviderConnectionException );
 %Docstring
 Renames a schema with the specified ``name``.
@@ -506,6 +519,16 @@ Executes raw ``sql`` and returns the (possibly empty) list of results in a multi
 .. seealso:: :py:func:`execSql`
 
 :raises QgsProviderConnectionException: if any errors are encountered.
+%End
+
+    virtual QgsVectorLayer *createSqlVectorLayer( const SqlVectorLayerOptions &options ) const throw( QgsProviderConnectionException ) /Factory/;
+%Docstring
+Creates and returns a vector layer based on the ``sql`` statement and optional ``options``.
+Raises a :py:class:`QgsProviderConnectionException` if any errors are encountered or if SQL layer creation is not supported.
+
+:raises :: py:class:`QgsProviderConnectionException`
+
+.. versionadded:: 3.20
 %End
 
     virtual QueryResult execSql( const QString &sql, QgsFeedback *feedback = 0 ) const throw( QgsProviderConnectionException );
@@ -526,11 +549,6 @@ Vacuum the database table with given ``schema`` and ``name`` (schema is ignored
 :raises QgsProviderConnectionException: if any errors are encountered.
 %End
 
-    struct SpatialIndexOptions
-    {
-      QString geometryColumnName;
-    };
-
     virtual void createSpatialIndex( const QString &schema, const QString &name, const QgsAbstractDatabaseProviderConnection::SpatialIndexOptions &options = QgsAbstractDatabaseProviderConnection::SpatialIndexOptions() ) const throw( QgsProviderConnectionException );
 %Docstring
 Creates a spatial index for the database table with given ``schema`` and ``name`` (schema is ignored if not supported by the backend).

diff --git a/python/core/auto_generated/qgsqueryresultmodel.sip.in b/python/core/auto_generated/qgsqueryresultmodel.sip.in
@@ -44,6 +44,11 @@ Constructs a QgsQueryResultModel from a ``queryResult`` with optional ``parent``
     virtual QVariant headerData( int section, Qt::Orientation orientation, int role ) const;
 
 
+    QStringList columns() const;
+%Docstring
+Returns the column names
+%End
+
   public slots:
 
     void rowsReady( const QList<QList<QVariant> > &rows );

diff --git a/python/gui/auto_generated/qgsqueryresultwidget.sip.in b/python/gui/auto_generated/qgsqueryresultwidget.sip.in
@@ -15,12 +15,11 @@
 class QgsQueryResultWidget: QWidget
 {
 %Docstring
-The QgsQueryResultWidget class allow users to enter and run an SQL query on a
-DB connection (an instance of QgsAbstractDatabaseProviderConnection).
+The :py:class:`QgsQueryResultWidget` class allow users to enter and run an SQL query on a
+DB connection (an instance of :py:class:`QgsAbstractDatabaseProviderConnection`).
 
 Query results are displayed in a table view.
-Query execution and result
-fetching can be interrupted by pressing the "Stop" push button.
+Query execution and result fetching can be interrupted by pressing the "Stop" push button.
 
 .. note::
 
@@ -41,26 +40,31 @@ Creates a QgsQueryResultWidget with the given ``connection``, ownership is trans
 
     virtual ~QgsQueryResultWidget();
 
-    void setConnection( QgsAbstractDatabaseProviderConnection *connection );
+    void setConnection( QgsAbstractDatabaseProviderConnection *connection /Transfer/ );
 %Docstring
 Set the connection to ``connection``, ownership is transferred to the widget.
 %End
 
     void setQuery( const QString &sql );
 %Docstring
-Convenience method to set the SQL editor test to ``sql``.
+Convenience method to set the SQL editor text to ``sql``.
 %End
 
-  public slots:
+    void setSqlVectorLayerOptions( const QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions &options );
+%Docstring
+Set the SQL layer ``options``. This method automatically populates and shows the "Load as new layer" panel.
+%End
 
-    void executeQuery();
+    QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions sqlVectorLayerOptions() const;
 %Docstring
-Executes the query
+Returns the sqlVectorLayerOptions
 %End
 
-    void updateButtons();
+  public slots:
+
+    void executeQuery();
 %Docstring
-Updates buttons status
+Executes the query
 %End
 
     void showError( const QString &title, const QString &message );
@@ -73,6 +77,19 @@ Hides the result table and shows the error ``title`` and ``message`` in the mess
 Triggered when the threaded API fetcher has new ``tokens`` to add.
 %End
 
+  signals:
+
+    void createSqlVectorLayer( const QString &providerKey, const QString &connectionUri, const QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions &options );
+%Docstring
+Emitted when a new vector SQL (query) layer must be created.
+
+:param providerKey: name of the data provider
+:param connectionUri: the connection URI as returned by :py:func:`QgsAbstractProviderConnection.uri()`
+:param options:
+%End
+
+    void columnNamesReady();
+
 };
 
 /************************************************************************

diff --git a/src/core/providers/ogr/qgsgeopackageproviderconnection.cpp b/src/core/providers/ogr/qgsgeopackageproviderconnection.cpp
@@ -358,6 +358,7 @@ void QgsGeoPackageProviderConnection::setDefaultCapabilities()
     Capability::DeleteField,
     Capability::AddField,
     Capability::DropRasterTable
+    Capability::SqlLayers
   };
   mGeometryColumnCapabilities =
   {

diff --git a/src/core/providers/ogr/qgsgeopackageproviderconnection.h b/src/core/providers/ogr/qgsgeopackageproviderconnection.h
@@ -82,7 +82,6 @@ class QgsGeoPackageProviderConnection : public QgsAbstractDatabaseProviderConnec
     //! Use GDAL to execute SQL
     QueryResult executeGdalSqlPrivate( const QString &sql, QgsFeedback *feedback = nullptr ) const;
 
-
 };
 
 

diff --git a/src/core/providers/qgsabstractdatabaseproviderconnection.cpp b/src/core/providers/qgsabstractdatabaseproviderconnection.cpp
@@ -158,6 +158,12 @@ void QgsAbstractDatabaseProviderConnection::createSpatialIndex( const QString &,
   checkCapability( Capability::CreateSpatialIndex );
 }
 
+QgsVectorLayer *QgsAbstractDatabaseProviderConnection::createSqlVectorLayer( const SqlVectorLayerOptions & ) const
+{
+  checkCapability( Capability::SqlLayers );
+  return nullptr;
+}
+
 void QgsAbstractDatabaseProviderConnection::deleteSpatialIndex( const QString &, const QString &, const QString & ) const
 {
   checkCapability( Capability::DeleteSpatialIndex );

diff --git a/src/core/providers/qgsabstractdatabaseproviderconnection.h b/src/core/providers/qgsabstractdatabaseproviderconnection.h
@@ -197,6 +197,20 @@ class CORE_EXPORT QgsAbstractDatabaseProviderConnection : public QgsAbstractProv
 
     };
 
+    /**
+     * \brief The SqlVectorLayerOptions stores all information required to create a SQL (query) layer.
+     * \see createSqlVectorLayer()
+     *
+     * \since QGIS 3.20
+     */
+    struct CORE_EXPORT SqlVectorLayerOptions
+    {
+      QString sql; //! The SQL expression that defines the SQL (query) layer
+      QString filter; //! Additional subset string (provider-side filter), not all data providers support this feature: check support with SqlLayerFilters capability
+      QString layerName; //! Optional name for the new layer
+      QStringList primaryKeyColumns; //! List of primary key column names
+      QString geometryColumn; //! Name of the geometry column
+    };
 
     /**
      * The TableProperty class represents a database table or view.
@@ -409,6 +423,17 @@ class CORE_EXPORT QgsAbstractDatabaseProviderConnection : public QgsAbstractProv
         QVariantMap                   mInfo;
     };
 
+    /**
+     * \brief The SpatialIndexOptions contains extra options relating to spatial index creation.
+     *
+     * \since QGIS 3.14
+     */
+    struct CORE_EXPORT SpatialIndexOptions
+    {
+      //! Specifies the name of the geometry column to create the index for
+      QString geometryColumnName;
+    };
+
     /**
      * The Capability enum represents the operations supported by the connection.
      */
@@ -435,6 +460,7 @@ class CORE_EXPORT QgsAbstractDatabaseProviderConnection : public QgsAbstractProv
       DeleteField = 1 << 19,        //!< Can delete an existing field/column
       DeleteFieldCascade = 1 << 20, //!< Can delete an existing field/column with cascade
       AddField = 1 << 21,           //!< Can add a new field/column
+      SqlLayerFilters = 1 << 22,    //!< SQL Layers support filters (subset strings), implies SqlLayers capability
     };
     Q_ENUM( Capability )
     Q_DECLARE_FLAGS( Capabilities, Capability )
@@ -585,7 +611,6 @@ class CORE_EXPORT QgsAbstractDatabaseProviderConnection : public QgsAbstractProv
      */
     virtual void addField( const QgsField &field, const QString &schema, const QString &tableName ) const SIP_THROW( QgsProviderConnectionException );
 
-
     /**
      * Renames a schema with the specified \a name.
      * Raises a QgsProviderConnectionException if any errors are encountered.
@@ -602,6 +627,14 @@ class CORE_EXPORT QgsAbstractDatabaseProviderConnection : public QgsAbstractProv
      */
     virtual QList<QList<QVariant>> executeSql( const QString &sql, QgsFeedback *feedback = nullptr ) const SIP_THROW( QgsProviderConnectionException );
 
+    /**
+     * Creates and returns a vector layer based on the \a sql statement and optional \a options.
+     * Raises a QgsProviderConnectionException if any errors are encountered or if SQL layer creation is not supported.
+     * \throws QgsProviderConnectionException
+     * \since QGIS 3.20
+     */
+    virtual QgsVectorLayer *createSqlVectorLayer( const SqlVectorLayerOptions &options ) const SIP_THROW( QgsProviderConnectionException ) SIP_FACTORY;
+
     /**
      * Executes raw \a sql and returns the (possibly empty) query results, optionally \a feedback can be provided.
      *
@@ -618,17 +651,6 @@ class CORE_EXPORT QgsAbstractDatabaseProviderConnection : public QgsAbstractProv
      */
     virtual void vacuum( const QString &schema, const QString &name ) const SIP_THROW( QgsProviderConnectionException );
 
-    /**
-     * Contains extra options relating to spatial index creation.
-     *
-     * \since QGIS 3.14
-     */
-    struct CORE_EXPORT SpatialIndexOptions
-    {
-      //! Specifies the name of the geometry column to create the index for
-      QString geometryColumnName;
-    };
-
     /**
      * Creates a spatial index for the database table with given \a schema and \a name (schema is ignored if not supported by the backend).
      *

diff --git a/src/core/qgsqueryresultmodel.cpp b/src/core/qgsqueryresultmodel.cpp
@@ -48,6 +48,11 @@ void QgsQueryResultModel::cancel()
   }
 }
 
+QStringList QgsQueryResultModel::columns() const
+{
+  return mColumns;
+}
+
 QgsQueryResultModel::~QgsQueryResultModel()
 {
   if ( mWorker )

diff --git a/src/core/qgsqueryresultmodel.h b/src/core/qgsqueryresultmodel.h
@@ -96,6 +96,9 @@ class CORE_EXPORT QgsQueryResultModel : public QAbstractTableModel
     QVariant data( const QModelIndex &index, int role ) const override;
     QVariant headerData( int section, Qt::Orientation orientation, int role ) const override;
 
+    //! Returns the column names
+    QStringList columns() const;
+
   public slots:
 
     /**