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