FB: Kopie eines Datensatzes in Tabelle mit unbekannter Struktur erzeugen

[RSE]

Hallo,

ich möchte in einer unbekannten Tabelle temporär einen Datensatz kopieren und dabei die Schlüsselfelder ändern.

Hintergrund:
Wir haben in unserer DB eine Tabelle in der Keys für alle Einrichtungen hinterlegt sind. In allen anderen Tabellen, in denen Einrichtungen vorkommen, ist dieser Key als Foreign Key referenziert, teils mehrstufig. Nun kommt es vor, dass eine neue Einrichtung mit Key 2 angelegt wird, die es eigentlich bereits gibt (mit Key 1), weil vorher schlecht gesucht wurde. Dann müssen diese 2 Einrichtungen zusammengelegt werden. Nach dem Abgleich der Daten zu den IDs müssen alle Referenzen auf Key 2 auf Key 1 umgebogen/geändert werden, um anschließend Key 2 löschen zu können. Das Umbiegen einer mehrstufigen Abhängigkeit ist nicht ganz trivial:

• Tab0 enthält die Schlüssel
• Tab1 ist die 1. Abhängigkeit (enthält einen FK auf Tab0)
• Tab2 ist die 2. Abhängigkeit (enthält einen FK auf Tab1)

Der Algorithmus startet auf Tab0, dort existieren Key1 und Key 2 bereits, alle bestehenden Keys 2 sollen auf Key 1 umgebogen/geändert werden. Tab1 und Tab2 enthalten jeweils nur Key 2. Wird nun Tab1 zuerst geändert, schlägt das fehl, weil in Tab2 der Foreign-Key-Constraint sofort anschlägt (Key 2 ist dort immernoch referenziert). Wird Tab2 zuerst geändert, schlägt der Foreign-Key-Constraint ebenso an, weil in Tab1 Key 1 noch nicht existiert. Also müssen in Tab1 übergangsweise beide Keys existieren.

Der Algorithmus arbeitet potentiell auf allen Tabellen in der DB. Welche davon einen FK auf die Basistabelle (Tab0) haben, bekomme ich in den Systemtabellen heraus. Wenn eine andere Tabelle einen Foreign Key auf die aktuelle Tabelle hat, dann ist das aktuelle Feld in der aktuellen Tabelle unique. Die restliche Struktur kenne ich nicht. Mein Vorgehen im oben skizzierten Fall ist wie folgt:

1. Ich erzeuge in Tab1 eine Kopie des Datensatzes mit dem Key. Dabei müssen alle Felder kopiert werden, für den Fall dass weitere Abhängigkeiten existieren, und alle Felder mit einem Unique-Index (in Systemtabellen zu finden) geändert werden.
2. Dann rufe ich meine Procedure rekursiv auf Tab1 auf und ändere den Key auf den temporären Key.
3. Daraufhin ist der ursprünglichen Datensatz mit Key 2 frei und kann auf Key 1 geändert werden.
4. Nun rufe ich meine Procedure nochmals rekursiv auf, um den temporären Key in den abhängigen Tabellen (ab Tab2) auf den Key 1 zu ändern.
5. Schlussendlich kann der temporäre Datensatz gelöscht werden.

Da das Ganze in einer Transaktion abläuft, ist die einzige Differenz zwischen vorher und nachher der geänderte Key. ich brauche mir also keine Gedanken um Generatoren zu machen, die zwischenzeitlich den selben Wert wie in dem temporären Datensatz geliefert hätten. Dieser Wert ist nach der Transaktion wieder valide.

Das Problem ist also das Kopieren aller Werte eines Datensatzes mit Ausnahme der Felder mit einem Unique Key. In MySQL kann ich hier mit temporären Tabellen arbeiten, die offenbar nur im Speicher angelegt werden. Wie ist das mit diesen Global Temporary Tables? Werden die bis zum committen auch erstmal im Speicher gehalten, oder wird da sofort eine Datei angelegt? Das würde natürlich zu erheblichen Performance-Einbußen führen, so dass ich lieber aus den Systemtabellen alle Felder von Tab1 ermittle und mir ein SQL zusammenschustere, das alle Felder enthält, die ich kopieren möchte. Aber das ergibt eben mehr SQL-Abfragen als die temporäre Tabelle und es ist weniger gut lesbar was da passiert.

Vielleicht gibt es aber auch noch eine ganz andere Lösung, ich bin gespannt auf jede Art von Response.

[shmia]

http://www.delphipraxis.net/17291-ak...plizieren.html

[Sir Rufo]

Kannst du mal das Szenario mit Beispieldaten beleben und ein vorher/nacher zeigen.

IMHO denkst du da gerade zu kompliziert

[RSE]

Ich will eine PSQL-Procedure schreiben, die für eine Tabelle einen Wert ändert, der in vielen weiteren Tabellen als Foreign Key "verlinkt" ist. Deshalb ist der Ansatz, den shmia verlinkt hat, nicht umsetzbar.

Beispiel:
Tab0:

Code:

ID
1
2
3
5

Tab1 (FK: ID references Tab0.ID):

Code:

ID   Wert
1   bla
2   foo
5   foo

Tab2 (FK: ID references Tab1.ID):

Code:

ID   Zusatzwert
2   zweiundvierzig
5   foobar

OT: Wie kann man hier Tabellen ordentlich formatieren?

Tab0.ID = 5 ist der doppelte Datensatz zum Original Tab0.ID = 3. Tab0.ID = 5 soll also gelöscht werden, alle Referenzen auf Tab0.ID = 5 sollen umgebogen werden auf Tab0.ID = 3. Tab0.ID = 3 hat in weiteren hier nicht dargestellten Tabellen Referenzen/Abhängigkeiten, so dass Tab0.ID = 3 nicht gelöscht werden kann, um Tab0.ID = 5 einfach zu ändern in Tab0.ID = 3. Ergebnis soll folgendes sein:
Tab0:

Code:

ID
1
2
3

Tab1 (FK: ID references Tab0.ID):

Code:

ID   Wert
1   bla
2   foo
3   foo

Tab2 (FK: ID references Tab1.ID):

Code:

ID   Zusatzwert
2   zweiundvierzig
3   foobar

Um dieses Ergebnis zu erreichen müssen in Tab1 und Tab2 Datensätze geändert werden, die abhängig voneinander sind, das geht nur über den Zwischenschritt eines temporären Datensatzes (Erklärung siehe erster Post).
Meine Prozedur: ChangeForeignKeys(Tabelle, Feld, WertAlt, WertNeu);
Eingabe im Beispiel für meine Prozedur: ChangeForeignKeys(Tab0, ID, 5, 3);
Die Prozedur iteriert über alle Foreign Keys anderer Tabellen in der DB, die auf Tab0.ID referenzieren (auch über mehrere Schritte).

Es geht um das Kopieren eines Datensatzes in einer Tabelle, dessen Struktur ich nicht näher kenne. Was ich über die Struktur herausfinden muss, kann ich in den Systemtabellen herausfinden, das Prinzip muss aber dynamisch sein, damit es auf andere Strukturen/andere Tabellen ebenso angewendet werden kann. Der Datensatz muss also komplett kopiert werden, dabei muss genau der Primary Key und das zu ändernde Feld (im Beispiel ID) geändert werden.

Mein aktueller (vielversprechender) Ansatz sieht so aus:

SQL-Code:

/*
str1, str2: temporäre strings
fkrel1: entspricht im Beispiel Tab1
fkfield1: entspricht im Beispiel ID
*/
      str1 = '';
      for select rdb$field_name
            from rdb$relation_fields 
            where rdb$relation_name = :fkrel1
            order by -- unfertig - Ordnung ist wichtig!
        into str2 do
      begin
        if (str1 <> '') then
          str1 = str1 || ', ';
        -- Eine Änderung aller uniques muss noch umgesetzt werden
        if (str2 = fkfield1) then
          str1 = str1 || '2'; -- hier muss ein sinniger temporärer Wert rein
        else
          str1 = str1 || str2;
      end
      execute statement 'insert into ' || fkrel1 ||
                        '  select ' || str1 ||
                        '    from ' || fkrel1 ||
                        '    where ' || fkfield1 || ' = ' || oldvalue;

[Olli73]

Zitat von RSE:

Tab1 (FK: ID references Tab0.ID):

Tab2 (FK: ID references Tab1.ID):

Du refernzierst beim FK in TAB2 auf den FK in TAB1 (und nicht auf einen PK von TAB1)?

Oder ist das eine 1:1(:1)-Beziehung mit PK=FK in Tab1 (und evtl. auch Tab2)?

Normalerweise hätte Tab1 einen eigenen PK (verdient) auf den in Tab2 refernziert wird und dann würde es genügen den FK in Tab1 anzupassen und alles was danachkommt (Tab2, ...) wäre nicht betroffen...

[RSE]

