Difference between revisions of "AppSuite:DBMigration"
(→Database tables) |
|||
| (9 intermediate revisions by 4 users not shown) | |||
| Line 5: | Line 5: | ||
<div class="title">Database migration for Open-Xchange server.</div> | <div class="title">Database migration for Open-Xchange server.</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 | + | '''Summary''': With release 7.6.1 it is possible to change the database schema based on usage of the open source tool Liquibase. Currently this applies to the config database, as well as so-called global databases for storing cross-context data (available with v7.8). This article gives a short introduction about the current implementation. |
| − | Please have a look at Liquibase, its features and documentation before working with database migrations for Open-Xchange server: http://www.liquibase.org/documentation/ . | + | Please have a look at Liquibase, its features and documentation before working with database migrations for Open-Xchange server: http://www.liquibase.org/documentation/ and http://www.liquibase.org/bestpractices.html . |
Ho to create custom bundles is explained in an extra article: http://oxpedia.org/wiki/index.php?title=AppSuite:CustomDBMigration | Ho to create custom bundles is explained in an extra article: http://oxpedia.org/wiki/index.php?title=AppSuite:CustomDBMigration | ||
| Line 16: | Line 16: | ||
The following bundles provide the functionality for executing database migration statements. All are included within the open-xchange-core package. | The following bundles provide the functionality for executing database migration statements. All are included within the open-xchange-core package. | ||
| − | * com.openexchange.database.migration: Provides the service to execute database migrations | + | * com.openexchange.database.migration: Provides the service to execute database migrations and the core database ChangeLogs |
| − | + | * com.openexchange.database.migration.clt: Provides a command line tool that might help by working with database migrations based on Liquibase | |
| − | * com.openexchange.database.migration.clt: Provides | ||
== Current restrictions == | == Current restrictions == | ||
| − | |||
* Open-Xchange server currently uses Liquibase version 3.0.7. All features provided with later versions cannot be used. | * Open-Xchange server currently uses Liquibase version 3.0.7. All features provided with later versions cannot be used. | ||
| Line 32: | Line 30: | ||
=== Tags === | === 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. | 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. | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
=== Database === | === Database === | ||
| Line 45: | Line 36: | ||
Liquibase creates two tables to handle its current state: | Liquibase creates two tables to handle its current state: | ||
| − | * | + | * DATABASECHANGELOG: 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. |
| − | * | + | * DATABASECHANGELOGLOCK: Handles access to the database to not execute database migration statements twice. |
==== Database lock ==== | ==== Database lock ==== | ||
| Line 56: | Line 47: | ||
Implementing CustomSqlChange enables you to write custom SQL statements instead of using declarative statements. | Implementing CustomSqlChange enables you to write custom SQL statements instead of using declarative statements. | ||
| − | == | + | == Implementation details == |
| − | |||
| − | |||
| − | |||
| − | + | The Open-Xchange server will wait within the startup until all provided Liquibase update tasks have been executed to ensure consistent data usage! | |
| − | + | == Command line tool == | |
| − | + | Currently a command line tools is provided to manage the migrations: ''/opt/open-xchange/sbin/dbmigrations'' (Note: in versions prior v7.8, the utitlity is named ''configdbmigrations'', and the ''name'' option is missing): | |
| − | + | usage: dbmigrations | |
| + | -A,--adminuser <arg> Admin username | ||
| + | -h,--help Prints a help text | ||
| + | -l,--login <arg> The optional JMX login (if JMX | ||
| + | authentication is enabled) | ||
| + | -ll,--list-locks Lists all currently acquired locks. | ||
| + | -n,--name <arg> The database schema name to use | ||
| + | -P,--adminpass <arg> Admin password | ||
| + | -p,--port <arg> The optional JMX port (default:9999) | ||
| + | -r,--run Forces a run of the current core changelog. | ||
| + | --responsetimeout <arg> The optional response timeout in seconds | ||
| + | when reading data from server (default: 0s; | ||
| + | infinite) | ||
| + | -s,--password <arg> The optional JMX password (if JMX | ||
| + | authentication is enabled) | ||
| + | -t,--host <arg> The optional JMX host (default:localhost) | ||
| + | -u,--force-unlock Forces a release of all locks. | ||
| + | Prints the current migration status if no option is set. | ||
== Important hints == | == Important hints == | ||
| − | * | + | * ChangeSets are executed top-down within a ChangeLog, so always add new migrations at the bottom of the file. For migrations regarding core components always use the configdbChangeLog.xml in com.openexchange.database.migration/resource/liquibase. For non-core components see [[AppSuite:CustomDBMigration]]. |
| − | * 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. | + | * ChangeSets must be constructed in an idempotent manner, i.e. they must not fail or break something if they are run several times. Use preconditions with onFail="MARK_RAN" to achieve this. |
| + | * If you are adding the first migration for an upcoming release, add an according milestone tag after the actual migration ChangeSets! | ||
| + | * 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. | ||
* http://www.liquibase.org/documentation/sql_output.html | * http://www.liquibase.org/documentation/sql_output.html | ||
Latest revision as of 10:03, 14 March 2019
This information is valid from 7.6.1 on.
Database migration for Open-Xchange server.
Summary: With release 7.6.1 it is possible to change the database schema based on usage of the open source tool Liquibase. Currently this applies to the config database, as well as so-called global databases for storing cross-context data (available with v7.8). This article gives a short introduction about the current implementation.
Please have a look at Liquibase, its features and documentation before working with database migrations for Open-Xchange server: http://www.liquibase.org/documentation/ and http://www.liquibase.org/bestpractices.html .
Ho to create custom bundles is explained in an extra article: http://oxpedia.org/wiki/index.php?title=AppSuite:CustomDBMigration
Contents
Database migration bundles
The following bundles provide the functionality for executing database migration statements. All are included within the open-xchange-core package.
- com.openexchange.database.migration: Provides the service to execute database migrations and the core database ChangeLogs
- com.openexchange.database.migration.clt: Provides a command line tool that might help by working with database migrations based on Liquibase
Current restrictions
- Open-Xchange server currently uses Liquibase version 3.0.7. All features provided with later versions cannot be used.
Liquibase
The following chapter describes shortly 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.
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.
Database
Database tables
Liquibase creates two tables to handle its current state:
- DATABASECHANGELOG: 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.
- DATABASECHANGELOGLOCK: Handles access to the database to not execute database migration statements twice.
Database lock
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.
Custom Java Classes
Implementing CustomSqlChange enables you to write custom SQL statements instead of using declarative statements.
Implementation details
The Open-Xchange server will wait within the startup until all provided Liquibase update tasks have been executed to ensure consistent data usage!
Command line tool
Currently a command line tools is provided to manage the migrations: /opt/open-xchange/sbin/dbmigrations (Note: in versions prior v7.8, the utitlity is named configdbmigrations, and the name option is missing):
usage: dbmigrations
-A,--adminuser <arg> Admin username
-h,--help Prints a help text
-l,--login <arg> The optional JMX login (if JMX
authentication is enabled)
-ll,--list-locks Lists all currently acquired locks.
-n,--name <arg> The database schema name to use
-P,--adminpass <arg> Admin password
-p,--port <arg> The optional JMX port (default:9999)
-r,--run Forces a run of the current core changelog.
--responsetimeout <arg> The optional response timeout in seconds
when reading data from server (default: 0s;
infinite)
-s,--password <arg> The optional JMX password (if JMX
authentication is enabled)
-t,--host <arg> The optional JMX host (default:localhost)
-u,--force-unlock Forces a release of all locks.
Prints the current migration status if no option is set.
Important hints
- ChangeSets are executed top-down within a ChangeLog, so always add new migrations at the bottom of the file. For migrations regarding core components always use the configdbChangeLog.xml in com.openexchange.database.migration/resource/liquibase. For non-core components see AppSuite:CustomDBMigration.
- ChangeSets must be constructed in an idempotent manner, i.e. they must not fail or break something if they are run several times. Use preconditions with onFail="MARK_RAN" to achieve this.
- If you are adding the first migration for an upcoming release, add an according milestone tag after the actual migration ChangeSets!
- 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.
- http://www.liquibase.org/documentation/sql_output.html