Guidance on InitSpatialMetaDataFull

[David Smith]

Hi All,

I hope you are all well. Thank you for everything you do in maintaining Spatialite. 

I've been looking at ensuring Django (specically GeoDjango) is compatible with latest versions of various libaries and I've come accross an issue when using Spatialite 5.0 with sqlite 3.36. 

Since Django introduced support for Spatialite 5.0 `InitSpatialMetaDataFull(1)` is called instead of `InitSpatialMetaData(1)` as per the guidance seen here. [1] 

This worked fine until sqlite 3.36 which introduced this change "An error is raised on any attempt to access the rowid of a VIEW or subquery". See their full release notes [2]

This change causes the Django tests to fail with "no such column: rowid". Reverting back to `InitSpatialMetaData` seems to fix the issue. 

Could I ask for your views on what the recomended approach is here? I appreciate this may be a little vauge. The ticket I raised against Django is here, for reference. [3].

Thank you for any guidance you could provide. 

Kind Regards

David 

[1] https://www.gaia-gis.it/fossil/libspatialite/wiki?name=PROJ.6

[2] https://www.sqlite.org/draft/releaselog/current.html

[3] https://code.djangoproject.com/ticket/32935

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

On Tue, 20 Jul 2021 01:16:47 -0700 (PDT), David Smith wrote:
> Hi All,
>
> I hope you are all well. Thank you for everything you do in
> maintaining Spatialite.
>
> I've been looking at ensuring Django (specically GeoDjango) is
> compatible with latest versions of various libaries and I've come
> accross an issue when using Spatialite 5.0 with sqlite 3.36.
>
> Since Django introduced support for Spatialite 5.0
> `InitSpatialMetaDataFull(1)` is called instead of
> `InitSpatialMetaData(1)` as per the guidance seen here. [1]
>
> This worked fine until sqlite 3.36 which introduced this change "An

> error is raised on any attempt to access the rowid [1] of a VIEW or

> subquery". See their full release notes [2]
>
> This change causes the Django tests to fail with "no such column:
> rowid". Reverting back to `InitSpatialMetaData` seems to fix the
> issue.
>
> Could I ask for your views on what the recomended approach is here? I
> appreciate this may be a little vauge. The ticket I raised against
> Django is here, for reference. [3].
>

On Tue, 20 Jul 2021 01:16:47 -0700 (PDT), David Smith wrote:
> I've been looking at ensuring Django (specically GeoDjango) is
> compatible with latest versions of various libaries and I've come
> accross an issue when using Spatialite 5.0 with sqlite 3.36.
>

> This worked fine until sqlite 3.36 which introduced this change "An

> error is raised on any attempt to access the rowid [1] of a VIEW or

> subquery". See their full release notes [2]
>
> This change causes the Django tests to fail with "no such column:
> rowid". Reverting back to `InitSpatialMetaData` seems to fix the
> issue.
>

Hi David,

yes, I confirm that there is an unexpected failure in a single
test of libspatialite when using libsqlite3 3.36

the failing test is absolutely surprising and almost
inexplicable: while attempting to execute this very
simple SQL statement

ALTER TABLE "main"."table_1" RENAME TO "newtable_1";

the following exception is thrown:

error in trigger ISO_metadata_reference_row_id_value_insert:
no such column: rowid

NOTE: I'm absolutely unable to undestrand why libsqlite3
fires a trigger on a table (ISO_metadata_reference) that
isn't minimally involved in the ALTER TABLE.
honestly it seems a bug introduced with the latest changes
of version 3.36 about rowid in views and subqueries.

that said, the SQL code of the trigger is surely dirty
(and almost surely malfunctioning also on previous
versions) but starting from 3.36 it causes a fatal
exception blocking ALTER TABLE.

a possible solution (for future versions of libspatialite)
seems to be a more wise rewrite of the trigger's SQL code
by replacing its last line as follows:

... AND NOT EXISTS
(SELECT eval('SELECT rowid FROM ' || NEW.table_name ||
' WHERE rowid = ' || NEW.row_id_value));

the same change should be applied to the similar trigger
ISO_metadata_reference_row_id_value_update as well.
after applying these two changes the whole test coverage
of libspatialite runs smoothly also on sqlite3 v.3.36

----------------------------

quick hot fix:
as reported by the release notes of version 3.36

"The -DSQLITE_ALLOW_ROWID_IN_VIEW compile-time option
is available to restore the legacy behavior for
applications that need it."

and it exactly seems to be the case of libspatialite.

bye Sandro


p.s. InitSpatialMetaDataFull() is not directly
connected to this extravagant issue.
it's reponsible for creating the ISO_metadata_reference
table and related triggers, but no exception is
raised during this step.
the problem occurs only when attempting to
rename any table non minimally being connected
to ISO_metadata_reference, that is really
surprising.

[David Smith]

Hi Sandro

Thank you for you quick and detiled response, and thank you again for everything you do for this project. :-)

David

[Christopher Banck]

Hi Sandro,

is there a ticket tracking this bug?

Maybe it is already fixed in a newer version?

Ubuntu 22.04 is now using Spatialite 5.0 with sqlite 3.37, and GeoDjango can't run with sqlite in this setup anymore.

Thank you!

Cheers, Chris