10000 [Cache] Add PDO + Doctrine DBAL adapter · symfony/symfony@11e8765 · GitHub
[go: up one dir, main page]
Skip to content

Commit 11e8765

Browse files
nicolas-grekasnicolas-grekas
committed
[Cache] Add PDO + Doctrine DBAL adapter
1 parent 64ace10 commit 11e8765

File tree

4 files changed

+485
-0
lines changed

4 files changed

+485
-0
lines changed

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

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

0 commit comments

Comments
 (0)
0