Hierarchisch verknotete Datensätze mit SP nach XML umformen

jmar83

SQL-Guru
Beiträge
146
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...

Unbenannt.png

...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
 
Werbung:
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
 
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 ;)
 
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
 
Rekursive Abfragen bringen moderne Datenbanken out-of-the-box. Wenn Du sowas brauchst, warum nutzt Du dann eine DB, die das nicht kann?
 
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
 
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
 
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
 
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...
 
(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.
 
@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
 
Zuletzt bearbeitet:
Für sowas
Unbenannt.jpg


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
 
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
 
Zuletzt bearbeitet:
Werbung:
Zurück
Oben