AppSuite:CustomDBMigration

This information is valid from 7.6.1 on.

How to write custom bundles to execute database migration statements.

Summary: With release 7.6.1 it is possible to change the database schema based on the open source tool Liquibase. Currently only updating the configdb is supported. This article gives a short introduction (based on an existing sample bundle) how to write custom database migration bundles and how to attach your custom statements to those statements given by Open-Xchange.

Please have a look at Liquibase, its features and documentation before writing custom bundles: http://www.liquibase.org/documentation/ .

Additional information about Liquibase within the Open-Xchange server (restrictions in usage, developer hints, available tools, ...) are available on http://oxpedia.org/wiki/index.php?title=AppSuite:DBMigration

Prerequisite

This article is based on an existing sample bundle located in the public git repository backend-samples. Clone the repository by executing

git clone https://git.open-xchange.com/git/examples/backend-samples

The required bundle is named 'com.openexchange.sample.database.migration'.

What the bundle does?

There is no real use case behind the sample custom bundle. It only explains how to be able to execute database migration statements. It shows how to create tables based on a Liquibase ChangeLog file and additionally by executing custom Java classes.

By using Liquibase it is extremly easy to change or enhance the given example with your desired change.

Bundle dependencies

To execute custom database migration for the configdb you have to track the service com.openexchange.database.migration.DBMigrationExecutorService (default provided within bundle com.openexchange.database.migration).

If you would like to have the Open-Xchange database migrations executed before your custom statements are used you even have to track the service com.openexchange.database.migration.ox.DBMigrationOXExcecutorService which is provided within the bundle com.openexchange.database.migration.ox

The sample bundle relies on both and tracks them so we make sure that the required service for executing migration statements is available and Open-Xchange migration statements are executed before the custom bundle will start.

Using database migration

First of all you have to reference the changelog file (xml) that contains the statements that should be exectued. The sample references 'custom.changelog.xml'.

You have to decide if you would like to have Liquibase generate the SQL statements for the migration of your database or if you would like to write the statements in SQL by yourself.

Descriptive changes

Liquibase executes statements based on declarative descriptions. Creating a table for instance will look like

<code>
	<changeSet author="martin.schneider" id="createTable-sample" logicalFilePath="custom-1">
		<comment>This is my comment for creating this table</comment>
		<createTable tableName="customtable1">
			<column name="id" type="int">
				<constraints primaryKey="true" nullable="false" />
			</column>
			<column name="name" type="varchar(50)">
				<constraints nullable="false" />
			</column>
			<column name="active" type="boolean" defaultValueBoolean="true" />
		</createTable>
	</changeSet>
</code>

A list of available changes (e. g. addAutoIncrement, addColumn, addForeignKeyConstraint, createIndex, createProcedure, dropUniqueConstraint, insert, update and many more) can be found at http://www.liquibase.org/documentation/changes/index.html