auraphp/Aura.SqlQuery

Upsert

cdekok opened this issue · 10 comments

cdekok commented

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 commented

I think #115 might cover that.

harikt commented

@pmjones that looks only for mysql. May be pgsql needs to add something similar.

cdekok commented

In postgres it's
ON CONFLICT DO UPDATE or ON CONFLICT DO NOTHING
https://www.postgresql.org/docs/9.5/static/sql-insert.html

pmjones commented

@cdekok Excellent -- can you post a comment on PR #115 to that effect? The contributor there might like to know.

cdekok commented

@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

pavarnos commented

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?

cdekok commented

@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?

pavarnos commented

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...

pmjones commented

@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.)

pavarnos commented