Fog Creek Software
Discussion Board

Version your database

I was having a peak at the new release of FogBugs and noticed at the bottom a comment that said "Database Version 324".

Whats the best way to go about versioning your database, say for a SQL database, not just a file based database like access?

Thursday, November 7, 2002

Access is an SQL database.

Matthew Lock
Thursday, November 7, 2002

Forgot the server, SQL Server database.

Thursday, November 7, 2002

I understand that you want to version the database schema during the development process. This is what we did: We had the database script in the source control repository (DDL and insert statements for catalog data and initial data).

If someone had to change the database script, the developer checked out the portion of the database script that needed to modify and checked the changes in when he was done. Then we set up the daily build so it dropped and created the database in the test environment so we were sure that there were nothing had broke.

The source control software generated the version numbers for our script files automatically.

Thursday, November 7, 2002

I generally create a table with some info. That info has standard stuff like Company name, address. Some defaults for the system, and usually a version number.

Any client software that thus runs, can check against the version when running.

Not much...but it is a handy start...

Albert D. Kallal
Edmonton, Alberta Canada

Albert D. Kallal
Thursday, November 7, 2002

Suggestion: Include a table with columns like "VersionNumber", "InstallDate", "Comments", etc, and in the database creation (or conversion) script insert an appropriate record into this table. The application can then read (and cache) this data and display it on any page or form. Depending on your needs, the version record can always replace the previous record (so that there is only one version record) or it can append to the table so that there is a history of version changes. In the second case, the application needs to select the MAX VersionNumber or InstallDate to get the current installed version. Additional columns can be included in this table (such as those suggested by Albert) if desired.

If you use SourceSafe, you can even automate the incrementing of the version number by including (in the database script file) an INSERT statement similar to:
INSERT INTO VersionTable
(ProductVersion, VersionString, ScriptDateString, InstallDate)
VALUES ('ProductName 2002', '$Revision:: nnn    $',
'$Date:: 11/17/02 10:25a  $', GETDATE())

When the script file is checked in (assuming that .SQL files are identified for keyword expansion in SourceSafe), the revision number and date will be updated in this statement. The disadvantage to this approach is that the SourceSafe keywords ($Revision:: and $Date::) are included in the string that is inserted in the table (but the application code can remove these before displaying the data).

Philip Dickerson
Thursday, November 7, 2002

*  Recent Topics

*  Fog Creek Home