/**
* Returns a merge/upsert (i.e. insert or update) SQL query when supported by the database.
*
* @return string|null The SQL string or null when not supported
*/
private function getMergeSql()
{
$platform = $this->con->getDatabasePlatform()->getName();
switch (true) {
case 'mysql' === $platform:
return "INSERT INTO {$this->table} ({$this->idCol}, {$this->dataCol}, {$this->timeCol}) VALUES (:id, :data, :time) " . "ON DUPLICATE KEY UPDATE {$this->dataCol} = VALUES({$this->dataCol}), {$this->timeCol} = VALUES({$this->timeCol})";
case 'oracle' === $platform:
// 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->timeCol}) VALUES (:id, :data, :time) " . "WHEN MATCHED THEN UPDATE SET {$this->dataCol} = :data2, {$this->timeCol} = :time";
case $this->con->getDatabasePlatform() instanceof SQLServer2008Platform:
// 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->timeCol}) VALUES (:id, :data, :time) " . "WHEN MATCHED THEN UPDATE SET {$this->dataCol} = :data, {$this->timeCol} = :time;";
case 'sqlite' === $platform:
return "INSERT OR REPLACE INTO {$this->table} ({$this->idCol}, {$this->dataCol}, {$this->timeCol}) VALUES (:id, :data, :time)";
case 'postgresql' === $platform && version_compare($this->con->getServerVersion(), '9.5', '>='):
return "INSERT INTO {$this->table} ({$this->idCol}, {$this->dataCol}, {$this->timeCol}) VALUES (:id, :data, :time) " . "ON CONFLICT ({$this->idCol}) DO UPDATE SET ({$this->dataCol}, {$this->timeCol}) = (EXCLUDED.{$this->dataCol}, EXCLUDED.{$this->timeCol})";
}
}