--
--
-- Company :      Piocon Technologies, Inc.
-- Project :      Offline Luminosity 
-- Author :       Jeremy Simmons
--
-- Date Created : Monday, January 27, 2003 13:43:38
-- Dare Modified: Monday, May 12, 2003
-- Target DBMS : Oracle
--


-- 
-- TABLE: BY_TICKS 
--

CREATE TABLE BY_TICKS(
    LBN            NUMBER(38, 0)    NOT NULL,
    SCALER_ID      NUMBER(38, 0)    NOT NULL,
    TICK_1         NUMBER(38, 0),
    TICK_2         NUMBER(38, 0),
    TICK_3         NUMBER(38, 0),    
    TICK_4         NUMBER(38, 0),
    TICK_5         NUMBER(38, 0),
    TICK_6         NUMBER(38, 0),
    TICK_7         NUMBER(38, 0),    
    TICK_8         NUMBER(38, 0),
    TICK_9         NUMBER(38, 0),
    TICK_10        NUMBER(38, 0),
    TICK_11        NUMBER(38, 0),    
    TICK_12        NUMBER(38, 0),
    TICK_13        NUMBER(38, 0),
    TICK_14        NUMBER(38, 0),
    TICK_15        NUMBER(38, 0),    
    TICK_16        NUMBER(38, 0),
    TICK_17        NUMBER(38, 0),
    TICK_18        NUMBER(38, 0),
    TICK_19        NUMBER(38, 0),    
    TICK_20        NUMBER(38, 0),
    TICK_21        NUMBER(38, 0),
    TICK_22        NUMBER(38, 0),
    TICK_23        NUMBER(38, 0),    
    TICK_24        NUMBER(38, 0),
    TICK_25        NUMBER(38, 0),
    TICK_26        NUMBER(38, 0),
    TICK_27        NUMBER(38, 0),    
    TICK_28        NUMBER(38, 0),
    TICK_29        NUMBER(38, 0),
    TICK_30        NUMBER(38, 0),
    TICK_31        NUMBER(38, 0),    
    TICK_32        NUMBER(38, 0),
    TICK_33        NUMBER(38, 0), 
    TICK_34        NUMBER(38, 0),
    TICK_35        NUMBER(38, 0),    
    TICK_36        NUMBER(38, 0),
    TICK_37        NUMBER(38, 0),
    TICK_38        NUMBER(38, 0),
    TICK_39        NUMBER(38, 0),    
    TICK_40        NUMBER(38, 0),
    TICK_41        NUMBER(38, 0),
    TICK_42        NUMBER(38, 0),
    TICK_43        NUMBER(38, 0),    
    TICK_44        NUMBER(38, 0),
    TICK_45        NUMBER(38, 0),
    TICK_46        NUMBER(38, 0),
    TICK_47        NUMBER(38, 0),    
    TICK_48        NUMBER(38, 0),
    TICK_49        NUMBER(38, 0),
    TICK_50        NUMBER(38, 0),
    TICK_51        NUMBER(38, 0),    
    TICK_52        NUMBER(38, 0),
    TICK_53        NUMBER(38, 0),
    TICK_54        NUMBER(38, 0),
    TICK_55        NUMBER(38, 0),    
    TICK_56        NUMBER(38, 0),
    TICK_57        NUMBER(38, 0),
    TICK_58        NUMBER(38, 0),
    TICK_59        NUMBER(38, 0),    
    TICK_60        NUMBER(38, 0),
    TICK_61        NUMBER(38, 0),
    TICK_62        NUMBER(38, 0),
    TICK_63        NUMBER(38, 0),    
    TICK_64        NUMBER(38, 0),
    TICK_65        NUMBER(38, 0), 
    TICK_66        NUMBER(38, 0),
    TICK_67        NUMBER(38, 0),    
    TICK_68        NUMBER(38, 0),
    TICK_69        NUMBER(38, 0),
    TICK_70        NUMBER(38, 0),
    TICK_71        NUMBER(38, 0),    
    TICK_72        NUMBER(38, 0),
    TICK_73        NUMBER(38, 0),
    TICK_74        NUMBER(38, 0),
    TICK_75        NUMBER(38, 0),    
    TICK_76        NUMBER(38, 0),
    TICK_77        NUMBER(38, 0),
    TICK_78        NUMBER(38, 0),
    TICK_79        NUMBER(38, 0),    
    TICK_80        NUMBER(38, 0),
    TICK_81        NUMBER(38, 0),
    TICK_82        NUMBER(38, 0),
    TICK_83        NUMBER(38, 0),    
    TICK_84        NUMBER(38, 0),
    TICK_85        NUMBER(38, 0),
    TICK_86        NUMBER(38, 0),
    TICK_87        NUMBER(38, 0),    
    TICK_88        NUMBER(38, 0),
    TICK_89        NUMBER(38, 0),
    TICK_90        NUMBER(38, 0),
    TICK_91        NUMBER(38, 0),    
    TICK_92        NUMBER(38, 0),
    TICK_93        NUMBER(38, 0),
    TICK_94        NUMBER(38, 0),
    TICK_95        NUMBER(38, 0),    
    TICK_96        NUMBER(38, 0),
    TICK_97        NUMBER(38, 0), 
    TICK_98        NUMBER(38, 0),
    TICK_99        NUMBER(38, 0),    
    TICK_100       NUMBER(38, 0),
    TICK_101       NUMBER(38, 0),
    TICK_102       NUMBER(38, 0),
    TICK_103       NUMBER(38, 0),    
    TICK_104       NUMBER(38, 0),
    TICK_105       NUMBER(38, 0),
    TICK_106       NUMBER(38, 0),
    TICK_107       NUMBER(38, 0),    
    TICK_108       NUMBER(38, 0),
    TICK_109       NUMBER(38, 0),
    TICK_110       NUMBER(38, 0),
    TICK_111       NUMBER(38, 0),    
    TICK_112       NUMBER(38, 0),
    TICK_113       NUMBER(38, 0),
    TICK_114       NUMBER(38, 0),
    TICK_115       NUMBER(38, 0),    
    TICK_116       NUMBER(38, 0),
    TICK_117       NUMBER(38, 0),
    TICK_118       NUMBER(38, 0),
    TICK_119       NUMBER(38, 0),    
    TICK_120       NUMBER(38, 0),
    TICK_121       NUMBER(38, 0),
    TICK_122       NUMBER(38, 0),
    TICK_123       NUMBER(38, 0),    
    TICK_124       NUMBER(38, 0),
    TICK_125       NUMBER(38, 0),
    TICK_126       NUMBER(38, 0),
    TICK_127       NUMBER(38, 0),    
    TICK_128       NUMBER(38, 0),
    TICK_129       NUMBER(38, 0),
    TICK_130       NUMBER(38, 0),
    TICK_131       NUMBER(38, 0),
    TICK_132       NUMBER(38, 0),
    TICK_133       NUMBER(38, 0),
    TICK_134       NUMBER(38, 0),
    TICK_135       NUMBER(38, 0),
    TICK_136       NUMBER(38, 0),
    TICK_137       NUMBER(38, 0),
    TICK_138       NUMBER(38, 0),
    TICK_139       NUMBER(38, 0),
    TICK_140       NUMBER(38, 0),
    TICK_141       NUMBER(38, 0),
    TICK_142       NUMBER(38, 0),
    TICK_143       NUMBER(38, 0),
    TICK_144       NUMBER(38, 0),
    TICK_145       NUMBER(38, 0),
    TICK_146       NUMBER(38, 0),
    TICK_147       NUMBER(38, 0),
    TICK_148       NUMBER(38, 0),
    TICK_149       NUMBER(38, 0),
    TICK_150       NUMBER(38, 0),
    TICK_151       NUMBER(38, 0), 
    TICK_152       NUMBER(38, 0),
    TICK_153       NUMBER(38, 0),
    TICK_154       NUMBER(38, 0),
    TICK_155       NUMBER(38, 0),
    TICK_156       NUMBER(38, 0),
    TICK_157       NUMBER(38, 0),
    TICK_158       NUMBER(38, 0),
    TICK_159       NUMBER(38, 0),
    STATUS_MASK    VARCHAR2(2000)
    )
    TABLESPACE LUM_LRG_DATA    
