Open Microscopy Environment

I am trying to move an image between two groups in the same user in OMERO (I have one group set as private and the other as collaborative Read Only).

I have identified these following sql calls that need to be made (assuming you have the group ids and permission ids that the image id needs to be moved to) but I think I've missed something. The first time I did this it worked, but when I tried this a second time with a new image it didn't work. I am making these calls from the postgresql backend. Could you suggest what I am forgetting? I wasn't sure if the id in logicalchannel corresponded to an image or something else.

UPDATE annotation SET permissions=-103 WHERE id=51;
UPDATE annotation SET group_id=153 WHERE id=51;
UPDATE channel SET permissions=-103 WHERE id=51;
UPDATE channel SET group_id=153 WHERE id=51;
UPDATE channelbinding SET permissions=-103 WHERE id=51;
UPDATE channelbinding SET group_id=153 WHERE id=51;
UPDATE datasetimagelink SET permissions=-103 WHERE child=51;
UPDATE datasetimagelink SET group_id=153 WHERE child=51;
UPDATE image SET permissions=-103 WHERE id=51;
UPDATE image SET group_id=153 WHERE id=51;
UPDATE imageannotationlink SET permissions=-103 WHERE id=51;
UPDATE imageannotationlink SET group_id=153 WHERE id=51;
UPDATE pixels SET permissions=-103 WHERE id=51;
UPDATE pixels SET group_id=153 WHERE id=51;
UPDATE planeinfo SET permissions=-103 WHERE id=51;
UPDATE planeinfo SET group_id=153 WHERE id=51;
UPDATE quantumdef SET permissions=-103 WHERE id=51;
UPDATE quantumdef SET group_id=153 WHERE id=51;
UPDATE renderingdef SET permissions=-103 WHERE id=51;
UPDATE renderingdef SET group_id=153 WHERE id=51;
UPDATE statsinfo SET permissions=-103 WHERE id=51;
UPDATE statsinfo SET group_id=153 WHERE id=51;
UPDATE thumbnail SET permissions=-103 WHERE id=51;
UPDATE thumbnail SET group_id=153 WHERE id=51;

I have identified these following sql calls that need to be made (assuming you have the group ids and permission ids that the image id needs to be moved to) but I think I've missed something. The first time I did this it worked, but when I tried this a second time with a new image it didn't work. I am making these calls from the postgresql backend. Could you suggest what I am forgetting?

Could you show us what the error is when running it the second time? Are the SQL statements you provided above for the first or the second image?

I wasn't sure if the id in logicalchannel corresponded to an image or something else.

A logical channel is pointed to by a channel, so you need something like:

Code: Select all

UPDATE logicalchannel SET permissions=-103 WHERE id in (select logicalchannel from channel where id = ...);
UPDATE logicalchannel SET group_id=153 WHERE id in (select logicalchannel from channel where id = ...);


UPDATE annotation SET permissions=-103 WHERE id=51;
UPDATE annotation SET group_id=153 WHERE id=51;
UPDATE channel SET permissions=-103 WHERE id=51;
UPDATE channel SET group_id=153 WHERE id=51;
UPDATE channelbinding SET permissions=-103 WHERE id=51;
UPDATE channelbinding SET group_id=153 WHERE id=51;
UPDATE datasetimagelink SET permissions=-103 WHERE child=51;
UPDATE datasetimagelink SET group_id=153 WHERE child=51;
UPDATE image SET permissions=-103 WHERE id=51;
UPDATE image SET group_id=153 WHERE id=51;
UPDATE imageannotationlink SET permissions=-103 WHERE id=51;
UPDATE imageannotationlink SET group_id=153 WHERE id=51;
UPDATE pixels SET permissions=-103 WHERE id=51;
UPDATE pixels SET group_id=153 WHERE id=51;
UPDATE planeinfo SET permissions=-103 WHERE id=51;
UPDATE planeinfo SET group_id=153 WHERE id=51;
UPDATE quantumdef SET permissions=-103 WHERE id=51;
UPDATE quantumdef SET group_id=153 WHERE id=51;
UPDATE renderingdef SET permissions=-103 WHERE id=51;
UPDATE renderingdef SET group_id=153 WHERE id=51;
UPDATE statsinfo SET permissions=-103 WHERE id=51;
UPDATE statsinfo SET group_id=153 WHERE id=51;
UPDATE thumbnail SET permissions=-103 WHERE id=51;
UPDATE thumbnail SET group_id=153 WHERE id=51;

