Implement constraint detection for spatialite provider

qgis · Nov 2, 2016 · 3e40f80 · 3e40f80
1 parent 21f885a
commit 3e40f80
Show file tree

Hide file tree

Showing 4 changed files with 127 additions and 3 deletions.
diff --git a/src/gui/editorwidgets/core/qgseditorwidgetregistry.cpp b/src/gui/editorwidgets/core/qgseditorwidgetregistry.cpp
@@ -263,8 +263,7 @@ void QgsEditorWidgetRegistry::readMapLayer( QgsMapLayer* mapLayer, const QDomEle
 
       formConfig.setReadOnly( idx, ewv2CfgElem.attribute( QStringLiteral( "fieldEditable" ), QStringLiteral( "1" ) ) != QLatin1String( "1" ) );
       formConfig.setLabelOnTop( idx, ewv2CfgElem.attribute( QStringLiteral( "labelOnTop" ), QStringLiteral( "0" ) ) == QLatin1String( "1" ) );
-      formConfig.setNotNull( idx, ewv2CfgElem.attribute( QStringLiteral( "notNull" ), QStringLiteral( "0" ) ) == QLatin1String( "1" ) );
-      if ( ewv2CfgElem.attribute( QStringLiteral("notNull"), QStringLiteral("0") ) == QLatin1String( "1" ) )
+      if ( ewv2CfgElem.attribute( QStringLiteral( "notNull" ), QStringLiteral( "0" ) ) == QLatin1String( "1" ) )
       {
         // upgrade from older config
         vectorLayer->setFieldConstraints( idx, vectorLayer->fieldConstraints( idx ) | QgsField::ConstraintNotNull );

diff --git a/src/providers/spatialite/qgsspatialiteprovider.cpp b/src/providers/spatialite/qgsspatialiteprovider.cpp
@@ -36,6 +36,7 @@ email                : a.furieri@lqt.it
 #include <QMessageBox>
 #include <QFileInfo>
 #include <QDir>
+#include <QRegularExpression>
 
 
 const QString SPATIALITE_KEY = QStringLiteral( "spatialite" );
@@ -811,6 +812,65 @@ QString QgsSpatiaLiteProvider::spatialiteVersion()
   return mSpatialiteVersionInfo;
 }
 
+void QgsSpatiaLiteProvider::fetchConstraints()
+{
+  char **results;
+  char *errMsg = nullptr;
+
+  // this is not particularly robust but unfortunately sqlite offers no way to check directly
+  // for the presence of constraints on a field (only indexes, but not all constraints are indexes)
+  QString sql = QStringLiteral( "SELECT sql FROM sqlite_master WHERE type='table' AND name=%1" ).arg( quotedIdentifier( mTableName ) );
+  int columns = 0;
+  int rows = 0;
+
+  int ret = sqlite3_get_table( mSqliteHandle, sql.toUtf8().constData(), &results, &rows, &columns, &errMsg );
+  if ( ret != SQLITE_OK )
+    goto error;
+  if ( rows < 1 )
+    ;
+  else
+  {
+    QString sqlDef = QString::fromUtf8( results[ 1 ] );
+    // extract definition
+    QRegularExpression re( QStringLiteral( "\\((.*)\\)" ) );
+    QRegularExpressionMatch match = re.match( sqlDef );
+    if ( match.hasMatch() )
+    {
+      QString matched = match.captured( 1 );
+      Q_FOREACH ( QString field, matched.split( ',' ) )
+      {
+        field = field.trimmed();
+        QString fieldName = field.left( field.indexOf( ' ' ) );
+        QString definition = field.mid( field.indexOf( ' ' ) + 1 );
+        QgsField::Constraints constraints = 0;
+        if ( definition.contains( "unique", Qt::CaseInsensitive ) || definition.contains( "primary key", Qt::CaseInsensitive ) )
+          constraints |= QgsField::ConstraintUnique;
+        if ( definition.contains( "not null", Qt::CaseInsensitive ) || definition.contains( "primary key", Qt::CaseInsensitive ) )
+          constraints |= QgsField::ConstraintNotNull;
+
+        int fieldIdx = mAttributeFields.lookupField( fieldName );
+        if ( fieldIdx >= 0 )
+        {
+          mAttributeFields[ fieldIdx ].setConstraints( constraints );
+        }
+      }
+    }
+
+  }
+  sqlite3_free_table( results );
+
+  return;
+
+error:
+  QgsMessageLog::logMessage( tr( "SQLite error: %2\nSQL: %1" ).arg( sql, errMsg ? errMsg : tr( "unknown cause" ) ), tr( "SpatiaLite" ) );
+  // unexpected error
+  if ( errMsg )
+  {
+    sqlite3_free( errMsg );
+  }
+
+}
+
 void QgsSpatiaLiteProvider::loadFields()
 {
   int ret;
@@ -866,6 +926,8 @@ void QgsSpatiaLiteProvider::loadFields()
     }
     sqlite3_free_table( results );
 
+    // check for constraints
+    fetchConstraints();
 
     // for views try to get the primary key from the meta table
     if ( mViewBased && mPrimaryKey.isEmpty() )
@@ -3604,6 +3666,14 @@ void QgsSpatiaLiteProvider::uniqueValues( int index, QList < QVariant > &uniqueV
   return;
 }
 
+QgsField::Constraints QgsSpatiaLiteProvider::fieldConstraints( int fieldIndex ) const
+{
+  if ( fieldIndex < 0 || fieldIndex >= mAttributeFields.count() )
+    return 0;
+
+  return mAttributeFields.at( fieldIndex ).constraints();
+}
+
 QString QgsSpatiaLiteProvider::geomParam() const
 {
   QString geometry;

diff --git a/src/providers/spatialite/qgsspatialiteprovider.h b/src/providers/spatialite/qgsspatialiteprovider.h
@@ -130,6 +130,7 @@ class QgsSpatiaLiteProvider: public QgsVectorDataProvider
     QVariant minimumValue( int index ) const override;
     QVariant maximumValue( int index ) const override;
     virtual void uniqueValues( int index, QList < QVariant > &uniqueValues, int limit = -1 ) const override;
+    QgsField::Constraints fieldConstraints( int fieldIndex ) const override;
 
     bool isValid() const override;
     virtual bool isSaveAndLoadStyleToDBSupported() const override { return true; }
@@ -446,6 +447,8 @@ class QgsSpatiaLiteProvider: public QgsVectorDataProvider
                                   int type, int nDims, int little_endian,
                                   int endian_arch );
 
+    void fetchConstraints();
+
     enum GEOS_3D
     {
       GEOS_3D_POINT              = -2147483647,

diff --git a/tests/src/python/test_provider_spatialite.py b/tests/src/python/test_provider_spatialite.py
@@ -19,7 +19,7 @@
 import shutil
 import tempfile
 
-from qgis.core import QgsVectorLayer, QgsPoint, QgsFeature, QgsGeometry, QgsProject, QgsMapLayerRegistry
+from qgis.core import QgsVectorLayer, QgsPoint, QgsFeature, QgsGeometry, QgsProject, QgsMapLayerRegistry, QgsField
 
 from qgis.testing import start_app, unittest
 from utilities import unitTestDataPath
@@ -135,6 +135,10 @@ def setUpClass(cls):
         sql = "SELECT AddGeometryColumn('test_relation_b', 'Geometry', 4326, 'POLYGON', 'XY')"
         cur.execute(sql)
 
+        # tables with constraints
+        sql = "CREATE TABLE test_constraints(id INTEGER PRIMARY KEY, num INTEGER NOT NULL, desc TEXT UNIQUE, desc2 TEXT, num2 INTEGER NOT NULL UNIQUE)"
+        cur.execute(sql)
+
         cur.execute("COMMIT")
         con.close()
 
@@ -382,6 +386,54 @@ def test_discover_relation(self):
             QgsMapLayerRegistry.instance().removeMapLayer(track.id())
             QgsMapLayerRegistry.instance().removeMapLayer(artist.id())
 
+    def testNotNullConstraint(self):
+        vl = QgsVectorLayer("dbname=%s table=test_constraints key='id'" % self.dbname, "test_constraints",
+                                "spatialite")
+        self.assertTrue(vl.isValid())
+        self.assertEqual(len(vl.fields()), 5)
+
+        # test some bad field indexes
+        self.assertEqual(vl.dataProvider().fieldConstraints(-1), QgsField.Constraints())
+        self.assertEqual(vl.dataProvider().fieldConstraints(1001), QgsField.Constraints())
+
+        self.assertTrue(vl.dataProvider().fieldConstraints(0) & QgsField.ConstraintNotNull)
+        self.assertTrue(vl.dataProvider().fieldConstraints(1) & QgsField.ConstraintNotNull)
+        self.assertFalse(vl.dataProvider().fieldConstraints(2) & QgsField.ConstraintNotNull)
+        self.assertFalse(vl.dataProvider().fieldConstraints(3) & QgsField.ConstraintNotNull)
+        self.assertTrue(vl.dataProvider().fieldConstraints(4) & QgsField.ConstraintNotNull)
+
+        # test that constraints have been saved to fields correctly
+        fields = vl.fields()
+        self.assertTrue(fields.at(0).constraints() & QgsField.ConstraintNotNull)
+        self.assertTrue(fields.at(1).constraints() & QgsField.ConstraintNotNull)
+        self.assertFalse(fields.at(2).constraints() & QgsField.ConstraintNotNull)
+        self.assertFalse(fields.at(3).constraints() & QgsField.ConstraintNotNull)
+        self.assertTrue(fields.at(4).constraints() & QgsField.ConstraintNotNull)
+
+    def testUniqueConstraint(self):
+        vl = QgsVectorLayer("dbname=%s table=test_constraints key='id'" % self.dbname, "test_constraints",
+                                "spatialite")
+        self.assertTrue(vl.isValid())
+        self.assertEqual(len(vl.fields()), 5)
+
+        # test some bad field indexes
+        self.assertEqual(vl.dataProvider().fieldConstraints(-1), QgsField.Constraints())
+        self.assertEqual(vl.dataProvider().fieldConstraints(1001), QgsField.Constraints())
+
+        self.assertTrue(vl.dataProvider().fieldConstraints(0) & QgsField.ConstraintUnique)
+        self.assertFalse(vl.dataProvider().fieldConstraints(1) & QgsField.ConstraintUnique)
+        self.assertTrue(vl.dataProvider().fieldConstraints(2) & QgsField.ConstraintUnique)
+        self.assertFalse(vl.dataProvider().fieldConstraints(3) & QgsField.ConstraintUnique)
+        self.assertTrue(vl.dataProvider().fieldConstraints(4) & QgsField.ConstraintUnique)
+
+        # test that constraints have been saved to fields correctly
+        fields = vl.fields()
+        self.assertTrue(fields.at(0).constraints() & QgsField.ConstraintUnique)
+        self.assertFalse(fields.at(1).constraints() & QgsField.ConstraintUnique)
+        self.assertTrue(fields.at(2).constraints() & QgsField.ConstraintUnique)
+        self.assertFalse(fields.at(3).constraints() & QgsField.ConstraintUnique)
+        self.assertTrue(fields.at(4).constraints() & QgsField.ConstraintUnique)
+
     # This test would fail. It would require turning on WAL
     def XXXXXtestLocking(self):