PHP Frage, Denkfehler für eine SQL Aktualisierung mit Versionierung

[Domi83]

Hallo Leute, damit ich mit HominiLupus nicht gleich wieder in einem Streit ende, versuche ich so viel wir möglich mitzuteilen und hoffe ich vergesse nichts. Auf jeden Fall geht es nicht um eine Hausaufgabe

Hier in der Firma haben wir eine Datenbank mit Kundendaten, meine Kollegen sollen diese aber auch aktualisieren können mit einer Versionierung wegen der Revisionssicherheit. Das heißt, die MySQL Daten werden nicht mit einem UPDATE bearbeitet, sondern mit einem INSERT neu in die Datenbank geschrieben, mit dem Vermerk "Version 2, 3, etc." und gut ist.

Nun möchte ich natürlich ohne alle Zeilen abzugleichen, sehen was sich verändert hat und das in eine E-Mail schreiben lassen die dann in einem Sammelpostfach aufläuft. Man könnte es auch in ein LOG schreiben, aber eine E-Mail wäre erst einmal ausreichend (so der Wunsch).

Mein Ansatz wäre jetzt wie folgt, mit Hilfe von PHP lasse ich mir alle Daten in ein Array speichern (MySQL SELECT) und beim INSERT lasse ich dann überprüfen, was sich verändert hat anhand der Inhalte. Diese Änderungen speichere ich in einem weiteren Array, füge diese dann der E-Mail bei und fertig ist.

Man kann wohl auch die Unterschiede von Zeilen direkt via MySQL abrufen lassen, dass wäre auch eine Option... einfach INSERT machen, abgleichen und das dann in die E-Mail packen, fertig. Aber da mangelt es dann komplett an MySQL Kenntnissen. Ich glaube ein paar Beispiele habe ich via Google "mysql select row differences" gefunden, da lasse ich aber erst einmal die Finger von und wollte fragen ob ihr da bessere oder einfachere Ansätze habt.

Gruß, Domi

Nachtrag: Unter den Kundendaten sind, Vorname, Nachname, Anschrift, Rufnummer etc. und wenn man jetzt Rufnummer und Nachname geändert hat, zwischen Version 1 und 2, oder 2 und 3, würde ich das gerne angezeigt bekommen.

 

[Peuserik]

Hallo,

Ich habe jetzt nicht die Ahnung von PHP, aber warum arbeitest du nicht mit triggern?

Datenbank trigger könnte auf deine Aktion die Änderungen in eine log Tabelle schreiben, die du dann nach belieben abfragen kannst. Das Ergebnis dieser Abfrage kannst du dann in deine Email packen.

-> http://phpforum.de/forum/showthread.php?t=269509


Gruß

 

[Mojo1987]

Man schreibt die Originaldaten eines Kundenstamm nicht mehrfach in den Kundenstamm... Das wiederspricht dem Sinn einer Datenbank und das ist auch nicht praktikabel. Du machst Updates für die Daten und schreibst die Änderungen und eine Log/Audittrail oder was auch immer Tabelle/Datenbank inkl deiner Versionsnummer. Die Logeinträge kannst du dann abrufen wie du willst und per Mail oder was auch immer versenden.

 

[Domi83]

Moin moin, Mojo1987 diverse Gedankengänge klaue ich mir halt von anderen Systemen... In diesem Fall hatte ich mir das vor Jahren (kann auch nur ein Jahr sein) mal in einer SQL Datenbank von WordPress abgeguckt. Wurde ein "Artikel" in WordPress überarbeitet, ist dieser als neue Version in die gleiche Datenbank geschrieben worden, hat eine neue Versionsnummer bekommen und gut ist.

Aber schlecht klingt es erst einmal nicht. Ich überlege gerade wo die Vorteile in solch einem Szenario wären...

Bezüglich dem "Trigger", dass klingt gut... den Thread lese ich mir mal durch, denn auch die Anleitung sieht Interessant aus.

Gruß, Domi

Nachtrag: Peuserik, der Tipp mit dem Trigger ist ja der Hammer!! Ich muss das Beispiel jetzt nur auf unsere Datenbank anpassen und dann wäre das eine Variante. So könnte man auch immer nachverfolgen was geändert wurde und was nicht. Ich denke mal, dass ist ja auch das gleiche was Mojo1987 meinte.

 

[Mojo1987]

