File indexing completed on 2024-04-06 12:22:59
0001
0002
0003
0004
0005
0006 CREATE TABLE COND2CONF_TYPE_DEF (
0007 DEF_ID NUMBER(2) NOT NULL,
0008 REC_TYPE VARCHAR2(20)
0009 );
0010
0011 ALTER TABLE cond2conf_type_def ADD CONSTRAINT COND2CONF_TYPE_DEF_PK PRIMARY KEY (DEF_ID);
0012
0013 INSERT INTO COND2CONF_TYPE_DEF(DEF_ID, REC_TYPE) VALUES( '0', 'PEDESTAL_OFFSETS' );
0014 INSERT INTO COND2CONF_TYPE_DEF(DEF_ID, REC_TYPE) VALUES( '1', 'DELAY_OFFSETS' );
0015 INSERT INTO COND2CONF_TYPE_DEF(DEF_ID, REC_TYPE) VALUES( '2', 'DCC_WEIGHTS' );
0016 INSERT INTO COND2CONF_TYPE_DEF(DEF_ID, REC_TYPE) VALUES( '3', 'BAD_CRYSTALS' );
0017 INSERT INTO COND2CONF_TYPE_DEF(DEF_ID, REC_TYPE) VALUES( '4', 'BAD_TT' );
0018
0019 CREATE TABLE COND2CONF_INFO (
0020 REC_ID NUMBER(10) NOT NULL,
0021 REC_TYPE_ID NUMBER(10) ,
0022 REC_date DATE ,
0023 LOCATION_ID NUMBER(10),
0024 RUN_NUMBER NUMBER(10),
0025 short_desc VARCHAR2(100),
0026 db_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
0027 );
0028
0029 ALTER TABLE cond2conf_info ADD CONSTRAINT COND2CONF_INFO_pk PRIMARY KEY (REC_ID);
0030 ALTER TABLE COND2CONF_INFO ADD CONSTRAINT COND2CONF_INFO_FK FOREIGN KEY (REC_TYPE_ID) REFERENCES COND2CONF_TYPE_DEF(DEF_ID);
0031
0032 CREATE SEQUENCE COND2CONF_INFO_SQ INCREMENT BY 1 START WITH 1;
0033
0034
0035
0036 CREATE TABLE pedestal_offsets_info (
0037 rec_id NUMBER(10) NOT NULL,
0038 TAG VARCHAR2(100),
0039 version NUMBER(10),
0040 db_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
0041 );
0042
0043 ALTER TABLE pedestal_offsets_INFO ADD CONSTRAINT pedestal_offsets_INFO_PK PRIMARY KEY (rec_id);
0044 ALTER TABLE pedestal_offsets_INFO ADD CONSTRAINT pedestal_offsets_INFO_uk UNIQUE(tag,version);
0045
0046
0047 CREATE TABLE pedestal_offsets_dat (
0048 REC_id NUMBER(10) NOT NULL,
0049 sm_id NUMBER(10),
0050 fed_id NUMBER(10),
0051 tt_id NUMBER(10),
0052 cry_id NUMBER(10),
0053 low NUMBER,
0054 mid NUMBER,
0055 high NUMBER
0056 );
0057
0058 ALTER TABLE PEDESTAL_OFFSETS_DAT ADD CONSTRAINT PEDESTAL_OFFSETS_DAT_FK FOREIGN KEY (REC_ID) REFERENCES COND2CONF_INFO (REC_ID);
0059 ALTER TABLE PEDESTAL_OFFSETS_DAT ADD CONSTRAINT PEDESTAL_OFFSETS_DAT_pk PRIMARY KEY (rec_id, sm_id,tt_id, cry_id );
0060
0061
0062
0063
0064
0065 CREATE TABLE DELAYS_info (
0066 rec_id NUMBER(10) NOT NULL,
0067 TAG VARCHAR2(100),
0068 version NUMBER(10),
0069 db_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
0070 );
0071
0072 ALTER TABLE delays_INFO ADD CONSTRAINT delays_INFO_PK PRIMARY KEY (rec_id);
0073 ALTER TABLE delays_INFO ADD CONSTRAINT delays_INFO_uk UNIQUE(tag,version);
0074
0075
0076 CREATE TABLE DELAYS_DAT (
0077 REC_ID NUMBER(10) NOT NULL,
0078 SM_ID NUMBER(10),
0079 FED_ID NUMBER(10),
0080 TT_ID NUMBER(10),
0081 TIME_OFFSET NUMBER
0082 );
0083
0084 ALTER TABLE DELAYS_DAT ADD CONSTRAINT DELAYS_DAT_FK FOREIGN KEY (REC_ID) REFERENCES COND2CONF_INFO (REC_ID);
0085 ALTER TABLE DELAYS_DAT ADD CONSTRAINT DELAYS_DAT_pk PRIMARY KEY (rec_id, sm_id,tt_id);
0086
0087
0088
0089
0090
0091
0092 CREATE TABLE dcc_weights_info (
0093 rec_id NUMBER(10) NOT NULL,
0094 TAG VARCHAR2(100),
0095 version NUMBER(10),
0096 db_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
0097 );
0098 ALTER TABLE dcc_weights_INFO ADD CONSTRAINT dcc_weights_INFO_PK PRIMARY KEY (rec_id);
0099 ALTER TABLE dcc_weights_INFO ADD CONSTRAINT dcc_weights_INFO_uk UNIQUE(tag,version);
0100
0101
0102 CREATE TABLE DCC_WEIGHTS_DAT (
0103 REC_ID NUMBER(10) NOT NULL,
0104 SM_ID NUMBER(10),
0105 FED_ID NUMBER(10),
0106 TT_ID NUMBER(10),
0107 CRY_ID NUMBER(10),
0108 WEI0 NUMBER,
0109 WEI1 NUMBER,
0110 WEI2 NUMBER,
0111 WEI3 NUMBER,
0112 WEI4 NUMBER,
0113 WEI5 NUMBER
0114 );
0115 ALTER TABLE DCC_WEIGHTS_DAT ADD CONSTRAINT DCC_WEIGHTS_DAT_FK FOREIGN KEY (REC_ID) REFERENCES COND2CONF_INFO (REC_ID);
0116 ALTER TABLE DCC_WEIGHTS_DAT ADD CONSTRAINT DCC_WEIGHTS_DAT_pk PRIMARY KEY (rec_id, sm_id,tt_id, cry_id );
0117
0118
0119 CREATE TABLE DCC_WEIGHTSAMPLE_DAT (
0120 REC_ID NUMBER(10) NOT NULL,
0121 FED_ID NUMBER(10),
0122 sample_id NUMBER(2),
0123 weight_number NUMBER(1)
0124 );
0125 ALTER TABLE DCC_WEIGHTSAMPLE_DAT ADD CONSTRAINT DCC_WEIGHTSAMPLE_DAT_pk PRIMARY KEY (rec_id, fed_id, sample_id );
0126 ALTER TABLE DCC_WEIGHTSAMPLE_DAT ADD CONSTRAINT DCC_WEIGHTSAMPLE_DAT_FK FOREIGN KEY (REC_ID) REFERENCES COND2CONF_INFO (REC_
0127 ID);
0128
0129
0130 create OR REPLACE TRIGGER cond2conf_auto_tg3
0131 BEFORE INSERT ON dcc_weights_info
0132 REFERENCING NEW AS newiov
0133 FOR EACH ROW
0134 CALL cond2conf_autoinsert('DCC_WEIGHTS', :newiov.rec_id, :newiov.tag, :newiov.version)
0135 /
0136
0137
0138
0139
0140
0141 CREATE TABLE BAD_Crystals_info (
0142 rec_id NUMBER(10) NOT NULL,
0143 TAG VARCHAR2(100),
0144 version NUMBER(10),
0145 db_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
0146 );
0147
0148 ALTER TABLE bad_crystals_INFO ADD CONSTRAINT bad_crystals_INFO_PK PRIMARY KEY (rec_id);
0149 ALTER TABLE bad_crystals_INFO ADD CONSTRAINT bad_crystals_INFO_uk UNIQUE(tag,version);
0150
0151
0152 CREATE TABLE bad_crystals_dat (
0153 REC_id NUMBER(10) NOT NULL,
0154 sm_id NUMBER(10),
0155 fed_id NUMBER(10),
0156 tt_id NUMBER(10),
0157 cry_id NUMBER(10),
0158 status NUMBER
0159 );
0160
0161 ALTER TABLE bad_crystals_DAT ADD CONSTRAINT bad_crystals_DAT_FK FOREIGN KEY (REC_ID) REFERENCES COND2CONF_INFO (REC_ID);
0162 ALTER TABLE bad_crystals_DAT ADD CONSTRAINT bad_crystals_DAT_pk PRIMARY KEY (rec_id, sm_id,tt_id, cry_id );
0163
0164
0165
0166 CREATE TABLE BAD_TT_info (
0167 rec_id NUMBER(10) NOT NULL,
0168 TAG VARCHAR2(100),
0169 version NUMBER(10),
0170 db_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
0171 );
0172
0173 ALTER TABLE bad_tt_INFO ADD CONSTRAINT bad_tt_INFO_PK PRIMARY KEY (rec_id);
0174 ALTER TABLE bad_tt_INFO ADD CONSTRAINT bad_tt_INFO_uk UNIQUE(tag,version);
0175
0176
0177 CREATE TABLE bad_tt_dat (
0178 REC_id NUMBER(10) NOT NULL,
0179 sm_id NUMBER(10),
0180 fed_id NUMBER(10),
0181 tt_id NUMBER(10),
0182 status NUMBER
0183 );
0184
0185 ALTER TABLE bad_tt_DAT ADD CONSTRAINT bad_tt_DAT_FK FOREIGN KEY (REC_ID) REFERENCES COND2CONF_INFO (REC_ID);
0186 ALTER TABLE bad_tt_DAT ADD CONSTRAINT bad_tt_DAT_pk PRIMARY KEY (rec_id, sm_id,tt_id );
0187
0188
0189
0190
0191
0192 CREATE TABLE TOWERS_TO_BYPASS_INFO (
0193 rec_id NUMBER(10) NOT NULL,
0194 TAG VARCHAR2(100),
0195 version NUMBER(10),
0196 db_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
0197 );
0198
0199 ALTER TABLE TOWERS_TO_BYPASS_INFO ADD CONSTRAINT TOWERS_TO_BYPASS_INFO_PK PRIMARY KEY (rec_id);
0200 ALTER TABLE TOWERS_TO_BYPASS_INFO ADD CONSTRAINT TOWERS_TO_BYPASS_INFO_uk UNIQUE(tag,version);
0201
0202
0203 CREATE TABLE TOWERS_TO_BYPASS_dat (
0204 REC_id NUMBER(10) NOT NULL,
0205 fed_id NUMBER(3) not null,
0206 tr_id NUMBER(3) not null,
0207 tt_id NUMBER(3) not null,
0208 time_corr NUMBER(10),
0209 status NUMBER(10)
0210 );
0211
0212 ALTER TABLE TOWERS_TO_BYPASS_DAT ADD CONSTRAINT TOWERS_TO_BYPASS_DAT_pk PRIMARY KEY (rec_id, fed_id, tr_id, tt_id );
0213
0214
0215
0216 CREATE TABLE VFES_TO_REJECT_INFO (
0217 rec_id NUMBER(10) NOT NULL,
0218 TAG VARCHAR2(100),
0219 version NUMBER(10),
0220 db_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
0221 );
0222
0223 ALTER TABLE VFES_TO_REJECT_INFO ADD CONSTRAINT VFES_TO_REJECT_INFO_PK PRIMARY KEY (rec_id);
0224 ALTER TABLE VFES_TO_REJECT_INFO ADD CONSTRAINT VFES_TO_REJECT_INFO_uk UNIQUE(tag,version);
0225
0226
0227 CREATE TABLE VFES_TO_REJECT_dat (
0228 REC_id NUMBER(10) NOT NULL,
0229 fed_id NUMBER(3) not null,
0230 tt_id NUMBER(3) not null,
0231 vfe_id NUMBER(3) not null,
0232 gain NUMBER(10),
0233 status NUMBER(10)
0234 );
0235
0236 ALTER TABLE VFES_TO_REJECT_DAT ADD CONSTRAINT VFES_TO_REJECT_DAT_pk PRIMARY KEY (rec_id, fed_id, tt_id, vfe_id );
0237
0238
0239
0240
0241 CREATE TABLE GOL_BIAS_CURRENT_INFO (
0242 rec_id NUMBER(10) NOT NULL,
0243 TAG VARCHAR2(100),
0244 version NUMBER(10),
0245 db_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
0246 );
0247
0248 ALTER TABLE GOL_BIAS_CURRENT_INFO ADD CONSTRAINT GOL_BIAS_CURRENT_INFO_PK PRIMARY KEY (rec_id);
0249 ALTER TABLE GOL_BIAS_CURRENT_INFO ADD CONSTRAINT GOL_BIAS_CURRENT_INFO_uk UNIQUE(tag,version);
0250
0251 CREATE TABLE GOL_BIAS_CURRENT_DAT (
0252 REC_id NUMBER(10) NOT NULL,
0253 fed_id NUMBER(3) not null,
0254 tt_id NUMBER(3) not null,
0255 gol_id NUMBER(2) not null,
0256 gol_current NUMBER(10),
0257 pll_current NUMBER(10),
0258 status NUMBER(10)
0259 );
0260
0261 ALTER TABLE GOL_BIAS_CURRENT_DAT ADD CONSTRAINT GOL_BIAS_CURRENT_DAT_pk PRIMARY KEY (rec_id, fed_id, tt_id, gol_id );
0262
0263
0264
0265
0266
0267
0268 CREATE TABLE FE_DAQ_CONFIG (
0269 config_id NUMBER(10) NOT NULL,
0270 tag VARCHAR2(20) not null,
0271 version NUMBER(10) not null,
0272 ped_id NUMBER(10),
0273 del_id NUMBER(10),
0274 wei_id NUMBER(10),
0275 bxt_id NUMBER(10),
0276 btt_id NUMBER(10),
0277 TR_BXT_ID NUMBER(10),
0278 TR_BTT_ID NUMBER(10),
0279 TBY_ID NUMBER(10),
0280 VFE_ID NUMBER(10),
0281 GOL_ID NUMBER(10),
0282 USER_COMMENT VARCHAR2(100),
0283 db_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
0284 );
0285
0286 ALTER TABLE FE_DAQ_CONFIG ADD CONSTRAINT FE_DAQ_CONFIG_pk PRIMARY KEY (config_id);
0287 ALTER TABLE FE_DAQ_CONFIG ADD CONSTRAINT FE_DAQ_CONFIG_uk UNIQUE(tag,version);
0288 ALTER TABLE FE_DAQ_CONFIG ADD CONSTRAINT FE_DAQ_CONFIG_fk1 FOREIGN KEY (ped_ID) REFERENCES COND2CONF_INFO (REC_ID);
0289 ALTER TABLE FE_DAQ_CONFIG ADD CONSTRAINT FE_DAQ_CONFIG_fk2 FOREIGN KEY (del_ID) REFERENCES COND2CONF_INFO (REC_ID);
0290 ALTER TABLE FE_DAQ_CONFIG ADD CONSTRAINT FE_DAQ_CONFIG_fk3 FOREIGN KEY (wei_ID) REFERENCES COND2CONF_INFO (REC_ID);
0291
0292 CREATE SEQUENCE FE_DAQ_CONDFIG_SQ INCREMENT BY 1 START WITH 1;
0293
0294
0295
0296
0297
0298 create table ECAL_FED_DEF(
0299 DEF_ID NUMBER NOT NULL,
0300 HOST VARCHAR2(100) NOT NULL,
0301 SLOT NUMBER NOT NULL,
0302 BOARD_ID NUMBER,
0303 FED_ID NUMBER NOT NULL
0304 );
0305
0306
0307 ALTER TABLE ECAL_FED_DEF ADD CONSTRAINT ecal_fed_def_pk PRIMARY KEY (DEF_ID);
0308
0309
0310
0311 CREATE SEQUENCE ecal_fed_def_sq INCREMENT BY 1 START WITH 1;
0312 CREATE trigger ecal_fed_def_trg
0313 before insert on ECAL_FED_DEF
0314 for each row
0315 begin
0316 select ecal_fed_def_sq.NextVal into :new.def_id from dual;
0317 end;
0318 /
0319
0320 create table ECAL_FED_TO_SUPERMODULE(
0321 DEF_ID NUMBER NOT NULL,
0322 FED_ID NUMBER NOT NULL,
0323 CONSTR_ID NUMBER NOT NULL,
0324 PSEUDO_SLOT_ID NUMBER NOT NULL,
0325 GEOM_ID VARCHAR(10)
0326 );
0327
0328 ALTER TABLE ECAL_FED_TO_SUPERMODULE ADD CONSTRAINT ECAL_FED_TO_SUPERMODULE_PK PRIMARY KEY (DEF_ID);
0329 ALTER TABLE ECAL_FED_TO_SUPERMODULE ADD CONSTRAINT ECAL_FED_TO_SUPERMODULE_UK1 UNIQUE (FED_ID);
0330 ALTER TABLE ECAL_FED_TO_SUPERMODULE ADD CONSTRAINT ECAL_FED_TO_SUPERMODULE_UK2 UNIQUE (CONSTR_ID);
0331 ALTER TABLE ECAL_FED_TO_SUPERMODULE ADD CONSTRAINT ECAL_FED_TO_SUPERMODULE_UK3 UNIQUE (PSEUDO_SLOT_ID);
0332
0333 CREATE SEQUENCE ECAL_FED_TO_SUPERMODULE_SQ INCREMENT BY 1 START WITH 1;
0334 CREATE trigger ECAL_FED_TO_SUPERMODULE_TRG
0335 before insert on ECAL_FED_TO_SUPERMODULE
0336 for each row
0337 begin
0338 select ECAL_FED_TO_SUPERMODULE_SQ.NextVal into :new.def_id from dual;
0339 end;
0340 /
0341 CREATE OR REPLACE procedure cond2conf_autoinsert
0342 (rec_type in varchar2, rec_id in NUMBER, tag in varchar2, version in number)
0343 IS
0344
0345 sql_str VARCHAR(1000);
0346 location_id number(10);
0347 type_id number;
0348 loca varchar2(10);
0349 short_descr varchar2(30) ;
0350
0351 BEGIN
0352
0353 sql_str := 'SELECT def_id from cond2conf_type_def where rec_type=:1 ';
0354 EXECUTE IMMEDIATE sql_str INTO type_id using rec_type ;
0355 loca:='P5';
0356 sql_str := 'SELECT def_id from location_def where location=:1 ';
0357 EXECUTE IMMEDIATE sql_str INTO location_id using loca ;
0358 short_descr:= tag || '_' || cast (version as varchar2) ;
0359 sql_str :='Insert into COND2CONF_INFO (rec_id,REC_TYPE_ID, LOCATION_ID, short_desc ) values (:1, :2, :3, :4) ';
0360 EXECUTE IMMEDIATE sql_str using rec_id, type_id, location_id, short_descr ;
0361 end;
0362 /
0363 show errors;
0364
0365
0366
0367
0368 create OR REPLACE TRIGGER cond2conf_auto_tg
0369 BEFORE INSERT ON delays_info
0370 REFERENCING NEW AS newiov
0371 FOR EACH ROW
0372 CALL cond2conf_autoinsert('DELAY_OFFSETS', :newiov.rec_id, :newiov.tag, :newiov.version)
0373 /
0374 create OR REPLACE TRIGGER cond2conf_auto2_tg
0375 BEFORE INSERT ON pedestal_offsets_info
0376 REFERENCING NEW AS newiov
0377 FOR EACH ROW
0378 CALL cond2conf_autoinsert('PEDESTAL_OFFSETS', :newiov.rec_id, :newiov.tag, :newiov.version)
0379 /
0380 create OR REPLACE TRIGGER cond2conf_auto_tg3
0381 BEFORE INSERT ON weights_info
0382 REFERENCING NEW AS newiov
0383 FOR EACH ROW
0384 CALL cond2conf_autoinsert('DCC_WEIGHTS', :newiov.rec_id, :newiov.tag, :newiov.version)
0385 /
0386 create OR REPLACE TRIGGER cond2conf_auto_tg4
0387 BEFORE INSERT ON bad_crystals_info
0388 REFERENCING NEW AS newiov
0389 FOR EACH ROW
0390 CALL cond2conf_autoinsert('BAD_CRYSTALS', :newiov.rec_id, :newiov.tag, :newiov.version)
0391 /
0392 create OR REPLACE TRIGGER cond2conf_auto_tg5
0393 BEFORE INSERT ON bad_tt_info
0394 REFERENCING NEW AS newiov
0395 FOR EACH ROW
0396 CALL cond2conf_autoinsert('BAD_TT', :newiov.rec_id, :newiov.tag, :newiov.version)
0397 /