[QGIS-Developer] GeoPackages with constraint problems

[Matthias Kuhn]

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?

Matthias

On 08/03/2018 08:40 PM, Even Rouault wrote:
> Matthias,
>
> Looking a bit at this, I see from sqlite doc that foreign key constraints are 
> only enforced if the user runs
>
> PRAGMA foreign_keys = ON
>
> which OGR does no activate by default, hence the easyness to break them.
>
> This can be enabled by defining the configuration option/environment variable
> OGR_SQLITE_PRAGMA="foreign_keys=ON"
>
> I'm contemplating if the GPKG driver should do that by default (would probably 
> makes sense since at opening it runs the PRAGMA foreign_key_check)
>
> Even
>
>> Hi everyone,
>>
>> I recently gave a course where geopackages were used as datasets. Those
>> geopackages had foreign key constraints (among others) activated. While
>> editing those files, at least on one machine, someone managed to get it
>> into a "corrupted" state (layer disappeared). Trying to load this layer
>> later on will result in a bad layer. The only thing we have is a tiny
>> message in the message log informing about "pragma foreign_key_check on
>> 'file.gpkg' failed. You can disable this check by setting the
>> OGR_GPKG_FOREIGN_KEY_CHECK configuration option to NO".
>>
>> I think it's quite strange that QGIS/OGR manages to bring a GeoPackage
>> into a corrupted state and then denies to open it.
>>
>> * It would - I guess - be preferable to prevent a GeoPackage from going
>> into such a state
>>
>> * Since it appears to be quite easy to bring a dataset into such a state
>> (although I'm afraid I can't provide detailed steps) QGIS should by
>> default probably rather open (and warn) or warn and give a possibility
>> to still open.
>>
>> Did others experience this as well and have more ideas what to do?
>>
>> Thanks and best wishes
>> Matthias
>> _______________________________________________
>> QGIS-Developer mailing list
>> QGIS-Developer at lists.osgeo.org
>> List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>

-- 
Matthias Kuhn
matthias at opengis.ch <mailto:matthias at opengis.ch>
+41 (0)76 435 67 63 <tel:+41764356763>
OPENGIS.ch Logo <http://www.opengis.ch>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20180809/7fe15cb4/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image.png
Type: image/png
Size: 6671 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20180809/7fe15cb4/attachment-0001.png>