EcalCondDB/sql/create_calibration_core.sql

0001 /*
0002  * Creates the def, tag and iov tables needed to define a set of calibrations
0003  */
0004
0005 /*  cali_tag */
0006 CREATE TABLE cali_tag (
0007   tag_id                NUMBER(10) NOT NULL,
0008   location_id           NUMBER(10) NOT NULL,
0009   gen_tag               VARCHAR(30) NOT NULL,
0010   method                VARCHAR(40) NOT NULL,
0011   version               VARCHAR(40) NOT NULL,
0012   data_type             VARCHAR(40) NOT NULL
0013 );
0014
0015 CREATE SEQUENCE cali_tag_sq INCREMENT BY 1 START WITH 1;
0016
0017 ALTER TABLE cali_tag ADD CONSTRAINT cali_tag_pk PRIMARY KEY (tag_id);
0018 ALTER TABLE cali_tag ADD CONSTRAINT cali_tag_fk FOREIGN KEY (location_id) REFERENCES location_def (def_id);
0019
0020 /* cali iov */
0021 CREATE TABLE cali_iov (
0022   iov_id                NUMBER(10) NOT NULL,
0023   tag_id                NUMBER(10) NOT NULL,
0024   since                 DATE NOT NULL,
0025   till                  DATE NOT NULL,
0026   db_timestamp          TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
0027 );
0028
0029 CREATE SEQUENCE cali_iov_sq INCREMENT BY 1 START WITH 1;
0030
0031 ALTER TABLE cali_iov ADD CONSTRAINT cali_iov_pk PRIMARY KEY (iov_id);
0032 CREATE INDEX cali_iov_ix ON cali_iov(since, till);
0033 ALTER TABLE cali_iov ADD CONSTRAINT cali_iov_fk FOREIGN KEY (tag_id) REFERENCES cali_tag (tag_id);
0034
0035
0036
0037 /* triggers, constraint checks */
0038 CREATE OR REPLACE TRIGGER cali_iov_tg
0039   BEFORE INSERT ON cali_iov
0040   REFERENCING NEW AS newiov
0041   FOR EACH ROW
0042   CALL update_iov_dates('cali_iov', 'since', 'till', :newiov.since, :newiov.till, :newiov.tag_id)
0043 /