Question

This is a very simple question, and maybe the man himself can provide insight on this :)

Can anyone provide the pseudocode that is used for schema management in FogBugz On Demand?

I'm running into an issue and I'm trying to figure out if I'm handling it right... I have a module that runs each time someone spins up their site and examines their database to make sure that they have the right changes in place. if they are missing changes, then the script makes the required changes.

My issue is that I was trying to tie it to the session_start portion of the Global.asax, but it seems to be rather flaky at times, and I'm trying to come up with a better scenario.

For reference, I'm trying to run 1 x web application that can respond to any number of hosts, where the host maps via a metabase to find out what database it belongs to and then makes the necessary connections. (I'm assuming that is the same model that Fog Creek is using for FogBugz?)

Answer 1

Every time any version of FogBugz runs, the first thing it does is check the Version table in the database to make sure we have the right version to run against.

• If the version table is not readable, we say that FogBugz couldn't read your database.

• If the version is higher than what the current version of FogBugz needs to run, we say that you need a newer version of FogBugz.

• If the version is correct, we proceed.

• If the version is lower, we run through CSchema, which is our schema description. This is basically just a huge switch statement that knows how to get from any database version to the current version. This goes all the way back to 0, which is how FogBugz knows how to build its own fresh database to run on.

For every FogBugz install, you can see the database version number next to the code version number (Help > About FogBugz).

The upgrade process for On Demand is basically the same, except that it is often done en masse, rather than waiting for a page load to happen.

It'd be a bit of overkill for anyone to replicate this system unless they wanted, like us, to write software that can be hosted and installed with minimal changed.

The more general question of how to handle database migrations for hosted ASP .NET apps might better be asked at StackOverflow. Our design is geared toward several special conditions:

• Need to segregate customer data into discrete databases
• Need to support all past versions of FogBugz (well, back to 3, anyway).
• Need to have near-identical versions of the software running both on our servers as FogBugz On Demand, and on customer servers.