Is the "id" for all of these really 51?? Usually, these will vary on a per table basis. Something like the following is more of what you need:

Code: Select all

-- Assuming the image is id=51
UPDATE annotation SET permissions=-103 WHERE id in (SELECT child FROM imageannotationlink WHERE parent = 51);
UPDATE pixels SET permissions = -103 where image = 51;
UPDATE channel SET permissions=-103 WHERE pixels in (SELECT id FROM pixels where image = 51);
...


Note: be sure to backup your DB before playing with it like this. You could easily get your state where a user's or group's data will no longer load appropriately. We are working on a tool to do just this.

Cheers,
~Josh.

jmoore wrote:Could you show us what the error is when running it the second time? Are the SQL statements you provided above for the first or the second image?

The sql statements are for the second image. I lost the sql statements that I made to the first one (PEBMAC error). The image would disappear from the first group and not appear in the second group.

Manz wrote:Is the "id" for all of these really 51?? Usually, these will vary on a per table basis. Something like the following is more of what you need:

I wasn't sure if id always corresponded to image id, sometimes I noticed child did instead and sometimes I just guessed. Thank you for the additional sql queries, I will try them out today.

Would you please be able to verify that I have identified the image id correctly in the following sql calls and that I have not missed a dependant table somewhere? Or possibly point me in the direction of the table mappings. I was not too sure how to reference statsinfo or whether it was necessary to.

UPDATE annotation SET permissions=-103 WHERE id in (SELECT child FROM imageannotationlink WHERE parent = 201);
UPDATE annotation SET group_id=153 WHERE id in (SELECT child FROM imageannotationlink WHERE parent = 201);
UPDATE channel SET permissions=-103 WHERE pixels in (SELECT id FROM pixels where image = 201);
UPDATE channel SET group_id=153 WHERE pixels in (SELECT id FROM pixels where image = 201);
UPDATE channelbinding SET permissions=-103 WHERE renderingdef=201;
UPDATE channelbinding SET group_id=153 WHERE renderingdef=201;
UPDATE datasetimagelink SET permissions=-103 WHERE child=201;
UPDATE datasetimagelink SET group_id=153 WHERE child=201;
UPDATE datasetimagelink SET parent=101 WHERE child=201;
UPDATE image SET permissions=-103 WHERE id=201;
UPDATE image SET group_id=153 WHERE id=201;
UPDATE imageannotationlink SET permissions=-103 WHERE parent=201;
UPDATE imageannotationlink SET group_id=153 WHERE parent=201;
UPDATE pixels SET permissions=-103 WHERE image=201;
UPDATE pixels SET group_id=153 WHERE image=201;
UPDATE planeinfo SET permissions=-103 WHERE pixels=201;
UPDATE planeinfo SET group_id=153 WHERE pixels=201;
UPDATE quantumdef SET permissions=-103 WHERE id=201;
UPDATE quantumdef SET group_id=153 WHERE id=201;
UPDATE renderingdef SET permissions=-103 WHERE pixels in (SELECT id FROM pixels WHERE image=201);
UPDATE renderingdef SET group_id=153 WHERE pixels in (SELECT id FROM pixels WHERE image=201);
UPDATE thumbnail SET permissions=-103 WHERE id=201;
UPDATE thumbnail SET group_id=153 WHERE id=201;
UPDATE logicalchannel SET permissions=-103 WHERE id in (select logicalchannel from channel where id = 201);
UPDATE logicalchannel SET group_id=153 WHERE id in (select logicalchannel from channel where id = 201);

#UPDATE statsinfo SET permissions=-39 WHERE id=201;
#UPDATE statsinfo SET group_id=3 WHERE id=201;

Manz wrote:Would you please be able to verify that I have identified the image id correctly in the following sql calls and that I have not missed a dependant table somewhere? Or possibly point me in the direction of the table mappings. I was not too sure how to reference statsinfo or whether it was necessary to.

