Sheila's Blog

When you’re developing a project, you put all your java code into source control.  You should also include database scripts in source control.  These scripts can be used by developers to keep their local copies of the database up to date in the same way as they get the latest code from source control.

As part of your automated build system you should run the database scripts whenever there’s a change to keep your code and database in synch.  You can add the code into your existing build in a db folder or check it into source control separately.

You follow almost the same process whether adding scripts for mysql or oraclexe and can modify the scripts to cater for multiple schemas across different databases.   In this example we’ll cover the configuration for setting up the testdb schema in both oraclexe and mysql before writing the scripts for each. This example assumes you already have both databases running locally on your machine.

To start with you create a PROJECT_HOME/db folder which contains: db/config db/datasources db/lib db/oraclexe/testdb db/mysql/testdb

In the lib folder add the connector jars for the databases eg. db/lib/oracle/ojdbc5.jar db/lib/mysql/mysql-connector-java-5.1.6-bin.jar

In the config folder we need some property files to configure the build. db/config/mysql.properties db/config/xe.properties db/config/schema.properties

Schema.properties is used to configure the build depending on whether you want to just use oracle xe or mysql or use both.  In this example oraclexe is enabled and mysql is disabled.

schema.properties: ## ## Use this property file to enable or disable the databases and ## schemas you want to run the build scripts against. ## Comment them out with a # if you don't want to enable a ## setting. #Databases xe.db.enable xe.username=testdb xe.password=testdb #mysql.db.enable #mysql.username=testdb #mysql.password=testdb

The next two give the access details for your oracle xe and mysql databases.

mysql.properties: # MySql Database Details mysql.rootuser=root mysql.rootpass=password mysql.url=jdbc:mysql://localhost:3306 mysql.schema=testdb mysql.driver=com.mysql.jdbc.Driver mysql.user.location=localhost mysql.create.tables.script=createTables.sql mysql.drop.tables.script=dropTables.sql mysql.populate.tables.script=populateTables.sql mysql.testdata.script=testData.sql print=false

xe.properties: # Oracle XE Database Details xe.rootuser=sys xe.host=localhost xe.sid=XE xe.rootpass=password xe.port=1521 xe.driver=oracle.jdbc.OracleDriver xe.url=jdbc:oracle:thin:@${xe.host}:${xe.port}:${xe.sid} xe.create.user.script=createUser.sql xe.delete.user.script=dropUser.sql xe.create.tables.script=createTables.sql xe.drop.tables.script=dropTables.sql xe.populate.tables.script=populateTables.sql xe.testdata.script=testdata.sql print=false

We start off with a master build.xml script in PROJECT_HOME which calls oraclexe/testdb/build-xe-testdb.xml and mysql/testdb/build-mysql-testdb.xml script.  The ‘ant dir’ value specifies the directory the script to be called in, ‘antfile’ gives the name of the script, and lastly you specify the target to be run in that script.

db/build.xml: <?xml version="1.0"?> <project basedir="." name="db-master-build" default="recreateTables"> <property name="build.dir" value="${basedir}/build" /> <target name="clean"> <delete dir="${build.dir}" failonerror="false" /> </target> <target name="install" depends="createUsers,createTables,populateTables,testData" description="Creates all users and schemas for a fresh install" /> <target name="recreateTables" description="Refreshes the database tables for all schemas"> <ant dir="oraclexe/testdb" antfile="build-xe-testdb.xml" target="recreateTables" /> <ant dir="mysql/testdb" antfile="build-mysql-testdb.xml" target="recreateTables" /> </target> <target name="createUsers" description="Creates users for all schemas"> <ant dir="oraclexe/testdb" antfile="build-xe-testdb.xml" target="createUser" /> <ant dir="mysql/testdb" antfile="build-mysql-testdb.xml" target="createUser" /> </target> <target name="dropUsers" description="Drops users for all schemas"> <ant dir="oraclexe/testdb" antfile="build-xe-testdb.xml" target="dropUser" /> <ant dir="mysql/testdb" antfile="build-mysql-testdb.xml" target="dropUser" /> </target> <target name="createTables" description="Creates Database Tables for all schemas"> <ant dir="oraclexe/testdb" antfile="build-xe-testdb.xml" target="createTables" /> <ant dir="mysql/testdb" antfile="build-mysql-testdb.xml" target="createTables" /> </target> <target name="populateTables" description="Populates Database Tables for all schemas"> <ant dir="oraclexe/testdb" antfile="build-xe-testdb.xml" target="populateTables" /> <ant dir="mysql/testdb" antfile="build-mysql-testdb.xml" target="populateTables" /> </target> <target name="dropTables" description="Drops Database Tables for all schemas"> <ant dir="oraclexe/testdb" antfile="build-xe-testdb.xml" target="dropTables" /> <ant dir="mysql/testdb" antfile="build-mysql-testdb.xml" target="dropTables" /> </target> <target name="testData" description="Refreshes test data for all schemas"> <ant dir="oraclexe/testdb" antfile="build-xe-testdb.xml" target="testData" /> <ant dir="mysql/testdb" antfile="build-mysql-testdb.xml" target="testData" /> </target> </project>

The install target is used to create the database schemas from scratch – including the users.

The recreateTables target is the default and is used when the schemas and users already exist and you just want to drop and create the tables to pick up changes.

The rest of the targets can be called separately for creating tables, populating them with default data (eg. lists of countries that would always be needed for an application) adding some test data and deleting the tables again.

The next step is to add the oraclexe-specific scripts and the mysql-specific scripts.

Tags:   automated build · database scriptsNo Comments