Add method to fetch constraints from a vector data provider

Implemented for unique and not null constraints for postgres provider
qgis · Nov 2, 2016 · d1fd588 · d1fd588
1 parent 5e3bef7
commit d1fd588
Show file tree

Hide file tree

Showing 7 changed files with 119 additions and 1 deletion.
diff --git a/python/core/qgsvectordataprovider.sip b/python/core/qgsvectordataprovider.sip
@@ -55,6 +55,17 @@ class QgsVectorDataProvider : QgsDataProvider
     /** Bitmask of all provider's editing capabilities */
     static const int EditingCapabilities;
 
+    /**
+     * Constraints which may be present on a field.
+     * @note added in QGIS 3.0
+     */
+    enum Constraint
+    {
+      ConstraintNotNull, //!< Field may not be null
+      ConstraintUnique, //!< Field must have a unique value
+    };
+    typedef QFlags<QgsVectorDataProvider::Constraint> Constraints;
+
     /**
      * Constructor of the vector provider
      * @param uri  uniform resource locator (URI) for a dataset
@@ -230,6 +241,13 @@ class QgsVectorDataProvider : QgsDataProvider
      */
     virtual QVariant defaultValue( int fieldId ) const;
 
+    /**
+     * Returns any constraints which are present at the provider for a specified
+     * field index.
+     * @note added in QGIS 3.0
+     */
+    virtual QgsVectorDataProvider::Constraints fieldConstraints( int fieldIndex ) const;
+
     /**
      * Changes geometries of existing features
      * @param geometry_map   A QgsGeometryMap whose index contains the feature IDs
@@ -434,3 +452,4 @@ class QgsVectorDataProvider : QgsDataProvider
 };
 
 QFlags<QgsVectorDataProvider::Capability> operator|(QgsVectorDataProvider::Capability f1, QFlags<QgsVectorDataProvider::Capability> f2);
+QFlags<QgsVectorDataProvider::Constraint> operator|(QgsVectorDataProvider::Constraint f1, QFlags<QgsVectorDataProvider::Constraint> f2);
diff --git a/src/core/qgsvectordataprovider.cpp b/src/core/qgsvectordataprovider.cpp
@@ -98,6 +98,12 @@ QVariant QgsVectorDataProvider::defaultValue( int fieldId ) const
   return QVariant();
 }
 
+QgsVectorDataProvider::Constraints QgsVectorDataProvider::fieldConstraints( int fieldIndex ) const
+{
+  Q_UNUSED( fieldIndex );
+  return 0;
+}
+
 bool QgsVectorDataProvider::changeGeometryValues( const QgsGeometryMap &geometry_map )
 {
   Q_UNUSED( geometry_map );

diff --git a/src/core/qgsvectordataprovider.h b/src/core/qgsvectordataprovider.h
@@ -106,6 +106,17 @@ class CORE_EXPORT QgsVectorDataProvider : public QgsDataProvider
                                            ChangeAttributeValues | ChangeGeometries | AddAttributes | DeleteAttributes |
                                            RenameAttributes;
 
+    /**
+     * Constraints which may be present on a field.
+     * @note added in QGIS 3.0
+     */
+    enum Constraint
+    {
+      ConstraintNotNull = 1, //!< Field may not be null
+      ConstraintUnique = 1 << 1, //!< Field must have a unique value
+    };
+    Q_DECLARE_FLAGS( Constraints, Constraint )
+
     /**
      * Constructor of the vector provider
      * @param uri  uniform resource locator (URI) for a dataset
@@ -281,6 +292,13 @@ class CORE_EXPORT QgsVectorDataProvider : public QgsDataProvider
      */
     virtual QVariant defaultValue( int fieldId ) const;
 
