Wednesday, November 23, 2005
Embedding ANT as automatic DB schema upgrader
Anyone who has developed code with other developers that uses a database can sympathize with this dialog
Joe : "Hey, just checked in some code, you need to run this SQL script to create the new tables."
Mary: "It's not working, my schema already has some of those tables from last time"
Joe: "Drop all those, maybe you better drop entire db to be safe"
Mary: "But I'm in the middle of developing something, dang I shouldn't have updated my code"
sipXconfig went to use ANT to break up these little DB schema upgrades into "patches" that are intelligent enough to know when to run and remember when they've been run before. I'm very happy with this and it actually required ZERO java coding!
Rule was you never edited a patch once it was checked in, you only patched other patches. It was still difficult in early development stages when your portion of the schema changed drastically and you left a long string of patches behind. So I came up with a patch that clears itself and applies itself everytime the application runs.
<target name="drop-firmware" if="firmware">
drop table firmware;
drop sequence firmware_seq;
delete from patch where name = 'firmware';
<target name="firmware" depends="drop-firmware">
sipx-sqlruns a SQL script and
db-patchdoes the same but also marks that the script has been applied before so that next time this ant file is run "if=firmare" will be true. Details are in the
At some point when these tables stablize, I can just change it to this
<target name="firmware" unless="firmware">