Unit Test Crazy!
Parent-Child Unit Tests

The Database Upgrade Problem

This is a problem I think everyone has at some point when working with databases that can’t lose data, and specifically those that generated their database from another intermediary. The problem is this: when someone changes the source file from which the database is generated, how do you upgrade said database without losing any data? Or, losing as little data as possible.

In my mind, there are two approaches to this. The first is to attempt to run a diff between the new file and the old file, and generate an alter script that can be run on the database. The alter script contains the information to add new properties and delete old properties as needed, and can be stored in source control to run in sequence if required. These generated alter scripts are also nice, because if there’s a problem with one of them (say, a property is renamed and the diff believes it was a drop + add) you can fix it manually and still leave the rest of the script alone.

The problem with this solution is that it requires the old version of the file as well as the new version, causing some interesting workflow problems. For example, let’s say I’m a programmer that wants to add a new property to the database. Here’s what I want to do:

  1. Open my XML
  2. Add the property
  3. Regenerate the code
  4. Run the new scripts on the database

In actuality, here’s what I have to do:

  1. Copy my old XML to a backup.
  2. Add my property to my new XML
  3. Run the generator with my old version and new version of my XML
  4. Run the new scripts on the database.

So, now, I have my old version and new version, I test what I’ve done and find that I added the property wrong or I need an extra property. What do I do? I can’t revert the database. I have to produce *another* copy of the XML, change it and run the upgrade system… this leaves me with lots of old backed up XMLs… it’s just not pretty. Then what happens when another programmer wants to change things? Provided you’re not checking in your alter scripts (and, sometimes, even if you are) he has to hold on to his old XML, update to the new one, run the generator, run the alter script and now he can get working. That’s best case. What if he’s working when you check in? It’s just not pretty.

The other method (and I’ve seen this done) is to consult the database for its current state, and diff it against what the XML is expecting, then change the database accordingly. In this case, you could also, of course, just generate an alter script, but you run into the issue of, again, having lots of alter scripts for a single check-in. In addition, sometimes manipulation of the database (as mentioned before) can have unforeseen consequences. If your diffing system interprets a rename as a drop and re-add, you lose that data, and if you’re going directly to the db instead of an alter script, that’s bad.

The only solution here I can think of, which isn’t great, is to also have a “revert” script generated with your alter script, then write a shell script for your day to day workflow that does the following:

  1. If it exists, run the revert script from your previous run of the upgrade generator (here, it would be nice to also be able to tell if this were actually necessary)
  2. Use your version control system to check the version of the XML you are developing against and grab the pristine copy of that file to a temporary location (most source control allows you to do this, if only for diffing purposes).
  3. Run the upgrade generator, which produces an alter and a revert script.
  4. Run the alter script on the database.

Then, you can change your XML again (or merge with something another programmer did) and what you’d just done to the database is reverted before applying the new change. Problems? You have to remember to delete the revert script when you check in, so that whatever you checked in doesn’t get undone. I’m sure there are ways to check to see if you actually want to run the revert script, but generally, I can’t think of a good one.

Anyone found other ways to solve this problem?

By: Jeff | 04/02/08 | Databases - Programming | Trackback | Comments [RSS 2.0]

