Re: [iROD-Chat:8559] 'or' queries in irods

[Reagan Moore]

Such a query could be composed with micro-services as two separate queries, with separate lists returned that are then processed.

*Select = "SELECT DATA_PATH where DATA_PATH like '%TOTO%%'";

msiExecStrCondQuery(*Select,*QOut);

foreach(*QOut) {

  msiPrintKeyValPair("stdout",*QOut)  }

*Select = "SELECT COLL_NAME where COLL_NAME like '%TITI%%'";

msiExecStrCondQuery(*Select,*QOut2);

foreach(*QOut2) {

  msiPrintKeyValPair("stdout",*QOut2)  }

Reagan Moore

From: Amine Achkar <achkar...@gmail.com>
Reply-To: "irod...@googlegroups.com" <irod...@googlegroups.com>
Date: Thursday, August 30, 2012 3:30 AM
To: "irod...@googlegroups.com" <irod...@googlegroups.com>
Subject: [iROD-Chat:8559] 'or' queries in irods

Good Morning :),

Do you know gays how to do an "or query"  with irods command line. Something like: iquest "SELECT DATA_PATH, COLL_NAME WHERE DATA_PATH like '%TOTO%' OR COLL_NAME like '%TITI%' ", 

cordially.

--
"iRODS: the Integrated Rule-Oriented Data-management System; A community driven, open source, data grid software solution" https://www.irods.org
 
iROD-Chat: http://groups.google.com/group/iROD-Chat

[Conway, Mike]

Hey Amine,

