1 |
joko |
1.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 |
|
|
?> |