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

Материал из GedeminWiki
Перейти к: навигация, поиск

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

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

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

db_repl_fb_1.png

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

db_repl_fb_2.png

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

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


 
Источник — «https://gsbelarus.com:80/gs/wiki/index.php?title=%D0%9E%D0%B4%D0%BD%D0%BE%D0%BD%D0%B0%D0%BF%D1%80%D0%B0%D0%B2%D0%BB%D0%B5%D0%BD%D0%BD%D0%B0%D1%8F_%D0%B0%D1%81%D0%B8%D0%BD%D1%85%D1%80%D0%BE%D0%BD%D0%BD%D0%B0%D1%8F_%D0%B4%D0%B5%D0%BB%D1%8C%D1%82%D0%B0_%D1%80%D0%B5%D0%BF%D0%BB%D0%B8%D0%BA%D0%B0%D1%86%D0%B8%D1%8F_%D1%81%D1%80%D0%B5%D0%B4%D1%81%D1%82%D0%B2%D0%B0%D0%BC%D0%B8_%D0%A1%D0%A3%D0%91%D0%94_Firebird&oldid=4682»
Персональные инструменты
Пространства имён

Варианты
Действия
Навигация
Инструменты