Kopier- und Löschreihenfolge in MySQL

Im Beitrag “Kopier- und Löschreihenfolge für Tabellen” (www.access-im-unternehmen.de/926) haben wir ermittelt, wie wir die richtige Reihenfolge für das Löschen von Tabellen und das Kopieren von Tabelleninhalten von einer Datenbank in die nächste ermitteln. Wenn Sie die Reihenfolge nicht beachten, kann es nämlich sein, dass Datensätze wegen Fremdschlüsselverletzungen nicht gelöscht und auch nicht kopiert werden können. Im vorliegenden Beitrag zeigen wir, wie Sie die vorgestellte Lösung für das Löschen und Kopieren von Tabellen in MySQL-Datenbanken nutzen können.

In oben genanntem Beitrag haben wir bereits erläutert, in welcher Reihenfolge die Tabellen gelöscht und deren Daten kopiert werden müssen, damit dies ohne Fehlermeldungen durch Restriktionen möglich ist. Wir fassen das noch einmal zusammen: Wenn Sie etwa nur die beiden Tabellen tblKunden und tblAnreden verwenden, bei denen die Tabelle über das Fremdschlüsselfeld AnredeID mit dem Feld AnredeID der Tabelle tblAnreden verknüpft ist, gelten folgende Regeln:

  • Beim Löschen der enthaltenen Datensätze müssen erst die Datensätze gelöscht werden, die in der Tabelle mit dem Fremdschlüsselfeld der Beziehung enthalten sind. Wir löschen also zunächst die Datensätze der Tabelle tblKunden und erst dann die Datensätze der Tabelle tblAnreden.
  • Beim Kopieren der Daten von Tabellen einer Datenbank in die Tabellen der anderen Datenbank müssen erst die Datensätze der Tabelle mit dem Primärschlüsselfeld der Beziehung kopiert werden und dann die der anderen Tabelle. Wir kopieren also erst die Datensätze der Tabelle tblAnreden und erst dann die der Tabelle tblKunden.

Auf die gleiche Weise können Sie bei komplexeren Datenmodellen vorgehen. Um das zu automatisieren, sucht man sich zum Löschen der Daten zunächst die Tabellen heraus, auf die keine andere Tabelle per Fremdschlüsselfeld verweist. Im zweiten Schritt ermitteln wir alle Tabellen, die nicht bereits ermittelt wurden und auf die keine andere der noch übrigen Tabellen per Fremdschlüsselfeld verweist und so weiter.

Beim Kopieren von Daten suchen wir im ersten Schritt zuerst die Tabellen heraus, die nicht per Fremdschlüsselfeld auf andere Tabellen verweisen. Dann suchen wir im zweiten Schritt die Tabellen heraus, die per Fremdschlüsselfeld nur auf die im ersten Schritt ermittelten Tabellen verweisen und so weiter.

Reihenfolge per VBA bestimmen

Im weiter oben erwähnten Beitrag haben wir bereits einen Algorithmus definiert, mit dem wir die Reihenfolge der Tabellen beim Löschen und beim Kopieren der Daten der Tabellen ermitteln. Dabei haben wir die Elemente der DAO-TableDefs-Auflistung durchlaufen und für die Informationen bezüglich der Beziehungen die Daten der Relations-Auflistung genutzt.

Unter MySQL stehen uns diese Elemente nicht zur Verfügung. Wenn Sie zwei miteinander in Beziehung stehende und per ODBC eingebundene MySQL-Tabellen in das Beziehungen-Fenster ziehen, sehen Sie bereits, dass die für diese Tabellen definierte Beziehung nicht in Access verfügbar ist (siehe Bild 1).

Keine Beziehungen von ODBC-verknüpften Tabellen sichtbar

Bild 1: Keine Beziehungen von ODBC-verknüpften Tabellen sichtbar

Alle Tabellen einbinden

