Equivalent JCR_SQL2 statement

[Rakesh Vidyadharan]

Since XPath is deprecated in JCR 2.0, I am trying to move to JCR_SQL2  
(JR-2.0-beta1).  I have the following XPath query:

//unitTestPage[@cms:nodeType = 'page']

I tried writing the query in SQL as:

select * from nt:unstructured where jcr:path like '/%/unitTestPage'  
and cms:nodeType = 'page'

This statement however fails with an exception:

javax.jcr.query.InvalidQueryException: Query:
select * from nt:(*)unstructured where jcr:path like '/%/
unitTestFolder' and cms:nodeType = 'page'; expected: <end>
        at org.apache.jackrabbit.commons.query.sql2.Parser.getSyntaxError
(Parser.java:968)
        at  
org.apache.jackrabbit.commons.query.sql2.Parser.createQueryObjectModel
(Parser.java:124)
        at  
org.apache.jackrabbit.commons.query.sql2
.SQL2QOMBuilder.createQueryObjectModel(SQL2QOMBuilder.java:55)
        at org.apache.jackrabbit.core.query.QOMQueryFactory.createQuery
(QOMQueryFactory.java:69)
        at org.apache.jackrabbit.core.query.CompoundQueryFactory.createQuery
(CompoundQueryFactory.java:67)
        at org.apache.jackrabbit.core.query.QueryManagerImpl.createQuery
(QueryManagerImpl.java:98)

Can some one tell me what I am missing?

Thanks
Rakesh

[Thomas Müller-2]

Hi,

Try:

select * from [nt:unstructured] where [jcr:path] like
'/%/unitTestPage' and [cms:nodeType] = 'page'

Regards,
Thomas


On Sun, Nov 8, 2009 at 5:30 AM, Rakesh Vidyadharan <[hidden email]> wrote:

> Since XPath is deprecated in JCR 2.0, I am trying to move to JCR_SQL2
> (JR-2.0-beta1).  I have the following XPath query:
>
> //unitTestPage[@cms:nodeType = 'page']
>
> I tried writing the query in SQL as:
>
> select * from nt:unstructured where jcr:path like '/%/unitTestPage' and
> cms:nodeType = 'page'
>
> This statement however fails with an exception:
>
> javax.jcr.query.InvalidQueryException: Query:
> select * from nt:(*)unstructured where jcr:path like '/%/unitTestFolder' and
> cms:nodeType = 'page'; expected: <end>
>        at
> org.apache.jackrabbit.commons.query.sql2.Parser.getSyntaxError(Parser.java:968)
>        at
> org.apache.jackrabbit.commons.query.sql2.Parser.createQueryObjectModel(Parser.java:124)
>        at
> org.apache.jackrabbit.commons.query.sql2.SQL2QOMBuilder.createQueryObjectModel(SQL2QOMBuilder.java:55)
>        at
> org.apache.jackrabbit.core.query.QOMQueryFactory.createQuery(QOMQueryFactory.java:69)
>        at
> org.apache.jackrabbit.core.query.CompoundQueryFactory.createQuery(CompoundQueryFactory.java:67)
>        at
> org.apache.jackrabbit.core.query.QueryManagerImpl.createQuery(QueryManagerImpl.java:98)
>
> Can some one tell me what I am missing?
>
> Thanks
> Rakesh
>
>
>

[Thomas Müller-2]

Hi,

I found the relevant section of the spec:
http://www.day.com/specs/jcr/2.0/6_Query.html#6.7.4%20Name
Name ::= '[' quotedName ']' | '[' simpleName ']' | simpleName
simpleName ::= /* A JCR Name that is also a legal SQL identifier  */
See the SQL:92 rules for <regular identifier> (in ISO/IEC 9075:1992 §
5.2 <token> and <separator>).
As far as I read the SQL-92 spec, this basically means the same rules
as for Java identifiers apply.

I guess we should have railroad diagrams. I will try to create them.

Regards,
Thomas




On Mon, Nov 9, 2009 at 8:20 AM, Thomas Müller <[hidden email]> wrote:

