Логирование в базах данных Interbase (Firebird)
Введение
Программистам баз данных часто приходится сталкиваться с необходимостью сохранения информации о работе пользователей или программы, т.е. логировать информацию. Данную задачу можно реализовать большим количеством способов. Каждый способ имеет свои преимущества и недостатки. Данная статья рассматривает какие есть способы логирования информации. Так же написано, как реализовать логирование информации в базах данных 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 ^
Следует отметить, что подобные триггера создаются для всех таблиц, в которых необходимо учитывать логи.
Данный способ логирования хорош, но он только учитывает какие операции производятся без возможности просмотреть что конкретно добавилось, изменилось или удалилось.
Следующий способ не только фиксирует все изменения, но и фиксирует саму информацию.
Логирование в базе данных с сохранением информации
Данный способ логирования более сложный, чем описанный выше. Так, как кроме сохранения информации о том, что произошла какая-то операция, он сохраняет еще и саму информацию. Так же этот способ потребует больший объем информации, чем в описанном выше.
Сложности, с которыми мы сталкиваемся при разработке программной реализации:
- Типы данных в полях бывают разные. Т.е. не только числовые, строковые и дата, но и блоб.
- Кроме учета вставки, удаления или изменения информации необходимо учитывать так же изменения каждого поля по отдельности. Нет необходимости показывать ту информацию, которая не изменилась.
Воспользуемся программой IBExpert для генерации логов. Разработаем модель, соответствующую структуре логов в IBExpert:
Как видите, модель состоит их 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 предназначена команда Инструменты -> Менеджер протоколов данных.
Вам остается только выбрать операции, где будят учитываться изменения, и нажать Compile (F9).
Вы можете просмотреть лог-информацию для заданной таблицы. Для этого щелкаете на таблице и выбираете протокол.
Резюме
Каждый способ логирования имеет свои преимущества и недостатки. Поэтому, применение может найти каждая из реализаций.
Текстовые логи целесообразно применять когда программа не работает с базой данных, и должна быть не больших размеров.
Логирование из программы - целесообразно применять, когда нужно вставлять в логи часто изменяемую текстовую информацию, не связанную на прямую с текстом в полях.
Логирование в базе данных - прекрасный способ логирования. Целесообразен тогда, когда необходимо достигнуть наибольшей стаблильности работы логов, но при этом нужно, чтобы не сильно увеличивался размер базы данных.
Логирование в базе данных с сохранением информации - целесообразен тогда, когда необходимо сохранить всю информацию, над которой производились операции. Надо учитывать, что при данной реализации логирования информации база данных будет сильно расти. Это связано с тем, что сохраняется вся информация, которая вносится в записи, причем столько раз, сколько производилось операций над ними. Если вы за ранее знаете, что некоторые поля в таблице могут достигать очень больших размеров, то лучше не используйте над данной таблицей этот способ логирования.