JCR-SQL2 query

[danisevsky <da...@gmail.com>]

Hi,

could someone please help me with JCR-SQL2 query? I would like to do
find out count of all files (photos) which are in some folder
hierarchy. This query is almost what I need

Select * from [nt:file] as file WHERE ISDESCENDANTNODE(file,
[/brix:root/Data/Users/2/35935/racesPhotos/79])

But I need to exclude all files which are on path "*/imported/small/*".

Thanks in advance.

Re: JCR-SQL2 query

[Alexander Klimetschek <ak...@adobe.com>]

On 12.10.2012, at 09:51, danisevsky <da...@gmail.com> wrote:

> could someone please help me with JCR-SQL2 query? I would like to do
> find out count of all files (photos) which are in some folder
> hierarchy.
>  
> This query is almost what I need
> 
> Select * from [nt:file] as file WHERE ISDESCENDANTNODE(file,
> [/brix:root/Data/Users/2/35935/racesPhotos/79])
> 
> But I need to exclude all files which are on path "*/imported/small/*".

Don't use a query for that, simply use the JCR (navigational) API and iterate over the tree. Will be faster and more efficent.

Something like:

public int countFiles(Node folder, String excludeGlob, bool deep) {
    int count = 0;
    for (NodeIterator iter = folder.getNodes(); iter.hasNext();) {
        Node node = iter.nextNode();
        if (node.isNodeType(JcrConstants.NT_FILE) && !matches(node.getPath(), excludeGlob)) {
            count++;
        }
        if (deep && node.isNodeType(JcrConstants.NT_FOLDER)) {
            count += traverse(node, excludeGlob, deep);
        }
    }
    return count;
}

int count = countFiles(session.getNode("/brix:root/Data/Users/2/35935/racesPhotos/79", "*/imported/small/*", true));


Cheers,
Alex

Re: JCR-SQL2 query

[Lukas Kahwe Smith <ml...@pooteeweet.org>]

On Oct 12, 2012, at 9:51 , danisevsky <da...@gmail.com> wrote:

> Hi,
> 
> could someone please help me with JCR-SQL2 query? I would like to do
> find out count of all files (photos) which are in some folder
> hierarchy. This query is almost what I need
> 
> Select * from [nt:file] as file WHERE ISDESCENDANTNODE(file,
> [/brix:root/Data/Users/2/35935/racesPhotos/79])
> 
> But I need to exclude all files which are on path "*/imported/small/*".


could be tricky. afaik Jackrabbit hasnt implemented the PATH() dynamic operand.
and while we are at it .. it also doesnt support a COUNT() :(

ModeShape supports PATH, but also doesnt cover COUNT:
https://docs.jboss.org/author/display/MODE/JCR-SQL2#JCR-SQL2-Pseudocolumns

regards,
Lukas Kahwe Smith
mls@pooteeweet.org