ui filter on new connection widget

schemas filtering as option filtering on server side and refactoring minor fixes
qgis · Jan 11, 2021 · 7d8e8ba · 7d8e8ba
1 parent 09f4f4c
commit 7d8e8ba
Show file tree

Hide file tree

Showing 9 changed files with 392 additions and 77 deletions.
diff --git a/src/providers/mssql/qgsmssqlconnection.cpp b/src/providers/mssql/qgsmssqlconnection.cpp
@@ -16,6 +16,7 @@
  ***************************************************************************/
 
 #include "qgsmssqlconnection.h"
+#include "qgsmssqlprovider.h"
 #include "qgslogger.h"
 #include "qgssettings.h"
 #include "qgsdatasourceuri.h"
@@ -324,16 +325,21 @@ QStringList QgsMssqlConnection::schemas( const QString &uri, QString *errorMessa
 // connect to database
   QSqlDatabase db = getDatabase( dsUri.service(), dsUri.host(), dsUri.database(), dsUri.username(), dsUri.password() );
 
-  if ( !openDatabase( db ) )
+  return schemas( db, errorMessage );
+}
+
+QStringList QgsMssqlConnection::schemas( QSqlDatabase &dataBase, QString *errorMessage )
+{
+  if ( !openDatabase( dataBase ) )
   {
     if ( errorMessage )
-      *errorMessage = db.lastError().text();
+      *errorMessage = dataBase.lastError().text();
     return QStringList();
   }
 
   const QString sql = QStringLiteral( "select s.name as schema_name from sys.schemas s" );
 
-  QSqlQuery q = QSqlQuery( db );
+  QSqlQuery q = QSqlQuery( dataBase );
   q.setForwardOnly( true );
   if ( !q.exec( sql ) )
   {
@@ -463,6 +469,74 @@ QList<QgsVectorDataProvider::NativeType> QgsMssqlConnection::nativeTypes()
          ;
 }
 
+QString QgsMssqlConnection::buildQueryForSchemas( const QString &connName )
+{
+  QgsSettings settings;
+
+  QString selectedSchemas;
+
+  QString databaseName = settings.value( QStringLiteral( "/MSSQL/connections/" ) + connName + "/database" ).toString();
+
+  bool schemaFilteringEnabled = settings.value( QStringLiteral( "/MSSQL/connections/" ) + connName + "/schemasFiltering" ).toBool();
+
+  if ( schemaFilteringEnabled )
+  {
+    QVariant schemaSettingsVariant = settings.value( QStringLiteral( "/MSSQL/connections/" ) + connName + "/schemasFiltered" );
+
+    if ( schemaSettingsVariant.type() == QVariant::Map )
+    {
+      QVariantMap schemaSettings = schemaSettingsVariant.toMap();
+      QVariantMap schemaSettingsForDatabase = schemaSettings.value( databaseName ).toMap();
+      //schema filter
+
+
+      QStringList schemaNames;
+      for ( const QString &schemaName : schemaSettingsForDatabase.keys() )
+      {
+        if ( schemaSettingsForDatabase.value( schemaName ).toBool() )
+        {
+          schemaNames.append( QgsMssqlProvider::quotedValue( schemaName ) );
+        }
+      }
+      if ( !schemaNames.empty() )
+        selectedSchemas = schemaNames.join( ',' );
+
+      selectedSchemas.prepend( QStringLiteral( "( " ) );
+      selectedSchemas.append( QStringLiteral( " )" ) );
+
+    }
+  }
+
+  // build sql statement
+  QString query( QStringLiteral( "SELECT " ) );
+  if ( geometryColumnsOnly( connName ) )
+  {
+    query += QStringLiteral( "f_table_schema, f_table_name, f_geometry_column, srid, geometry_type, 0 FROM geometry_columns" );
+    if ( !selectedSchemas.isEmpty() )
+      query += QStringLiteral( " WHERE f_table_schema IN %1" ).arg( selectedSchemas );
+  }
+  else
+  {
+    query += QStringLiteral( "sys.schemas.name, sys.objects.name, sys.columns.name, null, 'GEOMETRY', CASE when sys.objects.type = 'V' THEN 1 ELSE 0 END \n"
+                             "FROM sys.columns JOIN sys.types ON sys.columns.system_type_id = sys.types.system_type_id AND sys.columns.user_type_id = sys.types.user_type_id JOIN sys.objects ON sys.objects.object_id = sys.columns.object_id JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id \n"
+                             "WHERE (sys.types.name = 'geometry' OR sys.types.name = 'geography') AND (sys.objects.type = 'U' OR sys.objects.type = 'V')" );
+    if ( !selectedSchemas.isEmpty() )
+      query += QStringLiteral( " AND (sys.schemas.name IN %1)" ).arg( selectedSchemas );
+  }
+
+  if ( allowGeometrylessTables( connName ) )
+  {
+    query += QStringLiteral( "UNION ALL \n"
+                             "SELECT sys.schemas.name, sys.objects.name, null, null, 'NONE', case when sys.objects.type = 'V' THEN 1 ELSE 0 END \n"
+                             "FROM  sys.objects JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id "
+                             "WHERE NOT EXISTS (SELECT * FROM sys.columns sc1 JOIN sys.types ON sc1.system_type_id = sys.types.system_type_id WHERE (sys.types.name = 'geometry' OR sys.types.name = 'geography') AND sys.objects.object_id = sc1.object_id) AND (sys.objects.type = 'U' or sys.objects.type = 'V')" );
+    if ( !selectedSchemas.isEmpty() )
+      query += QStringLiteral( " AND sys.schemas.name IN %1" ).arg( selectedSchemas );
+  }
+
+  return query;
+}
+
 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
@@ -149,6 +149,11 @@ class QgsMssqlConnection
      */
     static QStringList schemas( const QString &uri, QString *errorMessage );
 
+    /**
+     * Returns a list of all schemas on the \a dataBase.
+     */
+    static QStringList schemas( QSqlDatabase &dataBase, QString *errorMessage );
+
     /**
      * Returns true if the given \a schema is a system schema.
      */
@@ -170,6 +175,11 @@ class QgsMssqlConnection
      */
     static QList<QgsVectorDataProvider::NativeType> nativeTypes();
 
+    /**
+     * Builds and returns a sql query string to obtain schemas list depending on settings
+     * \since QGIS 3.18
+     */
+    static QString buildQueryForSchemas( const QString &connName );
 
   private:
 

diff --git a/src/providers/mssql/qgsmssqldataitemguiprovider.cpp b/src/providers/mssql/qgsmssqldataitemguiprovider.cpp
@@ -171,6 +171,7 @@ void QgsMssqlDataItemGuiProvider::editConnection( QgsDataItem *item )
   {
     // the parent should be updated
     item->parent()->refreshConnections();
+    item->refresh();
   }
 }
 

