Italiano - English

Firebird: gestione di strutture ad albero con Stored Procedure Ricorsive

Sommario

    Firebird: gestione di strutture ad albero
    1. Firebird: gestione di strutture ad albero con Stored Procedure Ricorsive
    2. Firebird: gestione di strutture ad albero con CTE ricorsive

    In questa pagina viene presentata la funzionalità ricorsiva delle Stored Procedure di Firbird per gestire strutture ad albero (padre/figli)

    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
    Vota questa pagina:

    0 Commenti:

    Lascia il tuo commento:

    Note:
    • La tua email non è obligatoria e non sarà visibile in alcun modo
    • Si prega di inviare solo commenti relativi a questa pagina
    • Commenti inappropriati o offensivi saranno modificati o eliminati
    • Codici HTML non sono consentiti. Prego usare i BB code:
      [b]bold[/b], [u]underline[/u], [i]italic[/i], [code]code[/code]
    Il codice, le illustrazioni e gli esempi riportati in questa pagina sono solo a scopo illustrativo. L'autore non prende alcuna responsabilità per il loro utilizzo da parte dell'utente finale.
    Questo materiale è di proprietà di Pk Lab ed è utilizzabile liberamente a condizione di citarne la fonte.