XML-DBMS, Version 2.0

Java Middleware for Transferring Data between XML Documents and Relational Databases

1.0 Introduction

XML-DBMS is a set of Java packages for transferring data between XML documents and relational databases. This is the documentation for XML-DBMS version 2.0. If you are not familiar with XML-DBMS, see the version 1.01 readme file. Although the mapping language in version 2.0 is different from the mapping language in version 1.x, the basic ideas are the same.

Version 2.0 adds a number of new features, including:

• Updates and deletes, including insert-or-update semantics
• A filter language for use when retrieving results
• Heterogeneous joins
• Database-generated keys and per-table key generation
• Connection and statement pooling
• Generation of maps from database schema
• Generation of DTDs from maps
• Specifying the order of generated elements
• Abstract types, such as are found in XML Schemas
• Per-property formatting, as well as number formatting and custom formatting
• A command line tool for transferring data
• A command line tool for generating maps, DTDs, and CREATE TABLE statements.

2.0 Getting started

2.1 System requirements

To run XML-DBMS, you need the following software:

• XML-DBMS
  
  Download: http://www.rpbourret.com/xmldbms/index.htm
  
  
• JDK (Java Development Kit) 1.1.x or later
  
  Download: http://java.sun.com/products/jdk/1.1/index.html
  
  
• A relational database such as Oracle, DB2, Informix, MS Access, or MySQL
  
  You will need to buy this or find a free relational database on the Web. If you are thinking about using XML-DBMS, you probably already have a relational database.
  
  
• A JDBC driver for your database
  
  Most relational databases are shipped with JDBC and ODBC drivers. If you have an ODBC driver but not a JDBC driver, you can use an JDBC-ODBC bridge as your JDBC driver. This converts JDBC calls to ODBC calls. An experimental (and therefore somewhat buggy) JDBC-ODBC bridge is shipped with the JDK; JDBC-ODBC bridges are available from other companies as well. Note that the quality of JDBC drivers varies considerably, so if one JDBC driver does not work, you can try another. For a list of available JDBC drivers, see:
  
  http://industry.java.sun.com/products/jdbc/drivers
  
  
• JDBC 2.0 Optional Package Binary
  
  This is named jdbc2_0-stdext.jar and contains the javax.sql classes. If you are using JDK 1.2 or 1.3, you might already have this. Check your directories to be sure. If you are using JDK 1.1.x, you will always need to download this. To download, go to the JDBC 2.0 Optional Package section of the following site and click the "continue" button for JDBC 2.0 Optional Package Binary.
  
  Download: http://java.sun.com/products/jdbc/download.html
  
  
• JNDI Package Binary
  
  This is named jndi.jar and contains the javax.naming classes. If you are using JDK 1.2 or 1.3, you should already have this. Check your directories to be sure. If you are using JDK 1.1.x, you need to download this only if you want to rebuild the XML-DBMS jar. To download, go to the JNDI 1.2.1 section of the following site and click the "continue" button, then accept the license agreement, and then download the jndi-1_2_1.zip file.
  
  Download: http://java.sun.com/products/jndi/
  
  
• A DataSource that implements connection and statement pooling
  
  XML-DBMS ships four different DataSources that implement connection and statement pooling. However, these are simple implementations and you might get better performance and/or more options from other products. If your JDBC driver includes a DataSource that implements both connection and statement pooling, you can use that. You can also use a third-party DataSource, such as that from PoolMan. (PoolMan is Open Source.)
  
  Download: http://www.codestudio.com/PoolMan/
  
  
• An XML parser written in Java that supports SAX 2 and DOM Level 2 Core
  
  Both commercial and Open Source XML parsers are available. For a partial list of available parsers, see:
  
  http://www.xmlsoftware.com/parsers/
  http://www.garshol.priv.no/d ownload/xmltools/cat_ix.html#SC_XML
  
  

2.2 Downloading and installing XML-DBMS

To download XML-DBMS version 2.0, see:

• http://www.rpbourret.com/xmldbms/index.htm

To install XML-DBMS version 2.0 from a .zip file:

1. Unzip the downloaded file with a tool such as jar or WinZip.

2. Add xmldbms20.jar to your CLASSPATH. For example:
   
   

      c:\java\xmldbms20\xmldbms20.jar;c:\java\jdk1.2.1\bin;c:\java\xerces-1_4_4\xerces.jar;.\
   

To install XML-DBMS version 2.0 from CVS:

1. Follow the instructions on:
   
   http://sourceforge.net/cvs/?group_id=13613
   
   Version 2.0 code is in the org module, DTDs are in the dtds module, and sample applications, maps, etc. are in the samples module.

