Improvements to SQL Server connection dialog

- List databases found - Remove message boxes replace with message bar - Split into two columns for nicer layout. Funded by TechnoglogOne, Aus
qgis · Sep 11, 2015 · 14f84d1 · 14f84d1
1 parent e2684a4
commit 14f84d1
Show file tree

Hide file tree

Showing 3 changed files with 383 additions and 228 deletions.
diff --git a/src/providers/mssql/qgsmssqlnewconnection.cpp b/src/providers/mssql/qgsmssqlnewconnection.cpp
@@ -16,8 +16,8 @@
  ***************************************************************************/
 
 #include <QSettings>
-#include <QMessageBox>
 #include <QInputDialog>
+#include <QMessageBox>
 
 #include <QtSql/QSqlDatabase>
 #include <QtSql/QSqlError>
@@ -31,6 +31,8 @@ QgsMssqlNewConnection::QgsMssqlNewConnection( QWidget *parent, const QString& co
 {
   setupUi( this );
 
+  lblWarning->hide();
+
   if ( !connName.isEmpty() )
   {
     // populate the dialog with the information stored for the connection
@@ -40,7 +42,8 @@ QgsMssqlNewConnection::QgsMssqlNewConnection( QWidget *parent, const QString& co
     QString key = "/MSSQL/connections/" + connName;
     txtService->setText( settings.value( key + "/service" ).toString() );
     txtHost->setText( settings.value( key + "/host" ).toString() );
-    txtDatabase->setText( settings.value( key + "/database" ).toString() );
+    listDatabase->addItem( settings.value( key + "/database" ).toString() );
+    listDatabase->setCurrentRow( 0 );
     cb_geometryColumns->setChecked( settings.value( key + "/geometryColumns", true ).toBool() );
     cb_allowGeometrylessTables->setChecked( settings.value( key + "/allowGeometrylessTables", true ).toBool() );
     cb_useEstimatedMetadata->setChecked( settings.value( key + "/estimatedMetadata", false ).toBool() );
@@ -69,15 +72,6 @@ void QgsMssqlNewConnection::accept()
   QString baseKey = "/MSSQL/connections/";
   settings.setValue( baseKey + "selected", txtName->text() );
 
-  if ( chkStorePassword->isChecked() &&
-       QMessageBox::question( this,
-                              tr( "Saving passwords" ),
-                              tr( "WARNING: You have opted to save your password. It will be stored in plain text in your project files and in your home directory on Unix-like systems, or in your user profile on Windows. If you do not want this to happen, please press the Cancel button.\n" ),
-                              QMessageBox::Ok | QMessageBox::Cancel ) == QMessageBox::Cancel )
-  {
-    return;
-  }
-
   // warn if entry was renamed to an existing connection
   if (( mOriginalConnName.isNull() || mOriginalConnName.compare( txtName->text(), Qt::CaseInsensitive ) != 0 ) &&
       ( settings.contains( baseKey + txtName->text() + "/service" ) ||
@@ -98,9 +92,16 @@ void QgsMssqlNewConnection::accept()
   }
 
   baseKey += txtName->text();
+  QString database;
+  QListWidgetItem* item = listDatabase->currentItem();
+  if ( item && item->text() != "(from service)" )
+  {
+    database = item->text();
+  }
+
   settings.setValue( baseKey + "/service", txtService->text() );
   settings.setValue( baseKey + "/host", txtHost->text() );
-  settings.setValue( baseKey + "/database", txtDatabase->text() );
+  settings.setValue( baseKey + "/database", database );
   settings.setValue( baseKey + "/username", chkStoreUsername->isChecked() ? txtUsername->text() : "" );
   settings.setValue( baseKey + "/password", chkStorePassword->isChecked() ? txtPassword->text() : "" );
   settings.setValue( baseKey + "/saveUsername", chkStoreUsername->isChecked() ? "true" : "false" );
@@ -117,6 +118,11 @@ void QgsMssqlNewConnection::on_btnConnect_clicked()
   testConnection();
 }
 
+void QgsMssqlNewConnection::on_btnListDatabase_clicked()
+{
+  listDatabases();
+}
+
 void QgsMssqlNewConnection::on_cb_trustedConnection_clicked()
 {
   if ( cb_trustedConnection->checkState() == Qt::Checked )
@@ -137,32 +143,36 @@ void QgsMssqlNewConnection::on_cb_trustedConnection_clicked()
 
 QgsMssqlNewConnection::~QgsMssqlNewConnection()
 {
+  delete bar;
 }
 
-void QgsMssqlNewConnection::testConnection()
+bool QgsMssqlNewConnection::testConnection( QString testDatabase )
 {
-  if ( txtService->text().isEmpty() )
+  bar->pushMessage( "Testing connection", "....." );
+  // Gross but needed to show the last message.
+  qApp->processEvents();
+
+  if ( txtService->text().isEmpty() && txtHost->text().isEmpty() )
   {
-    if ( txtHost->text().isEmpty() )
-    {
-      QMessageBox::information( this,
-                                tr( "Test connection" ),
-                                tr( "Connection failed - Host name hasn't been specified.\n\n" ) );
-      return;
-    }
+    bar->clearWidgets();
+    bar->pushWarning( tr( "Connection Failed" ), tr( "Host name hasn't been specified' " ) );
+    return false;
+  }
 
-    if ( txtDatabase->text().isEmpty() )
-    {
-      QMessageBox::information( this,
-                                tr( "Test connection" ),
-                                tr( "Connection failed - Database name hasn't been specified.\n\n" ) );
-      return;
-    }
+  QString database;
+  QListWidgetItem* item = listDatabase->currentItem();
+  if ( !testDatabase.isEmpty() )
+  {
+    database = testDatabase;
+  }
+  else if ( item && item->text() != "(from service)" )
+  {
+    database = item->text();
   }
 
   QSqlDatabase db = QgsMssqlProvider::GetDatabase( txtService->text().trimmed(),
                     txtHost->text().trimmed(),
-                    txtDatabase->text().trimmed(),
+                    database,
                     txtUsername->text().trimmed(),
                     txtPassword->text().trimmed() );
 
@@ -171,19 +181,54 @@ void QgsMssqlNewConnection::testConnection()
 
   if ( !db.open() )
   {
-    QMessageBox::information( this,
-                              tr( "Test connection" ),
-                              db.lastError().text() );
+    bar->clearWidgets();
+    bar->pushWarning( tr( "Error opening connection" ), db.lastError().text() );
+    return false;
   }
   else
   {
-    QString dbName = txtDatabase->text();
-    if ( dbName.isEmpty() )
+    if ( database.isEmpty() )
+    {
+      database = txtService->text();
+    }
+    bar->clearWidgets();
+  }
+
+  return true;
+}
+
+void QgsMssqlNewConnection::listDatabases()
+{
+  testConnection( "master" );
+  listDatabase->clear();
+  QString queryStr = "SELECT name FROM master..sysdatabases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')";
+
+  QSqlDatabase db = QgsMssqlProvider::GetDatabase( txtService->text().trimmed(),
+                    txtHost->text().trimmed(),
+                    "master",
+                    txtUsername->text().trimmed(),
+                    txtPassword->text().trimmed() );
+  if ( db.open() )
+  {
+    QSqlQuery query = QSqlQuery( db );
+    query.setForwardOnly( true );
+    query.exec( queryStr );
+
+    if ( !txtService->text().isEmpty() )
     {
-      dbName = txtService->text();
+      listDatabase->addItem( "(from service)" );
     }
-    QMessageBox::information( this,
-                              tr( "Test connection" ),
-                              tr( "Connection to %1 was successful" ).arg( dbName ) );
+
+    if ( query.isActive() )
+    {
+      while ( query.next() )
+      {
+        QString name = query.value( 0 ).toString();
+        listDatabase->addItem( name );
+      }
+      listDatabase->setCurrentRow( 0 );
+    }
+    db.close();
   }
 }
+
diff --git a/src/providers/mssql/qgsmssqlnewconnection.h b/src/providers/mssql/qgsmssqlnewconnection.h
@@ -20,6 +20,7 @@
 #include "qgisgui.h"
 #include "qgscontexthelp.h"
 
+
 /** \class QgsMssqlNewConnection
  * \brief Dialog to allow the user to configure and save connection
  * information for an MSSQL database
@@ -30,12 +31,20 @@ class QgsMssqlNewConnection : public QDialog, private Ui::QgsMssqlNewConnectionB
   public:
     //! Constructor
     QgsMssqlNewConnection( QWidget *parent = 0, const QString& connName = QString::null, Qt::WindowFlags fl = QgisGui::ModalDialogFlags );
+
     //! Destructor
     ~QgsMssqlNewConnection();
+
     //! Tests the connection using the parameters supplied
-    void testConnection();
+    bool testConnection( QString testDatabase = QString() );
+
+    /**
+     * @brief List all databases found for the given server.
+     */
+    void listDatabases();
   public slots:
     void accept() override;
+    void on_btnListDatabase_clicked();
     void on_btnConnect_clicked();
     void on_cb_trustedConnection_clicked();
     void on_buttonBox_helpRequested() { QgsContextHelp::run( metaObject()->className() ); }