Tracking: Postgis WC improvements / issues

[olsen232]

• CRS clashes are not well explained. Since we currently never overwrite an existing CRS in the postgis database, and it comes pre-loaded with all the EPSG ones - this means if your dataset has a different dataset to your WC, you can commit the WC version to your dataset, but you can't reset / checkout the dataset version to your WC. This is not well documented.

• Description, XML metadata are not found in Postgis working copy. Titles are present, as comments. This is not documented.

• postgres is the only format we have so far that has strict types, eg an int16 really only has 16 bits - datasets V2 and GPKG don't really care. so, out-of-range data will fail in postgres, probably in an untidy way. This needs some design work, and changes outside of the postgis working copy itself.

• Find and fix other things that don't roundtrip well, eg timezones. Roundtrips in general also need more design work, and other changes outside of postgis

• If you try and create a working-copy in a schema that already exists, it will assume it's already created and try and read the state table, then fail. Need to handle more "bad-states" that the user could easily get into, and robustly handle them / help the user find their way to a good state.

• Port from psycopg2 to sqlalchemy for less code repetition

• yet to dig into - some bug involving Geometry, bytea and ST_SetSRID

• Make sure all SQL format strings are injection-safe - at least one has a bug.

[olsen232]

[x] bug involving Geometry, bytea and ST_SetSRID: f5863e9
[x] Make sure all SQL format strings are injection-safe - at least one has a bug: 9463a07

[olsen232]

Re: creating working copies using a schema that already exists.

This could be something the user wants to do if they want sno to take control of versioning of an existing table. Eg instead of importing a postgis DB, you could initialise a sno repo with that DB as its WC, and the commit the contents of that WC which will be showing up as a yet-to-be-committed insert. Now that data is version controlled.

To make this work, we would need a bit more work on the robustness working copies in general - make sure we can handle whatever odd state they can get into - and, we would need to fix FR #249

[olsen232]

[x] Custom CRS can be overwritten -34c7dce

[x] Stricter type checking - #300

Still TODO before launch:
[ ] Add documentation about Postgis and GPKG, including supported types, approximated types, supported/unsupported meta items, and configuring the postgres database.

[ ] If you try and create a working-copy in a schema that already exists, it will assume it's already created and try and read the state table, then fail. Need to handle more "bad-states" that the user could easily get into, and robustly handle them / help the user find their way to a good state.

[ ] Merge #304 - approximates types that can't be stored perfectly in GPKG or Postgis

Leave open and file separately:
[ ] Look into how DATETIMETZ and other odd types roundtrip.

[olsen232]

[x] Add documentation about Postgis and GPKG, including supported types, approximated types, supported/unsupported meta items, and configuring the postgres database. #309

[x] If you try and create a working-copy in a schema that already exists, it will assume it's already created and try and read the state table, then fail.... etc. #306

[x] Merge #304 - approximates types that can't be stored perfectly in GPKG or Postgis

[x] Filed separately: Look into how DATETIMETZ and other odd types roundtrip. #310