;

ALTER TABLE BY_TICKS ADD CONSTRAINT BY_TICKS_PK PRIMARY KEY (LBN,SCALER_ID) USING INDEX TABLESPACE LUM_LRG_IDX;


-- 
-- TABLE: FACTORS 
--
CREATE TABLE FACTORS(
    LBN            NUMBER(38, 0)       NOT NULL,
    PNAME          VARCHAR2(64)        NOT NULL,
    PVALUE         NUMBER(38, 0),
    STATUS_MASK    VARCHAR2(2000)
    )
    TABLESPACE LUM_MED_DATA
;

ALTER TABLE FACTORS ADD CONSTRAINT FACTORS_PK PRIMARY KEY (LBN,PNAME) USING INDEX TABLESPACE LUM_MED_IDX;

-- 
-- TABLE: GRP_STATUS 
--

CREATE TABLE GRP_STATUS(
    LBN_LO         NUMBER(38, 0)    NOT NULL,
    LBN_HI         NUMBER(38, 0)    NOT NULL,
    GRP            VARCHAR2(20)     NOT NULL,
    STATUS_COMMENT VARCHAR2(64)     NOT NULL,
    STATUS_MASK    VARCHAR2(2000),
    CREATE_USER    VARCHAR2(30)     NOT NULL,
    CREATE_DATE    DATE             NOT NULL,
    MODIFY_USER    VARCHAR2(30),
    MODIFY_DATE    DATE
    )
    TABLESPACE LUM_SML_DATA
