Demo33 repopulate

gistfile1.txt

OMERO_DIST='/home/omero/workspace/OMERO-server/OMERO.server'
IDR_METADATA='/tmp/idr-metadata'


# idr0008-rohn-actinome/screenA
# http://10.0.51.135/web/webclient/?show=screen-154
# delete gene annotations
$OMERO_DIST/bin/omero metadata populate --batch 10 --wait 600 --context deletemap --localcfg '{"ns":"openmicroscopy.org/mapr/gene"}' Screen:154 --report
# generate new table
$OMERO_DIST/bin/omero delete Annotation:6777027 --report
$OMERO_DIST/bin/omero metadata populate --file $IDR_METADATA/idr0008-rohn-actinome/screenA/idr0008-screenA-annotation.csv Screen:154
# populate new gene annotations
$OMERO_DIST/bin/omero metadata populate --context bulkmap --cfg $IDR_METADATA/idr0008-rohn-actinome/screenA/idr0008-screenA-bulkmap-config.yml Screen:154 --localcfg '{"ns":"openmicroscopy.org/mapr/gene"}' --report


# DELETE orphaned
# check how many orphaned
select distinct a.*, mv.* from annotation as a
inner join  annotation_mapvalue as mv on (mv.annotation_id = a.id)
left outer join imageannotationlink as ial on (ial.child = a.id)
left outer join wellannotationlink as wal on (wal.child = a.id)
where ial.child is null and wal.child  is null
and a.ns like 'openmicroscopy.org%'
and a.ns not in ('openmicroscopy.org/omero/client/mapAnnotation', 'openmicroscopy.org/omero/bulk_annotations.map')

$OMERO_DIST/bin/omero delete Annotation:6777389
$OMERO_DIST/bin/omero delete Annotation:6784588
$OMERO_DIST/bin/omero delete Annotation:6787011
$OMERO_DIST/bin/omero delete Annotation:6796010
$OMERO_DIST/bin/omero delete Annotation:6805811
  
# review if the following urls shows only one map in metadata pane
http://idr-demo.openmicroscopy.org/mapr/gene/?value=FBgn0026361
http://idr-demo.openmicroscopy.org/mapr/gene/?value=FBgn0014029
http://idr-demo.openmicroscopy.org/mapr/gene/?value=FBgn0259923
http://idr-demo.openmicroscopy.org/mapr/gene/?value=FBgn0000427
http://idr-demo.openmicroscopy.org/mapr/gene/?value=FBgn0011710


-------------------------------------------------------------------------------------------------------------------

# idr0012-fuchs-cellmorph/screenA
# http://10.0.51.135/web/webclient/?show=screen-1202
# delete organism annotations (there shouldn't be any anyway)
$OMERO_DIST/bin/omero metadata populate --batch 10 --wait 600 --context deletemap --localcfg '{"ns":"openmicroscopy.org/mapr/organism"}' Screen:1202 --report
# populate new organism annotations
$OMERO_DIST/bin/omero metadata populate --context bulkmap --cfg $IDR_METADATA/idr0012-fuchs-cellmorph/screenA/idr0012-screenA-bulkmap-config.yml Screen:1202 --localcfg '{"ns":"openmicroscopy.org/mapr/organism"}' --report



# idr0019-sero-nfkappab/screenA
# http://10.0.51.135/web/webclient/?show=screen-1203
# delete compound annotations (there shouldn't be any anyway)
$OMERO_DIST/bin/omero metadata populate --batch 10 --wait 600 --context deletemap --localcfg '{"ns":"openmicroscopy.org/mapr/compound"}' Screen:1203 --report
# generate new table
$OMERO_DIST/bin/omero delete Annotation:7306826
$OMERO_DIST/bin/omero metadata populate --file $IDR_METADATA/idr0019-sero-nfkappab/screenA/idr0019-screenA-annotation.csv Screen:1203
# populate new compound annotations
$OMERO_DIST/bin/omero metadata populate --context bulkmap --cfg $IDR_METADATA/idr0019-sero-nfkappab/screenA/idr0019-screenA-bulkmap-config.yml Screen:1203 --localcfg '{"ns":"openmicroscopy.org/mapr/compound"}' --report



# idr0028-pascualvargas-rhogtpases/screenA
# http://10.0.51.135/web/webclient/?show=screen-1651
# delete all map annotations 
$OMERO_DIST/bin/omero metadata populate --batch 10 --wait 600 --context deletemap --cfg $IDR_METADATA/idr0028-pascualvargas-rhogtpases/screenA/idr0028-screenA-bulkmap-config.yml Screen:1651 --report
# generate new table
$ $OMERO_DIST/bin/omero delete Annotation:7315507 
$OMERO_DIST/bin/omero metadata populate --file $IDR_METADATA/idr0028-pascualvargas-rhogtpases/screenA/idr0028-screenA-annotation.csv Screen:1651
# populate new map annotations
$OMERO_DIST/bin/omero -d1 metadata populate --context bulkmap --cfg $IDR_METADATA/idr0028-pascualvargas-rhogtpases/screenA/idr0028-screenA-bulkmap-config.yml Screen:1651 --report



