Generated Columns in SQLite not documented by GPKG Standard

[phidrho]

Hi,

SQLite supports 'Generated Columns' since SQLite version 3.31.0 (2020-01-22).

As @rouault investigated:

Because pragma table_xinfo() must be used to discover such columns and the type reported is e.g. "TEXT GENERATED ALWAYS" and not plain "TEXT"

it seems that there is a need to document this possibility properly in Standard.

[jratike80]

I am worried about interoperability. See https://sqlite.org/gencol.html#compatibility

Generated column support was added with SQLite version 3.31.0 (2020-01-22). If an earlier version of SQLite attempts to read a database file that contains a generated column in its schema, then that earlier version will perceive the generated column syntax as an error and will report that the database schema is corrupt.

To clarify: SQLite version 3.31.0 can read and write any database created by any prior version of SQLite going back to SQLite 3.0.0 (2004-06-18). And, earlier versions of SQLite, prior to 3.31.0, can read and write databases created by SQLite version 3.31.0 and later as long as the database schema does not contain features, such as generated columns, that are not understood by the earlier version. Problems only arise if you create a new database that contains generated columns, using SQLite version 3.31.0 or later, and then try to read or write that database file using an earlier version of SQLite that does not understand generated columns.

Does this mean that a GeoPackage that has a Genarated Column could not be read at all with any older SQLite library version than 3.31.0 (2020-01-22)? I just checked my computer and among the 30 sqlite3.dll files that I found for example the one used by ArcGIS Pro is older.

[rouault]

Does this mean that a GeoPackage that has a Genarated Column could not be read at all with any older SQLite library version than 3.31.0 (2020-01-22)?

yes, confirmed by my testing.

$ ~/sqlite-autoconf-3300100/sqlite3 my_db_with_generated_field.gpkg  "select sqlite_version(); select * from point_tbl"
3.30.1
Error: malformed database schema (point_tbl) - near "AS": syntax error

$ sqlite3 my_db_with_generated_field.gpkg  "select sqlite_version(); select * from point_tbl"
3.31.1
1|GP|a|b|ab|1
2|GP|c|d|cd|1

[bosborn]

Also worth noting Android 12 API 31 (October 4, 2021) is the first version with SQLite >= 3.31.0. Any GeoPackage with generated columns would most likely be incompatible with devices running pre API 31.

[phidrho]

Also worth noting Android 12 API 31 (October 4, 2021) is the first version with SQLite >= 3.31.0. Any GeoPackage with generated columns would most likely be incompatible with devices running pre API 31.

@m-kuhn Can you check this, would this affect QField?

[bosborn]

For iOS using bundled SQLite, iOS Version 14.1 (October 20, 2020, support on iPhone 6S or later) uses SQLite 3.32.3.

[m-kuhn]

QField uses sqlite version 3.39.2 (self-compiled, shipped with the app)

[jyutzler]

The GeoPackage Encoding Standard makes no statement regarding SQLite versions. From my perspective, the issue of generated columns is outside of GeoPackage scope and that anyone who uses newer SQLite capabilities runs the risk of interoperability issues, so this risk must fall on the implementer, not GeoPackage itself.

We are open to other perspectives on this matter.