EcalCondDB/sql/create_run_core.sql

0001 /*
0002  * Creates the def, tag and iov tables needed to define an ECAL run
0003  */
0004
0005
0006
0007 /* location definition table */
0008 CREATE TABLE location_def (
0009   def_id                NUMBER NOT NULL,
0010   location              VARCHAR2(100) NOT NULL
0011 );
0012
0013 CREATE SEQUENCE location_def_sq INCREMENT BY 1 START WITH 1;
0014
0015 ALTER TABLE location_def ADD CONSTRAINT location_def_pk PRIMARY KEY (def_id);
0016 ALTER TABLE location_def ADD CONSTRAINT location_def_uk UNIQUE (location);
0017
0018
0019
0020 /* run type definition table */
0021 CREATE TABLE run_type_def (
0022   def_id                NUMBER(10) NOT NULL,
0023   run_type              VARCHAR2(100) NOT NULL,
0024   description           VARCHAR2(1000) NOT NULL
0025 );
0026
0027 CREATE SEQUENCE run_type_def_sq INCREMENT BY 1 START WITH 1;
0028
0029 ALTER TABLE run_type_def ADD CONSTRAINT run_type_def_pk PRIMARY KEY (def_id);
0030 ALTER TABLE run_type_def ADD CONSTRAINT run_type_def_uk UNIQUE (run_type);
0031
0032
0033
0034 /* run tag*/
0035 CREATE TABLE run_tag (
0036   tag_id                NUMBER(10) NOT NULL,
0037   gen_tag               VARCHAR(100) NOT NULL,
0038   location_id           NUMBER(10) NOT NULL,
0039   run_type_id           NUMBER(10) NOT NULL
0040 );
0041
0042 CREATE SEQUENCE run_tag_sq INCREMENT BY 1 START WITH 1;
0043
0044 ALTER TABLE run_tag ADD CONSTRAINT run_tag_pk PRIMARY KEY (tag_id);
0045 ALTER TABLE run_tag ADD CONSTRAINT run_tag_uk UNIQUE (gen_tag, location_id, run_type_id);
0046 ALTER TABLE run_tag ADD CONSTRAINT run_tag_fk1 FOREIGN KEY (location_id) REFERENCES location_def (def_id);
0047 ALTER TABLE run_tag ADD CONSTRAINT run_tag_fk2 FOREIGN KEY (run_type_id) REFERENCES run_type_def (def_id);
0048
0049
0050
0051 /* run iov */
0052 CREATE TABLE run_iov (
0053   iov_id                NUMBER(10) NOT NULL,
0054   tag_id                NUMBER(10) NOT NULL,
0055   run_num               NUMBER(10) NOT NULL,
0056   run_start             DATE NOT NULL,
0057   run_end               DATE NOT NULL,
0058   db_timestamp          TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
0059 );
0060
0061 CREATE SEQUENCE run_iov_sq INCREMENT BY 1 START WITH 1;
0062
0063 ALTER TABLE run_iov ADD CONSTRAINT run_iov_pk PRIMARY KEY (iov_id);
0064 ALTER TABLE run_iov ADD CONSTRAINT run_iov_uk UNIQUE (tag_id, run_num);
0065 ALTER TABLE run_iov ADD CONSTRAINT run_iov_uk2 UNIQUE (tag_id, run_start);
0066 CREATE INDEX run_iov_ix ON run_iov (run_start, run_end);
0067 ALTER TABLE run_iov ADD CONSTRAINT run_iov_fk FOREIGN KEY (tag_id) REFERENCES run_tag (tag_id);
0068
0069
0070
0071 /* triggers, constraint checks */
0072 CREATE OR REPLACE TRIGGER run_iov_tg
0073   BEFORE INSERT ON run_iov
0074   REFERENCING NEW AS newiov
0075   FOR EACH ROW
0076   CALL update_iov_dates('run_iov', 'run_start', 'run_end', :newiov.run_start, :newiov.run_end, :newiov.tag_id)
0077 /