Source for file DBsimple.php
Documentation is available at DBsimple.php
// +----------------------------------------------------------------------+
// +----------------------------------------------------------------------+
// | Copyright (c) 1997-2003 The PHP Group |
// +----------------------------------------------------------------------+
// | This source file is subject to version 2.02 of the PHP license, |
// | that is bundled with this package in the file LICENSE, and is |
// | available at through the world-wide-web at |
// | http://www.php.net/license/2_02.txt. |
// | If you did not receive a copy of the PHP license and are unable to |
// | obtain it through the world-wide-web, please send a note to |
// | license@php.net so we can mail you a copy immediately. |
// +----------------------------------------------------------------------+
// | Authors: Wolfram Kriesing <wolfram@kriesing.de> |
// +----------------------------------------------------------------------+
// $Id: DBsimple.php 320703 2011-12-08 22:08:40Z danielc $
require_once 'Tree/OptionsDB.php';
require_once 'Tree/Error.php';
* the DB interface to the tree class
* @author Wolfram Kriesing <wolfram@kriesing.de>
// FIXXME should actually extend Tree_Common, to use the methods provided in there...
// but we need to connect to the db here, so we extend optionsMDB for now, may be use
// "aggregate" function to fix that
* @var array saves the options passed to the constructor. Valid options:
* - order: which column to order by when reading the data from the DB,
* this sorts the data even inside every level
* - whereAddOn: add on for the where clause, this string is simply
* added behind the WHERE in the select, so you better make
* sure its correct SQL :-), i.e. 'uid=3'.
* This is needed i.e. when you are saving many trees for different
* user in one table where each entry has a uid (user id)
* - columnNameMaps: the column-name maps are used for the "as" in the select
* queries so you can use any column name in the table and "map"
* it to the name that shall be used in the internal array,
* that is built, see the examples (in comments)
* - columnNameMaps: array for id/parentId/prevId/name table column names
'columnNameMaps' => array (
/* 'id' => 'tree_id', // use "tree_id" as "id"
'parentId' => 'parent_id',
'prevId' => 'previous_id',
* @var string the table where to read the tree data from
* can also be set using the DSN in the constructor
* @var object $dbh the handle to the DB-object
// {{{ Tree_Memory_DBsimple()
* @author Wolfram Kriesing <wolfram@kriesing.de>
* @param string $dsn this is a DSN of the for that PEAR::DB uses it
* only that additionally you can add parameters like ...?table=test_table
* to define the table it shall work on
* @param array $options additional options you can set
// just to be backward compatible, or to make the second paramter shorter
* retreive all the navigation data from the db and call build to build the
* tree in the array data and structure
* @author Wolfram Kriesing <wolfram@kriesing.de>
* @return boolean true on success
// TODO sort by prevId (parentId,prevId $addQuery) too if it exists in the table, or the root might be wrong
// TODO since the prevId of the root should be 0
if ($this->options['whereAddOn']) {
$whereAddOn = 'WHERE ' . $this->getOption('whereAddOn');
$orderBy = ',' . $this->options['order'];
if (isset ($map['parentId'])) {
$orderBy = $map['parentId'] . $orderBy;
$orderBy = 'parentId' . $orderBy;
// build the query this way, that the root, which has no parent (parentId=0)
// and no previous (prevId=0) is in first place (in case prevId is given)
$query = sprintf("SELECT * FROM %s %s ORDER BY %s",
$orderBy); //,prevId !!!!
if (DB ::isError ($res = $this->dbh->getAll ($query))) {
// FIXXME remove print use debug mode instead
printf("ERROR - Tree::setup - %s - %s<br>", DB ::errormessage ($res), $query);
return $this->_throwError ($res->getMessage (), __LINE__ );
// if the db-column names need to be mapped to different names
// FIXXME somehow we should be able to do this in the query, but i dont know how to select
// only those columns, use "as" on them and select the rest, without getting those columns again :-(
foreach($res as $id => $aResult) { // map each result
foreach($map as $key => $columnName) {
$res[$id][$key] = $res[$id][$columnName];
unset ($res[$id][$columnName]);
* adds _one_ new element in the tree under the given parent
* the values' keys given have to match the db-columns, because the
* value gets inserted in the db directly
* to add an entire node containing children and so on see 'addNode()'
* to ba compatible, to the DBnested u can also give the parent and previd as the second and third parameter
* @author Wolfram Kriesing <wolfram@kriesing.de>
* @param array $newValues this array contains the values that shall be inserted in the db-table
* the key for each element is the name of the column
* @return mixed either boolean false on failure or the id of the inserted row
function add($newValues, $parentId = 0 )
// FIXXME use $this->dbh->tableInfo to check which columns exist
// so only data for which a column exist is inserted
$newValues['parentId'] = $parentId;
foreach($newValues as $key => $value) { // quote the values, as needed for the insert
$newData[$this->_getColName ($key)] = $this->dbh->quote ($value);
// use sequences to create a new id in the db-table
$nextId = $this->dbh->nextId ($this->table);
$query = sprintf("INSERT INTO %s (%s,%s) VALUES (%s,%s)",
$this->_getColName ('id'),
if (DB ::isError ($res = $this->dbh->query ($query))) {
printf("ERROR - Tree::add - %s - %s<br>", DB ::errormessage ($res), $query);
* @author Wolfram Kriesing <wolfram@kriesing.de>
* @param mixed $id the id of the node to be removed, or an array of id's to be removed
* @return boolean true on success
// if the one to remove has children, get their id's to remove them too
$id = $this->walk (array ('_remove', $this), $id, 'array');
if (isset ($map['id'])) { // if there are maps given
$idColumnName = $map['id'];
$whereClause = " WHERE $idColumnName=$id";
$whereClause = " WHERE $idColumnName in (" . implode(',', $id) . ')';
$query = " DELETE FROM {$this->table} $whereClause";
if (DB::isError($res = $this->dbh->query ($query))) {
printf("ERROR - Tree::remove - %s - %s<br>", DB::errormessage($res), $query);
// TODO if remove succeeded set prevId of the following element properly
* move an entry under a given parent or behind a given entry
* @author Wolfram Kriesing <wolfram@kriesing.de>
* @param integer $idToMove the id of the element that shall be moved
* @param integer $newParentId the id of the element which will be the new parent
* @param integer $newPrevId if prevId is given the element with the id idToMove
* shall be moved _behind_ the element with id=prevId
* if it is 0 it will be put at the beginning
* if no prevId is in the DB it can be 0 too and won't bother
* since it is not written in the DB anyway
* @return boolean true for success
function move($idToMove, $newParentId, $newPrevId = 0)
$parentIdColumnName = 'parentId';
$idColumnName = $map['id'];
if (isset($map['parentId'])) {
$parentIdColumnName = $map['parentId'];
// FIXXME todo: previous stuff
// set the parent in the DB
$query = "UPDATE $this->table SET $parentIdColumnName= $newParentId WHERE $idColumnName= $idToMove";
if (DB::isError($res = $this->dbh->query ($query))) {
printf("ERROR - Tree::move - %s - %s<br>", DB::errormessage($res), $query);
// FIXXME update the prevId's of the elements where the element was moved away from and moved in
* update an element in the DB
* @author Wolfram Kriesing <wolfram@kriesing.de>
* @param array $newData all the new data, the key 'id' is used to
* build the 'WHERE id=' clause and all the other
* elements are the data to fill in the DB
* @return boolean true for success
function update($id, $newData)
// FIXXME check $this->dbh->tableInfo to see if all the columns that shall be updated
// really exist, this will also extract nextId etc. if given before writing it in the DB
// in case they dont exist in the DB
foreach($newData as $key => $value) { // quote the values, as needed for the insert
$setData[] = $this->_getColName ($key) . '=' . $this->dbh->quote ($value);
$query = sprintf('UPDATE %s SET %s WHERE %s=%s',
$this->_getColName ('id'),
if (DB ::isError ($res = $this->dbh->query ($query))) {
// FIXXME raise PEAR error
printf("ERROR - Tree::update - %s - %s<br>", DB::errormessage($res), $query);
* @author Wolfram Kriesing <wolfram@kriesing.de>
function _throwError($msg, $line, $mode = null)
return new Tree_Error($msg, $line, __FILE__, $mode, $this->db->last_query );
* prepare multiple results
* @author Wolfram Kriesing <wolfram@kriesing.de>
function _prepareResults($results)
foreach($results as $aResult)
$newResults[] = $this->_prepareResult ($aResult);
* map back the index names to get what is expected
* @author Wolfram Kriesing <wolfram@kriesing.de>
function _prepareResult($result)
foreach($map as $key => $columnName) {
$result[$key] = $result[$columnName];
unset($result[$columnName]);
* this method retreives the real column name, as used in the DB
* since the internal names are fixed, to be portable between different
* DB-column namings, we map the internal name to the real column name here
* @author Wolfram Kriesing <wolfram@kriesing.de>
function _getColName($internalName)
if ($map = $this->getOption('columnNameMaps')) {
if (isset($map[$internalName])) {
return $map[$internalName];
Documentation generated on Mon, 11 Mar 2019 15:47:04 -0400 by phpDocumentor 1.4.4. PEAR Logo Copyright © PHP Group 2004.
|