<?php //4
/**
* This class is designed to add, move or delete branches inside
* a database tree created with the
* Modified Preorder Tree Traversal Algorithm
* [ Joe Celko - http://www.dbmsmag.com/9603d06.html ]
* using MySQL 3.X database engine ( with mysql extension ).
* The database should be as you want but requires at least 2
* tables to use correctly this class:
* 1 - a table with 3 fields, called for example 'tree'
* CREATE TABLE tree (
* id INT(10) NOT NULL AUTO_INCREMENT,
* sx INT(10) UNSIGNED NOT NULL,
* dx INT(10) UNSIGNED NOT NULL,
* PRIMARY KEY (id)
* );
* 2 - another table with at least 2 fields, called for example 'branch'
* CREATE TABLE branch (
* id INT(10) NOT NULL AUTO_INCREMENT,
* tree_id INT(10) UNSIGNED NOT NULL
* [, name VARCHAR(255) NOT NULL, description TEXT, ....]
* );
* NOTE: the name of theese fields:
* id, sx, dx, tree_id
* must be exactly these, other 'branch' fields should be
* called as you want.
* ______________________________________________________________
* EXAMPLE:
* // database resource
* $db = mysql_connect( 'localhost', 'root', 'password' );
* // chose a database
* mysql_select_db( 'tree' );
* // class reference
* $MPTTA = &new PHP4_Mysql3MPTTA( $db );
* // root node
* $MPTTA->addRoot( Array( 'name', 'surname', 'age', ... ) );
* // branch node
* $MPTTA->addNode( 1, Array( 'sub_name1', 'sub_surname1', 'sub_age1', ... ) );
* $MPTTA->addNode( 1, Array( 'sub_name2', 'sub_surname2', 'sub_age2', ... ) );
* // move node
* $MPTTA->moveNode( 2, 3 ); // move sub_name1 under sub_name2
* // remove node
* $MPTTA->delNode( 2 ); // remove both sub_name1 and sub_name2
* --------------------------------------------------------------
* @Compatibility >= PHP 4.3.X / MySQL 3.X
* @Author Andrea Giammarchi
* @Site http://www.devpro.it/
* @Mail andreaAT3siteDOTit
* @Date 03/02/2005
* @LastModified 19/05/2005 12:30
* @Version 0.1b [ tested, seems efficient ]
* @Limits there are not transations
*/
class PHP4_Mysql3MPTTA {
/**
* private variables
* Resource SQLITE database resource
* String tree table name
* String branch table name
* Boolean used in internal operations
*/
var $__db;
var $__tree, $__branch;
var $__skeep = false;
var $__qs = '__';
/**
* constructor
* Creates a new PHP4_Mysql3MPTTA obect.
* Assigns internal private variables
* new PHP4_Mysql3MPTTA( Resource $db [, String $tree_name[, String $branch_name]] )
*
* @param Resource mysql 3.x dtabase resource
* @param String tree table name
* @param String branch table name
*/
function PHP4_Mysql3MPTTA( &$db, $tree_name = 'tree', $branch_name = 'branch' ) {
$this->__db = &$db;
$this->__tree = &$tree_name;
$this->__branch = &$branch_name;
$this->__qs = $this->__qs.md5(microtime()).$this->__qs;
}
/**
* public method
* Checks if id exists ( branch id ) then returns all nested informations
* for that id.
* Mixed self->getNode( Integer $id )
* @param Integer branch node to show
* @return Mixed query result / false if node doesn't exist
*/
function getNode( $branch_id ) {
$branch_id = (int)$branch_id;
return $this->__getNode( $branch_id );
}
/**
* public method
* Creates a new root branch node
* Void self->addRoot( Array $what )
* @param Array all informations about this node
* [ all fields on 'branch' table but not id and tree_id,
* these are inserted automatically ]
*/
function addRoot( $new_branch ) {
$this->__addNode( $target_branch_id = 0, $new_branch );
}
/**
* public method
* Add a new branch ( or leaf ) under a node id, if this exists, or
* creates a new root branch node ( for example with an id = 0 )
* Void self->addNode( Integer $where_id, Array $what )
* @param Integer branch node to show
* @param Array all informations about this node
* [ all fields on 'branch' table but not id and tree_id,
* these are inserted automatically ]
*/
function addNode( $target_branch_id, $new_branch ) {
$target_branch_id = (int)$target_branch_id;
if( $target_branch_id > 0 ) {
$this->__addNode( $target_branch_id, $new_branch );
}
}
/**
* public method
* Removes a root node by id ( root branch id ) and all its sub-nodes
* Void self->delNode( Integer $root_id )
* @param Integer root branch node id to remove ( all subnodes are removed too )
*/
function delRoot( $root_id ) {
$root_id = (int)$root_id;
$this->__delNode( $root_id );
}
/**
* public method
* Removes a node by id ( branch id ) and all its sub-nodes
* Void self->delNode( Integer $where_id )
* @param Integer branch node id to remove ( all subnodes are removed too )
*/
function delNode( $branch_id ) {
$branch_id = (int)$branch_id;
if( $branch_id > 1 ) {
$this->__delNode( $branch_id );
}
}
/**
* public method
* Moves a node by id to another node by destination id
* nodes id are branches id, not tree.id.
* Void self->moveNode( Integer $id_from, Integer $id_to )
* @param Integer branch node id to move
* @param Integer destination branch node id where to add moved branch
*/
function moveNode( $moved_branch_id, $target_branch_id ) {
$moved_branch_id = (int)$moved_branch_id;
$target_branch_id = (int)$target_branch_id;
if( $moved_branch_id !== $target_branch_id ) {
$this->__moveNode( $moved_branch_id, $target_branch_id );
}
}
/**
* All final private methods
* Sorry, no time to comment each private method ...
*/
function __getNode( &$branch_id ) {
$q1 = &$this->__firstQuery( $branch_id );
if( mysql_num_rows( $q1 ) === 1 ) {
while( $r = mysql_fetch_row( $q1 ) ) {
$q2 = mysql_query (
"SELECT
{$this->__branch}.*
FROM
{$this->__branch}, {$this->__tree}
WHERE
{$this->__branch}.tree_id = {$this->__tree}.id
AND
{$this->__tree}.sx <= {$r[0]}
AND
{$this->__tree}.dx >= {$r[1]}",
$this->__db
);
}
}
else {
$q2 = false;
}
return $q2;
}
function __addNode( &$target_branch_id, &$new_branch ) {
$q1 = &$this->__firstQuery( $target_branch_id );
if( mysql_num_rows( $q1 ) === 1 ) {
while( $r = mysql_fetch_row( $q1 ) ) {
$sx = $r[0];
}
$dx = $sx + 1;
$q2 = "UPDATE {$this->__tree} SET sx = ( sx + 2 ) WHERE sx >= {$sx}{$this->__qs}
UPDATE {$this->__tree} SET dx = ( dx + 2 ) WHERE dx >= {$sx}{$this->__qs}
INSERT INTO {$this->__tree} VALUES ( NULL, {$sx}, {$dx} ){$this->__qs}
";
$this->__queryRollBackable( $q2 );
$maxid = mysql_insert_id();
if( $this->__skeep === false ) {
$q2 = "INSERT INTO {$this->__branch} VALUES ( NULL, {$maxid}, <branch_values> ){$this->__qs}
";
if( !is_Array( $new_branch ) ) {
$new_branch = Array( $new_branch );
}
$q3 = "";
for( $a = 0, $b = count( $new_branch ); $a < $b; $a++ ) {
$q3 .= '"'.mysql_real_escape_string( $new_branch[$a] ).'", ';
}
$this->__queryReplaced( $q2, '<branch_values>', $q3, 2 );
}
else {
$q2 = "UPDATE {$this->__branch} SET tree_id = {$maxid} WHERE id = {$new_branch}{$this->__qs}
";
$this->__queryRollBackable( $q2 );
$this->__skeep = false;
}
}
else {
if( !is_Array( $new_branch ) ) {
$new_branch = Array( $new_branch );
}
$q3 = "";
for( $a = 0, $b = count( $new_branch ); $a < $b; $a++ ) {
$q3 .= '"'.mysql_real_escape_string( $new_branch[$a] ).'", ';
}
$maxid = 1;
$increment = 0;
$q2 = mysql_unbuffered_query( "SELECT MAX(id), MAX(dx) FROM {$this->__tree}", $this->__db );
while( $r = mysql_fetch_row( $q2 ) ) {
$maxid = $r[0] + 1;
$increment = $r[1];
}
$q2 = "INSERT INTO
{$this->__tree}
VALUES (
NULL,
( {$increment} + 1 ),
( {$increment} + 2 )
){$this->__qs}
INSERT INTO {$this->__branch} VALUES ( NULL, {$maxid}, <branch_values> ){$this->__qs}
";
$this->__queryReplaced( $q2, '<branch_values>', $q3, 2 );
}
}
function __delNode( &$branch_id ) {
$q1 = &$this->__firstQuery( $branch_id );
if( mysql_num_rows( $q1 ) === 1 ) {
while( $r = mysql_fetch_row( $q1 ) ) {
$dx = &$r[0];
$sx = &$r[1];
$nx = $dx - $sx + 1;
$q2 = "";
if( $this->__skeep === false ) {
$q2 .= "DELETE FROM {$this->__branch} WHERE <branch_delete>{$this->__qs}
";
}
$q2 .= "DELETE FROM {$this->__tree} WHERE sx >= {$sx} AND dx <= {$dx}{$this->__qs}
UPDATE {$this->__tree} SET sx = ( sx - {$nx} ) WHERE sx > {$dx}{$this->__qs}
UPDATE {$this->__tree} SET dx = ( dx - {$nx} ) WHERE dx > {$dx}{$this->__qs}
";
if( $this->__skeep === false ) {
$q3 = mysql_unbuffered_query (
"SELECT
id
FROM
{$this->__tree}
WHERE
sx >= {$sx}
AND
dx <= {$dx}",
$this->__db
);
$q4 = "";
while( $r = mysql_fetch_row( $q3 ) ) {
$q4 .= "tree_id = {$r[0]} OR ";
}
$this->__queryReplaced( $q2, '<branch_delete>', $q4, 4 );
}
else {
$this->__queryRollBackable( $q2 );
$this->__skeep = false;
}
}
}
}
function __moveNode( &$moved_branch_id, &$target_branch_id ) {
$q1 = &$this->__firstQuery( $moved_branch_id );
if( mysql_num_rows( $q1 ) === 1 ) {
while( $r = mysql_fetch_row( $q1 ) ) {
$fromdx = $r[0];
$fromsx = $r[1];
$fromid = $r[2];
}
$q1 = &$this->__firstQuery( $target_branch_id );
if( mysql_num_rows( $q1 ) === 1 ) {
while( $r = mysql_fetch_row( $q1 ) ) {
$todx = $r[0];
$tosx = $r[1];
$toid = $r[2];
}
$blocks = ( $fromdx - ( $fromsx - 1 ) ) / 2;
$changes = $blocks * 2;
if( $blocks > 1 ) {
$sql = "CREATE TABLE moved__{$this->__tree} ( id INT(10) NOT NULL AUTO_INCREMENT, old_id INT(10) UNSIGNED, sx INT(10) UNSIGNED, dx INT(10) UNSIGNED, PRIMARY KEY (id) ){$this->__qs}
INSERT INTO moved__{$this->__tree} SELECT NULL, id, sx, dx FROM {$this->__tree} WHERE sx >= {$fromsx} AND dx <= {$fromdx}{$this->__qs}
";
$this->__queryRollBackable( $sql );
$this->__skeep = true;
$this->delNode( $moved_branch_id );
$sql = "SELECT {$this->__tree}.sx, moved__{$this->__tree}.sx FROM {$this->__tree}, moved__{$this->__tree} WHERE {$this->__tree}.id = {$toid} AND moved__{$this->__tree}.old_id = {$fromid}";
$q = mysql_unbuffered_query( $sql, $this->__db );
while( $r = mysql_fetch_row( $q ) ) {
$newsx = $r[0] + 1;
$r[1] = (int)$r[1];
$diff = 0;
while( ( $r[1] + $diff ) !== $newsx ) {
if( (int)$tosx > (int)$fromsx ) {
$diff++;
}
else {
$diff--;
}
}
$sql = "UPDATE {$this->__tree} SET sx = ( sx + {$changes} ) WHERE sx >={$newsx}{$this->__qs}
UPDATE {$this->__tree} SET dx = ( dx + {$changes} ) WHERE dx >={$newsx}{$this->__qs}
UPDATE moved__{$this->__tree} SET sx = ( sx + {$diff} ), dx = ( dx + {$diff} ){$this->__qs}
INSERT INTO {$this->__tree} ( id, sx, dx ) SELECT old_id, sx, dx FROM moved__{$this->__tree}{$this->__qs}
DROP TABLE moved__{$this->__tree}{$this->__qs}
";
}
$this->__queryRollBackable( $sql );
}
else {
$this->__skeep = true;
$this->delNode( $moved_branch_id );
$this->__skeep = true;
$this->addNode( $target_branch_id, $moved_branch_id );
}
}
}
}
function __queryRollBackable( &$q ) {
$rows = explode( $this->__qs, $q );
for( $a = 0, $b = count($rows) - 1; $a < $b; $a++ ) {
$query = mysql_query( trim( $rows[$a] ), $this->__db );
if( !$query ) {
@mysql_free_result( $query );
}
}
}
function __firstQuery( &$branch_id ) {
$q = mysql_query (
$this->__getFirstQuery($branch_id),
$this->__db
);
return $q;
}
function __getFirstQuery( &$branch_id ){
$query = "
SELECT
{$this->__tree}.dx, {$this->__tree}.sx, {$this->__tree}.id
FROM
{$this->__branch}, {$this->__tree}
WHERE
{$this->__branch}.tree_id = {$this->__tree}.id
AND
{$this->__branch}.id = {$branch_id}
";
return $query;
}
function __queryReplaced( $q, $repl, $mod, $diff ) {
$q = str_replace( $repl, substr( $mod, 0, -$diff ), $q );
$this->__queryRollBackable( $q );
}
}
?>