8000 [Cache] Add PDO adapter · symfony/symfony@dcc7915 · GitHub
[go: up one dir, main page]
Skip to content

Commit dcc7915

Browse files
nicolas-grekasnicolas-grekas
committed
[Cache] Add PDO adapter
1 parent 64ace10 commit dcc7915

File tree

2 files changed

+424
-0
lines changed

2 files changed

+424
-0
lines changed

‎src/Symfony/Component/Cache/Adapter/PdoAdapter.php

Lines changed: 380 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,380 @@
1+
<?php
2+
3+
/*
4+
* This file is part of the Symfony package.
5+
*
6+
* (c) Fabien Potencier <fabien@symfony.com>
7+
*
8+
* For the full copyright and license information, please view the LICENSE
9+
* file that was distributed with this source code.
10+
*/
11+
12+
namespace Symfony\Component\Cache\Adapter;
13+
14+
use Symfony\Component\Cache\Exception\InvalidArgumentException;
15+
16+
class PdoAdapter extends AbstractAdapter
17+
{
18+
/**
19+
* {@inheritdoc}
20+
*/
21+
protected $maxIdLength = 255;
22+
23+
/**
24+
* @var \PDO|null PDO instance or null when not connected yet
25+
*/
26+
private $pdo;
27+
28+
/**
29+
* @var string|null DSN string or null when lazy connection disabled
30+
*/
31+
private $dsn;
32+
33+
/**
34+
* @var string Database driver
35+
*/
36+
private $driver;
37+
38+
/**
39+
* @var string Table name
40+
*/
41+
private $table = 'cache_items';
42+
43+
/**
44+
* @var string Column for item id
45+
*/
46+
private $idCol = 'item_id';
47+
48+
/**
49+
* @var string Column for item data
50+
*/
51+
private $dataCol = 'item_data';
52+
53+
/**
54+
* @var string Column for lifetime
55+
*/
56+
private $lifetimeCol = 'item_lifetime';
57+
58+
/**
59+
* @var string Column for timestamp
60+
*/
61+
private $timeCol = 'item_time';
62+
63+
/**
64+
* @var string Username when lazy-connect
65+
*/
66+
private $username = '';
67+
68+
/**
69+
* @var string Password when lazy-connect
70+
*/
71+
private $password = '';
72+
73+
/**
74+
* @var array Connection options when lazy-connect
75+
*/
76+
private $connectionOptions = array();
77+
78+
/**
79+
* Constructor.
80+
*
81+
* You can either pass an existing database connection as PDO instance or
82+
* pass a DSN string that will be used to lazy-connect to the database
83+
* when the cache is actually used.
84+
*
85+
* List of available options:
86+
* * db_table: The name of the table [default: cache_items]
87+
* * db_id_col: The column where to store the cache id [default: item_id]
88+
* * db_data_col: The column where to store the cache data [default: item_data]
89+
* * db_lifetime_col: The column where to store the lifetime [default: item_lifetime]
90+
* * db_time_col: The column where to store the timestamp [default: item_time]
91+
* * db_username: The username when lazy-connect [default: '']
92+
* * db_password: The password when lazy-connect [default: '']
93+
* * db_connection_options: An array of driver-specific connection options [default: array()]
94+
*
95+
* @param \PDO|string $pdoOrDsn A \PDO instance or DSN string or null
96+
* @param string $namespace
97+
* @param int $defaultLifetime
98+
* @param array $options An associative array of options
99+
*
100+
* @throws InvalidArgumentException When first argument is not PDO nor string
101+
* @throws InvalidArgumentException When PDO error mode is not PDO::ERRMODE_EXCEPTION
102+
* @throws InvalidArgumentException When namespace contains invalid characters
103+
*/
104+
public function __construct($pdoOrDsn, $namespace = '', $defaultLifetime = 0, array $options = array())
105+
{
106+
if (isset($namespace[0]) && preg_match('#[^-+.A-Za-z0-9]#', $namespace, $match)) {
107+
throw new InvalidArgumentException(sprintf('Namespace contains "%s" but only characters in [-+.A-Za-z0-9] are allowed.', $match[0]));
108+
}
109+
110+
if ($pdoOrDsn instanceof \PDO) {
111+
if (\PDO::ERRMODE_EXCEPTION !== $pdoOrDsn->getAttribute(\PDO::ATTR_ERRMODE)) {
112+
throw new InvalidArgumentException(sprintf('"%s" requires PDO error mode attribute be set to throw Exceptions (i.e. $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION))', __CLASS__));
113+
}
114+
115+
$this->pdo = $pdoOrDsn;
116+
$this->driver = $this->pdo->getAttribute(\PDO::ATTR_DRIVER_NAME);
117+
} elseif (is_string($pdoOrDsn)) {
118+
$this->dsn = $pdoOrDsn;
119+
} else {
120+
throw new InvalidArgumentException(sprintf('"%s" requires PDO instance or DSN string as first argument, "%s" given.', __CLASS__, is_object($pdoOrDsn) ? get_class($pdoOrDsn) : gettype($pdoOrDsn)));
121+
}
122+
123+
$this->table = isset($options['db_table']) ? $options['db_table'] : $this->table;
124+
$this->idCol = isset($options['db_id_col']) ? $options['db_id_col'] : $this->idCol;
125+
$this->dataCol = isset($options['db_data_col']) ? $options['db_data_col'] : $this->dataCol;
126+
$this->lifetimeCol = isset($options['db_lifetime_col']) ? $options['db_lifetime_col'] : $this->lifetimeCol;
127+
$this->timeCol = isset($options['db_time_col']) ? $options['db_time_col'] : $this->timeCol;
128+
$this->username = isset($options['db_username']) ? $options['db_username'] : $this->username;
129+
$this->password = isset($options['db_password']) ? $options['db_password'] : $this->password;
130+
$this->connectionOptions = isset($options['db_connection_options']) ? $options['db_connection_options'] : $this->connectionOptions;
131+
132+
parent::__construct($namespace, $defaultLifetime);
133+
}
134+
135+
/**
136+
* Creates the table to store cache items which can be called once for setup.
137+
*
138+
* Cache ID are saved in a column of maximum length 255. Cache data is
139+
* saved in a BLOB.
140+
*
141+
* @throws \PDOException When the table already exists
142+
* @throws \DomainException When an unsupported PDO driver is used
143+
*/
144+
public function createTable()
145+
{
146+
// connect if we are not yet
147+
$this->getConnection();
148+
149+
switch ($this->driver) {
150+
case 'mysql':
151+
// We use varbinary for the ID column because it prevents unwanted conversions:
152+
// - character set conversions between server and client
153+
// - trailing space removal
154+
// - case-insensitivity
155+
// - language processing like é == e
156+
$sql = "CREATE TABLE $this->table ($this->idCol VARBINARY(255) NOT NULL PRIMARY KEY, $this->dataCol BLOB NOT NULL, $this->lifetimeCol INTEGER UNSIGNED, $this->timeCol INTEGER UNSIGNED NOT NULL) COLLATE utf8_bin, ENGINE = InnoDB";
157+
break;
158+
case 'sqlite':
159+
$sql = "CREATE TABLE $this->table ($this->idCol TEXT NOT NULL PRIMARY KEY, $this->dataCol BLOB NOT NULL, $this->lifetimeCol INTEGER, $this->timeCol INTEGER NOT NULL)";
160+
break;
161+
case 'pgsql':
162+
$sql = "CREATE TABLE $this->table ($this->idCol VARCHAR(255) NOT NULL PRIMARY KEY, $this->dataCol BYTEA NOT NULL, $this->lifetimeCol INTEGER, $this->timeCol INTEGER NOT NULL)";
163+
break;
164+
case 'oci':
165+
$sql = "CREATE TABLE $this->table ($this->idCol VARCHAR2(255) NOT NULL PRIMARY KEY, $this->dataCol BLOB NOT NULL, $this->lifetimeCol INTEGER, $this->timeCol INTEGER NOT NULL)";
166+
break;
167+
case 'sqlsrv':
168+
$sql = "CREATE TABLE $this->table ($this->idCol VARCHAR(255) NOT NULL PRIMARY KEY, $this->dataCol VARBINARY(MAX) NOT NULL, $this->lifetimeCol INTEGER, $this->timeCol INTEGER NOT NULL)";
169+
break;
170+
default:
171+
throw new \DomainException(sprintf('Creating the cache table is currently not implemented for PDO driver "%s".', $this->driver));
172+
}
173+
174+
$this->pdo->exec($sql);
175+
}
176+
177+
public function __destruct()
178+
{
179+
parent::__destruct();
180+
181+
if (null !== $this->dsn) {
182+
// only close lazy-connection
183+
$this->pdo = null;
184+
}
185+
}
186+
187+
/**
188+
* {@inheritdoc}
189+
*/
190+
protected function doFetch(array $ids)
191+
{
192+
$now = time();
193+
$expired = array();
194+
195+
$sql = str_pad('', (count($ids) << 1) - 1, '?,');
196+
$sql = "SELECT $this->idCol, CASE WHEN $this->lifetimeCol IS NULL OR $this->lifetimeCol + $this->timeCol > ? THEN $this->dataCol ELSE NULL END FROM $this->table WHERE $this->idCol IN ($sql)";
197+
$stmt = $this->getConnection()->prepare($sql);
198+
$stmt->bindValue($i = 1, $now, \PDO::PARAM_INT);
199+
foreach ($ids as $id) {
200+
$stmt->bindValue(++$i, $id);
201+
}
202+
$stmt->execute();
203+
204+
while ($row = $stmt->fetch(\PDO::FETCH_NUM)) {
205+
if (null === $row[1]) {
206+
$expired[] = $row[0];
207+
} else {
208+
yield $row[0] => unserialize(is_resource($row[1]) ? stream_get_contents($row[1]) : $row[1]);
209+
}
210+
}
211+
212+
if ($expired) {
213+
$sql = str_pad('', (count($expired) << 1) - 1, '?,');
214+
$sql = "DELETE FROM $this->table WHERE $this->lifetimeCol + $this->timeCol <= ? AND $this->idCol IN ($sql)";
215+
$stmt = $this->getConnection()->prepare($sql);
216+
$stmt->bindValue($i = 1, $now, \PDO::PARAM_INT);
217+
foreach ($expired as $id) {
218+
$stmt->bindValue(++$i, $id);
219+
}
220+
$stmt->execute($expired);
221+
}
222+
}
223+
224+
/**
225+
* {@inheritdoc}
226+
*/
227+
protected function doHave($id)
228+
{
229+
$sql = "SELECT 1 FROM $this->table WHERE $this->idCol = :id AND ($this->lifetimeCol IS NULL OR $this->lifetimeCol + $this->timeCol > :time)";
230+
$stmt = $this->getConnection()->prepare($sql);
231+
232+
$stmt->bindValue(':id', $id);
233+
$stmt->bindValue(':time', time(), \PDO::PARAM_INT);
234+
$stmt->execute();
235+
236+
return (bool) $stmt->fetchColumn();
237+
}
238+
239+
/**
240+
* {@inheritdoc}
241+
*/
242+
protected function doClear($namespace)
243+
{
244+
if ('' === $namespace) {
245+
if ('sqlite' === $this->driver) {
246+
$sql = "DELETE FROM $this->table";
247+
} else {
248+
$sql = "TRUNCATE TABLE $this->table";
249+
}
250+
} else {
251+
$sql = "DELETE FROM $this->table WHERE $this->idCol LIKE '$namespace%'";
252+
}
253+
254+
$this->getConnection()->exec($sql);
255+
256+
return true;
257+
}
258+
259+
/**
260+
* {@inheritdoc}
261+
*/
262+
protected function doDelete(array $ids)
263+
{
264+
$sql = str_pad('', (count($ids) << 1) - 1, '?,');
265+
$sql = "DELETE FROM $this->table WHERE $this->idCol IN ($sql)";
266+
$stmt = $this->getConnection()->prepare($sql);
267+
$stmt->execute(array_values($ids));
268+
269+
return true;
270+
}
271+
272+
/**
273+
* {@inheritdoc}
274+
*/
275+
protected function doSave(array $values, $lifetime)
276+
{
277+
$serialized = array();
278+
$failed = array();
279+
280+
foreach ($values as $id => $value) {
281+
try {
282+
$serialized[$id] = serialize($value);
283+
} catch (\Exception $e) {
284+
$failed[] = $id;
285+
}
286+
}
287+
288+
if (!$serialized) {
289+
return $failed;
290+
}
291+
292+
$driver = $this->driver;
293+
$insertSql = "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time)";
294+
295+
switch (true) {
296+
case 'mysql' === $driver:
297+
$sql = $insertSql." ON DUPLICATE KEY UPDATE $this->dataCol = VALUES($this->dataCol), $this->lifetimeCol = VALUES($this->lifetimeCol), $this->timeCol = VALUES($this->timeCol)";
298+
break;
299+
case 'oci' === $driver:
300+
// DUAL is Oracle specific dummy table
301+
$sql = "MERGE INTO $this->table USING DUAL ON ($this->idCol = ?) ".
302+
"WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (?, ?, ?, ?) ".
303+
"WHEN MATCHED THEN UPDATE SET $this->dataCol = ?, $this->lifetimeCol = ?, $this->timeCol = ?";
304+
break;
305+
case 'sqlsrv' === $driver && version_compare($this->pdo->getAttribute(\PDO::ATTR_SERVER_VERSION), '10', '>='):
306+
// MERGE is only available since SQL Server 2008 and must be terminated by semicolon
307+
// It also requires HOLDLOCK according to http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx
308+
$sql = "MERGE INTO $this->table WITH (HOLDLOCK) USING (SELECT 1 AS dummy) AS src ON ($this->idCol = ?) ".
309+
"WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (?, ?, ?, ?) ".
310+
"WHEN MATCHED THEN UPDATE SET $this->dataCol = ?, $this->lifetimeCol = ?, $this->timeCol = ?;";
311+
break;
312+
case 'sqlite' === $driver:
313+
$sql = 'INSERT OR REPLACE'.substr($insertSql, 6);
314+
break;
315+
case 'pgsql' === $driver && version_compare($this->pdo->getAttribute(\PDO::ATTR_SERVER_VERSION), '9.5', '>='):
316+
$sql = $insertSql." ON CONFLICT ($this->idCol) DO UPDATE SET ($this->dataCol, $this->lifetimeCol, $this->timeCol) = (EXCLUDED.$this->dataCol, EXCLUDED.$this->lifetimeCol, EXCLUDED.$this->timeCol)";
317+
break;
318+
default:
319+
$driver = null;
320+
$sql = "UPDATE $this->table SET $this->dataCol = :data, $this->lifetimeCol = :lifetime, $this->timeCol = :time WHERE $this->idCol = :id";
321+
break;
322+
}
323+
324+
$now = time();
325+
$lifetime = $lifetime ?: null;
326+
$stmt = $this->getConnection()->prepare($sql);
327+
328+
if ('sqlsrv' === $driver || 'oci' === $driver) {
329+
$stmt->bindParam(1, $id);
330+
$stmt->bindParam(2, $id);
331+
$stmt->bindParam(3, $data, \PDO::PARAM_LOB);
332+
$stmt->bindValue(4, $lifetime, \PDO::PARAM_INT);
333+
$stmt->bindValue(5, $now, \PDO::PARAM_INT);
334+
$stmt->bindParam(6, $data, \PDO::PARAM_LOB);
335+
$stmt->bindValue(7, $lifetime, \PDO::PARAM_INT);
336+
$stmt->bindValue(8, $now, \PDO::PARAM_INT);
337+
} else {
338+
$stmt->bindParam(':id', $id);
339+
$stmt->bindParam(':data', $data, \PDO::PARAM_LOB);
340+
$stmt->bindValue(':lifetime', $lifetime, \PDO::PARAM_INT);
341+
$stmt->bindValue(':time', $now, \PDO::PARAM_INT);
342+
}
343+
if (null === $driver) {
344+
$insertStmt = $this->getConnection()->prepare($insertSql);
345+
346+
$insertStmt->bindParam(':id', $id);
347+
$insertStmt->bindParam(':data', $data, \PDO::PARAM_LOB);
348+
$insertStmt->bindValue(':lifetime', $lifetime, \PDO::PARAM_INT);
349+
$insertStmt->bindValue(':time', $now, \PDO::PARAM_INT);
350+
}
351+
352+
foreach ($serialized as $id => $data) {
353+
$stmt->execute();
354+
355+
if (null === $driver && !$stmt->rowCount()) {
356+
try {
357+
$insertStmt->execute();
358+
} catch (\PDOException $e) {
359+
// A concurrent write won, let it be
360+
}
361+
}
362+
}
363+
364+
return $failed;
365+
}
366+
367+
/**
368+
* @return \PDO
369+
*/
370+
private function getConnection()
371+
{
372+
if (null === $this->pdo) {
373+
$this->pdo = new \PDO($this->dsn, $this->username, $this->password, $this->connectionOptions);
374+
$this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
375+
$this->driver = $this->pdo->getAttribute(\PDO::ATTR_DRIVER_NAME);
376+
}
377+
378+
return $this->pdo;
379+
}
380+
}

0 commit comments

Comments
 (0)
0