We're Hiring!

OMERO database question

General and open developer discussion about using OMERO APIs from C++, Java, Python, Matlab and more! Please new questions at https://forum.image.sc/tags/omero
Please note:
Historical discussions about OMERO. Please look for and ask new questions at https://forum.image.sc/tags/omero

If you are having trouble with custom code, please provide a link to a public repository, ideally GitHub.

OMERO database question

Postby robbertvdzon » Wed Dec 07, 2011 11:50 am

Hi all!

We like to query the omero database directly using SQL and use that to find all the files in the /Files, /Thumbnails and /Pixels that belong to a screen or project.

Currently, for screens we use the following mechanism:
First we find all the WellSample records that belong to the screen.
The ID of this WellSample record is the same is the filename in the /Files folder.
For each WellSample, we find the Pixel record and the ID of the Pixel record if the same as the filename in the /Pixel folder.
For each Pixel, we find the Thumbnail record and the ID of this Thumbnail record is the same as the filename in the /Thumbnail folder.

For projects, we first find the datasetimagelink records that belong to the project.
The datasetimagelink.child is the same as the filename in the /Files folder.
For each datasetimagelink record, I find the Pixels and Thumbnail record and use their ID for the files in the /Pixels and /Thumbnails folder.

We had occasions however that we assumed that there shoud file files in the /Files folder (because there were WellSample records) but these files were not there.
We were noted not to use the WellSample or DatasetImageLink record to files the files in the /Files folder, but use the 'originalfile' table.
Can anybody confirm that?


I like to know if I can add the following to the query:
pixelsoriginalfilemap.child = originalfile.id
pixelsoriginalfilemap.parent = pixels.id

Then use the originalfile.id as the file in the /File folder.

Can someone confirm that this is the correct way of finding the files in the /Files, /Pixels and /Thumbnails folder?

Thanks,

Robbert vd Zon

note: In reality we use a more optimized queries
robbertvdzon
 
Posts: 3
Joined: Wed Dec 07, 2011 11:25 am

Re: OMERO database question

Postby jmoore » Wed Dec 07, 2011 1:32 pm

robbertvdzon wrote:Hi all!


Hi Robbert.

...

We had occasions however that we assumed that there shoud file files in the /Files folder (because there were WellSample records) but these files were not there.
We were noted not to use the WellSample or DatasetImageLink record to files the files in the /Files folder, but use the 'originalfile' table.
Can anybody confirm that?


Correct. The ids in the originalfile table match those under /Files, just as those from pixels match /Pixels, etc. Any other match-up is coincidental.

I like to know if I can add the following to the query:
pixelsoriginalfilemap.child = originalfile.id
pixelsoriginalfilemap.parent = pixels.id

Then use the originalfile.id as the file in the /File folder.


The other way around. pixelsoriginalfilemap.parent points to originalfile.id, and .child points to pixels.id. Otherwise, yes, this is how to join pixels to originalfiles in the case where the original files were archived along with the image import.

Cheers,
~Josh
User avatar
jmoore
Site Admin
 
Posts: 1591
Joined: Fri May 22, 2009 1:29 pm
Location: Germany

Re: OMERO database question

Postby robbertvdzon » Wed Dec 07, 2011 1:56 pm

Thanks Josh!

Robbert
robbertvdzon
 
Posts: 3
Joined: Wed Dec 07, 2011 11:25 am

Re: OMERO database question

Postby robbertvdzon » Wed Dec 07, 2011 2:21 pm

While I am at this, perhaps you or someone can also verify the query to pixel records for a screen or project.
This is what I use now.

For screens:

select
pixels.id
from
pixels,screenplatelink,plate,well,wellsample
where
pixels.image=wellsample.image
and
wellsample.well=well.id
and
well.plate=plate.id
and
screenplatelink.child=plate.id
and
screenplatelink.parent=(screenID)

and I combine that result with the following query:

select
pixels.id
from
pixels,screenacquisition,screenacquisitionwellsamplelink,wellsample
where
pixels.image=wellsample.image
and
wellsample.id=screenacquisitionwellsamplelink.child
and
screenacquisitionwellsamplelink.parent=screenacquisition.id
and
screenacquisition.screen=(screenID)

For projects, I use the following query:

select
pixels.id
from
pixels,projectdatasetlink,datasetimagelink
where
pixels.image=datasetimagelink.child
and
projectdatasetlink.child=datasetimagelink.parent
and
projectdatasetlink.parent=(projectID)

Thanks,

Robbert
robbertvdzon
 
Posts: 3
Joined: Wed Dec 07, 2011 11:25 am

Re: OMERO database question

Postby cxallan » Thu Dec 15, 2011 10:03 am

I don't see a problem with any of those three queries. The only couple things to note are that ScreenAcquisition is completely optional in the schema and that it has been replaced by PlateAcquisition from 4.2.0 onwards. For reference: http://www.openmicroscopy.org/site/supp ... ll-2010-04
cxallan
Site Admin
 
Posts: 509
Joined: Fri May 01, 2009 8:07 am


Return to Developer Discussion

Who is online

Users browsing this forum: Google [Bot] and 1 guest