set term ^;
create or alter trigger trgcommit on transaction commit as
begin
end^
execute block as begin
    begin
    execute statement 'drop sequence g';
    when any do begin end
    end
    begin
    execute statement 'create sequence g';
    when any do begin end
    end
end^
set term ;^
commit;

recreate table t(id int primary key, tid int default current_transaction, f1 int, f2 int);
commit;
create index t_f1 on t(f1);
create index t_f2 on t(f2);
commit;

recreate exception ex_crossed_dirty 'crossed_intervals_encountered_in_uncommitted_data';
recreate exception ex_crossed_flushed 'crossed_intervals_encountered_in_committed_data';
commit;

set term ^;
create or alter trigger T_AIU for T
active after insert or update position 0 as
begin
  rdb$set_context( 'USER_TRANSACTION', 'ID_NEW'||cast(new.id as varchar(11)), cast(new.id as varchar(11)) );
  rdb$set_context( 'USER_TRANSACTION', 'F1_NEW'||cast(new.id as varchar(11)), cast(new.f1 as varchar(11)) );
  rdb$set_context( 'USER_TRANSACTION', 'F2_NEW'||cast(new.id as varchar(11)), cast(new.f2 as varchar(11)) );
end^
set term ;^
commit;

set term ^;
create or alter trigger trgcommit on transaction commit as
declare variable v_mode varchar(1);
declare v_id int;
declare v_f1 int;
declare v_f2 int;
begin
    if (NOT exists(select * from mon$context_variables c where c.mon$transaction_id=current_transaction and c.mon$variable_name starting with 'ID_NEW'))
    then
      exit;

    -- first check uncommitted data in all other transactions:
    if (
            exists(
                with c as(
                    select
                        c.mon$transaction_id tid
                        ,substring(c.mon$variable_name from 3) vn
                        ,min(cast( iif( c.mon$variable_name starting with 'ID_NEW', c.mon$variable_value, null) as int) ) ctx_id_new
                        ,min(cast( iif( c.mon$variable_name starting with 'F1_NEW', c.mon$variable_value, null) as int) ) ctx_f1_new
                        ,min(cast( iif( c.mon$variable_name starting with 'F2_NEW', c.mon$variable_value, null) as int) ) ctx_f2_new
                    from mon$context_variables c
                    group by 1,2
                )
                select *
                from c ca join c cb
                    on ca.ctx_id_new<>cb.ctx_id_new
                        and sign(ca.ctx_f1_new - cb.ctx_f2_new) * sign(cb.ctx_f1_new - ca.ctx_f2_new)>0
                 ) -- exists
       )
    then
       exception ex_crossed_dirty;

    -- then check already committed data:
    if (
          exists(
                  select *
                  from
                      (
                        select tid, f1, f2 from t where tid = current_transaction
                      ) ta
                      join t tb on
                        ta.tid<>tb.tid
                        and (   ta.f1 between tb.f1 and tb.f2
                                or ta.f2 between tb.f1 and tb.f2
                                or tb.f1 between ta.f1 and ta.f2
                                or tb.f2 between ta.f1 and ta.f2
                            )

                ) -- exists
        )
     then
       exception ex_crossed_flushed;
end^
set term ;^
commit;

CREATE OR ALTER VIEW V_CROSS_M(
    TID_A,
    VN_A,
    ID_A,
    F1_A,
    F2_A,
    TID_B,
    VN_B,
    ID_B,
    F1_B,
    F2_B)
AS
with c as(
        select
            c.mon$transaction_id tid
            ,substring(c.mon$variable_name from 3) vn
            ,min(cast( iif( c.mon$variable_name starting with 'ID_NEW', c.mon$variable_value, null) as int) ) ctx_id_new
            ,min(cast( iif( c.mon$variable_name starting with 'F1_NEW', c.mon$variable_value, null) as int) ) ctx_f1_new
            ,min(cast( iif( c.mon$variable_name starting with 'F2_NEW', c.mon$variable_value, null) as int) ) ctx_f2_new
        from mon$context_variables c
        group by 1,2
    )
    select
     ca.tid tid_a, ca.vn vn_a, ca.ctx_id_new id_a, ca.ctx_f1_new f1_a, ca.ctx_f2_new f2_a
    ,cb.tid tid_b, cb.vn vn_b, cb.ctx_id_new id_b, cb.ctx_f1_new f1_b, cb.ctx_f2_new f2_b
    from c ca join c cb
        on ca.ctx_id_new<>cb.ctx_id_new
            and sign(ca.ctx_f1_new - cb.ctx_f2_new) * sign(cb.ctx_f1_new - ca.ctx_f2_new)>0
;

CREATE OR ALTER VIEW V_CROSS_T(
    ID_A,
    TID_A,
    F1_A,
    F2_A,
    ID_B,
    TID_B,
    F1_B,
    F2_B)
AS
select
 ta.id id_a, ta.tid tid_a, ta.f1 f1_a, ta.f2 f2_a
,tb.id id_b, tb.tid tid_b, tb.f1 f1_b, tb.f2 f2_b
from
  (
    select id, tid, f1, f2 from t where tid = 2229
  ) ta
  join t tb on
    (ta.id<>tb.id)
    and (   ta.f1 between tb.f1 and tb.f2
            or ta.f2 between tb.f1 and tb.f2
            or tb.f1 between ta.f1 and ta.f2
            or tb.f2 between ta.f1 and ta.f2
        )
;

set echo off;
show version;
commit;
set transaction read committed record_version no wait;
set stat on;
set term ^;
execute block as
  declare n int = 1000;
  declare k int = 20;
  declare f_min int = 0;
  declare f_max int = 1000;
  declare i int;
  declare m int;
  declare v1 int;
  declare v2 int;
begin
  for
    with recursive 
    nx as(
      select 1 m from rdb$database union all select r.m+1 from nx r where r.m < :n
    )
    select m from nx 
    order by rand()
  into m
  do begin
    in autonomous transaction do
    begin
        insert into t(id, f1, f2)
        with recursive data as(
          select 1 x from rdb$database union all select r.x+1 from data r where r.x < :k
        )
        select gen_id(g,1), :m*10000 + 100*d.x+rand()*50 v1, :m*10000 + 100*d.x+51+rand()*49
        from data d
        order by rand();
        when any do begin end
    end
  end
end^
set term ;^
set stat off;
--commit;
set echo on;
select count(*) from v_cross_m;
set echo off;
rollback;
set echo on;
select count(*) from v_cross_t;
select count(*) from t;
rollback;