Introduction

This document describes the DBInit and DBMigrate tool which is available since Intershop version 7.4. DBPrepare combines both tools in one and is available since 7.10.14.0. DBPrepare is using the declaration files of dbinit and dbmigrate.

Goals

The DBInit is a tool for initially creating the database schema objects and for preparing the database content of one or more cartridges. DBInit is not designed to migrate the database content and structure.

The DBMigrate tool is used to migrate the data and the database structure of an Intershop installation from defined former releases to an installed current release. It defines one global migration process, which can be executed on different environments. DBMigrate reuses some recurring migration steps (e.g., table creation), supports different development teams to create their own migration processes, and can minimize the effort to upgrade the development environment.

Documentation

• Cookbook - DBInit
• Cookbook - DBMigrate
• Cookbook - DBPrepare

Glossary

This glossary describes the terms in this concept:

General Processing

There are three processing phases in the DBInit and DBMigrate tools with the following processing order:

1. Pre-processing
2. Main-processing
3. Post-processing

For additional information about the processing phases, see also the section on DBInit below.

Each phase loops through the list of cartridges. See also the DBInit description for the property cartridges.dbinit within cartridgelist.properties.

Each cartridge executes the preparers defined in the version-independent properties file(s) and in the version-specific properties file(s) (DBMigrate only).

DBInit

The following picture illustrates this processing behavior for DBInit:

List of cartridges:

• Configuration file: cartrigelist.properties
• Property: cartridges.dbinit = Cartridge_1 Cartridge_2 ... Cartridge_n

Cartridge-specific preparer:

• Configuration file: dbinit.properties
• Property: [pre.|post.]ClassN = Preparer [params]
  • N - floating point number support, >= 0

DBMigrate

The following picture illustrates this processing behavior for DBMigrate:

List of cartridges:

• Configuration file: cartridgelist.properties
• Property: cartridges.dbinit = Cartridge_1 Cartridge_2 ... Cartridge_n

Cartridge-specific preparer:

• Configuration file:
  • Version-independent: migration.properties
  • Version-dependent: migration-to-<version>.properties
• Property: [pre.|post.]ClassN = Preparer [params]
  • N - floating point number support , >= 0

Migration Path:

• The migration version path is calculated for each cartridge based on the migration-to-<version>.properties files.

The order of the migration files is calculated from the version part of the file name:

• Version_1, Version_2, ... Version_n string should map to the corresponding file name
  • migration-to- Version_1.properties ,
  • migration-to- Version_2.properties ...
  • migration-to- Version_N.properties

DBInit


General Information

The list of cartridges to be processed by the DBInit and DBMigrate tools is specified in IS_SHARE/system/cartridges/cartridgelist.properties. The property name is specified by cartridges.dbinit. The supported value type is a space-separated list of cartridges.

Syntax for cartridges.dbinit in cartridgelist.properties:

cartridges.dbinit = cartridge1 cartridge2 ...

The DBInit process is configured using dbinit.properties files that exist for each cartridge at the location staticfiles/cartridge/dbinit.properties.

The file follows the standard Java properties file format. It contains a list of preparers that must be invoked during the DBInit process.

Each preparer is defined by an artificial property key with name Class, which is followed by a number. The number indicates the order in which the preparers will be called.

In addition, there are three preparer phases:

Syntax for the preparers in dbinit.properties:

[pre|post.]ClassN = cartridgePackage.prepareName [parameter1 parameter2 ...]
N - positive floating point number, example N = {0.0, ... 0.5, 0.75, 1, 2, 3, 3.5, ..., n}
n - largest positive finite floating point value, see java Double.MAX_VALUE

Preparer Parameters and Input Files

The currently known and used DBInit preparer parameters include:

• Files
  • Java properties
  • SQL scripts
• Simple String, Boolean or Numeric Parameter

The location patterns for preparer input files are:

Java properties:

