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>

How to change file type in CVS

When working with CVS there might be times where files get added in the wrong format (binary or ascii). To convert between these two there are a couple of commands you can use.

  • Convert from binary to ascii
    • Using CVSNT
      Remove all files and then execute in a shell:
      cvs update -ktkv <FILES>
      cvs commit -fm "Changed format to ascii" <FILES>
    • Using CVS
      cvs admin -kkv <FILES>
      cvs update -A <FILES>
      cvs commit -fm "Changed format to ascii" <FILES>
  • Convert from ascii to binary
    • Using CVSNT
      Remove all files and then execute in a shell:
      cvs update -kb <FILES>
      cvs commit -fm "Changed format to binary" <FILES>
    • Using CVS
      cvs admin -kb <FILES>
      cvs update -A <FILES>
      cvs commit -fm "Changed format to binary" <FILES>

updateCVSRoot.bat

Ever been in a situation where you needed users to update the CVSROOT of their locally checked out source?
It's not always as straight forward as checking out the code again so here's a batch script to recursively update the CVS\Root file with the new CVSROOT.
Synopsis: updateCVSRoot.bat <WORKSPACEDIR> <CVSROOT>

@echo off
REM This script is used to update CVS Root in checked out directory
REM SYNOPSIS: updateCVSRoot.bat <WORKSPACEDIR> <CVSROOT>

REM Check arguments
if "%2" == "" goto MISSINGARGUMENT

REM Check if workspace directory exist
IF NOT EXIST %1 GOTO NOWORKSPACEDIR


REM Setup variables
SET W_DRIVE=%~d1
SET W_PATH=%~dp1

REM Enter drive and directory for the root of CVS checked out directory
%W_DRIVE%
cd %W_PATH%

REM Find all Root files and replace with CVSROOT
for /f "tokens=*" %%a in ('dir Root /b /s') do (
  echo %2> "%%a"
)


echo Done!
exit /b 0

:MISSINGARGUMENT
printf "Usage:  %~n0 <WORKSPACEDIR> <CVSROOT>\n"
printf "  WORKSPACEDIR               Root directory to search for filenames called Root\n"
printf "  CVSROOT                            Fully qualified CVSROOT\n"
printf "\n"
printf "Example: %~n0 c:\\\\cvsdir :sserver:username@cvs.localdomain:/my/cvsroot\n"
printf "\n"
exit /b 1

:NOWORKSPACEDIR
echo ERROR: %1 doesn't exist
echo.
exit /b 1

Welcome to a sysadmin's blog


After many years in the IT industry it is time to start sharing my knowledge and findings, both to myself and others.
Without over-committing, I will try to keep the blog going with frequent updates.

Enjoy