You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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 <riccardo@sideralis.org> 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();
?>
The text was updated successfully, but these errors were encountered:
The text was updated successfully, but these errors were encountered: