8000 [HttpFoundation] Precalculate expiry timestamp · bcremer/symfony@5780201 · GitHub < 8000 meta name="color-scheme" content="light dark" />
[go: up one dir, main page]
Skip to content

Commit 5780201

Browse files
bcremerbcremer
committed
[HttpFoundation] Precalculate expiry timestamp
Removed timestamp and lifetime columns in favor of expiry column. Based on the work done in symfony#14625. Fix symfony#13955.
1 parent b9b6ebd commit 5780201

File tree

4 files changed

+59
-34
lines changed

4 files changed

+59
-34
lines changed

‎UPGRADE-3.3.md

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,30 @@ FrameworkBundle
3939
---------------
4040

4141
* The `Symfony\Bundle\FrameworkBundle\DependencyInjection\Compiler\AddConsoleCommandPass` has been deprecated. Use `Symfony\Component\Console\DependencyInjection\AddConsoleCommandPass` instead.
42+
43+
HttpFoundation
44+
--------------
45+
46+
* The schema of the `PdoSessionHandler` to store sessions in a database changed.
47+
The following changes must be made to your `session` table:
48+
49+
- Create a new `sess_expiry` column. In MySQL this would be:
50+
51+
```sql
52+
ALTER TABLE `session` ADD `sess_expiry` INTEGER UNSIGNED NOT NULL;
53+
```
54+
55+
- Migrate the old data to the `sess_expiry` column. In MySQL this would be:
56+
57+
```sql
58+
UPDATE `session` SET `sess_expiry` = `sess_time` + `sess_lifetime`;
59+
```
60+
61+
- Remove the `sess_time` and `sess_lifetime` columns. In MySQL this would be:
62+
63+
```sql
64+
ALTER TABLE `session` DROP COLUMN `sess_time`, DROP COLUMN `sess_lifetime`;
65+
```
4266

4367
HttpKernel
4468
-----------

‎src/Symfony/Component/HttpFoundation/CHANGELOG.md

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,8 @@ CHANGELOG
88
disabling `Range` and `Content-Length` handling, switching to chunked encoding instead
99
* added the `Cookie::fromString()` method that allows to create a cookie from a
1010
raw header string
11-
11+
* PdoSessionHandler: [BC BREAK] removed the `lifetime` and `timestamp` columns in favor of `expiry` column
12+
1213
3.1.0
1314
-----
1415

‎src/Symfony/Component/HttpFoundation/Session/Storage/Handler/PdoSessionHandler.php

Lines changed: 31 additions & 31 deletions
Original file line numberDiff line numberDiff line change
@@ -98,7 +98,7 @@ class PdoSessionHandler implements \SessionHandlerInterface
9898
/**
9999
* @var string Column for lifetime
100100
*/
101-
private $lifetimeCol = 'sess_lifetime';
101+
private $expiryCol = 'sess_expiry';
102102