WordPress ist dafür ein schlechtes Beispiel. Du solltest dich eher an CRM Software bzw Software aus dem Profibereich orientieren. Genau darum geht es dir ja auch. Und die handhaben das genau so. Beispiel mit dem ich selbst schon gearbeitet hab ist z.B. SAP Business One. Die Tabelle für Kunden ist dafür da den Kundenstamm zu pflegen und nicht dazu um mit dem selben Kunden in unterschiedlichen Versionen aufgeblasen zu werden. Mit ERM hat das nicht viel zutun...

Du willst ne Änderungsverfolgung dann musst du schon auch eine loggen

 

[Domi83]

Moin moin und frohe Weihnachten nachträglich

Ich hätte da noch mal eine Frage zu der Trigger Geschichte... Gibt es eine einfache Abfrage, wie man Veränderungen, Insert und Delete prüft? In einer unserer Tabellen befinden sich 48 Spalten. Unter anderem halt ID, Nachname, Vorname, Anrede, Titel, Anschrift, PLZ, Ort etc. und wenn ich das Beispiel vom phpForum so anschaue, müsste ich wohl jedes Feld irgendwie prüfen lassen.

Daher wäre nun meine Frage, ob es einen einfachen Befehl gibt, wie man alle Spalten prüfen lassen kann. Wenn ich das schon mal korrekt verstanden habe, muss ich ja in der Trigger-Tabelle 48x2 (Old + New) Spalten anlegen, aber die Frage wäre nun ob man den Trigger selbst besser programmieren kann um Änderungen zu prüfen.

Gruß, Domi

 

[mental.dIseASe]

Ich kann mir nicht vorstellen, dass es so einen Mechanismus gibt. Als wir solch einen AUDIT_TRAIL bei einem anderen DBMS gemacht haben, haben wir die Trigger immer aus anderen Meta-Daten generieren lassen. Vielleicht ist es auch eine Überlegung wert, ob man tatsächlich die Änderung in allen Spalten festhalten will. Vielleicht sind ja nur ein paar interessant. Bei 48 Spalten scheint mir das wahrscheinlich.

Außerdem bräuchte dein AUDIT_TRAIL vielleicht auch nicht allzu viele Spalten. TABLE_NAME, COLUMN_NAME, ENTITY_ID, CHANGING_DUDE, TIMESTAMP, OLD_VALUE und NEW_VALUE könnten vielleicht reichen.

 

[kelox]

Falls ihr SQL Server nutzen könnt (den gibt es auch kostenlos), könntest du auch temporal tables verwenden:

https://msdn.microsoft.com/en-us/library/dn935015.aspx

Vielleicht gibt es so ein Feature auch bei MySQL.

 

[Domi83]

Hallo Leute, ich habe mal nach längerer Zeit und diversen Tests etc. eine kleine Frage zu dem Thema "trigger", was ja wirklich gut funktioniert.

In Post 2 gab es ja den Link zu diesem Beitrag, damit hatte ich das auch mal grob aufgebaut und hin und her getestet. Kam leider auch erst relativ spät dazu, dass ganze Szenario einmal zu testen.

Nun wäre da eine weitere Frage, wenn man nun eine Datenbank Tabelle mit grob 20 spalten hat, muss ich dann für die "logging" Tabelle 40 Spalten (angenommen ich logge alles) erstellen, oder gibt es da eine schönere Lösung, oder habt ihr das ebenfalls so?

tabelle1
- name
- vorname
- anschrift

tabelle1_log
- name_old
- name_new
- vorname_old
- vorname_new
- anschrift_old
- anschrift_new

Ich hoffe man kann grob nachvollziehen wie dieser Aufbau gemeint ist.

 

[mental.dIseASe]

Erlaube mir, mich selbst zu zitieren:

Außerdem bräuchte dein AUDIT_TRAIL vielleicht auch nicht allzu viele Spalten. TABLE_NAME, COLUMN_NAME, ENTITY_ID, CHANGING_DUDE, TIMESTAMP, OLD_VALUE und NEW_VALUE könnten vielleicht reichen.

Die Trigger prüfen bei einem Update, welche Felder sich geändert haben und schreiben dann einen Datensatz TABLE_NAME, COLUMN_NAME, ENTITY_ID, OLD, NEW in den AUDIT_TRAIL.

Wenn du jede Spalte einmal in sone Schattentabelle duplizierst, wirst du eher genau gar nichts gewinnen, denke ich.

 

[Teisi]

