Exemplo n.º 1
0
 /**
  * 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})";
     }
 }