Diskussion IBExpert, Firebird 2.5 und UUID als PK

[Kostas]

Diskussion IBExpert, Firebird 2.5 und UUID als PK
Hallo zusammen,

das Problem vorab: IBExpert zeigt den Inhalt des UUID Feldes nicht an.

ich plane demnächst eine Anwendung die für mehrere Filialen gedacht ist.
Alle Filialen gleichen ihre Daten mit einer zentralen Datenbank ab.
Dabei unterscheiden sich die Datenbankstrukturen zwischen Filiale und Zentrale.
Somit muss ich wohl oder übel auf UUIDs für die PK Felder gehen. Auf dem ersten Blick könnte man meinen, eine zusammengesetzter PK
aus [FilialeID]+[Generator] würde reichen. Doch das tut es nicht da es vorkommen kann das ein Aussendienstler
eine Version kopiert und einem Kollegen zur Verfügung stellt. Dann hätte ich intern die gleiche FilialeID.

Also bleibt nur eine GUID bzw. UUID.

Firebird 2.5 kann über GEN_UUID eine UUID im Trigger anlegen. Schon mal nicht schlecht dachte ich,
also gleich mal eine TestDB als UTF8 angelegt mit zwei Tabellen. Die eine mit Int als PK
und die andere mit char(16) octets als PK.
Danach die Tabellen mit IBExpert Testdatengenerator 1 Mio. Datensätze anlegen lassen.

Code:

CREATE DOMAIN S_UUID AS
CHAR(16) CHARACTER SET OCTETS
COLLATE OCTETS;



CREATE TABLE TESTTBL (
    UUIDPK  S_UUID NOT NULL /* S_UUID = CHAR(16) */,
    NAME    VARCHAR(50)
);

ALTER TABLE TESTTBL ADD CONSTRAINT PK_TESTTBL PRIMARY KEY (UUIDPK);

SET TERM ^ ;
CREATE OR ALTER TRIGGER TESTTBL_BI0 FOR TESTTBL
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (new.uuidpk is null) then
    new.uuidpk = gen_uuid();
end
^


SET TERM ; ^

Die Int.PK Tabelle ist fertig in 7min.
Die UUID.PK Tabelle ist fertig in 20min.

OK, ein deutlicher unterschied aber irgendein Tod muss man sterben.
Meine Datenbank wird mehrere GB groß sein und mehrere Mio. Datensätze beinhalten, das ist bereits klar,
aber es werden nicht viele Datensätzen auf einmal in die DB gepumpt. Deshalb kann ich den Performance-Verlust beim
massen inserts schon verkraften. Die eigentliche Performance beim Select, Update darf jedoch nicht leiden. Das ist wichtig!


Während der Entwicklungsphase werde ich sehr oft mit IBExpert die Datensätze suchen müssen. Da beginnt schon das erste
Problem. Die DB ist in UTF8 angelegt und das UUID Feld ist ein char(16) character set octets somit sehe ich die UUID nicht. War es nicht angezeigt wird, ist schon klar. Aber ich brauche eine Möglichkeit während der Entwicklungsphase, Wenn das Programm fertig ist, ist es natürlich egal.
Möglicherweise gibt es eine Einstellung dafür. Ich habe keine Erfahrung mit UUIDs was könnt ihr darüber berichten. Was für
Probleme werden noch auf mich zukommen wenn ich nicht mit Int als PK arbeiten kann sondern mit UUIDs?

[Edit]
Über ein Select kann ich sicherlich die UUID anzeigen, möglicherweise gibt es eine Einstellung in IBExpert das der selbst erledgt.

Code:

select first 10 UUID_TO_CHAR(uuidpk), Name from testtbl

Gruß Kostas

[hoika]

Hallo,

da musst du einfach bei IBExpert mal nachfragen.

http://tracker.firebirdsql.org/brows...story-tabpanel

Der hat das gleiche Problem.


Heiko

[Kostas]

Ah, sehr wichtiger Hinweis wie ich finde,

in einem Shop wird es schwierig die PK in der URL zu verwenden. Es muss also immer eine Konvertierung durchgeführt werden
mittels UUID_TO_CHAR(PKField) bzw. CHAR_TO_UUID(UUID) das ist wirklich ein signifikanter Nachteil. Um das zu umgehen müsste man
ein berechnetes Feld haben welches die UUID konvertiert ausgibt.

