Support User-Defined Mapping Table UUIDs

[jyutzler]

In the current draft, the IDs used to establish relationships are the integer primary keys of the contents themselves. This requirement could be relaxed to allow string values (instead of integers) and that these string values could represent UUIDs. This should not be a significant coding change on most platforms.

[bradh]

GeoPackage does require data tables to have integer PK, and that is a fast lookup. Allowing strings would be more complexity (because the related table extension metadata would need to specify what column needs to be looked up - it would no longer be the integer PK for the data table). It would also impose greater overhead to either maintain the index for the extra row, or to do a full scan on read.

Overall I'm fairly strongly opposed to relaxing the current constraint on PKs.

There is another way to make it possible to write this kind of data (if you know you are the only writer and performance is not critical on write):

• create the data tables with the UUIDs as a data column
• create the mapping table with empty IDs, but add two new columns for the UUIDs (additional columns are permitted)
• for each UUID, do the lookup of PK in the mapping table and populate the mapping table IDs

[jyutzler]

Note that in gpkgext_relations we do specify the name of the key column so that part is not a concern. I agree with the rest of your post.

One facet that might not be clear is that you could possibly have multiple feature rows with the same UUID (perhaps due to topological structuring of some sort) and you could conceivably link a feature (by its UUID) to some related content that way. I'll let you decide whether this would be a bug or a feature.

[jyutzler]

moved to :point_up: