Hierarchisch verknotete Datensätze mit SP nach XML umformen

jmar83 · 30 Juli 2015

Hallo zusammen
Ich bin gerade dran meine rekursive Funktion in eine MySQL Stored Procedure umzuformen (Stored Function kann ich scheinbar nicht verwenden, da Trigger in der gleichen DB sind...?)

Deshalb wird die Wertübergabe nun über eine String-Variable OUT (also by Reference wenn man so will) erfolgen.

Und: Die Funktion sollte rekursiv aufgebaut bleiben, ich weiss es gibt andere (evtl. bessere) Möglichkeiten das zu bewerkstelligen. (Ist halt nicht so performant, da immer wieder SELECT's abgesetzt werden, schon klar...)

"str" ist die variable welchee immer wieder neu übergeben wird und am schluss "by reference" geholt wird:

Code:

DELIMITER //

DROP PROCEDURE IF EXISTS `getNestedXMLTree`;

CREATE PROCEDURE `getNestedXMLTree` (IN parentNodeID INT, OUT str VARCHAR(21844) CHARACTER SET utf8)
BEGIN
  DECLARE done  INT DEFAULT FALSE;
   
  DECLARE _adr_id  INT;
  DECLARE _nachname  VARCHAR(64);
  DECLARE _nodeOf  INT;
   
  DECLARE _cur CURSOR FOR
  SELECT
  adressen.adr_id,
  adressen.nachname,
  adressen.nodeOf
   
  FROM
  CUST_v_adressen adressen
   
  WHERE
  adressen.nodeOf
   
  ORDER BY
  adressen.adr_id ASC;
   
   
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   
  SET @@SESSION.max_sp_recursion_depth = 255;
   
  IF ISNULL(str) THEN
  SET str = '';
  END IF;
   
  OPEN _cur;
  _loop: LOOP
  FETCH _cur INTO _adr_id, _nachname, _nodeOf;
  IF done THEN
  LEAVE _loop;
  END IF;
  IF (SELECT COUNT('') FROM CUST_v_adressen adressen WHERE adressen.nodeOf = _adr_id) > 0 THEN
     SET str = CONCAT(str, CONCAT('<entry value="', CONCAT(_nachname,'">')));
     CALL getNestedXMLTree(_adr_id, str);
     SET str = CONCAT(str, '</entry>');
  ELSE
     SET str = CONCAT(str, CONCAT('<entry value="', CONCAT(_nachname, '" />')));
  END IF;
  END LOOP;
  CLOSE _cur;
END; //
DELIMITER ;

Aufruf zum testen in der Konsole:

Code:

CALL getNestedXMLTree(1,@str);
SELECT @str;

Wenn ich nur 1 Ebene unter dem "Hauptknoten" habe, klappt alles. Ausgabe:

HTML:

<entry value="Jäggi" /><entry value="*" /><entry value="Simoes" /><entry value="Nünlist" /><entry value="Byland" /><entry value="Elbe" />

Wenn ich aber 2 Ebenen habe...

...komme eine Fehlermeldung ich solle den "thread_stack" erhöhen.

Ich frage mich, ob meine Funktion falsch ist, oder ob ich wirklich den "thread_stack" erhöhen muss?

Die gleiche Sache hatte ich hier mit SQL Server, da habe ich aber letztendlich eine UDF verwendet: https://www.datenbankforum.com/thre...men-und-per-varchar-max-output-ausgeben.2518/

Grüsse, Jan

jmar83 · 30 Juli 2015

Nun habe ich noch mal das MSSQL-Beispiel angeschaut, und noch etwas entdeckt was nicht gleich ist. Doch leider löst dies das PRoblem nicht, gleicht Threadstack_size-Meldung:

Code:

    OPEN _cur;
    FETCH _cur INTO _adr_id, _nachname, _nodeOf;
        _loop: LOOP
           
            IF done THEN
                LEAVE _loop;
            END IF;
            IF (SELECT COUNT('') FROM CUST_v_adressen adressen WHERE adressen.nodeOf = _adr_id) > 0 THEN
                SET str = CONCAT(str, CONCAT('<entry value="', CONCAT(_nachname,'">')));
                CALL getNestedXMLTree(_adr_id, str);
                SET str = CONCAT(str, '</entry>');
            ELSE
                SET str = CONCAT(str, CONCAT('<entry value="', CONCAT(_nachname, '" />')));
            END IF;
            FETCH _cur INTO _adr_id, _nachname, _nodeOf;
        END LOOP;
    CLOSE _cur;

GRüsse, Jan

Distrilec · 30 Juli 2015

5 Sekunden in der MySQL-Doku und ich weiß wo dein Problem ist...
https://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_thread_stack

der Doku schrieb:
If the thread stack size is too small, it limits the complexity of the SQL statements that the server can handle, the recursion depth of stored procedures, and other memory-consuming actions.

Was wäre nun die naheliegenste Lösung?

Richtig... Lernen wie sich deine Datenbank verhält (vllt. mal ne Schulung?...) Oder wen es den gibt, den zuständigen DBA ansprechen

jmar83 · 30 Juli 2015

Das fällt mir irgendwie schwer zu glauben dass 6 konten auf ebene 2 (1 Ebene unter dem Hauptknoten) sowie 2 Knoten auf Ebene 3 bereits ein Problem damit verursachen. So viele Rekursionen sind das nun auch wieder nicht...?

threadstack_size ist zur Zeit auf 192K eingestellt, das sollte doch reichen dafür?

Ich denke eher bei meinem Algorithmus läuft es ins unendliche weil irgendwo ein Fehler ist?

Grüsse,
Jan

akretschmer · 31 Juli 2015

Rekursive Abfragen bringen moderne Datenbanken out-of-the-box. Wenn Du sowas brauchst, warum nutzt Du dann eine DB, die das nicht kann?

jmar83 · 31 Juli 2015

MySQL könnte das sicher, es ist einfach interessanter die Sachen selbst zu programmieren! (wenn auch komplizierter und weniger performant - ich lerne zumindest mehr so!)

(WITH könnte was sein (?), eine "LEFT JOIN"-Lösung wäre eher weniger angebracht, ich will kein Query Resultset mit Ebenen, sondern einen hierarchisch aufgebauten XML-String, der dann auf PHP-Ebene noch entsprechend tranformiert wird um eine jQuery-Organigramm-Komponente damit zu "füttern"...)

Nun habe ich den thread_stack auf 512K erhöht, und erhalte immer noch folgende Fehlermeldung:

/* SQL Fehler (1436): Thread stack overrun: 399680 bytes used of a 524288 byte stack, and 128000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack. */

Grüsse,
Jan

jmar83 · 31 Juli 2015

Mit dem WHILE-Loop habe ich den gleichen Fehler:

Code:

  SET done = 0;
  OPEN _cur;
  _loop:WHILE(done = 0) DO
          FETCH _cur INTO _adr_id, _nachname, _nodeOf;
          IF done = 1 THEN
              LEAVE _loop;
          END IF;
         
         
          IF (SELECT COUNT('') FROM CUST_v_adressen adressen WHERE adressen.nodeOf = _adr_id) > 0 THEN
     SET str = CONCAT(str, CONCAT('<entry value="', CONCAT(_nachname,'">')));
     CALL getNestedXMLTree(_adr_id, str);
     SET str = CONCAT(str, '</entry>');
  ELSE
     SET str = CONCAT(str, CONCAT('<entry value="', CONCAT(_nachname, '" />')));
  END IF;
         
         
  END WHILE _loop;
  CLOSE _cur;
  SET done = 0;

Grüsse, Jan

akretschmer · 31 Juli 2015

Joa. Dann viel Spaß am Gerät ;-)

jmar83 · 31 Juli 2015

Am threadstack_size lag es zumindest nicht!

So macht man das:

Code:

DELIMITER //

DROP PROCEDURE IF EXISTS `getNestedXMLTree`;

CREATE PROCEDURE `getNestedXMLTree` (IN parentNodeID INT, OUT str VARCHAR(21844) CHARACTER SET utf8)
BEGIN
    DECLARE done  BOOLEAN DEFAULT FALSE;
   
    DECLARE _adr_id  INT;
    DECLARE _nachname  VARCHAR(64);
    DECLARE _nodeOf  INT;
   
    DECLARE _cur CURSOR FOR
    SELECT
        adressen.adr_id,
        adressen.nachname,
        adressen.nodeOf
   
    FROM
        CUST_v_adressen adressen
       
    WHERE
        adressen.nodeOf = COALESCE(parentNodeID,0);
       
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
    IF ISNULL(str) THEN
        SET str = '';
    END IF;

    -- Variante mit WHILE-Schleife:
    SET done = FALSE;
    OPEN _cur;
        _loop:WHILE(!done) DO
            FETCH _cur INTO _adr_id, _nachname, _nodeOf;
            IF done THEN
                LEAVE _loop;
            END IF;
            IF (SELECT COUNT('') FROM CUST_v_adressen adressen WHERE adressen.nodeOf = _adr_id) > 0 THEN
                SET str = CONCAT(str, CONCAT('<entry value="', CONCAT(_nachname,'">')));
                CALL getNestedXMLTree(_adr_id, @str);
                SET str = CONCAT(CONCAT(str, @str), '</entry>');
            ELSE
                SET str = CONCAT(str, CONCAT('<entry value="', CONCAT(_nachname, '" />')));
            END IF;
        END WHILE _loop;
    CLOSE _cur;

    /*
    -- Variante mit "normalem" Cursor:
    OPEN _cur;
        _loop: LOOP
            FETCH _cur INTO _adr_id, _nachname, _nodeOf;
            IF done THEN
                LEAVE _loop;
            END IF;
            IF (SELECT COUNT('') FROM CUST_v_adressen adressen WHERE adressen.nodeOf = _adr_id) > 0 THEN
                SET str = CONCAT(str, CONCAT('<entry value="', CONCAT(_nachname,'">')));
                CALL getNestedXMLTree(_adr_id, @str);
                SET str = CONCAT(CONCAT(str, @str), '</entry>');
            ELSE
                SET str = CONCAT(str, CONCAT('<entry value="', CONCAT(_nachname, '" />')));
            END IF;
        END LOOP;
    CLOSE _cur;
    */
   
END; //
DELIMITER ;

Evtl. kann mir nun jemand erklären, welche besseren Lösungen es gibt um direkt einen String mit verschachtelten Tags drin zurückzugeben...?

Grüsse, Jan

Distrilec · 31 Juli 2015

Kann jetzt sein, das es am Morgen liegt... Aber ich sehe dich hier nirgends deine Variable "done" auf TRUE setzen...

Code:

    -- Variante mit WHILE-Schleife:
    SET done = FALSE;
    OPEN _cur;
        _loop:WHILE(!done) DO
            FETCH _cur INTO _adr_id, _nachname, _nodeOf;
            IF done THEN
                LEAVE _loop;
            END IF;
            IF (SELECT COUNT('') FROM CUST_v_adressen adressen WHERE adressen.nodeOf = _adr_id) > 0 THEN
                SET str = CONCAT(str, CONCAT('<entry value="', CONCAT(_nachname,'">')));
                CALL getNestedXMLTree(_adr_id, @str);
                SET str = CONCAT(CONCAT(str, @str), '</entry>');
            ELSE
                SET str = CONCAT(str, CONCAT('<entry value="', CONCAT(_nachname, '" />')));
            END IF;
        END WHILE _loop;
    CLOSE _cur;

Hier wieder...

Code:

  -- Variante mit "normalem" Cursor:
    OPEN _cur;
        _loop: LOOP
            FETCH _cur INTO _adr_id, _nachname, _nodeOf;
            IF done THEN
                LEAVE _loop;
            END IF;
            IF (SELECT COUNT('') FROM CUST_v_adressen adressen WHERE adressen.nodeOf = _adr_id) > 0 THEN
                SET str = CONCAT(str, CONCAT('<entry value="', CONCAT(_nachname,'">')));
                CALL getNestedXMLTree(_adr_id, @str);
                SET str = CONCAT(CONCAT(str, @str), '</entry>');
            ELSE
                SET str = CONCAT(str, CONCAT('<entry value="', CONCAT(_nachname, '" />')));
            END IF;
        END LOOP;
    CLOSE _cur;
    */

Kann aber auch daran liegen, das ich dem MySQL nicht mächtig bin...

ukulele · 31 Juli 2015

jmar83 schrieb:
(WITH könnte was sein (?), eine "LEFT JOIN"-Lösung wäre eher weniger angebracht, ich will kein Query Resultset mit Ebenen, sondern einen hierarchisch aufgebauten XML-String, der dann auf PHP-Ebene noch entsprechend tranformiert wird um eine jQuery-Organigramm-Komponente damit zu "füttern"...)

Ich bin mir nicht ganz sicher was du vor hast und habe deinen Code auch nicht genau gelesen aber für Rekursion und Hirachien gibt es einige Ansätze. Mit WITH müsste das gehen und zwar deutlich besser als mit einer Schleife, kann MySQL nur nicht, für deine Zwecke scheinst du dir wirklich die schlechteste DB ausgewählt zu haben.
Wenn du dir sowieso schon MSSQL Beispiele angeguckt haßt, da gibt es auch noch sachen wie HirachyID und XML, aber andere DBs können da auch viel mehr als MySQL.

