2

I work on a project that uses a SQL database as a back end, and a desktop app as a front end. We do releases on a regular basis, and as part of a release, our client needs to update their SQL server.

Our business requires that we are upgradable from version 1.x to 1.y, and the way we've been doing it so far is by following a simple pattern for dozens/hundreds of tables:

  • IF NOT EXISTS (XXX) CREATE TABLE/VIEW YYY ...
  • ALTER TABLE (XXX) ADD COLUMN (WWW) ...
  • CREATE INDEX (ZZZ) on table XXX (if needed)
  • So on...

The problem I see with this is that the steps laid out above are all laid out in a one-for-one relationship with the table that's being created. This is fine for simpler databases that have no constraints, but becomes a pain when foreign keys/triggers/anything complex becomes involved.

I was wondering if there was a pattern out there that could help me clean this up and make modifications to my script easier. I was thinking something along the lines of following these steps for every table in the database:

  • perform cleanup from any "old" version
  • create/update all tables/columns (without constraints)
  • create/update all views based on tables
  • create/update all foreign keys / constraints
  • create/update all indexes
  • create/update all triggers
  • ...

I use a tool to merge all of the SQL files in my repository into one, but the rhyme/reason to which file gets merged at what point is becoming unclear - often I just give up and hack something in that says "include file X directly before/after file Y". It's becoming unmaintainable (I can still fix problems, but I want to be able to hand off this responsibility).

It's this last annoyance that I want to be able to avoid. I want to be able to create a new table by just dropping a script with the content "CREATE TABLE X..." in a "Tables" directory and call it done.

Are there any patterns out there that can help me? I'm really looking for some sort of ammunition that I can use when I propose this to my colleagues by saying "see - there IS a solution to this cluster* and THIS is how other companies address it."

Improve this question
6
  • Are you familiar with Red-Gate's products? It could be that you're reinventing the wheel.
    – Dan Pichelman
    May 28, 2013 at 16:00
  • 1
    Or if you don't want to pay for redgate, you could use something like RoundHousE to migrate your database.
    – Wyatt Barnett
    May 28, 2013 at 16:58
  • If you're using MS SQL Server, SQL Server Data Tools can also help with this problem.
    – alroc
    May 28, 2013 at 17:28
  • 1
    This question doesn't look like an "SQL script update" to me.
    – Tulains Córdova
    May 28, 2013 at 19:56
  • 1
    Answered: stackoverflow.com/questions/861728/…
    – Tulains Córdova
    May 28, 2013 at 20:05

1 Answer 1

Reset to default
7

This is not originally my idea, but here's a manual way to do it:

  1. Create a "UpdateScripts" directory.
  2. Create a field in the database which tracks the ID# of the last-run script.
  3. Every time any change is made to the database structure, save it as a script in that directory, with a sequential number (such as 00001) in front.
  4. Every time the customer launches, run every script in order between the stored ID and the last script in the directory.
  5. Update the last script field.

There are a number of alternatives, most more automated than this, discussed here.

Improve this answer
3
  • 1
    +1. It may be worthwhile for you to check out how Rails' migrations work. Martin Fowler's Evolutionary Database Design article (martinfowler.com/articles/evodb.html) may also be helpful.
    – Jason Swett
    May 28, 2013 at 20:04
  • 1
    +1. This is actually a pretty standard way of doing it.
    – Mason Wheeler
    May 28, 2013 at 20:54
  • @MasonWheeler - I figured it was, but I've never used it myself. I don't have any databases which need to be versioned this way.
    – Bobson
    May 28, 2013 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.