> Hi,
>
> Try:
>
> select * from [nt:unstructured] where [jcr:path] like
> '/%/unitTestPage' and [cms:nodeType] = 'page'
>
> Regards,
> Thomas
>
>
> On Sun, Nov 8, 2009 at 5:30 AM, Rakesh Vidyadharan <[hidden email]> wrote:
>> Since XPath is deprecated in JCR 2.0, I am trying to move to JCR_SQL2
>> (JR-2.0-beta1).  I have the following XPath query:
>>
>> //unitTestPage[@cms:nodeType = 'page']
>>
>> I tried writing the query in SQL as:
>>
>> select * from nt:unstructured where jcr:path like '/%/unitTestPage' and
>> cms:nodeType = 'page'
>>
>> This statement however fails with an exception:
>>
>> javax.jcr.query.InvalidQueryException: Query:
>> select * from nt:(*)unstructured where jcr:path like '/%/unitTestFolder' and
>> cms:nodeType = 'page'; expected: <end>
>>        at
>> org.apache.jackrabbit.commons.query.sql2.Parser.getSyntaxError(Parser.java:968)
>>        at
>> org.apache.jackrabbit.commons.query.sql2.Parser.createQueryObjectModel(Parser.java:124)
>>        at
>> org.apache.jackrabbit.commons.query.sql2.SQL2QOMBuilder.createQueryObjectModel(SQL2QOMBuilder.java:55)
>>        at
>> org.apache.jackrabbit.core.query.QOMQueryFactory.createQuery(QOMQueryFactory.java:69)
>>        at
>> org.apache.jackrabbit.core.query.CompoundQueryFactory.createQuery(CompoundQueryFactory.java:67)
>>        at
>> org.apache.jackrabbit.core.query.QueryManagerImpl.createQuery(QueryManagerImpl.java:98)
>>
>> Can some one tell me what I am missing?
>>
>> Thanks
>> Rakesh
>>
>>
>>
>

[Rakesh Vidyadharan]

Hi Thomas,

Thanks for the clarifications.  The query now "compiles" but I get  
back no results (the XPath versions work as I would expect as these  
are part of a unit test suite).  I had looked into the JR 2 unit test  
suite before I posted, but I think all of those are based on the older  
SQL/XPath standards.

Rakesh

On 9 Nov 2009, at 1:36:25 AM, Thomas Müller wrote:

> Hi,
>
> I found the relevant section of the spec:
> http://www.day.com/specs/jcr/2.0/6_Query.html#6.7.4%20Name
> Name ::= '[' quotedName ']' | '[' simpleName ']' | simpleName
> simpleName ::= /* A JCR Name that is also a legal SQL identifier  */
> See the SQL:92 rules for <regular identifier> (in ISO/IEC 9075:1992 §
> 5.2 <token> and <separator>).
> As far as I read the SQL-92 spec, this basically means the same rules
> as for Java identifiers apply.
>
> I guess we should have railroad diagrams. I will try to create them.
>
> Regards,
> Thomas
>
>
>
>
> On Mon, Nov 9, 2009 at 8:20 AM, Thomas Müller  
> <[hidden email]> wrote:
>> Hi,
>>
>> Try:
>>
>> select * from [nt:unstructured] where [jcr:path] like
>> '/%/unitTestPage' and [cms:nodeType] = 'page'
>>
>> Regards,
>> Thomas
>>
>>
>> On Sun, Nov 8, 2009 at 5:30 AM, Rakesh Vidyadharan  
>> <[hidden email]> wrote:
>>> Since XPath is deprecated in JCR 2.0, I am trying to move to  
>>> JCR_SQL2
>>> (JR-2.0-beta1).  I have the following XPath query:
>>>
>>> //unitTestPage[@cms:nodeType = 'page']
>>>
>>> I tried writing the query in SQL as:
>>>
>>> select * from nt:unstructured where jcr:path like '/%/
>>> unitTestPage' and
>>> cms:nodeType = 'page'
>>>
>>> This statement however fails with an exception:
>>>
>>> javax.jcr.query.InvalidQueryException: Query:
>>> select * from nt:(*)unstructured where jcr:path like '/%/
>>> unitTestFolder' and
>>> cms:nodeType = 'page'; expected: <end>
>>>        at
>>> org.apache.jackrabbit.commons.query.sql2.Parser.getSyntaxError
>>> (Parser.java:968)
>>>        at
>>> org.apache.jackrabbit.commons.query.sql2
>>> .Parser.createQueryObjectModel(Parser.java:124)
>>>        at
>>> org.apache.jackrabbit.commons.query.sql2
>>> .SQL2QOMBuilder.createQueryObjectModel(SQL2QOMBuilder.java:55)
>>>        at
>>> org.apache.jackrabbit.core.query.QOMQueryFactory.createQuery
>>> (QOMQueryFactory.java:69)
>>>        at
>>> org.apache.jackrabbit.core.query.CompoundQueryFactory.createQuery
>>> (CompoundQueryFactory.java:67)
>>>        at
>>> org.apache.jackrabbit.core.query.QueryManagerImpl.createQuery
>>> (QueryManagerImpl.java:98)
>>>
>>> Can some one tell me what I am missing?
>>>
>>> Thanks
>>> Rakesh
>>>
>>>
>>>
>>

