Zum Hauptinhalt springen

Datenbank Umstellung von GUID auf Integer Schlüssel

Ziel: Alle GUID Verbindungen für Primärschlüssel und Fremdschlüssel sollen auf Integer Felder umgestellt werden.

Vorteile

  • Höhere Geschwindigkeit
  • Kleinere Datenbank
  • Der Index wird nicht mehr so sehr fragmentiert, da ein neuer Datensatz auch immer der Reihenfolge entspricht. Nur bei gelöschten finden kleinere Fragmentierungen statt.
  • Wenn wir die Zahlen die wir generieren in die System Datenbank platzieren, dann haben wir auch eindeutige Schlüssel pro Mandant, also Mandanten übergreifend.
    Somit kann auch eine Mandanten Zusammenführung gemacht werden. Außer diese Mandant kommt durch eine Firmenerweiterung zustande. In diesem Fall brauchen wir eine Indiv-Lösung.

Nachteile

  • Beim Erstellen eines neuen Schlüssels im Client muss mit dem Server kommuniziert werden. Ist aber ein kleiner Nachteil und eigentlich sehr schnell da sehr wenig Daten übertragen werden.
  • Beim Erstellen eines neuen Schlüssels in TSQL muss eine gesp. Funktion oder Sequenzen aufgerufen werden. Aber das wird jetzt auch mit newid() gemacht.
    • Achtung: Sequenzen werden erst ab SQL Server 2014 unterstützt.

Datenbank Änderungen

  • Wir brauchen eine Tabelle in welcher wir eintragen ob die Umstellung bereits gemacht worden ist. Z.B. System.CP_PK_TRANSFORMATION. Eine Spalte mit 0/1. Die Funktion cp_new_pk(‘Tabelle’) zum Ermitteln der neuen Keys greift auf diese Tabelle zu. Somit können wir unabhängig, ob umgestellt wurde oder nicht, den Quellcode anpassen. Ist umgestellt bekommen wir eine Zahl ansonsten eine GUID.
  • Diese Tabelle wird gescriptet und mit 0 ausgeliefert. Also noch noch nicht umgestellt.
  • Neue Funktion cp_new_pk(‘Tabelle’) generiert nach Umstellung eindeutige Zahlen und schreibt diese in eine eigene neue Tabelle (CP_PK_KEY), ansonsten GUIDs.
    Sie gibt einen varchar zurück was aber kein Problem darstellen sollte.
  • Es muss in jeder Tabelle die neue Spalte XX_GUID erstellt werden.
    Diese wird jedoch als varchar(36) und nicht als GUID angelegt. Der Grund ist, dass wir nicht immer GUIDs in den Primärschlüsseln haben. Später können wir jedoch die nicht GUID Werte leicht ersetzen da nicht mehr als ForeignKeys verwendet.
  • Als Default Value setzen wir eine neue Funktion cp_default_guid() ein. Diese generiert jedoch nur dann Werte, wenn bereits transformiert wurde.
    Somit lösen wir das Problem, dass wir sofort alle Insert Anweisungen umstellen müssen im Quellcode um auch die XX_GUID zu schreiben.
  • Die Spalte XX_GUID bekommt einen eindeutigen Index aber mit möglichen NULL Werten.
  • Alle SQL Prozeduren und Funktionen müssen statt newid() die neue Funktion cp_new_pk(‘Tabelle’) aufrufen.
  • In diesem Zuge würde ich auch gerne alle fehlenden Indexe bei den ForeignKeys ergänzen. Da fehlen sicher einige.

