Smalltalk › Frameworks & Tools › GLORP

Glorp: Is there some way to do insert-or-update?

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

7 messages Options

Herby Vojčík

Glorp: Is there some way to do insert-or-update?

Hello!

Is there some way to do insert-or-update operation (that is, roughly, to
be able to register an object with possibly existing primary key(s) and
let it be written regardless?

Thanks, Herby

P.S.: Use case - I want to have log of USER-DEVICE pairing with last
timestamp and 'enabled' field that would be set to false once push
notification fails - but set to true once user actually logs from the
device (again). I don't want to have many historical records, so I want
to have USER+DEVICE to be a composed primary key. Which means it is
inserted first time, but possibly updated later.

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.

Esteban A. Maringolo

Re: Glorp: Is there some way to do insert-or-update?

Herby,

What are you looking for is an "UPSERT" operation, it is... UPDATE if
it exists, INSERT if doesn't. Glorp doesn't support this, and I don't
know how it would, since it's not an ORM operation, but more a "low
level" DB operation, that depends on the underlying RDBMS.

I don't know how you're registering your objects, you should do it vía
`session register: myObject`, this will decide whether to update or
insert the object.

But if `myObject` was created externally, and it's not in Glorp's
session registry, then Glorp will attempt to insert it.

Let's say you create a UserDevicePairingNotificationStatus, that has
user, device and notiifed attributes, if you define such object with
the user and device as primary keys, you could achieve that by:

glorpSession inUnitOfWorkDo: [:session |
(session
readOneOf: UserDevicePairingNotificationStatus
where: [:each | each user = 'foo' AND: [each device = 'af01ff...']])
ifNil: [ session register: (UserDevicePairingNotificationStatus
user: 'foo' device: 'af91ff...' notified: aBoolean)]
ifNotNil: [:object | object notified: aBoolean ]
]

Regards,

Esteban A. Maringolo

2017-08-22 7:13 GMT-03:00 Herby Vojčík <[hidden email]>:

> Hello!
>
> Is there some way to do insert-or-update operation (that is, roughly, to be
> able to register an object with possibly existing primary key(s) and let it
> be written regardless?
>
> Thanks, Herby
>
> P.S.: Use case - I want to have log of USER-DEVICE pairing with last
> timestamp and 'enabled' field that would be set to false once push
> notification fails - but set to true once user actually logs from the device
> (again). I don't want to have many historical records, so I want to have
> USER+DEVICE to be a composed primary key. Which means it is inserted first
> time, but possibly updated later.
>
> --
> You received this message because you are subscribed to the Google Groups
> "glorp-group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [hidden email].
> To post to this group, send email to [hidden email].
> Visit this group at https://groups.google.com/group/glorp-group.
> For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.

Herby Vojčík

Re: Glorp: Is there some way to do insert-or-update?

Esteban A. Maringolo wrote:

> Herby,
>
> What are you looking for is an "UPSERT" operation, it is... UPDATE if
> it exists, INSERT if doesn't. Glorp doesn't support this, and I don't
> know how it would, since it's not an ORM operation, but more a "low
> level" DB operation, that depends on the underlying RDBMS.
>
> I don't know how you're registering your objects, you should do it vía
> `session register: myObject`, this will decide whether to update or
> insert the object.
>
> But if `myObject` was created externally, and it's not in Glorp's
> session registry, then Glorp will attempt to insert it.
>
> Let's say you create a UserDevicePairingNotificationStatus, that has
> user, device and notiifed attributes, if you define such object with
> the user and device as primary keys, you could achieve that by:
>
> glorpSession inUnitOfWorkDo: [:session |
> (session
> readOneOf: UserDevicePairingNotificationStatus
> where: [:each | each user = 'foo' AND: [each device = 'af01ff...']])
> ifNil: [ session register: (UserDevicePairingNotificationStatus
> user: 'foo' device: 'af91ff...' notified: aBoolean)]
> ifNotNil: [:object | object notified: aBoolean ]
> ]

Yeah, ifNil:ifNotNil:. There probably isn't a a way for true upsert as
you say, but deciding in code (which hopefully won't be very parallel
otherwise there always may be that the entry is created in the tiny
timeframe between read-not-found and insert-as-new-but-alas.

I thought about using DictionaryMapping instead, so I just do

aUser at: deviceId put: DeviceReadinessObject new

and Glorp actually does these decisions behind the scenes.

But dictionary mapping does not look simple. :-)

> Regards,
>
> Esteban A. Maringolo

Thanks, Herby

> 2017-08-22 7:13 GMT-03:00 Herby Vojčík<[hidden email]>:
>> Hello!
>>
>> Is there some way to do insert-or-update operation (that is, roughly, to be
>> able to register an object with possibly existing primary key(s) and let it
>> be written regardless?
>>
>> Thanks, Herby
>>
>> P.S.: Use case - I want to have log of USER-DEVICE pairing with last
>> timestamp and 'enabled' field that would be set to false once push
>> notification fails - but set to true once user actually logs from the device
>> (again). I don't want to have many historical records, so I want to have
>> USER+DEVICE to be a composed primary key. Which means it is inserted first
>> time, but possibly updated later.
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "glorp-group" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [hidden email].
>> To post to this group, send email to [hidden email].
>> Visit this group at https://groups.google.com/group/glorp-group.
>> For more options, visit https://groups.google.com/d/optout.
>

Herby Vojčík

Re: Glorp: Is there some way to do insert-or-update?

Herby Vojčík wrote:

> Esteban A. Maringolo wrote:
>> Herby,
>>
>> What are you looking for is an "UPSERT" operation, it is... UPDATE if
>> it exists, INSERT if doesn't. Glorp doesn't support this, and I don't
>> know how it would, since it's not an ORM operation, but more a "low
>> level" DB operation, that depends on the underlying RDBMS.
>>
>> I don't know how you're registering your objects, you should do it vía
>> `session register: myObject`, this will decide whether to update or
>> insert the object.
>>
>> But if `myObject` was created externally, and it's not in Glorp's
>> session registry, then Glorp will attempt to insert it.
>>
>> Let's say you create a UserDevicePairingNotificationStatus, that has
>> user, device and notiifed attributes, if you define such object with
>> the user and device as primary keys, you could achieve that by:
>>
>> glorpSession inUnitOfWorkDo: [:session |
>> (session
>> readOneOf: UserDevicePairingNotificationStatus
>> where: [:each | each user = 'foo' AND: [each device = 'af01ff...']])
>> ifNil: [ session register: (UserDevicePairingNotificationStatus
>> user: 'foo' device: 'af91ff...' notified: aBoolean)]
>> ifNotNil: [:object | object notified: aBoolean ]
>> ]
>
> Yeah, ifNil:ifNotNil:. There probably isn't a a way for true upsert as
> you say, but deciding in code (which hopefully won't be very parallel
> otherwise there always may be that the entry is created in the tiny
> timeframe between read-not-found and insert-as-new-but-alas.
>
> I thought about using DictionaryMapping instead, so I just do
>
> aUser at: deviceId put: DeviceReadinessObject new

I mean, aUser devicePairings at: ...

> and Glorp actually does these decisions behind the scenes.
>
> But dictionary mapping does not look simple. :-)
>
>> Regards,
>>
>> Esteban A. Maringolo
>
> Thanks, Herby
>
>> 2017-08-22 7:13 GMT-03:00 Herby Vojčík<[hidden email]>:
>>> Hello!
>>>
>>> Is there some way to do insert-or-update operation (that is, roughly,
>>> to be
>>> able to register an object with possibly existing primary key(s) and
>>> let it
>>> be written regardless?
>>>
>>> Thanks, Herby
>>>
>>> P.S.: Use case - I want to have log of USER-DEVICE pairing with last
>>> timestamp and 'enabled' field that would be set to false once push
>>> notification fails - but set to true once user actually logs from the
>>> device
>>> (again). I don't want to have many historical records, so I want to have
>>> USER+DEVICE to be a composed primary key. Which means it is inserted
>>> first
>>> time, but possibly updated later.
>>>
>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups
>>> "glorp-group" group.
>>> To unsubscribe from this group and stop receiving emails from it,
>>> send an
>>> email to [hidden email].
>>> To post to this group, send email to [hidden email].
>>> Visit this group at https://groups.google.com/group/glorp-group.
>>> For more options, visit https://groups.google.com/d/optout.
>>
>

Alan Knight

Re: Glorp: Is there some way to do insert-or-update?

In reply to this post by Herby Vojčík

If you're in a transaction it won't appear, or if it does, the transaction will just fail.

You can also do things like just query for exists rather than reading the whole thing, or doing an update where ... and checking the row count for how many were updated, as is done in some optimistic locking situations.

On Tue, Aug 22, 2017 at 12:53 PM Herby Vojčík <[hidden email]> wrote:

Esteban A. Maringolo wrote:
> Herby,
>
> What are you looking for is an "UPSERT" operation, it is... UPDATE if
> it exists, INSERT if doesn't. Glorp doesn't support this, and I don't
> know how it would, since it's not an ORM operation, but more a "low
> level" DB operation, that depends on the underlying RDBMS.
>
> I don't know how you're registering your objects, you should do it vía
> `session register: myObject`, this will decide whether to update or
> insert the object.
>
> But if `myObject` was created externally, and it's not in Glorp's
> session registry, then Glorp will attempt to insert it.
>
> Let's say you create a UserDevicePairingNotificationStatus, that has
> user, device and notiifed attributes, if you define such object with
> the user and device as primary keys, you could achieve that by:
>
> glorpSession inUnitOfWorkDo: [:session |
> (session
> readOneOf: UserDevicePairingNotificationStatus
> where: [:each | each user = 'foo' AND: [each device = 'af01ff...']])
> ifNil: [ session register: (UserDevicePairingNotificationStatus
> user: 'foo' device: 'af91ff...' notified: aBoolean)]
> ifNotNil: [:object | object notified: aBoolean ]
> ]

Yeah, ifNil:ifNotNil:. There probably isn't a a way for true upsert as
you say, but deciding in code (which hopefully won't be very parallel
otherwise there always may be that the entry is created in the tiny
timeframe between read-not-found and insert-as-new-but-alas.

I thought about using DictionaryMapping instead, so I just do

aUser at: deviceId put: DeviceReadinessObject new

and Glorp actually does these decisions behind the scenes.

But dictionary mapping does not look simple. :-)

> Regards,
>
> Esteban A. Maringolo

Thanks, Herby

> 2017-08-22 7:13 GMT-03:00 Herby Vojčík<[hidden email]>:
>> Hello!
>>
>> Is there some way to do insert-or-update operation (that is, roughly, to be
>> able to register an object with possibly existing primary key(s) and let it
>> be written regardless?
>>
>> Thanks, Herby
>>
>> P.S.: Use case - I want to have log of USER-DEVICE pairing with last
>> timestamp and 'enabled' field that would be set to false once push
>> notification fails - but set to true once user actually logs from the device
>> (again). I don't want to have many historical records, so I want to have
>> USER+DEVICE to be a composed primary key. Which means it is inserted first
>> time, but possibly updated later.
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "glorp-group" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [hidden email].
>> To post to this group, send email to [hidden email].
>> Visit this group at https://groups.google.com/group/glorp-group.
>> For more options, visit https://groups.google.com/d/optout.
>

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.

jtuchel

Re: Glorp: Is there some way to do insert-or-update?

In reply to this post by Herby Vojčík

Herby,

as Esteban already said, UPSERT doesn't make any sense in an ORM. It either knows the object as one that has been read in this session or not. If not, it is new and needs to be inserted.

You could, of course, try and see what happens if you let Glorp's insert operation always issue an UPSERT. This is probably very easy to do and at first sight there isn't too much I could think of that could go wrong with it.

But I guess including a check for existence of an object as Esteban suggests isn't too bad from the performance and "safety" POV. not sure I understand how a Dictionary Mapping could help here....

Joachim

Am Dienstag, 22. August 2017 12:13:30 UTC+2 schrieb Herbert Vojčík:

Hello!

Is there some way to do insert-or-update operation (that is, roughly, to
be able to register an object with possibly existing primary key(s) and
let it be written regardless?

Thanks, Herby

P.S.: Use case - I want to have log of USER-DEVICE pairing with last
timestamp and 'enabled' field that would be set to false once push
notification fails - but set to true once user actually logs from the
device (again). I don't want to have many historical records, so I want
to have USER+DEVICE to be a composed primary key. Which means it is
inserted first time, but possibly updated later.

Herby Vojčík

Re: Glorp: Is there some way to do insert-or-update?

jtuchel wrote:
> Herby,
>
> as Esteban already said, UPSERT doesn't make any sense in an ORM. It

I don't know... I just create new object (with same "primary key") and
register it (yes, I know I get an error - maybe I should be able to set
the policy to "overwrite" and it would makes sense; or not?).

> either knows the object as one that has been read in this session or
> not. If not, it is new and needs to be inserted.
>
> You could, of course, try and see what happens if you let Glorp's insert
> operation always issue an UPSERT. This is probably very easy to do and
> at first sight there isn't too much I could think of that could go wrong
> with it.
>
> But I guess including a check for existence of an object as Esteban
> suggests isn't too bad from the performance and "safety" POV. not sure I
> understand how a Dictionary Mapping could help here....

Similarly to what was posted above: I can simply at:put: and I don't
care if I created the new key-value pair or overwritten the old value
(in cases where simply putting new object under a key is feasible, which
is in this case).

> Joachim

Herby

> Am Dienstag, 22. August 2017 12:13:30 UTC+2 schrieb Herbert Vojčík:
>
> Hello!
>
> Is there some way to do insert-or-update operation (that is,
> roughly, to
> be able to register an object with possibly existing primary key(s) and
> let it be written regardless?
>
> Thanks, Herby
>
> P.S.: Use case - I want to have log of USER-DEVICE pairing with last
> timestamp and 'enabled' field that would be set to false once push
> notification fails - but set to true once user actually logs from the
> device (again). I don't want to have many historical records, so I want
> to have USER+DEVICE to be a composed primary key. Which means it is
> inserted first time, but possibly updated later.
>
> --
> You received this message because you are subscribed to the Google
> Groups "glorp-group" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to [hidden email]
> <mailto:[hidden email]>.
> To post to this group, send email to [hidden email]
> <mailto:[hidden email]>.
> Visit this group at https://groups.google.com/group/glorp-group.
> For more options, visit https://groups.google.com/d/optout.