File indexing completed on 2024-04-06 12:22:59
0001
0002
0003
0004
0005
0006
0007
0008
0009
0010
0011 DECLARE
0012 LOGIC_ID INTEGER := 1050000000;
0013 Y NUMBER := 1;
0014 X NUMBER := 1;
0015 CH NUMBER := 0;
0016 BEGIN
0017 INSERT INTO VIEWDESCRIPTION VALUES ('LED_PS_CHANNEL',
0018 'LED Power Supplies CHANNEL NUMBER',
0019 'ENDCAP', 'DEE', NULL);
0020 INSERT INTO VIEWDESCRIPTION VALUES ('LED_PM_CHANNEL',
0021 'LED Power Monitor CHANNEL NUMBER',
0022 'ENDCAP', 'DEE', 'CHANNEL');
0023 INSERT INTO VIEWDESCRIPTION VALUES ('LED_CB_CHANNEL',
0024 'LED Control Boxes CHANNEL NUMBER',
0025 'ENDCAP', 'DEE', NULL);
0026 INSERT INTO VIEWDESCRIPTION VALUES ('LED_LED_CHANNEL',
0027 'LED Boxes CHANNEL NUMBER',
0028 'ENDCAP', 'DEE', 'CHANNEL');
0029
0030 FOR X IN 1..2 LOOP
0031 INSERT INTO CHANNELVIEW VALUES('LED_PS_CHANNEL', +1, X, NULL,
0032 'LED_PS_CHANNEL', LOGIC_ID);
0033 LOGIC_ID := LOGIC_ID + 1;
0034 INSERT INTO CHANNELVIEW VALUES('LED_PS_CHANNEL', -1, X, NULL,
0035 'LED_PS_CHANNEL', LOGIC_ID);
0036 LOGIC_ID := LOGIC_ID + 1;
0037 END LOOP;
0038
0039 FOR X IN 1..4 LOOP
0040 INSERT INTO CHANNELVIEW VALUES('LED_PM_CHANNEL', +1, 2, X,
0041 'LED_PM_CHANNEL', LOGIC_ID);
0042 LOGIC_ID := LOGIC_ID + 1;
0043 INSERT INTO CHANNELVIEW VALUES('LED_PM_CHANNEL', -1, 2, X,
0044 'LED_PM_CHANNEL', LOGIC_ID);
0045
0046 LOGIC_ID := LOGIC_ID + 1;
0047 END LOOP;
0048 FOR X IN 5..8 LOOP
0049 INSERT INTO CHANNELVIEW VALUES('LED_PM_CHANNEL', +1, 1, X,
0050 'LED_PM_CHANNEL', LOGIC_ID);
0051 LOGIC_ID := LOGIC_ID + 1;
0052 INSERT INTO CHANNELVIEW VALUES('LED_PM_CHANNEL', -1, 1, X,
0053 'LED_PM_CHANNEL', LOGIC_ID);
0054
0055 LOGIC_ID := LOGIC_ID + 1;
0056 END LOOP;
0057
0058 FOR X IN 1..2 LOOP
0059 INSERT INTO CHANNELVIEW VALUES('LED_CB_CHANNEL', +1, X, NULL,
0060 'LED_CB_CHANNEL', LOGIC_ID);
0061 LOGIC_ID := LOGIC_ID + 1;
0062 INSERT INTO CHANNELVIEW VALUES('LED_CB_CHANNEL', -1, X, NULL,
0063 'LED_CB_CHANNEL', LOGIC_ID);
0064 LOGIC_ID := LOGIC_ID + 1;
0065 END LOOP;
0066
0067 FOR X IN 1..2 LOOP
0068 FOR Y IN 1..4 LOOP
0069 CH := 166 + 2 * Y;
0070 INSERT INTO CHANNELVIEW VALUES('LED_LED_CHANNEL', +1, X, CH,
0071 'LED_LED_CHANNEL', LOGIC_ID);
0072 LOGIC_ID := LOGIC_ID + 1;
0073 INSERT INTO CHANNELVIEW VALUES('LED_LED_CHANNEL', -1, X, CH,
0074 'LED_LED_CHANNEL', LOGIC_ID);
0075 LOGIC_ID := LOGIC_ID + 1;
0076 END LOOP;
0077 END LOOP;
0078 END;
0079 /
0080
0081 @update_iov_dates_procedure_new
0082
0083
0084 CREATE TABLE LED_SYSTEM_TAG (
0085 TAG_ID NUMBER(10) NOT NULL,
0086 GEN_TAG VARCHAR(100) NOT NULL,
0087 VERSION INTEGER NOT NULL
0088 );
0089
0090 CREATE SEQUENCE LED_SYSTEM_TAG_SQ INCREMENT BY 1 START WITH 1 NOCACHE;
0091
0092 ALTER TABLE LED_SYSTEM_TAG ADD CONSTRAINT LED_SYSTEM_TAG_PK PRIMARY
0093 KEY (TAG_ID);
0094 ALTER TABLE LED_SYSTEM_TAG ADD CONSTRAINT LED_SYSTEM_TAG_UK UNIQUE (GEN_TAG,
0095 VERSION);
0096
0097
0098
0099 CREATE TABLE LED_DATTABLE_INDEX (
0100 ID NUMBER(3) NOT NULL,
0101 TABLE_NAME VARCHAR(25) NOT NULL,
0102 DESCRIPTION VARCHAR(255)
0103 );
0104
0105 ALTER TABLE LED_DATTABLE_INDEX ADD CONSTRAINT LED_DATTABLE_INDEX_PK
0106 PRIMARY KEY (TABLE_NAME);
0107 ALTER TABLE LED_DATTABLE_INDEX ADD CONSTRAINT LED_DATTABLE_INDEX_UK
0108 UNIQUE (ID, TABLE_NAME);
0109
0110 INSERT INTO LED_DATTABLE_INDEX VALUES (1, 'LED_POWER_SUPPLY_MON', '');
0111 INSERT INTO LED_DATTABLE_INDEX VALUES (1, 'LED_POWER_MONBOX_MON', '');
0112 INSERT INTO LED_DATTABLE_INDEX VALUES (1, 'LED_POWER_CTRLBOX_MON', '');
0113 INSERT INTO LED_DATTABLE_INDEX VALUES (1, 'LED_POWER_LED_MON', '');
0114 INSERT INTO LED_DATTABLE_INDEX VALUES (2, 'LED_POWER_SUPPLY_STATUS', '');
0115 INSERT INTO LED_DATTABLE_INDEX VALUES (2, 'LED_POWER_MONBOX_STATUS', '');
0116 INSERT INTO LED_DATTABLE_INDEX VALUES (2, 'LED_POWER_CTRLBOX_STATUS', '');
0117
0118
0119 CREATE TABLE LED_SYSTEM_IOV (
0120 IOV_ID NUMBER(10),
0121 MASK NUMBER(3) NOT NULL,
0122 TAG_ID NUMBER(10) NOT NULL,
0123 SINCE DATE NOT NULL,
0124 TILL DATE NOT NULL,
0125 DB_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
0126 )
0127 PARTITION BY RANGE ("IOV_ID")
0128 (PARTITION "LED_SYSTEM_IOV_0" VALUES LESS THAN (MAXVALUE))
0129 ;
0130
0131 CREATE SEQUENCE LED_SYSTEM_IOV_SQ INCREMENT BY 1 START WITH 1 NOCACHE;
0132
0133 ALTER TABLE LED_SYSTEM_IOV ADD CONSTRAINT LED_SYSTEM_IOV_PK
0134 PRIMARY KEY (IOV_ID);
0135 ALTER TABLE LED_SYSTEM_IOV ADD CONSTRAINT LED_SYSTEM_IOV_FK1
0136 FOREIGN KEY (TAG_ID) REFERENCES LED_SYSTEM_TAG (TAG_ID);
0137
0138 CREATE INDEX LED_SYSTEM_IOV_IX ON LED_SYSTEM_IOV (SINCE, TILL);
0139
0140 CREATE OR REPLACE TRIGGER LED_SYSTEM_IOV_TG
0141 BEFORE INSERT ON LED_SYSTEM_IOV
0142 REFERENCING NEW AS newiov
0143 FOR EACH ROW
0144 CALL update_iov_dates_new('LED_SYSTEM_IOV', :newiov.mask,
0145 'SINCE', 'TILL',
0146 :newiov.since,
0147 :newiov.till, :newiov.tag_id)
0148 /
0149
0150
0151 CREATE TABLE LED_POWER_SUPPLY_MON (
0152 IOV_ID NUMBER(10) NOT NULL,
0153 LOGIC_ID NUMBER(10),
0154 PS_VOLTAGE_SET_POINT FLOAT,
0155 PS_STATUS INTEGER,
0156 PS_STANDEVT_STATUS INTEGER,
0157 PS_EXEC_ERR INTEGER,
0158 PS_QUERY_ERR INTEGER
0159 )
0160 PARTITION BY RANGE ("IOV_ID")
0161 (PARTITION "LED_PWRSPPL_M_0" VALUES LESS THAN (MAXVALUE))
0162 ;
0163
0164 ALTER TABLE LED_POWER_SUPPLY_MON ADD CONSTRAINT LED_POWER_SUPPLY_MON_PK
0165 PRIMARY KEY (IOV_ID, LOGIC_ID);
0166 ALTER TABLE LED_POWER_SUPPLY_MON ADD CONSTRAINT LED_POWER_SUPPLY_MON_FK1
0167 FOREIGN KEY (IOV_ID) REFERENCES LED_SYSTEM_IOV (IOV_ID);
0168
0169 CREATE TABLE LED_POWER_MONBOX_MON (
0170 IOV_ID NUMBER,
0171 LOGIC_ID NUMBER(10),
0172 PM_VOLTAGE_LIM FLOAT,
0173 PM_CURRENT_LIMIT FLOAT
0174 )
0175 PARTITION BY RANGE ("IOV_ID")
0176 (PARTITION "LED_PWRMNBX_M_0" VALUES LESS THAN (MAXVALUE))
0177 ;
0178
0179 ALTER TABLE LED_POWER_MONBOX_MON ADD CONSTRAINT LED_POWER_MONBOX_MON_PK
0180 PRIMARY KEY (IOV_ID, LOGIC_ID);
0181 ALTER TABLE LED_POWER_MONBOX_MON ADD CONSTRAINT LED_POWER_MONBOX_MON_FK1
0182 FOREIGN KEY (IOV_ID) REFERENCES LED_SYSTEM_IOV (IOV_ID);
0183
0184 CREATE TABLE LED_POWER_CTRLBOX_MON (
0185 IOV_ID NUMBER,
0186 LOGIC_ID NUMBER(10),
0187 CB_OUT_DELAY_COARSE INTEGER,
0188 CB_OUT_DELAY_FINE FLOAT,
0189 CB_OUT_PULSE_WIDTH INTEGER
0190 )
0191 PARTITION BY RANGE ("IOV_ID")
0192 (PARTITION "LED_PWRCTRLBX_M_0" VALUES LESS THAN (MAXVALUE))
0193 ;
0194
0195 ALTER TABLE LED_POWER_CTRLBOX_MON ADD CONSTRAINT LED_POWER_CTRLBOX_MON_PK
0196 PRIMARY KEY (IOV_ID, LOGIC_ID);
0197 ALTER TABLE LED_POWER_CTRLBOX_MON ADD CONSTRAINT LED_POWER_CTRLBOX_MON_FK1
0198 FOREIGN KEY (IOV_ID) REFERENCES LED_SYSTEM_IOV (IOV_ID);
0199
0200 CREATE TABLE LED_POWER_LED_MON (
0201 IOV_ID NUMBER,
0202 LOGIC_ID NUMBER(10),
0203 STATUS NUMBER(1)
0204 )
0205 PARTITION BY RANGE ("IOV_ID")
0206 (PARTITION "LED_PWRLD_M_0" VALUES LESS THAN (MAXVALUE))
0207 ;
0208
0209 ALTER TABLE LED_POWER_LED_MON ADD CONSTRAINT LED_POWER_LED_MON_PK
0210 PRIMARY KEY (IOV_ID, LOGIC_ID);
0211 ALTER TABLE LED_POWER_LED_MON ADD CONSTRAINT LED_POWER_LED_MON_FK1
0212 FOREIGN KEY (IOV_ID) REFERENCES LED_SYSTEM_IOV (IOV_ID);
0213
0214
0215 CREATE TABLE LED_POWER_SUPPLY_STATUS (
0216 IOV_ID NUMBER(10) NOT NULL,
0217 LOGIC_ID NUMBER(10),
0218 PS_CURRENT NUMBER,
0219 PS_VOLTAGE NUMBER,
0220 PS_DELTAV NUMBER,
0221 PS_CURRENT_LIMIT NUMBER
0222 )
0223 PARTITION BY RANGE ("IOV_ID")
0224 (PARTITION "LED_PWRSPPL_S_0" VALUES LESS THAN (MAXVALUE))
0225 ;
0226
0227 ALTER TABLE LED_POWER_SUPPLY_STATUS ADD CONSTRAINT LED_POWER_SUPPLY_STATUS_PK
0228 PRIMARY KEY (IOV_ID, LOGIC_ID);
0229 ALTER TABLE LED_POWER_SUPPLY_STATUS ADD CONSTRAINT LED_POWER_SUPPLY_STATUS_FK1
0230 FOREIGN KEY (IOV_ID) REFERENCES LED_SYSTEM_IOV (IOV_ID);
0231
0232 CREATE TABLE LED_POWER_MONBOX_STATUS (
0233 IOV_ID NUMBER(10) NOT NULL,
0234 LOGIC_ID INTEGER,
0235 PM_VOLTAGE FLOAT,
0236 PM_CURRENT FLOAT,
0237 PM_STATE INTEGER
0238
0239 )
0240 PARTITION BY RANGE ("IOV_ID")
0241 (PARTITION "LED_PWRMNBX_S_0" VALUES LESS THAN (MAXVALUE))
0242 ;
0243
0244 ALTER TABLE LED_POWER_MONBOX_STATUS ADD CONSTRAINT LED_POWER_MONBOX_STATUS_PK
0245 PRIMARY KEY (IOV_ID, LOGIC_ID);
0246 ALTER TABLE LED_POWER_MONBOX_STATUS ADD CONSTRAINT LED_POWER_MONBOX_STATUS_FK1
0247 FOREIGN KEY (IOV_ID) REFERENCES LED_SYSTEM_IOV (IOV_ID);
0248
0249 CREATE TABLE LED_POWER_CTRLBOX_STATUS (
0250 IOV_ID NUMBER(10) NOT NULL,
0251 LOGIC_ID NUMBER(10),
0252 CB_EXT_COUNT FLOAT,
0253 CB_EXT_FREQ FLOAT,
0254 CB_INT_COUNT FLOAT,
0255 CB_INT_FREQ FLOAT,
0256 CB_OUT_COUNT FLOAT,
0257 CB_OUT_FREQ FLOAT
0258 )
0259 PARTITION BY RANGE ("IOV_ID")
0260 (PARTITION "LED_PWRCTRLBX_S_0" VALUES LESS THAN (MAXVALUE))
0261 ;
0262
0263 ALTER TABLE LED_POWER_CTRLBOX_STATUS ADD CONSTRAINT LED_POWER_CTRLBOX_STATUS_PK
0264 PRIMARY KEY (IOV_ID, LOGIC_ID);
0265 ALTER TABLE LED_POWER_CTRLBOX_STATUS ADD CONSTRAINT
0266 LED_POWER_CTRLBOX_STATUS_FK1
0267 FOREIGN KEY (IOV_ID) REFERENCES LED_SYSTEM_IOV (IOV_ID);
0268
0269 SET HEADING OFF
0270 SET ECHO OFF
0271 SPOOL led_grants.sql
0272 SELECT 'GRANT SELECT ON ', TABLE_NAME, ' TO CMS_ECAL_R;' FROM USER_TABLES;
0273 SELECT 'GRANT SELECT ON ', TABLE_NAME, ' TO CMS_WBM_R;' FROM USER_TABLES;
0274 SPOOL OFF;
0275
0276 @led_grants
0277
0278 SPOOL led_synonyms.sql
0279 SELECT 'CREATE OR REPLACE SYNONYM ', TABLE_NAME, ' FOR CMS_ECAL_COND.', TABLE_NAME, ';' FROM USER_TABLES WHERE TABLE_NAME LIKE 'LED%';
0280 SPOOL OFF
0281
0282 @led_synonyms
0283
0284 SET HEADING ON