Gruß Kostas

[IBExpert]

Wir haben gerade eine größeres Replikationsprojekt in Arbeit, mit momentan 35 synchronen Firebird Servern and diversen Standorten,
am Ende werden das ca. 100 sein.

Wir haben aus diversen Gründen dabei auf den Bigint gesetzt, der hat zwar Nachteile (Offset je Replikationspartner muss irgendwo
zentral koordiniert werden), aber auch diverse Vorteile, zum Beispiel direkte Lesbarkeit, halber Speicherplatz, etc.

Was für unsere Implementation aber noch viel wichtiger ist, ist die Fähigkeit, eine Reihenfolge darzustellen, was insbesondere
bei asynchroner Replikation wichtig ist.

Dafür reicht die Genauigkeit von Timestamps oft nicht aus und den bräuchtest du noch zusätzlich, um bei der Datenübergabe zu
wissen, welcher Datensatz als Master für welchen anderen Datensatz als Detail schon exitieren sollte, damit dir die die fks
nicht um die Ohren fliegen. Die Reihenfolge in der Datenbank helfen die dabei nicht weiter.

Bezgl. direkter lesbarer Darstellung in IBExpert war bisher noch niemand an uns mit dem Wunsch herangetreten, ist aber sicherlich
in einer der nächsten Versionen machbar. Als Workaround könntest du für deine Tests auf updatable views arbeiten, die mit den
beiden Funktionen die Umwandlung beim Lesen/Schreiben übernehmen.

[sx2008]

UUIDs haben den Nachteil dass sie quasi zufällig verteilt sind.
Da aber Datensätze häufig in der Reihenfolge ihres Primärschlüssels eingefügt werden,
hat dies zur Folge dass neu eingefügte Datensätze über die gesamte Tabelle verstreut werden.
Es gibt aber auch UUIDs die pro Rechner sequenziell aufsteigend bleiben.

Manche Datenbankserver bieten dazu SQL-Funktionen an:
http://msdn.microsoft.com/de-de/library/ms189786.aspx

Ansonsten muss man sequenzielle UUIDs selbst erzeugen (lassen):
http://www.delphipraxis.net/152385-s...-erzeugen.html

Man darf aber normale UUIDs und sequenzielle UUIDs nicht mischen, da es theoretisch zu Kollisionen
kommen kann (auch wenn die Wahrscheinlichkeit sehr gering ist).

Delphi-Quellcode:

var
   guid: TGUID;
begin
   CreateSequentialGuid(guid);
   ReverseBlock(PAnsichar(@guid), sizeof(guid));  // optional

Evtl. ist es notwendig die Reihenfolge der 16 Byte umzudrehen.
(Insbesondere wenn man die UUIDs mit Base32 oder Base64 codieren möchte)
Dazu dient diese Prozedur:

Delphi-Quellcode:

// Reihenfolge der Bytes umdrehen
procedure ReverseBlock(buf:PAnsiChar; size:integer);
   procedure SwapBytes(a,b:PAnsiChar);
   var c: Char;
   begin
      c := a^;
      a^ := b^;
      b^ := c;
   end;
var
   i : Integer;
begin
   for i := 0 to size div 2 do
   begin
      SwapBytes(buf + i, buf + size -1 -i);
   end;
end;

[Kostas]

Hallo Holger,

wenn ich dich richtig verstanden habe, verwendest du ein BigInt als PK und reservierst ein gewissen Range für jede NL.
Dabei sind deine NL-IDs wahrscheinlich fix und werden sich nicht ändern. In meinem Fall darf ich nicht davon ausgehen.
Mein Client läuft komplett ohne Installation einfach alles auf ein Stick kopieren und direkt vom Stick laufen lassen.
Diese Freiheit hat zur Folge, das Anwender den Stick einfach kopieren und laufen lassen. Somit habe ich die gleiche
NL-ID ein zweites mal.

Es ist so, wenn das Projekt abgeschlossen ist, stört mich die fehlende Lesbarkeit der UUIDs eher weniger. Während der
Entwicklungsphase ist es halt sehr störend.

