File indexing completed on 2023-03-17 11:15:10
0001
0002
0003
0004
0005
0006
0007
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
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
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
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
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 /