算法更新关系数据(Algorithms for Updating Relational Data)

2019-07-29 16:05发布

站内文章 / 前沿技术

43 0

三岁会撩人

女 | 书童

私信

什么算法是已知的以执行插入，更新，并在数据库约束的存在删除行更新数据库的任务呢？

更具体而言，说要被删除的行的图像之前，要被插入的行的图像之后，并且被更新的行的两个图像在存储器中。该行可能是几个表。更新的精确序列或者不知道或没有被保存下来 - 只有之前的图像和数据库最终必须以反映被称为之后的图像。

该数据库包含主键，外键和唯一索引约束。问题是要找到命令序列，以使数据库保持最新。为简单起见，我愿意指定行的主键，将永远不会被修改。

数据库系统不支持延迟的约束检查。（该溶液是微不足道的这种数据库）。我还必须让该主键列可能无法插入后更新的规则，它是不允许删除行并重新插入一个具有相同的主键，即使有些算法否则可能会发现可以方便地做到这一点。（这是必要的，其中所有的主键由数据库系统自动生成的常见方案）。

什么是算法：

假设外键约束必须在任何时候被强制执行，但唯一索引不被使用。
假设外键约束和唯一索引约束必须在任何时候都可以执行。

我问这两个，因为我觉得＃1可能是相当简单。

编辑：这样做的目的是为了解决缺乏延迟约束在一般（或几乎通用）的方式检查的问题。 我想，高品质的ORM包必须做到这一点。 我想要的算法的解释，无论是你的学术文章在这里提供的还是外部的等等。我不会考虑一个指向软件包或源代码的回答这个问题。

朴素的算法：

通过表和被添加，修改或删除生成单个INSERT，UPDATE，或DELETE语句，分别对每个每行循环。遍历生成的语句，并应用到数据库。如果一个语句不适用，继续与其他语句。重试已失败的语句。保持迭代，直到不再有故障或次传球成功执行任何声明。如果报表仍然存在，试图对数据的临时扭捏的问题列试图让他们得逞。

这是一个丑陋的蛮力算法，并摸清了“临时调整”的部分是它自己的一个挑战。所以，我想改进和完善算法。

EDIT2：

RBarryYoung职位的答案，靠拢（但没有雪茄）全面解决方案＃1，同时两个问题解决最常见的场景＃。下面是我见过很多时候在应用程序，但在该解决方案还没有到达一个场景＃1更新模式的一个例子。 DELETE / UPDATE，INSERT是完全正确很多的场景＃1的时间，但诀窍是要弄清楚什么时候偏离它。我还怀疑从中偏离放大的每个场景＃2，独特的问题的发生可能增加我的解决方案＃2和兴趣。

请注意，有没有周期也不是修改的任何主键。然而，外键父被修改。

CREATE TABLE A
(
    AId INT NOT NULL PRIMARY KEY
)

CREATE TABLE B
(
    BId INT NOT NULL PRIMARY KEY,
    AId INT NOT NULL FOREIGN KEY REFERENCES A (AId)
)

CREATE TABLE C
(
    CId INT NOT NULL PRIMARY KEY,
    AId INT NOT NULL FOREIGN KEY REFERENCES A (AId),
    BId INT NOT NULL FOREIGN KEY REFERENCES B (BId)
)

图像之前：

A (1)
B (1,1)
C (1,1,1)

后图片：

A (1)
B (2,1) [To be deleted: (1,1)]
C (1,1,2)

排序：A，B，C

第一个命令是DELETE B（1,1），其由于失败C（1,1,1）。

请注意，如果在C第三列允许NULL（它不会在这种情况下），一个纯粹的解决方案可能涉及在早期通归零出来，因为这将允许给定的算法来处理正常进行，并有其一贯的优势大多数情况下排名第2的问题。一个很好的解决这个问题需要采取这样的事情考虑为好。这个问题的完整通用无疑是一个令人着迷的问题。

Answer 1:

为什么你甚至试图做到这一点？做正确的方法是让数据库引擎，直到事务被提交推迟的约束检查。

你提出的问题是，在一般情况下难以解决。如果你考虑要在数据库中更新行的外键的只是一个传递闭包，然后它是唯一可能解决这个地方的图描述了一棵树。如果图中的一个周期，你可以通过用NULL替换外键值打破这个循环，那么你可以重新写一个SQL并添加另一个更新此列。如果你不能用NULL替换的关键值，那么就不能得到解决。

正如我所说，这样做正确的做法是，直到所有的SQL已运行到关闭的限制，然后再将其打开的提交。提交如果约束得不到满足将失败。 Postgres的（例如）有一个特点，这使得这个非常容易。

Answer 2:

我写了一个过一次，但它是别人的IP，所以我不能去太多的细节。不过，我愿意告诉你，教我如何做到这一点的过程。这是使客户的“数据库”的卷影副本驻留在salesforce.com，写在.NET 1.1的工具。

我开始做它蛮力方式（从架构创建数据集和数据库，关闭约束在DataSet中，通过每个表重复，那是还没有在表中加载行，忽略错误，行，重复，直到更多的行添加，或不再有错误，或者在错误的数量没有变化，那么转储数据集到数据库，直到没有错误，等等）。

蛮力是起点，因为它是不肯定的是，我们能做到这一点的。在salesforce.com的“模式”是不是真正的关系模式。举例来说，如果我没记错的话，有哪些是有关的几个父表的一个外键的某些列。

这永远拿了，甚至在调试。我开始注意到，大多数的时间花费在处理违反约束在数据库中。我开始注意到违反约束的模式，因为每个迭代收敛，慢慢地，向获得保存的所有行。

所有我的启示是由于我的无聊，看着系统坐在接近100％的CPU 15-20分钟的时间，即使是小型的数据库。 “需要是发明之母”和“等待20分钟为同一行的前景，往往把重点记”，我想通了如何通过超过100倍，以加快速度。

Answer 3:

OK，我认为这是它，但唯一关键的事情是非常很难搞清楚。需要注意的是，在SQL执行中遇到的任何错误应该导致整个交易的完成回滚。

更新：我实现的原来的顺序是：

每个表，BottumUp（全部删除的表）每个表，自上而下（所有更新，则所有插入）

一个反例被张贴后，我相信，我知道山楂以纠正restriced问题只（问题＃1，无UCS）：通过更改为：

每个表，自上而下（所有插入）每个表，自上而下（所有更新）每个表，BottumUp（全部删除）

这肯定不会，虽然具有唯一约束的工作，这只要我可以计算需要一个行基于内容的相关性排序（而不是静态表FK依赖排序我目前使用）。是什么让这个particularily困难的是，它可能需要大约纪录的含量比改变之外的其他（尤其是检查的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