Когда то видел реализацию «истории записей» — версионирования, на стороне программы, работающей с SQL базой. Перед изменением записи, из базы получалась старая версия, записывалась в XML и полученная строка XML записывалась в отдельную таблицу версий.
Изначально, в своей программе планировал версионирование сделать когда нибудь потом, какой то срочной необходимости не было. Помню, было желание где-то использовать тип данных jsonb, как только додумался до простой и лаконичной реализации версионирования на стороне SQL, не сделать не смог. Всего лишь одна таблица версий с 5 колонками и одна триггерная функция в 3 строчки кода.
Для описания реализации одной таблицы версий мало, поэтому придется описать еще несколько таблиц для примера.
Практически во всех базах данных, за редким исключением, есть таблица users – пользователи. Историю изменений — версий пользователя полезно хранить, например, для возможности откатится на старую версию, силами самого пользователя.
Пример таблицы пользователей:
Последние два поля на картинке, нужны для таблицы версий, также их можно назвать «автор версии» и «дата версии», но, при желании, можно обойтись без них.
Таблица версий:
Триггерная функция для сохранения версий:
Первые два поля заполняются из сохраняемой записи OLD.changestamp и OLD.userid.
В таблице версий, могут храниться не только записи таблицы users, третье поле MD5 хеш имени версионируемой таблицы, преобразованный в uuid.
В примерах ранее описана очень простая структура, но, как правило, у различных справочных данных могут быть дополнительные таблицы, с отношением «один ко многим».
Например, таблица «Группы пользователей».
И вторая таблица «Пользователи групп», состав группы — пользователи входящие в группу.
Для того, чтобы не усложнять простой механизм версионирования можно сделать небольшое дублирование данных в таблице групп, добавить jsonb поле, повторяющее структуру таблицы «Пользователи групп».
Для упрощения работы с задублированными данными, можно сделать дополнительную триггерную функцию, при INSERT или UPDATE, заполняющее таблицу «Пользователи групп» из jsonb поля.
Описанное выше дублирование, нужно только, когда требуется часто и максимально быстро получить данные из таблицы. Например, если часто делается запрос к таблице «Пользователи групп» для определения входит ли пользователь в группу Администраторы. В остальных случаях, данные можно получать запросом прямо из поля jsonb, и не использовать дублирующую таблицу.
Автор: PloAl
