[QGIS-Developer] GPKG and FID -- can we fix this mess?

[Nyall Dawson]

On Fri, 16 Oct 2020 at 01:48, Matthias Kuhn <matthias at opengis.ch> wrote:
>
> If there is a managed database where there has been a conscious decision to have an integer id column (postgres case) and workflows are built around this: filling the id on insert, building foreign keys against it etc, that works just fine.
>
> But if GeoPackage is used as a generic dataformat (typical Shapefile replacement), requirements are different and workflows less managed. E.g. The "simple" case of merging two datasets already poses the question what to do with the two colliding fid attributes. Delete the original fid columns and regenerate one. Or preserve both and generate a new one (leading to fid, fid_1 and fid_2).

I agree with Matthias here -- a table from a full enterprise level
database like Postgres or SQL server naturally lends itself to
stricter constraints on the features. Geopackage is not an enterprise
level database solution, so we should be forgiving here and just let
users do their work without imposing artificial constraints on them.


>
> If we look at the fid attribute as part of the data (where we have foreign keys pointing two etc) we want it to be preserved.
> If we look at it as a purely internal technical id (for RTree management etc) we don't care for it and can just regenerate and forget about it.
>
> It looks as if we need to keep it as a technical id.
> I like the idea to transparently generate it in addFeature.
> I would also prefer to hide it away from the user and only leave it available through $id. This would make it clear to users that it's for internal use and no one would expect it to be preserved after a merge operation. For backwards compatibility there could be a provider flag to re-enable it.

I don't think we should do that -- having them show in some
circumstances and not others will be confusing.

I'm ok with exposing it, but just being tolerant and automatically
handling constraint violations for the users.

Nyall



> I hope that makes sense.


>
> Matthias