[Ant][SQL] データベース自動作成サンプル

build-database.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration>
<project basedir="." default="all" name="sample">
  <property name="db.user" value="root" />
  <property name="db.pass" value="root" />
  <property name="db.host" value="localhost" />
  <property name="db.port" value="3306" />
  <property name="db.name" value="sample1" />
  <property name="db.charset" value="utf8" />

  <property name="db.url" value="jdbc:mysql://${db.host}:${db.port}/" />
  <property name="db.driver" value="com.mysql.jdbc.Driver" />
  <property name="db.src.dir" value="database" />
  <property name="db.ddl.dir" value="${db.src.dir}/ddl" />
  <property name="db.data.dir" value="${db.src.dir}/data" />

  <path id="db.classpath">
    <fileset dir="lib" includes="mysql*.jar" />
  </path>

  <tstamp>
    <format property="TSTAMP" pattern="yyyyMMddHHmmss" />
  </tstamp>
  <property name="export.file" value="${db.data.dir}/${db.name}_${TSTAMP}.sql" />

  <target name="all"
    depends="export, create-database, create-tables, import"
    description="全て実行" />

  <target name="create-database" description="データベースの作成">
    <sql driver="${db.driver}" url="${db.url}"
      userid="${db.user}" password="${db.pass}"
      classpathref="db.classpath"
      src="${db.ddl.dir}/create-database.sql" />
  </target>

  <target name="create-tables" description="表の作成">
    <antcall target="create-a-table">
      <param name="sql.file" value="create-table-sample1_1.sql"/>
    </antcall>
    <antcall target="create-a-table">
      <param name="sql.file" value="create-table-sample1_2.sql"/>
    </antcall>
  </target>

  <target name="create-a-table">
    <sql driver="${db.driver}" url="${db.url}${db.name}"
      userid="${db.user}" password="${db.pass}"
      classpathref="db.classpath"
      src="${db.ddl.dir}/${sql.file}" />
  </target>

  <target name="export" description="データの退避">
    <mkdir dir="${db.data.dir}" />
    <exec executable="mysqldump" output="${export.file}">
      <arg value="--no-create-info" />
      <arg value="--complete-insert" />
      <arg value="--default-character-set=${db.charset}" />
      <arg value="--user=${db.user}" />
      <arg value="--password=${db.pass}" />
      <arg value="${db.name}" />
    </exec>
  </target>

  <target name="import" description="データの復元">
    <exec executable="mysql" input="${export.file}">
      <arg value="--default-character-set=${db.charset}" />
      <arg value="--user=${db.user}" />
      <arg value="--password=${db.pass}" />
      <arg value="${db.name}" />
    </exec>
  </target>
</project>

create-database.sql

DROP DATABASE IF EXISTS sample1;
CREATE DATABASE IF NOT EXISTS sample1;

GRANT USAGE ON *.*
TO sample1@localhost IDENTIFIED BY 'sample1';
DROP USER sample1@localhost;

GRANT SELECT, INSERT, UPDATE, DELETE ON sample1.*
TO sample1@localhost IDENTIFIED BY 'sample1';

create-table-sample1_1.sql

CREATE TABLE sample1_1(
  col1 INTEGER PRIMARY KEY,
  col2 VARCHAR(50)
);