Project Lock Request needs to be able to work equivalently to how it currently works from REGI

[rma-rripken]

Currently in REGI a user can get some project locks. When a lock is acquired the database grabs the username, os_user, machine_name and program out of the database sesssion and stores those into the lock. The user gets back a lock id (guid).

REGI does not persist the lock_id.

If REGI gets some locks and then is killed it has no chance to return/release the locks.

When REGI starts after a crash it can query the db for project locks and realize that there are locks in the db for the current user that the current instance doesn't know about. When that is detected REGI can prompt the user to sort it out.

If we just call the same pl/sql procs from CDA then CDA is going to pull the username, os_user, machine_name, program of the CDA server.

Unless we do something os_user, machine_name and program will be the same for all CDA calls.
CDA might be sticking the db_user into username - to be verified. But even if that is true its likely the database username not the REGI username.

So far one proposed solution is to refactor REQUEST_LOCK into two procedures. One that pulls the values out of the env and one that does the actual work like:

function request_lock2(
    p_project_id      in varchar2,
    p_application_id  in varchar2,
    p_revoke_existing in varchar2 default 'F',
    p_revoke_timeout  in integer  default 30,
    p_office_id       in varchar2 default null,
    p_username        in varchar2,
    p_osuser          in varchar2,
    p_program         in varchar2,
    p_machine         in varchar2)
    return varchar2
is
    revocation_denied exception;
    already_locked    exception;
    pragma            exception_init(revocation_denied, -20998);
    pragma            exception_init(already_locked,    -00001);
    pragma            autonomous_transaction;

    l_lock_id         varchar2(40);
    l_do_lock         boolean := true;
    l_office_id       varchar2(16);
    l_already_locked  boolean := false;
    l_id              integer;
    l_queue_name      varchar2(61);
    l_text_msg        varchar2(32767);
begin
    -------------------
    -- sanity checks --
    -------------------
    if p_revoke_existing is null then
        cwms_err.raise('NULL_ARGUMENT', 'p_revoke_existing');
    end if;
    if p_revoke_timeout is null then
        cwms_err.raise('NULL_ARGUMENT', 'p_revoke_timeout');
    end if;
    -----------------
    -- do the work --
    -----------------
    if is_locked(p_project_id, p_application_id, p_office_id) = 'T' then
        if p_revoke_existing in ('T', 't') then
            begin
                revoke_lock(
                        p_project_id,
                        p_application_id,
                        p_revoke_timeout,
                        p_office_id);
            exception
                when revocation_denied then l_do_lock := false;
            end;
        else
            l_do_lock := false;
        end if;
    end if;
    if l_do_lock then
        l_lock_id := rawtohex(sys_guid());

        begin
            insert
            into at_project_lock
            ( lock_id,
              project_code,
              application_id,
              acquire_time,
              session_user,
              os_user,
              session_program,
              session_machine
            )
            values ( l_lock_id,
                     cwms_loc.get_location_code(p_office_id, p_project_id),
                     lower(p_application_id),
                     systimestamp at time zone 'UTC',
                     p_username,
                     p_osuser,
                     p_program,
                     p_machine
                   );
        exception
            when already_locked then
                ----------------------------------------------
                -- encountered a race condition and another --
                -- lock attempt beat us to the punch        --
                ----------------------------------------------
                l_already_locked := true;
        end;
        if l_already_locked then
            l_lock_id := null;
        else
            commit;
            ------------------------------
            -- publish the state change --
            ------------------------------
            l_office_id := cwms_util.get_db_office_id(p_office_id);
            l_queue_name  := l_office_id||'_'||'STATUS';
            l_text_msg := '
            <cwms_message type="State">
               <property name="new state"   type="String"> locked        </property>
               <property name="old state"   type="String"> unlocked      </property>
               <property name="action"      type="String"> lock acquired </property>
               <property name="office"      type="String"> $office       </property>
               <property name="project"     type="String"> $project      </property>
               <property name="application" type="String"> $application  </property>
               <property name="user"        type="String"> $user         </property>
            </cwms_message>';
            l_text_msg := replace(l_text_msg, '$office',      l_office_id);
            l_text_msg := replace(l_text_msg, '$project',     dbms_xmlgen.convert(p_project_id));
            l_text_msg := replace(l_text_msg, '$application', dbms_xmlgen.convert(lower(p_application_id)));
            l_text_msg := replace(l_text_msg, '$user',        cwms_util.get_user_id);
            l_id := cwms_msg.publish_message(l_text_msg, l_queue_name, true);
        end if;
    end if;
    return l_lock_id;
end request_lock2;

function request_lock(
   p_project_id      in varchar2,
   p_application_id  in varchar2,
   p_revoke_existing in varchar2 default 'F',
   p_revoke_timeout  in integer  default 30,
   p_office_id       in varchar2 default null)
   return varchar2
is
   l_username        varchar2(30);
   l_osuser          varchar2(30);
   l_program         varchar2(64);
   l_machine         varchar2(64);

begin
    select username,
           osuser,
           program,
           machine
    into l_username,
        l_osuser,
        l_program,
        l_machine
    from v$session
    where sid = sys_context('userenv', 'sid');

    return request_lock2(
        p_project_id,
        p_application_id,
        p_revoke_existing,
        p_revoke_timeout,
        p_office_id,
        l_username,
        l_osuser,
        l_program,
        l_machine);

end request_lock;

There may be downsides - not sure how to verify that users are who they claim to be.

[rma-rripken]

Another possible direction is to look into whether REGI could start persisting the locks it has obtained.

[rma-rripken]

@rma-psmorris

[rma-rripken]

Database changes similar to those above were made to cwms_database. @MikeNeilson Before the associated Project changes can be merged I need the database pl/sql changes pulled into a release that CDA can use. Is that something I should do or are there existing plans to make a new db release?

[MikeNeilson]

We'll have to make a special release. I can take care of it tomorrow. Though the only thing required to is to have it under the "release/" branch name and the system will take care of making the artifacts (so long as you update the pom.xml in the database project.)