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

Commit f042580

Browse files
nicolas-grekasnicolas-grekas
committed
[Cache] Add PDO adapter
1 parent e408b50 commit f042580

File tree

2 files changed

+393
-0
lines changed

2 files changed

+393
-0
lines changed

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

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

‎src/Symfony/Component/Cache/Tests/Adapter/PdoAdapterTest.php

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,46 @@
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\Tests\Adapter;
13+
14+
use Symfony\Component\Cache\Adapter\PdoAdapter;
15+
16+
class PdoAdapterTest extends AdapterTestCase
17+
{
18+
protected static $dbFile;
19+
20+
public static function setupBeforeClass()
21+
{
22+
if (!extension_loaded('pdo_sqlite')) {
23+
throw new \PHPUnit_Framework_SkippedTestError('Extension pdo_sqlite required.');
24+
}
25+
26+
self::$dbFile = tempnam(sys_get_temp_dir(), 'sf_sqlite_cache');
27+
28+
$pool = new PdoAdapter('sqlite:'.self::$dbFile);
29+
$pool->createTable();
30+
}
31+
32+
public static function tearDownAfterClass()
33+
{
34+
@unlink(self::$dbFile);
35+
}
36+
37+
public function createCachePool($defaultLifetime = 0)
38+
{
39+
if (defined('HHVM_VERSION')) {
40+
$this->skippedTests['testDeferredSaveWithoutCommit'] = 'Fails on HHVM';
41+
}
42+
43+
return new PdoAdapter('sqlite:'.self::$dbFile, 'ns', $defaultLifetime);
44+
}
45+
}
46+

0 commit comments

Comments
 (0)
0