Firebird: Tree data mangement with recursive Stored Procedure
Table of Contents
- Firebird: Tree data mangement with recursive Stored Procedure
- Firebird: Tree data mangement with recursive CTE
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
-
Son1, Età:60
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 | EYIDROOT | 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
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.
This work is property of Pk Lab. You can use it for free but you must retain author's copyright.