CREATE DOMAIN DSYSREGION AS
INTEGER
DEFAULT 1;

DECLARE EXTERNAL FUNCTION BASE_ID
RETURNS INTEGER BY VALUE
ENTRY_POINT 'f_1' MODULE_NAME 'consts';

COMMENT ON EXTERNAL FUNCTION BASE_ID IS 
'Идентификатор БД';

CREATE GENERATOR BASE_ID;
SET GENERATOR BASE_ID TO 1;

COMMENT ON GENERATOR BASE_ID IS 
'Идентификатор БД';

-- Для каждой БД значение по-умолчанию домена DSYSREGION функции BASE_ID() и генератора BASE_ID должны быть равны

CREATE GENERATOR GEN_TRANSACTION;


CREATE TABLE SYS$TRANSACTIONS (
    ID_TRANSACTION  DID NOT NULL /* DID = INTEGER NOT NULL */,
    ID_HOST         DID NOT NULL /* DID = INTEGER NOT NULL */,
    SYSDAY          DSYSDATE /* DSYSDATE = TIMESTAMP DEFAULT CURRENT_TIMESTAMP */
);
COMMENT ON TABLE SYS$TRANSACTIONS IS
'Репликация. История формирования пакетов репликации для каждого региона';

ALTER TABLE SYS$TRANSACTIONS ADD CONSTRAINT PK_SYS$TRANSACTIONS PRIMARY KEY (ID_TRANSACTION, ID_HOST);

CREATE TABLE UPDATE_LOG (
    ID_TABLE        DID /* DID = INTEGER NOT NULL */,
    ID_RECORD       DID /* DID = INTEGER NOT NULL */,
    OPER            DSMALL NOT NULL /* DSMALL = SMALLINT */,
    ID_TRANSACTION  DID NOT NULL /* DID = INTEGER NOT NULL */,
    SYSDAY          DSYSDATE /* DSYSDATE = TIMESTAMP DEFAULT CURRENT_TIMESTAMP */,
    SYSUSER         DSYSUSER  /* DSYSUSER = VARCHAR(12) DEFAULT CURRENT_USER */,
    SYSREGION       DSYSREGION /* DSYSREGION = INTEGER DEFAULT 1 */,
    SYSTRANSACTION  DSYSTRANSACTION /* DSYSTRANSACTION = INTEGER DEFAULT current_transaction */
);

COMMENT ON TABLE UPDATE_LOG IS 
'Репликация. Лог обновлений';


ALTER TABLE UPDATE_LOG ADD CONSTRAINT PK_UPDATE_LOG PRIMARY KEY (ID_TRANSACTION);
CREATE INDEX UPDATE_LOG_IDX1 ON UPDATE_LOG (ID_RECORD, ID_TABLE, ID_TRANSACTION);
CREATE INDEX UPDATE_LOG_IDX2 ON UPDATE_LOG (ID_TABLE, ID_RECORD);

CREATE OR ALTER TRIGGER UPDATE_LOG_BI0 FOR UPDATE_LOG
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  new.id_transaction = gen_id(gen_transaction,1);
  if (current_user = 'REPLICATOR' and gen_id(base_id, 0) = 1) then
    new.sysregion = coalesce(rdb$get_context('USER_TRANSACTION', 'FROMREGION'), new.sysregion);
end;

COMMENT ON TRIGGER UPDATE_LOG_BI0 IS 
'Если данные пришли через репликатор на главную базу (base_id = 1),
данные рассылаются остальным (кроме региона FROMREGION).
Контекстная переменная устанавливается в процедуре BEFORE_LOADDELTA';

CREATE TABLE UPDATE_TABLES (
    ID      DID /* DID = INTEGER NOT NULL */,
    NAME    DSTRING30 /* DSTRING30 = VARCHAR(30) DEFAULT '' NOT NULL */,
    PK      DSTRING30 /* DSTRING30 = VARCHAR(30) DEFAULT '' NOT NULL */,
    HOSTS   DSTRING84N /* DSTRING84N = VARCHAR(84) DEFAULT '' */,
    BODY    DLONGTEXT /* DLONGTEXT = BLOB SUB_TYPE 1 SEGMENT SIZE 256 */
);
ALTER TABLE UPDATE_TABLES ADD CONSTRAINT PK_UPDATE_TABLES PRIMARY KEY (ID);

CREATE OR ALTER TRIGGER UPDATE_TABLES_BIU0 FOR UPDATE_TABLES
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
as
declare variable r int;
begin
  if (coalesce(new.pk,'') = '') then
  select first 1 min(trim(s.rdb$field_name))
  from RDB$INDICES i
  join RDB$INDEX_SEGMENTS s on (s.RDB$INDEX_NAME = i.RDB$INDEX_NAME)
  join RDB$RELATION_CONSTRAINTS c on (c.rdb$index_name = i.RDB$INDEX_NAME)
  join RDB$RELATION_FIELDS r on (r.RDB$RELATION_NAME = i.rdb$relation_name and r.rdb$field_name = s.rdb$field_name)
  where c.rdb$constraint_type in ('PRIMARY KEY', 'UNIQUE') and upper(i.rdb$relation_name) = upper(new.name)
  group by c.rdb$constraint_type, i.rdb$relation_name
  having count(*) = 1
  into new.pk;

  if (exists(select * from RDB$RELATION_FIELDS where RDB$RELATION_NAME = new.name and rdb$field_name = 'SYSREGION')) then r=1; else r=0;

new.body = '/*  '||trim(new.NAME)||'.'||trim(new.PK)||'  */

CREATE OR ALTER TRIGGER R_LOG_'||trim(new.NAME)||' FOR '||trim(new.NAME)||'
ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 9999
AS
BEGIN
  if (gen_id(base_id, 0) <> base_id()) then exit;
  if (rdb$get_context(''USER_SESSION'', ''REPLICATOR_OFF'') = ''1'') then exit;'||
  iif(r = 1, '
  if (CURRENT_USER <> ''REPLICATOR'') then
  begin
    if ((updating or inserting) and (coalesce(new.sysregion,0) not in (0, gen_id(base_id,0)))) then exception regionerror;
    if (deleting and (coalesce(old.sysregion,0) not in (0, gen_id(base_id,0)))) then exception regionerror;
  end', cast('' as varchar(1)))||'
  if (CURRENT_USER <> ''REPLICATOR'' or gen_id(base_id, 0) = 1) then
  INSERT INTO UPDATE_LOG(ID_TABLE, ID_RECORD, OPER)
  VALUES('||cast(new.ID as Varchar(6))||', iif(deleting, old.'||trim(new.PK)||', new.'||trim(new.PK)||'), iif(updating, 1, iif(inserting, 2, 3)));
END;
';
end;

CREATE TABLE UPDATE_TRANSACTIONS (
    ID                DID NOT NULL /* DID = INTEGER NOT NULL */,
    NAME              DSTRING30N /* DSTRING30N = VARCHAR(30) DEFAULT '' */,
    SYSDAY            DSYSDATE /* DSYSDATE = TIMESTAMP DEFAULT CURRENT_TIMESTAMP */,
    ID_TRANSACTION    DINT /* DINT = INTEGER */,
    ID_LASTRECEIVE    DINT /* DINT = INTEGER */,
    PHONE             DSTRING30N /* DSTRING30N = VARCHAR(30) DEFAULT '' */,
    EMAIL             DSTRING50 /* DSTRING50 = VARCHAR(50) */,
    CONF              DLONGTEXT /* DLONGTEXT = BLOB SUB_TYPE 1 SEGMENT SIZE 256 */,
    DATE_TRANSACTION  DSYSDATE /* DSYSDATE = TIMESTAMP DEFAULT CURRENT_TIMESTAMP */,
    DATE_LASTRECEIVE  DSYSDATE /* DSYSDATE = TIMESTAMP DEFAULT CURRENT_TIMESTAMP */,
    DATE_LAST         DSYSDATE /* DSYSDATE = TIMESTAMP DEFAULT CURRENT_TIMESTAMP */,
    DATE_SEND         DSYSDATE /* DSYSDATE = TIMESTAMP DEFAULT CURRENT_TIMESTAMP */
);

COMMENT ON COLUMN UPDATE_TRANSACTIONS.DATE_TRANSACTION IS
'Дата формирования данных (регион)';

COMMENT ON COLUMN UPDATE_TRANSACTIONS.DATE_LASTRECEIVE IS 
'Последние подтвержденные данные (наши)';

COMMENT ON COLUMN UPDATE_TRANSACTIONS.DATE_LAST IS 
'Дата последнего получения';

COMMENT ON COLUMN UPDATE_TRANSACTIONS.DATE_SEND IS 
'Дата последней отравки';

ALTER TABLE UPDATE_TRANSACTIONS ADD CONSTRAINT PK_UPDATE_TRANSACTIONS PRIMARY KEY (ID);

CREATE OR ALTER PROCEDURE GET_MINTIME_CONNECTIONS
RETURNS (
    MIN_TIME TIMESTAMP)
AS
declare variable usr varchar(12) = 'SYSDBA';
declare variable psw varchar(12) = 'masterkey';
begin
--select first 1 ib_user, ib_psw from userlst
--where ib_user = 'SYSDBA' and visible = 1 and sysregion in (0, gen_id(base_id, 0))
--into usr, psw;

  for execute statement ('select min(mon$timestamp) from mon$transactions where mon$read_only = 0 and mon$attachment_id <> current_connection')
  as user :usr password :psw
  into min_time do
    suspend;
end;

COMMENT ON PROCEDURE GET_MINTIME_CONNECTIONS IS 
'Репликация. Время старта самой старой активной транзакции';

CREATE OR ALTER PROCEDURE AFTER_LOADDELTA (
    HOST_ID INTEGER,
    STARTID INTEGER,
    ENDID INTEGER)
AS
declare variable m integer;
begin
  select min(ID_LASTRECEIVE) from update_transactions into m;
-- Удаление мусора из истории
  delete from update_log where id_transaction <= :m;
  delete from sys$transactions where id_transaction <= :m;

  execute procedure on_after_loaddelta(:host_id, :startid, :endid);
end;

COMMENT ON PROCEDURE AFTER_LOADDELTA IS
'Репликация. После приема информации';

CREATE OR ALTER PROCEDURE AFTER_SAVEDELTA (
    HOST_ID INTEGER,
    STARTID INTEGER,
    ENDID INTEGER)
AS
begin
  exit;
end;

COMMENT ON PROCEDURE AFTER_SAVEDELTA IS 
'Репликация. После формирования пакета информации';

CREATE OR ALTER PROCEDURE BEFORE_LOADDELTA (
    HOST_ID INTEGER,
    STARTID INTEGER,
    ENDID INTEGER)
AS
begin
--Блокировка от одновременных изменений
  update UPDATE_TRANSACTIONS set ID = ID where id = :HOST_ID;
  rdb$set_context('USER_TRANSACTION', 'FROMREGION', :HOST_ID);
end;

COMMENT ON PROCEDURE BEFORE_LOADDELTA IS
'Репликация. Перед началом приема пакета информации';


CREATE OR ALTER PROCEDURE BEFORE_SAVEDELTA (
    HOST_ID INTEGER,
    OLDEST_TR INTEGER,
    ENDID INTEGER)
RETURNS (
    END_ID INTEGER)
AS
declare variable max_tr integer;
declare variable id_table integer;
declare variable id_record integer;
declare variable min_id_trans integer;
declare variable max_id_trans integer;
declare variable ins_count integer;
declare variable upd_count integer;
declare variable del_count integer;
declare variable startid int;
declare variable min_time timestamp;
begin
--Блокировка от одновременных изменений
  update UPDATE_TRANSACTIONS set ID = ID where id = base_id();

  select ID_LASTRECEIVE from UPDATE_TRANSACTIONS where id = :host_id into startid;
  select min_time from get_mintime_connections into min_time;
  min_time = coalesce(min_time, current_timestamp - 3*3e0/(24*60));

  /* Если транзакция старая (до b/r) то обнуляем */
  update update_log set
    SYSTRANSACTION = 0
  where id_transaction <= :endid and (SYSTRANSACTION > current_transaction or SYSTRANSACTION is null);

  select max(id_transaction) from sys$transactions into max_tr;
  max_tr = coalesce(max_tr, 0);

  select max(id_transaction) from update_log
  where id_transaction <= :endid  and systransaction <= :oldest_tr and sysday < :min_time
  into end_id;

  if (end_id is null) then
  begin
    end_id = startid;
    exit;
  end

  /* Сжатие данных */
  for select id_table, id_record,
             sum(iif(oper = 2, 1, 0)),
             sum(iif(oper = 1, 1, 0)),
             sum(iif(oper = 3, 1, 0)),
             min(id_transaction)
      from update_log
      where id_transaction between :max_tr + 1 and :end_id
      group by 2, 1
      having count(*) > 1
  into id_table, id_record, ins_count, upd_count, del_count, min_id_trans
  do
  begin
    if (del_count > 0) then
      delete from update_log
      where id_table = :id_table and id_record = :id_record and id_transaction between :min_id_trans and :end_id and oper in (1, 2);
    else
    if (ins_count > 0) then
      delete from update_log
      where id_table = :id_table and id_record = :id_record and id_transaction between :min_id_trans and :end_id and oper = 1;
    else
    if (upd_count > 0) then
      delete from update_log
      where id_table = :id_table and id_record = :id_record and id_transaction between :min_id_trans+1 and :end_id and oper = 1;
  end

  /* Удаление обновлений записи сделанных ранее, оставляем только последнюю операцию */
  for select id_table, id_record, max(id_transaction)
      from update_log
      where id_transaction <= :end_id and oper = 1
      group by 2, 1
      having count(*) > 1
  into id_table, id_record, max_id_trans do
  begin
    delete from update_log
    where id_table = :id_table and id_record = :id_record and id_transaction < :max_id_trans and oper = 1;
  end

  update or insert into sys$transactions (id_transaction, id_host)
  values (:end_id, :host_id)
  matching (id_transaction, id_host);
end;

COMMENT ON PROCEDURE BEFORE_SAVEDELTA IS 
'Репликация. Перед началом формирования пакета информации';


CREATE OR ALTER PROCEDURE ON_AFTER_LOADDELTA (
    HOST_ID INTEGER,
    STARTID INTEGER,
    ENDID INTEGER)
AS
begin
  Exit;
end;

COMMENT ON PROCEDURE ON_AFTER_LOADDELTA IS 
'Репликация. После приема информации. Специфика конкретной БД';