Saturday 12 November 2011

Automated DB deployment

Automation is one of the key stones in the build and deploy process.
Taking a closer look at dbdeploy (http://dbdeploy.com/) shows us how to automate the database changes.

To illustrate a structured approach using dbdeploy, I have taken the example/build.xml and rewritten it to show how it can be used in an enterprise environment.


Step 1 - Download dbdeploy
Download package from http://code.google.com/p/dbdeploy/downloads/list
Unzip and put build-sysadmin.xml from the bottom of this post in the example directory.


Step 2 - Prepare the database
In the downloaded package, apply the createSchemaVersionTable.<DB>.sql script to the database schemas for which dbdeploy will be used.
This will create the changelog table which dbdeploy uses to keep track of which changes have been applied.

Using the build-sysadmin.xml, execute:
ant -f build-sysadmin.xml init


Step 3 - Setup source repository for deltas
In the source repository, create a new folder where the deltas will be stored.
For each database change, create a new file in sequence, e.g.:
001_create_test_table.sql
002_insert_addresses_into_test_table.sql
003_....

Prepare for using build-sysadmin.xml, create example/TESTDB directory and copy example/*.sql into this directory.


Step 4 - Build script
A common practise is to create artifacts during the build process which are then promoted to the different environments. This script could be as simple as a Ant zip task packaging all files in the delta folder.

Using the build-sysadmin.xml, execute:
ant -f build-sysadmin.xml build
This will create the artifact dist/dbdeploy-artifact.zip


Step 5 - Deploy script
A good approach is to use "update-database-and-apply-as-separate-step" in example/build.xml as a template. This twostep approach gives more control over the changes that have been made in the database.
The build-sysadmin.xml makes use of this, print change and the rollback script before executing them.

Using the build-sysadmin.xml, execute:
ant -f build-sysadmin.xml deploy



More information
See http://code.google.com/p/dbdeploy/w/list for more details on how to use dbdeploy.

build-sysadmin.xml:
<?xml version="1.0" encoding="UTF-8"?>

<project name="dbdeploy_modified_example" default="build">

    <property name="db.driver" value="org.hsqldb.jdbcDriver" />
    <property name="db.url" value="jdbc:hsqldb:file:db/testdb;shutdown=true" />
    <property name="db.user" value="sa" />
    <property name="db.password" value="" />
    <property name="dir.delta" value="TESTDB" />
    <property name="dist" value="dist" />
    <property name="work" value="work" />

    <path id="hsql.classpath">
        <fileset dir=".">
            <include name="hsqldb*.jar"/>
        </fileset>
    </path>

    <path id="dbdeploy.classpath">
        <!-- include the dbdeploy-ant jar -->
        <fileset dir="..">
            <include name="dbdeploy-ant-*.jar"/>
        </fileset>

        <!-- the dbdeploy task also needs the database driver jar on the classpath -->
        <path refid="hsql.classpath" />

    </path>

    <taskdef name="dbdeploy" classname="com.dbdeploy.AntTarget" classpathref="dbdeploy.classpath"/>

    <target name="build" depends="-clean-build-artifact, -build-artifact"/>
    
    <target name="deploy" depends="-clean-deploy-artifact, -deploy-artifact"/>
    
    <target name="init" depends="-clean-build-artifact, -clean-deploy-artifact, drop-and-create-database, create-changelog-table"/>

    <target name="create-changelog-table">
        <sql driver="${db.driver}" url="${db.url}"
             userid="sa" password="" classpathref="hsql.classpath" >
            <fileset file="../scripts/createSchemaVersionTable.hsql.sql"/>
        </sql>
    </target>

    <target name="-clean-build-artifact" description="Cleanup old artifact">
        <delete dir="${dist}"/>
    </target>
    
    <target name="-build-artifact" description="Build an artifact with database changes">
        <mkdir dir="${dist}"/>
        
        <!-- Create artifact with all deltas -->
        <zip destfile="${dist}/dbdeploy-artifact.zip"
            basedir="${dir.delta}"
            includes="*.sql"
        />
    </target>
    
    <target name="-clean-deploy-artifact" description="">
        <delete dir="${work}"/>
    </target>
    
    <target name="-deploy-artifact" description="generate a sql upgrade script">
        <mkdir dir="${work}/deltas"/>
        
        <!-- Unzip deltas to be used for deployment -->
        <unzip src="${dist}/dbdeploy-artifact.zip" dest="${work}/deltas"/>
        
        <!-- use dbdeploy to generate the change script -->
        <dbdeploy driver="${db.driver}" url="${db.url}"
                  userid="${db.user}"
                  password="${db.password}"
                  dir="${work}/deltas"
                  outputfile="${work}/output.sql"
                  undoOutputfile="${work}/undo.sql"
                  dbms="hsql"
                />

        <!-- Print actions to be taken for auditing purposes -->
        <echo message="Executing dbdeploy agains ${db.url} as ${db.user}:"/>
        <loadfile property="outputsql" srcFile="${work}/output.sql"/>
        <echo>${outputsql}</echo>
        
        <!-- Print undo actions for rollback purposes -->
        <echo message="Undo:"/>
        <loadfile property="undosql" srcFile="${work}/undo.sql"/>
        <echo>${undosql}</echo>
        
        <!-- now apply the changescript to the database -->
        <sql driver="${db.driver}" url="${db.url}"
             userid="sa" password="" classpathref="hsql.classpath">
            <fileset file="${work}/output.sql"/>
        </sql>
    </target>

    <target name="drop-and-create-database">
        <delete dir="db"/>
        <mkdir dir="db"/>
    </target>
</project>

1 comment:

  1. Thank you for sharing your experience.
    Our system administrator had troubles with this case. I will share the post with him. By the way, we started reading data room providers comparison after his failing in order to store our DB there. Your approach might prevent us from doing that.

    ReplyDelete