You're certainly making progress, but unfortunately many more of the SQL statements will need to be changed. For example, anything with "from channel where id = 201" will need to be modified to use the pixels and image tables. (StatsInfo is also referenced from Channel: "...from statsinfo where id in (select statsinfo from channel where pixels = (select id from pixels where image = 201)")

I'm worried that this may become a quite difficult investigation, where we're not even sure of the correctness of your data after you've run it. We discussed the problem internally and have a few alternatives.

• We are planning (#3532) to include a chgrp and chown functionality in 4.4. If possible, waiting for 4.4.0 or even a pre-release version would certainly be the safest bet.
• If you can't wait, the branch that we're working on (3532-chgrp branch) is available for testing. Since the SQL you're writing by hand is being generated, it's more likely to be correct, but there may still be bugs.
• Another though perhaps less desirable option is to export the data from the one group and re-import it into another. Depending on just what you are trying to achieve this may suffice until the chgrp works becomes ready.

Let us know what you think about these options, and how we can help you to move forward.
Best,
~Josh.

Dear Josh

Thank you for the time and effort you have put in so far. We have a deadline coming up soon so I need a solution as soon as possible. The export and import option may actually be the best for what we are looking for, at least to tide us over until the next release. Is there functions set up for export and importing?

To explain a little more on what we are doing, we are adding the ability to store metadata about data sets which then can be harvested via sql calls. Using webtest, I've created a catalogue with this extra metadata.

Currently I am not using the mapped table django functionality or api to achieve my sql calls, I have instead written a python script using pyscopg2 that is called at a button that I have included on webclient. This was done as I needed to include a lot of dataset metadata and to do that, we've just created an extra schema in the database to query.

Hi Amanda,

Manz wrote:Thank you for the time and effort you have put in so far. We have a deadline coming up soon so I need a solution as soon as possible.

Gladly. Hopefully we can find something that can get you safely through your deadline.

The export and import option may actually be the best for what we are looking for, at least to tide us over until the next release. Is there functions set up for export and importing?

Where would you like to use the function? From the command-line you could do:

Code: Select all

bin/omero export -f Image1.ome.tif Image:1
bin/omero sessions group "MyOtherGroup"
bin/omero import Image1.ome.tif


To explain a little more on what we are doing, we are adding the ability to store metadata about data sets which then can be harvested via sql calls. Using webtest, I've created a catalogue with this extra metadata.

Currently I am not using the mapped table django functionality or api to achieve my sql calls, I have instead written a python script using pyscopg2 that is called at a button that I have included on webclient. This was done as I needed to include a lot of dataset metadata and to do that, we've just created an extra schema in the database to query.

Hmmm....this sounds fairly far out of the bounds of OMERO. Is your code available anywhere? Maybe there's a function/call somewhere that would make things easier. But, now's probably not the time to be changing things if it's working for you.

Best of luck,
~Josh.

That export/import functionality should tide us over the next deadline and into when we have more time. Does it keep any metadata that has been put into OMERO previously or is it lost? Even if it is lost, I should be able to work with that.

We are still in very early stages so no code deposit as of yet but I can send you the python scripts I have so far if that helps.

Manz wrote:That export/import functionality should tide us over the next deadline and into when we have more time. Does it keep any metadata that has been put into OMERO previously or is it lost? Even if it is lost, I should be able to work with that.

It keeps some of the metadata, but some is lost. It will depend on how you've attached it.

We are still in very early stages so no code deposit as of yet but I can send you the python scripts I have so far if that helps.

That'd be great!

Thanks,
~Josh.

Where would you like to use the function? From the command-line you could do:

Code: Select all

bin/omero export -f Image1.ome.tif Image:1
bin/omero sessions group "MyOtherGroup"
bin/omero import Image1.ome.tif


So I've started to play with this, just using a command line to test out the code before scripting it. However, I'm coming across the problem that it needs a user/password and I was hoping to automate this in a script. I've worked out how to log in using sessions, but is it possible just to grab a session out of the sessions table in postgres instead?