Schemamule operates through a single ant task: schemacopy.
The first thing you'll need to do is make the task available from within your build script. For this you use the typedef ant task:
<typedef resource="edu/northwestern/bioinformatics/schemamule/antlib.xml"
uri="http://bioinformatics.northwestern.edu/schemamule"/>
Depending on where you are keeping the Schemamule jar, you may also need to specify the classpath on which it can be found.
The simplest invocation of schemacopy looks like this:
<schemacopy xmlns="http://bioinformatics.northwestern.edu/schemamule">
<to uri="jdbc:hsqldb:file:${basedir}/hsqldb/mydb" username="sa" password=""/>
<from uri="jdbc:oracle:thin:mydb" username="scott" password="tiger"/>
</schemacopy>
This will copy all the tables and views from the Oracle database "mydb" into a filesystem-based HSQL database in the hsqldb directory. That HSQL database can then be loaded with data using dbunit.
If you only want to include certain tables in your target database, Schemamule can accommodate you. Say mydb includes three tables: TABLE1, TABLE2, and TABLE3. If you only want to copy 1 & 3, you can:
<schemacopy xmlns="http://bioinformatics.northwestern.edu/schemamule">
<to uri="jdbc:hsqldb:file:${basedir}/hsqldb/mydb" username="sa" password=""/>
<from uri="jdbc:oracle:thin:mydb" username="scott" password="tiger">
<table name="TABLE1"/>
<table name="TABLE3"/>
</from>
</schemacopy>
Another way to say the same thing is:
<schemacopy xmlns="http://bioinformatics.northwestern.edu/schemamule">
<to uri="jdbc:hsqldb:file:${basedir}/hsqldb/mydb" username="sa" password=""/>
<from uri="jdbc:oracle:thin:mydb" username="scott" password="tiger">
<table name="TABLE2" exclude="true"/>
</from>
</schemacopy>
If you combine includes and excludes, the excludes are ignored.
By default, views in the source database are copied as views to the target
database. This works well unless there are syntax differences between the
SQL supported by the source and target databases. In that case, you can use
the to element's viewsAsTables option:
<schemacopy xmlns="http://bioinformatics.northwestern.edu/schemamule">
<to uri="jdbc:hsqldb:file:${basedir}/hsqldb/mydb"
viewsAsTables="true"
username="sa" password=""/>
<from uri="jdbc:oracle:thin:mydb" username="scott" password="tiger"/>
</schemacopy>
This option behaves just like it sounds -- it copies views in the source into tables in the target. This means you'll explicitly have to provide data for any views your tests use.
(It could be argued that this is a more pure unit-testing approach as well -- you are testing your code's response to the views instead of the views themselves.)