EcalCondDB/sql/update_subrun_iov_dates_procedure.sql

0001 /*
0002  *
0003  * Procedure to validate an subrun IoV to be inserted and update a previous
0004  * IoV so that there are no overlaps.
0005  * The run_iov is essentially another tag for a subrun-type IoV table
0006  */
0007 CREATE OR REPLACE PROCEDURE update_subrun_iov_dates
0008 ( my_table IN VARCHAR2,
0009   start_col IN VARCHAR2,
0010   end_col IN VARCHAR2,
0011   new_start IN DATE,
0012   new_end IN OUT DATE,
0013   new_tag_id IN NUMBER,
0014   new_run_iov_id IN NUMBER ) IS
0015
0016   sql_str VARCHAR(1000);
0017   future_start DATE;
0018   last_start DATE;
0019   last_end DATE;
0020
0021   BEGIN
0022     -- Ensure IoV time has positive duration
0023     IF new_end <= new_start THEN
0024        raise_application_error(-20000, 'IoV must have ' || start_col || ' < ' || end_col);
0025     END IF;
0026
0027     -- Truncate for IoVs in the future of this one
0028     -- Fetch IOV immediately after this one
0029     sql_str := 'SELECT min(' || start_col || ') FROM ' || my_table ||
0030                ' WHERE ' || start_col || ' > :s AND tag_id = :t AND run_iov_id = :r';
0031     EXECUTE IMMEDIATE sql_str INTO future_start USING new_start, new_tag_id, new_run_iov_id;
0032
0033     IF future_start IS NOT NULL THEN
0034       -- truncate this IOV
0035       new_end := future_start;
0036     END IF;
0037
0038     -- Fetch the most recent IoV prior to this one
0039     sql_str := 'SELECT max(' || start_col || ') FROM ' || my_table ||
0040                ' WHERE ' || start_col || ' <= :s AND tag_id = :t AND run_iov_id = :r';
0041     EXECUTE IMMEDIATE sql_str INTO last_start USING new_start, new_tag_id, new_run_iov_id;
0042
0043     IF last_start IS NULL THEN
0044         -- table has no previous data for this tag, nothing to do
0045         return;
0046     END IF;
0047
0048     -- Fetch the end of this most recent IoV
0049     sql_str := 'SELECT ' || end_col || ' FROM ' || my_table || ' WHERE ' || start_col || ' = :s AND tag_id = :t AND run_iov_id = :r';
0050     EXECUTE IMMEDIATE sql_str INTO last_end USING last_start, new_tag_id, new_run_iov_id;
0051
0052     IF new_start = last_start THEN
0053         -- Attempted to insert overlapping IoV!
0054         raise_application_error(-20020, 'New IOV ''' || start_col || ''' overlaps older ''' || start_col || '''');
0055     ELSIF new_start < last_end THEN
0056        -- Truncate the last IoV
0057        sql_str := 'UPDATE ' || my_table || ' SET ' || end_col || ' = :new_start' ||
0058                   ' WHERE ' || start_col || ' = :last_start AND ' || end_col || ' = :last_end AND tag_id = :t AND run_iov_id = :r';
0059        EXECUTE IMMEDIATE sql_str USING new_start, last_start, last_end, new_tag_id, new_run_iov_id;
0060     END IF;
0061   END;
0062 /
0063 show errors;