In diesem Beispiel wäre eine flachere Hierarchie durchaus denkbar, aber in der Praxis leider nicht. Ich komme um mehrstufige Abhängigkeiten nicht drumherum. In der Praxis habe ich z.B. Einrichtungen (Tab0, EinrID) mit Mitarbeitern (Tab1 mit FK (EinrID) auf Tab0, PK ist EinrID + MAID), die im Bestellwesen referenziert werden (Tab2 mit FK auf PK von Tab1 (EinrID + MAID)). Wenn sich also die EinrID wie im Beispiel ändert, wirkt sich das bis zu dem MA im Bestellwesen aus, eine flachere Abbildung ist logisch nicht sinnvoll.

[Sir Rufo]

(das war gerade mist)

Wenn du in den direkt referenzierten Tabellen ( Tab0 -> Tab1 ) den Wert änderst, dann sollten die anderen Werte automatisch nachfolgen.

In diesem Beispiel sollte also folgendes ausreichend sein:

Code:

UPDATE
  Tab1
SET
  ID = 3
WHERE
  ID = 5;

DELETE FROM
  Tab0
WHERE
  ID = 5;

[Olli73]

Zitat von RSE:

In diesem Beispiel wäre eine flachere Hierarchie durchaus denkbar, aber in der Praxis leider nicht. Ich komme um mehrstufige Abhängigkeiten nicht drumherum. In der Praxis habe ich z.B. Einrichtungen (Tab0, EinrID) mit Mitarbeitern (Tab1 mit FK (EinrID) auf Tab0, PK ist EinrID + MAID), die im Bestellwesen referenziert werden (Tab2 mit FK auf PK von Tab1 (EinrID + MAID)). Wenn sich also die EinrID wie im Beispiel ändert, wirkt sich das bis zu dem MA im Bestellwesen aus, eine flachere Abbildung ist logisch nicht sinnvoll.

Also ich mache das i.d.R. (es gibt Ausnahmen, wo es Sinn macht) so, dass dann in Tab1 der PK die MAID (alleine) ist und EinrID dort nur der FK; in Tab2 wird dann nur auf MAID referenziert. Vom Bestellwesen kommt man dann natürlich nur noch auf die Enrichtung, wenn die Mitarbeiter-Tabelle "zwischenschaltet", aber dafür minimiert es die Abhängigkeiten mit den IDs und ich brauche immer nur 1 Datenfeld für die Verknüfung der Tabellen.

[Blup]

Der Vorschlag von Olli73 sieht doch gar keine flachere Struktur vor.
Nur die Rolle die der Mitarbeiter einer Einrichtung einnimmt, sollte eine eigen ID bekommen...

Tabelle1
T1.ID PK

Tabelle2
T2.ID PK

Tabelle3 {Verknüpfung zwischen 1 und 2}
T3.ID PK
T1.ID FK UNQ
T2.ID FK UNQ

Tabelle4 {z.B. Bestellungen der Rolle in Tabelle3
T4.ID PK
T3.ID FK

Insbesondere wenn man mit einem OPF arbeitet, wird meist nur ein Feld als PK angenommen.

@Sir Rufo
Das funktioniert nicht, wenn ein Feld sowohl mit FK verknüpft ist und gleichzeitig Teil eines PK ist.

[Sir Rufo]

Zitat von Blup:

@Sir Rufo
Das funktioniert nicht, wenn ein Feld sowohl mit FK verknüpft ist und gleichzeitig Teil eines PK ist.

Ok, dann muss ich aber mal mit meinem MySQL Server schimpfen ... dann ist der wohl kaputt, denn der macht das genau so.

SQL Fiddle DEMO

Code:

CREATE TABLE `tab0` (
  `id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `tab1` (
  `id` int(11) unsigned NOT NULL,
  `Wert` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_tab1_tab0` FOREIGN KEY (`id`) REFERENCES `tab0` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tab2` (
  `id` int(11) unsigned NOT NULL,
  `Zusatzwert` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_tab2_tab1` FOREIGN KEY (`id`) REFERENCES `tab1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tab0 ( id ) VALUES (1),(2),(3),(5);
INSERT INTO tab1 ( id, wert ) VALUES (1,'bla'),(2,'foo'),(5,'foo');
INSERT INTO tab2 ( id, zusatzwert ) VALUES (2,'zweiundvierzig'),(5,'foobar');

UPDATE
  Tab1
SET
  ID = 3
WHERE
  ID = 5;

DELETE FROM
  Tab0
WHERE
  ID = 5;