Eine wirkliche versionierung einer Datenbank ist mir nicht bekannt, soweit ich weiß sind auch große firmen beim versuch daran gescheitert.
Ich kenne nur die Microsoft lösung die einen ansatz für versionierung hat, aber trotzdem keine richtige ist!

Wenn wer was anderes weiß oder es was neues dazu gibt... BITTE Posten! DANKE.

 

[Andreas_]

Eine wirkliche versionierung einer Datenbank ist mir nicht bekannt, soweit ich weiß sind auch große firmen beim versuch daran gescheitert.

Nach den Postings hier im Thread geht es Domi83 nicht um die "Versionierung" einer Datenbank sondern um eine Änderungshistorie.

mental.dIseASe hat einen generischen Ansatz dazu gepostet, den ich für praktikabler halte, als die Idee von Domi83 ...

 

[Domi83]

@mental.dIseASe, verstehe ich es richtig dass durch das von dir vorgeschlagene Verfahren in meiner "Log Tabelle" aufgeschrieben, welches Spalte (Column) sich verändert hat und wie der alte sowie neue Datensatz ist, richtig?

Beispiel wäre ja (wenn ich das korrekt wiedergeben kann), ich verändere in Tabelle 1 einen Datensatz (Vorname, Nachname, Adresse) und in meiner "Log Tabelle" werden anschließend drei Einträge erstellt mit der Information was sich geändert hat, korrekt? Wenn ich das Verfahren richtig verstanden habe, würde ich dieses begrüßen. Dann müsste ich meiner "Log Tabelle" nicht 48 Spalten anlegen. Das einzige was ich dann noch nicht herausgefunden habe (da fehlen mir dann Fachbegriffe), wie erkläre ich dem Trigger das er den Spaltennamen sowie OLD.value und NEW.value ausließt? Das hatte ich durch meine Suche noch nicht herausgefunden.

 

[Andreas_]

Das einzige was ich dann noch nicht herausgefunden habe (da fehlen mir dann Fachbegriffe), wie erkläre ich dem Trigger das er den Spaltennamen sowie OLD.value und NEW.value ausließt?

Du musst für jede Spalte einzeln prüfen ob OLD.value <=> NEW.value. Da hast Du in dem Moment also sowohl Spaltennamen als auch neuen und alten Wert.

 

[ayngush]

Hallo,

ich habe vor kurzem für einen Teil unseres CRM ein Protokoll / audit trail neu geschrieben und dabei einen ähnlichen Ansatz verfolgt.
Es werden jeweils Create, Update und Delete Aktionen geloggt.
Beim Anlegen / Create wird gespeichert, welcher Benutzer welchen Kunden erstellt hat aber nur in der Form: BenutzerID, AktionsID, KundenID, Zeitstempel
Beim Aktualisieren und Löschen wird zusätzlich das ursprüngliche Tupel als JSON in ein Datenfeld der Protokolltabelle gespeichert.
Ein umfangreicher Vergleich findet beim Protokollieren nicht statt. Dafür kann man sich dann ja eine Anwendung schreiben, die das im Bedarfsfall vornimmt, zum Beispiel, wenn man die Änderungen bei Vandalismus punktuell rückgängig machen muss.

Das hat den Vorteil, dass eine Aktion auf einer Entität auch genau einen Protokolleintrag hat und nicht 0,1-48 oder so. Das kann ja niemand mehr vernünftig auswerten

Grüße

 

[Domi83]

Moin moin, einmal für mich zur Klarstellung... Ich bin aufgrund der etwas zurückliegenden Ausbildung aus dem Programmierbereich raus. Es ist nicht so, dass ich es nicht kann, mir sind nur diverse Begriffe nicht mehr so ganz geläufig. Bin froh, wenn ich noch "Kardinalitäten" im Kopf habe

Es handelt sich z.B. bei der Tabelle um Kontaktdaten die ich triggern möchte. Darin enthalten sind aktuell folgende Spalten enthalten...

id (int)
createDate (datetime)
updateDate (datetime)
anrede (varchar)
titel (varchar)
firma (varchar)
vorname (varchar)
nachname (varchar)
anschrift (varchar)
plz (varchar)
ort (varchar)
tel (varchar)

Wenn ich euch richtig verstanden habe, macht es also keinen Sinn wenn ich in meine 'log_tabelle' für jede kleine Veränderung einen separaten Eintrag mache. Denn, wenn ich PLZ und Ort ändern würde, wäre das zwei neue Einträge, soweit korrekt?

Macht man dann am besten (wie in dem php.net Beispiel) eine Tabelle die ungefähr so aussieht?

