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
proposal.zip
Description: Binary data
-- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
