I have a website solution that uses on SQLite database for each tenant account. Without going into much depth about why we chose this solution, we chose it due to SQLite support on distributed/offline systems.
All databases are manipulated using the same PHP file structure. I wish to update the database version iteratively for all accounts so that they are all at the same version number.
I have a script that loops over each, and can use either PHP(Yii) or the shell to execute queries.
I would like to wrap the manipulation to each database in a transaction. It appears as though DDL commands may already be auto-commit in nature.
Question: How to accomplish a SQLite DB upgrade which, if it fails, will report a failure? Using that report, I could prompt the system to re-attempt or report an error to an admin. Ideally, I would like to wrap the whole upgrade in a transaction to prevent inconsistencies, but I’m fairly certain that this is not possible.
One though I had was to backup the database temporarily during upgrade, and delete it on success.