Skip to content

Commit 59be24f

Browse files
authoredJun 10, 2021
[mssql] use nullable info from db to set field ConstraintNotNull
1 parent be23718 commit 59be24f

File tree

3 files changed

+183
-133
lines changed

3 files changed

+183
-133
lines changed
 

‎src/providers/mssql/qgsmssqlprovider.cpp

Lines changed: 133 additions & 133 deletions
Original file line numberDiff line numberDiff line change
@@ -364,175 +364,175 @@ void QgsMssqlProvider::loadFields()
364364
return;
365365
}
366366

367-
if ( query.isActive() )
367+
while ( query.next() )
368368
{
369-
while ( query.next() )
370-
{
371-
mComputedColumns.append( query.value( 0 ).toString() );
372-
}
369+
mComputedColumns.append( query.value( 0 ).toString() );
373370
}
374371

375372
if ( !query.exec( QStringLiteral( "exec sp_columns @table_name = N%1, @table_owner = %2" ).arg( quotedValue( mTableName ), quotedValue( mSchemaName ) ) ) )
376373
{
377374
pushError( query.lastError().text() );
378375
return;
379376
}
380-
if ( query.isActive() )
377+
378+
int i = 0;
379+
QStringList pkCandidates;
380+
while ( query.next() )
381381
{
382-
int i = 0;
383-
QStringList pkCandidates;
384-
while ( query.next() )
385-
{
386-
const QString colName = query.value( 3 ).toString();
387-
const QString sqlTypeName = query.value( 5 ).toString();
382+
const QString colName = query.value( QStringLiteral( "COLUMN_NAME" ) ).toString();
383+
const QString sqlTypeName = query.value( QStringLiteral( "TYPE_NAME" ) ).toString();
388384

389-
// if we don't have an explicitly set geometry column name, and this is a geometry column, then use it
390-
// but if we DO have an explicitly set geometry column name, then load the other information if this is that column
391-
if ( ( mGeometryColName.isEmpty() && ( sqlTypeName == QLatin1String( "geometry" ) || sqlTypeName == QLatin1String( "geography" ) ) )
392-
|| colName == mGeometryColName )
385+
// if we don't have an explicitly set geometry column name, and this is a geometry column, then use it
386+
// but if we DO have an explicitly set geometry column name, then load the other information if this is that column
387+
if ( ( mGeometryColName.isEmpty() && ( sqlTypeName == QLatin1String( "geometry" ) || sqlTypeName == QLatin1String( "geography" ) ) )
388+
|| colName == mGeometryColName )
389+
{
390+
mGeometryColName = colName;
391+
mGeometryColType = sqlTypeName;
392+
mParser.mIsGeography = sqlTypeName == QLatin1String( "geography" );
393+
}
394+
else
395+
{
396+
QVariant::Type sqlType = DecodeSqlType( sqlTypeName );
397+
if ( sqlTypeName == QLatin1String( "int identity" ) || sqlTypeName == QLatin1String( "bigint identity" ) )
393398
{
394-
mGeometryColName = colName;
395-
mGeometryColType = sqlTypeName;
396-
mParser.mIsGeography = sqlTypeName == QLatin1String( "geography" );
399+
mPrimaryKeyType = PktInt;
400+
mPrimaryKeyAttrs << mAttributeFields.size();
401+
isIdentity = true;
397402
}
398-
else
403+
else if ( sqlTypeName == QLatin1String( "int" ) || sqlTypeName == QLatin1String( "bigint" ) )
399404
{
400-
QVariant::Type sqlType = DecodeSqlType( sqlTypeName );
401-
if ( sqlTypeName == QLatin1String( "int identity" ) || sqlTypeName == QLatin1String( "bigint identity" ) )
402-
{
403-
mPrimaryKeyType = PktInt;
404-
mPrimaryKeyAttrs << mAttributeFields.size();
405-
isIdentity = true;
406-
}
407-
else if ( sqlTypeName == QLatin1String( "int" ) || sqlTypeName == QLatin1String( "bigint" ) )
408-
{
409-
pkCandidates << query.value( 3 ).toString();
410-
}
405+
pkCandidates << query.value( 3 ).toString();
406+
}
411407

412-
if ( sqlType == QVariant::String )
413-
{
414-
// Field length in chars is column 7 ("Length") of the sp_columns output,
415-
// except for uniqueidentifiers which must use column 6 ("Precision").
416-
int length = query.value( sqlTypeName.startsWith( QLatin1String( "uniqueidentifier" ), Qt::CaseInsensitive ) ? 6 : 7 ).toInt();
417-
if ( sqlTypeName.startsWith( QLatin1Char( 'n' ) ) )
418-
{
419-
length = length / 2;
420-
}
421-
mAttributeFields.append(
422-
QgsField(
423-
colName, sqlType,
424-
sqlTypeName,
425-
length ) );
426-
}
427-
else if ( sqlType == QVariant::Double )
428-
{
429-
mAttributeFields.append(
430-
QgsField(
431-
colName, sqlType,
432-
sqlTypeName,
433-
query.value( 6 ).toInt(),
434-
sqlTypeName == QLatin1String( "decimal" ) ? query.value( 8 ).toInt() : -1 ) );
435-
}
436-
else if ( sqlType == QVariant::Date || sqlType == QVariant::DateTime || sqlType == QVariant::Time )
437-
{
438-
mAttributeFields.append(
439-
QgsField(
440-
colName, sqlType,
441-
sqlTypeName,
442-
-1,
443-
-1 ) );
444-
}
445-
else
408+
QgsField field;
409+
if ( sqlType == QVariant::String )
410+
{
411+
// Field length in chars is column 7 ("Length") of the sp_columns output,
412+
// except for uniqueidentifiers which must use column 6 ("Precision").
413+
int length = query.value( sqlTypeName.startsWith( QLatin1String( "uniqueidentifier" ), Qt::CaseInsensitive ) ? 6 : 7 ).toInt();
414+
if ( sqlTypeName.startsWith( QLatin1Char( 'n' ) ) )
446415
{
447-
mAttributeFields.append(
448-
QgsField(
449-
colName, sqlType,
450-
sqlTypeName ) );
416+
length = length / 2;
451417
}
418+
field = QgsField( colName,
419+
sqlType,
420+
sqlTypeName,
421+
length );
422+
}
423+
else if ( sqlType == QVariant::Double )
424+
{
425+
field = QgsField( colName,
426+
sqlType,
427+
sqlTypeName,
428+
query.value( 6 ).toInt(),
429+
sqlTypeName == QLatin1String( "decimal" ) ? query.value( 8 ).toInt() : -1 );
430+
}
431+
else if ( sqlType == QVariant::Date || sqlType == QVariant::DateTime || sqlType == QVariant::Time )
432+
{
433+
field = QgsField( colName,
434+
sqlType,
435+
sqlTypeName,
436+
-1,
437+
-1 );
438+
}
439+
else
440+
{
441+
field = QgsField( colName,
442+
sqlType,
443+
sqlTypeName );
444+
}
452445

453-
//COLUMN_DEF
454-
if ( !query.value( 12 ).isNull() )
455-
{
456-
mDefaultValues.insert( i, query.value( 12 ).toString() );
457-
}
446+
// Field nullable
447+
const bool nullable = query.value( QStringLiteral( "NULLABLE" ) ).toBool();
448+
QgsFieldConstraints constraints;
449+
if ( !nullable )
450+
constraints.setConstraint( QgsFieldConstraints::ConstraintNotNull, QgsFieldConstraints::ConstraintOriginProvider );
451+
field.setConstraints( constraints );
458452

459-
++i;
453+
mAttributeFields.append( field );
454+
455+
//COLUMN_DEF
456+
if ( !query.value( 12 ).isNull() )
457+
{
458+
mDefaultValues.insert( i, query.value( 12 ).toString() );
460459
}
460+
461+
++i;
461462
}
462-
// get primary key
463-
if ( mPrimaryKeyAttrs.isEmpty() )
463+
}
464+
// get primary key
465+
if ( mPrimaryKeyAttrs.isEmpty() )
466+
{
467+
query.clear();
468+
query.setForwardOnly( true );
469+
if ( !query.exec( QStringLiteral( "exec sp_pkeys @table_name = N%1, @table_owner = %2 " ).arg( quotedValue( mTableName ), quotedValue( mSchemaName ) ) ) )
464470
{
465-
query.clear();
466-
query.setForwardOnly( true );
467-
if ( !query.exec( QStringLiteral( "exec sp_pkeys @table_name = N%1, @table_owner = %2 " ).arg( quotedValue( mTableName ), quotedValue( mSchemaName ) ) ) )
468-
{
469-
QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( query.lastQuery(), query.lastError().text() ) );
470-
}
471+
QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( query.lastQuery(), query.lastError().text() ) );
472+
}
471473

