Here is the ant script that I was referring to & the properties file that powers it. Also included are the caveats that I gave Janet:
A few caveats: 1. It is targeted specifically to MSSQL. 2. I am using the open source JTDS drivers ( http://jtds.sourceforge.net/) to access our databases. One problem I ran into when resetting our database was when constraints existed on existing tables so I use some MSSQL specific SQL to temporarily detach the constraints during the reset process. If you are not using MSSQL, you will need to change the insert operation to CLEAN_INSERT (if i remember correctly) as it is currently using MSSQL_CLEAN_INSERT in addition to updating the constraint code. I hope this helps you with what you are working on. Good luck! HTH, Rich Kroll ******************************* Properties file (db-build.properties) ******************************* db.driver=net.sourceforge.jtds.jdbc.Driver db.urlprefix=jdbc:jtds:sqlserver:// db.server=RKROLL db.port=1433 db.instance= db.database=MYDATABASE db.username=user db.password=password db.exportfile=dataset ******************************* Build file (build.xml) ******************************* <project name="Build" default="menu" basedir="."> <taskdef name="dbunit" classname="org.dbunit.ant.DbUnitTask" /> <property file="db-build.properties" /> <target name="menu" depends="init"> <echo> MENU ---- Available tasks: export: Exports the database to a flat xml file. (Should only be done on schema changes) export-dtd: Exports the database schema DTD reset: Reset the database to a last known good state disable-constraints: Disable database constraints enable-constraints: Enable database constraints </echo> <input message="Choose a task:" validargs="export,export-dtd,reset,disable-constraints,enable-constraints" defaultvalue="reset" addproperty="task" /> <antcall target="${task}" inheritAll="true" /> </target> <!-- ================================= target: INIT ================================= --> <target name="init" description="--> Initilization"> <property name="db.server" value="RKROLL"/> <property name="db.port" value="1433"/> <property name="db.instance" value=""/> <property name="db.username" value="sa"/> <property name="db.password" value="password"/> <property name="db.exportfile" value="dataset"/> </target> <!-- ================================= target: reset ================================= --> <target name="reset" depends="init,disable-constraints" description="--> resets the database to a last known good state"> <echo>Performing database reset on ${db.server}:${db.port}/${db.database};instance=${db.instance}</echo> <echo>clean insert beginning...</echo> <dbunit driver="${db.driver}" url="${db.urlprefix}${db.server}:${db.port}/${db.database};instance=${db.ins tance}" userid="${db.username}" password="${db.password}"> <operation type="MSSQL_CLEAN_INSERT" src="${db.exportfile}.xml" format="flat" /> </dbunit> <echo>clean insert complete</echo> <echo>enabling constraints...</echo> <antcall target="enable-constraints" /> <echo>database reset complete.</echo> </target> <!-- ================================= target: export ================================= --> <target name="export" depends="init"> <echo>Accessing DB [jdbc:jtds:sqlserver://${db.server}:${db.port}/${db.database};instance=${db. instance}]</echo> <dbunit driver="${db.driver}" url="${db.urlprefix}${db.server}:${db.port}/${db.database};instance=${db.ins tance}" userid="${db.username}" password="${db.password}"> <export dest="${db.exportfile}.xml" /> </dbunit> </target> <!-- ================================= target: export-dtd ================================= --> <target name="export-dtd" depends="init" description="--> Exports the database DTD"> <dbunit driver="${db.driver}" url="${db.urlprefix}${db.server}:${db.port}/${db.database};INSTANCE=${db.ins tance}" userid="${db.username}" password="${db.password}"> <export dest="${db.exportfile}.dtd" format="dtd"/> </dbunit> </target> <!-- ================================= target: disable-constraints ================================= --> <target name="disable-constraints" depends="init" description="--> Disables all constraints in the database"> <echo>disabling constraints...</echo> <sql driver="${db.driver}" url="${db.urlprefix}${db.server}:${db.port}/${db.database};instance=${db.ins tance}" userid="${db.username}" password="${db.password}"> DECLARE @SQL VARCHAR (8000) SET @SQL='EXEC sp_msforeachtable ' + '@command1="ALTER TABLE ? NOCHECK CONSTRAINT all", @command2="ALTER TABLE ? DISABLE TRIGGER all"' SELECT @SQL EXEC(@SQL) </sql> <echo>constraints disabled.</echo> </target> <!-- ================================= target: enable-constraints ================================= --> <target name="enable-constraints" depends="init" description="--> Enables all constraints in the database"> <sql driver="${db.driver}" url="${db.urlprefix}${db.server}:${db.port}/${db.database};instance=${db.ins tance}" userid="${db.username}" password="${db.password}"> DECLARE @SQL VARCHAR (8000) SET @SQL='EXEC sp_msforeachtable ' + '@command1="ALTER TABLE ? CHECK CONSTRAINT ALL", @command2="ALTER TABLE ? ENABLE TRIGGER ALL"' SELECT @SQL EXEC(@SQL) </sql> </target> <!-- ================================= target: compare ================================= --> <target name="compare" depends="init" description="--> compare the existing db dataset to the known good set"> <dbunit driver="${db.driver}" url="${db.urlprefix}" userid="${db.username}" password="${db.password}"> <compare src="${db.exportfile}.xml"/> </dbunit> </target> </project> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Get the answers you are looking for on the ColdFusion Labs Forum direct from active programmers and developers. http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid-72&catid=648 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291154 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
