OGR/Spatialite UNIQUE detection

Fixes #36468 and un reported NOT NULL detection failure in spatialite.
qgis · May 29, 2020 · bb47d7f · bb47d7f
1 parent 582adf5
commit bb47d7f
Show file tree

Hide file tree

Showing 5 changed files with 172 additions and 6 deletions.
diff --git a/src/core/providers/ogr/qgsogrprovider.cpp b/src/core/providers/ogr/qgsogrprovider.cpp
@@ -46,7 +46,6 @@ email                : sherman at mrcc.com
 #include "qgsprovidermetadata.h"
 #include "qgsogrdbconnection.h"
 #include "qgsgeopackageproviderconnection.h"
-
 #include "qgis.h"
 
 
@@ -56,6 +55,11 @@ email                : sherman at mrcc.com
 #include <ogr_srs_api.h>
 #include <cpl_string.h>
 
+// Temporary solution until GDAL Unique suppport is available
+#include "qgssqliteutils.h"
+#include <sqlite3.h>
+// end temporary
+
 #include <limits>
 #include <memory>
 
@@ -1099,6 +1103,22 @@ void QgsOgrProvider::loadFields()
   mFirstFieldIsFid = !fidColumn.isEmpty() &&
                      fdef.GetFieldIndex( fidColumn ) < 0;
 
+  // This is a temporary solution until GDAL Unique support is available
+  QSet<QString> uniqueFieldNames;
+  if ( mGDALDriverName == QLatin1String( "GPKG" ) )
+  {
+    sqlite3_database_unique_ptr dsPtr;
+    if ( dsPtr.open( mFilePath ) == SQLITE_OK )
+    {
+      QString errMsg;
+      uniqueFieldNames = dsPtr.uniqueFields( mOgrLayer->name(), errMsg );
+      if ( ! errMsg.isEmpty() )
+      {
+        QgsMessageLog::logMessage( tr( "GPKG error searching for unique constraints on fields for table %1" ).arg( QString( mOgrLayer->name() ) ), tr( "OGR" ) );
+      }
+    }
+  }
+
   int createdFields = 0;
   if ( mFirstFieldIsFid )
   {
@@ -1233,6 +1253,13 @@ void QgsOgrProvider::loadFields()
       newField.setConstraints( constraints );
     }
 
+    if ( uniqueFieldNames.contains( OGR_Fld_GetNameRef( fldDef ) ) )
+    {
+      QgsFieldConstraints constraints = newField.constraints();
+      constraints.setConstraint( QgsFieldConstraints::ConstraintUnique, QgsFieldConstraints::ConstraintOriginProvider );
+      newField.setConstraints( constraints );
+    }
+
     // check if field has default value
     QString defaultValue = textEncoding()->toUnicode( OGR_Fld_GetDefault( fldDef ) );
     if ( !defaultValue.isEmpty() && !OGR_Fld_IsDefaultDriverSpecific( fldDef ) )

diff --git a/src/core/qgssqliteutils.cpp b/src/core/qgssqliteutils.cpp
@@ -20,6 +20,12 @@
 #include <sqlite3.h>
 #include <cstdarg>
 #include <QVariant>
