DB codgen next steps

[Rainer Döbele <do...@esteam.de>]

Hi everyone,

I have now checked in Benjamin's patch of Oct 26th.
I have changed some bits about the logging and the exclusion of tables containing a '$' in populateTableMetaData() in order to test it with Oracle. Also I had to add one Apache license header in DBUtil.java.

For our next steps I would like to make the following suggestions:

1. At the moment we're parsing metadata obtained via JDBC into generator specific classes (Database, Table, Column) which hold the properties required for the velocity templates. The classes are simple bean type classes.
However wouldn't it be much smarter if the metadata would be parsed directly into an Empire-db class hierarchy i.e. use DBDatabase, DBTable, DBColumn etc.? 
This would then allow to use the class hierarchy directly even without the generation of the class files.

In a second step the class files should then be generated from an Empire-db database (DBDatabase). This step should be independent from step one, so that in theory one could provide the class hierarchy in java and have the same classes generated again. 
For properties required for the generation process which are not available in the corresponding Empire-db classes we probably need to provide a helper class or provide them through an extension of the database class.

2. Afterwards we should be are able to test and debug it on different database systems. I have currently tested it with the DBSAMPLE data model on Oracle and I can do the same for Microsoft SQL-Server. We should make sure that it works with the DBSAMPLE and DBSAMPLEADV data model on all DBMS that we currently support. All committers please let everyone else know what you are developing and testing with.

3. I assume that although JDBC should retrieve metadata in a consistent way there will be aspects that are specific for a certain DBMS (like the exclusion of tables containing a '$' symbol in Oracle). This should be handled by the driver. At the moment however we're not using the Empire-db database drivers and we need to think about how we can let the driver intercept and modify the generation process.

Suggestion 2 and 3 can wait. Most important for now is suggestion No 1.
Thomas and Benjamin what do you thing about this idea?
Anyone volunteering for getting this done?

Regards
Rainer

Re: DB codgen next steps

[Francis De Brabandere <fr...@gmail.com>]

sounds like a good idea to me

