Änderungen an Tabelle speichern und auswertbar machen (MsSQL)

sql
mssql

#1

Hallo,
ich bin in die missliche Lage geraten, eine Tabellenstruktur (MsSQL) zu erstellen, die so funktioniert, dass jede Änderung an einer Zelle historisiert abgespeichert wird. Die einzige Spalte die immer gleich bleibt, ist die Ident-Spalte. Im Nachhinein soll es möglichsein, die Tabelle zu einem bestimmten Zeitpunkt wieder zu “rekonstruieren”.

Da mir die Erfahrung mit Datenbanken fehlt, wollte ich euch mal fragen, wie man dieses Problem am besten angeht. Sollte ich immer die gesamte Zeile mit einem Zeitstempel in eine Archivtabelle speichern, oder wäre es sinnvoller nur die veränderten Werte weg zu speichern? Gibt es eventuell noch andere Ansätze, die hier mehr Sinn machen?

Vielen Dank für eure Unterstützung!
Grüße Hans


#2

Moin,

bin mir nicht sicher, ob ich es richtig verstanden habe, aber wenn sich ‘nur’ die Werte ändern, dann sollte die Archivtabelle von der Struktur auch gleich aufgebaut sein!

VG Klaus


#3

Die wohl einfachste Möglichkeit wäre Datomic zu verwenden oder zumindest mal zu verstehen welche der Mechanismen dahinter stehen. Den Datomic wurde mehr oder minder genau für diesen Anwendungsfall entworfen.

http://www.datomic.com/

Wenn man bei SQL bleiben möchte, dann gibt es mit der Embedded DB HSQLDB, den sogenannten Log-Modus

Logdatei-Tabellen: Alle SQL-Befehle, die die Tabellen-Daten verändern (CREATE, ALTER, INSERT, UPDATE), werden in einer großen SQL-Logdatei gespeichert. Die Logdatei wird bei jedem Start wieder abgearbeitet und dann im Speicher abgelegt.

Würde man jetzt einfach hingehen und das ganze soweit patchen, dass vor jeden Eintrag ein zusätzlicher Timestamp geschrieben wird, dann könnte man das ganze auch soweit patchen, dass die Datenbank zu einem bestimmten Zeitpunkt wiederhergestellt werden kann. Zum Beispiel führe beim Neustart alle Statements bis gestern 12:00 Uhr aus.

Ist aber eben nicht MsSQL.

Dann gibt es noch NoSQL Datenbanken, die in der Regel bei jeder Änderung eines Datensatzes eine neue Revision mit Timestamp und geänderten Daten erstellen. CouchDB zum Beispiel. Das ist vergleichbar mit einer Kompletten Kopie einer Table-Row. Alte Revisionen können bei Bedarf entsorgt werden.

Wenn man das ganze selbst in einer Datenbank implementieren möchte, dann ist das auch möglich, allerdings aufwendig und es gibt einiges was schiefgehen kann. Vorallem wenn das ganze konstisten bleiben soll. Man führt parallel eine Historientabelle, die man auch zum Beispiel mit Triggern updaten kann. Wenn nicht, dann ist die Wahrscheinlichkeit hoch, dass da irgendwas daran vorbei geht und man ein Update nicht mitbekommt. Das grösste Problem wird das wiederherstellen eines bestimmten Zeitpunkts. Schemaänderungen stelle ich mir katastrophal vor.


#4

Falls du sowieso Hibernate verwendest, könnte vielleicht Hibernate Envers das Richtige für dich sein: http://hibernate.org/orm/envers/

Aus der Feature-Beschreibung:


#5

Möglicherweise wäre auch was in Richtung ein Event-Sourcing interessant, das würde allerdings noch eine Ebene höher ansetzen.


#6

Hallo @all,

danke für eure Tipps.
Also meine Frage zielte in erster Linie darauf ab, die DB-Struktur zu bestimmen. Welche Libs ich dann in Java verwende um an die Daten zu kommen und diese aufzubereiten, ist für mich erst mal kein Thema.

In Sachen DB habe ich klare Vorgaben, die MsSQL heißen. Ich darf die DB nicht umkonfigurieren. Mir steht lediglich ein leeres Schema zur Verfügung in dem ich agieren darf. Somit fallen andere DB-System als Alternative aus.

Ich werde wohl oder übel um eine manuelle Implementierung nicht herrum kommen. Ich hatte gehofft, das MsSQL hierfür unterstützende Funktionen mitbringt.


#7

Ich würde bei so etwas auch eher mit Triggern in der DB arbeiten. Hibernate Envers funktioniert zwar, aber m.M.n. ist es ein Nachteil dass die Versionierung Javaseitig erfolgt und nicht in der DB. Ich weiß nicht welche Last auf deine Anwendung kommen wird und ob es überhaupt relevant wird, aber wenn du die Versionierung in der Software durchführst, dann schickst du auch die doppelten Queries über die Leitung.


#8

Wen ich mich recht entsinne, lässt Hibernate Envers alle Originaltabellen so wie sind, und legt zu jeder eine History-Tabelle an. Ich vermute mal, dass das auch in einem Extra-Schema erfolgen kann, was dann deinen Anforderungen entsprechen würde, jedenfalls wenn nur per Java auf die DB zugegriffen wird.


#9

Ein Punkt den man bei Envers abklären sollte ist, ob es ausreichend ist eine Versionierung nach Revisionen zu haben oder wie im ersten Post zu lesen eine Rekonstruktion zu einem bestimmten Zeitpunkt (nach meinem Verständnis Timestamp) zu machen und wie das möglich ist.

Der Zweite Punkt ist die Frage nach Schemaänderungen. Sind diese zu erwarten, wird das unterstützt und wie soll damit umgegangen werden.


#10

@NetHans : Eigentlich müsstest du so etwas angeben:

use der_name_der_test_database;

DROP TABLE IF EXISTS Seite;
DROP TABLE IF EXISTS Buch;

CREATE TABLE Buch (
    BuchID int PRIMARY KEY AUTO_INCREMENT,
    Autor varchar(255) NOT NULL DEFAULT 'unbekannter Autor',
    Title varchar(255) NOT NULL DEFAULT 'unbekannter Titel'
);

CREATE TABLE Seite (
    SeiteID int PRIMARY KEY AUTO_INCREMENT,
    BuchID int NOT NULL,
    Seite text NOT NULL,
    FOREIGN KEY(BuchID) REFERENCES Buch(BuchID)
);

SHOW FULL TABLES;
SHOW FULL COLUMNS FROM Buch;
SHOW FULL COLUMNS FROM Seite;

und dann danach fragen, wie das zu sichern wäre - wenn ein neues Buch oder eine neue Seite hinzugefügt wird.

Ich würd sagen, es gibt 3 Möglichkeiten: vollständig, inkrementell oder differentiell - wobei vollständig vielleicht noch am einfachsten.


Edit 2: Sorry, ich hab MSSQL (das s wurde fälschlicherweise kleingeschrieben) mit MySQL verwechselt. Die Syntax von MSSQL ist komplett anders. D. h., mein Posting ist deplatziert. Bitte entschuldigt und entfernt es, wenn nötig.