How to migrate an existing openCRX database

This guide explains how to migrate from an existing openCRX database to another database.

IMPORTANT: This guide assumes that openCRX 2.9 Server is successfully setup as described in openCRX 2.9.0 Server Installation Guide and openCRX 2.9 SDK is successfully setup as described in openCRX 2.9 SDK for Ant Step-by-Step.

openCRX provides the tools to migrate from an existing database to an other database

• DbSchemaWizard: The schema wizard allows to create or upgrade an openCRX schema for a given target database. The target database is specified with the JDBC connection URL, username and password.
• DbCopy: The database copy wizard allows to copy the openCRX data from a source database to a given target database. All rows of all tables are copied, so the DbCopy in fact performs creates a clone. Source and target database are specified by their JDBC connection URLs, usernames and passwords.

IMPORTANT: The wizards assume that the required JDBC database driver JARs are installed in the Apache TomEE directory apache-tomee-webprofile-1.0.0/lib/. The driver for HSQLDB is installed by default. However, the drivers for other databases must be installed manually. After installing the drivers restart openCRX.

Create the database schema

Start openCRX Server dad login as admin-Root. Launch the Database schema wizard as shown below.

Specify the JDBC connection URL, username and password for the target database.

Now click on Validate. The wizard checks the schema of the target database and reports any missing tables and views. It also checks the table columns and reports missing and extra columns.

For every reported problem the wizard also offers a fix in form a a database statement (CREATE TABLE ..., CREATE VIEW ..., ALTER TABLE ..., etc.). You can copy/paste the statement and run it in your favorite SQL Editor in order to update the database. To make things easier, the wizard offers the Validate & Fix function which runs the statements on the target database. In case of a database with an empty schema you have to run the Validate & Fix two or three times: In a first step it creates all tables, in a second step all dependent views and indexes. Finally, if everything goes well no errors should be reported.

Copy the database

If you want to migrate the data from an existing database to the new target database you can do it with the DbCopy wizard. Start openCRX Server and login as admin-Root.

Specify the JDBC connection URL, username and password of the source database and target database.

Then click Copy. The wizard shows to progress of the copy procedure. Clicking Refresh updates the progress output.

Troubleshooting

Insert failed. Reason: Data truncation: Data too long for column 'CONTENT' at row 1
statement=INSERT INTO OOCKE1_MEDIA  (OBJECT_ID, ACCESS_LEVEL_BROWSE, ACCESS_LEVEL_DELETE, ACCESS_LEVEL_UPDATE, CONTENT, CONTENT_MIME_TYPE, CONTENT_NAME, CREATED_AT, CREATED_BY_, P$$PARENT, MODIFIED_BY_, NAME, OWNER_, MODIFIED_AT, DTYPE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

This error occurs if the target column is too small to store the data of the source column. In this case increase the size of the target column using a native SQL Browser / Editor. In the case shown above the column CONTENT in the table OOCKE1_MEDIA must be redefined from BLOB to LONGBLOB using the MySQL Query Browser.

IMPORTANT: Only use the target database if the copy runs without errors.

Setup a database connector

Next configure the database connector which allows Apache TomEE to connect to your newly created database. For this purpose open the file {opencrxServer_installdir}/apache-tomee-webprofile-1.0.0/conf/openejb.xml and adapt the following section:

<Resource id="jdbc_opencrx_CRX" type="DataSource">
 JdbcDriver org.hsqldb.jdbcDriver
 JdbcUrl jdbc:hsqldb:hsql://127.0.0.1:9001/CRX
 UserName sa
 Password manager99
 JtaManaged true
</Resource>

according to your database settings.

PostgreSQL:

 JdbcDriver org.postgresql.Driver
 JdbcUrl jdbc:postgresql://localhost/CRX

MySQL:

 JdbcDriver com.mysql.jdbc.Driver
 JdbcUrl jdbc:mysql://localhost:3306/CRX

DB/2:

 JdbcDriver com.ibm.db2.jcc.DB2Driver
 JdbcUrl jdbc:db2://localhost:50000/CRX

Oracle:

 JdbcDriver oracle.jdbc.driver.OracleDriver
 JdbcUrl jdbc:oracle:thin:@localhost:1521:XE
 UserName CRX

SQL Server:

 JdbcDriver com.microsoft.sqlserver.jdbc.SQLServerDriver
 JdbcUrl jdbc:sqlserver://localhost:1433;databaseName=CRX;selectMethod=cursor

Also adapt correspondingly the openCRX launch script {opencrxServer_installdir}/apache-tomee-webprofile-1.0.0/bin/opencrx.sh and {opencrxServer_installdir}/apache-tomee-webprofile-1.0.0/bin/opencrx.bat. If required, disable the START and STOP commands for the HSQLDB database.

Start Apache TomEE

Now you are ready to start Apache TomEE. openCRX now connects to the newly created and populated database.