Loading data from an old database without modifying it

[mgunyho]

I have seen that after updating Qcodes, loading a dataset from some old database triggers a message like "updating database from vX to vY" (I guess the schema changes? And the database is migrated?).

I have a huge (~50 GB) database that is fairly old, and I'm not sure which version of qcodes it was created with. I am scared that if I now load a dataset from the database, it triggers a lengthy database migration, and if something goes wrong, the entire database gets corrupted. Is there a way to open a database so that it for example raises an error instead of trying to upgrade it if the version has changed? Or is there an easy way to find out the schema version?

If I can find out the schema version, I could use an older version of qcodes to export just the data that I need to a smaller database, and upgrade that. I could also make a backup of the db and try it, but that would take up quite a bit of disk space (we actually have several databases that are tens of gb) and require extra care to not mess up the backups.

[mgunyho]

Based on how it's done in qcodes, I checked the versions with the following code:

import sqlite3
from qcodes.dataset.sqlite.db_upgrades import _latest_available_version, get_user_version
from qcodes.dataset.sqlite.connection import ConnectionPlus

fname = "path/to/my_database.db"

sql_conn = sqlite3.connect(fname, detect_types=sqlite3.PARSE_DECLTYPES, check_same_thread=True)
conn = ConnectionPlus(sql_conn)
print(f"{_latest_available_version() = }")
print(f"{get_user_version(conn) = }")

For now the versions were matching, but this would be nice to know for future reference. Perhaps qc.dataset.sqlite.database.connect() could take a parameter like allow_upgrade, and then that should be possible to pass also from qc.load_or_create_experiment, and somehow also from with initialised_database_at() (my use case is the latter, I'm looking at data from multiple databases).

[jenshnielsen]

It is not possible for QCoDeS to operate on an older version of a database since the code in a given version of QCoDeS expects the database to have the schema implemented by the matching db version. It is not feasible for QCoDeS to be able to load data from older versions.

All upgrades have been extensively tested and we have not seen any issues but its of cause not possible to guarantee that corruption cannot happen but it is very unlikely and has as far as I know never been observed.

You should be able to find the db version upgrades in the changelogs. For instance you can see here that https://qcodes.github.io/Qcodes/changes/0.3.0.html#new-improved qcodes 0.3.0 upgraded to db version 5.
Another way to figure this out is to look at the git blame of this file https://github.com/QCoDeS/Qcodes/blame/master/qcodes/dataset/sqlite/db_upgrades/__init__.py
if you check the commit of each change you can see which tag -> release it was first included in

[mgunyho]

It is not possible for QCoDeS to operate on an older version of a database

This is understandable, and I agree that it's not feasible to support loading from old databases without upgrading them.

But maybe what I would like is that the upgrade isn't so automatic. I would prefer if opening a database that is an old version would raise an exception by default, which could then be explicitly overridden with an option like allow_upgrade=True. The case I have in mind here is that we have some database that is e.g. a year old, and currently trying to just open it for reading has a nonzero risk of corrupting it.

If a user is for example trying to just quickly check the snapshot from some old database, they will probably not be looking at the modification date of the db, then cross-checking the qcodes changelog (and the version of qcodes we're using, which always lags a bit behind the latest release) to see if the database will be upgraded. In fact, the average user in our lab probably doesn't even know anything about SQL or schemas or why the db needs to be upgraded. If qcodes would warn the user that reading from the db will modify it, they have the chance to make a backup (by copy-pasting the db file, or if the db is huge, manually downgrading qcodes, exporting the relevant runs to another db, and then upgrading that) if the data is important.