Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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."

share|improve this question
    
Are you familiar with Red-Gate's products? It could be that you're reinventing the wheel. –  Dan Pichelman May 28 '13 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 '13 at 16:58
    
If you're using MS SQL Server, SQL Server Data Tools can also help with this problem. –  alroc May 28 '13 at 17:28
1  
This question doesn't look like an "SQL script update" to me. –  user61852 May 28 '13 at 19:56
1  
Answered: stackoverflow.com/questions/861728/… –  user61852 May 28 '13 at 20:05
show 1 more comment

1 Answer

up vote 7 down vote accepted

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.

share|improve this answer
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 '13 at 20:04
1  
+1. This is actually a pretty standard way of doing it. –  Mason Wheeler May 28 '13 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 '13 at 21:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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