Implementace UPSERTu (UPdate-or-inSERT)

Ahoj,

Narazil jsem tady na fóru na toto:
https://forum.dibiphp.com/…-modifikator
ale nějak to usnulo. Rád bych se do implementace upsertu pustil, ale potřebuji od vás nějaké názory. Níže jsem shrnul to, co jsem našel a čeho bych se zhruba chtěl držet.

Inspirace – MongoDB

MongoDB implementuje upsert na úrovni klasického updatu s příznakem. Toto řešení je asi snadno uchopitelné, ale má imho jednu nevýhodu:

Pokud mám kromě samotného klíče ještě nějaký sloupec, který chci mít v insertu a nechci updatovat (např. registration_date v tabulce users), tak to tímhle způsobem neudělám.

MySQL

Jednu implementaci pro MySQL už popsal @Skic v příspěvku výše:

INSERT INTO `tbl`  (`id`, `text`, `desc`) VALUES (1, 'h', 'hh') , (2, 'g', 'gg')
ON DUPLICATE KEY UPDATE `text`=VALUES(`text`), `desc`=VALUES(`desc`)

Varianta s REPLACE INTO:

REPLACE INTO `online_users` SET `session_id`='3580cc4e61117c0785372c426eddd11c', `user_id` = 'XXX', `page` = '/', `lastview` = NOW();

PostgreSQL

http://stackoverflow.com/…esql/6527838#…

UPDATE table SET field='C', field2='Z' WHERE id=3;
INSERT INTO table (id, field, field2)
       SELECT 3, 'C', 'Z'
       WHERE 1 NOT IN (SELECT 1 FROM table WHERE id=3);

Oracle

http://stackoverflow.com/…into-a-table

UPDATE tablename
    SET val1 = in_val1,
        val2 = in_val2
    WHERE val3 = in_val3;

IF ( sql%rowcount = 0 )
    THEN
    INSERT INTO tablename
        VALUES (in_val1, in_val2, in_val3);
END IF;

2. varianta s MERGE

MERGE INTO Employee USING dual ON ( "id"=2097153 )
WHEN MATCHED THEN UPDATE SET "last"="smith" , "name"="john"
WHEN NOT MATCHED THEN INSERT ("id","last","name")
    VALUES ( 2097153,"smith", "john" )

MSSQL

http://www.webfaq.cz/…E-KEY-UPDATE
http://maxolbi.blogspot.com/…er-2008.html

Firebird

http://www.firebirdsql.org/…-insert.html

SQLite

http://stackoverflow.com/…18352/335717

Nový modifikátor

Některá řešení vynucují existenci modifikátoru, který v tuto chvíli v dibi není. Tím je obyčejná expanze pole stringů – prakticky obdoba %l %in, ale bez zárovek.

Rozhraní upsertu

1. varianta

PRO: Umožňuje mít zcela odlišné UPDATE a INSERT data

PROTI: Pokud jsou hodně podobné (liší se třeba jen v jednom poli), duplikuje se velké množství dat.

// upsert ( $table, $uniqueKeys, $insertData, $updateData = NULL )
$connection->upsert( 'users', array('uid'), array(
    'uid' => 1244,
    'name' => 'John Doe'.
    'update_time' => date('Y-m-d H:i:s'),
    'registration_time' => date('Y-m-d H:i:s')
), array(
    'uid' => 1244,
    'name' => 'John Doe'.
    'update_time' => date('Y-m-d H:i:s'),
) );

// Pokud $updateData nepředám, vezmou se všechny $insertData kromě $uniqueKeys

2. varianta

PRO: Předávají se jen nejnutnější data (= neduplikují se)

PROTI: Nenapadá mě řešit případ, kdy budu chtít jeden sloupec v UPDATE jinak v SELECT jinak (jiná hodnota).

// upsert ( $table, array $uniqueKeys, $insertData, $excludeForUpdate = NULL )
$connection->upsert( 'users', array('uid'), array(
    'uid' => 1244,
    'name' => 'John Doe'.
    'update_time' => date('Y-m-d H:i:s'),
    'registration_time' => date('Y-m-d H:i:s')
), array( 'registration_time' ) );
// Při updatu budou afektovány sloupce z $insertData - ($uniqueKeys + $excludeForUpdate)

Otázky

1. Jak by se modifikátor měl jmenovat, případně jestli jít do BC breaku a udělat %l %in bez závorek?
2. Jak by mělo vypadat rozhraní upsert() metody? = pochlubte se se svými use-cases.
3. Víte o dalších hotových implementacích, jako v MongoDB, ze kterých bychom se mohli inspirovat?

Editoval maarlin (27. 11. 2011 13:32)