Serverseitiges Logbuch über Datenänderungen

[ZOD]

Hallo Zusammen.

Wir denken gerade darüber nach, ob in unserem Umfeld die Integration eines Änderungslogbuchs auf Datenbankserverseite für uns sinnvoll möglich ist.

Hintergrund:
Produktivdatenbank läuft unter Firebird 2.5 (Entwicklungstests zusätzlich mit FB3).
Sämtliche Tabellen haben eindeutige ID-Spalten (über Generatoren erzeugte integer IDs).
Eher unbedeutend in diesem Zusammenhang, aber zur Vollständigkeit:
die Clientanwendung ist unter Delphi entwickelt, DB-Zugriff über DBExpress Komponenten.

Unser gedanklicher Ansatz ist nun folgender:
jede Änderung an einem Datensatz löst den Logbuchprozess aus.
Als Logbuch stellen wir uns eine Tabellenstruktur vor, in der die Änderungen abgelegt werden (Details noch offen).
Im Logbuchprozess soll über Steuertabellen entschieden werden, bei welcher Tabelle in welchem Umfang ein Log geführt wird.
Optimal wäre natürlich z.B. eine stored procedure, die als Parameter den Tabellen-Namen und die Datensatz-ID übergeben bekommt,
so dass mithilfe der Systemtabellen (Metadaten) dann automatisiert Änderungen in der o.g. Logbuchstruktur protokolliert werden - siehe hier Frage 2) unten.

Das ganze ist - gelinde gesagt - noch ein nebulöses Wolkenschloss an Wünschen und Ideen.

Es gibt nun einige Fragen zu diesen Überlegungen, für die ich bei meinen Recherchen (Google und Co) noch keine Antworten gefunden habe - oder ich habe vielleicht ein Verständnisproblem. Hier also die Frageliste:

1) Anstoßen des Logbuchprozesses
Natürlich könnte ich in jeder Tabelle einen Trigger für insert/update/delete anlegen und da den Logbuchprozess anstoßen,
geht das nicht einfacher - sozusagen als zentraler Trigger?

2) Änderungen erkennen
Eine triviale Notwendigkeit ist, dass - um Änderungen zu erkennen - der Zustand vorher mit dem Zustand nacher verglichen werden muss. Prinzipiell ist das mit Tabellen-Trigger vom Typ "before" möglich (old.xx zu new.xx vergleichen).
Gibt es eine Möglichkeit, die beiden Datenmengen (old und new) z.B. einer stored procedure verfügbar zu machen? Dann könnte es vermieden werden, die gesamte Prüflogig in JEDER Tabelle mehrfach abzulegen.

Ich bin dankbar für Tips und Hinweise.
Gruß
Markus

[Fritzew]

Schaut Euch mal IBexpert an.
Da gibt es so etwas, meine ich schon. Wenn ich mich richtig erinnere werden Trigger und Tabellen in der DB erzeugt die alle Änderungen mitlogen. Wir dort glaube ich für die Replication verwendet. Sollte eigentlich alles erschlagen.

[jobo]

Mir fallen dazu 2,5 Dinge ein:
1. Gerade Firebird handhabt die Updates doch mit neuen Recordversionen. Ich kenne diesen Mechnismus überhaupt nicht, aber es scheint mir naheliegend, sich das vielleicht nutzebar zu machen.. ?
(Einschränkung: Es wäre ein Verfahren, das sehr Produktnahe ist, mit den üblichen Haken.)
2. Gar keine Updates machen, sondern nur inserts und aktive Flag, also quasi eine Highlevel Variante von Firebirds log Mechnismus.
(Einschränkung: Auf Anhieb nicht unbedingt filigran)
2,5. Postgres erlaubt zentrale Trigger, die man prima zum Logging nutzen kann.

[joachimd]

ist http://www.upscene.com/database_audi...and_interbase/ eigentlich noch aktiv? Ist doch genau, was Du willst.

[MyRealName]

