rgzn / beastr Goto Github PK

On writing to geopackage sqlite, the timezone from the R datetime object is erased. When read back into R, the time field no longer has a zone.

Currently have to handle this manually. Avoid confusion by always writing UTC times. In visualization we can convert back to local times.

Using append_layer() or append_database() uses sf::st_write() to modify an existing database. This is convenient because it handles all the necessary spatial columns, and modifies a geopackage's metadata to include new spatial data.

However, using st_write with geopackages in Shiny is difficult. A geopackage is an sqlite database, but the st_write interface to them treats them as a normal file. File I/O in Shiny is designed to isolate the side effects on a local computer, so it reads the entire file into a temporary directory, then you are just modifying Shiny's copy.

There are two potential workarounds:

• Do all the modifications on the Shiny copy of the database, and then provide a download button for the user to write over their local copy of the geopackage.
• Connect to the geopackage as a database using DBI::dbConnect(). This will allow for the Shiny app to interface with the data through a database connection instead of copying the file.

The latter is the proper approach. Besides being a cleaner interface, it starts down the road of extending these functions to other databases besides geopackage. But it will require some more work:

• Shiny DB connections should be done with the pool library to avoid complications with repeatedly connecting/disconnecting.
• This will require some additional options in append_layer(), perhaps a use_dbi = TRUE parameter.
• If the data added is spatial, we will have to write the necessary SQL modify a geopackage's metadata tables. There is an example of how to do this in build_database() because it was necessary for creating the View that ties fixes to animals. This process should be separated into its own function for re-use in append_layer() if using geopackages.

Not yet handling collar events/mortalities.

Using sf::st_write() to create tables in the geopackage doesn't support speciging UNIQUE constraints on tables (I think?). And SQLite3 doesn't support adding them to existing tables.

Possible workarounds:

• After creating tables but before creating View in build_database(), create the constraints via copying the tables and creating new ones as in this example: https://stackoverflow.com/questions/42969127/add-constraint-to-existing-sqlite-table

• Do everything in R. This is what I'm doing currently. This is not ideal, as the constraints only come from the R code within this package and any external modification could corrupt data