The ToDo list..

[Martin van den Bemt <ml...@mvdb.net>]

Hi Tom,

I just read through the ToDo.txt you setup and just wanted to say that 
adding integration with DBUnit is kind of problematic atm (at least on 
apache infrastructure), since DBUnit is LGPL..

Mvgr,
Martin

Re: The ToDo list..

[Martin van den Bemt <ml...@mvdb.net>]

Thomas Dudziak wrote:
> On 10/28/05, Martin van den Bemt <ml...@mvdb.net> wrote:
> 
> 
>>Sweet.. I did some "reverse" mapping in my fork, so I could probably
>>port those mappings to the new stuff you'r doing..
> 
> DdlUtils should IMO at least be able to reverse (via the model reader)
> the database layout it created in the first place (roundtrip).

I agree we first need to get the roundtrip working, though I also think 
the step after that should be to improve the import from an existing 
database, which should give (new) users a nice starting point to start 
using ddl-utils. Currently a pretty big mess is created (withouth having 
looked at your refactorings you may have done in this area though). I 
love the idea to import a mssql schema and directly export it to hsqldb 
without real effort.

> 
> 
>>Maybe we also need some integration test (sub) projects or (ant) build
>>conditionals, so people can test on a specific database when it's present ?
> 
> 
> Mhmm, something like Ant's <if> task should be easily doable. Where do
> you see a use for this ?

This way you can have tests for all supported databasetypes and just 
enable the ones that you actually have installed. Also being able to 
test different drivers per database (eg mssql the MS driver and JTDS) 
can give pretty usefull feedback. Would be nice if we are able to test 
them all. I should be able to run those roundtrip tests on at least the 
java databases, mysql, mssql and oracle.

>>I can have a look at that, but this heavily depends on the reverse
>>mapping working correctly. I also have a method called
>>isUpToDate(Database database), which could also be usefull in ddl-utils
>>and needs a good functioning reverse mapping..
> 
> 
> That would be good!

I'll move it over to ddl-utils..

Mvgr,
Martin

Re: The ToDo list..

[Thomas Dudziak <to...@gmail.com>]

On 10/28/05, Martin van den Bemt <ml...@mvdb.net> wrote:

> Why is the dtd used from torque, is this a mandatory dependency (I don't
>   know who actually heavily depends on ddl-utils)..

Well OJB is (or nearly so), and I honestly don't intend to rewrite the
XDoclet module to a new DTD. Besides, the DTD is not too bad (when
leaving out the Torque stuff which is not necessary for DdlUtils
anyway). Not to mention that one of the goals of bringing commons-sql
to db was to consolidate the database creation/alteration between
Torque and OJB (and OJB is going to switch from Torque to DdlUtils in
the 1.1).

> > That's the next big ToDo on my list. On (I think) this weekend I'll
> > integrate the public Api of the model reader into the Platform and
> > create a structure of the readers similar to the sql builders thus
> > allowing for database-specific handling. Once that is in place, I'll
> > probably add unit tests to ensure at least the roundtrip scenario, eg.
> > XML schems -> database -> XML schema generates the same schema.
>
> Sweet.. I did some "reverse" mapping in my fork, so I could probably
> port those mappings to the new stuff you'r doing..

DdlUtils should IMO at least be able to reverse (via the model reader)
the database layout it created in the first place (roundtrip).

> Maybe we also need some integration test (sub) projects or (ant) build
> conditionals, so people can test on a specific database when it's present ?

Mhmm, something like Ant's <if> task should be easily doable. Where do
you see a use for this ?

> > Hmm, that might be tricky. Probably something for 1.1 or later.
> > What is necessary though is the completion of the existing support for
> > table alteration, esp. regarding foreign keys and the like.
>
> I can have a look at that, but this heavily depends on the reverse
> mapping working correctly. I also have a method called
> isUpToDate(Database database), which could also be usefull in ddl-utils
> and needs a good functioning reverse mapping..

That would be good!

> Can we drop that dependency on the torque dtd ?

IMO that's not a good idea (see above).

> Seperating conceptual and physical schema is a cleaner approach indeed.

and has the nice benefit of being easily integrateable into the Ant
tasks (see the new parameter handling for database creation).

> Nope, not committer on a db project yet, so I am probably not in the db
> group (if that is needed at all with svn?).
>
> > Either way, I could start a vote for you for becoming a DdlUtils
> > committer on the PMC ?
>
> Sounds good to me... If I need to do anything for this, let me know..

I will.

Tom

Re: The ToDo list..

[Martin van den Bemt <ml...@mvdb.net>]

See inline :)

