commons-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tim Anderson" <>
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 :)

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.


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.

  // 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
type, using a set of alternatives types if the type isn't supported.

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

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

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

. 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
  and version string eg:


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




View raw message