Remove all unlinked non-shareable annotations

[jamoore]

As a carry over from the delete rework (#2615), some objects may have been left dangling (#2932). These should be removed on the next database upgrade.

[jmoore]

Referencing ticket #2568 has changed sprint.

[jmoore]

Referencing ticket #4891 has changed sprint.

[mtbcarroll]

Which annotations to delete may be informed by the outcome of ​https://trello.com/c/T3UX9YZe.

[mtbcarroll]

Note that integration tests like AnnotationMoveTest.testMoveImageAnnotatedByUsersOneNotInDestinationGroupDestinationGroupRWRAtoRWRA seem to positively require that non-sharable annotations are left dangling.

[mtbcarroll]

If unlinking a non-sharable annotation via Delete2 can automatically delete it (e.g., a rating) then OMERO.web has trouble deleting some annotations -- see ​https://github.com/openmicroscopy/openmicroscopy/blob/8cf95413/components/blitz/resources/ome/services/graph-rules/blitz-delete-rules.xml#L191 -- perhaps that needs fixing first or more objects may become dangled.

[jamoore]

Is 5.2 still appropriate here?

[mtbcarroll]

Yes if changes to OMERO.web and integration tests are also made for 5.2.

[jamoore]

Splitting due to milestone decoupling.

[mtbcarroll]

Note from comments above that if this is to be done in any useful way for 5.2.0 then web changes and integration test changes are probably needed.

[mtbcarroll]

So, need confirmation that it's okay to change ​https://trac.openmicroscopy.org/ome/ticket/2999#comment:5. Also, I could make the change in the server, and then web people can deal with ​https://trac.openmicroscopy.org/ome/ticket/2999#comment:6?

[wmoore]

Mark, once this ticket is done, if web needs to subsequently change, just let me know what to fix and re-assign ticket to me - Thanks.

[mtbcarroll]

Thank you! Just realized that even this web change would probably be technically breaking, so will do this once we branch dev_5_2.

[jburel]

Milestone OMERO-5.2.1 deleted

[jburel]

Milestone OMERO-5.2.2 deleted

[mtbcarroll]

Unlinked non-shareable annotations can be counted by,

SELECT discriminator, COUNT(*) FROM annotation WHERE discriminator IN ('/basic/bool/', '/basic/num/double/', '/basic/num/long/', '/basic/term/', '/basic/time/', '/basic/text/comment/') AND id NOT IN (SELECT child FROM annotationannotationlink) AND id NOT IN (SELECT child FROM channelannotationlink) AND id NOT IN (SELECT child FROM datasetannotationlink) AND id NOT IN (SELECT child FROM detectorannotationlink) AND id NOT IN (SELECT child FROM dichroicannotationlink) AND id NOT IN (SELECT child FROM experimenterannotationlink) AND id NOT IN (SELECT child FROM experimentergroupannotationlink) AND id NOT IN (SELECT child FROM filesetannotationlink) AND id NOT IN (SELECT child FROM filterannotationlink) AND id NOT IN (SELECT child FROM imageannotationlink) AND id NOT IN (SELECT child FROM instrumentannotationlink) AND id NOT IN (SELECT child FROM lightpathannotationlink) AND id NOT IN (SELECT child FROM lightsourceannotationlink) AND id NOT IN (SELECT child FROM namespaceannotationlink) AND id NOT IN (SELECT child FROM nodeannotationlink) AND id NOT IN (SELECT child FROM objectiveannotationlink) AND id NOT IN (SELECT child FROM originalfileannotationlink) AND id NOT IN (SELECT child FROM planeinfoannotationlink) AND id NOT IN (SELECT child FROM plateacquisitionannotationlink) AND id NOT IN (SELECT child FROM plateannotationlink) AND id NOT IN (SELECT child FROM projectannotationlink) AND id NOT IN (SELECT child FROM reagentannotationlink) AND id NOT IN (SELECT child FROM roiannotationlink) AND id NOT IN (SELECT child FROM screenannotationlink) AND id NOT IN (SELECT child FROM sessionannotationlink) AND id NOT IN (SELECT child FROM shapeannotationlink) AND id NOT IN (SELECT child FROM wellannotationlink) GROUP BY discriminator;

[mtbcarroll]

jamoore is going to check some production databases next week to judge the character and prevalence of such unlinked non-shareable annotations. The result of that determines the next steps.

[jamoore]

Referencing ticket #9165 has changed sprint.

[jamoore]

Referencing ticket #9165 has changed sprint.

[mtbcarroll]

I tried deleting a comment and saw separate delete calls for ImageAnnotationLink, CommentAnnotation (dry run), CommentAnnotation. With rating I just got ImageAnnotationLink then LongAnnotation. The rules adjustment should make it such that deletion of the ImageAnnotationLink alone suffices.

[mtbcarroll]

Opened ​https://github.com/openmicroscopy/openmicroscopy/pull/4585 to try to prevent further unlinked non-shareable annotations from being unintentionally created.

[mtbcarroll]

Fixed by ​https://github.com/openmicroscopy/openmicroscopy/pull/4608. Feel free to reopen and reassign if ​https://trac.openmicroscopy.org/ome/ticket/2999#comment:18 still requires work.

[jamoore]

I'd assume something should at least look at that for testing.

[mtbcarroll]

See discussion on ​https://trello.com/c/QrfLtInf/68-should-we-really-be-creating-the-value-foo-in-our-user-s-production-databases regarding protecting some annotations.

[sbesson]

I don't think this belongs to the BF-5.2.0 milestone. Do we have a placeholder to come back to this as part of the OMERO 5.3.0 testing Jean-Marie?

[mtbcarroll]

Already quite outstandingly tested by pwalczysko in ​https://github.com/openmicroscopy/openmicroscopy/pull/4732#issuecomment-229388429.

[sbesson]

Closing in favor of a checklist item on the OMERO 5.3.0 QA card