Queries over the MIR

Note The page relates to the MIR version 3 of 2003, and has now been superceeded by the InformationModel

RobertsMIRViews provides an initial target set of alternative views.

-- MarkGreenwood - 30 Apr 2003

Robert's queries

Some attempts to resolve them; note heavy use of nested selects, e.g. for mapping between external URIs (used outside of MIR3 service) and thing IDs, used in the internal tables.

I haven't tried to run these, and my knowledge of SQL is limited

-- ChrisGreenhalgh - 12 May 2003

Show me my projects and related entities

1. Projects created by me:
   • "SELECT ExternalURI^? FROM MIR3.Thing WHERE Thing.localType='WorkContext' AND Thing.createdBy=(SELECT ThingID^? FROM MIR3.Thing WHERE ExternalURI^?=?)"
   • arguments are:
     1. User's ExternalURI^?, e.g. "user:MIR3TestSet1".
   • Note: 'my' could mean other things, such as 'that officially involve me'; there are currently no predicates standardised to support this.
2. DataThings^?, WFDefinitions, WFInstances and nested WorkContexts^? created in a particular WorkContext^? (e.g. from the above):
   • "SELECT ExternalURI^? FROM MIR3.Thing WHERE ThingID^? IN (SELECT MetaData.subject FROM MetaData^? WHERE MetaData.predicate=(SELECT ThingID^? FROM MIR3.Thing WHERE ExternalURI^?='wasCreatedIn') AND MetaData.object=(SELECT ThingID^? FROM MIR3.Thing WHERE ExternalURI^?=?))"
   • Arguments:
     1. WorkContext^?'s External URI.
   • Note: could combine with the first one.
   • Note: includes things not created by me.
   • Note: not explicitly restricted to DataThings^?, etc., but these are the ones for which this Annotation is established automatically at present on creation by the MIR3 Service.
3. DataThing^?, WFDefinitions, and WFInstances created by me: see 1, for localTypes of 'DataThing', 'WFDefinition' and 'WFInstance'

Show me the experiments and related entities for project x.

1. See 2 in previous section: 'Project x' is a particular work context, and 'experiements' are related to it (currently) by 'wasCreatedIn'. To get a work context with a particular name (rather than LSID):
   • "SELECT ExternalURI^? FROM MIR3.Thing WHERE Thing.localType='WorkContext' AND Thing.name=?"
   • Arguments:
     1. WorkContext^?'s user-specified name (defaults to its LSID).

show me the history of runs for experiment x

1. WFInstances created in WorkContext^? corresponding to experiment x, in time order:
   • "SELECT ExternalURI^? FROM MIR3.Thing WHERE Thing.localType='WFInstance' AND ThingID^? IN (SELECT MetaData.subject FROM MetaData^? WHERE MetaData.predicate=(SELECT ThingID^? FROM MIR3.Thing WHERE ExternalURI^?='wasCreatedIn') AND MetaData.object=(SELECT ThingID^? FROM MIR3.Thing WHERE ExternalURI^?=?)) ORDER BY Thing.created"
   • Arguments:
     1. LSID of WorkContext^? corresponding to experiment x
   • Note: This gives the LSID's of the provenace records, rather than the actual content of the provenance records (but then the provenance records themselves are currently not complete without the associations to inputs, outputs and WFDefinition that are also in the MIR).
   • Note: option to use WorkContext^? name rather than LSID (see above)

What experiments has person x run.

1. What WorkContexts^? has person x created - see 'Show me my projects and related entities'
2. What WFInstance has person x created:
   • "SELECT ExternalURI^? FROM MIR3.Thing WHERE Thing.localType='WFInstance' AND Thing.createdBy=(SELECT ThingID^? FROM MIR3.Thing WHERE ExternalURI^?=?)"
   • Arguments:
     1. User's ExternalURI^?, e.g. "user:MIR3TestSet1".
   • Note: option to use User's name rather than LSID (see above)

Who owns/manages prpject x

1. Who created the WorkContext^? corresponding to project x:
   • "SELECT ExternalURI^?,Thing.name,User.x509DN FROM MIR3.Thing,MIR3.User WHERE ThingID^?=User.ThingID AND ThingID^?=(SELECT Thing.createdBy FROM MIR3.Thing WHERE Thing.localType='WorkContext' AND ExternalURI^?=?)"
   • Arguments:
     1. WorkContext^?'s external URI
   • Note: may only want a subset of User's external URI, name and X509DN.
   • Note: option to use WorkContext^? name rather than LSID (see above)
   • Note: currently no specific property standardised for 'owns' or 'manages', hence use of 'createdBy' standard metadata.

