View Source

{noformat}
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 ????
.
.
.
);
{noformat}