id (int)
anrede_old (varchar, NULL)
anrede_new (varchar, NULL)
titel_old (varchar, NULL)
titel_new (varchar, NULL)
firma_old (varchar, NULL)
firma_new (varchar, NULL)
vorname_old (varchar, NULL)
vorname_new (varchar, NULL)
nachname_old (varchar, NULL)
nachname_new (varchar, NULL)
...

Es gibt ja viele Wege die nach Rom führen, mich würde halt interessieren welches die sinnvollere Variante dafür wäre.

Gruß, Domi

 

[ayngush]

Uff, ne. Damit legst Du dich zu sehr fest imho.

Ich würde das so machen:

Tabelle ProtokollAktionen:

	[ProtokollAktionID] [int] NOT NULL,
	[AktionBezeichnung] [varchar](50) NOT NULL,
	[Datenbank] [varchar](50) NOT NULL,
	[Tabelle] [varchar](50) NOT NULL,
	[AktionBezugIDFeld] [varchar](50) NOT NULL,

Beispieldatensatz:

	ProtokollAktionID: 131
	AktionBezeichnung: 'Kundenkontaktdaten bearbeitet'
	Datenbank: 'CRM20123'
	Tabelle: 'Kontaktdaten'
	AktionBezugIDFeld: 'id'

Tabelle Protokoll:

	[ProtokollID] [bigint] IDENTITY(1,1) NOT NULL,
	[BenutzerID] [int] NOT NULL,
	[ProtokollAktionID] [int] NOT NULL,
	[SystemDatum] [datetime] NOT NULL,
	[AktionBezugID] [int] NULL,
	[Daten] [varchar](max) NULL,

Beispieldatensatz:

	ProtokollID: 254024
	BenutzerID: 123
	ProtokollAktionID: 131
	SystemDatum: 2017-05-11 09:21:13
	AktionBezugID: 587497
	Daten: '{"id":587497,"createDate":"2017-03-01 09:00:00","updateDate":"2017-05-11 09:21:13","anrede":"Herr","titel":"Dr.","firma":"KlausKunde GmbH","vorname":"Klaus","nachname":"Kunde","anschrift":"Straße 99","plz":"30659","ort":"Hannover","tel":"+49 511 1234567"}'

Für das konkrete "Zurückrollen" / oder konkrete "Vergleichen" oder das betrachten der "Vorgängerversion eines Datensatzes" etc. brauchst Du dann zwar ein Tool, welches das JSON im Datenfeld parsen kann, dafür bist du aber beim eigentlichen protokollieren viel flexibler.
Für Statistiken (Welcher Benutzer ändert am meisten Kundendaten, wie viele neue Kunden wurden im Zeitraum x angelegt, etc.) kannst Du ohne komplexes JSON geparse einfache querys auf das Protokoll anwenden, da eine Aktion (Anlegen, Ändern, Löschen) auf einer Entität (Kundendaten oder Kontaktdaten oder Produkte oder Bestellungen oder...) so immer genau einen Protokolleintrag hat...

Der tiefere Sinn eines CRM ist es ja nicht (nur) seine Kudendaten irgendwie zu speichern, das kannst Du auch in Excel, sondern eben auch statistische Aussagen treffen zu können.

Grüße

PS: Wenn du "Kardinalitäten" noch im Kopf hast, dann schau auch nochmal auf die Begriffe "3. Normalform", "Entität", "Attribut" und "Tupel" im Zusammenhang mit Datenbanken und so

 

[Andreas_]

Wenn ich euch richtig verstanden habe, macht es also keinen Sinn wenn ich in meine 'log_tabelle' für jede kleine Veränderung einen separaten Eintrag mache. Denn, wenn ich PLZ und Ort ändern würde, wäre das zwei neue Einträge, soweit korrekt?

Es sind zwar zwei Einträge, allerdings spart man gegenüber der Speicherung aller Datenfelder in einem Eintrag erheblich Speicherplatz. Auch ist es problemlos möglich an Hand von Timestamp und Bearbeiter die zusammengehörigen Logeinträge darzustellen.

Für das konkrete "Zurückrollen" / oder konkrete "Vergleichen" oder das betrachten der "Vorgängerversion eines Datensatzes" etc. brauchst Du dann zwar ein Tool, welches das JSON im Datenfeld parsen kann, dafür bist du aber beim eigentlichen protokollieren viel flexibler.

