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             ????
.
.
.
);
  • No labels