Italiano - English

Firebird: Tree data mangement with recursive Stored Procedure

Table of Contents

    Firebird: Tree data mangement
    1. Firebird: Tree data mangement with recursive CTE
    2. Firebird: Tree data mangement with recursive Stored Procedure
    Translation for this document is not available or is not complete,
    if you are intrested to receive information please write to

    This document shown how to use Firebird Recursive Stored Procedure to walking trees

    Una Stored Procedure (SP) è un oggetto molto comune nelle Basi di Dati ed il suo utilizzo ricorsivo nelle interrogazioni di strutture ad albero è molto comodo per evitare intenso traffico client/server e puo' dare grande flessibilità.

    Una SP ricorsiva ha il seguente schema:

    • SELECT di tutti i nodi radice (PADRE=NULL)
    • SUSPEND
    • Per ogni nodo figlio
      • Chiama se stessa utilizzando il nodo corrente come nodo radice
      • SUSPEND

    Un Esempio pratico

    Facendo riferimento alla tabella FAMILY ed i dati presentati nella pagina principale

    • GrandFather1,  Età:80
      • Son1, Età:60
        • GrandSon1, Eta 14
        • GrandSon2, Età 19
      • Son2, Età 58

    Tramite una SP ricorsiva è possibile interrogare la tabella FAMILY ed ottenere la struttura correttamente elencata

     
    CREATE OR ALTER PROCEDURE SP_FAMILYTREE (
        root_id INTEGER)
    returns (
        id INTEGER,
        id_padre INTEGER,
        name VARCHAR(200),
        age SMALLINT)
    AS
    DECLARE variable child_id INTEGER;
    DECLARE variable newname VARCHAR(200);
    BEGIN
      IF (:root_id IS NULL) THEN
        FOR SELECT ID FROM FAMILY WHERE IDFATHER IS NULL ORDER BY AGE INTO :child_id
        DO BEGIN
          FOR SELECT id,id_padre,name,age FROM SP_FAMILYTREE( :child_id)
            INTO :id,:id_padre,:name,:age DO
            SUSPEND; -- return recursive output for all childs
        END
     
      ELSE BEGIN
        SELECT ID,IDFATHER,nome,age FROM FAMILY WHERE ID= :root_id
          INTO :id,id_padre,:name,:age;
        SUSPEND; -- return root node data
       END
     
      newname = :name;
     
      -- loop through all direct children
      FOR SELECT ID FROM FAMILY WHERE IDFATHER =:root_id ORDER BY AGE INTO :child_id
      DO BEGIN
        FOR SELECT id,id_padre,:newname||'>'||name,age FROM SP_FAMILYTREE( :child_id) INTO :id,:id_padre,:name,:age DO
          SUSPEND; -- return recursive output for all childs
      END
    END;
     
    GRANT SELECT ON FAMILY TO PROCEDURE SP_FAMILYTREE;
    GRANT EXECUTE ON PROCEDURE SP_FAMILYTREE TO PROCEDURE SP_FAMILYTREE;
    GRANT EXECUTE ON PROCEDURE SP_FAMILYTREE TO SYSDBA;

    L'esecuzione di SP_FAMILYTREE(Null) di cui sopra produce il seguente risultato

    ID ID_PADRE NAME AGE
    1 null GrandFather1 80
    3 1 GrandFather1>Son2 58
    2 1 GrandFather1>Son1 60
    4 2 GrandFather1>Son1>GrandSon1 14
    5 2 GrandFather1>Son1>GrandSon2 19

    Inoltre la SP di cui sopra puo' essere chiamata con parametro diverso da null in modo da ottenere solo il ramo desiderato, ad esempio SP_FAMILYTREE(2) produce:

    ID ID_PADRE NAME AGE
    2 1 GrandFather1>Son1 60
    4 2 GrandFather1>Son1>GrandSon1 14
    5 2 GrandFather1>Son1>GrandSon2 19

    Memorizzare i campi per la gestione della struttura

    Come si è visto una SP fornisce l'accesso e la navigazione delle strutture padre/figli con grande semplicità, tuttavia si tratta sempre di chiamate ricorsive che possono essere onerose per il server.

    Nel caso in cui la struttura non cambia troppo di frequente è possibile utilizzare una SP all'interno di un trigger per memorizzare staticamente alcuni campi utili ai fini della gestione della struttura.

    Ad esempio nella tabella FAMILY si potrebbero creare i seguenti campi per memorizzare le informazioni relative alla struttura:

    • TREE_SORT_KEY: per memorizzare l'ordine sequenziale nella struttura
    • IDROOT: per memorizzare la radice a cui appartiene un record
    • PATH: per avere il percorso dei nomi sempre disponibile
    • PATHID: per avere il percorso degli ID sempre disponibile

    Modifichiamo la tabella FAMILY

     
    ALTER TABLE FAMILY ADD TREE_SORT_KEY INTEGER;
    ALTER TABLE FAMILY ADD IDROOT INTEGER;
    ALTER TABLE FAMILY ADD PATH VARCHAR(150);
    ALTER TABLE FAMILY ADD PATHID VARCHAR(50)

    Ora creaiamo una SP per la compilazione/aggiornamento dei campi di gestione. La SP utilizza un generatore per avere una sorta di variabile globale da utilizzare come autoinc tra le chiamate ricorsive.

     
    -- Create a generator for sort key
    CREATE SEQUENCE GEN_FAMILY_SORTKEY;
     
    CREATE OR ALTER PROCEDURE SP_FAMILYTREE_UPD (
        root_id INTEGER = NULL,
        idradice INTEGER = NULL,
        path VARCHAR(200) = NULL,
        pathid VARCHAR(200) = NULL)
    AS
    DECLARE variable child_id INTEGER;
    DECLARE variable autoinc INTEGER;
    BEGIN
     
      -- CAUTION!! first calls MUST have :root_id = NULL
      IF (:root_id IS NULL) THEN BEGIN
     
        --reset generator for sort key
        autoinc = gen_id( gen_FAMILY_sortkey  , 0 - GEN_ID( gen_FAMILY_sortkey, 0 ) );
     
        --loop for all main nodes using AGE as sort field
        FOR
          SELECT ID,ID,cast('' AS VARCHAR(200)),cast(';' AS VARCHAR(200))
          FROM   FAMILY WHERE IDFATHER IS NULL ORDER BY AGE
          INTO   :child_id,:idradice,:path,:pathid
     
        -- recursive process for all childs
        DO EXECUTE PROCEDURE SP_FAMILYTREE_UPD(:child_id,:idradice,:path,:pathid);
     
      END --if
     
      ELSE -- update current node
        UPDATE FAMILY SET
            IDROOT = :idradice,
            PATHID   = IIF(:pathid IS NULL,';',:pathid) || ID || ';' ,
            PATH     = IIF(:path IS NULL,'',:path) || nome||'>' ,
            TREE_SORT_KEY  = gen_id(gen_FAMILY_sortkey,1)
        WHERE ID = :root_id
        RETURNING idroot,path,pathid
        INTO :idradice,:path,:pathid;
     
      -- loop through all direct children using AGE as sort field
      FOR SELECT ID FROM FAMILY WHERE IDFATHER =:root_id ORDER BY INTO :child_id 
        DO EXECUTE PROCEDURE SP_FAMILYTREE_UPD(:child_id,:idradice,:path,:pathid);
     
    END;
     
    GRANT SELECT,UPDATE ON FAMILY TO PROCEDURE SP_FAMILYTREE_UPD;
    GRANT EXECUTE ON PROCEDURE SP_FAMILYTREE_UPD TO PROCEDURE SP_FAMILYTREE_UPD;
    GRANT EXECUTE ON PROCEDURE SP_FAMILYTREE_UPD TO SYSDBA;

    Una chiamata alla procedura SP_FAMILYTREE_UPD(null) aggiorna i campi necessari alla gestione della struttura ad albero. Dopo una esecuzione di SP_FAMILYTREE_UPD(null) è possibile utilizzare una query semplice per interrogare la struttura

     
    SELECT * FROM family ORDER BY TREE_SORT_KEY

    E ottenere il sequente risultato

    ID IDFATHER AGE NAME PATH TREE_SORT_K EY IDROOT PATHID
    1 null 80 GrandFather1 GrandFather1 0 1 ;1;
    2 1 60 Son1 GrandFather1>Son1 1 1 ;1;2;
    4 2 14 GrandSon1 GrandFather1>Son1>GrandSon1 2 1 ;1;2;4;
    5 2 19 GrandSon2 GrandFather1>Son1>GrandSon2 3 1 ;1;2;5;
    3 1 58 Son2 GrandFather1>Son2 4 1 ;1;3;

    A questo punto sarebbe desiderabile che i campi per la gestione della struttura fossero sempre aggiornati. Per ottenere ciò è possibile creare un Trigger AFTER INSERT/UPDATE/DELETE che chiama SP_FAMILYTREE_UPD(null) ad ogni modifica nei dati:

     
    CREATE TRIGGER family_aiud32000 FOR family
    active after INSERT OR UPDATE OR DELETE position 32000
    AS
    BEGIN
      IF ( (NEW.ID IS DISTINCT FROM OLD.ID) OR
           (NEW.IDFATHER IS DISTINCT FROM OLD.IDFATHER) OR
           (NEW.NOME IS DISTINCT FROM OLD.NOME)  OR
           (NEW.AGE IS DISTINCT FROM OLD.AGE)) THEN
     
      EXECUTE PROCEDURE sp_familytree_upd;
    END
    Nota: questo trigger chiama la procedure sp_familytree_upd che modifica i campi IDROOT,PATHID,PATH,TREE_SORT_KEY di tutti i record della tabella, ogni volta che uno dei campi "sensibili" ID,IDFATHER, NOME di un singolo record viene modificato.
    Questo implica che la modifica ad un singolo campo "sensibile" è molto onerosa inquanto viene eseguita una CTE ricorsiva da cui viene generato un UPDATE sull'intera tabella. Si consiglia di ulilizzare questa tecnica solo per strutture i cui campi "sensibili" cambiano molto di rado mentre al contrario le interrogazioni sulla struttura sono molto frequenti.
    E' necessario monitorare anche il campo AGE perchè è utilizzato in ORDER BY dalla procedura
    Vote this page:

    0 Comments:

    Leave your comment:

    Note:
    • Your email email will not be visible or used in any way, and is not required
    • Please keep comments relevant
    • Any content deemed inappropriate or offensive may be edited and/or deleted
    • HTML code is not allowed. Please use BBCode to format your text
      [b]bold[/b], [u]underline[/u], [i]italic[/i], [code]code[/code]
    The coding examples presented here are for illustration purposes only. The author takes no responsibility for end-user use
    This work is property of Pk Lab. You can use it for free but you must retain author's copyright.