MSSQL connections API initial implementation

qgis · Mar 13, 2020 · 2760f8a · 2760f8a
1 parent b133b81
commit 2760f8a
Show file tree

Hide file tree

Showing 12 changed files with 839 additions and 25 deletions.
diff --git a/src/providers/mssql/CMakeLists.txt b/src/providers/mssql/CMakeLists.txt
@@ -1,6 +1,7 @@
 SET(MSSQL_SRCS
   qgsmssqlconnection.cpp
   qgsmssqlprovider.cpp
+  qgsmssqlproviderconnection.cpp
   qgsmssqlgeometryparser.cpp
   qgsmssqltablemodel.cpp
   qgsmssqldataitems.cpp

diff --git a/src/providers/mssql/qgsmssqlconnection.cpp b/src/providers/mssql/qgsmssqlconnection.cpp
@@ -361,6 +361,68 @@ bool QgsMssqlConnection::isSystemSchema( const QString &schema )
   return sSystemSchemas.contains( schema );
 }
 
+QgsDataSourceUri QgsMssqlConnection::connUri( const QString &connName )
+{
+
+  QgsSettings settings;
+
+  const QString key = "/MSSQL/connections/" + connName;
+
+  const QString service = settings.value( key + "/service" ).toString();
+  const QString host = settings.value( key + "/host" ).toString();
+  const QString database = settings.value( key + "/database" ).toString();
+  const QString username = settings.value( key + "/username" ).toString();
+  const QString password = settings.value( key + "/password" ).toString();
+
+  const bool useGeometryColumns { QgsMssqlConnection::geometryColumnsOnly( connName ) };
+  const bool useEstimatedMetadata { QgsMssqlConnection::useEstimatedMetadata( connName ) };
+  const bool allowGeometrylessTables { QgsMssqlConnection::allowGeometrylessTables( connName ) };
+  const bool disableGeometryHandling { QgsMssqlConnection::isInvalidGeometryHandlingDisabled( connName ) };
+
+  QgsDataSourceUri uri;
+  if ( !service.isEmpty() )
+  {
+    uri.setConnection( service, database, username, password );
+  }
+  else
+  {
+    uri.setConnection( host, QString(), database, username, password );
+  }
+
+  uri.setParam( QStringLiteral( "geometryColumnsOnly" ), useGeometryColumns ? QStringLiteral( "true" ) : QStringLiteral( "false" ) );
+  uri.setUseEstimatedMetadata( useEstimatedMetadata );
+  uri.setParam( QStringLiteral( "allowGeometrylessTables" ), allowGeometrylessTables ? QStringLiteral( "true" ) : QStringLiteral( "false" ) );
+  uri.setParam( QStringLiteral( "disableInvalidGeometryHandling" ), disableGeometryHandling ? QStringLiteral( "true" ) : QStringLiteral( "false" ) );
+
+  if ( settings.value( QStringLiteral( "saveUsername" ) ).isValid() )
+  {
+    const bool saveUsername { settings.value( QStringLiteral( "saveUsername" ) ).toBool() };
+    uri.setParam( QStringLiteral( "saveUsername" ), saveUsername ? QStringLiteral( "true" ) : QStringLiteral( "false" ) );
+    if ( ! saveUsername )
+    {
+      uri.setUsername( QString() );
+    }
+  }
+  if ( settings.value( QStringLiteral( "savePassword" ) ).isValid() )
+  {
+    const bool savePassword { settings.value( QStringLiteral( "savePassword" ) ).toBool() };
+    uri.setParam( QStringLiteral( "savePassword" ), savePassword ? QStringLiteral( "true" ) : QStringLiteral( "false" ) );
+    if ( ! savePassword )
+    {
+      uri.setPassword( QString() );
+    }
+  }
+
+  return uri;
+}
+
+QStringList QgsMssqlConnection::connectionList()
+{
+  QgsSettings settings;
+  settings.beginGroup( QStringLiteral( "MSSQL/connections" ) );
+  return settings.childGroups();
+}
+
 QString QgsMssqlConnection::dbConnectionName( const QString &name )
 {
   // Starting with Qt 5.11, sharing the same connection between threads is not allowed.

diff --git a/src/providers/mssql/qgsmssqlconnection.h b/src/providers/mssql/qgsmssqlconnection.h
@@ -21,6 +21,8 @@
 #include <QStringList>
 #include <QMutex>
 
+#include "qgsdatasourceuri.h"
+
 class QString;
 class QSqlDatabase;
 
@@ -151,6 +153,17 @@ class QgsMssqlConnection
      */
     static bool isSystemSchema( const QString &schema );
 
+    /**
+     * Reads a connection named \a connName from the settings and returns the datasource uri
+     */
+    static QgsDataSourceUri connUri( const QString &connName );
+
+    /**
+     * Reads MSSQL connections from the settings and return a list of connection names
+     */
+    static QStringList connectionList();
+
+
   private:
 
     /**

diff --git a/src/providers/mssql/qgsmssqldataitems.cpp b/src/providers/mssql/qgsmssqldataitems.cpp
@@ -658,3 +658,4 @@ bool QgsMssqlSchemaItem::layerCollection() const
 {
   return true;
 }
+
diff --git a/src/providers/mssql/qgsmssqlgeomcolumntypethread.cpp b/src/providers/mssql/qgsmssqlgeomcolumntypethread.cpp
@@ -55,21 +55,20 @@ void QgsMssqlGeomColumnTypeThread::run()
 
     if ( !mStopped )
     {
-      QString table;
-      table = QStringLiteral( "%1[%2]" )
-              .arg( layerProperty.schemaName.isEmpty() ? QString() : QStringLiteral( "[%1]." ).arg( layerProperty.schemaName ),
-                    layerProperty.tableName );
-
-      QString query = QString( "SELECT %3"
-                               " UPPER([%1].STGeometryType()),"
-                               " [%1].STSrid"
-                               " FROM %2"
-                               " WHERE [%1] IS NOT NULL %4"
-                               " GROUP BY [%1].STGeometryType(), [%1].STSrid" )
-                      .arg( layerProperty.geometryColName,
-                            table,
-                            mUseEstimatedMetadata ? "TOP 1" : "",
-                            layerProperty.sql.isEmpty() ? QString() : QStringLiteral( " AND %1" ).arg( layerProperty.sql ) );
+      const QString table = QStringLiteral( "%1[%2]" )
+                            .arg( layerProperty.schemaName.isEmpty() ? QString() : QStringLiteral( "[%1]." ).arg( layerProperty.schemaName ),
+                                  layerProperty.tableName );
+
+      const QString query = QStringLiteral( "SELECT %3"
+                                            " UPPER([%1].STGeometryType()),"
+                                            " [%1].STSrid"
+                                            " FROM %2"
+                                            " WHERE [%1] IS NOT NULL %4"
+                                            " GROUP BY [%1].STGeometryType(), [%1].STSrid" )
+                            .arg( layerProperty.geometryColName,
+                                  table,
+                                  mUseEstimatedMetadata ? "TOP 1" : "",
+                                  layerProperty.sql.isEmpty() ? QString() : QStringLiteral( " AND %1" ).arg( layerProperty.sql ) );
 
       // issue the sql query
       QSqlDatabase db = QgsMssqlConnection::getDatabase( mService, mHost, mDatabase, mUsername, mPassword );

diff --git a/src/providers/mssql/qgsmssqlprovider.cpp b/src/providers/mssql/qgsmssqlprovider.cpp
@@ -17,6 +17,7 @@
 
 #include "qgsmssqlprovider.h"
 #include "qgsmssqlconnection.h"
+#include "qgsmssqlproviderconnection.h"
 
 #include <QtGlobal>
 #include <QFileInfo>
@@ -490,6 +491,11 @@ QString QgsMssqlProvider::quotedValue( const QVariant &value )
   }
 }
 
+QString QgsMssqlProvider::quotedIdentifier( const QString &value )
+{
+  return QStringLiteral( "[%1]" ).arg( value );
+}
+
 QString QgsMssqlProvider::defaultValueClause( int fieldId ) const
 {
   return mDefaultValues.value( fieldId, QString() );
@@ -2050,6 +2056,31 @@ QList<QgsDataItemProvider *> QgsMssqlProviderMetadata::dataItemProviders() const
   return providers;
 }
 
+QMap<QString, QgsAbstractProviderConnection *> QgsMssqlProviderMetadata::connections( bool cached )
+{
+  return connectionsProtected<QgsMssqlProviderConnection, QgsMssqlConnection>( cached );
+}
+
+QgsAbstractProviderConnection *QgsMssqlProviderMetadata::createConnection( const QString &name )
+{
+  return new QgsMssqlProviderConnection( name );
+}
+
+QgsAbstractProviderConnection *QgsMssqlProviderMetadata::createConnection( const QString &uri, const QVariantMap &configuration )
+{
+  return new QgsMssqlProviderConnection( uri, configuration );
+}
+
+void QgsMssqlProviderMetadata::deleteConnection( const QString &name )
+{
+  deleteConnectionProtected<QgsMssqlProviderConnection>( name );
+}
+
+void QgsMssqlProviderMetadata::saveConnection( const QgsAbstractProviderConnection *conn, const QString &name )
+{
+  saveConnectionProtected( conn, name );
+}
+
 QgsVectorLayerExporter::ExportError QgsMssqlProviderMetadata::createEmptyLayer(
   const QString &uri,
   const QgsFields &fields,
@@ -2389,6 +2420,128 @@ QString QgsMssqlProviderMetadata::getStyleById( const QString &uri, QString styl
   return style;
 }
 
+QVariantMap QgsMssqlProviderMetadata::decodeUri( const QString &uri )
+{
+  const QgsDataSourceUri dsUri { uri };
+  QVariantMap uriParts;
+
+  if ( ! dsUri.database().isEmpty() )
+    uriParts[ QStringLiteral( "dbname" ) ] = dsUri.database();
+  if ( ! dsUri.host().isEmpty() )
+    uriParts[ QStringLiteral( "host" ) ] = dsUri.host();
+  if ( ! dsUri.port().isEmpty() )
+    uriParts[ QStringLiteral( "port" ) ] = dsUri.port();
+  if ( ! dsUri.service().isEmpty() )
+    uriParts[ QStringLiteral( "service" ) ] = dsUri.service();
+  if ( ! dsUri.username().isEmpty() )
+    uriParts[ QStringLiteral( "username" ) ] = dsUri.username();
+
+  // Supported?
+  //if ( ! dsUri.authConfigId().isEmpty() )
+  //  uriParts[ QStringLiteral( "authcfg" ) ] = dsUri.authConfigId();
+
+  if ( dsUri.wkbType() != QgsWkbTypes::Type::Unknown )
+    uriParts[ QStringLiteral( "type" ) ] = dsUri.wkbType();
+
+  // Supported?
+  // uriParts[ QStringLiteral( "selectatid" ) ] = dsUri.selectAtIdDisabled();
+
+  if ( ! dsUri.table().isEmpty() )
+    uriParts[ QStringLiteral( "table" ) ] = dsUri.table();
+  if ( ! dsUri.schema().isEmpty() )
+    uriParts[ QStringLiteral( "schema" ) ] = dsUri.schema();
+  if ( ! dsUri.keyColumn().isEmpty() )
+    uriParts[ QStringLiteral( "key" ) ] = dsUri.keyColumn();
+  if ( ! dsUri.srid().isEmpty() )
+    uriParts[ QStringLiteral( "srid" ) ] = dsUri.srid();
+
+  uriParts[ QStringLiteral( "estimatedmetadata" ) ] = dsUri.useEstimatedMetadata();
+
+  // is this supported?
+  // uriParts[ QStringLiteral( "sslmode" ) ] = dsUri.sslMode();
+
+  if ( ! dsUri.sql().isEmpty() )
+    uriParts[ QStringLiteral( "sql" ) ] = dsUri.sql();
+  if ( ! dsUri.geometryColumn().isEmpty() )
+    uriParts[ QStringLiteral( "geometrycolumn" ) ] = dsUri.geometryColumn();
+
+  // From configuration
+  static const QStringList configurationParameters
+  {
+    QStringLiteral( "geometryColumnsOnly" ),
+    QStringLiteral( "allowGeometrylessTables" ),
+    QStringLiteral( "saveUsername" ),
+    QStringLiteral( "savePassword" ),
+    QStringLiteral( "estimatedMetadata" ),
+    QStringLiteral( "disableInvalidGeometryHandling" ),
+  };
+
+  for ( const auto &configParam : configurationParameters )
+  {
+    if ( dsUri.hasParam( configParam ) )
+    {
+      uriParts[ configParam ] = dsUri.param( configParam );
+    }
+  }
+
+  return uriParts;
+}
+
+QString QgsMssqlProviderMetadata::encodeUri( const QVariantMap &parts )
+{
+  QgsDataSourceUri dsUri;
+  if ( parts.contains( QStringLiteral( "dbname" ) ) )
+    dsUri.setDatabase( parts.value( QStringLiteral( "dbname" ) ).toString() );
+  // Also accepts "database"
+  if ( parts.contains( QStringLiteral( "database" ) ) )
+    dsUri.setDatabase( parts.value( QStringLiteral( "database" ) ).toString() );
+  // Supported?
+  //if ( parts.contains( QStringLiteral( "port" ) ) )
+  //  dsUri.setParam( QStringLiteral( "port" ), parts.value( QStringLiteral( "port" ) ).toString() );
+  if ( parts.contains( QStringLiteral( "host" ) ) )
+    dsUri.setParam( QStringLiteral( "host" ), parts.value( QStringLiteral( "host" ) ).toString() );
+  if ( parts.contains( QStringLiteral( "service" ) ) )
+    dsUri.setParam( QStringLiteral( "service" ), parts.value( QStringLiteral( "service" ) ).toString() );
+  if ( parts.contains( QStringLiteral( "username" ) ) )
+    dsUri.setUsername( parts.value( QStringLiteral( "username" ) ).toString() );
+  if ( parts.contains( QStringLiteral( "password" ) ) )
+    dsUri.setPassword( parts.value( QStringLiteral( "password" ) ).toString() );
+  // Supported?
+  //if ( parts.contains( QStringLiteral( "authcfg" ) ) )
+  //  dsUri.setAuthConfigId( parts.value( QStringLiteral( "authcfg" ) ).toString() );
+  if ( parts.contains( QStringLiteral( "type" ) ) )
+    dsUri.setParam( QStringLiteral( "type" ), QgsWkbTypes::displayString( static_cast<QgsWkbTypes::Type>( parts.value( QStringLiteral( "type" ) ).toInt() ) ) );
+  // Supported?
+  //if ( parts.contains( QStringLiteral( "selectatid" ) ) )
+  //  dsUri.setParam( QStringLiteral( "selectatid" ), parts.value( QStringLiteral( "selectatid" ) ).toString() );
+  if ( parts.contains( QStringLiteral( "table" ) ) )
+    dsUri.setTable( parts.value( QStringLiteral( "table" ) ).toString() );
+  if ( parts.contains( QStringLiteral( "schema" ) ) )
+    dsUri.setSchema( parts.value( QStringLiteral( "schema" ) ).toString() );
+  if ( parts.contains( QStringLiteral( "key" ) ) )
+    dsUri.setParam( QStringLiteral( "key" ), parts.value( QStringLiteral( "key" ) ).toString() );
+  if ( parts.contains( QStringLiteral( "srid" ) ) )
+    dsUri.setSrid( parts.value( QStringLiteral( "srid" ) ).toString() );
+  if ( parts.contains( QStringLiteral( "estimatedmetadata" ) ) )
+    dsUri.setParam( QStringLiteral( "estimatedmetadata" ), parts.value( QStringLiteral( "estimatedmetadata" ) ).toString() );
+  // Supported?
+  //if ( parts.contains( QStringLiteral( "sslmode" ) ) )
+  //  dsUri.setParam( QStringLiteral( "sslmode" ), QgsDataSourceUri::encodeSslMode( static_cast<QgsDataSourceUri::SslMode>( parts.value( QStringLiteral( "sslmode" ) ).toInt( ) ) ) );
+  if ( parts.contains( QStringLiteral( "sql" ) ) )
+    dsUri.setSql( parts.value( QStringLiteral( "sql" ) ).toString() );
+  // Supported?
+  //if ( parts.contains( QStringLiteral( "checkPrimaryKeyUnicity" ) ) )
+  //  dsUri.setParam( QStringLiteral( "checkPrimaryKeyUnicity" ), parts.value( QStringLiteral( "checkPrimaryKeyUnicity" ) ).toString() );
+  if ( parts.contains( QStringLiteral( "geometrycolumn" ) ) )
+    dsUri.setGeometryColumn( parts.value( QStringLiteral( "geometrycolumn" ) ).toString() );
+  if ( parts.contains( QStringLiteral( "disableInvalidGeometryHandling" ) ) )
+    dsUri.setParam( QStringLiteral( "disableInvalidGeometryHandling" ), parts.value( QStringLiteral( "disableInvalidGeometryHandling" ) ).toString() );
+  if ( parts.contains( QStringLiteral( "allowGeometrylessTables" ) ) )
+    dsUri.setParam( QStringLiteral( "allowGeometrylessTables" ), parts.value( QStringLiteral( "allowGeometrylessTables" ) ).toString() );
+  if ( parts.contains( QStringLiteral( "geometryColumnsOnly" ) ) )
+    dsUri.setParam( QStringLiteral( "geometryColumnsOnly" ), parts.value( QStringLiteral( "geometryColumnsOnly" ) ).toString() );
+  return dsUri.uri();
+}
 
 QGISEXTERN QgsProviderMetadata *providerMetadataFactory()
 {

diff --git a/src/providers/mssql/qgsmssqlprovider.h b/src/providers/mssql/qgsmssqlprovider.h
@@ -25,6 +25,7 @@
 
 #include <QStringList>
 #include <QFile>
+#include <QVariantMap>
 #include <QtSql/QSqlDatabase>
 #include <QtSql/QSqlQuery>
 #include <QtSql/QSqlError>
@@ -128,6 +129,7 @@ class QgsMssqlProvider final: public QgsVectorDataProvider
 
     //! Convert values to quoted values for database work *
     static QString quotedValue( const QVariant &value );
+    static QString quotedIdentifier( const QString &value );
 
     QString defaultValueClause( int fieldId ) const override;
 
@@ -249,6 +251,20 @@ class QgsMssqlProviderMetadata final: public QgsProviderMetadata
       const QMap<QString, QVariant> *options ) override;
     QgsMssqlProvider *createProvider( const QString &uri, const QgsDataProvider::ProviderOptions &options ) override;
     virtual QList< QgsDataItemProvider * > dataItemProviders() const override;
+
+    // Connections API
+    QMap<QString, QgsAbstractProviderConnection *> connections( bool cached = true ) override;
+    QgsAbstractProviderConnection *createConnection( const QString &name ) override;
+    QgsAbstractProviderConnection *createConnection( const QString &uri, const QVariantMap &configuration ) override;
+    void deleteConnection( const QString &name ) override;
+    void saveConnection( const QgsAbstractProviderConnection *createConnection, const QString &name ) override;
+
+    // Data source URI API
+    QVariantMap decodeUri( const QString &uri ) override;
+    QString encodeUri( const QVariantMap &parts ) override;
+
 };
 
+
+
 #endif // QGSMSSQLPROVIDER_H