1. Introduction

1.1. Purpose

Pax Warp is a tool for managing relational database schemas and data sets in a system-independent format.

Pax Warp is implemented in Java 8. You can use it as a stand-alone tool via its command line interface, or embed it into Java EE 7 or OSGi applications, using the dependency injection framework of the given platform (i.e. CDI or Declarative Services).

1.2. Managing stuctural changes

The central concept of Pax Warp is a change log, representing a sequence of change sets, each containing a number of changes applied to a given database.

Starting from an empty database, you can set up the initial schema by elementary changes such as createTable, addForeignKey, createIndex etc.

As the schema evolves, you create additional change sets with changes like addColumn, dropTable etc. to transform the database schema into the desired state.

Thus, a change log captures the version history of a given database schema. When applying a change log to a database, Pax Warp tracks all applied change sets in a history table, skipping any change set that has been applied in a previous run.

Applying a change log to a database is an idempotent operation. When adding one or more change sets to a change log and applying the change log again, only the new change sets will be applied to the database.

1.3. Managing data sets

Besides representing structural changes, a change log can also represent a data set as a sequence of insert changes in a system independent way. Using Pax Warp, you can export data from a given database and import them into another database, even on a different database management system.

This is mainly intended to manage small data sets as fixtures for integration or system tests of applications supporting multiple database management systems. Pax Warp should not be used as a database backup tool.

1.4. Supported database systems

Pax Warp currently supports the following relational database management systems:

  • Apache Derby

  • H2

  • MariaDB

  • MySQL

  • Oracle

  • PostgreSQL

Any relational database with a JDBC driver can be supported by Pax Warp by adding a set of SQL templates and a new DBMS profile.

The Pax Warp binary distribution contains drivers for Derby, H2, MariaDB and PostgreSQL. The MariaDB driver can be used both for MariaDB and MySQL. The MySQL driver is not included to avoid ambiguities in driver selection.

The Oracle driver is not included due to incompatible licensing. To work with Pax Warp on Oracle databases, please register with Oracle to access Oracle’s Maven repository or to download the driver directly.

2. Getting Started

2.1. Stand-alone usage

Download the pax-warp-dist ZIP artifact from Maven Central and unpack it in a local directory of your choice. The top-level directory of the unpacked archive is called pax-warp-x.y.z, where x.y.z denotes the release version. We will refer to this installation directory as PAX_WARP_HOME.

The bin subdirectory contains launch scripts warp for Linux/Unix and warp.cmd for Windows.

Pax Warp requires a Java 8 Runtime Environment. The launch scripts will use the JAVA_HOME environment variable to locate the java command, falling back to the PATH variable if JAVA_HOME is not set.

All runtime dependencies of Pax Warp are contained in the lib and drivers subdirectories. The drivers directory contains JDBC drivers for the supported databases. You can replace a driver JAR by a different version if needed.

Running ${PAX_WARP_HOME}/bin/warp without arguments prints a synopsis of the command-line syntax and the supported commands:

Usage: warp [options] [command] [command options]
  Options:
        --help
       prints help
        --version
       prints version
  Commands:
   dumpAll      dumps all structure and data from a database
     Usage: dumpAll [options]
       Options:
             --output
            output file path
             --password
            JDBC password
       *     --url
            JDBC URL
             --username
            JDBC username
dumpData      dumps all data from a database
  Usage: dumpData [options]
    Options:
          --output
         output file path
          --password
         JDBC password
    *     --url
         JDBC URL
          --username
         JDBC username
dumpStructure      dumps a database structure, including tables, indexes and constraints, but no data
  Usage: dumpStructure [options]
    Options:
          --change-log
         change log file
          --password
         JDBC password
    *     --url
         JDBC URL
          --username
         JDBC username
importData      imports data from from a change log
  Usage: importData [options]
    Options:
          --change-log
         change log file
          --password
         JDBC password
          --url
         JDBC URL
          --username
         JDBC username
migrate      migrates a database, applying change sets from a change log
  Usage: migrate [options]
    Options:
          --change-log
         change log file
          --password
         JDBC password
          --url
         JDBC URL
          --username
         JDBC username

2.2. Embedded usage in Java EE

You can embed Pax Warp into your Java EE application, e.g. to run database migrations automatically when deploying your application.

To do so, simply include the following Maven dependency

    <dependency>
        <groupId>org.ops4j.pax.warp</groupId>
        <artifactId>pax-warp-core</artifactId>
        <version>${pax.warp.version}</version>
    </dependency>

in your build and inject org.ops4j.pax.warp.core.command.CommandRunner into one of your own beans.

The CommandRunner interface has several overloaded methods corresponding to the commands of the stand-alone application.

You can invoke Pax Warp methods with a JDBC URL, a Connection or a DataSource. Working with a data source, you can simply inject a corresponding @Resource defined in the container.

Since Pax Warp uses resource-local JDBC transactions, you cannot invoke CommandRunner methods in the context of a container-managed transaction. Annotate the calling method with @Transactional(NOT_SUPPORTED) if needed.

2.3. Embedded usage in OSGi

You can embed Pax Warp in your OSGi application by provisioning pax-warp-core, pax-warp-jaxb and a number of dependent bundles. Pax Warp requires Declarative Services for dependency injection. It will register a CommandRunner service in the service registry.

For more details about bundle dependencies and configuration, have a look at the Pax Exam integration tests in the itest-osgi module, e.g. org.ops4j.pax.warp.itest.CommandRunnerTest.

2.4. Maven plugin

Pax Warp provides a Maven plugin, with goals and parameters matching the command-line syntax of the stand-alone application. The Maven plugin has the following goals:

  • dump-all

  • dump-data

  • dump-structure

  • import-data

  • migrate

These goals are bound to the default lifecycle phase pre-integation-test.

This is an example configuration:

    <build>
        <plugins>
            <plugin>
                <groupId>org.ops4j.pax.maven</groupId>
                <artifactId>warp-maven-plugin</artifactId>
                <version>${pax.warp.version}</version>
                <configuration>
                    <url>jdbc:mysql://localhost/warp</url>
                    <username>warp</username>
                    <password>warp</password>
                    <changeLog>${project.build.directory}/warp.xml</changeLog>
                </configuration>
                <executions>
                    <execution>
                        <id>dump</id>
                        <phase>verify</phase>
                        <goals>
                            <goal>dump-structure</goal>
                        </goals>
                    </execution>
                </executions>
                <dependencies>
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>5.1.34</version>
                    </dependency>
                </dependencies>
            </plugin>
        </plugins>
    </build>

3. Change Log Reference

3.1. Overview

A change log is an XML document with namespace urn:org.ops4j.pax.warp:changelog defined in the XML schema xsd/warp.xsd located in the pax-warp-jaxb JAR.

A change log has a root element changeLog which contains any number of changeSet elements, each with a unique identity. Each change set contains a number of changes of different types. Change sets can be used to group related changes. Each change set is executed in a new transaction.

Note that some databases do not support transactions for DDL statements (or implicitly wrap each DDL statement in an auto-commit transaction, which amounts to the same thing).

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<changeLog xmlns="urn:org.ops4j.pax.warp:changelog" version="0.1">
    <changeSet id="1">
        <createTable tableName="strings">
            <column name="id" type="varchar" length="255" nullable="false"/>
            <column name="c4" type="char" length="4"/>
            <column name="c254" type="char" length="254"/>
            <column name="v4" type="varchar" length="4"/>
            <column name="v255" type="varchar" length="255"/>
            <column name="t" type="clob"/>
            <column name="enabled" type="boolean"/>
        </createTable>
    </changeSet>
</changeLog>

3.2. Shared types

3.2.1. Table attributes

Most changes refer to a single table referenced by a tableName attribute.

3.2.2. Generic SQL types

Pax Warp supports a number of generic SQL types which are mapped by a DBMS profile to the native column type of the given DBMS. The generic types are mapped as follows:

Pax Warp Derby H2 MariaDB, MySQL Oracle PostgreSQL

blob

blob

blob

BLOB

BLOB

bytea

boolean

boolean

boolean

BOOLEAN

NUMBER(1,0)

boolean

char

char

char

CHAR

CHAR

char

clob

clob

text

TEXT

CLOB

text

date

date

date

DATE

DATE

date

decimal

decimal

decimal

DECIMAL

DECIMAL

decimal

double

double precision

double precision

DOUBLE PRECISION

DOUBLE PRECISION

double precision

float

real

real

REAL

FLOAT

real

int8

smallint

tinyint

TINYINT

NUMBER(3,0)

smallint

int16

smallint

smallint

SMALLINT

NUMBER(5,0)

smallint

int32

integer

int

INT

NUMBER(10,0)

int

int64

bigint

bigint

BIGINT

NUMBER(19,0)

bigint

time

time

time

TIME

DATE

time

timestamp

timestamp

datetime

TIMESTAMP

TIMESTAMP

timestamp

varchar

varchar

varchar

VARCHAR

VARCHAR2

varchar

3.2.3. Column

A table column is specified by <column> element with the following attributes

Attribute Type Meaning

name

string

Column name

type

SqlType

SQL type of column

primaryKey

boolean

Is the column part of primary key?

length

int

Length (for char, varchar)

precision

int

Precision (for decimal)

scale

int

Scale (for decimal)

defaultValue

string

Default value

autoIncrement

boolean

Is this an autoincrement column?

nullable

boolean

Is this column nullable? (default: true)

3.3. Changes

The following sections list all supported changes with a brief description and an XML example. For more details, take a look at the XML schema warp.xsd.

3.3.1. Add column

Adds a column to a given table.

<addColumn tableName="numbers">
    <column name="n16" type="int16" nullable="true"/>
</addColumn>

3.3.2. Add foreign key

Adds a foreign key to a given table, referencing one or more columns of some other table.

<addForeignKey constraintName="fk_numbers_strings_numbers">
    <baseTable tableName="numbers_strings"/>
    <referencedTable tableName="numbers"/>
    <columnPair>
        <base columnName="number_id"/>
        <referenced columnName="id"/>
    </columnPair>
</addForeignKey>

3.3.3. Add primary key

Adds a primary key to a given table.

<addPrimaryKey tableName="numbers_strings">
    <column>number_id</column>
    <column>string_id</column>
</addPrimaryKey>

3.3.4. Create index

Creates an index on one or more columns of a given table.

<createIndex tableName="numbers" indexName="ix_i32_i64">
    <column name="i32"/>
    <column name="i64"/>
</createIndex>

3.3.5. Create table

Creates a table.

<createTable tableName="numbers_strings">
    <column name="number_id" type="int32" nullable="false"/>
    <column name="string_id" type="varchar" length="255" nullable="false"/>
</createTable>

3.3.6. Drop column

Drops a column from a given table.

<dropColumn tableName="numbers" columnName="n16"/>

3.3.7. Drop foreign key

Drops a given foreign key from a table.

<dropForeignKey tableName="numbers" constraintName="fk_numbers_strings"/>

3.3.8. Drop index

Drops a given index from a table.

<dropIndex tableName="numbers" indexName="ix_i32_i64"/>

3.3.9. Drop primary key

Drops a given primary key from a table.

<dropPrimaryKey tableName="numbers" constraintName="pk_numbers"/>

3.3.10. Drop table

Drops a given table.

<dropTable tableName="foo_bar" />

3.3.11. Insert

Inserts a data row into a given table.

<insert tableName="strings">
    <column name="id" type="VARCHAR">myid</column>
    <column name="c4" type="CHAR">abcd</column>
    <column name="c254" type="CHAR">foo</column>
    <column name="v4" type="VARCHAR" null="true"></column>
    <column name="v255" type="VARCHAR"/>
    <column name="t" type="CLOB">A long string</column>
    <column name="enabled" type="BOOLEAN">true</column>
</insert>

3.3.12. Rename Column

Renames a given column of a table.

<renameColumn tableName="strings" currentColumnName="t" newColumnName="txt" />

3.3.13. Rename Table

Renames a given table.

<renameTable currentTableName="foo" newTableName="foo_bar" />

3.3.14. Run SQL

Runs an arbitrary SQL statement, optionally only for a given list of suppported DBMSs.

Use with care, as this is not portable. The DBMS-independent actions are preferred.

<runSql dbms="h2, oracle">INSERT INTO ch4 VALUES (1)</runSql>

3.3.15. Truncate table

Truncates a given table, deleting all records, but preserving the empty table.

<truncateTable tableName="foo_bar" />