You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cocoon.apache.org by Torsten Schlabach <ts...@apache.org> on 2005/09/03 16:07:30 UTC
SQLTransformer
Hi all,
I have done some work with and on SQLTransformer recently and came across
one fundamental question, that I would appreciate some views on.
Today, the SQL transformer will generate XML using field names in the
result set as XML tages, i.e. a result set might look like this:
<rowset>
<row>
<lastname>Schlabach</name>
<firstname>Torsten</torsten>
</row>
</rowset>
In situations, where you would want to build a more generic data browsing
application that makes use of the database metadata to extract information
about the data (in ohther words: you don't know the fieldnames upfront) it
would be much cleaner if the XML generated would read:
<rowset>
<row>
<column name="lastname">Schlabach</column>
<column name="firstname">Torsten</column>
</row>
</rowset>
as if would allow for a generic XSLT which for example renders the rowset
into an HTML table.
WDYT?
Regards,
Torsten
Re: OT: O/R collection mapping [was: SQLTransformer]
Posted by Leszek Gawron <lg...@mobilebox.pl>.
Antonio Gallardo wrote:
> Not sure is there can be a usecase where you will not need to store a
> value in the DB. But, let assume the special usecase exists, then the
> additional index should be "optional" and not obligatory as is now in
> hibernate. ;-)
It is obligatory for mapping a List. You can map your collection as Set
and do not need index then.
> I also noted [0]: "The index of an array or list is always of type
> integer."
Again: this is the context of List mapping.
>
> Again, this is a non-sense: Obligatory index + obligatory index type!
>
> Of course, this does not cover cases where the user prefer to use
> another index type, for example a string or a char or whatever other
> type the user want to use. This is another reason to choose Apache OJB
> [1]. The worse is that instead of offer a solution in hibernate 3, they
> offer just "a cool justification" why this non-sense [0]. I really
> wonder how is people using hibernate after all. The only answer I found
> is "they must have an excelent marketing engine a la
> ${readerPreferedCorporate.getName()}". ;-)
There is completely no problem to use another index type but this is not
List then. How would you like to index your list with non integers when
java.util.List is indexed with integers only?
For your usecase you should map your collection as java.util.Map.
I am doing hibernate almost 2 years now and haven't found a single
usecase where I would have to adjust my database schema just because I'm
using hibernate.
I am sure both OJB and Hibernate would do just fine for 99% cases. We
could bring up a lot of arguments and the other side could probably
easily turn them down. Let's maybe do it like this: You create a
database schema which you map to OJB and I will provide hibernate
mappings. Then I will challenge you :) We can put results on wiki so our
users see that both O/R techniques are feasible for single database
schema. WDYT?
--
Leszek Gawron lgawron@mobilebox.pl
IT Manager MobileBox sp. z o.o.
+48 (61) 855 06 67 http://www.mobilebox.pl
mobile: +48 (501) 720 812 fax: +48 (61) 853 29 65
Re: OT: O/R collection mapping [was: SQLTransformer]
Posted by Antonio Gallardo <ag...@agssa.net>.
Leszek Gawron wrote:
> Vadim Gritsenko wrote:
>
>> Leszek Gawron wrote:
>>
>>> - Marathon class:
>>> - name
>>> - place
>>> - date
>>> - thoseWhoFinishedTheRace collection which is a sorted list of
>>> those Persons who managed to get to the finish line. The list is
>>> sorted in an order of finishing so thoseWhoFinishedTheRace().get( 0
>>> ) is the winner.
>>>
>>> In this example the collection is not _sorted_ - it is _ordered_
>>> with information not available to Person bean. The use of index
>>> field is mandatory - it's not bad database design.
>>
>>
>> It is bad - good design will have 'finished' timestamp ;-P
>
> LOL. Still there are several usecases when you do not have a field to
> sort on like "a list of shirts I like best starting from the most
> favourite one". :)
>
Not sure is there can be a usecase where you will not need to store a
value in the DB. But, let assume the special usecase exists, then the
additional index should be "optional" and not obligatory as is now in
hibernate. ;-)
I also noted [0]: "The index of an array or list is always of type integer."
Again, this is a non-sense: Obligatory index + obligatory index type!
Of course, this does not cover cases where the user prefer to use
another index type, for example a string or a char or whatever other
type the user want to use. This is another reason to choose Apache OJB
[1]. The worse is that instead of offer a solution in hibernate 3, they
offer just "a cool justification" why this non-sense [0]. I really
wonder how is people using hibernate after all. The only answer I found
is "they must have an excelent marketing engine a la
${readerPreferedCorporate.getName()}". ;-)
IMO, a good O/R mapping tool should not force us to "hack" our database
or our code. ;-)
Best Regards,
Antonio Gallardo.
[0] http://www.hibernate.org/hib_docs/reference/en/html/collections.html
[1] http://db.apache.org/ojb/
Re: OT: O/R collection mapping [was: SQLTransformer]
Posted by Leszek Gawron <lg...@mobilebox.pl>.
Vadim Gritsenko wrote:
> Leszek Gawron wrote:
>
>> - Marathon class:
>> - name
>> - place
>> - date
>> - thoseWhoFinishedTheRace collection which is a sorted list of those
>> Persons who managed to get to the finish line. The list is sorted in
>> an order of finishing so thoseWhoFinishedTheRace().get( 0 ) is the
>> winner.
>>
>> In this example the collection is not _sorted_ - it is _ordered_ with
>> information not available to Person bean. The use of index field is
>> mandatory - it's not bad database design.
>
>
> It is bad - good design will have 'finished' timestamp ;-P
LOL. Still there are several usecases when you do not have a field to
sort on like "a list of shirts I like best starting from the most
favourite one". :)
--
Leszek Gawron lgawron@mobilebox.pl
IT Manager MobileBox sp. z o.o.
+48 (61) 855 06 67 http://www.mobilebox.pl
mobile: +48 (501) 720 812 fax: +48 (61) 853 29 65
Re: OT: O/R collection mapping [was: SQLTransformer]
Posted by Vadim Gritsenko <va...@reverycodes.com>.
Leszek Gawron wrote:
> - Marathon class:
> - name
> - place
> - date
> - thoseWhoFinishedTheRace collection which is a sorted list of those
> Persons who managed to get to the finish line. The list is sorted in an
> order of finishing so thoseWhoFinishedTheRace().get( 0 ) is the winner.
>
> In this example the collection is not _sorted_ - it is _ordered_ with
> information not available to Person bean. The use of index field is
> mandatory - it's not bad database design.
It is bad - good design will have 'finished' timestamp ;-P
Vadim
OT: O/R collection mapping [was: SQLTransformer]
Posted by Leszek Gawron <lg...@mobilebox.pl>.
Antonio Gallardo wrote:
> Using "orderby" attribute -
> http://db.apache.org/ojb/docu/guides/repository.html#collection-descriptor
> There is no need of an extra field.
you can do it same way in hibernate. see:
http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#collections-sorted
<quote>
If you want the database itself to order the collection elements use
the order-by attribute of set, bag or map mappings. This solution is
only available under JDK 1.4 or higher (it is implemented using
LinkedHashSet or LinkedHashMap). This performs the ordering in the SQL
query, not in memory.
<set name="aliases" table="person_aliases" order-by="lower(name) asc">
<key column="person"/>
<element column="name" type="string"/>
</set>
</quote>
Moreover you can use a TreeSet that allows you to implement sorting not
available to SQL (via use of Comparable interface).
Still your bean needs to have properties that define the order.
Let me give you an example that advocates the use of index field.
- a Person class (firstName, lastName, age)
- Marathon class:
- name
- place
- date
- thoseWhoFinishedTheRace collection which is a sorted list of those
Persons who managed to get to the finish line. The list is sorted in an
order of finishing so thoseWhoFinishedTheRace().get( 0 ) is the winner.
In this example the collection is not _sorted_ - it is _ordered_ with
information not available to Person bean. The use of index field is
mandatory - it's not bad database design.
> In my case, I met the Unsupported Operation Exception in jxpath using
> @direction="save" not while loading a not with a repeater, but using a
> cform multivalue field.
Could you give me some test case? I tried to stay out of MultiValueField
binding as it always rewrites the whole collection which causes multiple
deletes/inserts.
--
Leszek Gawron lgawron@mobilebox.pl
IT Manager MobileBox sp. z o.o.
+48 (61) 855 06 67 http://www.mobilebox.pl
mobile: +48 (501) 720 812 fax: +48 (61) 853 29 65
Re: SQLTransformer
Posted by Antonio Gallardo <ag...@agssa.net>.
Leszek Gawron wrote:
> Antonio Gallardo wrote:
>
>> I will suggest OJB instead - http://db.apache.org/ojb/.
>
> On the contrary I strongly recommend Hibernate :)
>
>> I was working on a hibernate the last weeks and I need to say that I
>> prefer OJB. Here 2 samples:
>>
>> 1-When using a java List for a collection inside a bean, in Hibernate
>> the table need an additional field to manage the order of the table
>> rows! What is that?
>
> That is the persistent order of your collection. A list needs to know
> what is the sequence of it's child beans. How do you do it in OJB?
Using "orderby" attribute -
http://db.apache.org/ojb/docu/guides/repository.html#collection-descriptor
There is no need of an extra field.
>
>> 2-I don't want in my database more fields than what I need, so let
>> replace the Java List witth a Java Set --> then I meet a new problem:
>> cforms binding use jxpath and jxpath don't work with Sets. jxpath
>> throws an Unsupported Operation Exception! Boom!
>
> strange:
>
> <fb:context xmlns:fb="http://apache.org/cocoon/forms/1.0#binding"
> xmlns:fd="http://apache.org/cocoon/forms/1.0#definition"
> xmlns:ci="http://apache.org/cocoon/include/1.0"
> path="/" >
> <fb:repeater id="questionDefinitions" parent-path="."
> row-path="questionDefinitions" direction="load">
> <fb:identity>
> <fb:value id="id" path="id"/>
> </fb:identity>
> <fb:on-bind>
> <fb:value id="questionName" path="name"/>
> <fb:value id="questionCategory" path="category/name"/>
> </fb:on-bind>
> </fb:repeater>
> </fb:context>
>
> In this case I have bound SurveyDefinition.questionDefinitions
> collection and it works.
In my case, I met the Unsupported Operation Exception in jxpath using
@direction="save" not while loading a not with a repeater, but using a
cform multivalue field.
>
>> I wonder how people using hibernate deal with this situations. The
>> 1st shows me a poor database desing and the second a restriction to
>> use cforms at all!
>
> I still do not get why this is a poor database design. In hibernate
> you can use:
>
> 1. unordered set
> 2. a treeset ordered with Comparable contract
> 3. a List which has a persistent order of your choice.
>
> Only the third choice for obvious reasons needs an indexing column.
What is the obvious reason? IMO, an additional index field shows a poor
database design.
Best Regards,
Antonio Gallardo.
Re: SQLTransformer
Posted by Leszek Gawron <lg...@mobilebox.pl>.
Antonio Gallardo wrote:
> I will suggest OJB instead - http://db.apache.org/ojb/.
On the contrary I strongly recommend Hibernate :)
> I was working on a hibernate the last weeks and I need to say that I
> prefer OJB. Here 2 samples:
>
> 1-When using a java List for a collection inside a bean, in Hibernate
> the table need an additional field to manage the order of the table
> rows! What is that?
That is the persistent order of your collection. A list needs to know
what is the sequence of it's child beans. How do you do it in OJB?
> 2-I don't want in my database more fields than what I need, so let
> replace the Java List witth a Java Set --> then I meet a new problem:
> cforms binding use jxpath and jxpath don't work with Sets. jxpath throws
> an Unsupported Operation Exception! Boom!
strange:
<fb:context xmlns:fb="http://apache.org/cocoon/forms/1.0#binding"
xmlns:fd="http://apache.org/cocoon/forms/1.0#definition"
xmlns:ci="http://apache.org/cocoon/include/1.0"
path="/" >
<fb:repeater id="questionDefinitions" parent-path="."
row-path="questionDefinitions" direction="load">
<fb:identity>
<fb:value id="id" path="id"/>
</fb:identity>
<fb:on-bind>
<fb:value id="questionName" path="name"/>
<fb:value id="questionCategory" path="category/name"/>
</fb:on-bind>
</fb:repeater>
</fb:context>
In this case I have bound SurveyDefinition.questionDefinitions
collection and it works.
> I wonder how people using hibernate deal with this situations. The 1st
> shows me a poor database desing and the second a restriction to use
> cforms at all!
I still do not get why this is a poor database design. In hibernate you
can use:
1. unordered set
2. a treeset ordered with Comparable contract
3. a List which has a persistent order of your choice.
Only the third choice for obvious reasons needs an indexing column.
> I have been developing applications with Cocoon and OJB for 2 years now.
> IMO O/R mapping offer far more features than SQLTransformer and . I hear
> people telling there is too much work to create and O/R mapping. IMO
> this is FUD. In fact a O/R mapping need less work or at least the same
> amount of work or less in more complex applications. here I explained
> how easy is to work with this an OR mapping:
>
> http://marc.theaimsgroup.com/?l=xml-cocoon-dev&m=107569798213119
I am using 100% hibernate for my solutions. There is one small but for
others who whould like to use any O/R mapping tool - they have to know
java well.
For simple cases SQLTransformer or ESQL are very good tools. Users do
not have to know java at all yet they can manage to create a dynamic site.
This is the same case with Ruby on Rails. It is advertised as super easy
and fast web framework. Is it an appropriate tool to create a system
with 100+ entities? I would be scared to try. Still for small sites it's
a blast.
--
Leszek Gawron lgawron@mobilebox.pl
IT Manager MobileBox sp. z o.o.
+48 (61) 855 06 67 http://www.mobilebox.pl
mobile: +48 (501) 720 812 fax: +48 (61) 853 29 65
Re: SQLTransformer
Posted by Antonio Gallardo <ag...@agssa.net>.
Daniel Fagerstrom wrote:
> Torsten Schlabach wrote:
>
>> Hi all,
>>
>> I have done some work with and on SQLTransformer recently and came
>> across
>> one fundamental question, that I would appreciate some views on.
>
> ...
>
>> In situations, where you would want to build a more generic data
>> browsing
>> application that makes use of the database metadata to extract
>> information
>> about the data (in ohther words: you don't know the fieldnames
>> upfront) it
>> would be much cleaner if the XML generated would read:
>
> ...
>
> This have been discussed for years but not resulted in much code. What
> you ask for can rather easily be done in ESQL (an XSP library), IIRC.
> But XSP is hard to maintain and develop new functionality in due to
> its design, so we don't recomend people to use it anymore.
>
> There have also been various proposals to move ESQL functionality to the
> SQLTransformer, but no action.
>
> One camp have been so entusiastic about OR tools that they don't think
> that there is a need for things like the SQLTransformer at all,
> Hibernate should be used instead.
I will suggest OJB instead - http://db.apache.org/ojb/.
I was working on a hibernate the last weeks and I need to say that I
prefer OJB. Here 2 samples:
1-When using a java List for a collection inside a bean, in Hibernate
the table need an additional field to manage the order of the table
rows! What is that?
2-I don't want in my database more fields than what I need, so let
replace the Java List witth a Java Set --> then I meet a new problem:
cforms binding use jxpath and jxpath don't work with Sets. jxpath throws
an Unsupported Operation Exception! Boom!
I wonder how people using hibernate deal with this situations. The 1st
shows me a poor database desing and the second a restriction to use
cforms at all! In my case I prefer to stay on the OJB side and stayt
away of this silly problems.
>
> When we refactored JXTG, I designed it in such a way so that it should
> be easy to add new sets of instructions to it, I did this in particular
> to be able to reimplement ESQL as instruction tags usable within JXTG.
> Then we had a long debate where some people where strongly against
> allowing such things in JXTG, see
> http://wiki.apache.org/cocoon/Templates#head-21328543e917fa0537016fe30cb85c3ee42fc68a
>
> for links to the discussions.
Yep. We believe JXTG is not XSP. ;-)
>
> In http://marc.theaimsgroup.com/?l=xml-cocoon-dev&m=110207408722028&w=2
> Sylvain give some ideas about how to replace ESQL and in
> http://marc.theaimsgroup.com/?l=xml-cocoon-dev&m=110202403115200&w=2
> Stefano gives his view. Both suggest, from somewhat different
> perspectives that the query should be prepared in the controler (e.g.
> flowscripts) and that the result set should be itereated through in the
> view (e.g. JXTG).
I agree with the Stefano POV. This is the way I an working and believe
me my
>
> Now, having written plenty of webapps that use tons of queries performed
> in the SQLTransformer, I'm of the opinion that the SQLTransformer is a
> rather convenient way of handling low to intermediate compleity SQL
> applications. And it would be even more convenient to embed the queries
> in JXTG. And it would be fairly easy to implement the needed
> instructions.
See the Stefano's solution above.
I have been developing applications with Cocoon and OJB for 2 years now.
IMO O/R mapping offer far more features than SQLTransformer and . I hear
people telling there is too much work to create and O/R mapping. IMO
this is FUD. In fact a O/R mapping need less work or at least the same
amount of work or less in more complex applications. here I explained
how easy is to work with this an OR mapping:
http://marc.theaimsgroup.com/?l=xml-cocoon-dev&m=107569798213119
More on this here:
http://wiki.apache.org/cocoon/Templates?action=fullsearch&context=180&value=OJB&fullsearch=Text#head-21328543e917fa0537016fe30cb85c3ee42fc68a
>
> --- o0o ---
>
> To sum up, I find it rather unsatisfying that we don't have "modern" and
> convenient solution for simple SQL reporting. I'm certain that OR tools
> are fantastic in some cases, but they are IMO overkill for simple
> reporting applications. Both ESQL and the SQLTransformer are somewhat
> unconvenient to use, although in different ways.
Please, never understimate the power of a O/R mapping tool! ;-)
>
> IMO we should either provide JXTG instructions for SQL reporting or give
> some kind of support and examples on how to use SQL with query
> preparation in the controler and report construction in the view layer,
> (following Sylvain's or Stefano's sugestions).
The 1st option is not a solution is only a workaround. The worse is that
I see that as a return to the old complexity and mix of concerns. Seems
to be not a good idea.
I prefer the 2nd option: give some kind of support and examples on how
to use SQL with query
preparation in the controler and report construction in the view layer.
Maybe someone in GT2005 can do a presentation on this topic. I remember
that Bertrand showed some of his work related to this way of doing
things: http://wiki.apache.org/cocoon/GT2004Bertrand
Best Regards,
Antonio Gallardo.
Re: SQLTransformer
Posted by Daniel Fagerstrom <da...@nada.kth.se>.
Torsten Schlabach wrote:
> Hi all,
>
> I have done some work with and on SQLTransformer recently and came across
> one fundamental question, that I would appreciate some views on.
...
> In situations, where you would want to build a more generic data browsing
> application that makes use of the database metadata to extract information
> about the data (in ohther words: you don't know the fieldnames upfront) it
> would be much cleaner if the XML generated would read:
...
This have been discussed for years but not resulted in much code. What
you ask for can rather easily be done in ESQL (an XSP library), IIRC.
But XSP is hard to maintain and develop new functionality in due to its
design, so we don't recomend people to use it anymore.
There have also been various proposals to move ESQL functionality to the
SQLTransformer, but no action.
One camp have been so entusiastic about OR tools that they don't think
that there is a need for things like the SQLTransformer at all,
Hibernate should be used instead.
When we refactored JXTG, I designed it in such a way so that it should
be easy to add new sets of instructions to it, I did this in particular
to be able to reimplement ESQL as instruction tags usable within JXTG.
Then we had a long debate where some people where strongly against
allowing such things in JXTG, see
http://wiki.apache.org/cocoon/Templates#head-21328543e917fa0537016fe30cb85c3ee42fc68a
for links to the discussions.
In http://marc.theaimsgroup.com/?l=xml-cocoon-dev&m=110207408722028&w=2
Sylvain give some ideas about how to replace ESQL and in
http://marc.theaimsgroup.com/?l=xml-cocoon-dev&m=110202403115200&w=2
Stefano gives his view. Both suggest, from somewhat different
perspectives that the query should be prepared in the controler (e.g.
flowscripts) and that the result set should be itereated through in the
view (e.g. JXTG).
Now, having written plenty of webapps that use tons of queries performed
in the SQLTransformer, I'm of the opinion that the SQLTransformer is a
rather convenient way of handling low to intermediate compleity SQL
applications. And it would be even more convenient to embed the queries
in JXTG. And it would be fairly easy to implement the needed instructions.
--- o0o ---
To sum up, I find it rather unsatisfying that we don't have "modern" and
convenient solution for simple SQL reporting. I'm certain that OR tools
are fantastic in some cases, but they are IMO overkill for simple
reporting applications. Both ESQL and the SQLTransformer are somewhat
unconvenient to use, although in different ways.
IMO we should either provide JXTG instructions for SQL reporting or give
some kind of support and examples on how to use SQL with query
preparation in the controler and report construction in the view layer,
(following Sylvain's or Stefano's sugestions).
WDYT?
/Daniel
Re: SQLTransformer
Posted by Luca Morandini <lu...@tin.it>.
Torsten Schlabach wrote:
> as if would allow for a generic XSLT which for example renders the rowset
> into an HTML table.
>
> WDYT?
Not sure I understood you, but, if you want something like:
<table>
<tr>
<th>firstname</th>
<th>lastname</th>
</tr>
<tr>
<td>Schlabach</td>
<td>Torsten</td>
</tr>
</table>
You can have it with both XML formats.
It boils down to using "local-name()" instead of "@name" in the relevant
XSLT, like:
<xsl:template match="rowset">
<table>
<xsl:apply-templates select="*"/>
</table>
</xsl:template>
<xsl:template match="row">
<xsl:if test="position()=1">
<tr>
<xsl:for-each select="*">
<th><xsl:value-of select="local-name()"/></th>
</xsl:for-each>
</tr>
</xsl:if>
<tr>
<xsl:for-each select="*">
<td><xsl:value-of select="."/></td>
</xsl:for-each>
</tr>
</xsl:template>
On the other hand, the format you proposed is cleaner indeed.
Regards,
--------------------
Luca Morandini
www.lucamorandini.it
--------------------