OnlineDB/EcalCondDB/sql/create_laser_new_2010.sql - master - githubmirror/cmssw

OnlineDB/EcalCondDB/sql/create_laser_new_2010.sql
1 208 строк41 KB
Andrea Bocci
Add missing newline at the end of file
27 фев 2024, 16:21
27 фев 2024, 16:21718f8fe
/*
 *
 * GO 20091112: indexes added
 *
 * PhG 20010-01-06:
 *   LMF_LASER_COLORS_DEF -> LMF_LASER_COLOR_DEF + column changes.
 *   Err. on norm. factor
 * 
 * GO 20010109: new indexes added - removed SEQ_ID from 
 *   LMF_PRIM_DATASET_DAT - partitioned tables 
 *
 * GO 20100323: sequences redefined with NOCACHE
 *	
 * GO 20100910: removed contraints against RUN_IOV (cannot constraint on a
 *   different account): substituted by triggers
 *
 * GO 20100927: SYNONYMs created (may need grant on CMS_ECAL_COND)
 * GO 20101011: modified table structure for XXX_CLS_XXX tables
 * GO 20101020: LMF_CLS_XXX tables modified to move the REF field after
 *              LOGIC_ID (needed to use only one class in C++) 
 * GO 20101124: modified Corr coeff table
 * GO 20110301: final (hopefully) version with pre-partitioned 
 *              tables and local indices, as well as with
 *              splitted DB sequences.
 * GO 20110531: added support for CLS classes. Added partitions %_11b. 
 * GO 20110907: updated the script to reflect the current table layout 
 *              (PN_PRIM_DAT tables columns swapped w.r.t. the real ones)
 * GO 20110907: added USING INDEX LOCAL ENABLE clause to the creation of
 *              LMF_LED_XX_PN_PRIM_DAT table as a test (see e-mail from 
 *              Skorupinski)
 */

PROMPT "Starting creating laser tables: "
PROMPT "   please GRANT SELECT ON RUN_IOV from CMS_ECAL_COND account"

/* create sequences. Create different sequences for different years */

CREATE SEQUENCE LMF_RUN_TAG_SQ INCREMENT BY 1 START WITH 1 NOCACHE;
CREATE SEQUENCE LMF_RUN_IOV_10_SQ INCREMENT BY 1 START WITH 1 NOCACHE;
CREATE SEQUENCE LMF_RUN_IOV_11_SQ INCREMENT BY 1 START WITH 1158001 NOCACHE;
CREATE SEQUENCE LMF_LMR_SUB_IOV_ID_10_SQ INCREMENT BY 1 START WITH 1 NOCACHE;
CREATE SEQUENCE LMF_LMR_SUB_IOV_ID_11_SQ INCREMENT BY 1 START WITH 2401 NOCACHE;
CREATE SEQUENCE LMF_IOV_10_SQ INCREMENT BY 1 START WITH 1 NOCACHE;
CREATE SEQUENCE LMF_IOV_11_SQ INCREMENT BY 1 START WITH 2401 NOCACHE;
CREATE SEQUENCE SEQ_ID_10_SQ INCREMENT BY 1 START WITH 1 NOCACHE;
CREATE SEQUENCE SEQ_ID_11_SQ INCREMENT BY 1 START WITH 18001 NOCACHE;

/*  LMF_RUN_TAG: done  */
CREATE TABLE LMF_RUN_TAG
(
  GEN_TAG VARCHAR2(100),
  VERSION NUMBER,
  TAG_ID  NUMBER NOT NULL
)
/

INSERT INTO LMF_RUN_TAG VALUES ('gen', 1, lmf_run_tag_sq.nextVal);

ALTER TABLE LMF_RUN_TAG
  ADD CONSTRAINT LMF_RUN_TAG_PK PRIMARY KEY (TAG_ID) USING INDEX LOCAL ENABLE
/

/* LMF_COLOR_DEF: done */
CREATE TABLE LMF_COLOR_DEF
(
  COLOR_ID    NUMBER NOT NULL,
  COLOR_INDEX NUMBER    NOT NULL,
  SNAME       VARCHAR2(10) NOT NULL,
  LNAME       VARCHAR2(100) NOT NULL
)
/

ALTER TABLE LMF_COLOR_DEF
  ADD CONSTRAINT LM_COLOR_DEF_PK PRIMARY KEY (COLOR_ID) USING INDEX LOCAL 
	ENABLE
/

INSERT INTO LMF_COLOR_DEF VALUES (1, 0, 'blue', 
	'blue laser (440 nm) or blue led');
INSERT INTO LMF_COLOR_DEF VALUES (2, 1, 'green', 'green (495 nm)');
INSERT INTO LMF_COLOR_DEF VALUES (3, 2, 
	'red/orange', 'red laser (706 nm) or orange led');
INSERT INTO LMF_COLOR_DEF VALUES (4, 3, 'IR', 'infrared (796 nm)');

/* LMF_TRIG_TYPE_DEF */
CREATE TABLE LMF_TRIG_TYPE_DEF
(
  TRIG_TYPE NUMBER,
  SNAME VARCHAR2(5),
  LNAME VARCHAR2(50),
  CONSTRAINT LMF_TRIG_TYPE_DEF PRIMARY KEY(TRIG_TYPE) USING INDEX LOCAL ENABLE
)
/

INSERT INTO LMF_TRIG_TYPE_DEF VALUES (1, 'las', 'laser');
INSERT INTO LMF_TRIG_TYPE_DEF VALUES (2, 'led', 'led');
INSERT INTO LMF_TRIG_TYPE_DEF VALUES (3, 'tp', 'test pulse');
INSERT INTO LMF_TRIG_TYPE_DEF VALUES (4, 'ped', 'pedestal');

/* LMF_SEQ_VERS */
CREATE TABLE LMF_SEQ_VERS
(
  VERS NUMBER(3),
  DB_TIMESTAMP TIMESTAMP DEFAULT sys_extract_utc(SYSTIMESTAMP) NOT NULL,
  DESCR VARCHAR2(100),
  CONSTRAINTS LMF_SEQ_VERS_PK PRIMARY KEY(VERS) USING INDEX LOCAL ENABLE
)
/

INSERT INTO LMF_SEQ_VERS VALUES (0, DEFAULT, 'none');
INSERT INTO LMF_SEQ_VERS VALUES (1, DEFAULT, 'default');

