Search Dataset in Investigation (query)

[c.felder.m.sc]

Hello,

I am looking for a *tested* query string to search a Dataset with name dsName in an Investigation.

Assuming c is an authenticated ICatClient (PyICAT) instance or a suds.client.Client instance and

the following initialization.

#c = ...

#sessionId = ...

investigationId = 42

lst_res = None

dsName = "My Dataset"

(1) The following query returns an empty list:

lst_res = c.service.search(sessionId, "Dataset" +

                           "[name='%s']" %dsName)

(2) To achieve what I want I have to use the following Code:

inv = c.service.get(sessionId, "Investigation" +

                    " INCLUDE " + "Dataset", investigationId)

# search for an Dataset with name == dsName

if hasattr(inv, "datasets"):

    lst_res = filter(lambda d: d.name == dsName, inv.datasets)

I tried some other query strings in (1) but did not find a solution to my problem.

So I have written (2) which gets all Datasets in an Investigation and do a client/python search on this.

It would be nice if somebody knows how to do this in style of (1) so I am directly searching the database and

need not to search on client side.

Thanks for your help in advance.

Christian

[tom.g...@stfc.ac.uk]

Hi Christian,

 

I have tested the following and all 3 work as I would expect.

 

1)      Find all datasets with a given name (‘Default’)

object[] results = service.search(sessionId, "1,5 Dataset [name='Default']");

 

2)      Find all investigations with a given name (‘Fe70Ni30-MA RT-5min’) that have datasets with a given name (‘Default’)

object[] results = service.search(sessionId, "Investigation [title='Fe70Ni30-MA RT-5min'] <-> Dataset [name='Default']");

 

3)      Find all datasets with a given name (‘Default’) in investigations with a given name ('Fe70Ni30-MA RT-5min’)

object[] results = service.search(sessionId, "Dataset [name='Default'] <-> Investigation [title='Fe70Ni30-MA RT-5min']");

 

 

This is tested in an environment where my account has direct permission on all objects,

The code is part of my ingest code for ISIS (hardcoded name and title values in this email example)

This is done from a C# client.

 

Best,

Tom

--
http://groups.google.com/group/icat-developers

--
Scanned by iCritical.

[c.felder.m.sc]

Hi Tom,

thanks for your reply. For me it is mystery why it is not working on my ICAT and on yours it works.

I am using latest ICAT version 4.2.1.

1)      Find all datasets with a given name (‘Default’)

object[] results = service.search(sessionId, "1,5 Dataset [name='Default']");

In [26]: c.service.search(sid,"Dataset[name='PG']")

Out[26]: []

This code is similar to yours just in Python and what I get is an empty result.

icat.log for this operation prints out the following

2012-12-06 16:09:04,869 [http-thread-pool-8443(4)] DEBUG ICAT - Invoking org.icatproject.exposed.ICAT.search()

2012-12-06 16:09:04,870 [http-thread-pool-8443(4)] DEBUG BeanManager - user: root is associated with: 62c3a32b-949c-402d-8bd9-3f04d683f82a

2012-12-06 16:09:04,871 [http-thread-pool-8443(4)] DEBUG BeanManager - root searches for Dataset[name='PG']

2012-12-06 16:09:04,873 [http-thread-pool-8443(4)] DEBUG SearchQuery - Got 2 authz queries for search by root to a Dataset

2012-12-06 16:09:04,874 [http-thread-pool-8443(4)] DEBUG SearchQuery - Substituted SearchJPQL: (InvestigationUser$.user.name = 'root')

2012-12-06 16:09:04,874 [http-thread-pool-8443(4)] DEBUG SearchQuery - Related beans: Investigation InvestigationUser

2012-12-06 16:09:04,875 [http-thread-pool-8443(4)] INFO  SearchQuery - Null restriction => Operation permitted

2012-12-06 16:09:04,875 [http-thread-pool-8443(4)] DEBUG BeanManager - JPQL: SELECT Dataset$ FROM Dataset AS Dataset$ JOIN Dataset$.investigation AS Investigation$ JOIN Investigation$.investigationUsers AS InvestigationUser$  WHERE (Dataset$.name = 'PG') 

2012-12-06 16:09:04,876 [http-thread-pool-8443(4)] DEBUG BeanManager - Obtained 0 results.

2012-12-06 16:09:04,877 [http-thread-pool-8443(4)] DEBUG ICAT - Method org.icatproject.exposed.ICAT.search() took 0.0080s to execute

Best

Christian

[tom.g...@stfc.ac.uk]

The actual JPQL that is being generated is quite different

 

Tom:                      JPQL: SELECT Dataset$ FROM Dataset AS Dataset$  WHERE (Dataset$.name = 'Default')

Christian:             JPQL: SELECT Dataset$ FROM Dataset AS Dataset$ JOIN Dataset$.investigation AS Investigation$ JOIN Investigation$.investigationUsers AS InvestigationUser$  WHERE (Dataset$.name = 'PG')

I suspect this is because a permission rule is being applied via Investigation and InvestigationUser (see log file output below – mine only has 1 authz rule)

 

