The general process I use each time is:
- Record the database version number in the database
- Define the expected db version the application
- Keep a master db for comparison
- Only make one set of changes at a time
- Automate differencing as much as possible
- Unit test, test and test again. The tests should fail the moment a table is modified.
MSDE (= SQL server) database
8 developers (pair programming)
Every developer had 2 databases, Unit Test db and Application Test DB (UnitDb and AppDb from now on). With msde (now sql express) there are no licensing costs to worry about.
There was also a Master database stored on a central server that served as the canonical reference.
The database version number was stored in a view ('select xx as VersionNumber).
To make database changes, the following process was used:
- Check out the latest version of the app
- Increase the version number in the application
- Make and test DB changes. Usually this was done in UnitDB. AppDb was kept synchronised using SQL Compare
- Update the version number in UnitDB
- Generate an update script using Sql Compare. Scripts were named UpgradeXXX.sql where XXX is the version that was being upgraded from.
- Generate a CreateDatabase.sql script (for shipping with the app) and a CreateDatabaseXXX.sql (for unit tests only) script. In this case XXX is the version that will be created. The 2 scripts are the same except for the name.
- If necessary (rarely) append further queries to the scripts. E.g. to set the values of new fields, or to add new data rows.
- Update unit tests to suit the new db
- Check in changes
- If no database is found, then run CreateDatabase.sql
- Get the current version number from the database
- If it is less than the expected version number then
run UpgradeXXX.sql and go to 2
- Make sure the current version is correct
- Make sure that every table and every field exist (this doesn't always need to be explicit as the persistence unit tests should pick up any problems here).
- Create a new blank database (for a number of different versions) and work though the upgrade process to make sure the final database is correct.
Also the upgrade scripts can do more than one set of version changes. ie Upgrade001.sql could upgrade the version to v10 so that scripts 002 - 009 don't need to be run.
Situation 2 (delphi and dbisam) will follow in a later post.