# idr0028-pascualvargas-rhogtpases/screenC
# http://10.0.51.135/web/webclient/?show=screen-1653
# delete all map annotations 
$OMERO_DIST/bin/omero metadata populate --batch 10 --wait 600 --context deletemap --cfg $IDR_METADATA/idr0028-pascualvargas-rhogtpases/screenC/idr0028-screenC-bulkmap-config.yml Screen:1653 --report
# generate new table
$OMERO_DIST/bin/omero delete Annotation:7320855
$OMERO_DIST/bin/omero metadata populate --file $IDR_METADATA/idr0028-pascualvargas-rhogtpases/screenC/idr0028-screenC-annotation.csv Screen:1653
# populate new map annotations
$OMERO_DIST/bin/omero metadata populate --context bulkmap --cfg $IDR_METADATA/idr0028-pascualvargas-rhogtpases/screenC/idr0028-screenC-bulkmap-config.yml Screen:1653 --report



### OPTIONAL
# idr0003-breker-plasticity/screenA
# http://10.0.51.135/web/webclient/?show=screen-51
$OMERO_DIST/bin/omero metadata populate --batch 10 --wait 600 --context deletemap --localcfg '{"ns":"openmicroscopy.org/mapr/phenotype"}' Screen:51 --report
# populate new phenotype annotations
$OMERO_DIST/bin/omero metadata populate --context bulkmap --cfg $IDR_METADATA/idr0003-breker-plasticity/screenA/idr0003-screenA-bulkmap-config.yml Screen:51 --localcfg '{"ns":"openmicroscopy.org/mapr/phenotype"}' --report



#### SQL

# count how many annotations and links by ns
select count(distinct (a.id)), count(distinct (ial.id)), a.ns from annotation as a
inner join annotation_mapvalue as mv on (mv.annotation_id = a.id)
inner join imageannotationlink as ial on (ial.child = a.id)
inner join image as i on (ial.parent=i.id)
inner join wellsample as ws on (ws.image=i.id) 
inner join well as w on (ws.well=w.id)
inner join plate as p on (w.plate=p.id)
inner join screenplatelink as spl on (spl.child=p.id)
where spl.parent = 154
group by a.ns 

# check events for image
select distinct el.entityid, el.entitytype, el.action, el.entitytype from eventlog  as el
inner join event as ev on (el.event=ev.id)
inner join image as i on (el.entityid=i.id)
inner join wellsample as ws on (ws.image=i.id) 
inner join well as w on (ws.well=w.id)
inner join plate as p on (w.plate=p.id)
inner join screenplatelink as spl on (spl.child=p.id)
where ev."time" > '2017-02-06 00:00:00.000'
and el.entitytype = 'ome.model.core.Image'
and spl.parent = 154
order by 2;

# check events for well
select distinct el.entityid, el.entitytype, el.action, el.entitytype from eventlog  as el
inner join event as ev on (el.event=ev.id)
inner join well as w on (el.entityid=w.id)
inner join plate as p on (w.plate=p.id)
inner join screenplatelink as spl on (spl.child=p.id)
where ev."time" > '2017-02-06 00:00:00.000'
and el.entitytype = 'ome.model.screen.Well'
and spl.parent = 154
order by 2


# check events
select count(distinct el.entityid), count(distinct el.entitytype), el.action, el.entitytype from eventlog  as el
inner join event as ev on (el.event=ev.id)
where ev."time" > '2017-02-06 00:00:00.000'
and (el.entitytype = 'ome.model.screen.Well' or el.entitytype = 'ome.model.core.Image')
group by el.action, el.entitytype
order by 2




# check if all wells belong to the container
select distinct el.entityid, el.entitytype, el.action, el.entitytype from eventlog  as el
inner join event as ev on (el.event=ev.id)
inner join well as w on (el.entityid=w.id)
inner join plate as p on (w.plate=p.id)
inner join screenplatelink as spl on (spl.child=p.id)
where ev."time" > '2017-02-06 00:00:00.000'
and el.entitytype = 'ome.model.screen.Well'
and spl.parent = 154
and el.entityid in (
select w.id from well
inner join plate as p on (w.plate=p.id)
inner join screenplatelink as spl on (spl.child=p.id)
where spl.parent = 154
)
# check if all images belong to the container
select distinct el.entityid, el.entitytype, el.action, el.entitytype from eventlog  as el
inner join event as ev on (el.event=ev.id)
inner join image as i on (el.entityid=i.id)
inner join wellsample as ws on (ws.image=i.id) 
inner join well as w on (ws.well=w.id)
inner join plate as p on (w.plate=p.id)
inner join screenplatelink as spl on (spl.child=p.id)
where ev."time" > '2017-02-06 00:00:00.000'
and el.entitytype = 'ome.model.core.Image'
and spl.parent = 154
and el.entityid in (
select i.id from image
inner join wellsample as ws on (ws.image=i.id) 
inner join well as w on (ws.well=w.id)
inner join plate as p on (w.plate=p.id)
inner join screenplatelink as spl on (spl.child=p.id)
where spl.parent = 154
)