472-
if ( query.isActive() )
473-
{
474-
mPrimaryKeyType = PktInt;
474+
if ( query.isActive() )
475+
{
476+
mPrimaryKeyType = PktInt;
475477

476-
while ( query.next() )
477-
{
478-
QString fidColName = query.value( 3 ).toString();
479-
int idx = mAttributeFields.indexFromName( fidColName );
480-
const QgsField &fld = mAttributeFields.at( idx );
478+
while ( query.next() )
479+
{
480+
QString fidColName = query.value( 3 ).toString();
481+
int idx = mAttributeFields.indexFromName( fidColName );
482+
const QgsField &fld = mAttributeFields.at( idx );
481483

482-
if ( !mPrimaryKeyAttrs.isEmpty() ||
483-
( fld.type() != QVariant::Int &&
484-
fld.type() != QVariant::LongLong &&
485-
( fld.type() != QVariant::Double || fld.precision() != 0 ) ) )
486-
mPrimaryKeyType = PktFidMap;
484+
if ( !mPrimaryKeyAttrs.isEmpty() ||
485+
( fld.type() != QVariant::Int &&
486+
fld.type() != QVariant::LongLong &&
487+
( fld.type() != QVariant::Double || fld.precision() != 0 ) ) )
488+
mPrimaryKeyType = PktFidMap;
487489

488-
mPrimaryKeyAttrs << idx;
489-
}
490+
mPrimaryKeyAttrs << idx;
491+
}
490492

491-
if ( mPrimaryKeyAttrs.isEmpty() )
492-
{
493-
mPrimaryKeyType = PktUnknown;
494-
}
493+
if ( mPrimaryKeyAttrs.isEmpty() )
494+
{
495+
mPrimaryKeyType = PktUnknown;
495496
}
496497
}
498+
}
497499

