commons-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tim Anderson" <t...@intalio.com>
Subject [commons][sql] Write once, run anywhere support for DDL
Date Sun, 22 Sep 2002 09:00:29 GMT
Hi there,
	attached is a proposal for an extension to commons-sql which aims
to provide 'Write once, run anywhere support for DDL (c)' - in
theory at least :)

Background
----------
In OpenJMS, we currently support a number of databases and
get the odd request to support a few more. This has resulted
in database specific SQL scripts which are a pain to maintain.
The main problem is that not all vendors support all datatypes.

To get round this, we tinkered with a tool to dynamically create SQL
based on meta-data reported by a database connection. This has been mostly
successful, except for where vendor X's DatabaseMetaData implementation
doesn't correctly report the types it supports.

Overview
--------

The attached proposal adapts the work from OpenJMS, but will hopefully prove
to be more robust. It can be used to generate SQL from a connection,
or from a stored definition describing the meta data that a particular
implementation supports.

Eg.
  // construct a builder to execute DDL based on a schema
  // and an active connection
  Database myDatabase = // get database schema
  DataSource source = // get a data source
  Connection connection = source.getConnection();
  SqlBuilder builder = new DynamicBuilder(connection);
  DDLExecutor executor = new DDLExecutor(source, builder);

  executor.createDatabase(myDatabase, true);

The DynamicBuilder class is a simple builder implementation which
extracts the types that the database supports into a TypeSet.
This TypeSet is used by a TypeMapper to map the requested Column types
to those supported by the target database.

The TypeMapper implementation tries to find the closest match to the
requested
type, using a set of alternatives types if the type isn't supported.


Limitations
-----------
The above is limited by the fact that
. you need a connection to the database in order to generate SQL
. it relies on reported meta-data being correct.

To get round this, TypeSet instances can be generated, verified/massaged
and stored:
  TypeSet types = TypeSetFactory.create(connection);
  TypeSetWriter writer = new TypeSetWriter(out);
  wrtier.write();

An SqlBuilder could then use stored TypeSets to map column types eg.

  TypeSetReader reader = new TypeSetReader(in);
  TypeSet types = (TypeSet) reader.read();
  TypeMapper mapper = new TypeMapper(set);

Uses
----
. The TypeSet and TypeMapper classes can be integrated with the existing
  SqlBuilder classes, to support automatic mapping of types.

. SqlBuilderFactory could be extended so that SQL could be generated for
  specific versions of a DB eg:

	SqlBuilder builder = SqlBuilderFactory.newSqlBuilder("oracle", "8i");

. TypeSet instances could be stored in META-INF, and resolved using the
vendor
  and version string eg:

    /META-INF/typesets/oracle/8i/typeset.xml
    /META-INF/typesets/oracle/9i/typeset.xml
    /META-INF/typesets/mysql/typeset.xml
    /META-INF/typesets/hsqldb/typeset.xml

Status
------
. DefaultTypeMapper is incomplete
. Need to sort out betwixt serialization of TypeSet
. no test cases ;)
. WIP!

Thoughts?

Thanks,

Tim




Mime
View raw message