Update Esql XSD file...

[Matt Sergeant <ma...@sergeant.org>]

The Esql XSD schema is broken in a few places - the EsqlColumnFunction and
EsqlFormattedColumnFunction are only used once each. I'm pretty darn sure
that's incorrect - there should be one of them on each get-<foo> tag.

Also where do you find what processed ESQL in the Cocoon source? Its darn
hard to find anything in there! I've checked src/org/apache/cocoon in
components, generator(s), filters, processor(s), producer(s)... I thought
I'd just ask rather than continuing this charade!

-- 
<Matt/>

    /||    ** Director and CTO **
   //||    **  AxKit.com Ltd   **  ** XML Application Serving **
  // ||    ** http://axkit.org **  ** XSLT, XPathScript, XSP  **
 // \\| // **     Personal Web Site: http://sergeant.org/     **
     \\//
     //\\
    //  \\

Re: Update Esql XSD file...

[Donald Ball <ba...@webslingerZ.com>]

On Thu, 25 Jan 2001, Matt Sergeant wrote:

> > well, i dunno, you're typically aiming for database independence, but you
> > don't really want it at the expense of speed - so you want to use this
> > optimization but you don't want to have to rewrite it by hand if you
> > switch from mysql to postgresql, say, so you abstract it at the esql
> > layer. if someone wants to do it by hand for some reason, they still can,
> > they just won't use esql's skip and max rows elements.
>
> Are you going to add tags for outer join syntaxes too (and emulate them in
> Java for MySQL)? This is meant tongue in cheek, because there are so many
> database dependant bits that change from SQL to SQL that its really too
> hard to do a totally transparent layer.

sure, but the bits that we can do easily, why not do them?

> > > But I'm not 100% sure.
> >
> > if you don't think use-limit-clause belongs in the esql namespace, i'll
> > fork off an esqlopt namespace and let people use it there if they want.
> > the only thing i'd ask you to do would be to have your esql processor
> > strip elements in the esqlopt namespace, so that if someone were to switch
> > engines, they wouldn't suddenly see extraneous elements in their result
> > tree.
>
> Right, thats trivial enough. I still think its a bad idea though - maybe
> we should throw it out to a wider audience? (I'm thinking Cocoon-users,
> since most AxKit people haven't been using taglibs yet since they were
> broken for so long).

i'll ask.

- donald

Re: Update Esql XSD file...

[Matt Sergeant <ma...@sergeant.org>]

On Thu, 25 Jan 2001, Donald Ball wrote:

> On Wed, 24 Jan 2001, Matt Sergeant wrote:
>
> > Yes it works, no there's no performance benefit (ex Sybase guru here).
>
> there is some with postgresql though, maybe with mysql too...
>
> > > esql:connection element:
> > >
> > > esql:use-like-clause - true/false, default false
> >
> > use limit clause I think you meant...
> >
> > I don't think so. This stuff is really treading into DB specific code now,
> > and we should probably take the simple route of letting people set these
> > things with parameters manually.
>
> well, i dunno, you're typically aiming for database independence, but you
> don't really want it at the expense of speed - so you want to use this
> optimization but you don't want to have to rewrite it by hand if you
> switch from mysql to postgresql, say, so you abstract it at the esql
> layer. if someone wants to do it by hand for some reason, they still can,
> they just won't use esql's skip and max rows elements.

Are you going to add tags for outer join syntaxes too (and emulate them in
Java for MySQL)? This is meant tongue in cheek, because there are so many
database dependant bits that change from SQL to SQL that its really too
hard to do a totally transparent layer.

> > But I'm not 100% sure.
>
> if you don't think use-limit-clause belongs in the esql namespace, i'll
> fork off an esqlopt namespace and let people use it there if they want.
> the only thing i'd ask you to do would be to have your esql processor
> strip elements in the esqlopt namespace, so that if someone were to switch
> engines, they wouldn't suddenly see extraneous elements in their result
> tree.