;

ALTER TABLE GRP_STATUS ADD CONSTRAINT GRP_STATUS_PK PRIMARY KEY (LBN_LO,LBN_HI,GRP) USING INDEX TABLESPACE LUM_SML_IDX;

-- 
-- TABLE: LBNS 
--

CREATE TABLE LBNS(
    LBN            NUMBER(38, 0)    NOT NULL,
    DURATION       FLOAT(38)        NOT NULL,
    STATUS_MASK   VARCHAR2(2000)
    )
    TABLESPACE LUM_MED_DATA    
;

ALTER TABLE LBNS ADD CONSTRAINT LBNS_PK PRIMARY KEY (LBN) USING INDEX TABLESPACE LUM_MED_IDX;
 
-- 
-- TABLE: PRD_FILE_CHKS 
--

CREATE TABLE PRD_FILE_CHKS(
    LBN            NUMBER(38, 0)    NOT NULL,
    STREAM_NAME    VARCHAR2(64)     NOT NULL,
    RUN_NUMBER     NUMBER(38, 0)    NOT NULL,
    VERSION        VARCHAR2(64)     NOT NULL,
    DATA_TIER      VARCHAR2(64)     NOT NULL,
    FILE_ID        NUMBER(38, 0),
    STATUS_MASK   VARCHAR2(2000)
    )
    TABLESPACE LUM_MED_DATA
;

ALTER TABLE PRD_FILE_CHKS ADD CONSTRAINT PRD_FILE_CHKS_PK PRIMARY KEY (LBN,STREAM_NAME,RUN_NUMBER,VERSION,DATA_TIER) USING INDEX TABLESPACE LUM_MED_IDX;


-- 
-- TABLE: RANGED_FACTORS 
--

CREATE TABLE RANGED_FACTORS(
    BEGIN_LBN      NUMBER(38, 0)       NOT NULL,
    END_LBN        NUMBER(38, 0)       NOT NULL,
    PNAME          VARCHAR2(64)        NOT NULL,
    PVALUE         NUMBER(38, 0),
    STATUS_MASK    VARCHAR2(2000)
    )
    TABLESPACE LUM_SML_DATA
;

ALTER TABLE RANGED_FACTORS ADD CONSTRAINT RANGED_FACTORS_PK PRIMARY KEY (BEGIN_LBN,END_LBN,PNAME)  USING INDEX TABLESPACE LUM_SML_IDX;

-- 
-- TABLE: SCALER_TYPES 
--

CREATE TABLE SCALER_TYPES(
    SCALER_ID         NUMBER(38, 0)    NOT NULL,
    SCALER_TYPE       VARCHAR2(64)     NOT NULL,
    EXPOSURE_GROUP    VARCHAR2(64)
    )
    TABLESPACE LUM_SML_DATA   
;

ALTER TABLE SCALER_TYPES ADD CONSTRAINT SCALER_TYPES_PK PRIMARY KEY (SCALER_ID) USING INDEX TABLESPACE LUM_SML_IDX;

-- 
-- TABLE: STATUS_MASK_EXPLAIN 
--

CREATE TABLE STATUS_MASK_EXPLAIN(
    MASK_TYPE       VARCHAR2(32)      NOT NULL,
    BIT_POSITION    NUMBER(4, 0)      NOT NULL,
    BIT_DESC        VARCHAR2(2000)
    )
    TABLESPACE LUM_SML_DATA
;

ALTER TABLE STATUS_MASK_EXPLAIN ADD CONSTRAINT STATUS_MASK_EXPLAIN_PK PRIMARY KEY (MASK_TYPE,BIT_POSITION) USING INDEX TABLESPACE LUM_SML_IDX;

-- 
-- TABLE: STREAMS 
--

CREATE TABLE STREAMS(
    LBN            NUMBER(38, 0)    NOT NULL,
    STREAM_NAME    VARCHAR2(64)     NOT NULL,
    RUN_NUMBER     NUMBER(38, 0)    NOT NULL,
    RECORDED       NUMBER(38, 0)    NOT NULL,
    STATUS_MASK    VARCHAR2(2000)
    )
    TABLESPACE LUM_LRG_DATA
