Re: Idiomatic way to "upsert" while returning ID

2018-05-02 Thread Lukas Eder 
Hello,

I'm sorry for the delay. I've just stumbled upon this curiosity recently in
an unrelated issue:
https://github.com/jOOQ/jOOQ/issues/6764

In fact, that's how the MySQL Server operates, see the manual (
https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html):

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the
row is inserted as a new row, 2 if an existing row is updated, and 0 if an
existing row is set to its current values.

And to make matters more interesting...
https://bugs.mysql.com/bug.php?id=90688

So, be careful in this area :)

Thanks,
Lukas

2018-04-20 9:14 GMT+02:00 :

> Hi Lukas,
> Thanks so much for the explanation!
>
> This is likely not JOOQ related but...
>
> What I'm doing now is:
>
> val p = TestOnDuplicateKeyUpdateRow()
> p.a = "a0"
> p.b = "b2"
> val r = TaskContext.get().jooq
>  .insertInto(Tables.TEST_ON_DUPLICATE_KEY_UPDATE)
>  .set(p)
>  .onDuplicateKeyUpdate()
>  .set(p)
>  .returning(Tables.TEST_ON_DUPLICATE_KEY_UPDATE.ID)
>  .fetch()
>
>
> What's happening is:
> - If the key didn't exist, one row with a new ID is returned correctly
> - If the key existed, two rows are returned... why? Looks like the first
> one has the key of the updated row... Can I actually rely on this?
>
> I wish there was a simple upsert method on the Record :-)
>
> Thanks!
>
>
> On Monday, April 16, 2018 at 12:35:25 AM UTC-7, Lukas Eder wrote:
>>
>> Hello,
>>
>> Thanks for your message. For the record (as this feature has been
>> requested a few times), there is no 
>> UpdatableRecord.insertOnDuplicateKeyUpdate()
>> method because the "on duplicate key" semantics is different depending on
>> the database. In MySQL, all unique keys and indexes are considered for
>> duplicate keys. In other databases, only the primary key is considered, and
>> in other databases, the key has to be specified explicitly. It would be
>> difficult to make the right API choice here.
>>
>> Regarding your attempted workaround: it would be unexpected for a
>> statement to modify the passed record just because it happens to be a
>> record and because a call on the record itself does update the record. You
>> could even pass different records to the insert / update clauses, or
>> several records to the insert clause, when the update clause is always a
>> bulk update.
>>
>> Think of passing a record to a jOOQ statement in the same way as of
>> passing a map or individual values to a jOOQ statement. So, your statement
>> is akin to:
>>
>> jooq
>>  .insertInto(Tables.TEST_ON_DUPLICATE_KEY_UPDATE)
>>  .set(p.intoMap())
>>  .onDuplicateKeyUpdate()
>>  .set(p.intoMap())
>>  .execute()
>>
>>
>> You can use the returning clause on the INSERT statement, to be explicit
>> about this:
>>
>> jooq
>>  .insertInto(Tables.TEST_ON_DUPLICATE_KEY_UPDATE)
>>  .set(p.intoMap())
>>  .onDuplicateKeyUpdate()
>>  .set(p.intoMap())
>>  .returning()
>>
>>  .fetch()
>>
>>
>> I hope this helps,
>> Lukas
>>
>> 2018-04-15 4:39 GMT+02:00 :
>>
>>> Hello,
>>> Thanks for the great product!
>>>
>>> I am trying to do "upsert" (insert on duplicate key update) while still
>>> returning ID for the row.
>>>
>>> This is my table:
>>>
>>> create table test_on_duplicate_key_update (
>>>   id bigint not null primary key auto_increment,
>>>
>>>   a varchar(255) not null,
>>>   unique(a),
>>>
>>>   b varchar(255)
>>> );
>>>
>>> This is how I perform regular insert (Kotlin):
>>>
>>> val p = jooq.newRecord(Tables.TEST_ON_DUPLICATE_KEY_UPDATE)
>>> p.a = "a0"
>>> p.b = "b0"
>>> p.store()
>>> // p.id is available here
>>>
>>>
>>> Now for "upsert", what I do is:
>>>
>>> val p = TestOnDuplicateKeyUpdateRow()
>>> p.a = "a1"
>>> p.b = "b1"
>>> jooq
>>>  .insertInto(Tables.TEST_ON_DUPLICATE_KEY_UPDATE)
>>>  .set(p)
>>>  .onDuplicateKeyUpdate()
>>>  .set(p)
>>>  .execute()
>>> // p.id is null :-(
>>>
>>>
>>> What is the idiomatic way to do this?
>>>
>>>
>>> I am using:
>>>
>>> - Jooq 3.10.6
>>>
>>> - MySQL
>>>
>>>
>>> Thanks!
>>>
>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "jOOQ User Group" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to jooq-user+...@googlegroups.com.
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>> --
> You received this message because you are subscribed to the Google Groups
> "jOOQ User Group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to jooq-user+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
You received this message because you are subscribed to the Google Groups "jOOQ 
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to jooq-user+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: Idiomatic way to "upsert" while returning ID

