Логирование в базах данных Interbase (Firebird)

Источник: delphikingdom
Рудюк Сергей

Автор: Рудюк Сергей, Королевство Delphi

Введение

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

Лог в текстовом файле

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

Var StrTmp: TStringList;
...

StrTmp.Add('Тест лога 1.'); // Строка лога 1
...
StrTmp.Add('Тест лога 2.'); // Строка лога 2
...
StrTmp.SaveToFile('C:\FileLog.txt');  // Сохранение информации из лога

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

Логирование информации в базе данных Firebird (Intebase)

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

Логирование в базе данных из программы

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

Недостатки данного способа:
  • Нужно не забывать вставлять информацию в лог при любой операции.
  • Если вставка записи в лог производится не в одной транзакции со вставкой информации, то может возникнуть ситуация, когда вставка информации произойдет, а в логе информация не отразится (и наоборот).
Опишем приблизительный реализации этого способа логирования информации.
Создадим таблицу: 
CREATE TABLE LOGS(
    ID        INT_64 NOT NULL /* INT_64 = BIGINT */,
    DATE_LOG    TIMESTAMP,
    TEXT_LOG   VARCHAR(1500)
    USERNAME  VARCHAR(30),
    CODE_OPER    INT_64 /* INT_64 = BIGINT */

);
Где:
  • ID - первичное поле лога информации.
  • DATE_LOG - время и дата лога.
  • TEXT_LOG - текст лога информации.
  • USERNAME - имя пользователя.
  • CODE_OPER - код операции.
Создадим ключ: 
ALTER TABLE EVENTS ADD CONSTRAINT PKEVENTS PRIMARY KEY (ID);

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

Логирование в базе данных

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

Напишем приблизительный для данной реализации лога. 
/* Создаем генератор для ID лога */
SET GENERATOR GENID TO 1000

^
/* Создаем процедуру, которая будет извлекать необходимый генератор */
CREATE PROCEDURE PROC_GENID
RETURNS (NEWID BIGINT)
AS BEGIN

   NEWID = GEN_ID(GENID, 1);
/* COMMIT;*/
   SUSPEND;
END
^

/* Триггер вставки */
CREATE TRIGGER RTI_TABLE_NAME FOR TABLE_NAME AFTER INSERT POSITION 0 AS

DECLARE VARIABLE IDLOG BIGINT;
BEGIN

          SELECT NEWID
          FROM PROC_GENID
          INTO :IDLOG;

       INSERT INTO LOGDVIG (IDLOG, DATE_LOG, TEXT_LOG, USERNAME, CODE_OPER)
   VALUES (:IDLOG, 'NOW', 'Текст лога вставки', CURRENT_USER, 1);


END
^

/* Триггер обновления */
CREATE TRIGGER RTU_ TABLE_NAME FOR TABLE_NAME AFTER UPDATE POSITION 0 AS

DECLARE VARIABLE IDLOG BIGINT;
BEGIN

          SELECT NEWID
          FROM PROC_GENID
          INTO :IDLOG;

       INSERT INTO LOGDVIG (IDLOG, DATE_LOG, TEXT_LOG, USERNAME, CODE_OPER)
   VALUES (:IDLOG, 'NOW', 'Текст лога обновления', CURRENT_USER, 1);


END
^

/* Триггер удаления */
CREATE TRIGGER RTD_ TABLE_NAME FOR TABLE_NAME AFTER DELETE POSITION 0 AS

DECLARE VARIABLE IDLOG BIGINT;
BEGIN

          SELECT NEWID
          FROM PROC_GENID
          INTO :IDLOG;

       INSERT INTO LOGDVIG (IDLOG, DATE_LOG, TEXT_LOG, USERNAME, CODE_OPER)
   VALUES (:IDLOG, 'NOW', 'Текст лога удаления', CURRENT_USER, 1);


END
^

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

Логирование в базе данных с сохранением информации

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

Сложности, с которыми мы сталкиваемся при разработке программной реализации:
  • Типы данных в полях бывают разные. Т.е. не только числовые, строковые и дата, но и блоб.
  • Кроме учета вставки, удаления или изменения информации необходимо учитывать так же изменения каждого поля по отдельности. Нет необходимости показывать ту информацию, которая не изменилась.
Для начала, разработаем модель.
Как видите, модель состоит их 4-х таблиц:
  • IBE$Log_Tables - таблица операций. В данной таблице отмечается в какой таблице произошла операция, какая именно операция, во сколько, а так же какой пользователь.
  • IBE$Log_Fields - отражает изменения информации в полях.
  • IBE$Log_Keys - отражает информацию в ключевом поле.
  • IBE$Log_Blob_Fields - отражает изменения в блоб-полях.