Vorgehensweise beim Transformieren

  • Für die Umstellung würde ich eine eigene Applikation empfehlen. In dieser wird die Web.Config ausgewählt damit wir die notwendigen Verbindungszeichenfolgen haben.
    Nennen wir mal die Applikation CP.DB.Transformator “CP.DBT”.
    • Der Transformationsvorgang wird wahrscheinlich sehr aufwendig sein und sollte auch in vorab von jedem kunden getestet werden können.
    • Der Output sollte transparent sein damit man auch weiß wo und wieso die Umstellung fehlgeschlagen ist.
  • Der CP.DBT hat eine XML Datei in welcher alle notwendigen Schlüssel-Transformationen definiert sind. Diese wird erstmalig aus der DB erstellt und danach von uns ergänzt. Auch über Werte zusammenhänge finden wäre möglich, weil es egal ist wie lange es dauert. Diese XML Datei ist wahrscheinlich auch in eine Reihenfolge zu bringen.
    • PK=Company.NET_PERSON.PE_ID
    • FK=Company.NET_MANGEL.PE_ID
    • FK=Company.NET_PER_EINHEIT_ZU.PE_ID
    • FK=System.NET_POSTEINGANG_ZUORDNUNGEN.PE_ID
    • usw.
  • Die App kopiert als erstes die Werte aus XX_ID in die Spalte XX_GUID. Damit ist der alte PK gesichert worden.
  • Dann löst die App den PK, FKs und Indexe auf.
    Davor wird jedoch die Konstellation gesichert.
  • Dann wird auf die aktuelle Tabelle laut der gesp. Funktion/Sequenz ein neuer PK vergeben.
  • Dann wird mit der Hilfe der Spalte XX_GUID dieser neue generierte PK auch auf die Fremdschlüssel übertragen.
  • Danach wird der PK, die Fremdschlüssel und auch die Indexe neu angelegt.
    Diese werden aus der vorherigen Sicherung übernommen.
  • Achtung: Wir müssen die Fälle ermitteln wo wir ID Abhängigkeiten über mehrere Tabellen haben. Also nicht Fremdschlüssel sondern eigene Kreationen.
    • Im DMS gibt es abhängige IDs durch mehrere Tabellen. Z.B. Container und Container History. Muss für die gleich GUID der gleiche Int generiert werden?
    • Bei Personen über mehrere Mandanten, haben wir da die gleiche ID, oder ist das die Sync-ID?
    • History Einträge müssen migriert werden.
    • Sonstiges?

Code Änderungen in Delphi/C#

  • Überall wo im Code newid() verwendet wird, muss auf die Funktion cp_new_pk(‘Tabelle’) umgestellt werden. Diese kommuniziert dann mit dem Server und holt sich über die Datenbank Sequenz einen neuen Wert. Somit kann diese auch vor dem Speichern abgeholt werden. Diesen Nachteil hätte die identity vom SQL Server. Dieser Wert ist natürlich abhängig von Einstellung in System.CP_PK_TRANSFORMATION.
  • Überall wo im SQL newid() verwendet wird, muss auf die Datenbank Funktion cp_new_pk(‘Tabelle’) umgestellt werden. Diese holt sich über die Datenbank Sequenz einen neuen Wert. Dieser Wert ist natürlich abhängig von Einstellung in System.CP_PK_TRANSFORMATION.
  • Die Verwendung von virtuellen bzw. temporären Tabellen muss im ersten Schritt nicht umgestellt werden, auch wenn schon transformiert wurde. Eine generierte Zahl sollte ohne Probleme in ein varchar(36) Feld geschrieben werden können.
  • Falls Objekte mit Eigenschaften verwendet worden sind für die XX_ID sollten diese auch nicht umgestellt werden müssen, auch diese nehmen eine Zahl ohne Probleme auf.
  • TRPCTable sollte uns auch keine Probleme machen. Diese wird zwar nach der Transformation mit Integer dimensioniert jedoch kann diese ohne Probleme mit asString bzw. Value gelesen werden.
  • Überall wo ein Link generiert wird muss im Code auf XX_GUID umgestellt werden. Wir sollten aus Sicherheitsgründen dann nicht mehr die Spalte XX_ID verwenden.
    Dies müssen wir jedoch abhängig vom Wert in der Tabelle System.CP_PK_TRANSFORMATION durchführen. Ist transformiert verwenden wir XX_GUID ansonsten immer noch XX_ID.
  • Beim Verarbeiten der Links müssen wir auch auf den Wert CP_PK_TRANSFORMATION abfragen. Je nachdem suchen wir in XX_ID oder XX_GUID.
  • Die Eigenschaft XX_ID der Modelle DARF NOCH NICHT auf int umgestellt werden.
    Erst wenn ALLE Kunden die Datenbank Transformation gemacht haben kann langsam mit der Umstellung angefangen werden. Ansonsten sind wir nicht mehr kompatibel mit den Kunden die noch nicht umgestellt wurden. Sollte aber kein Problem sein, da eine String-Eigenschaft mit einem Zahlenwert trotzdem geschrieben und gelesen werden kann.

