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
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'.
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).
Using database migration
First of all you have to create the ChangeLog XML file that contains the ChangeSets. The sample bundle shows two different approaches: A declarative one via liquibase migration directives and a programmatic one that executes plain SQL. Programmatic changes must also be referenced in the ChangeLog file.
Liquibase executes statements based on declarative descriptions. Creating a table for instance will look like
<changeSet id="7.6.1:com.openexchange.sample.database.migration:sampleTable" author="email@example.com"> <preConditions onFail="MARK_RAN"> <not> <tableExists tableName="sample" /> </not> </preConditions> <comment>Creates the 'sample' table</comment> <createTable tableName="sample"> <column name="id" type="int" autoIncrement="true"> <constraints primaryKey="true" nullable="false" /> </column> <column name="value" type="varchar(255)" /> </createTable> </changeSet>
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
It is possible to reference Java classes from within the ChangeLog file (see below). The referenced Java class has to implement CustomSqlChange as shown in the bundle.
<changeSet id="7.6.1:com.openexchange.sample.database.migration:sampleTable2" author="firstname.lastname@example.org"> <preConditions onFail="MARK_RAN"> <not> <tableExists tableName="sample_2" /> </not> </preConditions> <comment> This ChangeSet executes custom sql based on the implementation of CustomSqlChange. The execution depends on the precondions result. </comment> <customChange class="com.openexchange.sample.database.migration.custom.ExampleCustomSqlChange" /> </changeSet>
Running the migrations
After you retrieved the DBMigrationExecutorService from the OSGi service registry, you can schedule your migration ChangeLog via the
scheduleConfigDBMigration(String fileLocation, ResourceAccessor accessor) method.
The first argument is the path to your ChangeLog file, starting at your bundles root (i.e. the highest level within your JAR file). If your structure looks like below, this would be
com.example.ox.extension.jar |-com | |-example | | |-ox | | | |-extension | | | | |-Activator.class |-META-INF | |-MANIFEST.MF |-liquibase | |-configdbChangeLog.xml
The second argument must be of type
liquibase.resource.ResourceAccessor and must be able to load the ChangeLog file and all referenced resources therein. You most likely want to use an instance of
com.openexchange.database.migration.resource.accessor.BundleResourceAccessor here, initialized with your custom bundles context.
After using Liquibase the first time you will have two additional tables for managing its state. Furthermore the following output shows the two sample tables:
mysql> show tables; +------------------------+ | Tables_in_configdb | +------------------------+ | ... | | DATABASECHANGELOG | | DATABASECHANGELOGLOCK | | sample | | sample_2 | | ... | +------------------------+
- AppSuite:DBMigration is a must-read before writing custom configdb migrations!
- If you are using the Open-Xchange build system to build your bundle JAR, your migration files have to be placed below the folder
resource, that has to be created at the bundles root directory. At build time everything below the
resourcedirectory is moved directly to the bundles root directory and the directory
- The attribute
logicalFilePathmust be set for the
databaseChangeLogtag. It must be set to a unique identifier for your ChangeLog file. The convention is to name it
- Liquibase validates every ChangeLog XML file against its schema. The schema reference must be set for the
databaseChangeLogtag. The schema is provided by the
com.openexchange.database.migrationbundle. Just reference it like in the sample bundle.
- The IDs for your ChangeSets should contain your bundles name. The
authortag should be a valid mail address of the responsible developer.