Зачем и почему?
Работая с БД часто приходится вручную писать SQL для создания history-таблиц, которые хранят "историю" о каждой записи из таблицы. То есть, если запись создана/изменена/удалена, для неё создается новая запись в таблице с окончанием "_hist" или "_history".
Задача знакомая, но крайне рутинная: для каждой таблицы нужно вручную писать SQL, проверять, чтобы все колонки были учтены, тип колонок был корректным, и не было опечаток.
Поэтому, я решил сделать небольшой плагин для DBeaver, который предоставляет удобное меню выбора колонок и событий.
После нескольких выходных дней получилась минимальная реализация, которой хочу с вами поделиться.
В данный момент реализован самый простой функционал:
Указать колонки, которые будут логироваться
Выбрать события (INSERT, UPDATE, DELETE)
Генерация и применение SQL для выбранной таблицы
Репозиторий проекта: GitHub - HistoryHelper
Кратко про установку
Для установки плагина нужно сделать несколько простых шагов:
Иметь установленный DBeaver
Зайти в репозиторий проекта и перейти в Release1.1.0
Скачать JAR файл (либо собрать из исходников. Инструкция в readme)
Перенести полученный JAR файл в папку
/plugins/(предпочтительнее) или/dropins/Перезапустить DBeaver с флагами
-clean -clearPersistedState
После перезапуска DBeaver, можно проверить, открыв в меню Справка выбрать пункт Информация об установке (см. скриншот ниже).
Затем в панели выбрать пункт Плагины и написать название плагина "HistoryHelper" (см. скриншот ниже).
Если после всех выполненных шагов установки, в плагинах вы смогли найти HistoryHelper, значит мы можем переходить непосредственно к генерации SQL для history-таблицы.
Использование плагина
Порядок действий будет представлен вместе со скриншотами для наглядности.
1. Необходимо выбрать нужную таблицу и кликнуть правой кнопкой мыши (работает только для таблиц) (см. скриншот ниже).
2. Нужно кликнуть на «Generate History Table». У вас выпадет диалоговое окно, где вам необходимо выбрать нужные колонки и также события (см. скриншот ниже).
3.При клике на «ОК» у вас выпадет меню, в котором вы увидите сгенерированный SQL и сможете сделать действия (см. скриншот ниже):
Кнопка «Применить» применяет сгенерированный SQL к выбранной таблице, затем также копирует скрипт в буфер обмена (заложил функцию для проектов, использующих liquibase)
Кнопка «Копировать» просто копирует сгенерированный SQL в буфер обмена
Кнопка «Закрыть» если что‑то вас смутило, и вы не хотите использовать сгенерированный SQL
Сгенерированный sql скрипт
CREATE TABLE IF NOT EXISTS test_table_hist
(id int4 NOT NULL,
name varchar,
epk_id int8,
short_name varchar,
deleted_at timestamp,
created_at timestamp,
modified_at timestamp);
--trigger on insert
CREATE OR REPLACE FUNCTION test_table_ins()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO test_table_hist (id,name,epk_id,short_name,deleted_at,created_at,modified_at)
VALUES (NEW.id,NEW.name,NEW.epk_id,NEW.short_name,NEW.deleted_at,NEW.created_at,NEW.modified_at);
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE TRIGGER test_table_hist_ins
AFTER INSERT ON test_table
FOR EACH ROW
EXECUTE FUNCTION test_table_ins();
--trigger on update
CREATE OR REPLACE FUNCTION test_table_upd()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO test_table_hist (id,name,epk_id,short_name,deleted_at,created_at,modified_at)
VALUES (NEW.id,NEW.name,NEW.epk_id,NEW.short_name,NEW.deleted_at,NEW.created_at,NEW.modified_at);
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE TRIGGER test_table_hist_upd
AFTER UPDATE ON test_table
FOR EACH ROW
EXECUTE FUNCTION test_table_upd();
--trigger on delete
CREATE OR REPLACE FUNCTION test_table_del()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO test_table_hist (id,name,epk_id,short_name,deleted_at,created_at,modified_at)
VALUES (OLD.id,OLD.name,OLD.epk_id,OLD.short_name,OLD.deleted_at,OLD.created_at,OLD.modified_at);
RETURN OLD;
END;
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE TRIGGER test_table_hist_del
AFTER DELETE ON test_table
FOR EACH ROW
EXECUTE FUNCTION test_table_del();4.После применения сгенерированного SQL (и refresh в бд), вы сможете увидеть таблицу и триггеры.
Roadmap
В ближайшем будущем планирую продолжать улучшать плагин, и, пока что, ближайшие улучшения такие:
Доп. вариант "оптимизированное" хранение в history-таблицах. В отличии от текущей реализации, в "оптимизированном" хранении будет записывать только предпоследние состояние объекта, а не все предыдущие состояния. Необходимо для экономии ресурсов.
Поддержка других СУБД (MySql, Oracle и т.д.) - в данный момент реализация подходит только для PostgreSQL.
Локализация на английский язык.
Добавление ограничений на выпадающее меню: в данный момент при клике на любую папку, базу данных и т.п. - вы увидите "Generate History Table", но при нажатии будет выведено предупреждение "Выберите таблицу в навигаторе БД".
Добавление предупреждений при исключительных сценариях (создания history-таблицы на уже созданную history-таблицу и т.п.)
Заключение
Это мой первый опыт написания плагина для DBeaver и, вообще, первая публичная статья. Понимаю, что плагин пока еще сыроват, но в ближайшем будущем планирую довести его до ума, чтобы облегчить нашу с вами рутину.
Если заметите баги или у вас появятся идеи для улучшений - добро пожаловать в Issues.
Комментарий или звездочка на GitHub будет самой лучшей поддержкой. Не судите строго :)
Ссылка на репозиторий: GitHub - HistoryHelper
Комментарии (21)
IvanVakhrushev
07.09.2025 04:33Вы бы хоть SQL показали...
Как решали проблему с bloat'ом? History-таблицы будут append-only?
Как решали проблему с wraparound и вакуумом для его предотвращения? Замораживаете строки сразу при вставке?
Есть ли поддержка для секционированных таблиц?
spersics Автор
07.09.2025 04:33Спасибо за вопросы! Сейчас плагин в самом "сыром" виде. Поэтому он генерит append-only историю, это дает полный аудит, но, конечно, вызывает рост таблицы (благодарен за напоминание про bloat).
В будущем планирую подумать над оптимизациями (замораживание, секционирование, режим хранения)Добавил спойлер с sql скриптом (Под скриншотом со скриптом)
BrIgVal
07.09.2025 04:33History-таблицы априори должны быть append-only. Это строки в основной таблице могут быть вставлены(INSERT), обновлены(UPDATE), удалены(DELETE). В history таблицах разрешается только вставка (INSERT) с указанием операции. Но при этом есть особенности - как хранить изменения: полностью копировать измененную строку основной таблицы с указанием типа операции(I,U.D) и атрибутов того, кто это сделал и времени изменения. При этом history-таблица (или таблица аудита) будет в разы больше основной таблицы. Либо же есть вариант, когда в history-таблицы данные заносятся по технологии "разряженных массивов", то есть фиксируются только значения измененных столбцов (при INSERT - изначально вставляются все столбцы и их значения, а при UPDATE - только измененные столбцы, при DELETE - только факт операции). При этом уменьшается размер history-аудит таблицы, но увеличивается время анализа измененных данных, логика и тип хранения этих данных с указанием измененного столбца, процедура восстановления полного состояния полей строки таблицы на определенную дату. Также для history-аудит таблиц делают архивные(archive) таблицы, куда периодически переносят древние строки аудит-таблиц и где-нить хранят отдельно от базы.
surly
07.09.2025 04:33Не всегда append-only. В таблицах аудита да. А если таблица хранит историю актуализации атрибутов, то иногда возникает необходимость исправить задним числом ошибку ввода или удалить запись, вставленную по ошибке.
BrIgVal
07.09.2025 04:33Не придумывай. Ты попутал мягкое с теплым. То, для чего автор топика делает свое расширение - это Аудит или Лог-таблицы (в которых кроме ID объекта должно быть ВРЕМЯ_ОПЕРАЦИИ, ТИП_ОПЕРАЦИИ, ПОЛЬЗОВАТЕЛЬ_СОВЕРШИВШИЙ_ОПЕРАЦИЮ). То о чем говоришь ты - это Темпоральные таблицы, в которых кроме ID и атрибутов объекта должны быть 2 обязательных столбца ВРЕМЯ_С и ВРЕМЯ_ПО. И эти 3 столбца ID, ВРЕМЯ_С, ВРЕМЯ_ПО образуют уникальный ключ и позволяют отслеживать временное изменение данных, в том числе и обновлять данные "задним числом". И на эту таблицу для отслеживания всех действий пользователя тоже можно сделать свою аудит-таблицу, которую автор и обозначает как _hist
Writer4
07.09.2025 04:33И в темпоральных таблицах более сложная логика поддержания целостности атрибутов время_с, время_по.
IvanG
07.09.2025 04:33Чем вызван выбор "кастомной" лицензии в репозитории, да ещё с такими жестокими ограничениями по изменениям, которые по сути запрещают форк и изменение кода "без разрешения/уведомления" автора?
spersics Автор
07.09.2025 04:33Я выбрал кастомную лицензию, чтобы сохранить проект в том виде, в котором я его задумывал. Если у вас есть идеи или предложения по улучшению, их всегда можно оставить в репо в виде issue/feature request, с радостью ознакомлюсь :)
IvanG
07.09.2025 04:33Реквесты и без лицензии могут быть и авторство плагина навряд ли кем-нибудь оспаривалось бы.
Я эту лицензию прочитал так, что даже в форке не могу вносить несогласованные изменения, а значит и пул/мердж реквест невозможен.
Чем то навеяло старые ЧСВ замашки в виде комментов авторства в файле, и субъективно вызывает нежелание влезать в кодбазу
spersics Автор
07.09.2025 04:33Запрещается:
-изменять, модифицировать и распространять изменённые версии плагина без предварительного согласия автора.
Вы не можете распространять изменённые версии плагина без согласия автора, но это не запрещает вам создавать пул/мердж реквест.
Условия:
-любые улучшения или предложения должны направляться автору для согласования;
Т.е. пул/мердж реквест рассматривается как "согласование".
Dimonogen
Не до конца понял решаемую бизнес задачу, для какого проекта нужны таблицы историчности? Почему таблицы истории создаются руками через скрипт, а не через код системы или ещё как-то?
spersics Автор
В ряде корпоративных проектов (например, в банковских), принято хранить историю изменений каждой записи. Это нужно для аудита, отката ошибок и в целом для отслеживания состояния объекта во времени.
Обычно такие history-таблицы создают вручную, что довольно рутинно: прописать колонки, проверять корректность и избегать опечаток.
Собственно, плагин и был сделан, чтобы этот процесс автоматизировать: выбираешь таблицу, нужные колонки и события - а скрипт генерится автоматически.
simon_logic
У потгреса, например, есть такая штука:
Даже можно тут же колонки дополнительные добавить.
spersics Автор
Да, like быстро копирует структуру, но моя цель - автоматизировать вес цикл (хист таблица + триггеры). А так вы получаете лишь копию но без триггеров
cooperative2
Не проще ли написать генерирующую скрипт процедурку в БД, если уж логика БД на этом построена?
Если вам даже нужен простой аудит через триггеры, то не проще все изменения в одну табличку в json пихать? Открыл и сразу видно, какие записи и в каких полях менялись, не надо сравнивать две таблицы и никакого зоопарка лишних таблиц в БД.
spersics Автор
В корпоративных проекта (особенно в банковских) часто требуют нормализованную структуру с отдельной history-таблицей.
А из практики - в реальных приложениях намного удобнее работать со строками, чем каждый раз разбирать json.
Да и задача плагина - не изобрести новый способ хранения истории, а ускорить рутинный процесс.
cooperative2
Не работал в банках, но честно говоря думал, что там все приличнее в архитектуре.
С чего оно удобнее? Если вываливать пользователю портянку, чтобы сам глазами искал, то действительно проще, а для анализа намного сложнее. Чтобы понять, когда поменялось какое-то значение в поле, нужно текущую запись сравнить с какой из всех записей history? Писать запрос для нахождения первой отличающейся записи? А если нужен не один случай, а все и с какой-то извращенной логикой? Запросец уже не кажется таким уж простым? Оказывается, что не понятно как выстроить индексы для всех ваших history таблиц?
Обычно плагин расширяет функции IDE, но БД ведь к ней не привязана. А чего делать, если ваш плагин не встанет на новую версию DBeaver или потребуется какие-то доработки сделать? Искать специалиста по разработке плагинов? Зачем весь этот онанизм, если можно просто хранить скрипт или процедурку в БД, которая делает то же самое.
spersics Автор
Хранить историю это не прихоть, а практическая необходимость. Но вопрос про концепцию скорее не ко мне, я просто сделал упрощение рутинного процесса, а не предлагаю новую концепцию хранения.
Когда хранишь как скрипт - легко ошибиться. Процедурка привязана к конкретной СУБД (скрипт в дальнейшем будет расширять поддержку.
spersics Автор
*(плагин в дальнейшем будет расширять поддержку СУБД)
surly
Пример. Фирма переименовалась в январе 2025-го. В феврале 2025-го мы начинаем составлять некий статистический отчёт за 2024 год. В этом отчёте наименование фирмы должно быть не тем, какое оно стало на момент начала (или окончания) работы над отчётом, а тем, какое оно было в отчётном периоде, т.е. до переименования. Его мы возьмём из исторической таблицы.