In Firebird gibt es keinen zentralen trigger, glaube ich.
im Trigger der tabelle hast du im Insert/update zugriff auf die NEW-Variable, im Update/delete auf die OLD-variable
technisch kannst du dann deine steuertabelle auslesen und dann entsprechend die old/new variableninhalte vergleichen (beim update).
beim insert würde ich keine feld-log schreiben, weil die werte sind ja eh in der tabelle. beim delete musst halt ma schaun, ob du wegspeichern willst, was gelöscht wurde.

Ich selbst mache es nicht in der datenbank, ich habe mir ein Objekt in Delphi geschrieben, welches beim Dokument erstellen sich eine liste der ausgangswerte im Dataset macht und diese in einer stringliste (hab ich vor Jahren geschrieben, jetzt ginge da vllt auch was anderes, denk da an JSON) und diese liste dann komprimiert in die BD.
Beim Editieren dann wurde die alte Liste wieder geladen und beim erneuten Speichern gegen die alte verglichen und gleiche werte rausgeworfen. Geblieben sind nur die veránderten werte und die wurden wieder komprimiert weggeschrieben.
Das ganze waren dann pro formular und zu überwachender tabelle 3 zeilen, das kann man aber noch optimieren mit einem Wrapper der sich um mehrere tabellen kümmert

[MyRealName]

Da fällt mir ein : dadurch dass du ja eine steuertabelle hast, kannst du naturlich dir den trigger code zusammenbauen on-the-fly wenn man die steuertabelle speichert und dann den trigger schreiben, also programmtechnisch schreiben und als script in die DB setzen.

ich habe das mal schnell geschrieben, musst man eventuell anpassen, habe UniDAC und EhLib genutzt und keine Komfort-Sachen reingeschrieben, also alles fest gecoded :

Code:

unit frmMain;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, DBGridEhGrouping, ToolCtrlsEh,
  DBGridEhToolCtrls, DynVarsEh, EhLibVCL, GridsEh, DBAxisGridsEh, DBGridEh,
  Vcl.StdCtrls, Vcl.ExtCtrls, UniProvider, InterBaseUniProvider, DBAccess, Uni, Data.DB, MemDS;

