Open Provenance Model

Implementing the OPM relational database schema: OPMRDB

The following script is used to create the physical database schema of OPM into 'MySQL' database. Some minor changes are required if using other DBMSs.

SET autocommit=0;
START TRANSACTION;

DROP DATABASE OPM_DB1_1;
CREATE DATABASE OPM_DB1_1;
USE OPM_DB1_1;

CREATE TABLE OPMGraph (
  GraphKey bigint(20) NOT NULL AUTO_INCREMENT,
  GraphId varchar(255) DEFAULT NULL,
  GraphName varchar(255) DEFAULT NULL,
  GraphDescription varchar(255) DEFAULT NULL,
  GraphUser varchar(255) DEFAULT '',
  StartsAt datetime DEFAULT NULL,
  EndsAt datetime DEFAULT NULL,
  NumberJobs int(10) NOT NULL DEFAULT '0',
  RetriedJobs int(10) NOT NULL DEFAULT '0',
  CompletedJobs int(10) NOT NULL DEFAULT '0',
  FailedJobs int(10) NOT NULL DEFAULT '0',
  GraphStatus varchar(20) DEFAULT '',
  PRIMARY KEY (GraphKey)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE OPMArtifact (
  ArtifactKey bigint(20) NOT NULL AUTO_INCREMENT,
  ArtifactId varchar(255) DEFAULT NULL,
  ArtifactValue varchar(1024) DEFAULT NULL,
  GraphKey bigint(20) DEFAULT NULL,
  PRIMARY KEY (ArtifactKey),
  FOREIGN KEY (GraphKey) REFERENCES OPMGraph (GraphKey)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE OPMProcess (
  ProcessKey bigint(20) NOT NULL AUTO_INCREMENT,
  ProcessId varchar(255) DEFAULT NULL,
  QueueTime datetime DEFAULT NULL,
  StartTime datetime DEFAULT NULL,
  EndTime datetime DEFAULT NULL,
  GraphKey bigint(20) DEFAULT NULL,
  PRIMARY KEY (ProcessKey),
  FOREIGN KEY (GraphKey) REFERENCES OPMGraph (GraphKey)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE OPMAgent (
  AgentKey bigint(20) NOT NULL AUTO_INCREMENT,
  AgentId varchar(255) DEFAULT NULL,
  GraphKey bigint(20) DEFAULT NULL,
  PRIMARY KEY (AgentKey),
  FOREIGN KEY (GraphKey) REFERENCES OPMGraph (GraphKey)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE OPMAccount (
  AccountKey bigint(20) NOT NULL AUTO_INCREMENT,
  AccountId varchar(255) DEFAULT NULL,
  GraphKey bigint(20) DEFAULT NULL,
  PRIMARY KEY (AccountKey),
  FOREIGN KEY (GraphKey) REFERENCES OPMGraph (GraphKey)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE OPMAnnotation (
  AnnotationKey bigint(20) NOT NULL AUTO_INCREMENT,
  AnnotationType varchar(255) NOT NULL,
  AnnotationId varchar(255) DEFAULT NULL,
  ExternalSubject varchar(255) DEFAULT NULL,
  LocalSubject varchar(255) DEFAULT NULL,
  LabelValue varchar(255) DEFAULT NULL,
  GraphKey bigint(20) DEFAULT NULL,
  PRIMARY KEY (AnnotationKey),
  FOREIGN KEY (GraphKey) REFERENCES OPMGraph (GraphKey)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE ProcessAccount (
  ProcessKey bigint(20) NOT NULL,
  AccountKey bigint(20) NOT NULL,
  PRIMARY KEY (AccountKey,ProcessKey),
  FOREIGN KEY (ProcessKey) REFERENCES OPMProcess (ProcessKey),
  FOREIGN KEY (AccountKey) REFERENCES OPMAccount (AccountKey)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE OPMProperty (
  PropertyId bigint(20) NOT NULL AUTO_INCREMENT,
  PropertyKey varchar(255) DEFAULT NULL,
  PropertyValue varchar(255) DEFAULT NULL,
  PRIMARY KEY (PropertyId)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE OPMDependency (
  DependencyKey bigint(20) NOT NULL AUTO_INCREMENT,
  DependencyType varchar(255) NOT NULL,
  DependencyId varchar(255) DEFAULT NULL,
  Time datetime DEFAULT NULL,
  DependencyRole varchar(255) DEFAULT NULL,
  DependencyCause bigint(20) DEFAULT NULL,
  DependencyEffect bigint(20) DEFAULT NULL,
  GraphKey bigint(20) DEFAULT NULL,
  PRIMARY KEY (DependencyKey),
  KEY DependencyEffect (DependencyEffect),
  KEY DependencyEffect_2 (DependencyEffect),
  KEY DependencyCause (DependencyCause),
  KEY DependencyCause_2 (DependencyCause),
  KEY DependencyCause_3 (DependencyCause),
  FOREIGN KEY (GraphKey) REFERENCES OPMGraph (GraphKey)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE DependencyAnnotation (
  DependencyKey bigint(20) NOT NULL,
  AnnotationKey bigint(20) NOT NULL,
  PRIMARY KEY (DependencyKey,AnnotationKey),
  FOREIGN KEY (AnnotationKey) REFERENCES OPMAnnotation (AnnotationKey),
  FOREIGN KEY (DependencyKey) REFERENCES OPMDependency (DependencyKey)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE DependencyAccount (
  DependencyKey bigint(20) NOT NULL,
  AccountKey bigint(20) NOT NULL,
  PRIMARY KEY (AccountKey,DependencyKey),
  FOREIGN KEY (DependencyKey) REFERENCES OPMDependency (DependencyKey),
  FOREIGN KEY (AccountKey) REFERENCES OPMAccount (AccountKey)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE ArtifactAnnotation (
  ArtifactKey bigint(20) NOT NULL,
  AnnotationKey bigint(20) NOT NULL,
  PRIMARY KEY (AnnotationKey,ArtifactKey),
  FOREIGN KEY (ArtifactKey) REFERENCES OPMArtifact (ArtifactKey),
  FOREIGN KEY (AnnotationKey) REFERENCES OPMAnnotation (AnnotationKey)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE ArtifactAccount (
  ArtifactKey bigint(20) NOT NULL,
  AccountKey bigint(20) NOT NULL,
  PRIMARY KEY (AccountKey,ArtifactKey),
  FOREIGN KEY (ArtifactKey) REFERENCES OPMArtifact (ArtifactKey),
  FOREIGN KEY (AccountKey) REFERENCES OPMAccount (AccountKey)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE AnnotationProperty (
  AnnotationKey bigint(20) NOT NULL,
  PropertyId bigint(20) NOT NULL,
  PRIMARY KEY (AnnotationKey,PropertyId),
  FOREIGN KEY (PropertyId) REFERENCES OPMProperty (PropertyId),
  FOREIGN KEY (AnnotationKey) REFERENCES OPMAnnotation (AnnotationKey)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE AnnotationAccount (
  AnnotationKey bigint(20) NOT NULL,
  AccountKey bigint(20) NOT NULL,
  PRIMARY KEY (AccountKey,AnnotationKey),
  FOREIGN KEY (AnnotationKey) REFERENCES OPMAnnotation (AnnotationKey),
  FOREIGN KEY (AccountKey) REFERENCES OPMAccount (AccountKey)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE AgentAccount (
  AgentKey bigint(20) NOT NULL,
  AccountKey bigint(20) NOT NULL,
  PRIMARY KEY (AccountKey,AgentKey),
  FOREIGN KEY (AgentKey) REFERENCES OPMAgent (AgentKey),
  FOREIGN KEY (AccountKey) REFERENCES OPMAccount (AccountKey)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE AgentAnnotation (
  AgentKey bigint(20) NOT NULL,
  AnnotationKey bigint(20) NOT NULL,
  PRIMARY KEY (AgentKey,AnnotationKey),
  FOREIGN KEY (AnnotationKey) REFERENCES OPMAnnotation (AnnotationKey),
  FOREIGN KEY (AgentKey) REFERENCES OPMAgent (AgentKey)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE AccountAnnotation (
  AccountKey bigint(20) NOT NULL,
  AnnotationKey bigint(20) NOT NULL,
  PRIMARY KEY (AccountKey,AnnotationKey),
  FOREIGN KEY (AnnotationKey) REFERENCES OPMAnnotation (AnnotationKey),
  FOREIGN KEY (AccountKey) REFERENCES OPMAccount (AccountKey)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE ProcessAnnotation (
  ProcessKey bigint(20) NOT NULL,
  AnnotationKey bigint(20) NOT NULL,
  PRIMARY KEY (ProcessKey,AnnotationKey),
  FOREIGN KEY (AnnotationKey) REFERENCES OPMAnnotation (AnnotationKey),
  FOREIGN KEY (ProcessKey) REFERENCES OPMProcess (ProcessKey)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
COMMIT;
SET autocommit=1;

-- AmmarBenabdelkader - 10 Jan 2011
to top