Code Änderungen in d+ OSC

  • Hier muss die Umstellung automatisch erfolgen beim ersten Zugriff der Sync.
    Das geschieht dann über einen Parameter den der Client sendet. Dieser wird aus der Tabelle System.CP_ID_TRANSFORAMTION ausgelesen.
  • Es muss in jeder Tabelle die neue Spalte XX_GUID erstellt werden.
    Diese wird jedoch als varchar(36) und nicht als GUID angelegt. Der Grund ist, dass wir nicht immer GUIDs in den Primärschlüsseln haben. Später können wir jedoch die nicht GUID Werte leicht ersetzen da nicht mehr als ForeignKeys verwendet.
  • Als Default Value setzen wir eine neue Funktion cp_default_guid() ein. Diese generiert jedoch nur dann Werte, wenn bereits transformiert wurde.
    Somit lösen wir das Problem, dass wir sofort alle Insert Anweisungen umstellen müssen im Quellcode um auch die XX_GUID zu schreiben.
  • Die Spalte XX_GUID bekommt einen eindeutigen Index aber mit möglichen NULL Werten.
  • Alle SQL Prozeduren und Funktionen müssen statt newid() die neue Funktion cp_new_pk(‘Tabelle’) aufrufen.
  • Alle Links die generiert werden, müssen im Code auf XX_GUID umgestellt werden. Wir sollten aus Sicherheitsgründen dann nicht mehr die Spalte XX_ID verwenden.
    Dies müssen wir jedoch abhängig vom Wert in der Tabelle System.CP_PK_TRANSFORMATION durchführen. Ist transformiert verwenden wir XX_GUID ansonsten immer noch XX_ID.
  • Alle REST Urls müssen in der Datenbank den Schlüssel je nach CP_PK_TRANSFORMATION entweder in XX_ID oder XX_GUID suchen.
    ODER: Wir schreiben die XX_ID in die XX_GUID beim Insert wenn noch nicht umgestellt.
    • Trigger?
    • Default Value, wüsste aber nicht wie
  • Abhängig von CP_PK_TRANSFORMATION muss auch die Sync auf XX_GUID umgestellt werden.
  • Cache-Db könnte in Prinzip gleich bleiben. Es sollte kein Problem sein Zahlen in varchar(36) Felder zu schreiben.
  • Angular Modelle könnten auch bleiben. Auch hier sollte es kein Problem sein Zahlen in varchar(36) Felder zu schreiben.
  • Die Eigenschaft XX_ID der Modelle DARF NOCH NICHT auf int umgestellt werden.
    Erst wenn ALLE Kunden die Datenbank Transformation gemacht haben kann langsam mit der Umstellung angefangen werden. Ansonsten sind wir nicht mehr kompatibel mit den Kunden die noch nicht umgestellt wurden. Sollte aber kein Problem sein, da eine String-Eigenschaft mit einem Zahlenwert trotzdem geschrieben und gelesen werden kann.

Risikobereiche

  • Fremdart und Fremdid im Ticketing muss geprüft werden, falls diese nicht mit der Fremdart kombiniert wurde.
  • Im COLD könnte direkt eine GUID angegeben werden, deshalb muss der COLD ggf. um die suche über CON_GUID erweitert werden.
  • Die gespeicherten Serienbrief/Report Datenmengen müssen ggf. auch ausgetauscht werden. Hier sind sonst die GUIDs drinnen und wir würden diese nicht mehr finden.
  • Bei den Benutzereinstellungen sollten wir einen Bigint verwenden, da hier laut BAF schon mal ein Überlauf war.

Erstellung der IDs

  • Leider lässt sich eine ID nicht über gesp. Funktionen erzeugen. Diese erlaubt es nicht eine Tabelle upzudaten. Hier muss im TSQL mit einer gesp. Prozedur gearbeitet werden. Diese kann jedoch nicht direkt in eine Insert Anweisung eingebettet werden sondern muss über eine Variable zugewiesen werden.
    Das hat den Nachteil, dass zwei Inserts hintereinander einen Fehler wegen der Variable werfen, ist nicht optimal.
  • Die Sequenzen werden erst ab SQL Server 2012 unterstützt. Ich würde die Umstellung mit der Bedingung machen, dass wir erst ab 2012 umsteigen auf Int-IDs.