• IS_SOURCE/<cartridge>/javasource/com/intershop/beehive/<cartridge>/dbinit/data/<type_of_data> or
• IS_SHARE/system/cartridges/<cartridge>/release/lib/com/intershop/beehive/<cartridge>/dbinit/data/<type_of_data>

Example of DBInit preparer input file for <type_of_data> : job, locking, organization, preference, ..., user, ...

SQL scripts:

• IS_SOURCE/<cartridge>/staticfiles/cartridge/lib/resources/<cartridge>/dbinit/scripts or
• IS_SHARE/system/cartridges/<cartridge>/release/lib/resources/<cartridge>/dbinit/scripts

Migration Preparer Registration

The DBInit process stores all currently configured DBMigrate preparer steps from all migration*.properties files for each processed cartridge. This is done to prevent the execution of these DBMigrate steps after a DBInit.

DBMigrate


Precondition

DBMigrate saves fine-grained migration steps with unique keys rather than the last migrated version of a cartridge. This approach allows the exact calculation of migration steps for a given migration path and avoids that newer database changes of older maintenance releases are never executed in newer migration operations.

The version based migration step processing works as follows:

• Load version based migration steps within correct execution order
• Load already completed migrations steps from database
• Detect all not and not yet successful before processed migrations steps
• Execute the resulting list of version based migration steps
• Save information about processed (success/failure) migrations steps

The unique key for a preparation step consists of:

• cartridgeUUID- The cartridge uuid within for the preparation step.
• ID- The property key of the preparation step, [pre|post.]ClassN.
• version- The version for the preparation step, e.g.: 7.4.3.0 or n/a.
• type- The preparation type, known types dbmigrate or dbinit.

Additional -classic command line parameters support functionality for:

• dry-run
• and/or re-execution
• and/or version-[in]dependent-exclude

Migration Version Path

The migration path is calculated for each cartridge based on the version of the migration-to-<version>.properties files. The version supports up to 4 decimal parts (a.b.c.d).

The global migration properties are stored in:

• IS_SHARE/system/config/cluster/migration.properties.

This file defines the intershop.migration.DropTables = [true|false], a global property that specifies whether old database tables should be dropped during the migration.

After DBInit and DBMigrate have run, the last entry of the migration path for each cartridge is committed to the database table CartridgeInformation into the column MigrationVersion.

Example A

Migrated Intershop installation to 7.4.3.0 is stored cartridge-specific within CartridgeInformation:

Example B

Migration steps for an Intershop cartridge <example> with the following starting situation:

• Previous migration of cartridge <example> to version: 7.4.3.0

• Migration preparation steps stored within database for cartridge <example> during previous migration to 7.4.3.0:

  Property Key	Version	Preparation Class	Preparation Status
  pre.Class1	7.4.2.0	ExecuteSQLScriptPreparer	SUCCESS
  Class1	7.4.3.0	DropTable	SUCCESS
  Class2	7.4.3.0	UpdateJobsPreparer	FAILURE
  post.Class1	7.4.3.0	MigrateStagingEnvironment	SUCCESS

• Installed version for cartridge <example> within file system: 7.4.5.0

• Migration steps within file system for cartridge <example>:

  Property Key	Version	Preparation Class
  pre.Class1	7.4.2.0	ExecuteSQLScriptPreparer
  pre.Class1	7.4.4.0	ExecuteSQLScriptPreparer
  Class1	7.4.3.0	DropTable
  Class2	7.4.3.0	UpdateJobsPreparer
  Class1	7.4.5.0	DropTable
  post.Class1	7.4.3.0	MigrateStagingEnvironment
  post.Class1	7.4.5.0	DeletePreferenceDefinition

• Executed migration preparation steps for cartridge <example> during migration to 7.4.5.0:

  Property Key	Version	Preparation Class	Why Execution	Preparation Status
  Class2	7.4.3.0	UpdateJobsPreparer	previous failure	SUCCESS
  pre.Class1	7.4.4.0	ExecuteSQLScriptPreparer	new step	SUCCESS
  Class1	7.4.5.0	DropTable	new step	SUCCESS
  post.Class1	7.4.5.0	DeletePreferenceDefinition	new step	SUCCESS