Thomas Dudziak wrote:
> On 10/27/05, Martin van den Bemt <ml...@mvdb.net> wrote:
> 
> 
>>At the time of moving the ddlutils from commons, I had too much
>>outstanding changes that needed to be in production, so I forked
>>commons-sql, using that fork for several projects. Currently I am
>>migrating back to ddlutils, so I can drop the (internal) fork. I am
>>depending on some extensions though that are (probably) not in the
>>current ddl-utils. One part is in the model : I added a javatype to the
>>Table, so I can use that to lookup the table that goes with a specific
>>object. This functionality is also pretty usefull for creating OJB xml
>>files (since they need that type). Also with Column I return the name if
>>the javaName is null, but I can move this one to my personal code (since
>>the model writer will else always write a javaName, even if you don't
>>want that).
> 
> 
> That should be easy to add. DdlUtils does not use it internally (I
> wouldn't know what for anyway), so that's probably only a single line
> in the betwixt mapping and the corresponding accessors for Column and
> for Table each - after all the DTD already contains attributes for
> that.
> In fact, there are a couple of attributes in the DTD that are
> currently not represented in DdlUtils but that might be useful in the
> API.
> 

Why is the dtd used from torque, is this a mandatory dependency (I don't 
  know who actually heavily depends on ddl-utils)..

> 
>>Another part is the JdbcModelReader. I didn't monitor if you did any
>>hacking on that one, but the at least the old modelreader is seriously
>>flawed, since the system doesn't do any mapping from native sql types to
>>ddl-utils generic types, which causes a lot schema comparisons (which I
>>use a lot) to say the real database is different from the xml model. In
>>general it is a big pain to import a model from eg mssqlserver and
>>directly use that model in eg mysql (eg type lengths are set when there
>>shouldn't be, dtproperties system table is imported, etc, etc). We will
>>hit some limits with this in the future, because of different
>>capabilities of the database system (ehh Platforms as it's called now),
>>but I think we can get it as close as possible.
> 
> 
> That's the next big ToDo on my list. On (I think) this weekend I'll
> integrate the public Api of the model reader into the Platform and
> create a structure of the readers similar to the sql builders thus
> allowing for database-specific handling. Once that is in place, I'll
> probably add unit tests to ensure at least the roundtrip scenario, eg.
> XML schems -> database -> XML schema generates the same schema.

Sweet.. I did some "reverse" mapping in my fork, so I could probably 
port those mappings to the new stuff you'r doing..
Maybe we also need some integration test (sub) projects or (ant) build 
conditionals, so people can test on a specific database when it's present ?

> 
> 
>>Also a big todo (not in a hurry on this though) on my list (I saw
>>something similar to the ToDo.txt in SVN) is upgrading from one xml
>>database model to another database model and therefore make use of the
>>version identifier in the Database object. Since moving from model to
>>model in most cases need some kind of conversion of data, we should add
>>some kind of way people can add a conversion strategy between 2 model
>>versions. First a simple API and maybe later some more specifics, based
>>on experience (eg documention, examples) of best practices in this regard.
> 
> 
> Hmm, that might be tricky. Probably something for 1.1 or later.
> What is necessary though is the completion of the existing support for
> table alteration, esp. regarding foreign keys and the like.

I can have a look at that, but this heavily depends on the reverse 
mapping working correctly. I also have a method called 
isUpToDate(Database database), which could also be usefull in ddl-utils 
and needs a good functioning reverse mapping..

> 
> 
>>Also support for views, triggers and stored procedures would be nice,
>>maybe starting of with simple support for them by means of pure sql eg
>><table name="SiteView" type="VIEW" javaType="some.nice.type">
>>   <column name="id" javaName="id"/>
>>   <sql platform="MsSql">
>>     <![CDATA[
>>        the mssql view select
>>     ]]>
>>   </sql>
>>   <sql platform="HsqlDb">
>>     <![CDATA[
>>       the hsqldb view select
>>     ]]>
>>   </sql>
>></table>
>>This needs more consideration I you can imagine, though DynaSql would be
>>quite a nice fit for this and it allows easier migration from current
>>database schema's to ddl utils.
> 
> 
> I'm not so sure whether that is something DdlUtils should provide (not
> to mention that I got no control over the DTD so adding the sql is
> tricky).

Can we drop that dependency on the torque dtd ?

> Perhaps something better would be to allow extension points using the
> constructs in the schema. Eg. when DdlUtils creates table A, then some
> user-specified stuff might be executed before or after (think of
> aspects). Per default this might be the plain execution of SQL.
> This way, the database-specific stuff could be put into a separate
> file with one file per database type which IMO makes a nice
> separation.
> E.g.
> 
> <extension table="A" for-action="creation" application-type="post">
>   <sql>
>     ...
>   </sql>
> </extension>
> 
> or something of the like

Seperating conceptual and physical schema is a cleaner approach indeed.

> 
> 
>>Btw my current main development is for hsqldb & mysql. I also have
>>access to MsSqlserver 2000 (and soon 2005) and Oracle.
> 
> 
> That's cool, esp. regarding Sql Server which is notoriously difficult.
> 
> 
>>Btw I tried committing a couple of times in the past, but I don't seem
>>to be a member yet, do I have to ask the db pmc to make that happen (I
>>can see if I can find the original move request if that helps).
> 
> 
> Yep, I think so, though perhaps I should do that. Do you already have
> commit rights for any DB project ?

Nope, not committer on a db project yet, so I am probably not in the db 
group (if that is needed at all with svn?).

> Either way, I could start a vote for you for becoming a DdlUtils
> committer on the PMC ?

Sounds good to me... If I need to do anything for this, let me know..

Mvgr,
Martin

Re: The ToDo list..

[Thomas Dudziak <to...@gmail.com>]

On 10/27/05, Martin van den Bemt <ml...@mvdb.net> wrote:

> At the time of moving the ddlutils from commons, I had too much
> outstanding changes that needed to be in production, so I forked
> commons-sql, using that fork for several projects. Currently I am
> migrating back to ddlutils, so I can drop the (internal) fork. I am
> depending on some extensions though that are (probably) not in the
> current ddl-utils. One part is in the model : I added a javatype to the
> Table, so I can use that to lookup the table that goes with a specific
> object. This functionality is also pretty usefull for creating OJB xml
> files (since they need that type). Also with Column I return the name if
> the javaName is null, but I can move this one to my personal code (since
> the model writer will else always write a javaName, even if you don't
> want that).

That should be easy to add. DdlUtils does not use it internally (I
wouldn't know what for anyway), so that's probably only a single line
in the betwixt mapping and the corresponding accessors for Column and
for Table each - after all the DTD already contains attributes for
that.
In fact, there are a couple of attributes in the DTD that are
currently not represented in DdlUtils but that might be useful in the
API.

> Another part is the JdbcModelReader. I didn't monitor if you did any
> hacking on that one, but the at least the old modelreader is seriously
> flawed, since the system doesn't do any mapping from native sql types to
> ddl-utils generic types, which causes a lot schema comparisons (which I
> use a lot) to say the real database is different from the xml model. In
> general it is a big pain to import a model from eg mssqlserver and
> directly use that model in eg mysql (eg type lengths are set when there
> shouldn't be, dtproperties system table is imported, etc, etc). We will
> hit some limits with this in the future, because of different
> capabilities of the database system (ehh Platforms as it's called now),
> but I think we can get it as close as possible.

That's the next big ToDo on my list. On (I think) this weekend I'll
integrate the public Api of the model reader into the Platform and
create a structure of the readers similar to the sql builders thus
allowing for database-specific handling. Once that is in place, I'll
probably add unit tests to ensure at least the roundtrip scenario, eg.
XML schems -> database -> XML schema generates the same schema.

> Also a big todo (not in a hurry on this though) on my list (I saw
> something similar to the ToDo.txt in SVN) is upgrading from one xml
> database model to another database model and therefore make use of the
> version identifier in the Database object. Since moving from model to
> model in most cases need some kind of conversion of data, we should add
> some kind of way people can add a conversion strategy between 2 model
> versions. First a simple API and maybe later some more specifics, based
> on experience (eg documention, examples) of best practices in this regard.

Hmm, that might be tricky. Probably something for 1.1 or later.
What is necessary though is the completion of the existing support for
table alteration, esp. regarding foreign keys and the like.

> Also support for views, triggers and stored procedures would be nice,
> maybe starting of with simple support for them by means of pure sql eg
> <table name="SiteView" type="VIEW" javaType="some.nice.type">
>    <column name="id" javaName="id"/>
>    <sql platform="MsSql">
>      <![CDATA[
>         the mssql view select
>      ]]>
>    </sql>
>    <sql platform="HsqlDb">
>      <![CDATA[
>        the hsqldb view select
>      ]]>
>    </sql>
> </table>
> This needs more consideration I you can imagine, though DynaSql would be
> quite a nice fit for this and it allows easier migration from current
> database schema's to ddl utils.

I'm not so sure whether that is something DdlUtils should provide (not
to mention that I got no control over the DTD so adding the sql is
tricky).
Perhaps something better would be to allow extension points using the
constructs in the schema. Eg. when DdlUtils creates table A, then some
user-specified stuff might be executed before or after (think of
aspects). Per default this might be the plain execution of SQL.
This way, the database-specific stuff could be put into a separate
file with one file per database type which IMO makes a nice
separation.
E.g.

<extension table="A" for-action="creation" application-type="post">
  <sql>
    ...
  </sql>
</extension>

or something of the like

> Btw my current main development is for hsqldb & mysql. I also have
> access to MsSqlserver 2000 (and soon 2005) and Oracle.

That's cool, esp. regarding Sql Server which is notoriously difficult.

> Btw I tried committing a couple of times in the past, but I don't seem
> to be a member yet, do I have to ask the db pmc to make that happen (I
> can see if I can find the original move request if that helps).

Yep, I think so, though perhaps I should do that. Do you already have
commit rights for any DB project ?
Either way, I could start a vote for you for becoming a DdlUtils
committer on the PMC ?

> Sorry for the long mail :)

Don't worry :-)

Tom

Re: The ToDo list..

[Martin van den Bemt <ml...@mvdb.net>]

Hi Thomas,

> 
> Hi Martin, nice to have you here :-)
> 

Thanx :)

At the time of moving the ddlutils from commons, I had too much 
outstanding changes that needed to be in production, so I forked 
commons-sql, using that fork for several projects. Currently I am 
migrating back to ddlutils, so I can drop the (internal) fork. I am 
depending on some extensions though that are (probably) not in the 
current ddl-utils. One part is in the model : I added a javatype to the 
Table, so I can use that to lookup the table that goes with a specific 
object. This functionality is also pretty usefull for creating OJB xml 
files (since they need that type). Also with Column I return the name if 
the javaName is null, but I can move this one to my personal code (since 
the model writer will else always write a javaName, even if you don't 
want that).
Another part is the JdbcModelReader. I didn't monitor if you did any 
hacking on that one, but the at least the old modelreader is seriously 
flawed, since the system doesn't do any mapping from native sql types to 
ddl-utils generic types, which causes a lot schema comparisons (which I 
use a lot) to say the real database is different from the xml model. In 
general it is a big pain to import a model from eg mssqlserver and 
directly use that model in eg mysql (eg type lengths are set when there 
shouldn't be, dtproperties system table is imported, etc, etc). We will 
hit some limits with this in the future, because of different 
capabilities of the database system (ehh Platforms as it's called now), 
but I think we can get it as close as possible.
Also a big todo (not in a hurry on this though) on my list (I saw 
something similar to the ToDo.txt in SVN) is upgrading from one xml 
database model to another database model and therefore make use of the 
version identifier in the Database object. Since moving from model to 
model in most cases need some kind of conversion of data, we should add 
some kind of way people can add a conversion strategy between 2 model 
versions. First a simple API and maybe later some more specifics, based 
on experience (eg documention, examples) of best practices in this regard.
Also support for views, triggers and stored procedures would be nice, 
maybe starting of with simple support for them by means of pure sql eg
<table name="SiteView" type="VIEW" javaType="some.nice.type">
   <column name="id" javaName="id"/>
   <sql platform="MsSql">
     <![CDATA[
        the mssql view select
     ]]>
   </sql>
   <sql platform="HsqlDb">
     <![CDATA[
       the hsqldb view select
     ]]>
   </sql>
</table>
This needs more consideration I you can imagine, though DynaSql would be 
quite a nice fit for this and it allows easier migration from current 
database schema's to ddl utils.

Let me know what your opinion is on these things..

Btw my current main development is for hsqldb & mysql. I also have 
access to MsSqlserver 2000 (and soon 2005) and Oracle.

Btw I tried committing a couple of times in the past, but I don't seem 
to be a member yet, do I have to ask the db pmc to make that happen (I 
can see if I can find the original move request if that helps).

Sorry for the long mail :)


Mvgr,
Martin

> I didn't plan to add DBUnit anywhere in DdlUtils, only provide a few
> classes that can be used by somebody using DBUnit to utilize DdlUtils'
> abilities regarding schemas and data insertion. Do you think that
> providing an extension jar file containing only these class but
> without the DBUnit jar is a problem ? It's not that important anyway,
> but may be a nice thing to have and is not really difficult to
> achieve.
> 
> Tom
> 
> 

Re: The ToDo list..

[Thomas Dudziak <to...@gmail.com>]

On 10/25/05, Martin van den Bemt <ml...@mvdb.net> wrote:

> I just read through the ToDo.txt you setup and just wanted to say that
> adding integration with DBUnit is kind of problematic atm (at least on
> apache infrastructure), since DBUnit is LGPL..

Hi Martin, nice to have you here :-)

I didn't plan to add DBUnit anywhere in DdlUtils, only provide a few
classes that can be used by somebody using DBUnit to utilize DdlUtils'
abilities regarding schemas and data insertion. Do you think that
providing an extension jar file containing only these class but
without the DBUnit jar is a problem ? It's not that important anyway,
but may be a nice thing to have and is not really difficult to
achieve.

Tom