;

ALTER TABLE STREAMS ADD CONSTRAINT STREAMS_PK PRIMARY KEY (LBN,STREAM_NAME,RUN_NUMBER) USING INDEX TABLESPACE LUM_LRG_IDX;

-- 
-- TABLE: TRANSITION_RECORDS 
--

CREATE TABLE TRANSITION_RECORDS(
    TRANS_ID       NUMBER(38, 0)    NOT NULL,
    LBN            NUMBER(38, 0)    NOT NULL,
    TRANS_TIME     DATE             NOT NULL,
    PNAME          VARCHAR2(64)     NOT NULL,
    PVALUE         VARCHAR2(256),
    STATUS_MASK    VARCHAR2(2000)
    )
    TABLESPACE LUM_MED_DATA
;

ALTER TABLE TRANSITION_RECORDS ADD CONSTRAINT TRANS_RECS_PK PRIMARY KEY (TRANS_ID)  USING INDEX TABLESPACE LUM_MED_IDX;

-- 
-- TABLE: TRIGGERS 
--

CREATE TABLE TRIGGERS(
    LBN               NUMBER(38, 0)       NOT NULL,
    L3_BIT_NAME       VARCHAR2(32)        NOT NULL,
    RUN_NUMBER        NUMBER(38, 0)       NOT NULL,
    DECORR_SCALER     NUMBER(38, 0)       NOT NULL,
    L1_ACCEPTS        NUMBER(38, 0)       NOT NULL,
    L2_ACCEPTS        NUMBER(38, 0)       NOT NULL,
    RECORDED          NUMBER(38, 0)       NOT NULL,
    EXPOSURE_GROUP    NUMBER(2, 0)        NOT NULL,
    PRESCALE          FLOAT               NOT NULL,
    STATUS_MASK       VARCHAR(2000)
    )
    TABLESPACE LUM_LRG_DATA
;

ALTER TABLE TRIGGERS ADD CONSTRAINT TRIGGERS_PK PRIMARY KEY (LBN,L3_BIT_NAME,RUN_NUMBER) USING INDEX TABLESPACE LUM_LRG_IDX

-- 
-- INDEX: REF 12 
--

CREATE INDEX BY_TICKS_LBN_FKIDX  ON BY_TICKS(LBN)
TABLESPACE LUM_LRG_IDX
;
-- 
-- INDEX: Ref1315 
--

CREATE INDEX BY_TICKS_SCALER_ID_FKIDX ON BY_TICKS(SCALER_ID)
TABLESPACE LUM_LRG_IDX
;

-- 
-- INDEX: Ref18 
--

CREATE INDEX GRP_STATUS_LBN_FKIDX ON GRP_STATUS(LBN_LO,LBN_HI)
TABLESPACE LUM_SML_IDX
;
-- 
-- INDEX: Ref1114 
--

CREATE INDEX PRD_FILE_CHKS_RN_SN_LBN_FKIDX ON PRD_FILE_CHKS(RUN_NUMBER,STREAM_NAME,LBN)
TABLESPACE LUM_MED_IDX
;
-- 
-- INDEX: Ref110 
--

CREATE INDEX RANGED_FACTORS_BEGIN_LBN_FKIDX ON RANGED_FACTORS(BEGIN_LBN)
TABLESPACE LUM_SML_IDX
;
-- 
-- INDEX: Ref111 
--

CREATE INDEX RANGED_FACTORS_END_LBN_FKIDX ON RANGED_FACTORS(END_LBN)
TABLESPACE LUM_SML_IDX
;
-- 
-- INDEX: Ref113 
--

CREATE INDEX STREAMS_LBN_FKIDX ON STREAMS(LBN)
TABLESPACE LUM_LRG_IDX
;
-- 
-- INDEX: Ref116 
--

CREATE INDEX  TRANSITION_RECORDS_LBN_FKIDX ON TRANSITION_RECORDS(LBN)
TABLESPACE LUM_MED_IDX
;
-- 
-- INDEX: Ref11 
--

CREATE INDEX TRIGGERS_LBN_FKIDX ON TRIGGERS(LBN)
TABLESPACE LUM_LRG_IDX
;
-- 
-- TABLE: BY_TICKS 
--

ALTER TABLE BY_TICKS ADD CONSTRAINT BY_TICKS_LBNS_FK 
    FOREIGN KEY (LBN)
    REFERENCES LBNS(LBN)
