You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commons-dev@db.apache.org by Oliver Heger <Ol...@t-online.de> on 2004/01/18 18:33:07 UTC
new project question
This posting was originally sent to the Jakarta commons-dev list. The
people there pointed me to the Apache DB project.
Oliver Heger wrote:
> Hi all,
>
> I am working on a SQL generator library and would like to donate it to
> an open source community. I think it would fit into Jakarta Commons.
> Here is some information about it:
>
> The library was part of a view framework for web applications. It can
> generate SELECT statements to retrieve data. The single parts of a
> SELECT query are represented as objects that can be plugged together
> (e.g. fields to be retrieved, conditions). Finally a renderer
> processes this object tree and generates SQL code. This is the list of
> the goals that are to be achieved:
>
> - Hiding the complex syntax of SQL queries by providing a high level
> API to construct them.
>
> - Improving performance over naive programming by consequent usage of
> prepared statements with placeholders where possible.
>
> - Providing mapping facilities between logic names of (bean)
> properties and database column names.
>
> - Providing (a certain level of) database independency by supporting
> different renderers that can be customized to specific SQL dialects.
>
> - Persistence of database queries in different storages (e.g. XML
> files, in a database, ...)
>
> - Supporting dynamic query generation, e.g. if columns from different
> database tables are involved, the correct join conditions can be
> generated automatically
>
> - Easy extensibility: just implement your own query part classes
>
> Please let me know if there is some interest in this code. If not,
> what other options do I have to make it open source?
>
> Thanks!
> Oliver
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-dev-help@jakarta.apache.org
>
>
In the meantime I found out that there is surely some overlapping with
existing code in DB, e.g. the Criteria in Torque. But I suppose the
goals are a bit different (so an advantage of this library may be that
it is independent of a specific persistence engine). Maybe there is
enough space for a coexistence at first and later doublicate code can be
refactored?
Thanks!
Oliver
Re: new project question
Posted by Oliver Heger <he...@med.uni-marburg.de>.
Thank you, Brian.
The StringBuffer approach is mostly used for performance reasons.
Because a whole SQL statement will likely consist of a bunch of
QueryPart objects I tried to avoid that each object would have to create
a String in its render() method. The actual approach makes it possible
that they all render in the same buffer.
I was also thinking about moving this StringBuffer into the
RenderContext class to get rid off this parameter in many methods. But I
am not sure about that.
Oliver
Brian McCallister schrieb:
> First glance over it is quite complete -- though I am having trouble
> seeing the best ways to use it.
>
> A few questions --
>
> Why render query into the StringBuffer argument instead of returning the
> query string directly as a String? Do you foresee a use case where the
> StringBuffer will need to be modified before or after rendering? This is
> particularly true as you (quite cleanly) abstract out the ability to
> plug different systems sql quirks in.
>
> -Brian
>
Re: new project question
Posted by Oliver Heger <Ol...@t-online.de>.
I put an actualized version of the library online
(http://www.oliver-heger.privat.t-online.de/retrieve.zip). There are a
some more QueryPart classes and more unit tests.
There is also a new class QueryWrapper (not sure if the name fits) that
deals with executing queries: an instance is initialized with a
QueryPart object, and then for this a PreparedStatement can be created.
This statement can then be executed (for execution itself I would like
to use something like commons dbutils).
Oliver
Brian McCallister wrote:
> First glance over it is quite complete -- though I am having trouble
> seeing the best ways to use it.
>
> A few questions --
>
> Why render query into the StringBuffer argument instead of returning
> the query string directly as a String? Do you foresee a use case where
> the StringBuffer will need to be modified before or after rendering?
> This is particularly true as you (quite cleanly) abstract out the
> ability to plug different systems sql quirks in.
>
> -Brian
>
Re: new project question
Posted by Brian McCallister <mc...@forthillcompany.com>.
First glance over it is quite complete -- though I am having trouble
seeing the best ways to use it.
A few questions --
Why render query into the StringBuffer argument instead of returning
the query string directly as a String? Do you foresee a use case where
the StringBuffer will need to be modified before or after rendering?
This is particularly true as you (quite cleanly) abstract out the
ability to plug different systems sql quirks in.
-Brian
On Feb 8, 2004, at 2:38 PM, Oliver Heger wrote:
> Hi,
>
> did anyone had a look at that code? I would really appreciate some
> comments.
>
> Thanks.
> Oliver
>
> Oliver Heger wrote:
>
>> Brian,
>>
>> sorry for the delay. You can now find the actual code at the
>> following URL:
>>
>> http://www.oliver-heger.privat.t-online.de/retrieve.zip
>>
>> Some short words for explanation:
>>
>> Heart of the library is the Query class in the parts sub package.
>> Together with the other part classes it can be used to construct
>> SELECT statements. Some important parts are still missing, e.g. for
>> ORDER BY clauses or for generating lists for the IN operator.
>>
>> In the retrieve package there are some classes and interfaces that
>> represent database structures. They have two purposes:
>>
>> 1. They are used for the mapping facilities. It is possible to
>> address tables and columns with logic names, which are then
>> transformed into real database names.
>> 2. They provide information for automatic generation of join
>> conditions. One use case for this library was a highly configurable
>> application that allows its user to choose the fields of a table and
>> its related tables that should be displayed. For instance imagine an
>> application that reads a tasks table for displaying a todo list. It
>> allows its users to define the view of their tasks. If a user only
>> wants to see fields that are contained in the tasks table, the
>> resulting SELECT statement will be quite simple. If he or she is also
>> interested in the name and some other attributes of the user who has
>> created the task, the user table has to be joined. The library is
>> designed that it can automatically detect the involved tables and
>> fetch the necessary information about the relations between them
>> through the RelationProvider interface. With this information it can
>> dynamically create a statement with a join if necessary; the calling
>> application need not bother about that.
>>
>> Of course a couple of unit tests must be added, though the test for
>> Query also tests many other classes. Now I am looking forward to your
>> comments!
>>
>> Oliver
>
>
>
>
Re: new project question
Posted by Brian McCallister <mc...@forthillcompany.com>.
Woo hoo, I was trying to find your email to download and look at it but
couldn't. Much appreciate the prodding!
-Brian
On Feb 8, 2004, at 2:38 PM, Oliver Heger wrote:
> Hi,
>
> did anyone had a look at that code? I would really appreciate some
> comments.
>
> Thanks.
> Oliver
>
> Oliver Heger wrote:
>
>> Brian,
>>
>> sorry for the delay. You can now find the actual code at the
>> following URL:
>>
>> http://www.oliver-heger.privat.t-online.de/retrieve.zip
>>
>> Some short words for explanation:
>>
>> Heart of the library is the Query class in the parts sub package.
>> Together with the other part classes it can be used to construct
>> SELECT statements. Some important parts are still missing, e.g. for
>> ORDER BY clauses or for generating lists for the IN operator.
>>
>> In the retrieve package there are some classes and interfaces that
>> represent database structures. They have two purposes:
>>
>> 1. They are used for the mapping facilities. It is possible to
>> address tables and columns with logic names, which are then
>> transformed into real database names.
>> 2. They provide information for automatic generation of join
>> conditions. One use case for this library was a highly configurable
>> application that allows its user to choose the fields of a table and
>> its related tables that should be displayed. For instance imagine an
>> application that reads a tasks table for displaying a todo list. It
>> allows its users to define the view of their tasks. If a user only
>> wants to see fields that are contained in the tasks table, the
>> resulting SELECT statement will be quite simple. If he or she is also
>> interested in the name and some other attributes of the user who has
>> created the task, the user table has to be joined. The library is
>> designed that it can automatically detect the involved tables and
>> fetch the necessary information about the relations between them
>> through the RelationProvider interface. With this information it can
>> dynamically create a statement with a join if necessary; the calling
>> application need not bother about that.
>>
>> Of course a couple of unit tests must be added, though the test for
>> Query also tests many other classes. Now I am looking forward to your
>> comments!
>>
>> Oliver
>
>
>
>
Re: new project question
Posted by Oliver Heger <Ol...@t-online.de>.
Hi,
did anyone had a look at that code? I would really appreciate some comments.
Thanks.
Oliver
Oliver Heger wrote:
> Brian,
>
> sorry for the delay. You can now find the actual code at the following
> URL:
>
> http://www.oliver-heger.privat.t-online.de/retrieve.zip
>
> Some short words for explanation:
>
> Heart of the library is the Query class in the parts sub package.
> Together with the other part classes it can be used to construct
> SELECT statements. Some important parts are still missing, e.g. for
> ORDER BY clauses or for generating lists for the IN operator.
>
> In the retrieve package there are some classes and interfaces that
> represent database structures. They have two purposes:
>
> 1. They are used for the mapping facilities. It is possible to address
> tables and columns with logic names, which are then transformed into
> real database names.
> 2. They provide information for automatic generation of join
> conditions. One use case for this library was a highly configurable
> application that allows its user to choose the fields of a table and
> its related tables that should be displayed. For instance imagine an
> application that reads a tasks table for displaying a todo list. It
> allows its users to define the view of their tasks. If a user only
> wants to see fields that are contained in the tasks table, the
> resulting SELECT statement will be quite simple. If he or she is also
> interested in the name and some other attributes of the user who has
> created the task, the user table has to be joined. The library is
> designed that it can automatically detect the involved tables and
> fetch the necessary information about the relations between them
> through the RelationProvider interface. With this information it can
> dynamically create a statement with a join if necessary; the calling
> application need not bother about that.
>
> Of course a couple of unit tests must be added, though the test for
> Query also tests many other classes. Now I am looking forward to your
> comments!
>
> Oliver
Re: new project question
Posted by Oliver Heger <Ol...@t-online.de>.
Brian,
sorry for the delay. You can now find the actual code at the following URL:
http://www.oliver-heger.privat.t-online.de/retrieve.zip
Some short words for explanation:
Heart of the library is the Query class in the parts sub package.
Together with the other part classes it can be used to construct SELECT
statements. Some important parts are still missing, e.g. for ORDER BY
clauses or for generating lists for the IN operator.
In the retrieve package there are some classes and interfaces that
represent database structures. They have two purposes:
1. They are used for the mapping facilities. It is possible to address
tables and columns with logic names, which are then transformed into
real database names.
2. They provide information for automatic generation of join conditions.
One use case for this library was a highly configurable application that
allows its user to choose the fields of a table and its related tables
that should be displayed. For instance imagine an application that reads
a tasks table for displaying a todo list. It allows its users to define
the view of their tasks. If a user only wants to see fields that are
contained in the tasks table, the resulting SELECT statement will be
quite simple. If he or she is also interested in the name and some other
attributes of the user who has created the task, the user table has to
be joined. The library is designed that it can automatically detect the
involved tables and fetch the necessary information about the relations
between them through the RelationProvider interface. With this
information it can dynamically create a statement with a join if
necessary; the calling application need not bother about that.
Of course a couple of unit tests must be added, though the test for
Query also tests many other classes. Now I am looking forward to your
comments!
Oliver
Brian McCallister wrote:
> For posting code, put it on a web server and post a link. Let me know
> if you need somewhere to put it temporarily and I can provide a spot.
>
> -Brian
Re: new project question
Posted by Brian McCallister <mc...@forthillcompany.com>.
For posting code, put it on a web server and post a link. Let me know
if you need somewhere to put it temporarily and I can provide a spot.
-Brian
On Jan 18, 2004, at 4:30 PM, Oliver Heger wrote:
> Okay, I will need a few days (depending on my actual work load) to
> perform a bit cleanup a finish the classes I am working on. Then I
> will post the code to this list.
>
> Oliver
>
> Brian McCallister wrote:
>
>> I would like to see the code, or at least what it looks like. The
>> implementation I use most of the time (if I'm not not using OJB for
>> the project ;-) is at http://kasparov.skife.org/sqlbuilder.html . It
>> is far from a complete solution, and I know of at least three other
>> open, or "email me and I'll give it to you" implementations in Java,
>> and many more in other languages. As such it seems like a really good
>> candidate for a *-commons implementation.
>>
>> I know both OJB and Torque have SQL building tools in them as well.
>>
>> The jakarta commons-sql project, which is moving to db-commons is a
>> programmatically convenient API for doing DDL, so I tend to think
>> that tools for other SQL statements would be good complements (when
>> the original discussion of moving sql came up this was part of the
>> discussion).
>>
>> One benefit to it NOT being open and available right now, and it
>> being small (just selects) is that it could potentially be
>> contributed into commons-sql without an incubator hassle. OTOH, your
>> particular implementation may not wind up being the final form it
>> takes, but if you are thinking in the problem domain it is good to
>> see what you are doing =)
>>
>> Jakarta DBUtils has a number of tools for working with JDBC and
>> database stuff at a pretty high level (mapping result sets to beans
>> etc) which falls into a similar domain, but still doesn't provide a
>> nice tool for doing sql generation.
>>
>> -Brian
>>
>> On Jan 18, 2004, at 2:57 PM, Oliver Heger wrote:
>>
>>> Brian,
>>>
>>> the code only resides on my harddisk at the moment and I am the only
>>> developer.
>>>
>>> Maybe some short words about the history: I developed a framework
>>> for web applications for my last employer, which also contained some
>>> code for database access. I was never quite content with this code,
>>> so I started again from scratch with a complete different design and
>>> at the moment I am working on this new version.
>>>
>>> So the library is by far not finished, it is rather in a relative
>>> early stage of development. The basic architecture for generating
>>> SELECT statements is about 80% complete, but some goals like the
>>> persistence are not even started.
>>>
>>> If there is interest, I can post a zip with the actual code.
>>>
>>> Oliver
>>>
>>> Brian McCallister wrote:
>>>
>>>> Do you have the code available anywhere at the moment? Does anyone
>>>> work on it aside from yourself?
>>>>
>>>> -Brian
>>>>
>>>> On Jan 18, 2004, at 12:33 PM, Oliver Heger wrote:
>>>
>>>
>>> <snip>
>>>
>>>
>>
>>
>>
>
>
>
Re: new project question
Posted by Oliver Heger <Ol...@t-online.de>.
Okay, I will need a few days (depending on my actual work load) to
perform a bit cleanup a finish the classes I am working on. Then I will
post the code to this list.
Oliver
Brian McCallister wrote:
> I would like to see the code, or at least what it looks like. The
> implementation I use most of the time (if I'm not not using OJB for
> the project ;-) is at http://kasparov.skife.org/sqlbuilder.html . It
> is far from a complete solution, and I know of at least three other
> open, or "email me and I'll give it to you" implementations in Java,
> and many more in other languages. As such it seems like a really good
> candidate for a *-commons implementation.
>
> I know both OJB and Torque have SQL building tools in them as well.
>
> The jakarta commons-sql project, which is moving to db-commons is a
> programmatically convenient API for doing DDL, so I tend to think that
> tools for other SQL statements would be good complements (when the
> original discussion of moving sql came up this was part of the
> discussion).
>
> One benefit to it NOT being open and available right now, and it being
> small (just selects) is that it could potentially be contributed into
> commons-sql without an incubator hassle. OTOH, your particular
> implementation may not wind up being the final form it takes, but if
> you are thinking in the problem domain it is good to see what you are
> doing =)
>
> Jakarta DBUtils has a number of tools for working with JDBC and
> database stuff at a pretty high level (mapping result sets to beans
> etc) which falls into a similar domain, but still doesn't provide a
> nice tool for doing sql generation.
>
> -Brian
>
> On Jan 18, 2004, at 2:57 PM, Oliver Heger wrote:
>
>> Brian,
>>
>> the code only resides on my harddisk at the moment and I am the only
>> developer.
>>
>> Maybe some short words about the history: I developed a framework for
>> web applications for my last employer, which also contained some code
>> for database access. I was never quite content with this code, so I
>> started again from scratch with a complete different design and at
>> the moment I am working on this new version.
>>
>> So the library is by far not finished, it is rather in a relative
>> early stage of development. The basic architecture for generating
>> SELECT statements is about 80% complete, but some goals like the
>> persistence are not even started.
>>
>> If there is interest, I can post a zip with the actual code.
>>
>> Oliver
>>
>> Brian McCallister wrote:
>>
>>> Do you have the code available anywhere at the moment? Does anyone
>>> work on it aside from yourself?
>>>
>>> -Brian
>>>
>>> On Jan 18, 2004, at 12:33 PM, Oliver Heger wrote:
>>
>>
>> <snip>
>>
>>
>
>
>
Re: new project question
Posted by Brian McCallister <mc...@forthillcompany.com>.
I would like to see the code, or at least what it looks like. The
implementation I use most of the time (if I'm not not using OJB for the
project ;-) is at http://kasparov.skife.org/sqlbuilder.html . It is
far from a complete solution, and I know of at least three other open,
or "email me and I'll give it to you" implementations in Java, and many
more in other languages. As such it seems like a really good candidate
for a *-commons implementation.
I know both OJB and Torque have SQL building tools in them as well.
The jakarta commons-sql project, which is moving to db-commons is a
programmatically convenient API for doing DDL, so I tend to think that
tools for other SQL statements would be good complements (when the
original discussion of moving sql came up this was part of the
discussion).
One benefit to it NOT being open and available right now, and it being
small (just selects) is that it could potentially be contributed into
commons-sql without an incubator hassle. OTOH, your particular
implementation may not wind up being the final form it takes, but if
you are thinking in the problem domain it is good to see what you are
doing =)
Jakarta DBUtils has a number of tools for working with JDBC and
database stuff at a pretty high level (mapping result sets to beans
etc) which falls into a similar domain, but still doesn't provide a
nice tool for doing sql generation.
-Brian
On Jan 18, 2004, at 2:57 PM, Oliver Heger wrote:
> Brian,
>
> the code only resides on my harddisk at the moment and I am the only
> developer.
>
> Maybe some short words about the history: I developed a framework for
> web applications for my last employer, which also contained some code
> for database access. I was never quite content with this code, so I
> started again from scratch with a complete different design and at the
> moment I am working on this new version.
>
> So the library is by far not finished, it is rather in a relative
> early stage of development. The basic architecture for generating
> SELECT statements is about 80% complete, but some goals like the
> persistence are not even started.
>
> If there is interest, I can post a zip with the actual code.
>
> Oliver
>
> Brian McCallister wrote:
>
>> Do you have the code available anywhere at the moment? Does anyone
>> work on it aside from yourself?
>>
>> -Brian
>>
>> On Jan 18, 2004, at 12:33 PM, Oliver Heger wrote:
>
> <snip>
>
>
Re: new project question
Posted by Oliver Heger <Ol...@t-online.de>.
Brian,
the code only resides on my harddisk at the moment and I am the only
developer.
Maybe some short words about the history: I developed a framework for
web applications for my last employer, which also contained some code
for database access. I was never quite content with this code, so I
started again from scratch with a complete different design and at the
moment I am working on this new version.
So the library is by far not finished, it is rather in a relative early
stage of development. The basic architecture for generating SELECT
statements is about 80% complete, but some goals like the persistence
are not even started.
If there is interest, I can post a zip with the actual code.
Oliver
Brian McCallister wrote:
> Do you have the code available anywhere at the moment? Does anyone
> work on it aside from yourself?
>
> -Brian
>
> On Jan 18, 2004, at 12:33 PM, Oliver Heger wrote:
<snip>
Re: new project question
Posted by Brian McCallister <br...@apache.org>.
Do you have the code available anywhere at the moment? Does anyone work
on it aside from yourself?
-Brian
On Jan 18, 2004, at 12:33 PM, Oliver Heger wrote:
> This posting was originally sent to the Jakarta commons-dev list. The
> people there pointed me to the Apache DB project.
>
> Oliver Heger wrote:
>
>> Hi all,
>>
>> I am working on a SQL generator library and would like to donate it
>> to an open source community. I think it would fit into Jakarta
>> Commons. Here is some information about it:
>>
>> The library was part of a view framework for web applications. It can
>> generate SELECT statements to retrieve data. The single parts of a
>> SELECT query are represented as objects that can be plugged together
>> (e.g. fields to be retrieved, conditions). Finally a renderer
>> processes this object tree and generates SQL code. This is the list
>> of the goals that are to be achieved:
>>
>> - Hiding the complex syntax of SQL queries by providing a high level
>> API to construct them.
>>
>> - Improving performance over naive programming by consequent usage of
>> prepared statements with placeholders where possible.
>>
>> - Providing mapping facilities between logic names of (bean)
>> properties and database column names.
>>
>> - Providing (a certain level of) database independency by supporting
>> different renderers that can be customized to specific SQL dialects.
>>
>> - Persistence of database queries in different storages (e.g. XML
>> files, in a database, ...)
>>
>> - Supporting dynamic query generation, e.g. if columns from different
>> database tables are involved, the correct join conditions can be
>> generated automatically
>>
>> - Easy extensibility: just implement your own query part classes
>>
>> Please let me know if there is some interest in this code. If not,
>> what other options do I have to make it open source?
>>
>> Thanks!
>> Oliver
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
>> For additional commands, e-mail: commons-dev-help@jakarta.apache.org
>>
>>
> In the meantime I found out that there is surely some overlapping with
> existing code in DB, e.g. the Criteria in Torque. But I suppose the
> goals are a bit different (so an advantage of this library may be that
> it is independent of a specific persistence engine). Maybe there is
> enough space for a coexistence at first and later doublicate code can
> be refactored?
>
> Thanks!
> Oliver
>
>