EcalCondDB/sql/trigger_add_doubleweights.sql

0001 -- SQL script used to add the double weights key in the ConfDB
0002
0003 alter table fe_config_main drop constraint fe_config_main_pk ;
0004
0005 ALTER TABLE FE_CONFIG_MAIN drop CONSTRAINT FE_CONFIG_L2_UNIQUE_uk ;
0006
0007 ALTER TABLE FE_CONFIG_MAIN drop CONSTRAINT FE_CONFIG_MAIN_to_PED_fk ;
0008 ALTER TABLE FE_CONFIG_MAIN drop CONSTRAINT FE_CONFIG_MAIN_to_lin_fk ;
0009 ALTER TABLE FE_CONFIG_MAIN drop CONSTRAINT FE_CONFIG_MAIN_to_lut_fk ;
0010 ALTER TABLE FE_CONFIG_MAIN drop CONSTRAINT FE_CONFIG_MAIN_to_fgr_fk ;
0011 ALTER TABLE FE_CONFIG_MAIN drop CONSTRAINT FE_CONFIG_MAIN_to_sli_fk ;
0012 ALTER TABLE FE_CONFIG_MAIN drop CONSTRAINT FE_CONFIG_MAIN_to_WEIGHT_fk ;
0013 ALTER TABLE FE_CONFIG_MAIN drop CONSTRAINT FE_CONFIG_MAIN_to_spi_fk ;
0014 ALTER TABLE FE_CONFIG_MAIN drop CONSTRAINT FE_CONFIG_MAIN_to_tim_fk ;
0015
0016
0017 alter table fe_config_main rename to old_fe_config_main3;
0018
0019
0020 CREATE TABLE FE_CONFIG_MAIN (
0021 conf_id NUMBER NOT NULL,
0022 ped_conf_id NUMBER NOT NULL,
0023 lin_conf_id NUMBER NOT NULL,
0024 lut_conf_id NUMBER NOT NULL,
0025 fgr_conf_id NUMBER NOT NULL,
0026 sli_conf_id NUMBER NOT NULL,
0027 wei_conf_id NUMBER NOT NULL,
0028 spi_conf_id NUMBER DEFAULT 0 NOT NULL,
0029 tim_conf_id NUMBER DEFAULT 0 NOT NULL,
0030 bxt_conf_id NUMBER NOT NULL,
0031 btt_conf_id NUMBER NOT NULL,
0032 bst_conf_id NUMBER DEFAULT 0 NOT NULL,
0033 tag         VARCHAR2(100),
0034 version NUMBER  NOT NULL,
0035 description VARCHAR2(200)  ,
0036 db_timestamp            TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
0037 coke_conf_id NUMBER DEFAULT 0 NOT NULL
0038 wei2_conf_id NUMBER DEFAULT 1 NOT NULL
0039 );
0040
0041
0042 insert into fe_config_main (conf_id, ped_conf_id ,lin_conf_id,
0043 lut_conf_id, fgr_conf_id, sli_conf_id, wei_conf_id, spi_conf_id, tim_conf_id, bxt_conf_id,
0044 btt_conf_id, bst_conf_id, tag, version, description, db_timestamp,coke_conf_id ) (select conf_id, ped_conf_id ,lin_conf_id,
0045 lut_conf_id, fgr_conf_id, sli_conf_id, wei_conf_id, spi_conf_id, tim_conf_id, bxt_conf_id,
0046 btt_conf_id, bst_conf_id, tag, version, description, db_timestamp,coke_conf_id from old_fe_config_main3 ) ;
0047
0048 -- drop table old_fe_config_main3;
0049
0050
0051 ALTER TABLE FE_CONFIG_MAIN ADD CONSTRAINT FE_CONFIG_MAIN_PK PRIMARY KEY (CONF_ID);
0052
0053 ALTER TABLE FE_CONFIG_MAIN ADD CONSTRAINT FE_CONFIG_L2_UNIQUE_uk UNIQUE (tag,version);
0054
0055 ALTER TABLE FE_CONFIG_MAIN ADD CONSTRAINT FE_CONFIG_MAIN_to_PED_fk FOREIGN KEY (ped_conf_id) REFERENCES FE_CONFIG_PED_INFO (ped_conf_id);
0056 ALTER TABLE FE_CONFIG_MAIN ADD CONSTRAINT FE_CONFIG_MAIN_to_lin_fk FOREIGN KEY (lin_conf_id) REFERENCES FE_CONFIG_LIN_INFO (lin_conf_id);
0057 ALTER TABLE FE_CONFIG_MAIN ADD CONSTRAINT FE_CONFIG_MAIN_to_lut_fk FOREIGN KEY (lut_conf_id) REFERENCES FE_CONFIG_LUT_INFO (lut_conf_id);
0058 ALTER TABLE FE_CONFIG_MAIN ADD CONSTRAINT FE_CONFIG_MAIN_to_fgr_fk FOREIGN KEY (fgr_conf_id) REFERENCES FE_CONFIG_fgr_INFO (fgr_conf_id);
0059 ALTER TABLE FE_CONFIG_MAIN ADD CONSTRAINT FE_CONFIG_MAIN_to_sli_fk FOREIGN KEY (sli_conf_id) REFERENCES FE_CONFIG_sliding_INFO (sli_conf_id);
0060 ALTER TABLE FE_CONFIG_MAIN ADD CONSTRAINT FE_CONFIG_MAIN_to_WEIGHT_fk FOREIGN KEY (wei_conf_id) REFERENCES FE_CONFIG_WEIGHT_INFO (wei_conf_id);
0061
0062 ALTER TABLE FE_CONFIG_MAIN ADD CONSTRAINT FE_CONFIG_MAIN_to_spi_fk FOREIGN KEY (spi_conf_id) REFERENCES FE_CONFIG_spike_INFO (spi_conf_id);
0063 ALTER TABLE FE_CONFIG_MAIN ADD CONSTRAINT FE_CONFIG_MAIN_to_tim_fk FOREIGN KEY (tim_conf_id) REFERENCES FE_CONFIG_time_INFO (tim_conf_id);
0064 --  ALTER TABLE FE_CONFIG_MAIN ADD CONSTRAINT FE_CONFIG_MAIN_to_cok_fk FOREIGN KEY (coke_conf_id) REFERENCES FE_CONFIG_coke_INFO (coke_conf_id);
0065
0066
0067 CREATE OR REPLACE TRIGGER fe_config_main_auto3_ver_tg
0068   BEFORE INSERT ON FE_CONFIG_MAIN
0069   FOR EACH ROW
0070     begin
0071   select test_update_tag_and_version('FE_CONFIG_MAIN', :new.tag, :new.version) into :new.version from dual;
0072 end;
0073 /
0074 SHOW ERRORS;
0075
0076
0077
0078 CREATE TABLE FE_CONFIG_WEIGHT2_INFO (
0079  wei2_conf_id NUMBER(10) NOT NULL,
0080  number_of_groups NUMBER(10) , -- (the number of groups of weights)
0081  db_timestamp  TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
0082  TAG VARCHAR2(100)
0083 );
0084 ALTER TABLE FE_CONFIG_WEIGHT2_INFO ADD CONSTRAINT  FE_CONFIG_WEIGHT2_INFO_PK PRIMARY KEY (wei2_conf_id);
0085
0086 insert into FE_CONFIG_WEIGHT2_INFO (wei2_conf_id, number_of_groups, TAG) values (1,0, 'NoOddWeights' ) ;
0087
0088
0089 create table FE_WEIGHT2_PER_GROUP_DAT(
0090  wei2_conf_id number not null,
0091  group_id number(10) not null,
0092  W0 NUMBER,
0093  W1 NUMBER,
0094  W2 NUMBER,
0095  W3 NUMBER,
0096  W4 NUMBER,
0097  W5 NUMBER
0098  );
0099
0100 ALTER TABLE FE_WEIGHT2_PER_GROUP_DAT ADD CONSTRAINT FE_WEIGHT2_PER_GROUP_pk PRIMARY KEY (wei2_conf_id , group_id);
0101 ALTER TABLE FE_WEIGHT2_PER_GROUP_DAT ADD CONSTRAINT FE_WEIGHT2_PER_GROUP_fk foreign KEY (wei2_conf_id) REFERENCES FE_CONFIG_WEIGHT2_INFO (wei2_conf_id);
0102
0103 insert into FE_CONFIG_WEIGHT2_PER_GROUP (1,0,0,0,0,0,0,0);
0104 insert into FE_CONFIG_WEIGHT2_PER_GROUP (1,1,0,0,0,0,0,0);
0105
0106
0107 CREATE TABLE FE_CONFIG_WEIGHT2_DAT (
0108  wei2_conf_id NUMBER NOT NULL,
0109  logic_id NUMBER(10) not null, -- ( of the strip)
0110  group_id number(10) not null);
0111
0112 ALTER TABLE FE_CONFIG_WEIGHT2_DAT ADD CONSTRAINT FE_CONFIG_WEIGHT2_fk  FOREIGN KEY (wei2_conf_id) REFERENCES FE_CONFIG_WEIGHT2_INFO (wei2_conf_id);
0113 ALTER TABLE FE_CONFIG_WEIGHT2_DAT ADD CONSTRAINT FE_CONFIG_WEIGHT2_fk2  FOREIGN KEY (wei2_conf_id, group_id)
0114 REFERENCES FE_WEIGHT2_PER_GROUP_DAT (wei2_conf_id, group_id);
0115
0116 insert into FE_CONFIG_WEIGHT2_DAT (wei2_conf_id,logic_id,group_id) (select 1,logic_id,group_id from FE_CONFIG_WEIGHT_DAT where wei_conf_id=480);
0117
0118
0119 create table FE_WEIGHT2_MODE_DAT(
0120  wei2_conf_id number not null,
0121  EnableEBOddFilter number DEFAULT 0 NOT NULL,
0122  EnableEEOddFilter number DEFAULT 0 NOT NULL,
0123  EnableEBOddPeakFinder number DEFAULT 0 NOT NULL,
0124  EnableEEOddPeakFinder number DEFAULT 0 NOT NULL,
0125  DisableEBEvenPeakFinder number DEFAULT 0 NOT NULL,
0126  FenixEBStripOutput number DEFAULT 0 NOT NULL,
0127  FenixEEStripOutput number DEFAULT 0 NOT NULL,
0128  FenixEBStripInfobit2 number DEFAULT 0 NOT NULL,
0129  FenixEEStripInfobit2 number DEFAULT 0 NOT NULL,
0130  EBFenixTcpOutput number DEFAULT 0 NOT NULL,
0131  EBFenixTcpInfobit1 number DEFAULT 0 NOT NULL,
0132  FenixPar12  number DEFAULT 0 NOT NULL,
0133  FenixPar13  number DEFAULT 0 NOT NULL,
0134  FenixPar14  number DEFAULT 0 NOT NULL,
0135  FenixPar15  number DEFAULT 0 NOT NULL
0136  );
0137
0138 ALTER TABLE FE_WEIGHT2_MODE_DAT ADD CONSTRAINT FE_WEIGHT2_MODE_pk PRIMARY KEY (wei2_conf_id);
0139 ALTER TABLE FE_WEIGHT2_MODE_DAT ADD CONSTRAINT FE_WEIGHT2_MODE_fk foreign KEY (wei2_conf_id) REFERENCES FE_CONFIG_WEIGHT2_INFO (wei2_conf_id);
0140
0141 INSERT into FE_WEIGHT2_MODE_DAT(wei2_conf_id) values (1);
0142
0143
0144
0145 CREATE SEQUENCE FE_CONFIG_WEIGHT2GROUP_SQ INCREMENT BY 1 START WITH 1;
0146 select FE_CONFIG_WEIGHT2GROUP_SQ.NextVal from DUAL;
0147
0148 -- this selects the first number that we have already used
0149
0150 /*
0151  *  the id is auto-incremented at each time you insert in the table
0152  *  no need to bother about inserting conf_id
0153  */
0154
0155 CREATE trigger FE_CONFIG_WEI2_TRG
0156 before insert on FE_CONFIG_WEIGHT2_INFO
0157 for each row
0158 begin
0159 select FE_CONFIG_WEIGHT2GROUP_SQ.NextVal into :new.wei2_conf_id from dual;
0160 end;
0161 /
0162
0163
0164
0165
0166