On Sun, Nov 8, 2009 at 9:39 PM, Rainer Döbele <do...@esteam.de> wrote:
> Hi Benni,
>
> I am sorry that I have not been able to respond earlier - job and family have just been to busy recently.
>
> I am not 100% sure whether you fully understood what I intended to say - so I'll try again with different words:
> Basically it is that I would perfer - if possible of course - to split the db code generation into a two step process.
>
> In the first step - the parsing - we obtain the metadata of an existing database and generate an in-memory object model using the Empire-db classes. Hence the step could be possibly implemented with a function that takes a connection and some more info (i.e. via the CodeGenConfig object) and that returns a new DBDabase object which contains all the tables, views, references etc. The function may look like this:
>
>    public DBDatabase parseDataModel(Connection conn, CodeGenConfig config)
>
> The second step takes provides the functionality to take any DBDatabase object - no matter whether it's the outcome of the parse process or taken from somewhere else - and generates the corresponding class files.
> The method for this step could look like this:
>
>    public void generateCodeFiles(DBDatabase db, CodeGenConfig config)
>
> Ideally the objects in org.apache.empire.db.codegen.types would not be required any more.
> The logic we have not in those classes should then all go into one class (possible called CodeGenParser).
> The aim should also be not to extend the Empire-db core classes just for the generation process.
> But should this be nessary and there is no other way then I would consider that too.
>
> This would mean quite a new structure of our project - but I believe it would be worth it.
> Now do you feel you could handle this?
>
> If so, then please feel free to go ahead (unless anyone has got another idea).
> If I can be of any help, please let me know.
>
> Regards
> Rainer
>
>
> benniven@web.de wrote:
>> Re: DB codgen next steps
>>
>> Hi Rainer,
>>
>> I really appreciate that my patch was useful, and I'd like to join up
>> for the next issue.
>>
>> Just a summary about the issues you postet, I'm not sure if I got them
>> right.
>>
>> 1. So the data collection through JDBC will remain in the codegen, but
>> uses already existing classes like DBDatabase, DBTable, DBColumn etc.
>> to
>> set up the class hierarchy. Using the already existing classes instead
>> of another set of beans seems reasonable to me. The actual class file
>> generation will then do the Empire-db database (DBDatabase) itself.
>>
>> QUOTE>> This would then allow to use the class hierarchy directly even
>> without the generation of the class files.<<QUOTE
>>
>> How could the class hierarchy be used without generating them? Of
>> course
>> the hierarchy is already set up in memory, but to access a specific
>> Table object one would have to iterate all tables and find the desired
>> table with a string comparison.
>>
>> In fact I like the idea, that a DBDatabase can replicate itself by
>> writing class files to the filesystem. However I don't see when that
>> could be useful. I'd prefer not to modify any of the existing classes
>> from empire-db core. If we still like to do so, I think it should be
>> done when the codegen is more mature.
>>
>> 2. I am working on HSQL and H2. For testing I used a data model from a
>> university project I was once involved in. Where can I get DBSAMPLE and
>> the DBSAMPLEADV data models from?
>>
>> Please let me know what you think about my comments.
>> Regards
>>     Benjamin
>>
>>
>>
>> Rainer Döbele schrieb:
>> > Hi everyone,
>> >
>> > I have now checked in Benjamin's patch of Oct 26th.
>> > I have changed some bits about the logging and the exclusion of
>> tables containing a '$' in populateTableMetaData() in order to test it
>> with Oracle. Also I had to add one Apache license header in
>> DBUtil.java.
>> >
>> > For our next steps I would like to make the following suggestions:
>> >
>> > 1. At the moment we're parsing metadata obtained via JDBC into
>> generator specific classes (Database, Table, Column) which hold the
>> properties required for the velocity templates. The classes are simple
>> bean type classes.
>> > However wouldn't it be much smarter if the metadata would be parsed
>> directly into an Empire-db class hierarchy i.e. use DBDatabase,
>> DBTable, DBColumn etc.?
>> > This would then allow to use the class hierarchy directly even
>> without the generation of the class files.
>> >
>> > In a second step the class files should then be generated from an
>> Empire-db database (DBDatabase). This step should be independent from
>> step one, so that in theory one could provide the class hierarchy in
>> java and have the same classes generated again.
>>
>> > For properties required for the generation process which are not
>> available in the corresponding Empire-db classes we probably need to
>> provide a helper class or provide them through an extension of the
>> database class.
>> >
>> > 2. Afterwards we should be are able to test and debug it on different
>> database systems. I have currently tested it with the DBSAMPLE data
>> model on Oracle and I can do the same for Microsoft SQL-Server. We
>> should make sure that it works with the DBSAMPLE and DBSAMPLEADV data
>> model on all DBMS that we currently support. All committers please let
>> everyone else know what you are developing and testing with.
>> >
>> > 3. I assume that although JDBC should retrieve metadata in a
>> consistent way there will be aspects that are specific for a certain
>> DBMS (like the exclusion of tables containing a '$' symbol in Oracle).
>> This should be handled by the driver. At the moment however we're not
>> using the Empire-db database drivers and we need to think about how we
>> can let the driver intercept and modify the generation process.
>> >
>> > Suggestion 2 and 3 can wait. Most important for now is suggestion No
>> 1.
>> > Thomas and Benjamin what do you thing about this idea?
>> > Anyone volunteering for getting this done?
>> >
>> > Regards
>> > Rainer
>> >
>> >
>
>



-- 
http://www.somatik.be
Microsoft gives you windows, Linux gives you the whole house.

Re: DB codgen next steps

[Benjamin Venditti <be...@web.de>]

Hi Rainer,

thanks für your further explanation. I think i got you right this time.
I can't work on that right now, but i'll have time at the weekend.

Regards
Benjamin