What auth rules do you have set?

 

I am also on 4.2.1

I use the “1,5” restriction simply because every investigation in my ICAT has a ‘Default’ dataset – 150,000+ in total.

 

Best,
Tom

 

 

2012-12-06 15:13:23,106 [http-thread-pool-8181(1)] DEBUG ICAT - Method org.icatproject.exposed.ICAT.login() took 0.482s to execute

2012-12-06 15:13:49,375 [http-thread-pool-8181(5)] DEBUG ICAT - Invoking org.icatproject.exposed.ICAT.search()

2012-12-06 15:13:49,378 [http-thread-pool-8181(5)] DEBUG BeanManager - user: uows/13574 is associated with: eda7f99a-40e3-4777-92ac-d904bbd5a566

2012-12-06 15:13:49,378 [http-thread-pool-8181(5)] DEBUG BeanManager - uows/13574 searches for 1,5 Dataset [name='Default']

2012-12-06 15:13:49,381 [http-thread-pool-8181(5)] DEBUG SearchQuery - Got 1 authz queries for search by uows/13574 to a Dataset

2012-12-06 15:13:49,381 [http-thread-pool-8181(5)] INFO  SearchQuery - Null restriction => Operation permitted

2012-12-06 15:13:49,382 [http-thread-pool-8181(5)] DEBUG BeanManager - JPQL: SELECT Dataset$ FROM Dataset AS Dataset$  WHERE (Dataset$.name = 'Default')

2012-12-06 15:13:49,384 [http-thread-pool-8181(5)] DEBUG BeanManager - Obtained 5 results.

2012-12-06 15:13:49,386 [http-thread-pool-8181(5)] DEBUG ICAT - Method org.icatproject.exposed.ICAT.search() took 0.011s to execute

--
http://groups.google.com/group/icat-developers

--
Scanned by iCritical.

[c.felder.m.sc]

I have now identified the rule which causes problems.

If I delete this rule I get similar JPQL.

what = "Dataset <-> Investigation <-> InvestigationUser [user.name = :user]"

JPQL: SELECT Dataset$ FROM Dataset AS Dataset$  WHERE (Dataset$.name = 'PG'

Now we know what causes the problem on my installation. Any suggestions?

Thank you very much.

[Steve Fisher]

There may be an ICAT bug in this area. I am just looking into something similar.

Steve

--
http://groups.google.com/group/icat-developers

[Steve Fisher]

The problem appears to be that the JPQL: "SELECT DISTINCT r  FROM Rule r LEFT JOIN r.group g LEFT JOIN g.userGroups ug LEFT JOIN ug.user u WHERE (u.name = :member OR g IS NULL) AND r.bean = :bean AND r.r = TRUE" in my code

is translated by eclipselink to: SELECT DISTINCT t3.ID, t3.BEAN, t3.BEANS, t3.C, t3.CREATE_ID, t3.CREATE_TIME, t3.CRUDFLAGS, t3.CRUDJPQL, t3.D, t3.MOD_ID, t3.MOD_TIME, t3.R, t3.RESTRICTED, t3.SEARCHJPQL, t3.U, t3.WHAT, t3.GROUP_ID FROM RULE t3 LEFT OUTER JOIN GROUP_ t2 ON (t2.ID = t3.GROUP_ID) LEFT OUTER JOIN USERGROUP t1 ON (t1.GROUP_NAME = t2.ID), USER_ t0 WHERE (((((t0.NAME = ?) OR (t3.GROUP_ID IS NULL)) AND (t3.BEAN = ?)) AND (t3.R = ?)) AND (t0.ID = t1.USER_NAME))

which contains an unconditional t0.ID = t1.USER_NAME requiring that the user be part of a group - which was always the case in my tests. This does not appear to correspond to my original JPQL query which is all LEFT JOINs

JPQL does not contain unions, and even if it did it might rewrite the SQL. So the only way I can see around it is to issue two queries to find candidate rules and combine the results:

SELECT DISTINCT r  FROM Rule r LEFT JOIN r.group g LEFT JOIN g.userGroups ug  WHERE ug.user.name = :member AND r.bean = :bean AND r.r = TRUE

combined with

SELECT DISTINCT r  FROM Rule r WHERE r.g IS NULL AND r.bean = :bean AND r.r = TRUE

I will test this out and if I am correct then a bug fix release will appear ASAP.

[Steve Fisher]

Having spent several hours trying to debug this I noticed that the system where the error was reported was ICAT 4.2.0 rather than ICAT 4.2.1. Version 4.2.0 does indeed have this bug which is fixed in 4.2.1. So Christian, please confirm that you are using 4.2.1

Steve

[c.felder.m.sc]

Hello Steve,

I can confirm that I am using version 4.2.1.

In [61]: print c.service.getApiVersion()

4.2.1

[glassfish@apps v4.2.1]$ svn log -l 1

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

r2121 | dr.s.m...@gmail.com | 2012-09-11 12:30:22 +0200 (Tue, 11 Sep 2012) | 1 line

Best

Christian