103103
/**
104104
* @var string Column for timestamp
@@ -159,7 +159,7 @@ class PdoSessionHandler implements \SessionHandlerInterface
159159
* * db_table: The name of the table [default: sessions]
160160
* * db_id_col: The column where to store the session id [default: sess_id]
161161
* * db_data_col: The column where to store the session data [default: sess_data]
162-
* * db_lifetime_col: The column where to store the lifetime [default: sess_lifetime]
162+
* * db_expiry_col: The column where to store the expirytime [default: sess_expiry]
163163
* * db_time_col: The column where to store the timestamp [default: sess_time]
164164
* * db_username: The username when lazy-connect [default: '']
165165
* * db_password: The password when lazy-connect [default: '']
@@ -187,7 +187,7 @@ public function __construct($pdoOrDsn = null, array $options = array())
187187
$this->table = isset($options['db_table']) ? $options['db_table'] : $this->table;
188188
$this->idCol = isset($options['db_id_col']) ? $options['db_id_col'] : $this->idCol;
189189
$this->dataCol = isset($options['db_data_col']) ? $options['db_data_col'] : $this->dataCol;
190-
$this->lifetimeCol = isset($options['db_lifetime_col']) ? $options['db_lifetime_col'] : $this->lifetimeCol;
190+
$this->expiryCol = isset($options['db_expiry_col']) ? $options['db_expiry_col'] : $this->expiryCol;
191191
$this->timeCol = isset($options['db_time_col']) ? $options['db_time_col'] : $this->timeCol;
192192
$this->username = isset($options['db_username']) ? $options['db_username'] : $this->username;
193193
$this->password = isset($options['db_password']) ? $options['db_password'] : $this->password;
@@ -218,19 +218,19 @@ public function createTable()
218218
// - trailing space removal
219219
// - case-insensitivity
220220
// - language processing like é == e
221-
$sql = "CREATE TABLE $this->table ($this->idCol VARBINARY(128) NOT NULL PRIMARY KEY, $this->dataCol BLOB NOT NULL, $this->lifetimeCol MEDIUMINT NOT NULL, $this->timeCol INTEGER UNSIGNED NOT NULL) COLLATE utf8_bin, ENGINE = InnoDB";
221+
$sql = "CREATE TABLE $this->table ($this->idCol VARBINARY(128) NOT NULL PRIMARY KEY, $this->dataCol BLOB NOT NULL, $this->expiryCol MEDIUMINT NOT NULL, $this->timeCol INTEGER UNSIGNED NOT NULL) COLLATE utf8_bin, ENGINE = InnoDB";
222222
break;
223223
case 'sqlite':
224-
$sql = "CREATE TABLE $this->table ($this->idCol TEXT NOT NULL PRIMARY KEY, $this->dataCol BLOB NOT NULL, $this->lifetimeCol INTEGER NOT NULL, $this->timeCol INTEGER NOT NULL)";
224+
$sql = "CREATE TABLE $this->table ($this->idCol TEXT NOT NULL PRIMARY KEY, $this->dataCol BLOB NOT NULL, $this->expiryCol INTEGER NOT NULL, $this->timeCol INTEGER NOT NULL)";
225225
break;
226226
case 'pgsql':
227-
$sql = "CREATE TABLE $this->table ($this->idCol VARCHAR(128) NOT NULL PRIMARY KEY, $this->dataCol BYTEA NOT NULL, $this->lifetimeCol INTEGER NOT NULL, $this->timeCol INTEGER NOT NULL)";
227+
$sql = "CREATE TABLE $this->table ($this->idCol VARCHAR(128) NOT NULL PRIMARY KEY, $this->dataCol BYTEA NOT NULL, $this->expiryCol INTEGER NOT NULL, $this->timeCol INTEGER NOT NULL)";
228228
break;
229229
case 'oci':
230-
$sql = "CREATE TABLE $this->table ($this->idCol VARCHAR2(128) NOT NULL PRIMARY KEY, $this->dataCol BLOB NOT NULL, $this->lifetimeCol INTEGER NOT NULL, $this->timeCol INTEGER NOT NULL)";
230+
$sql = "CREATE TABLE $this->table ($this->idCol VARCHAR2(128) NOT NULL PRIMARY KEY, $this->dataCol BLOB NOT NULL, $this->expiryCol INTEGER NOT NULL, $this->timeCol INTEGER NOT NULL)";
231231
break;
232232
case 'sqlsrv':
233-
$sql = "CREATE TABLE $this->table ($this->idCol VARCHAR(128) NOT NULL PRIMARY KEY, $this->dataCol VARBINARY(MAX) NOT NULL, $this->lifetimeCol INTEGER NOT NULL, $this->timeCol INTEGER NOT NULL)";
233+
$sql = "CREATE TABLE $this->table ($this->idCol VARCHAR(128) NOT NULL PRIMARY KEY, $this->dataCol VARBINARY(MAX) NOT NULL, $this->expiryCol INTEGER NOT NULL, $this->timeCol INTEGER NOT NULL)";
234234
break;
235235
default:
236236
throw new \DomainException(sprintf('Creating the session table is currently not implemented for PDO driver "%s".', $this->driver));
@@ -333,11 +333,11 @@ public function write($sessionId, $data)
333333
}
334334

335335
$updateStmt = $this->pdo->prepare(
336-
"UPDATE $this->table SET $this->dataCol = :data, $this->lifetimeCol = :lifetime, $this->timeCol = :time WHERE $this->idCol = :id"
336+
"UPDATE $this->table SET $this->dataCol = :data, $this->expiryCol = :expiry, $this->timeCol = :time WHERE $this->idCol = :id"
337337
);
338338
$updateStmt->bindParam(':id', $sessionId, \PDO::PARAM_STR);
339339
$updateStmt->bindParam(':data', $data, \PDO::PARAM_LOB);
340-
$updateStmt->bindParam(':lifetime', $maxlifetime, \PDO::PARAM_INT);
340+
$updateStmt->bindValue(':expiry', time() + $maxlifetime, \PDO::PARAM_INT);
341341
$updateStmt->bindValue(':time', time(), \PDO::PARAM_INT);
342342
$updateStmt->execute();
343343

@@ -349,11 +349,11 @@ public function write($sessionId, $data)
349349
if (!$updateStmt->rowCount()) {
350350
try {
351351
$insertStmt = $this->pdo->prepare(
352-
"INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time)"
352+
"INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->expiryCol, $this->timeCol) VALUES (:id, :data, :expiry, :time)"
353353
);
354354
$insertStmt->bindParam(':id', $sessionId, \PDO::PARAM_STR);
355355
$insertStmt->bindParam(':data', $data, \PDO::PARAM_LOB);
356-
$insertStmt->bindParam(':lifetime', $maxlifetime, \PDO::PARAM_INT);
356+
$insertStmt->bindValue(':expiry', time() + $maxlifetime, \PDO::PARAM_INT);
357357
$insertStmt->bindValue(':time', time(), \PDO::PARAM_INT);
358358
$insertStmt->execute();
359359
} catch (\PDOException $e) {
@@ -389,7 +389,7 @@ public function close()
389389
$this->gcCalled = false;
390390

391391
// delete the session records that have expired
392-
$sql = "DELETE FROM $this->table WHERE $this->lifetimeCol + $this->timeCol < :time";
392+
$sql = "DELETE FROM $this->table WHERE $this->expiryCol < :time";
393393

394394
$stmt = $this->pdo->prepare($sql);
395395
$stmt->bindValue(':time', time(), \PDO::PARAM_INT);
@@ -510,7 +510,7 @@ private function doRead($sessionId)
510510
$sessionRows = $selectStmt->fetchAll(\PDO::FETCH_NUM);
511511

512512
if ($sessionRows) {
513-
if ($sessionRows[0][1] + $sessionRows[0][2] < time()) {
513+
if ($sessionRows[0][1] < time()) {
514514
$this->sessionExpired = true;
515515

516516
return '';
@@ -524,11 +524,11 @@ private function doRead($sessionId)
524524
// until other connections to the session are committed.
525525
try {
526526
$insertStmt = $this->pdo->prepare(
527-
"INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time)"
527+
"INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->expiryCol, $this->timeCol) VALUES (:id, :data, :expiry, :time)"
528528
);
529529
$insertStmt->bindParam(':id', $sessionId, \PDO::PARAM_STR);
530530
$insertStmt->bindValue(':data', '', \PDO::PARAM_LOB);
531-
$insertStmt->bindValue(':lifetime', 0, \PDO::PARAM_INT);
531+
$insertStmt->bindValue(':expiry', 0, \PDO::PARAM_INT);
532532
$insertStmt->bindValue(':time', time(), \PDO::PARAM_INT);
533533
$insertStmt->execute();
534534
} catch (\PDOException $e) {
@@ -629,9 +629,9 @@ private function getSelectSql()
629629
case 'mysql':
630630
case 'oci':
631631
case 'pgsql':
632-
return "SELECT $this->dataCol, $this->lifetimeCol, $this->timeCol FROM $this->table WHERE $this->idCol = :id FOR UPDATE";
632+
return "SELECT $this->dataCol, $this->expiryCol, $this->timeCol FROM $this->table WHERE $this->idCol = :id FOR UPDATE";
633633
case 'sqlsrv':
634-
return "SELECT $this->dataCol, $this->lifetimeCol, $this->timeCol FROM $this->table WITH (UPDLOCK, ROWLOCK) WHERE $this->idCol = :id";
634+
return "SELECT $this->dataCol, $this->expiryCol, $this->timeCol FROM $this->table WITH (UPDLOCK, ROWLOCK) WHERE $this->idCol = :id";
635635
case 'sqlite':
636636
// we already locked when starting transaction
637637
break;
@@ -640,7 +640,7 @@ private function getSelectSql()
640640
}
641641
}
642642

643-
return "SELECT $this->dataCol, $this->lifetimeCol, $this->timeCol FROM $this->table WHERE $this->idCol = :id";
643+
return "SELECT $this->dataCol, $this->expiryCol, $this->timeCol FROM $this->table WHERE $this->idCol = :id";
644644
}
645645

646646
/**
@@ -657,28 +657,28 @@ private function getMergeStatement($sessionId, $data, $maxlifetime)
657657
$mergeSql = null;
658658
switch (true) {
659659
case 'mysql' === $this->driver:
660-
$mergeSql = "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time) ".
661-
"ON DUPLICATE KEY UPDATE $this->dataCol = VALUES($this->dataCol), $this->lifetimeCol = VALUES($this->lifetimeCol), $this->timeCol = VALUES($this->timeCol)";
660+
$mergeSql = "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->expiryCol, $this->timeCol) VALUES (:id, :data, :expiry, :time) ".
661+
"ON DUPLICATE KEY UPDATE $this->dataCol = VALUES($this->dataCol), $this->expiryCol = VALUES($this->expiryCol), $this->timeCol = VALUES($this->timeCol)";
662662
break;
663663
case 'oci' === $this->driver:
664664
// DUAL is Oracle specific dummy table
665665
$mergeSql = "MERGE INTO $this->table USING DUAL ON ($this->idCol = ?) ".
666-
"WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (?, ?, ?, ?) ".
667-
"WHEN MATCHED THEN UPDATE SET $this->dataCol = ?, $this->lifetimeCol = ?, $this->timeCol = ?";
666+
"WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->expiryCol, $this->timeCol) VALUES (?, ?, ?, ?) ".
667+
"WHEN MATCHED THEN UPDATE SET $this->dataCol = ?, $this->expiryCol = ?, $this->timeCol = ?";
668668
break;
669669
case 'sqlsrv' === $this->driver && version_compare($this->pdo->getAttribute(\PDO::ATTR_SERVER_VERSION), '10', '>='):
670670
// MERGE is only available since SQL Server 2008 and must be terminated by semicolon
671671
// It also requires HOLDLOCK according to http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx
672672
$mergeSql = "MERGE INTO $this->table WITH (HOLDLOCK) USING (SELECT 1 AS dummy) AS src ON ($this->idCol = ?) ".
673-
"WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (?, ?, ?, ?) ".
674-
"WHEN MATCHED THEN UPDATE SET $this->dataCol = ?, $this->lifetimeCol = ?, $this->timeCol = ?;";
673+
"WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->expiryCol, $this->timeCol) VALUES (?, ?, ?, ?) ".
674+
"WHEN MATCHED THEN UPDATE SET $this->dataCol = ?, $this->expiryCol = ?, $this->timeCol = ?;";
675675
break;
676676
case 'sqlite' === $this->driver:
677-
$mergeSql = "INSERT OR REPLACE INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time)";
677+
$mergeSql = "INSERT OR REPLACE INTO $this->table ($this->idCol, $this->dataCol, $this->expiryCol, $this->timeCol) VALUES (:id, :data, :expiry, :time)";
678678
break;
679679
case 'pgsql' === $this->driver && version_compare($this->pdo->getAttribute(\PDO::ATTR_SERVER_VERSION), '9.5', '>='):
680-
$mergeSql = "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time) ".
681-
"ON CONFLICT ($this->idCol) DO UPDATE SET ($this->dataCol, $this->lifetimeCol, $this->timeCol) = (EXCLUDED.$this->dataCol, EXCLUDED.$this->lifetimeCol, EXCLUDED.$this->timeCol)";
680+
$mergeSql = "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->expiryCol, $this->timeCol) VALUES (:id, :data, :expiry, :time) ".
681+
"ON CONFLICT ($this->idCol) DO UPDATE SET ($this->dataCol, $this->expiryCol, $this->timeCol) = (EXCLUDED.$this->dataCol, EXCLUDED.$this->expiryCol, EXCLUDED.$this->timeCol)";
682682
break;
683683
}
684684

@@ -689,15 +689,15 @@ private function getMergeStatement($sessionId, $data, $maxlifetime)
689689
$mergeStmt->bindParam(1, $sessionId, \PDO::PARAM_STR);
690690
$mergeStmt->bindParam(2, $sessionId, \PDO::PARAM_STR);
691691
$mergeStmt->bindParam(3, $data, \PDO::PARAM_LOB);
692-
$mergeStmt->bindParam(4, $maxlifetime, \PDO::PARAM_INT);
692+
$mergeStmt->bindValue(4, time() + $maxlifetime, \PDO::PARAM_INT);
693693
$mergeStmt->bindValue(5, time(), \PDO::PARAM_INT);
694694
$mergeStmt->bindParam(6, $data, \PDO::PARAM_LOB);
695-
$mergeStmt->bindParam(7, $maxlifetime, \PDO::PARAM_INT);
695+
$mergeStmt->bindValue(7, time() + $maxlifetime, \PDO::PARAM_INT);
696696
$mergeStmt->bindValue(8, time(), \PDO::PARAM_INT);
697697
} else {
698698
$mergeStmt->bindParam(':id', $sessionId, \PDO::PARAM_STR);
699699
$mergeStmt->bindParam(':data', $data, \PDO::PARAM_LOB);
700-
$mergeStmt->bindParam(':lifetime', $maxlifetime, \PDO::PARAM_INT);
700+
$mergeStmt->bindValue(':expiry', time() + $maxlifetime, \PDO::PARAM_INT);
701701
$mergeStmt->bindValue(':time', time(), \PDO::PARAM_INT);
702702
}
703703

‎src/Symfony/Component/HttpFoundation/Tests/Session/Storage/Handler/PdoSessionHandlerTest.php

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -146,7 +146,7 @@ public function testReadConvertsStreamToString()
146146
$stream = $this->createStream($content);
147147

148148
$pdo->prepareResult->expects($this->once())->method('fetchAll')
149-
->will($this->returnValue(array(array($stream, 42, time()))));
149+
->will($this->returnValue(array(array($stream, 42 + time()))));
150150

151151
$storage = new PdoSessionHandler($pdo);
152152
$result = $storage->read('foo');
@@ -174,7 +174,7 @@ public function testReadLockedConvertsStreamToString()
174174

175175
$selectStmt->expects($this->atLeast(2))->method('fetchAll')
176176
->will($this->returnCallback(function () use (&$exception, $stream) {
177-
return $exception ? array(array($stream, 42, time())) : array();
177+
return $exception ? array(array($stream, 42 + time())) : array();
178178
}));
179179

180180
$insertStmt->expects($this->once())->method('execute')

0 commit comments

Comments
 (0)
0