Rainer Döbele schrieb:
> Hi Benni,
>
> I am sorry that I have not been able to respond earlier - job and family have just been to busy recently.
>
> I am not 100% sure whether you fully understood what I intended to say - so I'll try again with different words:
> Basically it is that I would perfer - if possible of course - to split the db code generation into a two step process.
>
> In the first step - the parsing - we obtain the metadata of an existing database and generate an in-memory object model using the Empire-db classes. Hence the step could be possibly implemented with a function that takes a connection and some more info (i.e. via the CodeGenConfig object) and that returns a new DBDabase object which contains all the tables, views, references etc. The function may look like this:
>
>     public DBDatabase parseDataModel(Connection conn, CodeGenConfig config)
>
> The second step takes provides the functionality to take any DBDatabase object - no matter whether it's the outcome of the parse process or taken from somewhere else - and generates the corresponding class files.
> The method for this step could look like this:
>
>     public void generateCodeFiles(DBDatabase db, CodeGenConfig config)
>
> Ideally the objects in org.apache.empire.db.codegen.types would not be required any more.
> The logic we have not in those classes should then all go into one class (possible called CodeGenParser).
> The aim should also be not to extend the Empire-db core classes just for the generation process. 
> But should this be nessary and there is no other way then I would consider that too.
>
> This would mean quite a new structure of our project - but I believe it would be worth it.
> Now do you feel you could handle this?
>
> If so, then please feel free to go ahead (unless anyone has got another idea).
> If I can be of any help, please let me know.
>
> Regards
> Rainer
>
>
> benniven@web.de wrote:
>   
>> Re: DB codgen next steps
>>
>> Hi Rainer,
>>
>> I really appreciate that my patch was useful, and I'd like to join up
>> for the next issue.
>>
>> Just a summary about the issues you postet, I'm not sure if I got them
>> right.
>>
>> 1. So the data collection through JDBC will remain in the codegen, but
>> uses already existing classes like DBDatabase, DBTable, DBColumn etc.
>> to
>> set up the class hierarchy. Using the already existing classes instead
>> of another set of beans seems reasonable to me. The actual class file
>> generation will then do the Empire-db database (DBDatabase) itself.
>>
>> QUOTE>> This would then allow to use the class hierarchy directly even
>> without the generation of the class files.<<QUOTE
>>
>> How could the class hierarchy be used without generating them? Of
>> course
>> the hierarchy is already set up in memory, but to access a specific
>> Table object one would have to iterate all tables and find the desired
>> table with a string comparison.
>>
>> In fact I like the idea, that a DBDatabase can replicate itself by
>> writing class files to the filesystem. However I don't see when that
>> could be useful. I'd prefer not to modify any of the existing classes
>> from empire-db core. If we still like to do so, I think it should be
>> done when the codegen is more mature.
>>
>> 2. I am working on HSQL and H2. For testing I used a data model from a
>> university project I was once involved in. Where can I get DBSAMPLE and
>> the DBSAMPLEADV data models from?
>>
>> Please let me know what you think about my comments.
>> Regards
>>     Benjamin
>>
>>
>>
>> Rainer Döbele schrieb:
>>     
>>> Hi everyone,
>>>
>>> I have now checked in Benjamin's patch of Oct 26th.
>>> I have changed some bits about the logging and the exclusion of
>>>       
>> tables containing a '$' in populateTableMetaData() in order to test it
>> with Oracle. Also I had to add one Apache license header in
>> DBUtil.java.
>>     
>>> For our next steps I would like to make the following suggestions:
>>>
>>> 1. At the moment we're parsing metadata obtained via JDBC into
>>>       
>> generator specific classes (Database, Table, Column) which hold the
>> properties required for the velocity templates. The classes are simple
>> bean type classes.
>>     
>>> However wouldn't it be much smarter if the metadata would be parsed
>>>       
>> directly into an Empire-db class hierarchy i.e. use DBDatabase,
>> DBTable, DBColumn etc.?
>>     
>>> This would then allow to use the class hierarchy directly even
>>>       
>> without the generation of the class files.
>>     
>>> In a second step the class files should then be generated from an
>>>       
>> Empire-db database (DBDatabase). This step should be independent from
>> step one, so that in theory one could provide the class hierarchy in
>> java and have the same classes generated again.
>>
>>     
>>> For properties required for the generation process which are not
>>>       
>> available in the corresponding Empire-db classes we probably need to
>> provide a helper class or provide them through an extension of the
>> database class.
>>     
>>> 2. Afterwards we should be are able to test and debug it on different
>>>       
>> database systems. I have currently tested it with the DBSAMPLE data
>> model on Oracle and I can do the same for Microsoft SQL-Server. We
>> should make sure that it works with the DBSAMPLE and DBSAMPLEADV data
>> model on all DBMS that we currently support. All committers please let
>> everyone else know what you are developing and testing with.
>>     
>>> 3. I assume that although JDBC should retrieve metadata in a
>>>       
>> consistent way there will be aspects that are specific for a certain
>> DBMS (like the exclusion of tables containing a '$' symbol in Oracle).
>> This should be handled by the driver. At the moment however we're not
>> using the Empire-db database drivers and we need to think about how we
>> can let the driver intercept and modify the generation process.
>>     
>>> Suggestion 2 and 3 can wait. Most important for now is suggestion No
>>>       
>> 1.
>>     
>>> Thomas and Benjamin what do you thing about this idea?
>>> Anyone volunteering for getting this done?
>>>
>>> Regards
>>> Rainer
>>>
>>>
>>>       
>
>   

re: DB codgen next steps

[Rainer Döbele <do...@esteam.de>]

Hi Benni,

I am sorry that I have not been able to respond earlier - job and family have just been to busy recently.

I am not 100% sure whether you fully understood what I intended to say - so I'll try again with different words:
Basically it is that I would perfer - if possible of course - to split the db code generation into a two step process.

In the first step - the parsing - we obtain the metadata of an existing database and generate an in-memory object model using the Empire-db classes. Hence the step could be possibly implemented with a function that takes a connection and some more info (i.e. via the CodeGenConfig object) and that returns a new DBDabase object which contains all the tables, views, references etc. The function may look like this:

    public DBDatabase parseDataModel(Connection conn, CodeGenConfig config)

The second step takes provides the functionality to take any DBDatabase object - no matter whether it's the outcome of the parse process or taken from somewhere else - and generates the corresponding class files.
The method for this step could look like this:

    public void generateCodeFiles(DBDatabase db, CodeGenConfig config)

Ideally the objects in org.apache.empire.db.codegen.types would not be required any more.
The logic we have not in those classes should then all go into one class (possible called CodeGenParser).
The aim should also be not to extend the Empire-db core classes just for the generation process. 
But should this be nessary and there is no other way then I would consider that too.

This would mean quite a new structure of our project - but I believe it would be worth it.
Now do you feel you could handle this?

If so, then please feel free to go ahead (unless anyone has got another idea).
If I can be of any help, please let me know.

Regards
Rainer