Der Hinweis von SX2008 ist hier sehr wichtig. Das würde bedeuten dass die Anwendung insgesamt träger sein wird bei normalen
Operationen wie Select, Update und Delete. Das ist natürlich sehr sehr schlecht und spricht doch sehr gegen UUIDs.
Wenn sequentielle UUIDs genau so eindeutig sind wie die aktuelle Version der UUIDs stellt sich die Frage warum gibt es
die Funktion in Firebird nicht? Sicherlich könnte man dafür eine UDF schreiben aber die wird deutlich langsamer sein als
eine interne Funktion.

Um nicht gleich die Flinke ins Korn zu werfen, müsste man in Sache Performance mit den derzeitigen UUIDs mehr Erfahrungsberichte haben.

Auf die Gefahr hin das ich jetzt gesteinigt werde bringe ich einen weitere Vorschlag der zwar zu meinen Konzept nicht passt aber
bezüglich Filialenfähigkeit:
-Wenn die Filialen(FL) eindeutig sind und man sicherstellen kann dass sie nicht "kopiert" werden wie bei mir, könnte man
den PK auch als Double anlegen. die Nachkommstelle ist die FL-ID darüber habe ich schon lange nachgedacht aber mich nie getraut
das mal auszuprobieren.

Für meinen Fall habe ich gerade eine Blitz-Idee.
-Ich designe alle PKs als normale integer.
-Die Filialen(FL) werden nicht unterschieden!
-Weiterhin kann eine Installation kopiert werden.
-Die Datenbankstruktur unterscheidet sich zwischen FL und der zentralen Datenbank.

Dabei haben ich ja das Problem dass beim Abgleich die IDs gleich sein können.
So jetzt zur Lösung:
-Wenn eine Filiale einen neuen Datensatz anleget, wird die ID ins minus angelegt.
-Die zentrale Datenbank macht das nicht. Sie macht positive IDs.
-Wenn der Client die Daten an den Server sende will, muss er zwangsläufig sich mit meinem Kommunikationsserver verbinden
der für den Datenabgleich zuständig ist. Der Client geht alle Tabellen in der RICHTIGEN Reihenfolge durch und fordert
für jeden neuen Datensatz innerhalb dieser Tabelle eine neue ID von der zentralen DB an. Der Client überschreibt seine negative ID durch die positive ID
von der zentralen DB. Das Update Cascade in der ClientDB führ dazu dass alle FKs aktualisiert werden.
-Jetzt können die Daten im zweiten Stepp problemlos übertragen werden denn sie haben eindeutige IDs von der zentralen DB.

-Somit ist es egal wie viele FLs existieren und sogar egal auch wenn die DB kopiert wird.
-maximale Performance wegen integer oder bigint.


[Edit]
-Keine UUID oder GUID notwendig (sind langsam da sie nicht sequenziell angelegt werden innerhalb einer Instanz)
-Kein Konflikt mehr bei den PKs
-Ich weiß ob der Client neue Daten zum Senden hat, eben wenn in irgend einer Tabelle ein negativer Wert als PK
steht.


Was noch offen bleibt ist, wie Änderungen von vorhandenen Datensätzen abgleichen.
Ein Update-Konflikt-Management bleibt nicht aus. Dafür werden Regeln gebildet die
bei Konflikt vorschreiben was zu tun ist.
Um das zu vereinfachen:
-Tabellen werden normalisiert und dabei auf die Regeln geachtet.
-Wenn ein Feld vom Client und vom Server geändert wurde, muss die Regel sagen wer Recht hat.
-Dafür muss eine Tabelle unter Umständen in so vielen einzelnen Tabellen zerlegt werden mit Referenz 1:1
so das die Regel für alle Felder gilt. D.h. z.B.: die Tabelle Adress wird zerlegt in Tabelle AdressC
und AdressS.
-Wenn irgend ein Feld aus AdressC von Client und Server geändert wird, hat der Client Recht.
-Wenn irgend ein Feld aus AdressS von Client und Server geändert wird, hat der Server Recht.
-Theoretisch könnte es eine AdressU geben bei einem Konflikt User fragen.

Somit ist auch das Update sehr leicht nachvollziehbar.

[/Edit]

Das hört sich doch ziemlich gut an und ist sehr leicht umsetzbar.


Gruß Kostas

[IBExpert]

