/** * 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})"; } }