Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
First nail in the coffin of DB Manager
Replace DB Manager legend custom menu action with
C++ implementation for SQL layer
  • Loading branch information
elpaso authored and nyalldawson committed Jul 22, 2021
1 parent f5946a1 commit 387c403
Show file tree
Hide file tree
Showing 24 changed files with 266 additions and 59 deletions.
Expand Up @@ -146,6 +146,7 @@ Sets the query execution time to ``queryExecutionTime`` milliseconds.
QStringList primaryKeyColumns;
QString geometryColumn;
bool disableSelectAtId;

};

struct TableProperty
Expand Down Expand Up @@ -551,6 +552,19 @@ Creates and returns a (possibly invalid) vector layer based on the ``sql`` state

:raises QgsProviderConnectionException: if any errors are encountered or if SQL layer creation is not supported.

.. versionadded:: 3.22
%End

virtual SqlVectorLayerOptions sqlOptions( const QString &layerSource );
%Docstring
Returns the SQL layer options from a ``layerSource``.

.. note::

the default implementation returns a default constructed option object.

:raises QgsProviderConnectionException: if any errors are encountered or if SQL layer creation is not supported.

.. versionadded:: 3.22
%End

Expand Down
Expand Up @@ -170,7 +170,6 @@ Returns ``True`` if this is a valid layer. It is up to individual providers
to determine what constitutes a valid layer.
%End


virtual void updateExtents();
%Docstring
Update the extents of the layer. Not implemented by default.
Expand Down
2 changes: 1 addition & 1 deletion python/core/auto_generated/qgsmaplayer.sip.in
Expand Up @@ -1036,7 +1036,7 @@ Read the style for the current layer from the DOM node supplied.
virtual bool writeSymbology( QDomNode &node, QDomDocument &doc, QString &errorMessage, const QgsReadWriteContext &context,
StyleCategories categories = AllStyleCategories ) const = 0;
%Docstring
Write the style for the layer into the docment provided.
Write the style for the layer into the document provided.

:param node: the node that will have the style element added to it.
:param doc: the document that will have the QDomNode added.
Expand Down
21 changes: 21 additions & 0 deletions python/gui/auto_generated/qgsqueryresultwidget.sip.in
Expand Up @@ -20,6 +20,10 @@ 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.

The widget supports an optional SQL layer update mode where the GUI is optimized for the update of
an existing SQL (query) layer (i.e. buttons are labeled differently and the group box for
query layers is expanded).

.. note::

the ownership of the connection is transferred to the widget.
Expand All @@ -39,6 +43,18 @@ Creates a QgsQueryResultWidget with the given ``connection``, ownership is trans

virtual ~QgsQueryResultWidget();

void setSqlVectorLayerOptions( const QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions &options );
%Docstring
Initializes the widget from ``options``.
%End

void setUpdateSqlLayerMode( bool updateMode );
%Docstring
Sets the update SQL layer mode flag to ``updateMode`` (default is ``False``).
When the widget is in update mode the create SQL layer button is renamed to "update" and the
SQL layer creation group box is expanded.
%End

void setConnection( QgsAbstractDatabaseProviderConnection *connection /Transfer/ );
%Docstring
Sets the connection to ``connection``, ownership is transferred to the widget.
Expand All @@ -52,6 +68,11 @@ Convenience method to set the SQL editor text to ``sql``.

public slots:

void notify( const QString &title, const QString &text, Qgis::MessageLevel level = Qgis::MessageLevel::Info );
%Docstring
Displays a message with ``text`` ``title`` and ``level`` in the widget's message bar.
%End

void executeQuery();
%Docstring
Starts executing the query.
Expand Down
22 changes: 0 additions & 22 deletions python/plugins/db_manager/db_manager_plugin.py
Expand Up @@ -57,15 +57,6 @@ def initGui(self):
else:
self.iface.addPluginToMenu(QApplication.translate("DBManagerPlugin", "DB Manager"), self.action)

self.layerAction = QAction(QgsApplication.getThemeIcon('dbmanager.svg'), QApplication.translate("DBManagerPlugin", "Update SQL Layer…"),
self.iface.mainWindow())
self.layerAction.setObjectName("dbManagerUpdateSqlLayer")
self.layerAction.triggered.connect(self.onUpdateSqlLayer)
self.iface.addCustomActionForLayerType(self.layerAction, "", QgsMapLayerType.VectorLayer, False)
for l in list(QgsProject.instance().mapLayers().values()):
self.onLayerWasAdded(l)
QgsProject.instance().layerWasAdded.connect(self.onLayerWasAdded)

