Как контролировать версию записи в базе данных
Допустим, у меня есть запись в базе данных и что как администраторы, так и обычные пользователи могут делать обновления.
Может кто-нибудь предложить хороший подход / архитектуру, как контролировать версию каждого изменения в этой таблице, чтобы можно было откатить запись до предыдущей ревизии.
11 ответов
Допустим, у вас есть FOO таблица, которую администраторы и пользователи могут обновить. Большую часть времени вы можете писать запросы к таблице FOO. Счастливые дни.
Тогда я бы создал FOO_HISTORY Таблица. Это имеет все столбцы FOO Таблица. Первичный ключ такой же, как FOO плюс столбец RevisionNumber. Существует внешний ключ от FOO_HISTORY в FOO, Вы также можете добавить столбцы, связанные с ревизией, такие как UserId и RevisionDate. Заполните RevisionNumbers все возрастающим образом во всех *_HISTORY таблицы (т.е. из последовательности Oracle или эквивалентной). Не полагайтесь только на одно изменение в секунду (т.е. не ставьте RevisionDate в первичный ключ).
Теперь каждый раз, когда вы обновляете FOOперед обновлением вы вставляете старые значения в FOO_HISTORY, Вы делаете это на каком-то фундаментальном уровне в вашем дизайне, чтобы программисты не могли случайно пропустить этот шаг.
Если вы хотите удалить строку из FOO у вас есть выбор. Либо каскад и удалить всю историю, либо выполнить логическое удаление с помощью пометки FOO как удалено
Это решение хорошо, когда вы в значительной степени интересуетесь текущими ценностями и только изредка в истории. Если вам всегда нужна история, вы можете указать эффективные даты начала и окончания и сохранить все записи в FOO сам. Каждый запрос должен проверить эти даты.
Я думаю, что вы ищете версии файлов базы данных (как это делает Stackru, когда кто-то редактирует вопрос / ответ). Хорошей отправной точкой может стать использование модели базы данных, в которой используется отслеживание версий.
Лучший пример, который приходит на ум, это MediaWiki, движок Wikipedia. Сравните схему базы данных здесь, в частности таблицу ревизий.
В зависимости от того, какие технологии вы используете, вам придется найти несколько хороших алгоритмов сравнения / слияния.
Проверьте этот вопрос, если это для.NET.
В мире BI это можно сделать, добавив startDate и endDate в таблицу, для которой вы хотите создать версию. Когда вы вставляете первую запись в таблицу, startDate заполняется, но endDate является нулевым. Когда вы вставляете вторую запись, вы также обновляете endDate первой записи на startDate второй записи.
Когда вы хотите просмотреть текущую запись, вы выбираете ту, в которой endDate равен нулю.
Это иногда называют медленно изменяющимся размером 2 типа. Смотрите также TupleVersioning
Обновление до SQL 2008.
Попробуйте использовать отслеживание изменений SQL в SQL 2008. Вместо взлома меток времени и захоронения столбцов вы можете использовать эту новую функцию для отслеживания изменений данных в вашей базе данных.
Два варианта:
- Иметь таблицу истории - вставляйте старые данные в эту таблицу истории при каждом обновлении оригинала.
- Таблица аудита - сохраните значения до и после - только для измененных столбцов в таблице аудита вместе с другой информацией, например, кто обновил и когда.
Просто хотел добавить, что одним из хороших решений этой проблемы является использование временной базы данных. Многие поставщики баз данных предлагают эту функцию либо из коробки, либо через расширение. Я успешно использовал расширение временных таблиц с PostgreSQL, но у других оно тоже есть. Всякий раз, когда вы обновляете запись в базе данных, база данных также сохраняет предыдущую версию этой записи.
Вы можете выполнить аудит таблицы SQL с помощью триггеров SQL. Из триггера вы можете получить доступ к 2 специальным таблицам ( вставлены и удалены). Эти таблицы содержат точные строки, которые были вставлены или удалены при каждом обновлении таблицы. В триггере SQL вы можете взять эти измененные строки и вставить их в таблицу аудита. Этот подход означает, что ваш аудит прозрачен для программиста; не требуя от них никаких усилий или каких-либо практических знаний.
Дополнительным преимуществом этого подхода является то, что аудит будет выполняться независимо от того, была ли операция sql выполнена через ваши DLL-библиотеки доступа к данным или через SQL-запрос вручную; (так как аудит выполняется на самом сервере).
Алок предложил Audit table выше, я хотел бы объяснить это в моем посте.
Я принял этот проект без единой таблицы в своем проекте.
Схема:
- id - INTEGER AUTO INCREMENT
- имя пользователя - STRING
- имя таблицы - STRING
- oldvalue - TEXT / JSON
- новое значение - TEXT / JSON
- созданные на - DATETIME
Эта таблица может содержать исторические записи для каждой таблицы в одном месте, с полной историей объекта в одной записи. Эта таблица может быть заполнена с помощью триггеров / хуков, где данные изменяются, сохраняя старый и новый снимок значения целевой строки.
Плюсы с этим дизайном:
- Меньшее количество таблиц для управления историей.
- Хранит полный снимок каждой строки старого и нового состояния.
- Легко искать на каждом столе.
- Можно создать раздел по таблице.
- Можно определить политику хранения данных для каждой таблицы.
Минусы с этим дизайном:
- Размер данных может быть большим, если в системе происходят частые изменения.
Вы не говорите, какая база данных, а я не вижу ее в тегах записей. Если это для Oracle, я могу порекомендовать подход, встроенный в Designer: использовать журнальные таблицы. Если это для какой-либо другой базы данных, ну, в принципе, я тоже рекомендую то же самое...
То, как это работает, в случае, если вы хотите скопировать его в другую БД, или, может быть, если вы просто хотите это понять, заключается в том, что для таблицы также создается теневая таблица, просто обычная таблица базы данных с теми же характеристиками полей, плюс некоторые дополнительные поля: например, какое действие было выполнено последним (строка, типичные значения "INS" для вставки, "UPD" для обновления и "DEL" для удаления), дата и время, когда было выполнено действие, и идентификатор пользователя, который сделал Это.
Посредством триггеров каждое действие в любой строке таблицы вставляет новую строку в таблицу журнала с новыми значениями, какое действие было выполнено, когда и каким пользователем. Вы никогда не удаляете строки (по крайней мере, за последние несколько месяцев). Да, он будет расти большими, легко миллионами строк, но вы можете легко отслеживать значение для любой записи в любой момент времени с момента начала ведения журнала или последней очистки старых строк журнала, а также того, кто внес последнее изменение.
В Oracle все, что вам нужно, генерируется автоматически в виде кода SQL, все, что вам нужно сделать, это скомпилировать / запустить его; и это идет с основным приложением CRUD (фактически только "R"), чтобы осмотреть это.
Я тоже делаю то же самое. Я делаю базу данных для планов уроков. Эти планы нуждаются в гибкой версионности атомных изменений. Другими словами, каждое изменение, независимо от того, насколько оно мало, в планах урока, должно быть разрешено, но старая версия также должна быть сохранена. Таким образом, создатели уроков могут редактировать планы уроков, пока ученики их используют.
То, как это будет работать, состоит в том, что, как только ученик сделает урок, его результаты будут приложены к версии, которую он закончил. Если изменения сделаны, их результаты всегда будут указывать на их версию.
Таким образом, если критерии урока удалены или перемещены, их результаты не изменятся.
В настоящее время я делаю это, обрабатывая все данные в одной таблице. Обычно у меня было бы только одно поле id, но в этой системе я использую id и sub_id. Sub_id всегда остается со строкой, через обновления и удаления. Идентификатор автоматически увеличивается. Программное обеспечение плана урока будет связано с самым новым sub_id. Результаты студента будут связаны с идентификатором. Я также включил временную метку для отслеживания, когда произошли изменения, но нет необходимости обрабатывать управление версиями.
Одна вещь, которую я мог бы изменить, после того как я протестировал это, я мог бы использовать ранее упомянутую идею нулевого конца endDate. В моей системе, чтобы найти самую новую версию, мне нужно найти max(id). Другая система просто ищет endDate = null. Не уверен, что преимущества получаются вне поля с другой датой.
Мои два цента.
Пока @WW. Ответ - хороший ответ. Другой способ - создать столбец версий и сохранить все версии в одной таблице.
Для одного стола подходите либо:
- Используйте флаг, чтобы указать последние слова Word Press
- ИЛИ сделать противное больше версии
outer join,
Пример SQL из outer join Метод с использованием номера ревизии:
SELECT tc.*
FROM text_content tc
LEFT OUTER JOIN text_content mc ON tc.path = mc.path
AND mc.revision > tc.revision
WHERE mc.revision is NULL
AND tc.path = '/stuff' -- path in this case is our natural id.
Плохая новость заключается в том, что вышесказанное требует outer join и внешние соединения могут быть медленными. Хорошей новостью является то, что создание новых записей теоретически дешевле, потому что вы можете сделать это за одну операцию записи без транзакций (при условии, что ваша база данных является атомарной).
Пример создания новой редакции для '/stuff' возможно:
INSERT INTO text_content (id, path, data, revision, revision_comment, enabled, create_time, update_time)
(
SELECT
(md5(random()::text)) -- {id}
, tc.path
, 'NEW' -- {data}
, (tc.revision + 1)
, 'UPDATE' -- {comment}
, 't' -- {enabled}
, tc.create_time
, now()
FROM text_content tc
LEFT OUTER JOIN text_content mc ON tc.path = mc.path
AND mc.revision > tc.revision
WHERE mc.revision is NULL
AND tc.path = '/stuff' -- {path}
)
Вставляем, используя старые данные. Это особенно полезно, если, например, вы хотите обновить только один столбец и избежать оптимистической блокировки и / или транзакций.
Подход с использованием флагов и таблиц истории требует вставки / обновления двух строк.
Другое преимущество с outer join Подход с использованием номера редакции заключается в том, что вы всегда можете рефакторинг для подхода с несколькими таблицами позже с помощью триггеров, потому что ваш триггер должен, по сути, делать что-то подобное вышеописанному.
В качестве дополнительного шага к приведенным выше ответам я бы предложил дать каждому сгенерированному изменению уникальный идентификатор, вероятно, что-то с датой / временем и уникальным счетчиком на каждый день (чтобы несколько обновлений в секунду не перекрывались). Я бы включил в этот код код типа действия, так что "9129128213939REPLACE". Это обеспечивает надежность, позволяющую проверять правильность работы другой вашей системы истории.