Cartridge-Specific Migration Properties

The locations for various cartridge-specific migration properties are:

• IS_SOURCE/<cartridge>/staticfiles/cartridge, or
• IS_SHARE/system/cartridges/<cartridge>/release

The file names are migration*.properties and contain pre.ClassN, ClassN and post.ClassN preparer, see also dbinit.properties syntax.

• migration.properties, a version-independent preparer file and all preparers that are always to be executed
• migration-to-<version>.properties, preparers to be executed if the database PREPARATIONSTEP table does not contain a success entry for the configured preparer

Generally, version-independent migration properties are used for:

• Structural database migration like tables, indexes, constraints and procedural SQL like packages, functions and procedures
• Data-replication (staging) environment migration globally over all cartridges

Preparer Parameters and Input Files

The currently known and used DBMigrate preparer parameters include:

• Files
  • Java properties
  • SQL scripts
  • UUIDs
• Simple string, boolean or numeric parameter

The location patterns for DBMigrate preparer input files are:

Java Properties

• See DBinit, the DBMigrate preparers use the original properties initially used by DBinit.
  For example, see also the "version-dependent migration-to-....properties example" below.

Class1= .... dbmigrate .preparer.job.AddJobsPreparer root \ 
com/intershop/beehive/core/ dbinit /data/job/JobConfigurations.properties \ 
com/intershop/beehive/core/ dbmigrate /data/<version>/job/JobConfigurationUUIDs.properties

SQL Scripts

• version-independent
  
  • IS_SOURCE/<cartridge>/staticfiles/cartridge/lib/resources/<cartridge>/dbinit/scripts or
  • IS_SHARE/system/cartridges/<cartridge>/release/lib/resources/<cartridge>/dbinit/scripts
• version-dependent
  • IS_SOURCE/<cartridge>staticfiles/cartridge/lib/com/intershop/<cartridge>/dbmigrate/data/<version>/scripts or
  • IS_SHARE/system/cartridges/<cartridge>/release/lib/com/intershop/<cartridge>/dbmigrate/data/<version>/scripts

UUID Files (Version-dependent Only)

• IS_SOURCE/<cartridge>staticfiles/cartridge/lib/com/intershop/<cartridge>/dbmigrate/data/<version>/<type_of_data> , or
• IS_SHARE/system/cartridges/<cartridge>/release/lib/com/intershop/<cartridge>/dbmigrate/data/<version>/<type_of_data>

Programming Initial and Migration Preparer

General

Style Guide - Localization Resource Bundle File Names

For all new files it is required to use the *Localization_en.properties naming. All old files with name *Information s _en.properties are deprecated.

ClassN = com.intershop.beehive.<>.dbinit.preparer.<>.PrepareFooBar \
         com.intershop.beehive.<>.dbinit.data.<>.FooBar \
         com.intershop.beehive.<>.dbinit.data.<>.FooBarLocalization

FooBarLocalization_en.properties
FooBarLocalization_de.properties
FooBarLocalization_<language>.properties

If no locale-specific bundle can be found an extra dbinit/dbmigrate fallback locale is used to get a suitable localized bundle before an unknown-localized base.properties resource bundle is used as last fallback.

The following property controls the dbinit/dbmigrate fallback locale handling, see IS_SHARE/system/config/cluster/dbinit.properties:

• intershop.dbinit.fallbackLocale=en_US, default: feature enabled and value en_US

Others

DBMigrate preparers can be recalled several times. They call the migrate() method of each preparer. DBMigrate preparers should reside in the *.dbmigrate.* packages.

The Java documentation has to document the usage in migration properties files. The migration preparers are derived from the class com.intershop.beehive.core.dbinit.capi.Preparer.

Golden Rules

