Firebird Generator/Trigger für Autoinc (Verständnis_Problem)

[Satty67]

Hallo,

ich beschäftige mich erst seit einigen Tagen richtig mit SQL-Datenbanken (FireBird via Zeos). Gerade bei Funktionen, die "innerhalb" der Datenbank ablaufen, hapert es bei mir noch arg mit Wissen und Verständnis.

Ich hab' mir jetzt eine Funktion gebaut (via DP-Tutorial), die für eine Tabelle in einer FireBird-Datenbank ein AutoIncrement-Feld definiert. Das funktioniert auch soweit gut:

Delphi-Quellcode:

procedure FireBirdAutoInc(const zConnection: TZConnection; Table, forField : String);
var
  zQuery : TZQuery;
  Column : Integer;
begin
  { TODO : Spalten-Nummer anhand Feldnahme ermitteln, statt der statischen 0 }
  Column := 0;

  zQuery := TZQuery.Create(Application);
  try
    with zQuery do begin

      // Verbindung festlegen und prüfen
      Connection := zConnection;
      if Assigned(Connection) and Connection.Connected then begin

        // Generator erzeugen
        SQL.Text := 'CREATE GENERATOR SetID_AUTOINC;';
        ExecSQL;
        // Generator mit Spalte verbinden
        SQL.Text := 'SET GENERATOR SetID_AUTOINC TO '+IntToStr(Column);
        ExecSQL;
        // Trigger definieren
        SQL.Clear;
        SQL.Add('CREATE TRIGGER AUTOINC_TRG for '+Table);
        SQL.Add('active before insert position '+IntToStr(Column));
        SQL.Add('as');
        SQL.Add('begin');
        SQL.Add('  new.'+forField+' = gen_id( SetID_AUTOINC, 1 );');
        SQL.Add('end');
        ExecSQL;
      end;
    end;

  finally
    zQuery.Free;
  end;
end;

Bei meinem nächsten Übungsprojekt sollen jetzt mehrere Tabellen einer Datenbank ein Autoincrement-Feld bekommen. Da sind mir jetzt ein paar Fragen gekommen, mangels Wissen zu Generator/Trigger. Das Tutorial ist darauf, soweit ich es nicht übersehen habe, auch nicht eingegangen:

"Generator" scheint Global zu sein, also nicht direkt an die Tabelle gebunden, andererseits aber an eine Spaltennummer.

• Wann muss ich einen Generator setzen? Für jedes Autoincrement-Feld jeder Tabelle oder nur einmal, wenn alle Autoincrement-Felder in den verschiedenen Tabellen den gleichen Namen und/oder Spalte haben?
• Wenn nur einmal, wie kann ich ermitteln ob er schon existiert? Ist Abfrage der Systemtabelle RDB$GENERATORS dafür richtig?
• Der Trigger nimmt direkt Bezug auf eine Spalte einer bestimmten Tabelle und muss für jedes einzelne Autoincrement-Feld individuell gesetzt werden?

Hoffe die Antworten sind nicht allzu offensichtlich schon irgendwo hier bereit gelegen, gefunden hab' ich die bisher nicht. :wink: Dokumentationen zu Firebird gibt es massig, sind aber arg zerstückelt. Auch stark auf bereits erfahrene Firebird-Anwender zugeschnitten. Vielleicht hab' ich bisher aber auch nur noch nicht das richtige gefunden.

[mkinzler]

Zitat:

"Generator" scheint Global zu sein, also nicht direkt an die Tabelle gebunden, andererseits aber an eine Spaltennummer.

Ein Generator ist ein tabellenunabhängiger Wert (Variable) welche semaphorengestützt erhöht werden kann. Im SQL Standard nennt man das auch eine Sequenz.

Zitat:

* Wann muss ich einen Generator setzen? Für jedes Autoincrement-Feld jeder Tabelle oder nur einmal, wenn alle Autoincrement-Felder in den verschiedenen Tabellen den gleichen Namen und/oder Spalte haben?

Kommt darauf an ob alle Tabellen/spalten einen Nummernkreis haben sollen oder jede einen eigenen.

Zitat:

