Ankündigung

Einklappen
Keine Ankündigung bisher.

Datenbankhistorisierung mittels Triggern

Einklappen

Neue Werbung 2019

Einklappen
X
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • Scriptangebot Datenbankhistorisierung mittels Triggern

    Hi,

    ich habe eine schnelle Lösung gebraucht um alle Änderungen meiner Datenbank in einer Tabelle mitzuloggen. Sprich wenn jemand etwas ändert, möchte ich mitbekommen wer das war, wann das war und was geändert wurde. Ist noch nicht ganz ausgereift, aber sieht soweit gut aus. Hier zwei PHP-Skripte, die die Trigger für alle Tabellen der Datenbank erzeugen (bzw. in ein File schreiben, Anlegen muss selbst übernommen werden).

    PHP-Code:
    <?php
    mysql_connect
    ("localhost""***""***") or die(mysql_error());
    mysql_select_db("agb") or die(mysql_error());

    $excludeTables       = array("tag");
    $excludeColumns      = array("id""edited");
    $excludeTableColumns = array();

    $sql1     "SHOW FULL TABLES";
    $res1     mysql_query($sql1) or die(mysql_error());
    $triggers = array();
    while (
    $row1 mysql_fetch_assoc($res1)) {
        
    $trigger null;
        
    $columns = array();
        
    $table   array_shift($row1);
        
    $type    array_shift($row1);
        if (
    $type === "VIEW") {
            continue;
        }
        if (
    in_array($table$excludeTables)) {
            continue;
        }
        
    $sql2    "SHOW COLUMNS FROM $table";
        
    $res2    mysql_query($sql2) or die(mysql_error());
        while (
    $row2 mysql_fetch_assoc($res2)) {
            
    $column array_shift($row2);
            if (
    in_array($column$excludeColumns)) {
                continue;
            }
            if (
    array_key_exists($table$excludeTableColumns) && in_array($column$excludeTableColumns[$table])) {
                continue;
            }
            
    $columns[] = $column;
        }
        if (empty(
    $columns)) {
            continue;
        }
        
    $trigger .= <<<MYSQL_CODE

    -- 
    {$table}

    CREATE TRIGGER `
    {$table}_after_update` AFTER UPDATE ON `{$table}`
        FOR EACH ROW BEGIN

    MYSQL_CODE;
        foreach (
    $columns as $column) {
            
    $trigger .= <<<MYSQL_CODE
            IF COALESCE(OLD.{$column} != NEW.{$column}, 1) THEN
                INSERT INTO `history` VALUES (
                    NULL,
                    'update',
                    '
    {$table}',
                    OLD.id,
                    '
    {$column}',
                    OLD.
    {$column},
                    NEW.
    {$column},
                    @AGB_PROCESS_ID,
                    @AGB_USER_ID,
                    CURRENT_TIMESTAMP
                );
            END IF;

    MYSQL_CODE;
        }
        
    $trigger .= <<<MYSQL_CODE

        END;

    \$\$

    CREATE TRIGGER `
    {$table}_before_delete` BEFORE DELETE ON `{$table}`
        FOR EACH ROW BEGIN

    MYSQL_CODE;
        foreach (
    $columns as $column) {
            
    $trigger .= <<<MYSQL_CODE
                INSERT INTO `history` VALUES (
                    NULL,
                    'delete',
                    '
    {$table}',
                    OLD.id,
                    '
    {$column}',
                    OLD.
    {$column},
                    NULL,
                    @AGB_PROCESS_ID,
                    @AGB_USER_ID,
                    CURRENT_TIMESTAMP
                );

    MYSQL_CODE;
        }
        
    $trigger .= <<<MYSQL_CODE

        END;

    \$\$

    CREATE TRIGGER `
    {$table}_after_insert` AFTER INSERT ON `{$table}`
        FOR EACH ROW BEGIN

    MYSQL_CODE;
        foreach (
    $columns as $column) {
            
    $trigger .= <<<MYSQL_CODE
                INSERT INTO `history` VALUES (
                    NULL,
                    'insert',
                    '
    {$table}',
                    NEW.id,
                    '
    {$column}',
                    NULL,
                    NEW.
    {$column},
                    @AGB_PROCESS_ID,
                    @AGB_USER_ID,
                    CURRENT_TIMESTAMP
                );

    MYSQL_CODE;
        }
        
    $trigger .= <<<MYSQL_CODE

        END;

    MYSQL_CODE;
        
    $triggers[] = $trigger;
    }

    $triggers   implode(PHP_EOL '$$' PHP_EOL$triggers);
    $triggers   = <<<MYSQL_CODE

    DELIMITER \$\$

    $triggers

    \$\$

    DELIMITER ;

    SHOW TRIGGERS;

    MYSQL_CODE;
    $outputFile dirname(__FILE__) . "/" basename(__FILE__".php") . ".sql";
    file_put_contents($outputFile$triggers);

    echo 
    $triggers;
    In der Anwendung setze ich jetzt per
    Code:
    SET @AGB_USER_ID = '$user_email';
    z.B. den aktuellen Benutzer.

    Jede Spaltenänderung landet dann hier:
    Code:
    CREATE  TABLE IF NOT EXISTS `agb`.`history` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
      `event` ENUM('insert','update','delete') NULL ,
      `entity` VARCHAR(255) NULL ,
      `entity_id` VARCHAR(255) NULL ,
      `entity_property` VARCHAR(255) NULL ,
      `value_before` TEXT NULL ,
      `value_after` TEXT NULL ,
      `process_id` CHAR(13) NULL ,
      `user` VARCHAR(255) NULL ,
      `created` TIMESTAMP NOT NULL ,
      PRIMARY KEY (`id`) )
    ENGINE = InnoDB
    Ausgabe:
    Code:
    id	event	entity	entity_id	entity_property	value_before	value_after	process_id	user	created
    1	update	text	2021	inter_localized_label	NULL	 	4ebbf5a781820	admin@example.com	2011-11-10 17:02:47
    usw.
    entity ist dabei die Tabelle, entity_id der Primärschlüsselwert, entity_property die Spalte, value_before und value_after sollten selbsterklärend sein.
    process_id generiere ich mit uniqid() und setze es wie den Userwert.

    Um zum Testen die Trigger auch mal entfernen zu können gibts das hier:
    PHP-Code:
    <?php
    mysql_connect
    ("localhost""***""***") or die(mysql_error());
    mysql_select_db("agb") or die(mysql_error());

    $sql1  "SHOW TRIGGERS";
    $res1  mysql_query($sql1) or die(mysql_error());
    $drops = array();
    while (
    $row1 mysql_fetch_assoc($res1)) {
        
    $trigger array_shift($row1);
        
    $drops[] = <<<MYSQL_CODE
    DROP TRIGGER IF EXISTS `{$trigger}`;

    MYSQL_CODE;
    }

    $drops      implode(PHP_EOL$drops);
    $outputFile dirname(__FILE__) . "/" basename(__FILE__".php") . ".sql";
    file_put_contents($outputFile$drops);

    echo 
    $drops;
    Vielleicht kanns jemand gebrauchen ..
    "Mein Name ist Lohse, ich kaufe hier ein."


  • #2
    Das sieht ja richtig gut aus!
    Wenn ich daran denke, wie umständlich ich es innerhalb der Anwendung selbst gelöst habe, scheinen mir Trigger die beste Wahl zu sein.

    Wenn du die Daten im Backend (MySQl-Query-Browser o.ä.) änderst, wird die History auch geloggt (mit User-ID NULL), oder?

    Einziger Nachteil, den ich erkennen kann, ist, dass bei DB-Strukturänderungen ein neues SQL-Script generiert und ausgeführt werden muss. Dazu wäre es dann ganz gut, die "DROP TRIGGER IF EXISTS"-Anweisungen gleich mit im Create-Script zu integrieren.

    Kommentar


    • #3
      Klar, die Wartung halte ich auch für etwas schwierig, eigentlich mag ich Prozeduren und Trigger nicht, aber die Alternative war eben pro UPDATE/DELETE/INSERT selbst nochmal welche abzusenden. Und selbst da hatte ich nicht mal mehr alle benötigten Infos. OLD und NEW sind da schon recht hilfreich. Beim Änderungscheck einer Spalte habe ich noch eine Erweiterung eingefügt:
      COALESCE(OLD.value != NEW.value, NOT(OLD.value IS NULL AND NEW.value IS NULL))
      Jetzt werden NULL-Wertänderungen auch wirklich korrekt erkannt. Mit dem DROP IF EXISTS geb ich dir Recht, allerdings hats in einem Skript zwischenzeitlich nicht geklappt, weiß der Geier obs am DELIMITER lag, da hatte ich es kurzerhand ausgelagert.

      Performance ist auch deutlich höher als bei manuellem Logging.

      Zusätzlich hab ich noch ne operation_id für jede Trigger-Aktion hinzugefügt (auch wieder mit SET @OPERATION_ID = MD5(UUID())), damit die Änderung von zwei Spaltenwerten eines Datensatzes auch später als eine Aktion erkannt werden. Greade der INSERT und DELETE Trigger erzeugt ja mehrere Datensätze, die man in der Ausgabe später zusammen anzeigen möchte.

      Hab demnach folgende Identifier:
      session_id
      request_id
      operation_id
      user_id

      Später kann man dann sehen wer in welcher Sitzung was genau geändert hat. Gut wäre es, wenn man die Daten dann noch aggregieren würde, andernfalls läuft die DB evtl. zu. Muss ich mal schauen wie das im Livebetrieb ist.

      Wenn du die Daten im Backend (MySQl-Query-Browser o.ä.) änderst, wird die History auch geloggt (mit User-ID NULL), oder?
      Genau, im Prinzip könnte man sicherlich auch hier wieder ein COALESCE(@USER_ID, 'system') einfügen, aber NULL ist mir aussagekräftig genug. Ist halt irgendwie etwas unsauber Variablen zu benutzen, dies ggf. nicht gibt, aber eine andere Lösung, wie man den User erkennt ist mir nicht eingefallen mit Triggern.

      Einziger Nachteil, den ich erkennen kann, ist, dass bei DB-Strukturänderungen ein neues SQL-Script generiert und ausgeführt werden muss.
      Ja - und vor allem muss man kommunizieren, dass man Trigger einsetzt und eine Anleitung geben, wie man sie ändert, erweitert oder neu setzt. Werd dazu in meinem Projekt noch - neben den Skripten oben - ne README.txt anlegen.

      Edit: Beim AFTER INSERT und BEFORE DELETE-Trigger kann ich die INSERTs für die Spalten der Tabelle noch mal in ein INSERT zusammenfassen, könnte auch nochmal was an Performance rausholen.
      "Mein Name ist Lohse, ich kaufe hier ein."

      Kommentar


      • #4
        Zitat von Chriz Beitrag anzeigen
        ... aber die Alternative war eben pro UPDATE/DELETE/INSERT selbst nochmal welche abzusenden. Und selbst da hatte ich nicht mal mehr alle benötigten Infos. OLD und NEW sind da schon recht hilfreich.
        Ja, so habe ich es momentan. Beim Update schreibe ich die Werte in die Tabelle und lese sie anschließend wieder aus, um die geänderten Felder festzustellen und dann ggfs. die Inserts in die History-Tabelle durchzuführen.
        Klingt umständlich - ist es auch Hatte auch den Grund gehabt, dass wenn MySQL Daten abschneidet/rundet, bekommt man tatsächliche Änderungen nur mit, wenn man die Daten wieder ausliest, selbst wenn man die Originaldaten vorliegen hat.

        Aber die Trigger haben für mich auch den Vorteil, dass eben auch Änderungen direkt am Backend gespeichert werden. Manchmal muss ich nämlich Daten direkt ändern, wenn Benutzer irgendwas verbockt haben - und das ist dann eben nicht Protokolliert (manuell ändere ich nicht auch noch die History ab).

        Also danke für deine Anregung, werde es für ein Rewrite oder Redesign im Hinterkopf behalten!

        EDIT
        Achso, weil du gerade beim Optimieren bist.
        Du brauchst beim Loggen nicht immer den alten und neuen Wert zu speichern, einer ergibt sich aus den aktuellen Daten bzw. aus den letzten der History. Lässt sich so zwar besser handeln, aber bläht die History auch mächtig auf.

        Kommentar


        • #5
          Da ist was dran. War nur nicht sicher, ob ich aus den Daten wirklich einen Änderungsstream herausbekomme.
          "Mein Name ist Lohse, ich kaufe hier ein."

          Kommentar


          • #6
            Ich habe übrigens festgestellt, dass ein thread_stack von 128K nicht reicht. 196K reicht bei meinen Tabellen dann aber aus. Je nach Anzahl und Art der Tabellenspalten kann sich das aber noch erhöhen.
            "Mein Name ist Lohse, ich kaufe hier ein."

            Kommentar


            • #7
              Hallo Chriz,

              vielen Dank für die Anregung der Protokollierung mit Triggern und entschuldigt, dass ich den alten Thread noch einmal aufwärme
              Ich bin auf der Suche nach einer automatischen Protokollierung auf diesen Thread hier gestoßen, nachdem ich viel unnützes im Web gefunden habe - die Idee mit den Triggern hatte ich zwar im Hinterkopf, aber der letztendliche "Trick" den Benutzer von der GUI mittels Benutzervariable nach SQL zu bekommen, hatte mir noch gefehlt... Danke!

              Aber eine kleine Verständnisfrage: Warum die Prüfung mittels COALESCE( ) und nicht einfach nur der direkte Vergleich OLD != NEW?
              Das würde auch die NULL-problematik direkt umgehen, oder?

              VG Michael

              Kommentar


              • #8
                Zitat von michasch Beitrag anzeigen
                Aber eine kleine Verständnisfrage: Warum die Prüfung mittels COALESCE( ) und nicht einfach nur der direkte Vergleich OLD != NEW?
                Das würde auch die NULL-problematik direkt umgehen, oder?
                Weil vergleiche mit NULL immer NULL sind. (außer IS NULL/IS NOT NULL)

                NULL != NULL -> NULL
                NULL = NULL -> NULL
                ''blub" != NULL -> NULL
                "blub" = NULL -> NULL

                Damit würdest du keine Änderungen protokollieren können wo NULL im Spiel ist. Der Ausdruck ist aber auch nicht ganz richtig, da, wenn old NULL und new NULL ist, auch als Änderung gewertet wird. Um das zu umgehen müsste eigentlich explizit auf IS NULL bei beiden Werten geprüft werden. Der Ausdruck wird dann aber recht lang.

                Kommentar


                • #9
                  Hallo erc,

                  danke für die Aufklärung, das war mir entgangen...
                  Die Erweiterung der NULL-Behandlung hatte Chriz ja schon in Post #3 (http://www.php.de/scriptboerse/85514...tml#post631665) beschrieben, wenn auch nicht im Originalbeitrag angepasst.

                  VG Michael

                  Kommentar


                  • #10
                    Das hab ich übersehen. Die Lösung ist auf jedenfall elegant. Ich hatte sowas im Kopf:
                    PHP-Code:
                    (new.value IS NOT NULL AND old.value IS NOT NULL AND new.value != old.value) OR (new.value IS NULL AND old.value IS NOT NULL) OR (new.value IS NOT NULL AND old.value IS NULL

                    Kommentar

                    Lädt...
                    X