Rakesh Vidyadharan
President & CEO
Sans Pareil Technologies, Inc.
http://sptci.com/


| 100 W. Chestnut, Suite 1305 | Chicago, IL 60610-3296 USA |
| Ph: [phone] | Mobile: [phone] (US), [phone]  
0873 (IN) | Fax: [phone] | E-mail: [hidden email]

[Rakesh Vidyadharan]

On 9 Nov 2009, at 1:36:25 AM, Thomas Müller wrote:

> Hi,
>
> I found the relevant section of the spec:
> http://www.day.com/specs/jcr/2.0/6_Query.html#6.7.4%20Name
> Name ::= '[' quotedName ']' | '[' simpleName ']' | simpleName
> simpleName ::= /* A JCR Name that is also a legal SQL identifier  */
> See the SQL:92 rules for <regular identifier> (in ISO/IEC 9075:1992 §
> 5.2 <token> and <separator>).
> As far as I read the SQL-92 spec, this basically means the same rules
> as for Java identifiers apply.

Just to clarify, I tried the following variants of the query:

select * from ['nt:unstructured'] where ['jcr:path'] like '/%/
unitTestPage' and ['cms:nodeType'] = 'page'
select * from [nt:unstructured] where [jcr:path] like '/%/
unitTestPage' and [cms:nodeType] = 'page'

Rakesh

>
>
>
> On Mon, Nov 9, 2009 at 8:20 AM, Thomas Müller  
> <[hidden email]> wrote:
>> Hi,
>>
>> Try:
>>
>> select * from [nt:unstructured] where [jcr:path] like
>> '/%/unitTestPage' and [cms:nodeType] = 'page'
>>
>> Regards,
>> Thomas
>>
>>
>> On Sun, Nov 8, 2009 at 5:30 AM, Rakesh Vidyadharan  
>> <[hidden email]> wrote:
>>> Since XPath is deprecated in JCR 2.0, I am trying to move to  
>>> JCR_SQL2
>>> (JR-2.0-beta1).  I have the following XPath query:
>>>
>>> //unitTestPage[@cms:nodeType = 'page']
>>>
>>> I tried writing the query in SQL as:
>>>
>>> select * from nt:unstructured where jcr:path like '/%/
>>> unitTestPage' and
>>> cms:nodeType = 'page'
>>>
>>> This statement however fails with an exception:
>>>
>>> javax.jcr.query.InvalidQueryException: Query:
>>> select * from nt:(*)unstructured where jcr:path like '/%/
>>> unitTestFolder' and
>>> cms:nodeType = 'page'; expected: <end>
>>>        at
>>> org.apache.jackrabbit.commons.query.sql2.Parser.getSyntaxError
>>> (Parser.java:968)
>>>        at
>>> org.apache.jackrabbit.commons.query.sql2
>>> .Parser.createQueryObjectModel(Parser.java:124)
>>>        at
>>> org.apache.jackrabbit.commons.query.sql2
>>> .SQL2QOMBuilder.createQueryObjectModel(SQL2QOMBuilder.java:55)
>>>        at
>>> org.apache.jackrabbit.core.query.QOMQueryFactory.createQuery
>>> (QOMQueryFactory.java:69)
>>>        at
>>> org.apache.jackrabbit.core.query.CompoundQueryFactory.createQuery
>>> (CompoundQueryFactory.java:67)
>>>        at
>>> org.apache.jackrabbit.core.query.QueryManagerImpl.createQuery
>>> (QueryManagerImpl.java:98)
>>>
>>> Can some one tell me what I am missing?
>>>
>>> Thanks
>>> Rakesh
>>>
>>>
>>>
>>

Rakesh Vidyadharan
President & CEO
Sans Pareil Technologies, Inc.
http://sptci.com/


| 100 W. Chestnut, Suite 1305 | Chicago, IL 60610-3296 USA |
| Ph: [phone] | Mobile: [phone] (US), [phone]  
0873 (IN) | Fax: [phone] | E-mail: [hidden email]

[Marcel Reutegger]

Hi,

please note that there are no plans to abandon XPath support in
jackrabbit. the deprecation status is only with the specification.
therefore I suggest you stick with XPath unless you need functionality
that requires JQOM or SQL-2.

On Mon, Nov 9, 2009 at 14:32, Rakesh Vidyadharan <[hidden email]> wrote:
> On 9 Nov 2009, at 1:36:25 AM, Thomas Müller wrote:
> select * from ['nt:unstructured'] where ['jcr:path'] like '/%/unitTestPage'
> and ['cms:nodeType'] = 'page'

