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

Tue Feb 18 02:00:56 PST 2014

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: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
>>>
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/gdal-dev