type
  TForm5 = class(TForm)
    UniConnection1: TUniConnection;
    UniTransaction1: TUniTransaction;
    InterBaseUniProvider1: TInterBaseUniProvider;
    Panel1: TPanel;
    ComboBox1: TComboBox;
    DBGridEh1: TDBGridEh;
    umdTables: TUniMetaData;
    Memo1: TMemo;
    umdCols: TUniMetaData;
    DataSource1: TDataSource;
    procedure FormShow(Sender: TObject);
    procedure UniConnection1AfterConnect(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure DBGridEh1SelectionChanged(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form5: TForm5;

implementation

{$R *.dfm}

procedure TForm5.FormShow(Sender: TObject);
begin
  UniConnection1.Connected := True;
  If UniConnection1.Connected Then begin
    umdTables.Active := True;
    While Not umdTables.Eof Do begin
      ComboBox1.Items.Add(umdTables.FieldByName('TABLE_NAME').AsString);
      umdTables.Next;
    end;
    umdTables.Close;
  end;
  If ComboBox1.Items.Count > 0 Then
    ComboBox1.ItemIndex := 0;
end;

procedure TForm5.UniConnection1AfterConnect(Sender: TObject);
begin
  UniTransaction1.StartTransaction;
end;

procedure TForm5.ComboBox1Change(Sender: TObject);
begin
  umdCols.Close;
  umdCols.Filtered := False;
  umdCols.Filter := 'TABLE_NAME=''' + ComboBox1.Text + '''';
  umdCols.Filtered := True;
  umdCols.Open;
end;

const TRIGGER_START : String = 'SET TERM ^ ; ' + #13#10 + #13#10 +
                               'CREATE OR ALTER TRIGGER TR_LOG_[TABLE_NAME] FOR [TABLE_NAME]' + #13#10 +
                               'ACTIVE AFTER UPDATE POSITION 100' + #13#10 +
                               'AS' + #13#10 +
                               'declare variable LogStr varchar(10000);' + #13#10 +
                               'begin' + #13#10 +
                               '  Logstr = '''';';
      TRIGGER_LOG_LINE : String = '  IF (NEW.DocId <> OLD.DocId) THEN' + #13#10 +
                                  '    Logstr = :LogStr || IIF(:LogStr <> '''', '','', '''')  || ''"[FIELD_NAME]":'' || ''"'' || OLD.[FIELD_NAME] || ''"'';';
      TRIGGER_LOG_END : String = '  IF (:LogStr <> '''') THEN' + #13#10 +
                                 '  BEGIN' + #13#10 +
                                 '    LogStr = ''{ "CC": {'' || :LogStr || ''}}'';' + #13#10 +
                                 '    -- INSERT CODE HERE FOR WRITING INTO THE LOGGING TABLE' + #13#10 +
                                 '  end' + #13#10 +
                                 'end^' + #13#10 + #13#10 +
                                 'SET TERM ; ^';

procedure TForm5.DBGridEh1SelectionChanged(Sender: TObject);
var i: Integer;
    sLine : String;
begin
//  sTriggerName := 'TR_LOG_' + ComboBox1.Text;  // TODO: delete everything longer than 31 chars
  Memo1.Clear;
  If DBGridEh1.SelectedRows.Count > 0 Then
    Memo1.Lines.Text := TRIGGER_START;
    Memo1.Lines.Text := StringReplace(Memo1.Lines.Text, '[TABLE_NAME]', ComboBox1.Text, [rfReplaceAll, rfIgnoreCase]);
    For i := 0 To DBGridEh1.SelectedRows.Count - 1 Do begin
      umdCols.GotoBookmark(TBookmark(DBGridEh1.SelectedRows.Items[i]));
      sLine := TRIGGER_LOG_LINE;
      sLine := StringReplace(sLine, '[FIELD_NAME]', umdCols.FieldByName('Column_name').AsString, [rfReplaceAll, rfIgnoreCase]);
      Memo1.Lines.Add(sLine);
    end;
    memo1.Lines.Add(TRIGGER_LOG_END)
end;

end.

Was macht der code :

Jedesmal wenn du ein Feld an- oder abwählst in der Tabelle wird der Trigger Code mit der aktuellen Feldliste erstellt, der einen JSON string erstellt mit den alten Werten, wenn was geändert wurde, Damit hast du in der Daten-Tabelle deine aktuelle Version der Daten und mit Zeitstempel rückwärts kannst du zum Ursprung zurückkehren.

man kann noch einen Delete trigger machen mit allen Feldern, wenn man will, damit man auch bei gelöschten Registern noch Daten hat

https://puu.sh/y3FJg/e1f21a30f9.png

[tsteinmaurer]

Hallo Joachim,

Zitat:

ist http://www.upscene.com/database_audi...and_interbase/ eigentlich noch aktiv?

Natürlich. :thumb:

[ZOD]

Guten Morgen,

vielen Dank für die Tipps und Vorschläge. Ich schaue mir das an und werde mich
hier zurückmelden - sozusagen als Fortschrittsbalken ;-).

[mquadrat]

Wir haben bei unseren Desktop-Anwendungen die Variante mit den Triggern je Tabelle. Ist bei uns Grundlage für einen Replizierungslayer. Bei den Web-Anwendungen sitzt die Logik in der Datenzugriffsschicht.

[ZOD]

Hier die versprochene Rückmeldung:

wir setzen zur DB Wartung IBExpert ein. Hier gibt es einen Log-Manager.
Ich hatte wohl Tomaten auf den Auge, dass ich das bei meiner Suche nicht gesehen habe.

Wir haben das nun scharf geschaltet (es werden im IBExpert entsprechende Tabellen und Trigger erzeugt) und werden mit den hier entstehenden Daten die Darstellung der Änderungen in unserer Applikation gestalten.

Nochmals danke für alle Beiträge - insbesondere an MyRealName und Fritzew