Open Microscopy Environment

Code: Select all

query = "select fileann from FileAnnotation fileann join fileann.file as f where f.name = :filename"


If you need to retrieve other details of the file itself, use "fetch"

Code: Select all

query = "select fileann from FileAnnotation fileann join fetch fileann.file as f where f.name = :filename"


Ivan,

can you show your complete example along with what error message you are receiving? In general, to retrieve a count you should use:

Code: Select all

select count(f) from FileAnnotation f where f.file.name = 'example.jpg'


This returns a list of lists of RTypes. So to unwrap the result, you'll need:

Code: Select all

rv = iquery.projection(...)
count = rv[0][0].val


Cheers,
~Josh

For backup and automated testing purposes I'm trying to retrieve all files from a specific user from the OMERO data dir.

Copying the managedrepository files has been no problem. Now, I'm trying to copy the pyramid files from Pixels/. I've been trying this HQL query:

Code: Select all

omero hql -q --all --style plain --limit -1 --ids-only 'from Pixels where details.owner = 2'

But this returns for me many IDs which do not have an associated Pixel file. While I could ignore this, I'm wondering why, and whether I can improve the query.

Regarding the "why", since OMERO 5.0 a Pixels file is not usually generated for imported images. http://ome.github.io/presentations/2013 ... -paris/#/3 describes a "big images without subresolutions" caveat: basically, one will have Pixels files for OMERO 4 images and for large images of just certain formats such as JPEG. Additionally, I believe that images generated by scripts may have a Pixels file. Wherever possible, the OMERO server uses the original image files, and Bio-Formats on the fly for rendering, in trying to prevent users from having to duplicate their data anywhere.

The "whether" is also a good question and I do hope that somebody can help with that one.

Cheers,
Mark

There is a shortage of ideas from others about the "whether" I'm afraid! From the client, one's principal clues are image dimensions and format and if there is an associated fileset. In HQL, to find the images that do not have a fileset, thus probably do have a file under the Pixels directory,

Code: Select all

SELECT id FROM Image WHERE fileset IS NULL


For images with filesets, for which only large enough images in certain formats will have a file under the Pixels directory, there will generally also be a Bio-Formats reader already noted for them, permitting in SQL,

Code: Select all

SELECT i.id, p.sizex, p.sizey, vi.value FROM image i, pixels p, fileset f, filesetjoblink fjl, uploadjob uj, uploadjob_versioninfo vi WHERE i.id = p.image AND i.fileset = f.id AND fjl.parent = f.id AND fjl.child = uj.job_id AND uj.job_id = vi.uploadjob_id AND vi.name = 'bioformats.reader'


Unfortunately, what that latter query would be in HQL, I've no idea; since the latest changes for 5.1 I do not yet know how to query the new map values' key-value pairs from HQL in this kind of situation. However, it has to be possible, even if it is best split up into multiple queries.

If you have further questions or ideas on this issue please do feel free to bounce them off us. I will make a note that we should look again at the Pixels directory issue, especially for your use case, and I'll check again if someone else can add more to this thread here.

Cheers,
Mark

I realized that one workaround exists from OMERO 5.1 onward: using the disk usage counter to check what file storage is directly associated with an image's Pixels object. This would include pyramids, which also appear in the Pixels/ directory, but would not include empty files. Filling in the login details appropriately, a simple script might be,

Code: Select all

from omero.callbacks import CmdCallbackI
from omero.cmd import DiskUsage, DoAll
from omero.gateway import BlitzGateway

conn = BlitzGateway(username, password, host=server_host, port=server_port)
conn.connect()

query = 'SELECT id FROM Image ORDER BY id'
ids = [result[0].val for result in
       conn.getQueryService().projection(query, None)]

do_all = DoAll()
do_all.requests = [DiskUsage(objects={'Image': [id]}) for id in ids]

prx = conn.c.sf.submit(do_all)

have_pixels = []

for response in CmdCallbackI(conn.c, prx).loop(500, 500).responses:
    have_pixels.append(any('Pixels' in counts
                           for counts in response.fileCountByReferer.values()))

conn._closeSession()

for report in zip(ids, have_pixels):
    print "Image id #%s has pixels? %s" % report


I hope that helps.

Cheers,
Mark

Hi Mark,

Thanks for your detailed help. It's interesting to know that for many formats OMERO will no longer build pyramids, I don't realize that yet. Unfortunately, we are working with true JPEG files, with pixel sizes which have a mix between being pyramided or not.

I will keep your last solution in mind, and revisit this when version 5.1 is deployed. For now, I will simply ignore non existent files.

Thanks,

Paul

I should follow up to mention that http://www.openmicroscopy.org/site/supp ... nd-queries now provides some HQL map annotation examples.

Cheers,
Mark

Hi,

still learning SQL and HQL so a super basic question... how do I join two tables in HQL?

I've rebuilt a psql database from a dump so I can get used to structure of the tables. Simple queries like this work in sql:

Code: Select all

SELECT i.name,e.firstName,e.lastName  FROM Image i  LEFT OUTER JOIN Experimenter e ON (e.id=i.owner_id)


but I can't get the HQL query to work. There some stack overflow questions that indicate that his type of is possible in HQL and the there needs to be a relationship between the two table foreign and primary key:
https://stackoverflow.com/questions/18545390/how-to-perform-left-join-in-hibernate-query-language

Any pointers would be greatly appreciated,

Chris

OMERO already contains some magic to return the details of the user who created an object, e.g.

Code: Select all

omero hql 'SELECT name, details.owner.firstName, details.owner.lastName FROM Image'