2. Add your CVS directory to your CLASSPATH. For example:
   
   

      [CVS directory];c:\java\jdk1.2.1\bin;c:\java\xerces-1_4_4\xerces.jar;.\
   

3. Compile the .java files.

3.0 Using version 2.0

You can use XML-DBMS in one of two ways:

• Through the Transfer tool
• Through your own application

If you write your own application, please note that version 2.0 is NOT backwards-compatible with version 1.x.

3.1 Configuring XML-DBMS

Regardless of how you use XML-DBMS, you will need to configure it to use your database, XML documents, XML parser, etc. This consists of the following steps:

1. Write or generate a 2.0 map file, or convert a 1.x map file to 2.0. For more information, see Using the MapManager tool, Converting 1.x maps to 2.0 maps, and xmldbms2.dtd.

2. If you will use the Transfer tool to transfer data from XML to the database or to delete data, write one or more action documents. You also need action documents if you are calling DOMToDBMS or DBMSDelete directly and want to perform more than a single action on the data in your document -- for example, you want to mix inserts and insert-or-updates. For more information, see actions.dtd.

3. If you will transfer data from the database to XML or delete data, write one or more filter documents. For more information, see filters.dtd.

4. Write/compile implementations of interfaces as needed. For more information, see Interfaces.

3.2 Converting 1.x maps to 2.0 maps

XML-DBMS version 2.0 ships with an XSLT style sheet that converts 1.x map files to 2.0 map files. This is named V1MapToV2Map.xslt and can be found in the directory you installed XML-DBMS in. Thanks to Dan Wolfe for writing this.

WARNING: The conversion is only partial -- you need to edit the resulting XSLT file -- but should help in converting large numbers of map files. Changes to the XSLT file that make the conversion more complete are welcome.

3.3 Using the MapManager tool

The MapManager tool in the org.xmlmiddleware.xmldbms.tools package provides a simple command line interface for generating maps and related documents, such as DTDs and CREATE TABLE statements. It also provides a simple, high-level API for people writing map editing tools. It is designed so all configuration information (which parser and JDBC driver to use, database URL, etc.) can be stored in properties files.

As a general rule, you will start with a DTD and generate a map file and a set of CREATE TABLE statements, or you will start with a database schema and generate a map file and a DTD. Regardless of where you start, you will probably need to edit the resulting map file and DTD / database schema by hand. There are two reasons for this. First, the generation tools cannot possibly predict some things, such as the data types to use when generating a map from a DTD. Second, your database structure will rarely exactly match your XML document structure. Although XML-DBMS can perform some simple transformations, you may need to map the database to a document with a different structure than yours (but which XML-DBMS can handle) and then use XSLT to transform this document to/from your own document when you actually transfer data.

3.3.1 Using the MapManager tool from the command line

The command line interface is the easiest way to use MapManager. With this, you pass information to MapManager as a series of property/value pairs. For example, the following command generates a map file and a database schema from a DTD:

   java org.xmlmiddleware.xmldbms.tools.MapManager File1=xerces.props File2=db.props
          Input=DTD DTDFile=orders.dtd
          Output1=Map MapFile=orders.map Output2=SQL SQLFile=orders.sql

The Input property specifies the source of the map; legal values are Map (a map file), DTD, and Database. The Output property specifies what generate from the map; legal values are Map (generate a map file), DTD (generate a DTD file), and SQL (generate a file with CREATE TABLE statements). If you want more than one output, use numbered output properties (Output1, Output2, etc.) The XxxxFile properties specify what files to read from / write to.

The File1 and File2 properties indicate that the property values are the names of property files. These are particularly useful for properties that don't change often, such as parser and database properties.

NOTE: People who are not using XML-DBMS can still use the MapManager tool to generate DTDs from database schema and vice versa. To do this, just specify an Input value of DTD and an Output value of SQL, or an Input value of Database and an Output value of DTD, along with any other necessary properties.

For complete details about MapManager's command line syntax and what other properties are available, see the JavaDocs for MapManager.

3.3.2 Using the MapManager tool API

You can also use MapManager programmatically. This is generally useful only if you are writing a map editing tool, such as a GUI-based map editor. MapManager provides a very simple API that consists of the following methods:

• compileMap
• createMapFromDatabase
• createMapFromDTD
• writeMap
• createDTD
• createSQL

For complete details, see the the JavaDocs for MapManager.

3.3.3 Samples

The MapManager comes with a small set of samples, which you can find in the samples subdirectory:

   orders.dtd -- DTD for a sales order document

There is also a DOS batch file (manage.bat) that runs the MapManager tool. It assumes that the parser properties are in parser.props, the database properties are in db.props, and the map manager properties (what you actually want to do) are in a file passed in on the command line. To use this, just type the following:

   manage <property-file>

For example:

   manage map.props