2018-04-20 Thread alamothe 
Hi Lukas,
Thanks so much for the explanation!

This is likely not JOOQ related but...

What I'm doing now is:

val p = TestOnDuplicateKeyUpdateRow()
p.a = "a0"
p.b = "b2"
val r = TaskContext.get().jooq
 .insertInto(Tables.TEST_ON_DUPLICATE_KEY_UPDATE)
 .set(p)
 .onDuplicateKeyUpdate()
 .set(p)
 .returning(Tables.TEST_ON_DUPLICATE_KEY_UPDATE.ID)
 .fetch()


What's happening is:
- If the key didn't exist, one row with a new ID is returned correctly
- If the key existed, two rows are returned... why? Looks like the first 
one has the key of the updated row... Can I actually rely on this?

I wish there was a simple upsert method on the Record :-)

Thanks!


On Monday, April 16, 2018 at 12:35:25 AM UTC-7, Lukas Eder wrote:
>
> Hello,
>
> Thanks for your message. For the record (as this feature has been 
> requested a few times), there is no 
> UpdatableRecord.insertOnDuplicateKeyUpdate() method because the "on 
> duplicate key" semantics is different depending on the database. In MySQL, 
> all unique keys and indexes are considered for duplicate keys. In other 
> databases, only the primary key is considered, and in other databases, the 
> key has to be specified explicitly. It would be difficult to make the right 
> API choice here.
>
> Regarding your attempted workaround: it would be unexpected for a 
> statement to modify the passed record just because it happens to be a 
> record and because a call on the record itself does update the record. You 
> could even pass different records to the insert / update clauses, or 
> several records to the insert clause, when the update clause is always a 
> bulk update.
>
> Think of passing a record to a jOOQ statement in the same way as of 
> passing a map or individual values to a jOOQ statement. So, your statement 
> is akin to:
>
> jooq
>  .insertInto(Tables.TEST_ON_DUPLICATE_KEY_UPDATE)
>  .set(p.intoMap())
>  .onDuplicateKeyUpdate()
>  .set(p.intoMap())
>  .execute()
>
>
> You can use the returning clause on the INSERT statement, to be explicit 
> about this:
>
> jooq
>  .insertInto(Tables.TEST_ON_DUPLICATE_KEY_UPDATE)
>  .set(p.intoMap())
>  .onDuplicateKeyUpdate()
>  .set(p.intoMap())
>  .returning()
>
>  .fetch()
>
>
> I hope this helps,
> Lukas
>
> 2018-04-15 4:39 GMT+02:00 :
>
>> Hello,
>> Thanks for the great product!
>>
>> I am trying to do "upsert" (insert on duplicate key update) while still 
>> returning ID for the row.
>>
>> This is my table:
>>
>> create table test_on_duplicate_key_update (
>>   id bigint not null primary key auto_increment,
>>
>>   a varchar(255) not null,
>>   unique(a),
>>
>>   b varchar(255)
>> );
>>
>> This is how I perform regular insert (Kotlin):
>>
>> val p = jooq.newRecord(Tables.TEST_ON_DUPLICATE_KEY_UPDATE)
>> p.a = "a0"
>> p.b = "b0"
>> p.store()
>> // p.id is available here
>>
>>
>> Now for "upsert", what I do is:
>>
>> val p = TestOnDuplicateKeyUpdateRow()
>> p.a = "a1"
>> p.b = "b1"
>> jooq
>>  .insertInto(Tables.TEST_ON_DUPLICATE_KEY_UPDATE)
>>  .set(p)
>>  .onDuplicateKeyUpdate()
>>  .set(p)
>>  .execute()
>> // p.id is null :-(
>>
>>
>> What is the idiomatic way to do this? 
>>
>>
>> I am using:
>>
>> - Jooq 3.10.6
>>
>> - MySQL
>>
>>
>> Thanks!
>>
>> -- 
>> You received this message because you are subscribed to the Google Groups 
>> "jOOQ User Group" group.
>> To unsubscribe from this group and stop receiving emails from it, send an 
>> email to jooq-user+...@googlegroups.com .
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>

