JDBC-Script is an Ant task that helps to set up complex databases. The database commands are written in an XML file and executed via JDBC. This helps to execute them in the right sequence and have an automatic failure detection.
I'm working on the web page and the documentation, be patient.
JDBC-Script is a plugin for ant 1.6 and requires Java 5 (1.5.x). To be useful you need a database with a JDBC driver.
ToDo:
Modify the build script to create a bundle (jar file).
Check the copyright of JAXB if it is possible to put it into the jar file.
Create some example scripts.
Fix the XML schema to use jdbcscript.sf.net.
Create a documentation for the ant task, howto use it.
JDBC-Script processes multiple XML files and executes the JDBC statements in them. The Ant task makes it easy to split the whole work into manageable chunks. It is possible to create modules for certain business components.
Creating a complex database normally means creating a user or schema, the tables, the constraints, some views or synonyms, stored procedures, types and so on. And at the development phase you regularly replay the operation for different development databases and to test the scripts or to update existing databases. Updating is extremely complex and often done by dropping all existing objects and recreating them. Creating a drop statement for every create statement is a manageable and practical solution. But the worst situation is the dependency between different statements, e.g. types. If type X has to be compiled before type A you can not use a simple scripting solution based on statement type, you have to store the sequence of the statements.
JDBC-Script stores a sequence number for each statement. This makes it possible to create an Ant target that processes only a subset of all statements.
<target name="create-database">
<jdbcscript connect="jdbc:derby:TestDB;create=true"
driverClass="org.apache.derby.jdbc.EmbeddedDriver"
sequence="1..99" >
<fileset dir="database" >
<include name="**/*.xml"/>
</fileset>
</jdbcscript>
</target>The above target would process all statements with a sequence number between 1 and 99. The XML file might contain a create statement with a sequence number of 10 and a drop statement with the sequence number of -10. The create and drop statements are close together in one file which helps to maintain them and you have only one sequence if you use positive and negative numbers. If you want to drop everything in the right sequence you can create a target processing all statements with a sequence number between -99 and -1. A build script might look like the following:
<?xml version="1.0" encoding="UTF-8"?>
<jdbc-script xmlns='http://jdbcscript.sourceforge.net/script'
xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
xsi:schemaLocation='http://jdbcscript.sourceforge.net/script file:/home/haug/jdbcscript/resource/jdbc-script.xsd'>
<statement sequence="10" >
CREATE TABLE COFFEES (COF_NAME VARCHAR(32), SUP_ID INTEGER, PRICE FLOAT, SALES INTEGER, TOTAL INTEGER)
</statement>
<statement sequence="-10" >
DROP TABLE COFFEES
</statement>
</jdbc-script>If you create a script you should define a sequence number e.g. for type creation. And do not use 1,2,3,4 because if you have the case of dependencies between types you have to resequence all statements. Its better to use 100,200,300,400, use the range possible.
The Ant task has two more options to set, validateFiles and failonerror. ValidateFiles switches the XML schema validation on or of. Failonerror controls if the build process continues after an error or not.
Use multiple threads for each sequence number.
This seems useful e.g. for drop scripts to avoid errors if the object is already deleted by a constraint. Experience has shown that dropping objects could be difficult with scripts, hopefully JDBC-Script is easier.
Writing SAX parsers isn't really fun, I decided to give JAXB a try. JAXB from the Java Web Service Development Pack 1.6 is easy to use and does the XML reading know. JAXB uses an XML schema to generate the more than 30 classes to read the XML file and create an object model. This reduced the whole source to less than 600 lines for my first implementation.