10000 [HttpFoundation] changed MERGE queries by hjkl · Pull Request #18501 · symfony/symfony · GitHub
[go: up one dir, main page]
Skip to content

[HttpFoundation] changed MERGE queries #18501

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 1 commit into from
Jun 19, 2016
Merged

[HttpFoundation] changed MERGE queries #18501

Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
67 changes: 47 additions & 20 deletions src/Symfony/Component/HttpFoundation/Session/Storage/Handler/PdoSessionHandler.php
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -325,14 +325,8 @@ public function write($sessionId, $data)

try {
// We use a single MERGE SQL query when supported by the database.
$mergeSql = $this->getMergeSql();

if (null !== $mergeSql) {
$mergeStmt = $this->pdo->prepare($mergeSql);
$mergeStmt->bindParam(':id', $sessionId, \PDO::PARAM_STR);
$mergeStmt->bindParam(':data', $data, \PDO::PARAM_LOB);
$mergeStmt->bindParam(':lifetime', $maxlifetime, \PDO::PARAM_INT);
$mergeStmt->bindValue(':time', time(), \PDO::PARAM_INT);
$mergeStmt = $this->getMergeStatement($sessionId, $data, $maxlifetime);
if (null !== $mergeStmt) {
$mergeStmt->execute();

return true;
Expand Down Expand Up @@ -653,32 +647,65 @@ private function getSelectSql()
}

/**
* Returns a merge/upsert (i.e. insert or update) SQL query when supported by the database for writing session data.
* Returns a merge/upsert (i.e. insert or update) statement when supported by the database for writing session data.
*
* @return string|null The SQL string or null when not supported
* @param string $sessionId Session ID
* @param string $data Encoded session data
* @param int $maxlifetime session.gc_maxlifetime
*
* @return \PDOStatement|null The merge statement or null when not supported
*/
private function getMergeSql()
private function getMergeStatement($sessionId, $data, $maxlifetime)
{
$mergeSql = null;
switch (true) {
case 'mysql' === $this->driver:
return "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time) ".
$mergeSql = "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time) ".
"ON DUPLICATE KEY UPDATE $this->dataCol = VALUES($this->dataCol), $this->lifetimeCol = VALUES($this->lifetimeCol), $this->timeCol = VALUES($this->timeCol)";
break;
case 'oci' === $this->driver:
// DUAL is Oracle specific dummy table
return "MERGE INTO $this->table USING DUAL ON ($this->idCol = :id) ".
"WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time) ".
"WHEN MATCHED THEN UPDATE SET $this->dataCol = :data, $this->lifetimeCol = :lifetime, $this->timeCol = :time";
$mergeSql = "MERGE INTO $this->table USING DUAL ON ($this->idCol = ?) ".
"WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (?, ?, ?, ?) ".
"WHEN MATCHED THEN UPDATE SET $this->dataCol = ?, $this->lifetimeCol = ?, $this->timeCol = ?";
break;
case 'sqlsrv' === $this->driver && version_compare($this->pdo->getAttribute(\PDO::ATTR_SERVER_VERSION), '10', '>='):
// MERGE is only available since SQL Server 2008 and must be terminated by semicolon
// It also requires HOLDLOCK according to http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx
return "MERGE INTO $this->table WITH (HOLDLOCK) USING (SELECT 1 AS dummy) AS src ON ($this->idCol = :id) ".
"WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time) ".
"WHEN MATCHED THEN UPDATE SET $this->dataCol = :data, $this->lifetimeCol = :lifetime, $this->timeCol = :time;";
$mergeSql = "MERGE INTO $this->table WITH (HOLDLOCK) USING (SELECT 1 AS dummy) AS src ON ($this->idCol = ?) ".
"WHEN NOT MATCHED THEN INSERT ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (?, ?, ?, ?) ".
"WHEN MATCHED THEN UPDATE SET $this->dataCol = ?, $this->lifetimeCol = ?, $this->timeCol = ?;";
break;
case 'sqlite' === $this->driver:
return "INSERT OR REPLACE INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time)";
$mergeSql = "INSERT OR REPLACE INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time)";
break;
case 'pgsql' === $this->driver && version_compare($this->pdo->getAttribute(\PDO::ATTR_SERVER_VERSION), '9.5', '>='):
return "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time) ".
$mergeSql = "INSERT INTO $this->table ($this->idCol, $this->dataCol, $this->lifetimeCol, $this->timeCol) VALUES (:id, :data, :lifetime, :time) ".
"ON CONFLICT ($this->idCol) DO UPDATE SET ($this->dataCol, $this->lifetimeCol, $this->timeCol) = (EXCLUDED.$this->dataCol, EXCLUDED.$this->lifetimeCol, EXCLUDED.$this->timeCol)";
break;
}

if (null !== $mergeSql) {
$mergeStmt = $this->pdo->prepare($mergeSql);

if ('sqlsrv' === $this->driver || 'oci' === $this->driver) {
$mergeStmt->bindParam(1, $sessionId, \PDO::PARAM_STR);
$mergeStmt->bindParam(2, $sessionId, \PDO::PARAM_STR);
$mergeStmt->bindParam(3, $data, \PDO::PARAM_LOB);
$mergeStmt->bindParam(4, $maxlifetime, \PDO::PARAM_INT);
$mergeStmt->bindValue(5, time(), \PDO::PARAM_INT);
$mergeStmt->bindParam(6, $data, \PDO::PARAM_LOB);
$mergeStmt->bindParam(7, $maxlifetime, \PDO::PARAM_INT);
$mergeStmt->bindValue(8, time(), \PDO::PARAM_INT);
}
Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

@hjkl, could you please submit another PR fixing this minor CS issue? Thank you in advance.

Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

@phansys sure, I can't see the coding standards issue though, could you please clarify for me?

Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Sure @hjkl, I refer to PSR-2 5.1:

} else {

Thank you.

else {
$mergeStmt->bindParam(':id', $sessionId, \PDO::PARAM_STR);
$mergeStmt->bindParam(':data', $data, \PDO::PARAM_LOB);
$mergeStmt->bindParam(':lifetime', $maxlifetime, \PDO::PARAM_INT);
$mergeStmt->bindValue(':time', time(), \PDO::PARAM_INT);
}

return $mergeStmt;
}
}

Expand Down
0