[gdal-dev] GeoPackage fails after touching it with Spatialite-gui

[Rahkonen Jukka (Tike)]

Hi,

Is this the same issue about how GPKG is handling ExecuteSQL()? If it is, then I will make a ticket because a database without indexes is missing a lot. Or is there some other way how I could create index into GPKG with GDAL? Alessandro Furieri told already that using spatialite-gui/tools for GeoPackages is currently not safe.

ogrinfo test.gpkg -dialect sqlite -sql "create index knro_idx on test (knro)"
INFO: Open of `test.gpkg'
      using driver `GPKG' successful.
ERROR 1: In ExecuteSQL(): sqlite3_prepare(create index knro_idx on test (knro)):
  no such table: main.test

-Jukka-

Even Rouault wrote:
> 
> Selon Jukka Rahkonen <jukka.rahkonen at mmmtike.fi>:
> 
> > Even Rouault <even.rouault <at> mines-paris.org> writes:
> >
> > >
> > > Jukka,
> > >
> > > I highly suspect that the ALTER TABLE must imply a rewriting of the
> > > file by spatialite/spatialite-gui, and when doing so, it doesn't
> > > preserve the "application id" (4 bytes in the header of sqlite
> > > file), that geopackage specification mentions to be set to a
> > > particular value. Consequently the geopackage driver later fails when
> checking the signature.
> >
> > I have not yet found a way to reproduce crash in a reliable way but
> > while testing I found something else. Does the following mean that the
> > GPKG file is not totally valid SQLite file or just that GDAL gets
> > puzzled because there are separate implementations for SQLite/Spatialite and
> GPKG?
> >
> >
> > Step 1: create plain SQLite and GPKG databases
> >
> > C:\data>ogr2ogr -f sqlite  test.sqlite temp.shp C:\data>ogr2ogr -f
> > gpkg  test.gpkg temp.shp
> >
> > Step 2: try to rename a table
> > C:\data>ogrinfo test.sqlite -dialect sqlite -sql "alter table temp
> > rename to temp2"
> > INFO: Open of `test.sqlite'
> >       using driver `SQLite' successful.
> >
> > C:\data>ogrinfo test.gpkg -dialect sqlite -sql "alter table temp
> > rename to temp2"
> > INFO: Open of `test.gpkg'
> >       using driver `GPKG' successful.
> > ERROR 1: In ExecuteSQL(): sqlite3_prepare(alter table temp rename to temp2):
> >   no such table: temp
> 
> Hum, well I can see that you are going to run into problems. The GPKG driver has
> no ExecuteSQL() implementation that should directly handle your SQL requests
> (what the SQLite driver would do). So it fallbacks to the generic ExecuteSQL()
> implementation, which uses the sqlite VirtualOGR mechanism, which does not
> support table renaming.
> Ideally, the GPKG driver should be extended to implement ExecuteSQL() in a
> similar way than the SQLite driver.
> 
> >
> > I noticed that OGR dialect does not support renaming tables, it
> > suggests to rename a column instead.
> >
> > -Jukka-
> >
> > _______________________________________________
> > gdal-dev mailing list
> > gdal-dev at lists.osgeo.org
> > http://lists.osgeo.org/mailman/listinfo/gdal-dev
> >
>