[gdal-dev] GPKG: Drop table but how to drop the rest?

[Rahkonen Jukka (MML)]

Hi,

I wonder if it could be possible to do something like "DROP CASCADING" in GeoPackage and delete the spatial index by the same. It will for sure be unusable once the table is dropped. I feel that SQLite does not support that because cascading drop seems to require that parent and child tables have foreign key relation, but RTree index which is created through the Virtual RTRee does not offer any way for defining the foreign key.

I know I can delete the index with another SQL request with ogrinfo

ogrinfo -sql "drop table bar" foo.gpkg
ogrinfo -sql "drop table rtree_bar_geom" foo.gpkg

Metadata tables are not cleaned automatically and two more commands would be needed:

ogrinfo -sql "delete from gpkg_contents where table_name='bar' and identifier='bar'" foo.gpkg
ogrinfo -sql "delete from gpkg_geometry_columns where table_name='bar' and column_name='geom'" foo.gpkg

However, it is impossible to run the latter one because GPKG database is at this moment in a bad shape


ogrinfo -sql "delete from gpkg_geometry_columns where table_name=bar' and column_name='geom'" foo.gpkg
ERROR 1: bad result for PRAGMA foreign_key_check, got 1 rows, expected 0
ERROR 1: pragma foreign_key_check on 'foo.gpkg' failed
ERROR 1: bad result for PRAGMA foreign_key_check, got 1 rows, expected 0
ERROR 1: pragma foreign_key_check on 'foo.gpkg' failed
FAILURE:
Unable to open datasource `foo.gpkg' with the following drivers.
...
I can delete that row from gpkg_geometry_columns with spatialite_gui.

So what is the right procedure and order for dropping a table from GeoPackage with ogrinfo?

-Jukka Rahkonen-
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20160929/0807ab64/attachment.html>