Using DdlUtils to Migrate Databases to Derby

DdlUtils, an Apache DB subproject, provides utilities for creating and working with Database Definition (DDL) files and for exporting and importing data in XML format via Ant tasks or programmatically in Java.

Among other uses, DdlUtils makes it easier to migrate from one database vendor to another. This paper provides a simple example for using DdlUtils to migrate a database to Derby using the DdlUtils Ant tasks.

This example features Derby as both the source and target database because Derby is easily available and is what the author knows, but it can be used with any of the databases that DdlUtils supports. Usually there's an even easier way to migrate a Derby database from one machine to another: just copy the Derby database files. If the source and target machines use official Derby releases you don't need to export/import the schema and data, not even for machines with different architectures, because the files that make up a Derby database are platform-independent. However, if you want to upgrade a Derby database to or from an alpha version of Derby to an official release, you must export the schema and data because Derby doesn't support upgrading an alpha database. DdlUtils makes this migration easier.

Target Audience

DdlUtils doesn't have an official release yet, so this example is intended for developers -- and for users who have a developer spirit. This project walks you through checking the DdlUtils code out of the Apache Subversion repository and building it using the Ant build tool. You don't need to be a java developer, in fact you don't need to know any Java at all, but you should be comfortable installing software, setting environment variables, and troubleshooting any problems that might occur.

Prerequisites

This project assumes a basic knowledge of Derby. If you're new, start with the on-line Apache Derby Tutorial. Familiarity with XML is helpful, but not necessary. You also need the software listed below:

• Install the most current official release of Apache Derby.
• Install Apache Ant version 1.6. Set the ANT_HOME environment variable and add the Ant bin directory to your PATH.
• Install Subversion and add the Subversion bin directory to your PATH.

Somewhere on your computer create a working directory, then cd into it. This is where you'll check out and build DdlUtils, create the Derby databases, etc. Also create a lib subdirectory; this is where you'll install the DdlUtils software.

mkdir DdlUtils-test cd DdlUtils-test mkdir lib

Check out the DdlUtils repository as shown below:

svn co http://svn.apache.org/repos/asf/db/ddlutils/trunk ddlutils

After this command completes, you should have a local working copy of the DdlUtils code in a subdirectory called ddlutils. Change directory into that subdirectory and build the DdlUtils jar file as shown below:

cd ddlutils ant jar

You might see some deprecation warnings in the build output, but don't worry about those as long as at the end you see a BUILD SUCCESSFUL message. The resulting jar will be output to a newly created dist subdirectory:

UNIX $ ls dist DdlUtils-1.0-dev.jar Windows C:\jta\DdlUtils-test\ddlutils>dir dist Volume in drive C has no label. Volume Serial Number is 8017-F8A8 Directory of C:\jta\DdlUtils-test\ddlutils\dist 03/10/2006 12:38 PM <DIR> . 03/10/2006 12:38 PM <DIR> .. 03/10/2006 12:38 PM 252,846 DdlUtils-1.0-dev.jar 1 File(s) 252,846 bytes 2 Dir(s) 66,830,884,864 bytes free

Copy files from the dist and lib directories into the lib directory you created in Step 1, then cd back up to your working directory:

UNIX cp dist/*.jar ../lib cp lib/*.jar ../lib cd .. Windows copy dist\*.jar ..\lib copy lib\*.jar ..\lib cd ..

This section creates the database that the Apache Torque Tutorial uses, but you'll use Derby's ij utility to create it instead.

Include derby.jar and derbytools.jar in your CLASSPATH (the on-line Derby tutorial shows how to do this), then create the database:

java org.apache.derby.tools.ij connect 'jdbc:derby:Books1;create=true';

Cut and paste the statements below to create the tables:

CREATE TABLE publisher ( publisher_id INTEGER NOT NULL, name VARCHAR(128) NOT NULL, PRIMARY KEY(publisher_id)); CREATE TABLE author ( author_id INTEGER NOT NULL, first_name VARCHAR(128) NOT NULL, last_name VARCHAR(128) NOT NULL, PRIMARY KEY(author_id)); CREATE TABLE book ( book_id INTEGER NOT NULL, title VARCHAR(255) NOT NULL, isbn VARCHAR(24) NOT NULL, publisher_id INTEGER NOT NULL, author_id INTEGER NOT NULL, PRIMARY KEY(book_id), FOREIGN KEY (publisher_id) REFERENCES publisher (publisher_id) , FOREIGN KEY (author_id) REFERENCES author (author_id) );

Cut and paste the statements below to insert some data:

insert into publisher values (1, 'Addison Wesley Professional'); insert into author values (11, 'Joshua', 'Bloch'); insert into author values (12, 'W.', 'Stevens'); insert into book values (101, 'Effective Java Programming Language Guide', '0-201-31005-8', 1, 11); insert into book values (102, 'TCP/IP Illustrated, Volume 1', '0-201-63346-9', 1, 12);

Finally, exit the database:

exit;

This step uses the DdlUtils DatabaseToDdlTask to dump the Books1 database to disk. You'll use the DdlUtils Ant tasks to execute the export.

Create an Ant build.xml file with an export target

Complete source for the Ant build file is below. Cut and paste it into a file called build.xml:

<?xml version="1.0"?> <project name="MigrateToDerby" basedir="."> <path id="classpath"> <fileset dir="./lib"> <include name="**/*.jar"/> </fileset> </path> <target name="export-source-db" description="Dumps db structure and data"> <taskdef name="databaseToDdl" classname="org.apache.ddlutils.task.DatabaseToDdlTask"> <classpath refid="classpath"/> </taskdef> <databaseToDdl modelName="MigrateTest"> <database url="jdbc:derby:Books1" driverClassName="org.apache.derby.jdbc.EmbeddedDriver" username="" password=""/> <writeSchemaToFile outputFile="db-schema.xml"/> <writeDataToFile outputFile="data.xml"/> </databaseToDdl> </target> </project>

