How to create a database in MySQL, SQL Server and Oracle from a single ANT assembly?

Question

How to create a database from scratch by creating tables and filling them with data examples in MySQL, SQL Server, and also Oracle, using a single ANT construct?

Answer 1

What we do with ant and mysql :

Prepare the SQL file as follows:

drop database if exists test; create database test character set utf8; use test; create table Account_ ( ...fields ) engine InnoDB; create .... more tables ... 

Use ant script as follows:

 <exec executable="mysql" input="test.sql" /> 

So, I think that the main problem you have to deal with is to create a sample sql file without working with Ant.

What we do is write a sample-sql-builder that generates data according to ftl files (which show the table structures). We just run it from ant, all the logic is in pure java.

Answer 2

I would recommend using something like Liquibase if you need to manage schema definition with multiple DBMSs.

The definition of the Liquibase schema is independent of the DBMS, and Liquibase will handle the translation into various dialects for you. However, you can still have a specific DBMS code in the schema definition.

Liquibase has an Ant task so you can integrate it into your build script. But since you can basically invoke any Java program from Ant, you can also use the “normal” command line mode.

Note that "creating a database" in the Oracle world usually means creating a full-fledged instance, which is certainly not what you want (and, of course, will not be allowed to do DBA)

Answer 3

No one mentioned this, but you can also use the ant sql task. Below is the ant macro I'm using. A macro depends on many properties that are read from a .properties file over the years when I used variants of this macro with Postgres, Oracle, MySQL, DB2, MS SQL Server

 <macrodef name="oracle-admin"> <attribute name="sqlfile" /> <attribute name="onerror" default="continue" /> <attribute name="autocommit" default="true" /> <attribute name="expandproperties" default="true" /> <attribute name="delimitertype" default="normal" /> <sequential> <sql driver="oracle.jdbc.OracleDriver" url="${oracle.url}" userid="${oracle.db.admin.user}" password="${oracle.db.admin.password}" classpath="${oracle.jdbc.jar}" expandproperties="@{expandproperties}" keepformat="true" delimitertype="@{delimitertype}" rdbms="oracle" onerror="@{onerror}" print="true" autocommit="@{autocommit}"> <transaction src="@{sqlfile}"> </transaction> </sql> </sequential> </macrodef> 

The macro is loaded by the SQL file, the sql file can contain ant properties, which will be replaced by the values ​​from the properties file.

 CREATE ROLE ${postgres.db.user} WITH LOGIN PASSWORD '${postgres.db.password}'; CREATE DATABASE ${postgres.db.database} OWNER ${postgres.db.user} ENCODING='UTF8'; 

Answer 4

One option is to support various sql scripts and run them from ant using the exec task . The second option would be to have one meta-information file from which you create the appropriate sql for the database, and then use the exec task.

I agree that Liquibase will be the most elegant open source solution for you. You may need steps.

What we do is a little different. We create a custom java program, than load sql and make jdbc calls. This works, the weakest part will be when parsing .sql files. This java program is called from an ant task using java task

Creating a database - this can be automated, but it is very closely connected with the database server. Oracle dbca has command lines. If this is for some kind of internal deployment, say, testing? In this case, using another scheme will be good enough, which can be processed through jdbc.