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. 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

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...

[mkinzler]

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