Просмотр исходного текста страницы Однонаправленная асинхронная дельта репликация средствами СУБД Firebird

Увеличение размера файла базы данных практически всегда приводит к уменьшению скорости выполнения запросов. Однажды размер базы переходит ту критическую черту, за которой пользователи начинают ощущать дискомфорт от работы с программой, а потери времени на построение отчетов становятся существенными. Одним из решений указанной проблемы является создание отдельной оперативной базы с данными за небольшой период (например, за один год). Пользователи работают с оперативной базой, а исходная база (назовем ее архивной) используется для накопления всей информации и построения аналитических отчетов за прошлые периоды. Если оперативная и архивная базы расположены на разных компьютерах, то последняя может выполнять роль моментальной замены в случае выхода из строя основного сервера.

Периодически, все изменения из оперативной базы должны передаваться на архивную. Т.е. должна происходить однонаправленная репликация. В сети можно найти достаточное количество платных и бесплатных репликаторов. Мы же ставим перед собой задачу реализовать механизм однонаправленной асинхронной дельта репликации исключительно средствами СУБД Firebird без привлечения сторонних программ или библиотек UDF.

=== Создание оперативной базы данных ===

Исходная ситуация выглядит следующим образом:

http://gsbelarus.com/gs/images/gs/2010/db_repl_fb_1.png

Существующая база данных будет архивной. Копированием создадим из нее оперативную, из которой удалим информацию прошлых периодов, заменив ее сальдовыми записями для корректного построения отчетов. Разумеется, можно ничего не удалять. Тогда мы получим две идентичные базы данных.

http://gsbelarus.com/gs/images/gs/2010/db_repl_fb_2.png

С помощью приведенного ниже [[#Подготовка оперативной базы данных|скрипта]] создаем в оперативной базе данных необходимые объекты для хранения истории изменений. Проиллюстрируем их на примере таблицы TEST, следующей структуры:

<syntaxhighlight lang="SQL">
CREATE TABLE test (
  id     INTEGER NOT NULL PRIMARY KEY,
  str    VARCHAR(60))
</syntaxhighlight>

История изменений каждой записи из TEST записывается с помощью трех триггеров в созданную скриптом таблицу GR2$TEST, которая отличается от исходной только префиксом в названии и добавленным полем GR2$SEQ.

<syntaxhighlight lang="SQL">
CREATE TABLE GR2$TEST 
(
  ID	        INTEGER,
  STR	        VARCHAR(60) CHARACTER SET WIN1251,
  GR2$SEQ	INTEGER NOT NULL,

  PRIMARY KEY (GR2$SEQ)
);

CREATE TRIGGER GR2$_AI_TEST FOR TEST
  AFTER INSERT
  POSITION 32000
AS   
  DECLARE VARIABLE I INTEGER; 
BEGIN   
  I = GEN_ID(GR2$GSEQ, 1);   
  INSERT INTO GR2$TEST (ID,STR,GR2$SEQ) VALUES (NEW.ID,NEW.STR,:I);   
  INSERT INTO gr2$log (gr2$seq, gr2$relation_name, gr2$log_relation_name, gr2$act) 
    VALUES (:I, 'TEST', 'GR2$TEST', 'I'); 
END;

CREATE TRIGGER GR2$_AU_TEST FOR TEST
  AFTER UPDATE
  POSITION 32000
AS   
  DECLARE VARIABLE I INTEGER; 
BEGIN   
  I = GEN_ID(GR2$GSEQ, 2);   
  INSERT INTO GR2$TEST (ID,STR,GR2$SEQ) VALUES (OLD.ID,OLD.STR,:I - 1);   
  INSERT INTO GR2$TEST (ID,STR,GR2$SEQ) VALUES (NEW.ID,NEW.STR,:I);   
  INSERT INTO gr2$log (gr2$seq, gr2$relation_name, gr2$log_relation_name, gr2$act) 
    VALUES (:I, 'TEST', 'GR2$TEST', 'U'); 
END;

CREATE TRIGGER GR2$_AD_TEST FOR TEST
  AFTER DELETE
  POSITION 32000
AS   
  DECLARE VARIABLE I INTEGER; 
BEGIN   
  I = GEN_ID(GR2$GSEQ, 1);   
  INSERT INTO GR2$TEST (ID,STR,GR2$SEQ) VALUES (OLD.ID,OLD.STR,:I);   
  INSERT INTO gr2$log (gr2$seq, gr2$relation_name, gr2$log_relation_name, gr2$act) 
    VALUES (:I, 'TEST', 'GR2$TEST', 'D'); 
END;
</syntaxhighlight>

Очередность операций, выполненных сервером, фиксируется в таблице GR2$LOG с помощью генератора GR2$GSEQ:

<syntaxhighlight lang="SQL">
CREATE TABLE GR2$LOG 
(
  GR2$SEQ	        INTEGER NOT NULL,
  GR2$RELATION_NAME	CHAR(31) CHARACTER SET UNICODE_FSS NOT NULL,
  GR2$LOG_RELATION_NAME	CHAR(31) CHARACTER SET UNICODE_FSS NOT NULL,
  GR2$ACT	CHAR(1) CHARACTER SET WIN1251,

  CHECK (GR2$ACT IN ('I', 'U', 'D'))
);
</syntaxhighlight>

Кроме указанных метаданных, скрипт формирует код переноса изменений на архивную базу данных и сохраняет его в таблице GR2$BLOCK_CODE:

<syntaxhighlight lang="SQL">
CREATE TABLE GR2$BLOCK_CODE 
(
  GR2$RELATION_NAME	CHAR(31) CHARACTER SET UNICODE_FSS NOT NULL,
  GR2$BLOCK_TEXT	BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET WIN1251 NOT NULL,

  PRIMARY KEY (GR2$RELATION_NAME)
);
</syntaxhighlight>

Для таблицы TEST сформированный код будет выглядеть следующим образом:

<syntaxhighlight lang="SQL">
EXECUTE BLOCK (seq INTEGER = :seq, act CHAR(1) = :act) 
AS 
  DECLARE VARIABLE ID INTEGER;
  DECLARE VARIABLE OLD_ID INTEGER;
  DECLARE VARIABLE STR VARCHAR(60) CHARACTER SET WIN1251;
BEGIN   
  IF (:act = 'I') THEN   
  BEGIN     
    SELECT ID,STR    
    FROM GR2$TEST    
    WHERE gr2$seq = :seq     
    INTO :ID,:STR;     

    EXECUTE STATEMENT ('INSERT INTO TEST (ID,STR) VALUES (:ID,:STR) ') (ID:=:ID,STR:=:STR)       
    ON EXTERNAL 'xxx:yyy'       
    WITH COMMON TRANSACTION       
    AS USER 'sysdba'       
    PASSWORD 'masterkey';  
  END 
  ELSE IF (:act = 'U') THEN   
  BEGIN     
    SELECT ID,STR    
    FROM GR2$TEST    
    WHERE gr2$seq = :seq     
    INTO :ID,:STR;     

    SELECT ID    
    FROM GR2$TEST    
    WHERE gr2$seq = :seq - 1     
    INTO :OLD_ID;     

    EXECUTE STATEMENT ('UPDATE TEST SET ID=:ID,STR=:STR WHERE ID=:OLD_ID') (ID:=:ID,STR:=:STR,OLD_ID:=:OLD_ID)       
    ON EXTERNAL 'xxx:yyy'       
    WITH COMMON TRANSACTION       
    AS USER 'sysdba'       
    PASSWORD 'masterkey';  
  END   
  ELSE BEGIN     
    SELECT ID    
    FROM GR2$TEST    
    WHERE gr2$seq = :seq     
    INTO :OLD_ID;     

    EXECUTE STATEMENT ('DELETE FROM TEST WHERE ID=:OLD_ID') (OLD_ID:=:OLD_ID)       
    ON EXTERNAL 'xxx:yyy'       
    WITH COMMON TRANSACTION       
    AS USER 'sysdba'       
    PASSWORD 'masterkey';  
  END 
END
</syntaxhighlight>

=== Перенос изменений на архивную базу данных ===

В процессе работы с оперативной базой изменения будут накапливаться в таблицах "дублерах", а их очередность фиксироваться в логе GR2$LOG. Перенос изменений выполняется с помощью [[#Репликация изменений на архивную базу данных|скрипта]]. Его можно выполнять вручную или сохранить в файл средствами операционной системы организовать автоматический периодический вызов. 

Сам по себе скрипт чрезвычайно прост и делает ровно следующее:

# Проверим, что нет иных подключений к архивной базе кроме нашего.
# Проверим, что нет иных активных транзакций.
# Запомним список активных триггеров в таблице GR2$TRIGGERS. Если такой таблицы еще нет, то предварительно создадим ее.
# Последовательно будем читать историю изменений из GR2$LOG и для каждого выполнять заранее заготовленный в GR2$BLOCK_CODE код переноса данных на архивную базу.
# Подключим триггеры.
# Очистим лог и все таблицы "дублеры". 

Обратите внимание: очистка лога и перенос данных выполняются на одной транзакции, что страхует нас от потерь в случае выключения сервера или падения канала связи.

=== Недостатки предложенной схемы ===

# Всегда хранится полная копия вставляемой или удаляемой записи. Для изменяемой записи всегда сохраняются ее старое и новое состояние.
# Перенос изменений требует прямого коннекта к архивной базе данных.

== Исходный код ==

Переменная external_db хранит полное имя архивной базы данных (сервер, протокол, путь к файлу или алиас). Не забудьте скорректировать ее перед выполнением скрипта. 

=== Подготовка оперативной базы данных ===

Блок вернет список таблиц без первичных ключей, для которых невозможно создать метаданные репликации. 

<syntaxhighlight lang="SQL">
EXECUTE BLOCK
  RETURNS(
    out_rn VARCHAR(200) CHARACTER SET UNICODE_FSS
  )
AS
  DECLARE VARIABLE rn VARCHAR(31) CHARACTER SET UNICODE_FSS;
  DECLARE VARIABLE fn VARCHAR(31) CHARACTER SET UNICODE_FSS;
  DECLARE VARIABLE ft VARCHAR(128) CHARACTER SET UNICODE_FSS;
  DECLARE VARIABLE is_pk INTEGER;
  DECLARE VARIABLE create_statement VARCHAR(32000);
  DECLARE VARIABLE trigger_statement VARCHAR(32000);
  
  DECLARE VARIABLE block_statement BLOB SUB_TYPE 1;
  DECLARE VARIABLE block_declare VARCHAR(32000);
  DECLARE VARIABLE block_fields_list VARCHAR(32000);
  DECLARE VARIABLE block_fields_params VARCHAR(32000);
  DECLARE VARIABLE block_fields_set_params VARCHAR(32000);
  DECLARE VARIABLE block_update_list VARCHAR(32000);
  DECLARE VARIABLE block_keys_list VARCHAR(32000);
  DECLARE VARIABLE block_keys_params VARCHAR(32000);
  DECLARE VARIABLE block_keys_set_params VARCHAR(32000);
  DECLARE VARIABLE block_condition VARCHAR(32000);

  DECLARE VARIABLE relname VARCHAR(31) CHARACTER SET UNICODE_FSS;
  DECLARE VARIABLE ai_trigger VARCHAR(31) CHARACTER SET UNICODE_FSS;
  DECLARE VARIABLE au_trigger VARCHAR(31) CHARACTER SET UNICODE_FSS;
  DECLARE VARIABLE ad_trigger VARCHAR(31) CHARACTER SET UNICODE_FSS;
  
  DECLARE VARIABLE external_db VARCHAR(255) = 'xxx:yyy';
  DECLARE VARIABLE external_user VARCHAR(31) = 'sysdba';
  DECLARE VARIABLE external_password VARCHAR(10) = 'masterkey';
BEGIN
  IF (EXISTS(SELECT * FROM rdb$relations WHERE rdb$relation_name LIKE 'GR2$%')) THEN
    EXIT;
    
  IF (EXISTS(SELECT * FROM rdb$generators WHERE rdb$generator_name = 'GD_G_UNIQUE')) THEN
    EXECUTE STATEMENT 'SELECT GEN_ID(gd_g_unique, 10000) FROM rdb$database'
    WITH AUTONOMOUS TRANSACTION;

  EXECUTE STATEMENT
    'create table gr2$log (' ||
      'gr2$seq INTEGER NOT NULL, ' ||
      'gr2$relation_name CHAR(31) CHARACTER SET UNICODE_FSS NOT NULL, ' ||
      'gr2$log_relation_name CHAR(31) CHARACTER SET UNICODE_FSS NOT NULL, ' ||
      'gr2$act CHAR(1), CHECK (GR2$ACT IN (''I'', ''U'', ''D'')) ' ||
    ')'
    WITH AUTONOMOUS TRANSACTION;

  EXECUTE STATEMENT
    'GRANT ALL ON gr2$log TO Administrator'
    WITH AUTONOMOUS TRANSACTION;

  EXECUTE STATEMENT
    'CREATE TABLE gr2$block_code (' ||
      'gr2$relation_name CHAR(31) CHARACTER SET UNICODE_FSS NOT NULL PRIMARY KEY, ' ||
      /*'gr2$block_text VARCHAR(32000) NOT NULL ' ||*/
      'gr2$block_text BLOB SUB_TYPE 1 NOT NULL ' ||
    ')'
    WITH AUTONOMOUS TRANSACTION;
    
  EXECUTE STATEMENT
    'GRANT ALL ON gr2$block_code TO Administrator'
    WITH AUTONOMOUS TRANSACTION;
    
  EXECUTE STATEMENT
    'create generator gr2$gseq'
    WITH AUTONOMOUS TRANSACTION;

  FOR
    SELECT TRIM(rdb$relation_name)
    FROM rdb$relations
    WHERE COALESCE(rdb$system_flag, 0) = 0
      AND NOT rdb$relation_name LIKE 'GR2$%'
      AND NOT (rdb$relation_name IN ('LOG', 'BLOCK_CODE'))
      AND rdb$external_file IS NULL
      AND rdb$view_source IS NULL
    INTO :rn
  DO BEGIN
    IF (EXISTS(SELECT * FROM rdb$relation_constraints WHERE rdb$constraint_type = 'PRIMARY KEY'
      AND rdb$relation_name = :rn)) THEN
    BEGIN
      create_statement = '';
      trigger_statement = '';
      block_declare = '';
      block_fields_list = '';
      block_fields_params = '';
      block_fields_set_params = '';
      block_update_list = '';
      block_keys_list = '';
      block_keys_params = '';
      block_keys_set_params = '';
      block_condition = '';
      FOR
        SELECT TRIM(rf.rdb$field_name),
          CASE f.rdb$field_type
            WHEN 7 THEN 'SMALLINT'
            WHEN 8 THEN
              CASE f.rdb$field_sub_type
                WHEN 0 THEN 'INTEGER'
                WHEN 1 THEN 'NUMERIC(' || f.rdb$field_precision || ',' || ABS(f.rdb$field_scale) || ')'
                WHEN 2 THEN 'DECIMAL(' || f.rdb$field_precision || ',' || ABS(f.rdb$field_scale) || ')'
                ELSE 'unknown'
              END
            WHEN 10 THEN 'FLOAT'
            WHEN 12 THEN 'DATE'
            WHEN 13 THEN 'TIME'
            WHEN 14 THEN 'CHAR(' || f.rdb$character_length || ') CHARACTER SET ' ||
              (SELECT TRIM(rdb$character_set_name) FROM rdb$character_sets WHERE rdb$character_set_id = f.rdb$character_set_id)
            WHEN 16 THEN 'BIGINT'
            WHEN 27 THEN 'DOUBLE PRECISION'
            WHEN 35 THEN 'TIMESTAMP'
            WHEN 37 THEN 'VARCHAR(' || f.rdb$character_length || ') CHARACTER SET ' ||
              (SELECT TRIM(rdb$character_set_name) FROM rdb$character_sets WHERE rdb$character_set_id = f.rdb$character_set_id)
            WHEN 261 THEN
              CASE f.rdb$field_sub_type
                WHEN 1 THEN 'BLOB SUB_TYPE 1 CHARACTER SET ' ||
                  (SELECT TRIM(rdb$character_set_name) FROM rdb$character_sets WHERE rdb$character_set_id = f.rdb$character_set_id)
                ELSE 'BLOB SUB_TYPE 0'
              END
            ELSE 'unknown'
          END,
          (SELECT 1
           FROM rdb$relation_constraints rc2
             JOIN rdb$indices i2 ON i2.rdb$index_name = rc2.rdb$index_name
             JOIN rdb$index_segments iseg2 ON iseg2.rdb$index_name = i2.rdb$index_name
           WHERE
             rc2.rdb$constraint_type = 'PRIMARY KEY'
             AND rc2.rdb$relation_name = :rn AND iseg2.rdb$field_name = rf.rdb$field_name)
        FROM rdb$relation_fields rf JOIN rdb$fields f
          ON f.rdb$field_name = rf.rdb$field_source
        WHERE rf.rdb$relation_name = :rn AND f.rdb$computed_source IS NULL
        ORDER BY rf.rdb$field_position
        INTO :fn, :ft, :is_pk
      DO
      BEGIN
        create_statement = :create_statement || :fn || ' ' || :ft || ',';
        trigger_statement = :trigger_statement || 'meta.' || :fn || ',';
        block_declare = :block_declare || 'DECLARE VARIABLE ' || :fn || ' ' || :ft || ';';
        block_fields_list = :block_fields_list || :fn || ',';
        block_fields_params = :block_fields_params || ':' || :fn || ',';
        block_fields_set_params = :block_fields_set_params || :fn || ':=:' || :fn || ',';
        block_update_list = :block_update_list || :fn || '=:' || :fn || ',';
        IF (:is_pk = 1) THEN
        BEGIN
          block_declare = :block_declare || 'DECLARE VARIABLE OLD_' || :fn || ' ' || :ft || ';';
          block_keys_list = :block_keys_list || :fn || ',';
          block_keys_params = :block_keys_params || ':OLD_' || :fn || ',';
          block_keys_set_params = :block_keys_set_params || 'OLD_' || :fn || ':=:OLD_' || :fn || ',';
          block_condition = :block_condition || :fn || '=:OLD_' || :fn || ' AND ';
        END
      END
      
      create_statement = LEFT(:create_statement, CHAR_LENGTH(:create_statement) - 1);
      trigger_statement = LEFT(:trigger_statement, CHAR_LENGTH(:trigger_statement) - 1);
      
      block_fields_list = LEFT(:block_fields_list, CHAR_LENGTH(:block_fields_list) - 1);
      block_fields_params = LEFT(:block_fields_params, CHAR_LENGTH(:block_fields_params) - 1);
      block_fields_set_params = LEFT(:block_fields_set_params, CHAR_LENGTH(:block_fields_set_params) - 1);
      block_update_list = LEFT(:block_update_list, CHAR_LENGTH(:block_update_list) - 1);
      block_keys_list = LEFT(:block_keys_list, CHAR_LENGTH(:block_keys_list) - 1);
      block_keys_params = LEFT(:block_keys_params, CHAR_LENGTH(:block_keys_params) - 1);
      block_keys_set_params = LEFT(:block_keys_set_params, CHAR_LENGTH(:block_keys_set_params) - 1);
      block_condition = LEFT(:block_condition, CHAR_LENGTH(:block_condition) - 5);

      relname = LEFT('GR2$' || :rn, 31);
      ai_trigger = LEFT('GR2$_AI_' || :rn, 31);
      au_trigger = LEFT('GR2$_AU_' || :rn, 31);
      ad_trigger = LEFT('GR2$_AD_' || :rn, 31);

      EXECUTE STATEMENT 'CREATE TABLE ' || :relname || ' (' || :create_statement ||
        ',GR2$SEQ INTEGER NOT NULL, PRIMARY KEY(GR2$SEQ))';

      --out_rn = 'Ok! ' || :rn;
      --SUSPEND;

      EXECUTE STATEMENT
        'CREATE TRIGGER ' || :ai_trigger || ' FOR ' || :rn || ' AFTER INSERT POSITION 32000 ' ||
        'AS ' ||
        '  DECLARE VARIABLE I INTEGER; ' ||
        'BEGIN ' ||
        '  I = GEN_ID(GR2$GSEQ, 1); ' ||
        '  INSERT INTO ' || :relname || ' (' || REPLACE(:trigger_statement, 'meta.', '') || ',GR2$SEQ) VALUES (' ||
             REPLACE(:trigger_statement, 'meta.', 'NEW.') || ',:I); ' ||
        '  INSERT INTO gr2$log (gr2$seq, gr2$relation_name, gr2$log_relation_name, gr2$act) VALUES (' ||
             ':I, ''' || :rn || ''', ''' || :relname || ''', ''I''); ' ||
        'END';

      EXECUTE STATEMENT
        'CREATE TRIGGER ' || :au_trigger || ' FOR ' || :rn || ' AFTER UPDATE POSITION 32000 ' ||
        'AS ' ||
        '  DECLARE VARIABLE I INTEGER; ' ||
        'BEGIN ' ||
        '  I = GEN_ID(GR2$GSEQ, 2); ' ||
        '  INSERT INTO ' || :relname || ' (' || REPLACE(:trigger_statement, 'meta.', '') || ',GR2$SEQ) VALUES (' ||
             REPLACE(:trigger_statement, 'meta.', 'OLD.') || ',:I - 1); ' ||
        '  INSERT INTO ' || :relname || ' (' || REPLACE(:trigger_statement, 'meta.', '') || ',GR2$SEQ) VALUES (' ||
             REPLACE(:trigger_statement, 'meta.', 'NEW.') || ',:I); ' ||
        '  INSERT INTO gr2$log (gr2$seq, gr2$relation_name, gr2$log_relation_name, gr2$act) VALUES (' ||
             ':I, ''' || :rn || ''', ''' || :relname || ''', ''U''); ' ||
        'END';

      EXECUTE STATEMENT
        'CREATE TRIGGER ' || :ad_trigger || ' FOR ' || :rn || ' AFTER DELETE POSITION 32000 ' ||
        'AS ' ||
        '  DECLARE VARIABLE I INTEGER; ' ||
        'BEGIN ' ||
        '  I = GEN_ID(GR2$GSEQ, 1); ' ||
        '  INSERT INTO ' || :relname || ' (' || REPLACE(:trigger_statement, 'meta.', '') || ',GR2$SEQ) VALUES (' ||
             REPLACE(:trigger_statement, 'meta.', 'OLD.') || ',:I); ' ||
        '  INSERT INTO gr2$log (gr2$seq, gr2$relation_name, gr2$log_relation_name, gr2$act) VALUES (' ||
             ':I, ''' || :rn || ''', ''' || :relname || ''', ''D''); ' ||
        'END';

      block_statement =
        'EXECUTE BLOCK (seq INTEGER = :seq, act CHAR(1) = :act) AS ' ||
          :block_declare ||
        'BEGIN ' ||
        '  IF (:act = ''I'') THEN ' ||
        '  BEGIN ' ||
        '    SELECT ' || :block_fields_list ||
        '    FROM ' || :relname ||
        '    WHERE gr2$seq = :seq ' ||
        '    INTO ' || :block_fields_params || '; ' ||
        '    EXECUTE STATEMENT (''INSERT INTO ' || :rn || ' (' || :block_fields_list || ') VALUES (' || :block_fields_params || ') '') ' ||
        '      (' || :block_fields_set_params || ') ' ||
        '      ON EXTERNAL ''' || :external_db || ''' ' ||
        '      WITH COMMON TRANSACTION ' ||
        '      AS USER ''' || :external_user || ''' ' ||
        '      PASSWORD ''' || :external_password || ''';' ||
        '  END ELSE IF (:act = ''U'') THEN ' ||
        '  BEGIN ' ||
        '    SELECT ' || :block_fields_list ||
        '    FROM ' || :relname ||
        '    WHERE gr2$seq = :seq ' ||
        '    INTO ' || :block_fields_params || '; ' ||
        '    SELECT ' || :block_keys_list ||
        '    FROM ' || :relname ||
        '    WHERE gr2$seq = :seq - 1 ' ||
        '    INTO ' || :block_keys_params || '; ' ||
        '    EXECUTE STATEMENT (''UPDATE ' || :rn || ' SET ' || :block_update_list || ' WHERE ' || :block_condition || ''') (' ||
               :block_fields_set_params || ',' || :block_keys_set_params || ') ' ||
        '      ON EXTERNAL ''' || :external_db || ''' ' ||
        '      WITH COMMON TRANSACTION ' ||
        '      AS USER ''' || :external_user || ''' ' ||
        '      PASSWORD ''' || :external_password || ''';' ||
        '  END ' ||
        '  ELSE BEGIN '||
        '    SELECT ' || :block_keys_list ||
        '    FROM ' || :relname ||
        '    WHERE gr2$seq = :seq ' ||
        '    INTO ' || :block_keys_params || '; ' ||
        '    EXECUTE STATEMENT (''DELETE FROM ' || :rn || ' WHERE ' || :block_condition || ''') (' || :block_keys_set_params || ') ' ||
        '      ON EXTERNAL ''' || :external_db || ''' ' ||
        '      WITH COMMON TRANSACTION ' ||
        '      AS USER ''' || :external_user || ''' ' ||
        '      PASSWORD ''' || :external_password || ''';' ||
        '  END ' ||
        'END';
        
      EXECUTE STATEMENT ('INSERT INTO gr2$block_code (gr2$relation_name, gr2$block_text) VALUES (?, ?)')
        (:rn, :block_statement);
    END ELSE
    BEGIN
      out_rn = 'No PK! ' || :rn;
      SUSPEND;
    END
  END
END
</syntaxhighlight>

=== Репликация изменений на архивную базу данных ===

Если по структуре архивной базы данных некоторые триггеры не должны быть деактивированы перед переносом данных, соответствующие условия должны быть добавлены в код ниже.

<syntaxhighlight lang="SQL">
EXECUTE BLOCK
AS
  DECLARE VARIABLE external_db VARCHAR(255) = 'xxx:yyy';
  DECLARE VARIABLE external_user VARCHAR(31) = 'sysdba';
  DECLARE VARIABLE external_password VARCHAR(10) = 'masterkey';
  DECLARE VARIABLE c INTEGER;
  DECLARE VARIABLE seq INTEGER;
  DECLARE VARIABLE act CHAR(1);
  DECLARE VARIABLE block_text BLOB SUB_TYPE 1;
  DECLARE VARIABLE activate_triggers VARCHAR(2048) = '';
  DECLARE VARIABLE rn VARCHAR(31) CHARACTER SET UNICODE_FSS;
BEGIN
  IF (EXISTS(SELECT * FROM rdb$relations WHERE rdb$relation_name = 'GR2$LOG')) THEN
  BEGIN
    EXECUTE STATEMENT
      'SELECT COUNT(*) FROM mon$attachments'
      ON EXTERNAL :external_db
      WITH AUTONOMOUS TRANSACTION
      AS USER :external_user
      PASSWORD :external_password
      INTO :C;

    IF (:c <> 1) THEN
      EXIT;

    EXECUTE STATEMENT
      'SELECT COUNT(*) FROM mon$transactions WHERE mon$state = 1 AND mon$read_only = 0 '
      ON EXTERNAL :external_db
      WITH AUTONOMOUS TRANSACTION
      AS USER :external_user
      PASSWORD :external_password
      INTO :C;

    IF (:c <> 1) THEN
      EXIT;

    EXECUTE STATEMENT
      'SELECT COUNT(*) FROM rdb$relations WHERE rdb$relation_name = ''GR2$TRIGGERS'' '
      ON EXTERNAL :external_db
      WITH AUTONOMOUS TRANSACTION
      AS USER :external_user
      PASSWORD :external_password
      INTO :C;
      
    IF (:c = 0) THEN
    BEGIN
      EXECUTE STATEMENT
        'CREATE TABLE gr2$triggers ( ' ||
          'gr2$trigger_name VARCHAR(31) CHARACTER SET UNICODE_FSS NOT NULL) '
        ON EXTERNAL :external_db
        WITH AUTONOMOUS TRANSACTION
        AS USER :external_user
        PASSWORD :external_password;
    END ELSE
    BEGIN
      EXECUTE STATEMENT
        'DELETE FROM gr2$triggers '
        ON EXTERNAL :external_db
        WITH AUTONOMOUS TRANSACTION
        AS USER :external_user
        PASSWORD :external_password;
    END
    
    EXECUTE STATEMENT
      'EXECUTE BLOCK AS ' ||
      '  DECLARE VARIABLE TN VARCHAR(31) CHARACTER SET UNICODE_FSS; ' ||
      'BEGIN ' ||
      '  INSERT INTO gr2$triggers (gr2$trigger_name) SELECT rdb$trigger_name ' ||
      '    FROM rdb$triggers WHERE rdb$system_flag = 0 AND rdb$trigger_inactive = 0;' ||
      '  FOR SELECT gr2$trigger_name FROM gr2$triggers INTO :tn ' ||
      '  DO BEGIN ' ||
      '    EXECUTE STATEMENT ''ALTER TRIGGER '' || :tn || '' INACTIVE ''; ' ||
      '  END ' ||
      'END'
      ON EXTERNAL :external_db
      WITH AUTONOMOUS TRANSACTION
      AS USER :external_user
      PASSWORD :external_password;
      
    activate_triggers =
      'EXECUTE BLOCK AS ' ||
      '  DECLARE VARIABLE TN VARCHAR(31) CHARACTER SET UNICODE_FSS; ' ||
      'BEGIN ' ||
      '  FOR SELECT gr2$trigger_name FROM gr2$triggers INTO :tn ' ||
      '  DO BEGIN ' ||
      '    EXECUTE STATEMENT ''ALTER TRIGGER '' || :tn || '' ACTIVE ''; ' ||
      '  END ' ||
      '  DELETE FROM gr2$triggers; ' ||
      'END';

    FOR
      SELECT l.gr2$seq, l.gr2$act, b.gr2$block_text
      FROM gr2$log l JOIN gr2$block_code b ON
        l.gr2$relation_name = b.gr2$relation_name
      ORDER BY l.gr2$seq
      INTO :seq, :act, :block_text
    DO BEGIN
      EXECUTE STATEMENT (:block_text) (seq := :seq, act := :act);
    END
    
    FOR
      SELECT DISTINCT(rdb$relation_name)
      FROM rdb$relation_fields
      WHERE rdb$field_name = 'GR2$SEQ'
      INTO :rn
    DO
      EXECUTE STATEMENT 'DELETE FROM ' || :rn || ' WHERE gr2$seq <= ' || :seq;
      
    EXECUTE STATEMENT :activate_triggers
      ON EXTERNAL :external_db
      WITH AUTONOMOUS TRANSACTION
      AS USER :external_user
      PASSWORD :external_password;
  END
    
  WHEN ANY DO
  BEGIN
    IF (:activate_triggers > '') THEN
      EXECUTE STATEMENT :activate_triggers
        ON EXTERNAL :external_db
        WITH AUTONOMOUS TRANSACTION
        AS USER :external_user
        PASSWORD :external_password;
    EXCEPTION;
  END
END
</syntaxhighlight>

=== Удаление метаданных репликатора из архивной и оперативной баз данных ===

Скрипт выполняется на каждой из баз.

<syntaxhighlight lang="SQL">
execute block
as
  declare variable tn varchar(31) character set unicode_fss;
  declare variable rn varchar(31) character set unicode_fss;
  declare variable gn varchar(31) character set unicode_fss;
begin
  for select rdb$trigger_name from rdb$triggers
    where rdb$trigger_name LIKE 'GR2$%'
    into :tn
  do begin
    execute statement 'DROP TRIGGER ' || :tn;
  end

  for select rdb$relation_name from rdb$relations
    where rdb$relation_name LIKE 'GR2$%'
    into :rn
  do begin
    execute statement 'DROP TABLE ' || :rn;
  end
  
  for select rdb$generator_name from rdb$generators
    where rdb$generator_name LIKE 'GR2$%'
    into :gn
  do begin
    execute statement 'DROP GENERATOR ' || :gn;
  end
end
</syntaxhighlight>

[[Category:Школа системного администратора]]
[[Category:Firebird]]
[[Category:Репликация]]

__NOTOC__