EcalCondDB/sql/create_monitoring_core.sql

0001 /*
0002  * Creates the def, tag, and iov tables necessary to define a monitoring subrun
0003  * Requires: create_run_core.sql
0004  */
0005
0006 /* monitoring version definition */
0007 CREATE TABLE mon_version_def (
0008   def_id                NUMBER(10) NOT NULL,
0009   mon_ver               VARCHAR2(100) NOT NULL,
0010   description           VARCHAR2(1000) NOT NULL
0011 );
0012
0013 CREATE SEQUENCE mon_version_def_sq INCREMENT BY 1 START WITH 1;
0014
0015 ALTER TABLE mon_version_def ADD CONSTRAINT mon_version_def_pk PRIMARY KEY (def_id);
0016 ALTER TABLE mon_version_def ADD CONSTRAINT mon_version_def_uk UNIQUE (mon_ver);
0017
0018
0019
0020 /* monitoring tag */
0021 CREATE TABLE mon_run_tag (
0022   tag_id                NUMBER(10) NOT NULL,
0023   gen_tag               VARCHAR2(100) NOT NULL,
0024   mon_ver_id            NUMBER(10) NOT NULL
0025 );
0026
0027 CREATE SEQUENCE mon_run_tag_sq INCREMENT BY 1 START WITH 1;
0028
0029 ALTER TABLE mon_run_tag ADD CONSTRAINT mon_run_tag_pk PRIMARY KEY (tag_id);
0030 ALTER TABLE mon_run_tag ADD CONSTRAINT mon_run_tag_uk UNIQUE (gen_tag, mon_ver_id);
0031 ALTER TABLE mon_run_tag ADD CONSTRAINT mon_run_tag_fk FOREIGN KEY (mon_ver_id) REFERENCES mon_version_def (def_id);
0032
0033
0034
0035 /* monitoring IOV */
0036 CREATE TABLE mon_run_iov (
0037   iov_id                NUMBER(10) NOT NULL,
0038   tag_id                NUMBER(10) NOT NULL,
0039   run_iov_id            NUMBER(10) NOT NULL,
0040   subrun_num            NUMBER(10) NOT NULL,
0041   subrun_start          DATE NOT NULL,
0042   subrun_end            DATE NOT NULL,
0043   db_timestamp          TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
0044 );
0045
0046 CREATE SEQUENCE mon_run_iov_sq INCREMENT BY 1 START WITH 1;
0047
0048 ALTER TABLE mon_run_iov ADD CONSTRAINT mon_run_iov_pk PRIMARY KEY (iov_id);
0049 ALTER TABLE mon_run_iov ADD CONSTRAINT mon_run_iov_uk UNIQUE (tag_id, run_iov_id, subrun_num);
0050 CREATE INDEX mon_run_iov_ix ON mon_run_iov (subrun_start, subrun_end);
0051 ALTER TABLE mon_run_iov ADD CONSTRAINT mon_run_iov_fk1 FOREIGN KEY (tag_id) REFERENCES mon_run_tag (tag_id);
0052 ALTER TABLE mon_run_iov ADD CONSTRAINT mon_run_iov_fk2 FOREIGN KEY (run_iov_id) REFERENCES run_iov (iov_id);
0053
0054
0055
0056 /* monitoring triggers, constraint checks */
0057 CREATE OR REPLACE TRIGGER mon_run_iov_tg
0058   BEFORE INSERT ON mon_run_iov
0059   REFERENCING NEW AS newiov
0060   FOR EACH ROW
0061   CALL update_subrun_iov_dates('mon_run_iov', 'subrun_start', 'subrun_end', :newiov.subrun_start, :newiov.subrun_end, :newiov.tag_id, :newiov.run_iov_id)
0062 /
0063 SHOW ERRORS;
0064
0065
0066 CREATE TABLE mon_task_def (
0067   def_id                NUMBER(10) NOT NULL,
0068   task_code             VARCHAR2(3) NOT NULL,
0069   task_bit              number(10) NOT NULL,
0070   task_name             varchar2(30) not null,
0071   task_description      varchar2(100) not null
0072 );
0073
0074 CREATE SEQUENCE mon_task_def_sq INCREMENT BY 1 START WITH 1;
0075
0076 ALTER TABLE mon_task_def ADD CONSTRAINT mon_task_def_pk PRIMARY KEY (def_id);
0077 ALTER TABLE mon_task_def ADD CONSTRAINT mon_task_def_uk UNIQUE (task_code);
0078 ALTER TABLE mon_task_def ADD CONSTRAINT mon_task_def_uk2 UNIQUE (task_bit);