JPumpDX - Relational Data Migration with XML

JPumpDX is an effort to implement best practices in relational data migration. It uses XML as external data store. Table and record data can be migrated from one database to another, and from a database to XML and vice versa. Currently ten databases are tested.
You can use JPumpDX as INSERT and UPDATE tool.
The contained SQL dialect is configurable with XML. You possibly can adapt it to your database without coding Java, just by writing an XML dialect configuration.
Export and import behaviour is configurable via XML.
JPumpDX is written in Java. It builds on JDBC database connections, and on meta data delivered by JDBC. You need a Java with an XML parser (since 1.3). At the time it has no GUI, just a commandline migration tool.

This package is open source, published under Lesser GNU Public License (LGPL).
Thanks to all database manufacturers for making it possible to test JPumpDX with their products (although some products were too big for my harddisk).

Author: Ritzberger Fritz, May 2005

Download Page
Features
Terms
Motivation and aims
Quick start

Import
Export
Migration
Tool Syntax

Manual

Configuring JDBC connections
Table design only (using XML)
Record editing with table design (using XML)

Text and numbers
Binary content
Java code

Table and record migration

Java sample code

Establishing foreign keys
Configuration

Migration
Export
Import
Dialect

Developing an XML dialect configuration

Software Design

Java-XML binding
XML configurable SQL dialect
The SQL bridge
Data type mapping
Primary key generation and mapping
Source code hints

Javadoc

APPENDIX:

List of tested databases
Database Type Table
Database Meta Data Table

Features

SQL bridge via JDBC, providing

Record, Table and all related classes to handle instance data and meta data (Java-SQL layer)
CREATE SCHEMA, TABLE, SEQUENCE, INDEX
DROP SCHEMA, TABLE, INDEX, SEQUENCE
INSERT
UPDATE
DELETE
SELECT
Key generator statements (SEQUENCEs, AUTOINCREMENT columns)
Default key generator implementation (SequenceTable)

XML configurable SQL dialect
Editing data in XML without knowing the target database
Data migration

from XML to database
from database to XML
from database to database.

Automatic data type mapping, automatic value conversion
'Serial' record migration (does not fill memory with all records to migrate).
Supports

catalogs,
schemas,
tables,
generated and 'natural' primary keys (non-numeric),
indexes,
unique constraints
foreign key constraints.

Migration

renaming catalogs, schemas, tables, columns
excluding/including catalogs, schemas, tables, columns
excluding certain data types

Export

by catalog/schema/table selection
graph of related records
configurable via XML

Import

insert or update service
record existence check
deferred foreign key definition
primary key mapping with database-defined key generators
configurable via XML

Lightweight Java-XML binding (JAXB).
Tested with eight database products.

Terms

Definition of some terms used in implementation and this document.

Migration	Copying relational data to some other store (database or XML), including foreign key associations granting referential integrity. Used as the base term for both export and import.
Mapping: datatype-mapper, identifier-mapper, primarykey-mapper	Used in three different contexts: 1. The finding of a new data type for a source data type that the target database does not support. Typically this happens on BOOLEAN (see class DataTypeMapper). 2. Renaming catalogs/schemas/tables/columns on migration (see class DbIdentifierMapper). 3. Primary key mapping happens when key generator must be used on import. The old and new value of the key is stored into a Map to resolve foreign key values (see interface PrimaryKeyMapper).
Filter	Excluding or including certain schemas, tables, columns on migration. Neccessary for selective migration. See class DbIdentifierMapper.
Condition	An SQL-abstracted WHERE clause that reduces the number of records to be exported from a table. Configurable via XML on export.
Appender	When a JDBC driver does not report indexes and unique constraints correctly, you need to append them to retrieved MetaData. This can be done by XML configuration, using the elements UniqueConstraintAppender, IndexAppender and ForeignKeyAppender.
Meta data	Data about other data, needed to understand and process the referenced data. In this context this means catalogs, schemas, tables, constraints, indexes. Everything that must be in the database to enable record import. Everthing that is contained in JDBC DatabaseMetaData.
Instance data	Non-metadata, records and their column values. Instance data would include column values, but not their data types. To ensure consistency, JPumpDX instance data are always accompained by their meta data, on any level (Record, NamedValue) and in every context (XML, Java). So in fact they are not pure instance data, which makes these terms redundant.
Catalog	An SQL-92 term that defines a container for schemas. Here it is understood in the JDBC meaning, everything that is returned from DatabaseMetaData.getCatalogs() call. Some vendors call this "database" or "database instance". You always need vendor tools to create a catalog. Sometimes the catalog name must be included in the JDBC database URL. Old databases like Oracle use this term for other contexts. Some databases support catalogs but not schemas (MySQL), some support both (Frontbase, Daffodil), some schemas only (Oracle, McKoi, Pointbase) and some none (HSQL, InstantDB), for some this is not quite clear (Postgres, every created table appears in every catalog).
Schema	An SQL-92 term that defines a container for tables. Here it is understood in the JDBC meaning, everything that is returned from DatabaseMetaData.getSchemas() call. Many databases provide the SQL-92 command to create a schema. Nevertheless you might need privileges to do this.
Natural foreign key	One or more significant columns (with values) that characterize a record of a non-exported table. Needed to find that record in another database when a foreign key relation must be established. The best natural foreign key is a unique column that is not a key column (as key columns might get mapped). A natural foreign key includes a catalog/schema/table name, the column name in referencing table, referenced column names with their data types and values.
Precision	JDBC term, used in two different contexts: 1. Defines the length of character/binary data types, and the digits for numbers, including digits before and after decimal point, excluding the point itself. Scale gives the digits after the decimal point. JDBC even provides a radix to report this, most databases do this with radix 10. Sun has published recommendations for JDBC data type precisions. 2. Defines the actual maximum or fixed (CHAR) length of some character/binary fields, and the maximum digits for number fields. The better term would have been "maximum length".
Native	Here this means database- or product-native (not Java-native). For example, some databases provide only uppercase identifiers, some define a limit of 30 characters for identifiers, ...
Identifier	SQL and JDBC term that includes schema-, table-, index-, constraint- and column-names. Most databases provide identifier quoting to allow names that would conflict with SQL keywords, e.g. "ORDER".
Key recognizer	When keys are alphanumeric, TableImporter will not recognize them as mapable keys. When they must be mapped, providing an alphanumeric key generator by a PrimaryKeyMapper implementation, configuring a KeyRecognizer helps to mark these keys as mapable. Mind that this is not a workaround the fact that some relational models do not declare keys (either for performance tuning or because they are temporal).

Motivation and aims

Following was the motivation to write this library.

Having basic load data for development and production databases is a common necessity in projects. Mostly the data are packed into vendor-specific loader files, or in SQL files, wrapped into INSERT statements, hoping to have a utiltity that can parse that SQL and import the data when another database comes into sight. Having the load data in XML format and being able to load them into any database would be a nice alternative.

JPumpDX supports continuity and connectivity by providing an XML data format, and an SQL dialect configurable by XML. It supports most JDBC data types listed in java.sql.Types. Data of type ARRAY, DATALINK, REF, JAVA_OBJECT, NULL, OTHERS could work from database to database, but not with XML (except you implement the String-Object conversion).

These were the targets:

Being able to communicate with any database by JDBC and Java without using SQL statements - having a generic SQL bridge.
Retrieve data from any JDBC database and store them as XML.
Load data from XML into any database.
Being able to update database data from XML data.
Being able to copy tables and records from one database to another, even without using XML.
Migrate data of selected catalog/schema/tables/columns.
Migrate data that satisfy certain conditions. A graph of related records should be selectable.
Integrate key generators and perform primary key mapping on migration.

Quick Start

You need a Java that has an XML parser. Everything since JDK 1.3 will do it. No libraries except the JDBC drivers for your databases is required.

All necessary XML document type definitions are in dtds subdirectory in the distribution. They can be used to create the XML files passed as arguments to the application.

Write every needed JDBC connection into an XML file that looks like the following. You can load ConnectionParameters.dtd when using an XML editor.

<?xml version="1.0"?>
<ConnectionParameters
    Url="jdbc:mckoi://localhost:9158"
    DriverClass="com.mckoi.JDBCDriver"
    User="test"
    Password="test"
/>

Import (from XML)

Before you import any data, make sure that the target catalog and schema exist in the database. JPumpDX has a CREATE SCHEMA command, but there are many bases where this needs special privileges. Creating catalogs mostly is not possible without native tools, as catalog is a synonym for database and often appears in the JDBC URL.

Write your meta- and instance-data into a file that looks like the following. You can load DatabaseInstanceData.dtd when using an XML editor. The TypeId attributes must be good for your data values, but the TypeName need not match exactly what your database provides. The column type will be found using the TypeId when TypeName can not be found. You find a list of all possible JDBC TypeIds in the JDK reference of java.sql.Types, or in DialectConfiguration.dtd.

(Alternatively you can use one of the XML files in the sample directory.)

<?xml version='1.0'?>
<DatabaseData>

    <TableData>
        <Table Name='AirCraft' Schema='TEST'>
            <Column Name='id' TypeId='2' TypeName='BIGINT' PrimaryKey='true' Nullable='false'>
                <ReferencedBy Table='Flight' Column='aircraft_id' Schema='TEST' Constraint='fk_aircraft'/>
            </Column>
            <Column Name='name' TypeId='12' TypeName='VARCHAR' MaxLength='50'/>
            <Column Name='description' TypeId='12' TypeName='VARCHAR' MaxLength='250'/>
        </Table>
        <Rec>
            <Nv Name='id'>1</Nv>
            <Nv Name='name'>DC-10-A</Nv>
            <Nv Name='description'>DC-10 Passenger Plane</Nv>
        </Rec>
</TableData>

    <TableData>
        <Table Name='Flight' Schema='TEST'>
            <Column Name='id' TypeId='2' TypeName='BIGINT' PrimaryKey='true' Nullable='false'/>
            <Column Name='number' TypeId='12' TypeName='VARCHAR' MaxLength='50'/>
            <Column Name='from' TypeId='12' TypeName='VARCHAR' MaxLength='150'/>
            <Column Name='to' TypeId='12' TypeName='VARCHAR' MaxLength='150'/>
            <Column Name='departs' TypeId='93' TypeName='TIMESTAMP'/>
            <Column Name='arrives' TypeId='93' TypeName='TIMESTAMP'/>
            <Column Name='aircraft_id' TypeId='2' TypeName='BIGINT'>
                <ReferenceTo Table='AirCraft' Column='id' Schema='TEST' Constraint='fk_aircraft'/>
            </Column>
        </Table>
        <Rec>
            <Nv Name='id'>2</Nv>
            <Nv Name='number'>EN-15</Nv>
            <Nv Name='from'>Sheffield, England</Nv>
            <Nv Name='to'>Isle Of Wight, England</Nv>
            <Nv Name='aircraft_id'>1</Nv>
            <Nv Name='departs'>2001-09-07 10:45:00:000 CEST</Nv>
            <Nv Name='arrives'>2001-09-07 13:25:00:000 CEST</Nv>
        </Rec>
</TableData>

</DatabaseData>

