The Database Patch Runner

Over here Wilfred van der Deijl has raised the time old question when dealing with databases.

How do you easily upgrade a database from an arbitrary version to a new particular version with the smallest amount of effort.

I responded with a brief description here , which caused Amihay Gohen to ask a question or two over E-mail. I thought I'd put together something a little more coherent and detailed here, rather than just send it on to Amihay directly.

This is the basic solution...

Whenever a developer / pair of developers need to change the database they write a patch. This patch contains any alter tables / data migrations / etc. A single file contains the full set of changes for a single conceptual change and may cover changes to several tables.

The patches are written in PL/SQL procedures. That is, each single set of changes is implemented in a single procedure. Each DDL command is issued in an EXECUTE IMMEDIATE, or similar, as is any DML that references a table or column that does not yet exist. The patch is written in a procedure so that we can use the Oracle exception handler to propagate fatal errors in the code to a controlling procedure, something that is far more difficult to do in a script.

These patch procedures are then installed by a "patch runner". This runner is guided by a .txt list of patches that is created by the developers. If a developer wants to add a patch then they write the procedure and add it to the patch list. The runner then uses this patch list to tell it which patches need to be installed, and in what order. The runner is written in PL/SQL, using UTL_FILE and DBMS_SQL to install the procedures.

The runner logs when a patch starts, in a table, and marks the completion or failure of that patch when it finishes. By looking in the patch log before it runs a patch, the patch runner ensures that it never attempts to run a patch that has previously been marked as complete.

In addition, if any patch fails then the patch runner aborts, stating that it failed. "Crash don’t trash!". If the patch runner is restarted then it will attempt to re-run any patches that previously failed. This makes it easier to restart a failed upgrade if ever required. You reverse out the changes already made by the failed patch and then rerun the patch runner.

The runner itself is installed in a single PL/SQL script, and we make sure that it installs all the components it needs before it starts, and tears itself down once its finished. It does not rely on any other part of the database or application in order to work. Since there are no dependencies outside of the patch runner install script, we can use a single patch runner for all of our database based applications, with no changes.

The installation and upgrade of the patch_log table is dealt with in the more traditional manner of a single CREATE TABLE at the start followed by ALTER TABLE statements as it changes over time. The patch log is not destroyed down with the runner.

So, in summary, you check out a given tagged version of the application to run against an arbitrary database and run the patch runner. It loads the list of patches that are applicable for that tagged version. It runs over each patch in turn and checks if it has previously ran. If it has not, then it runs the patch.
By the time it reaches the end of the list it has ran all the patches, none of them twice. You can even run the patch runner twice in succession and guarantee that the second run will not change the state of the database.

The key to that is then to make sure that you use the build often. Get your developers their own database workspaces and make sure that that rebuild and upgrade them often. By repeating the upgrades often you ensure that they basically work before they get moved into a test environment with the bigger worry of large volumes of data.

The patch runner is, of course, only a small part of the database build, and a smaller part of the whole application build. So the runner needs to put into the context of a larger build script. We use batch files here in order to manage our database build process, but I see no reason why other, for complete scripting languages shouldn't be used, or ANT. The runner is, after all, just a PL/SQL script.

As an aside, we also write tests for the patches, checking the pre and post conditions are valid… but that’s a very different topic!

Technorati Tags: Oracle, software, development, database, upgrade, blog, agile, patch, Robert+Baillie, Wilfred+van+der+Deijl, Amihay+Gohen

Oracle Firefox

Whilst it doesn't give you anything that a bookmark doesn't give you anyway, that little search box next to the address bar still feels pretty handy.

And if you're an Oracle developer, then tahiti.oracle.com is pretty handy too.

Well, someone out there has put the two together here.

Not sure I'll use it that often, but it's worth a look!

Update: 25/01/06
Turns out I use this absolutely loads. If you haven't installed it yet... get it on!

Technorati Tags: Oracle, Firefox, extension, oracle+documentation, Mozilla, Robert+Baillie, Eddie+Awad

Some screenshots of the Haloscan Ping Firefox Extension

The configuration screen...


And the Ping Screen...


Technorati Tags: Robert+Baillie,Andrew+Beacock Firefox, extensions, Haloscan, trackback, Mozilla, browser, internet, web, blog, software, tech, technology

Trackbacks are getting easier

Ever since I was introduced to trackbacks a couple of months ago I've had a worry about them... they're difficult to setup. So I thought I'd do something about it.

After a bit of help from my good friend Andrew Beacock, I got my head around trackbacks and set up a Haloscan account so I could get them installed onto my own blog. Getting it up and running was pretty straight forward, but then the tricky bit arrived... actually performing a trackback ping request.

In order to make a request you need to go to the Haloscan account and enter the trackback ping URL for the post that you want to ping. You then need to enter the details of your own blog... the blog title, post link, title and extract. It all seemed like a lot of hard work to me.

So here's what I did:
I wrote a Firefox extension to send trackback ping requests to Haloscan

In order to use it you need to set it up:

1. Install the extension


2. Configure it with the address of your blog feed (rss and atom are supported). You'll find it under Tools -> Haloscan Ping
   
3. Log into Haloscan

In order to send a ping request:

1. Write your blog entry and publish it


2. Go to the blog you want to ping, and select the trackback URL


3. Right-click and pick Ping Haloscan


4. Pick the entry from your blog to ping with and click OK

Simple as that!

At the moment I'd regard the extension as being at alpha stage, so for now I'm sending it out on request. If you'd like to try it out then mail me and I'll send you a copy.
Once I've got some positive feedback for it I'll try to publish it on Mozilla Update and Mozdev.

Update:You can mail me here...

Technorati Tags: Robert+Baillie,Andrew+Beacock Firefox, extensions, Haloscan, trackback, Mozilla, browser, internet, web, blog, software, tech, technology

Index Cards

In our version of XP, Stories get written on index cards.

Text on the front, in big marker pen describes the user action in a single sentence.
The aim is to get the people involved to think in small chunks, and to think about the fundamentals of each piece of work.

Text on the back, in biro describes any particular behaviour required, we call them the acceptance criteria. The aim is to get people to think about the exceptional requirements of a story, the bits that the simple view of the action doesn't cover, to think of the little gotchas that'll appear.

They work extremely well, focussing the mind on the job in hand.

But there's something I think we've missed the point of: For each story there is only one card.

Here are my thoughts...

Since there's only one card, the card needs to be with the people that are currently working on it. If the story's still being worked out, it's with the customer, if it's being developed then it's with the pair working on it.

That means that if the customer wants to change their mind after the story's been started by the development team then they need to find the pair that's currently working on it and speak to them.
Fair enough, we could have the stories stored electronically and then put a system in place to ensure that the pair are notified of any change as soon as its put onto the system. It wouldn't be difficult, the customer enters each story on the system, revising it until they think it's ready. The story gets marked as ready to start when they've had a discussion with the development team. Each pair would register which story they're working on, marking the story as in progress. And so, and so on.

Or, we could do the simple thing and use natural capabilities of the card; do the simplest thing that will work.

Simplicity is only part of the issue. The big advantage of actually tracking down the pair and speaking to them is that you then get into a conversation. A notification is one way, a conversation is two way. And things that are written down are open to interpretation.

Conversation is crucial to the success of XP, and keeping the story on the card just seems to me to be a great way of keeping that conversation going.


Technorati Tags: Robert+Baillie, Extreme+Programming, user+stories