jackrabbit-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mark Herman" <MHer...@NBME.org>
Subject RE: SQL2 (via davex)
Date Tue, 09 Aug 2011 21:28:22 GMT
I'm just learning myself so I figured I'd take a crack at it.  It appears
that PATH() isn't implemented, so I went more along the lines of your 2nd
approach.  I'm able to get a list of all the dates and able to filter by
date, but not sure how to filter by length of name considering I only know
how to get the name by dynamic operands, and I can't nest it inside the
length operand.

This gets me all the dates:
SELECT days.* FROM [nt:unstructured] AS issues 
	INNER JOIN [nt: unstructured] AS years ON ISCHILDNODE(years,issues) 
	INNER JOIN [nt:unstructured] AS months ON ISCHILDNODE(months,years) 
	INNER JOIN [nt:unstructured] AS days ON ISCHILDNODE(days,months)
	WHERE NAME(issues) = 'issues'

Easy enough to filter on a day:
SELECT days.* FROM [nt:unstructured] AS issues 
	INNER JOIN [nt: unstructured] AS years ON ISCHILDNODE(years,issues) 
	INNER JOIN [nt:unstructured] AS months ON ISCHILDNODE(months,years) 
	INNER JOIN [nt:unstructured] AS days ON ISCHILDNODE(days,months) 
	WHERE NAME(issues) = 'issues'  
		AND NAME(months) =  '08'  

What I would like to do is:
SELECT days.* FROM [nt:unstructured] AS issues 
	INNER JOIN [nt: unstructured] AS years ON ISCHILDNODE(years,issues) 
	INNER JOIN [nt:unstructured] AS months ON ISCHILDNODE(months,years) 
	INNER JOIN [nt:unstructured] AS days ON ISCHILDNODE(days,months) 
	WHERE NAME(issues) = 'issues'  
		AND LENGTH(NAME(years)) =  4
		AND LENGTH(NAME(months)) =  2  
		AND LENGTH(NAME(days)) =  2  

But that failed. From what I can tell the grammar doesn't allow nested
dynamic operands.

Depends on your use case, but it might just be easier to put a property on
your days (or create a new node type) and just search for that.  Alternately
you could go by a property on your "foo" and "bar" and join with the parent.

-----Original Message-----
From: Lukas Kahwe Smith [mailto:mls@pooteeweet.org] 
Sent: Tuesday, August 09, 2011 10:18 AM
To: users@jackrabbit.apache.org
Subject: SQL2 (via davex)

Hi,

I am undergoing some frustrations with SQL2, which hopefully someone on this
list can resolve.

I have a structure like follows

/issues
/issues/2011
/issues/2011/08
/issues/2011/08/09
/issues/2011/08/09/foo
/issues/2011/08/09/bar
/issues/2011/08/02
/issues/2011/08/02/foo
/issues/2011/08/02/bar

now I am trying to get a list of all the "dates"

aka fetch all nodes that match LIKE '/issues/____/__/__' (not sure if
underscore is supported in SQL)

I tried two approaches.

The first the obvious one just trying to match the PATH (note for now I
hardcoded the 2011 and 08 but eventually I would want those to be place
holders):

HTTP 400: Query:
SELECT * FROM [nt:unstructured] WHERE PATH()(*)LIKE '/issue/2011/08/%';
expected: LENGTH, NAME, LOCALNAME, SCORE, LOWER, UPPER, or CAST

Passing  [nt:unstructured] to PATH() doesnt help. Nor does using jcr:path.

The other approach I tried was finding the node by matching the child nodes
aka (/issues/2011/08/09/*) using an INNER JOIN ON ISDECENDENTNODE() but that
either got me no matches or a 403. Not sure if I am getting the 403 if there
are too many matches because if I remove certain conditions the 403 happend
if I left all relevant conditions in I got no result. But without the join
just looking without filters I get results, so I was pretty certain my
queries worked.

Any tipps? I am also wondering if there are any tools that can help debug the
internals of whats going on inside Jackrabbit when sending SQL2 queries via
davex.

regards,
Lukas Kahwe Smith
mls@pooteeweet.org




Mime
View raw message