You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@jackrabbit.apache.org by Lukas Kahwe Smith <ml...@pooteeweet.org> on 2011/09/27 22:20:26 UTC

SQL2 and references

Hi,

I am wondering how to do a search given the following

/foo/bar (UUID 1234, property "lala" is "huii")
/ding/dong (referencing UUID 1234)

now I want to do a search limited to children of "/ding" but when searching I want it to search the contents of referenced nodes.

i was hoping that SQL2 would just do this automatically:
SELECT * FROM [nt:unstructured] WHERE ISCHILDNODE('/ding') AND [nt:unstructured].lala = 'huii'

but this doesnt match .. 

regards,
Lukas Kahwe Smith
mls@pooteeweet.org

Re: SQL2 and references

Posted by Lukas Kahwe Smith <ml...@pooteeweet.org>.
On Sep 28, 2011, at 01:15 , Mark Herman wrote:

> 
> Lukas Kahwe Smith wrote:
>> 
>> 
>> <D:searchrequest xmlns:D="DAV:"><JCR-SQL2></JCR-SQL2></D:searchrequest>
>> 
>> 
> 
> I'm a bit confused about the use of "reference" but i was able to get
> results using the property name. I dont' know if "reference" a special word
> or just an example of a property name.  Here is my query that does return a
> result:

yeah it was just an example property name that Jukka used in his reply.

> SELECT * FROM [nt:unstructured] AS data INNER JOIN [nt:unstructured] AS
> referring ON referring.[dong] = data.[jcr:uuid] WHERE data.lala= 'huii'
> 
> I tried to get the data set up how you explained it, just in case, here is
> mine:
> 
> /foo [nt:unstructured]
> /foo/bar [nt:unstructured]
> /foo/bar@lala = huii (lala is string property of bar)
> /ding [nt:unstructured]
> /ding@dong = ##barUUID### (dong is a property of type "Reference")


ok thx .. i will need to simplify my setup as much as i can, because i see no reason why i should have issues with running JOINs specifically.

regards,
Lukas Kahwe Smith
mls@pooteeweet.org

Re: SQL2 and references

Posted by Lukas Kahwe Smith <ml...@pooteeweet.org>.
On Oct 6, 2011, at 14:15 , Lukas Kahwe Smith wrote:

> Also if I move the ISDESCENDANTNODE() checks to the WHERE it the query doesnt explode, but obviously I will get additional rows due to the LEFT OUTER JOIN


nevermind .. got things to work by using the ISDESCENDANTNODE() filter in the WHERE clause after all.

regards,
Lukas Kahwe Smith
mls@pooteeweet.org

Re: SQL2 and references

Posted by Lukas Kahwe Smith <ml...@pooteeweet.org>.
On Sep 30, 2011, at 14:01 , Lukas Kahwe Smith wrote:

> 
> On Sep 30, 2011, at 13:25 , Jukka Zitting wrote:
> 
>> Hi,
>> 
>> On Fri, Sep 30, 2011 at 11:05 AM, Lukas Kahwe Smith <ml...@pooteeweet.org> wrote:
>>> @Jukka: could this really mean that JOIN's simply do not work via Davex?
>> 
>> Yes, that's what I was referring to in my previous message. Most of
>> the davex remoting layer was built before JCR 2.0, so new concepts
>> like multiple selectors per result set may well not yet be supported.
>> I didn't check the code yet, but it should be fairly easy to track the
>> server-side code down to see if it's using Row.getNode() or
>> Row.getNode(String).
> 
> 
> ok thx .. will see if we at Liip can figure this out. in the mean time i have created a ticket:
> https://issues.apache.org/jira/browse/JCR-3089


ok running a patched 2.3 with the fix from the above ticket I can now do JOIN's via Davex.

but now i am running into trouble with the original query I needed:

SELECT data.* FROM [nzz:unstructured] AS data LEFT OUTER JOIN [nzz:unstructured] AS referring ON
                (
                    (referring.reference = data.[jcr:uuid] AND ISDESCENDANTNODE(referring, '/article/2011/01/10'))
                    OR ISDESCENDANTNODE([nzz:unstructured], '/article/2011/01/10')
                )

The goal would be to get all [nt:unstructured] nodes that are either direct descendants of the path '/article/2011/01/10' or that have a defendant node of '/article/2011/01/10' that references them.

So given this structure:
/article/2011/01/10/foo@reference=1234
/article/2011/01/10/bar
/article/2011/01/09/ding@uuid=1234

I want it to return:
/article/2011/01/10/bar
/article/2011/01/09/ding@uuid=1234

However when sending the above query via Davex I get:

<?xml version="1.0"?>
<response><status><![CDATA[error]]></status><status_code>500</status_code><status_text><![CDATA[Internal Server Error]]></status_text><currentContent><![CDATA[]]></currentContent><message><![CDATA[HTTP 400: Query:
SELECT data.* FROM [nzz:unstructured] AS data LEFT OUTER JOIN [nzz:unstructured] AS referring ON
                (
                    ((*)referring.reference = data.[jcr:uuid] AND ISDESCENDANTNODE(referring, '/article/2011/01/10'))
                    OR ISDESCENDANTNODE([nzz:unstructured], '/article/2011/01/10')
                ); expected: .]]></message></response>

Not sure where its expecting the "."

Also if I move the ISDESCENDANTNODE() checks to the WHERE it the query doesnt explode, but obviously I will get additional rows due to the LEFT OUTER JOIN

regards,
Lukas Kahwe Smith
mls@pooteeweet.org

Re: SQL2 and references

Posted by Lukas Kahwe Smith <ml...@pooteeweet.org>.
On Sep 30, 2011, at 13:25 , Jukka Zitting wrote:

> Hi,
> 
> On Fri, Sep 30, 2011 at 11:05 AM, Lukas Kahwe Smith <ml...@pooteeweet.org> wrote:
>> @Jukka: could this really mean that JOIN's simply do not work via Davex?
> 
> Yes, that's what I was referring to in my previous message. Most of
> the davex remoting layer was built before JCR 2.0, so new concepts
> like multiple selectors per result set may well not yet be supported.
> I didn't check the code yet, but it should be fairly easy to track the
> server-side code down to see if it's using Row.getNode() or
> Row.getNode(String).


ok thx .. will see if we at Liip can figure this out. in the mean time i have created a ticket:
https://issues.apache.org/jira/browse/JCR-3089

regards,
Lukas Kahwe Smith
mls@pooteeweet.org

Re: SQL2 and references

Posted by Jukka Zitting <ju...@gmail.com>.
Hi,

On Fri, Sep 30, 2011 at 11:05 AM, Lukas Kahwe Smith <ml...@pooteeweet.org> wrote:
> @Jukka: could this really mean that JOIN's simply do not work via Davex?

Yes, that's what I was referring to in my previous message. Most of
the davex remoting layer was built before JCR 2.0, so new concepts
like multiple selectors per result set may well not yet be supported.
I didn't check the code yet, but it should be fairly easy to track the
server-side code down to see if it's using Row.getNode() or
Row.getNode(String).

BR,

Jukka Zitting

Re: SQL2 and references

Posted by Lukas Kahwe Smith <ml...@pooteeweet.org>.
On Sep 28, 2011, at 01:15 , Mark Herman wrote:

> 
> Lukas Kahwe Smith wrote:
>> 
>> 
>> <D:searchrequest xmlns:D="DAV:"><JCR-SQL2></JCR-SQL2></D:searchrequest>
>> 
>> 
> 
> I'm a bit confused about the use of "reference" but i was able to get
> results using the property name. I dont' know if "reference" a special word
> or just an example of a property name.  Here is my query that does return a
> result:
> 
> SELECT * FROM [nt:unstructured] AS data INNER JOIN [nt:unstructured] AS
> referring ON referring.[dong] = data.[jcr:uuid] WHERE data.lala= 'huii'
> 
> I tried to get the data set up how you explained it, just in case, here is
> mine:
> 
> /foo [nt:unstructured]
> /foo/bar [nt:unstructured]
> /foo/bar@lala = huii (lala is string property of bar)
> /ding [nt:unstructured]
> /ding@dong = ##barUUID### (dong is a property of type "Reference")


