[processing][feature][needs-docs] add algorithm for executing SQL que…

…ries against registered SpatiaLite databases
qgis · May 13, 2020 · 319d34c · 319d34c
1 parent b99fe4e
commit 319d34c
Show file tree

Hide file tree

Showing 5 changed files with 169 additions and 5 deletions.
diff --git a/src/analysis/CMakeLists.txt b/src/analysis/CMakeLists.txt
@@ -52,6 +52,7 @@ SET(QGIS_ANALYSIS_SRCS
   processing/qgsalgorithmdropmzvalues.cpp
   processing/qgsalgorithmexecutepostgisquery.cpp
   processing/qgsalgorithmexecutespatialitequery.cpp
+  processing/qgsalgorithmexecutespatialitequeryregistered.cpp
   processing/qgsalgorithmexplode.cpp
   processing/qgsalgorithmexplodehstore.cpp
   processing/qgsalgorithmextendlines.cpp

diff --git a/src/analysis/processing/qgsalgorithmexecutespatialitequery.cpp b/src/analysis/processing/qgsalgorithmexecutespatialitequery.cpp
@@ -59,25 +59,36 @@ QgsExecuteSpatialiteQueryAlgorithm *QgsExecuteSpatialiteQueryAlgorithm::createIn
 
 void QgsExecuteSpatialiteQueryAlgorithm::initAlgorithm( const QVariantMap & )
 {
-  addParameter( new QgsProcessingParameterProviderConnection( QStringLiteral( "DATABASE" ), QObject::tr( "Database (connection name)" ), QStringLiteral( "spatialite" ) ) );
+  addParameter( new QgsProcessingParameterVectorLayer( QStringLiteral( "DATABASE" ), QObject::tr( "Database (connection name)" ), QList< int >() << QgsProcessing::TypeVector ) );
   addParameter( new QgsProcessingParameterString( QStringLiteral( "SQL" ), QObject::tr( "SQL query" ), QVariant(), true ) );
 }
 
 QVariantMap QgsExecuteSpatialiteQueryAlgorithm::processAlgorithm( const QVariantMap &parameters, QgsProcessingContext &context, QgsProcessingFeedback *feedback )
 {
-  Q_UNUSED( feedback );
+  //Q_UNUSED( feedback );
+  QgsVectorLayer *layer = parameterAsVectorLayer( parameters, QStringLiteral( "DATABASE" ), context );
+  QString databaseUri = layer->dataProvider()->dataSourceUri();
+  QgsDataSourceUri uri( databaseUri );
+  if ( uri.database().isEmpty() )
+  {
+    if ( databaseUri.contains( QStringLiteral( "|layername" ), Qt::CaseInsensitive ) )
+      databaseUri = databaseUri.left( databaseUri.indexOf( QStringLiteral( "|layername" ) ) );
+    else if ( databaseUri.contains( QStringLiteral( "|layerid" ), Qt::CaseInsensitive ) )
+      databaseUri = databaseUri.left( databaseUri.indexOf( QStringLiteral( "|layerid" ) ) );
 
-  QString connName = parameterAsConnectionName( parameters, QStringLiteral( "DATABASE" ), context );
+    uri = QgsDataSourceUri( QStringLiteral( "dbname='%1'" ).arg( databaseUri ) );
+  }
 
+  feedback->pushInfo( databaseUri );
   std::unique_ptr<QgsAbstractDatabaseProviderConnection> conn;
   try
   {
     std::unique_ptr<QgsProviderMetadata> md( QgsProviderRegistry::instance()->providerMetadata( QStringLiteral( "spatialite" ) ) );
-    conn.reset( static_cast<QgsAbstractDatabaseProviderConnection *>( md->createConnection( connName ) ) );
+    conn.reset( static_cast<QgsAbstractDatabaseProviderConnection *>( md->createConnection( uri.uri(), QVariantMap() ) ) );
   }
   catch ( QgsProviderConnectionException & )
   {
-    throw QgsProcessingException( QObject::tr( "Could not retrieve connection details for %1" ).arg( connName ) );
+    throw QgsProcessingException( QObject::tr( "Could not connect to %1" ).arg( uri.uri() ) );
   }
 
   QString sql = parameterAsString( parameters, QStringLiteral( "SQL" ), context ).replace( '\n', ' ' );

diff --git a/src/analysis/processing/qgsalgorithmexecutespatialitequeryregistered.cpp b/src/analysis/processing/qgsalgorithmexecutespatialitequeryregistered.cpp
@@ -0,0 +1,96 @@
+/***************************************************************************
+                         qgsalgorithmexecutepostgisqueryregistered.cpp
+                         ---------------------
+    begin                : May 2020
+    copyright            : (C) 2020 by Alexander Bruy
+    email                : alexander dot bruy 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 "qgsalgorithmexecutespatialitequeryregistered.h"
+#include "qgsproviderregistry.h"
+#include "qgsprovidermetadata.h"
+#include "qgsabstractdatabaseproviderconnection.h"
+
+///@cond PRIVATE
+
+QString QgsExecuteRegisteredSpatialiteQueryAlgorithm::name() const
+{
+  return QStringLiteral( "spatialiteexecutesqlregistered" );
+}
+
+QString QgsExecuteRegisteredSpatialiteQueryAlgorithm::displayName() const
+{
+  return QObject::tr( "SpatiaLite execute SQL (registered DB)" );
+}
+
+QStringList QgsExecuteRegisteredSpatialiteQueryAlgorithm::tags() const
+{
+  return QObject::tr( "database,sql,spatialite,execute" ).split( ',' );
+}
+
+QString QgsExecuteRegisteredSpatialiteQueryAlgorithm::group() const
+{
+  return QObject::tr( "Database" );
+}
+
+QString QgsExecuteRegisteredSpatialiteQueryAlgorithm::groupId() const
+{
+  return QStringLiteral( "database" );
+}
+
+QString QgsExecuteRegisteredSpatialiteQueryAlgorithm::shortHelpString() const
+{
+  return QObject::tr( "Executes a SQL command on a SpatiaLite database." );
+}
+
+QgsExecuteRegisteredSpatialiteQueryAlgorithm *QgsExecuteRegisteredSpatialiteQueryAlgorithm::createInstance() const
+{
+  return new QgsExecuteRegisteredSpatialiteQueryAlgorithm();
+}
+
+void QgsExecuteRegisteredSpatialiteQueryAlgorithm::initAlgorithm( const QVariantMap & )
+{
+  addParameter( new QgsProcessingParameterProviderConnection( QStringLiteral( "DATABASE" ), QObject::tr( "Database (connection name)" ), QStringLiteral( "spatialite" ) ) );
+  addParameter( new QgsProcessingParameterString( QStringLiteral( "SQL" ), QObject::tr( "SQL query" ), QVariant(), true ) );
+}
+
+QVariantMap QgsExecuteRegisteredSpatialiteQueryAlgorithm::processAlgorithm( const QVariantMap &parameters, QgsProcessingContext &context, QgsProcessingFeedback *feedback )
+{
+  Q_UNUSED( feedback );
+
+  QString connName = parameterAsConnectionName( parameters, QStringLiteral( "DATABASE" ), context );
+
+  std::unique_ptr<QgsAbstractDatabaseProviderConnection> conn;
+  try
+  {
+    std::unique_ptr<QgsProviderMetadata> md( QgsProviderRegistry::instance()->providerMetadata( QStringLiteral( "spatialite" ) ) );
+    conn.reset( static_cast<QgsAbstractDatabaseProviderConnection *>( md->createConnection( connName ) ) );
+  }
+  catch ( QgsProviderConnectionException & )
+  {
+    throw QgsProcessingException( QObject::tr( "Could not retrieve connection details for %1" ).arg( connName ) );
+  }
+
+  QString sql = parameterAsString( parameters, QStringLiteral( "SQL" ), context ).replace( '\n', ' ' );
+  try
+  {
+    conn->executeSql( sql );
+  }
+  catch ( QgsProviderConnectionException &ex )
+  {
+    throw QgsProcessingException( QObject::tr( "Error executing SQL:\n%1" ).arg( ex.what() ) );
+  }
+
+  return QVariantMap();
+}
+
+///@endcond
diff --git a/src/analysis/processing/qgsalgorithmexecutespatialitequeryregistered.h b/src/analysis/processing/qgsalgorithmexecutespatialitequeryregistered.h
@@ -0,0 +1,54 @@
+/***************************************************************************
+                         qgsalgorithmexecutespatialitequeryregistered.h
+                         ------------------------------
+    begin                : May 2020
+    copyright            : (C) 2020 by Alexander Bruy
+    email                : alexander dot bruy 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 QGSALGORITHMEXECUTESPATIALITEQUERYREGISTERED_H
+#define QGSALGORITHMEXECUTESPATIALITEQUERYREGISTERED_H
+
+#define SIP_NO_FILE
+
+#include "qgis_sip.h"
+#include "qgsprocessingalgorithm.h"
+
+///@cond PRIVATE
+
+/**
+ * Native execute SpoatiaLite (registered) query algorithm.
+ */
+class QgsExecuteRegisteredSpatialiteQueryAlgorithm : public QgsProcessingAlgorithm
+{
+
+  public:
+
+    QgsExecuteRegisteredSpatialiteQueryAlgorithm() = default;
+    void initAlgorithm( const QVariantMap &configuration = QVariantMap() ) override;
+    QString name() const override;
+    QString displayName() const override;
+    QStringList tags() const override;
+    QString group() const override;
+    QString groupId() const override;
+    QString shortHelpString() const override;
+    QgsExecuteRegisteredSpatialiteQueryAlgorithm *createInstance() const override SIP_FACTORY;
+
+  protected:
+
+    QVariantMap processAlgorithm( const QVariantMap &parameters,
+                                  QgsProcessingContext &context, QgsProcessingFeedback *feedback ) override;
+};
+
+///@endcond PRIVATE
+
+#endif // QGSALGORITHMEXECUTESPATIALITEQUERYREGISTERED_H
diff --git a/src/analysis/processing/qgsnativealgorithms.cpp b/src/analysis/processing/qgsnativealgorithms.cpp
@@ -47,6 +47,7 @@
 #include "qgsalgorithmdropmzvalues.h"
 #include "qgsalgorithmexecutepostgisquery.h"
 #include "qgsalgorithmexecutespatialitequery.h"
+#include "qgsalgorithmexecutespatialitequeryregistered.h"
 #include "qgsalgorithmexplode.h"
 #include "qgsalgorithmexplodehstore.h"
 #include "qgsalgorithmextendlines.h"
@@ -248,6 +249,7 @@ void QgsNativeAlgorithms::loadAlgorithms()
   addAlgorithm( new QgsDropGeometryAlgorithm() );
   addAlgorithm( new QgsDropMZValuesAlgorithm() );
   addAlgorithm( new QgsExecutePostgisQueryAlgorithm() );
+  addAlgorithm( new QgsExecuteRegisteredSpatialiteQueryAlgorithm() );
   addAlgorithm( new QgsExecuteSpatialiteQueryAlgorithm() );
   addAlgorithm( new QgsExplodeAlgorithm() );
   addAlgorithm( new QgsExplodeHstoreAlgorithm() );