/* LMF_SEQ_DAT 
-- In the table the list of calibration sequences, that is a scan
-- of the whole ECAL with the different event type. 1 row = 1 sequence.
*/
CREATE TABLE LMF_SEQ_DAT
(
  SEQ_ID        NUMBER,
  RUN_IOV_ID    NUMBER,
  SEQ_NUM       NUMBER,
  SEQ_START     DATE NOT NULL,
  SEQ_STOP      DATE NOT NULL,
  VMIN          NUMBER,
  VMAX          NUMBER,
  CONSTRAINTS LMF_SEQ_DAT_PK PRIMARY KEY(SEQ_ID) USING INDEX LOCAL ENABLE,
  CONSTRAINTS LMF_SEQ_DAT_FK2 FOREIGN KEY(VMIN) REFERENCES LMF_SEQ_VERS(VERS),
  CONSTRAINTS LMF_SEQ_DAT_FK3 FOREIGN KEY(VMAX) REFERENCES LMF_SEQ_VERS(VERS)
)
PARTITION BY RANGE ("SEQ_ID")
(PARTITION "LMF_SEQ_DAT_10" VALUES LESS THAN (18000) 
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_SEQ_DAT_11" VALUES LESS THAN (36000)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

/*  LMF_RUN_IOV  */
-- This table holds the list of monitoring subruns, that a batch
-- of consecutive events on the same region and of the same type.
CREATE TABLE LMF_RUN_IOV
(
  LMF_IOV_ID     NUMBER NOT NULL,
  TAG_ID         NUMBER NOT NULL,
  SEQ_ID         NUMBER NOT NULL,
  LMR            NUMBER NOT NULL,
  COLOR_ID       NUMBER NOT NULL,
  TRIG_TYPE      NUMBER NOT NULL,
  SUBRUN_START   DATE NOT NULL,
  SUBRUN_END     DATE NOT NULL,
  SUBRUN_TYPE    VARCHAR2(20) NOT NULL,
  DB_TIMESTAMP   TIMESTAMP DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL
)
PARTITION BY RANGE ("LMF_IOV_ID")
(PARTITION "LMF_RUN_IOV_10" VALUES LESS THAN (1158000) 
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_RUN_IOV_11" VALUES LESS THAN (2316000)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

ALTER TABLE LMF_RUN_IOV
  ADD CONSTRAINT LMF_RUN_IOV_PK PRIMARY KEY (LMF_IOV_ID) USING INDEX LOCAL 
	ENABLE
/

ALTER TABLE LMF_RUN_IOV
  ADD CONSTRAINT LMF_RUN_IOV_UK UNIQUE (SEQ_ID, LMR, COLOR_ID, TRIG_TYPE)
/

ALTER TABLE LMF_RUN_IOV
  ADD CONSTRAINT LMF_RUN_IOV_FK1 FOREIGN KEY (TAG_ID)
  REFERENCES LMF_RUN_TAG(TAG_ID)
/

ALTER TABLE LMF_RUN_IOV
  ADD CONSTRAINT LMF_RUN_IOV_FK2 FOREIGN KEY (SEQ_ID)
  REFERENCES LMF_SEQ_DAT(SEQ_ID)
/

ALTER TABLE LMF_RUN_IOV
  ADD CONSTRAINT LMF_RUN_IOV_FK3 FOREIGN KEY (COLOR_ID)
  REFERENCES LMF_COLOR_DEF(COLOR_ID)
/

ALTER TABLE LMF_RUN_IOV
  ADD CONSTRAINT LMF_RUN_IOV_FK4 FOREIGN KEY (TRIG_TYPE)
  REFERENCES LMF_TRIG_TYPE_DEF
/

/*  LMF_LASER_CONFIG_DAT  */
CREATE TABLE LMF_LASER_CONFIG_DAT
(
  LMF_IOV_ID     NUMBER NOT NULL,
  LOGIC_ID       NUMBER NOT NULL,
  WAVELENGTH     NUMBER,
  VFE_GAIN       NUMBER,
  PN_GAIN        NUMBER,
  LSR_POWER      NUMBER,
  LSR_ATTENUATOR NUMBER,
  LSR_CURRENT    NUMBER,
  LSR_DELAY_1    NUMBER,
  LSR_DELAY_2    NUMBER
)
PARTITION BY RANGE ("LMF_IOV_ID")
(PARTITION "LMF_LASER_CONFIG_DAT_10" VALUES LESS THAN (1158000) 
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_LASER_CONFIG_DAT_11" VALUES LESS THAN (2316000)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

ALTER TABLE LMF_LASER_CONFIG_DAT
  ADD CONSTRAINT LMF_LASER_CONFIG_DAT_PK PRIMARY KEY (LMF_IOV_ID,LOGIC_ID)
	USING INDEX LOCAL ENABLE	
/

ALTER TABLE LMF_LASER_CONFIG_DAT
  ADD CONSTRAINT LMF_LASER_CONFIG_DAT_FK1 FOREIGN KEY (LMF_IOV_ID)
  REFERENCES LMF_RUN_IOV(LMF_IOV_ID)
/

/*  LMF_RUN_DAT  */
CREATE TABLE LMF_RUN_DAT
(
  LMF_IOV_ID   NUMBER NOT NULL,
  LOGIC_ID     NUMBER NOT NULL,
  NEVENTS      NUMBER,
  QUALITY_FLAG NUMBER
)
PARTITION BY RANGE ("LMF_IOV_ID")
(PARTITION "LMF_RUN_DAT_10" VALUES LESS THAN (1158000)
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_RUN_DAT_11" VALUES LESS THAN (2316000)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

ALTER TABLE LMF_RUN_DAT
  ADD CONSTRAINT LMF_RUN_DAT_PK PRIMARY KEY (LMF_IOV_ID,LOGIC_ID)
	USING INDEX LOCAL ENABLE
/

ALTER TABLE LMF_RUN_DAT
  ADD CONSTRAINT LMF_RUN_DAT_FK1 FOREIGN KEY (LMF_IOV_ID)
  REFERENCES LMF_RUN_IOV(LMF_IOV_ID)
/


/*  LMF_TEST_PULSE_CONFIG_DAT  */
CREATE TABLE LMF_TEST_PULSE_CONFIG_DAT
(
  LMF_IOV_ID NUMBER NOT NULL,
  LOGIC_ID   NUMBER NOT NULL,
  VFE_GAIN   NUMBER,
  DAC_MGPA   NUMBER,
  PN_GAIN    NUMBER,
  PN_VINJ    NUMBER
)
PARTITION BY RANGE ("LMF_IOV_ID")
(PARTITION "LMF_TEST_PULSE_CONFIG_DAT_10" VALUES LESS THAN (1158000)
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_TEST_PULSE_CONFIG_DAT_11" VALUES LESS THAN (2316000)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

ALTER TABLE LMF_TEST_PULSE_CONFIG_DAT
  ADD CONSTRAINT LMF_TEST_PULSE_CONFIG_DAT_PK PRIMARY KEY (LMF_IOV_ID,LOGIC_ID) USING INDEX LOCAL ENABLE
/

ALTER TABLE LMF_TEST_PULSE_CONFIG_DAT
  ADD CONSTRAINT LMF_TEST_PULSE_CONFIG_DAT_FK1 FOREIGN KEY (LMF_IOV_ID)
  REFERENCES LMF_RUN_IOV(LMF_IOV_ID)
/


/* LMF_PRIM_VERS */
CREATE TABLE LMF_PRIM_VERS
(
  VERS         NUMBER,
  DB_TIMESTAMP TIMESTAMP DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL,
  DESCR        VARCHAR2(100),
  CONSTRAINTS LMF_PRIM_VERS_PK  PRIMARY KEY(VERS) USING INDEX LOCAL ENABLE
)
/

INSERT INTO LMF_PRIM_VERS VALUES (0, DEFAULT, 'none');
INSERT INTO LMF_PRIM_VERS VALUES (1, DEFAULT, 'default');
INSERT INTO LMF_PRIM_VERS VALUES (2, DEFAULT, 'vers. 2');
INSERT INTO LMF_PRIM_VERS VALUES (3, DEFAULT, 'vers. 3');
INSERT INTO LMF_PRIM_VERS VALUES (4, DEFAULT, 'vers. 4');

/*  LMF_CLS_XXX_DAT  */
CREATE TABLE LMF_CLS_BLUE_DAT
(
  LMF_IOV_ID        NUMBER NOT NULL,
  LOGIC_ID          NUMBER NOT NULL,
  LMF_IOV_ID_REF    NUMBER,
  MEAN              NUMBER, 
  NORM              NUMBER, 
  RMS               NUMBER, 
  NEVT		    NUMBER,
  ENORM             NUMBER,
  FLAG              NUMBER,
  FLAGNORM          NUMBER,
  VMIN              NUMBER,
  VMAX              NUMBER,
  CONSTRAINTS LMF_CLS_BLUE_DAT_FK1 FOREIGN KEY(VMIN)
    REFERENCES LMF_PRIM_VERS(VERS),
  CONSTRAINTS LMF_CLS_BLUE_DAT_FK2 FOREIGN KEY(VMAX)
    REFERENCES LMF_PRIM_VERS(VERS),
  CONSTRAINTS LMF_CLS_BLUE_DAT_FK3 FOREIGN KEY(LMF_IOV_ID)
    REFERENCES LMF_RUN_IOV(LMF_IOV_ID)
)
PARTITION BY RANGE ("LMF_IOV_ID")
(PARTITION "LMF_CLS_BLUE_DAT_10" VALUES LESS THAN (1158000)
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_CLS_BLUE_DAT_11" VALUES LESS THAN (2316000)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

CREATE TABLE LMF_CLS_IR_DAT
(
  LMF_IOV_ID        NUMBER NOT NULL,
  LOGIC_ID          NUMBER NOT NULL,
  LMF_IOV_ID_REF    NUMBER,
  MEAN              NUMBER, 
  NORM              NUMBER, 
  RMS               NUMBER, 
  NEVT		    NUMBER,
  ENORM             NUMBER,
  FLAG              NUMBER,
  FLAGNORM          NUMBER,
  VMIN              NUMBER,
  VMAX              NUMBER,
  CONSTRAINTS LMF_CLS_IR_DAT_FK1 FOREIGN KEY(VMIN)
    REFERENCES LMF_PRIM_VERS(VERS),
  CONSTRAINTS LMF_CLS_IR_DAT_FK2 FOREIGN KEY(VMAX)
    REFERENCES LMF_PRIM_VERS(VERS),
  CONSTRAINTS LMF_CLS_IR_DAT_FK3 FOREIGN KEY(LMF_IOV_ID)
    REFERENCES LMF_RUN_IOV(LMF_IOV_ID)
)
PARTITION BY RANGE ("LMF_IOV_ID")
(PARTITION "LMF_CLS_IR_DAT_10" VALUES LESS THAN (1158000)
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_CLS_IR_DAT_11" VALUES LESS THAN (2316000)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

ALTER TABLE LMF_CLS_IR_DAT
  ADD CONSTRAINT LMF_CLS_IR_DAT_PK PRIMARY KEY (LMF_IOV_ID,LOGIC_ID,
        VMIN) USING INDEX LOCAL ENABLE
/

/* LMF_CORR_VERS */
CREATE TABLE LMF_CORR_VERS
(
  VERS NUMBER,
  DB_TIMESTAMP TIMESTAMP DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL,
  DESCR VARCHAR2(100),
  CONSTRAINTS LMF_CORR_VERS_PK PRIMARY KEY(VERS) USING INDEX LOCAL ENABLE
)
/

INSERT INTO LMF_CORR_VERS VALUES (0, DEFAULT, 'none');
INSERT INTO LMF_CORR_VERS VALUES (1, DEFAULT, 'default');

/* LMF_IOV */
CREATE TABLE LMF_IOV
(
  IOV_ID    NUMBER,
  IOV_START DATE,
  IOV_STOP  DATE,
  VMIN      NUMBER,
  VMAX      NUMBER,
  CONSTRAINTS LMF_IOV_PK PRIMARY KEY(IOV_ID) USING INDEX LOCAL ENABLE,
  CONSTRAINTS LMF_IOV_FK1 FOREIGN KEY(VMIN) REFERENCES LMF_CORR_VERS(VERS),
  CONSTRAINTS LMF_IOV_FK2 FOREIGN KEY(VMAX) REFERENCES LMF_CORR_VERS(VERS)
)
PARTITION BY RANGE ("IOV_ID")
(PARTITION "LMF_IOV_10" VALUES LESS THAN (2400)
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_IOV_11" VALUES LESS THAN (4800)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

/* LMF_LMR_SUB_IOV */
CREATE TABLE LMF_LMR_SUB_IOV
(
  LMR_SUB_IOV_ID     NUMBER,
  IOV_ID             NUMBER,
  T1                 DATE,
  T2                 DATE,
  T3                 DATE,
  CONSTRAINTS LMF_LMR_SUB_IOV_PK PRIMARY KEY(LMR_SUB_IOV_ID)
	USING INDEX LOCAL ENABLE,
  CONSTRAINTS LMF_LMR_SUB_IOV_FK FOREIGN KEY(IOV_ID) REFERENCES LMF_IOV
)
PARTITION BY RANGE ("LMR_SUB_IOV_ID")
(PARTITION "LMF_LMR_SUB_IOV_10" VALUES LESS THAN (2400)
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_LMR_SUB_IOV_11" VALUES LESS THAN (4800)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

/* LMF_CORR_COEF_DAT */
CREATE TABLE LMF_CORR_COEF_DAT
(
  LMR_SUB_IOV_ID NUMBER NOT NULL,
  LOGIC_ID       NUMBER NOT NULL,
  P1             NUMBER, -- OR BINARY_FLOAT
  P2             NUMBER, -- OR BINARY_FLOAT
  P3             NUMBER, -- OR BINARY_FLOAT
  P1_ERR         NUMBER, -- OR BINARY_FLOAT
  P2_ERR         NUMBER, -- OR BINARY_FLOAT
  P3_ERR         NUMBER, -- OR BINARY_FLOAT
  FLAG           NUMBER,
  SEQ_ID	 NUMBER
)
PARTITION BY RANGE ("LMR_SUB_IOV_ID")
(PARTITION "LMF_CORR_COEF_DAT_10" VALUES LESS THAN (2400)
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_CORR_COEF_DAT_11" VALUES LESS THAN (4800)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA,
 PARTITION "LMF_CORR_COEF_DAT_11b" VALUES LESS THAN (10000) /* added 30-05-2011 */
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

ALTER TABLE LMF_CORR_COEF_DAT
  ADD CONSTRAINT LMF_CORR_COEF_DAT_PK PRIMARY KEY (LOGIC_ID, LMR_SUB_IOV_ID)
	USING INDEX LOCAL ENABLE
/

ALTER TABLE LMF_CORR_COEF_DAT
  ADD CONSTRAINT LMF_CORR_COEF_DAT_FK1 FOREIGN KEY (LMR_SUB_IOV_ID)
  REFERENCES LMF_LMR_SUB_IOV
/

ALTER TABLE LMF_CORR_COEF_DAT
 ADD CONSTRAINT LMF_CORR_COEF_DAT_FK2 FOREIGN KEY (SEQ_ID)
 REFERENCES LMF_SEQ_DAT
/

/*  RUN_LASERRUN_CONFIG_DAT  */
CREATE TABLE RUN_LASERRUN_CONFIG_DAT
(
  IOV_ID              NUMBER NOT NULL,
  LOGIC_ID            NUMBER NOT NULL,
  LASER_SEQUENCE_TYPE VARCHAR2(20),
  LASER_SEQUENCE_COND VARCHAR2(20)
)
/

ALTER TABLE RUN_LASERRUN_CONFIG_DAT
  ADD CONSTRAINT RUN_LASERRUN_CONFIG_DAT_PK UNIQUE (IOV_ID,LOGIC_ID)
/

-- BLUE LASER PRIMITIVE TABLES

/*  LMF_LASER_BLUE_PN_PRIM_DAT  */
CREATE TABLE LMF_LASER_BLUE_PN_PRIM_DAT
(
  LMF_IOV_ID        NUMBER NOT NULL,
  LOGIC_ID          NUMBER NOT NULL,
  SHAPE_COR_PN      NUMBER, 
  MEAN              NUMBER, -- or BINARY_FLOAT
  RMS               NUMBER, -- or BINARY_FLOAT
  M3                NUMBER, -- or BINARY_FLOAT
  PNA_OVER_PNB_MEAN NUMBER, -- or BINARY_FLOAT
  PNA_OVER_PNB_RMS  NUMBER, -- or BINARY_FLOAT
  PNA_OVER_PNB_M3   NUMBER, -- or BINARY_FLOAT
  FLAG              NUMBER,
  VMIN              NUMBER,
  VMAX              NUMBER,
  CONSTRAINTS LMF_LASER_BLUE_PN_PRIM_DAT_FK1 FOREIGN KEY(VMIN) 
    REFERENCES LMF_PRIM_VERS(VERS),
  CONSTRAINTS LMF_LASER_BLUE_PN_PRIM_DAT_FK2 FOREIGN KEY(VMAX) 
    REFERENCES LMF_PRIM_VERS(VERS)
)
PARTITION BY RANGE ("LMF_IOV_ID")
(PARTITION "LMF_LASER_BLUE_PN_PRIM_DAT_10" VALUES LESS THAN (1158000)
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_LASER_BLUE_PN_PRIM_DAT_11" VALUES LESS THAN (2316000)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

ALTER TABLE LMF_LASER_BLUE_PN_PRIM_DAT
  ADD CONSTRAINT LMF_LASER_BLUE_PN_PRIM_DAT_PK PRIMARY KEY (LMF_IOV_ID,LOGIC_ID,
	VMIN) USING INDEX LOCAL ENABLE
/

ALTER TABLE LMF_LASER_BLUE_PN_PRIM_DAT
  ADD CONSTRAINT LMF_LASER_BLUE_PN_PRIM_DAT_FK3 FOREIGN KEY (LMF_IOV_ID)
  REFERENCES LMF_RUN_IOV
/

/*  LMF_LASER_BLUE_PULSE_DAT  */
CREATE TABLE LMF_LASER_BLUE_PULSE_DAT
(
  LMF_IOV_ID  NUMBER NOT NULL,
  LOGIC_ID    NUMBER NOT NULL,
  FIT_METHOD  NUMBER,
  MTQ_AMPL    NUMBER, -- OR BINARY_FLOAT
  MTQ_TIME    NUMBER, -- OR BINARY_FLOAT
  MTQ_RISE    NUMBER, -- OR BINARY_FLOAT
  MTQ_FWHM    NUMBER, -- OR BINARY_FLOAT
  MTQ_FW20    NUMBER, -- OR BINARY_FLOAT
  MTQ_FW80    NUMBER, -- OR BINARY_FLOAT
  MTQ_SLIDING NUMBER,  -- OR BINARY_FLOAT
  VMIN        NUMBER,
  VMAX        NUMBER,
  CONSTRAINTS LMF_LASER_BLUE_PULSE_DAT_FK1 FOREIGN KEY(VMIN) 
    REFERENCES LMF_PRIM_VERS(VERS),
  CONSTRAINTS LMF_LASER_BLUE_PULSE_DAT_FK2 FOREIGN KEY(VMAX) 
    REFERENCES LMF_PRIM_VERS(VERS)
)
PARTITION BY RANGE ("LMF_IOV_ID")
(PARTITION "LMF_LASER_BLUE_PULSE_DAT_10" VALUES LESS THAN (1158000)
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_LASER_BLUE_PULSE_DAT_11" VALUES LESS THAN (2316000)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

ALTER TABLE LMF_LASER_BLUE_PULSE_DAT
  ADD CONSTRAINT LMF_LASER_BLUE_PULSE_DAT_PK PRIMARY KEY (LMF_IOV_ID,LOGIC_ID,
	VMIN) USING INDEX LOCAL ENABLE
/

ALTER TABLE LMF_LASER_BLUE_PULSE_DAT
  ADD CONSTRAINT LMF_LASER_BLUE_PULSE_DAT_FK3 FOREIGN KEY (LMF_IOV_ID)
  REFERENCES LMF_RUN_IOV(LMF_IOV_ID)
/

/*  LMF_TESTPULSE_PN_PRIM_DAT */
CREATE TABLE LMF_TESTPULSE_PN_PRIM_DAT
(
  LMF_IOV_ID        NUMBER NOT NULL,
  LOGIC_ID          NUMBER NOT NULL,
  MEAN              NUMBER, -- or BINARY_FLOAT
  RMS               NUMBER, -- or BINARY_FLOAT
  M3                NUMBER, -- or BINARY_FLOAT
  FLAG              NUMBER,
  VMIN              NUMBER,
  VMAX              NUMBER,
  CONSTRAINTS LMF_TESTPULSE_PN_PRIM_DAT_FK1 FOREIGN KEY(VMIN) 
    REFERENCES LMF_PRIM_VERS(VERS),
  CONSTRAINTS LMF_TESTPULSE_PN_PRIM_DAT_FK2 FOREIGN KEY(VMAX) 
    REFERENCES LMF_PRIM_VERS(VERS)
)
PARTITION BY RANGE ("LMF_IOV_ID")
(PARTITION "LMF_TESTPULSE_PN_PRIM_DAT_10" VALUES LESS THAN (1158000)
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_TESTPULSE_PN_PRIM_DAT_11" VALUES LESS THAN (2316000)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

ALTER TABLE LMF_TESTPULSE_PN_PRIM_DAT
  ADD CONSTRAINT LMF_TESTPULSE_PN_DAT_PK PRIMARY KEY (LMF_IOV_ID,LOGIC_ID,
	VMIN) USING INDEX LOCAL ENABLE
/

ALTER TABLE LMF_TESTPULSE_PN_PRIM_DAT
  ADD CONSTRAINT LMF_TESTPULSE_PN_DAT_FK1 FOREIGN KEY (LMF_IOV_ID)
  REFERENCES LMF_RUN_IOV
/

/*  LMF_TESTPULSE_PRIM_DAT */
CREATE TABLE LMF_TESTPULSE_PRIM_DAT
(
  LMF_IOV_ID        NUMBER NOT NULL,
  LOGIC_ID          NUMBER NOT NULL,
  MEAN              NUMBER, -- or BINARY_FLOAT
  RMS               NUMBER, -- or BINARY_FLOAT
  M3                NUMBER, -- or BINARY_FLOAT
  FLAG              NUMBER,
  VMIN              NUMBER,
  VMAX              NUMBER,
  CONSTRAINTS LMF_TESTPULSE_PRIM_DAT_FK1 FOREIGN KEY(VMIN) 
    REFERENCES LMF_PRIM_VERS(VERS),
  CONSTRAINTS LMF_TESTPULSE_PRIM_DAT_FK2 FOREIGN KEY(VMAX) 
    REFERENCES LMF_PRIM_VERS(VERS)
)
PARTITION BY RANGE ("LMF_IOV_ID")
(PARTITION "LMF_TESTPULSE_PRIM_DAT_10" VALUES LESS THAN (1158000)
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_TESTPULSE_PRIM_DAT_11" VALUES LESS THAN (2316000)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

ALTER TABLE LMF_TESTPULSE_PRIM_DAT
  ADD CONSTRAINT LMF_TESTPULSE_PRIM_DAT_PK PRIMARY KEY (LMF_IOV_ID,LOGIC_ID,
	VMIN) USING INDEX LOCAL ENABLE
/

ALTER TABLE LMF_TESTPULSE_PRIM_DAT
  ADD CONSTRAINT LMF_TESTPULSE_PRIM_DAT_FK3 FOREIGN KEY (LMF_IOV_ID)
  REFERENCES LMF_RUN_IOV
/

/*  LMF_LASER_BLUE_PRIM_DAT: done  */
CREATE TABLE LMF_LASER_BLUE_PRIM_DAT
(
  LMF_IOV_ID        NUMBER NOT NULL,
  LOGIC_ID          NUMBER NOT NULL,
  FLAG              NUMBER,
  MEAN              NUMBER, -- OR BINARY_FLOAT
  RMS               NUMBER, -- OR BINARY_FLOAT
  M3                NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNA_MEAN NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNA_RMS  NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNA_M3   NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNB_MEAN NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNB_RMS  NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNB_M3   NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PN_MEAN  NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PN_RMS   NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PN_M3    NUMBER, -- OR BINARY_FLOAT
  ALPHA             NUMBER, -- OR BINARY_FLOAT
  BETA              NUMBER, -- OR BINARY_FLOAT
  SHAPE_COR         NUMBER, -- OR BINARY_FLOAT
  VMIN              NUMBER,
  VMAX              NUMBER,
  CONSTRAINTS LMF_LASER_BLUE_PRIM_DAT_FK1 FOREIGN KEY(VMIN) 
    REFERENCES LMF_PRIM_VERS(VERS),
  CONSTRAINTS LMF_LASER_BLUE_PRIM_DAT_FK2 FOREIGN KEY(VMAX) 
    REFERENCES LMF_PRIM_VERS(VERS)
)
PARTITION BY RANGE ("LMF_IOV_ID")
(PARTITION "LMF_LASER_BLUE_PRIM_DAT_10" VALUES LESS THAN (1158000)
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_LASER_BLUE_PRIM_DAT_11" VALUES LESS THAN (2316000)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

ALTER TABLE LMF_LASER_BLUE_PRIM_DAT
  ADD CONSTRAINT LMF_LASER_BLUE_PRIM_DAT_PK PRIMARY KEY (LMF_IOV_ID,LOGIC_ID,
	VMIN) USING INDEX LOCAL ENABLE
/

ALTER TABLE LMF_LASER_BLUE_PRIM_DAT
  ADD CONSTRAINT LMF_LASER_BLUE_PRIM_DAT_FK3 FOREIGN KEY (LMF_IOV_ID)
  REFERENCES LMF_RUN_IOV(LMF_IOV_ID)
/

-- IR LASER PRIMITIVE TABLES

/*  LMF_LASER_IR_PN_PRIM_DAT  */
CREATE TABLE LMF_LASER_IR_PN_PRIM_DAT
(
  LMF_IOV_ID        NUMBER NOT NULL,
  LOGIC_ID          NUMBER NOT NULL,
  SHAPE_COR_PN      NUMBER, 
  MEAN              NUMBER, -- or BINARY_FLOAT
  RMS               NUMBER, -- or BINARY_FLOAT
  M3                NUMBER, -- or BINARY_FLOAT
  PNA_OVER_PNB_MEAN NUMBER, -- or BINARY_FLOAT
  PNA_OVER_PNB_RMS  NUMBER, -- or BINARY_FLOAT
  PNA_OVER_PNB_M3   NUMBER, -- or BINARY_FLOAT
  FLAG              NUMBER,
  VMIN              NUMBER,
  VMAX              NUMBER,
  CONSTRAINTS LMF_LASER_IR_PN_PRIM_DAT_FK1 FOREIGN KEY(VMIN) 
    REFERENCES LMF_PRIM_VERS(VERS),
  CONSTRAINTS LMF_LASER_IR_PN_PRIM_DAT_FK2 FOREIGN KEY(VMAX) 
    REFERENCES LMF_PRIM_VERS(VERS)
)
PARTITION BY RANGE ("LMF_IOV_ID")
(PARTITION "LMF_LASER_IR_PN_PRIM_DAT_10" VALUES LESS THAN (1158000)
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_LASER_IR_PN_PRIM_DAT_11" VALUES LESS THAN (2316000)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

ALTER TABLE LMF_LASER_IR_PN_PRIM_DAT
  ADD CONSTRAINT LMF_LASER_IR_PN_PRIM_DAT_PK PRIMARY KEY (LMF_IOV_ID,LOGIC_ID,
	VMIN) USING INDEX LOCAL ENABLE
/

ALTER TABLE LMF_LASER_IR_PN_PRIM_DAT
  ADD CONSTRAINT LMF_LASER_IR_PN_PRIM_DAT_FK3 FOREIGN KEY (LMF_IOV_ID)
  REFERENCES LMF_RUN_IOV
/

/*  LMF_LASER_IR_PULSE_DAT  */
CREATE TABLE LMF_LASER_IR_PULSE_DAT
(
  LMF_IOV_ID  NUMBER NOT NULL,
  LOGIC_ID    NUMBER NOT NULL,
  FIT_METHOD  NUMBER,
  MTQ_AMPL    NUMBER, -- OR BINARY_FLOAT
  MTQ_TIME    NUMBER, -- OR BINARY_FLOAT
  MTQ_RISE    NUMBER, -- OR BINARY_FLOAT
  MTQ_FWHM    NUMBER, -- OR BINARY_FLOAT
  MTQ_FW20    NUMBER, -- OR BINARY_FLOAT
  MTQ_FW80    NUMBER, -- OR BINARY_FLOAT
  MTQ_SLIDING NUMBER, -- OR BINARY_FLOAT
  VMIN        NUMBER,
  VMAX        NUMBER,
  CONSTRAINTS LMF_LASER_IR_PULSE_DAT_FK1 FOREIGN KEY(VMIN) 
    REFERENCES LMF_PRIM_VERS(VERS),
  CONSTRAINTS LMF_LASER_IR_PULSE_DAT_FK2 FOREIGN KEY(VMAX) 
    REFERENCES LMF_PRIM_VERS(VERS)
)
PARTITION BY RANGE ("LMF_IOV_ID")
(PARTITION "LMF_LASER_IR_PULSE_DAT_10" VALUES LESS THAN (1158000)
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_LASER_IR_PULSE_DAT_11" VALUES LESS THAN (2316000)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

ALTER TABLE LMF_LASER_IR_PULSE_DAT
  ADD CONSTRAINT LMF_LASER_IR_PULSE_DAT_PK PRIMARY KEY (LMF_IOV_ID,LOGIC_ID,
	VMIN) USING INDEX LOCAL ENABLE
/

ALTER TABLE LMF_LASER_IR_PULSE_DAT
  ADD CONSTRAINT LMF_LASER_IR_PULSE_DAT_FK3 FOREIGN KEY (LMF_IOV_ID)
  REFERENCES LMF_RUN_IOV(LMF_IOV_ID)
/

/*  LMF_LASER_IR_PRIM_DAT  */
CREATE TABLE LMF_LASER_IR_PRIM_DAT
(
  LMF_IOV_ID        NUMBER NOT NULL,
  LOGIC_ID          NUMBER NOT NULL,
  FLAG              NUMBER,
  MEAN              NUMBER, -- OR BINARY_FLOAT
  RMS               NUMBER, -- OR BINARY_FLOAT
  M3                NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNA_MEAN NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNA_RMS  NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNA_M3   NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNB_MEAN NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNB_RMS  NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNB_M3   NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PN_MEAN  NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PN_RMS   NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PN_M3    NUMBER, -- OR BINARY_FLOAT
  ALPHA             NUMBER, -- OR BINARY_FLOAT
  BETA              NUMBER, -- OR BINARY_FLOAT
  SHAPE_COR         NUMBER, -- OR BINARY_FLOAT
  VMIN              NUMBER,
  VMAX              NUMBER,
  CONSTRAINTS LMF_LASER_IR_PRIM_DAT_FK1 FOREIGN KEY(VMIN) 
    REFERENCES LMF_PRIM_VERS(VERS),
  CONSTRAINTS LMF_LASER_IR_PRIM_DAT_FK2 FOREIGN KEY(VMAX) 
    REFERENCES LMF_PRIM_VERS(VERS)
)
PARTITION BY RANGE ("LMF_IOV_ID")
(PARTITION "LMF_LASER_IR_PRIM_DAT_10" VALUES LESS THAN (1158000)
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_LASER_IR_PRIM_DAT_11" VALUES LESS THAN (2316000)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

ALTER TABLE LMF_LASER_IR_PRIM_DAT
  ADD CONSTRAINT LMF_LASER_IR_PRIM_DAT_PK PRIMARY KEY (LMF_IOV_ID,LOGIC_ID,
	VMIN) USING INDEX LOCAL ENABLE
/

ALTER TABLE LMF_LASER_IR_PRIM_DAT
  ADD CONSTRAINT LMF_LASER_IR_PRIM_DAT_FK3 FOREIGN KEY (LMF_IOV_ID)
  REFERENCES LMF_RUN_IOV(LMF_IOV_ID)
/

-- BLUE LED PRIMITIVE TABLES

/*  LMF_LED_BLUE_PN_PRIM_DAT  */
CREATE TABLE LMF_LED_BLUE_PN_PRIM_DAT
(
  LMF_IOV_ID        NUMBER NOT NULL,
  LOGIC_ID          NUMBER NOT NULL,
  MEAN              NUMBER, -- or BINARY_FLOAT
  RMS               NUMBER, -- or BINARY_FLOAT
  M3                NUMBER, -- or BINARY_FLOAT
  PNA_OVER_PNB_MEAN NUMBER, -- or BINARY_FLOAT
  PNA_OVER_PNB_RMS  NUMBER, -- or BINARY_FLOAT
  PNA_OVER_PNB_M3   NUMBER, -- or BINARY_FLOAT
  FLAG              NUMBER,
  VMIN              NUMBER,
  VMAX              NUMBER,
  CONSTRAINTS LMF_LED_BLUE_PN_PRIM_DAT_FK1 FOREIGN KEY(VMIN) 
    REFERENCES LMF_PRIM_VERS(VERS),
  CONSTRAINTS LMF_LED_BLUE_PN_PRIM_DAT_FK2 FOREIGN KEY(VMAX) 
    REFERENCES LMF_PRIM_VERS(VERS)
)
PARTITION BY RANGE ("LMF_IOV_ID")
(PARTITION "LMF_LED_BLUE_PN_PRIM_DAT_10" VALUES LESS THAN (1158000)
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_LED_BLUE_PN_PRIM_DAT_11" VALUES LESS THAN (2316000)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

ALTER TABLE LMF_LED_BLUE_PN_PRIM_DAT
  ADD CONSTRAINT LMF_LED_BLUE_PN_PRIM_DAT_PK PRIMARY KEY (LMF_IOV_ID,LOGIC_ID,
	VMIN) USING INDEX LOCAL ENABLE 
/

ALTER TABLE LMF_LED_BLUE_PN_PRIM_DAT
  ADD CONSTRAINT LMF_LED_BLUE_PN_PRIM_DAT_FK3 FOREIGN KEY (LMF_IOV_ID)
  REFERENCES LMF_RUN_IOV
/

/*  LMF_LED_BLUE_PRIM_DAT  */
CREATE TABLE LMF_LED_BLUE_PRIM_DAT
(
  LMF_IOV_ID        NUMBER NOT NULL,
  LOGIC_ID          NUMBER NOT NULL,
  FLAG              NUMBER,
  MEAN              NUMBER, -- OR BINARY_FLOAT
  RMS               NUMBER, -- OR BINARY_FLOAT
  M3                NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNA_MEAN NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNA_RMS  NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNA_M3   NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNB_MEAN NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNB_RMS  NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNB_M3   NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PN_MEAN  NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PN_RMS   NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PN_M3    NUMBER, -- OR BINARY_FLOAT
  ALPHA             NUMBER, -- OR BINARY_FLOAT
  BETA              NUMBER, -- OR BINARY_FLOAT
  SHAPE_COR         NUMBER, -- OR BINARY_FLOAT
  VMIN              NUMBER,
  VMAX              NUMBER,
  CONSTRAINTS LMF_LED_BLUE_PRIM_DAT_FK1 FOREIGN KEY(VMIN) 
    REFERENCES LMF_PRIM_VERS(VERS),
  CONSTRAINTS LMF_LED_BLUE_PRIM_DAT_FK2 FOREIGN KEY(VMAX) 
    REFERENCES LMF_PRIM_VERS(VERS)
)
PARTITION BY RANGE ("LMF_IOV_ID")
(PARTITION "LMF_LED_BLUE_PRIM_DAT_10" VALUES LESS THAN (1158000)
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_LED_BLUE_PRIM_DAT_11" VALUES LESS THAN (2316000)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

ALTER TABLE LMF_LED_BLUE_PRIM_DAT
  ADD CONSTRAINT LMF_LED_BLUE_PRIM_DAT_PK PRIMARY KEY (LMF_IOV_ID,LOGIC_ID,
	VMIN) USING INDEX LOCAL ENABLE
/

ALTER TABLE LMF_LED_BLUE_PRIM_DAT
  ADD CONSTRAINT LMF_LED_BLUE_PRIM_DAT_FK3 FOREIGN KEY (LMF_IOV_ID)
  REFERENCES LMF_RUN_IOV(LMF_IOV_ID)
/

-- ORANGE LED PRIMITIVE TABLES

/*  LMF_LED_ORANGE_PN_PRIM_DAT  */
CREATE TABLE LMF_LED_ORANGE_PN_PRIM_DAT
(
  LMF_IOV_ID        NUMBER NOT NULL,
  LOGIC_ID          NUMBER NOT NULL,
  MEAN              NUMBER, -- or BINARY_FLOAT
  RMS               NUMBER, -- or BINARY_FLOAT
  M3                NUMBER, -- or BINARY_FLOAT
  PNA_OVER_PNB_MEAN NUMBER, -- or BINARY_FLOAT
  PNA_OVER_PNB_RMS  NUMBER, -- or BINARY_FLOAT
  PNA_OVER_PNB_M3   NUMBER, -- or BINARY_FLOAT
  FLAG              NUMBER,
  VMIN              NUMBER,
  VMAX              NUMBER,
  CONSTRAINTS LMF_LED_ORANGE_PN_PRIM_DAT_FK1 FOREIGN KEY(VMIN) 
    REFERENCES LMF_PRIM_VERS(VERS),
  CONSTRAINTS LMF_LED_ORANGE_PN_PRIM_DAT_FK2 FOREIGN KEY(VMAX) 
    REFERENCES LMF_PRIM_VERS(VERS)
)
PARTITION BY RANGE ("LMF_IOV_ID")
(PARTITION "LMF_LED_ORANGE_PN_PRIM_DAT_10" VALUES LESS THAN (1158000)
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_LED_ORANGE_PN_PRIM_DAT_11" VALUES LESS THAN (2316000)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

ALTER TABLE LMF_LED_ORANGE_PN_PRIM_DAT
  ADD CONSTRAINT LMF_LED_ORANGE_PN_PRIM_DAT_PK PRIMARY KEY (LMF_IOV_ID,LOGIC_ID,
	VMIN) USING INDEX LOCAL ENABLE
/

ALTER TABLE LMF_LED_ORANGE_PN_PRIM_DAT
  ADD CONSTRAINT LMF_LED_ORANGE_PN_PRIM_DAT_FK3 FOREIGN KEY (LMF_IOV_ID)
  REFERENCES LMF_RUN_IOV
/

/*  LMF_LED_ORANGE_PRIM_DAT  */
CREATE TABLE LMF_LED_ORANGE_PRIM_DAT
(
  LMF_IOV_ID        NUMBER NOT NULL,
  LOGIC_ID          NUMBER NOT NULL,
  FLAG              NUMBER,
  MEAN              NUMBER, -- OR BINARY_FLOAT
  RMS               NUMBER, -- OR BINARY_FLOAT
  M3                NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNA_MEAN NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNA_RMS  NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNA_M3   NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNB_MEAN NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNB_RMS  NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PNB_M3   NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PN_MEAN  NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PN_RMS   NUMBER, -- OR BINARY_FLOAT
  APD_OVER_PN_M3    NUMBER, -- OR BINARY_FLOAT
  ALPHA             NUMBER, -- OR BINARY_FLOAT
  BETA              NUMBER, -- OR BINARY_FLOAT
  SHAPE_COR         NUMBER, -- OR BINARY_FLOAT
  VMIN              NUMBER,
  VMAX              NUMBER,
  CONSTRAINTS LMF_LED_ORANGE_PRIM_DAT_FK1 FOREIGN KEY(VMIN) 
    REFERENCES LMF_PRIM_VERS(VERS),
  CONSTRAINTS LMF_LED_ORANGE_PRIM_DAT_FK2 FOREIGN KEY(VMAX) 
    REFERENCES LMF_PRIM_VERS(VERS)
)
PARTITION BY RANGE ("LMF_IOV_ID")
(PARTITION "LMF_LED_ORANGE_PRIM_DAT_10" VALUES LESS THAN (1158000)
 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
 PARTITION "LMF_LED_ORANGE_PRIM_DAT_11" VALUES LESS THAN (2316000)
 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

ALTER TABLE LMF_LED_ORANGE_PRIM_DAT
  ADD CONSTRAINT LMF_LED_ORANGE_PRIM_DAT_PK PRIMARY KEY (LMF_IOV_ID,LOGIC_ID,
	VMIN) USING INDEX LOCAL ENABLE
/

ALTER TABLE LMF_LED_ORANGE_PRIM_DAT
  ADD CONSTRAINT LMF_LED_ORANGE_PRIM_DAT_FK3 FOREIGN KEY (LMF_IOV_ID)
  REFERENCES LMF_RUN_IOV(LMF_IOV_ID)
/

PROMPT "Creating indices";

CREATE INDEX LMF_RUN_STRT_IX ON LMF_RUN_IOV(SUBRUN_START) LOCAL
  (PARTITION LMF_RUN_IOV_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
   PARTITION LMF_RUN_IOV_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

CREATE INDEX LMF_RUN_END_IX ON LMF_RUN_IOV(SUBRUN_END) LOCAL
  (PARTITION LMF_RUN_IOV_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
   PARTITION LMF_RUN_IOV_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

CREATE INDEX LMF_SEQ_DAT_IX ON LMF_SEQ_DAT(RUN_IOV_ID) LOCAL
  (PARTITION LMF_SEQ_DAT_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
   PARTITION LMF_SEQ_DAT_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

CREATE INDEX LMF_IOV_START_IX ON LMF_IOV(IOV_START) LOCAL
  (PARTITION LMF_IOV_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
   PARTITION LMF_IOV_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/
CREATE INDEX LMF_IOV_STOP_IX ON LMF_IOV(IOV_STOP) LOCAL
  (PARTITION LMF_IOV_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
   PARTITION LMF_IOV_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

CREATE INDEX RUN_LASRRUN_CONFIG_DAT_IX ON RUN_LASERRUN_CONFIG_DAT(IOV_ID)
/

CREATE INDEX LMF_LMR_SUB_IOV_IX ON LMF_LMR_SUB_IOV(T1) LOCAL
  (PARTITION LMF_LMR_SUB_IOV_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
   PARTITION LMF_LMR_SUB_IOV_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

/* FOREIGN KEYS INDEXES */

CREATE INDEX LMF_CLS_BLUE_DAT_FIX1 ON LMF_CLS_BLUE_DAT(LMF_IOV_ID) LOCAL
/
CREATE INDEX LMF_CLS_BLUE_DAT_FIX2 ON LMF_CLS_BLUE_DAT(LMF_IOV_ID_REF) LOCAL
  (PARTITION LMF_CLS_BLUE_DAT_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
   PARTITION LMF_CLS_BLUE_DAT_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

CREATE INDEX LMF_CLS_IR_DAT_FIX1 ON LMF_CLS_IR_DAT(LMF_IOV_ID) LOCAL
/
CREATE INDEX LMF_CLS_IR_DAT_FIX2 ON LMF_CLS_IR_DAT(LMF_IOV_ID_REF) LOCAL
  (PARTITION LMF_CLS_IR_DAT_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
   PARTITION LMF_CLS_IR_DAT_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

CREATE INDEX LMF_RUN_IOV_FIX1 ON LMF_RUN_IOV(SEQ_ID) LOCAL
  (PARTITION LMF_RUN_IOV_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
   PARTITION LMF_RUN_IOV_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/
CREATE INDEX LMF_RUN_IOV_FIX2 ON LMF_RUN_IOV(TAG_ID) LOCAL
  (PARTITION LMF_RUN_IOV_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
   PARTITION LMF_RUN_IOV_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/
CREATE INDEX LMF_RUN_IOV_FIX3 ON LMF_RUN_IOV(COLOR_ID) LOCAL
  (PARTITION LMF_RUN_IOV_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
   PARTITION LMF_RUN_IOV_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/
CREATE INDEX LMF_RUN_IOV_FIX4 ON LMF_RUN_IOV(TRIG_TYPE) LOCAL
  (PARTITION LMF_RUN_IOV_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
   PARTITION LMF_RUN_IOV_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

CREATE INDEX LMF_LASER_BLUE_PULSE_DAT_FIX ON LMF_LASER_BLUE_PULSE_DAT(LMF_IOV_ID) LOCAL
/

CREATE INDEX LMF_LASER_CONFIG_DAT_FIX ON LMF_LASER_CONFIG_DAT(LMF_IOV_ID) LOCAL
/

CREATE INDEX LMF_RUN_DAT_FIX ON LMF_RUN_DAT(LMF_IOV_ID) LOCAL
/

CREATE INDEX LMF_TEST_PULSE_CONFIG_DAT_FIX ON LMF_TEST_PULSE_CONFIG_DAT(LMF_IOV_ID) LOCAL
/   

CREATE INDEX LMF_LMR_SUB_IOV_FIX ON LMF_LMR_SUB_IOV(IOV_ID) LOCAL
  (PARTITION LMF_LMR_SUB_IOV_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
   PARTITION LMF_LMR_SUB_IOV_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

CREATE INDEX LMF_CORR_COEF_DAT_FIX1 ON LMF_CORR_COEF_DAT(LMR_SUB_IOV_ID) LOCAL
  (PARTITION LMF_CORR_COEF_DAT_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
   PARTITION LMF_CORR_COEF_DAT_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

CREATE INDEX LMF_CORR_COEF_DAT_FIX2 ON LMF_CORR_COEF_DAT(SEQ_ID) LOCAL
  (PARTITION LMF_CORR_COEF_DAT_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA,
   PARTITION LMF_CORR_COEF_DAT_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

CREATE INDEX LMF_LASER_BLUE_PN_PRIM_DAT_FIX ON LMF_LASER_BLUE_PN_PRIM_DAT(LMF_IOV_ID) LOCAL
/

CREATE INDEX LMF_TESTPULSE_PN_PRIM_DAT_FIX ON LMF_TESTPULSE_PN_PRIM_DAT(LMF_IOV_ID) LOCAL
/

CREATE INDEX LMF_TESTPULSE_PRIM_DAT_FIX ON LMF_TESTPULSE_PRIM_DAT(LMF_IOV_ID) LOCAL
/

CREATE INDEX LMF_LASER_BLUE_PRIM_DAT_FIX ON LMF_LASER_BLUE_PRIM_DAT(LMF_IOV_ID) LOCAL
/

CREATE INDEX LMF_LASER_IR_PN_PRIM_DAT_FIX ON LMF_LASER_IR_PN_PRIM_DAT(LMF_IOV_ID) LOCAL
/

CREATE INDEX LMF_LASER_IR_PULSE_DAT_FIX ON LMF_LASER_IR_PULSE_DAT(LMF_IOV_ID) LOCAL
/

CREATE INDEX LMF_LASER_IR_PRIM_DAT_FIX ON LMF_LASER_IR_PRIM_DAT(LMF_IOV_ID) LOCAL
/

CREATE INDEX LMF_LED_BLUE_PN_PRIM_DAT_FIX ON LMF_LED_BLUE_PN_PRIM_DAT(LMF_IOV_ID) LOCAL
/

CREATE INDEX LMF_LED_BLUE_PRIM_DAT_FIX ON LMF_LED_BLUE_PRIM_DAT(LMF_IOV_ID) LOCAL
/

/* VMIN, VMAX indices */
CREATE INDEX LMF_SEQ_FKIX ON LMF_SEQ_DAT(VMIN) LOCAL 
  (PARTITION LMF_SEQ_DAT_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA, 
   PARTITION LMF_SEQ_DAT_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/
CREATE INDEX LMF_CLS_BLUE_FKIX ON LMF_CLS_BLUE_DAT(VMIN) LOCAL 
  (PARTITION LMF_CLS_BLUE_DAT_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA, 
   PARTITION LMF_CLS_BLUE_DAT_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/
CREATE INDEX LMF_CLS_IR_FKIX ON LMF_CLS_IR_DAT(VMIN) LOCAL 
  (PARTITION LMF_CLS_IR_DAT_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA, 
   PARTITION LMF_CLS_IR_DAT_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/
CREATE INDEX LMF_IOVFKIX ON LMF_IOV(VMIN) LOCAL 
  (PARTITION LMF_IOV10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA, 
   PARTITION LMF_IOV11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/
CREATE INDEX LMF_LASER_BLUE_PULSE_FKIX ON LMF_LASER_BLUE_PULSE_DAT(VMIN) LOCAL 
  (PARTITION LMF_LASER_BLUE_PULSE_DAT_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA, 
   PARTITION LMF_LASER_BLUE_PULSE_DAT_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/
CREATE INDEX LMF_LASER_BLUE_PN_PRIM_FKIX ON LMF_LASER_BLUE_PN_PRIM_DAT(VMIN) LOCAL 
  (PARTITION LMF_LASER_BLUE_PN_PRIM_DAT_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA, 
   PARTITION LMF_LASER_BLUE_PN_PRIM_DAT_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/
CREATE INDEX LMF_LASER_BLUE_PRIM_FKIX ON LMF_LASER_BLUE_PRIM_DAT(VMIN) LOCAL 
  (PARTITION LMF_LASER_BLUE_PRIM_DAT_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA, 
   PARTITION LMF_LASER_BLUE_PRIM_DAT_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/
CREATE INDEX LMF_LED_BLUE_PN_PRIM_FKIX ON LMF_LED_BLUE_PN_PRIM_DAT(VMIN) LOCAL 
  (PARTITION LMF_LED_BLUE_PN_PRIM_DAT_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA, 
   PARTITION LMF_LED_BLUE_PN_PRIM_DAT_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/
CREATE INDEX LMF_LED_BLUE_PRIM_FKIX ON LMF_LED_BLUE_PRIM_DAT(VMIN) LOCAL 
  (PARTITION LMF_LED_BLUE_PRIM_DAT_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA, 
   PARTITION LMF_LED_BLUE_PRIM_DAT_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/
CREATE INDEX LMF_LED_ORANGE_PN_PRIM_FKIX ON LMF_LED_ORANGE_PN_PRIM_DAT(VMIN) LOCAL 
  (PARTITION LMF_LED_ORANGE_PN_PRIM_DAT_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA, 
   PARTITION LMF_LED_ORANGE_PN_PRIM_DAT_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/
CREATE INDEX LMF_LED_ORANGE_PRIM_FKIX ON LMF_LED_ORANGE_PRIM_DAT(VMIN) LOCAL 
  (PARTITION LMF_LED_ORANGE_PRIM_DAT_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA, 
   PARTITION LMF_LED_ORANGE_PRIM_DAT_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/
CREATE INDEX LMF_LASER_IR_PULSE_FKIX ON LMF_LASER_IR_PULSE_DAT(VMIN) LOCAL 
  (PARTITION LMF_LASER_IR_PULSE_DAT_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA, 
   PARTITION LMF_LASER_IR_PULSE_DAT_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/
CREATE INDEX LMF_LASER_IR_PN_PRIM_FKIX ON LMF_LASER_IR_PN_PRIM_DAT(VMIN) LOCAL 
  (PARTITION LMF_LASER_IR_PN_PRIM_DAT_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA, 
   PARTITION LMF_LASER_IR_PN_PRIM_DAT_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/
CREATE INDEX LMF_LASER_IR_PRIM_FKIX ON LMF_LASER_IR_PRIM_DAT(VMIN) LOCAL 
  (PARTITION LMF_LASER_IR_PRIM_DAT_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA, 
   PARTITION LMF_LASER_IR_PRIM_DAT_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/
CREATE INDEX LMF_TESTPULSE_PN_PRIM_FKIX ON LMF_TESTPULSE_PN_PRIM_DAT(VMIN) LOCAL 
  (PARTITION LMF_TESTPULSE_PN_PRIM_DAT_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA, 
   PARTITION LMF_TESTPULSE_PN_PRIM_DAT_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/
CREATE INDEX LMF_TESTPULSE_PRIM_FKIX ON LMF_TESTPULSE_PRIM_DAT(VMIN) LOCAL 
  (PARTITION LMF_TESTPULSE_PRIM_DAT_10 TABLESPACE CMS_ECAL_LASER_COND_2010_DATA, 
   PARTITION LMF_TESTPULSE_PRIM_DAT_11 TABLESPACE CMS_ECAL_LASER_COND_2011_DATA)
/

CREATE OR REPLACE TRIGGER LMF_CHECK_SEQ_DAT_TG
  BEFORE INSERT	ON LMF_SEQ_DAT
  REFERENCING NEW AS new_seq_dat
  FOR EACH ROW
  DECLARE
  result NUMBER;
  BEGIN
    SELECT COUNT(IOV_ID) INTO result FROM CMS_ECAL_COND.RUN_IOV
        WHERE IOV_ID = :new_seq_dat.RUN_IOV_ID;
    IF result =	0 THEN
       RAISE_APPLICATION_ERROR(-20000, 'RUN.IOV_ID = ' ||
         :new_seq_dat.RUN_IOV_ID || ' does not exists!');
    END IF;
  END;
/

CREATE OR REPLACE TRIGGER LMF_CHECK_LASERCONF_TG
  BEFORE INSERT	ON RUN_LASERRUN_CONFIG_DAT
  REFERENCING NEW AS new_config_dat
  FOR EACH ROW
  DECLARE
  result NUMBER;
  BEGIN
    SELECT COUNT(IOV_ID) INTO result FROM CMS_ECAL_COND.RUN_IOV
        WHERE IOV_ID = :new_config_dat.IOV_ID;
    IF result =	0 THEN
       RAISE_APPLICATION_ERROR(-20000, 'RUN.IOV_ID = ' ||
         :new_config_dat.IOV_ID || ' does not exists!');
    END IF;
  END;
/

CREATE OR REPLACE TRIGGER LMF_CHECK_TRIPLETS_TG
  BEFORE INSERT ON LMF_LMR_SUB_IOV
  REFERENCING NEW AS new_sub_iov
  FOR EACH ROW
    BEGIN
      IF ((:new_sub_iov.t2 < :new_sub_iov.t1) OR
          (:new_sub_iov.t3 < :new_sub_iov.t2) OR
          (:new_sub_iov.t3 < :new_sub_iov.t1))
      THEN
        RAISE_APPLICATION_ERROR (
           num => -20000,
           msg =>       'Wrong triplets detected: T2 < T1 or T3 < T2');
      END IF;
    END;
/

/* create triggers to support 0 values in LMF_IOV_ID_REF and ensure referential
   integrity */

CREATE OR REPLACE TRIGGER LMF_CLS_BLUE_DAT_TG
  BEFORE INSERT ON LMF_CLS_BLUE_DAT
  REFERENCING NEW AS new
  FOR EACH ROW
    DECLARE
      rowcount NUMBER;
    BEGIN
      IF (:new.LMF_IOV_ID_REF != 0) THEN
        SELECT COUNT(LMF_IOV_ID) INTO rowcount FROM LMF_RUN_IOV WHERE LMF_IOV_ID = :n\
ew.LMF_IOV_ID_REF;
        IF (rowcount = 0) THEN
          RAISE_APPLICATION_ERROR(-20001, 'LMF_IOV_ID_REF = ' || :new.LMF_IOV_ID_REF \
|| ' not found');
        END IF;
      END IF;
    END;
/

CREATE OR REPLACE TRIGGER LMF_CLS_IR_DAT_TG
  BEFORE INSERT ON LMF_CLS_IR_DAT
  REFERENCING NEW AS new
  FOR EACH ROW
    DECLARE
      rowcount NUMBER;
    BEGIN
      IF (:new.LMF_IOV_ID_REF != 0) THEN
        SELECT COUNT(LMF_IOV_ID) INTO rowcount FROM LMF_RUN_IOV WHERE LMF_IOV_ID = :n\
ew.LMF_IOV_ID_REF;
        IF (rowcount = 0) THEN
          RAISE_APPLICATION_ERROR(-20001, 'LMF_IOV_ID_REF = ' || :new.LMF_IOV_ID_REF \
|| ' not found');
        END IF;
      END IF;
    END;
/
 
/*
CREATE SYNONYM RUN_TYPE_DEF FOR CMS_ECAL_COND.RUN_TYPE_DEF;
CREATE SYNONYM RUN_IOV FOR CMS_ECAL_COND.RUN_IOV;
CREATE SYNONYM VIEWDESCRIPTION FOR CMS_ECAL_COND.VIEWDESCRIPTION;
CREATE SYNONYM CHANNELVIEW FOR CMS_ECAL_COND.CHANNELVIEW;
CREATE SYNONYM LOCATION_DEF FOR CMS_ECAL_COND.LOCATION_DEF;
*/