Here are a few notes about the build.xml source:

• In Step 2 you copied all the jars from ddlutils/lib into the lib directory of the working directory. Rather than add them to the CLASSPATH variable, the Ant path element adds all jars in that subdirectory to the runtime environment. The path element is available to any target in this file; right now, there's just one target (export-source-db), but Step 5 will add another.
• The export-source-db target exports the schema and data from a database.
  • modelName specifies a name for the model and will be assigned to the name attribute in the generated schema file.
  • database url specifies all the connection parameters for the database, including the Books1 database. This example uses the Derby Embedded JDBC driver (derby.jar), but it could just as easily use the Derby Network Client JDBC driver (derbyclient.jar).
  • writeSchemaToFile exports the schema to a file called db-schema.xml.
  • writeDataToFile exports the data to a file called data.xml.

Execute the Ant task

Now execute the task with ant:

ant export-source-db

A BUILD SUCCESSFUL message indicates that that the export succeeded and there should be two new files in your current working directory:

• db-schema.xml contains the database schema in XML format.
• data.xml contains the data in XML format.

This step uses the DdlUtils DdlToDatabaseTask to import the schema and data into a new Books2 database. As with the export task, you'll use the DdlUtils Ant tasks to execute the import.

Copy and (maybe) modify the db-schema.xml file

The export step created a file called db-schema.xml. Copy that to a new file called project-schema.xml:

UNIX cp db-schema.xml project-schema.xml Windows copy db-schema.xml project-schema.xml

Since Derby is both the source and target databases in this example, no changes to the generated schema file are required. However, changes might be required if the source database is not Derby, for example, to change the mapping of a data type:

• Derby does not support the BOOLEAN type (yet). Use a SQL SMALLINT instead.
• A Derby user posted that he edited the generated schema file to change some LONG VARCHARs in the source database to a VARCHAR(32672) to retain search ability and others to CLOB to retain long text > 32,672 characters.

Add an import target to the build.xml

Copy the source for the import-target-db target below and add it to your build.xml file:

<target name="import-target-db" description="Creates db and loads data"> <taskdef name="ddlToDatabase" classname="org.apache.ddlutils.task.DdlToDatabaseTask"> <classpath refid="classpath"/> </taskdef> <ddlToDatabase schemaFile="project-schema.xml"> <database url="jdbc:derby:Books2" driverClassName="org.apache.derby.jdbc.EmbeddedDriver" username="" password=""/> <createDatabase failonerror="false"/> <writeSchemaToDatabase/> <writeDataToDatabase datafile="data.xml"/> </ddlToDatabase> </target>

Here are a few notes about the source:

• There is just one file for the schema, so it can be specified as an argument to the ddlToDatabase task using the schemaFile argument. You can also use an embedded fileset for multiple files -- the DdlUtils site shows an example using that syntax.
• The createDatabase subtask creates the Derby database.
• The writeSchemaToDatabase subtask creates the schema from the file specified by schemaFile.
• The writeDataToDatabase subtask loads the data from the specified data file.

Execute the Ant task

Execute the task with ant:

ant import-target-db

Successful execution should look something like this:

ant import-target-db Buildfile: build.xml import-target-db: [ddlToDatabase] Read schema file /home/jta/DerbyDb/DdlUtils/project-schema.xml [ddlToDatabase] Created database [ddlToDatabase] Mar 10, 2006 4:03:05 PM org.apache.ddlutils.platform.SqlBuilder alterDatabase [ddlToDatabase] INFO: Creating table AUTHOR [ddlToDatabase] Mar 10, 2006 4:03:05 PM org.apache.ddlutils.platform.SqlBuilder alterDatabase [ddlToDatabase] INFO: Creating table BOOK [ddlToDatabase] Mar 10, 2006 4:03:05 PM org.apache.ddlutils.platform.SqlBuilder alterDatabase [ddlToDatabase] INFO: Creating table PUBLISHER [ddlToDatabase] Mar 10, 2006 4:03:06 PM org.apache.ddlutils.platform.PlatformImplBase evaluateBatch [ddlToDatabase] INFO: Executed 5 SQL command(s) with 0 error(s) [ddlToDatabase] Written schema to database [ddlToDatabase] Read data file /home/jta/DerbyDb/DdlUtils/data.xml BUILD SUCCESSFUL Total time: 4 seconds

You should now have two databases.

One way to verify the schema in the target Derby database is to examine it with dblook:

java org.apache.derby.tools.dblook -d jdbc:derby:Books2 -o Books2_schema.sql

To verify the data was imported correctly, query the data in both databases:

ij> select * from publisher; ij> select * from author; ij> select * from book;

The DdlUtils model uses JDBC datatypes exclusively, which can be an issue for older drivers. Be sure to review DdlUtils database support on the DdlUtils site.

Please post questions about this example to derby-user <at> db.apache.org. Please post questions about DdlUtils to ddlutils-user <at> db.apache.org.