This is a mysql and postgresql store procedure and trigger implementation of closure table in RDBMS about hierarchy data model.
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(
DELETEold paths and
INSERTnew paths) paths when you update
parent_idof 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 the
node_idhas descendant)
trigger_add_paths
The trigger is execute when insert a node into
prefix_nodestable, and call
p_node_addto add update paths.
prefix_node_move:
The trigger is execute when update the
parent_idcolumn of
prefix_nodestable 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_nodestable
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:
call p_get_tree(6)get the
HARDWAREsubtree,
call p_node_hide(6, 0)to hide a subtree,
call p_get_tree(6)get the
HARDWAREsubtree, when you get a subtree, it is not show in the result.
call p_node_hide(6, 1)show
HARDWAREsubtree
./mysql/tables.sql
Create tables.
./mysql/sample_data.sql
Some insert statements for testing
TODO::