Der einfachere Weg - p

Backend
Created for Tilman Börner ([email protected]) - 2533 on 30.04.2014 15:04:08
Please do not make illegal copies!
Eine SQL-Server-Änderungshistorie implementieren
Der einfachere Weg
Es muss kein kompliziertes Event Sourcing sein, um in einer relationalen Datenbank Änderungen an den Datensätzen
zu protokollieren. Es geht auch einfacher mit zwei eigens dafür geschaffenen Tabellen.
Auf einen Blick
Andy Grothe ist freiberuflicher Softwarearchitekt und
spezialisiert auf die Entwicklung
von WCF-, WPF-, Silverlight-,
Windows-8- und WindowsPhone-Anwendungen. Sie
erreichen ihn über seine Website
www.P-Serv.de oder direkt
unter [email protected].
Inhalt
▸ Mit zusätzlichen Datenbank­
tabellen Veränderungen protokollieren.
▸ Implementierung über Trigger
und die SQL Server Data
Tools.
▸ Laufzeitvergleich verschiedener Techniken.
dnpCode
A1405History
W
er ärgert sich nicht, wenn die EMail eines Kunden aus der Firmendatenbank gelöscht wurde; egal ob
das mit Absicht oder aus Versehen geschah.
Wie kommen Sie jetzt so schnell wie möglich
wieder an die Daten? Eine Möglichkeit wäre,
das Datenbank-Backup des Vortags zurückzuspielen und dort nachzuschauen. Das geht
meistens nicht ohne Zutun der Administratoren. Einfacher wäre es, wenn der Benutzer ohne Weiteres und ohne Zutun eines Dritten die
Möglichkeit hätte, in einer Änderungshistorie
nachzusehen.
Um den Anwendern ein Protokoll der Datenänderungen zur Verfügung stellen, gibt es
mehrere Ansätze. Einer wäre der Einsatz einer
Datenbank mit Event Sourcing; die dotnetproAusgabe 12/2013 widmete dem Thema Event
Sourcing einen ganzen Schwerpunkt. Eine weitere Möglichkeit bietet Microsoft seit dem SQL
Server 2008 mit dem Merkmal „Tracking Data
Changes“ [1]. Es gibt aber noch eine andere
Möglichkeit, die sich mit geringem Aufwand in
eine bestehende SQL-Server-Datenbank einbauen lässt und zudem den relationalen Charakter der Daten beibehält.
Die Werkzeuge dafür sind Visual Studio 2012
und die SQL Server Data Tools (SSDT) [2]. Welche Rolle diese Tools spielen, wird sich am
Schluss erweisen.
[Abb. 1] Die Live- und History-Tabellen des Beispielprojekts und ihre Beziehungen.
76
Live- und History-Tabellen
Das hier vorgestellte Konzept basiert auf zusätzlichen Tabellen in der Datenbank, deren
Aufgabe es ist, die Datenänderungen zu erfassen. Sie werden als „Live-Tabellen“ und „History-Tabellen“ bezeichnet. Live-Tabellen stellen
die jeweils letzten (und damit aktuellen) Datensätze bereit; die History-Tabellen dagegen
erfassen die Änderungen in den Live-Tabellen.
Die Protokollierung – auch temporale Datenhaltung oder Historisierung genannt – wird für
jede Live-Tabelle separat konfiguriert. Dazu ist
für jede Live-Tabelle eine History-Tabelle anzulegen. In der Live-Tabelle sorgt dann ein Trigger
dafür, dass bei jedem Insert-, Update- oder Delete-Vorgang die History-Tabelle entsprechend
aktualisiert wird.
Um dies an einem Beispiel zu veranschaulichen, öffnen Sie Visual Studio und legen ein
neues SQL-Server-Datenbankprojekt an. Die
Projekteigenschaften stellen auf der Registerkarte Projekteinstellungen die Auswahl der Zielplattform zur Verfügung, im vorliegenden Fall
ist dies SQL Server 2012. Auf der Registerkarte
Debuggen kann die Verbindungszeichenfolge
zum SQL Server konfiguriert werden, dabei hilft
ein Assistent. Er startet nach einem Mausklick
auf die Schaltfläche Bearbeiten …
Nun erstellen Sie einen Ordner namens
Ta­b les und legen in ihm die Live-Tabellen
t_Address und t_ContactDetails sowie die History-Tabellen th_Address und th_ContactDetails
an; ihre Schemas sind in Abbildung 1 zu sehen.
Die History-Tabellen folgen immer dem gleichen Schema: Sie enthalten die gleichen Felder
wie die jeweils zugrunde liegende Live-Tabelle
und dazu noch die folgenden vier Felder:
◼ h
ID
◼ h
Command
◼ h
User
◼ h
DateTime
hID ist der neue Primärschlüssel der Tabelle, der mit Autoinkrement ausgestattet wird;
hCommand informiert darüber, ob es sich um
einen Insert-, Update- oder Delete-Befehl handelt; hUser speichert den Namen des an der
Session angemeldeten Benutzers; hDateTime
enthält das Datum und die Zeit des Vorgangs.
Die restlichen Felder entsprechen genau der
5.2014 www.dotnetpro.de
Backend
Created for Tilman Börner ([email protected]) - 2533 on 30.04.2014 15:04:08
Please do not make illegal copies!
Definition der jeweiligen Live-Tabelle.
Ausnahme ist der Primärschlüssel mit
seinem Autoinkrement, der vom Feld ID
auf das Feld hID verschoben wird. Die
Fremdschlüsselbeziehungen zwischen
den History-Tabellen können natürlich
nicht mehr gesetzt werden, da in ihnen
die Felder, die ehemals den Primärschlüssel enthielten, keine eindeutigen Felder
mehr sind.
Jetzt können die Tabellen bereits auf
dem SQL Server eingerichtet werden.
Dazu drücken Sie die Taste [F5] oder klicken im Kontextmenü des Projekts auf
den Menüeintrag Veröffentlichen … Das
Visual-Studio-Fenster Datentoolvorgänge
zeigt an, ob der Vorgang erfolgreich war
(Abbildung 2).
[Abb. 2] Im Fenster „Datentoolvorgänge“ ist zu sehen, ob das Anlegen der Tabellen erfolgreich war.
Trigger startet Historisierung
Nachdem die Live- und History-Tabellen
angelegt sind, bedarf es eines Mechanismus, der Datensatzänderungen feststellt und diese in den History-Tabellen
speichert. Für jede Live-Tabelle wird also ein Trigger eingerichtet. Dazu erhält
das Projekt einen neuen Ordner namens
Triggers. Diesem fügen Sie einen SQL CLR
VB-Trigger hinzu; bei einem C#-Projekt ist
es ein SQL CLR C#-Trigger. Hier heißt es
aufgepasst, denn an dieser Stelle besteht
Verwechslungsgefahr mit dem normalen
T-SQL-Trigger. Um ein möglichst gutes
Laufzeitverhalten zu erzielen, kommt hier
ein expliziter CLR-Trigger zum Zug.
Abbildung 3 zeigt den Aufbau der Trigger-Klasse, die hier HistoryTrigger heißt.
Die Konstante sqlToTemplate enthält einen
mit Platzhaltern versehenen T-SQL-Befehl.
Es folgen zwei Trigger, für jede Live-Tabelle
einer. Am Ende enthält die Klasse noch die
Hilfsfunktion ConvertToSql(), die in jedem
Trigger mehrmals verwendet wird und die
in Listing 1 zu sehen ist. Je nach Typ bereitet sie den Wert für den T-SQL Befehl
auf [3]. Datumswerte formatiert sie nach
ISO-Norm, damit der SQL Server, bedingt
durch unterschiedliche Ländereinstellungen, nicht ins Stolpern gerät. Textwerte
setzt die Funktion in einfache Hochkommas und natürlich werden die Werte auch
auf DBNull geprüft.
Eine kleine Einschränkung gibt es bei
Feldern, die intern dem DbType-Enumerationswert Binary entsprechen. Dazu
zählen die SQL-Server-Datenbankmodultypen Binary, Image, Rowversion,
Timestamp und VarBinary [3]. Enthält
eine Tabelle die Felder Timestamp oder
Rowversion, wird das Speichern des Ände-
[Abb. 3] Die Gliederung der HistoryTrigger-Klasse.
[Abb. 4] Ein Ausschnitt aus der History-Tabelle t_Address nach dem Testlauf.
rungsdatensatzes fehlschlagen, da diese
beiden Felder nicht manuell gesetzt oder
geändert werden dürfen. In der Hilfsfunktion muss die Codezeile, die für die Behandlung von DbType-Feldern des Typs
Binary zuständig ist, auskommentiert
werden; das heißt aber auch, dass Typen
wie Binary oder VarBinary nicht in die
Änderungshistorie übernommen werden.
Der Quellcode der Trigger ist bis auf die
ersten drei Codezeilen immer identisch,
siehe Listing 2. Die Information über die
Änderungen wird aus den beiden temporären Tabellen Inserted oder Deleted
gewonnen. Diese stehen nur im Kontext
eines Triggers zur Verfügung. Das ist auch
der Grund, weshalb sich der Quellcode
nicht weiter modularisieren lässt.
Nun wird jeder Datensatz verarbeitet.
Dabei werden die Spaltennamen und die
Feldinhalte zusammengetragen, der TSQL-Befehl erzeugt und die Änderungen
in die History-Tabelle gespeichert.
Das Ergebnis lässt sich sehen
Nachdem die HistoryTrigger-Klasse fertiggestellt ist, kann sie auf dem SQL Server
eingerichtet und getestet werden. Um die
Funktionsfähigkeit der Klasse zu prüfen,
tragen Sie manuell oder per T-SQL-Skript
einen Datensatz in die Tabelle t_Address
ein, ändern ihn einige Male und löschen
ihn zuletzt wieder. Die History-Tabelle
sollte dann in etwa aussehen, wie es in
Abbildung 4 dargestellt ist, während der
Datensatz in der Live-Tabelle bereits nicht
mehr vorhanden ist – denn er wurde ja
wieder gelöscht.
Der Praxistest
Nun stellt sich lediglich noch die Frage,
wie viel Zeit das zusätzliche Ausführen
des Triggers eigentlich benötigt. Eine
kleine Testreihe auf dem Entwicklungsrechner des Autors (Windows 8.1, Intel
i5, 8 GB RAM, SQL Server 2012 Developer
Edition) beantwortet die Frage. Dazu wurde der Trigger der Live-Tabelle t_Address
deaktiviert und für das Feld Birthday ein
Standardwert gesetzt; die Funktion getdate() setzte beim Einfügen eines Datensatzes einen aktuellen Zeitstempel. Ein TSQL-Skript führte dann hundert Mal den
folgenden Befehl aus :
INSERT INTO t_Address (
LastName, FirstName, Street,
PostalCode, City, Country)
VALUES (
'Donald', 'Duck', 'Am Ententeich 3b',
'99770', 'Entenhausen', 'Germany')
www.dotnetpro.de 5.201477
Backend Eine SQL-Server-Änderungshistorie implementieren
Created for Tilman Börner ([email protected]) - 2533 on 30.04.2014 15:04:08
Please do not make illegal copies!
Listing 1
Die Hilfsfunktion ConvertToSql().
Public Shared Function ConvertToTSql(ByVal value As Object) As String
If TypeOf value Is System.DBNull Then
Return "NULL"
ElseIf TypeOf value Is System.DateTime Then
Return "'" & CDate(value).ToString("yyyy-MM-ddTHH:mm:ss.fffffff") & "'"
ElseIf TypeOf value Is System.TimeSpan Then
Return "'" & value.ToString & "'"
ElseIf TypeOf value Is System.DateTimeOffset Then
Return "'" & CType(value, DateTimeOffset).ToString("o") & "'"
ElseIf TypeOf value Is SqlTypes.SqlBinary Then
Return If(CType(value, SqlBinary).IsNull, "NULL", "0x" & BitConverter.ToString(CType(value, SqlBinary).Value).Replace("-", ""))
ElseIf TypeOf value Is SqlTypes.SqlBoolean Then
Return If(CType(value, SqlBoolean).IsNull, "NULL", CType(value, SqlBoolean).ByteValue.ToString)
ElseIf TypeOf value Is SqlTypes.SqlDateTime Then
Return If(CType(value, SqlDateTime).IsNull, "NULL", "'" & CType(value, SqlDateTime).ToString & "'")
ElseIf TypeOf value Is SqlTypes.SqlDecimal Then
Return If(CType(value, SqlDecimal).IsNull, "NULL",
CType(value, SqlDecimal).Value.ToString(CultureInfo.CreateSpecificCulture("en-US")))
ElseIf TypeOf value Is SqlTypes.SqlDouble Then
Return If(CType(value, SqlDouble).IsNull, "NULL",
CType(value, SqlDouble).Value.ToString(CultureInfo.CreateSpecificCulture("en-US")))
ElseIf TypeOf value Is SqlTypes.SqlGuid Then
Return If(CType(value, SqlGuid).IsNull, "NULL", "'" & CType(value, SqlGuid).Value.ToString & "'")
ElseIf TypeOf value Is SqlTypes.SqlInt16 Then
Return If(CType(value, SqlInt16).IsNull, "NULL", CType(value, SqlInt16).Value.ToString)
ElseIf TypeOf value Is SqlTypes.SqlInt32 Then
Return If(CType(value, SqlInt32).IsNull, "NULL", CType(value, SqlInt32).Value.ToString)
ElseIf TypeOf value Is SqlTypes.SqlInt64 Then
Return If(CType(value, SqlInt64).IsNull, "NULL", CType(value, SqlInt64).Value.ToString)
ElseIf TypeOf value Is SqlTypes.SqlMoney Then
Return If(CType(value, SqlMoney).IsNull, "NULL",
CType(value, SqlMoney).Value.ToString(CultureInfo.CreateSpecificCulture("en-US")))
ElseIf TypeOf value Is SqlTypes.SqlSingle Then
Return If(CType(value, SqlSingle).IsNull, "NULL",
CType(value, SqlSingle).Value.ToString(CultureInfo.CreateSpecificCulture("en-US")))
ElseIf TypeOf value Is SqlTypes.SqlString Then
Return If(CType(value, SqlString).IsNull, "NULL", "'" & CType(value, SqlString).Value.ToString & "'")
ElseIf TypeOf value Is SqlTypes.SqlXml Then
Return If(CType(value, SqlXml).IsNull, "NULL", "'" & CType(value, SqlXml).Value.ToString & "'")
Else
Return "NULL"
End If
End Function
78
5.2014 www.dotnetpro.de
Backend
Created for Tilman Börner ([email protected]) - 2533 on 30.04.2014 15:04:08
Please do not make illegal copies!
Nach einem Lauf des Skripts ließ sich
anhand des Feldes Birthday schnell ausrechnen, wie viel Zeit vom Einfügen des
ersten Datensatzes bis zum letzten verstrichen ist. Anschließend wurde das Ganze mit aktiviertem Trigger wiederholt.
Nun zeigt sich auch, welche Rolle die
eingangs erwähnten SQL Server Data
Tools spielen. Bei den Vorbereitungen zu
diesem Artikel hat der Autor das Ganze
zunächst mit Triggern und einer gespeicherten Prozedur auf herkömmliche Art
und Weise per T-SQL entwickelt. Dies ließ
sich allerdings zum einen nicht debuggen
und zum anderen war der Unterschied
im Laufzeitverhalten so dramatisch, dass
der Artikel zunächst „gestorben“ schien –
zum Historisieren von Datensätzen kam
dieser Weg nicht ernsthaft infrage. Erst
der Einsatz der SQL Server Data Tools
und der mit ihnen ausgelieferten .NETAssemblies gaben dieser Lösung den nötigen Schub und ließen den Einsatz für
professionelle Zwecke zu. Die Zeiten für
das Einfügen von 100 Datensätzen betrug
◼ 50 Millisekunden ohne Trigger
◼ 200 Millisekunden mit CLR-Trigger
◼ 2800 Millisekunden mit T-SQL-Trigger
Listing 2
Daten vom Typ SqlBinary erfordern einen speziellen Umgang.
<Microsoft.SqlServer.Server.SqlTrigger(Name:="HistoryTrigger_t_Address", _
Target:="t_Address", Event:="FOR INSERT, UPDATE, DELETE")> _
Public Shared Sub HistoryTrigger_t_Address()
Const historyTableName As String = "th_Address"
Dim
Dim
Dim
Dim
Dim
Dim
Dim
Dim
Dim
action As String = SqlContext.TriggerContext.TriggerAction.ToString
sqlFrom As String
sqlTo() As String = Nothing
dynFields As String
dynValues As String
sqlConnection As SqlConnection
sqlCommand As SqlCommand
sqlDataReader As SqlDataReader
index As Integer
Try
'Zugriff auf die Tabellen 'Deleted' oder 'Inserted'
If SqlContext.TriggerContext.TriggerAction = TriggerAction.Delete Then
sqlFrom = "SELECT * FROM Deleted"
Else
sqlFrom = "SELECT * FROM Inserted"
End If
sqlConnection = New SqlConnection("context connection=true")
sqlConnection.Open()
sqlCommand = sqlConnection.CreateCommand()
sqlCommand.CommandText = sqlFrom
sqlDataReader = sqlCommand.ExecuteReader
Do While sqlDataReader.Read()
dynFields = Nothing
dynValues = Nothing
Ein T-SQL-Trigger erfordert also 56mal so viel Zeit wie das Ausführen ohne
Trigger, ein CLR-Trigger nur noch viermal so viel. Dies ist ein durchaus akzeptabler Wert.
'jede Tabellen-Spalte verarbeiten
For index = 0 To SqlContext.TriggerContext.ColumnCount - 1
If dynFields Is Nothing Then
dynFields = "[" & sqlDataReader.GetName(index) & "]"
dynValues = ConvertToTSql(sqlDataReader.GetSqlValue(index))
Else
dynFields &= ", [" & sqlDataReader.GetName(index) & "]"
dynValues &= ", " & ConvertToTSql(sqlDataReader.GetSqlValue(index))
End If
Next
Fazit
Das Protokollieren von Änderungen von
Datensätzen in Datenbanken ist ein effizienter Weg, um beispielsweise bei unabsichtlichen Änderungen an Daten den
vorherigen Zustand wiederherzustellen –
ganz ohne Wühlen in einem Backup. Ein
Weg hierfür wäre die Implementierung
von Event Sourcing, aber so umständlich
muss es gar nicht sein. Das Einrichten
von zusätzlichen Tabellen, in denen sich
Datenänderungen über die Zeit hinweg
festhalten lassen, sogenannten HistoryTabellen, erfordert nur geringen Aufwand
und bleibt dem relationalen Charakter der
Daten treu.
[jp]
[1] M
SDN: Nachverfolgen von Datenänderungen
(SQL Server),
www.dotnetpro.de/SL1405History1
[2] M
SDN: SQL Server Data Tools,
www.dotnetpro.de/SL1405History2
[3] M
SDN: SQL Server-Datentypmappings,
www.dotnetpro.de/SL1405History3
'T-Sql zwischenspeichern
index = If(sqlTo Is Nothing, 0, sqlTo.Length)
ReDim Preserve sqlTo(index)
sqlTo(index) = String.Format(sqlToTemplate, historyTableName, dynFields,
action, Date.Now.ToString("yyyy-MM-ddTHH:mm:ss.fffffff"), dynValues)
Loop
sqlDataReader.Close()
'T-Sql ausführen (Änderung wird in die History-Tabelle geschrieben)
For index = 0 To sqlTo.Length - 1
sqlCommand = sqlConnection.CreateCommand()
sqlCommand.CommandText = sqlTo(index)
sqlCommand.ExecuteNonQuery()
Next
Catch ex As Exception
End Try
End Sub
www.dotnetpro.de 5.201479