Version control database schema Part II

Tags: Ant, Version Control, SQL Compare, Coldfusion, Database

In a previous post I showed how I use Red-Gate SQL Compare from Red-Gate to create SQL Scripts from my database, and today I will now show how you can then run these in an Ant script to recreate your database.

So now that we can export or switch a project out of Subversion, how do we now recreate our database if we need to from our script files?

Before I go into to much detail on how this is achieved, the first thing that I wish to point out is that I follow a directory structure similar to the image below, which allows me to control my project via my ant script.

eclipse_projectThe build directory is where the build.xml file is kept, and the directory that all the SQL scripts will be created are stored in the so named directory. The build file is not complicated and I have created a few variables that can be changed depending on the project, normally I would setup a properties file so that the build file can be copied around if need be. So lets look at the first part of the build.xml file.

<property name="mssql.directory" value="${basedir}\SQL Scripts\Tables" />
<property name="mssql.server" value="localhost" />
<property name="mssql.port" value="1433" />
<property name="mssql.database" value="DatabaseName" />
<property name="mssql.username" value="username" />
<property name="mssql.password" value="password" />

As you can see it is fairly straight forward and there is nothing special here, now lets look at the target that does the job of doing what we need it to do.

<target name="build-database">
   <sql driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
      url="jdbc:sqlserver://${mssql.server}:${mssql.port};databaseName=${mssql.database}"
      userid="${mssql.username}"
      password="${mssql.password}"
      delimiter="GO">
      <classpath>
    <pathelement path="lib/sqljdbc4.jar"/>   
   </classpath>
      <transaction>
         EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"
      </transaction>
      <fileset dir="${mssql.directory}">
         <include name="*.sql" />
      </fileset>
   </sql>
</target>

The first thing to note in the above target is that we have setup the SQL task using our properties defined earlier, and we have then used the classpath element to tell us where to find the JDBC drive. As you will have noted above in the image I have a lib directory where I store the lib's I need for my project, so you will need to download the MS SQL JDBC driver and drop it into this directory.

The next part of the target script tells us that we are now going to make sure that all tables are dropped from the database, this is important as currently SQL Compare doesn't include any script code to check if the table exists and drop the table.

And finally the last bit loads all our scripts to be executed via our target script, and creates all the tables for us. And that is pretty much it, hope you have enjoyed this little snippet.


TweetBacks


Comments (Moderated) | Add Comment

  • Andrew Scott's Gravatar It should be noted that due to a bug in the blog software, the code in the fileset is missing a critical step. And it should have this in between the tags

    include name="*.sql"
    # Posted By Andrew Scott | 4/2/09 7:10 AM

Comments (Moderated) | Add Comment