All geek questions in one place

Relational Data Update Algorithms

It is known what algorithms perform the task of updating the database by inserting, updating, and deleting rows in the presence of database restrictions?

In particular, let's say that before the images of the lines that need to be deleted, after the images of the inserted lines and by the two images of the lines that need to be updated, they are in memory. Rows can be for several tables. The exact sequence of updates is either unknown or has not been saved - only previous images and images are known after the database has to be made for reflection.

The database contains a primary key, a foreign key, and unique index restrictions. The problem is to find a sequence of commands to update the database. For simplicity, I’m ready to point out that the primary key of the string will never be changed.

The database system does not support pending constraint checking. (This solution is trivial for such databases). I also have to make a rule that columns of primary keys cannot be updated after insertion, and it is not allowed to delete a row and reinsert it with the same primary key, although some algorithms may otherwise find this convenient. (This is necessary for common scenarios where all primary keys are automatically generated by the database system.)

What are the algorithms:

  • , , .
  • , .

, , №1 .

: - ( ) . , ORM . , , .. .

:

, INSERT, UPDATE DELETE, . . , . , . , , . , , .

" " - . , .

EDIT2:

RBarryYoung , ( ), № 1 №2. № 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 ( ), NULLing , №2. . , , .

+2
6

? - , .

, , . , , , . , , NULL, SQL , . NULL, .

, - , SQL- , . , . Postgres () , .

+4

, - IP, . , , , . " ", salesforce.com, .NET 1.1.

( DataSet , DataSet, , , , , , , , , , DataSet , ..).

, , . "" salesforce.com . , , , , .

, . , . , , .

, , - , , 100% 15-20 , . " - ", " 20 , ", , 100 .

+1

, , , Unique Key . , , SQL, .

UPDATE: , , :

, 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' 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' 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
+1

, . , , , .

, " ", , " "? , , , " ". ( , , , , .)

- , " ...". " ".

, , - " , , ". .

.

+1

Source: https://habr.com/ru/post/1712251/

More articles:


All Articles