is equivalent to:

   java org.xmlmiddleware.xmldbms.tools.MapManager File1=parser.props File2=db.props 
File3=map.props

The samples directory includes the following sample property files:

   parser.props -- Parser properties for the Xerces parser
   db.props     -- Database properties to use the Sun JDBC-ODBC Bridge
   map.props    -- File to read a DTD and create a map file and an SQL file

3.4 Using the Transfer tool

The Transfer tool in the org.xmlmiddleware.xmldbms.tools package provides a simple command line interface to XML-DBMS, as well as a simple, high-level API. It is designed so all configuration information (which parser and JDBC driver to use, database URL, etc.) can be stored in properties files.

3.4.1 Using the Transfer tool from the command line

The command line interface is the easiest way to use Transfer. With this, you pass information to Transfer as a series of property/value pairs. For example, the following command stores the contents of the orders.xml document in the database:

   java org.xmlmiddleware.xmldbms.tools.Transfer File1=xerces.props File2=db.props
          Method=StoreDocument MapFile=orders.map XMLFile=orders.xml ActionFile=orders.act

The Method property specifies the method to call (StoreDocument) and the MapFile, XMLFile, and ActionFile properties specify the names of the map, XML, and action files to use. The File1 and File2 properties indicate that the property values are the names of property files. These are particularly useful for properties that don't change often, such as parser and database properties.

For complete details about Transfer's command line syntax and what other properties are available, see the JavaDocs for Transfer.

3.4.2 Using the Transfer tool API

You can also use Transfer programmatically. It provides a very simple API for XML-DBMS that consists of the following methods:

• setDatabaseProperties
• storeDocument (variations for passing XML as a file, a string, and an InputStream)
• retrieveDocument (variations for retrieving XML as a file or a string, by filter or by result set)
• deleteDocument

Transfer is optimized to reuse things like database connections and compiled map objects on subsequent calls, so it is a reasonably efficient way to use XML-DBMS with little effort. Again, for complete details, see the the JavaDocs for Transfer.

3.4.3 Samples

XML-DBMS comes with a small set of samples, which you can find in the samples subdirectory. Most of these are sample XML, map, action, and filter files:

   orders.xml       -- XML document showing a sales order
   orders.sql       -- CREATE TABLE statements for the tables to which orders.xml is mapped
   orders.map       -- Map file mapping orders.xml to four tables in the database
   orders1.act      -- Action file for to insert orders.xml
   orders2.act      -- Action file to update the item quantity in orders.xml
   delete.act       -- Action file to delete orders and items but not customers or parts
   orders.ftr       -- Filter file that uses only a root table filter
   ordersconst.ftr  -- Filter file that uses constant values in WHERE clauses
   ordersparams.ftr -- Filter file that uses named parameters in WHERE clauses
   ordersrs.ftr     -- Filter file that uses result sets

There is also a DOS batch file (transfer.bat) that runs the Transfer tool. It assumes that the parser properties are in parser.props, the database properties are in db.props, and the method properties (what you actually want to do) are in a file passed in on the command line. To use this, just type the following:

   transfer <property-file>

For example:

   transfer store.props

is equivalent to:

   java org.xmlmiddleware.xmldbms.tools.Transfer File1=parser.props File2=db.props 
File3=store.props

The samples directory includes the following sample property files:

   parser.props   -- Parser properties for the Xerces parser
   db.props       -- Database properties to use the Sun JDBC-ODBC Bridge
   delete.props   -- Method properties to delete an order according to delete.act and orders.ftr
   retrieve.props -- Method properties to retrieve an order according to orders.ftr
   store.props    -- Method properties to store an order according to orders1.act

3.5 Writing your own application

For the most control over XML-DBMS, you can write your own application. Here are the basic steps:

1. Write code to set up your databases:

   1. Call MapCompiler.compile to compile your map.
   2. Instantiate a DataSource for each database you will use.
   3. Call MetadataInitializer if you did not include database metadata in your map file.
   4. Instantiate a DataHandler for each database you will use.
   5. Build a TransferInfo object from your Map and each of your DataHandlers.

2. Write code to transfer data from an XML document to the database (if needed):

   1. Call ActionCompiler.compile to compiler your action documents (if used).
   2. Instantiate DOMToDBMS and set any key generators and the commit mode.
   3. Open your document using ParserUtils.openDocument().
   4. Call DOMToDBMS.processDocument and pass the TransferInfo, DOM tree, and Actions object (or just specify a single action).

3. Write code to transfer data from the database to an XML document (if needed):

   1. Call FilterCompiler.compile to compiler your filter documents.
   2. Build a Hashtable with containing the parameters used in your filter (if any).
   3. Instantiate DBMSToDOM.
   4. Call DOMToDBMS.retrieveDocument.

