Upgrading an Older Database with upgrade

kent upgrade takes a database written by an older Cordelia and writes a new one that the current Cordelia can open. Your activities come across. The old database is only read and is not changed.

When you need it

A Cordelia database has a schema version, which changes when a new Cordelia stores something the previous one did not. A newer Cordelia refuses to open a database written at an older version, and so do the Kent commands that read one:

$ kent info old.sqlite
kent info: failed to open database: This database uses an older schema and cannot be opened; version 202611 is required. To keep its data, convert it with kent upgrade, which writes a new database at the current version.

Importing the .FIT files again works only if you still have them. upgrade does not need them.

Synopsis

kent upgrade --in <dbfilename> --out <newdbfilename>

Arguments

Flag	Required	Description
`--in <dbfilename>`	Yes	The older database to read. It is not changed.
`--out <newdbfilename>`	Yes	The database to create. Refused if it already exists.

$ kent upgrade --in old.sqlite --out new.sqlite
Upgraded old.sqlite (schema version 2) to new.sqlite (schema version 202611)
Tables carried:          9
Rows carried:            420
Reference tables kept:   3
New columns left empty:  32
    Lap.nec_lat
    Lap.nec_long
...
    Session.max_push_cadence
Warnings:                0

Keep the old database until you have opened the new one and looked at it.

What it does

Kent creates a new, empty database exactly as createdb does, with the current tables and the current schema version. It then copies your rows into it, table by table, matching each column by its name.

In the old database	In the new one	What happens
A column	The same column	The values are carried.
Nothing	A new column	It is left empty, and listed in the report.
A column or a table	Nothing	It is left behind, with a warning.
A reference table	The same table, already filled	The new database keeps its own rows.

The reference tables hold the labels Cordelia shows for sports, devices and the like. An upgraded database gets the current labels, not the ones the old database was created with.

The report

Line	Meaning
`Tables carried`	Tables whose rows were copied from the old database.
`Rows carried`	Rows copied across those tables.
`Reference tables kept`	Reference tables where the new database kept its own rows.
`New columns left empty`	Columns the new database has and the old one did not. Each is listed under this line.
`Warnings`	Tables and columns of the old database that had nowhere to go.

Warnings

A warning means something in the old database is not in the new one. The upgrade still finishes, and the exit code is still 0.

$ kent upgrade --in old.sqlite --out new.sqlite
kent upgrade: warning: Activity.num_sessions is not in the new schema; 2 values were not carried
kent upgrade: warning: DeviceInfo.cum_operating_time is not in the new schema; 0 values were not carried
...
Warnings:                18

Read the number in each warning. 0 values means the column was empty, so nothing was lost. Any other number is data that is in the old database and not in the new one. It is still in the old database, which is the reason to keep it.

The usual cause is a column whose name changed. Kent matches columns by name, so it reports the old name as left behind and lists the new name as left empty.

A database at schema version 1 or 2 upgrades with no warnings. The example above is at schema version 0, where some columns had other names.

What it refuses

Nothing is written in any of these cases.

$ kent upgrade --in mydata.sqlite --out new.sqlite
kent upgrade: mydata.sqlite is already at schema version 202611, the version this build of kent creates; there is nothing to upgrade
$ echo $?
1

$ kent upgrade --in old.sqlite --out existing.sqlite
kent upgrade: existing.sqlite already exists

Refusal	Why
The database is already current	There is nothing to do.
The database is newer than this Kent	It was written by a newer Cordelia than this Kent knows. Kent does not write an older version. Use a newer Kent.
`--out` already exists	Kent never writes over a database, the same rule `createdb` and `restore` follow.
`--in` is not a database	It must be a SQLite database. A `.kent` archive is not accepted: use `restore` on the archive first, then upgrade the database it gives you.
A row cannot be stored in the new database	The message names the table.

A failure leaves nothing behind: no partial database and no temporary files.

While it runs

Kent works in a directory beside --out, named after it with .staging added, and removes it when it finishes or fails. Allow free space of about twice the size of the old database.