1. Create always a new key=value entry into migration-to-<version>.properties for preparation steps during branch-development where version ranges (sub-numbering) exists and the affected migration steps are under construction (changes for: parameters, file content and code).
2. Verify if database changes are already done, and use semantic keys for this check.
3. Do not create UUIDs during the migration. Add and use static UUID properties files for the DBMigrate processing. A replication environment does not support different UUIDs on semantically identical objects.
4. Each migration preparer is "idempotent". That is, it has to be re-executable several times on the same database dump with the same configuration, whereby only the first run performs the changes.
5. Each preparer has to detect if it has been run before. This simplifies the troubleshooting if something goes wrong in the production system.
6. Return false or throw an exception on failure in order to give the migrator the possibility to decide whether the error should be ignored or not.
7. The SQLScriptPreparer does not allow ORA- errors during the execution of SQL scripts. Blind drops of tables, materialized views, etc. lead to failures.
8. Do not try to develop the single, all-purpose preparer – there is no such thing as the "Swiss army knife" of preparers.
9. Separate update, delete and insert statements in several preparers, e.g.: UpdateJobPreparer, DeleteJobsPreparer, AddJobsPreparer

How to Create and Get UUIDs

1. Open a development shell IS_HOME/bin/environment.bat.
2. Go to IS_HOME/tools/misc.
3. Call ant uuid.

Syntax:

ant uuid [-DamountUUID=number]

Example:

ant uuid -DamountUUID=2
createuuid:
  [echo] Generating an amount of 2 UUIDs...
  [java] UUID: 8rKsFt249DIAAAExssSBUPak
  [java] UUID: CKOsFt24gu8AAAExtMSBUPak

Available Preparer for DBInit and Migration Preparer for DBMigrate

To find all currently available preparers (DBInit and DBMigrate), check the installed Java documentation of the Intershop server installation.

1. For information on accessing Javadocs, see Support Article - How to Get the ICM Javadoc. 
2. Select a component (cartridge) and navigate to the cartridge-specific DBInit and DBMigrate preparers.
3. The DBInit and DBMigrate package naming is specified by com.intershop.*.<cartridge>.<dbinit|dbmigrate>.preparer.*.

Example of core cartridge:

Usage

The execution of DBInit and DBMigrate supports the execution in a non-interactive console-based mode called classic mode.

The following property controls the further preparer execution if an error occurs, see IS_SHARE/system/config/cluster/dbinit.properties:

• intershop.dbinit|dbmigrate.breakOnError=true|false, default false

The optional command line parameters for DBInit and DBMigrate are:

Example:
Success: bc_mvc:Class6          ...Preparer ...ms
Failure: bc_mvc:7.1.0.0:Class10 ...Preparer ...ms
Success: bc_preview_orm:Class1  ...Preparer ...ms

-h,--h,-help,--help,-?,?,/help,/h,/?

-v=<FLAG>,-verbose=<FLAG>,--verbose=<FLAG>

DBInit

Startup

1. Execute IS_HOME/bin/dbinit[.bat|.sh].

Preparer Configuration Files

The content of the (version-independent) dbinit.properties files looks like this:

pre.Class1   = com.intershop.beehive.core.dbinit.preparer.database.DatabaseTablesPreparer
...
Class1       = com.intershop.beehive.core.dbinit.preparer.database.SQLScriptPreparer \
               resources/core/dbinit/scripts/utctimestamp.sql
...
post.Class50 = com.intershop.beehive.core.dbinit.preparer.locking.RemoveAllProcesses

DBMigrate

Startup

1. Execute IS_HOME/bin/dbmigrate[.bat|.sh].

Preparer Configuration Files

Example: The content of the migration*.properties files looks like this:

pre.Class0.5 = com.intershop.beehive.core.dbmigrate.preparer.database.DatabaseTablesPreparer
pre.Class1   = com.intershop.beehive.core.dbmigrate.preparer.database.BackupDatabasePreparer
...
Class1 = com.intershop.beehive.core.dbmigrate.preparer.database.DatabaseTablesPreparer
Class2 = com.intershop.beehive.core.dbmigrate.preparer.database.ExecuteSQLScriptPreparer \
         resources/core/dbinit/scripts/spmainfile.ddl
