Question

After seeing this question I wonder how fogbugz does this what is the strategic what are the steps. I think this is a great topic for a Blog post.

Answer 1

When FogBugz connects to a database it looks for a table named Version which contains the database version ID. This is a serial number. If it's 0 or missing, it's not a FogBugz database.

The FogBugz source code knows what version of the database it's expecting. If the version number is too low, it runs through a list of steps to upgrade it to the current version. This may including creating tables, columns, and indexes. Some new columns may have to be filled in to preset values which we call "backfilling."

Every time a programmer on the FogBugz team needs another table or column, they add it to the source code and increment the database version serial number. That way the next time FogBugz hits the database it will automatically create the new table or column as needed.

Answer 2

Further to Joel's answer, if you have a local FogBugz install you can see how this all happens in the CSchema.was/asp/php file. It's basically a big switch-case statement.

The strategic value is huge. We don't need to know what database version you're using. We know automatically all the changes that need to happen between your version and the version of FogBugz you've just installed, be it 5, 6, or 7. While they're usually a bit more complex because of the amount of history, we've done successful upgrades from FogBugz 3 to 7. That's five years of history in one click.

The ability to have FogBugz create its own schema is valuable to support as well. With databases living in the wild for years, it's possible that some odd stuff can happen. FogBugz itself is pretty forgiving of oddness in its database, but if things get too out of whack, it's lovely to always have the option to start from scratch and create a new database that is exactly as FogBugz wants it.

I have a MySQL script which reads the schema of the new database and pulls the info out from the old database and into the new, fresh schema.

SET @old_database = 'myolddb';

SET @new_database = 'fbnew';

SELECT CONCAT(
  "SELECT '",TABLE_NAME, "'; ",
  " TRUNCATE ",@new_database,".", TABLE_NAME,"; ",
  " INSERT INTO ", @new_database,".",TABLE_NAME,"(", GROUP_CONCAT(COLUMN_NAME ORDER BY ORDINAL_POSITION), ") ",
  " SELECT ", GROUP_CONCAT(COLUMN_NAME ORDER BY ORDINAL_POSITION), " FROM ",@old_database,".", TABLE_NAME,"; ",
  " SHOW WARNINGS;") AS Statement 
FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = @new_database 
      AND TABLE_NAME NOT IN ('dbcopier','dbupdate','version','fblock','fragmentcache','fragmentdependency','id','indexdelta','indexfile','indexfilepage','trainingrequest') 
      AND COLUMN_NAME NOT LIKE 'FILTER_%' 
    GROUP BY TABLE_NAME 
INTO OUTFILE 'c:\\dump.sql';

SOURCE c:\\dump.sql