Now write following commandline (syntax is: from - to), assuming you want to import to schema APP.
You must have jpumpdx.jar and the JDBC driver JAR in your CLASSPATH, e.g.: java -cp jpumpdx.jar;mckoidb.jar ...

java -cp ... fri.util.database.tools.Migration-xml importdata.xml -db connection.xml -schema APP

The data will be imported into the database, tables get created when not existing, foreign keys get established when tables did not exist. When tables and records exist, they will be updated.

Export (to XML)

Write following commandline (syntax is: from - to), assuming you want to export the schema APP.

java -cp ... fri.util.database.tools.Migration-db connection.xml -schema APP-xml exportdata.xml -schema APP

The data of all catalogs/schemas/tables/columns in the database will appear in exportdata.xml. Surely you can select certain schema(s) or table(s) using the -schema option. See below for syntax.

Migration (database to database)

Write following commandline (syntax is: from - to), assuming you want to copy the schema APP to another database into schema TST:

java -cp ... fri.util.database.tools.Migration-db source-connection.xml -schema APP -db target-connection.xml -schema TST

All catalogs and schemas from source database will be copied to target database. You can specify a certain catalog/schema/table by using the appropriate options (-catalog, -schema, -table), or by using export- and import configuration (-config, see manual).

Tool Syntax

The Migration tool can quickly migrate from database to database, from XML to database, or from database to XML. It can optionally migrate a selected catalog/schema/table, when not given, it migrates all catalogs/schemas/tables. The migration can even be configured by XML. All XML must be in files.

This is the full syntax of the migration tool ('copy from - to'):

java -cp jpumpdx.jar;jdbcDriver.jar fri.util.database.tools.Migration
       { -xml instanceData.xml | -db connectionParameters.xml }
           { [-catalog CATALOGNAME] [-schema SCHEMANAME] [-table TABLENAME] } | [-config exportConfig.xml [-graph]]
       { -db connectionParameters.xml | -xml instanceData.xml }
    { [-catalog CATALOGNAME] [-schema SCHEMANAME] [-table TABLENAME] } | [-config importConfig.xml]

The source (from) can be XML or database, the target (to) can be XML or database. XML for both source and target will not be accepted. One catalog, schema and table can be passed as selection. Alternatively a compound XML configuration can be given. Use the -graph option for exporting a graph of related records, which will be built from contained table conditions.

IMPORTANT:
The -catalog and -schema options perform (1) source selection and (2) target mapping of catalog/schema names. Mapping to null is possible. So when you map an export from schema APP to null in XML, no schema name will appear in XML. When you re-import the data, you must map them to an target schema, else the import will fail! So when exporting schema APP, and when you want the schema to appear in XML, you must write the -schema option two times: -db connection.xml -schema APP -xml exportdata.xml -schema APP

You find DTD's for all configuration XML files in the dtds subdirectory in the distribution (-config option).
Do not forget to put the JDBC driver JAR into CLASSPATH!

To drop your test tables, there is another tool:

java -cp jpumpdx.jar;jdbcDriver.jar fri.util.database.tools.DropTables
[-catalog CATALOGNAME] [-schema SCHEMANAME] [-table TABLENAME]

Be careful with -table option. You can not drop a single table from the database that others depend on. SqlBridge relies on compound dropping in dependency order. It does not check if the dropped table is referenced by others, it just drops the foreign key constraints to others. Else it would have to set foreign keys to null, and if these are mandatory, this would cause an error.

When errors occur, you might work on a dialect for your database. Here is the tool to do this:

java -cp jpumpdx.jar;jdbcDriver.jar fri.util.database.tools.CheckDataTypes -db ConnectionParameters.xml [-dialect DialectConfiguration.xml]

This tests all supported JDBC data types by creating a table with one column that has a certain data type, inserting a record and reading it back. The created data type is checked (if it is the given one or a compatible), the read value is checked for equality. If no dialect is passed, the generic Dialect is used. It is quite difficult to realize the resulting errors. The tool proposes a dialect configuration at end, but do not take this serious, the errors are too subtile.

Hope this works! When not, its time to look at the Manual.

Manual

JPumpDX is a database administration tool, running on JDBC. You can load databases with data and/or metadata from XML, you can export database data and/or metadata to XML, and you can migrate from one database to another. You can use JPumpDX either as blackbox tool, doing basic things by commandline and XML configuration, or you can use it as API to implement special migration logic or dialects. Several capabilities like establishing MetaData, defining PrimaryKeyMappers or Dialect configurations are not available via commandline. For API usage you should also read the Design chapters, and the JavaDoc of course.

Configuring JDBC connections

The well-known connection parameters for JDBC are:

Database URL
Driver class name
User (optional)
Password (optional)

These can be written and read from XML, the DTD is ConnectionParameters.dtd, and a connection looks like mentioned above.
Write this to a file named myConnection.xml, and pass it then as argument to the commandline Migration tool.

Or, coding Java, you can read this into an Java object by

ConnectionParameters connectionParameters = (ConnectionParameters)
JaxbObjectFactory.getFileInstance("myConnection.xml", ConnectionParameters.class);

You can allocate an SqlBridge directly using the XML file:

SqlBridge sqlBridge = SqlBridgeFactory.getInstance("myConnection.xml"); // could be already in use

SqlBridge sqlBridge = SqlBridgeFactory.newInstance(new File("myConnection.xml")); // ensure new connection