I did my very best trying to reproduce the issue with java (not really my native language).

DavexClient Client = new DavexClient(url);
Repository repo = Client.getRepository();
Credentials sc = new SimpleCredentials("admin","admin".toCharArray());
Session s = repo.login(sc,workspace);

QueryManager qm = s.getWorkspace().getQueryManager();

String sql = "SELECT data.* FROM [nt:unstructured] AS data WHERE data.lala= 'huii'";
sql = "SELECT * FROM [nt:unstructured] AS data INNER JOIN [nt:unstructured] AS referring ON referring.[dong] = data.[jcr:uuid] WHERE data.lala = 'huii'";
sql = "SELECT * FROM [nt:unstructured] AS data INNER JOIN [nt:unstructured] AS referring ON ISDESCENDANTNODE(data, referring) WHERE data.lala = 'huii'";
Query query = qm.createQuery(sql, Query.JCR_SQL2);
QueryResult qr = query.execute();

The first query works just fine and I can iterate over the result. Neither the second nor the third query works.
In both cases I end up with a javax.jcr.RepositoryException

@Jukka: could this really mean that JOIN's simply do not work via Davex?

regards,
Lukas Kahwe Smith
mls@pooteeweet.org

Re: SQL2 and references

Posted by Mark Herman <MH...@NBME.org>.
Lukas Kahwe Smith wrote:
> 
> 
> <D:searchrequest xmlns:D="DAV:"><JCR-SQL2></JCR-SQL2></D:searchrequest>
> 
> 

I'm a bit confused about the use of "reference" but i was able to get
results using the property name. I dont' know if "reference" a special word
or just an example of a property name.  Here is my query that does return a
result:

SELECT * FROM [nt:unstructured] AS data INNER JOIN [nt:unstructured] AS
referring ON referring.[dong] = data.[jcr:uuid] WHERE data.lala= 'huii'

I tried to get the data set up how you explained it, just in case, here is
mine:

/foo [nt:unstructured]
/foo/bar [nt:unstructured]
/foo/bar@lala = huii (lala is string property of bar)
/ding [nt:unstructured]
/ding@dong = ##barUUID### (dong is a property of type "Reference")

--
View this message in context: http://jackrabbit.510166.n4.nabble.com/SQL2-and-references-tp3848951p3849480.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.

Re: SQL2 and references

Posted by Lukas Kahwe Smith <ml...@pooteeweet.org>.
On Sep 28, 2011, at 09:58 , Jukka Zitting wrote:

> Hi,
> 
> On Wed, Sep 28, 2011 at 12:27 AM, Lukas Kahwe Smith <ml...@pooteeweet.org> wrote:
>> speaking of JOIN, i ran into this issue before but getting a 403 Repository exception when I send a simple JOIN via davex:
>> 
>> <D:searchrequest xmlns:D="DAV:"><JCR-SQL2><![CDATA[SELECT * FROM [nt:unstructured] AS data INNER JOIN [nt:unstructured] AS referring ON referring.reference = data.[jcr:uuid] WHERE data.foo = 'bar']]></JCR-SQL2></D:searchrequest>
>> 
>> so far the only JOIN's I have managed to send without an error are those that have zero results ..
> 
> I wonder if that's a problem with the remoting layer having trouble
> dealing with results that contain more than a single selector. Does
> specifying SELECT data.* instead of just SELECT * help?


that doesnt seem to be it either ..

here is the code i am using. note its using PHPCR to setup the nodes (which means the next step will be doing all of the below via Java)

