/*
  #2182 Лог коннектов (LOG_CONNECT) - доработки
    добавление поля LOG_CONNECT.REMOTE_PROTOCOL
*/

set autoddl on;
set STOPONERROR on;

select rdb$set_context('USER_SESSION', 'PATCH_VERSION', 331) from rdb$database;

-- проверка на корректность версии патча и базы
execute ibeblock
as
begin
  select 
    iif(DB_VERSION is null, 0, DB_VERSION)           as DB_VERSION,
    rdb$get_context('USER_SESSION', 'PATCH_VERSION') as PATCH_VERSION
  from S5 
  into :db_version, :patch_version;
  --
  if ((:db_version + 1) <> (:patch_version)) then begin
    msg = 'Ошибка! '                              || ibec_CRLF() ||
          'Непоследовательное наложение патчей.'  || ibec_CRLF() ||
          'DB_VERSION = '    || db_version        || ibec_CRLF() ||
          'PATCH_VERSION = ' || patch_version     || ibec_CRLF();

    ibec_MessageDlg(msg, __mtError, __mbCancel);
    a = 'Ошибка. Непоследовательное наложение патчей.'/0; -- кинем исключение что бы скрипт закончился по ошибке.
  end --if
end;

/* ********************************************************* */

alter table LOG_CONNECT add proto varchar(10);
alter table LOG_CONNECT alter proto position 6;

SET TERM ^ ;



CREATE OR ALTER TRIGGER LOG_CONNECT_BI_ID FOR LOG_CONNECT
ACTIVE BEFORE INSERT POSITION 0
as
declare variable v_attachment_name varchar(255);
declare variable v_user_name       varchar(32);
declare variable v_role_name       varchar(32);
declare variable v_proto           varchar(10);
declare variable v_ip              varchar(253);
declare variable v_exe             varchar(253);
declare variable v_c_dt            timestamp;
begin
  --
  select
    mon$attachments.mon$attachment_name as attachment_name,
    mon$attachments.mon$user            as user_name,
    mon$attachments.mon$role            as role_name,
    mon$attachments.mon$remote_protocol as proto,
    mon$attachments.mon$remote_address  as ip,
    mon$attachments.mon$remote_process  as exe,
    mon$attachments.mon$timestamp       as c_dt
  from mon$attachments
  where mon$attachments.mon$attachment_id = current_connection
  into :v_attachment_name,
       :v_user_name,
       :v_role_name,
       :v_proto,
       :v_ip,
       :v_exe,
       :v_c_dt
  ;
  --
  new.id              = gen_id(gen_log_connect_id,1);
  new.attachment_id   = current_connection;
  new.attachment_name = trim(trailing from v_attachment_name);
  new.user_name       = trim(trailing from v_user_name      );
  new.role_name       = trim(trailing from v_role_name      );
  new.proto           = trim(trailing from v_proto          );
  new.ip              = trim(trailing from v_ip             );
  new.exe             = trim(trailing from v_exe            );
  new.c_dt            = v_c_dt;
  new.d_dt            = NULL;

end
^

SET TERM ; ^

comment on column log_connect.proto is 'Значение из mon$attachments.mon$remote_protocol ("TCPv4" / "WNET" / "XNET" / NULL)';
comment on column log_connect.ip    is 'Значение из mon$attachments.mon$remote_address (IP-адрес для TCPv4 или локальный ID процесса для XNET)';

create index LOG_CONNECT_C_DT on LOG_CONNECT(C_DT);


/* ********************************************************* */
-- Изменения завершены, проставляем новую версию
update S5 set DB_VERSION = (select rdb$get_context('USER_SESSION', 'PATCH_VERSION') from rdb$database);
commit;