+#include <QSet>
+
+// Temporary solution until GDAL Unique suppport is available
+#include <regex>
+#include <algorithm>
+// end temporary
 
 void QgsSqlite3Closer::operator()( sqlite3 *database )
 {
@@ -114,6 +120,86 @@ int sqlite3_database_unique_ptr::exec( const QString &sql, QString &errorMessage
   return ret;
 }
 
+QSet<QString> sqlite3_database_unique_ptr::uniqueFields( const QString &tableName, QString &errorMessage )
+{
+  QSet<QString> uniqueFieldsResults;
+  char *zErrMsg = 0;
+  std::vector<std::string> rows;
+  QString sql = sqlite3_mprintf( "select sql from sqlite_master where type='table' and name=%q", QgsSqliteUtils::quotedIdentifier( tableName ).toStdString().c_str() );
+  auto cb = [ ](
+              void *data /* Data provided in the 4th argument of sqlite3_exec() */,
+              int /* The number of columns in row */,
+              char **argv /* An array of strings representing fields in the row */,
+              char **/* An array of strings representing column names */ ) -> int
+  {
+    static_cast<std::vector<std::string>*>( data )->push_back( argv[0] );
+    return 0;
+  };
+
+  int rc = sqlite3_exec( get(), sql.toUtf8(), cb, ( void * )&rows, &zErrMsg );
+  if ( rc != SQLITE_OK )
+  {
+    errorMessage = zErrMsg;
+    sqlite3_free( zErrMsg );
+    return uniqueFieldsResults;
+  }
+
+  // Match identifiers with " or ` or no delimiter (and no spaces).
+  std::smatch uniqueFieldMatch;
+  static const std::regex sFieldIdentifierRe { R"raw(\s*(["`]([^"`]+)["`])|(([^\s]+)\s).*)raw" };
+  for ( auto tableDefinition : rows )
+  {
+    tableDefinition = tableDefinition.substr( tableDefinition.find( '(' ), tableDefinition.rfind( ')' ) );
+    std::stringstream tableDefinitionStream { tableDefinition };
+    while ( tableDefinitionStream.good() )
+    {
+      std::string fieldStr;
+      std::getline( tableDefinitionStream, fieldStr, ',' );
+      std::string upperCaseFieldStr { fieldStr };
+      std::transform( upperCaseFieldStr.begin(), upperCaseFieldStr.end(), upperCaseFieldStr.begin(), ::toupper );
+      if ( upperCaseFieldStr.find( "UNIQUE" ) != std::string::npos )
+      {
+        if ( std::regex_search( fieldStr, uniqueFieldMatch, sFieldIdentifierRe ) )
+        {
+          const std::string quoted { uniqueFieldMatch.str( 2 ) };
+          uniqueFieldsResults.insert( QString::fromStdString( quoted.length() ? quoted :  uniqueFieldMatch.str( 4 ) ) );
+        }
+      }
+    }
+  }
+  rows.clear();
+
+  // Search indexes:
+  sql = sqlite3_mprintf( "SELECT sql FROM sqlite_master WHERE type='index' AND"
+                         " tbl_name='%q' AND sql LIKE 'CREATE UNIQUE INDEX%%'" );
+  rc = sqlite3_exec( get(), sql.toUtf8(), cb, ( void * )&rows, &zErrMsg );
+  if ( rc != SQLITE_OK )
+  {
+    errorMessage = zErrMsg;
+    sqlite3_free( zErrMsg );
+    return uniqueFieldsResults;
+  }
+
+  if ( rows.size() > 0 )
+  {
+    static const std::regex sFieldIndexIdentifierRe { R"raw(\(\s*[`"]?([^",`\)]+)["`]?\s*\))raw" };
+    for ( auto indexDefinition : rows )
+    {
+      std::string upperCaseIndexDefinition { indexDefinition };
+      std::transform( upperCaseIndexDefinition.begin(), upperCaseIndexDefinition.end(), upperCaseIndexDefinition.begin(), ::toupper );
+      if ( upperCaseIndexDefinition.find( "UNIQUE" ) != std::string::npos )
+      {
+        indexDefinition = indexDefinition.substr( indexDefinition.find( '(' ), indexDefinition.rfind( ')' ) );
+        if ( std::regex_search( indexDefinition, uniqueFieldMatch, sFieldIndexIdentifierRe ) )
+        {
+          uniqueFieldsResults.insert( QString::fromStdString( uniqueFieldMatch.str( 1 ) ) );
+        }
+      }
+    }
+  }
+  return uniqueFieldsResults;
+}
+
 QString QgsSqliteUtils::quotedString( const QString &value )
 {
   if ( value.isNull() )

diff --git a/src/core/qgssqliteutils.h b/src/core/qgssqliteutils.h
@@ -153,6 +153,16 @@ class CORE_EXPORT sqlite3_database_unique_ptr : public std::unique_ptr< sqlite3,
      * \since QGIS 3.6
      */
     int exec( const QString &sql, QString &errorMessage SIP_OUT ) const;
+
+    /**
+     * Returns a list of field names for \a tableName having a UNIQUE constraint,
+     * fields that are part of a UNIQUE constraint that spans over multiple fields
+     * are not returned.
+     * \note the implementation is the same of GDAL but the test coverage is much
+     *       better in GDAL.
+     * \since QGIS 3.14
+     */
+    QSet<QString> uniqueFields( const QString &tableName, QString &errorMessage );
 };
 
 /**

diff --git a/src/providers/spatialite/qgsspatialiteprovider.cpp b/src/providers/spatialite/qgsspatialiteprovider.cpp
@@ -893,7 +893,7 @@ void QgsSpatiaLiteProvider::fetchConstraints()
   char **results = nullptr;
   char *errMsg = nullptr;
 
-  // this is not particularly robust but unfortunately sqlite offers no way to check directly
+  // this is not 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( QgsSqliteUtils::quotedIdentifier( mTableName ) );
   int columns = 0;
@@ -910,23 +910,51 @@ void QgsSpatiaLiteProvider::fetchConstraints()
     ;
   else
   {
+    // Use the same logic implemented in GDAL for GPKG
+    QSet<QString> uniqueFieldNames;
+    {
+      sqlite3_database_unique_ptr dsPtr;
+      if ( dsPtr.open( mSqlitePath ) == SQLITE_OK )
+      {
+        QString errMsg;
+        uniqueFieldNames = dsPtr.uniqueFields( mTableName, errMsg );
+        if ( ! errMsg.isEmpty() )
+        {
+          QgsMessageLog::logMessage( tr( "Error searching for unique constraints on fields for table %1" ).arg( mTableName ), tr( "spatialite" ) );
+        }
+      }
+    }
+
     QString sqlDef = QString::fromUtf8( results[ 1 ] );
     // extract definition
-    QRegularExpression re( QStringLiteral( "\\((.*)\\)" ) );
+    QRegularExpression re( QStringLiteral( R"raw(\((.*)\))raw" ) );
     QRegularExpressionMatch match = re.match( sqlDef );
     if ( match.hasMatch() )
     {
       const QString matched = match.captured( 1 );
       for ( auto &field : matched.split( ',' ) )
       {
         field = field.trimmed();
-        QString fieldName = field.left( field.indexOf( ' ' ) );
-        QString definition = field.mid( field.indexOf( ' ' ) + 1 );
+        QString fieldName;
+        QString definition;
+        const QChar delimiter { field.at( 0 ) };
+        if ( delimiter == '"' || delimiter == '`' )
+        {
+          const int start {  field.indexOf( delimiter ) + 1};
+          const int end { field.indexOf( delimiter, start ) };
+          fieldName = field.mid( start, end - start );
+          definition = field.mid( end + 1 );
+        }
+        else
+        {
+          fieldName = field.left( field.indexOf( ' ' ) );
+          definition = field.mid( field.indexOf( ' ' ) + 1 );
+        }
         int fieldIdx = mAttributeFields.lookupField( fieldName );
         if ( fieldIdx >= 0 )
         {
           QgsFieldConstraints constraints = mAttributeFields.at( fieldIdx ).constraints();
-          if ( definition.contains( QLatin1String( "unique" ), Qt::CaseInsensitive ) || definition.contains( QLatin1String( "primary key" ), Qt::CaseInsensitive ) )
+          if ( uniqueFieldNames.contains( fieldName ) || definition.contains( QLatin1String( "primary key" ), Qt::CaseInsensitive ) )
             constraints.setConstraint( QgsFieldConstraints::ConstraintUnique, QgsFieldConstraints::ConstraintOriginProvider );
           if ( definition.contains( QLatin1String( "not null" ), Qt::CaseInsensitive ) || definition.contains( QLatin1String( "primary key" ), Qt::CaseInsensitive ) )
             constraints.setConstraint( QgsFieldConstraints::ConstraintNotNull, QgsFieldConstraints::ConstraintOriginProvider );

diff --git a/tests/src/python/test_provider_spatialite.py b/tests/src/python/test_provider_spatialite.py
@@ -264,6 +264,12 @@ def setUpClass(cls):
         """
         cur.execute(sql)
 
+        # Unique and not null constraints
+        sql = "CREATE TABLE \"unique_not_null_constraints\"(pkuid integer primary key autoincrement, \"unique\" TEXT UNIQUE, \"not_null\" TEXT NOT NULL)"
+        cur.execute(sql)
+        sql = "SELECT AddGeometryColumn('unique_not_null_constraints', 'geometry', 4326, 'POINT', 'XY')"
+        cur.execute(sql)
+
         # Commit all test data
         cur.execute("COMMIT")
         con.close()
@@ -303,6 +309,15 @@ def getEditableLayerWithCheckConstraint(self):
             'spatialite')
         return vl
 
+    def getEditableLayerWithUniqueNotNullConstraints(self):
+        """Returns the layer for UNIQUE and NOT NULL constraints detection"""
+
+        vl = QgsVectorLayer(
+            'dbname=\'{}\' table="unique_not_null_constraints" (geometry) sql='.format(
+                self.dbname), 'unique_not_null_constraints',
+            'spatialite')
+        return vl
+
     def treat_time_as_string(self):
         return True