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