Opened 4 months ago

Closed 4 months ago

Last modified 4 months ago

#6807 closed defect (fixed)

GPKG: FID column should be declared with NOT NULL constraint

Reported by: Even Rouault Owned by: warmerdam
Priority: normal Milestone: 2.1.4
Component: OGR_SF Version: unspecified
Severity: normal Keywords: rouault
Cc:

Change History (5)

comment:1 Changed 4 months ago by Even Rouault

Resolution: fixed
Status: newclosed

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)

comment:2 Changed 4 months ago by 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)

comment:3 Changed 4 months ago by Even Rouault

Component: defaultOGR_SF
Milestone: 2.1.4

comment:4 Changed 4 months ago by 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.

comment:5 Changed 4 months ago by 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.

Note: See TracTickets for help on using tickets.