4. Write code to delete data from the database (if needed):

   1. Call ActionCompiler.compile to compiler your action documents (if used).
   2. Call FilterCompiler.compile to compiler your filter documents.
   3. Build a Hashtable with containing the parameters used in your filter (if any).
   4. Instantiate DBMSDelete.
   5. Call DBMSDelete.deleteDocument.

For examples of code that does this, see the following methods in the Transfer tool:

• storeDocumentInternal
• retrieveDocumentInternal (two variations -- one uses filters and the other uses result sets)
• deleteDocument

Note that the code in Transfer is probably more complex than most of your code will be. This is because Transfer needs to retrieve information from properties and check that it has gotten the correct information.

3.6 Interfaces

XML-DBMS abstracts a number of types of functionality through interfaces. Depending on your environment, you may need to write and/or compile implementations for these interfaces and add these to your CLASSPATH or the xmldbms20.jar file. The following interfaces are used:

• javax.sql.DataSource. Provides JDBC Connections. Normally, you will use a DataSource shipped with your JDBC driver, a third-party DataSource, or one of the DataSources shipped with XML-DBMS.

• org.xmlmiddleware.conversions.formatters.StringFormatter. Used for string formatting. You need to implement this only if the capabilities of the string formatters shipped with XML-DBMS are insufficient. XML-DBMS has built-in date and number formatters.

• org.xmlmiddleware.xmlutils.ParserUtils. Abstracts parser functionality. You will need to implement this for any parser except Xerces.

• org.xmlmiddleware.xmldbms.datahandlers.DataHandler. Executes SQL statements. In most cases, you can use GenericHandler or one of the database-specific implementations shipped with XML-DBMS.

• org.xmlmiddleware.xmldbms.keygenerators.KeyGenerator. Generates keys. If you want to generate keys yourself, you must implement this. If you want to use the high-low scheme from XML- DBMS v1.01, start with the v1.1 version of this.

The following table shows what implementations are included in XML-DBMS version 2.0, and whether compiled versions of these are in xmldbms20.jar.

3.7 Known bugs

Here is a list of known bugs in XML-DBMS version 2.0:

• With most drivers, Sybase and Microsoft SQL Server cannot retrieve data that is nested more than one table deep.
• Ordering at the root level is not yet supported by DBMSToDOM.
• The All element in the action language is not supported.
• The Extends element in the mapping language is not supported. This will not be fixed until version 2.1.
• The CREATE TABLE statements generated by DDLGenerator may include duplicate constraint names, which are illegal.
• Update and update-or-insert capabilities do not support updating order columns. That is, order columns are guaranteed to be correct only when inserting data. This is unlikely to be fixed.

3.8 Missing components

The following components have not yet been added to XML-DBMS version 2.0. If you are interested in implementing these features, please let me know.

• ActionSerializer
• MapFactory_W3CSchemas
• KeyGeneratorHighLow
• Additional implementations of ParserUtils
• Support for binary data using Base64

4.0 Licensing and support

4.1 Licensing

XML-DBMS, along with its source code, is in the public domain. It is freely available for use in both commercial and non-commercial settings. It is not copyrighted and has absolutely no warranty. The following limitations apply:

The software is provided "as is", without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fitness for a particular purpose, and noninfringement. In no event shall the author(s) be liable for any claim, damages, or other liability, whether in an action of contract, tort, or otherwise, arising from, out of, or in connection with the software or the use or other dealings in the software.

4.2 Support

If you have questions about how to use XML-DBMS, the first thing you should do is read the documentation and look at the sample programs. If you want to check for known bugs, you have two options. First, check the XML-DBMS bug database on SourceForge. Second, look at the archives of the XML-DBMS mailing list, which lists many known bugs and frequently asked questions.

If you still have a question, you can send email to the mailing list at xml-dbms@yahoogroups.com. Although you must be a member of the mailing list to post messages, anyone can read the messages in the archives. You can join the mailing list and read the archives at:

http://groups.yahoo.com/group/xml-dbms

4.3 How can I help?

XML-DBMS is an ongoing Open Source project and depends on users for feedback, bug reports, and new code. The best way to help out is to join the mailing list and actively participate in discussions, find and fix bugs, suggest new features, and submit changes you have made for inclusion in future releases. For the latest news, check the XML-DBMS home page at:

http://www.rpbourret.com/xmldbms/index.htm

or the XML-DBMS project page on SourceForge:

https://sourceforge.net/projects/xmldbms/

4.4 Contributors

Thanks to the many people who have contributed to XML-DBMS in the form of bug reports and suggested features. And special thanks to the following people, who have contributed significant amounts of code and design work:

Adam Flinton
Nick Semenov
Sean Walter
Tobias Schilgen
Himanshu Gupta