Back to home page

Project CMSSW displayed by LXR

 
 

    


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

0001 /*  
0002  * 
0003  * Procedure to validate an IoV to be inserted and update a previous
0004  * IoV so that there are no overlaps.
0005  * 
0006  * GO: september 2010
0007  * This new procedure allows multiple IOVs with the same start date
0008  * IOVs have a mask based on which one can assign a given IOV to a given table
0009  *
0010  * To test the script you have to run generate_iovs.pl before and send its
0011  * output to ttt.sql
0012  * ./generate_iovs.sql > ttt.sql
0013  * then run this script and check results using report.txt
0014  * 
0015  * TODO: MAKE IT INDEPENDENT ON TRIGGER NAME
0016  *       FINISH
0017  *
0018  */
0019 
0020 WHENEVER SQLERROR EXIT
0021 
0022 CREATE OR REPLACE PROCEDURE TESTDB IS
0023    gname VARCHAR2(64);
0024 BEGIN
0025    SELECT GLOBAL_NAME INTO gname FROM GLOBAL_NAME;
0026    IF gname = 'INT2R.CERN.CH' THEN
0027       -- do nothing
0028       return;
0029    ELSE
0030       RAISE_APPLICATION_ERROR(-20001, 'Wrong DB name: ' || gname);
0031    END IF;
0032 END;
0033 /
0034 
0035 EXEC TESTDB;
0036 
0037 /* Ok: we are running on the right database */
0038 
0039 CREATE OR REPLACE FUNCTION bitnot (x IN NUMBER) RETURN NUMBER AS
0040 BEGIN
0041   return (-1-x);
0042 END;
0043 /
0044 
0045 CREATE OR REPLACE PROCEDURE update_iov_dates_test
0046 ( my_table IN VARCHAR2,
0047   my_mask IN NUMBER,
0048   my_sequence IN VARCHAR2,
0049   my_dattable IN VARCHAR2,  
0050   start_col IN VARCHAR2,
0051   end_col IN VARCHAR2,
0052   new_start IN DATE,
0053   new_end IN OUT DATE,
0054   new_tag_id IN NUMBER ) IS
0055   sql_str    VARCHAR(1000);
0056   tn         VARCHAR(25);
0057   tnc        NUMBER;
0058   last_start DATE;
0059   last_iov   NUMBER;
0060   new_iov    NUMBER;
0061   zero_iov   NUMBER;
0062   last_mask  NUMBER;
0063   new_mask   NUMBER;
0064   my_rows    NUMBER;
0065   i          NUMBER;
0066   j          NUMBER;
0067 
0068   BEGIN
0069     dbms_output.enable(100000);
0070     -- Ensure IoV time has positive duration
0071     IF new_end <= new_start THEN
0072        raise_application_error(-20000, 'IOV must have ' || start_col || ' < ' 
0073                                || end_col);
0074     END IF;
0075     -- Look for records containing this mask
0076     sql_str := 'SELECT COUNT(IOV_ID) FROM ' || my_table || 
0077       ' WHERE BITAND(MASK, :my_mask) > 0 AND TAG_ID = :t AND ' || end_col || 
0078         ' >= TO_DATE(''31-12-9999 23:59:59'', ''DD-MM-YYYY HH24:MI:SS'')';
0079     EXECUTE IMMEDIATE sql_str INTO my_rows USING my_mask, new_tag_id;
0080     IF my_mask != 0 THEN
0081        dbms_output.put_line('----------------------------------------');
0082     END IF;
0083     dbms_output.put_line(sql_str);
0084     dbms_output.put_line('Searching for mask ' || my_mask || ' tag ' || new_tag_id);
0085     dbms_output.put_line('Found ' || my_rows || ' rows with good mask');
0086     -- Case select
0087     IF my_rows = 0 THEN
0088        -- do nothing, just insert the row
0089        dbms_output.put_line('Inserting row with mask ' || my_mask || ' and tag ' || new_tag_id);        
0090        return;
0091     ELSE
0092        -- IOV_ID found with the same bits: update them
0093        FOR i IN 1..my_rows LOOP
0094           -- look for IOV's with the same bits on
0095           sql_str := 'SELECT IOV_ID, MASK, SINCE FROM ' || my_table || 
0096              ' WHERE BITAND(MASK, ' ||
0097              my_mask || ') > 0 AND TAG_ID = :t AND ' || end_col || 
0098              ' >= TO_DATE(''31-12-9999 23:59:59'', ''DD-MM-YYYY HH24:MI:SS'')' ||
0099              ' AND ROWNUM = 1';
0100           dbms_output.put_line(sql_str);
0101           EXECUTE IMMEDIATE sql_str INTO last_iov, last_mask, last_start USING new_tag_id;
0102           dbms_output.put_line('Required insertion of data with mask: ' || my_mask);
0103           dbms_output.put_line('Found  data with mask               : ' || last_mask 
0104              || ' and IOV ' || last_iov);
0105           dbms_output.put_line('       and start date on ' || last_start);
0106           -- update the mask of those measurements not yet redone
0107           new_mask := BITAND(last_mask, BITNOT(my_mask));
0108           IF new_mask > 0 THEN 
0109              sql_str := 'UPDATE ' || my_table || ' SET MASK = BITAND(' ||
0110                 ':last_mask, BITNOT(:my_mask)) WHERE IOV_ID = :last_iov AND BITAND( ' 
0111                 || ':last_mask , BITNOT(:my_mask)) > 0';
0112              dbms_output.put_line(sql_str);
0113              EXECUTE IMMEDIATE sql_str USING last_mask, my_mask, last_iov, last_mask,
0114                 my_mask;        
0115              -- insert new record and update related tables (use mask 0 to avoid
0116              -- retriggering this procedure)
0117              last_mask := BITAND(last_mask, my_mask);
0118              sql_str := 'INSERT INTO ' || my_table || ' VALUES (' ||
0119                 my_sequence || '.NextVal, 0, :tag_id, :since, :till)';
0120              dbms_output.put_line('INSERTing new record with mask ' || last_mask || 
0121                 ' and since, till = ' || last_start || ', ' || new_start);
0122              EXECUTE IMMEDIATE sql_str USING new_tag_id, last_start, new_start;
0123              -- get the last inserted id
0124              sql_str := 'SELECT IOV_ID FROM ' || my_table || 
0125                 ' WHERE MASK = 0';
0126              EXECUTE IMMEDIATE sql_str INTO zero_iov;
0127              dbms_output.put_line('Found IOV_ID = ' || zero_iov || 
0128                 ' with mask = 0');
0129              -- still we have to update tables
0130              j := 1;
0131              WHILE j <= my_mask LOOP
0132                 -- we loop on tables looking for the just changed IOV_ID and modify it as the last one
0133                 sql_str := 'SELECT COUNT(TABLE_NAME) FROM ' || my_dattable || 
0134                    ' WHERE BITAND(BITAND(ID, :j), :my_mask) > 0';
0135                 EXECUTE IMMEDIATE sql_str INTO tnc USING j, my_mask;
0136                 IF tnc > 0 THEN
0137                    sql_str := 'SELECT TABLE_NAME FROM ' || 
0138                       my_dattable || 
0139                       ' WHERE BITAND(BITAND(ID, :j), :my_mask) > 0';
0140                    EXECUTE IMMEDIATE sql_str INTO tn USING j, my_mask;
0141                    dbms_output.put_line('Found table ' || tn || 
0142                       ' to be updated');
0143                    dbms_output.put_line('      Setting IOV_ID = ' || 
0144                       zero_iov || ' from ' || last_iov);
0145                    sql_str := 'UPDATE ' || tn || ' SET IOV_ID = :zero_iov WHERE IOV_ID = '
0146                       || ':last_iov';
0147                    EXECUTE IMMEDIATE sql_str USING zero_iov, last_iov;
0148                    dbms_output.put_line(sql_str);
0149                 END IF;
0150                 j := j * 2;
0151              END LOOP;
0152              -- remask last insert id
0153              sql_str := 'UPDATE ' || my_table || ' SET MASK = :my_mask WHERE MASK = 0';
0154              EXECUTE IMMEDIATE sql_str USING my_mask;   
0155              dbms_output.put_line(sql_str || ' using ' || my_mask);
0156           ELSE
0157            -- update the till of this last measurement
0158              sql_str := 'UPDATE ' || my_table || ' SET TILL = :new_start ' || 
0159                 ' WHERE IOV_ID = :last_iov';
0160              EXECUTE IMMEDIATE sql_str USING new_start, last_iov;
0161              dbms_output.put_line('Updated IOV ' || last_iov || ' Set TILL = ' ||
0162                 new_start);
0163           END IF;
0164        END LOOP;
0165     END IF;
0166   END;
0167 /
0168 
0169 show errors;
0170 
0171 SET ECHO OFF
0172 SET HEADING OFF 
0173 SPOOL tmp.sql
0174 SELECT 'DROP TABLE ' || TABLE_NAME || ';' FROM USER_TABLES WHERE 
0175         TABLE_NAME LIKE 'TEST%';
0176 SELECT 'DROP SEQUENCE ' || SEQUENCE_NAME || ';' FROM USER_SEQUENCES WHERE
0177         SEQUENCE_NAME LIKE 'TEST%';
0178 SPOOL OFF
0179 @tmp;
0180 
0181 SET HEADING ON
0182 
0183 CREATE TABLE TEST_IOV (
0184   IOV_ID NUMBER,
0185   MASK NUMBER,
0186   TAG_ID NUMBER,
0187   SINCE DATE,
0188   TILL DATE
0189 );
0190 
0191 CREATE TABLE TEST_DATTABLE_ID (
0192   ID NUMBER,
0193   TABLE_NAME VARCHAR2(25),
0194   DESCRIPTION VARCHAR2(255)
0195 );
0196 
0197 INSERT INTO TEST_DATTABLE_ID VALUES (1, 'TEST_A', '');
0198 INSERT INTO TEST_DATTABLE_ID VALUES (2, 'TEST_B', '');
0199 INSERT INTO TEST_DATTABLE_ID VALUES (4, 'TEST_C', '');
0200 
0201 CREATE TABLE TEST_A (
0202   IOV_ID NUMBER,
0203   DATA NUMBER
0204 );
0205 
0206 CREATE TABLE TEST_B (
0207   IOV_ID NUMBER,
0208   DATA NUMBER
0209 );
0210 
0211 CREATE TABLE TEST_C (
0212   IOV_ID NUMBER,
0213   DATA NUMBER
0214 );
0215 
0216 CREATE OR REPLACE TRIGGER TEST_IOV_TG
0217         BEFORE INSERT ON TEST_IOV
0218         REFERENCING NEW AS newiov
0219         FOR EACH ROW
0220         CALL update_iov_dates_test('TEST_IOV', :newiov.mask,
0221         'TEST_IOV_SQ', 'TEST_DATTABLE_ID', 'SINCE', 'TILL',
0222         :newiov.since,
0223         :newiov.till, :newiov.tag_id)
0224 /
0225 
0226 show errors;
0227 
0228 alter session set NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS';
0229 set linesize 110
0230 SET SERVEROUTPUT ON
0231 
0232 /* simulate measurements
0233    1 made on 1, 2, 3, 4, 6 feb
0234    2 made on 4, 6, 7 feb
0235    4 made on 5 feb
0236 */
0237 
0238 CREATE SEQUENCE TEST_IOV_SQ START WITH 1 NOCACHE;
0239 insert into test_iov values(TEST_IOV_SQ.NextVal, 1, 1, 
0240         to_date('01-01-2010 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), 
0241         to_date('31-12-9999 23:59:59', 'DD-MM-YYYY HH24:MI:SS'));
0242 INSERT INTO TEST_A VALUES (1, 1);
0243 insert into test_iov values(TEST_IOV_SQ.NextVal, 1, 1, 
0244         to_date('02-01-2010 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), 
0245         to_date('31-12-9999 23:59:59', 'DD-MM-YYYY HH24:MI:SS'));
0246 INSERT INTO TEST_A VALUES (2, 2);
0247 insert into test_iov values(TEST_IOV_SQ.NextVal, 1, 1, 
0248         to_date('03-01-2010 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), 
0249         to_date('31-12-9999 23:59:59', 'DD-MM-YYYY HH24:MI:SS'));
0250 INSERT INTO TEST_A VALUES (3, 3);
0251 insert into test_iov values(TEST_IOV_SQ.NextVal, 3, 1, 
0252         to_date('04-01-2010 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), 
0253         to_date('31-12-9999 23:59:59', 'DD-MM-YYYY HH24:MI:SS'));
0254 INSERT INTO TEST_A VALUES (4, 4);
0255 INSERT INTO TEST_B VALUES (4, 1);
0256 insert into test_iov values(TEST_IOV_SQ.NextVal, 4, 1, 
0257         to_date('05-01-2010 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), 
0258         to_date('31-12-9999 23:59:59', 'DD-MM-YYYY HH24:MI:SS'));
0259 INSERT INTO TEST_C VALUES (5, 1);
0260 insert into test_iov values(TEST_IOV_SQ.NextVal, 3, 1, 
0261         to_date('06-01-2010 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), 
0262         to_date('31-12-9999 23:59:59', 'DD-MM-YYYY HH24:MI:SS'));
0263 INSERT INTO TEST_A VALUES (6, 5);
0264 INSERT INTO TEST_B VALUES (7, 2);
0265 insert into test_iov values(TEST_IOV_SQ.NextVal, 2, 1, 
0266         to_date('07-01-2010 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), 
0267         to_date('31-12-9999 23:59:59', 'DD-MM-YYYY HH24:MI:SS'));
0268 INSERT INTO TEST_B VALUES (8, 3);
0269 
0270 SELECT * FROM TEST_IOV;
0271 SELECT * FROM TEST_IOV JOIN TEST_A ON TEST_IOV.IOV_ID = TEST_A.IOV_ID 
0272         WHERE BITAND(MASK, 1) = 1 ORDER BY SINCE ASC;
0273 SELECT * FROM TEST_IOV JOIN TEST_B ON TEST_IOV.IOV_ID = TEST_B.IOV_ID 
0274         WHERE BITAND(MASK, 2) = 2 ORDER BY SINCE ASC;
0275 SELECT * FROM TEST_IOV JOIN TEST_C ON TEST_IOV.IOV_ID = TEST_C.IOV_ID 
0276         WHERE BITAND(MASK, 4) = 4 ORDER BY SINCE ASC;