riquito / baobab Goto Github PK

DECLARE drop_tree_id INTEGER UNSIGNED;

namespace portal\Core;

class Folder extends \Baobab {
....
}

Fatal error: Uncaught exception 'sp_MySQL_Error' with message 'Query was empty' in /home/bvu/public_html/new/examples/test.php:622 Stack trace: #0 /home/bvu/public_html/new/examples/test.php(2148): Baobab->build() #1 /home/bvu/public_html/new/examples/test.php(499): ForumBaobab->build() #2 /home/bvu/public_html/new/examples/test.php(2162): Baobab->__construct(Object(mysqli), 'forum') #3 /home/bvu/public_html/new/examples/test.php(2177): start_over(Object(mysqli), 'forum') #4 /home/bvu/public_html/new/examples/test.php(2234): main() #5 {main} thrown in /home/bvu/public_html/new/examples/test.php on line 622



<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);


/**!
 * .. _api:
 * 
 * ==========
 * Baobab API
 * ==========
 *
 * Copyright and License
 * ---------------------
 * 
 * Copyright 2010-2011 Riccardo Attilio Galli <[email protected]> http://www.sideralis.org
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *    http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

// define BAOBAB_SQL_DIR as the absolute path with no leading / pointing to
// the root directory holding the sql files
if (!defined("BAOBAB_SQL_DIR")) define("BAOBAB_SQL_DIR", dirname(__FILE__).DIRECTORY_SEPARATOR."sql");

/**!
 * Exceptions
 * ----------
 */

/**!
 * .. class:: sp_Error
 *    
 *    Root exception. Each exception thrown in a Sideralis Programs library
 *    derive from this class
 */
class sp_Error extends Exception { }

/**!
 * .. class:: sp_MySQL_Error
 *    
 *    Exception holding informations about errors occurred when using mysql
 *
 *    :param $conn_or_msg: database connection object or an exception message
 *    :type $conn_or_msg:  mysqli or string
 *    :param $err_code: mysql error code
 *    :type $err_code:  int
 */
class sp_MySQL_Error extends sp_Error {

    public function __construct($conn_or_msg,$err_code=NULL) {
        if ($conn_or_msg instanceof mysqli || $conn_or_msg instanceof mysql) {
            $err_str = $conn_or_msg->error;
            $err_code = $conn_or_msg->errno;
        } else {
            $err_str = $conn_or_msg;
        }
        
        parent::__construct($err_str, $err_code);
    }
}


/**@
 * Utils
 * -----
 */

/**@
 * .. class:: sp_SQLUtils
 *    
 *    Class with helpers to work with SQL
 *
 *    :param $conn: database connection object
 *    :type $conn:  mysqli
 */
class sp_SQLUtils {
    private $conn;
    
    public function __construct($conn){
        $this->conn = $conn;
    }
    
    /**@
     * .. method:: vector_to_sql_tuple($ar)
     *    
     *    Transform an array in a valid SQL tuple. The array can contain only
     *    values of type int, float, boolean, string.
     *
     *    :param $ar: an array to convert (only array values are used)
     *    :type $ar:  array
     *
     *    :return: the generated SQL snippet
     *    :rtype:  string
     * 
     *    Example:
     *    
     *    .. code-block:: none
     *       
     *       php> $conn = new mysqli(  connection data )
     *       php> $sql_utils = new sp_SQLUtils($conn)
     *       php> echo $sql_utils->vector_to_sql_tuple(array("i'm a string", 28, NULL, FALSE));
     *       ( 'i\'m a string', '28', NULL, FALSE )
     * 
     */
    public function vector_to_sql_tuple($ar) {
        $tmp = array();
        foreach($ar as $value) {
            if ($value === NULL) $tmp[] = "NULL";
            else if (is_bool($value)) $tmp[] = ($value ? "TRUE" : "FALSE");
            else $tmp[] = "'".($this->conn->real_escape_string($value))."'";
        }
        return sprintf("( %s )", join(",", $tmp));
    }
    
    /**@
     * .. method:: array_to_sql_assignments($ar[,$sep=","])
     *    
     *    Convert an associative array in a series of "columnName = value" expressions
     *    as valid SQL.
     *    The expressions are separated using the parameter $sep (defaults to ",").
     *    The array can contain only values of type int, float, boolean, string.
     *
     *    :param $ar: an associative array to convert
     *    :type $ar:  array
     *    :param $sep: expression separator
     *    :type $sep:  string
     *
     *    :return: the generated SQL snippet
     *    :rtype:  string
     *
     *    Example:
     *    
     *    .. code-block:: none
     *       
     *       php> $conn = new mysqli( connection data )
     *       php> $sql_utils = new sp_SQLUtils($conn)
     *       php> $myArray = array("city address" => "main street", "married" => FALSE);
     *       php> echo $sql_utils->array_to_sql_assignments($myArray);
     *        `city address` = 'main street' , `married` = FALSE
     *       php> echo $sql_utils->array_to_sql_assignments($myArray, "AND");
     *        `city address` = 'main street' AND `married` = FALSE 
     */
    public function array_to_sql_assignments($ar,$sep=",") {
        $tmp = array();
        foreach($ar as $key => $value) {
            if ($value === NULL) $value = "NULL";
            else if (is_bool($value)) $value = ($value ? "TRUE" : "FALSE");
            else $value =  "'".($this->conn->real_escape_string($value))."'";
            
            $tmp[] = sprintf(" `%s` = %s ", str_replace("`", "``", $key), $value);
        }
        return join($sep, $tmp);
    }
    
    /**@
     * .. method:: flush_results()
     *    
     *    Empty connection results of the last single or multi query.
     *    If the last query generated an error, a sp_MySQL_Error exception
     *    is raised.
     *    Mostly useful after calling sql functions or procedures.
     */
    public function flush_results(){
        $conn = $this->conn;
        while($conn->more_results()) {
            if ($result = $conn->use_result()) $result->close();
            $conn->next_result();
        }
        
        if ($conn->errno) throw new sp_MySQL_Error($conn);
    }
    
    /**@
     * .. method:: get_table_fields($table_name)
     *    
     *    Retrieve the names of the fields in a table.
     *    
     *    :param $table_name: name of the table
     *    :type $table_name:  string
     *
     *    :return: fields' names
     *    :rtype:  array
     *    
     *    .. warning::
     *       The table name is not sanitized at all, use this method only
     *       if you're directly providing the correct name, otherwise
     *       sql injection is a given.
     */
    public function &get_table_fields($table_name){
        $table_name = str_replace('`', '``', $table_name);
        
        $result = $this->conn->query("SHOW COLUMNS FROM `{$table_name}`;", MYSQLI_STORE_RESULT);
        if (!$result) throw new sp_MySQL_Error($this->conn);
        
        $fields = array();
        while($row = $result->fetch_array(MYSQLI_ASSOC)) {
            $fields[] = $row["Field"];
        }
        $result->close();
        return $fields;
    }
    
    /**@
     * .. method:: table_exists($table_name[,$db_name=NULL])
     *    
     *    Check if a table exists.
     *    
     *    :param $table_name: name of the table
     *    :type $table_name:  string
     *    :param $db_name: name of the database (if different from the current in use)
     *    :type $db_name:  string
     *    
     *    :return: TRUE if exists, FALSE otherwise
     *    :rtype:  boolean
     */
    public function table_exists($table_name,$db_name=NULL){
        if (!$db_name) {
            $result = $this->conn->query("SELECT DATABASE()", MYSQLI_STORE_RESULT);
            if (!$result) throw new sp_MySQL_Error($this->conn);
            $row = $result->fetch_row();
            $db_name = $row[0];
            $result->close();
        }
        
        $result = $this->conn->query("
            SELECT COUNT(*)
            FROM information_schema.tables 
            WHERE table_schema = '{$db_name}' 
            AND ".($this->array_to_sql_assignments(array(
                 "table_name" => $table_name)))
        );
        
        if (!$result) throw new sp_MySQL_Error($this->conn);
        $row = $result->fetch_row();
        $result->close();
        
        return $row[0] != 0;
    }
}

/**@
 * .. class:: sp_Lib
 *    
 *    Generic utilities
 */
class sp_Lib {
    
    /**@
     * .. staticmethod:: map_method($array,$obj,$methodName)
     *    
     *    Call an object method on each item in an array and return an array
     *    with the results.
     *    
     *    :param $array: values to pass to the method
     *    :type $array:  array
     *    :param $obj: an object instance
     *    :type $obj:  object
     *    :param $methodName: a callable method of $obj
     *    :type $methodName:  string
     *    
     *    :return: the computed results
     *    :rtype:  array
     */
    public static function &map_method(&$array, $obj, $methodName) {
        $tmp = array();
        foreach ($array as $item){
            $tmp[] = $obj->$methodName($item);
        }
        return $tmp;
    }
}


