EcalCondDB/sql/create_dcu_core.sql

0001 /*
0002  * Creates the def, tag and iov tables needed to define a DCU data taking
0003  */
0004
0005
0006
0007
0008 /* dcu tag*/
0009 CREATE TABLE dcu_tag (
0010   tag_id                NUMBER(10) NOT NULL,
0011   gen_tag               VARCHAR(100) NOT NULL,
0012   location_id           NUMBER(10) NOT NULL
0013 );
0014
0015 CREATE SEQUENCE dcu_tag_sq INCREMENT BY 1 START WITH 1;
0016
0017 ALTER TABLE dcu_tag ADD CONSTRAINT dcu_tag_pk PRIMARY KEY (tag_id);
0018 ALTER TABLE dcu_tag ADD CONSTRAINT dcu_tag_uk UNIQUE (gen_tag, location_id);
0019 ALTER TABLE dcu_tag ADD CONSTRAINT dcu_tag_fk1 FOREIGN KEY (location_id) REFERENCES location_def (def_id);
0020
0021
0022
0023 /* dcu iov */
0024 CREATE TABLE dcu_iov (
0025   iov_id                NUMBER(10) NOT NULL,
0026   tag_id                NUMBER(10) NOT NULL,
0027   since                 DATE NOT NULL,
0028   till                  DATE NOT NULL,
0029   db_timestamp          TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
0030 );
0031
0032 CREATE SEQUENCE dcu_iov_sq INCREMENT BY 1 START WITH 1;
0033
0034 ALTER TABLE dcu_iov ADD CONSTRAINT dcu_iov_pk PRIMARY KEY (iov_id);
0035 CREATE INDEX dcu_iov_ix ON dcu_iov (since, till);
0036 ALTER TABLE dcu_iov ADD CONSTRAINT dcu_iov_fk FOREIGN KEY (tag_id) REFERENCES dcu_tag (tag_id);
0037
0038
0039
0040 /* triggers, constraint checks */
0041 CREATE OR REPLACE TRIGGER dcu_iov_tg
0042   BEFORE INSERT ON dcu_iov
0043   REFERENCING NEW AS newiov
0044   FOR EACH ROW
0045   CALL update_iov_dates('dcu_iov', 'since', 'till', :newiov.since, :newiov.till, :newiov.tag_id)
0046 /