EcalCondDB/sql/create_laser_core.sql

0001 /*
0002  * Creates the def, tag, and iov tables necessary to define a laser monitoring farm subrun
0003  * Requires: create_run_core.sql
0004  */
0005
0006
0007
0008 /* laser monitoring farm tag */
0009 CREATE TABLE lmf_run_tag (
0010   tag_id                NUMBER(10) NOT NULL,
0011   gen_tag               VARCHAR2(100) NOT NULL
0012 );
0013
0014 CREATE SEQUENCE lmf_run_tag_sq INCREMENT BY 1 START WITH 1;
0015
0016 ALTER TABLE lmf_run_tag ADD CONSTRAINT lmf_run_tag_pk PRIMARY KEY (tag_id);
0017 ALTER TABLE lmf_run_tag ADD CONSTRAINT lmf_run_tag_uk UNIQUE (gen_tag);
0018
0019
0020
0021 /* laser monitoring farm IOV */
0022 CREATE TABLE lmf_run_iov (
0023   iov_id                NUMBER(10) NOT NULL,
0024   tag_id                NUMBER(10) NOT NULL,
0025   run_iov_id            NUMBER(10) NOT NULL,
0026   subrun_num            NUMBER(10) NOT NULL,
0027   subrun_start          DATE NOT NULL,
0028   subrun_end            DATE NOT NULL,
0029   db_timestamp          TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
0030 );
0031
0032 CREATE SEQUENCE lmf_run_iov_sq INCREMENT BY 1 START WITH 1;
0033
0034 ALTER TABLE lmf_run_iov ADD CONSTRAINT lmf_run_iov_pk PRIMARY KEY (iov_id);
0035 ALTER TABLE lmf_run_iov ADD CONSTRAINT lmf_run_iov_uk UNIQUE (run_iov_id, subrun_num);
0036 CREATE INDEX lmf_run_iov_ix ON lmf_run_iov (subrun_start, subrun_end);
0037 ALTER TABLE lmf_run_iov ADD CONSTRAINT lmf_run_iov_fk1 FOREIGN KEY (tag_id) REFERENCES lmf_run_tag (tag_id);
0038 ALTER TABLE lmf_run_iov ADD CONSTRAINT lmf_run_iov_fk2 FOREIGN KEY (run_iov_id) REFERENCES run_iov (iov_id);
0039
0040
0041
0042 /* laser monitoring farm triggers, constraint checks */
0043 CREATE OR REPLACE TRIGGER lmf_run_iov_tg
0044   BEFORE INSERT ON lmf_run_iov
0045   REFERENCING NEW AS newiov
0046   FOR EACH ROW
0047   CALL update_subrun_iov_dates('lmf_run_iov', 'subrun_start', 'subrun_end', :newiov.subrun_start, :newiov.subrun_end, :newiov.tag_id, :newiov.run_iov_id)
0048 /