/**!
 * .. class:: BaobabNode($id,$lft,$rgt,$parentId[,$fields=NULL])
 *    
 *    Node of a Baobab tree
 *
 *    :param $id: the node id
 *    :type $id: int
 *    :param $lft: the node left bound
 *    :type $lft: int
 *    :param $rgt: the node right bound
 *    :type $rgt: int
 *    :param $parentId: the parent's node id, if any
 *    :type $parentId: int or NULL
 *    :param $fields_values: additional fields of the node (mapping fieldName => value)
 *    :type $fields_values: array or NULL
 *
 *    **Attributes**:
 *       **id** int, node id
 *       
 *       **lft**  int, left value
 *       
 *       **rgt**  int, right value
 *       
 *       **parentNode**  int, the parent node id
 *       
 *       **fields_values**  array, additional fields of the node
 *       
 *       **children** array, instances of BaobabNode children of the current node 
 *
 *    .. note::
 *       this class doesn't have database interaction, its purpose is
 *       just to have a runtime representation of a Baobab tree. The data
 *       inserted is supposed to be valid in his tree (e.g. $this->lft cant'
 *       be -1 or major of any node right value)
 * 
 */
class BaobabNode {
    public $id;
    public $lft;
    public $rgt;
    public $parentNode;
    public $fields_values;
    public $children;

    public function __construct($id,$lft,$rgt,$parentNode,&$fields_values=NULL) {
        $this->id = $id;
        $this->lft = $lft;
        $this->rgt = $rgt;
        $this->parentNode = $parentNode;
        $this->fields_values = $fields_values;
        
        $this->children = array();
    }
    
    /**!
     * .. method:: appendChild($child)
     *
     *    Add a node as last sibling of the node's children.
     *
     *    :param $child: append a node to the list of this node children
     *    :type $child: :class:`BaobabNode`
     *
     */
    public function appendChild($child) {
        $this->children[] = $child;
    }
    
    /**!
     * .. method:: stringify([$fields=NULL[,$diveInto=TRUE[,$indentChar=" "[,$indentLevel=0]]]])
     *
     *    Return a representation of the tree as a string.
     *
     *    :param $fields: what node fields include in the output. id, lft and rgt
     *                      are always included.
     *    :type $fields:  array
     *    :param $diveInto: wheter to continue with node's children or not
     *    :type $diveInto:  boolean
     *    :param $indentChar: character to use to indent
     *    :type $indentChar:  string
     *    :param $indentLevel: how deep we are indenting
     *    :type $indentLevel:  int
     *    
     *    :return: tree or node representation
     *    :rtype:  string
     *    
     *    .. note::
     *       $indentLevel is meant for internal use only.
     *
     *    .. todo::
     *       $fields is currently unused
     */
    public function stringify($fields=NULL,$diveInto=TRUE,$indentChar=" ",$indentLevel=0) {
        // XXX TODO $fields is not used at present (and remove the notice from documentation)
        $out = str_repeat($indentChar, $indentLevel*4)."({$this->id}) [{$this->lft}, {$this->rgt}]";
        if (!$diveInto) return $out;
        foreach ($this->children as $child) {
            $out .= "\n".$child->stringify($fields, TRUE, $indentChar, $indentLevel+1);
        }
        return $out;
    }
    
    /**!
     * .. method:: isRightmost()
     *    
     *    Check if the node is rightmost between his siblings.
     *
     *    :return: whether if the node is the rightmost or not
     *    :rtype:  boolean
     *
     *    .. note:
     *       root node is considered to be rightmost
     */
    public function isRightmost(){
        if (!$this->parentNode) return TRUE;
        
        return $this->parentNode->children[count($this->parentNode->children)-1]->id === $this->id;
    }
    
    /**!
     * .. method:: isLeftmost()
     *    
     *    Check if the node is leftmost between his siblings.
     *
     *    :return: whether if the node is the leftmost or not
     *    :rtype:  boolean
     *    
     *    .. note:
     *       root node is considered to be leftmost
     */
    public function isLeftmost(){
        if (!$this->parentNode) return TRUE;
        
        return $this->parentNode->children[0]->id === $this->id;
    }
}

/**!
 * Baobab
 * ------
 */

/**!
 * .. class:: Baobab($db,$forest_name[,$tree_id=NULL])
 *    
 *    This class lets you create, populate search and destroy a tree stored
 *    using the Nested Set Model described by Joe Celko.
 *
 *    :param $db: mysqli database connection in object oriented style
 *    :type $db:  an instance of mysqli_connect
 *    :param $forest_name: name of the forest, will be used to create an homonymous
 *                       table that will hold the data.
 *    :type $forest_name: string
 *    :param $tree_id: id of the tree (to create a new tree it must be NULL or an unused tree_id number).
 *                      If there is only a tree in the table, you can load it with -1;
 *                      A new tree created using NULL has $tree_id = 0 until an appendChild occurs.
 *    :type $tree_id: int or NULL
 *
 *    **Attributes**:
 *       **tree_id** int, id of the tree (0 means it's new with no nodes added yet)
 */
class Baobab  {
    private $_refresh_fields;
    private $_errors;
    private static $_version = "1.3.1";
    
    protected $db;
    protected $forest_name;
    protected $sql_utils;
    protected $fields;
    
    public $tree_id;
    
    public function __construct($db,$forest_name,$tree_id=NULL) {
        $this->db = $db;
        $this->sql_utils = new sp_SQLUtils($db);
        $this->forest_name = $forest_name;
        $this->fields = array();
        $this->_refresh_fields = TRUE;
        
        // load error's information from db (if tables were created)
        try { $this->_load_errors();
        } catch (sp_Error $e) {}
        
        // if $tree_id is -1 we suppose there is one and only one tree yet in
        //   the table, and we automatically retrieve his id
        if ($tree_id== -1 ) {
            $query = "SELECT DISTINCT tree_id FROM {$this->forest_name} LIMIT 2";
            if ($result = $this->db->query($query, MYSQLI_STORE_RESULT)) {
                $num_trees = $result->num_rows;
                
                if ($num_trees == 1) {
                    // one and only, let's use it
                    $row = $result->fetch_row();
                    $tree_id = intval($row[0]);
                }
                else if ($num_trees == 0) {
                    // no trees ? it will be the first
                    $tree_id = 0;
                }
                $result->close();
                
                if ($num_trees>1) {
                    throw new sp_Error("Too many trees found");
                }
                
            } else {
                if ($this->db->errno == 1146) {
                    // table does not exist (skip), so it will be the first tree
                } else throw new sp_MySQL_Error($this->db);
            }
        }
        
        $this->tree_id = intval($tree_id);
        
        $this->build();
    }
    
    /**
     * .. method:: _load_errors()
     *
     *    Fill the member $_errors with informations about error codes and 
     *      messages
     */
    private function _load_errors(){
        $this->_errors = array("by_code" => array(), "by_name" => array());
        if ($result = $this->db->query("SELECT code,name,msg FROM Baobab_Errors")) {
            
            while($row = $result->fetch_assoc()) {
                $this->_errors["by_code"][$row["code"]] = $row;
                $this->_errors["by_name"][$row["name"]] = &$this->_errors["by_code"][$row["code"]];
            }
            $result->close();
            
        } else throw new sp_Error("Cannot read info about errors (d'oh!)");
    }
    
    /**
     * .. method:: _sql_check_fields($fields)
     *
     *    Check that the supplied fields exists in this Baobab table.
     *    Throws an sp_Error if something is wrong.
     *
     *    :param $fields: names of the fields to check for
     *    :type $fields:  array
     *
     *    :note: it is public but for internal use only (see static methods import/export )
     *
     */
    public function _sql_check_fields(&$fields) {
        
        $real_fields = $this->_get_fields();
        // check that the requested fields exist
        foreach($fields as $fieldName) {
            if (!isset($real_fields[$fieldName])) throw new sp_Error("`{$fieldName}` wrong field name for table `{$this->forest_name}`");
        }
    }
    
    /**
     * .. method:: _get_fields()
     *
     *    Return the fields' names of the Baobab main table.
     *    It mantains the fields array in memory, and refresh it if the
     *    private variable _refresh_fields is TRUE
     *
     *    :return: associative array fieldName => TRUE
     *    :rtype:  array
     *
     *    .. note::
     *       An associative array is being returned because it's quicker to
     *       check for fields existence inside it.
     *     
     *    .. note::
     *       it is public but for internal use only (see static methods import/export )
     */
    public function &_get_fields(){
        
        if ($this->_refresh_fields) {
            $fields = $this->sql_utils->get_table_fields($this->forest_name);
            $this->fields = array();
            for($i=0,$il=count($fields);$i<$il;$i++) {
                $this->fields[$fields[$i]] = TRUE;
            }
            $this->_refresh_fields = FALSE;
        }
        
        return $this->fields;
    }
    