def unload(self):
# Remove the plugin menu item and icon
if hasattr(self.iface, 'databaseMenu'):
Expand All @@ -77,22 +68,9 @@ def unload(self):
else:
self.iface.removeToolBarIcon(self.action)

self.iface.removeCustomActionForLayerType(self.layerAction)
QgsProject.instance().layerWasAdded.disconnect(self.onLayerWasAdded)

if self.dlg is not None:
self.dlg.close()

def onLayerWasAdded(self, aMapLayer):
# Be able to update every Db layer from Postgres, Spatialite and Oracle
if hasattr(aMapLayer, 'dataProvider') and aMapLayer.dataProvider() and aMapLayer.dataProvider().name() in ['postgres', 'spatialite', 'oracle']:
self.iface.addCustomActionForLayer(self.layerAction, aMapLayer)
# virtual has QUrl source
# url = QUrl(QUrl.fromPercentEncoding(l.source()))
# url.queryItemValue('query')
# url.queryItemValue('uid')
# url.queryItemValue('geometry')

def onUpdateSqlLayer(self):
# Be able to update every Db layer from Postgres, Spatialite and Oracle
l = self.iface.activeLayer()
Expand Down
66 changes: 54 additions & 12 deletions src/app/qgsapplayertreeviewmenuprovider.cpp
Expand Up @@ -219,27 +219,69 @@ QMenu *QgsAppLayerTreeViewMenuProvider::createContextMenu()
}
}

if ( vlayer /* FIXME: no raster support in createSqlVectorLayer || rlayer */ )
// No raster support in createSqlVectorLayer (yet)
if ( vlayer )
{
std::unique_ptr< QgsAbstractDatabaseProviderConnection> conn { QgsMapLayerUtils::databaseConnection( layer ) };
if ( conn )
menu->addAction( QgsApplication::getThemeIcon( QStringLiteral( "/dbmanager.svg" ) ), tr( "Update SQL Layer…" ), menu, [ layer ]
{
std::unique_ptr< QgsAbstractDatabaseProviderConnection> conn { QgsMapLayerUtils::databaseConnection( layer ) };
if ( conn )
std::unique_ptr< QgsAbstractDatabaseProviderConnection> conn2 { QgsMapLayerUtils::databaseConnection( layer ) };
if ( conn2 )
{
QgsDialog dialog;
dialog.setObjectName( QStringLiteral( "SQLUpdateDialog" ) );
dialog.setObjectName( QStringLiteral( "SqlUpdateDialog" ) );
dialog.setWindowTitle( tr( "%1 — Update SQL" ).arg( layer->name() ) );
QgsGui::enableAutoGeometryRestore( &dialog );
QgsQueryResultWidget *widget { new QgsQueryResultWidget( &dialog, conn.release() ) };
widget->setQuery( layer->dataProvider()->uri().sql() );
widget->layout()->setMargin( 0 );
dialog.layout()->addWidget( widget );

// FIXME! connecting to createSqlVectorLayer won't work because we have no QgsVectorLayer *createSqlVectorLayer UPDATE functionality
// also, we'd need a raster equivalent.. and abstract out the QgsDataSourceUri manipulation part so that it can be used
// for both layer creation and update.
QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions options { conn2->sqlOptions( layer->source() ) };
options.layerName = layer->name();
QgsQueryResultWidget *queryResultWidget { new QgsQueryResultWidget( &dialog, conn2.release() ) };
queryResultWidget->setUpdateSqlLayerMode( true );
queryResultWidget->setSqlVectorLayerOptions( options );
queryResultWidget->executeQuery();
queryResultWidget->layout()->setMargin( 0 );
dialog.layout()->addWidget( queryResultWidget );

connect( queryResultWidget, &QgsQueryResultWidget::createSqlVectorLayer, queryResultWidget, [queryResultWidget, layer ]( const QString &, const QString &, const QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions & options )
{
std::unique_ptr< QgsAbstractDatabaseProviderConnection> conn3 { QgsMapLayerUtils::databaseConnection( layer ) };
if ( conn3 )
{
try
{
std::unique_ptr<QgsMapLayer> sqlLayer { conn3->createSqlVectorLayer( options ) };
if ( sqlLayer->isValid() )
{
// Store layer settings
QgsReadWriteContext context;
context.setPathResolver( QgsProject::instance()->pathResolver() );
context.setProjectTranslator( QgsProject::instance() );
QString errorMsg;
QDomDocument doc;
layer->exportNamedStyle( doc, errorMsg, context );
layer->setDataSource( sqlLayer->source(), sqlLayer->name(), sqlLayer->dataProvider()->name(), QgsDataProvider::ProviderOptions() );
layer->importNamedStyle( doc, errorMsg );
queryResultWidget->notify( QObject::tr( "Layer Update Success" ), QObject::tr( "The SQL layer was updated successfully" ), Qgis::MessageLevel::Success );
}
else
{
QString error { sqlLayer->dataProvider()->error().message( QgsErrorMessage::Format::Text ) };
if ( error.isEmpty() )
{
error = QObject::tr( "layer is not valid, check the log messages for more information" );
}
queryResultWidget->notify( QObject::tr( "Layer Update Error" ), QObject::tr( "Error updating the SQL layer: %1" ).arg( error ), Qgis::MessageLevel::Critical );
}
}
catch ( QgsProviderConnectionException &ex )
{
queryResultWidget->notify( QObject::tr( "Layer Update Error" ), QObject::tr( "Error updating the SQL layer: %1" ).arg( ex.what() ), Qgis::MessageLevel::Critical );
}
}

} );

dialog.exec();

}
} );
Expand Down
19 changes: 17 additions & 2 deletions src/core/providers/ogr/qgsgeopackageproviderconnection.cpp
Expand Up @@ -389,8 +389,6 @@ void QgsGeoPackageProviderConnection::setDefaultCapabilities()
mSqlLayerDefinitionCapabilities =
{
Qgis::SqlLayerDefinitionCapability::SubsetStringFilter,
Qgis::SqlLayerDefinitionCapability::PrimaryKeys,
Qgis::SqlLayerDefinitionCapability::GeometryColumn,
};
}