+    /**
+     * Returns any constraints which are present at the provider for a specified
+     * field index.
+     * @note added in QGIS 3.0
+     */
+    virtual Constraints fieldConstraints( int fieldIndex ) const;
+
     /**
      * Changes geometries of existing features
      * @param geometry_map   A QgsGeometryMap whose index contains the feature IDs
@@ -520,6 +538,7 @@ class CORE_EXPORT QgsVectorDataProvider : public QgsDataProvider
 };
 
 Q_DECLARE_OPERATORS_FOR_FLAGS( QgsVectorDataProvider::Capabilities )
+Q_DECLARE_OPERATORS_FOR_FLAGS( QgsVectorDataProvider::Constraints )
 
 
 #endif
diff --git a/src/providers/postgres/qgspostgresprovider.cpp b/src/providers/postgres/qgspostgresprovider.cpp
@@ -718,6 +718,7 @@ bool QgsPostgresProvider::loadFields()
 
   QMap<int, QMap<int, QString> > fmtFieldTypeMap, descrMap, defValMap;
   QMap<int, QMap<int, int> > attTypeIdMap;
+  QMap<int, QMap<int, bool> > notNullMap, uniqueMap;
   if ( result.PQnfields() > 0 )
   {
     // Collect table oids
@@ -742,9 +743,13 @@ bool QgsPostgresProvider::loadFields()
       QString tableoidsFilter = '(' + tableoidsList.join( QStringLiteral( "," ) ) + ')';
 
       // Collect formatted field types
-      sql = "SELECT attrelid, attnum, pg_catalog.format_type(atttypid,atttypmod), pg_catalog.col_description(attrelid,attnum), pg_catalog.pg_get_expr(adbin,adrelid), atttypid"
+      sql = "SELECT attrelid, attnum, pg_catalog.format_type(atttypid,atttypmod), pg_catalog.col_description(attrelid,attnum), pg_catalog.pg_get_expr(adbin,adrelid), atttypid, attnotnull::int, indisunique::int"
             " FROM pg_attribute"
             " LEFT OUTER JOIN pg_attrdef ON attrelid=adrelid AND attnum=adnum"
+
+            // find unique constraints if present. Text cast required to handle int2vector comparison. Distinct required as multiple unique constraints may exist
+            " LEFT OUTER JOIN ( SELECT DISTINCT indrelid, indkey, indisunique FROM pg_index WHERE indisunique ) uniq ON attrelid=indrelid AND attnum::text=indkey::text "
+
             " WHERE attrelid IN " + tableoidsFilter;
       QgsPostgresResult fmtFieldTypeResult( connectionRO()->PQexec( sql ) );
       for ( int i = 0; i < fmtFieldTypeResult.PQntuples(); ++i )
@@ -755,10 +760,14 @@ bool QgsPostgresProvider::loadFields()
         QString descr = fmtFieldTypeResult.PQgetvalue( i, 3 );
         QString defVal = fmtFieldTypeResult.PQgetvalue( i, 4 );
         int attType = fmtFieldTypeResult.PQgetvalue( i, 5 ).toInt();
+        bool attNotNull = fmtFieldTypeResult.PQgetvalue( i, 6 ).toInt();
+        bool uniqueConstraint = fmtFieldTypeResult.PQgetvalue( i, 7 ).toInt();
         fmtFieldTypeMap[attrelid][attnum] = formatType;
         descrMap[attrelid][attnum] = descr;
         defValMap[attrelid][attnum] = defVal;
         attTypeIdMap[attrelid][attnum] = attType;
+        notNullMap[attrelid][attnum] = attNotNull;
+        uniqueMap[attrelid][attnum] = uniqueConstraint;
       }
     }
   }
@@ -988,6 +997,14 @@ bool QgsPostgresProvider::loadFields()
 
     mAttrPalIndexName.insert( i, fieldName );
     mDefaultValues.insert( mAttributeFields.size(), defValMap[tableoid][attnum] );
+
+    Constraints constraints = 0;
+    if ( notNullMap[tableoid][attnum] )
+      constraints |= ConstraintNotNull;
+    if ( uniqueMap[tableoid][attnum] )
+      constraints |= ConstraintUnique;
+    mFieldConstraints.insert( mAttributeFields.size(), constraints );
+
     mAttributeFields.append( QgsField( fieldName, fieldType, fieldTypeName, fieldSize, fieldPrec, fieldComment, fieldSubType ) );
   }
 
@@ -1719,6 +1736,11 @@ QVariant QgsPostgresProvider::defaultValue( int fieldId ) const
   return defVal;
 }
 
+QgsVectorDataProvider::Constraints QgsPostgresProvider::fieldConstraints( int fieldIndex ) const
+{
+  return mFieldConstraints.value( fieldIndex, 0 );
+}
+
 QString QgsPostgresProvider::paramValue( const QString& fieldValue, const QString &defaultValue ) const
 {
   if ( fieldValue.isNull() )

diff --git a/src/providers/postgres/qgspostgresprovider.h b/src/providers/postgres/qgspostgresprovider.h
@@ -161,6 +161,7 @@ class QgsPostgresProvider : public QgsVectorDataProvider
     QgsAttributeList attributeIndexes() const override;
     QgsAttributeList pkAttributeIndexes() const override { return mPrimaryKeyAttrs; }
     QVariant defaultValue( int fieldId ) const override;
+    Constraints fieldConstraints( int fieldIndex ) const override;
 
     /** Adds a list of features
       @return true in case of success and false in case of failure*/
