Re: [QGIS-Developer] GeoPackages with constraint problems

[Patrick Dunford]

Hi Matthias

I have seen this type of error message comes up when trying to open a 
geopackage from a 3.x project in 2.18


No data corruption in the original layer as far as I can tell.


On 04/08/18 01:59, Matthias Kuhn wrote:

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@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer


___
QGIS-Developer mailing list
QGIS-Developer@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [QGIS-Developer] GeoPackages with constraint problems

[Mark Johnson]

>> Executing PRAGMA foreign_keys = ON
This is also done for Spatialite in the SpatialiteProvider, so this is not
a general GeoPackage issue.

The need to keep this on is important, since the TRIGGERS enforces the
'sanity' of the data (such as same srid and geometry-type rules).

Also the Database-Designer can add there own rules, make a general, semi
automatic  'repair' routines basically impossible.

Mark
___
QGIS-Developer mailing list
QGIS-Developer@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [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
___
QGIS-Developer mailing list
QGIS-Developer@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [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@lists.osgeo.org
>> List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>

-- 
Matthias Kuhn
matth...@opengis.ch 
+41 (0)76 435 67 63 
OPENGIS.ch Logo 
___
QGIS-Developer mailing list
QGIS-Developer@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

Re: [QGIS-Developer] GeoPackages with constraint problems

[Even Rouault]

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@lists.osgeo.org
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer


-- 
Spatialys - Geospatial professional services
http://www.spatialys.com
___
QGIS-Developer mailing list
QGIS-Developer@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

[QGIS-Developer] GeoPackages with constraint problems

[Matthias Kuhn]

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@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer