Apolgies up-front for the self promotion...
Jukka,
If you need a basic geopackage enabled sqlite shell, you can compile
https://bitbucket.org/luciad/libgpkg to get this. One of the outputs is
the 'gpkg' binary which is the sqlite shell with the libgpkg extension
preloaded. This is nowhere near as featureful as ogr or spatialite, but
should already allow you to do basic CLI geopackage management.
Regards,
Pepijn
On 18-02-14 10:46, Rahkonen Jukka (Tike) wrote:
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:[email protected]]
Lähetetty: 18. helmikuuta 2014 11:07
Vastaanottaja: Rahkonen Jukka (Tike)
Kopio: '[email protected]'
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 <[email protected]>:
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
[email protected]
http://lists.osgeo.org/mailman/listinfo/gdal-dev
_______________________________________________
gdal-dev mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/gdal-dev
_______________________________________________
gdal-dev mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/gdal-dev
_______________________________________________
gdal-dev mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/gdal-dev
