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).
In der Anwendung setze ich jetzt per
z.B. den aktuellen Benutzer.
Jede Spaltenänderung landet dann hier:
Ausgabe:
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:
Vielleicht kanns jemand gebrauchen ..
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;
Code:
SET @AGB_USER_ID = '$user_email';
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
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
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;
Kommentar