This is a mysql store procedure and trigger implementation of closure table in RDBMS about hierarchy data model.
-
Automatically add new paths when you insert a new node
-
Automatically update(
DELETE
old paths andINSERT
new paths) paths when you updateparent_id
of a node. (This means move a node/subtree to a new parent) -
A store procedure that is used to select a whole subtree by a
node_id
(if thenode_id
has descendant)
trigger_add_paths
The trigger is execute when insert a node into prefix_nodes
table, and call p_node_add
to add update paths.
prefix_node_move
:
The trigger is execute when update the parent_id
column of prefix_nodes
table only if OLD.parent_id != NEW.parent_id
-
p_node_add(param_node_new_id INT UNSIGNED,param_node_parent_id INT UNSIGNED)
Add new paths when insert a node to
prefix_nodes
table -
p_get_tree(node_id INT UNSIGNED)
Get subtree by a node id
-
p_node_move(node_old_parent_id INT UNSIGNED,node_new_parent_id INT UNSIGNED)
Update paths when move a node to a new parent node
-
p_node_hide(node_id INT UNSIGNED, is_deleted INT UNSIGNED)
Hide or show nodes from subtree, explains as following:
- Step 1.
call p_get_tree(6)
get theHARDWARE
subtree, - Step 2.
call p_node_hide(6, 0)
to hide a subtree, - Step 3.
call p_get_tree(6)
get theHARDWARE
subtree, when you get a subtree, it is not show in the result. - Step 4.
call p_node_hide(6, 1)
showHARDWARE
subtree
- Step 1.
-
./mysql/tables.sql
Create tables.
-
./mysql/sample_data.sql
Some insert statements for testing
TODO::