Replace ON DUPLICATE KEY UPDATE with new Syntax ON CONFLICT

[vanyasem]

Closes #10

[nook24]

Thanks @vanyasem for your contribution. Unfortunately it's not done by a simple string replace, because also the primary key needs to be specified within the new syntax.

Old syntax

INSERT INTO table (col1, col2, col3) VALUES(?,?, ?)
ON DUPLICATE KEY UPDATE
col2=VALUES(col2), col3=VALUES(col3);

New syntax

INSERT INTO table (col1, col2, col3) VALUES(?,?,?)
ON CONFLICT (col1)
DO UPDATE SET col2 = excluded.col2, col3 = excluded.col3;

In addition you also replace the MySQL statements, which does not support ON CONFLIC DO afaik.

[vanyasem]

Isn't the primary key optional in the new syntax? @nook24

[vanyasem]

And with MySQL - you're correct, that's my bad

[vanyasem]

It would be great if you re-opened the PR so that I can push fixes to it. Why would you close it in the first place?

[nook24]

Of cource, just an accident...

If i try to execute the new query without passing the PK I run into an SQLActionException[SQLParseException: line 4:20: mismatched input 'UPDATE' expecting 'NOTHING']

Schema is defined in this file: https://github.com/statusengine/worker/blob/master/lib/crateDB.sql

[vanyasem]

Should be good now

[nook24]

Good to see you again 🙂.

Unfortunately there is still a little issue with the syntax. After conflict the columns needs to be passed as col = excluded.col instead of col=VALUES(col).

Send from mobile

[vanyasem]

Should those https://github.com/statusengine/worker/pull/12/files#diff-92f70bc07cdac9cee023e69579b196d7L157 also be updated, or should they remain the same? @nook24

[vanyasem]

I used sed, and it seems that it also affected those entries, not sure if that's correct

[nook24]

looks good for me but hard to tell on a smartphone screen. I will give it a shot tomorrow and come back to you

I really appreciate your work Sir.

[vanyasem]

I manually went over all closing brackets, but I guess I missed some of them. Sorry

[nook24]

Good Job, thanks a lot!