Question regarding SQLite STRICT tables

[heidivanparys]

From https://sqlite.org/stricttables.html

Some developers appreciate the freedom that SQLite's flexible typing rules provide and use that freedom to advantage. But other developers are aghast at SQLite's flagrant rule-breaking and prefer the traditional rigid type system found in all other SQL database engines and in the SQL standard. For this latter group, SQLite supports a strict typing mode, as of version 3.37.0 (2021-11-27), that is enabled separately for each table.

Does this have influence on the GeoPackage specification? Taking into account #594 and related issues?

[rouault]

From a quick testing, STRICT table won't be usable with GeoPackage

Totaly unusable for feature tables, given that GeoPackage uses custom geometry types

$ ~/install-sqlite-3.39.0/bin/sqlite3
SQLite version 3.39.0 2022-06-25 14:57:57
sqlite> CREATE TABLE "poly" ( "fid" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "geom" POLYGON) STRICT;
Parse error: unknown datatype for poly.geom: "POLYGON"

It could still be used for attribute tables, but not if they use BOOLEAN, FLOAT, TINYINT, SMALLINT, MEDIUMINT, DOUBLE, DATE, DATETIME, TEXT(n) or BLOB(n) data types. So what is left is just INT, INTEGER, REAL, TEXT and BLOB. Quite restrictive...

[rouault]

You could actually achieve the equivalent of the STRICT behaviour by using the SQLite3 typeof() function in CHECK constraints (cf https://www.sqlite.org/lang_corefunc.html), which should work accross all existing SQLite3 verions (typeof() was introduced in 2002 in sqlite 2.5.2 according to https://www.sqlite.org/changes.html). The downsides would probably a longer insertion time as evaluation of CHECK is going to be more costly than built-in STRICT functionality

e.g.

-- we want to allow integer values in a real column. and we allow 'null' as well as there's no NOT NULL constraint
sqlite> CREATE TABLE test(fid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, AREA REAL check(typeof(AREA) in ("real", "integer", "null")));
sqlite> insert into test (AREA) values (1); -- ok
sqlite> insert into test (AREA) values (2.5); -- ok
sqlite> insert into test (AREA) values (NULL); -- ok
sqlite> insert into test (AREA) values ('x'); -- not allowed
Error: CHECK constraint failed: test

For geometry blobs, it might also be possible with something more advanced, by inspecting bytes 9 to 13 to get the geometry type (and byte 8 to get the endianness). (I've done things like that, and even a bit more advanced, like patching the SRID of blobs like in https://github.com/OSGeo/gdal/blob/master/ogr/ogrsf_frmts/gpkg/ogrgeopackagetablelayer.cpp#L6195)

[heidivanparys]

@rouault Thanks for the testing and the feedback!

[jyutzler]

@heidivanparys agreed that we can close this now.