that doesn't work because SQL-2 does not support the jcr:path pseudo
property, which is only present in JCR 1.0 SQL.

what you rather want is:

select * from [nt:base] where name() = 'unitTestPage' and
[cms:nodeType] = 'page'

regards
 marcel

> select * from [nt:unstructured] where [jcr:path] like '/%/unitTestPage' and
> [cms:nodeType] = 'page'
>
> Rakesh
>>
>>
>>
>> On Mon, Nov 9, 2009 at 8:20 AM, Thomas Müller <[hidden email]>
>> wrote:
>>>
>>> Hi,
>>>
>>> Try:
>>>
>>> select * from [nt:unstructured] where [jcr:path] like
>>> '/%/unitTestPage' and [cms:nodeType] = 'page'
>>>
>>> Regards,
>>> Thomas
>>>
>>>
>>> On Sun, Nov 8, 2009 at 5:30 AM, Rakesh Vidyadharan <[hidden email]>
>>> wrote:
>>>>
>>>> Since XPath is deprecated in JCR 2.0, I am trying to move to JCR_SQL2
>>>> (JR-2.0-beta1).  I have the following XPath query:
>>>>
>>>> //unitTestPage[@cms:nodeType = 'page']
>>>>
>>>> I tried writing the query in SQL as:
>>>>
>>>> select * from nt:unstructured where jcr:path like '/%/unitTestPage' and
>>>> cms:nodeType = 'page'
>>>>
>>>> This statement however fails with an exception:
>>>>
>>>> javax.jcr.query.InvalidQueryException: Query:
>>>> select * from nt:(*)unstructured where jcr:path like '/%/unitTestFolder'
>>>> and
>>>> cms:nodeType = 'page'; expected: <end>
>>>>       at
>>>>
>>>> org.apache.jackrabbit.commons.query.sql2.Parser.getSyntaxError(Parser.java:968)
>>>>       at
>>>>
>>>> org.apache.jackrabbit.commons.query.sql2.Parser.createQueryObjectModel(Parser.java:124)
>>>>       at
>>>>
>>>> org.apache.jackrabbit.commons.query.sql2.SQL2QOMBuilder.createQueryObjectModel(SQL2QOMBuilder.java:55)
>>>>       at
>>>>
>>>> org.apache.jackrabbit.core.query.QOMQueryFactory.createQuery(QOMQueryFactory.java:69)
>>>>       at
>>>>
>>>> org.apache.jackrabbit.core.query.CompoundQueryFactory.createQuery(CompoundQueryFactory.java:67)
>>>>       at
>>>>
>>>> org.apache.jackrabbit.core.query.QueryManagerImpl.createQuery(QueryManagerImpl.java:98)
>>>>
>>>> Can some one tell me what I am missing?
>>>>
>>>> Thanks
>>>> Rakesh
>>>>
>>>>
>>>>
>>>
>
> Rakesh Vidyadharan
> President & CEO
> Sans Pareil Technologies, Inc.
> http://sptci.com/
>
>
> | 100 W. Chestnut, Suite 1305 | Chicago, IL 60610-3296 USA |
> | Ph: [phone] | Mobile: [phone] (US), [phone]
> (IN) | Fax: [phone] | E-mail: [hidden email]
>
>
>
>
>

[Rakesh Vidyadharan]

On 11 Nov 2009, at 3:23:42 AM, Marcel Reutegger wrote:

> Hi,
>
> please note that there are no plans to abandon XPath support in
> jackrabbit. the deprecation status is only with the specification.
> therefore I suggest you stick with XPath unless you need functionality
> that requires JQOM or SQL-2.

Thanks, that is good news.  I will stick to XPath for now.

>
> On Mon, Nov 9, 2009 at 14:32, Rakesh Vidyadharan <[hidden email]> wrote:
>> On 9 Nov 2009, at 1:36:25 AM, Thomas Müller wrote:
>> select * from ['nt:unstructured'] where ['jcr:path'] like '/%/unitTestPage'
>> and ['cms:nodeType'] = 'page'
>
> that doesn't work because SQL-2 does not support the jcr:path pseudo
> property, which is only present in JCR 1.0 SQL.
>
> what you rather want is:
>
> select * from [nt:base] where name() = 'unitTestPage' and
> [cms:nodeType] = 'page'
>
> regards
> marcel

I tested this as well and still get back no results.  Strange, since I found the SQL2 unit tests in the QOM package of JR, and I was able to verify my syntax against those.  Anyway, XPath for me until later.

Thanks
Rakesh