I am focusing on an aura application that utilizes a nearby SQLite database and wondered the way i could manage database schema updates after i distribute new versions from the application. Also thinking about updates that skip some versions. E.g. rather than going from 1. to at least one.1, going from 1. to at least one.5.
What technique can you recommend?
We script every DDL switch to the DB so when we create a "release" we concatenate them right into a single "upgrade" script, along with any Saved Methods that have transformed "since beforeInch
There exists a table that stores the version quantity of the most recent patch applied - so upgrade tools can use any more recent patches.
Every Saved Procedure is within another file. Each begins by having an "place" statement to some logging table that stores Title of SProc, Version and "now". (Really an SProc is performed to keep this, it is not a raw place statement).
Sometimes throughout deployment we by hand change an SProc, or rollout odds &lifier finishes from DEV, and evaluating the login client's Make sure PRODUCTION databases allows us to check on that things are in the same version.
We have a "release" master-database, that we apply the updates, and that we make use of a restored backup of this for brand new installations (saves time of running the scripts, which clearly increase with time). We update that as &lifier when, because clearly if it's a little stale the later patch scripts does apply.
Our Release database also consists of sanitised starter data (that is erased, or sometimes adopted &lifier modified, before a brand new installation goes live - making this not incorporated in almost any update scripts)
SQL Server includes a plugin button to script a big change - so that you can make use of the GUI tools to create all the new changes, but instead of saving them produce a script rather. (really, there's a checkbox to always produce a script, if you forget and merely press SAVE still it provides you with the script it used after-the-fact, which may be saved because the patch file)
Within the situation of SQLite, you are able to take advantage from the user_version pragma to trace the version from the database. To obtain the version:
To create the version:
PRAGMA user_version = 5
Then i keep each number of updates within an SQL file (that's baked into the application) and run the updates required to wake up to the newest version:
Select Case currentUserVersion Case 1 // Upgrade to version 2 Case 2 // Upgrade to version 3 Case etc... End Select
This enables the application to update itself to the newest version no matter the present version from the DB.
IMO the simplest factor to complete would be to treat an update from e.g. 1. to at least one.5 like a succession of updates from 1. to at least one.1, 1.1-to-1.2, and so on. For every version change, have a conversion script/bit of code around.
Then, have a table having a version area within the database, and compile in to the the application the needed version. On startup, when the version area doesn't match the put together-in version, run all of the needed conversion scripts, 1 by 1.
The conversion scripts should ideally begin a transaction and write the brand new version in to the database because the last statement before carrying out the transaction.
Things I am thinking about is adding a SchemaVersion table towards the database which holds an archive for each version that is available. The final version from the SchemaVersion table may be the current degree of the database.
I will create (SQL) scripts that carry out the initial setup of just one. and after that the upgrade from 1. to at least one.1, 1.1-to-1.2, etc.
A fresh install to e.g. 1.2 will tell you each one of these scripts. This may appear just a little slow, but is just done once as well as on an (almost) empty database.
The large benefit of this really is that the fresh install will have a similar database schema being an upgraded install.
When I stated: I'm thinking about this. I'll most likely start applying this tomorrow. If you are interested I'm able to share my encounters. I'll be applying this for any c# application that utilizes LINQ-to-organizations with SQL Server and MySQL as DBMSes.
I'm interested to listen to anybody else's suggestions and concepts and when somebody can point me out a wide open source .Internet library or classes that implements something similar to this, that might be great.
EDIT: Within the response to another question here on SO I discovered a mention of the Migrator.Internet. I began utilizing it today also it appears like it is precisely what I had been searching for.