Do not forget to put the JDBC driver JAR into CLASSPATH! Some databases have their own JDBC driver in some product directory (Oracle, DB2, ...), some have open source drivers (MySQL, Postgres, MS SQL Server (jtds.sourceforge.net). Go to the Sun JDBC page to find your driver.

Table design only (using XML)

You can design your tables database-independently with XML. The DTD for table metadata is DatabaseMetaData.dtd. Use this when you want to define just meta data. Do not use this when you want to define records, too.

You do not have to care for import dependency order, as foreign keys get established at end of import. But when tables already exist, or when records are contained (using DatabaseInstanceData.dtd), you should write your data in dependency order. That means, when Flight depends on AirCraft by referencing it with a foreign key, AirCraft must be defined before Flight definition.

<?xml version='1.0'?>
<Database>
        <Table Name='AirCraft'>
            <Column Name='id' TypeId='2' TypeName='BIGINT' PrimaryKey='true' Nullable='false'>
                <ReferencedBy Table='Flight' Column='aircraft_id' Constraint='fk_aircraft'/>
            </Column>
            <Column Name='name' TypeId='12' TypeName='VARCHAR' MaxLength='50'/>
            <Column Name='description' TypeId='12' TypeName='VARCHAR' MaxLength='250'/>
        </Table>

        <Table Name='Flight' >
            <Column Name='id' TypeId='-5' TypeName='BIGINT' PrimaryKey='true' Nullable='false' />
            <Column Name='number' TypeId='12' TypeName='VARCHAR' MaxLength='50'/>
            <Column Name='from' TypeId='12' TypeName='VARCHAR' MaxLength='150'/>
            <Column Name='to' TypeId='12' TypeName='VARCHAR' MaxLength='150'/>
            <Column Name='aircraft_id' TypeId='-5' TypeName='BIGINT'>
                <ReferenceTo Table='AirCraft' Column='id' Constraint='fk_aircraft'/>
            </Column>
            <Column Name='departs' TypeId='93' TypeName='TIMESTAMP'/>
            <Column Name='arrives' TypeId='93' TypeName='TIMESTAMP'/>
    </Table>

</Database>

The TypeId attribute is mandatory and must be good for your data values. You can find a list of possible numeric values in JDK reference of class java.sql.Types, or in DialectConfiguration.dtd. The TypeName attribute needs not to match the type in your database exactly, it is optional to force a special product data type. All names will be converted to the database products naming conventions. This influences upper/lower case, and the length of identifiers. If the database supports mixed case, the names will remain as they are. If the table name is too long for the product, it will be cutten.

Every association is given bidirectionally by defining ReferencedBy and ReferencedTo in the table holding the foreign key. Some databases do not support associations across schemas, but it is possible to define it here.

And this is the Java code that establishes the MetaData within the database:

String sourceCatalog = null;    // assumethe catalog is not defined in XML
String sourceSchema = null;    // assume the schema is not defined in XMLString targetCatalog = "users";    // assume database has such a catalog
String targetSchema = "test";    // assume database lets create a schema or has such a schema
ImportConfiguration importConfig = new ImportConfiguration();
importConfig.getDbIdentifierMapper().getCatalogMapper().setMapping(sourceCatalog, targetCatalog);
importConfig.getDbIdentifierMapper().getSchemaMapper().setMapping(sourceSchema, targetSchema);
String fileName = "MyMetadata.xml";
SqlBridge sqlBridge = new SqlBridge(url, driverClass, user, password);try    {    new XmlToMetaData(fileName, sqlBridge, importConfig);
} finally    {
    sqlBridge.close();}

Record editing with table design (using XML)

Text and numbers

You should mix table metadata with instancedata (records) using DatabaseInstanceData.dtd. Doing so, you will not need to define MetaData separately. The Table element is the same as in DatabaseMetaData.dtd.

<?xml version='1.0'?>
<DatabaseData>

    <TableData>
        <Table Name='AirCraft'>
            <Column Name='id' TypeId='2' TypeName='BIGINT' PrimaryKey='true' Nullable='false'>
                <ReferencedBy Table='Flight' Column='aircraft_id' Constraint='fk_aircraft'/>
            </Column>
            <Column Name='name' TypeId='12' TypeName='VARCHAR' MaxLength='50'/>
            <Column Name='description' TypeId='12' TypeName='VARCHAR' MaxLength='250'/>
        </Table>

        <Rec>
            <Nv Name='id'>1</Nv>
            <Nv Name='name'>DC-10-A</Nv>
            <Nv Name='description'>DC-10 Passenger Plane</Nv>
        </Rec>
        <Rec>
            <Nv Name='id'>2</Nv>
            <Nv Name='name'>DC-10-B</Nv>
            <Nv Name='description'>DC-10 Passenger Plane</Nv>
        </Rec>

</TableData>

</DatabaseData>

Rec stands for record, Nv for named value (these abbreviations save a lot of space). Natural foreign keys are named Nfk, their named values Fnv (foreign named value).

Numbers must be written with "." as decimal point, national symbols are not supported. Double.valueOf() is called to decode decimals from XML text, Double.toString() to encode them. See AbstractNamedValue overrides on convertStringToObject() and convertObjectToString(), and their base implementations in AbstractReflectionXmlElement for learning more about value conversion.

If the tables already exist, it is possible to write the records as follows. But keep in mind that you depend on a database when doing so! It is better to keep metadata and instancedata in one place. Further it is not possible to construct a regular TableData object from such an XML file (without importing these data into a database), as the record values do not know its data type then, an can not be converted to Java objects.

<DatabaseData>
    <TableData>
        <Table Name='AirCraft' />
        <Rec>
            <Nv Name='id'>1</Nv>
            <Nv Name='name'>DC-10-A</Nv>
            <Nv Name='description'>DC-10 Passenger Plane</Nv>
        </Rec>

Binary content

When you need to put a binary content into the database, you need a BINARY, VARBINARY, LONGVARBINARY or BLOB column. You can write the data for that column into the XML file by using Base-64 encoding. JPumpDX provides a tool to convert a binary file into a Base-64 encoded file. Take the contents of the converted Base-64 data and paste it into the XML tag. Take care to not add any space or newline (except the normal closing newline of Base-64).

The syntax is "convert from - to" (text to binary):

java fri.util.database.tools.Base64Converter -txt base64file -bin binaryfile

or (binary to text):

java fri.util.database.tools.Base64Converter -bin binaryfile -txt base64file

This tool will never overwrite an existing file, you got to remove this before.

After pasting the contents of the textfile into XML your data will look similar to this:

<Nv Name='image'>jnicdrhSlejNde1KVllnsnT4s+M7TPE5h9RsmjlS0WeMoUzjfizhkUlwtYf94Z8/2WtH9l80klth
/J3+V+0SdB7q0f4SSWkumEBjlC/7RnzSSRHoT4Kb7J3sq0H2ZSSRLo4/klx/7tH/ANg/ZSpJIArz
/EE1v2aSSaJlxDI+ZViPkEkk5DiMPMqF6SSS6N8KbPtJv/c/gKRv23ySSQxR4Wj8bPmndEklK6Ni
HJa3CPgd7pJLm8z/AFGuD9kXaT/pp91yTeSSS8zB9OjN0//Z
</Nv>

Java code

Now

Following is the Java code to import manually written XML data into a database:

Table and record migration

You can use the Migration commandline utility to migrate meta data and records: see Quickstart - Tool Syntax.

The basic migration behaviour of JPumpDX is as follows:

On export:

Tables get sorted by foreign key dependency as far as possible.
Records in self-referencing tables get sorted by foreign key dependency as far as possible.
To grant consistency, record instancedata always are headed by their table metadata.
References to non-exported tables are stored into the record as "natural foreign key" values of the non-exported table (unique columns, or combination of all columns except LOBs and decimal numbers; the reason why decimal numbers are not integrated are possible precision leaks, but this is configurable).
All identifiers and data type names, precisions and other informations stay as they came from metadata, except when some explicit mapping is performed via configuration.

On import:

When getting the table to create or to do INSERT/UPDATE on, perform explicit mappings via configuration (setting the target catalog/schema etc).
Making table identifiers "native" means converting case, setting catalog/schema to null if the database does not support such, etc.
When an import table does not exist, create it, map data types to natives before.
Making the arriving record "native" means converting attribute names case, converting values to other JDBC types, etc.
Put mapped foreign key values into the record about to be imported when a PrimaryKeyMapper was configured.
Resolve "natural key" references to non-exported tables.
Look if the record exists. This is done when the table was not created. When no PrimaryKeyMapper is configured, the primary key is used to find the existing record. Else any unique column is used for the query. If no unique constraint exists, the combination of all columns except LOBs and decimal numbers are used to find the record (see "natural keys" above).
When the record does not exist:

optionally provide a generated primary key, or do anything necessary for autoincrement;
insert it;
optionally, for autoincrement, retrieve the generated key value;
optionally map the primary key value to the newly generated one.

When the record exists, update it if it is not equal to the one to be imported. Map its primary key to the one imported.
After all tables have been imported,

try to resolve foreign key values that could not be evaluated because tables were cyclic dependent, and
establish foreign key constraints for those tables that were created.

Currently there is no automatic DELETE service implemented. JPumpDX will never delete a record. Use the SqlBridge API for that.

Most of the responsibilities are implemented in TableExporter and TableImporter class. TableImporter gets any Table within setTable(Table) call, and any Record for that Table in addRecord(Record) call. The same is with TableExporter and getTable() and getRecord(). Please read the source to learn more about migration logic.

Java sample code

Database to database:

SqlBridge sourceSqlBridge = SqlBridgeFactory.newInstance(new File("sourceConnection.xml")); SqlBridge targetSqlBridge = SqlBridgeFactory.newInstance(new File("targetConnection.xml")); String sourceCatalog = "myCatalog";   // assume such a catalog exists in source DB String sourceSchema = "mySchema";   // assume such a schema exists in source DB
    ExportConfiguration exportConfig = new ExportConfiguration();     exportConfig.getDbIdentifierMapper().getCatalogFilter().include(sourceCatalog); // export only "myCatalog"    exportConfig.getDbIdentifierMapper().getSchemaFilter().include(sourceSchema); // export only "mySchema"
    ImportConfiguration importConfig = new ImportConfiguration();     importConfig.getDbIdentifierMapper().getCatalogMapper().setMapping(sourceCatalog, null); // assume target DB does not support catalogs    importConfig.getDbIdentifierMapper().getSchemaMapper().setMapping(sourceSchema, "test"); // assume target DB has such a target schema
importConfig.setPrimaryKeyMapper(targetSqlBridge.createPrimaryKeyMapper());    // perform primary key mapping
    try    {         new InstanceDataToInstanceData(sourceSqlBridge, targetSqlBridge, exportConfig, importConfig); }    catch (Exception e)    {        targetSqlBridge.rollback(); throw e; } finally    {         sourceSqlBridge.close();        targetSqlBridge.close(); // includes commit    }

Database to XML:

SqlBridge sqlBridge = SqlBridgeFactory.newInstance(new File("myConnection.xml")); String fileName = "myInstancedata.xml"; String catalog = "myCatalog"; String schema = "mySchema"; ExportConfiguration exportConfig = new ExportConfiguration(); exportConfig.getDbIdentifierMapper().getCatalogFilter().include(catalog); // export only "myCatalog" exportConfig.getDbIdentifierMapper().getSchemaFilter().include(schema); // export only "mySchema" exportConfig.getDbIdentifierMapper().getCatalogMapper().setMapping(catalog, null); // no catalog will appear in XML exportConfig.getDbIdentifierMapper().getSchemaMapper().setMapping(schema, null); // no schema will appear in XML
try { new InstanceDataToXml(new FileOutputStream(fileName), sqlBridge, exportConfig); } finally { sqlBridge.close(); }

XML to database:

String sourceCatalog = null;    // assume the catalog is not defined in XML
String sourceSchema = null;   // assume the schema is not defined in XML
ImportConfiguration importConfig = new ImportConfiguration();
importConfig.getDbIdentifierMapper().getCatalogMapper().setMapping(sourceCatalog, "users");// assume DB has such a catalog
importConfig.getDbIdentifierMapper().getSchemaMapper().setMapping(sourceSchema, "test"); // DB lets create schemas or has such one
SqlBridge sqlBridge = new SqlBridge(url, driverClass, user, password);
importConfig.setPrimaryKeyMapper(sqlBridge.createPrimaryKeyMapper());    // perform primary key mapping
String fileName = "MyInstancedata.xml";
try    {
    new XmlToInstanceData(fileName, sqlBridge, importConfig);
}
finally    {
    sqlBridge.close();
}

For porting meta data, the classes

MetaDataToMetaData,
MetaDataToXml and
XmlToMetaData

exist. Arguments and configurations are the same as with instancedata. Mind that you can do many more things with ExportConfiguration and ImportConfiguration.

Establishing foreign keys

Relational migration is a complicated thing, primarily because of table associations by foreign keys. There are a lot of cases to consider.

Primary key mapping with key generators and autoincrement columns.
Primary keys could also be foreign keys, to ensure that certain key values are the same as those in some other table. This has consequences for primary key mapping.
Associations could be cyclic, even within one table, when it has self-references.
There could be tables that have non-numeric key associations mixed with such that have numeric keys from a SEQUENCE or AUTOINCREMENT column declaration.
Foreign key constraints sometimes are not reported by the JDBC driver DatabaseMetaData.
Unique constraints, important to identify an existing record, sometimes are not reported by the JDBC driver DatabaseMetaData.

It would fill a lot of space to discuss all these topics and their solutions in JPumpDX. Please read the source of TableImporter and TableExporter to learn more about this.

Configuration

Like the data format, all configurations happen using XML. You find the needed DTDs in the dtds subdirectory in the distribution. They should be well-documented and easy to understand. In Java you use ExportConfiguration/ExportGraphConfiguration and ImportConfiguration classes, and their inner classes.

Migration

When porting data from one database to another, or from a database to XML or vice versa, a lot of settings can be used to customize the migration behaviour. The migration will never delete a record, so 'move' is not supported, only 'copy'. Nevertheless you can use the SqlBridge to delete records programmatically. You can even drop tables (or schemas, when the database supports this). Or you might use the method TableExporter.deleteExportedRecords() (experts, needs programming/overriding).

When copying data, you possibly would like to

do some catalog/schema/table/column renames ("mapping"),
migrate only selected catalogs/schemas/tables/columns ("include filter"),
ignore several tables or columns ("exclude filter"),
ignore columns of certain data types

Both import and export have these facilities. This is done by using DbIdentifierMapper, which is available within ImportConfiguration or ExportConfiguration (both extending MigrationConfiguration). The "filter" options works exclusive, when includes are defined, no excludes are possible. Wildcards are not supported at the time.

Further you would like to append manually written

unique constraints that were not contained in some export data (as JDBC drivers do rarely report them correctly)
indexes (mostly contained, but could be missing)
foreign key constraints (mostly contained, but could be missing)

Both import and export have these facilities. This is done by using UniqueConstraintAppender, IndexAppender and ForeignKeyAppender.

Import

Look at ImportConfiguration.dtd. You install the import configuration on construction of one of the classes XmlToInstanceData or InstanceDataToInstanceData. Custom primary key mappers can be installed in ImportConfiguration.

On import, you would like to

deny the automatic check for record existence (increases performance),
perform primary key mapping, using some database-native key generator (sequences, autoincrement columns)
deny primary key mapping for certain tables (that have fixed primary key values),
deny the import when one of the foreign keys of a record can not be evaluated from existing data (do no import when it is null).

This is done by using RecordExistenceCheck, NoPrimaryKeyMappingTable and NoInsertWhenForeignKeyNotEvaluable.

The primary key mapping is done by one of the three default key generator implementations that implement the interface PrimaryKeyMapper, or you can install your own implementation using ImportConfiguration (done by coding Java, this can not be configured using XML).

The SqlBridge provides one of the default implementations according to the XML settings of the dialect (SqlBridge.createPrimaryKeyMapper()). But the implementation has to be set into the MigrationConfiguration programmatically, else no mapping will happen. The Migration tool does this by default, so primary key mapping always happens when using this tool.

Furthermore it is possible to customize the identifiers and design of SequenceTable by overriding TableSequencePrimaryKeyMapper (when you must be compatible to some JDO mapper).

Export

Look at ExportConfiguration.dtd, or ExportGraphConfiguration.dtd. You install the export configuration on construction of one of the classes InstanceDataToXml or InstanceDataToInstanceData.

On export, you would like to

export references to non-exported tables, done by "natural foreign keys"
explicitely name the foreign attributes to be used for a natural foreign key reference to a non-exported table (if no uniques exist or combination of all columns is not possible)
export only certain records (that match a WHERE condition) from some tables

This is done by using NaturalKeyReferencedTable, ReferencedColumnsCsvList and TableCondition. Mind that there is no SQL contained in TableCondition. You have to mask XML meta characters. You can use the key-value comparison operators

=, <>, <, >, <=, >=, IN, NOT IN, LIKE, NOT LIKE, startsWith, endsWith

which can be logically associated by

AND, OR, AND NOT, OR NOT

Additionally you might like to

export a graph of records associated by foreign keys

When you want to export a graph of related records, you can do this using ExportGraphConfiguration. This is nearly the same as ExportConfiguration, except the condition flags TopDownBindingCsvColumns (binding records that an exported one references, can be as much as foreign keys exist) and BottomUpBindingCsvColumns (binding records that reference an exported one, can be as much as foreign keys referencing that table). These are like "JOIN" conditions. You have a sample XML config in samples/configs/FlightGraphConfiguration.xml.

Default "top down" binding is set to all foreign keys ("*"), which means that every referenced record will be exported (when its table is among exported ones). No bottom-up binding will happen by default. This favours hierarchies of records. When a record references a record in another table, and that record is is excluded by the Condition on that table, it will not be exported when that Condition has an AND operator, and it will be exported when it has an OR operator, or none (default OR is assumed). Then the foreign key will be set to null on import, except when you configure DoInsertWhenForeignKeyNotEvaluable to false on import (then it will not be imported, which again can cause dependent records not to be imported).

When binding top-down, you can restrict the hierarchy to certain foreign keys by using TopDownBindingCsvColumns, where you can name the foreign key attributes that should perform binding, e.g. for table "Ticket":

TopDownBindingCsvColumns="customer_id, flight_id"

The same is for BottomUpBindingCsvColumns, except that you must name the foreign key attributes of the foreign table (as several tables could point to the primary key of a table). You do this by naming these foreign keys with "qualifed" name, which means "catalog.schema.table.column", e.g. for table "Customer"

BottomUpBindingCsvColumns="APP.Ticket.customer_id, APP.WaitingList.customer_id"

(names separated by dot, leave out catalog and schema when the database does not support such). You can use the wildcard "*" for all references.
The strategy the export configuration performs then is propagation. The exported tables are looped until no more propagation happens. Within that loop the records of any table that has a condition are read, and their foreign keys (or primary key for bottom-up binding) are propagated to the peer tables, which means they get a condition. Recursively those tables then read their records and propagate their configured bindings to other tables. When key values are propagated that aleady are read, no new propagation takes place. A table that gets no condition will not be in export data.

Dialect

To provide connectivity to every database without programming efforts, the SQL dialect implementation is configurable via XML. Look at the well-documented DTD DialectConfiguration.dtd. Here is the Java code to register a dialect with a new XML configuration:

String dialectName = "sql_server"; // must be contained case-insensitive in product nameString xmlConfigFile = "mydialects/sql_server.xml"; // your dialect configuration
ConfiguredDialectFactory.singleton.registerDialect(new ConfiguredDialect(dialectName, xmlConfigFile));

When there is a problem that is not covered by the XML configuration, you can extend ConfiguredDialect (or Dialect) and register the new dialect at the factory:

Dialect myDialect = new ConfiguredDialect("mysql", "mydialects/mysql.xml") {
// do some overrides
};
ConfiguredDialectFactory.singleton.registerDialect(myDialect);

Every dialect must have a no-argument constructor. When the dialect was registered, the SqlBridge can instantiate it using ConfiguredDialectFactory. The factory finds it by the product contained in MetaData that get passed by SqlBridge. The factory will instantiate the dialect, calling the no-arg constructor. The registered dialect will never be used, it's only a "template".

The XML configuration is mainly to fix bugs in JDBC MetaData. You need not to write anything that is correctly in MetaData. When all JDBC drivers would deliver correct metadata, this configuration would be dispensable. Following things you can customize with XML dialect configuration (see DialectConfiguration.dtd in the dtds subdirectory for details):

Database specifics like

if it supports catalogs, schemas, sequences, autoincrement columns,
the identifier quote character,
wrong data type informations in MetaData,
data type name mapping (when migrating from one database to another),
precision limits for number, character and binary fields,
retrieval of ResultSet values that do not implement java.sql interfaces,
what are the system catalogs/schemas to be ignored,
the maximum length of identifiers (table names, column names, ...),
some required data type for key columns,
must trailing spaces be removed from CHAR column values
automatic generation of indexes on all key columns (MySQL)
hit a certain database version with that dialect
...

SQL specifics like

having to use CASCADE or RESTRICTon drop statements,
creating and dropping schemas (creating catalogs is not supported by most databases),
creating autoincrement columns,
creating and dropping sequences,
creating indexes,
how to get the next sequence or autoincrement column value,
where to use catalog and schema in statements and where not,
inserting on tables with autoincrement columns (set column to null, or must leave out column on INSERT),
if indexes or constraints can be dropped,
...

To get the flavor, here ist the DTD without comments, see in the dtds subdirectory for the full version. Every element and attribute is documented there.

<!ELEMENT DialectConfiguration (Supports?, Lengths?, TypeNameToIdMap?, DataDefinition?, DataManipulation?)>
    <!ATTLIST DialectConfiguration
        ProductName CDATA #IMPLIED
        ProductVersion CDATA #IMPLIED
    >
    <!ELEMENT Supports (Schemas?, Catalogs?)>
        <!ATTLIST Supports
            Sequences (oldStyle | newStyle | impliedPerTable | none) 'newStyle'
            AutoIncrementColumns (true | false) 'false'
            IdentifierQuote CDATA #IMPLIED
            IdentifierCase (mixed | lower | upper) 'upper'
            PreparedStatements (true | false) 'true'
        >
        <!ELEMENT Catalogs EMPTY>
            <!ATTLIST Catalogs
                Supports (true | false) 'true'
                InTableDefiniton (true | false) 'true'
                InConstraintDefiniton (true | false) 'true'
                InIndexDefiniton (true | false) 'true'
                InSequenceDefiniton (true | false) 'true'
                InDataManipulation (true | false) 'true'
                Separator CDATA #IMPLIED
                SystemCatalogs CDATA #IMPLIED
            >
        <!ELEMENT Schemas EMPTY>
            <!ATTLIST Schemas
                Supports (true | false) 'true'
                InTableDefiniton (true | false) 'true'
                InConstraintDefiniton (true | false) 'true'
                InIndexDefiniton (true | false) 'true'
                InSequenceDefiniton (true | false) 'true'
                InDataManipulation (true | false) 'true'
                Separator CDATA #IMPLIED
                SystemSchemas CDATA #IMPLIED
            >
    <!ELEMENT Lengths EMPTY>
        <!ATTLIST Lengths
            MaxCatalogName CDATA #IMPLIED
            MaxSchemaName CDATA #IMPLIED
            MaxTableName CDATA #IMPLIED
            MaxColumnName CDATA #IMPLIED
        >
    <!ELEMENT TypeNameToIdMap (Type*)>
        <!ELEMENT Type EMPTY>
            <!ATTLIST Type
                TypeName CDATA #REQUIRED
                TypeId CDATA #IMPLIED
                InsertValueTypeId CDATA #IMPLIED
                Precision CDATA #IMPLIED
                PrecisionRadix CDATA #IMPLIED
                MinScale CDATA #IMPLIED
                MaxScale CDATA #IMPLIED
                MustHaveLength (true | false) 'false'
                CanHaveLength (true | false) 'true'
                CanHavePrecision (true | false) 'true'
                CanHaveScale (true | false) 'true'
                CheckBinCharMaximum (true | false) 'true'
                CheckNumericMaximum (true | false) 'true'
                ColumnDefinitionMask CDATA #IMPLIED
                AppendCreateParamsLiterally (true | false) 'false'
                RetrieveProxyMethod CDATA #IMPLIED
            >
    <!ELEMENT DataDefinition EMPTY>
        <!ATTLIST DataDefinition
            CreateCatalogStatement CDATA #IMPLIED
            CreateSchemaStatement CDATA #IMPLIED
            AddToCreateSchema CDATA #IMPLIED
            AddToCreateTable CDATA #IMPLIED
            AddToCreateIndex CDATA #IMPLIED
            DropCatalogStatement CDATA #IMPLIED
            DropSchemaStatement CDATA #IMPLIED
            AddToDropSchema CDATA #IMPLIED
            AddToDropTable CDATA #IMPLIED
            AddToDropConstraint CDATA #IMPLIED
            AddToDropIndex CDATA #IMPLIED
            AddToDropSequence CDATA #IMPLIED
            AutoIncrementPrimaryKeyTypeName CDATA #IMPLIED
            AddToAutoIncrementPrimaryKeyDefinition CDATA #IMPLIED
            CreateIndexesOnAllKeyColumns (true | false) 'false'
            NeedsTableDotIndexOnDropIndex (true | false) 'false'
            ShouldDropForeignKeyConstraintsBeforeDropTable (true | false) 'true'
            ShouldDropIndexesBeforeDropTable (true | false) 'true'
            RequiredNumericPrimaryKeyType CDATA #IMPLIED
            SequenceNamesQuery CDATA #IMPLIED
            CreateSequenceStatement CDATA #IMPLIED
            MustCommitCreateSequence (true | false) 'false'
            DropSequenceStatement CDATA #IMPLIED
        >
    <!ELEMENT DataManipulation EMPTY>
        <!ATTLIST DataManipulation
            NextSequenceValueStatement CDATA #IMPLIED
            NextAutoIncrementedValueStatement CDATA #IMPLIED
            CanInsertNullOnAutoIncrementColumn (true | false) 'true'
            WriteNullAs_IS_NULL_InConditions (true | false) 'true'
            MustPrefetchBlob (true | false) 'false'
            TrimChar (true | false) 'false'
        >

Developing an XML dialect configuration

DataTypeChecker is a tool made to check basic CREATE TABLE and INSERT behaviour of the database and the XML dialect configuration. If all tests succeed, a database migration likely would work, except when there are problems with autoincrement key columns, or inserting null values.

The cecker creates a table with one column for every JDBC data type, checks if the type or a comptible one was created, inserts a value, re-reads the value and checks for equality. Do not take the XML configuration proposal at end serious. For syntax see above. You must provide a ConnectionParameter.xml file and the dialect file as arguments. When you do not pass a dialect, the generic Dialect is used (which likely will fail).

First the checker runs through meta data and looks if the database type names are plausible for the represented JDBC type. It outputs the types, and reports unplausible names, mostly occuring at BOOLEAN, as some database provide only TINYINT for that type. Then it starts to create test tables. Following are the most freqent type check errors.

Strings
For CHAR, VARCHAR, LONGVARCHAR and CLOB columns sometimes length arguments are mandatory. Omitting the length with CHAR columns mostly causes the database to create a CHAR(1) field, which might not be what you want. On the other hand, when there is no length for a VARCHAR column, JPumpDX passes the maximum length from metadata to that column, which also might not what you want. Configure this behaviour with CanHaveLength and MustHaveLength. Do not forget to set TrimChar to true for CHAR columns when the database fills it with spaces to the given length, else the test will fail.
Numbers
For decimal numbers, the provided precision is important. When metadata do not provide the correct values, configure this behaviour with the XML attribute Precision. Nevertheless the DataTypeChecker might fail because it is trying the recommended precision when meta data do not provide limits.
Timestamp
Many databases provide only seconds precision on timestamp, no millis. DataTypeChecker will report this as error, you have got to live with that.
JDBC type ids (java.sql.Types)
are sometimes not reported correctly, either by DatabaseMetaData, or when retrieving Table meta data. Work with XML TypeNameToIdMap element (using TypeName and TypeId attributes) to correct these errors. The TypeName must exactly match the database type name, the TypeId then can be set to the correct JDBC id. If you do not want some types to be used, map them to Types.OTHER (1111).

When all tests with DataTypeChecker succeeded, you must find out if catalog/schema names are supported in CONSTRAINT and INDEX definitions. Work with XML Supports - Schema - Catalog elements, using the attributes InConstraintDefinition and InIndexDefinition. Some databases show strange error messages on such mistakes. Then create a test schema in your database and try to import one of the XML files in the samples directory. Following are some import error examples.

The JDBC driver does not accept certain Java objects for certain data types. When the error occurs on e.g. TINYINT or SMALLINT, try Integer by assigning InsertValueTypeId='4' (Types.INTEGER). When such actions do not help, you must make a Java workaround, override ConfiguredDialect.convertObjectToJdbcType().
When special data types for IDENTITY (autoincrement) columns are reported together with normal data types, it could happen that foreign keys are created as IDENTITY columns. When those concerns are not separated in meta data, look at the workaround for SQL-Server in DataTypeMapper.anticipatesIdentityColumn(). There is no conceptual solution for that problem. Override ConfiguredDialect.createDataTypeMap() and extend ConfiguredDataTypeMapper to work around.
Catalog and schema names are not accepted at certain statement positions. As said, you can configure this in the Supports XML element.
The database does not deliver objects that implement java.sql.Clob or java.sql.Blob for CLOB or BLOB column values. You can wrap a proxy around those objects by configuration and define a no-argument method to retrieve the real implementation by configuration, look at oracle.xml for a sample: RetrieveProxyMethod='getCharacterStream'. When no such method is provided, we have a problem.
....

Software Design

The first version has about 15000 lines of Java and XML code, 67 Java source files and 20 XML and DTD files. There are no other open source components integrated, i decided to implement a small framework for Java-XML binding. As there is no open source SQL dialect implementation, i tried to design a new kind of SQL dialect which is configurable via XML. As things go, this XML-based configuration is the union of all workarounds that were necessary during testing. JPumpDX comes with several JUnit test cases that show the usage of the API. I spent much more time on unit testing and bugfixing than on writing sourcecode.

Here are the main "components":

lightweigth Java-XML binding (JAXB)
configurable SQL dialect
SQL bridge and JDBC/SQL utilities
configurable migration strategies (import, export)

Here are the most important classes and their responsibilities. The classes probably needed for programming are marked bold.

SqlBridge, SqlBridgeFactory
Obtains the JDBC Connection to the database. When constructed, it retrieves MetaData from the database and instantiates a Dialect with them, using ConfiguredDialectFactory. SqlBridge does not build SQL any statements. It provides CREATE SCHEMA, DROP SCHEMA, CREATE TABLE, DROP TABLE, CREATE SEQUENCE, DROP SEQUENCE, INSERT, UPDATE, DELETE, SELECT.
SqlBridge builds Record objects coming from a JDBC ResultSet, and its container TableData. It executes all SQL statements built by the Dialect.
Dialect, ConfiguredDialect, ConfiguredDialectFactory
Builds SQL statement texts. Provides generic behaviour and a lot of protected methods that can be overridden to modify the produced SQL. Instantiates a DataTypeMapper on construction. Gets a MetaData object from SqlBridge on construction. Dialect does not execute any SQL statement, this is done in SqlBridge.
StatementCache, PreparedStatementWrapper
Caches statements or prepared statements. Created by a factory method in SqlBridge. Works with a key built by involved attributes, statement type and null values. Can cache prepared and non-prepared statements.
MetaData, MetaData.TypeInfo
Reflects the existing catalogs, schemas, tables, indexes and unique and foreign key constraints of the database. Exposes a JDBC DatabaseMetaData object. MetaData objects are cached in SqlBridge. TypeInfo objects reflect all available data types of the database product.
DataTypeMapper
Maps data types that are not available in a target database on CREATE TABLE. Implements an algorithm to find the best type for a given one. Provides SQL text to create a table column. Holds an array of MetaData.TypeInfo objects.
QueryFilter
Generic specification of queries (like SQL WHERE clauses). Could also be used to generate other code than SQL (e.g. JDO OQL). The translation to SQL is done by a AbstractQueryWriter derivation. QueryFilter are specified with column names and values only, so they are restricted to an implied table.
TableImporter
Imports tables and records from XML or from another database. Contains all logic provided by import configuration. Uses the PrimaryKeyMapper interface to map primary key values to a native key generator. The idea of TableImporter is to use a SAX event (having read a Table or Record from XML) to put this immediately to database.
TableExporter
Exports tables and records from a database to XML or to another database. Contains all logic provided by export configuration. Builds "natural foreign keys" to non-exported tables. The idea of TableExporter is to loop an open ResultSet and write every exported Table or Record immediately to the XML stream.
PrimaryKeyMapper
This interface wraps the key generator for mapping key values to native ones. Three implementations for SEQUENCEs, AUTOINCREMENT columns and a general-purpose Sequence Table are provided.
XmlToInstanceData, InstanceDataToInstanceData, InstanceDataToXml
Wraps TableExporter and TableImporter to migrate tables and records at the same time. You do not need XmlToMetaData before launching these classes, as records and table metadata are expected to be both together in XML.
MetaDataToXml, MetaDataToMetaData, XmlToMetaData
Establishes metadata from XML in a database, or reflects metadata in XML, or migrates metadata from one database to another. Metadata are SCHEMA and TABLE definitions, foreign key and unique constraints, and indexes, but not records (instancedata).
AbstractReflectionXmlElement
The base class for all XML-driven classes. Performs reflection on SAX events to build an object on read, and to generate XML on write. Important derivations are MetaData, Table and its inner classes, Record, NamedValue, NaturalForeignKey, all configuration classes like DialectConfiguration, ImportConfiguration, ExportConfiguration, DbIdentifierMapper, TableSpecification and all its derivates, ...

Java-XML binding

This has no dependencies to the database package. It works with SAX to avoid big DOM models within memory.

Mixed content (elements embedded in text, like XHTML) is not supported.
It does not support document-type-definition capabilities very good, but it records the order elements were added.
Cyclic graphs are not supported, you will have an infinite loop when saving such a Java object to XML.
Primitive types are not supported, all fields must be Object or a subclass of it, and have a getter and a setter, or, for element lists, an indexed getter (returns null when out of bounds) and an adder.
There is no way to get comments into the generated XML.

The Java-XML binding is based on reflection. There is a factory to get a Java object hierarchy from an XML file or URL, and to store a Java object hierarchy to an XML file.
The package is

fri.util.xml.jaxb

The factory is

fri.util.xml.jaxb.JaxbObjectFactory

// load an Java object from an XML FileExportConfiguration exportConfig = JaxbObjectFactory.getFileInstance("exportConf.xml",ExportConfiguration.class); ... // do some programmatical changes

// save the Java object to a XML FileJaxbObjectFactory.putFileInstance(exportConfig, "exportConf.xml");

XML loadable and saveable Java classes must extend the AbstractReflectionXmlElement base class, or implement one of or both of XmlLoadable and XmlSaveable interfaces.

This mini-JAXB is used to load handcoded Java classes that simply reflect parameters and do not contain any logic (except returning null when an impossible element index is requested). These classes could be generated by a source generator (not yet implemented).

XML configurable SQL dialect

The XML configurable dialect is in package

fri.util.database.dialect.*

The ConfiguredDialect is configured by DialectConfiguration and extends the generic Dialect implementation in

fri.util.database.Dialect

The Dialect class implements generic SQL behaviour and exposes a lot of protected methods to subclasses.

fri.util.database.dialect.ConfiguredDialect

overwrites the protected methods to control behaviour from its XML configuration parameters. This class could be extended again to overwrite methods for solving some complicated problems. Such subclasses must provide a no-argument constructor, and pass a dialect name to the superclass. The ConfiguredDialectFactory uses every registered dialect as template and constructs new instances from their class, using getClass().newInstance() which calls the no-arg constructor, and passing the MetaData object after construction.

All ready-made dialects are in the this package:

fri/util/database/dialect/*.xml

Not all dialect.xml files are already tested. See the list of tested databases.
An XML configuration can be loaded from some File or URL. Look at the constructors of ConfiguredDialect.

The SQL bridge

The SqlBridge class encapsulates the database connection and the dialect. It provides methods to create and drop tables, constraints, indexes, sequences, and it provides select, insert, update and delete of records. It does not generate any SLQ, this is the responsibility of the dialect. It can work only on one table, no joins are possible (use RecordGraph for that purpose). An SqlBridge establishes a JDBC Connection on construction, and closes it on close() or garbage collection by finalize().

fri.util.database.SqlBridge

To express a WHERE clause, the class QueryFilter is used. This abstracts any SQL and uses a QueryWriter class to generate SQL (which is out of control of the dialect, as it uses only the generic keywords AND, OR, IN , and parentheses). Several QueryFilter instances can be logically associated using QueryFilterGroup. You could use these filters for JDO or any medium that supports a query language, assumed that a QueryWriter can be provided. The QueryFilter classes are in

fri.util.database.queryfilter.*
fri.util.database.queryfilter.jdbc.PreparedStatementQueryWriter

To manage and cache prepared statements, the package

fri.util.database.statement.*

contains classes for prepared and literal statements. Every Dialect has its own statement cache instance. The literal statements do not provide LOBs and are not very good tested, as most databases provide prepared statements.

Following is an query specification using the abstraction with SqlBridge and PreparedStatementQueryWriter.

QueryFilterGroup filter = new QueryFilterGroup(new QueryFilter []    {
    new QueryFilter("SURNAME", QueryFilter.LIKE, "Goslin%"),
    new QueryFilterGroup(QueryFilter.AND, new QueryFilter [] {
        new QueryFilter(                "DEVGROUP", QueryFilter.EQUAL, new Character('J')),
        new QueryFilter(QueryFilter.OR, "DEVGROUP", QueryFilter.EQUAL, new Character('C')),
    }),
    new QueryFilter(QueryFilter.AND_NOT, "JOB", QueryFilter.EQUAL, null)
});

sqlBridge.selectFromTable(table, filter);

Data type mapping

The DataTypeMapper is part of the Dialect.

fri.util.database.DataTypeMapper

Data types are the biggest problem when migrating data from one database to another. For example, REAL, FLOAT and DOUBLE types have other precisions in almost every database. Some databases provide only seconds precision in TIMESTAMP. Furthermore VARCHAR is restricted differently in every database, but sometimes it is not. Data types require different arguments on column definitions. Sometimes a CHAR column without precision is only one character long (which is definitely not what we want). Most CHAR type implementations fill with spaces when defined with a length. Etc., etc.

JPumpDX just checks for precision on character and binary types. For numbers it relies on JDBC standards. Errors could happen when some data require a high precision. When a binary or character target type is too narrow for the source type, it scales up to the next bigger, e.g. from LONGVARCHAR to CLOB.

For following data types a length argument is assumed to be mandatory (implemented in DataTypeMapper, can be configured using XML):

CHAR
BINARY
VARCHAR
VARBINARY

For following number types precision and scale arguments are assumed to be optional:

NUMBER
DECIMAL

Look at DialectConfiguration.dtd for customizing data types.
The algorithm to map data types from one database product to another is implemented in the method DataTypeMapper.findBestType().

Primary key generation and mapping

The primary key mapping classes are

fri.util.database.migration.PrimaryKeyMapper (interface)
fri.util.database.migration.NumericPrimaryKeyMapper (all style SEQUENCEs)
fri.util.database.migration.AutoIncrementPrimaryKeyMapper (for IDENTITY columns, needs a statement to retrieve the generated key after insert)
fri.util.database.migration.TableSequencePrimaryKeyMapper (workaround for databases that do not support SEQUENCES)

The problem solved by primary key mapping is the fact that associations between records are done by keys that are unique only per database instance, and even per table. That means when importing records from another database you have to change these values, else they will conflict with existing ones. Even when having no data in the target database, you will likely want to create new records after import, where to take the primary key values from? You need a key generator that knows about the already created records per table.

Primary key generation is mostly done by three methods:

Sequences, which hold exactly one numeric value, when it is retrieved by e.g. "SELECT MySequencs.NEXTVAL from DUAL", the value gets incremented by the database. This works under concurrency. There are databases that provide implied sequences per table, that means when having created a table there is a sequence ready to be used, queryable by a special SQL statement.
Autoincrement columns. These columns need to be defined specially on CREATE TABLE, and they automatically create a key value for every inserted record. Sometimes the key value on INSERT must be null, sometimes the key column must not be in the INSERT statement at all. The generated value is retrieveable after INSERT by some database-specific SQL command. All these things can be controlled by DialectConfiguration.
A "virtual sequence" table, providing a row for every needed sequence, with the name and the current numeric value as columns. For databases that do not provide any of the mentioned methods this is an acceptable solution. Fetching a sequence value means to SELECT the current value and UPDATE the value to the next higher number. This has to be atomic.

Primary key mapping then means to generate a new value, putting it into the record (about to be inserted), and storing it into a Map with key=oldValue and value=newValue. Or, for autincrement mapping, providing null for the key column, inserting the record, and retrieving the generated value by a database-specific SQL statement. Foreign keys now can be mapped by querying the newValue with their oldValue from the Map. Composed keys are not supported.

Primary key mapping can be switched on and off by configuration. When using one of the service classes like XmlToInstanceData, you have to set the PrimaryKeyMapper explicitely to turn on the mapping. You can use sqlBridge.createPrimaryKeyMapper() for that purpose, as SqlBridge knows by XML configuration about the way the database supports key generation.

The class that caches and maps all key values is TableImporter, the Map object is in the PrimaryKeyMapper implementation (AbstractPrimaryKeyMapper derivate) . So you should use the same TableImporter (or ImportConfiguration) instance for all imported data, else foreign keys will get no or a wrong value.

For adapting the mapper classes to already existing key generators, or to an already existing virtual sequence table, extend NumericPrimaryKeyMapper or TableSequencePrimaryKeyMapper . You can install this derivation by passing it to one of the Configuration classes: importConfig.setPrimaryKeyMapper(myMapper);

Source code hints

To make it easier to find the classes to override for certain purposes, here is a small HOWTO

How to switch off certain data types by XML configuration

A sample is in oracle.xml. Oracle provides LONG as character datatype, but it allows only one LONG column per table. So it is better not to use it on a migration when you do not know how much LONGVARCHAR columns are in source tables.

You can switch off the unwanted data type by setting its JDBC type to Types.OTHERS (1111). The dialect will find the data type by its name ('LONG') and assign another JDBC TypeId to it.

<DialectConfiguration>
    <TypeNameToIdMap>
        <Type TypeName='LONG' TypeId='1111' />
        <Type TypeName='LONG RAW' TypeId='1111' />
    </TypeNameToIdMap>
</DialectConfiguration>

How to use another Java object than the default on INSERT.

Every JDBC type has its default Java pendant. This defaults are hardcoded in DbUtil class, following the Sun recommendations. Some database drivers do not accept these default types, e.g. FrontBase insists on getting java.lang.Integer as value for a SMALLINT column, it throws an exception on getting the default java.lang.Short.

You must reassign the inserted JDBC type using dialect configuration. This looks like the following.

<DialectConfiguration>
    <TypeNameToIdMap>
        <Type TypeName='TINYINT' InsertValueTypeId='4' /> 
        <Type TypeName='SMALLINT' InsertValueTypeId='4' />
    </TypeNameToIdMap>
</DialectConfiguration>

How to exclude system catalogs or schemas by configuration

Most databases have their meta data within system catalogs or schemas. When exporting a database, you do not want these to be in export data.

You can explicitely exclude them by using ExportConfiguration/DbIdentifierMapper, or by telling the dialect their names:

<DialectConfiguration>
    <Supports>
        <Schemas SystemSchemas = "SYS_JDBC, SYS_INFO"/>
    </Supports>
<DialectConfiguration>

How to dedicate a dialect configuration to one or more specific database versions

When the ConfiguredDialectFactory allocates a dialect, it matches all registered dialects against the MetaData it got, by calling Dialect.matchesProductName(String productName). When this matches, it calls Dialect.matchesProductVersion(String productVersion).Both the product name and version come from JDBC DatabaseMetaData. When both calls return true, the dialect is instantiated by using the no-arg constructor of its class.

The ConfiguredDialect class overwrites these methods to ask the XML driven dialectConfig for values in attributes ProductName and ProductVersion. These must match exactly. When there are no such attributes configured, it delegates to super. Dialect implements a "lower-case contains" match against the product name, that means, for matching a product name like "Mckoi 1.0.3" the dialect's name must be something like "mckoi" (spaces are replaced by "_"). The Dialect version match always returns true (does no version check).

So all you got to do is write the version into the XML dialect configuration. You can match several versions by concatenating them using a comma:

<DialectConfiguration ProductVersion="1.2.1, 1.2.2, 1.3.0">
...

As you can not derive an XML file from another, you got to copy the version-specific XML file from the unspecific one.
Do not forget to register a newly constructed ConfiguredDialect instance with the modified XML configuration at ConfiguredDialectFactory.

Please read the DialectConfiguration.dtd comments to learn more about dialect configuration!

How to add STORAGE clauses per table to CREATE TABLE

You could add the same storage clause to every CREATE TABLE / CREATE INDEX statement by using the XML attributes AddToCreateTable / AddToCreateIndex (which would be enough to just put tables and indexes in separate tablespaces), but you must write a Java ConfiguredDialect derivate to do this per table/index.

public class StorageClauseAppendingDialect extends ConfiguredDialect
{
    public StorageClauseAppendingDialect()    {
        super("oracle");
    }

    protected void appendToEndOfCreateTableStatement(StringBuffer sb, Table table, List unsupportedAttributes)    {
        if (table.getName().equals("CUSTOMER"))
            sb.append(" TABLESPACE TS_CUSTOMER_TABLES STORAGE (INITIAL 20 M PCTINCREASE 2 M");
    }

    protected void appendToEndOfIndexDefinition(StringBuffer sb, Table table, String indexName)    {
        if (indexName.equals("INDEX_CUSTOMER"))
            sb.append(" TABLESPACE TS_CUSTOMER_INDEXESSTORAGE (INITIAL 5 M)");
    }
}

....
ConfiguredDialectFactory.singleton.registerDialect(new StorageClauseAppendingDialect());
SqlBridge sqlBridge = SqlBridgeFactory.newInstance(new File("oracleConnectionParameters.xml"));
....

How to implement a new String (XML) to Object (database) conversion (e.g. for Types.ARRAY, or some other currently unsupported JDBC type)

You can use the implementation for Base64 encoding of binary data types as a sample. You find it in AbstractNamedValue.convertStringToObject(String value, Class targetClass) and convertObjectToString(Object value). To extend the actually used derivate NamedValue you must override SqlBridge.createNamedValue(Table.Column column, Object value).

SqlBridge sqlBridge = new SqlBridge(...) { protected NamedValue createNamedValue(Table.Column column, Object value) { return new NamedValue(column, value) { protected Object convertStringToObject(String value, Class targetClass) throws JaxbException { if (targetClass.equals(Object.class)) { // value of NamedValue is arriving try { switch (getType().intValue()) { case Types.JAVA_OBJECT: .... // convert String to a Java object default: return super.convertStringToObject(value, targetClass); } } catch (Exception e) { throw new JaxbException(e); } } else { // name arriving return super.convertStringToObject(value, targetClass); } } protected String convertObjectToString(Object value) throws JaxbException { try { if (value instanceof MyJavaObject) { ... // convert the Java object to a String } else { return super.convertObjectToString(value); } } catch (JaxbException e) { throw e; } catch (Exception e) { throw new JaxbException(e); } } }; } };

How to introduce another XML configuration attribute

Assuming you need another configuration parameter for your database migration, you got to do the following:

Go to the configuration Java source (ExportConfiguration, ImportConfiguration, DialectConfiguration) and create your new private field with public getter and setter methods. It must be primitive. Else you need to make a new element, you can do this e.g. as public static inner class. If you need an embedded list of primitives, you can do this by using one of the protected convenience implementations of AbstractReflectionXmlElement (getCsvStringList, setCsvStringList, getCsvStringMap, setCsvStringMap) which do the tokenizing of the list embedded in an XML attribute. If you need an embedded list of elements, yo need to implement addXXX(XXX) and getXXX(i) for that element, and the element implementation itself as a public static inner class.

The most elegant way do that is by extending the class.
For migration functionality, now go to TableExporter or TableImporter class, read the source and implement your semantics, querying the new property from exportConfig member variable. For a sample, look at the implementation of the DoUpdateWhenExists property in TableImporter.
For dialect functionality, go to ConfiguredDialect class and implement your semantics, querying the new property from dialectConfig member variable.

The most elegant way do that all is by extending the class, but you got to override its factory method then:
For dialect, call

ConfiguredDialectFactory.singleton.registerDialect(myDialectInstance);

For migration, override

InstanceDataToXml.newTableExporter(...), or
InstanceDataToInstanceData.newTableImporter(...) or newTableExporter(...).

For XmlToInstanceData it is a bit more complicated:
XmlToInstanceDatamigration = new XmlToInstanceData(...) { protected XmlLoader newXmlLoader(...) { return new XmlDataLoader(...) { protected TableImporter newTableImporter(...) {
return new MyTableImporter(...); } } }
};
Put the new property into the DTD to be able to use your XML editor for editing the configuration file (ExportConfiguration.dtd, ImportConfiguration.dtd, DialectConfiguration.dtd). Do not forget to document it!

APPENDIX

List of tested databases

DaffodilDB 3.4
FrontBase 3.6.44
Mckoi 1.0.3
PointBase 5.2
PostgreSQL 7.2
MySQL 4.0.12-standard
InstantDb 3.26
HSQL 1.7.2
Oracle 9.2.0.1.0
Microsoft SQL Server 7.0.623

Database Type Table

This table was created directly (literally) from JDBC drivers DatabaseMetaData (including all errors) using XSLT. Data type arguments are written italic.

JDBC

InstantDB Version 3.26

Mckoi SQL Database ( 1.0.3 ) 1.0.3

HSQL Database Engine 1.7.2

DB2/NT 07.02.0005

Oracle 9.2.0.1.0

Firebird WI-V6.3.1.4481 Firebird 1.5

FrontBase 3.6.41

MySQL 4.0.12-standard

PointBase 4.4GA

DaffodilDB 3.4

PostgreSQL 7.2

Microsoft SQL Server Product Version: 7.0.623

-6: TINYINT

BYTE

TINYINT

NUMBER (3)

TINYINT

BOOLEAN

TINYINT [(M)] [UNSIGNED] [ZEROFILL]

tinyint

byte

tinyint

tinyint identity

-6: TINYINT

4: INTEGER

INTEGER

INTeger

NUMBER (10)

INTEGER

INTEGER [(M)] [UNSIGNED] [ZEROFILL]

INT [(M)] [UNSIGNED] [ZEROFILL]

MEDIUMINT [(M)] [UNSIGNED] [ZEROFILL]

INTEGER

int

integer

int4

oid

int

int identity

4: INTEGER

-5: BIGINT

LONG

CURRENCY

BIGINT

NUMBER

BIGINT

LONGINT

BIGINT [(M)] [UNSIGNED] [ZEROFILL]

long

bigint

int8

-5: BIGINT

8: DOUBLE

DOUBLE

FLOAT

DOUBLE PRECISION

DOUBLE [(M,D)] [ZEROFILL]

DOUBLE PRECISION [(M,D)] [ZEROFILL]

REAL [(M,D)] [ZEROFILL]

DOUBLE PRECISION

double precision

float8

money

8: DOUBLE

6: FLOAT

FLOAT

FLOAT [(precision)]

FLOAT Optional Precision

float

6: FLOAT

91: DATE

DATE

date

91: DATE

12: VARCHAR

CHAR length

VARCHAR

VARCHAR LENGTH

VARCHAR_IGNORECASE LENGTH

VARCHAR (length)

VARCHAR2

VARCHAR length

CHARACTER VARYING (length)

VARCHAR (M)

ENUM

SET

VARCHAR Length

character varying max length

char varying max length

varchar max length

name

text

varchar

varchar max length

12: VARCHAR

-4: LONGVARBINARY

BINARY

LONGVARBINARY

LONG VARCHAR FOR BIT DATA

LONG RAW

BLOB SUB_TYPE 0

LONG VARBINARY

MEDIUMBLOB

LONGBLOB

BLOB

TINYBLOB

long varbinary length

image

-4: LONGVARBINARY

-7: BIT

BIT

BOOLEAN

NUMBER (1)

BIT [(length)]

BIT

BOOL

boolean

bool

bit

-7: BIT

5: SMALLINT

SMALLINT

NUMBER (5)

SMALLINT

SMALLINT [(M)] [UNSIGNED] [ZEROFILL]

SMALLINT

smallint

int2

smallint

smallint identity

5: SMALLINT

7: REAL

REAL

FLOAT [(M,D)] [ZEROFILL]

REAL

real

float4

real

7: REAL

2: NUMERIC

NUMERIC

NUMERIC PRECISION,SCALE

NUMeric (precision,scale)

NUMBER

NUMERIC precision,scale

NUMERIC [ (precision[,scale]) ]

NUMERIC [(M[,D])] [ZEROFILL]

NUMERIC Optional Precision and Scale

numeric precision,scale

numeric

numeric precision,scale

numeric() identity precision

2: NUMERIC

3: DECIMAL

DECIMAL

DECIMAL PRECISION,SCALE

DECimal (precision,scale)

DECIMAL precision,scale

DECIMAL [ (precision[,scale]) ]

DECIMAL [(M[,D])] [ZEROFILL]

DECIMAL Optional Precision and Scale

decimal precision,scale

dec precision,scale

decimal precision,scale

money

smallmoney

decimal() identity precision

3: DECIMAL

1: CHAR

CHAR

CHAR LENGTH

CHARacter (length)

CHAR

CHAR length

CHARACTER [(length)]

CHAR (M)

CHARACTER Optional Length

character length

char length

char

bpchar

char length

1: CHAR

-1: LONGVARCHAR

LONGVARCHAR

LONG VARCHAR

LONG

BLOB SUB_TYPE 1

LONG VARCHAR

MEDIUMTEXT

LONGTEXT

TEXT

TINYTEXT

long varchar length

text

-1: LONGVARCHAR

92: TIME

TIME

DATE

TIME

TIME WITH TIME ZONE

TIME

time timeprecision

time

92: TIME

93: TIMESTAMP

TIMESTAMP

TIMESTAMP WITH TIME ZONE

DATETIME

TIMESTAMP [(M)]

TIMESTAMP

timeStamp timestampprecision

abstime

timestamp

timestamptz

datetime

smalldatetime

93: TIMESTAMP

-2: BINARY

BINARY

CHARacter () FOR BIT DATA (length) FOR BIT DATA

BINARY (M)

bit length

binary length

bytea

binary length

timestamp

-2: BINARY

-3: VARBINARY

VARBINARY

VARCHAR () FOR BIT DATA (length) FOR BIT DATA

RAW

BIT VARYING (length)

VARBINARY (M)

bit varying length

varbinary length

varbinary max length

-3: VARBINARY

2000: JAVA_OBJECT

JAVA_OBJECT

long varbinary length

2000: JAVA_OBJECT

16: BOOLEAN

BOOLEAN

boolean

16: BOOLEAN

1111: OTHER

OTHER

ARRAY

INTERVAL

long varbinary length

int2vector

regproc

tid

xid

cid

oidvector

SET

pg_type

pg_attribute

pg_proc

pg_class

pg_shadow

pg_group

pg_database

smgr

point

lseg

path

box

polygon

line

_line

reltime

tinterval

unknown

circle

_circle

_money

macaddr

inet

cidr

_name

_int2vector

_regproc

_tid

_xid

_cid

_oidvector

_bpchar

_point

_lseg

_path

_box

_reltime

_tinterval

_polygon

aclitem

_aclitem

_macaddr

_inet

_cidr

_timestamptz

interval

_interval

timetz

_timetz

bit

_bit

varbit

_varbit

refcursor

pg_attrdef

pg_relcheck

pg_inherits

pg_index

pg_operator

pg_opclass

pg_am

pg_amop

pg_amproc

pg_language

pg_largeobject

pg_aggregate

pg_statistic

pg_rewrite

pg_trigger

pg_listener

pg_description

pg_toast_16384

pg_toast_16416

pg_toast_1255

pg_toast_16386

pg_toast_16410

pg_toast_16408

pg_user

pg_rules

pg_views

pg_tables

pg_indexes

pg_stats

pg_stat_all_tables

pg_stat_sys_tables

pg_stat_user_tables

pg_statio_all_tables

pg_statio_sys_tables

pg_statio_user_tables

pg_stat_all_indexes

pg_stat_sys_indexes

pg_stat_user_indexes

pg_statio_all_indexes

pg_statio_sys_indexes

pg_statio_user_indexes

pg_statio_all_sequences

pg_statio_sys_sequences

pg_statio_user_sequences

pg_stat_activity

pg_stat_database

meinetesttabelle

pg_toast_16557

1111: OTHER

2004: BLOB

BLOB (length)

BLOB

BLOB SUB_TYPE <0

BLOB

BLOB Optional Large Object Length

blob max length

2004: BLOB

2005: CLOB

CLOB (length)

CLOB

CLOB Optional Large Object Length

character large object max length

char large object max length

clob max length

2005: CLOB

70: DATALINK

DATALINK

70: DATALINK

-104:

INTERVALDS

-104:

-103:

INTERVALYM

-103:

-102:

TIMESTAMP WITH LOCAL TIME ZONE

-102:

-101:

TIMESTAMP WITH TIME ZONE

-101:

2002: STRUCT

STRUCT

2002: STRUCT

2003: ARRAY

ARRAY

_bool

_bytea

_char

_int2

_int4

_text

_oid

_varchar

_int8

_float4

_float8

_abstime

_timestamp

_date

_time

_numeric

2003: ARRAY

2006: REF

REF

2006: REF

BIGINT

0: NULL

null

0: NULL

-11:

uniqueidentifier

-11:

-10:

ntext

-10:

-9:

nvarchar max length

sysname

-9:

-8:

nchar length

-8:

InstantDB Version 3.26

Mckoi SQL Database ( 1.0.3 ) 1.0.3

HSQL Database Engine 1.7.2

DB2/NT 07.02.0005

Oracle 9.2.0.1.0

Firebird WI-V6.3.1.4481 Firebird 1.5

FrontBase 3.6.41

MySQL 4.0.12-standard

PointBase 4.4GA

DaffodilDB 3.4

PostgreSQL 7.2

Microsoft SQL Server Product Version: 7.0.623

JDBC Meta Data Table

db2

mysql

hsql

frontbase

instantdb

firebird

postgres

pointbase

mssql

oracle

mckoi

daffodil

allProceduresAreCallable

false

true

false

true

false

true

allProceduresAreCallable

allTablesAreSelectable

false

true

false

true

false

true

allTablesAreSelectable

dataDefinitionCausesTransactionCommit

false

true

false

true

false

true

false

true

dataDefinitionCausesTransactionCommit

dataDefinitionIgnoredInTransactions

false

true

false

true

false

dataDefinitionIgnoredInTransactions

doesMaxRowSizeIncludeBlobs

false

true

false

true

false

true

false

true

false

doesMaxRowSizeIncludeBlobs

getCatalogSeparator

getCatalogTerm

stored procedure

database

CATALOG

Catalog

database

CATALOG

database

Catalog

getCatalogTerm

getDatabaseProductName

DB2/NT

MySQL

HSQL Database Engine

FrontBase

InstantDB

Firebird

PostgreSQL

PointBase

Microsoft SQL Server

Oracle

Mckoi SQL Database ( 1.0.3 )

DaffodilDB

getDatabaseProductName

getDatabaseProductVersion

07.02.0006

4.0.12-standard

1.7.2

3.6.41

Version 3.26

WI-V6.3.1.4481 Firebird 1.5

7.2

4.4GA

7.0.623

Oracle9i Enterprise Edition Release 9.2.0.1.0

1.0.3

3.4

getDatabaseProductVersion

getDefaultTransactionIsolation

READ_COMMITTED

READ_UNCOMMITTED

SERIALIZABLE

NONE

READ_COMMITTED

SERIALIZABLE

NONE

getDefaultTransactionIsolation

getDriverMajorVersion

getDriverMinorVersion

getDriverName

IBM DB2 JDBC 2.0 Type 2

MySQL-AB JDBC Driver

HSQL Database Engine Driver

FBJDriver

InstantDB JDBC Driver

JayBird JCA/JDBC driver

PostgreSQL Native Driver

PointBase JDBC Driver

i-net SPRINTA

Oracle JDBC driver

Mckoi JDBC Driver

DaffodilDBDriver

getDriverName

getDriverVersion

07.02.0005

mysql-connector-java-3.0.15-ga ( $Date: 2004/08/09 22:15:11 $, $Revision: 1.27.2.43 $ )

1.7.2

2.4.7

Version 3.26

1.5

PostgreSQL 7.2 JDBC2

4.4GA

6.03

9.2.0.1.0

1.0

getDriverVersion

getExtraNameCharacters

#@$

getExtraNameCharacters

getIdentifierQuoteString

getMaxBinaryLiteralLength

4000

16777208

268435456

256

65536

1000

getMaxBinaryLiteralLength

getMaxCatalogNameLength

128

getMaxCatalogNameLength

getMaxCharLiteralLength

4000

16777208

2147483647

256

32767

65536

2000

32768

4192

getMaxCharLiteralLength

getMaxColumnNameLength

128

256

128

256

128

getMaxColumnNameLength

getMaxColumnsInGroupBy

500

4096

getMaxColumnsInGroupBy

getMaxColumnsInIndex

1600

getMaxColumnsInIndex

getMaxColumnsInOrderBy

500

4096

getMaxColumnsInOrderBy

getMaxColumnsInSelect

500

256

2048

4096

getMaxColumnsInSelect

getMaxColumnsInTable

500

512

2048

32767

1600

250

1000

4096

getMaxColumnsInTable

getMaxConnections

128

8192

8000

getMaxConnections

getMaxCursorNameLength

128

getMaxCursorNameLength

getMaxIndexLength

255

256

2147483647

getMaxIndexLength

getMaxProcedureNameLength

256

128

getMaxProcedureNameLength

getMaxRowSize

4005

2147483639

65536

65531

1073741824

2000

16777216

getMaxRowSize

getMaxSchemaNameLength

128

getMaxSchemaNameLength

getMaxStatementLength

65535

65531

65536

65535

60000

getMaxStatementLength

getMaxStatements

512

1024

getMaxStatements

getMaxTableNameLength

128

256

128

getMaxTableNameLength

getMaxTablesInSelect

256

512

getMaxTablesInSelect

getMaxUserNameLength

128

256

128

getMaxUserNameLength

getProcedureTerm

stored procedure

PROCEDURE

Procedure

PROCEDURE

function

PROCEDURE

stored procedure

procedure

Procedure

getProcedureTerm

getSchemaTerm

schema

SCHEMA

Schema

schema

SCHEMA

owner

schema

Schema

getSchemaTerm

getSearchStringEscape

isCatalogAtStart

false

true

false

true

false

true

false

true

isCatalogAtStart

isReadOnly

false

isReadOnly

nullPlusNonNullIsNull

true

nullPlusNonNullIsNull

nullsAreSortedAtEnd

false

true

false

nullsAreSortedAtEnd

nullsAreSortedAtStart

false

nullsAreSortedAtStart

nullsAreSortedHigh

true

false

true

false

true

false

nullsAreSortedHigh

nullsAreSortedLow

false

true

false

true

false

true

nullsAreSortedLow

storesLowerCaseIdentifiers

false

true

false

storesLowerCaseIdentifiers

storesLowerCaseQuotedIdentifiers

false

storesLowerCaseQuotedIdentifiers

storesMixedCaseIdentifiers

false

true

false

true

false

true

false

true

storesMixedCaseIdentifiers

storesMixedCaseQuotedIdentifiers

false

true

false

true

false

storesMixedCaseQuotedIdentifiers

storesUpperCaseIdentifiers

true

false

true

false

true

false

true

false

true

false

storesUpperCaseIdentifiers

storesUpperCaseQuotedIdentifiers

false

true

false

storesUpperCaseQuotedIdentifiers

supportsANSI92EntryLevelSQL

true

false

true

false

true

false

true

supportsANSI92EntryLevelSQL

supportsANSI92FullSQL

false

true

false

true

supportsANSI92FullSQL

supportsANSI92IntermediateSQL

false

true

false

true

false

true

supportsANSI92IntermediateSQL

supportsAlterTableWithAddColumn

true

false

true

supportsAlterTableWithAddColumn

supportsAlterTableWithDropColumn

false

true

false

true

false

true

false

true

supportsAlterTableWithDropColumn

supportsBatchUpdates

true

supportsBatchUpdates

supportsCatalogsInDataManipulation

false

true

false

true

false

true

false

true

supportsCatalogsInDataManipulation

supportsCatalogsInIndexDefinitions

false

true

false

true

false

true

supportsCatalogsInIndexDefinitions

supportsCatalogsInPrivilegeDefinitions

false

true

false

true

false

true

supportsCatalogsInPrivilegeDefinitions

supportsCatalogsInProcedureCalls

false

true

false

true

supportsCatalogsInProcedureCalls

supportsCatalogsInTableDefinitions

false

true

false

true

supportsCatalogsInTableDefinitions

supportsColumnAliasing

true

supportsColumnAliasing

supportsConvert

true

false

true

false

true

supportsConvert

supportsCoreSQLGrammar

false

true

false

true

false

true

false

true

supportsCoreSQLGrammar

supportsCorrelatedSubqueries

true

false

true

false

true

false

true

supportsCorrelatedSubqueries

supportsDataDefinitionAndDataManipulationTransactions

true

false

true

supportsDataDefinitionAndDataManipulationTransactions

supportsDataManipulationTransactionsOnly

false

true

false

true

false

true

supportsDataManipulationTransactionsOnly

supportsDifferentTableCorrelationNames

false

true

false

true

false

true

false

true

supportsDifferentTableCorrelationNames

supportsExpressionsInOrderBy

true

false

true

supportsExpressionsInOrderBy

supportsExtendedSQLGrammar

true

false

true

false

true

false

true

false

true

false

true

supportsExtendedSQLGrammar

supportsFullOuterJoins

true

false

true

false

true

false

true

false

supportsFullOuterJoins

supportsGroupBy

true

false

true

supportsGroupBy

supportsGroupByBeyondSelect

true

false

true

false

true

false

true

supportsGroupByBeyondSelect

supportsGroupByUnrelated

false

true

false

true

false

true

false

supportsGroupByUnrelated

supportsIntegrityEnhancementFacility

true

false

true

false

true

false

true

false

true

supportsIntegrityEnhancementFacility

supportsLikeEscapeClause

true

supportsLikeEscapeClause

supportsLimitedOuterJoins

true

false

true

supportsLimitedOuterJoins

supportsMinimumSQLGrammar

false

true

false

true

false

true

supportsMinimumSQLGrammar

supportsMixedCaseIdentifiers

false

true

false

supportsMixedCaseIdentifiers

supportsMixedCaseQuotedIdentifiers

true

false

true

false

true

false

supportsMixedCaseQuotedIdentifiers

supportsMultipleResultSets

true

false

true

false

true

false

true

supportsMultipleResultSets

supportsMultipleTransactions

true

supportsMultipleTransactions

supportsNonNullableColumns

true

supportsNonNullableColumns

supportsOpenCursorsAcrossCommit

true

false

true

false

true

false

true

supportsOpenCursorsAcrossCommit

supportsOpenCursorsAcrossRollback

false

true

false

true

false

true

supportsOpenCursorsAcrossRollback

supportsOpenStatementsAcrossCommit

true

false

true

false

true

supportsOpenStatementsAcrossCommit

supportsOpenStatementsAcrossRollback

true

false

true

false

true

supportsOpenStatementsAcrossRollback

supportsOrderByUnrelated

true

false

true

false

true

supportsOrderByUnrelated

supportsOuterJoins

true

supportsOuterJoins

supportsPositionedDelete

true

false

true

false

true

false

true

false

supportsPositionedDelete

supportsPositionedUpdate

true

false

true

false

true

false

true

false

supportsPositionedUpdate

supportsSchemasInDataManipulation

true

false

true

false

true

supportsSchemasInDataManipulation

supportsSchemasInIndexDefinitions

true

false

true

false

true

supportsSchemasInIndexDefinitions

supportsSchemasInPrivilegeDefinitions

true

false

true

false

true

supportsSchemasInPrivilegeDefinitions

supportsSchemasInProcedureCalls

false

true

false

true

supportsSchemasInProcedureCalls

supportsSchemasInTableDefinitions

true

false

true

false

true

supportsSchemasInTableDefinitions

supportsSelectForUpdate

true

false

true

false

true

false

true

false

true

supportsSelectForUpdate

supportsStoredProcedures

true

false

true

false

true

false

true

false

true

supportsStoredProcedures

supportsSubqueriesInComparisons

true

false

true

false

true

supportsSubqueriesInComparisons

supportsSubqueriesInExists

true

false

true

false

true

false

true

supportsSubqueriesInExists

supportsSubqueriesInIns

true

false

true

supportsSubqueriesInIns

supportsSubqueriesInQuantifieds

true

false

true

false

true

false

true

supportsSubqueriesInQuantifieds

supportsTableCorrelationNames

true

supportsTableCorrelationNames

supportsTransactions

true

supportsTransactions

supportsUnion

true

false

true

false

true

false

true

supportsUnion

supportsUnionAll

true

false

true

false

true

supportsUnionAll

usesLocalFilePerTable

false

true

false

usesLocalFilePerTable

usesLocalFiles

false

true

false

usesLocalFiles

getDatabaseMajorVersion

getDatabaseMinorVersion

getJDBCMajorVersion

getJDBCMinorVersion

getResultSetHoldability

getSQLStateType

locatorsUpdateCopy

true

false

locatorsUpdateCopy

supportsGetGeneratedKeys

true

false

true

false

supportsGetGeneratedKeys

supportsMultipleOpenResults

false

supportsMultipleOpenResults

supportsNamedParameters

false

true

false

supportsNamedParameters

supportsSavepoints

false

true

false

true

false

supportsSavepoints

supportsStatementPooling

false

true

supportsStatementPooling

getDatabaseCreationVersion

3.6.41

getDatabaseCreationVersion

getDriverSubVersion

getObjectId

getDriverMajorVersionInfo

getDriverMinorVersionInfo

getDriverNameInfo

Oracle JDBC driver

getDriverNameInfo

getDriverVersionInfo

9.2.0.1.0

getDriverVersionInfo

getGetLobPrecision

true

getGetLobPrecision

getLobPrecision

4294967295

getLobPrecision

db2

mysql

hsql

frontbase

instantdb

firebird

postgres

pointbase

mssql

oracle

mckoi

daffodil

Author: Ritzberger Fritz, April 2005

JPumpDX - Relational Data Migration with XML

Contents

Features

Terms

Motivation and aims

Quick Start

Import (from XML)

Export (to XML)

Migration (database to database)

Tool Syntax

Manual

Configuring JDBC connections

Table design only (using XML)

Record editing with table design (using XML)

Text and numbers

Binary content

Java code

Table and record migration

Java sample code

Database to database:

Database to XML:

XML to database:

Establishing foreign keys

Configuration

Migration

Import

Export

Dialect

Developing an XML dialect configuration

Software Design

Java-XML binding

XML configurable SQL dialect

The SQL bridge

Data type mapping

Primary key generation and mapping

Source code hints

How to switch off certain data types by XML configuration

How to use another Java object than the default on INSERT.

How to exclude system catalogs or schemas by configuration

How to dedicate a dialect configuration to one or more specific database versions

How to add STORAGE clauses per table to CREATE TABLE

How to implement a new String (XML) to Object (database) conversion (e.g. for Types.ARRAY, or some other currently unsupported JDBC type)

How to introduce another XML configuration attribute

APPENDIX

List of tested databases

Database Type Table

JDBC Meta Data Table