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

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

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

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

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

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

Содержание

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

Исходный код

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

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) = 'C:\Program Files (x86)\Golden Software\Gedemin 2.5\Local\Database\target.fdb';
  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;
 
  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
    '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
    '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

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

-- перенос
EXECUTE BLOCK
AS
  DECLARE VARIABLE external_db VARCHAR(255) = 'C:\Program Files (x86)\Golden Software\Gedemin 2.5\Local\Database\target.fdb';
  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;
 
    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

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

-- удаление
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

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

Содержание

Исходный код

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

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

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

Персональные инструменты

Пространства имён

Варианты

Просмотры

Действия

Поиск

Навигация

Инструменты