EcalCondDB/sql/create_led_system.sql

0001 /*
0002         create_led_system
0003         created by Giovanni.Organtini@roma1.infn.it 2010
0004
0005         stores data about LED system power and control
0006
0007         Version: 20100901 (partitioned tables)
0008 */
0009
0010 /* channels */
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 /* Tags */
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 /* The following table associates a unique id to each table or group of
0098    tables used in IOV table to define IOV per group of tables */
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 /* Tables to store interval of validities */
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 /* low frequency status tables */
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 /* status tables */
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 /* This is an 8 bit integer encoding
0238                                    4 2-bit status words */
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