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

[Even Rouault]

Hi Jukka,

> 
> I wonder if it could be possible to do something like "DROP CASCADING" in
> GeoPackage and delete the spatial index by the same. 

I will work in a short term on this (as well as renaming columns)

The "usual" syntax "ogrinfo -sql 'DELLAYER:bar' foo.gpkg" will be available, 
and will be done through the proper OGRLayer::DeleteLayer() implementation.

> 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

You can turn off integrity checking with OGR_GPKG_INTEGRITY_CHECK = NO as a 
config option (documented only in code, since there's little reason to turn 
that off usually)

> 
> 
> 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?

Probably removing any reference to the table in gpkg_metadata*, gpkg_contents 
and gpkg_geometry_columns tables, then drop the rtree, then drop the main 
table.

Even

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