[OGR provider] Enable foreign key enforcement for newer transactions

Fixes #34728 Note however that integrity of existing content w.r.t foreign key is *not* checked at opening (see #9939), so this is just about not adding new violations.
qgis · May 21, 2021 · bce96e5 · bce96e5
1 parent bc3ba06
commit bce96e5
Show file tree

Hide file tree

Showing 2 changed files with 30 additions and 2 deletions.
diff --git a/src/core/providers/ogr/qgsogrprovider.cpp b/src/core/providers/ogr/qgsogrprovider.cpp
@@ -4752,9 +4752,12 @@ GDALDatasetH QgsOgrProviderUtils::GDALOpenWrapper( const char *pszPath, bool bUp
   if ( phDriver )
     *phDriver = hDrv;
 
-#if GDAL_VERSION_NUM < GDAL_COMPUTE_VERSION(3, 3, 1)
   if ( bUpdate && bIsGpkg && strcmp( GDALGetDriverShortName( hDrv ), "GPKG" ) == 0 )
   {
+    // Explicitly enable foreign key enforcement (for new transactions)
+    GDALDatasetExecuteSQL( hDS, "PRAGMA foreign_keys = ON", nullptr, nullptr );
+
+#if GDAL_VERSION_NUM < GDAL_COMPUTE_VERSION(3, 3, 1)
     // Fix wrong gpkg_metadata_reference_column_name_update trigger that was
     // generated by GDAL < 2.4.0
     OGRLayerH hSqlLyr = GDALDatasetExecuteSQL(
@@ -4785,8 +4788,8 @@ GDALDatasetH QgsOgrProviderUtils::GDALOpenWrapper( const char *pszPath, bool bUp
           nullptr, nullptr );
       }
     }
-  }
 #endif
+  }
 
   return hDS;
 }

diff --git a/tests/src/python/test_provider_ogr_gpkg.py b/tests/src/python/test_provider_ogr_gpkg.py
@@ -1880,6 +1880,31 @@ def testForeignKeyViolation(self):
         fids = set([f['fid'] for f in vl.getFeatures()])
         self.assertEqual(len(fids), 1)
 
+    def testForeignKeyViolationAfterOpening(self):
+        """Test that foreign keys are enforced"""
+
+        tmpfile = os.path.join(self.basetestpath, 'testForeignKeyViolationAfterOpening.gpkg')
+        ds = ogr.GetDriverByName('GPKG').CreateDataSource(tmpfile)
+        lyr = ds.CreateLayer('test', geom_type=ogr.wkbPoint)
+        f = ogr.Feature(lyr.GetLayerDefn())
+        f.SetGeometry(ogr.CreateGeometryFromWkt('POINT(0 1)'))
+        lyr.CreateFeature(f)
+        ds.ExecuteSQL(
+            "CREATE TABLE bar(fid INTEGER PRIMARY KEY, fkey INTEGER, CONSTRAINT fkey_constraint FOREIGN KEY (fkey) REFERENCES test(fid))")
+        ds = None
+        vl = QgsVectorLayer('{}'.format(tmpfile) + "|layername=bar", 'test', 'ogr')
+        self.assertTrue(vl.isValid())
+
+        # OK
+        f = QgsFeature()
+        f.setAttributes([None, 1])
+        self.assertTrue(vl.dataProvider().addFeature(f))
+
+        # violates foreign key
+        f = QgsFeature()
+        f.setAttributes([None, 10])
+        self.assertFalse(vl.dataProvider().addFeature(f))
+
     def testExportMultiFromShp(self):
         """Test if a Point is imported as single geom and MultiPoint as multi"""