Алгоритмы обновления реляционных данных

Почему вы вообще пытаетесь это сделать? Правильный способ сделать это - заставить ядро ​​базы данных отложить проверку ограничений до тех пор, пока транзакция не будет зафиксирована.

Поставленная вами проблема в общем случае неразрешима. Если вы рассматриваете только транзитивное закрытие внешних ключей в строках, которые вы хотите обновить в базе данных, то это можно решить только там, где граф описывает дерево. Если на графике есть цикл, и вы можете разорвать цикл, заменив значение внешнего ключа на NULL, вы можете переписать один SQL и добавить другой для последующего обновления столбца. Если вы не можете заменить значение ключа на NULL, то это не может быть решено.

Как я уже сказал, правильный способ сделать это - отключить ограничения до тех пор, пока не будет выполнен весь SQL, а затем снова включить их для фиксации. Фиксация не удастся, если ограничения не соблюдены. Postgres (например) имеет функцию, которая упрощает это.

Один раз я написал, но это чужой IP, поэтому я не могу вдаваться в подробности. Однако я хочу рассказать вам о процессе, который научил меня это делать. Это был инструмент для создания теневой копии клиентской «базы данных», расположенной на salesforce.com, написанной на .NET 1.1.

Я начал делать это методом грубой силы (создать DataSet и базу данных из схемы, отключить ограничения в DataSet, выполнить итерацию по каждой таблице, загрузить строки, игнорировать ошибки, для строк, которых еще нет в таблице, повторять до тех пор, пока не появится больше строк чтобы добавить, или больше ошибок, или не изменять количество ошибок, затем выгружать DataSet в базу данных, пока ошибок не будет и т. д.).

Грубая сила была отправной точкой, потому что не было уверенности, что мы вообще сможем это сделать. «Схема» salesforce.com не была настоящей реляционной схемой. Например, если я правильно помню, были некоторые столбцы, которые были внешними ключами, относящимися к одной из нескольких родительских таблиц.

Это длилось вечно, даже во время отладки. Я начал замечать, что большую часть времени уходит на обработку нарушений ограничений в базе данных. Я начал замечать паттерн нарушений ограничений, поскольку каждая итерация медленно приближалась к сохранению всех строк.

Все откровения, которые я получил, были связаны с моей скукой, когда я смотрел, как система загружает процессор почти на 100% в течение 15-20 минут за раз, даже с небольшой базой данных. «Необходимость - мать изобретений», и «перспектива подождать еще 20 минут для тех же строк имеет тенденцию сосредотачивать внимание», и я понял, как ускорить процесс более чем в 100 раз.

Хорошо, я думаю, что это все, хотя с уникальным ключом довольно сложно понять. Обратите внимание, что любые ошибки, обнаруженные при выполнении SQL, должны привести к полному откату всей транзакции.

ОБНОВЛЕНИЕ: исходный заказ, который я реализовал, был:

Каждая таблица, BottumUp (все удаления для таблицы) Каждая таблица, TopDown (все обновления, затем все вставки)

После того, как контрпример был опубликован, я считаю, что знаю, как исправить только ограниченную проблему (проблема №1, без UC): изменив порядок на:

Каждая таблица, TopDown (все вставки) Каждая таблица, TopDown (все обновления) Каждая таблица, BottumUp (все удаляются)

Это определенно НЕ будет работать с уникальными ограничениями, которые, насколько я могу понять, потребуют сортировки зависимостей на основе содержимого строки (в отличие от сортировки зависимостей FK статической таблицы, которую я использую в настоящее время). Что делает это особенно трудным, так это то, что может потребоваться получение информации о содержимом записи, отличном от измененного (в частности, проверка наличия конфликтных значений UC и дочерних зависимых записей для промежуточных шагов).

Во всяком случае, вот текущая версия:

Public Class TranformChangesToSQL
 Class ColVal
    Public name As String
    Public value As String  'note: assuming string values'
 End Class

 Class Row
    Public Columns As List(Of ColVal)
 End Class

 Class FKDef
    'NOTE: all FK''s are assumed to be of the same type: records in the FK table'
    ' must have a record in the PK table matching on FK=PK columns.'
    Public PKTableName As String
    Public FKTableName As String
    Public FK As String
 End Class

 Class TableInfo
    Public Name As String
    Public PK As String                     'name of the PK column'
    Public UniqueKeys As List(Of String)    'column name of each Unique key'
    'This table''s Foreign Keys (FK):'
    Public DependsOn As List(Of FKDef)
    'Other tables FKs that point to this table'
    Public DependedBy As List(Of FKDef)
    Public Columns As List(Of String)
    'note: all row collections are indexed by PK'
    Public inserted As List(Of Row)     'inserted after-images'
    Public deleted As List(Of Row)      'deleted before-images'
    Public updBefore As List(Of row)
    Public updAfter As List(Of row)
 End Class

 Sub MakeSQL(ByVal tables As List(Of TableInfo))
    'Note table dependencies(FKs) must NOT form a cycle'

    'Sort the tables by dependency so that'
    ' child tables (FKs) are always after their parents (PK tables)'
    TopologicalSort(tables)

    For Each tbl As TableInfo In tables
        'Do INSERTs, they *must* be done first in parent-> child order, because:'
        '   they may have FKs dependent on parent inserts'
        '   and there may be Updates that will make child records dependent on them'
        For Each r As Row In tbl.inserted
            Dim InsSQL As String = "INSERT INTO " & tbl.Name & "("
            Dim valstr As String = ") VALUES("
            Dim comma As String = ""
            For Each col As ColVal In r.Columns
                InsSQL = InsSQL & comma & col.name
                valstr = valstr & comma & "'" & col.value & "'"
                comma = ", "    'needed for second and later columns'
            Next
            AddSQL(InsSQL & valstr & ");")
        Next
    Next

    For Each tbl As TableInfo In tables
        'Do UPDATEs'
        For Each aft In tbl.updAfter
            'get the matching before-update row'
            Dim bef As Row = tbl.updBefore(aft.Columns(tbl.PK.ColName).value)
            Dim UpdSql As String = "UPDATE " & tbl.Name & " SET "
            Dim comma As String = ""
            For Each col As ColVal In aft.Columns
                If bef.Columns(col.name).value <> col.value Then
                    UpdSql = UpdSql & comma & col.name & " = '" & col.value & "'"
                    comma = ", "  'needed for second and later columns'
                End If
            Next
            'only add it if any columns were different:'
            If comma <> "" Then AddSQL(UpdSql & ";")
        Next
    Next

    'Now reverse it so that INSERTs & UPDATEs are done in parent->child order'
    tables.Reverse()

    For Each tbl As TableInfo In tables.Reverse
        'Do DELETEs, they *must* be done last, and in child->paernt order because:'
        '   Parents may have children that depend on them, so children must be deleted first,'
        '   and there may be children dependent until after Updates pointed them away'
        For Each r As Row In tbl.deleted
            AddSQL("DELETE From " & tbl.Name & " WHERE " & tbl.PK.ColName & " = '" & r.Columns(tbl.PK.ColName).value) & "';"
        Next
    Next

 End Sub
End Class

Нет, я не считаю это увлекательным. Я тоже не считаю проблему квадратуры круга увлекательной, и по этой теме тоже есть люди, которые категорически или даже категорически не согласны со мной.

Когда вы говорите, что «у него есть практическое применение», вы имеете в виду, что «решение этой проблемы имеет практическое применение»? Я предполагаю, что решение, которого не существует, по определению не может иметь «практического применения». (И я действительно предполагаю, что решение, которое вы ищете, не существует, как и квадратура круга.)

Вы что-то спорили о том, что «когда другие приложения вешают каскадные удаления ...». В вашей первоначальной формулировке проблемы вообще не упоминались «другие приложения».

Проблема, которую я нахожу более увлекательной, - это «как создать СУБД, которая была бы достаточно хорошей, чтобы программисты больше не сталкивались с подобными проблемами и не были вынуждены задавать подобные вопросы». Такая СУБД поддерживает множественное присвоение.

Желаю вам удачи.

Похоже, вы ищете инструмент Database Diff. Такой инструмент будет искать различия между двумя таблицами (или двумя базами данных) и генерировать необходимые сценарии для их выравнивания.

Дополнительную информацию см. В следующем сообщении:
https://stackoverflow.com/questions/104203/anyone-know-of-any-good-database-diff-tools.

OpenDbDiff имеет доступный исходный код. Вы можете посмотреть на это и выяснить алгоритмы.

http://opendbiff.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=25206