Need help with hierarchy-data-closure-table?
Click the “chat” button below for chat support from the developer who created it, or find similar developers for support.

About the developer

developerworks
145 Stars 51 Forks Other 25 Commits 8 Opened issues

Description

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

Services available

!
?

Need anything else?

Contributors list

# 47,750
React N...
Go
Apache ...
Xamarin
2 commits
# 633,202
trigger
1 commit

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.