@@ -493,6 +494,7 @@ class QgsPostgresProvider : public QgsVectorDataProvider
     void setTransaction( QgsTransaction* transaction ) override;
 
     QHash<int, QString> mDefaultValues;
+    QHash<int, QgsVectorDataProvider::Constraints > mFieldConstraints;
 };
 
 

diff --git a/tests/src/python/test_provider_postgres.py b/tests/src/python/test_provider_postgres.py
@@ -26,6 +26,7 @@
     QgsFeatureRequest,
     QgsFeature,
     QgsTransactionGroup,
+    QgsVectorDataProvider,
     NULL
 )
 from qgis.gui import QgsEditorWidgetRegistry
@@ -437,6 +438,34 @@ def testDoubleArray(self):
         self.assertTrue(isinstance(f.attributes()[value_idx], list))
         self.assertEqual(f.attributes()[value_idx], [1.1, 2, -5.12345])
 
+    def testNotNullConstraint(self):
+        vl = QgsVectorLayer('%s table="qgis_test"."constraints" sql=' % (self.dbconn), "constraints", "postgres")
+        self.assertTrue(vl.isValid())
+        self.assertEqual(len(vl.fields()), 4)
+
+        # test some bad field indexes
+        self.assertEqual(vl.dataProvider().fieldConstraints(-1), QgsVectorDataProvider.Constraints())
+        self.assertEqual(vl.dataProvider().fieldConstraints(1001), QgsVectorDataProvider.Constraints())
+
+        self.assertTrue(vl.dataProvider().fieldConstraints(0) & QgsVectorDataProvider.ConstraintNotNull)
+        self.assertFalse(vl.dataProvider().fieldConstraints(1) & QgsVectorDataProvider.ConstraintNotNull)
+        self.assertTrue(vl.dataProvider().fieldConstraints(2) & QgsVectorDataProvider.ConstraintNotNull)
+        self.assertFalse(vl.dataProvider().fieldConstraints(3) & QgsVectorDataProvider.ConstraintNotNull)
+
+    def testUniqueConstraint(self):
+        vl = QgsVectorLayer('%s table="qgis_test"."constraints" sql=' % (self.dbconn), "constraints", "postgres")
+        self.assertTrue(vl.isValid())
+        self.assertEqual(len(vl.fields()), 4)
+
+        # test some bad field indexes
+        self.assertEqual(vl.dataProvider().fieldConstraints(-1), QgsVectorDataProvider.Constraints())
+        self.assertEqual(vl.dataProvider().fieldConstraints(1001), QgsVectorDataProvider.Constraints())
+
+        self.assertTrue(vl.dataProvider().fieldConstraints(0) & QgsVectorDataProvider.ConstraintUnique)
+        self.assertTrue(vl.dataProvider().fieldConstraints(1) & QgsVectorDataProvider.ConstraintUnique)
+        self.assertTrue(vl.dataProvider().fieldConstraints(2) & QgsVectorDataProvider.ConstraintUnique)
+        self.assertFalse(vl.dataProvider().fieldConstraints(3) & QgsVectorDataProvider.ConstraintUnique)
+
     # See http://hub.qgis.org/issues/15188
     def testNumericPrecision(self):
         uri = 'point?field=f1:int'

diff --git a/tests/testdata/provider/testdata_pg.sql b/tests/testdata/provider/testdata_pg.sql
@@ -458,3 +458,24 @@ CREATE TABLE qgis_test.widget_styles(
 
 INSERT INTO qgis_editor_widget_styles VALUES
 ('qgis_test', 'widget_styles', 'fld1', 'FooEdit', '<config><option key="param1" value="value1"/><option key="param2" value="2"/></config>');
+
+
+-----------------------------
+-- Table for constraint tests
+--
+
+DROP TABLE IF EXISTS qgis_test.constraints;
+CREATE TABLE qgis_test.constraints
+(
+  gid serial NOT NULL PRIMARY KEY, -- implicit unique key
+  val int, -- unique constraint
+  name text NOT NULL, -- unique index
+  description text,
+  CONSTRAINT constraint_val UNIQUE (val),
+  CONSTRAINT constraint_val2 UNIQUE (val) -- create double unique constraint for test
+);
+
+CREATE UNIQUE INDEX constraints_uniq
+  ON qgis_test.constraints
+  USING btree
+  (name COLLATE pg_catalog."default"); -- unique index