1: <?php
2: /**
3: * This class provides attributes methods for any existing SQL class.
4: *
5: * Copyright 1999-2012 Horde LLC (http://www.horde.org/)
6: *
7: * See the enclosed file COPYING for license information (LGPL). If you
8: * did not receive this file, see http://www.horde.org/licenses/lgpl21.
9: *
10: * @author Chuck Hagenbuch <chuck@horde.org>
11: * @package Hylax
12: */
13: class Hylax_SQL_Attributes {
14:
15: /**
16: * The PEAR::DB object to run queries with.
17: *
18: * @var DB
19: */
20: var $_db;
21:
22: /**
23: * Parameters to use when generating queries:
24: * id_column - The primary id column to use in joins.
25: * primary_table - The main table name.
26: * attribute_table - The table that the attributes are stored in.
27: *
28: * @var array
29: */
30: var $_params = array();
31:
32: /**
33: * The number of copies of the attributes table that we need to join on in
34: * the current query.
35: *
36: * @var integer
37: */
38: var $_table_count = 1;
39:
40: /**
41: * Constructor.
42: *
43: * @param DB $dbh A PEAR::DB object.
44: * @param array $params The id column, table names, etc.
45: */
46: function Hylax_SQL_Attributes($dbh, $params)
47: {
48: $this->_db = $dbh;
49: $this->_params = $params;
50: }
51:
52: /**
53: * Returns all attributes for a given id or multiple ids.
54: *
55: * @param integer | array $id The id to fetch or an array of ids.
56: *
57: * @return array A hash of attributes, or a multi-level hash
58: * of ids => their attributes.
59: */
60: function getAttributes($id)
61: {
62: if (is_array($id)) {
63: $query = sprintf('SELECT %1$s, attribute_name as name, attribute_key as "key", attribute_value as value FROM %2$s WHERE %1$s IN (%3$s)',
64: $this->_params['id_column'],
65: $this->_params['attribute_table'],
66: implode(', ', $id));
67:
68: Horde::logMessage('SQL Query by Hylax_SQL_Attributes::getAttributes(): ' . $query, 'DEBUG');
69: $rows = $this->_db->getAll($query, DB_FETCHMODE_ASSOC);
70: if (is_a($rows, 'PEAR_Error')) {
71: return $rows;
72: }
73:
74: $id_column = $this->_params['id_column'];
75: $data = array();
76: foreach ($rows as $row) {
77: if (empty($data[$row[$id_column]])) {
78: $data[$row[$id_column]] = array();
79: }
80: $data[$row[$id_column]][] = array('name' => $row['name'],
81: 'key' => $row['key'],
82: 'value' => $row['value']);
83: }
84: return $data;
85: } else {
86: $query = sprintf('SELECT %1$s, attribute_name as name, attribute_key as "key", attribute_value as value FROM %2$s WHERE %1$s = %3$s',
87: $this->_params['id_column'],
88: $this->_params['attribute_table'],
89: (int)$id);
90: Horde::logMessage('SQL Query by Hylax_SQL_Attributes::getAttributes(): ' . $query, 'DEBUG');
91: return $this->_db->getAll($query, DB_FETCHMODE_ASSOC);
92: }
93: }
94:
95: /**
96: * Return a set of ids based on a set of attribute criteria.
97: *
98: * @param array $criteria The array of criteria. Example:
99: * $criteria['OR'] = array(
100: * array('field' => 'name',
101: * 'op' => '=',
102: * 'test' => 'foo'),
103: * array('field' => 'name',
104: * 'op' => '=',
105: * 'test' => 'bar'));
106: * This would return all ids for which the field
107: * attribute_name is either 'foo' or 'bar'.
108: */
109: function getByAttributes($criteria)
110: {
111: if (!count($criteria)) {
112: return array();
113: }
114:
115: /* Build the query. */
116: $this->_table_count = 1;
117: $query = '';
118: foreach ($criteria as $key => $vals) {
119: if ($key == 'OR' || $key == 'AND') {
120: if (!empty($query)) {
121: $query .= ' ' . $key . ' ';
122: }
123: $query .= '(' . $this->_buildAttributeQuery($key, $vals) . ')';
124: }
125: }
126:
127: /* Build the FROM/JOIN clauses. */
128: $joins = array();
129: $pairs = array();
130: for ($i = 1; $i <= $this->_table_count; $i++) {
131: $joins[] = sprintf('LEFT JOIN %1$s a%2$s ON a%2$s.%3$s = m.%3$s',
132: $this->_params['attribute_table'],
133: $i,
134: $this->_params['id_column']);
135:
136: $pairs[] = 'AND a1.attribute_name = a' . $i . '.attribute_name';
137: }
138: $joins = implode(' ', $joins);
139: $pairs = implode(' ', $pairs);
140:
141: $query = sprintf('SELECT DISTINCT a1.%s FROM %s m %s WHERE %s %s',
142: $this->_params['id_column'],
143: $this->_params['primary_table'],
144: $joins,
145: $query,
146: $pairs);
147:
148: Horde::logMessage('SQL Query by Hylax_SQL_Attributes::getByAttributes(): ' . $query, 'DEBUG');
149:
150: return $this->_db->getCol($query);
151: }
152:
153: /**
154: * Given a new attribute set and an id, insert each into the DB. If
155: * anything fails in here, rollback the transaction, return the relevant
156: * error and bail out.
157: *
158: * @param integer $id The id of the record for which attributes are
159: * being inserted.
160: * @param array $attributes An hash containing the attributes.
161: */
162: function insertAttributes($id, $attributes)
163: {
164: foreach ($attributes as $attr) {
165: $query = 'INSERT INTO ' . $this->_params['attribute_table'] .
166: ' (' . $this->_params['id_column'] . ', attribute_name,' .
167: ' attribute_key, attribute_value) VALUES (?, ?, ?, ?)';
168: $values = array((int)$id,
169: $attr['name'],
170: $attr['key'],
171: $attr['value']);
172:
173: Horde::logMessage('SQL Query by Hylax_SQL_Attributes::insertAttributes(): ' . $query, 'DEBUG');
174:
175: $result = $this->_db->query($query, $values);
176: if (is_a($result, 'PEAR_Error')) {
177: $this->_db->rollback();
178: $this->_db->autoCommit(true);
179: return $result;
180: }
181: }
182:
183: /* Commit the transaction, and turn autocommit back on. */
184: $result = $this->_db->commit();
185: $this->_db->autoCommit(true);
186: }
187:
188: /**
189: * Given an id, delete all attributes for that id from the
190: * attributes table.
191: *
192: * @param integer $id The id of the record for which attributes are being
193: * deleted.
194: */
195: function deleteAttributes($id)
196: {
197: /* Delete attributes. */
198: $query = sprintf('DELETE FROM %s WHERE %s = %s',
199: $this->_params['attribute_table'],
200: $this->_params['id_column'],
201: (int)$id);
202:
203: Horde::logMessage('SQL Query by Hylax_SQL_Attributes::deleteAttributes(): ' . $query, 'DEBUG');
204: $result = $this->_db->query($query);
205: if (is_a($result, 'PEAR_Error')) {
206: return $result;
207: }
208:
209: return true;
210: }
211:
212: /**
213: * Given an id, update all attributes for that id in the attributes table
214: * with the new attributes.
215: *
216: * @param integer $id The id of the record for which attributes are
217: * being deleted.
218: * @param array $attributes An hash containing the attributes.
219: */
220: function updateAttributes($id, $attributes)
221: {
222: /* Delete the old attributes. */
223: $result = $this->deleteAttributes($id);
224: if (is_a($result, 'PEAR_Error')) {
225: return $result;
226: }
227:
228: /* Insert the new attribute set. */
229: $result = $this->insertAttributes($id, $attributes);
230: return $result;
231: }
232:
233: /**
234: * Build a piece of an attribute query.
235: *
236: * @param string $glue The glue to join the criteria (OR/AND).
237: * @param array $criteria The array of criteria.
238: * @param boolean $join Should we join on a clean attributes table?
239: *
240: * @return string An SQL fragment.
241: */
242: function _buildAttributeQuery($glue, $criteria, $join = false)
243: {
244: /* Initialize the clause that we're building. */
245: $clause = '';
246:
247: /* Get the table alias to use for this set of criteria. */
248: if ($join) {
249: $alias = $this->_getAlias(true);
250: } else {
251: $alias = $this->_getAlias();
252: }
253:
254: foreach ($criteria as $key => $vals) {
255: if (!empty($vals['OR']) || !empty($vals['AND'])) {
256: if (!empty($clause)) {
257: $clause .= ' ' . $glue . ' ';
258: }
259: $clause .= '(' . $this->_buildAttributeQuery($glue, $vals) . ')';
260: } elseif (!empty($vals['JOIN'])) {
261: if (!empty($clause)) {
262: $clause .= ' ' . $glue . ' ';
263: }
264: $clause .= $this->_buildAttributeQuery($glue, $vals['JOIN'], true);
265: } else {
266: if (isset($vals['field'])) {
267: if (!empty($clause)) {
268: $clause .= ' ' . $glue . ' ';
269: }
270: $clause .= Horde_Sql::buildClause($this->_db, $alias . '.attribute_' . $vals['field'], $vals['op'], $vals['test']);
271: } else {
272: foreach ($vals as $test) {
273: if (!empty($clause)) {
274: $clause .= ' ' . $key . ' ';
275: }
276: $clause .= Horde_Sql::buildClause($this->_db, $alias . '.attribute_' . $test['field'], $test['op'], $test['test']);
277: }
278: }
279: }
280: }
281:
282: return $clause;
283: }
284:
285: /**
286: * Get an alias to an attributes table, incrementing it if
287: * necessary.
288: *
289: * @param boolean $increment Increment the alias count? Defaults to false.
290: */
291: function _getAlias($increment = false)
292: {
293: static $seen = array();
294:
295: if ($increment && !empty($seen[$this->_table_count])) {
296: $this->_table_count++;
297: }
298:
299: $seen[$this->_table_count] = true;
300: return 'a' . $this->_table_count;
301: }
302:
303: }
304: