3
* The DataTree_sql:: class provides an SQL implementation of the Horde
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.
16
* 'table' -- The name of the data table in 'database'. Defaults to
19
* The table structure for the DataTree system is in
20
* horde/scripts/db/datatree.sql.
22
* $Horde: framework/DataTree/DataTree/sql.php,v 1.156.2.6 2005/03/23 10:39:06 jan Exp $
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>
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.
32
* @author Chuck Hagenbuch <chuck@horde.org>
33
* @author Jan Schneider <jan@horde.org>
34
* @author Stephane Huther <shuther@bigfoot.com>
36
* @package Horde_DataTree
38
class DataTree_sql extends DataTree {
41
* Handle for the current database connection.
48
* The number of copies of the horde_datatree_attributes table
49
* that we need to join on in the current query.
51
* @var integer $_tableCount
56
* Constructs a new SQL DataTree object.
58
* @param array $params A hash containing connection parameters.
60
function DataTree_sql($params)
62
parent::DataTree($params);
67
* Returns a list of all groups (root nodes) of the data tree.
69
* @return array The the group IDs
73
$query = sprintf('SELECT DISTINCT group_uid FROM %s',
74
$this->_params['table']);
76
Horde::logMessage('SQL Query by DataTree_sql::getGroups(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
78
return $this->_db->getCol($query);
82
* Load (a subset of) the datatree into the $_data array.
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?
93
* @return mixed True on success or a PEAR_Error on failure.
95
function _load($root = DATATREE_ROOT, $loadTree = false, $reload = false)
97
/* Do NOT use DataTree::exists() here; that would cause an
100
(in_array($root, $this->_nameMap) ||
101
(count($this->_data) && $root == DATATREE_ROOT))) {
105
$query = $this->_buildLoadQuery($root, $loadTree, false);
106
if (is_a($query, 'PEAR_Error')) {
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')) {
118
return $this->set($data, $this->_params['charset']);
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
128
* @param string $root Which portion of the tree to load.
129
* Defaults to all of it.
131
* @return integer Number of objects
133
function _count($root = DATATREE_ROOT)
135
$query = $this->_buildLoadQuery($root, true, true);
136
if (is_a($query, 'PEAR_Error')) {
142
Horde::logMessage('SQL Query by DataTree_sql::_count(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
143
return $this->_db->getOne($query);
147
* Load (a subset of) the datatree into the $_data array.
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?
158
* @return mixed True on success or a PEAR_Error on failure.
160
function _buildLoadQuery($root = DATATREE_ROOT, $loadTree = false, $count = false)
162
if (!empty($root) && $root != DATATREE_ROOT) {
163
if (strpos($root, ':') !== false) {
164
$parts = explode(':', $root);
165
$root = array_pop($parts);
167
$root = (string)$root;
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']));
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')) {
180
if (count($root) == 0) {
186
foreach ($root as $object_id => $object_parents) {
187
$pstring = $object_parents . ':' . $object_id . '%';
189
if (!empty($object_parents)) {
190
$ids = substr($object_parents, 1);
191
$pquery = ' OR datatree_id IN (' . str_replace(':', ', ', $ids) . ')';
194
$pquery .= ' OR datatree_parents = ' . $this->_db->quote(substr($pstring, 0, -2));
200
$where .= sprintf('datatree_parents LIKE %s OR datatree_id = %s%s',
201
$this->_db->quote($pstring),
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'],
212
$this->_db->quote($this->_params['group']));
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']));
224
* Load a set of objects identified by their unique IDs, and their
225
* parents, into the $_data array.
229
* @param mixed $cids The unique ID of the object to load, or an array of
232
* @return mixed True on success or a PEAR_Error on failure.
234
function _loadById($cids)
236
/* Make sure we have an array. */
237
if (!is_array($cids)) {
238
$cids = array($cids);
241
/* Bail out now if there's nothing to load. */
246
/* Don't load any that are already loaded. Also, make sure
247
* that everything in the $ids array that we are building is
250
foreach ($cids as $cid) {
251
/* Do NOT use DataTree::exists() here; that would cause an
253
if (!isset($this->_data[$cid])) {
258
/* If there are none left to load, return. */
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'],
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')) {
277
foreach ($parents as $cid => $parent) {
278
$ids[(int)$cid] = (int)$cid;
280
$pids = explode(':', substr($parent, 1));
281
foreach ($pids as $pid) {
283
if (!isset($this->_data[$pid])) {
289
/* If $ids is empty, we have nothing to load. */
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'],
299
$this->_db->quote($this->_params['group']));
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')) {
307
return $this->set($data, $this->_params['charset']);
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.
319
function add($object, $id_as_name = false)
322
if (is_a($object, 'DataTreeObject')) {
323
$fullname = $object->getName();
324
$order = $object->order;
326
/* We handle data differently if we can map it to the
327
* horde_datatree_attributes table. */
328
if (method_exists($object, '_toAttributes')) {
332
/* Set a flag for later so that we know to insert the
336
require_once 'Horde/Serialize.php';
337
$ser = SERIALIZE_UTF7_BASIC;
338
$data = Horde_Serialize::serialize($object->getData(), $ser, NLS::getCharset());
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);
354
if (strpos($fullname, ':') !== false) {
355
$parts = explode(':', $fullname);
359
/* Requested use of ID as name, so discard current name. */
361
/* Set name to ID. */
363
/* Modify fullname to reflect new name. */
364
$fullname = implode(':', $parts) . ':' . $id;
365
if (is_a($object, 'DataTreeObject')) {
366
$object->setName($fullname);
371
$name = array_pop($parts);
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')) {
383
$parents .= ':' . $pid;
387
/* Requested use of ID as name, set fullname and name to ID. */
390
if (is_a($object, 'DataTreeObject')) {
391
$object->setName($fullname);
399
$pid = DATATREE_ROOT;
402
if (parent::exists($fullname)) {
403
return PEAR::raiseError(_("Already exists"));
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'],
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()),
416
$this->_db->quote($parents));
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);
425
$reorder = $this->reorder($parents, $order, $id);
426
if (is_a($reorder, 'PEAR_Error')) {
427
Horde::logMessage($reorder, __FILE__, __LINE__, PEAR_LOG_ERR);
431
$result = parent::_add($fullname, $id, $pid, $order);
432
if (is_a($result, 'PEAR_Error')) {
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')) {
449
* Changes the order of the children of an object.
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
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.
460
function reorder($parent, $order = null, $cid = null)
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.
469
if (!is_array($order) && !is_null($order)) {
470
/* Single update (add/del). */
472
/* No object id given so shuffle down. */
475
/* We have an object id so shuffle up. */
478
/* Leaving the newly inserted object alone. */
479
$pquery = sprintf(' AND datatree_id != %s', (int)$cid);
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'],
484
$this->_db->quote($this->_params['group']),
485
$this->_db->quote($parent),
486
is_null($order) ? 'NULL' : (int)$order) . $pquery;
488
Horde::logMessage('SQL Query by DataTree_sql::reorder(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
489
$result = $this->_db->query($query);
491
} elseif (is_array($order)) {
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));
498
Horde::logMessage('SQL Query by DataTree_sql::reorder(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
500
$result = $this->_db->getOne($query);
501
if (is_a($result, 'PEAR_Error')) {
503
} elseif (count($order) != $result) {
504
return PEAR::raiseError(_("Cannot reorder, number of entries supplied for reorder does not match number stored."));
508
foreach ($order as $o_cid) {
509
$query = sprintf('UPDATE %s SET datatree_order = %s WHERE datatree_id = %s',
510
$this->_params['table'],
512
is_null($o_cid) ? 'NULL' : (int)$o_cid);
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')) {
523
$pid = $this->getId($parent);
525
/* Re-order our cache. */
526
return $this->_reorder($pid, $order);
531
* Explicitly set the order for a datatree object.
533
* @param integer $id The datatree object id to change.
534
* @param integer $order The new order.
536
function setOrder($id, $order)
538
$query = sprintf('UPDATE %s SET datatree_order = %s WHERE datatree_id = %s',
539
$this->_params['table'],
540
is_null($order) ? 'NULL' : (int)$order,
543
Horde::logMessage('SQL Query by DataTree_sql::setOrder(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
544
return $this->_db->query($query);
550
* @param mixed $object The object to remove.
551
* @param boolean $force Force removal of every child object?
553
function remove($object, $force = false)
555
$id = $this->getId($object);
556
$order = $this->getOrder($object);
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 . ''));
565
Horde::logMessage('SQL Query by DataTree_sql::remove(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
566
$children = $this->_db->getAll($query, DB_FETCHMODE_ASSOC);
568
if (count($children)) {
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')) {
578
return PEAR::raiseError(sprintf(_("Cannot remove, %d children exist."), count($children)));
582
/* Remove attributes for this object. */
583
$query = sprintf('DELETE FROM %s WHERE datatree_id = %s',
584
$this->_params['table_attributes'],
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')) {
593
$query = sprintf('DELETE FROM %s WHERE datatree_id = %s',
594
$this->_params['table'],
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')) {
603
$parents = $this->getParentIdString($object);
604
if (is_a($parents, 'PEAR_Error')) {
608
$reorder = $this->reorder($parents, $order);
609
if (is_a($reorder, 'PEAR_Error')) {
613
return is_a(parent::remove($object), 'PEAR_Error') ? $id : true;
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.
624
* @param array $ids The objects to remove.
626
function removeByIds($ids)
628
/* Sanitize input. */
629
if (!is_array($ids)) {
630
$ids = array((int)$ids);
632
foreach ($ids as $key => $id) {
633
$ids[$key] = (int)$id;
637
/* Remove attributes for $ids. */
638
$query = sprintf('DELETE FROM %s WHERE datatree_id IN (%s)',
639
$this->_params['table_attributes'],
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')) {
648
$query = sprintf('DELETE FROM %s WHERE datatree_id IN %s',
649
$this->_params['table'],
652
Horde::logMessage('SQL Query by DataTree_sql::removeByIds(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
653
return $this->_db->query($query);
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.
664
* @param array $names The objects to remove.
666
function removeByNames($names)
668
/* Sanitize input. */
669
if (!is_array($names)) {
670
$names = array($this->_db->quote($names));
672
foreach ($names as $key => $name) {
673
$names[$key] = $this->_db->quote($name);
677
/* Remove attributes for $names. */
678
$query = sprintf('DELETE FROM %s WHERE datatree_name IN (%s)',
679
$this->_params['table_attributes'],
680
implode(',', $names));
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')) {
688
$query = sprintf('DELETE FROM %s WHERE datatree_name IN %s',
689
$this->_params['table'],
690
implode(',', $names));
692
Horde::logMessage('SQL Query by DataTree_sql::removeByNames(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
693
return $this->_db->query($query);
697
* Move an object to a new parent.
699
* @param mixed $object The object to move.
700
* @param string $newparent The new parent object. Defaults to the root.
702
function move($object, $newparent = null)
704
$old_parent_path = $this->getParentIdString($object);
705
$result = parent::move($object, $newparent);
706
if (is_a($result, 'PEAR_Error')) {
709
$id = $this->getId($object);
710
$new_parent_path = $this->getParentIdString($object);
712
/* Fetch the object being moved and all of its children, since
713
* we also need to update their parent paths to avoid creating
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 . ':%'),
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')) {
728
/* Update each object, replacing the old parent path with the
730
while ($row = $rowset->fetchRow(DB_FETCHMODE_ASSOC)) {
731
if (is_a($row, 'PEAR_Error')) {
736
if ($row['datatree_id'] == $id) {
737
$oquery = ', datatree_order = 0 ';
740
/* Do str_replace() only if this is not a first level
742
if (!empty($row['datatree_parents'])) {
743
$ppath = str_replace($old_parent_path, $new_parent_path, $row['datatree_parents']);
745
$ppath = $new_parent_path;
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']);
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')) {
759
$order = $this->getOrder($object);
761
/* Shuffle down the old order positions. */
762
$reorder = $this->reorder($old_parent_path, $order);
764
/* Shuffle up the new order positions. */
765
$reorder = $this->reorder($new_parent_path, 0, $id);
771
* Change an object's name.
773
* @param mixed $old_object The old object.
774
* @param string $new_object_name The new object name.
776
function rename($old_object, $new_object_name)
778
/* Do the cache renaming first */
779
$result = parent::rename($old_object, $new_object_name);
780
if (is_a($result, 'PEAR_Error')) {
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'])),
792
Horde::logMessage('SQL Query by DataTree_sql::rename(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
793
$result = $this->_db->query($query);
795
return is_a($result, 'PEAR_Error') ? $result : true;
799
* Retrieve data for an object from the datatree_data field.
801
* @param integer $cid The object id to fetch, or an array of object ids.
803
function getData($cid)
805
require_once 'Horde/Serialize.php';
807
if (is_array($cid)) {
812
$query = sprintf('SELECT datatree_id, datatree_data, datatree_serialized FROM %s WHERE datatree_id IN (%s)',
813
$this->_params['table'],
814
implode(', ', $cid));
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);
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));
831
$data[$id] = (is_null($data[$id]) || !is_array($data[$id])) ? array() : $data[$id];
836
$query = sprintf('SELECT datatree_data, datatree_serialized FROM %s WHERE datatree_id = %s',
837
$this->_params['table'],
840
Horde::logMessage('SQL Query by DataTree_sql::getData(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
841
$row = $this->_db->getRow($query, DB_FETCHMODE_ASSOC);
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));
848
return (is_null($data) || !is_array($data)) ? array() : $data;
853
* Retrieve data for an object from the horde_datatree_attributes
856
* @param integer | array $cid The object id to fetch,
857
* or an array of object ids.
859
* @param array $keys The attributes keys to fetch.
861
* @return array A hash of attributes, or a multi-level hash
862
* of object ids => their attributes.
864
function getAttributes($cid, $keys = false)
867
$filter = sprintf(' AND attribute_key IN (\'%s\')', implode("', '", $keys));
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'],
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')) {
885
foreach ($rows as $row) {
886
if (empty($data[$row['datatree_id']])) {
887
$data[$row['datatree_id']] = array();
889
$data[$row['datatree_id']][] = array('name' => $row['name'],
890
'key' => $row['key'],
891
'value' => String::convertCharset($row['value'], $this->_params['charset'], NLS::getCharset()));
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'],
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());
910
* Returns the number of objects matching a set of attribute
913
* @see buildAttributeQuery()
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.
922
function countByAttributes($criteria, $parent = DATATREE_ROOT, $allLevels = true, $restrict = 'name')
924
if (!count($criteria)) {
928
list($query, $values) = $this->buildAttributeQuery($criteria, $parent, $allLevels, $restrict, true);
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);
936
$result = $this->_db->execute($stmt, $values);
937
if (is_a($result, 'PEAR_Error')) {
938
Horde::logMessage($result, __FILE__, __LINE__, PEAR_LOG_ERR);
941
$row = &$result->fetchRow();
942
if (is_a($row, 'PEAR_Error')) {
943
Horde::logMessage($row, __FILE__, __LINE__, PEAR_LOG_ERR);
951
* Returns a set of object ids based on a set of attribute criteria.
953
* @see buildAttributeQuery()
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
964
function getByAttributes($criteria, $parent = DATATREE_ROOT, $allLevels = true, $restrict = 'name', $from = 0, $count = 0)
966
if (!count($criteria)) {
967
return PEAR::raiseError('no criteria');
970
list($query, $values) = $this->buildAttributeQuery($criteria, $parent, $allLevels, $restrict);
973
$query = $this->_db->modifyLimitQuery($query, $from, $count);
974
if (is_a($query, 'PEAR_Error')) {
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);
985
$result = $this->_db->execute($stmt, $values);
986
if (is_a($result, 'PEAR_Error')) {
987
Horde::logMessage($result, __FILE__, __LINE__, PEAR_LOG_ERR);
991
while ($row = &$result->fetchRow()) {
992
$rows[$row[0]] = $row[1];
999
* Builds an attribute query.
1001
* @param array $criteria The array of criteria. Example:
1002
* $criteria['OR'] = array(
1003
* array('AND' => array(
1004
* array('field' => 'name',
1007
* array('field' => 'key',
1009
* 'test' => 'abc'))),
1010
* array('AND' => array(
1011
* array('field' => 'name',
1014
* array('field' => 'key',
1016
* 'test' => 'xyz'))));
1017
* This would fetch all object ids where
1018
* attribute name is "foo" AND key is "abc", OR
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.
1027
* @return array An SQL query and a list of values suitable for binding
1030
function buildAttributeQuery($criteria, $parent = DATATREE_ROOT, $allLevels = true, $restrict = 'name', $count = false)
1032
if (!count($criteria)) {
1036
/* Build the query. */
1037
$this->_tableCount = 1;
1040
foreach ($criteria as $key => $vals) {
1041
if ($key == 'OR' || $key == 'AND') {
1042
if (!empty($query)) {
1043
$query .= ' ' . $key . ' ';
1045
$binds = $this->_buildAttributeQuery($key, $vals);
1046
$query .= '(' . $binds[0] . ')';
1047
$values += $binds[1];
1051
// Add filtering by parent, and for one or all levels.
1053
$levelValues = array();
1054
if ($parent != DATATREE_ROOT) {
1055
$parts = explode(':', $parent);
1058
foreach ($parts as $part) {
1059
$pstring .= (empty($pstring) ? '' : ':') . $part;
1060
$pid = $this->getId($pstring);
1061
if (is_a($pid, 'PEAR_Error')) {
1064
$parents .= ':' . $pid;
1068
$levelQuery = 'AND (datatree_parents = ? OR datatree_parents LIKE ?)';
1069
$levelValues = array($parents, $parents . ':%');
1071
$levelQuery = 'AND datatree_parents = ?';
1072
$levelValues = array($parents);
1074
} elseif (!$allLevels) {
1075
$levelQuery = "AND datatree_parents = ''";
1078
// Build the FROM/JOIN clauses.
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';
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';
1092
$joins = implode(' ', $joins);
1093
$pairs = implode(' ', $pairs);
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'],
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));
1107
* Build a piece of an attribute query.
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
1115
* @return array An SQL fragment and a list of values suitable for binding
1118
function _buildAttributeQuery($glue, $criteria, $join = false)
1120
require_once 'Horde/SQL.php';
1122
// Initialize the clause that we're building.
1126
// Get the table alias to use for this set of criteria.
1127
$alias = $this->_getAlias($join);
1129
foreach ($criteria as $key => $vals) {
1130
if (!empty($vals['OR']) || !empty($vals['AND'])) {
1131
if (!empty($clause)) {
1132
$clause .= ' ' . $glue . ' ';
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 . ' ';
1141
$binds = $this->_buildAttributeQuery($glue, $vals['JOIN'], true);
1142
$clause .= $binds[0];
1143
$values = array_merge($values, $binds[1]);
1145
if (isset($vals['field'])) {
1146
if (!empty($clause)) {
1147
$clause .= ' ' . $glue . ' ';
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]);
1153
if (!empty($clause)) {
1154
$clause .= ' ' . $glue . ' ';
1156
$binds = $this->_buildAttributeQuery($key, $vals);
1157
$clause .= $binds[0];
1158
$values = array_merge($values, $binds[1]);
1163
return array($clause, $values);
1167
* Get an alias to horde_datatree_attributes, incrementing it if
1170
* @param boolean $increment Increment the alias count? Defaults to no.
1172
function _getAlias($increment = false)
1174
static $seen = array();
1176
if ($increment && !empty($seen[$this->_tableCount])) {
1177
$this->_tableCount++;
1180
$seen[$this->_tableCount] = true;
1181
return 'a' . $this->_tableCount;
1185
* Update the data in an object. Does not change the object's
1186
* parent or name, just serialized data or attributes.
1188
* @param DataTree $object A DataTree object.
1190
function updateData($object)
1192
if (!is_a($object, 'DataTreeObject')) {
1193
/* Nothing to do for non objects. */
1197
/* Get the object id. */
1198
$id = $this->getId($object->getName());
1199
if (is_a($id, 'PEAR_Error')) {
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'],
1213
Horde::logMessage('SQL Query by DataTree_sql::updateData(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
1214
$this->_db->query($query);
1216
/* Start a transaction. */
1217
$this->_db->autoCommit(false);
1219
/* Delete old attributes. */
1220
$query = sprintf('DELETE FROM %s WHERE datatree_id = %s',
1221
$this->_params['table_attributes'],
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);
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'],
1240
$this->_db->quote($attr['name']),
1241
$this->_db->quote($attr['key']));
1243
Horde::logMessage('SQL Query by DataTree_sql::updateData(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
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);
1254
/* Commit the transaction, and turn autocommit back on. */
1255
$result = $this->_db->commit();
1256
$this->_db->autoCommit(true);
1258
return is_a($result, 'PEAR_Error') ? $result : true;
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());
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),
1272
Horde::logMessage('SQL Query by DataTree_sql::updateData(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
1273
$result = $this->_db->query($query);
1275
return is_a($result, 'PEAR_Error') ? $result : true;
1280
* Attempts to open a connection to the SQL server.
1282
* @return boolean True.
1286
Horde::assertDriverConfig($this->_params, 'storage',
1287
array('phptype', 'hostspec', 'username', 'database', 'charset'),
1290
if (!isset($this->_params['password'])) {
1291
$this->_params['password'] = '';
1294
if (!isset($this->_params['table'])) {
1295
$this->_params['table'] = 'horde_datatree';
1298
if (!isset($this->_params['table_attributes'])) {
1299
$this->_params['table_attributes'] = 'horde_datatree_attributes';
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__);
1310
$this->_db->setOption('portability', DB_PORTABILITY_LOWERCASE | DB_PORTABILITY_ERRORS);