AppSuite:CustomDBMigration: Difference between revisions

From Open-Xchange
No edit summary
 
(5 intermediate revisions by 2 users not shown)
Line 21: Line 21:


The required bundle is named 'com.openexchange.sample.database.migration'.
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 ==
== 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).
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 ==
== 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'.  
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.


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.
=== Declarative changes ===
 
=== Descriptive changes ===
Liquibase executes statements based on declarative descriptions. Creating a table for instance will look like
Liquibase executes statements based on declarative descriptions. Creating a table for instance will look like


<pre><code>
<pre>
<changeSet author="martin.schneider" id="createTable-sample" logicalFilePath="custom-1">
<changeSet id="7.6.1:com.openexchange.sample.database.migration:sampleTable" author="steffen.templin@open-xchange.com">
<comment>This is my comment for creating this table</comment>
  <preConditions onFail="MARK_RAN">
<createTable tableName="customtable1">
    <not>
<column name="id" type="int">
      <tableExists tableName="sample" />
<constraints primaryKey="true" nullable="false" />
    </not>
</column>
  </preConditions>
<column name="name" type="varchar(50)">
  <comment>Creates the 'sample' table</comment>
<constraints nullable="false" />
  <createTable tableName="sample">
</column>
    <column name="id" type="int" autoIncrement="true">
<column name="active" type="boolean" defaultValueBoolean="true" />
      <constraints primaryKey="true" nullable="false" />
</createTable>
    </column>
</changeSet>
    <column name="value" type="varchar(255)" />
</code></pre>  
  </createTable>
</changeSet>
</pre>  


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
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


=== SQL changes ===
=== SQL changes ===
You are even able to reference Java classes from within the changelog xml (see below). The referenced Java class have to implement CustomSqlChange as shown in the bundle.  
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.  
<pre>
<pre>
<code>
<changeSet id="7.6.1:com.openexchange.sample.database.migration:sampleTable2" author="martin.schneider@open-xchange.com">
<changeSet id="2" author="martin.schneider"
  <preConditions onFail="MARK_RAN">
logicalFilePath="release-x.y.z/ExampleCustomSqlChange">
    <not>
<preConditions>
      <tableExists tableName="sample_2" />
<changeSetExecuted author="martin.schneider" id="createTable-sample"
    </not>
changeLogFile="custom-1" />
  </preConditions>
</preConditions>
  <comment>
<comment>
    This ChangeSet executes custom sql based on the implementation of CustomSqlChange. The execution depends on the precondions result.
This changeset executes custom sql based on the
  </comment>
implementation of CustomSqlChange.
  <customChange class="com.openexchange.sample.database.migration.custom.ExampleCustomSqlChange" />
To become executed the above preCondition must be true.
</changeSet>
</comment>
</pre>
<customChange
 
class="com.openexchange.sample.database.migration.custom.ExampleCustomSqlChange" />
=== Running the migrations ===
</changeSet>
After you retrieved the DBMigrationExecutorService from the OSGi service registry, you can schedule your migration ChangeLog via the <code>scheduleConfigDBMigration(String fileLocation, ResourceAccessor accessor)</code> method.
</code></pre>  
 
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 <code>/liquibase/configdbChangeLog.xml</code>:
 
<pre>
com.example.ox.extension.jar
|-com
| |-example
| | |-ox
| | | |-extension
| | | | |-Activator.class
|-META-INF
| |-MANIFEST.MF
|-liquibase
| |-configdbChangeLog.xml
</pre>
 
The second argument must be of type <code>liquibase.resource.ResourceAccessor</code> and must be able to load the ChangeLog file and all referenced resources therein. You most likely want to use an instance of <code>com.openexchange.database.migration.resource.accessor.BundleResourceAccessor</code> here, initialized with your custom bundles context.


== Results ==
== Results ==
After using Liquibase the first time you will have two additional tables for managing its state. Furthermore the following output shows the two customtables:
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:


<pre><code>
<pre>
mysql> show tables;
mysql> show tables;
+------------------------+
+------------------------+
| Tables_in_configdb    |
| Tables_in_configdb    |
+------------------------+
+------------------------+
| ...                    |
| DATABASECHANGELOG      |
| DATABASECHANGELOG      |
| DATABASECHANGELOGLOCK  |
| DATABASECHANGELOGLOCK  |
| customtable1          |
| sample                |
| customtable2          |
| sample_2              |
| ...                    |
+------------------------+
+------------------------+
</code></pre>
</pre>
 
=== customtable1 ===
The createTable statement from above created the following table:
 
<pre><code>
mysql> show create table customtable1;
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                    |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customtable1 | CREATE TABLE `customtable1` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `active` bit(1) DEFAULT b'1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)
</code></pre>
 
 
=== customtable2 ===
The CustomSqlChange implementation referenced created the following table:
<pre><code>
mysql> show create table customtable2;
+--------------+----------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                            |
+--------------+----------------------------------------------------------------------------------------------------------+
| customtable2 | CREATE TABLE `customtable2` (
  `customColumn` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------+----------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)
</code></pre>
 


== Important hints ==
== Conventions ==
* Execute the statements by using the ResourceAccessor 'ClassLoaderResourceAccessor' as shown in the bundle. Call com.openexchange.database.migration.DBMigrationExecutorService.execute(String, List<ResourceAccessor>) and provide the ResourceAccessor. If you do not use this accessor custom Java classes won't be found.  
* [[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 <code>resource</code>, that has to be created at the bundles root directory. At build time everything below the <code>resource</code> directory is moved directly to the bundles root directory and the directory <code>resource</code> is removed.
* The attribute <code>logicalFilePath</code> must be set for the <code>databaseChangeLog</code> tag. It must be set to a unique identifier for your ChangeLog file. The convention is to name it <code><bundle-name>/<file-name></code>
* Liquibase validates every ChangeLog XML file against its schema. The schema reference must be set for the <code>databaseChangeLog</code> tag. The schema is provided by the <code>com.openexchange.database.migration</code> bundle. Just reference it like in the sample bundle.
* The IDs for your ChangeSets should contain your bundles name. The <code>author</code> tag should be a valid mail address of the responsible developer.  


[[Category: OX7]]
[[Category: OX7]]

Latest revision as of 12:57, 9 September 2014


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'.

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).

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.

Declarative changes

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="steffen.templin@open-xchange.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

SQL changes

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="martin.schneider@open-xchange.com">
  <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 /liquibase/configdbChangeLog.xml:

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.

Results

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               |
| ...                    |
+------------------------+

Conventions

  • 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 resource directory is moved directly to the bundles root directory and the directory resource is removed.
  • The attribute logicalFilePath must be set for the databaseChangeLog tag. It must be set to a unique identifier for your ChangeLog file. The convention is to name it <bundle-name>/<file-name>
  • Liquibase validates every ChangeLog XML file against its schema. The schema reference must be set for the databaseChangeLog tag. The schema is provided by the com.openexchange.database.migration bundle. Just reference it like in the sample bundle.
  • The IDs for your ChangeSets should contain your bundles name. The author tag should be a valid mail address of the responsible developer.