-- 
You received this message because you are subscribed to the Google Groups "jOOQ 
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to jooq-user+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: Idiomatic way to "upsert" while returning ID

2018-04-16 Thread Lukas Eder 
Hello,

Thanks for your message. For the record (as this feature has been requested
a few times), there is no UpdatableRecord.insertOnDuplicateKeyUpdate()
method because the "on duplicate key" semantics is different depending on
the database. In MySQL, all unique keys and indexes are considered for
duplicate keys. In other databases, only the primary key is considered, and
in other databases, the key has to be specified explicitly. It would be
difficult to make the right API choice here.

Regarding your attempted workaround: it would be unexpected for a statement
to modify the passed record just because it happens to be a record and
because a call on the record itself does update the record. You could even
pass different records to the insert / update clauses, or several records
to the insert clause, when the update clause is always a bulk update.

Think of passing a record to a jOOQ statement in the same way as of passing
a map or individual values to a jOOQ statement. So, your statement is akin
to:

jooq
 .insertInto(Tables.TEST_ON_DUPLICATE_KEY_UPDATE)
 .set(p.intoMap())
 .onDuplicateKeyUpdate()
 .set(p.intoMap())
 .execute()


You can use the returning clause on the INSERT statement, to be explicit
about this:

jooq
 .insertInto(Tables.TEST_ON_DUPLICATE_KEY_UPDATE)
 .set(p.intoMap())
 .onDuplicateKeyUpdate()
 .set(p.intoMap())
 .returning()

 .fetch()


I hope this helps,
Lukas

2018-04-15 4:39 GMT+02:00 :

> Hello,
> Thanks for the great product!
>
> I am trying to do "upsert" (insert on duplicate key update) while still
> returning ID for the row.
>
> This is my table:
>
> create table test_on_duplicate_key_update (
>   id bigint not null primary key auto_increment,
>
>   a varchar(255) not null,
>   unique(a),
>
>   b varchar(255)
> );
>
> This is how I perform regular insert (Kotlin):
>
> val p = jooq.newRecord(Tables.TEST_ON_DUPLICATE_KEY_UPDATE)
> p.a = "a0"
> p.b = "b0"
> p.store()
> // p.id is available here
>
>
> Now for "upsert", what I do is:
>
> val p = TestOnDuplicateKeyUpdateRow()
> p.a = "a1"
> p.b = "b1"
> jooq
>  .insertInto(Tables.TEST_ON_DUPLICATE_KEY_UPDATE)
>  .set(p)
>  .onDuplicateKeyUpdate()
>  .set(p)
>  .execute()
> // p.id is null :-(
>
>
> What is the idiomatic way to do this?
>
>
> I am using:
>
> - Jooq 3.10.6
>
> - MySQL
>
>
> Thanks!
>
> --
> You received this message because you are subscribed to the Google Groups
> "jOOQ User Group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to jooq-user+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
You received this message because you are subscribed to the Google Groups "jOOQ 
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to jooq-user+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Idiomatic way to "upsert" while returning ID

2018-04-14 Thread alamothe 
Hello,
Thanks for the great product!

I am trying to do "upsert" (insert on duplicate key update) while still 
returning ID for the row.

This is my table:

create table test_on_duplicate_key_update (
  id bigint not null primary key auto_increment,

  a varchar(255) not null,
  unique(a),

  b varchar(255)
);

This is how I perform regular insert (Kotlin):

val p = jooq.newRecord(Tables.TEST_ON_DUPLICATE_KEY_UPDATE)
p.a = "a0"
p.b = "b0"
p.store()
// p.id is available here


Now for "upsert", what I do is:

val p = TestOnDuplicateKeyUpdateRow()
p.a = "a1"
p.b = "b1"
jooq
 .insertInto(Tables.TEST_ON_DUPLICATE_KEY_UPDATE)
 .set(p)
 .onDuplicateKeyUpdate()
 .set(p)
 .execute()
// p.id is null :-(


What is the idiomatic way to do this? 


I am using:

- Jooq 3.10.6

- MySQL


Thanks!

-- 
You received this message because you are subscribed to the Google Groups "jOOQ 
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to jooq-user+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.