← All repositories

hierarchy-data-closure-table

25 commits · master

README.md

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::

Commit history

Files

25 tracked files at master.

  • assets
    • call p_prefix_nodes_get_subtree_by_node_id.png
    • closure-table-paths.png
  • mysql
    • data
      • sample_data.sql
    • procedures
      • p_get_tree.sql
      • p_node_add.sql
      • p_node_hide.sql
      • p_node_move.sql
    • triggers
      • trigger_add_node.sql
      • trigger_move_node.sql
    • all-in-one.sql
    • create_table_structure.sql
  • postgres
    • data
      • sample_data.sql
    • procedures
      • add_node.sql
      • delete_subtree.sql
      • get_child_nodes.sql
      • get_subtree.sql
      • json_output.sql
      • move_subtree.sql
    • triggers
      • add_new_paths_for_insert.sql
    • all-in-one.sql
    • create_table_structure.sql
    • README.md
  • .gitignore
  • LICENSE.md
  • README.md