JPumpDX - Relational Data Migration with XML

Contents

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

Motivation and aims

• 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.
  

• 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

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

Import (from XML)

<?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>

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

Export (to XML)

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

Migration (database to database)

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

Tool Syntax

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]
       { -db connectionParameters.xml | -xml instanceData.xml }
           { [-catalog CATALOGNAME] [-schema SCHEMANAME] [-table TABLENAME] } | [-config importConfig.xml]

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

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

Manual

Configuring JDBC connections

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

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

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

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

Table design only (using XML)

<?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>

String sourceCatalog = null;    // assume the catalog is not defined in XML
String sourceSchema = null;    // assume the schema is not defined in XML
String 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

<?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>

<!--
     Possible, but strongly discouraged, as record values can only be converted
     to Java objects when an import database prepares their data types!
-->
<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

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

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

Table and record migration

• 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.

Java sample code

Database to database:

Database to XML:

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();
}

• MetaDataToMetaData,
• MetaDataToXml and
• XmlToMetaData

Establishing foreign keys

• 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.

Configuration

Migration

• 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

• 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)

Import

• 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).

Export

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

• ... export a graph of records associated by foreign keys

Dialect

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

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

• 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,
  • ...
    

<!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

• 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).
  

• 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

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

• 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

• 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.
  

fri.util.xml.jaxb

fri.util.xml.jaxb.JaxbObjectFactory

XML configurable SQL dialect

fri.util.database.dialect.*

fri.util.database.Dialect

fri.util.database.dialect.ConfiguredDialect

fri/util/database/dialect/*.xml

The SQL bridge

fri.util.database.SqlBridge

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

fri.util.database.statement.*

Data type mapping

fri.util.database.DataTypeMapper

• CHAR
• BINARY
• VARCHAR
• VARBINARY

• NUMBER
• DECIMAL

Primary key generation and mapping

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)

• 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.

Source code hints

How to switch off certain data types by XML configuration

<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.

<DialectConfiguration>
    <TypeNameToIdMap>
        <Type TypeName='TINYINT' InsertValueTypeId='4' /> <!-- must have Types.INTEGER (4) on INSERT -->
        <Type TypeName='SMALLINT' InsertValueTypeId='4' />
    </TypeNameToIdMap>
</DialectConfiguration>

How to exclude system catalogs or schemas by configuration

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

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

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

How to add STORAGE clauses per table to CREATE TABLE

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_INDEXES STORAGE (INITIAL 5 M)");
    }
}

....
ConfiguredDialectFactory.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)

How to introduce another XML configuration attribute

• 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.registerDialect(myDialectInstance);
  
  For migration, override
  
    InstanceDataToXml.newTableExporter(...), or
    InstanceDataToInstanceData.newTableImporter(...) or  newTableExporter(...).
  
  For XmlToInstanceData it is a bit more complicated:
    XmlToInstanceData migration = 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

JDBC Meta Data Table