* Wenn nur einmal, wie kann ich ermitteln ob er schon existiert? Ist Abfrage der Systemtabelle RDB$GENERATORS dafür richtig?

Ja

Zitat:

* Der Trigger nimmt direkt Bezug auf eine Spalte einer bestimmten Tabelle und muss für jedes einzelne Autoincrement-Feld individuell gesetzt werden?

Ein Trigger ist einfach ein Stück Code der bei einem bestimmten Ereignis aufgerufen ( getriggert) wird.

[Satty67]

Dann ist meine Sequenz (Generator) SetID_AUTOINC eine globale Variable, die mit dem Code in Trigger hochgezählt und in das angegebene Feld eingefügt wird?

[mkinzler]

Ja so könnte man es beschreiben

[Satty67]

Danke, das hat mich doch einen großen Schritt weitergebracht. Zumindest der Zusammenhang ist jetzt wesentlich klarer :-D

Da meine Tabellen einen eigenen Nummernkreis bekommen sollen, bekommt Generator je einen Namen, der den Tabellennamen mit drin hat: Tabelle_1_AutoInc, Tabelle_2_AutoInc etc.

[Satty67]

Hier noch meine angepasste Funktion aus meinem Übungsprojekt. (fehlt noch etwas Sicherheit, aber denke erstmal sicher genug)

Legt für die angegebene Tabelle einer Firebird-Datenbank einen individuellen Generator (Sequenz) an. Wird dann von einer Trigger-Funktion als AutoIncrement für das angegebene Feld verwendet. (Hoffe soweit richtig formuliert)

Das ganze wird nach 'CREATE TABLE...' aufgerufen und nimmt an, das pro Tabelle nur ein Autoincremet-Feld nötig ist.

Delphi-Quellcode:

procedure FireBirdAutoInc(const zConnection: TZConnection; Table, forField : String);
var
  zQuery : TZQuery;
  Column : Integer;
