Re: [OJB-developers] Re: Named Root Objects

[Thomas M. <tho...@ho...>]

Hi Michael,

please excuse me for getting back to you so late. I have been quite busy 
and your mail got lost in my inbox...


Michael Nowotny wrote:

> Hi Thomas!
> 
> Is there a way to let OJB create and remove tables automatically. In my
> opinion such a feature could come in quite handy while developing your app.


That's a good idea. I have been dreaming of full forward/reverse 
engineering support in OJB.

There should be support for:
1. automatic generation (and maybe also execution) of SQL DDL (and a 
repository.xml) from a object model.
2. it should be possible to reverse-engineer database table to 
persistent classes and a repository.xml.
3. It should be possible to write a repository.xml and have both Java 
classes and DB tables generated.
4. convienant support to build mapping from existing Object Model on 
existing tables (maybe similar to the TOPLink mapping workbench).

Maybe XSL transformation is a good vehicle for 1.-3. ?!


> JBoss has such an option. An XSLT Stylesheet that transforms
> the repository XML into sql statements could look like this:
> 
> <?xml version="1.0"?>
> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
> version="1.0">
>   <xsl:output method="text" omit-xml-declaration="yes" indent="yes"/>
> 
>   <xsl:template match="/">
>     <xsl:for-each select="MappingRepository/ClassDescriptor">
>     <!-- generate sql drop table statement-->
>     <xsl:text>drop table </xsl:text><xsl:value-of
> select="table.name"/><xsl:text>;
>     </xsl:text>
>     <!-- generate sql create table statement-->
>     <xsl:text>create table </xsl:text><xsl:value-of
> select="table.name"/><xsl:text> (
>     </xsl:text>
>       <xsl:for-each select="FieldDescriptor">
>         <xsl:value-of select="column.name"/>
>         <xsl:text> </xsl:text>
>         <xsl:value-of select="jdbc_type"/>
>    <xsl:if test="PrimaryKey='true'">
>      <xsl:text> NOT NULL PRIMARY KEY</xsl:text>
>    </xsl:if>
>         <xsl:text>
>         </xsl:text>
>       </xsl:for-each>
>     <xsl:text>);
> 
>     </xsl:text>
>     </xsl:for-each>
>   </xsl:template>
> </xsl:stylesheet>
> 
> What still remains to be done is inserting switches that indicate whether
> you want automatic table creation/removal enabled or not. It might be
> possible to change
> the repository.dtd accordingly.



that's a cool approach ! I tried it and the output looks quite promising 
already.

But there are some caveats:
1. Multiple classes maybe mapped on one and the same table. They may 
even use different columns !
2. OJB allows to have compound primary key which are not handled 
properly by the above xsl stylesheet.
3. The current DTD does not contain size information (e.g. only VARCHAR 
but not VARCHAR(255) !)

having automatic creation configurable is a good idea! In some 
production evironment it won't be viable to have tables changed 
dynamically !


> Another thing I don't know is whether jdbc types are automatically mapped to
> sql types by the jdbc driver.


This is another caveat. It would be possible to deduce the type info for 
a given JDBC ResultSet column from the respective MetaData object.
But to be compatible with a large number of RDBMS OJB relies on an 
explicit type declaration in each FieldDescriptor. (Thus we can e.g. 
have a VARCHAR in the DB, but a byte[] in the Java Object)

Maybe it's a good idea to have the type info optional, as it is 
sufficient for most cases.


Feel free to work on this and contribute some code if you like! 

You have so many creative ideas, why not let some of them come OJB reality ;-)


cu,

Thomas

ps.: have you been digging into the GC idea further ?