Sync/Abgleich zweier Datenbanken - ATTACH, JOIN, etc

[Agenor]

Hallo!

Auf zwei unterschiedlichen Rechner läuft ein Programm, das zur Verwaltung seiner Daten eine eigene, lokale SQLite Datenbank verwendet. Die beiden Datenbanken von den Rechner A und B haben also die gleiche Struktur, jedoch (teilweise) unterschiedliche Daten.

Ich suche nun nach einer Möglichkeit die beiden Datenbanken zu synchronisieren. Es genügt zunächst einmal der einfacherer Fall der Synchronisation in eine Richtung, es sollen also die Daten von Datenbank B in A eingefügt werden.

Natürlich ist es kein Problem die alle Einträge einer Tabelle in DB A zu durchlaufen und mit allen Einträgen in der gleichen Tabelle in DB zu vergleichen. Das Ganze sollte aber etwas effizienter ablaufen.


All Tabellen sind nach dem gleichen Muster aufgebaut und haben neben einer ID als Primärschlüssel auch eine GUID die einen Eintrag eindeutig identifiziert. Zudem hat jeder Eintrag eine "Version". Das ist einfach eine zufällige Zeichenfolge, die beim Erstellen eines Eintrags und bei jeder Änderung verändert wird.

Beispiel:

Tabelle "Person"
  ID
  GUID
  Name
  Adresse
  GruppenGUID
  Version
 
Tabelle "Gruppe"
  ID
  GUID
  Name
  Version

Datenbank A
  Tabelle "Person"
    1    AAA    Meyer    G1    xyz
    2    BBB    Müller   G1    abc
 
  Tabelle "Gruppe"
    1    G1    Gruppe1    def
    2    G1    Gruppe2    dge
 
 
Datenbank B
  Tabelle "Person"
    1    HHH    Hinz     G1    ghu
    2    BBB    Müller   G2    vfh
    3    AAA    Meyer    G1    xyz
 
  Tabelle "Gruppe"
    1    G1    Gruppe1    def
    2    G1    Gruppe2    dge
    2    G3    Gruppe3    ttz

Nun sollen die Daten von B nach A übertragen werden. Hierbei gilt:

1. Die ID hat hierfür keine Bedeutung. In B gibt es die gleichen Personen wie in A, aber mit anderen IDs. Gleiche Einträge werden über die GUID identifiziert.
2. Alle Einträge einer Tabelle aus DB B mit einer GUID die es in der gleichen Tabelle in DB A nicht gibt müssen 1:1 in A eingefügt werden.
3. Das Feld Version ist ein Optimierung für den Sync. Von den Einträgen die es sowohl in A als auch in B gibt müssen nur solche mit unterschiedlicher Version berücksichtigt werden. Für die Tabelle "Person" muss also "BBB Müller" von B nach A kopiert werden (geänderte Gruppe und damit geänderte Version), Person "AAA Meyer" aber nicht (keine Änderung, gleiche Version).

Soviel zur Theorie. In der Praxis fehlt mir etwas Know-How um möglichst viel Arbeit von SQL erledigen zu lassen.

Beide Datenbank Dateien werden auf den gleichen Rechner kopiert, eine Verbindung zu DB A aufgebaut und die Datenbank B per ATTACH an diese Verbindung angehängt.


Fragen:
1. Mit welchem SQL Befehl wähle ich alle Einträge in Tabelle "datenbankB.Person" aus, die nicht in Tabelle "Person" enthalten sind? Kann das Ergebnis dieser Abfrage direkt per SQL in "Person" eingefügt werden oder muss das Ergebis Eintrag für Eintrag durchlaufen werden um diese dann einzeln per INSERT einzufügen?

2. Mit welchem SQL Befehl wähle ich alle Einträge in Tabele "datenbankB.Person" aus, die auch in Tabelle "Person" enthalten sind aber dort eine andere Version haben? Kann das Ergebnis dieser Abfrage direkt per SQL in "Person" eingefügt werden oder muss das Ergebis Eintrag für Eintrag durchlaufen werden um diese dann einzeln per INSERT einzufügen?


Vielen Dank!
Agenor

 

[akretschmer]

Hallo!

Auf zwei unterschiedlichen Rechner läuft ein Programm, das zur Verwaltung seiner Daten eine eigene, lokale SQLite Datenbank verwendet. Die beiden Datenbanken von den Rechner A und B haben also die gleiche Struktur, jedoch (teilweise) unterschiedliche Daten.

Hast Du nun MySQL oder SQLite?

Das ist u.U. schon mal wichtig zu wissen.

Fragen:
1. Mit welchem SQL Befehl wähle ich alle Einträge in Tabelle "datenbankB.Person" aus, die nicht in Tabelle "Person" enthalten sind? Kann das Ergebnis dieser Abfrage direkt per SQL in "Person" eingefügt werden oder muss das Ergebis Eintrag für Eintrag durchlaufen werden um diese dann einzeln per INSERT einzufügen?

2. Mit welchem SQL Befehl wähle ich alle Einträge in Tabele "datenbankB.Person" aus, die auch in Tabelle "Person" enthalten sind aber dort eine andere Version haben? Kann das Ergebnis dieser Abfrage direkt per SQL in "Person" eingefügt werden oder muss das Ergebis Eintrag für Eintrag durchlaufen werden um diese dann einzeln per INSERT einzufügen?


Vielen Dank!
Agenor

Schau Dir mal bitte das an:

http://www.postgresql.org/docs/9.2/interactive/queries-union.html Insbesondere intersect und except sind da für Dich interessant. Ich weiß aber nicht, ob SQLite diese Befehle kennt, MySQL kennt wohl insersect und except NICHT. Ob Du dafür temporäre Zwischentabellen benötigst oder nicht hängt auch sehr stark vom DB-System ab. Unter PostgreSQL könnte man Deine Probleme mittels writeable CTE, was hier hier https://www.datenbankforum.com/threads/erklärt-writeable-common-table-expressions.986/ schon mal vorgestellt habe, sehr wahrscheinlich in einem einzigen SQL lösen. Was Du aber generell nicht brauchst: eine Ergebnissmenge Eintrag für Eintrag zu durchlaufen und in einer Schleife oder so einzelne Selects oder Inserts loszutreten. Einfache Faustregel: niemals nie SQL in Schleifen ausführen.

 

[Agenor]

Hallo,

vielen Dank für die Antwort. Ich verwende SQLite. Das hätte ich natürlich deutlicher sagen müssen, sorry.

Für meine beiden Fragen konnte ich mir mittlerweile folgende beide Abfragen erarbeiten:

SELECT * FROM SyncDB.Table1 WHERE SyncDB.Table1.GUID NOT IN (SELECT GUID FROM Table1)
 
SELECT * FROM SyncDB.Table1 JOIN Table1 ON SyncDB.Table1.GUID = Table1.GUID WHERE SyncDB.Table1.VersionKey != Table1.VersionKey

Das klappt soweit ganz gut, es werden die richtigen Ergebnisse geliefert. Nun kann ich die Ergebnisse natürlich Zeile für Zeile durchlaufen und einzeln in INSERTs oder UPDATEs stecken. Schöner wäre es natürlich die Ergebnisse direkt in SQL weiter verwenden zu können. Hierzu habe ich folgendes Beispiel gefunden:

UPDATE
    Table1
SET
    col1 = SyncDB.Table1.col1,
    col2 = SyncDB.Table1.col2
FROM
    Table1
INNER JOIN
    SyncDB.Table1
ON
    Table1.GUID = SyncDB.Table1.GUID

Das sieht sehr vielversprechend aus und wäre genau was ich suche, funktioniert aber leider nicht. Hier meldet mir SQLite einen Syntax Fehler beim FROM. Weiß jemand ob diese Art der Abfrage bei SQLite generell nicht möglich ist, oder ob ich hier einen Fehler mache?

Damit das Ganze wirklich perfekt wäre, müsste man die Abfrage natürlich noch um einen Test der Versionen ergänzen. Zudem muss die Abfrage in dieser Form für jede Tabelle speziell mit den jeweiligen Spaltennamen erstellt werden. Gibt es auch ein Update der Form:

UPDATE
  Table1
SET
  "Alle Spalten in Table1 außer der ID-Spalte" = "Jeweilige Spalte aus Tabelle SyncDB.Table1"
FROM/WHERE
  ...