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

[Even Rouault]

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
>