Who created experiment x

1. See previous.

What were the results of running experiment x at time y.

1. (Part a) What WFinstances were run at what times in WorkContext^? corresponding to experiment x:
   • "SELECT ExternalURI^?,Thing.created FROM MIR3.Thing WHERE Thing.localType='WFInstance' AND ThingID^? IN (SELECT MetaData.subject FROM MetaData^? WHERE MetaData.predicate=(SELECT ThingID^? FROM Thing WHERE ExternalURI^?='wasCreatedIn') AND MetaData.object=(SELECT ThingID^? FROM MIR3.Thing WHERE ExternalURI^?=?)) ORDER BY Thing.created"
   • Arguments:
     1. LSID of WorkContext^? corresponding to experiment x
2. (Part b) What DataThing^?(s) were the results of a particular run (by LSID):
   • "SELECT MetaData.predicateQualifier,Thing.ExternalURI FROM MetaData^?,MIR3.Thing WHERE MetaData.object=Thing.ThingID AND MetaData.subject=(SELECT ThingID^? FROM MIR3.Thing WHERE ExternalURI^?=?) AND MetaData.predicate=(SELECT ThingID^? FROM Thing WHERE ExternalURI^?='hasOutput') ORDER BY MetaData.predicateQualifier"
   • Arguments:
     1. LSID of WFInstance of particular run in question
   • Returns output qualifiers (e.g. output message part name) and LSID of output value.

Show me the stats of usage of resource x.

1. Where 'resource x' is a DataThing^? and 'usage' is 'used as input to a WFInstance':
   • "SELECT ExternalURI^?,Thing.created FROM MIR3.Thing WHERE Thing.localType='WFInstance' AND ThingID^? IN (SELECT MetaData.subject FROM MetaData^? WHERE MetaData.object=(SELECT ThingID^? FROM MIR3.Thing WHERE ExternalURI^?=?) AND MetaData.predicate=(SELECT ThingID^? FROM MIR3.Thing WHERE ExternalURI^?='hasInput'))"
     1. LSID of DataThing^? corresponding to resouce x
   • Note: returns LSIDs and creation times of WFInstances using DataThing^? x
2. Where 'resource x' is particular web service:
   • ?
   • Note: Requires XMLExtender support and XPath Query to search Provenance records, or additional metadata properties written explicitly into MIR

What services to I commonly use.

1. What WFDefinitions have I used, and when, in time order:
   • "SELECT WFDefName^?,WFInstance.startTime FROM MIR3.Thing,MIR3.WFInstance WHERE ThingID^?=Thing.ThingID AND Thing.createdBy=(SELECT ThingID^? FROM MIR3.Thing WHERE ExternalURI^?=?) AND WFDefId^?=WFInstance.definedBy AND WFDefName^?=(SELECT ExternalURI^? FROM MIR3.Thing WHERE ThingID^?=WFDefId)"
   • Arguments:
     1. User's LSID
   • Note: really not sure about the syntax of this one!

"Each of these may be filtered"

"...managed by x"

See notes above (only 'createdBy' at the moment).

"...at time y"

1. "...WHERE Thing.created=?"
   • Note: this would be an exact comparison; presumably a looser notion of time or interval is desired??

"...run by z"

See notes above; currently only 'createdBy'

"these all have semantic variants"

Hmm

"Show me projects (across or within projects) to do with human SNPs."

1. Projects (WorkContexts^?) containing DataThings^? conceptually labelled with something subsumable to something in the Ontology to do with 'human' and 'SNP'?? I don't think that there is such a thing at the moment.
2. Projects (WorkContexts^?) containing WFInstances whose WFDefinitions are conceptually labelled in the MIR with operation concepts that have a subsumption relationship to something in the Ontology which in turn can be determined to be 'to do with human SNPs' (how would you do that, i wonder?!)

"What data do I have concerning GPCRs."

Similar issues... Plus, will they provide a GO term for 'GPCR', and if so, how will this be related to the myGrid Ontology terms used to conceptually label DataThings^? and WFDefinitions??

"What experiments do I do with task x."

As above.

-- ChrisGreenhalgh - 12 May 2003