Issue dropping a spatialite table

[Robert Oehler]

I'm facing issues in an Android application dropping a spatialite table.

I'm using spatialite version 3.0.1 and sqlite version 3.7.9

I create a table containing a geometry this way

CREATE TABLE 'result_table_1156' ('RESULT_ID' INTEGER PRIMARY KEY AUTOINCREMENT, 'id' INTEGER, 'id_geo_arco' INTEGER, 'rischio1' DOUBLE, 'rischio2' DOUBLE, 'CRS' INTEGER);

SELECT AddGeometryColumn('result_table_1156', 'GEOMETRY', 32632 , 'MULTILINESTRING', 'XY');

SELECT CreateSpatialIndex('result_table_1156', 'GEOMETRY');

This works out fine. 

Now I want to drop all data in the database according to this table.

I execute the following statements

 SELECT DisableSpatialIndex('result_table_1156', 'GEOMETRY');

 SELECT DiscardGeometryColumn('result_table_1156', 'GEOMETRY');

 DROP TABLE IF EXISTS idx_result_table_1156_GEOMETRY;

 DROP TABLE IF EXISTS result_table_1156;

Doing this in the spatialite-gui removes all data with success.

But on Android the 3rd statement fails giving the exception "unable to open database file"

What am I doing wrong? What is the correct way to drop a table and its related data?

Or, more specific, the idx_* tables ?

I found something in the spatialite 2.3.1 tutorial but I'm not sure if its valid for 3.0.1 too.

Thank you

[a.fu...@lqt.it]

On Wed, 8 Jul 2015 02:30:04 -0700 (PDT), Robert Oehler wrote:
> I'm facing issues in an Android application dropping a spatialite
> table.
>
> I'm using spatialite version 3.0.1 and sqlite version 3.7.9
>

Hi Robert,

you are using obsolete versions for both libraries: the more
recent SQLite is 3.8.10.2, and a brand new SpatiaLite 4.3.0
was released just last week.
updating to fresher versions seems to be appropriate.

https://www.gaia-gis.it/fossil/libspatialite/index
https://github.com/geopaparazzi/libjsqlite-spatialite-android/wiki

> I execute the following statements
>
> SELECT DisableSpatialIndex('result_table_1156', 'GEOMETRY');
> SELECT DiscardGeometryColumn('result_table_1156', 'GEOMETRY');
> DROP TABLE IF EXISTS idx_result_table_1156_GEOMETRY;
> DROP TABLE IF EXISTS result_table_1156;
> Doing this in the spatialite-gui removes all data with success.
>
> But on Android the 3rd statement fails giving the exception "unable
> to
> open database file"
>

I'm not an Android guru, but few general order considerations could
be useful anyway.
all idx_* tables aren't at all "ordinary" tables, they effectively
correspond to some R*Tree Spatial Index, and are implemented as
"special" Virtual Tables requiring active R*Tree support.
not all versions of libsqlite3 necessarily support the R*Tree driver:
it could by selectively disabled at build time.
dropping a Virtual Table strictly requires specific driver support,
so if for any reason you are effectively using at run-time some
libsqlite3 lacking R*Tree support this could easily explain your
reported issue.

> What am I doing wrong? What is the correct way to drop a table and
> its
> related data?
>
> Or, more specific, the idx_* tables ?
>

always using the DropGeoTable() SQL function in order to fully
and safely remove any Geometry Table and related paraphernalia
is the *warmly* suggested option starting since 4.2.1

bye Sandro

[Robert Oehler]

Thanks for your quick Sandro.

The project I'm working on is tied to these outdated versions, so regrettably, I cannot update.

But there must have existed a way to delete a geometry table in this outdated spatialite version too, or not?

[a.fu...@lqt.it]

On Wed, 8 Jul 2015 03:55:37 -0700 (PDT), Robert Oehler wrote:
> But there must have existed a way to delete a geometry table in this
> outdated spatialite version too, or not?
>

certainly yes: you are always free to attempt dropping a geometry
table using code of your own.
but in this case you should be absolutely well conscious that n
othing ensures that your code will continue to correctly work on
any possible future version of libspatialite: it could easily
fall victim of some cross-version issue before or after.

that said, the standard approach expected by obsolete versions
was exactly the one you've already adopted, i.e. calling first
DisableSpatialIndex() and DiscardGeometryColumn(), then dropping
any eventual spatial index and finally dropping the main table
itself.

accordingly to your report, you get an error while attempting to
drop the spatial index; this usually is a symptom that some
inappropriate version of libsqlite3 is loaded at run-rime, one
unable to support the R*Tree driver.

you can easily test this even using libsqlite3 alone: just
try to execute the following SQL statements:

CREATE VIRTUAL TABLE idx_test USING rtree(pkid, xmin, xmax, ymin,
ymax);
DROP TABLE idx_test;

if "CREATE VIRTUAL" fails this surely means that you're
using an incomplete libsqlite3.

OTH
Sandro

[Lorenzo Pini]

Hi Sandro,
I'm working with Robert and I did some test.
We are using an old version of GeoPaparazziSpatialiteLibrary, the one with Spatialite 3.0.1
This is the test I did:
https://gist.github.com/Gnafu/f9dac8801aae80e47896

The result is the following:

dbVersion() : 3.7.9
Column: spatialite_version( )
Data: 3.0.1
Column: proj4_version( )
Data: Rel. 4.7.1, 23 September 2009
Column: geos_version( )
Data: 3.2.2-CAPI-1.6.2
Running CREATE
Running DROP

unable to open database file

The "CREATE VIRTUAL TABLE idx_test USING rtree(pkid, xmin, xmax, ymin, ymax); " statement is run and the resulting tables can be found on the database:
- idx_test
- idx_test_node
- idx_test_parent
- idx_test_rowid

Only the "DROP TABLE idx_test;" statement fails.

Regards
Lorenzo Pini

[a.fu...@lqt.it]

Hi Lorenzo,

for some obscure reason your libsqlite3 is clearly malfunctioning;
I'm completely unable to imagine any rational reason explaining
why it's unable to DROP an R*Tree Virtual Table just being successfully
created in the previous step.

please note: libspatialite, libproj and libgeos have nothing to
do with this issue: creating and dropping R*Tree Virtual Tables
is a task exclusively managed by libsqlite3 itself.

(possibly) useful hint: try updating your libsqlite3 to a more
recent version (or to an alternative distribution).

my 2 cents,
Sandro