benniven@web.de wrote:
> Re: DB codgen next steps
> 
> Hi Rainer,
> 
> I really appreciate that my patch was useful, and I'd like to join up
> for the next issue.
> 
> Just a summary about the issues you postet, I'm not sure if I got them
> right.
> 
> 1. So the data collection through JDBC will remain in the codegen, but
> uses already existing classes like DBDatabase, DBTable, DBColumn etc.
> to
> set up the class hierarchy. Using the already existing classes instead
> of another set of beans seems reasonable to me. The actual class file
> generation will then do the Empire-db database (DBDatabase) itself.
> 
> QUOTE>> This would then allow to use the class hierarchy directly even
> without the generation of the class files.<<QUOTE
> 
> How could the class hierarchy be used without generating them? Of
> course
> the hierarchy is already set up in memory, but to access a specific
> Table object one would have to iterate all tables and find the desired
> table with a string comparison.
> 
> In fact I like the idea, that a DBDatabase can replicate itself by
> writing class files to the filesystem. However I don't see when that
> could be useful. I'd prefer not to modify any of the existing classes
> from empire-db core. If we still like to do so, I think it should be
> done when the codegen is more mature.
> 
> 2. I am working on HSQL and H2. For testing I used a data model from a
> university project I was once involved in. Where can I get DBSAMPLE and
> the DBSAMPLEADV data models from?
> 
> Please let me know what you think about my comments.
> Regards
>     Benjamin
> 
> 
> 
> Rainer Döbele schrieb:
> > Hi everyone,
> >
> > I have now checked in Benjamin's patch of Oct 26th.
> > I have changed some bits about the logging and the exclusion of
> tables containing a '$' in populateTableMetaData() in order to test it
> with Oracle. Also I had to add one Apache license header in
> DBUtil.java.
> >
> > For our next steps I would like to make the following suggestions:
> >
> > 1. At the moment we're parsing metadata obtained via JDBC into
> generator specific classes (Database, Table, Column) which hold the
> properties required for the velocity templates. The classes are simple
> bean type classes.
> > However wouldn't it be much smarter if the metadata would be parsed
> directly into an Empire-db class hierarchy i.e. use DBDatabase,
> DBTable, DBColumn etc.?
> > This would then allow to use the class hierarchy directly even
> without the generation of the class files.
> >
> > In a second step the class files should then be generated from an
> Empire-db database (DBDatabase). This step should be independent from
> step one, so that in theory one could provide the class hierarchy in
> java and have the same classes generated again.
> 
> > For properties required for the generation process which are not
> available in the corresponding Empire-db classes we probably need to
> provide a helper class or provide them through an extension of the
> database class.
> >
> > 2. Afterwards we should be are able to test and debug it on different
> database systems. I have currently tested it with the DBSAMPLE data
> model on Oracle and I can do the same for Microsoft SQL-Server. We
> should make sure that it works with the DBSAMPLE and DBSAMPLEADV data
> model on all DBMS that we currently support. All committers please let
> everyone else know what you are developing and testing with.
> >
> > 3. I assume that although JDBC should retrieve metadata in a
> consistent way there will be aspects that are specific for a certain
> DBMS (like the exclusion of tables containing a '$' symbol in Oracle).
> This should be handled by the driver. At the moment however we're not
> using the Empire-db database drivers and we need to think about how we
> can let the driver intercept and modify the generation process.
> >
> > Suggestion 2 and 3 can wait. Most important for now is suggestion No
> 1.
> > Thomas and Benjamin what do you thing about this idea?
> > Anyone volunteering for getting this done?
> >
> > Regards
> > Rainer
> >
> >

Re: DB codgen next steps

[Benjamin Venditti <be...@web.de>]

Hi Rainer,

I really appreciate that my patch was useful, and I'd like to join up 
for the next issue.

Just a summary about the issues you postet, I'm not sure if I got them 
right.

1. So the data collection through JDBC will remain in the codegen, but 
uses already existing classes like DBDatabase, DBTable, DBColumn etc. to 
set up the class hierarchy. Using the already existing classes instead 
of another set of beans seems reasonable to me. The actual class file 
generation will then do the Empire-db database (DBDatabase) itself.

QUOTE>> This would then allow to use the class hierarchy directly even without the generation of the class files.<<QUOTE

How could the class hierarchy be used without generating them? Of course 
the hierarchy is already set up in memory, but to access a specific 
Table object one would have to iterate all tables and find the desired 
table with a string comparison.

In fact I like the idea, that a DBDatabase can replicate itself by 
writing class files to the filesystem. However I don't see when that 
could be useful. I'd prefer not to modify any of the existing classes 
from empire-db core. If we still like to do so, I think it should be 
done when the codegen is more mature.

2. I am working on HSQL and H2. For testing I used a data model from a 
university project I was once involved in. Where can I get DBSAMPLE and 
the DBSAMPLEADV data models from?

Please let me know what you think about my comments.
Regards
    Benjamin



