You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by Evan Sho <in...@evansho.net> on 2002/05/12 19:17:52 UTC
Generate multi-level XML document using standard SQL select statement
Just tried MS SQL server 2000. I can type in browser:
http://localhost/mydb?sql=select * from dept, emp
where dept.deptno=emp.dept.no for xml auto&root=List
then, I will get XML:
<List>
<dept id='10' name='Sales'>
<emp id='10' name='King' salary='5000'/>
<emp id='20' name='John' salary='200'/>
</dept>
<dept id='20' name='Account'>
<emp id='30' name='Peter' salary='2000'/>
</dept>
...
</List>
The nesting of joined result is automatic, and I found
that it is very useful. so is there any similar tool
that can be used to get similar result?
Thanks
Evan
__________________________________________________
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: Pagination support
Posted by Christian Haul <ha...@dvs1.informatik.tu-darmstadt.de>.
On 13.May.2002 -- 09:31 AM, Evan Sho wrote:
> Just skim thur the suggestions. However, I have a
> question very fundamental to that in the pipeline
> process. If the pagination is done in transformer, it
> means the generator will have all result generated and
> the transformer just cut out the unwanted one. It
> could means many rows in the result will be unused.
> Also, even the generator is cached, many un-usually
> used rows will be in the cache.
Yep.
> My idea is, get what you need and cache what you
> usually use. for example, in the sitemap:
> <map:match pattern="longlist">
> <map:action type="pagination">
> <map:parameter name="item-per-page" value="20">
>
> <map:generate type="sql" src="select * from emp
> limit {starting-row}, {item-per-page}"/>
> ....
> if i have oracle database, i will write:
> <map:generate type="sql" src="select * from
> (select * from (select * from (select * from emp order
> by empno) e where rowid < {item-per-page-x-pageid}) f
> order by empno desc) g where rowid <
> {item-per-page}"/>
>
> (although it is a bit complicated, but if i
> usually get only 0.1% of my result in the first 3
> pages, it should be worthy)
OK, granted. In my opinion, this caching should be done in the jdbc
layer or at least a seperate component, though. I thought there has
been announced some framework for that but I couldn't find it again.
Anyway, have a look at
http://www.geocities.com/sundar_rajan_in/java/servlets/databasecaching.html
it looks like J2EE or jdk1.4 does have some provisions for that.
> would it be hard if i have the page break in the
> middle of a month? however, if i do the paginate
> calculation before generate, i know exactly how many
> row will be in the result.
To be honest, I have never used the pagination transformer and after
having a short look at it, it seems, well, underdocumented.
> I don't know if I have any mis-understanding in the
> pagination in scratchpad (no doc at all X-p), but I am
> quite sure that filtertransformer is good only when
> all pages are frequently used.
Cached items time out. I don't know enough about the cache system to
say whether all cached items from one invocation of the
FilterTransformer time out together or if more frequent items survive
longer. Carsten?
Chris.
--
C h r i s t i a n H a u l
haul@informatik.tu-darmstadt.de
fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Pagination support
Posted by Evan Sho <in...@evansho.net>.
Just skim thur the suggestions. However, I have a
question very fundamental to that in the pipeline
process. If the pagination is done in transformer, it
means the generator will have all result generated and
the transformer just cut out the unwanted one. It
could means many rows in the result will be unused.
Also, even the generator is cached, many un-usually
used rows will be in the cache.
My idea is, get what you need and cache what you
usually use. for example, in the sitemap:
<map:match pattern="longlist">
<map:action type="pagination">
<map:parameter name="item-per-page" value="20">
<map:generate type="sql" src="select * from emp
limit {starting-row}, {item-per-page}"/>
....
if i have oracle database, i will write:
<map:generate type="sql" src="select * from
(select * from (select * from (select * from emp order
by empno) e where rowid < {item-per-page-x-pageid}) f
order by empno desc) g where rowid <
{item-per-page}"/>
(although it is a bit complicated, but if i
usually get only 0.1% of my result in the first 3
pages, it should be worthy)
of course the pagination action will caculate and pass
all necessary value to the generator. the generator
cache key is generated by the hash of the sql
statement, so i am sure only those most frequently
used records are in my cache.
and the pagination support in transformer also give me
another problem, say if i have news item nested in
month node:
<month id="0cg234">
<news id="02384" title="XX"/>
<news id="20343" title="YY"/>
...
</month>
<month id="ouhou90">
...
</month>
would it be hard if i have the page break in the
middle of a month? however, if i do the paginate
calculation before generate, i know exactly how many
row will be in the result.
I don't know if I have any mis-understanding in the
pagination in scratchpad (no doc at all X-p), but I am
quite sure that filtertransformer is good only when
all pages are frequently used.
Just my 2 cents, please please kindly comment on my
idea.
Regards
Evan
--- Christian Haul
<ha...@dvs1.informatik.tu-darmstadt.de> wrote:
> > BTW, how should I do pagination in cocoon? I mean
> if I
> > have 100+ rows and want to display them page by
> page
> > with 10 rows per page, is there any general
> practice
> > to do that?
>
> a) esql supports skip rows and max rows
> b) there's a paginating transformer in scratchpad
> c) FilterTransformer does paginating
>
> Chris.
>
__________________________________________________
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: Generate multi-level XML document using standard SQL select statement
Posted by Christian Haul <ha...@dvs1.informatik.tu-darmstadt.de>.
On 13.May.2002 -- 01:44 AM, Evan Sho wrote:
> Well, just prepared a Generator which take a SQL
> statment as src, and generate multi-level XML
> document.
>
> Please kindly give me some comments?
> http://www.evansho.net/SqlGenerator.zip
Looks nice. Evan, do you like to donate that code (and some docs about
it ;-) ?
> BTW, how should I do pagination in cocoon? I mean if I
> have 100+ rows and want to display them page by page
> with 10 rows per page, is there any general practice
> to do that?
a) esql supports skip rows and max rows
b) there's a paginating transformer in scratchpad
c) FilterTransformer does paginating
Chris.
--
C h r i s t i a n H a u l
haul@informatik.tu-darmstadt.de
fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: Generate multi-level XML document using standard SQL select statement
Posted by Evan Sho <in...@evansho.net>.
Well, just prepared a Generator which take a SQL
statment as src, and generate multi-level XML
document.
Please kindly give me some comments?
http://www.evansho.net/SqlGenerator.zip
BTW, how should I do pagination in cocoon? I mean if I
have 100+ rows and want to display them page by page
with 10 rows per page, is there any general practice
to do that?
Regards
Evan
--- Christian Haul
<ha...@dvs1.informatik.tu-darmstadt.de> wrote:
> On 12.May.2002 -- 10:17 AM, Evan Sho wrote:
> > Just tried MS SQL server 2000. I can type in
> browser:
> > http://localhost/mydb?sql=select * from dept, emp
> > where dept.deptno=emp.dept.no for xml
> auto&root=List
> >
> > then, I will get XML:
> > <List>
> > <dept id='10' name='Sales'>
> > <emp id='10' name='King' salary='5000'/>
> > <emp id='20' name='John' salary='200'/>
> > </dept>
> > <dept id='20' name='Account'>
> > <emp id='30' name='Peter' salary='2000'/>
> > </dept>
> > ...
> > </List>
> >
> > The nesting of joined result is automatic, and I
> found
> > that it is very useful. so is there any similar
> tool
> > that can be used to get similar result?
>
> Have a look at <esql:group/>. It's not automatic as
> such but obtaining
> a result like the above would be easy. Alas, the
> query needs to be
> known beforehand.
>
> An automatic tools would need to have more knowledge
> about database
> metadata and parse the query.
>
> HTH
>
> Chris.
>
> --
> C h r i s t i a n H a u l
> haul@informatik.tu-darmstadt.de
> fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9
> 6856 335A 9E08
>
>
>
---------------------------------------------------------------------
> Please check that your question has not already been
> answered in the
> FAQ before posting.
> <http://xml.apache.org/cocoon/faqs.html>
>
> To unsubscribe, e-mail:
> <co...@xml.apache.org>
> For additional commands, e-mail:
> <co...@xml.apache.org>
>
__________________________________________________
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: Generate multi-level XML document using standard SQL select statement
Posted by Christian Haul <ha...@dvs1.informatik.tu-darmstadt.de>.
On 12.May.2002 -- 10:17 AM, Evan Sho wrote:
> Just tried MS SQL server 2000. I can type in browser:
> http://localhost/mydb?sql=select * from dept, emp
> where dept.deptno=emp.dept.no for xml auto&root=List
>
> then, I will get XML:
> <List>
> <dept id='10' name='Sales'>
> <emp id='10' name='King' salary='5000'/>
> <emp id='20' name='John' salary='200'/>
> </dept>
> <dept id='20' name='Account'>
> <emp id='30' name='Peter' salary='2000'/>
> </dept>
> ...
> </List>
>
> The nesting of joined result is automatic, and I found
> that it is very useful. so is there any similar tool
> that can be used to get similar result?
Have a look at <esql:group/>. It's not automatic as such but obtaining
a result like the above would be easy. Alas, the query needs to be
known beforehand.
An automatic tools would need to have more knowledge about database
metadata and parse the query.
HTH
Chris.
--
C h r i s t i a n H a u l
haul@informatik.tu-darmstadt.de
fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>