$session = $this->getContainer()->get('phpcr.session');
$rootNode = $session->getRootNode();

// /foo [nt:unstructured]
$fooNode = $rootNode->addNode('foo', 'nt:unstructured');

// /foo/bar [nt:unstructured]
$barNode = $fooNode->addNode('bar', 'nt:unstructured');
$barNode->addMixin("mix:referenceable");

// /foo/bar@lala = huii (lala is string property of bar)
$barNode->setProperty('lala', 'huii', \PHPCR\PropertyType::STRING);

// /ding [nt:unstructured]
$dingNode = $rootNode->addNode('ding', 'nt:unstructured');

$session->save();

// /ding@dong = ##barUUID### (dong is a property of type "Reference")
$dingNode->setProperty('dong', $barNode, \PHPCR\PropertyType::REFERENCE);

$session->save();

$qm = $session->getWorkspace()->getQueryManager();

$statement = "SELECT data.* FROM [nt:unstructured] AS data WHERE data.lala= 'huii'";
$query = $qm->createQuery($statement, \PHPCR\Query\QueryInterface::JCR_SQL2);

// this executes fine and the row is outputted properly
$nodes = $query->execute();
foreach ($nodes->getRows() as $row) {
    var_dump($row->getValues());
}

$statement = "SELECT data.* FROM [nt:unstructured] AS data INNER JOIN [nt:unstructured] AS referring ON referring.[dong] = data.[jcr:uuid] WHERE data.lala= 'huii'";
$query = $qm->createQuery($statement, \PHPCR\Query\QueryInterface::JCR_SQL2);

// the following line throws an exception unless I skip setting the reference
$nodes = $query->execute();
foreach ($nodes->getRows() as $row) {
    var_dump($row->getValues());
}

The second query is send as the following:
<D:searchrequest xmlns:D="DAV:"><JCR-SQL2><![CDATA[SELECT data.* FROM [nt:unstructured] AS data INNER JOIN [nt:unstructured] AS referring ON referring.[dong] = data.[jcr:uuid] WHERE data.lala= 'huii']]></JCR-SQL2></D:searchrequest>

And this is the response I am receiving:
<?xml version="1.0" encoding="UTF-8"?><D:error xmlns:D="DAV:"><dcr:exception xmlns:dcr="http://www.day.com/jcr/webdav/1.0"><dcr:class>javax.jcr.RepositoryException</dcr:class><dcr:message/></dcr:exception></D:error>"

regards,
Lukas Kahwe Smith
mls@pooteeweet.org

Re: SQL2 and references

Posted by Jukka Zitting <ju...@gmail.com>.
Hi,

On Wed, Sep 28, 2011 at 12:27 AM, Lukas Kahwe Smith <ml...@pooteeweet.org> wrote:
> speaking of JOIN, i ran into this issue before but getting a 403 Repository exception when I send a simple JOIN via davex:
>
> <D:searchrequest xmlns:D="DAV:"><JCR-SQL2><![CDATA[SELECT * FROM [nt:unstructured] AS data INNER JOIN [nt:unstructured] AS referring ON referring.reference = data.[jcr:uuid] WHERE data.foo = 'bar']]></JCR-SQL2></D:searchrequest>
>
> so far the only JOIN's I have managed to send without an error are those that have zero results ..

I wonder if that's a problem with the remoting layer having trouble
dealing with results that contain more than a single selector. Does
specifying SELECT data.* instead of just SELECT * help?

BR,

Jukka Zitting

Re: SQL2 and references

Posted by Lukas Kahwe Smith <ml...@pooteeweet.org>.
On Sep 27, 2011, at 22:47 , Lukas Kahwe Smith wrote:

> 
> On Sep 27, 2011, at 22:39 , Jukka Zitting wrote:
> 
>> Hi Lukas,
>> 
>> On Tue, Sep 27, 2011 at 10:20 PM, Lukas Kahwe Smith <ml...@pooteeweet.org> wrote:
>>> I am wondering how to do a search given the following
>>> 
>>> /foo/bar (UUID 1234, property "lala" is "huii")
>>> /ding/dong (referencing UUID 1234)
>>> 
>>> now I want to do a search limited to children of "/ding" but when
>>> searching I want it to search the contents of referenced nodes.
>> 
>> As you noticed, references are not automatically followed by the query
>> engine. What you can do instead is to use a join query like this:
>> 
>>   SELECT dst.*
>>   FROM [nt:base] AS src
>>   JOIN [nt:unstructured] AS dst ON src.[reference] = dst.[jcr:uuid]
>>   WHERE ISCHILDNODE(src, '/ding') AND dst.lala = 'huii'
> 
> 
> I see but what about still being able to match with the same query?
> 
> /ding/booo (property "lala" is "huii")
> 
> i guess then i need to do a LEFT JOIN, which will likely put another dent on the performance, but i guess thats the price of references.


speaking of JOIN, i ran into this issue before but getting a 403 Repository exception when I send a simple JOIN via davex:

<D:searchrequest xmlns:D="DAV:"><JCR-SQL2><![CDATA[SELECT * FROM [nt:unstructured] AS data INNER JOIN [nt:unstructured] AS referring ON referring.reference = data.[jcr:uuid] WHERE data.foo = 'bar']]></JCR-SQL2></D:searchrequest>

so far the only JOIN's I have managed to send without an error are those that have zero results .. i fear it might be something with how i am constructing my davex queries. then again without a join everything works nicely. guess i will need to debug this with some co-workers during normal work hours ..

regards,
Lukas Kahwe Smith
mls@pooteeweet.org

Re: SQL2 and references

Posted by Lukas Kahwe Smith <ml...@pooteeweet.org>.
On Sep 27, 2011, at 22:39 , Jukka Zitting wrote:

> Hi Lukas,
> 
> On Tue, Sep 27, 2011 at 10:20 PM, Lukas Kahwe Smith <ml...@pooteeweet.org> wrote:
>> I am wondering how to do a search given the following
>> 
>> /foo/bar (UUID 1234, property "lala" is "huii")
>> /ding/dong (referencing UUID 1234)
>> 
>> now I want to do a search limited to children of "/ding" but when
>> searching I want it to search the contents of referenced nodes.
> 
> As you noticed, references are not automatically followed by the query
> engine. What you can do instead is to use a join query like this:
> 
>    SELECT dst.*
>    FROM [nt:base] AS src
>    JOIN [nt:unstructured] AS dst ON src.[reference] = dst.[jcr:uuid]
>    WHERE ISCHILDNODE(src, '/ding') AND dst.lala = 'huii'


I see but what about still being able to match with the same query?

/ding/booo (property "lala" is "huii")

i guess then i need to do a LEFT JOIN, which will likely put another dent on the performance, but i guess thats the price of references.

regards,
Lukas Kahwe Smith
mls@pooteeweet.org

Re: SQL2 and references

Posted by Jukka Zitting <ju...@gmail.com>.
Hi Lukas,

On Tue, Sep 27, 2011 at 10:20 PM, Lukas Kahwe Smith <ml...@pooteeweet.org> wrote:
> I am wondering how to do a search given the following
>
> /foo/bar (UUID 1234, property "lala" is "huii")
> /ding/dong (referencing UUID 1234)
>
> now I want to do a search limited to children of "/ding" but when
> searching I want it to search the contents of referenced nodes.

As you noticed, references are not automatically followed by the query
engine. What you can do instead is to use a join query like this:

    SELECT dst.*
    FROM [nt:base] AS src
    JOIN [nt:unstructured] AS dst ON src.[reference] = dst.[jcr:uuid]
    WHERE ISCHILDNODE(src, '/ding') AND dst.lala = 'huii'

BR,

Jukka Zitting