В статье приведен новый легкий алгоритм для организации версионности справочников, он похож на Log trigger, но более универсален и надежен. Возможно, это все было придумано до меня, но в открытых источниках мне не удалось найти описание этого подхода. По некоторым техническим и политическим причинам мы отказались от использования готового ПО, анализа существующих систем в этой статье не будет. Все делали сами, на C# и реляционных СУБД, хотя это здесь не принципиально.С чего все началосьВ одном проекте у нас была задача вести и распространять большое количество справочников, иногда по нестабильным каналам связи, с возможными откатами времени на серверах и восстановлением их из бекапов. Спешу поделиться с вами, какие у нас возникали проблемы и как мы их решали.Мы использовали архитектуру Master-Slave: одна Ведущая система хранит в себе эталонный образец справочника и позволяет его редактировать (через GUI или импорт файлов), Ведомые системы периодически опрашивают Ведущую на наличие обновлений. Обмен данными - через пакеты JSON и WebAPI, т.к. клиенты были от Windows Server до Android. Количество Ведомых достигало 10000, запросы обновлений шли от каждого Ведомого в среднем раз в час. Нагрузка не очень большая, но использование файлового кэша с пакетами JSON и системы изготовления diff-ов позволило запускать Ведущую систему на очень слабом железе. Справочники были разные, самые сложные из них:
Для справочников и реестров был разработан единый механизм хранения и распространения, он довольно легковесный и вы можете воспользоваться им даже если в вашем проекте используются более простые структуры. ПроблемыПервый вопрос - что считать номером версии? Вариантов не много: дата-время редактирования справочника или некое целое число - автоинкремент. "Дата-время" очень популярный, но плохой вариант даже для Реестров, т.к.:
Второй вопрос - как хранить сразу несколько версий в таблицах? Ведь нужно хранить не только историю изменений реестра или справочника, но и иметь быстрый доступ сразу к нескольким версиям некоторых справочников. Здесь вариантов больше:
РешениеТаблица для хранения информации о самих версиях:
Таблица с данными:
Данная структура применяется как на стороне Ведущей системы, так и на стороне Ведомых. Может отличаться только количество хранимых версий. Одна запись в таблице с данными относится сразу к нескольким версиям, поэтому метод назван "Виртуальная версионность". Формирование справочникаПри редактировании справочника в таблице с номерами версий создается новая запись. Далее вносятся изменения в таблицу с данными:
Например:
Запись Kate была создана в версии 1 и существует во всех остальных версиях. Запись Lisa была создана в версии 1 и удалена в версии 2. Т.е. во 2 версии справочника этой записи уже нет. Запись Tom была создана в 1 версии и отредактирована в 3. Т.е. в 3 версии справочника он уже будет определяться как "famale". Одна строка в таблице с данными может относиться сразу к нескольким версиям, т.к. она привязана к диапазону версий между addInVersion и deleteInVersion. Уникальный идентификатор записи в таблице с данными являются ID + addInVersion. Если новая версия вашего справочника создается длительное время с большим количеством редактирований одних и тех же полей разными людьми, то алгоритм может быть следующий:
Версию "на редактировании" всегда можно безопасно откатить до последней стабильной, физически удалив все записи у которых addInVersion = N, и проставив NULL для всех полей, где deleteInVersion = N. Недостаток данного метода: не сохраняется история формирования версии (кто какую строку внес и т.д.), но для этого могут быть применены дополнительные журналы действий пользователей.Достоинство - не хранится промежуточный мусор в таблице с данными, промежуточные версии справочника не попадут на ведомые устройства. Несколько связанных таблицЕсли у вас есть несколько связанных таблиц и для них требуется единая версионность, то их поля addInVersion и deleteInVersion должны ссылаться на одну таблицу с номерами версий. Таблица Users:
Таблица Sex:
В примере выше при создании версии 3 были отредактированы сразу две таблицы. Целостность данных на стороне клиентов не будет нарушена. Обратите внимание, что поле SexID не уникально в таблице Sex, это не первичный ключ. Первичный ключ будет составной: SexID + addInVersion. При этом в таблице Users поле SexID не является внешним ключом, т.е. СУБД не контролирует ссылочную целостность для данных, покрытых версионностью. Ни в коем случае нельзя в таблице Users использовать составной внешний ключ SexID + addInVersion: при редактировании данных в таблице Sex составной вторичный ключ в таблице Users продолжит ссылаться на старую версию записи. Также не спасут и суррогатные ключи. При создании новой версии целостность данных должно контролировать ваше ПО. Алгоритм добавления новых записей и редактирования существующих мало отличается от описанного в предыдущем разделе: при редактировании полей-ссылок проверяйте, что существуют соответствующие записи, у которых deleteInVersion установлено как NULL, т.е. что эти записи не были удалены из справочника. Удаление записей: если в "Таблица Sex" одну из записей пометить как удаленную, то необходимо проверить, что на нее никто больше не ссылается в новой версии. СУБД автоматически это не проконтролирует, т.к. удаленные записи остаются в таблицах, у них только обновляется поле deleteInVersion. Пример с ошибкой: Таблица Users:
Таблица Sex:
Создали версию 4, в которой в таблице Sex удалили запись "other". В СУБД ошибка не возникнет, т.к. SexID в таблице Users продолжает спокойно ссылаться на SexID=3 в таблице Sex. В вашем ПО произойдет крах, когда в 4-ой версии справочника не обнаружится SexID=3. Пример без ошибки: Таблица Users:
Таблица Sex:
Запись "Tom" была удалена вместе с записью "other". Другой вариант без ошибки - запись "Tom" могла быть отредактирована в той же 4 версии с присвоением другого SexID, но видимо Tom не успел определиться :) Если в вашем проекте есть несколько не связанных таблиц, и для каждой требуется версионность, то в вашей базе данных будет несколько таблиц с номерами версий, на каждую из которых ссылается одна или несколько таблиц с данными. Подводный камень при использовании связанных справочников у нас был следующий: допустим, было принято решение отредактировать Таблицу Sex, данный процесс занимает 2 недели, т.к. требуется согласование с большим количеством руководителей. Создали версию 4, она находится в состоянии "на редактировании", внесли правку в Таблицу Sex, ждем... Тут неожиданно приходит команда завести новую запись в Таблице Users. Поскольку версия 4 уже создана и открыта на редактирование - просто вносим данные в Таблицу Users с addInVersion = 4. Но проблема в том, что Клиенты не получат эту запись до тех пор, пока не будет согласовано изменение в Таблице Sex, а это долго. Решения:
Очистка устаревших версийСтарые версии можно быстро удалить из базы, для этого:
Удалить можно только самую старую версию, т.е. настоятельно не рекомендуется удалять версию 5, если в базе еще есть версия 4. Сроки хранения старых версий могут отличаться для Ведущей и Ведомых систем, например в нашем случае Ведущая хранила все версии за последние 5 лет, а ведомые не более 1 года. Если говорить о реестрах, то на Ведомых вообще хранилась только последняя версия, а на Ведущей - те же 5 лет. Выборка из справочникаВсе запросы довольно простые, не требуют сложных вычислений.По таблице с номерами версий определите, какую версию вы хотите сейчас использовать. Это может зависеть от дат действия и текущего времени, или выставляться вручную - как требует ваш алгоритм. Поиск записи в справочнике в версии X: SELECT Name, Sex FROM Users WHERE Name = 'Kate' AND (addInVersion <= X OR
Запрос всех записей в справочнике за версию X: SELECT Name, Sex FROM Users WHERE
Запрос всех версий справочника, начиная с версии X (предполагается, что нет не утвержденных версий в БД, иначе запрос будет сложнее): SELECT * FROM Users WHERE deleteInVersion >= X OR deleteInVersion is NULL Мы использовали этот запрос для первоначальной инициализации Ведомых систем. Если же необходимо изготовить пакет изменений (diff) между версиями X и Y (где Y = X + 1) для передачи от Ведущей системы к Ведомой, то выполняются следующие два шага: 1) Получаем список записей, которые должны быть помечены на стороне Ведомой системы как добавленные: SELECT
2) Получаем список записей, которые должны быть помечены на стороне Ведомой системы как удаленные: SELECT * FROM Users WHERE deleteInVersion = Y Теперь остается только правильно сформировать пакет изменений, например в JSON. Обмен справочниками между Ведущим и ВедомымиЗдесь все просто, Ведомые сообщают Ведущей какая версия у них сейчас есть, Ведущая отдает им ссылку на скачивание пакета изменений или говорит что ничего нового нет. Пакеты изменений изготавливаются в виде JSON запакованных в архив сразу после выпуска новой версии, либо при первом запросе. На этом все, спасибо за внимание! |
Статьи >