| 1 |
<?php |
| 2 |
// |
| 3 |
// +----------------------------------------------------------------------+ |
| 4 |
// | PHP Version 4 | |
| 5 |
// +----------------------------------------------------------------------+ |
| 6 |
// | Copyright (c) 1997-2003 The PHP Group | |
| 7 |
// +----------------------------------------------------------------------+ |
| 8 |
// | This source file is subject to version 2.02 of the PHP license, | |
| 9 |
// | that is bundled with this package in the file LICENSE, and is | |
| 10 |
// | available at through the world-wide-web at | |
| 11 |
// | http://www.php.net/license/2_02.txt. | |
| 12 |
// | If you did not receive a copy of the PHP license and are unable to | |
| 13 |
// | obtain it through the world-wide-web, please send a note to | |
| 14 |
// | license@php.net so we can mail you a copy immediately. | |
| 15 |
// +----------------------------------------------------------------------+ |
| 16 |
// | Authors: Wolfram Kriesing <wolfram@kriesing.de> | |
| 17 |
// +----------------------------------------------------------------------+ |
| 18 |
// |
| 19 |
// $Id: DBsimple.php,v 1.11 2003/01/18 15:36:50 cain Exp $ |
| 20 |
|
| 21 |
require_once('Tree/OptionsDB.php'); |
| 22 |
require_once('Tree/Error.php'); |
| 23 |
|
| 24 |
/** |
| 25 |
* the DB interface to the tree class |
| 26 |
* |
| 27 |
* @access public |
| 28 |
* @author Wolfram Kriesing <wolfram@kriesing.de> |
| 29 |
* @version 2001/06/27 |
| 30 |
* @package Tree |
| 31 |
*/ |
| 32 |
class Tree_Memory_DBsimple extends Tree_OptionsDB |
| 33 |
// FIXXME should actually extend Tree_Common, to use the methods provided in there... but we need to connect |
| 34 |
// to the db here, so we extend optionsDB for now, may be use "aggregate" function to fix that |
| 35 |
{ |
| 36 |
|
| 37 |
/** |
| 38 |
* @access public |
| 39 |
* @var array saves the options passed to the constructor |
| 40 |
*/ |
| 41 |
var $options = array( 'order' =>'', // which column to order by when reading the data from the DB, this sorts the data even inside every level |
| 42 |
'whereAddOn'=>'', // add on for the where clause, this string is simply added behind the WHERE in the select |
| 43 |
// so you better make sure its correct SQL :-), i.e. 'uid=3' |
| 44 |
// this is needed i.e. when you are saving many trees for different user |
| 45 |
// in one table where each entry has a uid (user id) |
| 46 |
'table' =>'', // |
| 47 |
// the column-name maps are used for the "as" in the select queries |
| 48 |
// so you can use any column name in the table and "map" it to the name that shall be used in the |
| 49 |
// internal array, that is built, see the examples (in comments) |
| 50 |
'columnNameMaps'=>array( |
| 51 |
/* 'id' => 'tree_id', // use "tree_id" as "id" |
| 52 |
'parentId' => 'parent_id', |
| 53 |
'prevId' => 'previous_id', |
| 54 |
'name' => 'nodeName' |
| 55 |
*/ |
| 56 |
), |
| 57 |
); |
| 58 |
|
| 59 |
/** |
| 60 |
* @access public |
| 61 |
* @var string the table where to read the tree data from |
| 62 |
* can also be set using the DSN in the constructor |
| 63 |
*/ |
| 64 |
var $table; |
| 65 |
|
| 66 |
/** |
| 67 |
* @access private |
| 68 |
* @var object $dbh the handle to the DB-object |
| 69 |
*/ |
| 70 |
// var $dbh; |
| 71 |
|
| 72 |
/** |
| 73 |
* set up this object |
| 74 |
* |
| 75 |
* @version 2001/06/27 |
| 76 |
* @access public |
| 77 |
* @author Wolfram Kriesing <wolfram@kriesing.de> |
| 78 |
* @param string $dsn this is a DSN of the for that PEAR::DB uses it |
| 79 |
* only that additionally you can add parameters like ...?table=test_table |
| 80 |
* to define the table it shall work on |
| 81 |
* @param array $options additional options you can set |
| 82 |
*/ |
| 83 |
function Tree_Memory_DBsimple( $dsn , $options=array() ) |
| 84 |
{ |
| 85 |
$this->Tree_OptionsDB( $dsn , $options ); // instanciate DB |
| 86 |
if( is_string($options) ) // just to be backward compatible, or to make the second paramter shorter |
| 87 |
{ |
| 88 |
$this->setOption( 'order' , $options ); |
| 89 |
} |
| 90 |
|
| 91 |
$this->table = $this->getOption('table'); |
| 92 |
|
| 93 |
} // end of function |
| 94 |
|
| 95 |
/** |
| 96 |
* retreive all the navigation data from the db and call build to build the |
| 97 |
* tree in the array data and structure |
| 98 |
* |
| 99 |
* @version 2001/11/20 |
| 100 |
* @access public |
| 101 |
* @author Wolfram Kriesing <wolfram@kriesing.de> |
| 102 |
* @return boolean true on success |
| 103 |
*/ |
| 104 |
function setup() |
| 105 |
{ |
| 106 |
// TODO sort by prevId (parentId,prevId $addQuery) too if it exists in the table, or the root might be wrong |
| 107 |
// TODO since the prevId of the root should be 0 |
| 108 |
|
| 109 |
// |
| 110 |
$whereAddOn = ''; |
| 111 |
if( $this->options['whereAddOn'] ) |
| 112 |
{ |
| 113 |
$whereAddOn = 'WHERE '.$this->getOption('whereAddOn'); |
| 114 |
} |
| 115 |
|
| 116 |
// |
| 117 |
$orderBy = ''; |
| 118 |
if( $this->options['order'] ) |
| 119 |
{ |
| 120 |
$orderBy = ",".$this->options['order']; |
| 121 |
} |
| 122 |
|
| 123 |
$map = $this->getOption('columnNameMaps'); |
| 124 |
if( isset($map['parentId']) ) |
| 125 |
{ |
| 126 |
$orderBy = $map['parentId'].$orderBy; |
| 127 |
} |
| 128 |
else |
| 129 |
{ |
| 130 |
$orderBy = 'parentId'.$orderBy; |
| 131 |
} |
| 132 |
|
| 133 |
// build the query this way, that the root, which has no parent (parentId=0) |
| 134 |
// and no previous (prevId=0) is in first place (in case prevId is given) |
| 135 |
$query = sprintf( "SELECT * FROM %s %s ORDER BY %s", |
| 136 |
$this->table, |
| 137 |
$whereAddOn, |
| 138 |
$orderBy); //,prevId !!!! |
| 139 |
if( DB::isError( $res = $this->dbh->getAll( $query ) ) ) |
| 140 |
{ |
| 141 |
// FIXXME remove print use debug mode instead |
| 142 |
printf("ERROR - Tree::setup - %s - %s<br>",DB::errormessage($res),$query); |
| 143 |
return $this->_throwError($res->getMessage(),__LINE__); |
| 144 |
} |
| 145 |
|
| 146 |
// if the db-column names need to be mapped to different names |
| 147 |
// FIXXME somehow we should be able to do this in the query, but i dont know how to select |
| 148 |
// only those columns, use "as" on them and select the rest, without getting those columns again :-( |
| 149 |
if( $map ) |
| 150 |
foreach( $res as $id=>$aResult ) // map each result |
| 151 |
{ |
| 152 |
foreach( $map as $key=>$columnName ) |
| 153 |
{ |
| 154 |
$res[$id][$key] = $res[$id][$columnName]; |
| 155 |
unset($res[$id][$columnName]); |
| 156 |
} |
| 157 |
} |
| 158 |
|
| 159 |
return $res; |
| 160 |
} |
| 161 |
|
| 162 |
/** |
| 163 |
* adds _one_ new element in the tree under the given parent |
| 164 |
* the values' keys given have to match the db-columns, because the |
| 165 |
* value gets inserted in the db directly |
| 166 |
* to add an entire node containing children and so on see 'addNode()' |
| 167 |
* |
| 168 |
* to ba compatible, to the DBnested u can also give the parent and previd as the second and third parameter |
| 169 |
* |
| 170 |
* @see addNode() |
| 171 |
* @version 2001/10/09 |
| 172 |
* @access public |
| 173 |
* @author Wolfram Kriesing <wolfram@kriesing.de> |
| 174 |
* @param array $newValues this array contains the values that shall be inserted in the db-table |
| 175 |
* the key for each element is the name of the column |
| 176 |
* @return mixed either boolean false on failure or the id of the inserted row |
| 177 |
*/ |
| 178 |
function add( $newValues , $parentId=0 ) |
| 179 |
{ |
| 180 |
// FIXXME use $this->dbh->tableInfo to check which columns exist |
| 181 |
// so only data for which a column exist is inserted |
| 182 |
if( $parentId ) |
| 183 |
$newValues['parentId'] = $parentId; |
| 184 |
|
| 185 |
$newData = array(); |
| 186 |
foreach( $newValues as $key=>$value ) // quote the values, as needed for the insert |
| 187 |
{ |
| 188 |
$newData[$this->_getColName($key)] = $this->dbh->quote($value); |
| 189 |
} |
| 190 |
|
| 191 |
// use sequences to create a new id in the db-table |
| 192 |
$nextId = $this->dbh->nextId($this->table); |
| 193 |
$query = sprintf("INSERT INTO %s (%s,%s) VALUES (%s,%s)", |
| 194 |
$this->table , |
| 195 |
$this->_getColName('id'), |
| 196 |
implode( ',' , array_keys($newData) ) , |
| 197 |
$nextId, |
| 198 |
implode( ',' , $newData ) ); |
| 199 |
if( DB::isError( $res = $this->dbh->query( $query ) ) ) |
| 200 |
{ |
| 201 |
// TODO raise PEAR error |
| 202 |
printf("ERROR - Tree::add - %s - %s<br>",DB::errormessage($res),$query); |
| 203 |
return false; |
| 204 |
} |
| 205 |
|
| 206 |
return $nextId; |
| 207 |
} // end of function |
| 208 |
|
| 209 |
/** |
| 210 |
* removes the given node |
| 211 |
* |
| 212 |
* @version 2001/10/09 |
| 213 |
* @access public |
| 214 |
* @author Wolfram Kriesing <wolfram@kriesing.de> |
| 215 |
* @param mixed $id the id of the node to be removed, or an array of id's to be removed |
| 216 |
* @return boolean true on success |
| 217 |
*/ |
| 218 |
function remove( $id ) |
| 219 |
{ |
| 220 |
// if the one to remove has children, get their id's to remove them too |
| 221 |
if( $this->hasChildren($id) ) |
| 222 |
$id = $this->walk( array('_remove',$this) , $id , 'array' ); |
| 223 |
|
| 224 |
$idColumnName = 'id'; |
| 225 |
$map = $this->getOption('columnNameMaps'); |
| 226 |
if( isset($map['id']) ) // if there are maps given |
| 227 |
{ |
| 228 |
$idColumnName = $map['id']; |
| 229 |
} |
| 230 |
|
| 231 |
$whereClause = "WHERE $idColumnName=$id"; |
| 232 |
if( is_array($id) ) |
| 233 |
{ |
| 234 |
$whereClause = "WHERE $idColumnName in (".implode( ',' , $id ).')'; |
| 235 |
} |
| 236 |
|
| 237 |
$query = "DELETE FROM {$this->table} $whereClause"; |
| 238 |
//print("<br>".$query); |
| 239 |
if( DB::isError( $res = $this->dbh->query( $query ) ) ) |
| 240 |
{ |
| 241 |
// TODO raise PEAR error |
| 242 |
printf("ERROR - Tree::remove - %s - %s<br>",DB::errormessage($res),$query); |
| 243 |
return false; |
| 244 |
} |
| 245 |
// TODO if remove succeeded set prevId of the following element properly |
| 246 |
|
| 247 |
return true; |
| 248 |
} // end of function |
| 249 |
|
| 250 |
/** |
| 251 |
* move an entry under a given parent or behind a given entry |
| 252 |
* |
| 253 |
* @version 2001/10/10 |
| 254 |
* @access public |
| 255 |
* @author Wolfram Kriesing <wolfram@kriesing.de> |
| 256 |
* @param integer $idToMove the id of the element that shall be moved |
| 257 |
* @param integer $newParentId the id of the element which will be the new parent |
| 258 |
* @param integer $newPrevId if prevId is given the element with the id idToMove |
| 259 |
* shall be moved _behind_ the element with id=prevId |
| 260 |
* if it is 0 it will be put at the beginning |
| 261 |
* if no prevId is in the DB it can be 0 too and won't bother |
| 262 |
* since it is not written in the DB anyway |
| 263 |
* @return boolean true for success |
| 264 |
*/ |
| 265 |
function move( $idToMove , $newParentId , $newPrevId=0 ) |
| 266 |
{ |
| 267 |
|
| 268 |
$idColumnName = 'id'; |
| 269 |
$parentIdColumnName = 'parentId'; |
| 270 |
$map = $this->getOption('columnNameMaps'); |
| 271 |
if( isset($map['id']) ) |
| 272 |
$idColumnName = $map['id']; |
| 273 |
if( isset($map['parentId']) ) |
| 274 |
$parentIdColumnName = $map['parentId']; |
| 275 |
// FIXXME todo: previous stuff |
| 276 |
|
| 277 |
// set the parent in the DB |
| 278 |
$query = "UPDATE $this->table SET $parentIdColumnName=$newParentId WHERE $idColumnName=$idToMove"; |
| 279 |
//print($query); |
| 280 |
if( DB::isError( $res = $this->dbh->query( $query ) ) ) |
| 281 |
{ |
| 282 |
// TODO raise PEAR error |
| 283 |
printf("ERROR - Tree::move - %s - %s<br>",DB::errormessage($res),$query); |
| 284 |
return false; |
| 285 |
} |
| 286 |
// FIXXME update the prevId's of the elements where the element was moved away from and moved in |
| 287 |
|
| 288 |
return true; |
| 289 |
} // end of function |
| 290 |
|
| 291 |
/** |
| 292 |
* update an element in the DB |
| 293 |
* |
| 294 |
* @version 2002/01/17 |
| 295 |
* @access public |
| 296 |
* @author Wolfram Kriesing <wolfram@kriesing.de> |
| 297 |
* @param array $newData all the new data, the key 'id' is used to |
| 298 |
* build the 'WHERE id=' clause and all the other |
| 299 |
* elements are the data to fill in the DB |
| 300 |
* @return boolean true for success |
| 301 |
*/ |
| 302 |
function update( $id , $newData ) |
| 303 |
{ |
| 304 |
|
| 305 |
// FIXXME check $this->dbh->tableInfo to see if all the columns that shall be updated |
| 306 |
// really exist, this will also extract nextId etc. if given before writing it in the DB |
| 307 |
// in case they dont exist in the DB |
| 308 |
$setData = array(); |
| 309 |
foreach( $newData as $key=>$value ) // quote the values, as needed for the insert |
| 310 |
{ |
| 311 |
$setData[] = $this->_getColName($key).'='.$this->dbh->quote($value); |
| 312 |
} |
| 313 |
|
| 314 |
$query = sprintf( 'UPDATE %s SET %s WHERE %s=%s', |
| 315 |
$this->table, |
| 316 |
implode( ',' , $setData ), |
| 317 |
$this->_getColName('id'), |
| 318 |
$id |
| 319 |
); |
| 320 |
if( DB::isError( $res=$this->dbh->query($query) ) ) |
| 321 |
{ |
| 322 |
// FIXXME raise PEAR error |
| 323 |
printf("ERROR - Tree::update - %s - %s<br>",DB::errormessage($res),$query); |
| 324 |
return false; |
| 325 |
} |
| 326 |
|
| 327 |
return true; |
| 328 |
} // end of function |
| 329 |
|
| 330 |
/** |
| 331 |
* |
| 332 |
* |
| 333 |
* @access private |
| 334 |
* @version 2002/03/02 |
| 335 |
* @author Wolfram Kriesing <wolfram@kriesing.de> |
| 336 |
* @param |
| 337 |
* @return |
| 338 |
*/ |
| 339 |
function _throwError( $msg , $line , $mode=null ) |
| 340 |
{ |
| 341 |
return new Tree_Error( $msg , $line , __FILE__ , $mode , $this->db->last_query ); |
| 342 |
} |
| 343 |
|
| 344 |
|
| 345 |
|
| 346 |
/** |
| 347 |
* prepare multiple results |
| 348 |
* |
| 349 |
* @see _prepareResult() |
| 350 |
* @access private |
| 351 |
* @version 2002/03/03 |
| 352 |
* @author Wolfram Kriesing <wolfram@kriesing.de> |
| 353 |
* @param |
| 354 |
* @return |
| 355 |
*/ |
| 356 |
function _prepareResults( $results ) |
| 357 |
{ |
| 358 |
$newResults = array(); |
| 359 |
foreach( $results as $aResult ) |
| 360 |
$newResults[] = $this->_prepareResult($aResult); |
| 361 |
return $newResults; |
| 362 |
} |
| 363 |
|
| 364 |
/** |
| 365 |
* map back the index names to get what is expected |
| 366 |
* |
| 367 |
* @access private |
| 368 |
* @version 2002/03/03 |
| 369 |
* @author Wolfram Kriesing <wolfram@kriesing.de> |
| 370 |
* @param |
| 371 |
* @return |
| 372 |
*/ |
| 373 |
function _prepareResult( $result ) |
| 374 |
{ |
| 375 |
$map = $this->getOption('columnNameMaps'); |
| 376 |
|
| 377 |
if( $map ) |
| 378 |
foreach( $map as $key=>$columnName ) |
| 379 |
{ |
| 380 |
$result[$key] = $result[$columnName]; |
| 381 |
unset($result[$columnName]); |
| 382 |
} |
| 383 |
return $result; |
| 384 |
} |
| 385 |
|
| 386 |
/** |
| 387 |
* this method retreives the real column name, as used in the DB |
| 388 |
* since the internal names are fixed, to be portable between different |
| 389 |
* DB-column namings, we map the internal name to the real column name here |
| 390 |
* |
| 391 |
* @access private |
| 392 |
* @version 2002/03/02 |
| 393 |
* @author Wolfram Kriesing <wolfram@kriesing.de> |
| 394 |
* @param |
| 395 |
* @return |
| 396 |
*/ |
| 397 |
function _getColName( $internalName ) |
| 398 |
{ |
| 399 |
if( $map = $this->getOption( 'columnNameMaps' ) ) |
| 400 |
{ |
| 401 |
if( isset($map[$internalName]) ) |
| 402 |
return $map[$internalName]; |
| 403 |
} |
| 404 |
return $internalName; |
| 405 |
} |
| 406 |
|
| 407 |
|
| 408 |
} // end of class |
| 409 |
?> |
Parent Directory
| 
Revision Log
