PdoTrait.php 16 KB

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