OMERO database question

[robbertvdzon]

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

[jmoore]

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

[robbertvdzon]

Thanks Josh!

Robbert

[robbertvdzon]

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

[cxallan]

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