PHP Classes
elePHPant
Icontem

Closure Table: Manage transitive closure tables stored in MySQL

Recommend this page to a friend!
  Info   Screenshots Screenshots   View files View files (34)   DownloadInstall with Composer Download .zip   Reputation   Support forum (3)   Blog    
Last Updated Ratings Unique User Downloads Download Rankings
2009-08-02 (6 years ago) RSS 2.0 feedNot enough user ratingsTotal: 870 This week: 2All time: 3,852 This week: 488Up
Version License PHP version Categories
closuretable 0.1The PHP License5.0PHP 5, Databases, Data types
Description Author

This package can be used to manage transitive closure tables (TCT) stored in a MySQL database.

It can create MySQL database tables that will store information about hierarchies of nodes stored in other database tables.

Different classes can organize nodes hierarchically by weight and depth. They can insert, delete, retrieve and sort nodes.

Innovation Award
PHP Programming Innovation award nominee
July 2009
Number 8


Prize: One book of choice by Manning
Databases are often used to store records of information that are related in ways that form hierarchic trees and other types of graphs.

There are solutions to manipulate sets records that form these types of graph, like the Joe Celko's nested sets.

However, such solutions require that you have additional fields in the records to store information about the graph structures.

Using closure tables is a different approach that consists in using separate tables to store graph structural information.

This class implements an API that manages closure tables in PHP. It can be used to manage graphs of records of information created by existing PHP applications without changing the code that manages the application table records.

Manuel Lemos
Picture of Tom Schaefer
  Performance   Level  
Name: Tom Schaefer is available for providing paid consulting. Contact Tom Schaefer .
Classes: 39 packages by
Country: Germany Germany
Age: ???
All time rank: 1099 in Germany Germany
Week rank: 148 Down4 in Germany Germany Up
Innovation award
Innovation award
Nominee: 9x

Winner: 1x

Details
# 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}


Screenshots  
  • sample.png
  Files folder image Files  
File Role Description
Files folder imageimg (11 files)
Files folder imageDb (10 files)
Files folder imageTool (1 file, 2 directories)
Files folder imagesamples (1 file, 1 directory)
Accessible without login Plain text file index.php Aux. load classes
Accessible without login Plain text file Readme.txt Doc. Readme
Accessible without login Plain text file sample.sql Data sample db layout

  Files folder image Files  /  img  
File Role Description
  Accessible without login Image file node_delete.png Icon delete
  Accessible without login Image file node_delete_tree.png Icon delete tree
  Accessible without login Image file node_delete_tree_gray.png Icon delete tree unselect
  Accessible without login Image file node_move_down.png Icon move down
  Accessible without login Image file node_move_down_gray.png Icon move down unselect
  Accessible without login Image file node_move_left.png Icon move left
  Accessible without login Image file node_move_left_gray.png Icon move left unselect
  Accessible without login Image file node_move_right.png Icon move right
  Accessible without login Image file node_move_right_unselect.png Icon move right unselect
  Accessible without login Image file node_move_up.png Icon move up
  Accessible without login Image file node_move_up_gray.png Icon move up unselect

  Files folder image Files  /  Db  
File Role Description
  Plain text file ClosureTable.php Class Closure Table Wrapper
  Plain text file ClosureTableBase.php Class Closure Table Base
  Plain text file ClosureTableCreate.php Class Create Table Class
  Plain text file ClosureTableDelete.php Class Closure Table Delete
  Plain text file ClosureTableInsert.php Class Closure Table Insert
  Plain text file ClosureTableInterface.php Class interface for db class
  Plain text file ClosureTableRetrieve.php Class Closure Retrieve Data
  Plain text file ClosureTableSort.php Class Various Tree Sort Operations
  Plain text file Db.php Class Sample Db Adapter - very basic
  Accessible without login Plain text file index.php Aux. autoloader

  Files folder image Files  /  Tool  
File Role Description
Files folder imageClosureTable (2 files)
Files folder imageView (3 files)
  Accessible without login Plain text file index.php Aux. autoloader

  Files folder image Files  /  Tool  /  ClosureTable  
File Role Description
  Accessible without login Plain text file index.php Aux. autoloader
  Plain text file TreeAdmin.php Class Demo Helper Tool for visualizing a ul-tree

  Files folder image Files  /  Tool  /  View  
File Role Description
  Plain text file Href.php Class View Link Href Builder
  Accessible without login Plain text file index.php Aux. autoloader
  Plain text file Link.php Class View Link Builder

  Files folder image Files  /  samples  
File Role Description
Files folder imageClosureTable (3 files)
  Accessible without login Plain text file config.inc.php Conf. main config

  Files folder image Files  /  samples  /  ClosureTable  
File Role Description
  Accessible without login Plain text file config.inc.php Conf. config sample
  Accessible without login Plain text file index.php Example sample
  Accessible without login Plain text file server.php Example sample

 Version Control Unique User Downloads Download Rankings  
 0%
Total:870
This week:2
All time:3,852
This week:488Up
User Comments (1)
class doesn't work.
4 years ago (Chris Novoa)
50%StarStarStar