1
by Ola Lundqvist
Import upstream version 3.0.4 |
1 |
<?php
|
2 |
/**
|
|
3 |
* The DataTree_sql:: class provides an SQL implementation of the Horde
|
|
4 |
* DataTree system.
|
|
5 |
*
|
|
6 |
* Required parameter for the constructor:
|
|
7 |
* 'phptype' -- The database type (ie. 'pgsql', 'mysql, etc.).
|
|
8 |
* 'hostspec' -- The hostname of the database server.
|
|
9 |
* 'protocol' -- The communication protocol ('tcp', 'unix', etc.).
|
|
10 |
* 'username' -- The username with which to connect to the database.
|
|
11 |
* 'password' -- The password associated with 'username'.
|
|
12 |
* 'database' -- The name of the database.
|
|
13 |
* 'charset' -- The charset used by the database.
|
|
14 |
*
|
|
15 |
* Optional values:
|
|
16 |
* 'table' -- The name of the data table in 'database'. Defaults to
|
|
17 |
* 'horde_datatree'.
|
|
18 |
*
|
|
19 |
* The table structure for the DataTree system is in
|
|
20 |
* horde/scripts/db/datatree.sql.
|
|
21 |
*
|
|
22 |
* $Horde: framework/DataTree/DataTree/sql.php,v 1.156.2.6 2005/03/23 10:39:06 jan Exp $
|
|
23 |
*
|
|
24 |
* Copyright 1999-2005 Stephane Huther <shuther@bigfoot.com>
|
|
25 |
* Copyright 2001-2005 Chuck Hagenbuch <chuck@horde.org>
|
|
26 |
* Copyright 2005 Jan Schneider <jan@horde.org>
|
|
27 |
*
|
|
28 |
* See the enclosed file COPYING for license information (LGPL). If
|
|
29 |
* you did not receive this file, see
|
|
30 |
* http://www.fsf.org/copyleft/lgpl.html.
|
|
31 |
*
|
|
32 |
* @author Chuck Hagenbuch <chuck@horde.org>
|
|
33 |
* @author Jan Schneider <jan@horde.org>
|
|
34 |
* @author Stephane Huther <shuther@bigfoot.com>
|
|
35 |
* @since Horde 2.1
|
|
36 |
* @package Horde_DataTree
|
|
37 |
*/
|
|
38 |
class DataTree_sql extends DataTree { |
|
39 |
||
40 |
/**
|
|
41 |
* Handle for the current database connection.
|
|
42 |
*
|
|
43 |
* @var resource $_db
|
|
44 |
*/
|
|
45 |
var $_db; |
|
46 |
||
47 |
/**
|
|
48 |
* The number of copies of the horde_datatree_attributes table
|
|
49 |
* that we need to join on in the current query.
|
|
50 |
*
|
|
51 |
* @var integer $_tableCount
|
|
52 |
*/
|
|
53 |
var $_tableCount = 1; |
|
54 |
||
55 |
/**
|
|
56 |
* Constructs a new SQL DataTree object.
|
|
57 |
*
|
|
58 |
* @param array $params A hash containing connection parameters.
|
|
59 |
*/
|
|
60 |
function DataTree_sql($params) |
|
61 |
{
|
|
62 |
parent::DataTree($params); |
|
63 |
$this->_connect(); |
|
64 |
}
|
|
65 |
||
66 |
/**
|
|
67 |
* Returns a list of all groups (root nodes) of the data tree.
|
|
68 |
*
|
|
69 |
* @return array The the group IDs
|
|
70 |
*/
|
|
71 |
function getGroups() |
|
72 |
{
|
|
73 |
$query = sprintf('SELECT DISTINCT group_uid FROM %s', |
|
74 |
$this->_params['table']); |
|
75 |
||
76 |
Horde::logMessage('SQL Query by DataTree_sql::getGroups(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
77 |
||
78 |
return $this->_db->getCol($query); |
|
79 |
}
|
|
80 |
||
81 |
/**
|
|
82 |
* Load (a subset of) the datatree into the $_data array.
|
|
83 |
*
|
|
84 |
* @access private
|
|
85 |
*
|
|
86 |
* @param string $root Which portion of the tree to load.
|
|
87 |
* Defaults to all of it.
|
|
88 |
* @param boolean $loadTree Load a tree starting at $root, or just the
|
|
89 |
* requested level and direct parents?
|
|
90 |
* Defaults to single level.
|
|
91 |
* @param boolean $reload Re-load already loaded values?
|
|
92 |
*
|
|
93 |
* @return mixed True on success or a PEAR_Error on failure.
|
|
94 |
*/
|
|
95 |
function _load($root = DATATREE_ROOT, $loadTree = false, $reload = false) |
|
96 |
{
|
|
97 |
/* Do NOT use DataTree::exists() here; that would cause an
|
|
98 |
* infinite loop. */
|
|
99 |
if (!$reload && |
|
100 |
(in_array($root, $this->_nameMap) || |
|
101 |
(count($this->_data) && $root == DATATREE_ROOT))) { |
|
102 |
return true; |
|
103 |
}
|
|
104 |
||
105 |
$query = $this->_buildLoadQuery($root, $loadTree, false); |
|
106 |
if (is_a($query, 'PEAR_Error')) { |
|
107 |
return $query; |
|
108 |
}
|
|
109 |
if (empty($query)) { |
|
110 |
return true; |
|
111 |
}
|
|
112 |
||
113 |
Horde::logMessage('SQL Query by DataTree_sql::_load(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
114 |
$data = $this->_db->getAll($query); |
|
115 |
if (is_a($data, 'PEAR_Error')) { |
|
116 |
return $data; |
|
117 |
}
|
|
118 |
return $this->set($data, $this->_params['charset']); |
|
119 |
}
|
|
120 |
||
121 |
/**
|
|
122 |
* Counts (a subset of) the datatree which would be loaded into
|
|
123 |
* the $_data array if _load() is called with the same value of
|
|
124 |
* $root.
|
|
125 |
*
|
|
126 |
* @access private
|
|
127 |
*
|
|
128 |
* @param string $root Which portion of the tree to load.
|
|
129 |
* Defaults to all of it.
|
|
130 |
*
|
|
131 |
* @return integer Number of objects
|
|
132 |
*/
|
|
133 |
function _count($root = DATATREE_ROOT) |
|
134 |
{
|
|
135 |
$query = $this->_buildLoadQuery($root, true, true); |
|
136 |
if (is_a($query, 'PEAR_Error')) { |
|
137 |
return $query; |
|
138 |
}
|
|
139 |
if (empty($query)) { |
|
140 |
return 0; |
|
141 |
}
|
|
142 |
Horde::logMessage('SQL Query by DataTree_sql::_count(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
143 |
return $this->_db->getOne($query); |
|
144 |
}
|
|
145 |
||
146 |
/**
|
|
147 |
* Load (a subset of) the datatree into the $_data array.
|
|
148 |
*
|
|
149 |
* @access private
|
|
150 |
*
|
|
151 |
* @param string $root Which portion of the tree to load.
|
|
152 |
* Defaults to all of it.
|
|
153 |
* @param boolean $loadTree Load a tree starting at $root, or just the
|
|
154 |
* requested level and direct parents?
|
|
155 |
* Defaults to single level.
|
|
156 |
* @param integer $count Only count objects?
|
|
157 |
*
|
|
158 |
* @return mixed True on success or a PEAR_Error on failure.
|
|
159 |
*/
|
|
160 |
function _buildLoadQuery($root = DATATREE_ROOT, $loadTree = false, $count = false) |
|
161 |
{
|
|
162 |
if (!empty($root) && $root != DATATREE_ROOT) { |
|
163 |
if (strpos($root, ':') !== false) { |
|
164 |
$parts = explode(':', $root); |
|
165 |
$root = array_pop($parts); |
|
166 |
}
|
|
167 |
$root = (string)$root; |
|
168 |
||
169 |
$query = sprintf('SELECT datatree_id, datatree_parents FROM %s' . |
|
170 |
' WHERE datatree_name = %s AND group_uid = %s ORDER BY datatree_id', |
|
171 |
$this->_params['table'], |
|
172 |
$this->_db->quote($root), |
|
173 |
$this->_db->quote($this->_params['group'])); |
|
174 |
||
175 |
Horde::logMessage('SQL Query by DataTree_sql::_buildLoadQuery(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
176 |
$root = $this->_db->getAssoc($query); |
|
177 |
if (is_a($root, 'PEAR_Error')) { |
|
178 |
return $root; |
|
179 |
}
|
|
180 |
if (count($root) == 0) { |
|
181 |
return ''; |
|
182 |
}
|
|
183 |
||
184 |
$where = ''; |
|
185 |
$first_time = true; |
|
186 |
foreach ($root as $object_id => $object_parents) { |
|
187 |
$pstring = $object_parents . ':' . $object_id . '%'; |
|
188 |
$pquery = ''; |
|
189 |
if (!empty($object_parents)) { |
|
190 |
$ids = substr($object_parents, 1); |
|
191 |
$pquery = ' OR datatree_id IN (' . str_replace(':', ', ', $ids) . ')'; |
|
192 |
}
|
|
193 |
if ($loadTree) { |
|
194 |
$pquery .= ' OR datatree_parents = ' . $this->_db->quote(substr($pstring, 0, -2)); |
|
195 |
}
|
|
196 |
||
197 |
if (!$first_time) { |
|
198 |
$where .= ' OR '; |
|
199 |
}
|
|
200 |
$where .= sprintf('datatree_parents LIKE %s OR datatree_id = %s%s', |
|
201 |
$this->_db->quote($pstring), |
|
202 |
$object_id, |
|
203 |
$pquery); |
|
204 |
||
205 |
$first_time = false; |
|
206 |
}
|
|
207 |
||
208 |
$query = sprintf('SELECT %s FROM %s WHERE (%s) AND group_uid = %s', |
|
209 |
$count ? 'COUNT(*)' : 'datatree_id, datatree_name, datatree_parents, datatree_order', |
|
210 |
$this->_params['table'], |
|
211 |
$where, |
|
212 |
$this->_db->quote($this->_params['group'])); |
|
213 |
} else { |
|
214 |
$query = sprintf('SELECT %s FROM %s WHERE group_uid = %s', |
|
215 |
$count ? 'COUNT(*)' : 'datatree_id, datatree_name, datatree_parents, datatree_order', |
|
216 |
$this->_params['table'], |
|
217 |
$this->_db->quote($this->_params['group'])); |
|
218 |
}
|
|
219 |
||
220 |
return $query; |
|
221 |
}
|
|
222 |
||
223 |
/**
|
|
224 |
* Load a set of objects identified by their unique IDs, and their
|
|
225 |
* parents, into the $_data array.
|
|
226 |
*
|
|
227 |
* @access private
|
|
228 |
*
|
|
229 |
* @param mixed $cids The unique ID of the object to load, or an array of
|
|
230 |
* object ids.
|
|
231 |
*
|
|
232 |
* @return mixed True on success or a PEAR_Error on failure.
|
|
233 |
*/
|
|
234 |
function _loadById($cids) |
|
235 |
{
|
|
236 |
/* Make sure we have an array. */
|
|
237 |
if (!is_array($cids)) { |
|
238 |
$cids = array($cids); |
|
239 |
}
|
|
240 |
||
241 |
/* Bail out now if there's nothing to load. */
|
|
242 |
if (!count($cids)) { |
|
243 |
return true; |
|
244 |
}
|
|
245 |
||
246 |
/* Don't load any that are already loaded. Also, make sure
|
|
247 |
* that everything in the $ids array that we are building is
|
|
248 |
* an integer. */
|
|
249 |
$ids = array(); |
|
250 |
foreach ($cids as $cid) { |
|
251 |
/* Do NOT use DataTree::exists() here; that would cause an
|
|
252 |
* infinite loop. */
|
|
253 |
if (!isset($this->_data[$cid])) { |
|
254 |
$ids[] = (int)$cid; |
|
255 |
}
|
|
256 |
}
|
|
257 |
||
258 |
/* If there are none left to load, return. */
|
|
259 |
if (!count($ids)) { |
|
260 |
return true; |
|
261 |
}
|
|
262 |
||
263 |
$in = array_search(DATATREE_ROOT, $ids) === false ? sprintf('datatree_id IN (%s) AND ', implode(', ', $ids)) : ''; |
|
264 |
$query = sprintf('SELECT datatree_id, datatree_parents FROM %s' . |
|
265 |
' WHERE %sgroup_uid = %s' . |
|
266 |
' ORDER BY datatree_id', |
|
267 |
$this->_params['table'], |
|
268 |
$in, |
|
269 |
$this->_db->quote($this->_params['group'])); |
|
270 |
Horde::logMessage('SQL Query by DataTree_sql::_loadById(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
271 |
$parents = $this->_db->getAssoc($query); |
|
272 |
if (is_a($parents, 'PEAR_Error')) { |
|
273 |
return $parents; |
|
274 |
}
|
|
275 |
||
276 |
$ids = array(); |
|
277 |
foreach ($parents as $cid => $parent) { |
|
278 |
$ids[(int)$cid] = (int)$cid; |
|
279 |
||
280 |
$pids = explode(':', substr($parent, 1)); |
|
281 |
foreach ($pids as $pid) { |
|
282 |
$pid = (int)$pid; |
|
283 |
if (!isset($this->_data[$pid])) { |
|
284 |
$ids[$pid] = $pid; |
|
285 |
}
|
|
286 |
}
|
|
287 |
}
|
|
288 |
||
289 |
/* If $ids is empty, we have nothing to load. */
|
|
290 |
if (!count($ids)) { |
|
291 |
return true; |
|
292 |
}
|
|
293 |
||
294 |
$query = sprintf('SELECT datatree_id, datatree_name, datatree_parents, datatree_order FROM %s' . |
|
295 |
' WHERE datatree_id IN (%s)'. |
|
296 |
' AND group_uid = %s ORDER BY datatree_id', |
|
297 |
$this->_params['table'], |
|
298 |
implode(', ', $ids), |
|
299 |
$this->_db->quote($this->_params['group'])); |
|
300 |
||
301 |
Horde::logMessage('SQL Query by DataTree_sql::_loadById(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
302 |
$data = $this->_db->getAll($query); |
|
303 |
if (is_a($data, 'PEAR_Error')) { |
|
304 |
return $data; |
|
305 |
}
|
|
306 |
||
307 |
return $this->set($data, $this->_params['charset']); |
|
308 |
}
|
|
309 |
||
310 |
/**
|
|
311 |
* Adds an object.
|
|
312 |
*
|
|
313 |
* @param mixed $object The object to add (string or DataTreeObject).
|
|
314 |
* @param bool $id_as_name Whether the object ID is to be used as object
|
|
315 |
* name. Used in situations where there is no
|
|
316 |
* available unique input for object name.
|
|
317 |
* Defaults to false.
|
|
318 |
*/
|
|
319 |
function add($object, $id_as_name = false) |
|
320 |
{
|
|
321 |
$attributes = false; |
|
322 |
if (is_a($object, 'DataTreeObject')) { |
|
323 |
$fullname = $object->getName(); |
|
324 |
$order = $object->order; |
|
325 |
||
326 |
/* We handle data differently if we can map it to the
|
|
327 |
* horde_datatree_attributes table. */
|
|
328 |
if (method_exists($object, '_toAttributes')) { |
|
329 |
$data = ''; |
|
330 |
$ser = null; |
|
331 |
||
332 |
/* Set a flag for later so that we know to insert the
|
|
333 |
* attribute rows. */
|
|
334 |
$attributes = true; |
|
335 |
} else { |
|
336 |
require_once 'Horde/Serialize.php'; |
|
337 |
$ser = SERIALIZE_UTF7_BASIC; |
|
338 |
$data = Horde_Serialize::serialize($object->getData(), $ser, NLS::getCharset()); |
|
339 |
}
|
|
340 |
} else { |
|
341 |
$fullname = $object; |
|
342 |
$order = null; |
|
343 |
$data = ''; |
|
344 |
$ser = null; |
|
345 |
}
|
|
346 |
||
347 |
/* Get the next unique ID. */
|
|
348 |
$id = $this->_db->nextId($this->_params['table']); |
|
349 |
if (is_a($id, 'PEAR_Error')) { |
|
350 |
Horde::logMessage($id, __FILE__, __LINE__, PEAR_LOG_ERR); |
|
351 |
return $id; |
|
352 |
}
|
|
353 |
||
354 |
if (strpos($fullname, ':') !== false) { |
|
355 |
$parts = explode(':', $fullname); |
|
356 |
$parents = ''; |
|
357 |
$pstring = ''; |
|
358 |
if ($id_as_name) { |
|
359 |
/* Requested use of ID as name, so discard current name. */
|
|
360 |
array_pop($parts); |
|
361 |
/* Set name to ID. */
|
|
362 |
$name = $id; |
|
363 |
/* Modify fullname to reflect new name. */
|
|
364 |
$fullname = implode(':', $parts) . ':' . $id; |
|
365 |
if (is_a($object, 'DataTreeObject')) { |
|
366 |
$object->setName($fullname); |
|
367 |
} else { |
|
368 |
$object = $fullname; |
|
369 |
}
|
|
370 |
} else { |
|
371 |
$name = array_pop($parts); |
|
372 |
}
|
|
373 |
foreach ($parts as $par) { |
|
374 |
$pstring .= (empty($pstring) ? '' : ':') . $par; |
|
375 |
$pid = $this->getId($pstring); |
|
376 |
if (is_a($pid, 'PEAR_Error')) { |
|
377 |
/* Auto-create parents. */
|
|
378 |
$pid = $this->add($pstring); |
|
379 |
if (is_a($pid, 'PEAR_Error')) { |
|
380 |
return $pid; |
|
381 |
}
|
|
382 |
}
|
|
383 |
$parents .= ':' . $pid; |
|
384 |
}
|
|
385 |
} else { |
|
386 |
if ($id_as_name) { |
|
387 |
/* Requested use of ID as name, set fullname and name to ID. */
|
|
388 |
$fullname = $id; |
|
389 |
$name = $id; |
|
390 |
if (is_a($object, 'DataTreeObject')) { |
|
391 |
$object->setName($fullname); |
|
392 |
} else { |
|
393 |
$object = $fullname; |
|
394 |
}
|
|
395 |
} else { |
|
396 |
$name = $fullname; |
|
397 |
}
|
|
398 |
$parents = ''; |
|
399 |
$pid = DATATREE_ROOT; |
|
400 |
}
|
|
401 |
||
402 |
if (parent::exists($fullname)) { |
|
403 |
return PEAR::raiseError(_("Already exists")); |
|
404 |
}
|
|
405 |
||
406 |
$query = sprintf('INSERT INTO %s (datatree_id, group_uid, datatree_name, datatree_order, datatree_data, user_uid, datatree_serialized, datatree_parents)' . |
|
407 |
' VALUES (%s, %s, %s, %s, %s, %s, %s, %s)', |
|
408 |
$this->_params['table'], |
|
409 |
(int)$id, |
|
410 |
$this->_db->quote($this->_params['group']), |
|
411 |
$this->_db->quote(String::convertCharset($name, NLS::getCharset(), $this->_params['charset'])), |
|
412 |
is_null($order) ? 'NULL' : (int)$order, |
|
413 |
$this->_db->quote($data), |
|
414 |
$this->_db->quote((string)Auth::getAuth()), |
|
415 |
(int)$ser, |
|
416 |
$this->_db->quote($parents)); |
|
417 |
||
418 |
Horde::logMessage('SQL Query by DataTree_sql::add(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
419 |
$result = $this->_db->query($query); |
|
420 |
if (is_a($result, 'PEAR_Error')) { |
|
421 |
Horde::logMessage($result, __FILE__, __LINE__, PEAR_LOG_ERR); |
|
422 |
return $result; |
|
423 |
}
|
|
424 |
||
425 |
$reorder = $this->reorder($parents, $order, $id); |
|
426 |
if (is_a($reorder, 'PEAR_Error')) { |
|
427 |
Horde::logMessage($reorder, __FILE__, __LINE__, PEAR_LOG_ERR); |
|
428 |
return $reorder; |
|
429 |
}
|
|
430 |
||
431 |
$result = parent::_add($fullname, $id, $pid, $order); |
|
432 |
if (is_a($result, 'PEAR_Error')) { |
|
433 |
return $result; |
|
434 |
}
|
|
435 |
||
436 |
/* If we succesfully inserted the object and it supports
|
|
437 |
* being mapped to the attributes table, do that now: */
|
|
438 |
if (!empty($attributes)) { |
|
439 |
$result = $this->updateData($object); |
|
440 |
if (is_a($result, 'PEAR_Error')) { |
|
441 |
return $result; |
|
442 |
}
|
|
443 |
}
|
|
444 |
||
445 |
return $id; |
|
446 |
}
|
|
447 |
||
448 |
/**
|
|
449 |
* Changes the order of the children of an object.
|
|
450 |
*
|
|
451 |
* @param string $parent The full id path of the parent object.
|
|
452 |
* @param mixed $order If an array it specifies the new positions for
|
|
453 |
* all child objects.
|
|
454 |
* If an integer and $cid is specified, the position
|
|
455 |
* where the child specified by $cid is inserted. If
|
|
456 |
* $cid is not specified, the position gets deleted,
|
|
457 |
* causing the following positions to shift up.
|
|
458 |
* @param integer $cid See $order.
|
|
459 |
*/
|
|
460 |
function reorder($parent, $order = null, $cid = null) |
|
461 |
{
|
|
462 |
if (!$parent || is_a($parent, 'PEAR_Error')) { |
|
463 |
// Abort immediately if the parent string is empty; we
|
|
464 |
// cannot safely reorder all top-level elements.
|
|
465 |
return; |
|
466 |
}
|
|
467 |
||
468 |
$pquery = ''; |
|
469 |
if (!is_array($order) && !is_null($order)) { |
|
470 |
/* Single update (add/del). */
|
|
471 |
if (is_null($cid)) { |
|
472 |
/* No object id given so shuffle down. */
|
|
473 |
$direction = '-'; |
|
474 |
} else { |
|
475 |
/* We have an object id so shuffle up. */
|
|
476 |
$direction = '+'; |
|
477 |
||
478 |
/* Leaving the newly inserted object alone. */
|
|
479 |
$pquery = sprintf(' AND datatree_id != %s', (int)$cid); |
|
480 |
}
|
|
481 |
$query = sprintf('UPDATE %s SET datatree_order = datatree_order %s 1 WHERE group_uid = %s AND datatree_parents = %s AND datatree_order >= %s', |
|
482 |
$this->_params['table'], |
|
483 |
$direction, |
|
484 |
$this->_db->quote($this->_params['group']), |
|
485 |
$this->_db->quote($parent), |
|
486 |
is_null($order) ? 'NULL' : (int)$order) . $pquery; |
|
487 |
||
488 |
Horde::logMessage('SQL Query by DataTree_sql::reorder(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
489 |
$result = $this->_db->query($query); |
|
490 |
||
491 |
} elseif (is_array($order)) { |
|
492 |
/* Multi update. */
|
|
493 |
$query = sprintf('SELECT COUNT(datatree_id) FROM %s WHERE group_uid = %s AND datatree_parents = %s GROUP BY datatree_parents', |
|
494 |
$this->_params['table'], |
|
495 |
$this->_db->quote($this->_params['group']), |
|
496 |
$this->_db->quote($parent)); |
|
497 |
||
498 |
Horde::logMessage('SQL Query by DataTree_sql::reorder(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
499 |
||
500 |
$result = $this->_db->getOne($query); |
|
501 |
if (is_a($result, 'PEAR_Error')) { |
|
502 |
return $result; |
|
503 |
} elseif (count($order) != $result) { |
|
504 |
return PEAR::raiseError(_("Cannot reorder, number of entries supplied for reorder does not match number stored.")); |
|
505 |
}
|
|
506 |
||
507 |
$o_key = 0; |
|
508 |
foreach ($order as $o_cid) { |
|
509 |
$query = sprintf('UPDATE %s SET datatree_order = %s WHERE datatree_id = %s', |
|
510 |
$this->_params['table'], |
|
511 |
(int)$o_key, |
|
512 |
is_null($o_cid) ? 'NULL' : (int)$o_cid); |
|
513 |
||
514 |
Horde::logMessage('SQL Query by DataTree_sql::reorder(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
515 |
$result = $this->_db->query($query); |
|
516 |
if (is_a($result, 'PEAR_Error')) { |
|
517 |
return $result; |
|
518 |
}
|
|
519 |
||
520 |
$o_key++; |
|
521 |
}
|
|
522 |
||
523 |
$pid = $this->getId($parent); |
|
524 |
||
525 |
/* Re-order our cache. */
|
|
526 |
return $this->_reorder($pid, $order); |
|
527 |
}
|
|
528 |
}
|
|
529 |
||
530 |
/**
|
|
531 |
* Explicitly set the order for a datatree object.
|
|
532 |
*
|
|
533 |
* @param integer $id The datatree object id to change.
|
|
534 |
* @param integer $order The new order.
|
|
535 |
*/
|
|
536 |
function setOrder($id, $order) |
|
537 |
{
|
|
538 |
$query = sprintf('UPDATE %s SET datatree_order = %s WHERE datatree_id = %s', |
|
539 |
$this->_params['table'], |
|
540 |
is_null($order) ? 'NULL' : (int)$order, |
|
541 |
(int)$id); |
|
542 |
||
543 |
Horde::logMessage('SQL Query by DataTree_sql::setOrder(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
544 |
return $this->_db->query($query); |
|
545 |
}
|
|
546 |
||
547 |
/**
|
|
548 |
* Removes an object.
|
|
549 |
*
|
|
550 |
* @param mixed $object The object to remove.
|
|
551 |
* @param boolean $force Force removal of every child object?
|
|
552 |
*/
|
|
553 |
function remove($object, $force = false) |
|
554 |
{
|
|
555 |
$id = $this->getId($object); |
|
556 |
$order = $this->getOrder($object); |
|
557 |
||
558 |
$query = sprintf('SELECT datatree_id FROM %s ' . |
|
559 |
' WHERE group_uid = %s AND datatree_parents LIKE %s' . |
|
560 |
' ORDER BY datatree_id', |
|
561 |
$this->_params['table'], |
|
562 |
$this->_db->quote($this->_params['group']), |
|
563 |
$this->_db->quote('%:' . (int)$id . '')); |
|
564 |
||
565 |
Horde::logMessage('SQL Query by DataTree_sql::remove(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
566 |
$children = $this->_db->getAll($query, DB_FETCHMODE_ASSOC); |
|
567 |
||
568 |
if (count($children)) { |
|
569 |
if ($force) { |
|
570 |
foreach ($children as $child) { |
|
571 |
$cat = $this->getName($child['datatree_id']); |
|
572 |
$result = $this->remove($cat, true); |
|
573 |
if (is_a($result, 'PEAR_Error')) { |
|
574 |
return $result; |
|
575 |
}
|
|
576 |
}
|
|
577 |
} else { |
|
578 |
return PEAR::raiseError(sprintf(_("Cannot remove, %d children exist."), count($children))); |
|
579 |
}
|
|
580 |
}
|
|
581 |
||
582 |
/* Remove attributes for this object. */
|
|
583 |
$query = sprintf('DELETE FROM %s WHERE datatree_id = %s', |
|
584 |
$this->_params['table_attributes'], |
|
585 |
(int)$id); |
|
586 |
||
587 |
Horde::logMessage('SQL Query by DataTree_sql::remove(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
588 |
$result = $this->_db->query($query); |
|
589 |
if (is_a($result, 'PEAR_Error')) { |
|
590 |
return $result; |
|
591 |
}
|
|
592 |
||
593 |
$query = sprintf('DELETE FROM %s WHERE datatree_id = %s', |
|
594 |
$this->_params['table'], |
|
595 |
(int)$id); |
|
596 |
||
597 |
Horde::logMessage('SQL Query by DataTree_sql::remove(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
598 |
$result = $this->_db->query($query); |
|
599 |
if (is_a($result, 'PEAR_Error')) { |
|
600 |
return $result; |
|
601 |
}
|
|
602 |
||
603 |
$parents = $this->getParentIdString($object); |
|
604 |
if (is_a($parents, 'PEAR_Error')) { |
|
605 |
return $parents; |
|
606 |
}
|
|
607 |
||
608 |
$reorder = $this->reorder($parents, $order); |
|
609 |
if (is_a($reorder, 'PEAR_Error')) { |
|
610 |
return $reorder; |
|
611 |
}
|
|
612 |
||
613 |
return is_a(parent::remove($object), 'PEAR_Error') ? $id : true; |
|
614 |
}
|
|
615 |
||
616 |
/**
|
|
617 |
* Remove one or more objects by id. This function does *not* do
|
|
618 |
* the validation, reordering, etc. that remove() does. If you
|
|
619 |
* need to check for children, re-do ordering, etc., then you must
|
|
620 |
* remove() objects one-by-one. This is for code that knows it's
|
|
621 |
* dealing with single (non-parented) objects and needs to delete
|
|
622 |
* a batch of them quickly.
|
|
623 |
*
|
|
624 |
* @param array $ids The objects to remove.
|
|
625 |
*/
|
|
626 |
function removeByIds($ids) |
|
627 |
{
|
|
628 |
/* Sanitize input. */
|
|
629 |
if (!is_array($ids)) { |
|
630 |
$ids = array((int)$ids); |
|
631 |
} else { |
|
632 |
foreach ($ids as $key => $id) { |
|
633 |
$ids[$key] = (int)$id; |
|
634 |
}
|
|
635 |
}
|
|
636 |
||
637 |
/* Remove attributes for $ids. */
|
|
638 |
$query = sprintf('DELETE FROM %s WHERE datatree_id IN (%s)', |
|
639 |
$this->_params['table_attributes'], |
|
640 |
implode(',', $ids)); |
|
641 |
||
642 |
Horde::logMessage('SQL Query by DataTree_sql::removeByIds(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
643 |
$result = $this->_db->query($query); |
|
644 |
if (is_a($result, 'PEAR_Error')) { |
|
645 |
return $result; |
|
646 |
}
|
|
647 |
||
648 |
$query = sprintf('DELETE FROM %s WHERE datatree_id IN %s', |
|
649 |
$this->_params['table'], |
|
650 |
implode(',', $ids)); |
|
651 |
||
652 |
Horde::logMessage('SQL Query by DataTree_sql::removeByIds(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
653 |
return $this->_db->query($query); |
|
654 |
}
|
|
655 |
||
656 |
/**
|
|
657 |
* Remove one or more objects by name. This function does *not* do
|
|
658 |
* the validation, reordering, etc. that remove() does. If you
|
|
659 |
* need to check for children, re-do ordering, etc., then you must
|
|
660 |
* remove() objects one-by-one. This is for code that knows it's
|
|
661 |
* dealing with single (non-parented) objects and needs to delete
|
|
662 |
* a batch of them quickly.
|
|
663 |
*
|
|
664 |
* @param array $names The objects to remove.
|
|
665 |
*/
|
|
666 |
function removeByNames($names) |
|
667 |
{
|
|
668 |
/* Sanitize input. */
|
|
669 |
if (!is_array($names)) { |
|
670 |
$names = array($this->_db->quote($names)); |
|
671 |
} else { |
|
672 |
foreach ($names as $key => $name) { |
|
673 |
$names[$key] = $this->_db->quote($name); |
|
674 |
}
|
|
675 |
}
|
|
676 |
||
677 |
/* Remove attributes for $names. */
|
|
678 |
$query = sprintf('DELETE FROM %s WHERE datatree_name IN (%s)', |
|
679 |
$this->_params['table_attributes'], |
|
680 |
implode(',', $names)); |
|
681 |
||
682 |
Horde::logMessage('SQL Query by DataTree_sql::removeByNames(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
683 |
$result = $this->_db->query($query); |
|
684 |
if (is_a($result, 'PEAR_Error')) { |
|
685 |
return $result; |
|
686 |
}
|
|
687 |
||
688 |
$query = sprintf('DELETE FROM %s WHERE datatree_name IN %s', |
|
689 |
$this->_params['table'], |
|
690 |
implode(',', $names)); |
|
691 |
||
692 |
Horde::logMessage('SQL Query by DataTree_sql::removeByNames(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
693 |
return $this->_db->query($query); |
|
694 |
}
|
|
695 |
||
696 |
/**
|
|
697 |
* Move an object to a new parent.
|
|
698 |
*
|
|
699 |
* @param mixed $object The object to move.
|
|
700 |
* @param string $newparent The new parent object. Defaults to the root.
|
|
701 |
*/
|
|
702 |
function move($object, $newparent = null) |
|
703 |
{
|
|
704 |
$old_parent_path = $this->getParentIdString($object); |
|
705 |
$result = parent::move($object, $newparent); |
|
706 |
if (is_a($result, 'PEAR_Error')) { |
|
707 |
return $result; |
|
708 |
}
|
|
709 |
$id = $this->getId($object); |
|
710 |
$new_parent_path = $this->getParentIdString($object); |
|
711 |
||
712 |
/* Fetch the object being moved and all of its children, since
|
|
713 |
* we also need to update their parent paths to avoid creating
|
|
714 |
* orphans. */
|
|
715 |
$query = sprintf('SELECT datatree_id, datatree_parents FROM %s' . |
|
716 |
' WHERE datatree_parents = %s OR datatree_parents LIKE %s OR datatree_id = %s', |
|
717 |
$this->_params['table'], |
|
718 |
$this->_db->quote($old_parent_path . ':' . $id), |
|
719 |
$this->_db->quote($old_parent_path . ':' . $id . ':%'), |
|
720 |
(int)$id); |
|
721 |
||
722 |
Horde::logMessage('SQL Query by DataTree_sql::move(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
723 |
$rowset = $this->_db->query($query); |
|
724 |
if (is_a($rowset, 'PEAR_Error')) { |
|
725 |
return $rowset; |
|
726 |
}
|
|
727 |
||
728 |
/* Update each object, replacing the old parent path with the
|
|
729 |
* new one. */
|
|
730 |
while ($row = $rowset->fetchRow(DB_FETCHMODE_ASSOC)) { |
|
731 |
if (is_a($row, 'PEAR_Error')) { |
|
732 |
return $row; |
|
733 |
}
|
|
734 |
||
735 |
$oquery = ''; |
|
736 |
if ($row['datatree_id'] == $id) { |
|
737 |
$oquery = ', datatree_order = 0 '; |
|
738 |
}
|
|
739 |
||
740 |
/* Do str_replace() only if this is not a first level
|
|
741 |
* object. */
|
|
742 |
if (!empty($row['datatree_parents'])) { |
|
743 |
$ppath = str_replace($old_parent_path, $new_parent_path, $row['datatree_parents']); |
|
744 |
} else { |
|
745 |
$ppath = $new_parent_path; |
|
746 |
}
|
|
747 |
$query = sprintf('UPDATE %s SET datatree_parents = %s' . $oquery . ' WHERE datatree_id = %s', |
|
748 |
$this->_params['table'], |
|
749 |
$this->_db->quote($ppath), |
|
750 |
(int)$row['datatree_id']); |
|
751 |
||
752 |
Horde::logMessage('SQL Query by DataTree_sql::move(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
753 |
$result = $this->_db->query($query); |
|
754 |
if (is_a($result, 'PEAR_Error')) { |
|
755 |
return $result; |
|
756 |
}
|
|
757 |
}
|
|
758 |
||
759 |
$order = $this->getOrder($object); |
|
760 |
||
761 |
/* Shuffle down the old order positions. */
|
|
762 |
$reorder = $this->reorder($old_parent_path, $order); |
|
763 |
||
764 |
/* Shuffle up the new order positions. */
|
|
765 |
$reorder = $this->reorder($new_parent_path, 0, $id); |
|
766 |
||
767 |
return true; |
|
768 |
}
|
|
769 |
||
770 |
/**
|
|
771 |
* Change an object's name.
|
|
772 |
*
|
|
773 |
* @param mixed $old_object The old object.
|
|
774 |
* @param string $new_object_name The new object name.
|
|
775 |
*/
|
|
776 |
function rename($old_object, $new_object_name) |
|
777 |
{
|
|
778 |
/* Do the cache renaming first */
|
|
779 |
$result = parent::rename($old_object, $new_object_name); |
|
780 |
if (is_a($result, 'PEAR_Error')) { |
|
781 |
return $result; |
|
782 |
}
|
|
783 |
||
784 |
/* Get the object id and set up the sql query. */
|
|
785 |
$id = $this->getId($old_object); |
|
786 |
$query = sprintf('UPDATE %s SET datatree_name = %s' . |
|
787 |
' WHERE datatree_id = %s', |
|
788 |
$this->_params['table'], |
|
789 |
$this->_db->quote(String::convertCharset($new_object_name, NLS::getCharset(), $this->_params['charset'])), |
|
790 |
(int)$id); |
|
791 |
||
792 |
Horde::logMessage('SQL Query by DataTree_sql::rename(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
793 |
$result = $this->_db->query($query); |
|
794 |
||
795 |
return is_a($result, 'PEAR_Error') ? $result : true; |
|
796 |
}
|
|
797 |
||
798 |
/**
|
|
799 |
* Retrieve data for an object from the datatree_data field.
|
|
800 |
*
|
|
801 |
* @param integer $cid The object id to fetch, or an array of object ids.
|
|
802 |
*/
|
|
803 |
function getData($cid) |
|
804 |
{
|
|
805 |
require_once 'Horde/Serialize.php'; |
|
806 |
||
807 |
if (is_array($cid)) { |
|
808 |
if (!count($cid)) { |
|
809 |
return array(); |
|
810 |
}
|
|
811 |
||
812 |
$query = sprintf('SELECT datatree_id, datatree_data, datatree_serialized FROM %s WHERE datatree_id IN (%s)', |
|
813 |
$this->_params['table'], |
|
814 |
implode(', ', $cid)); |
|
815 |
||
816 |
Horde::logMessage('SQL Query by DataTree_sql::getData(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
817 |
$result = $this->_db->getAssoc($query); |
|
818 |
if (is_a($result, 'PEAR_Error')) { |
|
819 |
Horde::logMessage($result, __FILE__, __LINE__, PEAR_LOG_ERR); |
|
820 |
return $result; |
|
821 |
}
|
|
822 |
||
823 |
$data = array(); |
|
824 |
foreach ($result as $id => $row) { |
|
825 |
$data[$id] = Horde_Serialize::unserialize($row[0], $row[1], NLS::getCharset()); |
|
826 |
/* Convert old data to the new format. */
|
|
827 |
if ($row[1] == SERIALIZE_BASIC) { |
|
828 |
$data[$id] = String::convertCharset($data[$id], NLS::getCharset(true)); |
|
829 |
}
|
|
830 |
||
831 |
$data[$id] = (is_null($data[$id]) || !is_array($data[$id])) ? array() : $data[$id]; |
|
832 |
}
|
|
833 |
||
834 |
return $data; |
|
835 |
} else { |
|
836 |
$query = sprintf('SELECT datatree_data, datatree_serialized FROM %s WHERE datatree_id = %s', |
|
837 |
$this->_params['table'], |
|
838 |
(int)$cid); |
|
839 |
||
840 |
Horde::logMessage('SQL Query by DataTree_sql::getData(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
841 |
$row = $this->_db->getRow($query, DB_FETCHMODE_ASSOC); |
|
842 |
||
843 |
$data = Horde_Serialize::unserialize($row['datatree_data'], $row['datatree_serialized'], NLS::getCharset()); |
|
844 |
/* Convert old data to the new format. */
|
|
845 |
if ($row['datatree_serialized'] == SERIALIZE_BASIC) { |
|
846 |
$data = String::convertCharset($data, NLS::getCharset(true)); |
|
847 |
}
|
|
848 |
return (is_null($data) || !is_array($data)) ? array() : $data; |
|
849 |
}
|
|
850 |
}
|
|
851 |
||
852 |
/**
|
|
853 |
* Retrieve data for an object from the horde_datatree_attributes
|
|
854 |
* table.
|
|
855 |
*
|
|
856 |
* @param integer | array $cid The object id to fetch,
|
|
857 |
* or an array of object ids.
|
|
858 |
*
|
|
859 |
* @param array $keys The attributes keys to fetch.
|
|
860 |
*
|
|
861 |
* @return array A hash of attributes, or a multi-level hash
|
|
862 |
* of object ids => their attributes.
|
|
863 |
*/
|
|
864 |
function getAttributes($cid, $keys = false) |
|
865 |
{
|
|
866 |
if ($keys) { |
|
867 |
$filter = sprintf(' AND attribute_key IN (\'%s\')', implode("', '", $keys)); |
|
868 |
} else { |
|
869 |
$filter = ''; |
|
870 |
}
|
|
871 |
||
872 |
if (is_array($cid)) { |
|
873 |
$query = sprintf('SELECT datatree_id, attribute_name AS name, attribute_key AS "key", attribute_value AS value FROM %s WHERE datatree_id IN (%s)%s', |
|
874 |
$this->_params['table_attributes'], |
|
875 |
implode(', ', $cid), |
|
876 |
$filter); |
|
877 |
||
878 |
Horde::logMessage('SQL Query by DataTree_sql::getAttributes(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
879 |
$rows = $this->_db->getAll($query, DB_FETCHMODE_ASSOC); |
|
880 |
if (is_a($rows, 'PEAR_Error')) { |
|
881 |
return $rows; |
|
882 |
}
|
|
883 |
||
884 |
$data = array(); |
|
885 |
foreach ($rows as $row) { |
|
886 |
if (empty($data[$row['datatree_id']])) { |
|
887 |
$data[$row['datatree_id']] = array(); |
|
888 |
}
|
|
889 |
$data[$row['datatree_id']][] = array('name' => $row['name'], |
|
890 |
'key' => $row['key'], |
|
891 |
'value' => String::convertCharset($row['value'], $this->_params['charset'], NLS::getCharset())); |
|
892 |
}
|
|
893 |
return $data; |
|
894 |
} else { |
|
895 |
$query = sprintf('SELECT attribute_name AS name, attribute_key AS "key", attribute_value AS value FROM %s WHERE datatree_id = %s%s', |
|
896 |
$this->_params['table_attributes'], |
|
897 |
(int)$cid, |
|
898 |
$filter); |
|
899 |
||
900 |
Horde::logMessage('SQL Query by DataTree_sql::getAttributes(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
901 |
$rows = $this->_db->getAll($query, DB_FETCHMODE_ASSOC); |
|
902 |
for ($i = 0; $i < count($rows); $i++) { |
|
903 |
$rows[$i]['value'] = String::convertCharset($rows[$i]['value'], $this->_params['charset'], NLS::getCharset()); |
|
904 |
}
|
|
905 |
return $rows; |
|
906 |
}
|
|
907 |
}
|
|
908 |
||
909 |
/**
|
|
910 |
* Returns the number of objects matching a set of attribute
|
|
911 |
* criteria.
|
|
912 |
*
|
|
913 |
* @see buildAttributeQuery()
|
|
914 |
*
|
|
915 |
* @param array $criteria The array of criteria.
|
|
916 |
* @param string $parent The parent node to start searching from.
|
|
917 |
* @param boolean $allLevels Return all levels, or just the direct
|
|
918 |
* children of $parent? Defaults to all levels.
|
|
919 |
* @param string $restrict Only return attributes with the same
|
|
920 |
* attribute_name or attribute_id.
|
|
921 |
*/
|
|
922 |
function countByAttributes($criteria, $parent = DATATREE_ROOT, $allLevels = true, $restrict = 'name') |
|
923 |
{
|
|
924 |
if (!count($criteria)) { |
|
925 |
return 0; |
|
926 |
}
|
|
927 |
||
928 |
list($query, $values) = $this->buildAttributeQuery($criteria, $parent, $allLevels, $restrict, true); |
|
929 |
||
930 |
Horde::logMessage('SQL Query by DataTree_sql::countByAttributes(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
931 |
$stmt = $this->_db->prepare($query); |
|
932 |
if (is_a($stmt, 'PEAR_Error')) { |
|
933 |
Horde::logMessage($stmt, __FILE__, __LINE__, PEAR_LOG_ERR); |
|
934 |
return $stmt; |
|
935 |
}
|
|
936 |
$result = $this->_db->execute($stmt, $values); |
|
937 |
if (is_a($result, 'PEAR_Error')) { |
|
938 |
Horde::logMessage($result, __FILE__, __LINE__, PEAR_LOG_ERR); |
|
939 |
return $result; |
|
940 |
}
|
|
941 |
$row = &$result->fetchRow(); |
|
942 |
if (is_a($row, 'PEAR_Error')) { |
|
943 |
Horde::logMessage($row, __FILE__, __LINE__, PEAR_LOG_ERR); |
|
944 |
return $row; |
|
945 |
}
|
|
946 |
||
947 |
return $row[0]; |
|
948 |
}
|
|
949 |
||
950 |
/**
|
|
951 |
* Returns a set of object ids based on a set of attribute criteria.
|
|
952 |
*
|
|
953 |
* @see buildAttributeQuery()
|
|
954 |
*
|
|
955 |
* @param array $criteria The array of criteria.
|
|
956 |
* @param string $parent The parent node to start searching from.
|
|
957 |
* @param boolean $allLevels Return all levels, or just the direct
|
|
958 |
* children of $parent? Defaults to all levels.
|
|
959 |
* @param string $restrict Only return attributes with the same
|
|
960 |
* attribute_name or attribute_id.
|
|
961 |
* @param integer $from The object to start to fetching
|
|
962 |
* @param integer $count The number of objects to fetch
|
|
963 |
*/
|
|
964 |
function getByAttributes($criteria, $parent = DATATREE_ROOT, $allLevels = true, $restrict = 'name', $from = 0, $count = 0) |
|
965 |
{
|
|
966 |
if (!count($criteria)) { |
|
967 |
return PEAR::raiseError('no criteria'); |
|
968 |
}
|
|
969 |
||
970 |
list($query, $values) = $this->buildAttributeQuery($criteria, $parent, $allLevels, $restrict); |
|
971 |
||
972 |
if ($count) { |
|
973 |
$query = $this->_db->modifyLimitQuery($query, $from, $count); |
|
974 |
if (is_a($query, 'PEAR_Error')) { |
|
975 |
return $query; |
|
976 |
}
|
|
977 |
}
|
|
978 |
||
979 |
Horde::logMessage('SQL Query by DataTree_sql::getByAttributes(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
980 |
$stmt = $this->_db->prepare($query); |
|
981 |
if (is_a($stmt, 'PEAR_Error')) { |
|
982 |
Horde::logMessage($stmt, __FILE__, __LINE__, PEAR_LOG_ERR); |
|
983 |
return $stmt; |
|
984 |
}
|
|
985 |
$result = $this->_db->execute($stmt, $values); |
|
986 |
if (is_a($result, 'PEAR_Error')) { |
|
987 |
Horde::logMessage($result, __FILE__, __LINE__, PEAR_LOG_ERR); |
|
988 |
return $result; |
|
989 |
}
|
|
990 |
$rows = array(); |
|
991 |
while ($row = &$result->fetchRow()) { |
|
992 |
$rows[$row[0]] = $row[1]; |
|
993 |
}
|
|
994 |
||
995 |
return $rows; |
|
996 |
}
|
|
997 |
||
998 |
/**
|
|
999 |
* Builds an attribute query.
|
|
1000 |
*
|
|
1001 |
* @param array $criteria The array of criteria. Example:
|
|
1002 |
* $criteria['OR'] = array(
|
|
1003 |
* array('AND' => array(
|
|
1004 |
* array('field' => 'name',
|
|
1005 |
* 'op' => '=',
|
|
1006 |
* 'test' => 'foo'),
|
|
1007 |
* array('field' => 'key',
|
|
1008 |
* 'op' => '=',
|
|
1009 |
* 'test' => 'abc'))),
|
|
1010 |
* array('AND' => array(
|
|
1011 |
* array('field' => 'name',
|
|
1012 |
* 'op' => '=',
|
|
1013 |
* 'test' => 'bar'),
|
|
1014 |
* array('field' => 'key',
|
|
1015 |
* 'op' => '=',
|
|
1016 |
* 'test' => 'xyz'))));
|
|
1017 |
* This would fetch all object ids where
|
|
1018 |
* attribute name is "foo" AND key is "abc", OR
|
|
1019 |
* "bar" AND "xyz".
|
|
1020 |
* @param string $parent The parent node to start searching from.
|
|
1021 |
* @param boolean $allLevels Return all levels, or just the direct
|
|
1022 |
* children of $parent? Defaults to all levels.
|
|
1023 |
* @param string $restrict Only return attributes with the same
|
|
1024 |
* attribute_name or attribute_id.
|
|
1025 |
* @param boolean $count Just count the number of elements that'd match.
|
|
1026 |
*
|
|
1027 |
* @return array An SQL query and a list of values suitable for binding
|
|
1028 |
* as an array.
|
|
1029 |
*/
|
|
1030 |
function buildAttributeQuery($criteria, $parent = DATATREE_ROOT, $allLevels = true, $restrict = 'name', $count = false) |
|
1031 |
{
|
|
1032 |
if (!count($criteria)) { |
|
1033 |
return 0; |
|
1034 |
}
|
|
1035 |
||
1036 |
/* Build the query. */
|
|
1037 |
$this->_tableCount = 1; |
|
1038 |
$query = ''; |
|
1039 |
$values = array(); |
|
1040 |
foreach ($criteria as $key => $vals) { |
|
1041 |
if ($key == 'OR' || $key == 'AND') { |
|
1042 |
if (!empty($query)) { |
|
1043 |
$query .= ' ' . $key . ' '; |
|
1044 |
}
|
|
1045 |
$binds = $this->_buildAttributeQuery($key, $vals); |
|
1046 |
$query .= '(' . $binds[0] . ')'; |
|
1047 |
$values += $binds[1]; |
|
1048 |
}
|
|
1049 |
}
|
|
1050 |
||
1051 |
// Add filtering by parent, and for one or all levels.
|
|
1052 |
$levelQuery = ''; |
|
1053 |
$levelValues = array(); |
|
1054 |
if ($parent != DATATREE_ROOT) { |
|
1055 |
$parts = explode(':', $parent); |
|
1056 |
$parents = ''; |
|
1057 |
$pstring = ''; |
|
1058 |
foreach ($parts as $part) { |
|
1059 |
$pstring .= (empty($pstring) ? '' : ':') . $part; |
|
1060 |
$pid = $this->getId($pstring); |
|
1061 |
if (is_a($pid, 'PEAR_Error')) { |
|
1062 |
return $pid; |
|
1063 |
}
|
|
1064 |
$parents .= ':' . $pid; |
|
1065 |
}
|
|
1066 |
||
1067 |
if ($allLevels) { |
|
1068 |
$levelQuery = 'AND (datatree_parents = ? OR datatree_parents LIKE ?)'; |
|
1069 |
$levelValues = array($parents, $parents . ':%'); |
|
1070 |
} else { |
|
1071 |
$levelQuery = 'AND datatree_parents = ?'; |
|
1072 |
$levelValues = array($parents); |
|
1073 |
}
|
|
1074 |
} elseif (!$allLevels) { |
|
1075 |
$levelQuery = "AND datatree_parents = ''"; |
|
1076 |
}
|
|
1077 |
||
1078 |
// Build the FROM/JOIN clauses.
|
|
1079 |
$joins = array(); |
|
1080 |
$pairs = array(); |
|
1081 |
for ($i = 1; $i <= $this->_tableCount; $i++) { |
|
1082 |
$joins[] = 'LEFT JOIN ' . $this->_params['table_attributes'] . ' a' . $i . ' ON a' . $i . '.datatree_id = c.datatree_id'; |
|
1083 |
||
1084 |
if ($i != 1) { |
|
1085 |
if ($restrict == 'name') { |
|
1086 |
$pairs[] = 'AND a1.attribute_name = a' . $i . '.attribute_name'; |
|
1087 |
} elseif ($restrict == 'id') { |
|
1088 |
$pairs[] = 'AND a1.datatree_id = a' . $i . '.datatree_id'; |
|
1089 |
}
|
|
1090 |
}
|
|
1091 |
}
|
|
1092 |
$joins = implode(' ', $joins); |
|
1093 |
$pairs = implode(' ', $pairs); |
|
1094 |
||
1095 |
return array(sprintf('SELECT %s FROM %s c %s WHERE c.group_uid = ? AND %s %s %s %s', |
|
1096 |
$count ? 'COUNT(DISTINCT c.datatree_id)' : 'c.datatree_id, c.datatree_name', |
|
1097 |
$this->_params['table'], |
|
1098 |
$joins, |
|
1099 |
$query, |
|
1100 |
$levelQuery, |
|
1101 |
$pairs, |
|
1102 |
$count ? '' : 'GROUP BY c.datatree_id, c.datatree_name, c.datatree_order ORDER BY c.datatree_order, c.datatree_name, c.datatree_id'), |
|
1103 |
array_merge(array($this->_params['group']), $values, $levelValues)); |
|
1104 |
}
|
|
1105 |
||
1106 |
/**
|
|
1107 |
* Build a piece of an attribute query.
|
|
1108 |
*
|
|
1109 |
* @param string $glue The glue to join the criteria (OR/AND).
|
|
1110 |
* @param array $criteria The array of criteria.
|
|
1111 |
* @param boolean $join Should we join on a clean
|
|
1112 |
* horde_datatree_attributes table? Defaults to
|
|
1113 |
* false.
|
|
1114 |
*
|
|
1115 |
* @return array An SQL fragment and a list of values suitable for binding
|
|
1116 |
* as an array.
|
|
1117 |
*/
|
|
1118 |
function _buildAttributeQuery($glue, $criteria, $join = false) |
|
1119 |
{
|
|
1120 |
require_once 'Horde/SQL.php'; |
|
1121 |
||
1122 |
// Initialize the clause that we're building.
|
|
1123 |
$clause = ''; |
|
1124 |
$values = array(); |
|
1125 |
||
1126 |
// Get the table alias to use for this set of criteria.
|
|
1127 |
$alias = $this->_getAlias($join); |
|
1128 |
||
1129 |
foreach ($criteria as $key => $vals) { |
|
1130 |
if (!empty($vals['OR']) || !empty($vals['AND'])) { |
|
1131 |
if (!empty($clause)) { |
|
1132 |
$clause .= ' ' . $glue . ' '; |
|
1133 |
}
|
|
1134 |
$binds = $this->_buildAttributeQuery($glue, $vals); |
|
1135 |
$clause .= '(' . $binds[0] . ')'; |
|
1136 |
$values = array_merge($values, $binds[1]); |
|
1137 |
} elseif (!empty($vals['JOIN'])) { |
|
1138 |
if (!empty($clause)) { |
|
1139 |
$clause .= ' ' . $glue . ' '; |
|
1140 |
}
|
|
1141 |
$binds = $this->_buildAttributeQuery($glue, $vals['JOIN'], true); |
|
1142 |
$clause .= $binds[0]; |
|
1143 |
$values = array_merge($values, $binds[1]); |
|
1144 |
} else { |
|
1145 |
if (isset($vals['field'])) { |
|
1146 |
if (!empty($clause)) { |
|
1147 |
$clause .= ' ' . $glue . ' '; |
|
1148 |
}
|
|
1149 |
$binds = Horde_SQL::buildClause($this->_db, $alias . '.attribute_' . $vals['field'], $vals['op'], $vals['test'], true); |
|
1150 |
$clause .= $binds[0]; |
|
1151 |
$values = array_merge($values, $binds[1]); |
|
1152 |
} else { |
|
1153 |
if (!empty($clause)) { |
|
1154 |
$clause .= ' ' . $glue . ' '; |
|
1155 |
}
|
|
1156 |
$binds = $this->_buildAttributeQuery($key, $vals); |
|
1157 |
$clause .= $binds[0]; |
|
1158 |
$values = array_merge($values, $binds[1]); |
|
1159 |
}
|
|
1160 |
}
|
|
1161 |
}
|
|
1162 |
||
1163 |
return array($clause, $values); |
|
1164 |
}
|
|
1165 |
||
1166 |
/**
|
|
1167 |
* Get an alias to horde_datatree_attributes, incrementing it if
|
|
1168 |
* necessary.
|
|
1169 |
*
|
|
1170 |
* @param boolean $increment Increment the alias count? Defaults to no.
|
|
1171 |
*/
|
|
1172 |
function _getAlias($increment = false) |
|
1173 |
{
|
|
1174 |
static $seen = array(); |
|
1175 |
||
1176 |
if ($increment && !empty($seen[$this->_tableCount])) { |
|
1177 |
$this->_tableCount++; |
|
1178 |
}
|
|
1179 |
||
1180 |
$seen[$this->_tableCount] = true; |
|
1181 |
return 'a' . $this->_tableCount; |
|
1182 |
}
|
|
1183 |
||
1184 |
/**
|
|
1185 |
* Update the data in an object. Does not change the object's
|
|
1186 |
* parent or name, just serialized data or attributes.
|
|
1187 |
*
|
|
1188 |
* @param DataTree $object A DataTree object.
|
|
1189 |
*/
|
|
1190 |
function updateData($object) |
|
1191 |
{
|
|
1192 |
if (!is_a($object, 'DataTreeObject')) { |
|
1193 |
/* Nothing to do for non objects. */
|
|
1194 |
return true; |
|
1195 |
}
|
|
1196 |
||
1197 |
/* Get the object id. */
|
|
1198 |
$id = $this->getId($object->getName()); |
|
1199 |
if (is_a($id, 'PEAR_Error')) { |
|
1200 |
return $id; |
|
1201 |
}
|
|
1202 |
||
1203 |
/* See if we can break the object out to datatree_attributes table. */
|
|
1204 |
if (method_exists($object, '_toAttributes')) { |
|
1205 |
/* If we can, clear out the datatree_data field to make
|
|
1206 |
* sure it doesn't get picked up by
|
|
1207 |
* getData(). Intentionally don't check for errors here in
|
|
1208 |
* case datatree_data goes away in the future. */
|
|
1209 |
$query = sprintf('UPDATE %s SET datatree_data = NULL WHERE datatree_id = %s', |
|
1210 |
$this->_params['table'], |
|
1211 |
(int)$id); |
|
1212 |
||
1213 |
Horde::logMessage('SQL Query by DataTree_sql::updateData(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
1214 |
$this->_db->query($query); |
|
1215 |
||
1216 |
/* Start a transaction. */
|
|
1217 |
$this->_db->autoCommit(false); |
|
1218 |
||
1219 |
/* Delete old attributes. */
|
|
1220 |
$query = sprintf('DELETE FROM %s WHERE datatree_id = %s', |
|
1221 |
$this->_params['table_attributes'], |
|
1222 |
(int)$id); |
|
1223 |
||
1224 |
Horde::logMessage('SQL Query by DataTree_sql::updateData(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
1225 |
$result = $this->_db->query($query); |
|
1226 |
if (is_a($result, 'PEAR_Error')) { |
|
1227 |
$this->_db->rollback(); |
|
1228 |
$this->_db->autoCommit(true); |
|
1229 |
return $result; |
|
1230 |
}
|
|
1231 |
||
1232 |
/* Get the new attribute set, and insert each into the DB. If
|
|
1233 |
* anything fails in here, rollback the transaction, return the
|
|
1234 |
* relevant error, and bail out. */
|
|
1235 |
$attributes = $object->_toAttributes(); |
|
1236 |
foreach ($attributes as $attr) { |
|
1237 |
$query = sprintf('INSERT INTO %s (datatree_id, attribute_name, attribute_key, attribute_value) VALUES (%s, %s, %s, ?)', |
|
1238 |
$this->_params['table_attributes'], |
|
1239 |
(int)$id, |
|
1240 |
$this->_db->quote($attr['name']), |
|
1241 |
$this->_db->quote($attr['key'])); |
|
1242 |
||
1243 |
Horde::logMessage('SQL Query by DataTree_sql::updateData(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
1244 |
||
1245 |
$statement = $this->_db->prepare($query); |
|
1246 |
$result = $this->_db->execute($statement, array(String::convertCharset($attr['value'], NLS::getCharset(), $this->_params['charset']))); |
|
1247 |
if (is_a($result, 'PEAR_Error')) { |
|
1248 |
$this->_db->rollback(); |
|
1249 |
$this->_db->autoCommit(true); |
|
1250 |
return $result; |
|
1251 |
}
|
|
1252 |
}
|
|
1253 |
||
1254 |
/* Commit the transaction, and turn autocommit back on. */
|
|
1255 |
$result = $this->_db->commit(); |
|
1256 |
$this->_db->autoCommit(true); |
|
1257 |
||
1258 |
return is_a($result, 'PEAR_Error') ? $result : true; |
|
1259 |
} else { |
|
1260 |
/* Write to the datatree_data field. */
|
|
1261 |
require_once 'Horde/Serialize.php'; |
|
1262 |
$ser = SERIALIZE_UTF7_BASIC; |
|
1263 |
$data = Horde_Serialize::serialize($object->getData(), $ser, NLS::getCharset()); |
|
1264 |
||
1265 |
$query = sprintf('UPDATE %s SET datatree_data = %s, datatree_serialized = %s' . |
|
1266 |
' WHERE datatree_id = %s', |
|
1267 |
$this->_params['table'], |
|
1268 |
$this->_db->quote($data), |
|
1269 |
(int)$ser, |
|
1270 |
(int)$id); |
|
1271 |
||
1272 |
Horde::logMessage('SQL Query by DataTree_sql::updateData(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG); |
|
1273 |
$result = $this->_db->query($query); |
|
1274 |
||
1275 |
return is_a($result, 'PEAR_Error') ? $result : true; |
|
1276 |
}
|
|
1277 |
}
|
|
1278 |
||
1279 |
/**
|
|
1280 |
* Attempts to open a connection to the SQL server.
|
|
1281 |
*
|
|
1282 |
* @return boolean True.
|
|
1283 |
*/
|
|
1284 |
function _connect() |
|
1285 |
{
|
|
1286 |
Horde::assertDriverConfig($this->_params, 'storage', |
|
1287 |
array('phptype', 'hostspec', 'username', 'database', 'charset'), |
|
1288 |
'DataTree SQL'); |
|
1289 |
||
1290 |
if (!isset($this->_params['password'])) { |
|
1291 |
$this->_params['password'] = ''; |
|
1292 |
}
|
|
1293 |
||
1294 |
if (!isset($this->_params['table'])) { |
|
1295 |
$this->_params['table'] = 'horde_datatree'; |
|
1296 |
}
|
|
1297 |
||
1298 |
if (!isset($this->_params['table_attributes'])) { |
|
1299 |
$this->_params['table_attributes'] = 'horde_datatree_attributes'; |
|
1300 |
}
|
|
1301 |
||
1302 |
/* Connect to the SQL server using the supplied parameters. */
|
|
1303 |
require_once 'DB.php'; |
|
1304 |
$this->_db = &DB::connect($this->_params, |
|
1305 |
array('persistent' => !empty($this->_params['persistent']))); |
|
1306 |
if (is_a($this->_db, 'PEAR_Error')) { |
|
1307 |
Horde::fatal($this->_db, __FILE__, __LINE__); |
|
1308 |
}
|
|
1309 |
||
1310 |
$this->_db->setOption('portability', DB_PORTABILITY_LOWERCASE | DB_PORTABILITY_ERRORS); |
|
1311 |
||
1312 |
return true; |
|
1313 |
}
|
|
1314 |
||
1315 |
}
|