begin
{ TODO : 
Prüfen, ob bereits eine Sequenz/Generator mit ensprechendem Namen existiert
Prüfen, ob erfolgreich }

  zQuery := TZQuery.Create(Application);
  try
    with zQuery do
    begin

      // Verbindung festlegen und prüfen
      Connection := zConnection;
      if Assigned(Connection) and Connection.Connected then
      begin

        // Generator erzeugen
        SQL.Text := 'CREATE GENERATOR '+Table+'_AUTOINC;';
        ExecSQL;
        // Generator mit Spalte verbinden
        SQL.Text := 'SET GENERATOR '+Table+'_AUTOINC TO 0';
        ExecSQL;
        // Trigger definieren
        SQL.Clear;
        SQL.Add('CREATE TRIGGER AUTOINC_TRG for '+Table);
        SQL.Add('active before insert position 0';
        SQL.Add('as');
        SQL.Add('begin');
        SQL.Add('  new.'+forField+' = gen_id( '+Table+'_AUTOINC, 1 );');
        SQL.Add('end');
        ExecSQL;

      end;  // if Assigned...
    end;  // with zQuery do

  finally
    zQuery.Free;
  end;
end;

Etwas unsicher bin ich nur noch, ob "zQuery.Connection.GetColumnNames" auch immer die richtige Reihenfolge der Spalten übergibt. Also ob die ermittelte "Column" sicher ist.

[mkinzler]

Position ist aber nicht die Spaltennummer sondern regelt nur die Reihenfolge des Aufrufs der Trigger, wenn mehrere Trigger für das selbe Ereignis vorhanden sind. Du kannst dir also die Ermittlung der Spaltenposition sparen.

[Satty67]

Ok, ich scheide es oben wieder raus.

Ich merke schon, für eine größere SQL-Datenbank muss ich mehr lernen als erwartet. Zu Sequenzen, Transaktion und Funktionen lerne ich am besten erstmal mehr mit einem guten Buch.

[Mithrandir]

(Nachtrag: Datenbank: Firebird Embedded, Version: 2.1.2, Zugriff über: ZEOS 6.6.4 )

Hi,

Ich wollte deine Funktion gerne verwenden, und habe mir daher folgendes Konstrukt gestrickt:

Delphi-Quellcode:

  DoQuery('CREATE TABLE MEMBERS (ID BIGINT NOT NULL, MEMBER_ID  BIGINT,"TYPE" VARCHAR(255), REF BIGINT,"ROLE" VARCHAR(255));');
  DoQuery('CREATE TABLE NODES (ID BIGINT NOT NULL, NODE_ID BIGINT, LAT FLOAT, LON FLOAT, TAGS VARCHAR(20000));');
  DoQuery('CREATE TABLE RELATIONS (ID BIGINT NOT NULL, RELATION_ID BIGINT, MEMBERS VARCHAR(5000), TAGS VARCHAR(5000));');
  DoQuery('CREATE TABLE TAGS (ID BIGINT NOT NULL, "KEY" VARCHAR(255), "VALUE" VARCHAR(255));');
  DoQuery('CREATE TABLE WAYS (ID BIGINT NOT NULL, WAY_ID BIGINT, NODES VARCHAR(5000), TAGS VARCHAR(5000));');

  FireBirdAutoInc(fConnection,'NODES','ID');
  FireBirdAutoInc(fConnection,'TAGS','ID');

DoQuery ist einfach nur eine Funktion, die ein Query ausführt. Wenn ich das so mache, wie geschrieben, kommt diese Fehlermeldung:

Code:

---------------------------
Benachrichtigung über Debugger-Exception
---------------------------
Im Projekt saxxmltest.exe ist eine Exception der Klasse EZSQLException mit der Meldung 'SQL Error:  unsuccessful metadata update DEFINE TRIGGER failed attempt to store duplicate value (visible to active transactions) in unique index "RDB$INDEX_8". Error Code: -607. This operation is not defined for system tables. The SQL: CREATE TRIGGER AUTOINC_TRG for TAGS
active before insert position 0
as
begin
  new.ID = gen_id( TAGS_AUTOINC, 1 );
end
; ' aufgetreten.
---------------------------
Anhalten   Fortsetzen   Hilfe   
---------------------------

Was läuft da falsch? Liegt das an den identischen Spaltennamen? Eigentlich dürfte das doch kein Problem darstellen, oder? Wobei ich auch ehrlich zugeben muss, dass ich das mit den Generatoren und Triggern noch nicht komplett durchgeblickt habe... :stupid:

[mkinzler]

Wegen fehlendem Commit sind die Tabellen bei der Anlage der Trigger noch nicht sichtbar

[Mithrandir]

Zitat:

Zitat von mkinzler

Wegen fehlendem Commit sind die Tabellen bei der Anlage der Trigger noch nicht sichtbar

Ähä.. Commit? Also das, was die ZeosLib unter ExecSQL versteht, oder? Meine DoQuery-Funktion liest sich so:

Delphi-Quellcode:

procedure TORPDataBase.DoQuery(QueryStr: String);
begin
  fQuery := TZQuery.Create(nil);
  try
    with fQuery do
    begin
      Connection := fConnection;
      SQL.Add(QueryStr);
      ExecSQL;
    end;
  finally
    fQuery.Free;
  end;
end;

Der erste Aufruf von FireBirdAutoInc funktioniert und auch später in der DB wird die Spalte "ID" artig inkrementiert, wenn das INSERT INTO Statement so aussieht:

SQL-Code:

INSERT INTO NODES (NODE_ID, LAT, LON, TAGS) VALUES (:node_id, :lat, :lon, :tags) RETURNING "ID"

Nur beim zweiten Aufruf scheitert es leider...

[mkinzler]

FireBird arbeitet mit Transaktion. Tabellen/Trigger sind Einträge in Systemtabellen, welche erst nach Commit der Transkation sichtbar werden.

[Mithrandir]

*Grübeldigrübel*

Das heißt, vermutlich wäre hier ein fConnection.Connect mit anschließendem fConnection.Disconnect vor dem FireBirdAutoInc-Block angebracht, oder? Wenn dem so ist, dann habe ich immer noch ein Problem. Beim Programmstart prüfe ich, ob die Datenbank bereits existiert. Wenn nicht, soll sie angelegt werden.

Delphi-Quellcode:

function TORPDatabase.Connect(DBUser, DBPassword, DBHostName: string): boolean;
begin
  with fConnection do
  begin
    Protocol  := 'firebird-2.0';
    ReadOnly  := false;
    User      := DBUser;
    Password  := Password;
    Database  := fCompleteDBPath;
    HostName  := DBHostName;
    if not FileExists(fCompleteDBPath) then
      CreateEmptyDB;
    Connect;
    Result := Connected;
  end;
end;

procedure TORPDataBase.CreateEmptyDB;
begin
  with fConnection do
  begin
    Properties.Add('CreateNewDatabase=CREATE DATABASE ' + QuotedStr(Database)
      + ' USER ' + QuotedStr(User) + ' PASSWORD ' +
      QuotedStr(Password) + ' PAGE_SIZE 4096');
  end;
  FillDB;
end;

procedure TORPDataBase.FillDB;
begin
  DoQuery('CREATE TABLE MEMBERS (ID BIGINT NOT NULL, MEMBER_ID  BIGINT,"TYPE" VARCHAR(255), REF BIGINT,"ROLE" VARCHAR(255));');
  DoQuery('CREATE TABLE NODES (ID BIGINT NOT NULL, NODE_ID BIGINT, LAT FLOAT, LON FLOAT, TAGS VARCHAR(20000));');
  DoQuery('CREATE TABLE RELATIONS (ID BIGINT NOT NULL, RELATION_ID BIGINT, MEMBERS VARCHAR(5000), TAGS VARCHAR(5000));');
  DoQuery('CREATE TABLE TAGS (ID BIGINT NOT NULL, "KEY" VARCHAR(255), "VALUE" VARCHAR(255));');
  DoQuery('CREATE TABLE WAYS (ID BIGINT NOT NULL, WAY_ID BIGINT, NODES VARCHAR(5000), TAGS VARCHAR(5000));');

  fConnection.Connect;
  fConnection.Disconnect;


  FireBirdAutoInc(fConnection,'NODES','ID');
  FireBirdAutoInc(fConnection,'TAGS','ID');
end;

Wenn die Datenbank nicht existiert, dann wird zuerst CreateEmptyDB() und dann FillDB() aufgerufen. Springt die Funktion wieder zurück zu Connect() in TORPDatabase.Connect(), dann erhalten ich folgende Meldung:

Code:

---------------------------
saxxmltest
---------------------------
SQL Error:  I/O error for file "G:\Dokumente und Einstellungen\Ich\Anwendungsdaten\OpenRoutePlanner\DB\ORP_MAIN.fdb" database or file exists. Error Code: -902. Unsuccessful execution caused by a system error that precludes
successful execution of subsequent statements The SQL: CREATE DATABASE 'G:\Dokumente und Einstellungen\Ich\Anwendungsdaten\OpenRoutePlanner\DB\ORP_MAIN.fdb' USER 'SYSADMIN' PASSWORD '' PAGE_SIZE 4096; 
---------------------------
OK   
---------------------------

Als hätte er das CREATE DATABASE Statement noch nicht ausgeführt.... :gruebel:

[Satty67]

Der Fehler tritt bereits bei CREATE DATABASE auf... alles danach ist erst mal noch nicht interessant.
Also beim Anlegen der Datei 'G:\Dokumente und Einstellungen\Ich\Anwendungsdaten\OpenRoutePlanner \DB\ORP_MAIN.fdb'

Passwort hast Du selbst ausgeklammert? masterkey bei embbed Firebird.

Evtl. die Datenbankdatei mal in einem anderen Ordner anlegen.

€: Edit...steht ja da... Du versuchst eine neue Datenbank anzulegen, aber die Datenbankdatei existiert bereits. Wenn die Datenbank ORP_MAIN.fdb noch leer ist, dann löschen.

[mkinzler]

Das Passwort ist bei embedded egal, da ja nur der Benutzernamen ausgewertet wird

[Satty67]

Kein Passwort nötig, Ok :drunken:

Also denke ist einfach, dass Du beim Testen versuchst, vorhandene Elemente nochmal anzulegen. Die erste Fehlermeldung könnte bedeuten, dass du den Trigger identisch nochmal anlegen willst. Die letzte, dass Du die bereits vorhandene Datenbank nochmal erzeugen willst.

Also falls noch beim Testen, die Datenbank löschen und nochmal alles neu anlegen lassen. Für später wäre eine Abfrage nötig... Datenbank existiert bereits, Trigger existiert bereits.

[Mithrandir]

Äh, ja, die Fehlermeldung habe ich ja auch gelesen. Ich lösche die Datenbank, und dann erscheint der Fehler. Und ich weiß nicht, wo ich zum zweiten Mal eine Datenbank erstelle. Oder habe ich gerade was falsch verstanden? :gruebel:

[Satty67]

also nach "properties.add('CreateNew..." müsste doch gleich ein Connect folgen?

Du kannst die Tabellen nicht in der erzeugten, aber nicht geöffneten Datenbank anlegen. Also zConnection.Connect vor FillDB.

Dein Ablauf:
CreateEmptyDB; -> FillDB
Connect;

Delphi-Quellcode:

if not FileExists(fCompleteDBPath) then
begin
  CreateEmptyDB;
  Connect; 
  FillDB;
end else 
  Connect;

...und FillDB aus CreateEmptyDB rausnehmen.

[Mithrandir]

Zitat:

Zitat von Satty67

Du kannst die Tabellen nicht in der erzeugten, aber nicht geöffneten Datenbank anlegen. Also zConnection.Connect vor FillDB.

Doch, das ging vorher einwandfrei. Die Tabellen waren da, und ich konnte sie mit Daten füllen. Erst als der Autoinc hinzukommen sollte, kamen die Probleme. MySQL gefällt mir in dem Zusammenhang besser...

Ich habe deinen Vorschlag umgesetzt, er hilft jedoch nicht alleine. Denn ich bekomme so immer noch die Meldung:

Code:

---------------------------
Benachrichtigung über Debugger-Exception
---------------------------
Im Projekt saxxmltest.exe ist eine Exception der Klasse EZSQLException mit der Meldung 'SQL Error:  unsuccessful metadata update DEFINE TRIGGER failed attempt to store duplicate value (visible to active transactions) in unique index "RDB$INDEX_8". Error Code: -607. This operation is not defined for system tables. The SQL: CREATE TRIGGER AUTOINC_TRG for TAGS
active before insert position 0
as
begin
  new.ID = gen_id( TAGS_AUTOINC, 1 );
end
; ' aufgetreten.
---------------------------
Anhalten   Fortsetzen   Hilfe   
---------------------------

Ich musste einen kleinen Teil deiner Prozedur anpassen:

Delphi-Quellcode:

SQL.Add('CREATE TRIGGER AUTOINC_TRG for '+Table);

wurde zu

Delphi-Quellcode:

SQL.Add('CREATE TRIGGER '+Table+'AUTOINC_TRG for '+Table);

So funktioniert es einwandfrei.

P.S.: Danke für den Hinweis auf "Password". Hatte im Eifer des Gefechts glatt das "DB" vergessen... :oops:

[DeddyH]

Das ist eben der Unterschied, ob man DB-globales Autoinc oder eines je Tabelle verwenden möchte. Wenn Letzteres, müssen alle DB-Objekte (also auch Trigger und Generatoren) eindeutig benannt sein.

[Mithrandir]

Angenommen, ich wollte ein globales Autoinc verwenden wollen. Wie würde ich das dann realisieren? Weil dafür scheint die Funktion dann ja ausgelegt gewesen zu sein, oder?

[DeddyH]

Dann muss der Trigger immer noch eindeutig benannt sein (ein Trigger gilt ja für eine Tabelle) und innerhalb desselben der globale Generator erhöht werden.

[Mithrandir]

Ah, ok. Das heißt, der Trigger muss einen eindeutigen Namen haben, der Generator kann, wenn er Global gilt, ein und denselben Bezeichner haben. Vielleicht sollte man die Funktion oben noch dahingehend erweitern... :stupid:

[DeddyH]

Hier schwirrt doch irgendwo ein iSQL-Script von Elvis herum. Daraus kann man sich doch bestimmt auch die eine oder andere Anregung holen ;)