Class3 = com.intershop.beehive.core.dbmigrate.preparer.database.DatabaseIndexesPreparer \
         resources/core/dbinit/scripts/dbindex.ddl
Class4 = com.intershop.beehive.core.dbmigrate.preparer.database.DatabaseConstraintsPreparer \
         resources/core/dbinit/scripts/dbconstraints.ddl
...
post.Class50 = com.intershop.beehive.core.dbmigrate.preparer.locking.ReleaseAllResources

Class1 = com.intershop.beehive.core.dbmigrate.preparer.job.AddJobsPreparer \
         root \
         com/intershop/beehive/core/dbinit/data/job/JobConfigurations.properties \
         com/intershop/beehive/core/dbmigrate/data/7.3.0.0/job/JobConfigurationUUIDs.properties

Class2 = com.intershop.beehive.core.dbmigrate.preparer.job.UpdateJobsPreparer \
         root \
         com.intershop.beehive.core.dbinit.data.job.JobConfigurations

Error Analysis

All log files reside in IS_SHARE/system/log. The general log files of preparers are:

- General log files: <loglevel>-*-migration|dbinit-<date>.log
- SQL log files: <IS_SHARE>/system/log/dbmigrate|dbinit-sql/*/*.log

The main log file of DBInit and DBMigrate tool is:

- dbinit-*.log
- migration-*.log

General Java debugging is possible with IS_HOME/bin/dbinit|dbmigrate[.bat|.sh]. Remove the REM or # before the debug setting, example:

SET DEBUG=-Xdebug -Xnoagent -Xrunjdwp:transport=dt_socket,server=y,address=6666,suspend=n -Xint

DEBUG="-Xdebug -Xnoagent -Xrunjdwp:transport=dt_socket,server=y,address=6666,suspend=n -Xint"

Example log files:

Startup DBInit (appserver mode DBInit)...
Startup finished in 34s
-------------------- pre-processing ---------------------
Success: core:pre.Class1 DatabaseTablesPreparer 1169ms
...
-------------------- main-processing --------------------
Success: core:Class1 SQLScriptPreparer [resources/core/dbinit/scripts/utctimestamp.sql] 194ms
...
-------------------- post-processing --------------------
Success: core:post.Class2 StagingEnvironmentPreparer [all] 0ms
...
-------------------- preparation summary ----------------
DBInit with 513 initialization steps (success: 513, failure: 0) finished in 1.037s

Startup DBMigrate (appserver mode DBMigrate)...
Startup finished in 51s
-------------------- pre-processing ---------------------
Success: core:pre.Class0.5 DatabaseTablesPreparer 548ms
Success: core:pre.Class1 BackupDatabasePreparer 12ms
...
-------------------- main-processing --------------------
Success: core:Class1 DatabaseTablesPreparer 538ms
Success: core:Class2 ExecuteSQLScriptPreparer [resources/core/dbinit/scripts/spmainfile.ddl,disableLogging] 334ms
...
-------------------- post-processing --------------------
Success: core:7.3.2.0:post.Class1 MigrateStagingEnvironment 17447ms
Success: core:post.Class50 ReleaseAllResources 3ms
...
-------------------- preparation summary ----------------
Failure: bc_mvc:7.1.0.0:pre.Class2 ...
Failure: bc_marketing:7.3.1.0:pre.Class1 ....
...
DBMigrate with 446 executed (success: 434, failure: 12) and 0 skipped migration steps finished in 515s

Properties File Formats

Some preparers support and/or require properties files that contain additional information. Usually, several data sets can be entered in this file, which are then processed iteratively.

Data.1.id   = Foo_ID
Data.1.name = Foo
Data.2.id   = Bar_ID
Data.2.name = Bar
Data.3.id   = FooBar_ID
Data.3.name = FooBar

The implementation of the preparers is not consistent here. Some collect all data sets to determine the different data set groups (Data.<>), others work per count variable and abort if there is a gap in the data set indexing. In the second case, if Data.2 did not exist or was commented out, Data.3 would not be reached and processed.