[QGIS-Developer] GeoPackages with constraint problems

[Even Rouault]

On jeudi 9 août 2018 07:07:10 CEST Matthias Kuhn wrote:
> Thanks Even,
> 
> Executing PRAGMA foreign_keys = ON sounds interesting, one thing we'll
> then need to check on the relation editing end is that we make proper
> use of deferred foreign key constraints.
> 
> What do you think about the way to go concerning handling broken files?
> The current situation is that there's very limited information for a
> user concerning the problems and it requires advanced sqlite skills to
> investigate and fix those problems. I am a bit worried that this might
> impact overall acceptance of gpkg.
> 
> * It looks like the error message is around (since it's in the message
> log), so that's mostly a visibility / UI problem
> * There is no clear "path of action" for a user. It would be nice to
> offer to open the dataset anyway when that happens. Is it possible to
> prevent OGR from running the PRAGMA foreign_key_check?

Yep, this is indicated in the GDAL error message ;-) : set the 
OGR_GPKG_FOREIGN_KEY_CHECK configuration option to NO

CPLSetConfigOption("OGR_GPKG_FOREIGN_KEY_CHECK", "NO"); (or as environment 
variable)

This check is done because of requirement 7 of http://www.geopackage.org/spec/
"""
The SQLite PRAGMA foreign_key_check SQL with no parameter value SHALL return 
an empty result set indicating no invalid foreign key values for a GeoPackage 
file.
"""

One way would be to systematically set OGR_GPKG_FOREIGN_KEY_CHECK=NO and after 
the file is open run GDALDatasetExecuteSQL("pragma foreign_key_check"). If it 
returns an empty layer, then fine, otherwise emit a warning to the user.

Even

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com