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

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.

Start by reviewing the existing request_lock procedure, the proposed request_lock2 interface, the at_project_lock columns, and the v$session lookup described in the issue. Determine and document how CDA should supply and verify lock identity, then confirm equivalent lock acquisition, conflict handling, release behavior, and user-facing recovery after a REGI crash.