Right, thats trivial enough. I still think its a bad idea though - maybe
we should throw it out to a wider audience? (I'm thinking Cocoon-users,
since most AxKit people haven't been using taglibs yet since they were
broken for so long).

-- 
<Matt/>

    /||    ** Director and CTO **
   //||    **  AxKit.com Ltd   **  ** XML Application Serving **
  // ||    ** http://axkit.org **  ** XSLT, XPathScript, XSP  **
 // \\| // **     Personal Web Site: http://sergeant.org/     **
     \\//
     //\\
    //  \\

Re: Update Esql XSD file...

[Donald Ball <ba...@webslingerZ.com>]

On Wed, 24 Jan 2001, Matt Sergeant wrote:

> Yes it works, no there's no performance benefit (ex Sybase guru here).

there is some with postgresql though, maybe with mysql too...

> > esql:connection element:
> >
> > esql:use-like-clause - true/false, default false
>
> use limit clause I think you meant...
>
> I don't think so. This stuff is really treading into DB specific code now,
> and we should probably take the simple route of letting people set these
> things with parameters manually.

well, i dunno, you're typically aiming for database independence, but you
don't really want it at the expense of speed - so you want to use this
optimization but you don't want to have to rewrite it by hand if you
switch from mysql to postgresql, say, so you abstract it at the esql
layer. if someone wants to do it by hand for some reason, they still can,
they just won't use esql's skip and max rows elements.

> But I'm not 100% sure.

if you don't think use-limit-clause belongs in the esql namespace, i'll
fork off an esqlopt namespace and let people use it there if they want.
the only thing i'd ask you to do would be to have your esql processor
strip elements in the esqlopt namespace, so that if someone were to switch
engines, they wouldn't suddenly see extraneous elements in their result
tree.

- donald

Re: Update Esql XSD file...

[Matt Sergeant <ma...@sergeant.org>]

On Wed, 24 Jan 2001, Donald Ball wrote:

> On Wed, 24 Jan 2001, Matt Sergeant wrote:
> 
> > > also, i note you don't have skip-rows and max-rows support yet. let me
> > > guess - also omitted in the schema. will correct. skip-rows and max-rows
> > > are element children of execute-query, they indicate how many rows to skip
> > > before returing results, and the maximum number of rows to return.
> > > currently, i only do it by manually skipping rows, but i'm working on
> > > adding optional support for seamlessly using the LIMIT clause of certain
> > > databases (mysql, postgresql, sybase) instead.
> >
> > Scratch sybase from that list. It has @@rowcount, but its not any good for
> > the things LIMIT is good for, sadly. I guess maybe I'll need that database
> > abstraction layer after all :-)
> 
> well, uli claims:
> 
> In Sybase you can say:
> set rowcount 10
> select * from TABLE
> set rowcount 0
> 
> This will return 10 rows and then set rowcount back to unlimited rows
> for the next query.
> 
> so even if you can't skip rows using this syntax, you should be able to
> set rowcount to skip_rows+max_rows (if max_rows is set), and get possibly
> some performance benefit...

Yes it works, no there's no performance benefit (ex Sybase guru here).

> anyway, the reason i'm hesitant to deploy it is due to a caveat in the
> postgresql docs of the LIMIT clause:
> 
>  As of Postgres 7.0, the query optimizer takes LIMIT into account when
> generating a query plan, so you are very likely to get different plans
> (yielding different row orders) depending on what you give for LIMIT and
> OFFSET. Thus, using different LIMIT/OFFSET values to select different
> subsets of a query result will give inconsistent results unless you
> enforce a predictable result ordering with ORDER BY. This is not a bug; it
> is an inherent consequence of the fact that SQL does not promise to
> deliver the results of a query in any particular order unless ORDER BY is
> used to constrain the order.
> 
> so, i'm wondering, should we add a special element underneath the
> esql:connection element:
> 
> esql:use-like-clause - true/false, default false

use limit clause I think you meant...

I don't think so. This stuff is really treading into DB specific code now,
and we should probably take the simple route of letting people set these
things with parameters manually.

But I'm not 100% sure.

-- 
<Matt/>

    /||    ** Director and CTO **
   //||    **  AxKit.com Ltd   **  ** XML Application Serving **
  // ||    ** http://axkit.org **  ** XSLT, XPathScript, XSP  **
 // \\| // **     Personal Web Site: http://sergeant.org/     **
     \\//
     //\\
    //  \\

Re: Update Esql XSD file...

[Donald Ball <ba...@webslingerZ.com>]

On Wed, 24 Jan 2001, Matt Sergeant wrote:

> > also, i note you don't have skip-rows and max-rows support yet. let me
> > guess - also omitted in the schema. will correct. skip-rows and max-rows
> > are element children of execute-query, they indicate how many rows to skip
> > before returing results, and the maximum number of rows to return.
> > currently, i only do it by manually skipping rows, but i'm working on
> > adding optional support for seamlessly using the LIMIT clause of certain
> > databases (mysql, postgresql, sybase) instead.
>
> Scratch sybase from that list. It has @@rowcount, but its not any good for
> the things LIMIT is good for, sadly. I guess maybe I'll need that database
> abstraction layer after all :-)

well, uli claims:

In Sybase you can say:
set rowcount 10
select * from TABLE
set rowcount 0

This will return 10 rows and then set rowcount back to unlimited rows
for the next query.

so even if you can't skip rows using this syntax, you should be able to
set rowcount to skip_rows+max_rows (if max_rows is set), and get possibly
some performance benefit...

anyway, the reason i'm hesitant to deploy it is due to a caveat in the
postgresql docs of the LIMIT clause:

 As of Postgres 7.0, the query optimizer takes LIMIT into account when
generating a query plan, so you are very likely to get different plans
(yielding different row orders) depending on what you give for LIMIT and
OFFSET. Thus, using different LIMIT/OFFSET values to select different
subsets of a query result will give inconsistent results unless you
enforce a predictable result ordering with ORDER BY. This is not a bug; it
is an inherent consequence of the fact that SQL does not promise to
deliver the results of a query in any particular order unless ORDER BY is
used to constrain the order.

so, i'm wondering, should we add a special element underneath the
esql:connection element:

esql:use-like-clause - true/false, default false

also, should we maybe use a seperate namespace for "optional" elements,
like this? elements the esql processors may safely ignore?

- donald

Re: Update Esql XSD file...

[Matt Sergeant <ma...@sergeant.org>]

On Wed, 24 Jan 2001, Donald Ball wrote:

> also, i note you don't have skip-rows and max-rows support yet. let me
> guess - also omitted in the schema. will correct. skip-rows and max-rows
> are element children of execute-query, they indicate how many rows to skip
> before returing results, and the maximum number of rows to return.
> currently, i only do it by manually skipping rows, but i'm working on
> adding optional support for seamlessly using the LIMIT clause of certain
> databases (mysql, postgresql, sybase) instead.

Scratch sybase from that list. It has @@rowcount, but its not any good for
the things LIMIT is good for, sadly. I guess maybe I'll need that database
abstraction layer after all :-)

-- 
<Matt/>

    /||    ** Director and CTO **
   //||    **  AxKit.com Ltd   **  ** XML Application Serving **
  // ||    ** http://axkit.org **  ** XSLT, XPathScript, XSP  **
 // \\| // **     Personal Web Site: http://sergeant.org/     **
     \\//
     //\\
    //  \\

Re: Update Esql XSD file...

[Donald Ball <ba...@webslingerZ.com>]

On Wed, 24 Jan 2001, Matt Sergeant wrote:

