Back to home page

Project CMSSW displayed by LXR

 
 

    


File indexing completed on 2021-02-14 13:31:55

0001 /* RUN TYPE DEFINITIONS */
0002 
0003  
0004 /* THIS IS THE MASTER COND2CONF TABLE */ 
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 /* PEDESTAL OFFSETS */
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 /* THIS IS FOR THE DELAYS */ 
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 /* THIS IS FOR THE WEIGHTS */ 
0090 /* THIS IS FOR THE WEIGHTS */ 
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 /* CRYSTAL bad channels */
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 /* TT bad channels */
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 /* towers to bypass */
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 /* vfes to reject */
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 /*  GOL bias current */
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 /* FE DAQ */
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 ---- Some mapping tables
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 -- DOESN'T WORK FOR PRESHOWER
0307 ALTER TABLE ECAL_FED_DEF ADD CONSTRAINT ecal_fed_def_pk  PRIMARY KEY (DEF_ID);
0308 -- ALTER TABLE ECAL_FED_DEF ADD CONSTRAINT ecal_fed_def_uk  UNIQUE (host,slot);
0309 
0310 -- NOT USED ANYMORE
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 /