1: <?php
2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44:
45: class Horde_DataTree_Sql extends Horde_DataTree {
46:
47: 48: 49: 50: 51:
52: var $_db;
53:
54: 55: 56: 57: 58: 59:
60: var $_write_db;
61:
62: 63: 64: 65: 66: 67:
68: var $_tableCount = 1;
69:
70: 71: 72: 73: 74:
75: function getGroups()
76: {
77: $query = 'SELECT DISTINCT group_uid FROM ' . $this->_params['table'];
78:
79: Horde::logMessage('SQL Query by Horde_DataTree_Sql::getGroups(): ' . $query, 'DEBUG');
80:
81: return $this->_db->getCol($query);
82: }
83:
84: 85: 86: 87: 88: 89: 90: 91: 92: 93: 94: 95: 96: 97: 98: 99: 100: 101: 102:
103: function _load($root = DATATREE_ROOT, $loadTree = false, $reload = false,
104: $sortby_name = null, $sortby_key = null, $direction = 0)
105: {
106: 107:
108: if (!$reload &&
109: (in_array($root, $this->_nameMap) ||
110: (count($this->_data) && $root == DATATREE_ROOT)) ||
111: (!is_null($this->_sortHash) &&
112: isset($this->_data[$root]['sorter'][$this->_sortHash]))) {
113: return true;
114: }
115:
116: $query = $this->_buildLoadQuery($root,
117: $loadTree,
118: DATATREE_BUILD_SELECT,
119: $sortby_name,
120: $sortby_key,
121: $direction);
122: if (empty($query)) {
123: return true;
124: }
125:
126: Horde::logMessage('SQL Query by Horde_DataTree_Sql::_load(): ' . $query, 'DEBUG');
127: $data = $this->_db->getAll($query);
128: if (is_a($data, 'PEAR_Error')) {
129: return $data;
130: }
131: return $this->set($data, $this->_params['charset']);
132: }
133:
134: 135: 136: 137: 138: 139: 140: 141: 142: 143: 144:
145: function _count($root = DATATREE_ROOT)
146: {
147: $query = $this->_buildLoadQuery($root, true, DATATREE_BUILD_COUNT);
148: if (empty($query)) {
149: return 0;
150: }
151: Horde::logMessage('SQL Query by Horde_DataTree_Sql::_count(): ' . $query, 'DEBUG');
152: return (int)$this->_db->getOne($query);
153: }
154:
155: 156: 157: 158: 159: 160: 161: 162: 163: 164: 165: 166: 167: 168: 169: 170: 171: 172: 173:
174: function _buildLoadQuery($root = DATATREE_ROOT, $loadTree = false,
175: $operation = DATATREE_BUILD_SELECT,
176: $sortby_name = null, $sortby_key = null,
177: $direction = 0)
178: {
179: $sorted = false;
180: $where = sprintf('c.group_uid = %s ', $this->_db->quote($this->_params['group']));
181:
182: if (!empty($root) && $root != DATATREE_ROOT) {
183: $parent_where = $this->_buildParentIds($root, $loadTree, 'c.');
184: if (empty($parent_where)) {
185: return '';
186: } elseif (!is_a($parent_where, 'PEAR_Error')) {
187: $where = sprintf('%s AND (%s)', $where, $parent_where);
188: }
189: }
190: if (!is_null($sortby_name)) {
191: $where = sprintf('%s AND a.attribute_name = %s ', $where, $this->_db->quote($sortby_name));
192: $sorted = true;
193: }
194: if (!is_null($sortby_key)) {
195: $where = sprintf('%s AND a.attribute_key = %s ', $where, $this->_db->quote($sortby_key));
196: $sorted = true;
197: }
198:
199: switch ($operation) {
200: case DATATREE_BUILD_COUNT:
201: $what = 'COUNT(*)';
202: break;
203:
204: default:
205: $what = 'c.datatree_id, c.datatree_name, c.datatree_parents, c.datatree_order';
206: break;
207: }
208:
209: if ($sorted) {
210: $query = sprintf('SELECT %s FROM %s c LEFT JOIN %s a ON (c.datatree_id = a.datatree_id OR c.datatree_name=%s) '.
211: 'WHERE %s GROUP BY c.datatree_id, c.datatree_name, c.datatree_parents, c.datatree_order ORDER BY a.attribute_value %s',
212: $what,
213: $this->_params['table'],
214: $this->_params['table_attributes'],
215: $this->_db->quote($root),
216: $where,
217: ($direction == 1) ? 'DESC' : 'ASC');
218: } else {
219: $query = sprintf('SELECT %s FROM %s c WHERE %s',
220: $what,
221: $this->_params['table'],
222: $where);
223: }
224:
225: return $query;
226: }
227:
228: 229: 230: 231: 232: 233: 234: 235: 236: 237: 238: 239: 240:
241: function _buildParentIds($root, $loadTree = false, $join_name = '')
242: {
243: if (strpos($root, ':') !== false) {
244: $parts = explode(':', $root);
245: $root = array_pop($parts);
246: }
247: $root = (string)$root;
248:
249: $query = 'SELECT datatree_id, datatree_parents' .
250: ' FROM ' . $this->_params['table'] .
251: ' WHERE datatree_name = ? AND group_uid = ?' .
252: ' ORDER BY datatree_id';
253: $values = array($root,
254: $this->_params['group']);
255:
256: Horde::logMessage('SQL Query by Horde_DataTree_Sql::_buildParentIds(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
257: $root = $this->_db->getAssoc($query, false, $values);
258: if (is_a($root, 'PEAR_Error') || !count($root)) {
259: return '';
260: }
261:
262: $where = '';
263: $first_time = true;
264: foreach ($root as $object_id => $object_parents) {
265: $pstring = $object_parents . ':' . $object_id . '%';
266: $pquery = '';
267: if (!empty($object_parents)) {
268: $ids = substr($object_parents, 1);
269: $pquery = ' OR ' . $join_name . 'datatree_id IN (' . str_replace(':', ', ', $ids) . ')';
270: }
271: if ($loadTree) {
272: $pquery .= ' OR ' . $join_name . 'datatree_parents = ' . $this->_db->quote(substr($pstring, 0, -1));
273: }
274:
275: if (!$first_time) {
276: $where .= ' OR ';
277: }
278: $where .= sprintf($join_name . 'datatree_parents LIKE %s OR ' . $join_name . 'datatree_id = %s%s',
279: $this->_db->quote($pstring),
280: $object_id,
281: $pquery);
282:
283: $first_time = false;
284: }
285:
286: return $where;
287: }
288:
289: 290: 291: 292: 293: 294: 295: 296: 297: 298: 299:
300: function _loadById($cids)
301: {
302:
303: if (!is_array($cids)) {
304: $cids = array((int)$cids);
305: } else {
306: array_walk($cids, 'intval');
307: }
308:
309:
310: if (!count($cids)) {
311: return true;
312: }
313:
314: 315:
316: $ids = array();
317: foreach ($cids as $cid) {
318: 319:
320: if (!isset($this->_data[$cid])) {
321: $ids[] = (int)$cid;
322: }
323: }
324:
325:
326: if (!count($ids)) {
327: return true;
328: }
329:
330: $in = array_search(DATATREE_ROOT, $ids) === false ? sprintf('datatree_id IN (%s) AND ', implode(', ', $ids)) : '';
331: $query = sprintf('SELECT datatree_id, datatree_parents FROM %s' .
332: ' WHERE %sgroup_uid = %s' .
333: ' ORDER BY datatree_id',
334: $this->_params['table'],
335: $in,
336: $this->_db->quote($this->_params['group']));
337: Horde::logMessage('SQL Query by Horde_DataTree_Sql::_loadById(): ' . $query, 'DEBUG');
338: $parents = $this->_db->getAssoc($query);
339: if (is_a($parents, 'PEAR_Error')) {
340: return $parents;
341: }
342: if (empty($parents)) {
343: return PEAR::raiseError('Object not found.', null, null, null, 'Horde_DataTree ids ' . implode(', ', $ids) . ' not found.');
344: }
345:
346: $ids = array();
347: foreach ($parents as $cid => $parent) {
348: $ids[(int)$cid] = (int)$cid;
349:
350: $pids = explode(':', substr($parent, 1));
351: foreach ($pids as $pid) {
352: $pid = (int)$pid;
353: if (!isset($this->_data[$pid])) {
354: $ids[$pid] = $pid;
355: }
356: }
357: }
358:
359:
360: if (!count($ids)) {
361: return true;
362: }
363:
364: $query = 'SELECT datatree_id, datatree_name, datatree_parents, datatree_order' .
365: ' FROM ' . $this->_params['table'] .
366: ' WHERE datatree_id IN (?' . str_repeat(', ?', count($ids) - 1) . ')' .
367: ' AND group_uid = ? ORDER BY datatree_id';
368: $values = array_merge($ids, array($this->_params['group']));
369:
370: Horde::logMessage('SQL Query by Horde_DataTree_Sql::_loadById(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
371: $data = $this->_db->getAll($query, $values);
372: if (is_a($data, 'PEAR_Error')) {
373: return $data;
374: }
375:
376: return $this->set($data, $this->_params['charset']);
377: }
378:
379: 380: 381: 382: 383: 384: 385:
386: function _exists($object_name)
387: {
388: $query = 'SELECT datatree_id FROM ' . $this->_params['table'] .
389: ' WHERE group_uid = ? AND datatree_name = ? AND datatree_parents = ?';
390:
391: $object_name = Horde_String::convertCharset($object_name, 'UTF-8', $this->_params['charset']);
392: $object_names = explode(':', $object_name);
393: $object_parents = '';
394: foreach ($object_names as $name) {
395: $values = array($this->_params['group'], $name, $object_parents);
396: Horde::logMessage('SQL Query by Horde_DataTree_Sql::_exists(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
397:
398: $result = $this->_db->getOne($query, $values);
399: if (is_a($result, 'PEAR_Error') || !$result) {
400: return false;
401: }
402:
403: $object_parents .= ':' . $result;
404: }
405:
406: return true;
407: }
408:
409: 410: 411: 412: 413: 414: 415:
416: function _getId($name)
417: {
418: $query = 'SELECT datatree_id FROM ' . $this->_params['table']
419: . ' WHERE group_uid = ? AND datatree_name = ?'
420: . ' AND datatree_parents = ?';
421:
422: $ids = array();
423: $name = Horde_String::convertCharset($name, 'UTF-8', $this->_params['charset']);
424: $parts = explode(':', $name);
425: foreach ($parts as $part) {
426: $result = $this->_db->getOne($query, array($this->_params['group'], $part, count($ids) ? ':' . implode(':', $ids) : ''));
427: if (is_a($result, 'PEAR_Error') || !$result) {
428: return null;
429: } else {
430: $ids[] = $result;
431: }
432: }
433:
434: return (int)array_pop($ids);
435: }
436:
437: 438: 439: 440: 441: 442: 443:
444: function _getName($id)
445: {
446: $query = 'SELECT datatree_name FROM ' . $this->_params['table'] .
447: ' WHERE group_uid = ? AND datatree_id = ?';
448: $values = array($this->_params['group'], (int)$id);
449: Horde::logMessage('SQL Query by Horde_DataTree_Sql::_getName(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
450:
451: $name = $this->_db->getOne($query, $values);
452: if (is_a($name, 'PEAR_Error')) {
453: return null;
454: } else {
455: $name = Horde_String::convertCharset($name, $this->_params['charset'], 'UTF-8');
456:
457: $parent = $this->getParentById($id);
458: if ($parent && !is_a($parent, 'PEAR_Error') &&
459: $parent != DATATREE_ROOT) {
460: return $this->getName($parent) . ':' . $name;
461: } else {
462: return $name;
463: }
464: }
465: }
466:
467: 468: 469: 470: 471: 472: 473: 474: 475: 476: 477: 478: 479: 480: 481: 482:
483: function getSortedTree($root, $loadTree = false, $sortby_name = null,
484: $sortby_key = null, $direction = 0)
485: {
486: $query = $this->_buildLoadQuery($root,
487: $loadTree,
488: DATATREE_BUILD_SELECT,
489: $sortby_name,
490: $sortby_key,
491: $direction);
492:
493: if (empty($query)) {
494: return array();
495: }
496: return $this->_db->getAll($query);
497: }
498:
499: 500: 501: 502: 503: 504: 505: 506: 507: 508:
509: function add($object, $id_as_name = false)
510: {
511: $attributes = false;
512: if (is_a($object, 'Horde_DataTreeObject')) {
513: $fullname = $object->getName();
514: $order = $object->order;
515:
516: 517:
518: if (method_exists($object, '_toAttributes')) {
519: $data = '';
520: $ser = null;
521:
522: 523:
524: $attributes = true;
525: } else {
526: $ser = Horde_Serialize::UTF7_BASIC;
527: $data = Horde_Serialize::serialize($object->getData(), $ser, 'UTF-8');
528: }
529: } else {
530: $fullname = $object;
531: $order = null;
532: $data = '';
533: $ser = null;
534: }
535:
536:
537: $id = $this->_write_db->nextId($this->_params['table']);
538: if (is_a($id, 'PEAR_Error')) {
539: Horde::logMessage($id, 'ERR');
540: return $id;
541: }
542:
543: if (strpos($fullname, ':') !== false) {
544: $parts = explode(':', $fullname);
545: $parents = '';
546: $pstring = '';
547: if ($id_as_name) {
548:
549: array_pop($parts);
550:
551: $name = $id;
552:
553: $fullname = implode(':', $parts) . ':' . $id;
554: if (is_a($object, 'Horde_DataTreeObject')) {
555: $object->setName($fullname);
556: } else {
557: $object = $fullname;
558: }
559: } else {
560: $name = array_pop($parts);
561: }
562: foreach ($parts as $par) {
563: $pstring .= (empty($pstring) ? '' : ':') . $par;
564: $pid = $this->getId($pstring);
565: if (is_a($pid, 'PEAR_Error')) {
566:
567: $pid = $this->add($pstring);
568: if (is_a($pid, 'PEAR_Error')) {
569: return $pid;
570: }
571: }
572: $parents .= ':' . $pid;
573: }
574: } else {
575: if ($id_as_name) {
576:
577: $fullname = $id;
578: $name = $id;
579: if (is_a($object, 'Horde_DataTreeObject')) {
580: $object->setName($fullname);
581: } else {
582: $object = $fullname;
583: }
584: } else {
585: $name = $fullname;
586: }
587: $parents = '';
588: $pid = DATATREE_ROOT;
589: }
590:
591: if (parent::exists($fullname)) {
592: return PEAR::raiseError(sprintf('"%s" already exists', $fullname));
593: }
594:
595: $query = 'INSERT INTO ' . $this->_params['table'] .
596: ' (datatree_id, group_uid, datatree_name, datatree_order,' .
597: ' datatree_data, user_uid, datatree_serialized,' .
598: ' datatree_parents)' .
599: ' VALUES (?, ?, ?, ?, ?, ?, ?, ?)';
600: $values = array((int)$id,
601: $this->_params['group'],
602: Horde_String::convertCharset($name, 'UTF-8', $this->_params['charset']),
603: is_null($order) ? NULL : (int)$order,
604: $data,
605: (string)$GLOBALS['registry']->getAuth(),
606: (int)$ser,
607: $parents);
608:
609: Horde::logMessage('SQL Query by Horde_DataTree_Sql::add(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
610: $result = $this->_write_db->query($query, $values);
611: if (is_a($result, 'PEAR_Error')) {
612: Horde::logMessage($result, 'ERR');
613: return $result;
614: }
615:
616: $reorder = $this->reorder($parents, $order, $id);
617: if (is_a($reorder, 'PEAR_Error')) {
618: Horde::logMessage($reorder, 'ERR');
619: return $reorder;
620: }
621:
622: $result = parent::_add($fullname, $id, $pid, $order);
623: if (is_a($result, 'PEAR_Error')) {
624: return $result;
625: }
626:
627: 628:
629: if (!empty($attributes)) {
630: $result = $this->updateData($object);
631: if (is_a($result, 'PEAR_Error')) {
632: return $result;
633: }
634: }
635:
636: return $id;
637: }
638:
639: 640: 641: 642: 643: 644: 645: 646: 647: 648: 649: 650:
651: function reorder($parent, $order = null, $cid = null)
652: {
653: if (!$parent || is_a($parent, 'PEAR_Error')) {
654:
655:
656: return;
657: }
658:
659: $pquery = '';
660: if (!is_array($order) && !is_null($order)) {
661:
662: if (is_null($cid)) {
663:
664: $direction = '-';
665: } else {
666:
667: $direction = '+';
668:
669:
670: $pquery = sprintf(' AND datatree_id != %s', (int)$cid);
671: }
672: $query = sprintf('UPDATE %s SET datatree_order = datatree_order %s 1 WHERE group_uid = %s AND datatree_parents = %s AND datatree_order >= %s',
673: $this->_params['table'],
674: $direction,
675: $this->_write_db->quote($this->_params['group']),
676: $this->_write_db->quote($parent),
677: is_null($order) ? 'NULL' : (int)$order) . $pquery;
678:
679: Horde::logMessage('SQL Query by Horde_DataTree_Sql::reorder(): ' . $query, 'DEBUG');
680: $result = $this->_write_db->query($query);
681: } elseif (is_array($order)) {
682:
683: $query = 'SELECT COUNT(datatree_id)' .
684: ' FROM ' . $this->_params['table'] .
685: ' WHERE group_uid = ? AND datatree_parents = ?' .
686: ' GROUP BY datatree_parents';
687: $values = array($this->_params['group'],
688: $parent);
689:
690: Horde::logMessage('SQL Query by Horde_DataTree_Sql::reorder(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
691:
692: $result = $this->_db->getOne($query, $values);
693: if (is_a($result, 'PEAR_Error')) {
694: return $result;
695: } elseif (count($order) != $result) {
696: return PEAR::raiseError('Cannot reorder, number of entries supplied for reorder does not match number stored.');
697: }
698:
699: $o_key = 0;
700: foreach ($order as $o_cid) {
701: $query = 'UPDATE ' . $this->_params['table'] .
702: ' SET datatree_order = ? WHERE datatree_id = ?';
703: $values = array($o_key, is_null($o_cid) ? NULL : (int)$o_cid);
704:
705: Horde::logMessage('SQL Query by Horde_DataTree_Sql::reorder(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
706: $result = $this->_write_db->query($query, $values);
707: if (is_a($result, 'PEAR_Error')) {
708: return $result;
709: }
710:
711: $o_key++;
712: }
713:
714: $pid = $this->getId($parent);
715:
716:
717: return $this->_reorder($pid, $order);
718: }
719: }
720:
721: 722: 723: 724: 725: 726:
727: function setOrder($id, $order)
728: {
729: $query = 'UPDATE ' . $this->_params['table'] .
730: ' SET datatree_order = ? WHERE datatree_id = ?';
731: $values = array(is_null($order) ? NULL : (int)$order,
732: (int)$id);
733:
734: Horde::logMessage('SQL Query by Horde_DataTree_Sql::setOrder(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
735: return $this->_write_db->query($query, $values);
736: }
737:
738: 739: 740: 741: 742: 743:
744: function remove($object, $force = false)
745: {
746: $id = $this->getId($object);
747: if (is_a($id, 'PEAR_Error')) {
748: return $id;
749: }
750: $order = $this->getOrder($object);
751:
752: $query = 'SELECT datatree_id FROM ' . $this->_params['table'] .
753: ' WHERE group_uid = ? AND datatree_parents LIKE ?' .
754: ' ORDER BY datatree_id';
755: $values = array($this->_params['group'],
756: '%:' . (int)$id . '');
757:
758: Horde::logMessage('SQL Query by Horde_DataTree_Sql::remove(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
759: $children = $this->_db->getAll($query, $values, DB_FETCHMODE_ASSOC);
760:
761: if (count($children)) {
762: if ($force) {
763: foreach ($children as $child) {
764: $cat = $this->getName($child['datatree_id']);
765: $result = $this->remove($cat, true);
766: if (is_a($result, 'PEAR_Error')) {
767: return $result;
768: }
769: }
770: } else {
771: return PEAR::raiseError(sprintf('Cannot remove, %d children exist.', count($children)));
772: }
773: }
774:
775:
776: $query = 'DELETE FROM ' . $this->_params['table_attributes'] .
777: ' WHERE datatree_id = ?';
778: $values = array((int)$id);
779:
780: Horde::logMessage('SQL Query by Horde_DataTree_Sql::remove(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
781: $result = $this->_write_db->query($query, $values);
782: if (is_a($result, 'PEAR_Error')) {
783: return $result;
784: }
785:
786: $query = 'DELETE FROM ' . $this->_params['table'] .
787: ' WHERE datatree_id = ?';
788: $values = array((int)$id);
789:
790: Horde::logMessage('SQL Query by Horde_DataTree_Sql::remove(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
791: $result = $this->_write_db->query($query, $values);
792: if (is_a($result, 'PEAR_Error')) {
793: return $result;
794: }
795:
796: $parents = $this->getParentIdString($object);
797: if (is_a($parents, 'PEAR_Error')) {
798: return $parents;
799: }
800:
801: $reorder = $this->reorder($parents, $order);
802: if (is_a($reorder, 'PEAR_Error')) {
803: return $reorder;
804: }
805:
806: return is_a(parent::remove($object), 'PEAR_Error') ? $id : true;
807: }
808:
809: 810: 811: 812: 813: 814: 815: 816: 817: 818: 819:
820: function removeByIds($ids)
821: {
822:
823: if (!is_array($ids)) {
824: $ids = array((int)$ids);
825: } else {
826: array_walk($ids, 'intval');
827: }
828:
829:
830: if (!$ids) {
831: return true;
832: }
833:
834:
835: $query = 'DELETE FROM ' . $this->_params['table_attributes'] .
836: ' WHERE datatree_id IN (?' . str_repeat(', ?', count($ids) - 1) . ')';
837: $values = $ids;
838:
839: Horde::logMessage('SQL Query by Horde_DataTree_Sql::removeByIds(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
840: $result = $this->_write_db->query($query, $values);
841: if (is_a($result, 'PEAR_Error')) {
842: return $result;
843: }
844:
845: $query = 'DELETE FROM ' . $this->_params['table'] .
846: ' WHERE datatree_id IN (?' . str_repeat(', ?', count($ids) - 1) . ')';
847: $values = $ids;
848:
849: Horde::logMessage('SQL Query by Horde_DataTree_Sql::removeByIds(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
850: return $this->_write_db->query($query, $values);
851: }
852:
853: 854: 855: 856: 857: 858: 859: 860: 861: 862: 863:
864: function removeByNames($names)
865: {
866: if (!is_array($names)) {
867: $names = array($names);
868: }
869:
870:
871: if (!$names) {
872: return true;
873: }
874:
875: $query = 'SELECT datatree_id FROM ' . $this->_params['table'] .
876: ' WHERE datatree_name IN (?' . str_repeat(', ?', count($names) - 1) . ')';
877: $values = $names;
878:
879: Horde::logMessage('SQL Query by Horde_DataTree_Sql::removeByNames(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
880: $ids = $this->_db->getCol($query, 0, $values);
881: if (is_a($ids, 'PEAR_Error')) {
882: return $ids;
883: }
884:
885: return $this->removeByIds($ids);
886: }
887:
888: 889: 890: 891: 892: 893:
894: function move($object, $newparent = null)
895: {
896: $old_parent_path = $this->getParentIdString($object);
897: $result = parent::move($object, $newparent);
898: if (is_a($result, 'PEAR_Error')) {
899: return $result;
900: }
901: $id = $this->getId($object);
902: $new_parent_path = $this->getParentIdString($object);
903:
904: 905: 906:
907: $query = 'SELECT datatree_id, datatree_parents' .
908: ' FROM ' . $this->_params['table'] .
909: ' WHERE datatree_parents = ? OR datatree_parents LIKE ?' .
910: ' OR datatree_id = ?';
911: $values = array($old_parent_path . ':' . $id,
912: $old_parent_path . ':' . $id . ':%',
913: (int)$id);
914:
915: Horde::logMessage('SQL Query by Horde_DataTree_Sql::move(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
916: $rowset = $this->_db->query($query, $values);
917: if (is_a($rowset, 'PEAR_Error')) {
918: return $rowset;
919: }
920:
921: 922:
923: while ($row = $rowset->fetchRow(DB_FETCHMODE_ASSOC)) {
924: if (is_a($row, 'PEAR_Error')) {
925: return $row;
926: }
927:
928: $oquery = '';
929: if ($row['datatree_id'] == $id) {
930: $oquery = ', datatree_order = 0 ';
931: }
932:
933: 934:
935: if (!empty($row['datatree_parents'])) {
936: $ppath = str_replace($old_parent_path, $new_parent_path, $row['datatree_parents']);
937: } else {
938: $ppath = $new_parent_path;
939: }
940: $query = sprintf('UPDATE %s SET datatree_parents = %s' . $oquery . ' WHERE datatree_id = %s',
941: $this->_params['table'],
942: $this->_write_db->quote($ppath),
943: (int)$row['datatree_id']);
944:
945: Horde::logMessage('SQL Query by Horde_DataTree_Sql::move(): ' . $query, 'DEBUG');
946: $result = $this->_write_db->query($query);
947: if (is_a($result, 'PEAR_Error')) {
948: return $result;
949: }
950: }
951:
952: $order = $this->getOrder($object);
953:
954:
955: $reorder = $this->reorder($old_parent_path, $order);
956:
957:
958: $reorder = $this->reorder($new_parent_path, 0, $id);
959:
960: return true;
961: }
962:
963: 964: 965: 966: 967: 968:
969: function rename($old_object, $new_object_name)
970: {
971:
972: $result = parent::rename($old_object, $new_object_name);
973: if (is_a($result, 'PEAR_Error')) {
974: return $result;
975: }
976:
977:
978: $id = $this->getId($old_object);
979: $query = 'UPDATE ' . $this->_params['table'] .
980: ' SET datatree_name = ? WHERE datatree_id = ?';
981: $values = array(Horde_String::convertCharset($new_object_name, 'UTF-8', $this->_params['charset']),
982: (int)$id);
983:
984: Horde::logMessage('SQL Query by Horde_DataTree_Sql::rename(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
985: $result = $this->_write_db->query($query, $values);
986:
987: return is_a($result, 'PEAR_Error') ? $result : true;
988: }
989:
990: 991: 992: 993: 994:
995: function getData($cid)
996: {
997: if (is_array($cid)) {
998: if (!count($cid)) {
999: return array();
1000: }
1001:
1002: $query = sprintf('SELECT datatree_id, datatree_data, datatree_serialized FROM %s WHERE datatree_id IN (%s)',
1003: $this->_params['table'],
1004: implode(', ', $cid));
1005:
1006: Horde::logMessage('SQL Query by Horde_DataTree_Sql::getData(): ' . $query, 'DEBUG');
1007: $result = $this->_db->getAssoc($query);
1008: if (is_a($result, 'PEAR_Error')) {
1009: Horde::logMessage($result, 'ERR');
1010: return $result;
1011: }
1012:
1013: $data = array();
1014: foreach ($result as $id => $row) {
1015: $data[$id] = Horde_Serialize::unserialize($row[0], $row[1],
1016: 'UTF-8');
1017:
1018: if ($row[1] == Horde_Serialize::BASIC) {
1019: $data[$id] = Horde_String::convertCharset($data[$id],
1020: $GLOBALS['registry']->getLanguageCharset(),
1021: 'UTF-8');
1022: }
1023:
1024: $data[$id] = (is_null($data[$id]) || !is_array($data[$id]))
1025: ? array()
1026: : $data[$id];
1027: }
1028:
1029: return $data;
1030: } else {
1031: $query = 'SELECT datatree_data, datatree_serialized' .
1032: ' FROM ' . $this->_params['table'] .
1033: ' WHERE datatree_id = ?';
1034: $values = array((int)$cid);
1035:
1036: Horde::logMessage('SQL Query by Horde_DataTree_Sql::getData(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
1037: $row = $this->_db->getRow($query, $values, DB_FETCHMODE_ASSOC);
1038:
1039: $data = Horde_Serialize::unserialize($row['datatree_data'],
1040: $row['datatree_serialized'],
1041: 'UTF-8');
1042:
1043: if ($row['datatree_serialized'] == Horde_Serialize::BASIC) {
1044: $data = Horde_String::convertCharset($data, $GLOBALS['registry']->getLanguageCharset(), 'UTF-8');
1045: }
1046: return (is_null($data) || !is_array($data)) ? array() : $data;
1047: }
1048: }
1049:
1050: 1051: 1052: 1053: 1054: 1055: 1056: 1057: 1058: 1059:
1060: function getAttributes($cid, $keys = false)
1061: {
1062: if (empty($cid)) {
1063: return array();
1064: }
1065:
1066: if ($keys) {
1067: $filter = sprintf(' AND attribute_key IN (\'%s\')',
1068: implode("', '", $keys));
1069: } else {
1070: $filter = '';
1071: }
1072:
1073: if (is_array($cid)) {
1074: $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',
1075: $this->_params['table_attributes'],
1076: implode(', ', $cid),
1077: $filter);
1078:
1079: Horde::logMessage('SQL Query by Horde_DataTree_Sql::getAttributes(): ' . $query, 'DEBUG');
1080: $rows = $this->_db->getAll($query, DB_FETCHMODE_ASSOC);
1081: if (is_a($rows, 'PEAR_Error')) {
1082: return $rows;
1083: }
1084:
1085: $data = array();
1086: foreach ($rows as $row) {
1087: if (empty($data[$row['datatree_id']])) {
1088: $data[$row['datatree_id']] = array();
1089: }
1090: $data[$row['datatree_id']][] = array('name' => $row['name'],
1091: 'key' => $row['key'],
1092: 'value' => Horde_String::convertCharset($row['value'], $this->_params['charset'], 'UTF-8'));
1093: }
1094: return $data;
1095: } else {
1096: $query = sprintf('SELECT attribute_name AS name, attribute_key AS "key", attribute_value AS value FROM %s WHERE datatree_id = %s%s',
1097: $this->_params['table_attributes'],
1098: (int)$cid,
1099: $filter);
1100:
1101: Horde::logMessage('SQL Query by Horde_DataTree_Sql::getAttributes(): ' . $query, 'DEBUG');
1102: $rows = $this->_db->getAll($query, DB_FETCHMODE_ASSOC);
1103: for ($i = 0; $i < count($rows); $i++) {
1104: $rows[$i]['value'] = Horde_String::convertCharset($rows[$i]['value'],
1105: $this->_params['charset'],
1106: 'UTF-8');
1107: }
1108: return $rows;
1109: }
1110: }
1111:
1112: 1113: 1114: 1115: 1116: 1117: 1118: 1119: 1120: 1121: 1122: 1123:
1124: function countByAttributes($criteria, $parent = DATATREE_ROOT,
1125: $allLevels = true, $restrict = 'name')
1126: {
1127: if (!count($criteria)) {
1128: return 0;
1129: }
1130:
1131: $aq = $this->buildAttributeQuery($criteria,
1132: $parent,
1133: $allLevels,
1134: $restrict,
1135: DATATREE_BUILD_COUNT);
1136: if (is_a($aq, 'PEAR_Error')) {
1137: return $aq;
1138: }
1139: list($query, $values) = $aq;
1140:
1141: Horde::logMessage('SQL Query by Horde_DataTree_Sql::countByAttributes(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
1142:
1143: $result = $this->_db->query($query, $values);
1144: if (is_a($result, 'PEAR_Error')) {
1145: Horde::logMessage($result, 'ERR');
1146: return $result;
1147: }
1148: $row = $result->fetchRow();
1149: if (is_a($row, 'PEAR_Error')) {
1150: Horde::logMessage($row, 'ERR');
1151: return $row;
1152: }
1153:
1154: return $row[0];
1155: }
1156:
1157: 1158: 1159: 1160: 1161: 1162: 1163: 1164: 1165: 1166: 1167: 1168: 1169: 1170: 1171: 1172: 1173: 1174: 1175:
1176: function getByAttributes($criteria, $parent = DATATREE_ROOT,
1177: $allLevels = true, $restrict = 'name', $from = 0,
1178: $count = 0, $sortby_name = null,
1179: $sortby_key = null, $direction = 0)
1180: {
1181: if (!count($criteria)) {
1182: return PEAR::raiseError('no criteria');
1183: }
1184:
1185:
1186:
1187:
1188: if ($count == 0 && $from == 0) {
1189: foreach ($criteria as $key => $vals) {
1190: if ($key == 'OR') {
1191: $rows = array();
1192: $num_or_statements = count($criteria[$key]);
1193: for ($i = 0; $i < $num_or_statements; $i++) {
1194: $criteria_or = $criteria['OR'][$i];
1195: list($query, $values) = $this->buildAttributeQuery(
1196: $criteria_or,
1197: $parent,
1198: $allLevels,
1199: $restrict,
1200: DATATREE_BUILD_SELECT,
1201: $sortby_name,
1202: $sortby_key,
1203: $direction);
1204: if ($count) {
1205: $query = $this->_db->modifyLimitQuery($query, $from, $count);
1206: }
1207:
1208: Horde::logMessage('SQL Query by Horde_DataTree_Sql::getByAttributes(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
1209:
1210: $result = $this->_db->query($query, $values);
1211: if (is_a($result, 'PEAR_Error')) {
1212: Horde::logMessage($result, 'ERR');
1213: return $result;
1214: }
1215: while ($row = $result->fetchRow()) {
1216: $rows[$row[0]] = Horde_String::convertCharset($row[1], $this->_params['charset'], 'UTF-8');
1217: }
1218: }
1219:
1220: return $rows;
1221: }
1222: }
1223: }
1224:
1225: $aq = $this->buildAttributeQuery($criteria,
1226: $parent,
1227: $allLevels,
1228: $restrict,
1229: DATATREE_BUILD_SELECT,
1230: $sortby_name,
1231: $sortby_key,
1232: $direction);
1233: if (is_a($aq, 'PEAR_Error')) {
1234: return $aq;
1235: }
1236:
1237: list($query, $values) = $aq;
1238:
1239: if ($count) {
1240: $query = $this->_db->modifyLimitQuery($query, $from, $count);
1241: }
1242:
1243: Horde::logMessage('SQL Query by Horde_DataTree_Sql::getByAttributes(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
1244: $result = $this->_db->query($query, $values);
1245: if (is_a($result, 'PEAR_Error')) {
1246: Horde::logMessage($result, 'ERR');
1247: return $result;
1248: }
1249:
1250: $rows = array();
1251: while ($row = $result->fetchRow()) {
1252: $rows[$row[0]] = Horde_String::convertCharset($row[1], $this->_params['charset'], 'UTF-8');
1253: }
1254:
1255: return $rows;
1256: }
1257:
1258: 1259: 1260: 1261: 1262: 1263: 1264: 1265: 1266: 1267: 1268: 1269: 1270:
1271: function sortByAttributes($unordered_ids, $sortby_name = null,
1272: $sortby_key = null, $direction = 0)
1273: {
1274:
1275: $where = '';
1276: if (!is_null($sortby_name)) {
1277: $where = sprintf(' AND attribute_name = %s ',
1278: $this->_db->quote($sortby_name));
1279: }
1280: if (!is_null($sortby_key)) {
1281: $where = sprintf('%s AND attribute_key = %s ',
1282: $where,
1283: $this->_db->quote($sortby_key));
1284: }
1285:
1286: $query = sprintf('SELECT datatree_id FROM %s WHERE datatree_id IN (%s) %s ORDER BY attribute_value %s',
1287: $this->_params['table_attributes'],
1288: implode(',', $unordered_ids),
1289: $where,
1290: ($direction == 1) ? 'DESC' : 'ASC');
1291:
1292: Horde::logMessage('SQL Query by Horde_DataTree_Sql::sortByAttributes(): ' . $query, 'DEBUG');
1293: $ordered_ids = $this->_db->getCol($query);
1294:
1295: 1296:
1297: if (count($ordered_ids) != count($unordered_ids)) {
1298: $ordered_ids = array_keys(array_flip(array_merge($ordered_ids, $unordered_ids)));
1299: }
1300:
1301: return $ordered_ids;
1302: }
1303:
1304: 1305: 1306: 1307: 1308: 1309: 1310: 1311: 1312: 1313: 1314: 1315: 1316: 1317: 1318: 1319:
1320: function countValuesByAttributes($criteria, $parent = DATATREE_ROOT,
1321: $allLevels = true, $restrict = 'name',
1322: $key = null, $name = null)
1323: {
1324: if (!count($criteria)) {
1325: return PEAR::raiseError('no criteria');
1326: }
1327:
1328: $aq = $this->buildAttributeQuery($criteria,
1329: $parent,
1330: $allLevels,
1331: $restrict,
1332: DATATREE_BUILD_VALUES_COUNT);
1333:
1334: $aq[0] .= ' AND a.datatree_id = c.datatree_id';
1335:
1336: if ($key !== null) {
1337: $aq[0] .= ' AND a.attribute_key = ?';
1338: $aq[1][] = $key;
1339: }
1340:
1341: if ($name !== null) {
1342: $aq[0] .= ' AND a.attribute_name = ?';
1343: $aq[1][] = $name;
1344: }
1345:
1346: return $this->_db->getOne($aq[0], $aq[1]);
1347: }
1348:
1349: 1350: 1351: 1352: 1353: 1354: 1355: 1356: 1357: 1358: 1359: 1360: 1361: 1362: 1363: 1364: 1365: 1366: 1367: 1368: 1369: 1370: 1371:
1372: function getValuesByAttributes($criteria, $parent = DATATREE_ROOT,
1373: $allLevels = true, $restrict = 'name', $from = 0,
1374: $count = 0, $sortby_name = null,
1375: $sortby_key = null, $direction = 0,
1376: $key = null, $name = null)
1377: {
1378: if (!count($criteria)) {
1379: return PEAR::raiseError('no criteria');
1380: }
1381:
1382: $aq = $this->buildAttributeQuery($criteria,
1383: $parent,
1384: $allLevels,
1385: $restrict,
1386: DATATREE_BUILD_VALUES,
1387: $sortby_name,
1388: $sortby_key,
1389: $direction);
1390:
1391: $aq[0] .= ' AND a.datatree_id = c.datatree_id';
1392:
1393: if ($key !== null) {
1394: $aq[0] .= ' AND a.attribute_key = ?';
1395: $aq[1][] = $key;
1396: }
1397:
1398: if ($name !== null) {
1399: $aq[0] .= ' AND a.attribute_name = ?';
1400: $aq[1][] = $name;
1401: }
1402:
1403: if ($count) {
1404: $aq[0] = $this->_db->modifyLimitQuery($aq[0], $from, $count);
1405: }
1406:
1407: return $this->_db->getCol($aq[0], 0, $aq[1]);
1408: }
1409:
1410: 1411: 1412: 1413: 1414: 1415: 1416: 1417: 1418: 1419: 1420: 1421: 1422: 1423:
1424: function getAttributeValues($attribute_name = null, $attribute_key = null,
1425: $parent = DATATREE_ROOT, $allLevels = true)
1426: {
1427:
1428: $where = '';
1429: if (!is_null($attribute_name)) {
1430: $where .= 'a.attribute_name = ' . $this->_db->quote($attribute_name);
1431: }
1432: if (!is_null($attribute_key)) {
1433: if ($where) {
1434: $where .= ' AND ';
1435: }
1436: $where .= 'a.attribute_key = ' . $this->_db->quote($attribute_key);
1437: }
1438:
1439:
1440: if (!$where) {
1441: return PEAR::raiseError('no criteria');
1442: }
1443:
1444:
1445: $levelQuery = '';
1446: if ($parent != DATATREE_ROOT) {
1447: $parts = explode(':', $parent);
1448: $parents = '';
1449: $pstring = '';
1450: foreach ($parts as $part) {
1451: $pstring .= (empty($pstring) ? '' : ':') . $part;
1452: $pid = $this->getId($pstring);
1453: if (is_a($pid, 'PEAR_Error')) {
1454: return $pid;
1455: }
1456: $parents .= ':' . $pid;
1457: }
1458:
1459: if ($allLevels) {
1460: $levelQuery = sprintf('AND (datatree_parents = %s OR datatree_parents LIKE %s)',
1461: $this->_db->quote($parents),
1462: $this->_db->quote($parents . ':%'));
1463: } else {
1464: $levelQuery = sprintf('AND datatree_parents = %s',
1465: $this->_db->quote($parents));
1466: }
1467: } elseif (!$allLevels) {
1468: $levelQuery = "AND datatree_parents = ''";
1469: }
1470:
1471:
1472: $joins = 'LEFT JOIN ' . $this->_params['table'] .
1473: ' c ON a.datatree_id = c.datatree_id';
1474:
1475: $query = sprintf('SELECT DISTINCT a.attribute_value FROM %s a %s WHERE c.group_uid = %s AND %s %s',
1476: $this->_params['table_attributes'],
1477: $joins,
1478: $this->_db->quote($this->_params['group']),
1479: $where,
1480: $levelQuery);
1481:
1482: Horde::logMessage('SQL Query by Horde_DataTree_Sql::getAttributeValues(): ' . $query, 'DEBUG');
1483:
1484: $rows = $this->_db->getCol($query);
1485: if (is_a($rows, 'PEAR_Error')) {
1486: Horde::logMessage($rows, 'ERR');
1487: }
1488:
1489: return $rows;
1490: }
1491:
1492: 1493: 1494: 1495: 1496: 1497: 1498: 1499: 1500: 1501: 1502: 1503: 1504: 1505: 1506: 1507: 1508: 1509: 1510: 1511: 1512: 1513: 1514: 1515: 1516: 1517: 1518: 1519: 1520: 1521: 1522: 1523: 1524: 1525: 1526: 1527: 1528: 1529: 1530: 1531:
1532: function buildAttributeQuery($criteria, $parent = DATATREE_ROOT,
1533: $allLevels = true, $restrict = 'name',
1534: $operation = DATATREE_BUILD_SELECT,
1535: $sortby_name = null, $sortby_key = null,
1536: $direction = 0)
1537: {
1538: if (!count($criteria)) {
1539: return array('', array());
1540: }
1541:
1542:
1543: $this->_tableCount = 1;
1544: $query = '';
1545: $values = array();
1546: foreach ($criteria as $key => $vals) {
1547: if ($key == 'OR' || $key == 'AND') {
1548: if (!empty($query)) {
1549: $query .= ' ' . $key . ' ';
1550: }
1551: $binds = $this->_buildAttributeQuery($key, $vals);
1552: $query .= '(' . $binds[0] . ')';
1553: $values += $binds[1];
1554: }
1555: }
1556:
1557:
1558: $levelQuery = '';
1559: $levelValues = array();
1560: if ($parent != DATATREE_ROOT) {
1561: $parts = explode(':', $parent);
1562: $parents = '';
1563: $pstring = '';
1564: foreach ($parts as $part) {
1565: $pstring .= (empty($pstring) ? '' : ':') . $part;
1566: $pid = $this->getId($pstring);
1567: if (is_a($pid, 'PEAR_Error')) {
1568: return $pid;
1569: }
1570: $parents .= ':' . $pid;
1571: }
1572:
1573: if ($allLevels) {
1574: $levelQuery = 'AND (datatree_parents = ? OR datatree_parents LIKE ?)';
1575: $levelValues = array($parents, $parents . ':%');
1576: } else {
1577: $levelQuery = 'AND datatree_parents = ?';
1578: $levelValues = array($parents);
1579: }
1580: } elseif (!$allLevels) {
1581: $levelQuery = "AND datatree_parents = ''";
1582: }
1583:
1584:
1585: $joins = array();
1586: $pairs = array();
1587: for ($i = 1; $i <= $this->_tableCount; $i++) {
1588: $joins[] = 'LEFT JOIN ' . $this->_params['table_attributes'] .
1589: ' a' . $i . ' ON a' . $i . '.datatree_id = c.datatree_id';
1590:
1591: if ($i != 1) {
1592: if ($restrict == 'name') {
1593: $pairs[] = 'AND a1.attribute_name = a' . $i . '.attribute_name';
1594: } elseif ($restrict == 'id') {
1595: $pairs[] = 'AND a1.datatree_id = a' . $i . '.datatree_id';
1596: }
1597: }
1598: }
1599:
1600:
1601: $sort = array();
1602: if (!is_null($sortby_name) || !is_null($sortby_key)) {
1603: $order_table = 'a' . $i;
1604: $joins[] = 'LEFT JOIN ' . $this->_params['table_attributes'] .
1605: ' ' . $order_table . ' ON ' . $order_table .
1606: '.datatree_id = c.datatree_id';
1607:
1608: if (!is_null($sortby_name)) {
1609: $pairs[] = sprintf('AND %s.attribute_name = ? ', $order_table);
1610: $sort[] = $sortby_name;
1611: }
1612: if (!is_null($sortby_key)) {
1613: $pairs[] = sprintf('AND %s.attribute_key = ? ', $order_table);
1614: $sort[] = $sortby_key;
1615: }
1616:
1617: $order = sprintf('%s.attribute_value %s',
1618: $order_table,
1619: ($direction == 1) ? 'DESC' : 'ASC');
1620: $group_by = 'c.datatree_id, c.datatree_name, c.datatree_order, ' .
1621: $order_table . '.attribute_value';
1622: } else {
1623: $order = 'c.datatree_order, c.datatree_name, c.datatree_id';
1624: $group_by = 'c.datatree_id, c.datatree_name, c.datatree_order';
1625: }
1626:
1627: $joins = implode(' ', $joins);
1628: $pairs = implode(' ', $pairs);
1629:
1630: switch ($operation) {
1631:
1632: case DATATREE_BUILD_VALUES_COUNT:
1633: $what = 'COUNT(DISTINCT(a.attribute_value))';
1634: $from = ' ' . $this->_params['table_attributes'] . ' a, ' . $this->_params['table'];
1635: $tail = '';
1636: break;
1637:
1638: case DATATREE_BUILD_VALUES:
1639: $what = 'DISTINCT(a.attribute_value)';
1640: $from = ' ' . $this->_params['table_attributes'] . ' a, ' . $this->_params['table'];
1641: $tail = '';
1642: break;
1643:
1644: case DATATREE_BUILD_COUNT:
1645: $what = 'COUNT(DISTINCT c.datatree_id)';
1646: $from = $this->_params['table'];
1647: $tail = '';
1648: break;
1649:
1650: default:
1651: $what = 'c.datatree_id, c.datatree_name';
1652: $from = $this->_params['table'];
1653: $tail = sprintf('GROUP BY %s ORDER BY %s', $group_by, $order);
1654: break;
1655: }
1656:
1657: return array(sprintf('SELECT %s FROM %s c %s WHERE c.group_uid = ? AND %s %s %s %s',
1658: $what,
1659: $from,
1660: $joins,
1661: $query,
1662: $levelQuery,
1663: $pairs,
1664: $tail),
1665: array_merge(array($this->_params['group']),
1666: $values,
1667: $levelValues,
1668: $sort));
1669: }
1670:
1671: 1672: 1673: 1674: 1675: 1676: 1677: 1678: 1679: 1680: 1681: 1682:
1683: function _buildAttributeQuery($glue, $criteria, $join = false)
1684: {
1685:
1686: $clause = '';
1687: $values = array();
1688:
1689:
1690: $alias = $this->_getAlias($join);
1691:
1692: foreach ($criteria as $key => $vals) {
1693: if (!empty($clause)) {
1694: $clause .= ' ' . $glue . ' ';
1695: }
1696: if (!empty($vals['OR']) || !empty($vals['AND'])) {
1697: $binds = $this->_buildAttributeQuery($glue, $vals);
1698: $clause .= '(' . $binds[0] . ')';
1699: $values = array_merge($values, $binds[1]);
1700: } elseif (!empty($vals['JOIN'])) {
1701: $binds = $this->_buildAttributeQuery($glue, $vals['JOIN'], true);
1702: $clause .= $binds[0];
1703: $values = array_merge($values, $binds[1]);
1704: } else {
1705: if (isset($vals['field'])) {
1706:
1707:
1708: if (is_array($vals['test'])) {
1709: for ($i = 0, $iC = count($vals['test']); $i < $iC; ++$i) {
1710: $vals['test'][$i] = (string)$vals['test'][$i];
1711: }
1712: } else {
1713: $vals['test'] = (string)$vals['test'];
1714: }
1715:
1716: $binds = self::buildClause($this->_db, $alias . '.attribute_' . $vals['field'], $vals['op'], $vals['test'], true);
1717: $clause .= $binds[0];
1718: $values = array_merge($values, $binds[1]);
1719: } else {
1720: $binds = $this->_buildAttributeQuery($key, $vals);
1721: $clause .= $binds[0];
1722: $values = array_merge($values, $binds[1]);
1723: }
1724: }
1725: }
1726:
1727: return array($clause, $values);
1728: }
1729:
1730: 1731: 1732: 1733: 1734: 1735:
1736: function _getAlias($increment = false)
1737: {
1738: static $seen = array();
1739:
1740: if ($increment && !empty($seen[$this->_tableCount])) {
1741: $this->_tableCount++;
1742: }
1743:
1744: $seen[$this->_tableCount] = true;
1745: return 'a' . $this->_tableCount;
1746: }
1747:
1748: 1749: 1750: 1751: 1752: 1753:
1754: function updateData($object)
1755: {
1756: if (!is_a($object, 'Horde_DataTreeObject')) {
1757:
1758: return true;
1759: }
1760:
1761:
1762: $id = $this->getId($object->getName());
1763: if (is_a($id, 'PEAR_Error')) {
1764: return $id;
1765: }
1766:
1767:
1768: if (method_exists($object, '_toAttributes')) {
1769: 1770: 1771: 1772:
1773: $query = 'UPDATE ' . $this->_params['table'] .
1774: ' SET datatree_data = ? WHERE datatree_id = ?';
1775: $values = array(NULL, (int)$id);
1776:
1777: Horde::logMessage('SQL Query by Horde_DataTree_Sql::updateData(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
1778: $this->_write_db->query($query, $values);
1779:
1780:
1781: $this->_write_db->autoCommit(false);
1782:
1783:
1784: $query = 'DELETE FROM ' . $this->_params['table_attributes'] .
1785: ' WHERE datatree_id = ?';
1786: $values = array((int)$id);
1787:
1788: Horde::logMessage('SQL Query by Horde_DataTree_Sql::updateData(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
1789: $result = $this->_write_db->query($query, $values);
1790: if (is_a($result, 'PEAR_Error')) {
1791: $this->_write_db->rollback();
1792: $this->_write_db->autoCommit(true);
1793: return $result;
1794: }
1795:
1796: 1797: 1798:
1799: $attributes = $object->_toAttributes();
1800: $query = 'INSERT INTO ' . $this->_params['table_attributes'] .
1801: ' (datatree_id, attribute_name, attribute_key, attribute_value)' .
1802: ' VALUES (?, ?, ?, ?)';
1803: $statement = $this->_write_db->prepare($query);
1804: foreach ($attributes as $attr) {
1805: $values = array((int)$id,
1806: $attr['name'],
1807: $attr['key'],
1808: Horde_String::convertCharset($attr['value'], 'UTF-8', $this->_params['charset']));
1809:
1810: Horde::logMessage('SQL Query by Horde_DataTree_Sql::updateData(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
1811:
1812: $result = $this->_write_db->execute($statement, $values);
1813: if (is_a($result, 'PEAR_Error')) {
1814: $this->_write_db->rollback();
1815: $this->_write_db->autoCommit(true);
1816: return $result;
1817: }
1818: }
1819:
1820:
1821: $result = $this->_write_db->commit();
1822: $this->_write_db->autoCommit(true);
1823:
1824: return is_a($result, 'PEAR_Error') ? $result : true;
1825: } else {
1826:
1827: $ser = Horde_Serialize::UTF7_BASIC;
1828: $data = Horde_Serialize::serialize($object->getData(), $ser, 'UTF-8');
1829:
1830: $query = 'UPDATE ' . $this->_params['table'] .
1831: ' SET datatree_data = ?, datatree_serialized = ?' .
1832: ' WHERE datatree_id = ?';
1833: $values = array($data,
1834: (int)$ser,
1835: (int)$id);
1836:
1837: Horde::logMessage('SQL Query by Horde_DataTree_Sql::updateData(): ' . $query . ', ' . var_export($values, true), 'DEBUG');
1838: $result = $this->_write_db->query($query, $values);
1839:
1840: return is_a($result, 'PEAR_Error') ? $result : true;
1841: }
1842: }
1843:
1844: 1845: 1846: 1847: 1848:
1849: function _init()
1850: {
1851: try {
1852: $this->_db = $GLOBALS['injector']->getInstance('Horde_Core_Factory_DbPear')->create('read');
1853: $this->_write_db = $GLOBALS['injector']->getInstance('Horde_Core_Factory_DbPear')->create('rw');
1854: } catch (Horde_Exception $e) {
1855: return PEAR::raiseError($e->getMessage());
1856: }
1857:
1858: $this->_params = array_merge(array(
1859: 'table' => 'horde_datatree',
1860: 'table_attributes' => 'horde_datatree_attributes',
1861: ), $this->_params);
1862:
1863: return true;
1864: }
1865:
1866: 1867: 1868: 1869: 1870: 1871: 1872: 1873: 1874: 1875: 1876: 1877: 1878: 1879: 1880:
1881: static public function buildClause($dbh, $lhs, $op, $rhs, $bind = false, $params = array())
1882: {
1883: $type = $dbh instanceof Horde_Db_Adapter ? Horde_String::lower($dbh->adapterName()) : $dbh->phptype;
1884:
1885: switch ($op) {
1886: case '|':
1887: case '&':
1888: switch ($type) {
1889: case 'pgsql':
1890: case 'pdo_postgresql':
1891:
1892:
1893: $query = 'CASE WHEN CAST(%s AS VARCHAR) ~ \'^-?[0-9]+$\' THEN (CAST(%s AS INTEGER) %s %s) <> 0 ELSE FALSE END';
1894: if ($bind) {
1895: return array(sprintf(self::escapePrepare($query),
1896: self::escapePrepare($lhs),
1897: self::escapePrepare($lhs),
1898: self::escapePrepare($op),
1899: '?'),
1900: array((int)$rhs));
1901: } else {
1902: return sprintf($query, $lhs, $lhs, $op, (int)$rhs);
1903: }
1904:
1905: case 'oci8':
1906:
1907:
1908: $query = 'bitand(%s, %s) = %s';
1909: if ($bind) {
1910: return array(sprintf(self::escapePrepare($query),
1911: self::escapePrepare($lhs), '?', '?'),
1912: array((int)$rhs, (int)$rhs));
1913: } else {
1914: return sprintf($query, $lhs, (int)$rhs, (int)$rhs);
1915: }
1916:
1917: case 'mssql':
1918:
1919: $query = '(CASE WHEN ISNUMERIC(%s) = 1 THEN (%s & %s) ELSE %s END) = %s';
1920: if ($bind) {
1921: return array(sprintf(self::escapePrepare($query),
1922: self::escapePrepare($lhs),
1923: self::escapePrepare($lhs), '?', '?', '?'),
1924: array((int)$rhs, (int)$rhs - 1, (int)$rhs));
1925: } else {
1926: return sprintf($query, $lhs, $lhs, (int)$rhs, (int)$rhs - 1, (int)$rhs);
1927: }
1928:
1929: case 'odbc':
1930:
1931: $query = '(%s & %s) = %s';
1932: if ($bind) {
1933: return array(sprintf(self::escapePrepare($query),
1934: self::escapePrepare($lhs), '?', '?'),
1935: array((int)$rhs, (int)$rhs));
1936: } else {
1937: return sprintf($query, $lhs, (int)$rhs, (int)$rhs);
1938: }
1939:
1940: default:
1941: if ($bind) {
1942: return array($lhs . ' ' . self::escapePrepare($op) . ' ?',
1943: array((int)$rhs));
1944: } else {
1945: return $lhs . ' ' . $op . ' ' . (int)$rhs;
1946: }
1947: }
1948:
1949: case '~':
1950: if ($type == 'mysql' || $type == 'mysqli' || $type == 'pdo_mysql') {
1951: $op = 'REGEXP';
1952: }
1953: if ($bind) {
1954: return array($lhs . ' ' . $op . ' ?', array($rhs));
1955: } else {
1956: return $lhs . ' ' . $op . ' ' . $rhs;
1957: }
1958:
1959: case 'IN':
1960: if ($bind) {
1961: if (is_array($rhs)) {
1962: return array($lhs . ' IN (?' . str_repeat(', ?', count($rhs) - 1) . ')', $rhs);
1963: } else {
1964:
1965:
1966: if (substr($rhs, 0, 1) == '(') {
1967: $rhs = substr($rhs, 1);
1968: }
1969: if (substr($rhs, -1) == ')') {
1970: $rhs = substr($rhs, 0, -1);
1971: }
1972:
1973: $ids = preg_split('/\s*,\s*/', $rhs);
1974:
1975: return array($lhs . ' IN (?' . str_repeat(', ?', count($ids) - 1) . ')', $ids);
1976: }
1977: } else {
1978: if (is_array($rhs)) {
1979: return $lhs . ' IN ' . implode(', ', $rhs);
1980: } else {
1981: return $lhs . ' IN ' . $rhs;
1982: }
1983: }
1984:
1985: case 'LIKE':
1986: if ($type == 'pgsql' || $type == 'pdo_pgsql') {
1987: $query = '%s ILIKE %s';
1988: } else {
1989: $query = 'LOWER(%s) LIKE LOWER(%s)';
1990: }
1991: if ($bind) {
1992: if (empty($params['begin'])) {
1993: return array(sprintf($query,
1994: self::escapePrepare($lhs),
1995: '?'),
1996: array('%' . $rhs . '%'));
1997: } else {
1998: return array(sprintf('(' . $query . ' OR ' . $query . ')',
1999: self::escapePrepare($lhs),
2000: '?',
2001: self::escapePrepare($lhs),
2002: '?'),
2003: array($rhs . '%', '% ' . $rhs . '%'));
2004: }
2005: } else {
2006: if (empty($params['begin'])) {
2007: return sprintf($query,
2008: $lhs,
2009: $dbh->quote('%' . $rhs . '%'));
2010: } else {
2011: return sprintf('(' . $query . ' OR ' . $query . ')',
2012: $lhs,
2013: $dbh->quote($rhs . '%'),
2014: $lhs,
2015: $dbh->quote('% ' . $rhs . '%'));
2016: }
2017: }
2018:
2019: default:
2020: if ($bind) {
2021: return array($lhs . ' ' . self::escapePrepare($op) . ' ?', array($rhs));
2022: } else {
2023: return $lhs . ' ' . $op . ' ' . $dbh->quote($rhs);
2024: }
2025: }
2026: }
2027:
2028: 2029: 2030: 2031: 2032: 2033: 2034: 2035:
2036: static public function escapePrepare($query)
2037: {
2038: return preg_replace('/[?!&]/', '\\\\$0', $query);
2039: }
2040: }
2041: