/
Sql.php
407 lines (374 loc) · 15.2 KB
/
Sql.php
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
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
<?php
/**
* Copyright 2003-2017 Horde LLC (http://www.horde.org/)
*
* See the enclosed file LICENSE for license information (LGPL). If you
* did not receive this file, see http://www.horde.org/licenses/lgpl21.
*
* @author Chuck Hagenbuch <chuck@horde.org>
* @category Horde
* @license http://www.horde.org/licenses/lgpl21 LGPL 2.1
* @package History
*/
/**
* The Horde_History_Sql class provides a method of tracking changes in Horde
* objects, stored in a SQL table.
*
* @author Chuck Hagenbuch <chuck@horde.org>
* @category Horde
* @copyright 2003-2017 Horde LLC
* @license http://www.horde.org/licenses/lgpl21 LGPL 2.1
* @package History
*/
class Horde_History_Sql extends Horde_History
{
/**
* Horde_Db_Adapter instance to manage the history.
*
* @var Horde_Db_Adapter
*/
protected $_db;
/**
* Constructor.
*
* @param string $auth The current user.
* @param Horde_Db_Adapter $db The database connection.
*/
public function __construct($auth, Horde_Db_Adapter $db)
{
parent::__construct($auth);
$this->_db = $db;
}
/**
* Returns the timestamp of the most recent change to $guid.
*
* @param string $guid The name of the history entry to retrieve.
* @param string $action An action: 'add', 'modify', 'delete', etc.
*
* @return integer The timestamp, or 0 if no matching entry is found.
* @throws Horde_History_Exception
*/
public function getActionTimestamp($guid, $action)
{
if (!is_string($guid) || !is_string($action)) {
throw new InvalidArgumentException('$guid and $action need to be strings!');
}
try {
$result = $this->_db->selectValue('SELECT MAX(history_ts) FROM horde_histories WHERE history_action = ? AND object_uid = ?', array($action, $guid));
} catch (Horde_Db_Exception $e) {
return 0;
}
return (int)$result;
}
/**
* Logs an event to an item's history log.
*
* Any other details about the event are passed in $attributes.
*
* @param Horde_History_Log $history The history item to add to.
* @param array $attributes The hash of name => value entries
* that describe this event.
* @param boolean $replaceAction If $attributes['action'] is already
* present in the item's history log,
* update that entry instead of creating
* a new one.
*
* @throws Horde_History_Exception
*/
protected function _log(Horde_History_Log $history, array $attributes,
$replaceAction = false)
{
/* If we want to replace an entry with the same action, try and find
* one. Track whether or not we succeed in $done, so we know whether or
* not to add the entry later. */
$done = false;
if ($replaceAction && !empty($attributes['action'])) {
foreach ($history as $entry) {
if (!empty($entry['action']) &&
$entry['action'] == $attributes['action']) {
$values = array(
$attributes['ts'],
$attributes['who'],
isset($attributes['desc']) ? $attributes['desc'] : null
);
unset($attributes['ts'], $attributes['who'], $attributes['desc'], $attributes['action']);
$values[] = $attributes
? serialize($attributes)
: null;
$values[] = $this->_nextModSeq();
$values[] = $entry['id'];
try {
$this->_db->update(
'UPDATE horde_histories SET history_ts = ?,' .
' history_who = ?,' .
' history_desc = ?,' .
' history_extra = ?,' .
' history_modseq = ? WHERE history_id = ?', $values
);
} catch (Horde_Db_Exception $e) {
throw new Horde_History_Exception($e);
}
$done = true;
break;
}
}
}
/* If we're not replacing by action, or if we didn't find an entry to
* replace, insert a new row. */
if (!$done) {
$values = array(
$history->uid,
$attributes['ts'],
$attributes['who'],
isset($attributes['desc']) ? $attributes['desc'] : null,
isset($attributes['action']) ? $attributes['action'] : null,
$this->_nextModSeq()
);
unset($attributes['ts'], $attributes['who'], $attributes['desc'], $attributes['action']);
$values[] = $attributes
? serialize($attributes)
: null;
try {
$this->_db->insert(
'INSERT INTO horde_histories (object_uid, history_ts, history_who, history_desc, history_action, history_modseq, history_extra)' .
' VALUES (?, ?, ?, ?, ?, ?, ?)', $values
);
} catch (Horde_Db_Exception $e) {
throw new Horde_History_Exception($e);
}
}
}
/**
* Returns a Horde_History_Log corresponding to the named history entry,
* with the data retrieved appropriately.
*
* @param string $guid The name of the history entry to retrieve.
*
* @return Horde_History_Log A Horde_History_Log object.
* @throws Horde_History_Exception
*/
public function _getHistory($guid)
{
try {
$rows = $this->_db->selectAll('SELECT * FROM horde_histories WHERE object_uid = ?', array($guid));
} catch (Horde_Db_Exception $e) {
throw new Horde_History_Exception($e);
}
return new Horde_History_Log($guid, $rows);
}
/**
* Finds history objects by timestamp, and optionally filtered on other
* fields as well.
*
* Note: For BC reasons, the results are returned keyed by the object UID,
* with a (fairly useless) history_id as the value. @todo This
* should be changed for Horde 6.
*
* @param string $cmp The comparison operator (<, >, <=, >=, or =) to
* check the timestamps with.
* @param integer $ts The timestamp to compare against.
* @param array $filters An array of additional (ANDed) criteria.
* Each array value should be an array with 3
* entries:
* - field: the history field being compared (i.e.
* 'action').
* - op: the operator to compare this field with.
* - value: the value to check for (i.e. 'add').
* @param string $parent The parent history to start searching at. If
* non-empty, will be searched for with a LIKE
* '$parent:%' clause.
*
* @return array An array of history object ids that have had at least one
* match for the given $filters. Will return empty array if
* none matched the criteria. If the same GUID has multiple
* matches withing the range requested, there is no
* guarantee which entry will be returned.
* @throws Horde_History_Exception
*/
public function _getByTimestamp($cmp, $ts, array $filters = array(),
$parent = null)
{
/* Build the timestamp test. */
$where = array("history_ts $cmp $ts");
/* Add additional filters, if there are any. */
try {
if ($filters) {
foreach ($filters as $filter) {
$where[] = 'history_' . $filter['field'] . ' ' . $filter['op'] . ' ' . $this->_db->quote($filter['value']);
}
}
if ($parent) {
$where[] = 'object_uid LIKE ' . $this->_db->quote($parent . ':%');
}
return $this->_db->selectAssoc('SELECT DISTINCT object_uid, history_id FROM horde_histories WHERE ' . implode(' AND ', $where));
} catch (Horde_Db_Exception $e) {
throw new Horde_History_Exception($e);
}
}
/**
* Returns history objects with changes during a modseq interval, and
* optionally filtered on other fields as well.
*
* Note: For BC reasons, the results are returned keyed by the object UID,
* with a (fairly useless) history_id as the value. @todo This
* should be changed for Horde 6.
*
* @param integer $start The (exclusive) start of the modseq range.
* @param integer $end The (inclusive) end of the modseq range.
* @param array $filters An array of additional (ANDed) criteria.
* Each array value should be an array with 3
* entries:
* - field: the history field being compared (i.e.
* 'action').
* - op: the operator to compare this field with.
* - value: the value to check for (i.e. 'add').
* @param string $parent The parent history to start searching at. If
* non-empty, will be searched for with a LIKE
* '$parent:%' clause.
*
* @return array An array of history object ids that have had at least one
* match for the given $filters. Will return empty array if
* none matched the criteria. If the same GUID has multiple
* matches withing the range requested, there is no
* guarantee which entry will be returned.
*/
protected function _getByModSeq($start, $end, $filters = array(), $parent = null)
{
// Build the modseq test.
$where = array(
sprintf(
'history_modseq > %d AND history_modseq <= %d',
$start,
$end)
);
// Add additional filters, if there are any.
try {
if ($filters) {
foreach ($filters as $filter) {
$where[] = 'history_' . $filter['field'] . ' ' . $filter['op'] . ' ' . $this->_db->quote($filter['value']);
}
}
if ($parent) {
$where[] = 'object_uid LIKE ' . $this->_db->quote($parent . ':%');
}
return $this->_db->selectAssoc('SELECT DISTINCT object_uid, history_id FROM horde_histories WHERE ' . implode(' AND ', $where));
} catch (Horde_Db_Exception $e) {
throw new Horde_History_Exception($e);
}
}
/**
* Removes one or more history entries by name.
*
* @param array $names The history entries to remove.
*
* @throws Horde_History_Exception
*/
public function removeByNames(array $names)
{
if (!count($names)) {
return;
}
$ids = array();
try {
foreach ($names as $name) {
$ids[] = $this->_db->quote($name);
if ($this->_cache) {
$this->_cache->expire('horde:history:' . $name);
}
}
$this->_db->delete('DELETE FROM horde_histories WHERE object_uid IN (' . implode(',', $ids) . ')');
} catch (Horde_Db_Exception $e) {
throw new Horde_History_Exception($e);
}
}
/**
* Returns the current value of the modseq.
*
* We take the MAX of the horde_histories table instead of the value of the
* horde_histories_modseq table to ensure we never miss an entry if we
* query the history system between the time we call nextModSeq() and the
* time the new entry is written.
*
* @param string $parent Restrict to entries a specific parent.
*
* @return integer|boolean The highest used modseq value, false if no
* history.
* @throws Horde_History_Exception
*/
public function getHighestModSeq($parent = null)
{
try {
$sql = 'SELECT history_modseq FROM horde_histories';
if (!empty($parent)) {
$sql .= ' WHERE object_uid LIKE ' . $this->_db->quote($parent . ':%');
}
$sql .= ' ORDER BY history_modseq DESC';
$sql = $this->_db->addLimitOffset($sql, array('limit' => 1));
$modseq = $this->_db->selectValue($sql);
if (is_null($modseq) || $modseq === false) {
$modseq = $this->_db->selectValue('SELECT MAX(history_modseq) FROM horde_histories_modseq');
if (!empty($modseq)) {
return $modseq;
} else {
return false;
}
}
} catch (Horde_Db_Exception $e) {
throw new Horde_History_Exception($e);
}
return $modseq;
}
/**
* Increments, and returns, the modseq value.
*
* @return integer The new modseq value.
* @throws Horde_History_Exception
*/
protected function _nextModSeq()
{
try {
$result = $this->_db->insert('INSERT INTO horde_histories_modseq (history_modseqempty) VALUES(0)');
// Don't completely empty the table to prevent sequence from being reset
// when using certain RDBMS, like postgres (see Bug #13876).
$this->_db->delete('DELETE FROM horde_histories_modseq WHERE history_modseq < (? - 25)', array($result));
} catch (Horde_Db_Exception $e) {
throw new Horde_History_Exception($e);
}
return $result;
}
/**
* Returns the latest entry of $guid.
*
* @param string $guid The name of the history entry to retrieve.
* @param boolean $use_ts If false we use the 'modseq' field to determine
* the latest entry. If true we use the timestamp
* instead of modseq to determine the latest entry.
* Note: Only 'modseq' can give a definitive
* answer.
*
* @return array|boolean The latest history entry, or false if $guid does
* not exist.
* @throws Horde_History_Exception
* @since 2.2.0
*/
public function getLatestEntry($guid, $use_ts = false)
{
$query = 'SELECT * from horde_histories WHERE object_uid = ? ORDER BY ';
if ($use_ts) {
$query .= 'history_ts ';
} else {
$query .= 'history_modseq ';
}
$query .= 'DESC LIMIT 1';
try {
$row = $this->_db->selectOne($query, array($guid));
if (empty($row['history_id'])) {
return false;
}
} catch (Horde_Db_Exception $e) {
throw new Horde_History_Exception($e);
}
$log = new Horde_History_Log($guid, array($row));
return $log[0];
}
}