> This model gets very slow when you have large numbers of taglibs. AxKit's
> XSP model is *always* a single pass parser. Yes, I admit its only slow for
> development, but on mod_perl its also one compile per httpd perl file
> change, not once per file change. Plus remember I'm insane about
> performance :-)

you sure are. well, whatever floats your boat, right?

> > 3. does it support accessing result set values from ancestor queries (man,
> > i need to document that in esql.xsd somehow too)
>
> Eek, I doubt it... Though you can do XPath's anywhere within XSP on AxKit
> now, so assuming you know what tag contains your value, then yes, you can
> use some sort of ancestor XPath query. But I haven't tried it (I haven't
> even run the code yet!). It would be something like
> <xsp:expr>$parent->findvalue('ancestor::results/foo')</xsp:expr>, assuming
> you had <results><foo>Value</foo> in the parent results section. But this
> is also a bit of a hack, requiring internal knowledge about the XSP
> implementation.
>
> How does yours achieve that?

any esql:get-foo method can have an optional ancestor attribute which
must be an integer specifying how many steps up the resultset stack it
should go before trying to find the column in question. the template in
question:

<xsl:template name="get-query">
  <xsl:choose>
    <xsl:when test="@ancestor">
      <xsl:text>((EsqlQuery)_esql_queries.elementAt(_esql_queries.size()-</xsl:text>
      <xsl:value-of select="@ancestor"/>
      <xsl:text>))</xsl:text>
    </xsl:when>
    <xsl:otherwise>
      <xsl:text>_esql_query</xsl:text>
    </xsl:otherwise>
  </xsl:choose>
</xsl:template>

this template is called for any esql:get-foo method, to find the EsqlQuery
object we should use to access the resultset. nothing fancy.

also, i note you don't have skip-rows and max-rows support yet. let me
guess - also omitted in the schema. will correct. skip-rows and max-rows
are element children of execute-query, they indicate how many rows to skip
before returing results, and the maximum number of rows to return.
currently, i only do it by manually skipping rows, but i'm working on
adding optional support for seamlessly using the LIMIT clause of certain
databases (mysql, postgresql, sybase) instead.

- donald

Re: Update Esql XSD file...

[Matt Sergeant <ma...@sergeant.org>]

On Wed, 24 Jan 2001, Donald Ball wrote:

> On Wed, 24 Jan 2001, Matt Sergeant wrote:
>
> > Since its only 227 lines, here ya go (consider it a SAX handler that only
> > gets called in the ESQL namespace). We don't use XSLT for taglibs - too
> > slow.
>
> too slow? but it's only applied once per page, at compile time, after that
> the compiled class is simply executed - very fast. couple of
> questions/thoughts on it:

This model gets very slow when you have large numbers of taglibs. AxKit's
XSP model is *always* a single pass parser. Yes, I admit its only slow for
development, but on mod_perl its also one compile per httpd perl file
change, not once per file change. Plus remember I'm insane about
performance :-)

> 1. does it support a stack of connections?

Yes, based on scope.

> 2. does it support nested queries?

Yes, based on scope.

> 3. does it support accessing result set values from ancestor queries (man,
> i need to document that in esql.xsd somehow too)

Eek, I doubt it... Though you can do XPath's anywhere within XSP on AxKit
now, so assuming you know what tag contains your value, then yes, you can
use some sort of ancestor XPath query. But I haven't tried it (I haven't
even run the code yet!). It would be something like
<xsp:expr>$parent->findvalue('ancestor::results/foo')</xsp:expr>, assuming
you had <results><foo>Value</foo> in the parent results section. But this
is also a bit of a hack, requiring internal knowledge about the XSP
implementation.

How does yours achieve that?

-- 
<Matt/>

    /||    ** Director and CTO **
   //||    **  AxKit.com Ltd   **  ** XML Application Serving **
  // ||    ** http://axkit.org **  ** XSLT, XPathScript, XSP  **
 // \\| // **     Personal Web Site: http://sergeant.org/     **
     \\//
     //\\
    //  \\

