def stat_screens(query): tb = TableBuilder("Screen") tb.cols(["ID", "Plates", "Wells", "Images", "Planes", "Bytes"]) plate_count = 0 well_count = 0 image_count = 0 plane_count = 0 byte_count = 0 for study, screens in sorted(studies().items()): for screen, plates_expected in screens.items(): params = ParametersI() params.addString("screen", screen) rv = unwrap(query.projection(( "select s.id, count(distinct p.id), " " count(distinct w.id), count(distinct i.id)," " sum(cast(pix.sizeZ as long) * pix.sizeT * pix.sizeC), " " sum(cast(pix.sizeZ as long) * pix.sizeT * pix.sizeC * " " pix.sizeX * pix.sizeY * 2) " "from Screen s " "left outer join s.plateLinks spl " "left outer join spl.child as p " "left outer join p.wells as w " "left outer join w.wellSamples as ws " "left outer join ws.image as i " "left outer join i.pixels as pix " "where s.name = :screen " "group by s.id"), params)) if not rv: tb.row(screen, "MISSING", "", "", "", "", "") else: for x in rv: plate_id, plates, wells, images, planes, bytes = x plate_count += plates well_count += wells image_count += images if planes: plane_count += planes if bytes: byte_count += bytes else: bytes = 0 if plates != len(plates_expected): plates = "%s of %s" % (plates, len(plates_expected)) tb.row(screen, plate_id, plates, wells, images, planes, filesizeformat(bytes)) tb.row("Total", "", plate_count, well_count, image_count, plane_count, filesizeformat(byte_count)) print str(tb.build())
def list_microscopes(request, conn=None, **kwargs): params = ParametersI() params.addString('ns', wrap(JSON_FILEANN_NS)) # q = """select new map(obj.id as id, # obj.description as desc, # o.firstName as firstName, # o.lastName as lastName, # e.time as time, # f.name as name, # obj as obj_details_permissions) # from FileAnnotation obj # join obj.details.owner as o # join obj.details.creationEvent as e # join obj.file.details as p # join obj.file as f where obj.ns=:ns""" q = """select obj from FileAnnotation obj join obj.details.owner as o join obj.details.creationEvent as e join obj.file.details as p join obj.file as f where obj.ns=:ns""" qs = conn.getQueryService() # file_anns = qs.projection(q, params, conn.SERVICE_OPTS) file_annotations = qs.findAllByQuery(q, params, conn.SERVICE_OPTS) rsp = [] for file_ann in file_annotations: print('fa', file_ann) fa_wrapper = FileAnnotationWrapper(conn, file_ann) file_wrapper = fa_wrapper.getFile() print('file_wrapper', file_wrapper) file_data = b"".join(list(file_wrapper.getFileInChunks())) print('file_data', file_data) json_data = json.loads(file_data.decode("utf-8")) # date = datetime.fromtimestamp(unwrap(fa['time'])/1000) # first_name = unwrap(file_ann) # last_name = unwrap(fa['lastName']) fig_file = { 'id': file_ann.id.val, # 'name': unwrap(fa['name']), # 'description': unwrap(fa['desc']), # 'ownerFullName': "%s %s" % (first_name, last_name), # 'creationDate': time.mktime(date.timetuple()), # 'canEdit': fa['obj_details_permissions'].get('canEdit'), 'microscope': json_data } rsp.append(fig_file) return JsonResponse({'data': rsp})
def stat_screens(query): tb = TableBuilder("Container") tb.cols(["ID", "Set", "Wells", "Images", "Planes", "Bytes"]) plate_count = 0 well_count = 0 image_count = 0 plane_count = 0 byte_count = 0 for study, containers in sorted(studies().items()): for container, set_expected in sorted(containers.items()): params = ParametersI() params.addString("container", container) if "Plate" in set_expected: expected = set_expected["Plate"] rv = unwrap(query.projection(SPW_QUERY, params)) elif "Dataset" in set_expected: expected = set_expected["Dataset"] rv = unwrap(query.projection(PDI_QUERY, params)) else: raise Exception("unknown: %s" % set_expected.keys()) if not rv: tb.row(container, "MISSING", "", "", "", "", "") else: for x in rv: plate_id, plates, wells, images, planes, bytes = x plate_count += plates well_count += wells image_count += images if planes: plane_count += planes if bytes: byte_count += bytes else: bytes = 0 if plates != len(expected): plates = "%s of %s" % (plates, len(expected)) tb.row(container, plate_id, plates, wells, images, planes, filesizeformat(bytes)) tb.row("Total", "", plate_count, well_count, image_count, plane_count, filesizeformat(byte_count)) print str(tb.build())
def testClassType(self): uuid = self.uuid() created = [] for Ann in (CommentAnnotationI, TagAnnotationI): ann = Ann() ann.setNs(rstring(uuid)) ann = self.update.saveAndReturnObject(ann) created.append(ann) query_string = """ select type(a.class) from Annotation a where a.ns = :uuid """ params = ParametersI() params.addString("uuid", uuid) rv = self.query.projection(query_string, params) rv = [x[0] for x in unwrap(rv)] assert len(rv) == 2 assert "ome.model.annotations.CommentAnnotation" in rv assert "ome.model.annotations.TagAnnotation" in rv
def annotation_ids_by_field(conn, value="CMPO_0000077", key="Phenotype Term Accession", ns="openmicroscopy.org/omero/bulk_annotations"): """ Return a list of IDs for map annotations with the given namespace that have a key=value pair matching the given parameters. """ from omero.rtypes import unwrap from omero.sys import ParametersI params = ParametersI() params.addString("value", value) params.addString("key", key) params.addString("ns", ns) q = ("select a.id from MapAnnotation a join a.mapValue as mv " "where a.ns = :ns and mv.name = :key and mv.value = :value") return unwrap(conn.getQueryService().projection(q, params))[0]
def annotation_ids_by_field(conn, value="CMPO_0000077", key="Phenotype Term Accession", ns="openmicroscopy.org/mapr/phenotype"): """ Return a list of IDs for map annotations with the given namespace that have a key=value pair matching the given parameters. """ from omero.rtypes import unwrap from omero.sys import ParametersI params = ParametersI() params.addString("value", value) params.addString("key", key) params.addString("ns", ns) q = ( "select a.id from MapAnnotation a join a.mapValue as mv " "where a.ns = :ns and mv.name = :key and mv.value = :value" ) return unwrap(conn.getQueryService().projection(q, params))[0]
def stat_plates(query, screen, images=False): params = ParametersI() params.addString("screen", screen) obj = query.findByQuery(( "select s from Screen s " "where s.name = :screen"), params) if not obj: raise Exception("unknown screen: %s" % screen) if images: q = ("select %s from Image i " "join i.wellSamples ws join ws.well w " "join w.plate p join p.screenLinks sl " "join sl.parent s where s.name = :screen") limit = 1000 found = 0 count = unwrap(query.projection( q % "count(distinct i.id)", params ))[0][0] print >>stderr, count params.page(0, limit) q = q % "distinct i.id" q = "%s order by i.id" % q while count > 0: rv = unwrap(query.projection(q, params)) count -= len(rv) found += len(rv) params.page(found, limit) for x in rv: yield x[0] return plates = glob(join(screen, "plates", "*")) plates = map(basename, plates) tb = TableBuilder("Plate") tb.cols(["PID", "Wells", "Images"]) well_count = 0 image_count = 0 for plate in plates: params.addString("plate", plate) rv = unwrap(query.projection(( "select p.id, count(distinct w.id), count(distinct i.id)" " from Screen s " "left outer join s.plateLinks spl join spl.child as p " "left outer join p.wells as w " "left outer join w.wellSamples as ws " "left outer join ws.image as i " "where s.name = :screen and p.name = :plate " "group by p.id"), params)) if not rv: tb.row(plate, "MISSING", "", "") else: for x in rv: plate_id, wells, images = x well_count += wells image_count += images tb.row(plate, plate_id, wells, images) tb.row("Total", "", well_count, image_count) print str(tb.build())
def sets(self, args): """List filesets by various criteria Filesets are bundles of original data imported into OMERO 5 and above which represent 1 *or more* images. Examples: bin/omero fs sets --order=newest # Default bin/omero fs sets --order=oldest bin/omero fs sets --order=largest bin/omero fs sets --without-images # Corrupt filesets bin/omero fs sets --with-transfer=ln_s # Symlinked filesets bin/omero fs sets --check # Proof the checksums """ from omero.constants.namespaces import NSFILETRANSFER from omero_sys_ParametersI import ParametersI from omero.rtypes import unwrap client = self.ctx.conn(args) service = client.sf.getQueryService() admin = client.sf.getAdminService() if args.check and not admin.getEventContext().isAdmin: self.error_admin_only(fatal=True) annselect = ( "(select ann.textValue from Fileset f4 " "join f4.annotationLinks fal join fal.child ann " "where f4.id = fs.id and ann.ns =:ns) ") select = ( "select fs.id, fs.templatePrefix, " "(select size(f2.images) from Fileset f2 " "where f2.id = fs.id)," "(select size(f3.usedFiles) from Fileset f3 " "where f3.id = fs.id),") \ + annselect query1 = ( "from Fileset fs " "where 1 = 1 ") query2 = ( "group by fs.id, fs.templatePrefix ") if args.order: if args.order == "newest": query2 += "order by fs.id desc" elif args.order == "oldest": query2 += "order by fs.id asc" elif args.order == "prefix": query2 += "order by fs.templatePrefix" if args.without_images: query = "%s and fs.images is empty %s" % (query1, query2) else: query = "%s %s" % (query1, query2) params = ParametersI() params.addString("ns", NSFILETRANSFER) count = service.projection("select count(fs) " + query1, params, {"omero.group": "-1"}) params.page(args.offset, args.limit) objs = service.projection(select + query, params, {"omero.group": "-1"}) objs = unwrap(objs) count = unwrap(count)[0][0] cols = ["Id", "Prefix", "Images", "Files", "Transfer"] if args.check: cols.append("Check") tb = self._table(args) tb.cols(cols) tb.page(args.offset, args.limit, count) # Map any requested transfers as well if args.with_transfer: restricted = [TRANSFERS.get(x, x) for x in args.with_transfer[0]] else: restricted = None for idx, obj in enumerate(objs): # Map the transfer name to the CLI symbols ns = obj[-1] if ns is None: ns = "" elif ns in TRANSFERS: ns = TRANSFERS[ns] obj[-1] = ns # Filter based on the ns symbols if restricted and ns not in restricted: continue # Now perform check if required if args.check: from omero.grid import RawAccessRequest desc, prx = self.get_managed_repo(client) ctx = client.getContext(group=-1) check_params = ParametersI() check_params.addId(obj[0]) rows = service.projection(( "select h.value, f.hash, " "f.path || '/' || f.name " "from Fileset fs join fs.usedFiles uf " "join uf.originalFile f join f.hasher h " "where fs.id = :id" ), check_params, ctx) if not rows: obj.append("Empty") err = None for row in rows: row = unwrap(row) raw = RawAccessRequest() raw.repoUuid = desc.hash.val raw.command = "checksum" raw.args = map(str, row) try: cb = client.submit(raw) cb.close(True) except CmdError, ce: err = ce.err self.ctx.dbg(err) if err: obj.append("ERROR!") elif rows: obj.append("OK") tb.row(idx, *tuple(obj))
def attributes_by_attributes(conn, name="Gene Symbol", value="ASH2L", ns="openmicroscopy.org/mapr/gene", ns2="openmicroscopy.org/mapr/phenotype", name2=None, sId=None ): """ Return a list of neighbours attributes for given case insensitive attribute value. (Uses the python blitz gateway) """ from omero.rtypes import rstring, rlist, unwrap from omero.sys import ParametersI params = ParametersI() params.addString("value", value.lower()) q = ( "select distinct new map( mv.value as value) " "from Annotation as a " "join a.mapValue as mv " "where lower(mv.value) = :value {where_claus}" ) where_claus = [] if name: params.addString("name", name) where_claus.append("and mv.name = :name") q = q.format(**{'where_claus':" ".join(where_claus)}) values = [v[0]['value'] for v in unwrap( conn.getQueryService().projection(q, params))] params = ParametersI() valuelist = [rstring(unicode(v)) for v in values] params.add('values', rlist(valuelist)) params.addString("ns", ns) params.addString("ns2", ns2) q = ( "select distinct new map(" "mv.name as name, " "mv.value as value, " "mv2.name as name2, " "mv2.value as value2) " "from Image as i " "join i.annotationLinks as ial " "join i.annotationLinks as ial2 " "join ial.child as a " "join a.mapValue as mv " "join ial2.child as a2 " "join a2.mapValue as mv2 " "where a.ns = :ns and a2.ns = :ns2 " "and mv.value in (:values) {where_claus} " ) where_claus = [] if name: params.addString("name", name) where_claus.append("and mv.name = :name") if name2: params.addString("name2", name2) where_claus.append("and mv2.name = :name2") q = q.format(**{'where_claus':" ".join(where_claus)}) if sId != None: q = q + ("and i in (select image from WellSample " "where well.plate in " "(select child from ScreenPlateLink where parent.id = {sId}))") screenidList = [] screenidList.append(str(sId)) q = q.format(**{'sId':" ".join(screenidList)}) res = {} for r in unwrap(conn.getQueryService().projection(q, params)): r = r[0] try: res[(r['name'], r['value'])].append((r['name2'], r['value2'])) except KeyError: res[(r['name'], r['value'])] = [(r['name2'], r['value2'])] return res
def sets(self, args): """List filesets by various criteria Filesets are bundles of original data imported into OMERO 5 and above which represent 1 *or more* images. Examples: bin/omero fs sets --order=newest # Default bin/omero fs sets --order=oldest bin/omero fs sets --order=largest bin/omero fs sets --without-images # Corrupt filesets bin/omero fs sets --with-transfer=ln_s # Symlinked filesets bin/omero fs sets --check # Proof the checksums """ from omero.constants.namespaces import NSFILETRANSFER from omero_sys_ParametersI import ParametersI from omero.rtypes import unwrap from omero.cmd import OK client = self.ctx.conn(args) service = client.sf.getQueryService() select = ( "select fs.id, fs.templatePrefix, " "(select size(f2.images) from Fileset f2 where f2.id = fs.id)," "(select size(f3.usedFiles) from Fileset f3 where f3.id = fs.id)," "ann.textValue ") query1 = ( "from Fileset fs " "left outer join fs.annotationLinks fal " "left outer join fal.child ann " "where (ann is null or ann.ns = :ns) ") query2 = ( "group by fs.id, fs.templatePrefix, ann.textValue ") if args.order: if args.order == "newest": query2 += "order by fs.id desc" elif args.order == "oldest": query2 += "order by fs.id asc" elif args.order == "prefix": query2 += "order by fs.templatePrefix" if args.without_images: query = "%s and fs.images is empty %s" % (query1, query2) else: query = "%s %s" % (query1, query2) params = ParametersI() params.addString("ns", NSFILETRANSFER) count = service.projection("select count(fs) " + query1, params, {"omero.group": "-1"}) params.page(args.offset, args.limit) objs = service.projection(select + query, params, {"omero.group": "-1"}) objs = unwrap(objs) count = unwrap(count)[0][0] cols = ["Id", "Prefix", "Images", "Files", "Transfer"] if args.check: cols.append("Check") tb = self._table(args) tb.cols(cols) tb.page(args.offset, args.limit, count) for idx, obj in enumerate(objs): # Map the transfer name to the CLI symbols ns = obj[-1] if ns is None: ns = "" elif ns in TRANSFERS: ns = TRANSFERS[ns] obj[-1] = ns # Map any requested transfers as well allowed = args.with_transfer is not None \ and args.with_transfer or [] for idx, x in enumerate(allowed): x = x[0] # Strip argparse wrapper x = TRANSFERS.get(x, x) # map allowed[idx] = x # Filter based on the ns symbols if allowed: if ns not in allowed: continue # Now perform check if required if args.check: from omero.grid import RawAccessRequest desc, prx = self.get_managed_repo(client) ctx = client.getContext(group=-1) check_params = ParametersI() check_params.addId(obj[0]) rows = service.projection(( "select h.value, f.hash, " "f.path || '/' || f.name " "from Fileset fs join fs.usedFiles uf " "join uf.originalFile f join f.hasher h " "where fs.id = :id" ), check_params, ctx) if not rows: obj.append("Empty") err = None for row in rows: row = unwrap(row) raw = RawAccessRequest() raw.repoUuid = desc.hash.val raw.command = "checksum" raw.args = map(str, row) cb = client.submit(raw) try: rsp = cb.getResponse() if not isinstance(rsp, OK): err = rsp break finally: cb.close(True) if err: obj.append("ERROR!") elif rows: obj.append("OK") tb.row(idx, *tuple(obj)) self.ctx.out(str(tb.build()))
def sets(self, args): """List filesets by various criteria Filesets are bundles of original data imported into OMERO 5 and above which represent 1 *or more* images. Examples: bin/omero fs sets --order=newest # Default bin/omero fs sets --order=oldest bin/omero fs sets --order=largest bin/omero fs sets --without-images # Corrupt filesets bin/omero fs sets --with-transfer=ln_s # Symlinked filesets bin/omero fs sets --check # Proof the checksums """ from omero.constants.namespaces import NSFILETRANSFER from omero_sys_ParametersI import ParametersI from omero.rtypes import unwrap client = self.ctx.conn(args) service = client.sf.getQueryService() admin = client.sf.getAdminService() if args.check and not admin.getEventContext().isAdmin: self.error_admin_only(fatal=True) annselect = ( "(select ann.textValue from Fileset f4 " "join f4.annotationLinks fal join fal.child ann " "where f4.id = fs.id and ann.ns =:ns) " ) select = ( "select fs.id, fs.templatePrefix, " "(select size(f2.images) from Fileset f2 " "where f2.id = fs.id)," "(select size(f3.usedFiles) from Fileset f3 " "where f3.id = fs.id)," ) + annselect query1 = "from Fileset fs " "where 1 = 1 " query2 = "group by fs.id, fs.templatePrefix " if args.order: if args.order == "newest": query2 += "order by fs.id desc" elif args.order == "oldest": query2 += "order by fs.id asc" elif args.order == "prefix": query2 += "order by fs.templatePrefix" if args.without_images: query = "%s and fs.images is empty %s" % (query1, query2) else: query = "%s %s" % (query1, query2) params = ParametersI() params.addString("ns", NSFILETRANSFER) count = service.projection("select count(fs) " + query1, params, {"omero.group": "-1"}) params.page(args.offset, args.limit) objs = service.projection(select + query, params, {"omero.group": "-1"}) objs = unwrap(objs) count = unwrap(count)[0][0] cols = ["Id", "Prefix", "Images", "Files", "Transfer"] if args.check: cols.append("Check") tb = self._table(args) tb.cols(cols) tb.page(args.offset, args.limit, count) # Map any requested transfers as well if args.with_transfer: restricted = [TRANSFERS.get(x, x) for x in args.with_transfer[0]] else: restricted = None for idx, obj in enumerate(objs): # Map the transfer name to the CLI symbols ns = obj[-1] if ns is None: ns = "" elif ns in TRANSFERS: ns = TRANSFERS[ns] obj[-1] = ns # Filter based on the ns symbols if restricted and ns not in restricted: continue # Now perform check if required if args.check: from omero.grid import RawAccessRequest desc, prx = self.get_managed_repo(client) ctx = client.getContext(group=-1) check_params = ParametersI() check_params.addId(obj[0]) rows = service.projection( ( "select h.value, f.hash, " "f.path || '/' || f.name " "from Fileset fs join fs.usedFiles uf " "join uf.originalFile f join f.hasher h " "where fs.id = :id" ), check_params, ctx, ) if not rows: obj.append("Empty") err = None for row in rows: row = unwrap(row) raw = RawAccessRequest() raw.repoUuid = desc.hash.val raw.command = "checksum" raw.args = map(str, row) try: cb = client.submit(raw) cb.close(True) except CmdError, ce: err = ce.err self.ctx.dbg(err) if err: obj.append("ERROR!") elif rows: obj.append("OK") tb.row(idx, *tuple(obj))
def stat_plates(query, screen, images=False): params = ParametersI() params.addString("screen", screen) obj = query.findByQuery(("select s from Screen s " "where s.name = :screen"), params) if not obj: raise Exception("unknown screen: %s" % screen) if images: q = ("select %s from Image i " "join i.wellSamples ws join ws.well w " "join w.plate p join p.screenLinks sl " "join sl.parent s where s.name = :screen") limit = 1000 found = 0 count = unwrap(query.projection(q % "count(distinct i.id)", params))[0][0] print >> stderr, count params.page(0, limit) q = q % "distinct i.id" q = "%s order by i.id" % q while count > 0: rv = unwrap(query.projection(q, params)) count -= len(rv) found += len(rv) params.page(found, limit) for x in rv: yield x[0] return plates = glob(join(screen, "plates", "*")) plates = map(basename, plates) tb = TableBuilder("Plate") tb.cols(["PID", "Wells", "Images"]) well_count = 0 image_count = 0 for plate in plates: params.addString("plate", plate) rv = unwrap( query.projection( ("select p.id, count(distinct w.id), count(distinct i.id)" " from Screen s " "left outer join s.plateLinks spl join spl.child as p " "left outer join p.wells as w " "left outer join w.wellSamples as ws " "left outer join ws.image as i " "where s.name = :screen and p.name = :plate " "group by p.id"), params)) if not rv: tb.row(plate, "MISSING", "", "") else: for x in rv: plate_id, wells, images = x well_count += wells image_count += images tb.row(plate, plate_id, wells, images) tb.row("Total", "", well_count, image_count) print str(tb.build())
def stat_top_level(client, study_list, *, release, fsusage, append_totals): # Calculate stats for the studies.tsv file. # client: OMERO client # release: THe name of the IDR release # study_list: List of studies # fsusage: If True use the OMERO DiskUsage2 command to get information # on disk usage, otherwise make a rough guess and ignore other fields # append_totals: If True append an additional row containing totals # Returns a pandas Dataframe that can be used for further analysis query = client.sf.getQueryService() df = pd.DataFrame(columns=( "Study", "Container", "Introduced", "ID", # "Internal ID" "Set", # Number of plates or datasets "Wells", "Experiments", # (wells for screens, imaging experiments for non-screens)", # TODO: remove "Targets", # (genes, small molecules, geographic locations, or combination of # factors (idr0019, 26, 34, 38)", # TODO: remove "Acquisitions", "Images", # "5D Images" "Planes", "Size (TB)", "Bytes", # "Size" "# of Files", "avg. size (MB)", "Avg. Image Dim (XYZCT)", )) # Placeholders: experiments = None targets = None for study, containers in sorted(studies(study_list).items()): for container, set_expected in sorted(containers.items()): logging.info("Retrieving stats for %s" % container) params = ParametersI() params.addString("container", container) if "Plate" in set_expected: parenttype = "Screen" expected = set_expected["Plate"] rv = unwrap(query.projection(SPW_QUERY, params)) elif "Dataset" in set_expected: parenttype = "Project" expected = set_expected["Dataset"] rv = unwrap(query.projection(PDI_QUERY, params)) else: raise Exception("unknown: %s" % list(set_expected.keys())) nexpected = len(expected) container1, container2 = container.split('/', 1) if not rv: df.loc[len(df)] = ( container1, container2, release, "MISSING", 0, 0, experiments, targets, 0, 0, 0, 0, 0, None, None, "", ) else: for x in rv: (plate_or_dataset_id, plate_or_datasets, wells, acquisitions, images, planes, bytes, avg_image_dim) = x if not planes: planes = 0 if not bytes: bytes = 0 if plate_or_datasets != nexpected: logging.debug('%s: got %d plate/datasets expected %d', container, plate_or_datasets, nexpected) if fsusage: logging.debug('Computing disk usage') fs_size, fs_num = fs_usage(client, parenttype, plate_or_dataset_id) else: fs_size = bytes fs_num = None if fs_num: fs_avg_size = fs_size / fs_num / (10**6) else: fs_avg_size = None df.loc[len(df)] = ( container1, container2, release, plate_or_dataset_id, plate_or_datasets, wells, experiments, targets, acquisitions, images, planes, fs_size / (10**12), fs_size, fs_num, fs_avg_size, avg_image_dim, ) if append_totals: totals = df.iloc[:, -12:-2].sum() df.loc[len(df)] = ["Total", "", "", ""] + totals.to_list() + ["", ""] return df