auraphp
Upsert
This is perhaps a bit db specific but it would be nice if there was an option for upsert in pgsql, seems there is something in mysql for it too not sure about other db's
@pmjones that looks only for mysql. May be pgsql needs to add something similar.
In postgres it's
ON CONFLICT DO UPDATE or ON CONFLICT DO NOTHING
https://www.postgresql.org/docs/9.5/static/sql-insert.html
@cdekok Excellent -- can you post a comment on PR #115 to that effect? The contributor there might like to know.
@pmjones I just looked at the code but I think this is for different functionality, in mysql it would be
ON DUPLICATE https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
replace causes a delete before insert which could cause issues: http://stackoverflow.com/a/9168948
ok. i wasn't aware of the implications (re the SO answer). lets abandon / delete #115 and start again with ON DUPLICATE KEY UPDATE. @cdekok do you want to make the new PR?
@pavarnos I could give it a shot, the one thing that would be hard is that with postgres you can specify the column or constraint on which the conflict occurs but on mysql it's always thrown on a primary key / unique index. I don't really see how we could keep a consistent interface for them, any ideas?
There is already something in the sqlite driver https://github.com/auraphp/Aura.SqlQuery/blob/3.x/src/Sqlite/Insert.php
https://www.sqlite.org/lang_conflict.html
#115 aimed to use the same method signature for MySQL and sqlite so unit tests could swap drivers with no compatibility layer needed. Would be cool if we could do the same for postgres. What about a method signature like this:
function onDuplicateKey($sql = 'driver specific sql with sensible default')
?
so the default is do nothing, then
Mysql\Insert::onDuplicateKey($sql = 'ON DUPLICATE KEY UPDATE');
Sqlite\Insert::onDuplicateKey($sql ='ON CONFLICT REPLACE');
Pgsql\Insert::onDuplicateKey($sql = 'ON CONFLICT DO UPDATE');
... just thinking out loud...
@pavarnos Do you want to add the appropriate code to the various driver-specific classes? (FWIW, I don't think the method would need the $sql argument; just add the appropriate string literals to the build process.)