hierarchy-data-closure-table

by developerworks

This is a mysql and postgresql store procedure and trigger implementation of closure table in RDBMS ...

132 Stars 47 Forks Last release: Not found Other 25 Commits 0 Releases

Available items

No Items, yet!

The developer of this repository has not created any items for sale yet. Need a bug fixed? Help with integration? A different license? Create a request here:

Closure Table

This is a mysql store procedure and trigger implementation of closure table in RDBMS about hierarchy data model.

Closure Table Node Paths

Closure Table Node Paths

Query the subtree nodes

Query the subtree nodes

Features

  • Automatically add new paths when you insert a new node

  • Automatically update(

    DELETE
    old paths and
    INSERT
    new paths) paths when you update
    parent_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 the
    node_id
    has descendant)

Triggers

  • 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

Store Procedures

  • 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 the
    HARDWARE
    subtree,
  • Step 2.
    call p_node_hide(6, 0)
    to hide a subtree,
  • Step 3.
    call p_get_tree(6)
    get the
    HARDWARE
    subtree, when you get a subtree, it is not show in the result.
  • Step 4.
    call p_node_hide(6, 1)
    show
    HARDWARE
    subtree

MySQL Files

  • ./mysql/tables.sql

Create tables.

  • ./mysql/sample_data.sql

Some insert statements for testing

Postgresql Files

TODO::

We use cookies. If you continue to browse the site, you agree to the use of cookies. For more information on our use of cookies please see our Privacy Policy.