REM Initial layout for a run process database Rem Runflag table create table RunFlags ( Flag varchar2(20) not null ); REM Run table create table Run ( RunID integer, Runstatus varchar2(10), RunQuality varchar2(10), Comment clob, constraint Run_pk primary key (RunID), constraint Run_FK foreign Key (run) references BRYSON_RUN_TABLE (run) ); REM Run Meta Data Tables create table RunMetaString ( Run integer not null, MetaName varchar2(20) not null, MetaValue varchar2(256), constraint FK_RunMetaString foreign key (Run) references Run (Run) on delete cascade, constraint UNQ_RunMetaString unique (Run, MetaName) ); create index IDX_RunMetaString on RunMetaString (Run); create index IDX_RumMetaString_MetaName on RunMetaString (MetaName); create table RunMetaNumber ( Run integer not null, MetaName varchar2(20) not null, MetaValue number, constraint FK_RunMetaNum foreign key (Run) references Run (Run) on delete cascade, on delete cascade, constraint UNQ_RunMetaNum unique (Run, MetaName) ); create index IDX_RunMetaNum on RunMetaNumber (Run); create index IDX_RunMetaNum_MetaName on RunMetaNumber (MetaName); create table RunMetaTimestamp ( Run integer not null, MetaName varchar2(20) not null, MetaValue timestamp, constraint FK_RunMetaTime foreign key (Run) references Run (Run) on delete cascade, constraint UNQ_RunMetaTime unique (Run, MetaName) ); create index IDX_RunMetaTime on RunMetaTimestamp (Run); create index IDX_RunMetaTime_MetaName on RunMetaTimestamp (MetaName); REM Run Processing table, sequence and trigger create table RunProcess ( RunProcessID integer, run integer, stream (?or streamID?) integer, Quality varchar2(10), Status varchar2(10), CompleteStatus varcha2(10), constraint RunProcess_pk primary key (RunProcessID), constraint RunProcess_FK foreign Key (run) references Run (runID), constraint RunProcessStream_FK foreign Key (stream) references Stream (stream) ); create index IDX_RunProcess_Stream on RunProcess (Run,Stream); create sequence RunProcessID_seq; create or replace trigger runProcess_PK before insert on RunProcess for each row begin select runProcessID_seq.nextval into :new.RunProcessID from dual; end; / REM Run History table, sequence and trigger create table RunHistory ( RunHistoryID integer, runId integer, RunProcessID integer, Change varchar2(120), CauseOfChange varchar2(120), ChangeDate timestamp, comments clob, constraint RunHistory_pk primary key (runHistoryID), constraint RunHistoryRun_FK foreign Key (run) references Run (run), constraint RunHistoryRunProcess_FK foreign Key (RunProcessID) references RunProcess (run) ); create sequence RunHistoryID_seq; create or replace trigger runHistory_pk before insert on RunHistory for each row begin select RunHistoryID_seq.nextval into :new.RunHistoryID from dual; end; / REM Run Configuration place holder for: table, sequence and trigger create table RunConfiguration ( runId, integer, config1 ??? config2 etc ???? . . . );