Skip to content

Commit

Permalink
schemas filtering for QgsMssqlProviderConnection
Browse files Browse the repository at this point in the history
  • Loading branch information
@vcloarec @wonder-sk
vcloarec authored and wonder-sk committed Jan 11, 2021
1 parent e6a2e1f commit f81737c
Show file tree
Hide file tree
Showing 9 changed files with 190 additions and 110 deletions.
92 changes: 61 additions & 31 deletions src/providers/mssql/qgsmssqlconnection.cpp
View file
Expand Up @@ -429,6 +429,10 @@ QgsDataSourceUri QgsMssqlConnection::connUri( const QString &connName )
}
}

QStringList excludedSchemas = QgsMssqlConnection::excludedSchemasList( connName );
if ( !excludedSchemas.isEmpty() )
uri.setParam( QStringLiteral( "excludedSchemas" ), excludedSchemas.join( ',' ) );

return uri;
}

Expand Down Expand Up @@ -469,59 +473,80 @@ QList<QgsVectorDataProvider::NativeType> QgsMssqlConnection::nativeTypes()
;
}

QString QgsMssqlConnection::buildQueryForSchemas( const QString &connName, bool allowTablesWithNoGeometry )
QStringList QgsMssqlConnection::excludedSchemasList( const QString &connName )
{
QgsSettings settings;

QString selectedSchemas;

QString databaseName = settings.value( QStringLiteral( "/MSSQL/connections/" ) + connName + "/database" ).toString();

return excludedSchemasList( connName, databaseName );
}

QStringList QgsMssqlConnection::excludedSchemasList( const QString &connName, const QString &database )
{
QgsSettings settings;
bool schemaFilteringEnabled = settings.value( QStringLiteral( "/MSSQL/connections/" ) + connName + "/schemasFiltering" ).toBool();

if ( schemaFilteringEnabled )
{
QVariant schemaSettingsVariant = settings.value( QStringLiteral( "/MSSQL/connections/" ) + connName + "/schemasFiltered" );
QVariant schemaSettingsVariant = settings.value( QStringLiteral( "/MSSQL/connections/" ) + connName + "/excludedSchemas" );

if ( schemaSettingsVariant.type() == QVariant::Map )
{
QVariantMap schemaSettings = schemaSettingsVariant.toMap();
QVariantMap schemaSettingsForDatabase = schemaSettings.value( databaseName ).toMap();
//schema filter
const QVariantMap schemaSettings = schemaSettingsVariant.toMap();
if ( schemaSettings.contains( database ) && schemaSettings.value( database ).type() == QVariant::StringList )
return schemaSettings.value( database ).toStringList();
}
}

return QStringList();
}

QStringList schemaNames;
for ( const QString &schemaName : schemaSettingsForDatabase.keys() )
{
if ( schemaSettingsForDatabase.value( schemaName ).toBool() )
{
schemaNames.append( QgsMssqlProvider::quotedValue( schemaName ) );
}
}
if ( !schemaNames.empty() )
selectedSchemas = schemaNames.join( ',' );
void QgsMssqlConnection::setExcludedSchemasList( const QString &connName, const QStringList &excludedSchemas )
{
QgsSettings settings;

selectedSchemas.prepend( QStringLiteral( "( " ) );
selectedSchemas.append( QStringLiteral( " )" ) );
QString currentDatabaseName = settings.value( QStringLiteral( "/MSSQL/connections/" ) + connName + "/database" ).toString();
setExcludedSchemasList( connName, currentDatabaseName, excludedSchemas );
}

}
void QgsMssqlConnection::setExcludedSchemasList( const QString &connName, const QString &database, const QStringList &excludedSchemas )
{
QgsSettings settings;
settings.setValue( QStringLiteral( "/MSSQL/connections/" ) + connName + "/schemasFiltering", excludedSchemas.isEmpty() ? 0 : 1 );

QVariant schemaSettingsVariant = settings.value( QStringLiteral( "/MSSQL/connections/" ) + connName + "/excludedSchemas" );
QVariantMap schemaSettings = schemaSettingsVariant.toMap();
schemaSettings.insert( database, excludedSchemas );
settings.setValue( QStringLiteral( "/MSSQL/connections/" ) + connName + "/excludedSchemas", schemaSettings );
}

QString QgsMssqlConnection::buildQueryForTables( bool allowTablesWithNoGeometry, bool geometryColumnOnly, const QStringList &excludedSchemaList )
{
QString notSelectedSchemas;
if ( !excludedSchemaList.isEmpty() )
{
QStringList quotedSchemas;
for ( const QString &sch : excludedSchemaList )
quotedSchemas.append( QgsMssqlProvider::quotedValue( sch ) );
notSelectedSchemas = quotedSchemas.join( ',' );
notSelectedSchemas.prepend( QStringLiteral( "( " ) );
notSelectedSchemas.append( QStringLiteral( " )" ) );
}

// build sql statement
QString query( QStringLiteral( "SELECT " ) );
if ( geometryColumnsOnly( connName ) )
if ( geometryColumnOnly )
{
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 );
if ( !notSelectedSchemas.isEmpty() )
query += QStringLiteral( " WHERE f_table_schema NOT IN %1" ).arg( notSelectedSchemas );
}
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 ( !notSelectedSchemas.isEmpty() )
query += QStringLiteral( " AND (sys.schemas.name NOT IN %1)" ).arg( notSelectedSchemas );
}

if ( allowTablesWithNoGeometry )
Expand All @@ -530,16 +555,21 @@ QString QgsMssqlConnection::buildQueryForSchemas( const QString &connName, bool
"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 );
if ( !notSelectedSchemas.isEmpty() )
query += QStringLiteral( " AND sys.schemas.name NOT IN %1" ).arg( notSelectedSchemas );
}

