Сегодня мы поговорим об организации отслеживания изменений в наша платформа .
Любая нормальная ERP-система должна иметь возможность проводить расследование внесенных изменений.
Без этой возможности невозможно по-настоящему передать в программу функцию управления ресурсами компании.
Таким образом, система отслеживания изменений должна позволять отслеживать все изменения, требовать минимального потребления памяти (ОЗУ и диска) и налагать минимальные накладные расходы на операции.
Система отслеживания изменений должна обеспечивать возможность поиска и просмотра изменений с указанием даты и описания внесенного изменения, например: новый смысл, кто это сделал, какие изменения.
В реальных условиях необходимо учитывать, что отслеживать нужно только реально произведенные изменения (записанные в СУБД).
Сразу оговорюсь, что мы попробовали несколько подходов, в том числе самый очевидный для Oracle — Flashback Archive. Почему он не всплыл, расскажу в конце статьи.
Выполнение
В результате мы остановились на реализации логирования по триггерам.
Для хранения всей истории достаточно 4 таблиц и немного логики: 
Таблицы LOG_TABLES и LOG_FIELDS содержат списки таблиц и отдельных полей, зарегистрированных системой.
Для управления этими таблицами в интерфейсе администратора есть специальные инструменты: 
В этой форме администратор может включить ведение журнала как для всей таблицы, так и для отдельных полей.
Для больших баз данных управление журналированием требует особой внимательности, и часто этим занимается администратор СУБД, поэтому в режиме по умолчанию мы показываем именно те таблицы и имена полей, какими они есть в базе данных.
Для других можно переключиться на просмотр системных объектов.
Таблицы LOG_CHANGES и LOG_FIELDS_CHANGED содержат информацию непосредственно об изменениях.
LOG_CHANGES — информация об изменениях в таблице, пользователе и сессии, из которой было произведено изменение.
Кстати, на всякий случай в сессии сохраняется информация о клиентской машине, с которой был выполнен вход на сервер приложений, что опять же упрощает расследования.
Еще небольшое отступление о пользователе и такой функции в системе, как маскарад. Маскарад — это возможность пользователю (обычно администратору, разработчику, инженеру технической поддержки или отделу тестирования) войти в систему со всеми правами и настройками другого пользователя без ввода его (другого пользователя) пароля.
На самом деле паролей никто не знает и в базе их не видно.
Итак, во время маскарада истории изменений мы вспоминаем настоящего пользователя.
Таким образом, разработчик может залогиниться и что-то проверить, но все изменения будут видны как сделанные под его реальным пользователем.
Вернемся к LOG_FIELDS_CHANGED. Как следует из названия, в этой таблице хранится подробная информация об измененных полях и их новых значениях.
Поле Тип содержит тип изменения – Вставка, Удаление, Обновление.
Система сама генерирует триггеры для объектов метаданных.
В триггере мы явно проверяем, что значение поля было обновлено.
Часто бывает удобно написать запрос, который обновляет поле до его собственного значения.
Такое изменение, а точнее, его отсутствие не должно нас интересовать.
Просто ради интереса вы можете проверить, что триггер BEFORE INSERT OR UPDATE FOR EACH ROW будет вызываться для каждой обновляемой строки.
Проверка добавляет минимальные накладные расходы и значительно снижает нагрузку на диск.
Такая система позволяет логировать произвольные таблицы (в том числе и не описанные как объекты метаданных — хотя это экзотическая ситуация).
Для реального использования помимо триггера на таблице необходим столбец, однозначно идентифицирующий строку; в нашей версии обязательна сплошная нумерация всех таблиц.
Итого — одна последовательность, столбец и триггер для ее расчета (последний оставлен для обратной совместимости с Oracle 11g; автоинкрементные поля появились в 12c, но мы их пока не использовали).
Чтобы облегчить жизнь, все эти детали скрыты в недрах системы и разработчику приложения (или администратору) можно либо воспользоваться приведенным выше интерфейсом системы, либо попросить сгенерировать скрипт для включения логирования: 
Ну и напоследок приведу пример триггера на столе, это позволит вам лучше понять механику:
Описанная схема соответствует требованиям, описанным в начале статьи, но имеет и свои отрицательные стороны.CREATE OR REPLACE TRIGGER LG_PARAM_MODES BEFORE INSERT OR DELETE OR UPDATE ON PARAM_MODES FOR EACH ROW DECLARE LOGCHANGEID NUMBER(18); LOGID NUMBER(18); CHANGEFLAG BOOLEAN; VCHANGE_TYPE CHAR(1); VCOL_TYPE CHAR(1); VTABLE_ID NUMBER(18); VFIELD_ID NUMBER(18); BEGIN LOGCHANGEID := LOG_CHANGES_SEQ.NEXTVAL; CHANGEFLAG := FALSE; VTABLE_ID := 164; IF INSERTING THEN LOGID := LOG_ID_SEQ.NEXTVAL; :NEW.LOG_ID := LOGID; VCHANGE_TYPE := 'I'; IF (:NEW.NAME IS NOT NULL) THEN CHANGEFLAG := TRUE; VFIELD_ID := 1639; VCOL_TYPE := 'V'; INSERT INTO LOG_FIELDS_CHANGED (ID, TYPE, VARCHAR2_VALUE, CHANGE_ID, FIELD_ID) VALUES (LOG_FIELDS_CHANGED_SEQ.NEXTVAL, VCOL_TYPE, :NEW.NAME, LOGCHANGEID, VFIELD_ID); END IF; IF (:NEW.ID IS NOT NULL) THEN CHANGEFLAG := TRUE; VFIELD_ID := 1638; VCOL_TYPE := 'N'; INSERT INTO LOG_FIELDS_CHANGED (ID, TYPE, NUMBER_VALUE, CHANGE_ID, FIELD_ID) VALUES (LOG_FIELDS_CHANGED_SEQ.NEXTVAL, VCOL_TYPE, :NEW.ID, LOGCHANGEID, VFIELD_ID); END IF; IF (CHANGEFLAG = TRUE) THEN INSERT INTO LOG_CHANGES (ID, LOG_ID, TIME, TYPE, TABLE_ID, USER_ID, SESSION_ID) VALUES (LOGCHANGEID, LOGID, SYSDATE, VCHANGE_TYPE, VTABLE_ID, GET_REAL_UID, GET_SESSION_ID); END IF; ELSIF DELETING THEN LOGID := :OLD.LOG_ID; IF LOGID IS NULL THEN LOGID := LOG_ID_SEQ.NEXTVAL; END IF; VCHANGE_TYPE := 'D'; IF (:OLD.NAME IS NOT NULL) THEN CHANGEFLAG := TRUE; VFIELD_ID := 1639; VCOL_TYPE := 'V'; INSERT INTO LOG_FIELDS_CHANGED(ID, TYPE, VARCHAR2_VALUE, CHANGE_ID, FIELD_ID) VALUES (LOG_FIELDS_CHANGED_SEQ.NEXTVAL, VCOL_TYPE, :OLD.NAME, LOGCHANGEID, VFIELD_ID); END IF; IF (:OLD.ID IS NOT NULL) THEN CHANGEFLAG := TRUE; VFIELD_ID := 1638; VCOL_TYPE := 'N'; INSERT INTO LOG_FIELDS_CHANGED(ID, TYPE, NUMBER_VALUE, CHANGE_ID, FIELD_ID) VALUES (LOG_FIELDS_CHANGED_SEQ.NEXTVAL, VCOL_TYPE, :OLD.ID, LOGCHANGEID, VFIELD_ID); END IF; IF (CHANGEFLAG = TRUE) THEN INSERT INTO LOG_CHANGES(ID, LOG_ID, TIME, TYPE, TABLE_ID, USER_ID, SESSION_ID) VALUES(LOGCHANGEID, LOGID, SYSDATE, VCHANGE_TYPE, VTABLE_ID, GET_REAL_UID, GET_SESSION_ID); END IF; ELSIF UPDATING THEN LOGID := :NEW.LOG_ID; IF LOGID IS NULL THEN LOGID := LOG_ID_SEQ.NEXTVAL; :NEW.LOG_ID := LOGID; END IF; VCHANGE_TYPE := 'U'; IF ((:OLD.NAME <> :NEW.NAME) OR (:OLD.NAME IS NULL AND :NEW.NAME IS NOT NULL) OR (:OLD.NAME IS NOT NULL AND :NEW.NAME IS NULL)) THEN CHANGEFLAG := TRUE; VFIELD_ID := 1639; VCOL_TYPE := 'V'; INSERT INTO LOG_FIELDS_CHANGED(ID, TYPE, VARCHAR2_VALUE, CHANGE_ID, FIELD_ID) VALUES (LOG_FIELDS_CHANGED_SEQ.NEXTVAL, VCOL_TYPE, :NEW.NAME, LOGCHANGEID, VFIELD_ID); END IF; IF ((:OLD.ID <> :NEW.ID) OR (:OLD.ID IS NULL AND :NEW.ID IS NOT NULL) OR (:OLD.ID IS NOT NULL AND :NEW.ID IS NULL)) THEN CHANGEFLAG := TRUE; VFIELD_ID := 1638; VCOL_TYPE := 'N'; INSERT INTO LOG_FIELDS_CHANGED(ID, TYPE, NUMBER_VALUE, CHANGE_ID, FIELD_ID) VALUES (LOG_FIELDS_CHANGED_SEQ.NEXTVAL, VCOL_TYPE, :NEW.ID, LOGCHANGEID, VFIELD_ID); END IF; IF (CHANGEFLAG = TRUE) THEN INSERT INTO LOG_CHANGES(ID, LOG_ID, TIME, TYPE, TABLE_ID, USER_ID, SESSION_ID) VALUES(LOGCHANGEID, LOGID, SYSDATE, VCHANGE_TYPE, VTABLE_ID, GET_REAL_UID, GET_SESSION_ID); END IF; END IF; END; /
1. Для физически удаленных строк, если уникальный ключ строки неизвестен, поиск «вторичных» характеристик займет значительно больше времени.
2. Поскольку используются обычные таблицы, для них формируются журналы повторов и архивирования, хотя известно, что в эти таблицы происходит только добавление.
Мы пробовали использовать другие методы с очередями, но сложность реализации отката изменений при откате транзакций заставила отказаться от этого варианта, и накладные расходы стали слишком большими.
Архив воспоминаний Oracle
Очень удобная функция, позволяющая реализовать логирование изменений и поиск по ним, встроена непосредственно в язык запросов SQL. Просто посмотрите на элегантность реализации: SELECT * FROM employee AS OF TIMESTAMP TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN';
Запрос возвращает сотрудника в то состояние, в котором он находился в указанный момент времени.
Вот аналогичный запрос, который возвращает историю изменений SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime,
versions_xid, versions_operation, name, salary
FROM employee
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE name = 'JOE';
Невероятно интуитивно и понятно.
К сожалению, оказалось, что этим невозможно пользоваться! 1.Нет поддержки трехуровневой архитектуры.
Для каждого изменения может быть идентифицирован только пользователь в СУБД.
Для 3-й ссылки всегда одно и то же.
Информации о клиентской машине нет. 2. Невероятное обжорство.
На одном из клиентов с включенным FBA база данных начала расти со скоростью почти гигабайт в час (до этого рост объема базы составлял примерно 50-100 МБ в час).
Оказалось, что для каждой логируемой таблицы FBA создаёт копию её структуры и при каждом изменении копирует ВСЮ строку.
Кроме того, он не проверяет, есть ли реальные изменения.
Мы надеемся, что в будущем коллеги смогут улучшить FBA, это действительно простой в использовании инструмент. Его по-прежнему можно использовать в классической архитектуре клиент-сервер.
Но будьте осторожны — наличие задачи, периодически меняющей даже одно поле в «широкой» таблице, может запросто съесть всё дисковое пространство.
В заключение.
До сих пор Oracle Streams оставался неисследованным.
Мы надеемся, что написание клиента для Oracle Streams позволит нам отказаться от триггеров и таблиц в системе и существенно снизить нагрузку на основную базу данных, переместив ее куда-нибудь еще.
Но на данный момент реализованный метод, несмотря на свои недостатки, является наименьшим из найденных зол.
Надеюсь, что факты, изложенные в статье, помогут вам выбрать удобный вариант реализации логирования.
Теги: #ERP-системы #программирование #База данных Oracle #программирование
-
Интерактивные Доски. Зачем Они И Для Кого?
19 Oct, 24 -
Uwp – Выпуск 239
19 Oct, 24 -
Microsoft Скрестила Spdy И Websockets
19 Oct, 24
