ANT Builds and MS SQL - Run .sql scripts in your build.

Yet another great ANT integration feature, executing .sql scripts. One of the things I like to do during development is keep a folder of all t-sql scripts needed to get my application up and running. These normally include creating tables, views, stored procs, and indexes as well as scripts that insert default records such as lookup values. I make sure the scripts can be run on a brand new installation as well as for updating an existing one (Listing 1.1).

Listing 1
/************************************************************
/ Script: Person Table /
/ Purpose: Creates Person Table /
/ Script Date: 02/07/2007 /
/ By: Phill Nacelli - Software Architect /
/************************************************************

-- if table does not exist create it.
IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = N'Person' AND type = 'U')
BEGIN

CREATE TABLE Person (
personID int IDENTITY (1, 1) NOT NULL ,
firstName varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
middleName varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
lastName varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
dateOfBirth datetime NULL ,
suffix varchar (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT PK_Person PRIMARY KEY CLUSTERED
(
personID
) ON PRIMARY
) ON PRIMARY

END
GO

-- insert new suffix column if does not exist
IF NOT EXISTS (SELECT sc.name FROM syscolumns sc INNER JOIN sysobjects so ON sc.id = so.id WHERE so.name = 'Person'

AND so.type = 'U' AND sc.name = 'suffix')
BEGIN

ALTER TABLE Person
ADD suffix varchar(30) NULL

END

-- insert new prefix column if does not exist
IF NOT EXISTS (SELECT sc.name FROM syscolumns sc INNER JOIN sysobjects so ON sc.id = so.id WHERE so.name = 'Person'

AND so.type = 'U' AND sc.name = 'prefix')
BEGIN

ALTER TABLE Person
ADD prefix varchar(30) NULL

END

However, running multiple t-sql scripts at a time is not a lot of fun, and that's when ANT comes in. It allows you to run .sql scripts using jdbc drivers directly into your database. Here's a step by step how:

1. Make sure you have your database's JDBC .jar file. These can be found either on the database manufacture's website or via online. Sun's Developer Network (SDN) has made this even easier by creating a JDBC search engine here.

2. In your build file, create the following properties to link to your database and .sql script files (Listing 1.2):

Listing 1.2 - build.xml
<!-- directory where db scritps reside -->
<property name="sql.scriptPath" value="D:\\inetpub\\aLogix\\database" />
<!-- db server name -->
<property name="sql.serverName" value="MSSQL_DevBox" />
<!-- db name -->
<property name="sql.dbName" value="aLogix" />
<!-- jdbc url -->
<property name="sql.jdbcURL" value="jdbc:sqlserver://${sql.serverName};databasename=${sql.dbName}" />
<!-- jdbc path -->
<property name="sql.jdbcPath" value="C:\jdbcDrivers\microsoft-jdbc-2005\lib\sqljdbc.jar" />
<!-- jdbc classpath -->
<property name="sql.jdbcClasspath" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />

3. Then simply create the target implementing the sql task tag to run your scripts. Here I have created two targets, the first runs all my sql scripts, the other prompts me which .sql file I want to run (Listing 1.3):

Listing 1.3 - build.xml
<target name="sql.getLogin">
<input message="Please enter sql server username:" addproperty="sql.username" />
<input message="Please enter sql server password:" addproperty="sql.password" />
</target>

<target name="sql.runAll" depends="sql.getLogin">
<sql driver="${sql.jdbcClasspath}" url="${sql.jdbcURL}" userid="${sql.username}" password="${sql.password}">
<classpath>
<pathelement path="${sql.jdbcPath}" />
</classpath>
<transaction src="${sql.scriptPath}\tables\Person.sql" />
<transaction src="${sql.scriptPath}\tables\Company.sql" />
<transaction src="${sql.scriptPath}\tables\Address.sql" />
<transaction src="${sql.scriptPath}\tables\Country.sql" />
<transaction src="${sql.scriptPath}\tables\StateProvince.sql" />
<transaction src="${sql.scriptPath}\lookupData\Country.sql" />
<transaction src="${sql.scriptPath}\lookupData\StateProvince.sql" />
</sql>
</target>

<target name="sql.runByFileName" depends="sql.getLogin">
<input message="Please enter sql script name to be executed:" addproperty="sql.filename" />
<sql driver="${sql.jdbcClasspath}" url="${sql.jdbcURL}" userid="${sql.username}" password="${sql.password}">
<classpath>
<pathelement path="${sql.jdbcClassPath}" />
</classpath>
<transaction src="${sql.scriptPath}\tables\${sql.filename}.sql" />
</sql>
</target>

That's it! Here's another simple way how ANT can make a developer's life a lot easier. Hopefully this example can be of some use on your toolbox as it has been for me.

ANT Builds and Subversion (SVN)

As I have mentioned in a previous blog entry, I have come to love using ANT in my development environment. One of the things that I like about it is how well it integrates with my Subversion repository using SVNAnt. If you are not using either ANT or Subversion, you owe it to yourself and your team to check it out.

Here we'll go over a simple build script that exports application files from the Subversion repository into a local folder in the CFEclipse project. You can then view the following entry on how to ftp that into your production/staging server.

First we need to make sure that your ANT install has the necessary SVNAnt jar files. You can download them here: http://subclipse.tigris.org/svnant.html. Once you unpack the svnant-1.0.0.zip file, you'll find threee jar files: svnant.jar, svnClientAdapter.jar, svnjavahl.jar. Place them on your [ANTInstall]/lib* folder.

Once you have the jar files in place, you can define them in your build file's property section like so:

<!-- svnant lib -->
<property name="svnant.lib" value="lib" />
<property name="svnant.jar" value="${svnant.lib}/svnant.jar" />
<property name="svnClientAdapter.jar" value="${svnant.lib}/svnClientAdapter.jar" />
<property name="svnjavahl.jar" value="${svnant.lib}/svnjavahl.jar" />

Followed by this path definition after all your properties have been defined:

<!-- path to the svnant libraries. Usually they will be located in ANT_HOME/lib -->
<path id="project.classpath">
<pathelement location="${svnjavahl.jar}" />
<pathelement location="${svnant.jar}" />
<pathelement location="${svnClientAdapter.jar}" />
</path>

Now all you have left to do is add the following task definition:

<!-- load the svn task -->
<taskdef resource="svntask.properties" classpathref="project.classpath"/>

Now you are all set! You can now call tasks like the following export target:

<!-- define properties -->
<!-- svn repo url -->
<property name="svn.url" value="http://[mysvnhost]/svn/repo/myprojectFiles/" />
<property name="svn.exportPath" value="[yourDirectory]:\\[pathToYourCFEclipseProject\\build" />

<target name="svn.export">
<echo message="Exporting application files from svn repository:" />
<input message="Please enter svn repo username:" addproperty="svn.username" />
<input message="Please enter svn repo password:" addproperty="svn.password" />
<mkdir dir="${svn.destPath}" />
<svn username="${svn.username}" password="${svn.password}">
<export srcUrl="${svn.url}" destPath="${svn.exportPath}" revision="HEAD" />
</svn>
<echo message=" ... finished exporting files." />
</target>

That's it. No more running command line tasks to get your subversion commands. For additional info and other SVN tasks you can go to http://subclipse.tigris.org/svnant/svn.html.

ANT Builds and FTP

The more I play with ANT the more I kick myself for not looking into it earlier. This past weekend I was playing with some different ways to run builds and one of the things I ran into is having ANT ftp files to your different servers as you deploy your application from dev to qa, staging and production.

One of the first things you need to do is add two jar files to your ANT installation's lib folder from Jakarta Commons Net and Jakarta-ORO. They are commons-net.jar and jakarta-oro-2.0.8.jar.

After you have these two files, create a target like the one below named "ftp.deploy" in your build.xml file. See sample below:

<project name="alogix" default="ftp.about" basedir=".">
   
      <!-- set global properties for this build -->
         <!-- hd path to application -->
            <property name="application.dir" value="D:\inetpub\alogix\" />
         <!-- ftp server to move files to -->
            <property name="ftp.server" value="www.alogix.net" />
         <!-- directory in server to move files to (root relative) -->
            <property name="ftp.remotedir" value="/" />
      
      <!-- filesets -->
         <fileset id="fileset.exclude" dir=".">
            <exclude name="*build.xml" />
            <!-- use this if you need to exclude a folder (uncomment line and change folderName) -->
               <!-- <exclude name="**/[folderName]/**" /> -->
            <!-- use this if you need to exclude a file (uncomment line and change filename & extension -->
               <!-- <exclude name="*[filename].[extension]" /> -->
         </fileset>   
         
      <!-- targets -->
         <target name="ftp.about">
            <echo message="Using FTP in ANT example." />
            <echo message="By: Phill Nacelli" />
            <echo message="www.phillnacelli.net" />
         </target>
      
         <target name="ftp.help">
            <echo message="usage: ftp.deploy" />
         </target>   
      
         <target name="ftp.deploy">
            <input message="Please enter ftp server username:" addproperty="ftp.username" />
            <input message="Please enter fpt server password:" addproperty="ftp.password" />
            <ftp action="send" server="${ftp.server}" userid="${ftp.username}" password="${ftp.password}" remotedir="${ftp.remotedir}">
               <fileset refid="fileset.exclude" />
            </ftp>
         </target>
   </project>

Notice that nowhere in the build.xml file you see any username and password written. Instead of storing it on the file as a property we actually prompt the user for it when running the ftp.deploy target. This ensures that you can distribute your build file without compromising security.

If you use any versioning systems, notice that in the fileset.exclude we did not enter anything to stop the system files that they embed in your project (ie. ".svn" folder for Subversion, "vssver.scc" for VSS), that's because they are already excluded by default (see Default Excludes section on this page).

BlogCFC was created by Raymond Camden. This blog is running version 5.003. Powered by ColdFusion Server v8,0,1,195765.