Skip to content

Commit

Permalink
[mssql] use unique constraint info from db to set field ConstraintUnique
Browse files Browse the repository at this point in the history
  • Loading branch information
@domi4484
domi4484 committed Jun 21, 2021
1 parent b474103 commit ad17471
Show file tree
Hide file tree
Showing 3 changed files with 71 additions and 14 deletions.
45 changes: 32 additions & 13 deletions src/providers/mssql/qgsmssqlprovider.cpp
View file
Expand Up @@ -369,6 +369,22 @@ void QgsMssqlProvider::loadFields()
mComputedColumns.append( query.value( 0 ).toString() );
}

// Field has unique constraint
QSet<QString> setColumnUnique;
{
if ( !query.exec( QStringLiteral( "SELECT * FROM information_schema.table_constraints TC"
" INNER JOIN information_schema.constraint_column_usage CC ON TC.Constraint_Name = CC.Constraint_Name"
" WHERE TC.CONSTRAINT_SCHEMA = '%1' AND TC.TABLE_NAME = '%2' AND TC.constraint_type = 'unique'" )
.arg( mSchemaName, mTableName ) ) )
{
pushError( query.lastError().text() );
return;
}

while ( query.next() )
setColumnUnique.insert( query.value( QStringLiteral( "COLUMN_NAME" ) ).toString() );
}

