GPKG: FID column should be declared with NOT NULL constraint

[Even Rouault]

See ​https://github.com/opengeospatial/geopackage/issues/282

[Even Rouault]

In 37323:

GPKG: declare feature id column of features tables and tile pyramid user data tables as NOT NULL (fixes #6807, relates to ​​https://github.com/opengeospatial/geopackage/issues/282)

[Even Rouault]

In 37324:

GPKG: declare feature id column of features tables and tile pyramid user data tables as NOT NULL (fixes #6807, relates to ​​https://github.com/opengeospatial/geopackage/issues/282)

[Jukka Rahkonen]

If someone happens to read this, explanation about what was fixed. Before the fix id column was created as

id INTEGER PRIMARY KEY AUTOINCREMENT

An integer primary key cannot be NULL in SQLite as documented in ​http://www.sqlite.org/lang_createtable.html#primkeyconst

However, the GeoPackage test is doing this:

Test Method
	

    SELECT table_name FROM gpkg_contents WERE data_type = ‘features’

    Not testable if returns an empty result set

    For each row from step 1

        PRAGMA table_info(table_name)

        Fail if returns an empty result set

        Fail if result set does not contain one row where the pk column value is 1 and the not null column value is 1 and the type column value is “INTEGER”

    Pass if no fails.

I think that the test was not good. It would be enough to check that column is integer and that it is defined as PK. If those two are true, column cannot be NULL. The NULL test is unnecessary.

[Even Rouault]

You're right Jukka. I read too fast/wrongly the SQLite documentation. What is surprising, and I'd say a sqlite bug, is that if you do "PRAGMA table_info(table_name)", then a column of type INTEGER PRIMARY KEY returns notnull = 0, whereas a column of type INTEGER PRIMARY KEY NOT NULL returns notnull = 1. In both cases, I'd expect notnull = 1 to be returned given SQLite doc.