498-
if ( mPrimaryKeyAttrs.isEmpty() )
500+
if ( mPrimaryKeyAttrs.isEmpty() )
501+
{
502+
const auto constPkCandidates = pkCandidates;
503+
for ( const QString &pk : constPkCandidates )
499504
{
500-
const auto constPkCandidates = pkCandidates;
501-
for ( const QString &pk : constPkCandidates )
505+
query.clear();
506+
query.setForwardOnly( true );
507+
if ( !query.exec( QStringLiteral( "select count(distinct [%1]), count([%1]) from [%2].[%3]" )
508+
.arg( pk, mSchemaName, mTableName ) ) )
502509
{
503-
query.clear();
504-
query.setForwardOnly( true );
505-
if ( !query.exec( QStringLiteral( "select count(distinct [%1]), count([%1]) from [%2].[%3]" )
506-
.arg( pk, mSchemaName, mTableName ) ) )
507-
{
508-
QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( query.lastQuery(), query.lastError().text() ) );
509-
}
510+
QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( query.lastQuery(), query.lastError().text() ) );
511+
}
510512

511-
if ( query.isActive() && query.next() && query.value( 0 ).toInt() == query.value( 1 ).toInt() )
512-
{
513-
mPrimaryKeyType = PktInt;
514-
mPrimaryKeyAttrs << mAttributeFields.indexFromName( pk );
515-
return;
516-
}
513+
if ( query.isActive() && query.next() && query.value( 0 ).toInt() == query.value( 1 ).toInt() )
514+
{
515+
mPrimaryKeyType = PktInt;
516+
mPrimaryKeyAttrs << mAttributeFields.indexFromName( pk );
517+
return;
517518
}
518519
}
520+
}
519521

520-
if ( mPrimaryKeyAttrs.isEmpty() )
521-
{
522-
QString error = QStringLiteral( "No primary key could be found on table %1" ).arg( mTableName );
523-
QgsDebugMsg( error );
524-
mValid = false;
525-
setLastError( error );
526-
}
522+
if ( mPrimaryKeyAttrs.isEmpty() )
523+
{
524+
QString error = QStringLiteral( "No primary key could be found on table %1" ).arg( mTableName );
525+
QgsDebugMsg( error );
526+
mValid = false;
527+
setLastError( error );
528+
}
527529

