[mssql] Misc browser fixes

- Add 'refresh' action to allow display of displays created outside of QGIS - Show empty schemas in browser too, as they can be useful for drag and dropping new data sets to import into - Add create schema item
qgis · Oct 9, 2018 · b6e6b90 · b6e6b90
1 parent e813fe8
commit b6e6b90
Show file tree

Hide file tree

Showing 4 changed files with 195 additions and 5 deletions.
diff --git a/src/providers/mssql/qgsmssqlconnection.cpp b/src/providers/mssql/qgsmssqlconnection.cpp
@@ -23,6 +23,7 @@
 #include <QThread>
 #include <QSqlError>
 #include <QSqlQuery>
+#include <QSet>
 
 int QgsMssqlConnection::sConnectionId = 0;
 
@@ -218,6 +219,88 @@ bool QgsMssqlConnection::truncateTable( const QString &uri, QString *errorMessag
   return true;
 }
 
+bool QgsMssqlConnection::createSchema( const QString &uri, const QString &schemaName, QString *errorMessage )
+{
+  QgsDataSourceUri dsUri( uri );
+
+  // connect to database
+  QSqlDatabase db = getDatabase( dsUri.service(), dsUri.host(), dsUri.database(), dsUri.username(), dsUri.password() );
+
+  if ( !openDatabase( db ) )
+  {
+    if ( errorMessage )
+      *errorMessage = db.lastError().text();
+    return false;
+  }
+
+  QSqlQuery q = QSqlQuery( db );
+  q.setForwardOnly( true );
+  const QString sql = QStringLiteral( "CREATE SCHEMA [%1]" ).arg( schemaName );
+  if ( !q.exec( sql ) )
+  {
+    if ( errorMessage )
+      *errorMessage = q.lastError().text();
+    return false;
+  }
+
+  return true;
+}
+
+QStringList QgsMssqlConnection::schemas( const QString &uri, QString *errorMessage )
+{
+  QgsDataSourceUri dsUri( uri );
+
+// connect to database
+  QSqlDatabase db = getDatabase( dsUri.service(), dsUri.host(), dsUri.database(), dsUri.username(), dsUri.password() );
+
+  if ( !openDatabase( db ) )
+  {
+    if ( errorMessage )
+      *errorMessage = db.lastError().text();
+    return QStringList();
+  }
+
+  const QString sql = QStringLiteral( "select s.name as schema_name from sys.schemas s" );
+
+  QSqlQuery q = QSqlQuery( db );
+  q.setForwardOnly( true );
+  if ( !q.exec( sql ) )
+  {
+    if ( errorMessage )
+      *errorMessage = q.lastError().text();
+    return QStringList();
+  }
+
+  QStringList result;
+
+  while ( q.next() )
+  {
+    const QString schemaName = q.value( 0 ).toString();
+    result << schemaName;
+  }
+  return result;
+}
+
+bool QgsMssqlConnection::isSystemSchema( const QString &schema )
+{
+  static QSet< QString > sSystemSchemas
+  {
+    QStringLiteral( "db_owner" ),
+    QStringLiteral( "db_securityadmin" ),
+    QStringLiteral( "db_accessadmin" ),
+    QStringLiteral( "db_backupoperator" ),
+    QStringLiteral( "db_ddladmin" ),
+    QStringLiteral( "db_datawriter" ),
+    QStringLiteral( "db_datareader" ),
+    QStringLiteral( "db_denydatawriter" ),
+    QStringLiteral( "db_denydatareader" ),
+    QStringLiteral( "INFORMATION_SCHEMA" ),
+    QStringLiteral( "sys" )
+  };
+
+  return sSystemSchemas.contains( schema );
+}
+
 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
@@ -18,6 +18,8 @@
 #ifndef QGSMSSQLCONNECTION_H
 #define QGSMSSQLCONNECTION_H
 
+#include <QStringList>
+
 class QString;
 class QSqlDatabase;
 
@@ -128,6 +130,21 @@ class QgsMssqlConnection
      */
     static bool truncateTable( const QString &uri, QString *errorMessage );
 
+    /**
+     * Creates a new schema under connection specified by \a uri.
+     */
+    static bool createSchema( const QString &uri, const QString &schemaName, QString *errorMessage );
+
+    /**
+     * Returns a list of all schemas on the connection specified \a uri.
+     */
+    static QStringList schemas( const QString &uri, QString *errorMessage );
+
+    /**
+     * Returns true if the given \a schema is a system schema.
+     */
+    static bool isSystemSchema( const QString &schema );
+
   private:
 
     /**

diff --git a/src/providers/mssql/qgsmssqldataitems.cpp b/src/providers/mssql/qgsmssqldataitems.cpp
@@ -38,6 +38,7 @@
 #include <QSqlDatabase>
 #include <QSqlError>
 #include <QProgressDialog>
+#include <QInputDialog>
 
 // ---------------------------------------------------------------------------
 QgsMssqlConnectionItem::QgsMssqlConnectionItem( QgsDataItem *parent, const QString &name, const QString &path )
@@ -161,6 +162,8 @@ QVector<QgsDataItem *> QgsMssqlConnectionItem::createChildren()
   q.setForwardOnly( true );
   ( void )q.exec( query );
 
+  QSet< QString > addedSchemas;
+
   if ( q.isActive() )
   {
     QVector<QgsDataItem *> newLayers;
@@ -214,6 +217,7 @@ QVector<QgsDataItem *> QgsMssqlConnectionItem::createChildren()
       {
         schemaItem = new QgsMssqlSchemaItem( this, layer.schemaName, mPath + '/' + layer.schemaName );
         schemaItem->setState( Populating );
+        addedSchemas.insert( layer.schemaName );
         children.append( schemaItem );
       }
 
@@ -251,6 +255,23 @@ QVector<QgsDataItem *> QgsMssqlConnectionItem::createChildren()
       }
     }
 
+    // add missing schemas (i.e., empty schemas)
+    const QString uri = connInfo();
+    const QStringList allSchemas = QgsMssqlConnection::schemas( uri, nullptr );
+    for ( const QString &schema : allSchemas )
+    {
+      if ( addedSchemas.contains( schema ) )
+        continue;
+
+      if ( QgsMssqlConnection::isSystemSchema( schema ) )
+        continue;
+
+      QgsMssqlSchemaItem *schemaItem = new QgsMssqlSchemaItem( this, schema, mPath + '/' + schema );
+      schemaItem->setState( Populated ); // no tables
+      addedSchemas.insert( schema );
+      children.append( schemaItem );
+    }
+
     // spawn threads (new layers will be added later on)
     if ( mColumnTypeThread )
     {
@@ -343,11 +364,20 @@ bool QgsMssqlConnectionItem::equal( const QgsDataItem *other )
 QList<QAction *> QgsMssqlConnectionItem::actions( QWidget *parent )
 {
   QList<QAction *> lst;
-  QAction *actionShowNoGeom = new QAction( tr( "Show Non-Spatial Tables" ), parent );
-  actionShowNoGeom->setCheckable( true );
-  actionShowNoGeom->setChecked( mAllowGeometrylessTables );
-  connect( actionShowNoGeom, &QAction::toggled, this, &QgsMssqlConnectionItem::setAllowGeometrylessTables );
-  lst.append( actionShowNoGeom );
+
+  QAction *actionRefresh = new QAction( tr( "Refresh" ), parent );
+  connect( actionRefresh, &QAction::triggered, this, [ = ]
+  {
+    refresh();
+    if ( mParent )
+      mParent->refreshConnections();
+  } );
+  lst.append( actionRefresh );
+
+  QAction *separator = new QAction( parent );
+  separator->setSeparator( true );
+  lst.append( separator );
+
 
   QAction *actionEdit = new QAction( tr( "Edit Connection…" ), parent );
   connect( actionEdit, &QAction::triggered, this, &QgsMssqlConnectionItem::editConnection );
@@ -357,6 +387,20 @@ QList<QAction *> QgsMssqlConnectionItem::actions( QWidget *parent )
   connect( actionDelete, &QAction::triggered, this, &QgsMssqlConnectionItem::deleteConnection );
   lst.append( actionDelete );
 
+  QAction *sep = new QAction( parent );
+  sep->setSeparator( true );
+  lst << sep;
+
+  QAction *actionShowNoGeom = new QAction( tr( "Show Non-spatial Tables" ), parent );
+  actionShowNoGeom->setCheckable( true );
+  actionShowNoGeom->setChecked( mAllowGeometrylessTables );
+  connect( actionShowNoGeom, &QAction::toggled, this, &QgsMssqlConnectionItem::setAllowGeometrylessTables );
+  lst.append( actionShowNoGeom );
+
+  QAction *actionCreateSchema = new QAction( tr( "Create Schema…" ), parent );
+  connect( actionCreateSchema, &QAction::triggered, this, &QgsMssqlConnectionItem::createSchema );
+  lst.append( actionCreateSchema );
+
   return lst;
 }
 
@@ -367,6 +411,27 @@ void QgsMssqlConnectionItem::setAllowGeometrylessTables( const bool allow )
   refresh();
 }
 
+void QgsMssqlConnectionItem::createSchema()
+{
+  QString schemaName = QInputDialog::getText( nullptr, tr( "Create Schema" ), tr( "Schema name:" ) );
+  if ( schemaName.isEmpty() )
+    return;
+
+  QString uri = connInfo();
+  QString error;
+  if ( !QgsMssqlConnection::createSchema( uri, schemaName, &error ) )
+  {
+    QMessageBox::warning( nullptr, tr( "Create Schema" ), tr( "Unable to create schema %1\n%2" ).arg( schemaName,
+                          error ) );
+    return;
+  }
+
+  refresh();
+  // the parent should be updated
+  if ( mParent )
+    mParent->refreshConnections();
+}
+
 void QgsMssqlConnectionItem::editConnection()
 {
   QgsMssqlNewConnection nc( nullptr, mName );
@@ -379,6 +444,11 @@ void QgsMssqlConnectionItem::editConnection()
 
 void QgsMssqlConnectionItem::deleteConnection()
 {
+  if ( QMessageBox::question( nullptr, QObject::tr( "Delete Connection" ),
+                              QObject::tr( "Are you sure you want to delete the connection to %1?" ).arg( mName ),
+                              QMessageBox::Yes | QMessageBox::No, QMessageBox::No ) != QMessageBox::Yes )
+    return;
+
   QgsMssqlSourceSelect::deleteConnection( mName );
   // the parent should be updated
   mParent->refreshConnections();
@@ -588,6 +658,21 @@ QVector<QgsDataItem *> QgsMssqlSchemaItem::createChildren()
   return QVector<QgsDataItem *>();
 }
 
+QList<QAction *> QgsMssqlSchemaItem::actions( QWidget *parent )
+{
+  QList<QAction *> lst;
+
+  QAction *actionRefresh = new QAction( tr( "Refresh" ), parent );
+  connect( actionRefresh, &QAction::triggered, this, [ = ]
+  {
+    if ( mParent )
+      mParent->refresh();
+  } );
+  lst.append( actionRefresh );
+
+  return lst;
+}
+
 void QgsMssqlSchemaItem::addLayers( QgsDataItem *newLayers )
 {
   // Add new items

diff --git a/src/providers/mssql/qgsmssqldataitems.h b/src/providers/mssql/qgsmssqldataitems.h
@@ -80,6 +80,7 @@ class QgsMssqlConnectionItem : public QgsDataCollectionItem
     void editConnection();
     void deleteConnection();
     void setAllowGeometrylessTables( bool allow );
+    void createSchema();
 #endif
 
     void setLayerType( QgsMssqlLayerProperty layerProperty );
@@ -113,6 +114,10 @@ class QgsMssqlSchemaItem : public QgsDataCollectionItem
 
     QVector<QgsDataItem *> createChildren() override;
 
+#ifdef HAVE_GUI
+    QList<QAction *> actions( QWidget *parent ) override;
+#endif
+
     QgsMssqlLayerItem *addLayer( const QgsMssqlLayerProperty &layerProperty, bool refresh );
     void refresh() override {} // do not refresh directly
     void addLayers( QgsDataItem *newLayers );