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

[Rahkonen Jukka (Tike)]

Hi,

Creating indexes with sqlite3 feels safe. Renaming table is unsafe and leads to ogrinfo crash.

sqlite> alter table test rename to test2;

C:\ohjelmat\sqlite3>ogrinfo kuti.gpkg
ERROR 1: (null)
INFO: Open of `kuti.gpkg'
      using driver `GPKG' successful.

Next: Crash.

What is good is that everything is good again after doing 
sqlite> alter table test2 rename to test; 

Renaming a table is not something that is absolutely needed but it is not a totally odd idea either and it should not lead to program crash. Now it looks like the crash comes from metadata and real table names being unsynchronized and a proper way to support renaming the tables would mean creating also triggers into the database for updating the metadata fields correctly.
Because it is sure at the moment that renaming a table will lead to a not usable GeoPackage it might be good to mention it in the Limitations section of driver page http://www.gdal.org/ogr/drv_geopackage.html

-Jukka-


> -----Alkuperäinen viesti-----
> Lähettäjä: Even Rouault [mailto:even.rouault at mines-paris.org]
> Lähetetty: 18. helmikuuta 2014 11:07
> Vastaanottaja: Rahkonen Jukka (Tike)
> Kopio: 'gdal-dev at lists.osgeo.org'
> Aihe: Re: [gdal-dev] GeoPackage fails after touching it with Spatialite-gui
> 
> Hi Jukka,
> 
> yes, this is the same issue. -dialect sqlite is quite powerfull, but can only handle
> SELECT / UPDATE / DELETE, no other fancy stuff. The GPKG driver should just
> have its ExecuteSQL() implementation.
> Currently I guess you could open a .gpkg with the sqlite3 binary. I wouldn't
> expect it to alter the application_id field (or perhaps in the same situation as
> spatialite_gui does).
> 
> Even
> 
> > 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
> > > >
> > >
> >
> > _______________________________________________
> > gdal-dev mailing list
> > gdal-dev at lists.osgeo.org
> > http://lists.osgeo.org/mailman/listinfo/gdal-dev
> >
>