-- FrmFirebird.QTrans
--
-- Текущие транзакции в базе
--

select
  tr.MON$TRANSACTION_ID     as MON$TRANSACTION_ID,
  tr.MON$ATTACHMENT_ID      as MON$ATTACHMENT_ID,

  att.MON$REMOTE_ADDRESS    as att_ip,
  att.MON$REMOTE_PROCESS    as att_exe,

  -- выкусить текст после последнего слэша, и в верхний регистр
  upper( reverse( substring( reverse(att.MON$REMOTE_PROCESS) from 1 for position('\', reverse(replace(att.MON$REMOTE_PROCESS,'/','\')), 1)-1 ) ) )
                            as att_exe2,

  case tr.MON$STATE
    when 0 then 'idle'
    when 1 then 'active'
    else        tr.MON$STATE
  end                       as state,

  tr.MON$TIMESTAMP          as timestamp_,
  tr.MON$TOP_TRANSACTION    as top_transaction,
  tr.MON$OLDEST_TRANSACTION as oldest_transaction,
  tr.MON$OLDEST_ACTIVE      as oldest_active,

  case tr.MON$ISOLATION_MODE
    when 0 then 'consistency'
    when 1 then 'concurrency'
    when 2 then 'RC record version'
    when 3 then 'RC no record version'
    else        tr.MON$ISOLATION_MODE
  end                       as isolation_mode,

  case tr.MON$LOCK_TIMEOUT
    when -1 then 'wait'
    when  0 then 'no wait'
    else         'timeout ' || tr.MON$LOCK_TIMEOUT
  end                       as lock_timeout,

  tr.MON$READ_ONLY          as read_only,
  tr.MON$AUTO_COMMIT        as auto_commit,
  tr.MON$AUTO_UNDO          as auto_undo,
  tr.MON$STAT_ID            as MON$STAT_ID,

  -- сколько часов коннект существует
  (datediff(HOUR,   tr.MON$TIMESTAMP, current_timestamp)) as dt_hour,

  -- сколько минут коннект существует
  (datediff(MINUTE, tr.MON$TIMESTAMP, current_timestamp)) as dt_minute,

  -- если транзакция (или коннект?) активен более 180 минут (3 часа) - выделим его красным шрифтом
  iif((datediff(MINUTE, tr.MON$TIMESTAMP, current_timestamp)) > 180, 'RED', '') as ROW_COLOR

from mon$transactions tr
  left join mon$attachments att on (att.mon$attachment_id =  tr.MON$ATTACHMENT_ID)

order by tr.MON$TIMESTAMP

-- FrmFirebird.QStat
--
-- Запросы в указанной транзакции
--

select
  st.MON$STATEMENT_ID       as MON$STATEMENT_ID,
  st.MON$ATTACHMENT_ID      as MON$ATTACHMENT_ID,
  st.MON$TRANSACTION_ID     as MON$TRANSACTION_ID,

  att.MON$REMOTE_ADDRESS    as att_ip,
  att.MON$REMOTE_PROCESS    as att_exe,

  case st.MON$STATE
    when 0 then 'idle'
    when 1 then 'active'
    when 2 then 'stalled'
    else        st.MON$STATE
  end                       as STATE,

  st.MON$TIMESTAMP          as TIMESTAMP_,

  -- сколько минут запрос существует
  (datediff(MINUTE, st.MON$TIMESTAMP, current_timestamp)) as dt_minute,

  st.MON$STAT_ID            as MON$STAT_ID

from mon$statements st
  left join mon$attachments att on (att.mon$attachment_id =  st.MON$ATTACHMENT_ID)

where (st.MON$TRANSACTION_ID = :TRANSACTION_ID)

order by 
  st.MON$STATEMENT_ID

-- FrmFirebird.QStatSQL
--
-- Текст указанного запроса
--

select 
  mon$sql_text

from mon$statements

where (mon$statement_id = :statement_id)