Einfügen wenn die Daten mit sich selbst verknüpft sind?

jmar83 · 4 August 2015

Hallo zusammen
Ich habe eine Tabelle wo sich Einträge drin befinden welche mit sich selbst verknüpft sind. (Baumstruktur)

Nun habe ich den FK, Feld "NodeOf", aus div. Gründen über einen Trigger implementiert. (BEFORE INSERT/UPDATE/DELETE)

Die verknüpften Daten kann ich zwar exportieren, allerdings habe ich dann das Problem dass im INSERT INTO-Statement im exportierten SQL zuerst die Datensätze drin sind welche auf einen "NodeOf"-Eintrag referenzieren welcher erst später im INSERT INTO drin steht...

Den BEFORE INSERT-Trigger zu deaktivieren und später wieder zu aktivieren wäre eine Möglichkeit. Und am besten wohl die 3 Sachen

Trigger ausschalten
Daten einfügen
Trigger einschalten

in einer Transaktion ausführen.

Oder kann mir jemand einen besseren Tipp geben?

Danke für die Feedbacks.

Grüsse,
Jan

akretschmer · 4 August 2015

Der Dump, der eine DB erstellt, sollte konsistent und ohne Änderung am Dump selbst direkt einlesbar sein.

jmar83 · 4 August 2015

Dann darf die Spalte kein Foreign Key sein und alles soll akzeptiert werden?

Irgendwie findet ein INSERT bei SQL Server mit mehreren Datensätzen auch nicht "atomar" statt. Obwohl man nur 1 INSERT hat, wird jede "Zeile" irgendwie separat abgearbeitet...

Ist das Problem, dass ich meine FK-Funktionalität über 3 before-Trigger regle?

Wie würde ich ein solcher INSERT bei einer InnoDB-Tabellen mit einem als FK deklarierten Feld verhalten?

Irgendwie habe ich das Gefühl, mein Problem sei so nicht lösbar...

Oder dafür sorgen, dass der SQL-Dump die Knoten vom Tree von quasi "oben nach unten" einfügt?

Grüsse, Jan

Distrilec · 4 August 2015

jmar83 schrieb:
Dann darf die Spalte kein Foreign Key sein und alles soll akzeptiert werden?

Ein Trigger ist KEIN FK... Nur um das klarzustellen...

jmar83 · 4 August 2015

Im Moment habe ich folgendes

Code:

/*
SET @t_adressen_delete_disabled = 1;
DELETE FROM adressen;
SET @t_adressen_delete_disabled = 0;
*/

TRUNCATE adressen;

SET @t_adressen_insert_disabled = 1;
INSERT INTO...
SET @t_adressen_insert_disabled = 0

Die Trigger berücksichtigen diese Variablen beim auslösen

TRUNCATE ist doch zumindest besser für's löschen als der auskommentierte "DELETE FROM..."-Block...;-)

Grüsse,
Jan

jmar83 · 4 August 2015

"Ein Trigger ist KEIN FK... Nur um das klarzustellen..."

Der Trigger selbst natürlich nicht, dieser stellt aber sicher dass sich das Feld wie ein FK (allows NULL) verhält.

BEFORE INSERT/UPDATE: prüfen ob der gewünschte NodeOf-Wert sich tatsächlich in der gleichen Tabelle als PK befindet
BEFORE DELETE: löschen verweigern wenn der PK des zu löschenden Datzensatzes referenziert ist / bei anderen Datensätzen als NodeOf eingetragen ist (resp. Unterkonten vorhanden sind....) = analog. "DELETE RESTRICT ON DELETE"

Nannte man "Foreign Keys" nicht "Foreign Keys", als es noch keine Datenbanken gab wo man Felder "direkt" als Foreign Key deklarieren konnte? (Sondern das wie ich über Trigger realisiert hat)

Ich glaube es gibt sogar Datenbanken welche nicht mal PK's deklarations-mässig unterstützen, sondern wo man nebenbei eine Tabelle mit den aktiven und bereits "verbrauchten" Keys. (Neuer Key = letzter Key-INT-Wert++ aus der Key-Tabelle) führen muss...?

Grüsse, Jan

akretschmer · 4 August 2015

jmar83 schrieb:
Ich glaube es gibt sogar Datenbanken welche nicht mal PK's deklarations-mässig unterstützen, sondern wo man nebenbei eine Tabelle mit den aktiven und bereits "verbrauchten" Keys. (Neuer Key = letzter Key-INT-Wert++ aus der Key-Tabelle) führen muss...?

