multiple database upgrade in maven

[Giancarlo Frison]

Hi all,

I'm wondering whether is possible to configure more than one database
upgrading in maven.
Something like:
<configuration>
<upgrade>
<driver>com.mysql.jdbc.Driver</driver>
<url>jdbc:mysql://localhost/db1</url>
<username>db1</username>
<password>db1</password>
<upgradefile>etc/db1.sql</upgradefile>
<target>1.0.*</target>
</upgrade>
<upgrade>
<driver>com.mysql.jdbc.Driver</driver>
<url>jdbc:mysql://localhost/db2</url>
<username>db2</username>
<password>db2</password>
<upgradefile>etc/db2.sql</upgradefile>
<target>1.0.*</target>
</upgrade>
</configuration>

[René de Bloois]

Hi Giancarlo,

If the 2 databases should evolve together (which is what I suspect you want), you should create 1 upgrade file, and then you will be able to upgrade them both from the same upgrade file. An example pom.xml is here:

http://code.google.com/p/solidbase/source/browse/trunk/dist/pom.xml (look at the secondary section in the configuration)

and you will need the following annotation to select between different connections in the upgrade file:

http://code.google.com/p/solidbase/wiki/ReferenceGuide#SELECT_CONNECTION

The primary connection will then contain the control tables (DBVERSION & DBVERSIONLOG).

-/-

If, however, you have 2 databases that evolve independently from each other, I think you could use Maven techniques.

I imagine that you are able to define the SolidBase plugin twice by giving it a different id each time.

Haven't tried that myself, however.

In case it isn't possible with Maven, you could also use the SolidBase Ant Task by adding an Ant build.xml fragment to the pom.xml.

Ant is much more flexible when it comes to these kind of things (like executing SolidBase twice).

If you want help with either of the above, please let me know.

-/-

Hope this helps, if you need more help, just let me know.

Regards,

René de Bloois

[René de Bloois]

Giancarlo,

I have done some research on the matter. With Maven you are able to
upgrade two independent databases, with 2 upgrade files.

However, if the 2 databases are part of the same system/application
you also have the option to let them evolve together by using 1
upgrade file.

Here is the pom.xml:

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://
www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
http://maven.apache.org/maven-v4_0_0.xsd">

<modelVersion>4.0.0</modelVersion>

<groupId>solidbase</groupId>
<artifactId>mavenplugintest</artifactId>
<version>1.0</version>
<packaging>pom</packaging>

<pluginRepositories>
<pluginRepository>
<id>solidbase</id>
<name>SolidBase Repository</name>
<layout>default</layout>
<url>http://solidbase.googlecode.com/svn/repository</url>
<releases><enabled>true</enabled></releases>
<snapshots><enabled>false</enabled></snapshots>
</pluginRepository>
</pluginRepositories>

<build>
<plugins>
<plugin>
<groupId>solidbase</groupId>
<artifactId>solidbase</artifactId>
<version>1.6.4</version>
<dependencies>
<dependency>
<groupId>hsqldb</groupId>
<artifactId>hsqldb</artifactId>
<version>1.8.0.7</version>
</dependency>
</dependencies>
<configuration>
<driver>org.hsqldb.jdbcDriver</driver>
<username>sa</username>
<password></password>
<target>1.0.*</target>
</configuration>
<executions>
<execution>
<id>upgrade-db1</id>
<phase>pre-integration-test</phase>
<goals><goal>upgrade</goal></goals>
<configuration>
<url>jdbc:hsqldb:mem:testplugin1</url>
<upgradefile>upgrade-hsqldb-example1.sql</upgradefile>
</configuration>
</execution>
<execution>
<id>upgrade-db2</id>
<phase>pre-integration-test</phase>
<goals><goal>upgrade</goal></goals>
<configuration>
<url>jdbc:hsqldb:mem:testplugin2</url>
<upgradefile>upgrade-hsqldb-example2.sql</upgradefile>
</configuration>
</execution>
</executions>
</plugin>
</plugins>
</build>

</project>

I have put configuration that is the same for each execution in the
plugin, and configuration that is different for each execution in the
execution sections.

Here is the output:

G:\PROJECTS\solidbase\maven-plugin\test>mvn install
[INFO] Scanning for projects...
[INFO]
------------------------------------------------------------------------
[INFO] Building Unnamed - solidbase:mavenplugintest:pom:1.0
[INFO] task-segment: [install]
[INFO]
------------------------------------------------------------------------
[INFO] [site:attach-descriptor {execution: default-attach-descriptor}]
[INFO] [solidbase:upgrade {execution: upgrade-db1}]
[INFO] SolidBase v1.6.4-rev584 (http://solidbase.org)
[INFO]
[INFO] Opening file 'G:\PROJECTS\solidbase\maven-plugin\test\upgrade-
hsqldb-example1.sql'
[INFO] Encoding is 'ISO-8859-1'
[INFO] Connecting to database...
[INFO] The database is unmanaged.
[INFO] Setting up control tables to "1.1"
[INFO] Creating table DBVERSION...
[INFO] Creating table DBVERSIONLOG...
[INFO] Upgrading to "1.0.1"
[INFO] Creating table USERS...
[INFO] Inserting admin user...
[INFO] Inserting user...
[INFO] The database is upgraded.
[INFO]
[INFO] Current database version is "1.0.1".
[INFO] [solidbase:upgrade {execution: upgrade-db2}]
[INFO] SolidBase v1.6.4-rev584 (http://solidbase.org)
[INFO]
[INFO] Opening file 'G:\PROJECTS\solidbase\maven-plugin\test\upgrade-
hsqldb-example2.sql'
[INFO] Encoding is 'ISO-8859-1'
[INFO] Connecting to database...
[INFO] The database is unmanaged.
[INFO] Setting up control tables to "1.1"
[INFO] Creating table DBVERSION...
[INFO] Creating table DBVERSIONLOG...
[INFO] Upgrading to "1.0.1"
[INFO] Creating table USERS...
[INFO] Inserting admin user...
[INFO] Inserting user...
[INFO] The database is upgraded.
[INFO]
[INFO] Current database version is "1.0.1".
[INFO] [install:install {execution: default-install}]
[INFO] Installing G:\PROJECTS\solidbase\maven-plugin\test\pom.xml
to ...
[INFO]
------------------------------------------------------------------------
[INFO] BUILD SUCCESSFUL
[INFO]
------------------------------------------------------------------------
[INFO] Total time: 2 seconds
[INFO] Finished at: Fri Nov 26 18:38:59 CET 2010
[INFO] Final Memory: 9M/22M
[INFO]
------------------------------------------------------------------------
G:\PROJECTS\solidbase\maven-plugin\test>

Regards,
René de Bloois