AppSuite:CustomDBMigration: Difference between revisions

From Open-Xchange
(Created page with "{{VersionFrom|7.6.1}} <div class="title">How to write custom bundles to execute database migration statements.</div> '''Summary''': With release 7.6.1 it is possible to chan...")
 
 
(11 intermediate revisions by 2 users not shown)
Line 1: Line 1:
<!-- Author: Martin Schneider <martin.schneider@open-xchange.com> -->
{{VersionFrom|7.6.1}}
{{VersionFrom|7.6.1}}


<div class="title">How to write custom bundles to execute database migration statements.</div>
<div class="title">How to write custom bundles to execute database migration statements.</div>


'''Summary''': With release 7.6.1 it is possible to change the database schema based on usage of the open source tool Liquibase. Currently only updating the configdb with the new mechanism is desired. 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 given by Open-Xchange.  
'''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/ .
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


__TOC__
__TOC__
Line 17: Line 21:


The required bundle is named 'com.openexchange.sample.database.migration'.
The required bundle is named 'com.openexchange.sample.database.migration'.
== Current restrictions ==
* Database migrations can only be applied to the configdb!
* Open-Xchange server currently uses liquibase version 3.0.7. All features provided with later versions cannot be used.


== 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
== 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.
Usage of both is shown within the sample bundle.
 
== Liquibase ==
The following chapter describes in short the liquibase features and how they are used by Open-Xchange. For further information have a look at the official liquibase documentation: http://www.liquibase.org/documentation/
 
=== ChangeLog file / changeSets ===
A ChangeLog file is the heart of this database migration and contains all information required to change database structure and/or contents. It consists of ChangeSets that are used to define a migration. Normally ChangeSets are executed transactionally.
 
You have to provide a ChangeLog file that contains some ChangeSets that reflect your desired database migration. Within the sample bundle the file 'custom.changelog.xml' is referenced.
 
=== Tags ===
A ChangeSet can be used to tag the database at a current state. Open-Xchange uses this mechanism to tag the database to be ready for a new release.
 
Tags are not used in the sample custom bundle.
 
=== Preconditions ===
You are even able to define dependencies between ChangeSets. With that you can attach your custom ChangeSet to whatever ChangeSet you would like to. Open-Xchange uses this for ChangeSets that should be executed after the database has been tagged for a new release. All ChangeSets after tagging checks if the precondition is true. You can create complex conditions by using nested conditions. For further information have a look at the following documentation: http://www.liquibase.org/documentation/preconditions.html


Within the custom bundle the precondition is defined as follows: the changeSet with id="7.6.1:login2context:login_info", author="martin.schneider" and changeLogFile="release-7.6.1/1.login_info.changelog.xml" have to be executed (defined in ox.changelog.xml) so that all changeSets within custom.changelog.xml will become executed.
=== Declarative changes ===
Liquibase executes statements based on declarative descriptions. Creating a table for instance will look like


=== Database ===
<pre>
<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>
</pre>


==== Database tables ====
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
Liquibase creates two tables to handle its current state:


* LIQUIBASECHANGELOG: Contains information about the execution status of ChangeSets. Each executed ChangeSet will be added to this table. If there is no entry available for a ChangeSet liquibase will try to execute the statement.
=== SQL changes ===
* LIQUIBASECHANGELOGLOCK: Handles access to the database to not execute database migration statements twice.
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>
<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>
</pre>


==== Database lock ====
=== Running the migrations ===
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.


Liquibase uses a lock to only grant one liquibase instace access to the database to change. For this purpose a table named 'DATABASECHANGELOGLOCK' is created. In cluster environments the first node that successfully aquired the lock will execute the database migration statements. During this time all other nodes are blocked. After the lock was released the second node will get access to the database and read already executed ChangeSets. If all ChangeSets have been executed nothing will be done and the lock will be released. This happens with each node within the cluster.
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>:


=== Custom Java Classes ===
<pre>
com.example.ox.extension.jar
|-com
| |-example
| | |-ox
| | | |-extension
| | | | |-Activator.class
|-META-INF
| |-MANIFEST.MF
|-liquibase
| |-configdbChangeLog.xml
</pre>


Within your custom ChangeLog xml file you are not only able to define changes descriptive but even execute database statements defined within a class that implements CustomSqlChange.  
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.


Have a look at the sample bundle which references and executes com.openexchange.sample.database.migration.custom.ExampleCustomSqlChange.
== 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:


== Command line tools ==
<pre>
mysql> show tables;
+------------------------+
| Tables_in_configdb    |
+------------------------+
| ...                    |
| DATABASECHANGELOG      |
| DATABASECHANGELOGLOCK  |
| sample                |
| sample_2              |
| ...                    |
+------------------------+
</pre>


== Important hints ==  
== Conventions ==
* changeSets are uniquely identified by its id, author and path. Use the parameter 'logicalFilePath' for every changeSet you create to set a path that can be globally identified! If you do not set a logical file path liquibase will use the physical path of the file the changeSet is located in. With that each changeSet on different cluster nodes might have been identified as a new one.
* [[AppSuite:DBMigration]] is a must-read before writing custom configdb migrations!
* if you would like to use <include> statements to reference other files with liquibase statements we suggest adding parameter 'relativeToChangelogFile="true"' to avoid adding complete paths.
* 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.