[Satty67]

Zitat:

Zitat von Daniel G

Vielleicht sollte man die Funktion oben noch dahingehend erweitern...

Ja, die Funktion hatte ich geschrieben, als ich FireBird und Trigger gerade so theoretisch kapiert hatte :lol:

Da kann man sicher noch optimieren. Deine Erweiterung hab' ich als Kommentar in meine Unit aufgenommen, da setze ich mich demnächst nochmal dran. Bin im Moment nur wo anders dran...

[mkinzler]

Andererseits könnte das man auch Daniel umfassen.

[Satty67]

Ja gerne, zumal er ein aktuelles Projekt zum Testen hat... ich wäre dankbar :wink:

[Mithrandir]

Zitat:

Zitat von Satty67

Ja gerne, zumal er ein aktuelles Projekt zum Testen hat... ich wäre dankbar :wink:

Was? Wie? *Schützend auf mein Projekt werf'*

Geht ja eigentlich nur darum, dass der Generator global sein soll oder nicht, oder?

[hoika]

Hallo,

also zusammengefasst:

Pro Tabelle muss je ein Trigger verwendet werden, der einen
in der DB eindeutigem Namen haben muss.

Alle Trigger können den gleichen Generator benutzen,
fragt sich nur wieso ?

Bsp: 2 Tabelle, Tab1, Tab2

