AppSuite:CustomDBMigration: Difference between revisions

From Open-Xchange
No edit summary
No edit summary
Line 7: Line 7:
'''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.  
'''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 (restrictions in usage, developer hints, available tools, ...) about liquibase within the Open-Xchange server are available on http://oxpedia.org/wiki/index.php?title=AppSuite:DBMigration
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 23: Line 23:


== What the bundle does? ==
== What the bundle does? ==
The sample bundle
The sample 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.


=== Bundle dependencies ===
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).
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
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


Usage of both is shown within the sample bundle.
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.
 
== 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.  
 
=== Database ===


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


* 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.
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.
* LIQUIBASECHANGELOGLOCK: Handles access to the database to not execute database migration statements twice.


==== Database lock ====
=== Descriptive changes ===
Liquibase executes statements based on declarative descriptions. Creating a table for instance will look like


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.
<pre><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></pre>


=== Custom Java Classes ===
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


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.
=== SQL changes ===


Have a look at the sample bundle which references and executes com.openexchange.sample.database.migration.custom.ExampleCustomSqlChange.


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

Revision as of 06:23, 29 August 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'.

What the bundle does?

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

SQL changes

Retrieved from "https://wiki.open-xchange.com/wiki/index.php?title=AppSuite:CustomDBMigration&oldid=18366"