Sheila's Blog

Having set up the basic folder structure in Automated Build Databse scripts you now need to add the mysql scripts.

You create them in PROJECT_HOME/db/mysql/testdb db/mysql/testdb/build-mysql-testdb.xml db/mysql/testdb/createTables.sql db/mysql/testdb/dropTables.sql db/mysql/testdb/populateTables.sql db/mysql/testdb/testData.sql

The build script itself is fairly self-explanatory.  It replicates the targets from the master build script.  It reads the mysql.properties and schema.properties files and adds the correct jar file to the classpath.

For each target you first check if the property mysql.db.enable has been set so the target will only run if you’ve configured schema.properties to include the mysql database.   For each target you connect to the database and execute the sql in the appropriate sql script.

mysql/testdb/build-mysql-testdb.xml: <?xml version="1.0"?> <project basedir="." name="-build"> <property file="../../config/mysql.properties" /> <property file="../../config/schema.properties" /> <property name="lib.dir" value="../../lib" /> <!-- Class path settings --> <fileset id="lib" dir="${lib.dir}/mysql"> <include name="*.jar" /> </fileset> <path id="classpath"> <fileset refid="lib" /> </path> <target name="createUser" if="mysql.db.enable" description="Creates user and schema specified in properties file"> <echo>Executing mysql-airline:createUser </echo> <sql driver="${mysql.driver}" url="${mysql.url}" userid="${mysql.rootuser}" password="${mysql.rootpass}" print="${print}"> <classpath refid="classpath"/> create schema ${mysql.schema}; GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,ALTER,INDEX,DROP ON ${mysql.schema}.* TO '${mysql.username}'@'${mysql.user.location}' IDENTIFIED BY '${mysql.password}'; </sql> </target> <target name="dropUser" if="mysql.db.enable" description="Drops user and schema specified in properties file"> <echo>Executing mysql-airline:dropUser </echo> <sql driver="${mysql.driver}" url="${mysql.url}" userid="${mysql.rootuser}" password="${mysql.rootpass}" print="${print}" onerror="continue"> <classpath refid="classpath"/> drop schema ${mysql.schema}; drop user '${mysql.username}'@'${mysql.user.location}'; </sql> </target> <target name="recreateTables" if="mysql.db.enable" depends="dropUser,createUser,createTables,populateTables,testData" description="refreshes the database tables"/> <target name="createTables" if="mysql.db.enable" description="Creates Database Tables"> <echo>Executing mysql-airline:createTables </echo> <sql driver="${mysql.driver}" src="${mysql.create.tables.script}" url="${mysql.url}/${mysql.schema}" userid="${mysql.username}" password="${mysql.password}" print="${print}"> <classpath refid="classpath"/> </sql> </target> <target name="populateTables" if="mysql.db.enable" description="Populates Database Tables"> <echo>Executing mysql-airline:populateTables </echo> <sql driver="${mysql.driver}" src="${mysql.populate.tables.script}" url="${mysql.url}/${mysql.schema}" userid="${mysql.username}" password="${mysql.password}" print="${print}"> <classpath refid="classpath"/> </sql> </target> <target name="dropTables" if="mysql.db.enable" description="Drops Database Tables"> <echo>Executing mysql-airline:dropTables </echo> <sql driver="${mysql.driver}" url="${mysql.url}/${mysql.schema}" userid="${mysql.username}" password="${mysql.password}" src="${mysql.drop.tables.script}" print="${print}" onerror="continue"> <classpath refid="classpath"/> </sql> </target> <target name="testData" if="mysql.db.enable" description="Refreshes test data"> <echo>Executing mysql-airline:testData </echo> <sql driver="${mysql.driver}" src="${mysql.testdata.script}" url="${mysql.url}/${mysql.schema}" userid="${mysql.username}" password="${mysql.password}" print="${print}"> <classpath refid="classpath"/> </sql> </target> </project>

You then edit the sql scripts to add the tables and data needed for your schema.  Note that for targets that drop tables or users we set onerror=”continue” so that the build won’t fail if you add in new tables and attempt to drop them before they’ve been created.

Tags:   automated build · mysqlNo Comments