4 Responses to “The Database Upgrade Problem” »»

  1. Ben Sizer
    Comment by Ben Sizer | 04/09/08 at 8:25 am

    The solution I’ve been thinking about is to scrap the generation of the database from an external definition, and instead use the database as the definitive source. This way you sidestep the problem of telling the difference between a renamed column and a replaced one. Most DBs provide a Comment field for each column that you can use if you need to specify additional metadata that specifies how a column would be translated to source code.

  2. Jeff
    Comment by Jeff | 04/09/08 at 8:40 am

    Hey Ben,

    I have actually done things that way in the past, generating my business objects from the data instead of the other way around. Unfortunately, I’m stuck working the other way simply because of the type of clients I’m working with (game developers) and what we want to actually want to do. Because of that, I have to have a generated database, generated “business logic” and generated stored procedures. It’s unfortunately unavoidable given the task.

    But, yes, if I were working on a web site or business site, I would go (and have gone) the other direction, Unfortunately, I’m still forced to generate my database, so the problem still exists.

  3. Ben Sizer
    Comment by Ben Sizer | 04/11/08 at 11:11 am

    Jeff,

    I’m an MMO developer, and currently we use a home-rolled tool to generate source code and SQL schema from a common definition for our game. The more I’ve used it, the more I’m convinced that not only is this the wrong thing to do, but also that there’s no reason why it should be done this way and not just be done from the database. After all, if that is the authoritative state of the data, it should form the authoritative format of the data too. It also just gives you yet another thing that can potentially get out of sync.

    I think in our case we ended up with this tool due to a distrust of relational databases and a belief that writing a row should be as transparent and as SQL-free as possible, for the benefit of those that don’t know databases. Making the internal tool the authority on both source code and SQL schema allows this, but severely hinders you in other ways, such as schema migration as you know. Rearranging the work flow to work ‘natively’ in SQL just seems to be the more reasonable thing to do.

    One idea, given the constraint we work under, is to have the generation tool demand that there is only ever 1 column change per version. It can compare the new XML with the current DB state and look for the changed, added, or deleted column. If a column in the DB has been replaced by one in the XML, the tool can safely assume it’s renamed, and can preserve the data. To completely replace a column would require 2 commits - one to remove the column, then one to add it. A diff can never really tell the difference between a replacement and a renaming without some external context, so a system like this bypasses that problem. It’s a bit more hassle in the replacement case, but in my experience that’s actually a more rare requirement.

  4. Jeff
    Comment by Jeff | 04/11/08 at 11:45 am

    Hey Ben,

    Sorry for misunderstanding where you’re coming from. I’ve found that the types of people that read my blog are pretty varied, so I couldn’t take for granted you were an MMO developer.

    You are absolutely right about using the database as the fixed definition. I think it’s great for most circumstances so long as you’re looking to specify what your data looks like and have that dictate your code. So long as your code generator can understand (say) 90% of what can happen in a database (constraints, nulls, FKs, and lookup tables) than this is the way to go. If you’re working from a fixed set of features (or want to supply features that can’t easilly be specified in a DB schema) this become tricky.

    For us, we’re not as interested in trying to create a data definition and go from there. What we’re looking at a system where we have a very strict set of rules for what your data looks like and what the stored procedures look like. We have specific sets of features that aren’t nested to the way the data looks, but the way the data behaves, and we’d rather keep that as detached from the data as possible. Additionally, we’re working cross platform, and it’s easier to go from a definition to multiple databases than read different types of db schemas and figure out the proper code to generate (though there are some great ORM libraries that do it). In general, I can’t see how Orbus can go from database to code for our particular tool. It just doesn’t strike me as an option. That said, I may be missing something.

    I will say that there are actually other solutions I’ve thought up for this problem that bypass the diff solution or that can supplement one. The first actually works well if you have a tool that you’re using to edit your data definitions. The idea is to supply “diff hints” as part of the upgraded schema, adding hints like “Rename”, “MovedFrom” etc that will show up in the diff, but who’s only meaning is to supply information about how the schema has changed. When you’re editing the schema directly (which is the case with our tool currently), this isn’t really an option, but we’re working from a much more constrained set of features so I may actually be able to get renames and moves to work pretty well (I’m writing up most of the unit tests for it now)

    The second solution is to provide an alter system yourself, where you do what might be considered a migration script. You save each iteration and the final resulting schema. Unfortunately that gets really unruly really quickly.

Leave a Reply »»

Get Firefox!

Jeff On Games is proudly powered by WordPress & equiX.
Code is Poetry | CSS | XHTML | dsgn by: Marcos Sader