Skip to content

Commit

Permalink
Create SQL layer and widget config based on enum
Browse files Browse the repository at this point in the history
  • Loading branch information
@elpaso
elpaso committed Jul 6, 2021
1 parent af44d35 commit cf26022
Show file tree
Hide file tree
Showing 16 changed files with 216 additions and 113 deletions.
3 changes: 3 additions & 0 deletions python/core/auto_additions/qgsabstractdatabaseproviderconnection.py
View file
Expand Up @@ -8,3 +8,6 @@
QgsAbstractDatabaseProviderConnection.GeometryColumnCapability.baseClass = QgsAbstractDatabaseProviderConnection
QgsAbstractDatabaseProviderConnection.GeometryColumnCapabilities.baseClass = QgsAbstractDatabaseProviderConnection
GeometryColumnCapabilities = QgsAbstractDatabaseProviderConnection # dirty hack since SIP seems to introduce the flags in module
QgsAbstractDatabaseProviderConnection.SqlLayerDefinitionCapability.baseClass = QgsAbstractDatabaseProviderConnection
QgsAbstractDatabaseProviderConnection.SqlLayerDefinitionCapabilities.baseClass = QgsAbstractDatabaseProviderConnection
SqlLayerDefinitionCapabilities = QgsAbstractDatabaseProviderConnection # dirty hack since SIP seems to introduce the flags in module
22 changes: 19 additions & 3 deletions python/core/auto_generated/providers/qgsabstractdatabaseproviderconnection.sip.in
View file
Expand Up @@ -330,7 +330,6 @@ This information is calculated from the geometry columns types.
DeleteField,
DeleteFieldCascade,
AddField,
SqlLayerFilters,
};
typedef QFlags<QgsAbstractDatabaseProviderConnection::Capability> Capabilities;

Expand All @@ -346,6 +345,16 @@ This information is calculated from the geometry columns types.
typedef QFlags<QgsAbstractDatabaseProviderConnection::GeometryColumnCapability> GeometryColumnCapabilities;


enum SqlLayerDefinitionCapability
{
Filters,
GeometryColumn,
PrimaryKeys
};

typedef QFlags<QgsAbstractDatabaseProviderConnection::SqlLayerDefinitionCapability> SqlLayerDefinitionCapabilities;


QgsAbstractDatabaseProviderConnection( const QString &name );
%Docstring
Creates a new connection with ``name`` by reading its configuration from the settings.
Expand Down Expand Up @@ -376,6 +385,13 @@ Returns connection geometry column capabilities (Z, M, SinglePart, Curves).
.. versionadded:: 3.16
%End

virtual SqlLayerDefinitionCapabilities sqlLayerDefinitionCapabilities();
%Docstring
Returns SQL layer definition capabilities (Filters, GeometryColumn, PrimaryKeys).

.. versionadded:: 3.20
%End


