sqlite

Create a SQLite database during an ant build

I have a SQLite database that is used by my application.  It is not the main database but provides some supporting data for the application.  Every time I deploy my application to my local tomcat installation using ant, I want to recreate this database, using a sql script.  I do this by executing a sqlite .read command as one step in my build.

  1. Install sqlite and ensure it is in your path.
  2. Update my Eclipse Ant installation to include the Ant Contrib jars.
    1. I unpacked the jars to my eclipse/plugins/org.apache.ant.XXX/lib directory.
    2. In Eclipse->Window->Preferences->Ant, go to the Runtime section and add the ant-contrib.jar to the classpath.
  3. Within my ant build file I did the following:

<project name="myproject" basedir="." default="clean-deploy">

<taskdef resource="net/sf/antcontrib/antcontrib.properties"/>

   <property file="build.properties"/>
   <property environment="env"/>

   <!-- assumes you have sqlite3 installed on the local machine and in the path -->
   <target name="createsqlitedb" description="build sqlite db">
      <propertyregex property="loc" input="${env.CATALINA_HOME}" regexp="\\" replace="\\\\\\\\\\\\\\\\" />
      <property name="myapp.web.dir" value="${loc}/webapps/${property.projectname}"/>
      <property name="tomcat.conf.dir" value="${loc}/conf"/>

      <echo message="src file for sqlitedb: ${myapp.web.dir}${property.sqlitedb.src}"/>
      <echo message="dest db for sqlitedb: ${tomcat.conf.dir}${property.sqlitedb.dest}" />
      <exec executable = "sqlite3">
          <arg value="${tomcat.conf.dir}${property.sqlitedb.dest}" />
          <arg value=".read ${myapp.web.dir}${property.sqlitedb.src}"/>
      </exec>
   </target>

</project>

The build.properties file has the properties in it such as property.sqlitedb.src and property.sqlitedb.dest.  The file itself has standard sqlite sql statements in it.

Helpful links:

Update 8/2/12 – I had to use the ant-contrib jars because I needed the propertyregex functionality. This can be avoided by defining the properties differently. If the properties are defined to have the full path, such as:
property.sqlitedb.dest=c:/programs/tomcat/conf/mysqlitedb.db
property.sqlite.exe=c:/dir/where/sqliteexe/is/sqlite3.exe
property.mysql.dir=c:/dir/where/mysql/file/is/

Then the ant task can look like this:

<exec executable="${property.propertydb.exe}">
	<arg value="${property.propertydb.loc}"/>
	<arg value=".read ${property.propertydb.sql}${property.sqlitedb.src}"/>
</exec>

Of course properties can be set up however it makes sense. The main point being, if using full paths, no need for propertyregex.

Advertisements