Сгенерируем скрипт для создания данной структуры: 
SET TERM ^;

CREATE TABLE IBE$LOG_TABLES (
    ID          NUMERIC(18,0) NOT NULL,
    TABLE_NAME  VARCHAR(67) NOT NULL,
    OPERATION   VARCHAR(1) NOT NULL,
    DATE_TIME   TIMESTAMP NOT NULL,
    USER_NAME   VARCHAR(67) NOT NULL

);
^
ALTER TABLE IBE$LOG_TABLES ADD PRIMARY KEY (ID);
^
CREATE TABLE IBE$LOG_KEYS (
    LOG_TABLES_ID  NUMERIC(18,0) NOT NULL,
    KEY_FIELD      VARCHAR(67) NOT NULL,
    KEY_VALUE      VARCHAR(255)
);
^

CREATE INDEX IBE$LOG_KEYS_IDX1 ON IBE$LOG_KEYS (LOG_TABLES_ID);
^
CREATE TABLE IBE$LOG_FIELDS (
    LOG_TABLES_ID  NUMERIC(18,0) NOT NULL,
    FIELD_NAME     VARCHAR(67) NOT NULL,
    OLD_VALUE      VARCHAR(255),
    NEW_VALUE      VARCHAR(255)
);
^

CREATE INDEX IBE$LOG_FIELDS_IDX1 ON IBE$LOG_FIELDS (LOG_TABLES_ID);
^

CREATE TABLE IBE$LOG_BLOB_FIELDS (
    LOG_TABLES_ID   NUMERIC(18,0) NOT NULL,
    FIELD_NAME      VARCHAR(67) NOT NULL,

    OLD_CHAR_VALUE  VARCHAR(10000),
    NEW_CHAR_VALUE  VARCHAR(10000),
    OLD_BLOB_VALUE  BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    NEW_BLOB_VALUE  BLOB SUB_TYPE 0 SEGMENT SIZE 80

);
^

CREATE INDEX IBE$LOG_BLOB_FIELDS_IDX1 ON IBE$LOG_BLOB_FIELDS (LOG_TABLES_ID);
^

Это мы написали всего лишь структуру для хранения информации логирования в базе данных. Теперь, нам необходимо написать реакцию на изменения информации. Для этого, поступаем как в способе описанном ранее - создаем ряд триггеров.

Предположим, есть у нас таблица такой структуры: 
CREATE TABLE BANKDOC (
    IDBANKDOC       INT_64 NOT NULL /* INT_64 = BIGINT */,
    SCHETID         INT_64 /* INT_64 = BIGINT */,
    DOCID           INT_64 NOT NULL /* INT_64 = BIGINT */,
    SUMMASPISAN     DECIMAL(15,5),
    SUMMAPRIHOD     DECIMAL(15,5),
    SUMMASPISANNDS  DECIMAL(15,5),
    SUMMAPRIHODNDS  DECIMAL(15,5),
    NAZNACH         BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    KODF            INT_64 /* INT_64 = BIGINT */

);
Создадим для данной таблицы триггера вставки информации в лог. 
CREATE TRIGGER IBE$BANKDOC_AI FOR BANKDOC
ACTIVE AFTER INSERT POSITION 32767

AS
DECLARE VARIABLE TID INTEGER;
BEGIN
  TID = GEN_ID(IBE$LOG_TABLES_GEN,1);

  INSERT INTO IBE$LOG_TABLES (ID, TABLE_NAME, OPERATION, DATE_TIME, USER_NAME)
    VALUES (:TID, 'BANKDOC', 'I', 'NOW', USER);

  INSERT INTO IBE$LOG_KEYS (LOG_TABLES_ID, KEY_FIELD, KEY_VALUE)
    VALUES (:TID, 'IDBANKDOC', NEW.IDBANKDOC);

  IF (NOT (NEW.IDBANKDOC IS NULL)) THEN

    INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
           VALUES (:TID, 'IDBANKDOC', NULL, NEW.IDBANKDOC);

  IF (NOT (NEW.SCHETID IS NULL)) THEN

    INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
           VALUES (:TID, 'SCHETID', NULL, NEW.SCHETID);

  IF (NOT (NEW.DOCID IS NULL)) THEN

    INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
           VALUES (:TID, 'DOCID', NULL, NEW.DOCID);

  IF (NOT (NEW.SUMMASPISAN IS NULL)) THEN

    INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
           VALUES (:TID, 'SUMMASPISAN', NULL, NEW.SUMMASPISAN);

  IF (NOT (NEW.SUMMAPRIHOD IS NULL)) THEN

    INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
           VALUES (:TID, 'SUMMAPRIHOD', NULL, NEW.SUMMAPRIHOD);

  IF (NOT (NEW.SUMMASPISANNDS IS NULL)) THEN

    INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
           VALUES (:TID, 'SUMMASPISANNDS', NULL, NEW.SUMMASPISANNDS);

  IF (NOT (NEW.SUMMAPRIHODNDS IS NULL)) THEN

    INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
           VALUES (:TID, 'SUMMAPRIHODNDS', NULL, NEW.SUMMAPRIHODNDS);

  IF (NOT (NEW.KODF IS NULL)) THEN

    INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
           VALUES (:TID, 'KODF', NULL, NEW.KODF);

  IF (NOT (NEW.NAZNACH IS NULL)) THEN

    INSERT INTO IBE$LOG_BLOB_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_BLOB_VALUE,
                                     NEW_BLOB_VALUE)
           VALUES (:TID,'NAZNACH',NULL, NEW.NAZNACH);
END
^

CREATE TRIGGER IBE$BANKDOC_AU FOR BANKDOC

ACTIVE AFTER UPDATE POSITION 32767
AS
DECLARE VARIABLE TID INTEGER;

BEGIN
  TID = GEN_ID(IBE$LOG_TABLES_GEN,1);

  INSERT INTO IBE$LOG_TABLES (ID, TABLE_NAME, OPERATION, DATE_TIME, USER_NAME)
    VALUES (:TID, 'BANKDOC', 'U', 'NOW', USER);

  INSERT INTO IBE$LOG_KEYS (LOG_TABLES_ID, KEY_FIELD, KEY_VALUE)
    VALUES (:TID, 'IDBANKDOC', OLD.IDBANKDOC);

  IF ((OLD.IDBANKDOC IS NULL AND NEW.IDBANKDOC IS NOT NULL) OR

      (NEW.IDBANKDOC IS NULL AND OLD.IDBANKDOC IS NOT NULL) OR

      (NEW.IDBANKDOC IS NOT NULL AND OLD.IDBANKDOC IS NOT NULL

                     AND NEW.IDBANKDOC <> OLD.IDBANKDOC)) THEN
    INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
           VALUES (:TID,'IDBANKDOC',OLD.IDBANKDOC, NEW.IDBANKDOC);

  IF ((OLD.SCHETID IS NULL AND NEW.SCHETID IS NOT NULL) OR

      (NEW.SCHETID IS NULL AND OLD.SCHETID IS NOT NULL) OR

      (NEW.SCHETID IS NOT NULL AND OLD.SCHETID IS NOT NULL

                             AND NEW.SCHETID <> OLD.SCHETID)) THEN
    INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
           VALUES (:TID,'SCHETID',OLD.SCHETID, NEW.SCHETID);

  IF ((OLD.DOCID IS NULL AND NEW.DOCID IS NOT NULL) OR

      (NEW.DOCID IS NULL AND OLD.DOCID IS NOT NULL) OR

      (NEW.DOCID IS NOT NULL AND OLD.DOCID IS NOT NULL

                            AND NEW.DOCID <> OLD.DOCID)) THEN
    INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
           VALUES (:TID,'DOCID',OLD.DOCID, NEW.DOCID);

  IF ((OLD.SUMMASPISAN IS NULL AND NEW.SUMMASPISAN IS NOT NULL) OR

      (NEW.SUMMASPISAN IS NULL AND OLD.SUMMASPISAN IS NOT NULL) OR

      (NEW.SUMMASPISAN IS NOT NULL AND OLD.SUMMASPISAN IS NOT NULL

                             AND NEW.SUMMASPISAN <> OLD.SUMMASPISAN)) THEN
    INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
           VALUES (:TID,'SUMMASPISAN',OLD.SUMMASPISAN, NEW.SUMMASPISAN);

  IF ((OLD.SUMMAPRIHOD IS NULL AND NEW.SUMMAPRIHOD IS NOT NULL) OR

      (NEW.SUMMAPRIHOD IS NULL AND OLD.SUMMAPRIHOD IS NOT NULL) OR

      (NEW.SUMMAPRIHOD IS NOT NULL AND OLD.SUMMAPRIHOD IS NOT NULL

                    AND NEW.SUMMAPRIHOD <> OLD.SUMMAPRIHOD)) THEN
    INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
           VALUES (:TID,'SUMMAPRIHOD',OLD.SUMMAPRIHOD, NEW.SUMMAPRIHOD);

  IF ((OLD.SUMMASPISANNDS IS NULL AND NEW.SUMMASPISANNDS IS NOT NULL) OR

      (NEW.SUMMASPISANNDS IS NULL AND OLD.SUMMASPISANNDS IS NOT NULL) OR

      (NEW.SUMMASPISANNDS IS NOT NULL AND OLD.SUMMASPISANNDS IS NOT NULL

                    AND NEW.SUMMASPISANNDS <> OLD.SUMMASPISANNDS)) THEN
    INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
           VALUES (:TID,'SUMMASPISANNDS',OLD.SUMMASPISANNDS, NEW.SUMMASPISANNDS);

  IF ((OLD.SUMMAPRIHODNDS IS NULL AND NEW.SUMMAPRIHODNDS IS NOT NULL) OR

      (NEW.SUMMAPRIHODNDS IS NULL AND OLD.SUMMAPRIHODNDS IS NOT NULL) OR

      (NEW.SUMMAPRIHODNDS IS NOT NULL AND OLD.SUMMAPRIHODNDS IS NOT NULL

                           AND NEW.SUMMAPRIHODNDS <> OLD.SUMMAPRIHODNDS)) THEN
    INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
           VALUES (:TID,'SUMMAPRIHODNDS',OLD.SUMMAPRIHODNDS, NEW.SUMMAPRIHODNDS);

  IF ((OLD.KODF IS NULL AND NEW.KODF IS NOT NULL) OR

      (NEW.KODF IS NULL AND OLD.KODF IS NOT NULL) OR

      (NEW.KODF IS NOT NULL AND OLD.KODF IS NOT NULL

                         AND NEW.KODF <> OLD.KODF)) THEN
    INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
           VALUES (:TID,'KODF',OLD.KODF, NEW.KODF);

  INSERT INTO IBE$LOG_BLOB_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_BLOB_VALUE,
                                   NEW_BLOB_VALUE)
         VALUES (:TID,'NAZNACH',OLD.NAZNACH, NEW.NAZNACH);