;

ALTER TABLE BY_TICKS ADD CONSTRAINT BY_TICKS_SCALER_TYPES_FK 
    FOREIGN KEY (SCALER_ID)
    REFERENCES SCALER_TYPES(SCALER_ID)
;

-- 
-- TABLE: GRP_STATUS 
--

ALTER TABLE GRP_STATUS ADD CONSTRAINT GRP_STATUS_LBN_LO_FK 
    FOREIGN KEY (LBN_LO)
    REFERENCES LBNS(LBN)
;

ALTER TABLE GRP_STATUS ADD CONSTRAINT GRP_STATUS_LBN_HI_FK 
    FOREIGN KEY (LBN_HI)
    REFERENCES LBNS(LBN)
;


-- 
-- TABLE: PRD_FILE_CHKS 
--

ALTER TABLE PRD_FILE_CHKS ADD CONSTRAINT PRD_FILE_CHKS_STREAMS_FK 
    FOREIGN KEY (LBN,STREAM_NAME,RUN_NUMBER)
    REFERENCES STREAMS(LBN,STREAM_NAME,RUN_NUMBER)
;

-- 
-- TABLE: FACTORS 
--

ALTER TABLE FACTORS ADD CONSTRAINT FACTORS_LBN_FK 
    FOREIGN KEY (LBN)
    REFERENCES LBNS(LBN)
;

-- 
-- TABLE: RANGED_FACTORS 
--

ALTER TABLE RANGED_FACTORS ADD CONSTRAINT RANGED_FACTORS_LBNS_B_FK
    FOREIGN KEY (BEGIN_LBN)
    REFERENCES LBNS(LBN)
;

-- 
-- TABLE: STREAMS 
--

ALTER TABLE STREAMS ADD CONSTRAINT STREAMS_LBNS_FK
    FOREIGN KEY (LBN)
    REFERENCES LBNS(LBN)
;


-- 
-- TABLE: TRANSITION_RECORDS 
--

ALTER TABLE TRANSITION_RECORDS ADD CONSTRAINT TRANSITION_RECORDS_LBNS_FK
    FOREIGN KEY (LBN)
    REFERENCES LBNS(LBN)
;


-- 
-- TABLE: TRIGGERS 
--

ALTER TABLE TRIGGERS ADD CONSTRAINT TRIGGERS_LBNS_FK 
    FOREIGN KEY (LBN)
    REFERENCES LBNS(LBN)
;


INSERT INTO SCALER_TYPES (SCALER_ID,SCALER_TYPE,EXPOSURE_GROUP) VALUES (0,'EXP0',0);
INSERT INTO SCALER_TYPES (SCALER_ID,SCALER_TYPE,EXPOSURE_GROUP) VALUES (1,'EXP1',1);
INSERT INTO SCALER_TYPES (SCALER_ID,SCALER_TYPE,EXPOSURE_GROUP) VALUES (2,'EXP2',2);
INSERT INTO SCALER_TYPES (SCALER_ID,SCALER_TYPE,EXPOSURE_GROUP) VALUES (3,'EXP3',3);
INSERT INTO SCALER_TYPES (SCALER_ID,SCALER_TYPE,EXPOSURE_GROUP) VALUES (4,'EXP4',4);
INSERT INTO SCALER_TYPES (SCALER_ID,SCALER_TYPE,EXPOSURE_GROUP) VALUES (5,'EXP5',5);
INSERT INTO SCALER_TYPES (SCALER_ID,SCALER_TYPE,EXPOSURE_GROUP) VALUES (6,'EXP6',6);
INSERT INTO SCALER_TYPES (SCALER_ID,SCALER_TYPE,EXPOSURE_GROUP) VALUES (7,'EXP7',7);
INSERT INTO SCALER_TYPES (SCALER_ID,SCALER_TYPE,EXPOSURE_GROUP) VALUES (8,'FASTZ',NULL);
INSERT INTO SCALER_TYPES (SCALER_ID,SCALER_TYPE,EXPOSURE_GROUP) VALUES (9,'PBARHALO1',NULL);
INSERT INTO SCALER_TYPES (SCALER_ID,SCALER_TYPE,EXPOSURE_GROUP) VALUES (10,'PROTONHALO1',NULL);
INSERT INTO SCALER_TYPES (SCALER_ID,SCALER_TYPE,EXPOSURE_GROUP) VALUES (11,'PBARHALO2',NULL);
INSERT INTO SCALER_TYPES (SCALER_ID,SCALER_TYPE,EXPOSURE_GROUP) VALUES (12,'PROTONHALO2',NULL);
EXIT;