When you are doing iquest, you are using something called 'General Query', or GenQuery for short.  This is an abstraction layer above SQL and has the advantage of having policies applied at invocation time.  The drawback is that it is somewhat limited (including the approach of supporting implicit ANDs in queries.

I'm interested to see myself whether any 'OR' facility has been added to GenQuery, and Wayne or Mike may jump in with some insights there.  The need for richer queries using the full range of SQL has been a common request, and for this reason Wayne implemented something called 'specific query'.  This was also just added to Jargon.  There are iadmin commands that a rods admin can use to specify parameterized sql statements under an alias, then one may use iquest with the —sql option to supply this alias and bind parameters.  Jargon also has services to maintain, query, and execute these specific queries.

I think specific query might help your use case, it's really an important development for those that maintain rich metadata in iRODS.

Cheers,

MC

---------------------

Mike Conway

Interface and Java API/Integration Developer  – DICE

www.irods.org

skype:michael.c.conway

LinkedIn: http://www.linkedin.com/pub/mike-conway/5/78a/231

[Wayne Schroeder]

Yes, there's only limited support for 'or's in the general-query. As
Reagan points out you can do this as two micro-service queries that
then return both sets of results and as Mike C points out the specific-
query can be used for more.

There's also limited support for 'OR's on the same column:
iquest "SELECT DATA_PATH WHERE DATA_PATH like '%x1%' || like '%y2%' ";
will work.

The general-query provides a simplified interface to ICAT SQL queries,
and is used quite a bit in iRODS both internally (server calls) and
via clients (ils, iquest, etc). Callers do not need to know the
structure of the ICAT schema or SQL, and the needed SQL with
appropriate access control conditions is dynamically generated on each
call. But it does not provide the full flexibility of SQL.

As Mike C pointed out, the specific-query is a good alternative in
these cases. The admin can (carefully) define the SQL to be run and
then clients can execute it. You could define the query as you want
and set it up via 'iadmin asq' (add specific query) and then execute
it via 'iquest --sql', for example.

- Wayne -

On Aug 30, 6:19 am, "Conway, Mike" <michael_con...@unc.edu> wrote:
> Hey Amine,
>
> When you are doing iquest, you are using something called 'General Query', or GenQuery for short.  This is an abstraction layer above SQL and has the advantage of having policies applied at invocation time.  The drawback is that it is somewhat limited (including the approach of supporting implicit ANDs in queries.
>
> I'm interested to see myself whether any 'OR' facility has been added to GenQuery, and Wayne or Mike may jump in with some insights there.  The need for richer queries using the full range of SQL has been a common request, and for this reason Wayne implemented something called 'specific query'.  This was also just added to Jargon.  There are iadmin commands that a rods admin can use to specify parameterized sql statements under an alias, then one may use iquest with the —sql option to supply this alias and bind parameters.  Jargon also has services to maintain, query, and execute these specific queries.
>
> I think specific query might help your use case, it's really an important development for those that maintain rich metadata in iRODS.
>
> Cheers,
> MC
>
> ---------------------
> Mike Conway
> Interface and Java API/Integration Developer  – DICEwww.irods.org
>
> skype:michael.c.conway
> LinkedIn:http://www.linkedin.com/pub/mike-conway/5/78a/231
>

[Conway, Mike]

No, the 'or' is not supported in gen query.  You can do such things using specific query, which allows SQL to be used.  I'll try and put together an example for you.

Cheers,

MC

---------------------

Mike Conway

Interface and Java API/Integration Developer  – DICE

www.irods.org

skype:michael.c.conway

LinkedIn: http://www.linkedin.com/pub/mike-conway/5/78a/231

From: <zani...@gmail.com>
Reply-To: <irod...@googlegroups.com>
Date: Thu, 4 Oct 2012 09:17:54 -0700
To: <irod...@googlegroups.com>

Subject: [iROD-Chat:8735] Re: 'or' queries in irods

Hello everybody,

I tried to execute the query you suggested using Jargon-core client (v 3.1.4):

doAO.findWhere("META_DATA_ATTR_VALUE like 'a' || like 'b'");

but I got the exception:

org.irods.jargon.core.query.JargonQueryException: untranslatable condition in position:1 after the where
    at org.irods.jargon.core.query.IRODSGenQueryTranslator.translateConditions(IRODSGenQueryTranslator.java:233)
    at org.irods.jargon.core.query.IRODSGenQueryTranslator.getTranslatedQuery(IRODSGenQueryTranslator.java:107)
    at org.irods.jargon.core.pub.IRODSGenQueryExecutorImpl.executeIRODSQueryWithPaging(IRODSGenQueryExecutorImpl.java:123)
    at org.irods.jargon.core.pub.DataObjectAOImpl.findWhere(DataObjectAOImpl.java:231)
    at org.irods.jargon.core.pub.DataObjectAOImpl.findWhere(DataObjectAOImpl.java:197)

is there a way to execute this kind of queries via java apis?

thanks,
Stefano

[Long Phan]

Hello everybody,

I got the same problem, please need some helps.
I query all files from database with resc_name = StorageResc2 like this
iquest select COLL_NAME,DATA_NAME where COLL_NAME like '/tempZone2/home/irods%' AND RESC_NAME = 'StorageResc2'

it worked great, iquery has queried all files ( StorageResc2 hat many Directories and files.)

But when I write this with microservice :
test {
    msiExecStrCondQuery("select COLL_NAME,DATA_NAME where COLL_NAME like '*Coll' AND RESC_NAME = '*QuellResource' ",*TT);
    foreach(*TT) {
        msiGetValByKey(*TT,"DATA_NAME",*Name);
        writeLine("stdout","Name : *Name  ");
        msiGetValByKey(*TT,"COLL_NAME",*Collname);
        writeLine("stdout","Coll : *Collname : ");
    }
}
INPUT *Coll = '/tempZone2/home/irods%' ,*QuellResource = 'StorageResc2'
OUTPUT ruleExecOut

It does not work right, it just query some files from StorageResc2 but not all. What did I do wrong here ?
Thanks,

Long

[Hao Xu]

Hello,

By default the buffer only contain a certain number of rows. In pre3.2 code, you need do a little management of the buffer. You need a microservice which indicates if there are additional rows and another microservice which retrieves additional rows from the database.

An example is located at /clients/icommands/test/rules3.0/rulemsiExecGenQuery.r in iRODS source.

If you are running 3.2, you can try language integrated gen query (LIGQ). It is documented on the iRODS wiki and mentioned on iRODS release notes:

https://www.irods.org/index.php/Release_Notes_3.2

With LIGQ, you don't need to call those microservices any more, the rule engine automatically manages the buffer.

  Hao

--

[Long Phan]

Thanks Hao. It helped me a lot, now I have to rewrite some rules with that. I used iRODS 3.1.
Later I will try Version 3.2

Long