database specific migration

[kirill.petrov]

How multiple databases are supported?
For example you have MySQL and PostgreSQL databases that support
different SQL syntax. Do you create two sets of the files to support
these?

[Christian Nelson]

We have a few projects that support multiple database types.  You can achieve a this pretty easily using maven profiles.  Here's is the relevant part of the pom for configuring c5-db-migrations to support 2 databases:

...

  <properties>

    <db.name>acme_test</db.name>

  </properties>

  <profiles>

    <profile>

      <id>mysql</id>

      <properties>

        <migrations.path>src/main/db/mysql/migrations</migrations.path>

        <db.url>jdbc:mysql://localhost/${db.name}</db.url>

        <db.username>dev</db.username>

        <db.password>dev</db.password>

      </properties>

    </profile>

    <profile>

      <id>postgresql</id>

      <properties>

        <migrations.path>src/main/db/postgresql/migrations</migrations.path>

        <db.url>jdbc:postgresql://localhost/${db.name}</db.url>

        <db.username>dev</db.username>

        <db.password>dev</db.password>

      </properties>

    </profile>

  </profiles>

  <build>

    <plugins>

      <plugin>

        <groupId>com.carbonfive</groupId>

        <artifactId>db-migration-maven-plugin</artifactId>

        <version>0.9.8</version>

        <configuration>

          <migrationsPath>${migrations.path}</migrationsPath>

          <url>${db.url}</url>

          <username>${db.username}</username>

          <password>${db.password}</password>

        </configuration>

        <dependencies>

          <dependency>

            <groupId>mysql</groupId>

            <artifactId>mysql-connector-java</artifactId>

            <version>5.1.6</version>

          </dependency>

          <dependency>

            <groupId>postgresql</groupId>

            <artifactId>postgresql</artifactId>

            <version>8.3-603.jdbc3</version>

          </dependency>

        </dependencies>

      </plugin>

    </plugins>

  </build>

...

You can imagine how this can be expanded to handle any number of databases and types.  You can also property-ify other things like database host so it can be easily overridden from the command line.

Cheers,

Christian

--
Christian Nelson -- (e) cne...@slac.com -- (m) 415-378-3988