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

Commit ab167dc

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

File tree

2 files changed

+419
-0
lines changed

2 files changed

+419
-0
lines changed

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

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

0 commit comments

Comments
 (0)
0