bezgl. Lauffähigkeit ohne Initialisierung: Ich hab die gleiche technische Lösung schon für eine Versicherungssoftware mit 8500 unanhängigen Maklern gemacht, dort fing jede datenbank auf einem defaultwert (zentraler generator für alle Tabellen steht auf 1 Mrd) an. Erst beim ersten synchronisieren bekam der den Offset (f1= 10 Mrd, f1= 20 Mrd, f3= 30 Mrd, usw.) und eine sp in der DB hat alle seine pk und fk und den zentralen generator um den Offset erhöht, bevor die daten an die Zentrale gesendet wurden. Ganz nebenbei kann man dann für alle gesammelten Daten durch den Bereich, in dem sich die PKs befinden, sogar erkennen, wo die Daten entstanden sind.

[Dejan Vu]

Ich finde die Lösung mit BigInt auch sehr charmant, auch wenn GUID vielerprobt und funktionsfähig.

Bezüglich der Performance würde ich keine Nachteile erwarten, außer bei den genannten Massenupdates, die Daten werden ja doch sequentiell eingetragen und mit einem Index ist das doch immer flott genug.

Und wenn man es nicht glaubt: Ausprobieren!

[Kostas]

Hallo,

wenn ich es richtig verstanden habe, setzt es voraus dass die Filiale eindeutig ist wegen dem Offset.
In meinem Fall ist das ein Problem. Natürlich wird es eine Vorgaben geben vom Konzern dass die
Version nicht kopieren werden darf um Konflikte zu meiden. Doch wenn sich einer nicht daran hält,
wird es eben zu Konflikten kommen. Der Anwender kann die Version vom Stick auf einen anderen Datenträger kopieren
und laufen lassen. Solange er das selbst macht, und immer nur die gleiche Installation nutzt ist alles in Ordnung.
Ich kann jedoch nicht verhindern dass die Installation sagen wir mal nach fünf Jahren einen Kollegen übergeben wird
wegen Urlaubsvertretung. Nach dem Urlaub macht er einfach weiter. Schon sind zwei parallel laufende System im Umlauf
die das gleiche Offset haben würden. Wie gesagt, das ist nur in meinem Fall problematisch.
Aber ich bin Froh jetzt gleich zwei Varianten zu haben die für Filialenbetrieb geeignet sind.

Gruß Kostas

@Holger, die UUID würde ich für dieses Projekt nicht benutzen aber grundsätzlich wäre das eine sehr nützliche Funktion
dass UUID Felder vom Typ Octets automatisch konvertiert werden.

[IBExpert]

Zitat von Kostas:

Hallo,
wenn ich es richtig verstanden habe, setzt es voraus dass die Filiale eindeutig ist wegen dem Offset.
In meinem Fall ist das ein Problem. Natürlich wird es eine Vorgaben geben vom Konzern dass die
Version nicht kopieren werden darf um Konflikte zu meiden.

Ich hab so ein wenig Schweirigkeiten zu verstehen, was du mit den Bgriffen Filiale und Version
assoziierst.

Wenn ein Datensatz x auf der db 1 entstanden ist, bekommt der z.B. die id 1.000.000.000.1 und behält
die auch bis in alle Ewigkeit auf allen Datenbankinstanzen, in denen die Daten hineinkopiert werden.

Die Problematik, das jemand die db kopiert und dann von 2 zwei unterschiedlichen Rechnern weiter benutzt,
ist durchaus nicht unkritisch und ob du es glaubst oder nicht, genau so bei uns im Projekt mit den Maklern
genau so aufgetaucht.

Lösung: Du musst in der DB irgendeine hardwarespezifische Information hinterlegen, an der deine
Software erkennt, das es sich um eine Kopie handelt und damit der Offset ungültig ist und ab dem
Zeitpunkt dann zum Beispiel wieder im Defaultbereich neue Datensätze erfasst werden und die dann
wieder bei einer Synchronisation um einen neue Offset angehoben werden. Wenn das USB Sticks sind,
dann reicht normalerweise schon die Hardwareseriennummer des USB Sticks oder ggf sogar die
Partitionsnummer.

Das gibt zwar keine Garantie der Eindeutigkeit, ist aber auch nicht so wichtig, weil man ja nur den
Wechsel erkennen will.

Zitat von Kostas:

@Holger, die UUID würde ich für dieses Projekt nicht benutzen
aber grundsätzlich wäre das eine sehr nützliche Funktion dass UUID Felder vom Typ
Octets automatisch konvertiert werden.

wir arbeiten dran