Expand Down Expand Up @@ -1170,6 +1168,23 @@ QMultiMap<Qgis::SqlKeywordCategory, QStringList> QgsGeoPackageProviderConnection
} );
}

QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions QgsGeoPackageProviderConnection::sqlOptions( const QString &layerSource )
{
SqlVectorLayerOptions options;
QgsProviderMetadata *providerMetadata { QgsProviderRegistry::instance()->providerMetadata( QStringLiteral( "ogr" ) ) };
Q_ASSERT( providerMetadata );
QMap<QString, QVariant> decoded = providerMetadata->decodeUri( layerSource );
if ( decoded.contains( QStringLiteral( "subset" ) ) )
{
options.sql = decoded[ QStringLiteral( "subset" ) ].toString();
}
else if ( decoded.contains( QStringLiteral( "layerName" ) ) )
{
options.sql = QStringLiteral( "SELECT * FROM %1" ).arg( QgsSqliteUtils::quotedIdentifier( decoded[ QStringLiteral( "layerName" ) ].toString() ) );
}
return options;
}

QgsGeoPackageProviderResultIterator::QgsGeoPackageProviderResultIterator( gdal::ogr_datasource_unique_ptr hDS, OGRLayerH ogrLayer )
: mHDS( std::move( hDS ) )
, mOgrLayer( ogrLayer )
Expand Down
1 change: 1 addition & 0 deletions src/core/providers/ogr/qgsgeopackageproviderconnection.h
Expand Up @@ -82,6 +82,7 @@ class QgsGeoPackageProviderConnection : public QgsAbstractDatabaseProviderConnec
QList<QgsVectorDataProvider::NativeType> nativeTypes() const override;
QgsFields fields( const QString &schema, const QString &table ) const override;
QMultiMap<Qgis::SqlKeywordCategory, QStringList> sqlDictionary() override;
SqlVectorLayerOptions sqlOptions( const QString &layerSource ) override;

private:

Expand Down
11 changes: 10 additions & 1 deletion src/core/providers/qgsabstractdatabaseproviderconnection.cpp
Expand Up @@ -48,13 +48,15 @@ Qgis::SqlLayerDefinitionCapabilities QgsAbstractDatabaseProviderConnection::sqlL
return mSqlLayerDefinitionCapabilities;
}