Es gibt bei MySQL den Datentyp JSON, die Datenbank ist in der Lage mit dem Datentyp zu arbeiten und kann ihn auch parsen. Allerdings verhält sich eine Spalte vom Datentyp JSON bis auf die speziellen JSON-Abfragen aus Datenbanksicht wie eine BLOB-Spalte.

Eine derartige Lösung käme für mich nur in Frage, wenn die Anwendung (z.B. CRM) selber loggen soll. Ansonsten ist es für mich eine gewaltige Speicherplatzverschwendung.

 

[ayngush]

Vor 20 Jahren hätte ich dir in diesem Punkt, Platzverschwendung, noch Recht gegeben.
Eine AS400 DB2 war teuer und vom Platz her sehr beschränkt. Zum Glück kostet das in 2017 alles nichts mehr, sodass man den Komfort / die Usability wichtiger als die Kostenfrage bewerten kann.
Letztlich muss jeder seine eigenen Anforderungen finden und umsetzen.

Wir verwenden dann für unser CRM doch eher einen Microsoft SQL Server, andere nehmen da SAP oder Oracle.
Gegen PostgreSQL habe ich in dem Bereich auch nichts einzuwenden.
MySQL kann man wohl machen aber es ist aus meiner Sicht nicht das beste Produkt für ein CRM.

Ob es glücklich und sinnvoll ist über Timestamp und Bearbeiter irgendwas in Relation zu bringen ... für mich irgendwie nicht.
Auch wäre dein System von der Performance her nicht gut, dafür weniger verschwenderisch mit dem Speicherplatz, das ist korrekt.
Aber die Vergleiche über alle Datenfelder + jedes mal ein Insert bei Abweichung... Bei meinen System gibt es bei einer Datenmanipulation ein Insert, ohne Vergleich. Ich muss dazu sagen, dass bei unseren REST-API-Ansatz, der protokolliert, die Alt-Daten ohnehin als JSON-String vorliegen, sodass das Parsen kein Zusatzaufwand fürs Protokoll ist.

Grüße

PS: Bei Deinem Modell, für jedes geänderte Attribut einen neuen Protokolleintrag anzulegen, verschwendest du übrigens auch Platz. Nämlich: Bearbeiter, Timestamp, Datenfeldbezeichnung und "New Value". New Value steht damit doppelt in der Datenbank. Nichts für ungut... aber so viel mehr Platz verbraucht JSON da dann auch wieder nicht, nur etwas mehr

 

[Andreas_]

Eine AS400 DB2 war teuer und vom Platz her sehr beschränkt. Zum Glück kostet das in 2017 alles nichts mehr, sodass man den Komfort / die Usability wichtiger als die Kostenfrage bewerten kann.

Welchen Vorteil in der Usability bringt Dein Vorschlag? Mir fällt da auf Anhieb keiner ein. Ein Vorteil wäre es, wenn die darüber liegende Anwendung direkt diese Logs schreiben und auch wieder lesen kann. Das ist aber im Fall des TE den geschilderten Fakten nach nicht gegeben.

Aus meiner Sicht bringt Deine Variante sogar Nachteile in der Usability. JSON Spalten können nicht indiziert werden, was bei größeren Datenmengen zu erheblichen Geschwindigkeitseinbußen bei der Abfrage führt.

MySQL kann man wohl machen aber es ist aus meiner Sicht nicht das beste Produkt für ein CRM.

Kannst Du dafür auch Gründe anführen?

Ob es glücklich und sinnvoll ist über Timestamp und Bearbeiter irgendwas in Relation zu bringen ... für mich irgendwie nicht.

Man kann auch für die Bearbeitung eine ID vergeben. Liegt ganz in der Hand des DB-Erstellers. Im Gegensatz zu Deiner Lösung ist man da sehr flexibel.

Auch wäre dein System von der Performance her nicht gut

Das Gegenteil ist der Fall. Noch einmal für Dich: BLOB Spalten können nicht indiziert werden. Für den Vorschlag von mental.dIseASe gilt das nicht (Wenn Du den Thread nicht nur oberflächlich gelesen hättest, wäre Dir klar, dass der Vorschlag nicht von mir stammt.), alle verwendeten Spalten können indiziert werden.

Aber die Vergleiche über alle Datenfelder + jedes mal ein Insert bei Abweichung...

Das ist einer der Anwendungszwecke, für den Trigger erfunden wurden ... Andere Datenbanken wie z.B. Oracle sind da zugegebener maßen etwas flexibler, da entfällt der Vergleich.