528-
if ( mPrimaryKeyAttrs.size() == 1 && !isIdentity )
529-
{
530-
// primary key has not null, unique constraints
531-
QgsFieldConstraints constraints = mAttributeFields.at( mPrimaryKeyAttrs[0] ).constraints();
532-
constraints.setConstraint( QgsFieldConstraints::ConstraintNotNull, QgsFieldConstraints::ConstraintOriginProvider );
533-
constraints.setConstraint( QgsFieldConstraints::ConstraintUnique, QgsFieldConstraints::ConstraintOriginProvider );
534-
mAttributeFields[ mPrimaryKeyAttrs[0] ].setConstraints( constraints );
535-
}
530+
if ( mPrimaryKeyAttrs.size() == 1 && !isIdentity )
531+
{
532+
// primary key has unique constraints
533+
QgsFieldConstraints constraints = mAttributeFields.at( mPrimaryKeyAttrs[0] ).constraints();
534+
constraints.setConstraint( QgsFieldConstraints::ConstraintUnique, QgsFieldConstraints::ConstraintOriginProvider );
535+
mAttributeFields[ mPrimaryKeyAttrs[0] ].setConstraints( constraints );
536536
}
537537
}
538538

‎tests/src/python/test_provider_mssql.py

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,7 @@
2020
QgsFeature,
2121
QgsField,
2222
QgsFields,
23+
QgsFieldConstraints,
2324
QgsDataSourceUri,
2425
QgsWkbTypes,
2526
QgsGeometry,
@@ -671,6 +672,42 @@ def testPktCompositeFloat(self):
671672

672673
self.assertFalse(got_feature)
673674

675+
def testNotNullConstraint(self):
676+
vl = QgsVectorLayer('%s table="qgis_test"."constraints" sql=' %
677+
(self.dbconn), "testdatetimes", "mssql")
678+
self.assertTrue(vl.isValid())
679+
self.assertEqual(len(vl.fields()), 4)
680+
681+
# test some bad field indexes
682+
self.assertEqual(vl.dataProvider().fieldConstraints(-1),
683+
QgsFieldConstraints.Constraints())
684+
self.assertEqual(vl.dataProvider().fieldConstraints(
685+
1001), QgsFieldConstraints.Constraints())
686+
687+
self.assertTrue(vl.dataProvider().fieldConstraints(0) &
688+
QgsFieldConstraints.ConstraintNotNull)
689+
self.assertFalse(vl.dataProvider().fieldConstraints(1)
690+
& QgsFieldConstraints.ConstraintNotNull)
691+
self.assertTrue(vl.dataProvider().fieldConstraints(2) &
692+
QgsFieldConstraints.ConstraintNotNull)
693+
self.assertFalse(vl.dataProvider().fieldConstraints(3)
694+
& QgsFieldConstraints.ConstraintNotNull)
695+
696+
# test that constraints have been saved to fields correctly
697+
fields = vl.fields()
698+
self.assertTrue(fields.at(0).constraints().constraints()
699+
& QgsFieldConstraints.ConstraintNotNull)
700+
self.assertEqual(fields.at(0).constraints().constraintOrigin(QgsFieldConstraints.ConstraintNotNull),
701+
QgsFieldConstraints.ConstraintOriginProvider)
702+
self.assertFalse(fields.at(1).constraints().constraints()
703+
& QgsFieldConstraints.ConstraintNotNull)
704+
self.assertTrue(fields.at(2).constraints().constraints()
705+
& QgsFieldConstraints.ConstraintNotNull)
706+
self.assertEqual(fields.at(2).constraints().constraintOrigin(QgsFieldConstraints.ConstraintNotNull),
707+
QgsFieldConstraints.ConstraintOriginProvider)
708+
self.assertFalse(fields.at(3).constraints().constraints()
709+
& QgsFieldConstraints.ConstraintNotNull)
710+
674711
def getSubsetString(self):
675712
return '[cnt] > 100 and [cnt] < 410'
676713

‎tests/testdata/provider/testdata_mssql.sql

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,9 @@ GO
3939
DROP TABLE IF EXISTS qgis_test.[tb_test_composite_float_pk];
4040
GO
4141

42+
DROP TABLE IF EXISTS qgis_test.[constraints];
43+
GO
44+
4245
DROP SCHEMA qgis_test;
4346
GO
4447

@@ -285,3 +288,13 @@ INSERT INTO [qgis_test].[tb_test_composite_float_pk] (pk1, pk2, pk3, value, geom
285288
(2, 2, 2.718281828, 'test 3', geometry::STGeomFromText('POINT(-47.902 -15.763)', 4326)),
286289
(2, 2, 1.0, 'test 4', geometry::STGeomFromText('POINT(-47.952 -15.781)', 4326));
287290
GO
291+
292+
-- Table for constraint tests
293+
CREATE TABLE [qgis_test].[constraints]
294+
(
295+
gid integer PRIMARY KEY,
296+
val int,
297+
name text NOT NULL,
298+
description text
299+
);
300+
GO

0 commit comments

Comments
 (0)
Please sign in to comment.