Bring an Existing Database up to the Current Schema

EpiSODIC records which version of its schema a database was built with, and refuses to open one it does not recognise rather than failing later with an unexplained SQL error (or, worse, not failing and reading a column that has since come to mean something else). When you upgrade the package and its schema has moved on, this is what brings your existing database along - in one transaction per step, so a failed migration leaves the database exactly as it was.

Usage

episodic_db_migrate(
  db_path = Sys.getenv("EPISODIC_DB", unset = NA),
  backup = TRUE
)

Arguments

db_path

Path to an existing SQLite database, or a MariaDB/MySQL DSN (see episodic_db_dsn_mariadb()). Defaults to the EPISODIC_DB environment variable.

backup

Whether to copy a SQLite database beside itself before the first step. Ignored for MariaDB/MySQL.

Value

Invisibly, the schema version the database is at afterwards.

Details

You rarely need to call this yourself: episodic_run_cron() does it at the start of every run that finds the database behind the installed package, unless database.auto_migrate is set to false in your configuration. Call it directly where schema changes go through change control rather than through the application - with that setting off, and from an account that holds the privileges to change the schema.

Nothing is ever dropped or rewritten: migrations add tables, add columns, and backfill values. Your surveillance history, your assessments and your audit trail are carried forward untouched. A SQLite database is copied beside itself before the first step (<file>.schema-<from>-to-<to>.bak); a MariaDB or MySQL database is not, since a server's backups belong to whoever runs the server.

Each step runs inside a transaction, which under SQLite covers the schema changes themselves. MariaDB and MySQL commit implicitly on every CREATE/ALTER, so there a failed step can leave its schema change in place with no version row recorded; every migration is written to skip what it finds already done, so simply running this again is the correct response.

Two processes can safely be asked to migrate the same database at once - a scheduled run and a manual call, or two servers sharing one MariaDB schema. One migrates; the other waits for it (up to ten minutes on MariaDB/MySQL, five seconds per step on SQLite), finds the work done, and carries on.

A database created by EpiSODIC 0.12.x or earlier carries no version at all, since the version table postdates it. Such a database is adopted at version 1 (which is the shape it already has) rather than refused.

Examples

db_path <- tempfile(fileext = ".sqlite")
con <- episodic_db_create(db_path)
DBI::dbDisconnect(con)

# already current: reports so and changes nothing
episodic_db_migrate(db_path)
#> Database is at schema version 14.

file.remove(db_path)
#> [1] TRUE