END
^

CREATE TRIGGER IBE$BANKDOC_AD FOR BANKDOC
ACTIVE AFTER DELETE POSITION 32767

AS
DECLARE VARIABLE TID INTEGER;
BEGIN
  TID = GEN_ID(IBE$LOG_TABLES_GEN,1);

  INSERT INTO IBE$LOG_TABLES (ID, TABLE_NAME, OPERATION, DATE_TIME, USER_NAME)
    VALUES (:TID, 'BANKDOC', 'D', 'NOW', USER);

  INSERT INTO IBE$LOG_KEYS (LOG_TABLES_ID, KEY_FIELD, KEY_VALUE)
    VALUES (:TID, 'IDBANKDOC', OLD.IDBANKDOC);

  INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
         VALUES (:TID,'IDBANKDOC',OLD.IDBANKDOC, NULL);

  INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
         VALUES (:TID,'SCHETID',OLD.SCHETID, NULL);

  INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
         VALUES (:TID,'DOCID',OLD.DOCID, NULL);

  INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
         VALUES (:TID,'SUMMASPISAN',OLD.SUMMASPISAN, NULL);

  INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
         VALUES (:TID,'SUMMAPRIHOD',OLD.SUMMAPRIHOD, NULL);

  INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
         VALUES (:TID,'SUMMASPISANNDS',OLD.SUMMASPISANNDS, NULL);

  INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
         VALUES (:TID,'SUMMAPRIHODNDS',OLD.SUMMAPRIHODNDS, NULL);

  INSERT INTO IBE$LOG_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_VALUE, NEW_VALUE)
         VALUES (:TID,'KODF',OLD.KODF, NULL);

  INSERT INTO IBE$LOG_BLOB_FIELDS (LOG_TABLES_ID, FIELD_NAME, OLD_BLOB_VALUE,
                                   NEW_BLOB_VALUE)
         VALUES (:TID,'NAZNACH',OLD.NAZNACH, NULL);

END
^

Как видите, получились достаточно громоздкие триггера. Это произошли потому, что мы учитываем не только изменения целой записи, но и каждого поля по отдельности.

Можно данный скрипты пытаться вводить вручную, но это займет очень много времени.

Для того, чтобы облегчить Ваш труд есть прекрасная программа под названием IBExpert (к тому же бесплатная для ex-USSR). Загрузить данную программу Вы можете по адресу: http://www.ibexpert.com. Эта программа не только позволит сгенерировать Вам триггера для ведения логов, описанных в данном разделе, но и просмотреть информацию в логах в нужных разрезах.

Для генерации логов в IBExpert предназначена команда Инструменты -> Менеджер протоколов данных.


Страница сайта http://test.interface.ru
Оригинал находится по адресу http://test.interface.ru/home.asp?artId=21778