[mssql] Minor refactoring

qgis · Oct 8, 2018 · fabc2c1 · fabc2c1
1 parent 7c5cc27
commit fabc2c1
Show file tree

Hide file tree

Showing 10 changed files with 199 additions and 123 deletions.
diff --git a/src/providers/mssql/CMakeLists.txt b/src/providers/mssql/CMakeLists.txt
@@ -1,4 +1,5 @@
 SET(MSSQL_SRCS
+  qgsmssqlconnection.cpp
   qgsmssqlprovider.cpp
   qgsmssqlgeometryparser.cpp
   qgsmssqltablemodel.cpp

diff --git a/src/providers/mssql/qgsmssqlconnection.cpp b/src/providers/mssql/qgsmssqlconnection.cpp
@@ -0,0 +1,115 @@
+/***************************************************************************
+                             qgsmssqlconnection.cpp
+                             ----------------------
+    begin                : October 2018
+    copyright            : (C) 2018 by Nyall Dawson
+    email                : nyall dot dawson 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 "qgsmssqlconnection.h"
+#include "qgslogger.h"
+#include <QSqlDatabase>
+#include <QThread>
+
+int QgsMssqlConnection::sConnectionId = 0;
+
+QSqlDatabase QgsMssqlConnection::getDatabase( const QString &service, const QString &host, const QString &database, const QString &username, const QString &password )
+{
+  QSqlDatabase db;
+  QString connectionName;
+
+  // create a separate database connection for each feature source
+  if ( service.isEmpty() )
+  {
+    if ( !host.isEmpty() )
+      connectionName = host + '.';
+
+    if ( database.isEmpty() )
+    {
+      QgsDebugMsg( QStringLiteral( "QgsMssqlProvider database name not specified" ) );
+      return db;
+    }
+
+    connectionName += QStringLiteral( "%1.%2" ).arg( database ).arg( sConnectionId++ );
+  }
+  else
+    connectionName = service;
+
+  const QString threadSafeConnectionName = dbConnectionName( connectionName );
+  if ( !QSqlDatabase::contains( threadSafeConnectionName ) )
+  {
+    db = QSqlDatabase::addDatabase( QStringLiteral( "QODBC" ), threadSafeConnectionName );
+    db.setConnectOptions( QStringLiteral( "SQL_ATTR_CONNECTION_POOLING=SQL_CP_ONE_PER_HENV" ) );
+  }
+  else
+    db = QSqlDatabase::database( threadSafeConnectionName );
+
+  db.setHostName( host );
+  QString connectionString;
+  if ( !service.isEmpty() )
+  {
+    // driver was specified explicitly
+    connectionString = service;
+  }
+  else
+  {
+#ifdef Q_OS_WIN
+    connectionString = "driver={SQL Server}";
+#else
+    connectionString = QStringLiteral( "driver={FreeTDS};port=1433" );
+#endif
+  }
+
+  if ( !host.isEmpty() )
+    connectionString += ";server=" + host;
+
+  if ( !database.isEmpty() )
+    connectionString += ";database=" + database;
+
+  if ( password.isEmpty() )
+    connectionString += QLatin1String( ";trusted_connection=yes" );
+  else
+    connectionString += ";uid=" + username + ";pwd=" + password;
+
+  if ( !username.isEmpty() )
+    db.setUserName( username );
+
+  if ( !password.isEmpty() )
+    db.setPassword( password );
+
+  db.setDatabaseName( connectionString );
+
+  // only uncomment temporarily -- it can show connection password otherwise!
+  // QgsDebugMsg( connectionString );
+  return db;
+}
+
+bool QgsMssqlConnection::openDatabase( QSqlDatabase &db )
+{
+  if ( !db.isOpen() )
+  {
+    if ( !db.open() )
+    {
+      return false;
+    }
+  }
+  return true;
+}
+
+QString QgsMssqlConnection::dbConnectionName( const QString &name )
+{
+  // Starting with Qt 5.11, sharing the same connection between threads is not allowed.
+  // We use a dedicated connection for each thread requiring access to the database,
+  // using the thread address as connection name.
+  const QString threadAddress = QStringLiteral( ":0x%1" ).arg( QString::number( reinterpret_cast< quintptr >( QThread::currentThread() ), 16 ) );
+  return name + threadAddress;
+}
diff --git a/src/providers/mssql/qgsmssqlconnection.h b/src/providers/mssql/qgsmssqlconnection.h
@@ -0,0 +1,55 @@
+/***************************************************************************
+                             qgsmssqlconnection.h
+                             --------------------
+    begin                : October 2018
+    copyright            : (C) 2018 by Nyall Dawson
+    email                : nyall dot dawson 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 QGSMSSQLCONNECTION_H
+#define QGSMSSQLCONNECTION_H
+
+class QString;
+class QSqlDatabase;
+
+/**
+ * \class QgsMssqlProvider
+ * Connection handler for SQL Server provider
+ *
+*/
+class QgsMssqlConnection
+{
+
+  public:
+
+    /**
+     * Returns a QSqlDatabase object for queries to SQL Server.
+     *
+     * The database may not be open -- openDatabase() should be called to
+     * ensure that it is ready for use.
+     */
+    static QSqlDatabase getDatabase( const QString &service, const QString &host, const QString &database, const QString &username, const QString &password );
+
+
+    static bool openDatabase( QSqlDatabase &db );
+
+  private:
+
+    /**
+     * Returns a thread-safe connection name for use with QSqlDatabase
+     */
+    static QString dbConnectionName( const QString &name );
+
+    static int sConnectionId;
+};
+
+#endif // QGSMSSQLCONNECTION_H
diff --git a/src/providers/mssql/qgsmssqldataitems.cpp b/src/providers/mssql/qgsmssqldataitems.cpp
@@ -26,6 +26,7 @@
 #include "qgsmssqlprovider.h"
 #include "qgssettings.h"
 #include "qgsmessageoutput.h"
+#include "qgsmssqlconnection.h"
 
 #ifdef HAVE_GUI
 #include "qgsmssqlsourceselect.h"
@@ -128,9 +129,9 @@ QVector<QgsDataItem *> QgsMssqlConnectionItem::createChildren()
 
   readConnectionSettings();
 
-  QSqlDatabase db = QgsMssqlProvider::GetDatabase( mService, mHost, mDatabase, mUsername, mPassword );
+  QSqlDatabase db = QgsMssqlConnection::getDatabase( mService, mHost, mDatabase, mUsername, mPassword );
 
-  if ( !QgsMssqlProvider::OpenDatabase( db ) )
+  if ( !QgsMssqlConnection::openDatabase( db ) )
   {
     children.append( new QgsErrorItem( this, db.lastError().text(), mPath + "/error" ) );
     return children;

diff --git a/src/providers/mssql/qgsmssqlfeatureiterator.cpp b/src/providers/mssql/qgsmssqlfeatureiterator.cpp
@@ -21,6 +21,7 @@
 #include "qgslogger.h"
 #include "qgssettings.h"
 #include "qgsexception.h"
+#include "qgsmssqlconnection.h"
 
 #include <QObject>
 #include <QTextStream>
@@ -300,7 +301,7 @@ bool QgsMssqlFeatureIterator::fetchFeature( QgsFeature &feature )
   {
     // No existing connection, so set it up now. It's safe to do here as we're now in
     // the thread were iteration is actually occurring.
-    mDatabase = QgsMssqlProvider::GetDatabase( mSource->mService, mSource->mHost, mSource->mDatabaseName, mSource->mUserName, mSource->mPassword );
+    mDatabase = QgsMssqlConnection::getDatabase( mSource->mService, mSource->mHost, mSource->mDatabaseName, mSource->mUserName, mSource->mPassword );
 
     if ( !mDatabase.open() )
     {

diff --git a/src/providers/mssql/qgsmssqlgeomcolumntypethread.cpp b/src/providers/mssql/qgsmssqlgeomcolumntypethread.cpp
@@ -19,6 +19,7 @@
 
 #include "qgslogger.h"
 #include "qgsmssqlprovider.h"
+#include "qgsmssqlconnection.h"
 
 QgsMssqlGeomColumnTypeThread::QgsMssqlGeomColumnTypeThread( const QString &service, const QString &host, const QString &database, const QString &username, const QString &password, bool useEstimatedMetadata )
   : mService( service )
@@ -71,8 +72,8 @@ void QgsMssqlGeomColumnTypeThread::run()
                             layerProperty.sql.isEmpty() ? QString() : QStringLiteral( " AND %1" ).arg( layerProperty.sql ) );
 
       // issue the sql query
-      QSqlDatabase db = QgsMssqlProvider::GetDatabase( mService, mHost, mDatabase, mUsername, mPassword );
-      if ( !QgsMssqlProvider::OpenDatabase( db ) )
+      QSqlDatabase db = QgsMssqlConnection::getDatabase( mService, mHost, mDatabase, mUsername, mPassword );
+      if ( !QgsMssqlConnection::openDatabase( db ) )
       {
         QgsDebugMsg( db.lastError().text() );
         continue;

diff --git a/src/providers/mssql/qgsmssqlnewconnection.cpp b/src/providers/mssql/qgsmssqlnewconnection.cpp
@@ -24,6 +24,7 @@
 #include "qgsmssqlnewconnection.h"
 #include "qgsmssqlprovider.h"
 #include "qgssettings.h"
+#include "qgsmssqlconnection.h"
 
 QgsMssqlNewConnection::QgsMssqlNewConnection( QWidget *parent, const QString &connName, Qt::WindowFlags fl )
   : QDialog( parent, fl )
@@ -170,7 +171,7 @@ bool QgsMssqlNewConnection::testConnection( const QString &testDatabase )
     database = item->text();
   }
 
-  QSqlDatabase db = QgsMssqlProvider::GetDatabase( txtService->text().trimmed(),
+  QSqlDatabase db = QgsMssqlConnection::getDatabase( txtService->text().trimmed(),
                     txtHost->text().trimmed(),
                     database,
                     txtUsername->text().trimmed(),
@@ -203,7 +204,7 @@ void QgsMssqlNewConnection::listDatabases()
   listDatabase->clear();
   QString queryStr = QStringLiteral( "SELECT name FROM master..sysdatabases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')" );
 
-  QSqlDatabase db = QgsMssqlProvider::GetDatabase( txtService->text().trimmed(),
+  QSqlDatabase db = QgsMssqlConnection::getDatabase( txtService->text().trimmed(),
                     txtHost->text().trimmed(),
                     QStringLiteral( "master" ),
                     txtUsername->text().trimmed(),