Re: Update Esql XSD file...

[Donald Ball <ba...@webslingerZ.com>]

On Wed, 24 Jan 2001, Matt Sergeant wrote:

> Since its only 227 lines, here ya go (consider it a SAX handler that only
> gets called in the ESQL namespace). We don't use XSLT for taglibs - too
> slow.

too slow? but it's only applied once per page, at compile time, after that
the compiled class is simply executed - very fast. couple of
questions/thoughts on it:

1. does it support a stack of connections?

2. does it support nested queries?

3. does it support accessing result set values from ancestor queries (man,
i need to document that in esql.xsd somehow too)

- donald

Re: Update Esql XSD file...

[Matt Sergeant <ma...@sergeant.org>]

On Wed, 24 Jan 2001, Donald Ball wrote:

> On Wed, 24 Jan 2001, Matt Sergeant wrote:
>
> > > as far as how the column is given... hmm... did i not put that in there
> > > anywhere? no, i didn't. hmm, where would it belong? anyway, the algorithm
> > > is simple. the get-foo element must have a column attribute. if that
> > > attribute's value is an integer, it's taken to be the position of the
> > > column in the resultset, otherwise it's taken to be the name of the
> > > column.
> >
> > Ah, I hadn't realised that bit. OK, will patch. ESQL taglib for AxKit is
> > pretty much done, albeit untested. Only took me about 3 hours once I got
> > going. I have a feeling AxKit taglibs are a lot simpler to author.
> >
> > # wc ~matt/esql.xsl
> >     782    2302   35387 /home/matt/esql.xsl
> > # wc ESQL.pm
> >     227     712    6869 ESQL.pm
>
> probably much more succinct - perl almost always is. in addition, though,
> i'm an _extremely_ verbose programmer, more so than anyone else i know.
>
> would you mind flipping the perl module my way for my own edification?

Since its only 227 lines, here ya go (consider it a SAX handler that only
gets called in the ESQL namespace). We don't use XSLT for taglibs - too
slow.

-- 
<Matt/>

    /||    ** Director and CTO **
   //||    **  AxKit.com Ltd   **  ** XML Application Serving **
  // ||    ** http://axkit.org **  ** XSLT, XPathScript, XSP  **
 // \\| // **     Personal Web Site: http://sergeant.org/     **
     \\//
     //\\
    //  \\

Re: Update Esql XSD file...

[Donald Ball <ba...@webslingerZ.com>]

On Wed, 24 Jan 2001, Matt Sergeant wrote:

> > as far as how the column is given... hmm... did i not put that in there
> > anywhere? no, i didn't. hmm, where would it belong? anyway, the algorithm
> > is simple. the get-foo element must have a column attribute. if that
> > attribute's value is an integer, it's taken to be the position of the
> > column in the resultset, otherwise it's taken to be the name of the
> > column.
>
> Ah, I hadn't realised that bit. OK, will patch. ESQL taglib for AxKit is
> pretty much done, albeit untested. Only took me about 3 hours once I got
> going. I have a feeling AxKit taglibs are a lot simpler to author.
>
> # wc ~matt/esql.xsl
>     782    2302   35387 /home/matt/esql.xsl
> # wc ESQL.pm
>     227     712    6869 ESQL.pm

probably much more succinct - perl almost always is. in addition, though,
i'm an _extremely_ verbose programmer, more so than anyone else i know.

would you mind flipping the perl module my way for my own edification?

- donald

Re: Update Esql XSD file...

[Matt Sergeant <ma...@sergeant.org>]

On Wed, 24 Jan 2001, Donald Ball wrote:

> On Wed, 24 Jan 2001, Matt Sergeant wrote:
>
> > On Mon, 22 Jan 2001, Matt Sergeant wrote:
> >
> > > The Esql XSD schema is broken in a few places - the EsqlColumnFunction and
> > > EsqlFormattedColumnFunction are only used once each. I'm pretty darn sure
> > > that's incorrect - there should be one of them on each get-<foo> tag.
> >
> > Also, get-column-<foo> says "Returns the <foo> of the given column", but
> > doesn't specify how the column is "given" in the schema. I'm going to go
> > look at the source code now, but I don't hold much hope of finding out -
> > reading Java written by XSLT is *not fun* :-)
>
> regarding the schema itself - i'm almost sure it's got bugs - i've never
> written a schema before, no one has been willing or able to help certify
> its correctness, so any patches you might have would be _greatly_
> appreciated.
>
> as far as how the column is given... hmm... did i not put that in there
> anywhere? no, i didn't. hmm, where would it belong? anyway, the algorithm
> is simple. the get-foo element must have a column attribute. if that
> attribute's value is an integer, it's taken to be the position of the
> column in the resultset, otherwise it's taken to be the name of the
> column.

Ah, I hadn't realised that bit. OK, will patch. ESQL taglib for AxKit is
pretty much done, albeit untested. Only took me about 3 hours once I got
going. I have a feeling AxKit taglibs are a lot simpler to author.

# wc ~matt/esql.xsl
    782    2302   35387 /home/matt/esql.xsl
# wc ESQL.pm
    227     712    6869 ESQL.pm

(esql.xsl is the Cocoon taglib)

-- 
<Matt/>

    /||    ** Director and CTO **
   //||    **  AxKit.com Ltd   **  ** XML Application Serving **
  // ||    ** http://axkit.org **  ** XSLT, XPathScript, XSP  **
 // \\| // **     Personal Web Site: http://sergeant.org/     **
     \\//
     //\\
    //  \\

Re: Update Esql XSD file...

[Donald Ball <ba...@webslingerZ.com>]

On Wed, 24 Jan 2001, Matt Sergeant wrote:

> On Mon, 22 Jan 2001, Matt Sergeant wrote:
>
> > The Esql XSD schema is broken in a few places - the EsqlColumnFunction and
> > EsqlFormattedColumnFunction are only used once each. I'm pretty darn sure
> > that's incorrect - there should be one of them on each get-<foo> tag.
>
> Also, get-column-<foo> says "Returns the <foo> of the given column", but
> doesn't specify how the column is "given" in the schema. I'm going to go
> look at the source code now, but I don't hold much hope of finding out -
> reading Java written by XSLT is *not fun* :-)

regarding the schema itself - i'm almost sure it's got bugs - i've never
written a schema before, no one has been willing or able to help certify
its correctness, so any patches you might have would be _greatly_
appreciated.

as far as how the column is given... hmm... did i not put that in there
anywhere? no, i didn't. hmm, where would it belong? anyway, the algorithm
is simple. the get-foo element must have a column attribute. if that
attribute's value is an integer, it's taken to be the position of the
column in the resultset, otherwise it's taken to be the name of the
column.

- donald

Re: Update Esql XSD file...

[Matt Sergeant <ma...@sergeant.org>]

On Mon, 22 Jan 2001, Matt Sergeant wrote:

> The Esql XSD schema is broken in a few places - the EsqlColumnFunction and
> EsqlFormattedColumnFunction are only used once each. I'm pretty darn sure
> that's incorrect - there should be one of them on each get-<foo> tag.

Also, get-column-<foo> says "Returns the <foo> of the given column", but
doesn't specify how the column is "given" in the schema. I'm going to go
look at the source code now, but I don't hold much hope of finding out -
reading Java written by XSLT is *not fun* :-)

-- 
<Matt/>

    /||    ** Director and CTO **
   //||    **  AxKit.com Ltd   **  ** XML Application Serving **
  // ||    ** http://axkit.org **  ** XSLT, XPathScript, XSP  **
 // \\| // **     Personal Web Site: http://sergeant.org/     **
     \\//
     //\\
    //  \\