XPATH to JCR-SQL2 Query translation

[Juan Diego Botiva Leon]

Hi!

I'm upgrading Jackrabbit from 1.6.1 to 2.1.1 and I need to translate an XPath query to JCR-SQL2, the query is as follows:

/jcr:root/some_node//element(*, mynt:document)[jcr:contains(jcr:content, 'textToFind')]

I tried the following (removing the path constraint because I couldn't find how to do it)

SELECT * FROM [mynt:document] AS doc WHERE CONTAINS(doc.*, 'textToFind')

But I get no results and the original query returns 23 nodes. Can you help me with this as I checked the JCR 2.0 SQL-2 Grammar (Railroad diagrams) and the jackrabbit-spi-commons test cases but still I'm not sure how to use CONTAINS and how to restrict the query to a specific path. Thanks in advance.

Regards

Juan Botiva
     

[Nicolas Betheuil]

I have discover recently that sql queries doesn't work pretty well on child nodes like your jcr:content should be a child node of any node of type mynt:document.
To make it working you must have a join on it.
Contains should only work on properties but maybe someone could tell the opposite.

To restrict an sql query on specific path you have the path function but the syntax could be a little bit complicated (on array nodes and sub child nodes).

But, to come back to the start of your mail, it's not because you go to JCR2.0 you must fell down XPATH, it's always there. The spec has deprecated this query language but it's always implemented in the last version. If any developer read it, i would be interested to know if there is any road map about this. Do you plan to remove xpath support ?

If someone has an other opinion, idea, suggestion ?


-----Message d'origine-----
De : Juan Diego Botiva Leon [mailto:[hidden email]]
Envoyé : vendredi 26 novembre 2010 16:36
À : [hidden email]
Objet : XPATH to JCR-SQL2 Query translation





Hi!

I'm upgrading Jackrabbit from 1.6.1 to 2.1.1 and I need to translate an XPath query to JCR-SQL2, the query is as follows:

/jcr:root/some_node//element(*, mynt:document)[jcr:contains(jcr:content, 'textToFind')]

I tried the following (removing the path constraint because I couldn't find how to do it)

SELECT * FROM [mynt:document] AS doc WHERE CONTAINS(doc.*, 'textToFind')

But I get no results and the original query returns 23 nodes. Can you help me with this as I checked the JCR 2.0 SQL-2 Grammar (Railroad diagrams) and the jackrabbit-spi-commons test cases but still I'm not sure how to use CONTAINS and how to restrict the query to a specific path. Thanks in advance.

Regards

Juan Botiva
     

[Ard]

On Fri, Nov 26, 2010 at 4:48 PM, Nicolas Betheuil
<[hidden email]> wrote:
> I have discover recently that sql queries doesn't work pretty well on child nodes like your jcr:content should be a child node of any node of type mynt:document.
> To make it working you must have a join on it.
> Contains should only work on properties but maybe someone could tell the opposite.
>
> To restrict an sql query on specific path you have the path function but the syntax could be a little bit complicated (on array nodes and sub child nodes).
>
> But, to come back to the start of your mail, it's not because you go to JCR2.0 you must fell down XPATH, it's always there. The spec has deprecated this query language but it's always implemented in the last version. If any developer read it, i would be interested to know if there is any road map about this. Do you plan to remove xpath support ?

Afaik, xpath is there to stay, and currently even to be preferred in
many occasions wrt performance

Regards Ard

>
> If someone has an other opinion, idea, suggestion ?
>
>
> -----Message d'origine-----
> De : Juan Diego Botiva Leon [mailto:[hidden email]]
> Envoyé : vendredi 26 novembre 2010 16:36
> À : [hidden email]
> Objet : XPATH to JCR-SQL2 Query translation
>
>
>
>
>
> Hi!
>
> I'm upgrading Jackrabbit from 1.6.1 to 2.1.1 and I need to translate an XPath query to JCR-SQL2, the query is as follows:
>
> /jcr:root/some_node//element(*, mynt:document)[jcr:contains(jcr:content, 'textToFind')]
>
> I tried the following (removing the path constraint because I couldn't find how to do it)
>
> SELECT * FROM [mynt:document] AS doc WHERE CONTAINS(doc.*, 'textToFind')
>
> But I get no results and the original query returns 23 nodes. Can you help me with this as I checked the JCR 2.0 SQL-2 Grammar (Railroad diagrams) and the jackrabbit-spi-commons test cases but still I'm not sure how to use CONTAINS and how to restrict the query to a specific path. Thanks in advance.
>
> Regards
>
> Juan Botiva
>
>

--
Hippo
Europe  •  Amsterdam  Oosteinde 11  •  1017 WT Amsterdam  •  [phone]
USA  • San Francisco  185 H Street Suite B  •  Petaluma CA 94952-5100
•  [phone]
Canada    •   Montréal  5369 Boulevard St-Laurent  •  Montréal QC H2T
1S5  •  [phone]
www.onehippo.com  •  www.onehippo.org  •  [hidden email]

[Jukka Zitting-6]

Hi,

On 29/11/10 10:51, Ard Schrijvers wrote:
> Afaik, xpath is there to stay,

Exactly. We'll be supporting XPath for the foreseeable future.

> to be preferred in many occasions wrt performance

Starting with Jackrabbit 2.2 the SQL2 (and QOM) query performance is
roughly equal to XPath and SQL1. SQL2 performance in 2.0 and 2.1 is much
worse. See https://issues.apache.org/jira/browse/JCR-2715 for details.

BR,

Jukka Zitting

[Jukka Zitting-6]

Hi,

On 26/11/10 16:36, Juan Diego Botiva Leon wrote:

> I'm upgrading Jackrabbit from 1.6.1 to 2.1.1 and I need to translate
> an XPath query to JCR-SQL2, the query is as follows:
>
> /jcr:root/some_node//element(*,
> mynt:document)[jcr:contains(jcr:content, 'textToFind')]
>
> I tried the following (removing the path constraint because I
> couldn't find how to do it)
>
> SELECT * FROM [mynt:document] AS doc WHERE CONTAINS(doc.*,
> 'textToFind')
>
> But I get no results and the original query returns 23 nodes. Can you
> help me with this as I checked the JCR 2.0 SQL-2 Grammar (Railroad
> diagrams) and the jackrabbit-spi-commons test cases but still I'm not
> sure how to use CONTAINS and how to restrict the query to a specific
> path. Thanks in advance.

You can use the ISDESCENDANTNODE constraint [1] in SQL2 to limit your
query to a specific subtree.

Note that this constraint is slightly different from the
descendant-or-self axis ("//") in XPath, as it doesn't match the
ancestor node under which you're querying. To duplicate the
descendant-or-self axis functionality in SQL2 you can combine
ISDESCENDANTNODE and ISSAMENODE [2] constraints like this:

     SELECT * FROM [mynt:document]
     WHERE ISDESCENDANTNODE('/some_node') OR ISSAMENODE('/some_node')

Can you please try the SQL2 query with the latest Jackrabbit snapshot to
see if you're still having problem getting the same results as with the
XPath query? Full text queries with SQL2 should work much better now
with the JCR-2715 improvements.

[1] http://www.day.com/specs/jcr/2.0/6_Query.html#DescendantNode
[2] http://www.day.com/specs/jcr/2.0/6_Query.html#SameNode

BR,

Jukka Zitting