virtual QString tableUri( const QString &schema, const QString &name ) const throw( QgsProviderConnectionException );
%Docstring
Expand Down Expand Up @@ -523,10 +539,10 @@ Executes raw ``sql`` and returns the (possibly empty) list of results in a multi

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``.
Creates and returns a (possibly invalid) 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`
:raises QgsProviderConnectionException:

.. versionadded:: 3.20
%End
Expand Down
4 changes: 2 additions & 2 deletions python/gui/auto_generated/qgsqueryresultwidget.sip.in
View file
Expand Up @@ -41,7 +41,7 @@ Creates a QgsQueryResultWidget with the given ``connection``, ownership is trans

void setConnection( QgsAbstractDatabaseProviderConnection *connection /Transfer/ );
%Docstring
Set the connection to ``connection``, ownership is transferred to the widget.
Sets the connection to ``connection``, ownership is transferred to the widget.
%End

void setQuery( const QString &sql );
Expand All @@ -51,7 +51,7 @@ Convenience method to set the SQL editor text to ``sql``.

void setSqlVectorLayerOptions( const QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions &options );
%Docstring
Set the SQL layer ``options``. This method automatically populates and shows the "Load as new layer" panel.
Sets the SQL layer ``options``. This method automatically populates and shows the "Load as new layer" panel.
%End

QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions sqlVectorLayerOptions() const;
Expand Down
5 changes: 5 additions & 0 deletions src/core/providers/ogr/qgsgeopackageproviderconnection.cpp
View file
Expand Up @@ -168,6 +168,11 @@ void QgsGeoPackageProviderConnection::renameVectorTable( const QString &schema,
}
}

QgsVectorLayer *QgsGeoPackageProviderConnection::createSqlVectorLayer( const QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions &options ) const
{
return new QgsVectorLayer( QStringLiteral( "%1|subset=%2" ).arg( uri(), options.sql ), options.layerName.isEmpty() ? QStringLiteral( "QueryLayer" ) : options.layerName, providerKey() );
}

QgsAbstractDatabaseProviderConnection::QueryResult QgsGeoPackageProviderConnection::execSql( const QString &sql, QgsFeedback *feedback ) const
{
checkCapability( Capability::ExecuteSql );
Expand Down
2 changes: 1 addition & 1 deletion src/core/providers/ogr/qgsgeopackageproviderconnection.h
View file
Expand Up @@ -66,6 +66,7 @@ class QgsGeoPackageProviderConnection : public QgsAbstractDatabaseProviderConnec
void dropVectorTable( const QString &schema, const QString &name ) const override;
void dropRasterTable( const QString &schema, const QString &name ) const override;
void renameVectorTable( const QString &schema, const QString &name, const QString &newName ) const override;
QgsVectorLayer *createSqlVectorLayer( const SqlVectorLayerOptions &options ) const override;
QueryResult execSql( const QString &sql, QgsFeedback *feedback = nullptr ) const override;
void vacuum( const QString &schema, const QString &name ) const override;
void createSpatialIndex( const QString &schema, const QString &name, const QgsAbstractDatabaseProviderConnection::SpatialIndexOptions &options = QgsAbstractDatabaseProviderConnection::SpatialIndexOptions() ) const override;
Expand All @@ -81,7 +82,6 @@ class QgsGeoPackageProviderConnection : public QgsAbstractDatabaseProviderConnec
void setDefaultCapabilities();
//! Use GDAL to execute SQL
QueryResult executeGdalSqlPrivate( const QString &sql, QgsFeedback *feedback = nullptr ) const;

};


Expand Down
5 changes: 5 additions & 0 deletions src/core/providers/qgsabstractdatabaseproviderconnection.cpp
View file
Expand Up @@ -43,6 +43,11 @@ QgsAbstractDatabaseProviderConnection::GeometryColumnCapabilities QgsAbstractDat
return mGeometryColumnCapabilities;
}

QgsAbstractDatabaseProviderConnection::SqlLayerDefinitionCapabilities QgsAbstractDatabaseProviderConnection::sqlLayerDefinitionCapabilities()
{
return mSqlLayerDefinitionCapabilities;
}

QString QgsAbstractDatabaseProviderConnection::tableUri( const QString &schema, const QString &name ) const
{
Q_UNUSED( schema )
Expand Down
63 changes: 40 additions & 23 deletions src/core/providers/qgsabstractdatabaseproviderconnection.h
View file
Expand Up @@ -439,28 +439,27 @@ class CORE_EXPORT QgsAbstractDatabaseProviderConnection : public QgsAbstractProv
*/
enum Capability
{
CreateVectorTable = 1 << 1, //!< Can CREATE a vector (or aspatial) table/layer
DropRasterTable = 1 << 2, //!< Can DROP a raster table/layer
DropVectorTable = 1 << 3, //!< Can DROP a vector (or aspatial) table/layer
RenameVectorTable = 1 << 4, //!< Can RENAME a vector (or aspatial) table/layer
RenameRasterTable = 1 << 5, //!< Can RENAME a raster table/layer
CreateSchema = 1 << 6, //!< Can CREATE a schema
DropSchema = 1 << 7, //!< Can DROP a schema
RenameSchema = 1 << 8, //!< Can RENAME a schema
ExecuteSql = 1 << 9, //!< Can execute raw SQL queries (without returning results)
Vacuum = 1 << 10, //!< Can run vacuum
Tables = 1 << 11, //!< Can list tables
Schemas = 1 << 12, //!< Can list schemas (if not set, the connection does not support schemas)
SqlLayers = 1 << 13, //!< Can create vector layers from SQL SELECT queries
TableExists = 1 << 14, //!< Can check if table exists
Spatial = 1 << 15, //!< The connection supports spatial tables
CreateSpatialIndex = 1 << 16, //!< The connection can create spatial indices
SpatialIndexExists = 1 << 17, //!< The connection can determine if a spatial index exists
DeleteSpatialIndex = 1 << 18, //!< The connection can delete spatial indices for tables
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
CreateVectorTable = 1 << 1, //!< Can CREATE a vector (or aspatial) table/layer
DropRasterTable = 1 << 2, //!< Can DROP a raster table/layer
DropVectorTable = 1 << 3, //!< Can DROP a vector (or aspatial) table/layer
RenameVectorTable = 1 << 4, //!< Can RENAME a vector (or aspatial) table/layer
RenameRasterTable = 1 << 5, //!< Can RENAME a raster table/layer
CreateSchema = 1 << 6, //!< Can CREATE a schema
DropSchema = 1 << 7, //!< Can DROP a schema
RenameSchema = 1 << 8, //!< Can RENAME a schema
ExecuteSql = 1 << 9, //!< Can execute raw SQL queries (without returning results)
Vacuum = 1 << 10, //!< Can run vacuum
Tables = 1 << 11, //!< Can list tables
Schemas = 1 << 12, //!< Can list schemas (if not set, the connection does not support schemas)
SqlLayers = 1 << 13, //!< Can create vector layers from SQL SELECT queries
TableExists = 1 << 14, //!< Can check if table exists
Spatial = 1 << 15, //!< The connection supports spatial tables
CreateSpatialIndex = 1 << 16, //!< The connection can create spatial indices
SpatialIndexExists = 1 << 17, //!< The connection can determine if a spatial index exists
DeleteSpatialIndex = 1 << 18, //!< The connection can delete spatial indices for tables
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
};
Q_ENUM( Capability )
Q_DECLARE_FLAGS( Capabilities, Capability )
Expand All @@ -483,6 +482,17 @@ class CORE_EXPORT QgsAbstractDatabaseProviderConnection : public QgsAbstractProv
Q_DECLARE_FLAGS( GeometryColumnCapabilities, GeometryColumnCapability )
Q_FLAG( GeometryColumnCapabilities )

enum SqlLayerDefinitionCapability
{
Filters = 1 << 1, //! SQL layer definition support filters
GeometryColumn = 1 << 2, //! SQL layer definition support geometry colum
PrimaryKeys = 1 << 3 //! SQL layer definition support primary keys
};

Q_ENUM( SqlLayerDefinitionCapability )
Q_DECLARE_FLAGS( SqlLayerDefinitionCapabilities, SqlLayerDefinitionCapability )
Q_FLAG( SqlLayerDefinitionCapabilities )

/**
* Creates a new connection with \a name by reading its configuration from the settings.
*
Expand Down Expand Up @@ -513,6 +523,12 @@ class CORE_EXPORT QgsAbstractDatabaseProviderConnection : public QgsAbstractProv
*/
virtual GeometryColumnCapabilities geometryColumnCapabilities();

/**
* Returns SQL layer definition capabilities (Filters, GeometryColumn, PrimaryKeys).
* \since QGIS 3.20
*/
virtual SqlLayerDefinitionCapabilities sqlLayerDefinitionCapabilities();

// Operations interface

/**
Expand Down Expand Up @@ -628,7 +644,7 @@ 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.
* Creates and returns a (possibly invalid) 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
Expand Down Expand Up @@ -756,6 +772,7 @@ class CORE_EXPORT QgsAbstractDatabaseProviderConnection : public QgsAbstractProv

Capabilities mCapabilities = Capabilities() SIP_SKIP;
GeometryColumnCapabilities mGeometryColumnCapabilities = GeometryColumnCapabilities() SIP_SKIP;
SqlLayerDefinitionCapabilities mSqlLayerDefinitionCapabilities = SqlLayerDefinitionCapabilities() SIP_SKIP;
QString mProviderKey;

};
Expand Down
32 changes: 28 additions & 4 deletions src/gui/qgsqueryresultwidget.cpp
View file
Expand Up @@ -3,8 +3,8 @@
---------------------
begin : 14.1.2021
copyright : (C) 2021 by ale
email : [your-email-here]
copyright : (C) 2021 by Alessandro Pasotti
email : elpaso at itopen dot it
***************************************************************************
* *
* This program is free software; you can redistribute it and/or modify *
Expand All @@ -29,7 +29,10 @@ QgsQueryResultWidget::QgsQueryResultWidget( QWidget *parent, QgsAbstractDatabase
setupUi( this );

connect( mExecuteButton, &QPushButton::pressed, this, &QgsQueryResultWidget::executeQuery );
connect( mClearButton, &QPushButton::pressed, this, [ = ] { mSqlEditor->setText( QString() ); } );
connect( mClearButton, &QPushButton::pressed, this, [ = ]
{
mSqlEditor->setText( QString() );
} );
connect( mLoadLayerPushButton, &QPushButton::pressed, this, [ = ]
{
if ( mConnection )
Expand Down Expand Up @@ -60,6 +63,27 @@ QgsQueryResultWidget::QgsQueryResultWidget( QWidget *parent, QgsAbstractDatabase

mStatusLabel->hide();
mSqlErrorText->hide();

// Configure the load layer interface
if ( ! connection->sqlLayerDefinitionCapabilities().testFlag( QgsAbstractDatabaseProviderConnection::SqlLayerDefinitionCapability::PrimaryKeys ) )
{
mPkColumnsCheckBox->hide();
mPkColumnsComboBox->hide();
}

if ( ! connection->sqlLayerDefinitionCapabilities().testFlag( QgsAbstractDatabaseProviderConnection::SqlLayerDefinitionCapability::GeometryColumn ) )
{
mGeometryColumnCheckBox->hide();
mGeometryColumnComboBox->hide();
}

if ( ! connection->sqlLayerDefinitionCapabilities().testFlag( QgsAbstractDatabaseProviderConnection::SqlLayerDefinitionCapability::Filters ) )
{
mFilterLabel->hide();
mFilterToolButton->hide();
mFilterLineEdit->hide();
}

mLoadAsNewLayerGroupBox->setCollapsed( true );
setConnection( connection );
}
Expand Down Expand Up @@ -129,7 +153,7 @@ void QgsQueryResultWidget::updateButtons()
{
mFilterToolButton->setEnabled( false );
mExecuteButton->setEnabled( ! mSqlEditor->text().isEmpty() );
mLoadAsNewLayerGroupBox->setEnabled( mFirstRowFetched && ! mSqlEditor->text().isEmpty() && mConnection && mConnection->capabilities().testFlag( QgsAbstractDatabaseProviderConnection::Capability::SqlLayers ) );
mLoadAsNewLayerGroupBox->setEnabled( mFirstRowFetched && mConnection && mConnection->capabilities().testFlag( QgsAbstractDatabaseProviderConnection::Capability::SqlLayers ) );
}

void QgsQueryResultWidget::updateSqlLayerColumns( )
Expand Down
16 changes: 8 additions & 8 deletions src/gui/qgsqueryresultwidget.h
View file
Expand Up @@ -40,7 +40,7 @@ class GUI_EXPORT QgsConnectionsApiFetcher: public QObject

public:

//! Constructs a result fetcher from \a queryResult
//! Constructs a result fetcher from \a queryResult.
QgsConnectionsApiFetcher( const QgsAbstractDatabaseProviderConnection *conn )
: mConnection( conn )
{}
Expand All @@ -53,7 +53,7 @@ class GUI_EXPORT QgsConnectionsApiFetcher: public QObject

signals:

//!! Emitted when \a newTokens have been fetched
//!! Emitted when \a newTokens have been fetched.
void tokensReady( const QStringList &newTokens );

//! Emitted when fetching of tokes has finished or has been interrupted.
Expand Down Expand Up @@ -95,7 +95,7 @@ class GUI_EXPORT QgsQueryResultWidget: public QWidget, private Ui::QgsQueryResul
virtual ~QgsQueryResultWidget();

/**
* Set the connection to \a connection, ownership is transferred to the widget.
* Sets the connection to \a connection, ownership is transferred to the widget.
*/
void setConnection( QgsAbstractDatabaseProviderConnection *connection SIP_TRANSFER );

Expand All @@ -105,7 +105,7 @@ class GUI_EXPORT QgsQueryResultWidget: public QWidget, private Ui::QgsQueryResul
void setQuery( const QString &sql );

/**
* Set the SQL layer \a options. This method automatically populates and shows the "Load as new layer" panel.
* Sets the SQL layer \a options. This method automatically populates and shows the "Load as new layer" panel.
*/
void setSqlVectorLayerOptions( const QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions &options );

Expand Down Expand Up @@ -166,23 +166,23 @@ class GUI_EXPORT QgsQueryResultWidget: public QWidget, private Ui::QgsQueryResul
QString mSqlErrorMessage;

/**
* Updates buttons status
* Updates buttons status.
*/
void updateButtons();

/**
* Updates SQL layer columns
* Updates SQL layer columns.
*/
void updateSqlLayerColumns();

/**
* Cancel and wait for finish
* Cancel and wait for finish.
*/
void cancelRunningQuery();


/**
* Starts the model population after initial query run
* Starts the model population after initial query run.
*/
void startFetching();

Expand Down
10 changes: 8 additions & 2 deletions src/providers/postgres/qgspostgresproviderconnection.cpp
View file
Expand Up @@ -83,6 +83,12 @@ void QgsPostgresProviderConnection::setDefaultCapabilities()
GeometryColumnCapability::SinglePart,
GeometryColumnCapability::Curves
};
mSqlLayerDefinitionCapabilities =
{
Filters,
PrimaryKeys,
GeometryColumn
};
}

void QgsPostgresProviderConnection::dropTablePrivate( const QString &schema, const QString &name ) const
Expand Down Expand Up @@ -772,7 +778,7 @@ QgsVectorLayer *QgsPostgresProviderConnection::createSqlVectorLayer( const SqlVe
tUri.setKeyColumn( QStringLiteral( "_uid%1_" ).arg( pkId ) );

int sqlId { 0 };
while ( options.sql.contains( QStringLiteral( "_subq_%s_" ).arg( sqlId ), Qt::CaseSensitivity::CaseInsensitive ) )
while ( options.sql.contains( QStringLiteral( "_subq_%1_" ).arg( sqlId ), Qt::CaseSensitivity::CaseInsensitive ) )
{
sqlId ++;
}
Expand All @@ -784,7 +790,7 @@ QgsVectorLayer *QgsPostgresProviderConnection::createSqlVectorLayer( const SqlVe
tUri.setGeometryColumn( options.geometryColumn );
}

return new QgsVectorLayer{ tUri.uri(), options.layerName.isEmpty() ? QStringLiteral( "QueryLayer" ) : options.layerName, mProviderKey };
return new QgsVectorLayer{ tUri.uri(), options.layerName.isEmpty() ? QStringLiteral( "QueryLayer" ) : options.layerName, providerKey() };
}

QgsFields QgsPostgresProviderConnection::fields( const QString &schema, const QString &tableName ) const
Expand Down

0 comments on commit cf26022

Please sign in to comment.