QString QgsAbstractDatabaseProviderConnection::tableUri( const QString &schema, const QString &name ) const
{
Q_UNUSED( schema )
Q_UNUSED( name )
throw QgsProviderConnectionException( QObject::tr( "Operation 'tableUri' is not supported" ) );
}


///@cond PRIVATE
void QgsAbstractDatabaseProviderConnection::checkCapability( QgsAbstractDatabaseProviderConnection::Capability capability ) const
{
Expand Down Expand Up @@ -1036,6 +1038,13 @@ void QgsAbstractDatabaseProviderConnection::renameVectorTable( const QString &,
checkCapability( Capability::RenameVectorTable );
}


QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions QgsAbstractDatabaseProviderConnection::sqlOptions( const QString & )
{
checkCapability( Capability::SqlLayers );
return SqlVectorLayerOptions();
}

void QgsAbstractDatabaseProviderConnection::renameRasterTable( const QString &, const QString &, const QString & ) const
{
checkCapability( Capability::RenameRasterTable );
Expand Down Expand Up @@ -1507,5 +1516,5 @@ long long QgsAbstractDatabaseProviderConnection::QueryResult::QueryResultIterato
return rowCountPrivate();
}


///@endcond private

10 changes: 10 additions & 0 deletions src/core/providers/qgsabstractdatabaseproviderconnection.h
Expand Up @@ -247,6 +247,7 @@ class CORE_EXPORT QgsAbstractDatabaseProviderConnection : public QgsAbstractProv
QString geometryColumn;
//! If SelectAtId is disabled (default is false), not all data providers support this feature: check support with SqlLayerDefinitionCapability::SelectAtId capability
bool disableSelectAtId = false;

};

/**
Expand Down Expand Up @@ -676,6 +677,15 @@ class CORE_EXPORT QgsAbstractDatabaseProviderConnection : public QgsAbstractProv
*/
virtual QgsVectorLayer *createSqlVectorLayer( const SqlVectorLayerOptions &options ) const SIP_THROW( QgsProviderConnectionException ) SIP_FACTORY;

/**
* Returns the SQL layer options from a \a layerSource.
*
* \note the default implementation returns a default constructed option object.
* \throws QgsProviderConnectionException if any errors are encountered or if SQL layer creation is not supported.
* \since QGIS 3.22
*/
virtual SqlVectorLayerOptions sqlOptions( const QString &layerSource );

/**
* Executes raw \a sql and returns the (possibly empty) query results, optionally \a feedback can be provided.
*
Expand Down
1 change: 0 additions & 1 deletion src/core/providers/qgsdataprovider.h
Expand Up @@ -236,7 +236,6 @@ class CORE_EXPORT QgsDataProvider : public QObject
*/
virtual bool isValid() const = 0;


/**
* Update the extents of the layer. Not implemented by default.
*/
Expand Down
2 changes: 1 addition & 1 deletion src/core/qgsmaplayer.h
Expand Up @@ -958,7 +958,7 @@ class CORE_EXPORT QgsMapLayer : public QObject
QgsReadWriteContext &context, StyleCategories categories = AllStyleCategories );

/**
* Write the style for the layer into the docment provided.
* Write the style for the layer into the document provided.
* \param node the node that will have the style element added to it.
* \param doc the document that will have the QDomNode added.
* \param errorMessage reference to string that will be updated with any error messages
Expand Down
2 changes: 1 addition & 1 deletion src/core/qgsmaplayerutils.cpp
Expand Up @@ -103,7 +103,7 @@ QgsAbstractDatabaseProviderConnection *QgsMapLayerUtils::databaseConnection( con
return nullptr;
}

std::unique_ptr< QgsAbstractDatabaseProviderConnection > conn { static_cast<QgsAbstractDatabaseProviderConnection *>( providerMetadata->createConnection( layer->dataProvider()->uri().uri(), {} ) ) };
std::unique_ptr< QgsAbstractDatabaseProviderConnection > conn { static_cast<QgsAbstractDatabaseProviderConnection *>( providerMetadata->createConnection( layer->source(), {} ) ) };
return conn.release();
}
catch ( const QgsProviderConnectionException &ex )
Expand Down

0 comments on commit 387c403

Please sign in to comment.