Oznámení
Implementace UPSERTu (UPdate-or-inSERT)
před 8 lety
- maarlin
- Člen | 207
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
- Jak by se modifikátor měl jmenovat, případně jestli jít do BC breaku a udělat %l %in bez závorek?
- Jak by mělo vypadat rozhraní upsert() metody? = pochlubte se se svými use-cases.
- 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)
před 8 lety
- Milo
- Nette Core | 1119
Řešil jsem to pro PostgreSQL. Ve stejnou dobu jsem si hrál s uživatelskými modifikátory aneb přidej si modifikátor jaký chceš.
Nad synaxí upsert dat jsem také váhal a použil jsem pomocnou syntax v klíči pole.
$upsertData = array(
'id#p' => 123, // primary key
'name' => 'John', // pro INSERT i UPDATE
'update_time#i' => xxx, // jiný pro INSERT...
'update_time#u' => yyy, // a UPDATE
);