Overview

Packages

  • Hylax
  • None

Classes

  • Hylax
  • Hylax_Driver
  • Hylax_Driver_hylafax
  • Hylax_Driver_spandsp
  • Hylax_Image
  • Hylax_SQL_Attributes
  • Hylax_Storage
  • Hylax_Storage_sql
  • Overview
  • Package
  • Class
  • Tree
  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: 
API documentation generated by ApiGen