    /**!
     * .. method:: build()
     *
     *    Apply the database schema
     *    
     *    :return: TRUE if any table was added, FALSE if the build was skipped
     *             (e.g. if the tree yet exists calling build will do nothing
     *             and return FALSE).
     *    :rtype:  boolean
     *
     *    .. note::
     *       This is automatically called while instantiating the class.
     *       If the version of the library didn't change in the meantime, nothing
     *       happens, otherwise it will throw an exception to inform you to
     *       use :class:`Baobab:upgrade()`
     */
    public function build() {
        
        // get the current sql version from the loaded db (if any)
        $sql_version = NULL;
        if (isset($this->_errors["by_name"]["VERSION"]))
            $sql_version = $this->_errors["by_name"]["VERSION"]["msg"];
        
        // check if the tree was yet built
        $treeExists = FALSE;
        if ($result = $this->db->query("SELECT name FROM Baobab_ForestsNames WHERE name='{$this->forest_name}'")) {
            $treeExists = $result->num_rows > 0;
            $result->close();
        } else {
             if ($this->db->errno !== 1146) { // do not count "missing table" as an error
                throw new sp_MySQL_Error($this->db);
            }
        }
        
        // if there are tables at an older version, ask to upgrade
        if ($sql_version && $sql_version != self::$_version) {
            // old schema found at a lower version, ensure user upgrade the database willingly
            $codename = "VERSION_NOT_MATCH";
            $errno = $this->_errors["by_name"][$codename]["code"];
            throw new sp_Error(sprintf(
                "[%s] %s", $codename, $this->_errors["by_code"][$errno]["msg"]).
                " (database tables v. ".$sql_version.", library v. ".self::$_version."). Please use Baobab::upgrade().",
                $errno);
        }
        
        // build the tree only if the database schema not yet exists
        if (!$treeExists) {
            $sql = file_get_contents(BAOBAB_SQL_DIR.DIRECTORY_SEPARATOR."schema_baobab.sql");
            if (!$this->db->multi_query(str_replace("GENERIC", $this->forest_name, $sql))) {
                throw new sp_MySQL_Error($this->db);
            }
            
            $this->sql_utils->flush_results();
            
            // load or reload the errors table
            $this->_load_errors();
        }
        
        return ! $treeExists;
    }
    
    /**!
     * .. staticmethod:: upgrade($db[,$untilVersion=NULL])
     * 
     *    Upgrade the database schema of all the trees to reflect the current
     *    library's version.
     *    
     *    :param $db: mysqli database connection object
     *    :type $db:  mysqli
     *    :param $untilVersion: version at which stop (or NULL to update to last version)
     *    :type $untilVersion:  string or NULL
     *
     *    .. warning::
     *       To avoid any possible data loss you should backup your database's
     *       tables first.
     *
     *    .. warning::
     *       For each release read release notes and check what's going to happen
     *       when you upgrade your database (see sql/upgrade/* files).
     *       Eventually stop with $untilVersion at a certain point to apply
     *       needed tweaks.
     */
    public static function upgrade($db,$untilVersion=NULL){
        
        $db->query("START TRANSACTION");
        try {
            
            $currentDbVersion = NULL;
            
            // get the current database version
            if ($result = $db->query("SELECT msg FROM Baobab_Errors WHERE name='VERSION'")) {
                $row = $result->fetch_assoc();
                $currentDbVersion = $row["msg"];
                $result->close();
            } else throw new sp_Error("You cannot upgrade if you didn't ever built before");
            
            // upgrade only if current db version is not yet the most recent
            //  or we wasn't asked to stop at the current version
            if ($currentDbVersion == self::$_version ||
                $untilVersion == $currentDbVersion) return;
            
            $upgradeList = array(); // name of the files with sql data to run
            $versions = array(); // mapping fileName -> version to which it will update
            
            $startingFileToUpgrade = NULL; // of the files in $upgradeList, start to update from this one
            
            // get all the upgrade files
            $upgradeDir = BAOBAB_SQL_DIR.DIRECTORY_SEPARATOR."upgrade";
            $pattern = "/^\d{8}_from_(?P<from>.+?)_to_(?P<to>.+?)\.sql$/";
            
            if ($handle = opendir($upgradeDir)) {
                while (FALSE !== ($fName = readdir($handle))) {
                    if ($fName == "." || $fName == "..") continue;
                    
                    $matches = array();
                    if (preg_match($pattern, $fName, $matches)) {
                        $upgradeList[] = $fName;
                        $versions[$fName] = $matches["to"];
                        
                        if ($matches["from"] == $currentDbVersion)
                            $startingFileToUpgrade = $fName;
                    }
                }
                closedir($handle);
                
            } else {
                throw new sp_Error(sprintf('Can not read the directory "%s"', $upgradeDir));
            }
            
            if (empty($upgradeList)) return;
            
            sort($upgradeList);
            
            // get the starting version (it wasn't added while scanning the directory)
            $matches = array();
            preg_match($pattern, $upgradeList[0], $matches);
            $baseVersion = $matches["from"];
            
            if ($untilVersion == $baseVersion) return;
            
            $pathToUntilVersion = array_search($untilVersion, $versions);
            
            if ($untilVersion !== NULL && FALSE === $pathToUntilVersion)
                throw new sp_Error("You requested to stop at an unknown version ({$untilVersion})");
            
            else if ($startingFileToUpgrade == NULL)
                throw new sp_Error("Couldn't find a file to upgrade from the current version ({$currentDbVersion})");
            
            // filter the availabe upgrades to remove the older than the current version
            $upgradeList = array_slice($upgradeList, array_search($startingFileToUpgrade, $upgradeList));
            
            // get the names of the existing forests
            $forestNames = array();
            if ($result = $db->query("SELECT name FROM Baobab_ForestsNames", MYSQLI_STORE_RESULT)) {
                if ($result->num_rows) {
                    $row = $result->fetch_row();
                    $forestNames[] = $row[0];
                }
                $result->close();
            
            } else throw new sp_MySQL_Error($db);
            
            $sql_utils = new sp_SQLUtils($db);
            
            // upgrade each tree until the choosen (or last) version
            foreach($upgradeList as $fName){
                $sql = file_get_contents($upgradeDir.DIRECTORY_SEPARATOR.$fName);
                
                foreach($forestNames as $name) {
                    if (!$db->multi_query(str_replace("GENERIC", $name, $sql))) {
                        throw new sp_MySQL_Error($db);
                    }
                    $sql_utils->flush_results();
                }
                
                if ($pathToUntilVersion == $fName) break;
            }
            
        
        } catch (Exception $e) {
            // whatever happens we must rollback
            $db->query("ROLLBACK");
            throw $e;
        }
        
        $result = $db->query("COMMIT");
        if (!$result) throw new sp_MySQL_Error($db);
    }
    
    /**!
     * .. method:: destroy()
     *
     *    Remove every table, procedure or view that were created via
     *      :class:`Baobab.build` for the current tree name
     *
     *    :param $removeDataTable: unless this value is TRUE, avoid to delete 
     *                             the table that holds the tree data
     *    :type $removeDataTable:  boolean
     *    
     */
    public function destroy($removeDataTable=FALSE) {
        
        $this->db->query("START TRANSACTION");
        
        try {
            $forestsExist = TRUE;
            
            if (!$this->db->multi_query(str_replace("GENERIC", $this->forest_name,"
                    DROP PROCEDURE IF EXISTS Baobab_GENERIC_getNthChild;
                    DROP PROCEDURE IF EXISTS Baobab_GENERIC_MoveSubtree_real;
                    DROP PROCEDURE IF EXISTS Baobab_GENERIC_MoveSubtreeAtIndex;
                    DROP PROCEDURE IF EXISTS Baobab_GENERIC_MoveSubtreeBefore;
                    DROP PROCEDURE IF EXISTS Baobab_GENERIC_MoveSubtreeAfter;
                    DROP PROCEDURE IF EXISTS Baobab_GENERIC_MoveSubtree_Different_Trees;
                    DROP PROCEDURE IF EXISTS Baobab_GENERIC_InsertChildAtIndex;
                    DROP PROCEDURE IF EXISTS Baobab_GENERIC_insertBefore;
                    DROP PROCEDURE IF EXISTS Baobab_GENERIC_insertAfter;
                    DROP PROCEDURE IF EXISTS Baobab_GENERIC_AppendChild;
                    DROP PROCEDURE IF EXISTS Baobab_GENERIC_DropTree;
                    DROP PROCEDURE IF EXISTS Baobab_GENERIC_Close_Gaps;
                    DROP VIEW IF EXISTS GENERIC_AdjTree;
                    ".
                    ($removeDataTable ? "DROP TABLE IF EXISTS GENERIC;" : "").
                    "DELETE FROM Baobab_ForestsNames WHERE name='GENERIC';"
                    ))) {
                
                throw new sp_MySQL_Error($this->db);
            }
            
            try { $this->sql_utils->flush_results(); }
            catch (Exception $e) {
                
                if ($this->db->errno === 1146) { // do not count "missing table" as an error
                    // died because BaobabTreeNames was missing (other drop are IF EXISTS)
                    $forestsExist = FALSE;
                }
                else throw $e;
            }
            
            if ($forestsExist) {
                
                if ($result = $this->db->query("SELECT COUNT(*) FROM Baobab_ForestsNames", MYSQLI_STORE_RESULT)) {
                    $dropIt = FALSE;
                    if ($result->num_rows) {
                        $row = $result->fetch_row();
                        $dropIt = ($row[0] == 0);
                    }
                    
                    $result->close();
                    
                    if ($dropIt) {
                        // there aren't anymore trees, lets remove the common tables too
                        if (!$this->db->multi_query("
                            DROP TABLE Baobab_ForestsNames;
                            DROP TABLE Baobab_Errors;
                            DROP FUNCTION Baobab_getErrCode;
                            ")) {
                            throw new sp_MySQL_Error($this->db);
                        } else {
                            $this->sql_utils->flush_results();
                        }
                    }
                
                } else throw new sp_MySQL_Error($this->db);
            }
            
        } catch (Exception $e) {
            // whatever happens we must rollback
            $this->db->query("ROLLBACK");
            throw $e;
        }
        
        $result = $this->db->query("COMMIT");
        if (!$result) throw new sp_MySQL_Error($this->db);
        
        // reset errors data
        try { $this->_load_errors();
        } catch (sp_Error $e) {}
    }
    
    /**!
     * .. method:: clean()
     *    
     *    Delete all the records about this tree.
     *    Other trees in the same table will be unaffected.
     *
     */
    public function clean() {
        if (!$this->db->query("
            DELETE FROM {$this->forest_name}
            WHERE tree_id={$this->tree_id}"
            ) && $this->db->errno !== 1146) { // do not count "missing table" as an error
            
            throw new sp_MySQL_Error($this->db);
        }
    }
    
    /**!
     * .. staticmethod:: cleanAll($db,$forest_name)
     *    
     *    Delete all the records in $forest_name
     *    
     *    :param $db: mysqli database connection in object oriented style
     *    :type $db:  an instance of mysqli_connect
     *    :param $forest_name: name of the forest, equals to the name of the table
     *                       holding the data
     *    :type $forest_name:  string
     */
    public static function cleanAll($db, $forest_name){
        // delete all records, ignoring "missing table" error if happening
        if (!$db->query("DELETE FROM {$forest_name}") && $db->errno !== 1146) {
            throw new sp_MySQL_Error($db);
        }
    }

    /**!
     * .. method:: getRoot()
     *    
     *    Return the id of the first node of the tree.
     *
     *    :return: id of the root, or NULL if empty
     *    :rtype:  int or NULL
     *
     */
    public function getRoot(){

        $query = "
          SELECT id AS root
          FROM {$this->forest_name}
          WHERE tree_id={$this->tree_id} AND lft = 1;
        ";
        
        $out = NULL;
        
        if ($result = $this->db->query($query, MYSQLI_STORE_RESULT)) {
            if ($result->num_rows) {
                $row = $result->fetch_row();
                $out = intval($row[0]);
            }
            $result->close();
        
        } else throw new sp_MySQL_Error($this->db);
        
        return $out;
    }
    
    /**!
     * .. method:: getParent($id_node)
     *    
     *    Return the id of the node's parent.
     *
     *    :return: id of the parent, or NULL if node is root
     *    :rtype:  int or NULL
     *
     */
    public function getParent($id_node){
        $id_node = intval($id_node);
        
        $query = "
          SELECT parent
          FROM {$this->forest_name}_AdjTree
          WHERE tree_id={$this->tree_id} AND child = {$id_node};
        ";

        $out = NULL;

        if ($result = $this->db->query($query, MYSQLI_STORE_RESULT)) {
            $row = NULL;
            if ($result->num_rows) $row = $result->fetch_row();
            $result->close();
            
            if ($row) {
                if ($row[0] === NULL) $out = NULL;
                else $out = intval($row[0]);
            }
            else {
                throw new sp_Error(sprintf("[%s] %s",
                    $this->_errors["by_code"][1400]["name"],
                    $this->_errors["by_code"][1400]["msg"]), 1400);
            }
        
        } else throw new sp_MySQL_Error($this->db);
        
        return $out;
    }

    /**!
     * .. method:: getSize([$id_node=NULL])
     *    
     *    Retrieve the number of nodes of the subtree starting at $id_node (or
     *    at tree root if $id_node is NULL).
     *    
     *    :param $id_node: id of the node to count from (or NULL to count from root)
     *    :type $id_node:  int or NULL
     *    
     *    :return: the number of nodes in the selected subtree
     *    :rtype:  int
     */
    public function getSize($id_node=NULL) {

        $query = "
          SELECT (rgt-lft+1) DIV 2
          FROM {$this->forest_name}
          WHERE ". ($id_node !== NULL ? "id = ".intval($id_node) : "lft = 1").
                " AND tree_id={$this->tree_id}";
        
        $out = 0;

        if ($result = $this->db->query($query, MYSQLI_STORE_RESULT)) {
            $row = $result->fetch_row();
            $out = intval($row[0]);
            $result->close();

        } else throw new sp_MySQL_Error($this->db);

        return $out;

    }
    
    /**!
     * .. method:: getDescendants([$id_node=NULL])
     *    
     *    Retrieve all the descendants of a node
     *    
     *    :param $id_node: id of the node whose descendants we're searching for,
     *                     or NULL to start from the tree root.
     *    :type $id_node:  int or NULL
     *    
     *    :return: the ids of node's descendants, in ascending order
     *    :rtype:  array
     *
     */
    public function &getDescendants($id_node=NULL) {

        if ($id_node === NULL) {
            // we search for descendants of root
            $query = "SELECT id FROM {$this->forest_name}
                    WHERE tree_id={$this->tree_id} AND lft <> 1 ORDER BY id";
        } else {
            // we search for a node descendants
            $id_node = intval($id_node);
            
            $query = "
              SELECT id
              FROM {$this->forest_name}
              WHERE tree_id = {$this->tree_id}
                AND lft > (SELECT lft FROM {$this->forest_name} WHERE id = {$id_node})
                AND rgt < (SELECT rgt FROM {$this->forest_name} WHERE id = {$id_node})
              ORDER BY id
            ";
        }
        
        $ar_out = array();

        if ($result = $this->db->query($query, MYSQLI_STORE_RESULT)) {
            while($row = $result->fetch_row()) {
                array_push($ar_out, intval($row[0]));
            }
            $result->close();

        } else throw new sp_MySQL_Error($this->db);

        return $ar_out;

    }
    
    /**!
     * .. method:: getLeaves([$id_node=NULL])
     *
     *    Find the leaves of a subtree.
     *    
     *    :param $id_node: id of a node or NULL to start from the tree root
     *    :type $id_node:  int or NULL
     *
     *    :return: the ids of the leaves, ordered from left to right
     *    :rtype:  array
     */
    public function &getLeaves($id_node=NULL){
        
        $query = "
          SELECT id AS leaf
          FROM {$this->forest_name}
          WHERE tree_id={$this->tree_id} AND lft = (rgt - 1)";
        
        if ($id_node !== NULL) {
            // check only leaves of a subtree adding a "where" condition
            
            $id_node = intval($id_node);
        
            $query .= " AND lft > (SELECT lft FROM {$this->forest_name} WHERE id = {$id_node}) ".
                      " AND rgt < (SELECT rgt FROM {$this->forest_name} WHERE id = {$id_node}) ";
        }
        
        $query .= " ORDER BY lft";
        
        $ar_out = array();
        
        if ($result = $this->db->query($query, MYSQLI_STORE_RESULT)) {
            while($row = $result->fetch_row()) {
                array_push($ar_out, intval($row[0]));
            }
            $result->close();
            
        } else throw new sp_MySQL_Error($this->db);

        return $ar_out;
    }
    
    /**!
     * .. method:: getLevels()
     *
     *    Find at what level of the tree each node is.
     *    
     *    :param $id_node: id of a node or NULL to start from the tree root
     *    :type $id_node:  int or NULL
     *
     *    :return: associative arrays with id => number, level => number, unordered
     *    :rtype:  array
     *
     *    .. note::
     *       tree root is at level 0
     *
     */
    public function &getLevels(){
    
        $query = "
          SELECT T2.id as id, (COUNT(T1.id) - 1) AS level
          FROM {$this->forest_name} AS T1 JOIN {$this->forest_name} AS T2
               on T1.tree_id={$this->tree_id} AND T1.tree_id = T2.tree_id
          WHERE T2.lft BETWEEN T1.lft AND T1.rgt
          GROUP BY T2.lft
          ORDER BY T2.lft ASC;
        ";

        $ar_out = array();

        if ($result = $this->db->query($query, MYSQLI_STORE_RESULT)) {
            while($row = $result->fetch_assoc()) {
                array_push($ar_out, array("id" => intval($row["id"]), "level" => intval($row["level"])));
            }
            $result->close();

        } else throw new sp_MySQL_Error($this->db);

        return $ar_out;
    }

    /**!
     * .. method:: getPath($id_node[,$fields=NULL[,$squash=FALSE]])
     *    
     *    Find all the nodes between tree root and a node.
     *    
     *    :param $id_node: id of the node used to calculate the path to
     *    :type $id_node:  int
     *    :param $fields: if not NULL, a string with a Baobab tree field name or 
     *                      an array of field names
     *    :type $fields:  mixed
     *    :param $squash: if TRUE the method will return an array with just the 
     *                      values of the first field in $fields (if $fields is
     *                      empty it will default to "id" )
     *    :type $squash:  boolean
     *
     *    :return: sequence of associative arrays mapping for each node
     *               fieldName => value, where field names are the one present
     *               in $fields plus the field "id" (unless $squash was set),
     *               ordered from root to $id_node
     *    :rtype:  array
     *    
     *    Example (considering a tree with two nodes with a field 'name'):
     *    
     *    .. code-block:: none
     *       
     *       php> $tree->getPath(2, array("name"))
     *       array([0] => array([id] => 1, [name] => 'rootName'), array([id] => 2, [name] => 'secondNodeName']))
     *       php> join("/", $tree->getPath(2, "name", TRUE))
     *       "rootName/secondNodeName"
     * 
     */
    public function &getPath($id_node,$fields=NULL,$squash=FALSE){
        $id_node = intval($id_node);
        
        if (empty($fields)) {
            if ($squash) $fields = array("id");
            else $fields = array(); // ensure it is not NULL
        }
        else if (is_string($fields)) $fields = array($fields);
        
        // append the field "id" if missing
        if (FALSE === array_search("id", $fields)) $fields[] = "id";
        
        $this->_sql_check_fields($fields);
        
        $fields_escaped = array();
        foreach($fields as $fieldName) {
            $fields_escaped[] = sprintf("`%s`", str_replace("`", "``", $fieldName));
        }
        
        $query = "".
        " SELECT ".join(",", $fields_escaped).
        " FROM {$this->forest_name}".
        " WHERE tree_id={$this->tree_id} AND ( SELECT lft FROM {$this->forest_name} WHERE id = {$id_node} ) BETWEEN lft AND rgt".
        " ORDER BY lft";

        $result = $this->db->query($query, MYSQLI_STORE_RESULT);
        if (!$result) throw new sp_MySQL_Error($this->db);

        $ar_out = array();
        if ($squash) {
            reset($fields);
            $fieldName = current($fields);
            while($rowAssoc = $result->fetch_assoc()) $ar_out[] = $rowAssoc[$fieldName];
            
        } else {
            while($rowAssoc = $result->fetch_assoc()) {
                $tmp_ar = array();
                foreach($fields as $fieldName) {
                    $tmp_ar[$fieldName] = $rowAssoc[$fieldName];
                }
                $ar_out[] = $tmp_ar;
            }
        }
        
        $result->close();

        return $ar_out;
    }
    
    /**!
     * .. method:: getFirstNChildren($id_parent[,$howMany=NULL[,$fromLeftToRight=TRUE]])
     *
     *    Find the first n node's children starting from left or right.
     *
     *    :param $id_parent: id of the parent node
     *    :type $id_parent:  int
     *    :param $howMany: maximum number of children to retrieve (NULL means all)
     *    :type $howMany:  int or NULL
     *    :param $fromLeftToRight: what order the children must follow
     *    :type $fromLeftToRight:  boolean
     *    
     *    :return: ids of the children nodes, ordered from left to right
     *    :rtype:  array
     *
     */
    public function &getFirstNChildren($id_parent,$howMany=NULL,$fromLeftToRight=TRUE){
        $id_parent = intval($id_parent);
        $howMany = intval($howMany);
        
        $query = " SELECT child FROM {$this->forest_name}_AdjTree ".
                 " WHERE parent = {$id_parent} ".
                 " ORDER BY lft ".($fromLeftToRight ? 'ASC' : 'DESC').
                 ($howMany ? " LIMIT $howMany" : "");
        
        $result = $this->db->query($query, MYSQLI_STORE_RESULT);
        if (!$result) throw new sp_MySQL_Error($this->db);
        
        $ar_out = array();
        while($row = $result->fetch_row()) {
            $ar_out[] = intval($row[0]);
        }
        $result->close();
        
        return $ar_out;
    }
    
    /**!
     * .. method:: getChildren($id_parent)
     *
     *    Find all node's children
     *
     *    :param $id_parent: id of the parent node
     *    :type $id_parent:  int
     *    
     *    :return: ids of the children nodes, ordered from left to right
     *    :rtype:  array
     *
     */
    public function &getChildren($id_parent) {
        return $this->getFirstNChildren($id_parent);
    }
    
    /**!
     * .. method:: getFirstChild($id_parent)
     *
     *    Find the leftmost child of a node
     *
     *    :param $id_parent: id of the parent node
     *    :type $id_parent:  int
     *    
     *    :return: id of the leftmost child node, or 0 if not found
     *    :rtype:  int
     *
     */
    public function getFirstChild($id_parent) {
        $res = $this->getFirstNChildren($id_parent, 1, TRUE);
        return empty($res) ? 0 : current($res);
    }
    
    /**!
     * .. method:: getLastChild($id_parent)
     *
     *    Find the rightmost child of a node
     *
     *    :param $id_parent: id of the parent node
     *    :type $id_parent:  int
     *    
     *    :return: id of the rightmost child node, or 0 if not found
     *    :rtype:  int
     *
     */
    public function getLastChild($id_parent) {
        $res = $this->getFirstNChildren($id_parent, 1, FALSE);
        return empty($res) ? 0 : current($res);
    }
    
    /**!
     * .. method:: getChildAtIndex($id_parent, $index)
     *
     *    Find the nth child of a parent node
     *
     *    :param $id_parent: id of the parent node
     *    :type $id_parent:  int
     *    :param $index: position between his siblings (0 is first).
     *                   Negative indexes are allowed (-1 is the last sibling).
     *    :type $index:  int
     *    
     *    :return: id of the nth child node
     *    :rtype:  int
     *
     */
    public function getChildAtIndex($id_parent, $index){
        $id_parent = intval($id_parent);
        $index = intval($index);
        
        if (!$this->db->multi_query("
                CALL Baobab_{$this->forest_name}_getNthChild({$id_parent}, {$index}, @child_id, @error_code);
                SELECT @child_id as child_id, @error_code as error_code"))
                throw new sp_MySQL_Error($this->db);

        $res = $this->_readLastResult('child_id');
        return intval($res['child_id']);
    }
    
    
    /**!
     * .. method:: getTree([$className="BaobabNode"[,$appendChild="appendChild"]])
     *
     *    Create a tree from the database data.
     *    It's possible to use a default tree or use custom classes/functions
     *    (it must extends the class :class:`BaobabNode`)
     *
     *    :param $className: name of the class holding a node's information
     *    :type $className:  string
     *    :param $appendChild: method of $className to call to append a node
     *    :type $appendChild:  string
     *
     *    :return: a node instance
     *    :rtype:  instance of $className
     *
     */
    public function &getTree($className="BaobabNode", $appendChild="appendChild") {
        
        // this is a specialized version of the query found in getLevel()
        //   (the difference lying in the fact that here we retrieve all the
        //    fields of the table)
        $query = "
          SELECT (COUNT(T1.id) - 1) AS level ,T2.*
          FROM {$this->forest_name} AS T1 JOIN {$this->forest_name} AS T2
                on T1.tree_id={$this->tree_id} AND T1.tree_id = T2.tree_id
          WHERE T2.lft BETWEEN T1.lft AND T1.rgt
          GROUP BY T2.lft
          ORDER BY T2.lft ASC;
        ";
        
        $root = NULL;
        $parents = array();
        
        if ($result = $this->db->query($query, MYSQLI_STORE_RESULT)) {
            
            while($row = $result->fetch_assoc()) {
                
                $numParents = count($parents);
                
                $id = $row["id"];
                $lft = $row["lft"];
                $rgt = $row["rgt"];
                $level = $row["level"];
                $parentNode = count($parents) ? $parents[$numParents-1] : NULL;
                
                unset($row["id"]);
                unset($row["lft"]);
                unset($row["rgt"]);
                unset($row["level"]);
                
                $node = new $className($id, $lft, $rgt, $parentNode, $row);
                
                if (!$root) $root = $node;
                else $parents[$numParents-1]->$appendChild($node);
                
                if ($rgt-$lft != 1) { // not a leaf
                    $parents[$numParents] = $node;
                }
                else if (!empty($parents) && $rgt+1 == $parents[$numParents-1]->rgt) {
                    
                    $k = $numParents-1;
                    $me = $node;
                    while ($k>-1 && $me->rgt+1 == $parents[$k]->rgt) {
                        $me = $parents[$k];
                        unset($parents[$k--]);
                    }
                    
                    /*
                    // alternative way using levels ($parents would have both the parent node and his level)
                    
                    // previous parent is the first one with a level minor than ours
                    if ($parents[count($parents)-1][1] >= $level) {
                        // remove all the previous subtree "parents" until our real parent
                        for($i=count($parents)-1;$parents[$i--][1] >= $level;)
                            array_pop($parents);
                    }
                    */
                }
            }
            $result->close();
            
        } else throw new sp_MySQL_Error($this->db);
        
        return $root;
    }

    /**!
     * .. method:: deleteNode($id_node[,$close_gaps=True])
     *
     *    Delete a node and all of his children. If $close_gaps is TRUE, mantains
     *    the Modified Preorder Tree consistent closing gaps.
     *
     *    :param $id_node: id of the node to drop
     *    :type $id_node:  int
     *    :param $close_gaps: whether to close the gaps in the tree or not (default TRUE)
     *    :type $close_gaps:  boolean
     *
     *    .. warning::
     *       If the gaps are not closed, you can't use most of the API. Usually
     *       you want to avoid closing gaps when you're deleting different
     *       subtrees and want to update the numbering just once
     *       (see :class:`Baobab.closeGaps`)
     */
    public function deleteNode($id_node,$close_gaps=TRUE) {
        $id_node = intval($id_node);
        $close_gaps = $close_gaps ? 1 : 0;
        
        if (!$this->db->multi_query("CALL Baobab_{$this->forest_name}_DropTree({$id_node}, {$close_gaps})"))
            throw new sp_MySQL_Error($this->db);
        
        $this->sql_utils->flush_results();
        
    }
    
    /**!
     * .. method:: closeGaps
     *    
     *    Update right and left values of each node to ensure there are no
     *    gaps in the tree.
     *
     *    .. warning::
     *       This is a really slow function, use it only if needed (e.g.
     *       to delete multiple subtrees and close gaps just once)
     */
    public function closeGaps() {
        if (!$this->db->multi_query("CALL Baobab_{$this->forest_name}_Close_Gaps({$this->tree_id})"))
            throw new sp_MySQL_Error($this->db);
        
        $this->sql_utils->flush_results();

    }

    /**!
     * .. method:: getTreeHeight()
     *    
     *    Calculate the height of the tree
     *
     *    :return: the height of the tree
     *    :rtype:  int
     *    
     *    .. note::
     *       A tree with one node has height 1.
     * 
     */
    public function getTreeHeight(){
        
        $query = "
        SELECT MAX(level)+1 as height
        FROM (SELECT T2.id as id,(COUNT(T1.id)-1) as level
              FROM {$this->forest_name} as T1 JOIN {$this->forest_name} as T2
                   on T1.tree_id={$this->tree_id} AND T1.tree_id = T2.tree_id
              WHERE T2.lft  BETWEEN T1.lft AND T1.rgt
              GROUP BY T2.id
             ) as ID_LEVELS";
        
        $result = $this->db->query($query, MYSQLI_STORE_RESULT);
        if (!$result) throw new sp_MySQL_Error($this->db);

        $row = $result->fetch_row();
        $out = intval($row[0]);
        
        $result->close();
        return $out;
    }

    /**!
     * .. method:: updateNode($id_node,$fields_values)
     *    
     *    Update data associeted to a node
     *
     *    :param $id_node: id of the node to update
     *    :type $id_node:  int
     *    :param $fields_values: mapping fields => values to update
     *                     (only supported types are string, int, float, boolean)
     *    :type $fields_values:  array
     * 
     */
    public function updateNode($id_node,$fields_values){
        $id_node = intval($id_node);
        
        if (empty($fields_values)) throw new sp_Error("\$fields_values cannot be empty");
        
        $fields = array_keys($fields_values);
        $this->_sql_check_fields($fields);
        
        $query = "".
         " UPDATE {$this->forest_name}".
         " SET ".( $this->sql_utils->array_to_sql_assignments($fields_values) ).
         " WHERE id = $id_node";
        
        $result = $this->db->query($query, MYSQLI_STORE_RESULT);
        if (!$result) throw new sp_MySQL_Error($this->db);
    }
    
    /**!
     * .. method:: getNodeData($id_node[,$fields=NULL])
     *    
     *    Retrieve informations about a node.
     *    
     *    :param $id_node: id of the node
     *    :type $id_node:  int
     *    :param $fields: fields' names to read values from
     *    :type $fields:  array
     *
     *    :return: the informations found
     *    :rtype:  array
     * 
     */
    public function getNodeData($id_node,$fields=NULL){
        $id_node = intval($id_node);
        
        if (!empty($fields)) $this->_sql_check_fields($fields);
        
        $query = "".
        " SELECT ".($fields === NULL ? "*" : join(",", $fields)).
        " FROM {$this->forest_name} ".
        " WHERE id = $id_node";
        
        $result = $this->db->query($query, MYSQLI_STORE_RESULT);
        if (!$result) throw new sp_MySQL_Error($this->db);
        
        return $result->fetch_assoc();
    }
    
    /**!
     * .. method:: appendChild([$id_parent=NULL[,$fields_values=NULL]])
     *    
     *    Create and append a node as last child of a parent node. If no
     *    parent is given, the new node will become the root node.
     *
     *    :param $id_parent: id of the parent node
     *    :type $id_parent: int or NULL
     *    :param $fields_values: mapping fields => values to assign to the new node
     *    :type $fields_values: array or NULL
     *    
     *    :return: id of new node
     *    :rtype:  int
     *
     */
    public function appendChild($id_parent=NULL,$fields_values=NULL){
        
        $id_parent = intval($id_parent);
        
        if (!$this->db->multi_query("
                CALL Baobab_{$this->forest_name}_AppendChild({$this->tree_id}, {$id_parent}, @new_id, @cur_tree_id);
                SELECT @new_id as new_id, @cur_tree_id as tree_id"))
                throw new sp_MySQL_Error($this->db);
        
        $res = $this->_readLastResult(array('new_id', 'tree_id'));
        $id = intval($res['new_id']);
        $this->tree_id = intval($res['tree_id']);
        
        //update the node if needed
        if ($fields_values !== NULL) $this->updateNode($id, $fields_values);
        
        return $id;
    }
    
    /**!
     * .. method:: insertAfter($id_sibling[,$fields_values=NULL])
     *
     *    Create a new node and insert it as the next sibling of the node
     *    chosen (which can not be root)
     *
     *    :param $id_sibling: id of a node in the tree (can not be root)
     *    :type $id_sibling:  int
     *    :param $fields_values: mapping fields => values to assign to the new node
     *    :type $fields_values: array or NULL
     *
     *    :return: id of the new node
     *    :rtype:  int
     * 
     */
    public function insertAfter($id_sibling,$fields_values=NULL) {
        $id_sibling = intval($id_sibling);

        if (!$this->db->multi_query("
                CALL Baobab_{$this->forest_name}_insertAfter({$id_sibling}, @new_id, @error_code);
                SELECT @new_id as new_id, @error_code as error_code"))
                throw new sp_MySQL_Error($this->db);

        $res = $this->_readLastResult('new_id');
        
        //update the node if needed
        if ($fields_values !== NULL) $this->updateNode($res['new_id'], $fields_values);
        
        return intval($res['new_id']);
    }

    /**!
     * .. method:: insertBefore($id_sibling[,$fields_values=NULL])
     *
     *    Create a new node and insert it as the previous sibling of the node
     *    chosen (which can not be root)
     *
     *    :param $id_sibling: id of a node in the tree (can not be root)
     *    :type $id_sibling:  int
     *    :param $fields_values: mapping fields => values to assign to the new node
     *    :type $fields_values: array or NULL
     *
     *    :return: id of the new node
     *    :rtype:  int
     * 
     */
    public function insertBefore($id_sibling,$fields_values=NULL) {
        $id_sibling = intval($id_sibling);

        if (!$this->db->multi_query("
                CALL Baobab_{$this->forest_name}_insertBefore({$id_sibling}, @new_id, @error_code);
                SELECT @new_id as new_id, @error_code as error_code"))
                throw new sp_MySQL_Error($this->db);

        $res = $this->_readLastResult('new_id');
        
        //update the node if needed
        if ($fields_values !== NULL) $this->updateNode($res['new_id'], $fields_values);
        
        return intval($res['new_id']);
    }

    /**!
     * .. method:: insertChildAtIndex($id_parent,$index[,$fields_values=NULL])
     *
     *    Create a new node and insert it as the nth child of the parent node
     *    chosen
     *
     *    :param $id_parent: id of a node in the tree
     *    :type $id_parent:  int
     *    :param $index: new child position between his siblings (0 is first).
     *                   You cannot insert a child as last sibling.
     *                   Negative indexes are allowed (-1 is the position before
     *                   the last sibling).
     *    :type $index:  int
     *    :param $fields_values: mapping fields => values to assign to the new node
     *    :type $fields_values:  array or NULL
     *    
     *    :return: id of the new node
     *    :rtype:  int
     */
    public function insertChildAtIndex($id_parent,$index,$fields_values=NULL) {
        $id_parent = intval($id_parent);
        $index = intval($index);

        if (!$this->db->multi_query("
                CALL Baobab_{$this->forest_name}_InsertChildAtIndex({$id_parent}, {$index}, @new_id, @error_code);
                SELECT @new_id as new_id, @error_code as error_code"))
            throw new sp_MySQL_Error($this->db);
        
        $res = $this->_readLastResult('new_id');
        
        //update the node if needed
        if ($fields_values !== NULL) $this->updateNode($res['new_id'], $fields_values);
        
        return intval($res['new_id']);
    }
    
    /**!
     * .. method:: moveAfter($id_to_move,$reference_node)
     *
     *    Move a node and all of his children as right sibling of another node.
     *
     *    :param $id_to_move: id of a node in the tree
     *    :type $id_to_move:  int
     *    :param $reference_node: the node that will become the left sibling
     *                              of $id_to_move
     *    :type $reference_node:  int
     *    
     *    .. warning:
     *       Moving a subtree after/before root or as a child of hisself will
     *         throw a sp_Error exception
     * 
     */
    public function moveAfter($id_to_move,$reference_node) {
        $id_to_move = intval($id_to_move);
        $reference_node = intval($reference_node);

        if (!$this->db->multi_query("
                CALL Baobab_{$this->forest_name}_MoveSubtreeAfter({$id_to_move}, {$reference_node}, @error_code);
                SELECT @error_code as error_code"))
            throw new sp_MySQL_Error($this->db);
        
        $this->_readLastResult();
    }
    
    /**!
     * .. method:: moveBefore($id_to_move,$reference_node)
     *
     *    Move a node and all of his children as left sibling of another node.
     *
     *    :param $id_to_move: id of a node in the tree
     *    :type $id_to_move:  int
     *    :param $reference_node: the node that will become the left sibling
     *                              of $id_to_move
     *    :type $reference_node:  int
     *    
     *    .. warning:
     *       Moving a subtree after/before root or as a child of hisself will
     *         throw a sp_Error exception
     * 
     */
    public function moveBefore($id_to_move,$reference_node) {
        $id_to_move = intval($id_to_move);
        $reference_node = intval($reference_node);

        if (!$this->db->multi_query("
                CALL Baobab_{$this->forest_name}_MoveSubtreeBefore({$id_to_move}, {$reference_node}, @error_code);
                SELECT @error_code as error_code"))
            throw new sp_MySQL_Error($this->db);
        
        $this->_readLastResult();
    }
    
    /**!
     * .. method:: moveNodeAtIndex($id_to_move,$id_parent,$index)
     *
     *    Move a node as nth child of another node.
     *
     *    :param $id_to_move: id of a node in the tree
     *    :type $id_to_move:  int
     *    :param $id_parent: id of a node that will become $id_to_move's parent
     *    :type $id_parent:  int
     *    :param $index: new child position between his siblings (0 is first).
     *                   Negative indexes are allowed (-1 is the position before
     *                   the last sibling).
     *    :type $index:  int
     *    
     *    .. warning:
     *       Moving a subtree after/before root or as a child of hisself will
     *         throw a sp_Error exception
     * 
     */
    public function moveNodeAtIndex($id_to_move,$id_parent,$index) {
        $id_to_move = intval($id_to_move);
        $id_parent = intval($id_parent);
        $index = intval($index);
        
        if (!$this->db->multi_query("
                CALL Baobab_{$this->forest_name}_MoveSubtreeAtIndex({$id_to_move}, {$id_parent}, {$index}, @error_code);
                SELECT @error_code as error_code"))
            throw new sp_MySQL_Error($this->db);
        
        $this->_readLastResult();
    }
    
    /**
     * .. method:: _readLastResult([$fields=NULL[,$error_field="error_code"[,$numResults=2]]])
     *    
     *    Read $numResults query results and return the values mapped to $fields.
     *    If a field named as the value of $error_field is found throw an 
     *    exception using that error informations.
     *
     *    :param $fields: name of a field to read or an array of fields names
     *    :type $fields:  string or array
     *    :param $error_field: name of the field that could contain an error code
     *    :type $error_field:  string
     *    :param $numResults: number of expected mysql results
     *    :type $numResults:  int
     *    
     *    :return: array, mapping $fields => values found in the last result
     *    :rtype:  int
     */
    private function &_readLastResult($fields=NULL,$error_field="error_code",$numResults=2){
        if (is_string($fields)) $fields = array($fields);
        else if ($fields === NULL) $fields = array();
        
        $k = 1;
        while($k++ < $numResults) {
            if ($result = $this->db->use_result()) { $result->close(); }
            if ($this->db->errno) throw new sp_MySQL_Error($this->db);
            $this->db->next_result();
        }
        
        $result = $this->db->use_result();
        $record = $result->fetch_assoc();
        
        if (isset($record[$error_field]) && $record[$error_field] != 0) {
            $error_code = intval($record[$error_field]);
            $result->close();
            throw new sp_Error(sprintf("[%s] %s",
                $this->_errors["by_code"][$error_code]["name"],
                $this->_errors["by_code"][$error_code]["msg"]), $error_code);
        }
        
        $ar_out = array();
        foreach($fields as $fieldName) {
            $ar_out[$fieldName] = $record[$fieldName];
        }
        $result->close();
        
        return $ar_out;
    }
    
    /**!
     * .. staticmethod:: import($db,$forest_name,$data)
     *    
     *    Load data about a single tree (as generated by the export method).
     *    
     *    :param $db: mysqli database connection in object oriented style
     *    :type $db:  an instance of mysqli_connect
     *    :param $forest_name: name of the forest, equals to the name of the table
     *                       holding the data
     *    :type $forest_name:  string
     *    :param $data: data to import
     *    :type $data:  string (JSON) or array
     *    
     *    :return: an array of Baobab tree instances
     *    :rtype:  array
     *    
     *    $data JSON format is like the following
     *    
     *    .. code-block:: json
     *       
     *       [
     *        {
     *          "tree_id": 6, //optional, it could also be one of the fields, or none at all
     *          "fields" : ["id", "lft", "rgt"],
     *          "values" : 
     *              [1,1,4,[
     *                  [2,2,3,[]]
     *              ]]
     *        }
     *        // more could follow ...
     *       ]
     *    
     *    .. note::
     *       If "id" field is used and the nodes values are not NULL, mustn't
     *       exist in the table a record with that same value.
     *      
     *    .. note::
     *       If "tree_id" is used (as attribute or field) and not NULL, mustn't
     *       exist in the table records belonging to that same tree.
     */
    public static function &import($db,$forest_name,$data){
        if (is_string($data)) $data = json_decode($data, TRUE);
        if (!$data || empty($data)) {$x = NULL;return $x;} // nothing to import
        
        // check if the table exists before doing anything else
        $sql_utils = new sp_SQLUtils($db);
        if ( ! $sql_utils->table_exists($forest_name)){
            throw new sp_Error("Table `{$forest_name}` does not exist");
        }
        
        $ar_out = array();
        
        $db->query("START TRANSACTION");
        
        try {
            
            foreach($data as $tmp_data) {
                
                if (empty($tmp_data["values"])) continue;
                
                // get the tree id, if any
                $tree_id = NULL;
                if (isset($tmp_data["tree_id"])) $tree_id = intval($tmp_data["tree_id"]);
                else {
                    $idx = array_search("tree_id", $tmp_data["fields"]);
                    if (FALSE !== $idx) {
                        // all the tree ids are equals, get the first
                        $tree_id = intval($tmp_data["values"][0][$idx]);
                    }
                }
                
                if (!$tree_id) { // check both NULL and 0
                    // there isn't a tree_id, we must get one
                    
                    // find a new tree_id
                    $query = "SELECT IFNULL(MAX(tree_id),0)+1 as new_id FROM {$forest_name}";
                    $result = $db->query($query, MYSQLI_STORE_RESULT);
                    if (!$result) throw new sp_MySQL_Error($db);
                    $row = $result->fetch_row();
                    $tree_id = intval($row[0]);
                    $result->close();
                    
                } else {
                    // ensure the tree_id isn't in use
                    $query = "SELECT DISTINCT tree_id FROM {$forest_name} WHERE tree_id={$tree_id}";
                    $result = $db->query($query, MYSQLI_STORE_RESULT);
                    if (!$result) throw new sp_MySQL_Error($db);
                    $tree_exists = $result->num_rows != 0;
                    $result->close();
                    
                    if ($tree_exists) throw new sp_Error("ImportError: tree_id {$tree_id} yet in use");
                }
                
                $tree = new Baobab($db, $forest_name, $tree_id);
                
                // if there are only standard fields we can also try to build the table
                //   (otherwise it must yet exist because we can't guess field types)
                
                $standard_fields = array("id", "lft", "rgt", "tree_id");
                if (count($tmp_data["fields"]) <= 4) {
                    $is_standard = TRUE;
                    foreach($tmp_data["fields"] as $fieldName) {
                        if (FALSE === array_search($fieldName, $standard_fields)) {
                            $is_standard = FALSE;
                            break;
                        }
                    }
                    if ($is_standard) {
                        try{
                            $tree->build(); // if yet exists is harmless
                        } catch (sp_MySQL_Error $e) {
                            if ($db->errno != 1050) throw $e; // 1050 is "table exists"
                        }
                    }
                }
                
                // check that the table has the involved fields
                $tree->_sql_check_fields($tmp_data["fields"]);
                
                $values = array();
                
                $nodes = array($tmp_data["values"]);
                
                while (!empty($nodes)) {
                    // get data of current node
                    $last_node = array_pop($nodes);
                     //save his children's array
                    $children = array_pop($last_node);
                    // append the children to the nodes to iterate over
                    if (count($children)) $nodes = array_merge($nodes, $children);
                    
                    // keep the data
                    $values[] = $last_node;
                }
                
                // get the tree_id to use
                
                if (FALSE === array_search("tree_id", $tmp_data["fields"])) {
                    // add tree_id to fields
                    $tmp_data["fields"][] = "tree_id";
                    
                    // add tree_id to the each row
                    foreach($values as &$row){
                        $row[] = $tree_id;
                    }
                }
                
                // add the values
                $result = $db->query(
                        "INSERT INTO {$forest_name}(".join(",", $tmp_data["fields"]).") VALUES ".
                        join(", ", sp_Lib::map_method($values, $sql_utils, "vector_to_sql_tuple"))
                    , MYSQLI_STORE_RESULT);
                if (!$result)  throw new sp_MySQL_Error($db);
                
                $ar_out[] = $tree;
                
            } // end foreach $data
        
        } catch (Exception $e) {
            // whatever happens we must rollback
            $db->query("ROLLBACK");
            throw $e;
        }
        
        $result = $db->query("COMMIT");
        if (!$result) throw new sp_MySQL_Error($db);
        
        
        return $ar_out;
    }
    
    /**
     * .. staticmethod:: _traverse_tree_to_export_data($node,&$data,&$fieldsFlags,&$fieldsOrder)
     *
     *   Traverse a baobab tree and create an array holding the data about each node.
     *   Each resulting node is represented as an array holding his values ordered as
     *   $fieldsOrder, with an array as most right element holding children nodes
     *   (in the same format).
     *
     *   :param $node: current node to retrieve values from
     *   :type $node:  BaobabNode
     */
    private static function _traverse_tree_to_export_data($node,&$data,&$fieldsFlags,&$fieldsOrder){
        
        $len_data = count($data);
        
        $tmp_ar = array();
        
        $i = 0;
        
        // get fields and values in the correct order
        foreach($fieldsOrder as $fieldName) {
            if ($fieldName == 'id') $value = $node->id;
            else if ($fieldName == 'lft') $value = $node->lft;
            else if ($fieldName == 'rgt') $value = $node->rgt;
            else $value = $node->fields_values[$fieldName];
            
            if ($fieldsFlags[$i++] & MYSQLI_NUM_FLAG) $value = floatval($value);
            
            $tmp_ar[] = $value;
        }
        
        $tmp_ar[] = array(); // the last element holds the children
        
        // append the child data to parent data
        $data[$len_data-1][] = &$tmp_ar;
        
        foreach($node->children as $childNode) {
            self::_traverse_tree_to_export_data($childNode, $tmp_ar, $fieldsFlags, $fieldsOrder);
        }
        
    }
    
    /**!
     * .. staticmethod:: export($db,$forest_name[,$fields=NULL[,$tree_id=NULL]])
     *    
     *    Create a JSON dump of one or more trees
     *    
     *    :param $db: mysqli database connection in object oriented style
     *    :type $db:  an instance of mysqli_connect
     *    :param $forest_name: name of the forest, equals to the name of the table
     *                       holding the data
     *    :type $forest_name:  string
     *    :param $fields: optional, the fields to be exported
     *    :type $fields:  array
     *    :param $tree_id: optional, a single tree_id to be exported, or an array of them.
     *                      If NULL all the trees in the table will be exported;
     *    :type $tree_id:  array or int
     *    
     *    :return: a dump of the trees in JSON format
     *    :rtype:  string
     *
     *    .. note::
     *       if 'tree_id' is passed as field, it will not appear in the field
     *       list because redundat (it will be present once in the tree_id attribute)
     *    
     *    Example of an exported tree
     *    
     *    .. code-block:: json
     *
     *       [
     *        {
     *          "tree_id": 6, 
     *          "fields" : ["id", "lft", "rgt"], // tree_id is stripped if requested via fields because redundant
     *          "values" : 
     *              [1,1,4,[
     *                  [2,2,3,[]]
     *              ]]
     *        }
     *        // more could follow ...
     *       ]
     * 
     */
    public static function export($db,$forest_name,$fields=NULL,$tree_id=NULL){
        
        // check if the table exists before doing anything else
        $sql_utils = new sp_SQLUtils($db);
        if ( ! $sql_utils->table_exists($forest_name)){
            throw new sp_Error("Table `Baobab_{$forest_name}` does not exist");
        }
        
        // get all the fields to export or check if the passed fields are valid
        $tree = new Baobab($db, $forest_name, NULL); // use a unexistent tree in the right table
        if ($fields !== NULL) $tree->_sql_check_fields($fields);
        else $fields = array_keys($tree->_get_fields());
        
        // remove tree_id from $fields to avoid writing it n times
        //   ( we give him a single property in the json format )
        $idx_treeId_field = array_search("tree_id", $fields);
        if (FALSE !== $idx_treeId_field) {
            unset($fields[$idx_treeId_field]);
            $fields = array_values($fields); // I want to mantain a correct index sequence
        }
        
        // get the ids of the trees to export
        $ar_tree_id = array();
        if ($tree_id) {
            if (!is_array($tree_id)) $tree_id = array($tree_id);
            // ensure $tree_id contains numbers
            foreach($tree_id as $tmp_id) $ar_tree_id[] = intval($tmp_id);
        }
        else {
            $query = "SELECT DISTINCT tree_id FROM {$forest_name}";
            $result = $db->query($query, MYSQLI_STORE_RESULT);
            if (!$result) throw new sp_MySQL_Error($db);
            while ($row = $result->fetch_row()) $ar_tree_id[] = intval($row[0]);
            $result->close();
        }
        
        // get the type of the columns mainly to write numbers as ... numbers
        $fieldsFlags = array(); // each index will have the field flag, to know his type
        $result = $db->query(
            "SELECT ".join(",", $fields)." FROM {$forest_name} LIMIT 1", MYSQLI_STORE_RESULT);
        if (!$result)  throw new sp_MySQL_Error($db);
        // retrieve the column names and their types
        while ($finfo = $result->fetch_field()) {
            $fieldsFlags[] = $finfo->flags;
        }
        $result->close();
        
        // parse each tree and build an array to jsonize later
        $ar_out = array();
        foreach ($ar_tree_id as $tree_id) {
            $tmp_ar = array("tree_id" => $tree_id, "fields" => $fields, "values" => NULL);
            
            // retrieve the data
            $tree = new Baobab($db, $forest_name, $tree_id);
            $root = $tree->getTree();
            
            if ($root !== NULL) {
                $data = array(array()); // the inner array emulate a node to gain root as child
                self::_traverse_tree_to_export_data($root, $data, $fieldsFlags, $tmp_ar["fields"]);
                if (!empty($data[0][0])) $tmp_ar["values"] = &$data[0][0];
                
                $ar_out[] = $tmp_ar;
            }
        }
        
        return json_encode($ar_out);
    }


}











//--------------------

class ForumBaobab extends Baobab {
    
    // override build function to create the table the way we want it
    public function build() {
        if (parent::build()) { // the table wasn't existing and has been built
            
            $result = $this->db->query("
                ALTER TABLE {$this->forest_name}
                ADD COLUMN username VARCHAR(100) NOT NULL,
                ADD COLUMN body MEDIUMTEXT
                "
            );
            if (!$result) throw new sp_MySQL_Error($this->db);
        }
    }
}

function start_over($conn,$forestName){
    $tree=new ForumBaobab($conn,$forestName);
    $tree->destroy(TRUE);
    $tree->build();
}

function main(){ // $conn is a mysqli connection object
    
     $host = 'localhost:3306';
             $username = '';//hidden
             $password = '';//hidden
             $databsename='';//hidden
             $conn = mysqli_connect($host, $username, $password,$databsename);
    
    
    $forestName="forum";
    start_over($conn,$forestName);
    
    // utility function to have something more readable later
    function makeFields($username,$body){
        return array("username"=>$username,"body"=>$body);
    }
    
    // Create two trees in the forest and populate them with some children.
    
    $thread1=new ForumBaobab($conn,$forestName);
    $thread2=new ForumBaobab($conn,$forestName);
    
    $a=$thread1->appendChild(NULL,makeFields("joe","I like flowers"));
    $b=$thread1->appendChild($a,makeFields("max","mee too"));
    $c=$thread1->appendChild($a,makeFields("carol","I don't"));
    $d=$thread1->appendChild($c,makeFields("joe","why not?"));
    
    $x=$thread2->appendChild(NULL,makeFields("carol","why should do this?"));
    $y=$thread2->appendChild($x,makeFields("joe","because we can"));
    $z=$thread2->appendChild($y,makeFields("max","isn't it just lame?"));
    $w=$thread2->appendChild($z,makeFields("joe","uh?"));
    
    
    // we can freely move the nodes around the same forest (any tree could ask
    // for the movements beeing the node ids unique in the forest)
    $thread1->moveAfter($z,$c);
    
    
    /*
    Now the content of the 'forum' table is
    
    =======  ==  ===  ===  ========  ===================
    tree_id  id  lft  rgt  username  body
    =======  ==  ===  ===  ========  ===================
          1   1    1   12  joe       I like flowers
          1   2    2    3  max       mee too
          1   3    4    7  carol     I don't
          1   4    5    6  joe       why not?
          2   5    1    4  carol     why should do this?
          2   6    2    3  joe       because we can
          1   7    8   11  max       isn't it just lame?
          1   8    9   10  joe       uh?
    =======  ==  ===  ===  ========  ===================
    
    */
    
    // to visualize simply our trees...
    print_r($thread1->getTree()->stringify());
    echo "\n---\n";
    print_r($thread2->getTree()->stringify());
    echo "\n";
    
    // Next time you want to access a particular tree, you have to know
    // his forest name and his tree id
    $tread1LoadedAgain=new AnimalsBaobab($conn,$forestName,$tread1->id);
}
            
main();
?>