We have been using DBDeploy on my project for more than 6 months now and wanted to show how things are going. First lets talk about set up, we are using dbdeploy in our Java development environment with ANT as our build scripting tool, against a Oracle 10g database.
Define the ANT task first
<taskdef name=“dbdeploy” classname=“net.sf.dbdeploy.AntTarget” classpath=“lib/dbdeploy.jar”/>;
Now we create the main dbinitialize task a ANT task to create you database schema, using the upgrade generated by the dbdeploy file shown below. The thing to note is that dbdeploy generates the upgrade file but does not run it against your database, so we have to make sure we call the generated upgrade file via a sql ANT task.
<target name=“dbinit” depends=“init,dbclean”> <echo message=“Working UserName: ${db.user}”/> <mkdir dir="${migrationfolder}"/> <dbdeploy driver="${driver.name}" url="${db.url}" userid="${db.user}" password="${db.password}" deltaset=“couger” dir=“db/migration” outputfile="${migrationfolder}/upgrade.sql" dbms=“ora” undoOutputfile="${migrationfolder}/undo.sql"/> <!–Now run the generate upgrade file –> <sql password="${db.password}" userid="${db.user}" url="${db.url}" driver="${driver.name}" classpath="${driver.classpath}" onerror=“abort”> <fileset includes=“upgrade.sql” dir="${migrationfolder}"/> </sql> </target> So lets say we want to write the first migration (migration or delta is the same in this context) we will create a new file in the db/migration folder named as 001_CreateCustomerTable.sql the 001 is just a number to sequence the migrations, dbdeploy only cares that the numbers increment and CreateCustomerTable is used to give it a meaningful name, you can name the migration as 1.sql but thats not meaningful is it? neither does the name really say what its doing. When we are done writing the migration and confirm that it works locally, we check in the file (we used subversion). When <a href=“http://cruisecontrol.sourceforge.net/">CruiseControl</a> build was done we also published all the migrations from db/migration folder on CruiseControl artifacts page using the onsuccess event <publishers> <onsuccess> … <artifactspublisher dest=“artifacts/${project.name}” dir=“projects/${project.name}/db/migration”/> </onsuccess> </publishers>
Publishing the migrations allowed us to know what all migrations are needed for this particular build. we have deployed to production twice already and have found this process to be smooth, doing migrations has allowed us to test our migrations hundreds of times and also test them against a copy of the production database before hand so that we can tune the migrations for performance if needed. Delivering the migrations to the client is also easy since its pure SQL that the client DBA’s can look at and be comfortable about the migration/upgrade of their database.