1
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
|
<?php
/**
* This class provides attributes methods for any existing SQL class.
*
* $Horde: framework/SQL/SQL/Attributes.php,v 1.14.10.9 2006/01/01 21:28:33 jan Exp $
*
* Copyright 1999-2006 Chuck Hagenbuch <chuck@horde.org>
*
* See the enclosed file COPYING for license information (LGPL). If you
* did not receive this file, see http://www.fsf.org/copyleft/lgpl.html.
*
* @author Chuck Hagenbuch <chuck@horde.org>
* @since Horde 2.2
* @package Horde_SQL
*/
class Horde_SQL_Attributes {
/**
* The PEAR::DB object to run queries with.
*
* @var DB
*/
var $_db;
/**
* Parameters to use when generating queries:
* id_column - The primary id column to use in joins.
* primary_table - The main table name.
* attribute_table - The table that the attributes are stored in.
*
* @var array
*/
var $_params = array();
/**
* The number of copies of the attributes table that we need to join on in
* the current query.
*
* @var integer
*/
var $_table_count = 1;
/**
* Constructor.
*
* @param DB $dbh A PEAR::DB object.
* @param array $params The id column, table names, etc.
*/
function Horde_SQL_Attributes($dbh, $params)
{
$this->_db = $dbh;
$this->_params = $params;
}
/**
* Returns all attributes for a given id or multiple ids.
*
* @param integer | array $id The id to fetch or an array of ids.
*
* @return array A hash of attributes, or a multi-level hash
* of ids => their attributes.
*/
function getAttributes($id)
{
if (is_array($id)) {
$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)',
$this->_params['id_column'],
$this->_params['attribute_table'],
implode(', ', $id));
Horde::logMessage('SQL Query by Horde_SQL_Attributes::getAttributes(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
$rows = $this->_db->getAll($query, DB_FETCHMODE_ASSOC);
if (is_a($rows, 'PEAR_Error')) {
return $rows;
}
$id_column = $this->_params['id_column'];
$data = array();
foreach ($rows as $row) {
if (empty($data[$row[$id_column]])) {
$data[$row[$id_column]] = array();
}
$data[$row[$id_column]][] = array('name' => $row['name'],
'key' => $row['key'],
'value' => $row['value']);
}
return $data;
} else {
$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',
$this->_params['id_column'],
$this->_params['attribute_table'],
(int)$id);
Horde::logMessage('SQL Query by Horde_SQL_Attributes::getAttributes(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
return $this->_db->getAll($query, DB_FETCHMODE_ASSOC);
}
}
/**
* Return a set of ids based on a set of attribute criteria.
*
* @param array $criteria The array of criteria. Example:
* $criteria['OR'] = array(
* array('field' => 'name',
* 'op' => '=',
* 'test' => 'foo'),
* array('field' => 'name',
* 'op' => '=',
* 'test' => 'bar'));
* This would return all ids for which the field
* attribute_name is either 'foo' or 'bar'.
*/
function getByAttributes($criteria)
{
if (!count($criteria)) {
return array();
}
/* Build the query. */
$this->_table_count = 1;
$query = '';
foreach ($criteria as $key => $vals) {
if ($key == 'OR' || $key == 'AND') {
if (!empty($query)) {
$query .= ' ' . $key . ' ';
}
$query .= '(' . $this->_buildAttributeQuery($key, $vals) . ')';
}
}
/* Build the FROM/JOIN clauses. */
$joins = array();
$pairs = array();
for ($i = 1; $i <= $this->_table_count; $i++) {
$joins[] = sprintf('LEFT JOIN %1$s a%2$s ON a%2$s.%3$s = m.%3$s',
$this->_params['attribute_table'],
$i,
$this->_params['id_column']);
$pairs[] = 'AND a1.attribute_name = a' . $i . '.attribute_name';
}
$joins = implode(' ', $joins);
$pairs = implode(' ', $pairs);
$query = sprintf('SELECT DISTINCT a1.%s FROM %s m %s WHERE %s %s',
$this->_params['id_column'],
$this->_params['primary_table'],
$joins,
$query,
$pairs);
Horde::logMessage('SQL Query by Horde_SQL_Attributes::getByAttributes(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
return $this->_db->getCol($query);
}
/**
* Given a new attribute set and an id, insert each into the DB. If
* anything fails in here, rollback the transaction, return the relevant
* error and bail out.
*
* @param integer $id The id of the record for which attributes are
* being inserted.
* @param array $attributes An hash containing the attributes.
*/
function insertAttributes($id, $attributes)
{
foreach ($attributes as $attr) {
$query = 'INSERT INTO ' . $this->_params['attribute_table'] .
' (' . $this->_params['id_column'] . ', attribute_name,' .
' attribute_key, attribute_value) VALUES (?, ?, ?, ?)';
$values = array((int)$id,
$attr['name'],
$attr['key'],
$attr['value']);
Horde::logMessage('SQL Query by Horde_SQL_Attributes::insertAttributes(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
$result = $this->_db->query($query, $values);
if (is_a($result, 'PEAR_Error')) {
$this->_db->rollback();
$this->_db->autoCommit(true);
return $result;
}
}
/* Commit the transaction, and turn autocommit back on. */
$result = $this->_db->commit();
$this->_db->autoCommit(true);
}
/**
* Given an id, delete all attributes for that id from the
* attributes table.
*
* @param integer $id The id of the record for which attributes are being
* deleted.
*/
function deleteAttributes($id)
{
/* Delete attributes. */
$query = sprintf('DELETE FROM %s WHERE %s = %s',
$this->_params['attribute_table'],
$this->_params['id_column'],
(int)$id);
Horde::logMessage('SQL Query by Horde_SQL_Attributes::deleteAttributes(): ' . $query, __FILE__, __LINE__, PEAR_LOG_DEBUG);
$result = $this->_db->query($query);
if (is_a($result, 'PEAR_Error')) {
return $result;
}
return true;
}
/**
* Given an id, update all attributes for that id in the attributes table
* with the new attributes.
*
* @param integer $id The id of the record for which attributes are
* being deleted.
* @param array $attributes An hash containing the attributes.
*/
function updateAttributes($id, $attributes)
{
/* Delete the old attributes. */
$result = $this->deleteAttributes($id);
if (is_a($result, 'PEAR_Error')) {
return $result;
}
/* Insert the new attribute set. */
$result = $this->insertAttributes($id, $attributes);
return $result;
}
/**
* Build a piece of an attribute query.
*
* @param string $glue The glue to join the criteria (OR/AND).
* @param array $criteria The array of criteria.
* @param boolean $join Should we join on a clean attributes table?
*
* @return string An SQL fragment.
*/
function _buildAttributeQuery($glue, $criteria, $join = false)
{
require_once 'Horde/SQL.php';
/* Initialize the clause that we're building. */
$clause = '';
/* Get the table alias to use for this set of criteria. */
if ($join) {
$alias = $this->_getAlias(true);
} else {
$alias = $this->_getAlias();
}
foreach ($criteria as $key => $vals) {
if (!empty($vals['OR']) || !empty($vals['AND'])) {
if (!empty($clause)) {
$clause .= ' ' . $glue . ' ';
}
$clause .= '(' . $this->_buildAttributeQuery($glue, $vals) . ')';
} elseif (!empty($vals['JOIN'])) {
if (!empty($clause)) {
$clause .= ' ' . $glue . ' ';
}
$clause .= $this->_buildAttributeQuery($glue, $vals['JOIN'], true);
} else {
if (isset($vals['field'])) {
if (!empty($clause)) {
$clause .= ' ' . $glue . ' ';
}
$clause .= Horde_SQL::buildClause($this->_db, $alias . '.attribute_' . $vals['field'], $vals['op'], $vals['test']);
} else {
foreach ($vals as $test) {
if (!empty($clause)) {
$clause .= ' ' . $key . ' ';
}
$clause .= Horde_SQL::buildClause($this->_db, $alias . '.attribute_' . $test['field'], $test['op'], $test['test']);
}
}
}
}
return $clause;
}
/**
* Get an alias to an attributes table, incrementing it if
* necessary.
*
* @param boolean $increment Increment the alias count? Defaults to false.
*/
function _getAlias($increment = false)
{
static $seen = array();
if ($increment && !empty($seen[$this->_table_count])) {
$this->_table_count++;
}
$seen[$this->_table_count] = true;
return 'a' . $this->_table_count;
}
/**
* Attempts to return a reference to a concrete SQL Attributes
* instance based on parameters passed. It will only create a new
* instance if no Attributes instance with the same parameters
* currently exists.
*
* This should be used if multiple SQL attribute tables are
* required.
*
* This method must be invoked as: $var =
* &Horde_SQL_Attributes::singleton()
*
* @param DB $dbh An object pointing to a SQL database handle.
*
* @param array $params Parameters for the attributes table, consisting
* of the following keys:
* 'primary_table' - the main SQL table
* 'attribute_table' - the second table containing
* the attributes to the main
* table.
* 'id_column' - the name of the column with
* the ID or key field.
*/
function &singleton($dbh, $params)
{
static $instances;
if (!isset($instances)) {
$instances = array();
}
$signature = serialize($params);
if (!isset($instances[$signature])) {
$instances[$signature] = &new Horde_SQL_Attributes($dbh, $params);
}
return $instances[$signature];
}
}
|
