1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace MatthiasMullie\Scrapbook\Adapters; |
4
|
|
|
|
5
|
|
|
use MatthiasMullie\Scrapbook\Adapters\Collections\SQL as Collection; |
6
|
|
|
use MatthiasMullie\Scrapbook\KeyValueStore; |
7
|
|
|
|
8
|
|
|
/** |
9
|
|
|
* SQL adapter. Basically just a wrapper over \PDO, but in an exchangeable |
10
|
|
|
* (KeyValueStore) interface. |
11
|
|
|
* |
12
|
|
|
* This abstract class should be a "fits all DB engines" normalization. It's up |
13
|
|
|
* to extending classes to optimize for that specific engine. |
14
|
|
|
* |
15
|
|
|
* @author Matthias Mullie <[email protected]> |
16
|
|
|
* @copyright Copyright (c) 2014, Matthias Mullie. All rights reserved |
17
|
|
|
* @license LICENSE MIT |
18
|
|
|
*/ |
19
|
|
|
abstract class SQL implements KeyValueStore |
20
|
|
|
{ |
21
|
|
|
/** |
22
|
|
|
* @var \PDO |
23
|
|
|
*/ |
24
|
|
|
protected $client; |
25
|
|
|
|
26
|
|
|
/** |
27
|
|
|
* @var string |
28
|
|
|
*/ |
29
|
|
|
protected $table; |
30
|
|
|
|
31
|
|
|
/** |
32
|
|
|
* Create the database/indices if it does not already exist. |
33
|
|
|
*/ |
34
|
|
|
abstract protected function init(); |
35
|
|
|
|
36
|
|
|
/** |
37
|
|
|
* @param string $table |
38
|
|
|
*/ |
39
|
|
|
public function __construct(\PDO $client, $table = 'cache') |
40
|
|
|
{ |
41
|
|
|
$this->client = $client; |
42
|
|
|
$this->table = $table; |
43
|
|
|
|
44
|
|
|
// don't throw exceptions - it's ok to fail, as long as the return value |
45
|
|
|
// reflects that! |
46
|
|
|
$this->client->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_SILENT); |
47
|
|
|
|
48
|
|
|
// make sure the database exists (or just "fail" silently) |
49
|
|
|
$this->init(); |
50
|
|
|
|
51
|
|
|
// now's a great time to clean up all expired items |
52
|
|
|
$this->clearExpired(); |
53
|
|
|
} |
54
|
|
|
|
55
|
|
|
/** |
56
|
|
|
* {@inheritdoc} |
57
|
|
|
*/ |
58
|
|
|
public function get($key, &$token = null) |
59
|
|
|
{ |
60
|
|
|
$statement = $this->client->prepare( |
61
|
|
|
"SELECT v |
62
|
|
|
FROM $this->table |
63
|
|
|
WHERE k = :key AND (e IS NULL OR e > :expire)" |
64
|
|
|
); |
65
|
|
|
$statement->execute(array( |
66
|
|
|
':key' => $key, |
67
|
|
|
':expire' => date('Y-m-d H:i:s'), // right now! |
68
|
|
|
)); |
69
|
|
|
|
70
|
|
|
$result = $statement->fetch(\PDO::FETCH_ASSOC); |
71
|
|
|
|
72
|
|
|
if (!isset($result['v'])) { |
73
|
|
|
$token = null; |
74
|
|
|
|
75
|
|
|
return false; |
76
|
|
|
} |
77
|
|
|
|
78
|
|
|
$token = $result['v']; |
79
|
|
|
|
80
|
|
|
return $this->unserialize($result['v']); |
81
|
|
|
} |
82
|
|
|
|
83
|
|
|
/** |
84
|
|
|
* {@inheritdoc} |
85
|
|
|
*/ |
86
|
|
|
public function getMulti(array $keys, array &$tokens = null) |
87
|
|
|
{ |
88
|
|
|
$tokens = array(); |
89
|
|
|
if (empty($keys)) { |
90
|
|
|
return array(); |
91
|
|
|
} |
92
|
|
|
|
93
|
|
|
// escape input, can't bind multiple params for IN() |
94
|
|
|
$quoted = array(); |
95
|
|
|
foreach ($keys as $key) { |
96
|
|
|
$quoted[] = $this->client->quote($key); |
97
|
|
|
} |
98
|
|
|
|
99
|
|
|
$statement = $this->client->prepare( |
100
|
|
|
"SELECT k, v |
101
|
|
|
FROM $this->table |
102
|
|
|
WHERE |
103
|
|
|
k IN (".implode(',', $quoted).') AND |
104
|
|
|
(e IS NULL OR e > :expire)' |
105
|
|
|
); |
106
|
|
|
$statement->execute(array(':expire' => date('Y-m-d H:i:s'))); |
107
|
|
|
$values = $statement->fetchAll(\PDO::FETCH_ASSOC); |
108
|
|
|
|
109
|
|
|
$result = array(); |
110
|
|
|
$tokens = array(); |
111
|
|
|
foreach ($values as $value) { |
112
|
|
|
$tokens[$value['k']] = $value['v']; |
113
|
|
|
$result[$value['k']] = $this->unserialize($value['v']); |
114
|
|
|
} |
115
|
|
|
|
116
|
|
|
return $result; |
117
|
|
|
} |
118
|
|
|
|
119
|
|
|
/** |
120
|
|
|
* {@inheritdoc} |
121
|
|
|
*/ |
122
|
|
|
public function set($key, $value, $expire = 0) |
123
|
|
|
{ |
124
|
|
|
// PostgreSQL doesn't have a decent UPSERT (like REPLACE or even INSERT |
125
|
|
|
// ... ON DUPLICATE KEY UPDATE ...); here's a "works for all" downgrade |
126
|
|
|
$success = $this->add($key, $value, $expire); |
127
|
|
|
if ($success) { |
128
|
|
|
return true; |
129
|
|
|
} |
130
|
|
|
|
131
|
|
|
$success = $this->replace($key, $value, $expire); |
132
|
|
|
if ($success) { |
133
|
|
|
return true; |
134
|
|
|
} |
135
|
|
|
|
136
|
|
|
return false; |
137
|
|
|
} |
138
|
|
|
|
139
|
|
|
/** |
140
|
|
|
* {@inheritdoc} |
141
|
|
|
*/ |
142
|
|
|
public function setMulti(array $items, $expire = 0) |
143
|
|
|
{ |
144
|
|
|
$success = array(); |
145
|
|
|
|
146
|
|
|
// PostgreSQL's lack of a decent UPSERT is even worse for multiple |
147
|
|
|
// values - we can only do them one at a time... |
148
|
|
|
foreach ($items as $key => $value) { |
149
|
|
|
$success[$key] = $this->set($key, $value, $expire); |
150
|
|
|
} |
151
|
|
|
|
152
|
|
|
return $success; |
153
|
|
|
} |
154
|
|
|
|
155
|
|
|
/** |
156
|
|
|
* {@inheritdoc} |
157
|
|
|
*/ |
158
|
|
|
public function delete($key) |
159
|
|
|
{ |
160
|
|
|
$statement = $this->client->prepare( |
161
|
|
|
"DELETE FROM $this->table |
162
|
|
|
WHERE k = :key" |
163
|
|
|
); |
164
|
|
|
|
165
|
|
|
$statement->execute(array(':key' => $key)); |
166
|
|
|
|
167
|
|
|
return 1 === $statement->rowCount(); |
168
|
|
|
} |
169
|
|
|
|
170
|
|
|
/** |
171
|
|
|
* {@inheritdoc} |
172
|
|
|
*/ |
173
|
|
|
public function deleteMulti(array $keys) |
174
|
|
|
{ |
175
|
|
|
if (empty($keys)) { |
176
|
|
|
return array(); |
177
|
|
|
} |
178
|
|
|
|
179
|
|
|
// we'll need these to figure out which could not be deleted... |
180
|
|
|
$items = $this->getMulti($keys); |
181
|
|
|
|
182
|
|
|
// escape input, can't bind multiple params for IN() |
183
|
|
|
$quoted = array(); |
184
|
|
|
foreach ($keys as $key) { |
185
|
|
|
$quoted[] = $this->client->quote($key); |
186
|
|
|
} |
187
|
|
|
|
188
|
|
|
$statement = $this->client->query( |
189
|
|
|
"DELETE FROM $this->table |
190
|
|
|
WHERE k IN (".implode(',', $quoted).')' |
191
|
|
|
); |
192
|
|
|
|
193
|
|
|
/* |
194
|
|
|
* In case of connection problems, we may not have been able to delete |
195
|
|
|
* any. Otherwise, we'll use the getMulti() results to figure out which |
196
|
|
|
* couldn't be deleted because they didn't exist at that time. |
197
|
|
|
*/ |
198
|
|
|
$success = 0 !== $statement->rowCount(); |
199
|
|
|
$success = array_fill_keys($keys, $success); |
200
|
|
|
foreach ($keys as $key) { |
201
|
|
|
if (!array_key_exists($key, $items)) { |
202
|
|
|
$success[$key] = false; |
203
|
|
|
} |
204
|
|
|
} |
205
|
|
|
|
206
|
|
|
return $success; |
207
|
|
|
} |
208
|
|
|
|
209
|
|
|
/** |
210
|
|
|
* {@inheritdoc} |
211
|
|
|
*/ |
212
|
|
|
public function add($key, $value, $expire = 0) |
213
|
|
|
{ |
214
|
|
|
$value = $this->serialize($value); |
215
|
|
|
$expire = $this->expire($expire); |
216
|
|
|
|
217
|
|
|
$this->clearExpired(); |
218
|
|
|
|
219
|
|
|
$statement = $this->client->prepare( |
220
|
|
|
"INSERT INTO $this->table (k, v, e) |
221
|
|
|
VALUES (:key, :value, :expire)" |
222
|
|
|
); |
223
|
|
|
|
224
|
|
|
$statement->execute(array( |
225
|
|
|
':key' => $key, |
226
|
|
|
':value' => $value, |
227
|
|
|
':expire' => $expire, |
228
|
|
|
)); |
229
|
|
|
|
230
|
|
|
return 1 === $statement->rowCount(); |
231
|
|
|
} |
232
|
|
|
|
233
|
|
|
/** |
234
|
|
|
* {@inheritdoc} |
235
|
|
|
*/ |
236
|
|
|
public function replace($key, $value, $expire = 0) |
237
|
|
|
{ |
238
|
|
|
$value = $this->serialize($value); |
239
|
|
|
$expire = $this->expire($expire); |
240
|
|
|
|
241
|
|
|
$this->clearExpired(); |
242
|
|
|
|
243
|
|
|
$statement = $this->client->prepare( |
244
|
|
|
"UPDATE $this->table |
245
|
|
|
SET v = :value, e = :expire |
246
|
|
|
WHERE k = :key" |
247
|
|
|
); |
248
|
|
|
|
249
|
|
|
$statement->execute(array( |
250
|
|
|
':key' => $key, |
251
|
|
|
':value' => $value, |
252
|
|
|
':expire' => $expire, |
253
|
|
|
)); |
254
|
|
|
|
255
|
|
|
if (1 === $statement->rowCount()) { |
256
|
|
|
return true; |
257
|
|
|
} |
258
|
|
|
|
259
|
|
|
// if the value we've just replaced was the same as the replacement, as |
260
|
|
|
// well as the same expiration time, rowCount will have been 0, but the |
261
|
|
|
// operation was still a success |
262
|
|
|
$statement = $this->client->prepare( |
263
|
|
|
"SELECT e |
264
|
|
|
FROM $this->table |
265
|
|
|
WHERE k = :key AND v = :value" |
266
|
|
|
); |
267
|
|
|
$statement->execute(array( |
268
|
|
|
':key' => $key, |
269
|
|
|
':value' => $value, |
270
|
|
|
)); |
271
|
|
|
|
272
|
|
|
return $statement->fetchColumn(0) === $expire; |
273
|
|
|
} |
274
|
|
|
|
275
|
|
|
/** |
276
|
|
|
* {@inheritdoc} |
277
|
|
|
*/ |
278
|
|
|
public function cas($token, $key, $value, $expire = 0) |
279
|
|
|
{ |
280
|
|
|
$value = $this->serialize($value); |
281
|
|
|
$expire = $this->expire($expire); |
282
|
|
|
|
283
|
|
|
$this->clearExpired(); |
284
|
|
|
|
285
|
|
|
$statement = $this->client->prepare( |
286
|
|
|
"UPDATE $this->table |
287
|
|
|
SET v = :value, e = :expire |
288
|
|
|
WHERE k = :key AND v = :token" |
289
|
|
|
); |
290
|
|
|
|
291
|
|
|
$statement->execute(array( |
292
|
|
|
':key' => $key, |
293
|
|
|
':value' => $value, |
294
|
|
|
':expire' => $expire, |
295
|
|
|
':token' => $token, |
296
|
|
|
)); |
297
|
|
|
|
298
|
|
|
if (1 === $statement->rowCount()) { |
299
|
|
|
return true; |
300
|
|
|
} |
301
|
|
|
|
302
|
|
|
// if the value we've just cas'ed was the same as the replacement, as |
303
|
|
|
// well as the same expiration time, rowCount will have been 0, but the |
304
|
|
|
// operation was still a success |
305
|
|
|
$statement = $this->client->prepare( |
306
|
|
|
"SELECT e |
307
|
|
|
FROM $this->table |
308
|
|
|
WHERE k = :key AND v = :value AND v = :token" |
309
|
|
|
); |
310
|
|
|
$statement->execute(array( |
311
|
|
|
':key' => $key, |
312
|
|
|
':value' => $value, |
313
|
|
|
':token' => $token, |
314
|
|
|
)); |
315
|
|
|
|
316
|
|
|
return $statement->fetchColumn(0) === $expire; |
317
|
|
|
} |
318
|
|
|
|
319
|
|
|
/** |
320
|
|
|
* {@inheritdoc} |
321
|
|
|
*/ |
322
|
|
|
public function increment($key, $offset = 1, $initial = 0, $expire = 0) |
323
|
|
|
{ |
324
|
|
|
if ($offset <= 0 || $initial < 0) { |
325
|
|
|
return false; |
326
|
|
|
} |
327
|
|
|
|
328
|
|
|
return $this->doIncrement($key, $offset, $initial, $expire); |
329
|
|
|
} |
330
|
|
|
|
331
|
|
|
/** |
332
|
|
|
* {@inheritdoc} |
333
|
|
|
*/ |
334
|
|
|
public function decrement($key, $offset = 1, $initial = 0, $expire = 0) |
335
|
|
|
{ |
336
|
|
|
if ($offset <= 0 || $initial < 0) { |
337
|
|
|
return false; |
338
|
|
|
} |
339
|
|
|
|
340
|
|
|
return $this->doIncrement($key, -$offset, $initial, $expire); |
341
|
|
|
} |
342
|
|
|
|
343
|
|
|
/** |
344
|
|
|
* {@inheritdoc} |
345
|
|
|
*/ |
346
|
|
|
public function touch($key, $expire) |
347
|
|
|
{ |
348
|
|
|
$expire = $this->expire($expire); |
349
|
|
|
|
350
|
|
|
$this->clearExpired(); |
351
|
|
|
|
352
|
|
|
$statement = $this->client->prepare( |
353
|
|
|
"UPDATE $this->table |
354
|
|
|
SET e = :expire |
355
|
|
|
WHERE k = :key" |
356
|
|
|
); |
357
|
|
|
|
358
|
|
|
$statement->execute(array( |
359
|
|
|
':key' => $key, |
360
|
|
|
':expire' => $expire, |
361
|
|
|
)); |
362
|
|
|
|
363
|
|
|
return 1 === $statement->rowCount(); |
364
|
|
|
} |
365
|
|
|
|
366
|
|
|
/** |
367
|
|
|
* {@inheritdoc} |
368
|
|
|
*/ |
369
|
|
|
public function flush() |
370
|
|
|
{ |
371
|
|
|
// TRUNCATE doesn't work on SQLite - DELETE works for all |
372
|
|
|
return false !== $this->client->exec("DELETE FROM $this->table"); |
373
|
|
|
} |
374
|
|
|
|
375
|
|
|
/** |
376
|
|
|
* {@inheritdoc} |
377
|
|
|
*/ |
378
|
|
|
public function getCollection($name) |
379
|
|
|
{ |
380
|
|
|
return new Collection($this, $this->client, $this->table, $name); |
381
|
|
|
} |
382
|
|
|
|
383
|
|
|
/** |
384
|
|
|
* Shared between increment/decrement: both have mostly the same logic |
385
|
|
|
* (decrement just increments a negative value), but need their validation |
386
|
|
|
* & use of non-ttl native methods split up. |
387
|
|
|
* |
388
|
|
|
* @param string $key |
389
|
|
|
* @param int $offset |
390
|
|
|
* @param int $initial |
391
|
|
|
* @param int $expire |
392
|
|
|
* |
393
|
|
|
* @return int|bool |
394
|
|
|
*/ |
395
|
|
|
protected function doIncrement($key, $offset, $initial, $expire) |
396
|
|
|
{ |
397
|
|
|
/* |
398
|
|
|
* I used to have all this logic in a huge & ugly query, but getting |
399
|
|
|
* that right on multiple SQL engines proved challenging (SQLite doesn't |
400
|
|
|
* do INSERT ... ON DUPLICATE KEY UPDATE ..., for example) |
401
|
|
|
* I'll just stuff it in a transaction & leverage existing methods. |
402
|
|
|
*/ |
403
|
|
|
$this->client->beginTransaction(); |
404
|
|
|
$this->clearExpired(); |
405
|
|
|
|
406
|
|
|
$value = $this->get($key); |
407
|
|
|
if (false === $value) { |
408
|
|
|
$return = $this->add($key, $initial, $expire); |
409
|
|
|
|
410
|
|
|
if ($return) { |
411
|
|
|
$this->client->commit(); |
412
|
|
|
|
413
|
|
|
return $initial; |
414
|
|
|
} |
415
|
|
|
} elseif (is_numeric($value)) { |
416
|
|
|
$value += $offset; |
417
|
|
|
// < 0 is never possible |
418
|
|
|
$value = max(0, $value); |
419
|
|
|
$return = $this->replace($key, $value, $expire); |
420
|
|
|
|
421
|
|
|
if ($return) { |
422
|
|
|
$this->client->commit(); |
423
|
|
|
|
424
|
|
|
return (int) $value; |
425
|
|
|
} |
426
|
|
|
} |
427
|
|
|
|
428
|
|
|
$this->client->rollBack(); |
429
|
|
|
|
430
|
|
|
return false; |
431
|
|
|
} |
432
|
|
|
|
433
|
|
|
/** |
434
|
|
|
* Expired entries shouldn't keep filling up the database. Additionally, |
435
|
|
|
* we will want to remove those in order to properly rely on INSERT (for |
436
|
|
|
* add) and UPDATE (for replace), which assume a column exists or not, not |
437
|
|
|
* taking the expiration status into consideration. |
438
|
|
|
* An expired column should simply not exist. |
439
|
|
|
*/ |
440
|
|
|
protected function clearExpired() |
441
|
|
|
{ |
442
|
|
|
$statement = $this->client->prepare( |
443
|
|
|
"DELETE FROM $this->table |
444
|
|
|
WHERE e < :expire" |
445
|
|
|
); |
446
|
|
|
|
447
|
|
|
$statement->execute(array(':expire' => date('Y-m-d H:i:s'))); |
448
|
|
|
} |
449
|
|
|
|
450
|
|
|
/** |
451
|
|
|
* Transforms expiration times into TIMESTAMP (Y-m-d H:i:s) format, which DB |
452
|
|
|
* will understand and be able to compare with other dates. |
453
|
|
|
* |
454
|
|
|
* @param int $expire |
455
|
|
|
* |
456
|
|
|
* @return string|null |
457
|
|
|
*/ |
458
|
|
|
protected function expire($expire) |
459
|
|
|
{ |
460
|
|
|
if (0 === $expire) { |
461
|
|
|
return; |
462
|
|
|
} |
463
|
|
|
|
464
|
|
|
// relative time in seconds, <30 days |
465
|
|
|
if ($expire < 30 * 24 * 60 * 60) { |
466
|
|
|
$expire += time(); |
467
|
|
|
} |
468
|
|
|
|
469
|
|
|
return date('Y-m-d H:i:s', $expire); |
470
|
|
|
} |
471
|
|
|
|
472
|
|
|
/** |
473
|
|
|
* I originally didn't want to serialize numeric values because I planned |
474
|
|
|
* on incrementing them in the DB, but revisited that idea. |
475
|
|
|
* However, not serializing numbers still causes some small DB storage gains |
476
|
|
|
* and it's safe (serialized data can never be confused for an int). |
477
|
|
|
* |
478
|
|
|
* @param mixed $value |
479
|
|
|
* |
480
|
|
|
* @return string|int |
481
|
|
|
*/ |
482
|
|
|
protected function serialize($value) |
483
|
|
|
{ |
484
|
|
|
return is_int($value) || is_float($value) ? $value : serialize($value); |
485
|
|
|
} |
486
|
|
|
|
487
|
|
|
/** |
488
|
|
|
* Numbers aren't serialized for storage size purposes. |
489
|
|
|
* |
490
|
|
|
* @param mixed $value |
491
|
|
|
* |
492
|
|
|
* @return mixed|int|float |
493
|
|
|
*/ |
494
|
|
|
protected function unserialize($value) |
495
|
|
|
{ |
496
|
|
|
if (is_numeric($value)) { |
497
|
|
|
$int = (int) $value; |
498
|
|
|
if ((string) $int === $value) { |
499
|
|
|
return $int; |
500
|
|
|
} |
501
|
|
|
|
502
|
|
|
$float = (float) $value; |
503
|
|
|
if ((string) $float === $value) { |
504
|
|
|
return $float; |
505
|
|
|
} |
506
|
|
|
|
507
|
|
|
return $value; |
508
|
|
|
} |
509
|
|
|
|
510
|
|
|
return unserialize($value); |
511
|
|
|
} |
512
|
|
|
} |
513
|
|
|
|