Wie ein PK intern realisiert wird ist Aufgabe der DB und von der SQL-Spec nicht vorgegeben. Oft wird es via Index realisiert. BTree.

jmar83 · 4 August 2015

Hauptsache den Key nicht nochmal vergeben, ist das Wichtigste würde ich sagen.

Zur Foreign Key-Sache: Inzwischen habe ich die Tabelle von "MyISAM" nach "InnoDB" konvertiert... als ich erfahren habe dass nicht mal Transaktionen unterstützt werden bei MyISAM. (Und sowas mit PHP nachzuprogrammieren, nein danke!! Bei Java/J2EE gibt's wohl sicher irgendwelche DB-Abstraktions-Frameworks, welche Transaktionen und Rollbacks auf Java-Ebene realisieren)

Kann es Probleme mit den Daten oder mit der PHP-Anwendung oder dem Abfrage-SQL-"Dialekt" geben wenn man einfach mal so nach "InnoDB" wechselt?

Grüsse, Jan

akretschmer · 4 August 2015

Sei jetzt bitte ganz tapfer: auch InnoDB z.B. kann keine Check-Constraints abbilden. Sie werden akzeptiert, aber nicht erzwungen. Bitter, aber wahr. Bei MySQL. Sondermüll, seit 30 Jahren, oder so, ...

jmar83 · 4 August 2015

"The CHECK clause is parsed but ignored by all storage engines."

Zum Vergrößern anklicken....

(http://dev.mysql.com/doc/refman/5.1/en/create-table.html)

Also nicht nur bei "InnoDB"...

Halb so tragisch, denn ich kannte das bisher gar nicht...

Gruss, Jan

akretschmer · 4 August 2015

jmar83 schrieb:
Halb so tragisch,

Bin ich anderer Meinung, aber egal ...

jmar83 · 4 August 2015

Ja wäre vielleicht eleganter als mein NodeOf-FK-Trigger, welcher folgendes akzeptiert:

- 0 (kein node)
- -1 (root node)
- null (kein node, aber wegen einer "inkompatibilität" mit dem gui kann ich zwar keine null-werte verwenden, wird aber per sql erlaubt, das gui fügt aber einfach 0 ein wenn es kein node ist)
- Gültiger PK als "FK"

Aber mit diesem MySQL-bashing kann ich nicht viel anfangen, ich finde SQL Server persönlich auch "besser". Postgre kenne ich nicht, vor Jahren habe ich dafür mal Ingres angeschaut. (Darauf basiert ja Postgres)

- SAP MaxDB ist sicher auch ziemlich "professionell", angesichts der Tatsache dass es für SAP verwendet wird. Vor allem ist es 100% gratis wenn es nicht mit SAP verwendet wird. (Und keine Express oder Lite Versionen) Aber kaum jemand scheint das zu verwenden...
- Bei der Gratis-Version von Oracle kann man max. 1 DB anlegen, fällt also aus.
- Ingres ist irgendwie komplett veraltet.
- SQL Server Express Edition macht schon vieles besser als MySQL
- In der Java-Welt wird für kleinere Sachen auch gerne mal HSQLDB verwendet, ist auch in Java geschreiben und kann auch "embedded" mitgeliefert werden.

Was von mir aus gesehen dagegen spricht, Postgres zu lernen:
- Die meisten Opensource-Webapps sind für MySQL ausgelegt. Da kommt man nicht drum herum. Ich denke dass ich da mit MySQL deutlich weiter komme...
- Bei Firmen wird für Enterprise-Anwendungen eher SQL Server (oder sonst was kommerzielles) als Postgres verwendet.

Mag sein dass Postgres in technischer hinsicher fortgeschrittener als manch andere DB. (?)

EIGENTLICH komme ich praktisch mit jedem DB-System klar wenn es unbedingt sein muss... mache sind eifach "mühsamer" als andere...

Gruss, Jan

jmar83 · 6 August 2015

Dieses Script generiert INSERT INTO-Code, welcher "von oben nach unten" einfügt:

Code:

select sqlInsert from (

select
concat(
     concat(
       concat(
         concat(
               concat(
                   concat(
                   concat(
                                   concat(
                   concat(
                         concat(
                                       concat(
                         concat(
                                                 concat(
                                                  concat(
                                                 concat(
                                                  concat(
                                                 concat(
                                       concat(
                         concat(                         concat(
                                       concat(
                         concat(
                                                 concat(
                                       concat(
                         concat(
                                                 concat(
                                       concat(
                         concat(
                                                 concat(
                                       concat(
                                                             concat(
                                                 concat(
                                       concat(
                                                             concat(
                                                 concat(
                                       concat(
                         concat(
                                                 concat(
                                       concat(
                         concat(
                                                                     concat(
                                       concat(
                         concat(
                                                                     concat(
                                       concat(
                         concat(
               concat(
                   concat(
                   concat(
                               concat(
                   concat(
         concat(
       concat(
         concat(
           concat(
             concat(
               concat(
                 concat('
                      INSERT into adressen (
                            adr_id,anrede,vorname,nachname, plz, ort, strasse, web1, mail1, web2, mail2, geburtstag, telefon1, handy1, abteilung, beruf, branche, fax, filter_id, urgend_id,user_sperre,user_id,infos,Firma,Kanton, Skype, NodeOf, MIG_UID,MIG_extension)
                        VALUES ('
                 ,adressen1.adr_id)
               ,', "')
             ,IFNULL(adressen1.anrede, ''))
           ,'", "')
         ,IFNULL(adressen1.vorname, ''))
          
            ,'", "')
         ,IFNULL(adressen1.nachname, ''))
       
       
                    ,'", ')
         ,IFNULL(adressen1.plz, 0))
       
                            ,', "')
         ,IFNULL(adressen1.ort, ''))
       
                     ,'", "')
         ,IFNULL(adressen1.strasse, ''))
       
                                    ,'", "')
         ,IFNULL(adressen1.web1, ''))
       
                     ,'", "')
         ,IFNULL(adressen1.mail1, ''))
                              ,'", "')
         ,IFNULL(adressen1.web2, ''))
                                       ,'", "')
         ,IFNULL(adressen1.mail2, ''))
                              ,'", "')
         ,IFNULL(adressen1.geburtstag, ''))
                              ,'", "')
         ,IFNULL(adressen1.telefon1, ''))
                                       ,'", "')
         ,IFNULL(adressen1.handy1, ''))
                                       ,'", "')
         ,IFNULL(adressen1.abteilung, ''))
                              ,'", "')
         ,IFNULL(adressen1.beruf, ''))
                                       ,'", "')
         ,IFNULL(adressen1.branche, ''))
                                                ,'", "')
         ,IFNULL(adressen1.fax, ''))
                              ,'", "')
         ,IFNULL(adressen1.filter_id, ''))
                                       ,'", "')
         ,IFNULL(adressen1.urgend_id, ''))
                                                         ,'", "')
         ,IFNULL(adressen1.user_sperre, ''))
                                                                  ,'", ')
         ,IFNULL(adressen1.user_id, 0))
                              ,', "')
         ,IFNULL(adressen1.infos, ''))
                                       ,'", "')
         ,IFNULL(adressen1.Firma, ''))
                                                                  ,'", "')
         ,IFNULL(adressen1.Kanton, ''))
                              ,'", "')
         ,IFNULL(adressen1.Skype, ''))
                                       ,'", ')
         ,IFNULL(adressen1.NodeOf, 0))
                                       ,', ')
         ,IFNULL(adressen1.MIG_uid, 0))
                                       ,', ')
         ,IFNULL(adressen1.MIG_extension, 0))
       ,');') AS sqlInsert ,getFamilyTree(adressen1.adr_id) AS familyTree,getAncestry(adressen1.adr_id) AS ancestry,
       LENGTH(getAncestry(adressen1.adr_id)) - LENGTH(REPLACE(getAncestry(adressen1.adr_id), ',', '')) + 1 AS tree_level
FROM
       adressen AS adressen1
     
     
LEFT JOIN adressen adressen2
    ON adressen1.NodeOf = adressen2.adr_id

) as test1

ORDER BY
    tree_level ASC;

Aber String-Concatenation ist echt beschissen in MySQL (habe einen grossen Teils des Codes per Copy/Paste/Replace erstellt), aber wirklich...

getAncestry:

Code:

DELIMITER //

DROP FUNCTION IF EXISTS getAncestry;

CREATE FUNCTION getAncestry (GivenID INT) RETURNS varchar(21844) CHARSET utf8
DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(1024);
    DECLARE cm CHAR(1);
    DECLARE ch INT;

    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    WHILE ch > 0 DO
        SELECT IFNULL(nodeOf,-1) INTO ch FROM
        (SELECT nodeOf FROM adressen WHERE adr_id = ch) A;
        IF ch > 0 THEN
            SET rv = CONCAT(rv,cm,ch);
            SET cm = ',';
        END IF;
    END WHILE;
    RETURN rv;
END; //

DELIMITER ;

Quelle: http://dba.stackexchange.com/questi...-of-a-hierarchical-field-with-vs-without-ctes

...die comma-separated "Ancestries" zu zählen und damit das level herauszufinden und dann danach zu sortieren, das scheint mir angebracht.

Und hier noch eine Funktion, welche alle Unterknoten ausgibt:

Code:

DELIMITER //

DROP FUNCTION IF EXISTS getFamilyTree;

CREATE FUNCTION getFamilyTree (GivenID INT) RETURNS varchar(21844) CHARSET utf8
DETERMINISTIC
BEGIN

    DECLARE rv,q,queue,queue_children VARCHAR(1024);
    DECLARE queue_length,front_id,pos INT;

    SET rv = '';
    SET queue = GivenID;
    SET queue_length = 1;

WHILE queue_length > 0 DO
    IF queue_length = 1 THEN
    SET front_id = queue;
        SET queue = '';
    ELSE
    SET front_id = SUBSTR(queue,1,LOCATE(',',queue)-1);
        SET pos = LOCATE(',',queue) + 1;
        SET q = SUBSTR(queue,pos);
        SET queue = q;
    END IF;
        SET queue_length = queue_length - 1;

        SELECT IFNULL(qc,'') INTO queue_children
        FROM (SELECT GROUP_CONCAT(adr_id) qc
        FROM adressen WHERE NodeOf = front_id) A;

        IF LENGTH(queue_children) = 0 THEN
            IF LENGTH(queue) = 0 THEN
                SET queue_length = 0;
            END IF;
        ELSE
            IF LENGTH(rv) = 0 THEN
                SET rv = queue_children;
            ELSE
                SET rv = CONCAT(rv,',',queue_children);
            END IF;
            IF LENGTH(queue) = 0 THEN
                SET queue = queue_children;
            ELSE
                SET queue = CONCAT(queue,',',queue_children);
            END IF;
            SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
        END IF;
    END WHILE;

    RETURN rv;

END; //
DELIMITER ;

Quelle: http://dba.stackexchange.com/questi...-of-a-hierarchical-field-with-vs-without-ctes

Grüsse, Jan

Distrilec · 6 August 2015

Eine hässliche Lösung für eine hässliche Anforderung... Wie immer...
Anstatt einfach mal den richtigen Weg zu gehen, nimmt man lieber den mit dem geringsten Widerstand... Bis einem irgendwann das Kartenhaus zusammenbricht...

Hab Spaß mit deiner "Lösung"

jmar83 · 6 August 2015

Immerhin weiss ich mir meist zu helfen, ziemlich einige andere (reine) DB-Entwickler würden nicht auf solche Ideen kommen... als SW-Entwickler hat man da halt schlicht eine andere Sicht...;-)

Was wäre dann die Lösung?

- Richtige FK's (habe nun eh ne InnoDB-Tabelle)? Die würde glaube ich auch keinen Stress machen wenn man den Dump wieder einliest, wird aber auch realisiert indem beim Dump vorher und nachher was gesetzt wird:

Code:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40000 ALTER TABLE `adressen` DISABLE KEYS */;

INSERT INTO...

/*!40000 ALTER TABLE `adressen` ENABLE KEYS */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

- Meine Trigger... Gross anders ist das ja auch nicht, auch bei der "richtigen" FK-Lösung müssen irgendwelche Sachen ein- und ausgeschaltet werden:

Code:

SET @t_adressen_insert_disabled = 1;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40000 ALTER TABLE `adressen` DISABLE KEYS */;

INSERT INTO...

/*!40000 ALTER TABLE `adressen` ENABLE KEYS */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

SET @t_adressen_insert_disabled = 0;

- Mein INSERT INTO-Generate-Script macht von Anfang an alles richtig von der Reihenfolge her...

Grüsse,
Jan

Einfügen wenn die Daten mit sich selbst verknüpft sind?

jmar83

SQL-Guru

akretschmer

Datenbank-Guru

jmar83

SQL-Guru

Distrilec

Datenbank-Guru

jmar83

SQL-Guru

jmar83

SQL-Guru

akretschmer

Datenbank-Guru

jmar83

SQL-Guru

akretschmer

Datenbank-Guru

jmar83

SQL-Guru

akretschmer

Datenbank-Guru

jmar83

SQL-Guru

jmar83

SQL-Guru

Distrilec

Datenbank-Guru

jmar83

SQL-Guru

Ähnliche Themen