Viewing file:  MDB2simple.php (15.86 KB) -rw-r--r--
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
<?php
// +----------------------------------------------------------------------+
// | PHP Version 4 |
// +----------------------------------------------------------------------+
// | 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> |
// | Lorenzo Alberton <l.alberton@quipo.it> |
// +----------------------------------------------------------------------+
// $Id: MDB2simple.php 320703 2011-12-08 22:08:40Z danielc $
require_once 'Tree/OptionsMDB2.php';
/**
* the MDB2 interface to the tree class
*
* @access public
* @author Lorenzo Alberton <l.alberton@quipo.it>
* @package Tree
*/
class Tree_Memory_MDB2simple extends Tree_OptionsMDB2
{
// 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 optionsMDB2 for now, may be use
// "aggregate" function to fix that
/**
* @access public
* @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
*/
var $options = array('order' => '',
'whereAddOn' => '',
'table' => '',
'columnNameMaps' => array(
/* 'id' => 'tree_id', // use "tree_id" as "id"
'parentId' => 'parent_id',
'prevId' => 'previous_id',
'name' => 'nodeName'
*/
),
);
/**
* @access public
* @var string the table where to read the tree data from
* can also be set using the DSN in the constructor
*/
var $table;
/**
* @access private
* @var object $dbh the handle to the DB-object
*/
// var $dbh;
// {{{ Tree_Memory_MDB2simple()
/**
* set up this object
*
* @access public
* @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
*/
function Tree_Memory_MDB2simple($dsn, $options = array())
{
$this->Tree_OptionsMDB2($dsn, $options); // instanciate DB
if (is_string($options)) {
// just to be backward compatible, or to make the second paramter shorter
$this->setOption('order', $options);
}
$this->table = $this->getOption('table');
}
// }}}
// {{{ setup()
/**
* retrieve all the navigation data from the db and call build to build the
* tree in the array data and structure
*
* @access public
* @return boolean true on success
*/
function setup()
{
// TODO sort by prevId (parentId, prevId $addQuery) too if it exists in the table,
// or the root might be wrong, since the prevId of the root should be 0
$whereAddOn = '';
if ($this->options['whereAddOn']) {
$whereAddOn = 'WHERE ' . $this->getOption('whereAddOn');
}
$orderBy = '';
if ($this->options['order']) {
$orderBy = ',' . $this->options['order'];
}
$map = $this->getOption('columnNameMaps');
if (isset($map['parentId'])) {
$orderBy = $map['parentId'] . $orderBy;
} else {
$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",
$this->table,
$whereAddOn,
$orderBy); //,prevId !!!!
if (MDB2::isError($res = $this->dbh->queryAll($query))) {
// FIXXME remove print use debug mode instead
printf("ERROR - Tree::setup - %s - %s<br />", MDB2::errorMessage($res), $query);
return Tree::raiseError('TREE_ERROR_RAISE_ERROR', $res->getMessage());
}
// Account for the fact the by default MDB2 lower cases field names
// But only if people are not doing the mapping them self
if (!isset($map['parentId']) || $map['parentId'] != 'parentid') {
foreach ($res as $index => $value) {
$res[$index]['parentId'] = $res[$index]['parentid'];
unset($res[$index]['parentid']);
}
}
// 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 :-(
if ($map) {
foreach ($res as $id => $aResult) { // map each result
foreach ($map as $key => $columnName) {
$res[$id][$key] = $res[$id][$columnName];
unset($res[$id][$columnName]);
}
}
}
return $res;
}
// }}}
// {{{ add()
/**
* 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 be compatible with the MDB2nested, u can also give the parent and previd
* as the second and third parameter
*
* @see addNode
* @access public
* @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
if ($parentId) {
$newValues['parentId'] = $parentId;
}
$newData = array();
foreach ($newValues as $key => $value) {
// quote the values, as needed for the insert
$newData[$this->_getColName($key)] = $this->_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->table ,
$this->_getColName('id'),
implode(',', array_keys($newData)) ,
$nextId,
implode(',', $newData));
if (MDB2::isError($res = $this->dbh->exec($query))) {
// TODO raise PEAR error
printf("ERROR - Tree::add - %s - %s<br />", MDB2::errorMessage($res), $query);
return false;
}
return $nextId;
}
// }}}
// {{{ remove()
/**
* removes the given node
*
* @access public
* @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
*/
function remove($id)
{
// if the one to remove has children, get their id's to remove them too
if ($this->hasChildren($id)) {
$id = $this->walk(array('_remove', $this), $id, 'array');
}
$idColumnName = 'id';
$map = $this->getOption('columnNameMaps');
if (isset($map['id'])) { // if there are maps given
$idColumnName = $map['id'];
}
$whereClause = "WHERE $idColumnName=$id";
if (is_array($id)) {
$whereClause = "WHERE $idColumnName in (" . implode(',', $id) . ')';
}
$query = "DELETE FROM {$this->table} $whereClause";
// print('<br />'.$query);
if (MDB2::isError($res = $this->dbh->exec($query))) {
// TODO raise PEAR error
printf("ERROR - Tree::remove - %s - %s<br>", MDB2::errormessage($res), $query);
return false;
}
// TODO if remove succeeded set prevId of the following element properly
return true;
}
// }}}
// {{{ move()
/**
* move an entry under a given parent or behind a given entry
*
* @version 2001/10/10
* @access public
* @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)
{
$idColumnName = 'id';
$parentIdColumnName = 'parentId';
$map = $this->getOption('columnNameMaps');
if (isset($map['id'])) {
$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";
// print($query);
if (MDB2::isError($res = $this->dbh->exec($query))) {
// TODO raise PEAR error
printf("ERROR - Tree::move - %s - %s<br>", MDB2::errorMessage($res), $query);
return false;
}
// FIXXME update the prevId's of the elements where the element was moved away from and moved in
return true;
}
// }}}
// {{{ update()
/**
* update an element in the DB
*
* @version 2002/01/17
* @access public
* @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
$setData = array();
foreach ($newData as $key => $value) { // quote the values, as needed for the insert
$setData[] = $this->_getColName($key) . '=' . $this->_quote($value);
}
$query = sprintf('UPDATE %s SET %s WHERE %s=%s',
$this->table,
implode(',', $setData),
$this->_getColName('id'),
$id
);
if (MDB2::isError($res = $this->dbh->exec($query))) {
// FIXXME raise PEAR error
printf("ERROR - Tree::update - %s - %s<br>", MDB2::errormessage($res), $query);
return false;
}
return true;
}
// }}}
// {{{ _prepareResults()
/**
* prepare multiple results
*
* @see _prepareResult
* @access private
* @version 2002/03/03
* @author Wolfram Kriesing <wolfram@kriesing.de>
*/
function _prepareResults($results)
{
$newResults = array();
foreach ($results as $aResult) {
$newResults[] = $this->_prepareResult($aResult);
}
return $newResults;
}
// }}}
// {{{ _prepareResult()
/**
* map back the index names to get what is expected
*
* @access private
* @version 2002/03/03
* @author Wolfram Kriesing <wolfram@kriesing.de>
*/
function _prepareResult($result)
{
$map = $this->getOption('columnNameMaps');
if ($map) {
foreach ($map as $key => $columnName) {
$result[$key] = $result[$columnName];
unset($result[$columnName]);
}
}
return $result;
}
// }}}
// {{{ _getColName()
/**
* 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
*
* @access private
* @version 2002/03/02
* @author Wolfram Kriesing <wolfram@kriesing.de>
*/
function _getColName($internalName)
{
if ($map = $this->getOption('columnNameMaps')) {
if (isset($map[$internalName])) {
return $map[$internalName];
}
}
return $internalName;
}
// }}}
// {{{ _quoteArray()
/**
* quotes all the data in this array
*
* @param array $data Array to quote
*/
function _quoteArray($data)
{
foreach ($data as $key => $val) {
$data[$key] = $this->_quote($val);
}
return $data;
}
// }}}
// {{{ _quote()
/**
* quotes all the data in this var (can be any type except object)
* @param mixed $data data to quote
*/
function _quote($data)
{
switch(gettype($data)) {
case 'array':
return $this->_quoteArray($data);
break;
case 'boolean':
return $this->dbh->quote($data);
break;
case 'double':
return $this->dbh->quote($data);
break;
case 'integer':
return $this->dbh->quote($data);
break;
case 'string': //if 'string' or 'unknown', quote as text
default:
return $this->dbh->quote($data);
}
}
// }}}
}
|