Firebird: Tree data mangement with recursive Stored Procedure

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

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:

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

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