Closure Table: Manage transitive closure tables stored in MySQL

# MySQL Transitive Closure Tables (TCT) or Adjacency Relations

Every one knows nested sets, or materialized path and adjacency lists to structure data hierarchically in relational databases. This is my approach to work with a fourth kind of hierarchy models. If you know something about graph theory, then you will immediately understand what the aim of my approach is. You can easily feed graph applications with data from a mysql database.


TCT organize nodes hierarchically by weight and depth. TCT are physically separated from content data tables. TCT administer structures (node, edge), not content. TCT work like nested sets. It is possible to compute nested set left and right values programmatically. Although DML operations are faster than nested set operations, there is a lack. TCT waste db space because each node is completely resolved to represent all related edges. 

Conclusion: Retrieving TCT data is as fast as nested sets retrievals. TCT DML real-time operations are much faster. There is no need to update myriads of left and right values. 



## Requirements
  - php 5+
  - mysql

## Sample Installation

  - create db mydb and insert sample sql from file
  - change connection data in samples/config.inc.php to access db 
  - call samples/ClosureTable/index.php (here resides the app)
  - Db class comes with an interface, so you can easily implement your own db wrapper
  !!! use create table operations when you know what you are doing !!!

## Db (Database) classes

### Basic features

The TCT comes within some basic table structures:
- first level:	ancestor - descendant
- second level: additional depth
- third level:	additional weight

Depending on the goal of a relation one of the three table layouts may be chosen.
For simple org charts a first level design is recommended. Third level designs
are used for TCT with weighted nodes (sorting algo.); second level layouts
are only sets of relations which allow easy access to node trees by depth.

ClosureTable offers interface methods which capsule all available commands.


### Retrieving data

Class ClosureTableRetrieve contains common queries to retrieve hierarchical lists.
To get a node list record set which represent the full path, use ClosureTableRetrieve::getPath().
Its reverse function is ClosureTableRetrieve::getUpPath().
The class provides some slightly deviating methods with different aims.
You can query node lists in different ways. ClosureTableRetrieve::getTree() returns
the full node tree. The class also provides four retrieve sibling methods.


### Inserting data

ClosureTableInsert has only a single method: insert().
Insert inserts and relates nodes as last child of its parent.


### Deleting data

ClosureTableDelete comes a long with two functions:
- delete() removes a leaf
- deleteSubtree() removes a node and its decendants


## Sorting data

ClosureTableSort consists of node movement operations:
- moveLeft() brings a node one level up
- moveRight() brings a node one level down to its previos sibling
- moveUp() sets a node before its previous sibling
- moveDown() sets a node before its next sibling



## Tool

The tool ClosureTable contains sample tree build mechanisms. On default a closure
relation is represented as a nested list of unordered items.

The tree generator method attaches basic actions to each node item of the tree.

Actions:
- add a new node = ?perm=add&a={nodeid}&node={parentnodeid}
- zoom into subtree = ?node={nodeid}
- move a node up:
  - up = ?perm=up&a={nodeid}&p={parentnodeid}
  - down = ?perm=down&a={nodeid}&p={parentnodeid}
  - left = ?perm=left&a={nodeid}&p={parentnodeid}
  - right = ?perm=right&a={nodeid}&p={parentnodeid}
- delete:
  - node = ?perm=del&a={nodeid}&p={parentnodeid}
  - subtree = ?perm=rem&a={nodeid}&p={parentnodeid}