[sql] quick heads up...

[James Strachan <ja...@yahoo.co.uk>]

Here's a quick heads up on the commons-sql component. It started with a few
folks trying to refactor the groovy SQL generation stuff from Torque into a
reusable form so that it can be reused on any project that needs to generate
some SQL DDL, such as OJB.

The current site is here...

http://jakarta.apache.org/commons/sandbox/sql/


The basic idea is there's an XML description of the relational database,
such as this...

http://cvs.apache.org/viewcvs.cgi/jakarta-commons-sandbox/sql/src/test-input
/datamodel.xml?rev=HEAD

This can then be loaded, via the DatabaseReader (which uses Betwixt) in the
*.sql.io package into a collection of regular beans in the *.sql.model
package...

http://jakarta.apache.org/commons/sandbox/sql/apidocs/index.html


Then the beans can be processed either with normal Java code or with a
templating tool like Velocity.

The *.sql.builder package contains a number of beans that can be used to
generate the SQL DDL to create and drop tables and constraints for different
database implementations etc. The original Velocity scripts for the DDL
creation for each database configuration were a little hard to maintain and
use IMHO, whereas just using regular java code for this bit seemed to work
out OK. Of course folks can easily plugin Velocity scripts to these beans if
they wish or want to preserve their existing scripts etc.

There's an Ant Task, DDLTask in the *.sql.task package, which uses the beans
above to generate the SQL DDL for different physical databases from a given
XML document describing the schema.

Going foward with some minor refactoring we should be able to generate the
documentation of the database, reverse engineer the logical model of a
database from a given JDBC connection etc. Plus ultimately we could enhance
this simple component to auto-create beans and repository descriptors for
OJB etc.

James
-------
http://radio.weblogs.com/0112098/

__________________________________________________
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com

--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>

Re: [sql] quick heads up...

[John Thorhauer <jo...@zenplex.com>]

On Thu, 2002-09-12 at 14:12, James Strachan wrote:
> There's now SqlBuilder support for Axion, HsqlDb, DB2, Sybase, MSSql, MySql
> and Oracle. Unfortunately its a bit hard to unit test most of those, but at
> least the Java databases (Axion and HsqlDb) are part of the unit test cases
> now. I'm sure we could add other, freely distributable, Java databases to
> the test cases over time too.

Cool.  I will be working on the postgres builder over the next few days.

Thanks,
John Thorhauer
-- 
********************************
** John Thorhauer
** john@zenplex.com
** take a look at:
**  http://tambora.zenplex.org
**  http://www.zenplex.org
**  http://www.zenplex.com
********************************



--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>

Re: [sql] quick heads up...

[James Strachan <ja...@yahoo.co.uk>]

There's now SqlBuilder support for Axion, HsqlDb, DB2, Sybase, MSSql, MySql
and Oracle. Unfortunately its a bit hard to unit test most of those, but at
least the Java databases (Axion and HsqlDb) are part of the unit test cases
now. I'm sure we could add other, freely distributable, Java databases to
the test cases over time too.


Incidentally I've just added an experimental, simple little API for
inserting/updating/deleting data from a database via DynaBeans. Its only a
couple of classes and essentially binds the commons-sql model beans
(Database, Table, Column) to DyanClass, DynaBean and DynaProperty instances.

So you can do things like

// lets parser the model from XML
DatabaseReader reader = new DatabaseReader();
Database model = (Database) reader.parse( "mymodel.xml" );

// JDBC connection pool, maybe using DBCP and Pool from commons
DataSource source = ...;

// now lets add some data
DynaSql dynaSql = new DynaSql(source, model);

DynaBean author = dynaSql.newInstance( "author" );
author.set( "name", "James" );
author.set( "whatever", new Integer(1234));
dynaSql.insert(author);

etc.

Its kinda like an insert/update/delete version of the ResultSetDynaBean in
beanutils.

This can be handy if you want something really simple and easy or need to
create dynamic tables or handle arbitrary database schemas at runtime (which
I have to do at the moment as part of my day job). Though if your persistent
schema is more well defined, maybe tools like OJB or Torque might be more
applicable.

James
-------
http://radio.weblogs.com/0112098/

__________________________________________________
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com

--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>

Re: [sql] quick heads up...

[Henri Yandell <ba...@generationjava.com>]

Is the XML input mandatory? Or does it resolve to a Java interface that I
can implement instead?

On Tue, 10 Sep 2002, James Strachan wrote:

> Here's a quick heads up on the commons-sql component. It started with a few
> folks trying to refactor the groovy SQL generation stuff from Torque into a
> reusable form so that it can be reused on any project that needs to generate
> some SQL DDL, such as OJB.
>
> The current site is here...
>
> http://jakarta.apache.org/commons/sandbox/sql/
>
>
> The basic idea is there's an XML description of the relational database,
> such as this...
>
> http://cvs.apache.org/viewcvs.cgi/jakarta-commons-sandbox/sql/src/test-input
> /datamodel.xml?rev=HEAD
>


--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>