Open Provenance Model

SQL Script to compute Summary fields

The following script is used to compute summary fields in the OPMProcess and OPMGraph tables, mainly:

1. Queue, Start, and End time for each Process in every experiment
2. Start/End time for each experiment,
3. Total number of executed jobs/processes in each experiment,
4. Total number of successful, failed, and re-tried jobs/processes in each experiment,
5. Final STATUS of each experiment (Succeeded, Failed, etc.)

Some minor changes may occur if using another DBMS than 'MySQL'.

drop table if exists OPM_PROCESS;
create table OPM_PROCESS as 
       (select distinct ProcessKey, min(FROM_UNIXTIME(left(PropertyValue,10))) Starts_at, max(FROM_UNIXTIME(left(PropertyValue,10))) Ends_at 
          from OPMAnnotation a, ProcessAnnotation pa, AnnotationProperty ap, OPMProperty p 
          where a.AnnotationKey=pa.AnnotationKey and a.AnnotationKey=ap.AnnotationKey and ap.PropertyId=p.PropertyId and 
                LocalSubject='STATUS' group by AnnotationId);
UPDATE OPM_PROCESS p, OPMProcess pp SET pp.QueueTime = p.Starts_at, pp.EndTime = p.Ends_at where p.ProcessKey = pp.ProcessKey;
drop table if exists OPM_PROCESS;

create table OPM_PROCESS as 
       (select distinct ProcessKey, min(FROM_UNIXTIME(left(PropertyValue,10))) Starts_at 
          from OPMAnnotation a, ProcessAnnotation pa, AnnotationProperty ap, OPMProperty p 
          where a.AnnotationKey=pa.AnnotationKey and a.AnnotationKey=ap.AnnotationKey and ap.PropertyId=p.PropertyId and 
                LocalSubject='STATUS' and PropertyKey='RUNNING' group by AnnotationId);
UPDATE OPM_PROCESS p, OPMProcess pp SET pp.StartTime = p.Starts_at where p.ProcessKey = pp.ProcessKey;
drop table if exists OPM_PROCESS;

create table OPM_GRAPH as 
       (select distinct p.GraphKey ,min(StartTime) StartTime, max(EndTime) EndTime, count(ProcessKey) Number_of_jobs 
          from OPMProcess p where ProcessKey in (select ProcessKey from ProcessAnnotation) group by p.GraphKey);
UPDATE OPM_GRAPH p, OPMGraph pp SET pp.StartsAt = p.StartTime, pp.EndsAt = p.EndTime, pp.NumberJobs=p.Number_of_jobs 
  where p.GraphKey = pp.GraphKey;
drop table if exists OPM_GRAPH;

create table OPM_GRAPH as 
       (Select GraphKey, count(ProcessKey) Completed_jobs, 'SUCCEEDED' from OPMProcess where ProcessKey in 
       (select distinct ProcessKey from OPMAnnotation a, ProcessAnnotation pa, AnnotationProperty ap, OPMProperty p 
        where LocalSubject='STATUS' and PropertyKey='COMPLETED' and a.AnnotationKey=pa.AnnotationKey and 
              a.AnnotationKey=ap.AnnotationKey and ap.PropertyId=p.PropertyId) group by GraphKey);
UPDATE OPM_GRAPH p, OPMGraph pp SET pp.CompletedJobs = p.Completed_jobs, pp.GraphStatus = p.SUCCEEDED where p.GraphKey = pp.GraphKey;
drop table if exists OPM_GRAPH;

update OPMGraph set FailedJobs=(NumberJobs-CompletedJobs);
update OPMGraph set GraphStatus=(if(NumberJobs>0 and (CompletedJobs+RetriedJobs=NumberJobs),'SUCCEEDED','FAILED'));
update OPMGraph set GraphStatus=concat(GraphStatus,(if(GraphStatus='SUCCEEDED' and RetriedJobs>0,'(f)','')));
update OPMGraph set GraphStatus=concat(GraphStatus,(if(GraphStatus='FAILED' and CompletedJobs>0,'(s)','')));
update OPMGraph set GraphUser= (select substring(AgentId,6) from OPMAgent 
       where OPMAgent.GraphKey= OPMGraph.GraphKey and AgentId like 'User: %');
update OPMGraph set GraphUser='UNKNOWN' where GraphUser is null;
update OPMGraph set GraphName= (select distinct substring_index(AccountId, '__', 1) from OPMAccount 
       where OPMAccount.GraphKey= OPMGraph.GraphKey);

-- AmmarBenabdelkader - 08 Mar 2011
to top