Mailing-List: contact users-help@jackrabbit.apache.org; run by ezmlm
Precedence: bulk
Reply-To: users@jackrabbit.apache.org
Received-SPF: pass (athena.apache.org: domain of jzitting@adobe.com designates
 64.18.1.181 as permitted sender)
Message-ID: <4CF52C92.6010907@adobe.com>
Date: Tue, 30 Nov 2010 17:55:46 +0100
From: Jukka Zitting <jzitting@adobe.com>
Organization: Adobe
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
 rv:1.9.1.9) Gecko/20100430 Fedora/3.0.4-2.fc11 Lightning/1.0b2pre
 Thunderbird/3.0.4
MIME-Version: 1.0
To: "users@jackrabbit.apache.org" <users@jackrabbit.apache.org>
Subject: Re: XPATH to JCR-SQL2 Query translation
References: <SNT135-w38DB4FCA409780A037C301BC210@phx.gbl>
In-Reply-To: <SNT135-w38DB4FCA409780A037C301BC210@phx.gbl>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
Content-Transfer-Encoding: 7bit

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