jmar83 · 31 Juli 2015

@Distrilec: Ist mein erster MySQL-Cursor, aber man sieht eigentlich alle Beipiele damit:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

...das "done" hat bei mir Initialwert "false" (=DECLARE done BOOLEAN FALSE), aber diese Zeile "DECLARE CONTINUE HANDLER..." bewirkt wohl irendwie dass diese auf "true" gesetzt wird falls der cursor am ende ist. Bei der While-Lösung wird diese auch als Kriterium genutzt, bei der LOOP-Lösung ist eher eine Endkosschliefe der Fall, mit BREAK (=MYSQL LEAVE) wenn done=true ist...

So verstehe ich das zumindest.

@ukulele: Direkt aus einer Tabelle mit Knoten eine hierarhische XML-Struktur zu formen und diese als String zurückzugeben ist halt keine alltägliche Sache... ich mache halt das unter SQL, was die meisten auf Ebene der "höheren" Programmiersprache machen. (Ich weiss, bei vielen verknoteten Einträgen werden massenhaft SELECT's abgegeben....)

Zum Them einen einzigen XML-String "zusammenbasteln": Bei SQL Server gibt's es doch eine STUFF/FOR XML PATH damit könnte man soviel ich weiss das ganze zu einem Resultset mit nur einem Eintrag formen, wo dann die gnaze XML-Sache drin inst... aber ich verwende halt MySQL.

Grüsse, Jan

jmar83 · 31 Juli 2015

Für sowas

soll sowas herauskommen (Ist aber normalerweise alles auf 1 Zeile):

<entry value="Jäggi">

<entry value="Nünlist">

<entry value="Byland" />

</entry>

</entry>

<entry value="*" />

<entry value="Simoes" />

<entry value="Elbe" />

<entry value="Jäggi">

<entry value="Nünlist">

<entry value="Byland" />

</entry>

</entry>

<entry value="*" />

<entry value="Simoes" />

<entry value="Elbe" />

Auf den root-Knoten kann ich in der Ausgbabe verzichten...

Grüsse, Jan

jmar83 · 31 Juli 2015

Ich mag mich noch an meine ehemalige Arbeitsstelle erinnern, wo alle immer von CTE sprachen, aber nicht mal wussten was es eigentlich für einen Algorithmus dazu braucht um einen Tree abzuarbeiten... aber verständlich, wenn man kaum was anderes als SQL programmiert hat...;-)

Ich habe lange Zeit SQL fast komplett gemieden, und mich auf CRUD/ORM-Lösungen fokussiert... nun habe ich aber gemerkt, wie leistungsfähig Prozeduren und Funktionen sein können. Und vor allem ist das Zeug ziemlich sicher, da man mit SQL nicht "jeden Mist" coden kann. (Irgendwelche Business Logik in Java oder PHP zu schreiben kann ja jeder Anfänger, aber dabei das richtige Exception/Fehlerhandling zu machen ist ein Thema für sich...)

Nachtrag: @ukulele: "Ich bin mir nicht ganz sicher was du vor hast" -> Das hier mit dem hierarchisch aufgebauten String zu "füttern": http://www.jqueryrain.com/?6N59fIiE, mein XML aus <entry id="..."> wird dann NICHT auf DB-Ebene noch mit HTML aufbereitet. (Es müssen HTML-Tags mit drin sein im Nested XML, damit kann man das Aussehen des Organigramms beeinflussen)

Grüsse, Jan

Distrilec · 31 Juli 2015

jmar83 schrieb:
nun habe ich aber gemerkt, wie leistungsfähig Prozeduren und Funktionen sein können.

Prozeduren/Funktionen sind in einer DB so ziemlich das langsamste... In Oracle wird es nurnoch auf der Java-Ebene langsamer...

Hierarchisch verknotete Datensätze mit SP nach XML umformen

jmar83

SQL-Guru

jmar83

SQL-Guru

Distrilec

Datenbank-Guru

jmar83

SQL-Guru

akretschmer

Datenbank-Guru

jmar83

SQL-Guru

jmar83

SQL-Guru

akretschmer

Datenbank-Guru

jmar83

SQL-Guru

Distrilec

Datenbank-Guru

ukulele

Datenbank-Guru

jmar83

SQL-Guru

jmar83

SQL-Guru

jmar83

SQL-Guru

Distrilec

Datenbank-Guru

Ähnliche Themen