Hier stehen wir am Scheideweg, denn nun gibt es zwei Möglichkeiten:

  • Sie fügen alle per ODBC eingebundenen Tabellen zum Beziehungen-Fenster hinzu und fügen die Beziehungen manuell ein. Das ist allerdings je nach Anzahl der Tabellen eine Fleißarbeit, die – wie bei solchen Arbeiten üblich – mit einer Fehleranfälligkeit ausgestattet ist. Danach können Sie die Reihenfolge der Tabelle zum Löschen und Kopieren mit den bereits vorgestellten Prozeduren ermitteln.
  • Sie schreiben eine Prozedur, die alle Tabellen der Datenbank unter MySQL hinsichtlich der Beziehungen untersucht und diese automatisiert zu den per ODBC eingebundenen Tabellen hinzufügt. Danach können Sie die Reihenfolge der Tabelle zum Löschen und Kopieren ebenfalls mit den bereits vorgestellten Prozeduren ermitteln.
  • Oder wir passen die vorhandenen Prozeduren so an, dass wir per ADO direkt auf die Informationen über die Beziehungen zwischen den Tabellen zugreifen. Hierzu müssten wir noch herausfinden, wie wir auf die gewünschten Informationen zugreifen. Da wir hierbei etwas Neues lernen, entscheiden wir uns an dieser Stelle für diese Option.

Tabellen durchlaufen

Das Durchlaufen der Tabellen erfolgt nicht über eine Auflistung wie TableDefs, sondern wir nutzen die Anweisung SHOW TABLES, mit der wir wie mit einer SELECT-Anweisung auf die Daten der MySQL-Datenbank zugreifen. Show Tables liefert uns ebenfalls ein Ergebnis in Form eines Recordsets zurück. In den folgenden Codezeilen nutzen wir eine Hilfsfunktion namens GetRecordset, deren ersten Parameter wir mit einer Konstanten füllen, welche die Verbindungszeichenfolge zu der gewünschten Datenbank enthält:

Dim rstTabellen As ADODB.Recordset
Set rstTabellen = GetRecordset(GetConnection( cStrVerbindungszeichenfolgeAlt), "SHOW TABLES")
Do While Not rstTabellen.EOF
     Debug.Print rstTabellen.Fields(0)
     rstTabellen.MoveNext
Loop

Hier füllen wir so ein Recordset namens rstTabellen mit allen Einträgen, die der Befehl SHOW TABLES zurückliefert und geben diese im Direktbereich des VBA-Fensters aus. Für die verwendeten Hilfsfunktionen benötigen wir noch einen Verweis auf die Bibliothek Microsoft ActiveX Data Objects x.y Library, den Sie über den Verweise-Dialog hinzufügen (VBA-Editor, Menüeintrag Extras|Verweise).

Beziehungen ermitteln

Nun wollen wir wissen, ob eine Tabelle über ein Fremdschlüsselfeld mit einer anderen Tabelle verknüpft ist oder ob eine Tabelle vom Fremdschlüsselfeld einer anderen Tabelle referenziert wird.

Dazu verwenden wir testweise die Prozedur Relationinfo aus Listing 1. In dieser weisen wir der Variablen strSQL die folgende Abfrage zu:

Public Sub Relationinfo()
     Dim rst As ADODB.Recordset
     Dim strSQL As String
     Dim fld As ADODB.Field
     strSQL = "SELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME " & vbCrLf _
         & "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE" & vbCrLf _
         & "WHERE REFERENCED_TABLE_NAME IS NOT NULL;"
     Set rst = GetRecordset(GetConnection(cStrVerbindungszeichenfolgeAlt), strSQL)
     For Each fld In rst.Fields
         Debug.Print fld.Name,
     Next fld
     Debug.Print
     Do While Not rst.EOF
         For Each fld In rst.Fields
             Debug.Print fld.Value,
         Next fld
         Debug.Print
         rst.MoveNext
     Loop
End Sub

Listing 1: Ermitteln aller Beziehungen mit beteiligten Tabellen und Feldern

SELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE REFERENCED_TABLE_NAME IS NOT NULL;

Hier ermitteln wir die Felder mit den relevanten Informationen aus der Tabelle INFORMATION_SCHEMA.KEY_COLUMN_USAGE für die Bedingung REFERENCED_TABLE_NAME IS NOT NULL. Danach durchlaufen wir alle Felder der Tabelle und geben die Namen der Felder als Kopfzeile im Direktbereich des VBA-Editors aus und liefern direkt anschließend die Werte dieser Felder für alle gefundenen Datensätze.

Das sieht dann etwa wie folgt aus:

TABLE_NAME    COLUMN_NAME   REFERENCED_TABLE_NAME       REFERENCED_COLUMN_NAME      
s_order_basket_attributes   basketID      s_order_basket              id            
s_core_auth_attributes      authID        s_core_auth   id            
s_articles_attributes       articleID     s_articles    id 

Damit kommen wir nun zumindest an alle Informationen heran, die wir theoretisch benötigen, um die Reihenfolge der Tabellen für das Löschen und das Kopieren zu bestimmen. Wir müssen die Informationen nur noch geschickt kombinieren, was wir in den folgenden Schritten erledigen werden.

Reihenfolge für das Kopieren ermitteln

Die Reihenfolge der Tabellen für das Kopieren der Daten ermitteln wir in der Funktion TabellenreihenfolgeKopieren aus Listing 2. In dieser Funktion, welche die zu verwendende Verbindungszeichenfolge als Parameter erwartet und die ein Collection-Objekt als Ergebnis zurückliefert, erstellen wir zwei Collections. Die erste heißt colOffen und speichert alle Tabellen, die noch nicht in die Reihenfolge eingearbeitet wurden. Die zweite heißt colReihenfolge und enthält die Tabellen in der ermittelten Reihenfolge.

Public Function TabellenreihenfolgeKopieren(strVerbindungszeichenfolge As String) As Collection
     Dim colOffen As Collection, colReihenfolge As Collection, bolIstDetailtabelle As Boolean
     Dim varOffen As Variant, varReihenfolge As Variant, intCountVorher As Integer
     Dim rstTabellen As ADODB.Recordset, rstBeziehungen As ADODB.Recordset, strSQL As String
     Set colOffen = New Collection
     Set colReihenfolge = New Collection
     Set rstTabellen = GetRecordset(GetConnection(strVerbindungszeichenfolge), "SHOW TABLES")
     Do While Not rstTabellen.EOF
         colOffen.Add rstTabellen.Fields(0).Value, rstTabellen.Fields(0).Value
         rstTabellen.MoveNext
     Loop
     strSQL = "SELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME " & vbCrLf _
         & "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE" & vbCrLf & "WHERE REFERENCED_TABLE_NAME IS NOT NULL;"
     Set rstBeziehungen = GetRecordset(GetConnection(cStrVerbindungszeichenfolgeAlt), strSQL)
     Do While colOffen.Count > 0
         If Not intCountVorher = colOffen.Count Then
             intCountVorher = colOffen.Count
             For Each varOffen In colOffen
                 bolIstDetailtabelle = False
                 rstBeziehungen.MoveFirst
                 Do While Not rstBeziehungen.EOF
                     If rstBeziehungen!TABLE_NAME = varOffen Then
                         bolIstDetailtabelle = True
                         For Each varReihenfolge In colReihenfolge
                             If rstBeziehungen!REFERENCED_TABLE_NAME = varReihenfolge Then
                                 bolIstDetailtabelle = False
                                 Exit For
                             End If
                         Next varReihenfolge
                     End If
                     rstBeziehungen.MoveNext
                 Loop
                 If bolIstDetailtabelle = False Then
                     colReihenfolge.Add varOffen, varOffen
                     colOffen.Remove varOffen
                 End If
                 DoEvents
             Next varOffen
         Else
             Exit Do
         End If
     Loop
     MsgBox "Anzahl offener Tabellen (da Zirkelbezug): " & colOffen.Count & vbCrLf _
         & "Die Tabellen werden an die Collection angehängt."
     For Each varOffen In colOffen
         colReihenfolge.Add varOffen, varOffen
     Next varOffen
     Set TabellenreihenfolgeKopieren = colReihenfolge
End Function

Listing 2: Ermitteln der Reihenfolge für das Kopieren von Daten

Um die Collection colOffen zu füllen, erstellen wir ein Recordset namens rstTabellen auf Basis der Abfrage SHOW TABLES. Dieses durchlaufen wir und tragen den Namen der Tabelle für jeden Datensatz in die Collection colOffen ein.

Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...

den kompletten Artikel im PDF-Format mit Beispieldatenbank

diesen und alle anderen Artikel mit dem Jahresabo

Schreibe einen Kommentar