Rainer Döbele schrieb:
> Hi everyone,
>
> I have now checked in Benjamin's patch of Oct 26th.
> I have changed some bits about the logging and the exclusion of tables containing a '$' in populateTableMetaData() in order to test it with Oracle. Also I had to add one Apache license header in DBUtil.java.
>
> For our next steps I would like to make the following suggestions:
>
> 1. At the moment we're parsing metadata obtained via JDBC into generator specific classes (Database, Table, Column) which hold the properties required for the velocity templates. The classes are simple bean type classes.
> However wouldn't it be much smarter if the metadata would be parsed directly into an Empire-db class hierarchy i.e. use DBDatabase, DBTable, DBColumn etc.? 
> This would then allow to use the class hierarchy directly even without the generation of the class files.
>
> In a second step the class files should then be generated from an Empire-db database (DBDatabase). This step should be independent from step one, so that in theory one could provide the class hierarchy in java and have the same classes generated again. 
> For properties required for the generation process which are not available in the corresponding Empire-db classes we probably need to provide a helper class or provide them through an extension of the database class.
>
> 2. Afterwards we should be are able to test and debug it on different database systems. I have currently tested it with the DBSAMPLE data model on Oracle and I can do the same for Microsoft SQL-Server. We should make sure that it works with the DBSAMPLE and DBSAMPLEADV data model on all DBMS that we currently support. All committers please let everyone else know what you are developing and testing with.
>
> 3. I assume that although JDBC should retrieve metadata in a consistent way there will be aspects that are specific for a certain DBMS (like the exclusion of tables containing a '$' symbol in Oracle). This should be handled by the driver. At the moment however we're not using the Empire-db database drivers and we need to think about how we can let the driver intercept and modify the generation process.
>
> Suggestion 2 and 3 can wait. Most important for now is suggestion No 1.
> Thomas and Benjamin what do you thing about this idea?
> Anyone volunteering for getting this done?
>
> Regards
> Rainer
>
>   

Re: DB codgen next steps

[Francis De Brabandere <fr...@gmail.com>]

I'm willing to test on mysql/postgre but I think we need a decent test
case for each of them so that we can rerun tests in the future...

On Sun, Nov 1, 2009 at 12:29 PM, Rainer Döbele <do...@esteam.de> wrote:
> Hi everyone,
>
> I have now checked in Benjamin's patch of Oct 26th.
> I have changed some bits about the logging and the exclusion of tables containing a '$' in populateTableMetaData() in order to test it with Oracle. Also I had to add one Apache license header in DBUtil.java.
>
> For our next steps I would like to make the following suggestions:
>
> 1. At the moment we're parsing metadata obtained via JDBC into generator specific classes (Database, Table, Column) which hold the properties required for the velocity templates. The classes are simple bean type classes.
> However wouldn't it be much smarter if the metadata would be parsed directly into an Empire-db class hierarchy i.e. use DBDatabase, DBTable, DBColumn etc.?
> This would then allow to use the class hierarchy directly even without the generation of the class files.
>
> In a second step the class files should then be generated from an Empire-db database (DBDatabase). This step should be independent from step one, so that in theory one could provide the class hierarchy in java and have the same classes generated again.
> For properties required for the generation process which are not available in the corresponding Empire-db classes we probably need to provide a helper class or provide them through an extension of the database class.
>
> 2. Afterwards we should be are able to test and debug it on different database systems. I have currently tested it with the DBSAMPLE data model on Oracle and I can do the same for Microsoft SQL-Server. We should make sure that it works with the DBSAMPLE and DBSAMPLEADV data model on all DBMS that we currently support. All committers please let everyone else know what you are developing and testing with.
>
> 3. I assume that although JDBC should retrieve metadata in a consistent way there will be aspects that are specific for a certain DBMS (like the exclusion of tables containing a '$' symbol in Oracle). This should be handled by the driver. At the moment however we're not using the Empire-db database drivers and we need to think about how we can let the driver intercept and modify the generation process.
>
> Suggestion 2 and 3 can wait. Most important for now is suggestion No 1.
> Thomas and Benjamin what do you thing about this idea?
> Anyone volunteering for getting this done?
>
> Regards
> Rainer
>
>



-- 
http://www.somatik.be
Microsoft gives you windows, Linux gives you the whole house.