return query;
}

QString QgsMssqlConnection::buildQueryForSchemas( const QString &connName )
QString QgsMssqlConnection::buildQueryForTables( const QString &connName, bool allowTablesWithNoGeometry )
{
return buildQueryForTables( allowTablesWithNoGeometry, geometryColumnsOnly( connName ), excludedSchemasList( connName ) );
}

QString QgsMssqlConnection::buildQueryForTables( const QString &connName )
{
return buildQueryForSchemas( connName, allowGeometrylessTables( connName ) );
return buildQueryForTables( allowGeometrylessTables( connName ), geometryColumnsOnly( connName ), excludedSchemasList( connName ) );
}

QString QgsMssqlConnection::dbConnectionName( const QString &name )
Expand Down
39 changes: 35 additions & 4 deletions src/providers/mssql/qgsmssqlconnection.h
View file
Expand Up @@ -45,7 +45,6 @@ class QgsMssqlConnection
*/
static QSqlDatabase getDatabase( const QString &service, const QString &host, const QString &database, const QString &username, const QString &password );


static bool openDatabase( QSqlDatabase &db );

/**
Expand Down Expand Up @@ -151,6 +150,7 @@ class QgsMssqlConnection

/**
* Returns a list of all schemas on the \a dataBase.
* \since QGIS 3.18
*/
static QStringList schemas( QSqlDatabase &dataBase, QString *errorMessage );

Expand All @@ -176,16 +176,47 @@ class QgsMssqlConnection
static QList<QgsVectorDataProvider::NativeType> nativeTypes();

/**
* Builds and returns a sql query string to obtain schemas list depending on settings and \a allowTablesWithNoGeometry
* Returns a list of excluded schemas for connection \a connName depending on settings, returns empty list if nothing is set for this connection
* \since QGIS 3.18
*/
static QStringList excludedSchemasList( const QString &connName );

/**
* Returns a list of excluded schemas for connection \a connName for a specific \a database depending on settings, returns empty list if nothing is set for this connection
* \since QGIS 3.18
*/
static QStringList excludedSchemasList( const QString &connName, const QString &database );

/**
* Sets a list of excluded schemas for connection \a connName depending on settings, returns empty list if nothing is set for this connection
* \since QGIS 3.18
*/
static void setExcludedSchemasList( const QString &connName, const QStringList &excludedSchemas );

/**
* Sets a list of excluded schemas for connection \a connName for a specific \a database depending on settings, returns empty list if nothing is set for this connection
* \since QGIS 3.18
*/
static void setExcludedSchemasList( const QString &connName, const QString &database, const QStringList &excludedSchemas );

/**
* Builds and returns a sql query string to obtain tables list depending on \a allowTablesWithNoGeometry, \a geometryColumnOnly and on \a notSelectedSchemasList
* \since QGIS 3.18
*/
static QString buildQueryForTables( bool allowTablesWithNoGeometry, bool geometryColumnOnly, const QStringList &excludedSchemaList = QStringList() );


/**
* Builds and returns a sql query string to obtain tables list depending on settings and \a allowTablesWithNoGeometry
* \since QGIS 3.18
*/
static QString buildQueryForSchemas( const QString &connName, bool allowTablesWithNoGeometry );
static QString buildQueryForTables( const QString &connName, bool allowTablesWithNoGeometry );

/**
* Builds and returns a sql query string to obtain schemas list depending only on settings
* \since QGIS 3.18
*/
static QString buildQueryForSchemas( const QString &connName );
static QString buildQueryForTables( const QString &connName );

private:

Expand Down
12 changes: 5 additions & 7 deletions src/providers/mssql/qgsmssqldataitems.cpp
View file
Expand Up @@ -74,9 +74,10 @@ void QgsMssqlConnectionItem::readConnectionSettings()

mSchemaSettings.clear();
mSchemasFilteringEnabled = settings.value( key + "/schemasFiltering" ).toBool();

if ( mSchemasFilteringEnabled )
{
QVariant schemasSettingsVariant = settings.value( key + "/schemasFiltered" );
QVariant schemasSettingsVariant = settings.value( key + "/excludedSchemas" );
if ( schemasSettingsVariant.isValid() && schemasSettingsVariant.type() == QVariant::Map )
mSchemaSettings = schemasSettingsVariant.toMap();
}
Expand Down Expand Up @@ -144,7 +145,7 @@ QVector<QgsDataItem *> QgsMssqlConnectionItem::createChildren()
}

// build sql statement
QString query = QgsMssqlConnection::buildQueryForSchemas( mName );
QString query = QgsMssqlConnection::buildQueryForTables( mName );

const bool disableInvalidGeometryHandling = QgsMssqlConnection::isInvalidGeometryHandlingDisabled( mName );

Expand Down Expand Up @@ -252,14 +253,13 @@ 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();
QStringList excludedSchema = QgsMssqlConnection::excludedSchemasList( mName );
for ( const QString &schema : allSchemas )
{
if ( mSchemasFilteringEnabled && !schemaSettings.value( schema ).toBool() )
if ( mSchemasFilteringEnabled && excludedSchema.contains( schema ) )
continue; // user does not want it to be shown

if ( addedSchemas.contains( schema ) )
Expand All @@ -274,8 +274,6 @@ QVector<QgsDataItem *> QgsMssqlConnectionItem::createChildren()
children.append( schemaItem );
}



// spawn threads (new layers will be added later on)
if ( mColumnTypeThread )
{
Expand Down

0 comments on commit f81737c

Please sign in to comment.