EcalCondDB/sql/create_run_data.sql

0001 /*
0002  *  Creates all the data tables referencing RUN_IOV
0003  */
0004
0005
0006 CREATE TABLE run_dat (
0007   iov_id                NUMBER(10),
0008   logic_id              NUMBER(10),
0009   num_events            NUMBER(10)
0010 );
0011
0012 ALTER TABLE run_dat ADD CONSTRAINT run_dat_pk PRIMARY KEY (iov_id, logic_id);
0013 ALTER TABLE run_dat ADD CONSTRAINT run_dat_fk FOREIGN KEY (iov_id) REFERENCES run_iov (iov_id);
0014
0015
0016
0017 CREATE TABLE run_config_dat (
0018   iov_id                NUMBER(10),
0019   logic_id              NUMBER(10),
0020   config_tag            VARCHAR2(100) NOT NULL,
0021   config_ver            NUMBER(10) NOT NULL
0022 );
0023
0024 ALTER TABLE run_config_dat ADD CONSTRAINT run_config_dat_pk PRIMARY KEY (iov_id, logic_id);
0025 ALTER TABLE run_config_dat ADD CONSTRAINT run_config_dat_fk FOREIGN KEY (iov_id) REFERENCES run_iov (iov_id);
0026
0027 CREATE TABLE run_tpgconfig_dat (
0028   iov_id                NUMBER(10),
0029   logic_id              NUMBER(10),
0030   config_tag            VARCHAR2(100) NOT NULL,
0031   version NUMBER(10) NOT NULL
0032 );
0033
0034  ALTER TABLE run_TPGCONFIG_DAT ADD CONSTRAINT run_tpgconfig_dat_pk PRIMARY KEY (iov_id, logic_id);
0035  ALTER TABLE run_TPGCONFIG_DAT ADD CONSTRAINT run_tpgconfig_dat_fk FOREIGN KEY (iov_id) REFERENCES run_iov (iov_id);
0036
0037
0038
0039 CREATE TABLE run_h4_table_position_dat (
0040   iov_id                NUMBER(10),
0041   logic_id              NUMBER(10),
0042   table_x               NUMBER(10),
0043   table_y               NUMBER(10),
0044   number_of_spills      NUMBER(10),
0045   number_of_events      NUMBER(10)
0046 );
0047
0048 ALTER TABLE run_h4_table_position_dat ADD CONSTRAINT run_h4_table_position_dat_pk PRIMARY KEY (iov_id, logic_id);
0049 ALTER TABLE run_h4_table_position_dat ADD CONSTRAINT run_h4_table_position_dat_fk FOREIGN KEY (iov_id) REFERENCES run_iov (iov_id);
0050
0051
0052
0053
0054 -- Tables for channel masking
0055 CREATE TABLE run_crystal_errors_dat (
0056   iov_id                NUMBER(10),
0057   logic_id              NUMBER(10),
0058   error_bits            NUMBER(38)
0059 );
0060
0061 ALTER TABLE run_crystal_errors_dat ADD CONSTRAINT run_crystal_errors_dat_pk PRIMARY KEY (iov_id, logic_id);
0062 ALTER TABLE run_crystal_errors_dat ADD CONSTRAINT run_crystal_errors_dat_fk FOREIGN KEY (iov_id) REFERENCES run_iov (iov_id);
0063
0064
0065
0066
0067 CREATE TABLE run_tt_errors_dat (
0068   iov_id                NUMBER(10),
0069   logic_id              NUMBER(10),
0070   error_bits            NUMBER(38)
0071 );
0072
0073 ALTER TABLE run_tt_errors_dat ADD CONSTRAINT run_tt_errors_dat_pk PRIMARY KEY (iov_id, logic_id);
0074 ALTER TABLE run_tt_errors_dat ADD CONSTRAINT run_tt_errors_dat_fk FOREIGN KEY (iov_id) REFERENCES run_iov (iov_id);
0075
0076
0077
0078 CREATE TABLE run_pn_errors_dat (
0079   iov_id                NUMBER(10),
0080   logic_id              NUMBER(10),
0081   error_bits            NUMBER(38)
0082 );
0083
0084 ALTER TABLE run_pn_errors_dat ADD CONSTRAINT run_pn_errors_dat_pk PRIMARY KEY (iov_id, logic_id);
0085 ALTER TABLE run_pn_errors_dat ADD CONSTRAINT run_pn_errors_dat_fk FOREIGN KEY (iov_id) REFERENCES run_iov (iov_id);
0086
0087
0088
0089 CREATE TABLE run_mem_ch_errors_dat (
0090   iov_id                NUMBER(10),
0091   logic_id              NUMBER(10),
0092   error_bits            NUMBER(38)
0093 );
0094
0095 ALTER TABLE run_mem_ch_errors_dat ADD CONSTRAINT run_mem_ch_errors_dat_pk PRIMARY KEY (iov_id, logic_id);
0096 ALTER TABLE run_mem_ch_errors_dat ADD CONSTRAINT run_mem_ch_errors_dat_fk FOREIGN KEY (iov_id) REFERENCES run_iov (iov_id);
0097
0098
0099
0100 CREATE TABLE run_mem_tt_errors_dat (
0101   iov_id                NUMBER(10),
0102   logic_id              NUMBER(10),
0103   error_bits            NUMBER(38)
0104 );
0105
0106 ALTER TABLE run_mem_tt_errors_dat ADD CONSTRAINT run_mem_tt_errors_dat_pk PRIMARY KEY (iov_id, logic_id);
0107 ALTER TABLE run_mem_tt_errors_dat ADD CONSTRAINT run_mem_tt_errors_dat_fk FOREIGN KEY (iov_id) REFERENCES run_iov (iov_id);
0108
0109
0110
0111 CREATE TABLE error_dictionary_def (
0112   def_id                NUMBER(10),
0113   error_mask            NUMBER(38),
0114   short_desc            VARCHAR2(100),
0115   long_desc             VARCHAR2(1000)
0116 );
0117
0118 CREATE SEQUENCE error_dictionary_def_sq INCREMENT BY 1 START WITH 1;
0119 ALTER TABLE error_dictionary_def ADD CONSTRAINT error_dictionary_def_pk PRIMARY KEY (def_id);
0120 ALTER TABLE error_dictionary_def ADD CONSTRAINT error_dictionary_def_uk UNIQUE (error_mask);
0121 CREATE INDEX error_dictionary_def_ix ON error_dictionary_def(short_desc);
0122
0123
0124 CREATE TABLE run_comment_dat (
0125   iov_id                NUMBER(10),
0126   comment_id            NUMBER(10),
0127   source                varchar2(20),
0128   user_comment          varchar2(200),
0129   db_timestamp  TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
0130 );
0131
0132 ALTER TABLE run_comment_dat ADD CONSTRAINT run_comment_dat_pk PRIMARY KEY (comment_id);
0133 ALTER TABLE run_comment_dat ADD CONSTRAINT run_comment_dat_fk FOREIGN KEY (iov_id) REFERENCES run_iov (iov_id);
0134 CREATE SEQUENCE run_comment_dat_SQ INCREMENT BY 1 START WITH 1;
0135
0136 CREATE trigger run_comment_dat_TRG
0137 before insert on run_comment_dat
0138 for each row
0139 begin
0140 select run_comment_dat_SQ.NextVal into :new.comment_id from dual;
0141 end;
0142 /
0143
0144 CREATE TABLE run_FEconfig_dat (
0145   iov_id                NUMBER(10),
0146   logic_id              NUMBER(10),
0147   config_id             NUMBER(10)
0148 );
0149
0150 ALTER TABLE run_FEconfig_dat ADD CONSTRAINT run_FEconfig_dat_pk PRIMARY KEY (iov_id, logic_id);
0151 ALTER TABLE run_FEconfig_dat ADD CONSTRAINT run_FEconfig_dat_fk FOREIGN KEY (iov_id) REFERENCES run_iov (iov_id);