Tab1.Insert -> Tab1.Id=1
Tab2.Insert -> Tab2.Id=2
Tab1.Insert -> Tab1.Id=3
Tab1.Insert -> Tab1.Id=4
Tab2.Insert -> Tab2.Id=5

und sortiert:
Tab1.Id=1
Tab1.Id=3
Tab1.Id=4

Tab2.Id=2
Tab2.Id=5


Was soll das bringen ?

Übrigens noch ein Tip am Rande:
Den aktuellen Generator-Wert kann man über Select Gen_Id(Gen_Name, 0) ermittelt.


Heiko

[mkinzler]

Besser wäre es aber die Abfrage um

SQL-Code:

... returning id;

zu ergänzen

[Mithrandir]

Zitat:

Zitat von hoika

fragt sich nur wieso ?

Eventuell ist es für bestimmte Umstände nötig, eine eineindeutige ID für die gesamte Datenbank zu haben... :gruebel:

Zitat:

Zitat von hoika

Übrigens noch ein Tip am Rande:
Den aktuellen Generator-Wert kann man über Select Gen_Id(Gen_Name, 0) ermittelt.

Oder man lässt ihn sich im Insert-Statement über RETURNING zurückgeben, jupp...

[hoika]

Hallo,

das steht aber FB1.5 ...
Im Text wieder mal FB2.0

*wirr warr* ;)


Heiko

[Mithrandir]

Zitat:

Zitat von hoika

Hallo,

das steht aber FB1.5 ...
Im Text wieder mal FB2.0

*wirr warr* ;)


Heiko

Kann ja sein, dass Satty das so nutzt, ich für meinen Teil hab den FB 2.x... :stupid: Aber stümmt, unterhalb des zweiers gehts nur mit deiner Methode.

[mkinzler]

Wenn dann aber mit

SQL-Code:

select Gen_Id(<Gen_Name>, 1) ...

und in Insert angeben!

[Satty67]

Also legt meinen Code nicht in die Goldwaage (macht Ihr doch sonst auch nicht), das die Funktion nicht gleichlautende Felder einzeln behandelt lag an meinem damaligen Mangel an Verständnis der Funktionsweise.

Zitat:

Zitat von Daniel G

Kann ja sein, dass Satty das so nutzt, ich für meinen Teil hab den FB 2.x...

Ja, inzwischen auch hier schon länger FB 2.x und natürlich "returning ID". Alles andere lässt zuviel Spielraum für Fehler.

Bevor ich den Quellcode von Zeos 6.6.4 an D5prof. angepasst hatte, gab es ein älteres Zeos und noch FB 1.5 Dlls