if ( !query.exec( QStringLiteral( "exec sp_columns @table_name = N%1, @table_owner = %2" ).arg( quotedValue( mTableName ), quotedValue( mSchemaName ) ) ) )
{
pushError( query.lastError().text() );
Expand All @@ -384,33 +400,33 @@ void QgsMssqlProvider::loadFields()

// if we don't have an explicitly set geometry column name, and this is a geometry column, then use it
// but if we DO have an explicitly set geometry column name, then load the other information if this is that column
if ( ( mGeometryColName.isEmpty() && ( sqlTypeName == QLatin1String( "geometry" ) || sqlTypeName == QLatin1String( "geography" ) ) )
if ( ( mGeometryColName.isEmpty() && ( sqlTypeName == QStringLiteral( "geometry" ) || sqlTypeName == QStringLiteral( "geography" ) ) )
|| colName == mGeometryColName )
{
mGeometryColName = colName;
mGeometryColType = sqlTypeName;
mParser.mIsGeography = sqlTypeName == QLatin1String( "geography" );
mParser.mIsGeography = sqlTypeName == QStringLiteral( "geography" );
}
else
{
QVariant::Type sqlType = DecodeSqlType( sqlTypeName );
if ( sqlTypeName == QLatin1String( "int identity" ) || sqlTypeName == QLatin1String( "bigint identity" ) )
if ( sqlTypeName == QStringLiteral( "int identity" ) || sqlTypeName == QStringLiteral( "bigint identity" ) )
{
mPrimaryKeyType = PktInt;
mPrimaryKeyAttrs << mAttributeFields.size();
isIdentity = true;
}
else if ( sqlTypeName == QLatin1String( "int" ) || sqlTypeName == QLatin1String( "bigint" ) )
else if ( sqlTypeName == QStringLiteral( "int" ) || sqlTypeName == QStringLiteral( "bigint" ) )
{
pkCandidates << query.value( 3 ).toString();
pkCandidates << colName;
}

QgsField field;
if ( sqlType == QVariant::String )
{
// Field length in chars is column 7 ("Length") of the sp_columns output,
// except for uniqueidentifiers which must use column 6 ("Precision").
int length = query.value( sqlTypeName.startsWith( QLatin1String( "uniqueidentifier" ), Qt::CaseInsensitive ) ? 6 : 7 ).toInt();
int length = query.value( sqlTypeName.startsWith( QStringLiteral( "uniqueidentifier" ), Qt::CaseInsensitive ) ? 6 : 7 ).toInt();
if ( sqlTypeName.startsWith( QLatin1Char( 'n' ) ) )
{
length = length / 2;
Expand All @@ -425,8 +441,8 @@ void QgsMssqlProvider::loadFields()
field = QgsField( colName,
sqlType,
sqlTypeName,
query.value( 6 ).toInt(),
sqlTypeName == QLatin1String( "decimal" ) ? query.value( 8 ).toInt() : -1 );
query.value( QStringLiteral( "PRECISION" ) ).toInt(),
sqlTypeName == QStringLiteral( "decimal" ) ? query.value( QStringLiteral( "SCALE" ) ).toInt() : -1 );
}
else if ( sqlType == QVariant::Date || sqlType == QVariant::DateTime || sqlType == QVariant::Time )
{
Expand All @@ -445,27 +461,31 @@ void QgsMssqlProvider::loadFields()

// Field nullable
const bool nullable = query.value( QStringLiteral( "NULLABLE" ) ).toBool();

// Set constraints
QgsFieldConstraints constraints;
if ( !nullable )
constraints.setConstraint( QgsFieldConstraints::ConstraintNotNull, QgsFieldConstraints::ConstraintOriginProvider );
if ( setColumnUnique.contains( colName ) )
constraints.setConstraint( QgsFieldConstraints::ConstraintUnique, QgsFieldConstraints::ConstraintOriginProvider );
field.setConstraints( constraints );

mAttributeFields.append( field );

//COLUMN_DEF
if ( !query.value( 12 ).isNull() )
// Default value
if ( !query.value( QStringLiteral( "COLUMN_DEF" ) ).isNull() )
{
mDefaultValues.insert( i, query.value( 12 ).toString() );
mDefaultValues.insert( i, query.value( QStringLiteral( "COLUMN_DEF" ) ).toString() );
}

++i;
}
}

// get primary key
if ( mPrimaryKeyAttrs.isEmpty() )
{
query.clear();
query.setForwardOnly( true );
if ( !query.exec( QStringLiteral( "exec sp_pkeys @table_name = N%1, @table_owner = %2 " ).arg( quotedValue( mTableName ), quotedValue( mSchemaName ) ) ) )
{
QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( query.lastQuery(), query.lastError().text() ) );
Expand Down Expand Up @@ -503,7 +523,6 @@ void QgsMssqlProvider::loadFields()
for ( const QString &pk : constPkCandidates )
{
query.clear();
query.setForwardOnly( true );
if ( !query.exec( QStringLiteral( "select count(distinct [%1]), count([%1]) from [%2].[%3]" )
.arg( pk, mSchemaName, mTableName ) ) )
{
Expand Down
36 changes: 36 additions & 0 deletions tests/src/python/test_provider_mssql.py
View file
Expand Up @@ -708,6 +708,42 @@ def testNotNullConstraint(self):
self.assertFalse(fields.at(3).constraints().constraints()
& QgsFieldConstraints.ConstraintNotNull)

def testUniqueConstraint(self):
vl = QgsVectorLayer('%s table="qgis_test"."constraints" sql=' %
(self.dbconn), "testdatetimes", "mssql")
self.assertTrue(vl.isValid())
self.assertEqual(len(vl.fields()), 4)

# test some bad field indexes
self.assertEqual(vl.dataProvider().fieldConstraints(-1),
QgsFieldConstraints.Constraints())
self.assertEqual(vl.dataProvider().fieldConstraints(
1001), QgsFieldConstraints.Constraints())

self.assertTrue(vl.dataProvider().fieldConstraints(0)
& QgsFieldConstraints.ConstraintUnique)
self.assertTrue(vl.dataProvider().fieldConstraints(1)
& QgsFieldConstraints.ConstraintUnique)
self.assertFalse(vl.dataProvider().fieldConstraints(2)
& QgsFieldConstraints.ConstraintUnique)
self.assertFalse(vl.dataProvider().fieldConstraints(3)
& QgsFieldConstraints.ConstraintUnique)

# test that constraints have been saved to fields correctly
fields = vl.fields()
self.assertTrue(fields.at(0).constraints().constraints()
& QgsFieldConstraints.ConstraintUnique)
self.assertEqual(fields.at(0).constraints().constraintOrigin(QgsFieldConstraints.ConstraintUnique),
QgsFieldConstraints.ConstraintOriginProvider)
self.assertTrue(fields.at(1).constraints().constraints()
& QgsFieldConstraints.ConstraintUnique)
self.assertEqual(fields.at(1).constraints().constraintOrigin(QgsFieldConstraints.ConstraintUnique),
QgsFieldConstraints.ConstraintOriginProvider)
self.assertFalse(fields.at(2).constraints().constraints()
& QgsFieldConstraints.ConstraintUnique)
self.assertFalse(fields.at(3).constraints().constraints()
& QgsFieldConstraints.ConstraintUnique)

def getSubsetString(self):
return '[cnt] > 100 and [cnt] < 410'

Expand Down
4 changes: 3 additions & 1 deletion tests/testdata/provider/testdata_mssql.sql
View file
Expand Up @@ -295,6 +295,8 @@ CREATE TABLE [qgis_test].[constraints]
gid integer PRIMARY KEY,
val int,
name text NOT NULL,
description text
description text,
CONSTRAINT constraint_val UNIQUE (val)
);
GO

2 comments on commit ad17471

@wonder-sk
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

hi @domi4484 when trying to run mssql provider tests, the testUniqueConstraint() test fails for me:

706: ======================================================================
706: FAIL: testUniqueConstraint (__main__.TestPyQgsMssqlProvider)
706: ----------------------------------------------------------------------
706: Traceback (most recent call last):
706:   File "/home/martin/qgis/git-master/tests/src/python/test_provider_mssql.py", line 723, in testUniqueConstraint
706:     self.assertTrue(vl.dataProvider().fieldConstraints(0)
706: AssertionError: <qgis._core.QgsFieldConstraints.Constraints object at 0x7fc9845c3940> is not true
  • vl.dataProvider().fieldConstraints(0) does not return that the first column (primary key) has unique constraint
  • the reason is that information_schema.table_constraints.constraint_type value for the primary key column is PRIMARY KEY and not UNIQUE which is what the SQL query looks for

I am wondering what is wrong - if the SQL query should be fixed to include constraint_type='PRIMARY KEY' or the test is wrong and the primary key should not have "unique" constraint? I assume the former, but checking just to be sure...

I am using MSSQL on linux from docker:

1> select @@version
2> go

Microsoft SQL Server 2019 (RTM-CU12) (KB5004524) - 15.0.4153.1 (X64) 
        Jul 19 2021 15:37:34 
        Copyright (C) 2019 Microsoft Corporation
        Developer Edition (64-bit) on Linux (Ubuntu 20.04.2 LTS) <X64>

@domi4484
Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

hi @wonder-sk , I was using a slightly different docker image server:2019-CU11-ubuntu-20.04.

I think the test is correct and the primary key should have the unique constraint.

Probably when I implemented this I did not pay attention to the fact that the query of line 375 does not return the primary key because the constraint for that are set later at line 549:

  if ( mPrimaryKeyAttrs.size() == 1 && !isIdentity )
  {
    // primary key has unique constraints
    QgsFieldConstraints constraints = mAttributeFields.at( mPrimaryKeyAttrs[0] ).constraints();
    constraints.setConstraint( QgsFieldConstraints::ConstraintUnique, QgsFieldConstraints::ConstraintOriginProvider );
    mAttributeFields[ mPrimaryKeyAttrs[0] ].setConstraints( constraints );
  }

Maybe is the detection of the primary key which is suddenly having some troubles?

Please sign in to comment.