3
* Turba directory driver implementation for the Horde_Db database abstraction
6
* Copyright 2010-2013 Horde LLC (http://www.horde.org/)
8
* See the enclosed file LICENSE for license information (ASL). If you did
9
* did not receive this file, see http://www.horde.org/licenses/apache.
11
* @author Jon Parise <jon@csh.rit.edu>
12
* @author Michael J. Rubinsky <mrubinsk@horde.org>
14
* @license http://www.horde.org/licenses/apache ASL
17
class Turba_Driver_Sql extends Turba_Driver
20
* What can this backend do?
24
protected $_capabilities = array(
25
'delete_addressbook' => true,
34
protected $_countCache = array();
37
* Handle for the current database connection.
39
* @var Horde_Db_Adapter
46
* @param string $name The source name
47
* @param array $params Additional parameters needed:
49
* 'db' - (Horde_Db_Adapter) A DB Adapter object.
52
public function __construct($name = '', array $params = array())
54
if (empty($params['db'])) {
55
throw new InvalidArgumentException('Missing required Horde_Db_Adapter object');
57
$this->_db = $params['db'];
60
parent::__construct($name, $params);
64
* Returns the number of contacts of the current user in this address book.
66
* @return integer The number of contacts that the user owns.
68
public function count()
70
$test = $this->getContactOwner();
71
if (!isset($this->_countCache[$test])) {
72
/* Build up the full query. */
73
$query = 'SELECT COUNT(*) FROM ' . $this->_params['table'] .
74
' WHERE ' . $this->toDriver('__owner') . ' = ?';
75
$values = array($test);
79
$this->_countCache[$test] = $this->_db->selectValue($query, $values);
80
} catch (Horde_Db_Exception $e) {
81
$this->_countCache[$test] = 0;
85
return $this->_countCache[$test];
89
* Searches the SQL database with the given criteria and returns a
90
* filtered list of results. If the criteria parameter is an empty array,
91
* all records will be returned.
93
* @param array $criteria Array containing the search criteria.
94
* @param array $fields List of fields to return.
95
* @param array $blobFields TODO
96
* @param boolean $count_only Only return the count of matching entries,
97
* not the entries themselves.
99
* @return array Hash containing the search results.
100
* @throws Turba_Exception
102
protected function _search(array $criteria, array $fields, array $blobFields = array(), $count_only = false)
104
return $this->_internalSearch($criteria, $fields, $blobFields, array(), $count_only);
108
* Searches the SQL database with the given criteria and returns a
109
* filtered list of results. If the criteria parameter is an empty array,
110
* all records will be returned.
112
* @param array $criteria Array containing the search criteria.
113
* @param array $fields List of fields to return.
114
* @param array $blobFields TODO
115
* @param array $appendWhere An additional where clause to append.
116
* Array should contain 'sql' and 'params'
117
* params are used as bind parameters.
118
* @param boolean $count_only Only return the count of matching entries,
119
* not the entries themselves.
121
* @return mixed array|integer Hash containing the search results or the
122
* count of matching entries.
123
* @throws Turba_Exception
125
protected function _internalSearch(array $criteria, array $fields, $blobFields = array(), $appendWhere = array(), $count_only = false)
127
/* Build the WHERE clause. */
131
if (count($criteria) || !empty($this->_params['filter'])) {
132
foreach ($criteria as $key => $vals) {
133
if ($key == 'OR' || $key == 'AND') {
134
if (!empty($where)) {
135
$where .= ' ' . $key . ' ';
137
$binds = $this->_buildSearchQuery($key, $vals);
138
$where .= '(' . $binds[0] . ')';
139
$values += $binds[1];
142
$where = ' WHERE ' . $where;
143
if (count($criteria) && !empty($this->_params['filter'])) {
146
if (!empty($this->_params['filter'])) {
147
$where .= $this->_params['filter'];
149
if (count($appendWhere)) {
150
$where .= ' AND ' . $appendWhere['sql'];
151
$values = array_merge($values, $appendWhere['params']);
153
} elseif (count($appendWhere)) {
154
$where = ' WHERE ' . $appendWhere['sql'];
155
$values = array_merge($values, $appendWhere['params']);
158
/* Build up the full query. */
160
$query = 'SELECT COUNT(*) FROM ' . $this->_params['table'] . $where;
162
return $this->_db->selectValue($query, $values);
163
} catch (Horde_Db_Exception $e) {
164
throw new Turba_Exception($e);
167
$query = 'SELECT ' . implode(', ', $fields) . ' FROM ' . $this->_params['table'] . $where;
169
return $this->_parseRead($blobFields, $this->_db->selectAll($query, $values));
170
} catch (Horde_Db_Exception $e) {
171
throw new Turba_Exception($e);
176
protected function _parseRead($blobFields, $result)
179
$columns = $this->_db->columns($this->_params['table']);
181
foreach ($result as $row) {
184
foreach ($row as $field => $val) {
185
if (isset($blobFields[$field])) {
186
$entry[$field] = $columns[$field]->binaryToString($val);
188
$entry[$field] = $this->_convertFromDriver($val);
199
* Prepares field lists for searchDuplicates().
201
* @param array $array A list of field names.
203
* @return array A prepared list of field names.
205
protected function _buildFields($array)
207
foreach ($array as &$entry) {
208
$entry = is_array($entry)
209
? implode(',', $this->_buildFields($entry))
217
* Builds the WHERE conditions for searchDuplicates().
219
* @param array $array A list of field names.
221
* @return array A list of WHERE conditions.
223
protected function _buildWhere($array)
225
foreach ($array as &$entry) {
226
if (is_array($entry)) {
227
$entry = reset($entry);
229
$entry = 'a1.' . $entry . ' IS NOT NULL AND a1.' . $entry . ' <> \'\'';
236
* Builds the JOIN conditions for searchDuplicates().
238
* @param array $array A list of field names.
240
* @return array A list of JOIN conditions.
242
protected function _buildJoin($array)
244
foreach ($array as &$entry) {
245
$entry = is_array($entry)
246
? implode(' AND ', $this->_buildJoin($entry))
247
: 'a1.' . $entry . ' = a2.' . $entry;
254
* Searches the current address book for duplicate entries.
256
* Duplicates are determined by comparing email and name or last name and
259
* @return array A hash with the following format:
261
* array('name' => array('John Doe' => Turba_List, ...), ...)
263
* @throws Turba_Exception
265
public function searchDuplicates()
267
$owner = $this->getContactOwner();
269
if (is_array($this->map['name'])) {
270
if (in_array('lastname', $this->map['name']['fields']) &&
271
isset($this->map['lastname'])) {
272
$field = array($this->map['lastname']);
273
if (in_array('firstname', $this->map['name']['fields']) &&
274
isset($this->map['firstname'])) {
275
$field[] = $this->map['firstname'];
280
$fields[] = $this->map['name'];
282
if (isset($this->map['email'])) {
283
$fields[] = $this->map['email'];
285
$nameFormat = $GLOBALS['prefs']->getValue('name_format');;
286
if ($nameFormat != 'first_last' && $nameFormat != 'last_first') {
287
$nameFormat = 'first_last';
290
$order = $this->_buildFields($fields);
291
$joins = $this->_buildJoin($fields);
292
$where = $this->_buildWhere($fields);
294
$duplicates = array();
295
for ($i = 0; $i < count($joins); $i++) {
296
/* Build up the full query. */
297
$query = sprintf('SELECT DISTINCT a1.%s,%s FROM %s a1 JOIN %s a2 ON %s AND a1.%s <> a2.%s WHERE a1.%s = ? AND a2.%s = ? AND %s ORDER BY %s',
300
$this->_params['table'],
301
$this->_params['table'],
305
$this->map['__owner'],
306
$this->map['__owner'],
312
$ids = $this->_db->selectValues($query, array($owner, $owner));
313
} catch (Horde_Db_Exception $e) {
314
throw new Turba_Exception($e);
319
: array_search($fields[$i], $this->map);
322
foreach ($ids as $id) {
323
$contact = $this->getObject($id);
324
$value = $contact->getValue($field);
325
if ($field == 'name') {
326
$value = Turba::formatName($contact, $nameFormat);
329
if ($field == 'email') {
330
$value = Horde_String::lower($value);
332
if (!isset($contacts[$value])) {
333
$contacts[$value] = new Turba_List();
335
$contacts[$value]->insert($contact);
338
$duplicates[$field] = $contacts;
346
* Reads the given data from the SQL database and returns the results.
348
* @param string $key The primary key field to use.
349
* @param mixed $ids The ids of the contacts to load.
350
* @param string $owner Only return contacts owned by this user.
351
* @param array $fields List of fields to return.
352
* @param array $blobFields Array of fields containing binary data.
354
* @return array Hash containing the search results.
355
* @throws Turba_Exception
357
protected function _read($key, $ids, $owner, array $fields,
358
array $blobFields = array())
363
if (is_array($ids)) {
368
foreach ($ids as $id) {
369
$in .= empty($in) ? '?' : ', ?';
370
$values[] = $this->_convertToDriver($id);
372
$where = $key . ' IN (' . $in . ')';
374
$where = $key . ' = ?';
375
$values[] = $this->_convertToDriver($ids);
377
if (isset($this->map['__owner'])) {
378
$where .= ' AND ' . $this->map['__owner'] . ' = ?';
379
$values[] = $this->_convertToDriver($owner);
381
if (!empty($this->_params['filter'])) {
382
$where .= ' AND ' . $this->_params['filter'];
385
$query = 'SELECT ' . implode(', ', $fields) . ' FROM '
386
. $this->_params['table'] . ' WHERE ' . $where;
389
return $this->_parseRead($blobFields, $this->_db->selectAll($query, $values));
390
} catch (Horde_Db_Exception $e) {
391
throw new Turba_Exception($e);
396
* Adds the specified object to the SQL database.
400
* @throws Turba_Exception
402
protected function _add(array $attributes, array $blob_fields = array())
404
list($fields, $values) = $this->_prepareWrite($attributes, $blob_fields);
405
$query = 'INSERT INTO ' . $this->_params['table']
406
. ' (' . implode(', ', $fields) . ')'
407
. ' VALUES (' . str_repeat('?, ', count($values) - 1) . '?)';
410
$this->_db->insert($query, $values);
411
} catch (Horde_Db_Exception $e) {
412
throw new Turba_Exception($e);
416
protected function _prepareWrite($attributes, $blob_fields)
418
$fields = $values = array();
420
foreach ($attributes as $field => $value) {
423
if (!empty($value) && isset($blob_fields[$field])) {
424
$values[] = new Horde_Db_Value_Binary($value);
426
$values[] = $this->_convertToDriver($value);
430
return array($fields, $values);
436
protected function _canAdd()
442
* Deletes the specified object from the SQL database.
444
* @throws Turba_Exception
446
protected function _delete($object_key, $object_id)
448
$query = 'DELETE FROM ' . $this->_params['table'] .
449
' WHERE ' . $object_key . ' = ?';
450
$values = array($object_id);
453
$this->_db->delete($query, $values);
454
} catch (Horde_Db_Exception $e) {
455
throw new Turba_Exception($e);
460
* Deletes all contacts from a specific address book.
462
* @param string $sourceName The source to remove all contacts from.
464
* @return array An array of UIDs
465
* @throws Turba_Exception
467
protected function _deleteAll($sourceName = null)
469
if (!$GLOBALS['registry']->getAuth()) {
470
throw new Turba_Exception('Permission denied');
474
$values = empty($sourceName)
475
? array($GLOBALS['registry']->getAuth())
476
: array($sourceName);
478
/* Need a list of UIDs so we can notify History */
479
$query = 'SELECT '. $this->map['__uid'] . ' FROM '
480
. $this->_params['table'] . ' WHERE owner_id = ?';
483
$ids = $this->_db->selectValues($query, $values);
484
} catch (Horde_Db_Exception $e) {
485
throw new Turba_Exception($e);
488
/* Do the deletion */
489
$query = 'DELETE FROM ' . $this->_params['table'] . ' WHERE owner_id = ?';
492
$this->_db->delete($query, $values);
493
} catch (Horde_Db_Exception $e) {
494
throw new Turba_Exception($e);
501
* Saves the specified object in the SQL database.
503
* @param Turba_Object $object The object to save.
505
* @return string The object id, possibly updated.
506
* @throws Turba_Exception
508
function _save(Turba_Object $object)
510
list($object_key, $object_id) = each($this->toDriverKeys(array('__key' => $object->getValue('__key'))));
511
$attributes = $this->toDriverKeys($object->getAttributes());
512
$blob_fields = $this->toDriverKeys($this->getBlobs());
514
$where = $object_key . ' = ?';
515
unset($attributes[$object_key]);
517
list($fields, $values) = $this->_prepareWrite($attributes, $blob_fields);
519
$values[] = $object_id;
521
$query = 'UPDATE ' . $this->_params['table'] . ' SET ' . implode(' = ?, ', $fields) . ' = ? WHERE ' . $where;
524
$this->_db->update($query, $values);
525
} catch (Horde_Db_Exception $e) {
526
throw new Turba_Exception($e);
533
* Creates an object key for a new object.
535
* @param array $attributes The attributes (in driver keys) of the
536
* object being added.
538
* @return string A unique ID for the new object.
540
protected function _makeKey(array $attributes)
542
return strval(new Horde_Support_Randomid());
546
* Builds a piece of a search query.
548
* @param string $glue The glue to join the criteria (OR/AND).
549
* @param array $criteria The array of criteria.
551
* @return array An SQL fragment and a list of values suitable for binding
554
protected function _buildSearchQuery($glue, array $criteria)
559
foreach ($criteria as $key => $vals) {
560
if (!empty($vals['OR']) || !empty($vals['AND'])) {
561
if (!empty($clause)) {
562
$clause .= ' ' . $glue . ' ';
564
$binds = $this->_buildSearchQuery(!empty($vals['OR']) ? 'OR' : 'AND', $vals);
565
$clause .= '(' . $binds[0] . ')';
566
$values = array_merge($values, $binds[1]);
568
if (isset($vals['field'])) {
569
if (!empty($clause)) {
570
$clause .= ' ' . $glue . ' ';
572
$rhs = $this->_convertToDriver($vals['test']);
573
$binds = $this->_db->buildClause($vals['field'], $vals['op'], $rhs, true, array('begin' => !empty($vals['begin'])));
574
if (is_array($binds)) {
575
$clause .= $binds[0];
576
$values = array_merge($values, $binds[1]);
581
foreach ($vals as $test) {
582
if (!empty($test['OR']) || !empty($test['AND'])) {
583
if (!empty($clause)) {
584
$clause .= ' ' . $glue . ' ';
586
$binds = $this->_buildSearchQuery(!empty($vals['OR']) ? 'OR' : 'AND', $test);
587
$clause .= '(' . $binds[0] . ')';
588
$values = array_merge($values, $binds[1]);
590
if (!empty($clause)) {
591
$clause .= ' ' . $key . ' ';
593
$rhs = $this->_convertToDriver($test['test']);
594
if ($rhs == '' && $test['op'] == '=') {
595
$clause .= '(' . $this->_db->buildClause($test['field'], '=', $rhs) . ' OR ' . $test['field'] . ' IS NULL)';
597
$binds = $this->_db->buildClause($test['field'], $test['op'], $rhs, true, array('begin' => !empty($test['begin'])));
598
if (is_array($binds)) {
599
$clause .= $binds[0];
600
$values = array_merge($values, $binds[1]);
611
return array($clause, $values);
615
* Converts a value from the driver's charset to the default charset.
617
* @param mixed $value A value to convert.
619
* @return mixed The converted value.
621
protected function _convertFromDriver($value)
623
return Horde_String::convertCharset($value, $this->_db->getOption('charset'), 'UTF-8');
627
* Converts a value from the default charset to the driver's charset.
629
* @param mixed $value A value to convert.
631
* @return mixed The converted value.
633
protected function _convertToDriver($value)
635
return Horde_String::convertCharset($value, 'UTF-8', $this->_db->getOption('charset'));
639
* Remove all entries owned by the specified user.
641
* @param string $user The user's data to remove.
643
* @throws Horde_Exception_PermissionDenied
645
public function removeUserData($user)
647
// Make sure we are being called by an admin.
648
if (!$GLOBALS['registry']->isAdmin()) {
649
throw new Horde_Exception_PermissionDenied(_("Permission denied"));
652
$this->_deleteAll($user);
656
* Obtain Turba_List of items to get TimeObjects out of.
658
* @param Horde_Date $start The starting date.
659
* @param Horde_Date $end The ending date.
660
* @param string $field The address book field containing the
661
* timeObject information (birthday, anniversary)
663
* @return Turba_List Object list.
664
* @throws Turba_Exception
666
public function getTimeObjectTurbaList(Horde_Date $start, Horde_Date $end, $field)
668
$t_object = $this->toDriver($field);
669
$criteria = $this->makesearch(
670
array('__owner' => $this->getContactOwner()),
672
array($this->toDriver('__owner') => true),
675
// Limit to entries that actually contain a birthday and that are in the
676
// date range we are looking for.
677
$criteria['AND'][] = array(
678
'field' => $t_object,
683
if ($start->year == $end->year) {
684
$start = sprintf('%02d-%02d', $start->month, $start->mday);
685
$end = sprintf('%02d-%02d', $end->month, $end->mday);
686
$where = array('sql' => $t_object . ' IS NOT NULL AND SUBSTR('
687
. $t_object . ', 6, 5) BETWEEN ? AND ?',
688
'params' => array($start, $end));
691
$diff = ($end->month + 12) - $start->month;
692
$newDate = new Horde_Date(array(
693
'month' => $start->month,
694
'mday' => $start->mday,
695
'year' => $start->year
697
for ($i = 0; $i <= $diff; ++$i) {
698
$months[] = sprintf('%02d', $newDate->month++);
700
$where = array('sql' => $t_object . ' IS NOT NULL AND SUBSTR('
701
. $t_object . ', 6, 2) IN ('
702
. str_repeat('?,', count($months) - 1) . '?)',
703
'params' => $months);
707
'__key', '__type', '__owner', 'name', 'birthday', 'category',
712
foreach ($fields_pre as $field) {
713
$result = $this->toDriver($field);
714
if (is_array($result)) {
715
foreach ($result as $composite_field) {
716
$composite_result = $this->toDriver($composite_field);
717
if ($composite_result) {
718
$fields[] = $composite_result;
726
return $this->_toTurbaObjects($this->_internalSearch($criteria, $fields, array(), $where));
added
removed
turba-4.1.0/lib/Driver/Sql.php