diff --git a/src/providers/mssql/qgsmssqldataitems.cpp b/src/providers/mssql/qgsmssqldataitems.cpp
@@ -72,6 +72,15 @@ void QgsMssqlConnectionItem::readConnectionSettings()
     mPassword = settings.value( key + "/password" ).toString();
   }
 
+  mSchemaSettings.clear();
+  mSchemasFilteringEnabled = settings.value( key + "/schemasFiltering" ).toBool();
+  if ( mSchemasFilteringEnabled )
+  {
+    QVariant schemasSettingsVariant = settings.value( key + "/schemasFiltered" );
+    if ( schemasSettingsVariant.isValid() && schemasSettingsVariant.type() == QVariant::Map )
+      mSchemaSettings = schemasSettingsVariant.toMap();
+  }
+
   mUseGeometryColumns = QgsMssqlConnection::geometryColumnsOnly( mName );
   mUseEstimatedMetadata = QgsMssqlConnection::useEstimatedMetadata( mName );
   mAllowGeometrylessTables = QgsMssqlConnection::allowGeometrylessTables( mName );
@@ -99,28 +108,22 @@ void QgsMssqlConnectionItem::refresh()
   QgsDebugMsgLevel( "mPath = " + mPath, 3 );
   stop();
 
-  // read up the schemas and layers from database
-  QVector<QgsDataItem *> items = createChildren();
-
-  // Add new items
-  const auto constItems = items;
-  for ( QgsDataItem *item : constItems )
+  // Clear all children
+  const QVector<QgsDataItem *> allChidren = children();
+  for ( QgsDataItem *item : allChidren )
   {
-    // Is it present in children?
-    int index = findItem( mChildren, item );
-    if ( index >= 0 )
-    {
-      static_cast< QgsMssqlSchemaItem * >( mChildren.at( index ) )->addLayers( item );
-      delete item;
-      continue;
-    }
-    addChildItem( item, true );
+    removeChildItem( item );
+    delete item;
   }
+
+  // read up the schemas and layers from database
+  const QVector<QgsDataItem *> items = createChildren();
+  for ( QgsDataItem *item : items )
+    addChildItem( item, true );
 }
 
 QVector<QgsDataItem *> QgsMssqlConnectionItem::createChildren()
 {
-
   setState( Populating );
 
   stop();
@@ -141,20 +144,7 @@ QVector<QgsDataItem *> QgsMssqlConnectionItem::createChildren()
   }
 
   // build sql statement
-  QString query( QStringLiteral( "select " ) );
-  if ( mUseGeometryColumns )
-  {
-    query += QLatin1String( "f_table_schema, f_table_name, f_geometry_column, srid, geometry_type, 0 from geometry_columns" );
-  }
-  else
-  {
-    query += QLatin1String( "sys.schemas.name, sys.objects.name, sys.columns.name, null, 'GEOMETRY', case when sys.objects.type = 'V' then 1 else 0 end from sys.columns join sys.types on sys.columns.system_type_id = sys.types.system_type_id and sys.columns.user_type_id = sys.types.user_type_id join sys.objects on sys.objects.object_id = sys.columns.object_id join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id where (sys.types.name = 'geometry' or sys.types.name = 'geography') and (sys.objects.type = 'U' or sys.objects.type = 'V')" );
-  }
-
-  if ( mAllowGeometrylessTables )
-  {
-    query += QLatin1String( " union all select sys.schemas.name, sys.objects.name, null, null, 'NONE', case when sys.objects.type = 'V' then 1 else 0 end from sys.objects join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id where not exists (select * from sys.columns sc1 join sys.types on sc1.system_type_id = sys.types.system_type_id where (sys.types.name = 'geometry' or sys.types.name = 'geography') and sys.objects.object_id = sc1.object_id) and (sys.objects.type = 'U' or sys.objects.type = 'V')" );
-  }
+  QString query = QgsMssqlConnection::buildQueryForSchemas( mName );
 
   const bool disableInvalidGeometryHandling = QgsMssqlConnection::isInvalidGeometryHandlingDisabled( mName );
 
@@ -262,11 +252,16 @@ QVector<QgsDataItem *> QgsMssqlConnectionItem::createChildren()
       }
     }
 
+
     // add missing schemas (i.e., empty schemas)
     const QString uri = connInfo();
     const QStringList allSchemas = QgsMssqlConnection::schemas( uri, nullptr );
+    QVariantMap schemaSettings = mSchemaSettings.value( mDatabase ).toMap();
     for ( const QString &schema : allSchemas )
     {
+      if ( mSchemasFilteringEnabled && !schemaSettings.value( schema ).toBool() )
+        continue;  // user does not want it to be shown
+
       if ( addedSchemas.contains( schema ) )
         continue;
 
@@ -279,6 +274,8 @@ QVector<QgsDataItem *> QgsMssqlConnectionItem::createChildren()
       children.append( schemaItem );
     }
 
+
+
     // spawn threads (new layers will be added later on)
     if ( mColumnTypeThread )
     {

diff --git a/src/providers/mssql/qgsmssqldataitems.h b/src/providers/mssql/qgsmssqldataitems.h
@@ -91,6 +91,8 @@ class QgsMssqlConnectionItem : public QgsDataCollectionItem
     bool mUseEstimatedMetadata;
     bool mAllowGeometrylessTables;
     QgsMssqlGeomColumnTypeThread *mColumnTypeThread = nullptr;
+    QVariantMap mSchemaSettings;
+    bool mSchemasFilteringEnabled = false;
 
     void readConnectionSettings();
     void stop();