(Recovered from my old Blog).
My source control contains many tables, many of which have dependencies on one another via foreign keys. When the table structures are being changed fairly regularly, it can be a pain to have to keep updating the database manually.
Ant has a built in SQL task which will pick up a file, then execute the contained statements on the database of your choice.
- Firstly, create the required scripts to be executed. For table creation scripts, I always add ‘DROP TABLE IF EXISTS’ (or a variant depending on SQL dialect) to make it easier to run a script on its own if it has no external dependencies.
- Create extra scripts for dropping the tables (with the commands in the correct order to respect foreign keys).
- Create an Ant build file (build.xml). Each script to be executed is placed in the file in order of execution (to again respect foreign keys).
- Execute Ant from the directory containing build.xml
Example for step one:
|
1 2 3 4 5 6 7 8 9 |
DROP TABLE IF EXISTS reference_generator; CREATE TABLE reference_generator ( ref_gen_type VARCHAR(20) NOT NULL, reference BIGINT NOT NULL, timestamp DATETIME NOT NULL, PRIMARY KEY (ref_gen_type) ) ENGINE=INNODB; |
Example for step two (filename – drop_party.sql):
|
1 2 3 4 |
DROP TABLE IF EXISTS party_attribute; DROP TABLE IF EXISTS party_ext_ref; DROP TABLE IF EXISTS party_flag; DROP TABLE IF EXISTS party; |
Example for step three (build.xml):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<project name=“DatabaseSetup” basedir=“.” default=“createAndPopulate”> <property name=“sql.driver” value=“com.mysql.jdbc.Driver” /> <property name=“sql.url” value=“jdbc:mysql://server/dbname” /> <property name=“sql.username” value=“myusername” /> <property name=“sql.password” value=“mypassword” /> <target name=“createAndPopulate”> <sql driver=“${sql.driver}” url=“${sql.url}” userid=“${sql.username}” password=“${sql.password}” > <transaction src=“./table/drop_party.sql” /> <transaction src=“./table/party.sql” /> <transaction src=“./table/party_attribute.sql” /> <transaction src=“./table/party_ext_ref.sql” /> <transaction src=“./table/party_flag.sql” /> <transaction src=“./table/reference_generator.sql” /> <transaction src=“./data/reference_generator.sql” /> </sql> </target> </project> |
It is of course important that the JDBC driver referenced (in the example com.mysql.jdbc.Driver) is available to Ant. A simple way of doing this is to add the relevant jar to Ant’s lib directory.
MySQL Stored Procedures
The above technique works for MySQL stored procedures, with one minor change. When writing a MySQL stored procedure, the delimiter to use must be explicitly set (as ; is used within the stored procedures statements).
To set this delimiter, the ‘delimiter’ attribute within the sql tag must be provided.
For example:
|
1 2 3 4 5 6 |
<!— Usage: ant createsp –Dfile=./dir/file.sql—> <target name=“createsp”> <sql driver=“${sql.driver}” url=“${sql.url}” userid=“${sql.username}” password=“${sql.password}” delimiter=“//” > <transaction src=“${file}” /> </sql> </target> |