UUIDs

[Andrus Adamchik]

I am actually glad we went on a tangent and started discussing UUIDs. I just 
ran into a use-case of an idempotent PUT API endpoint that takes a mix of new 
and existing objects, and there's no natural key in the entity to check whether 
new (PK-less) objects are already in DB (so that we UPDATE them instead of 
INSERT). UUID would come in handy in this situation :) 

(FWIW, the endpoint is running on Agrest with Cayenne underneath, and Agrest is 
the layer that ensures idempotent semantics).

Andrus


> On Aug 20, 2024, at 12:01 PM, Hugi Thordarson  wrote:
> 
> Judging from some very, very basic experimentation, Cayenne seems to do fine 
> with UUID PKs.
> 
> Db generated UUIDs really just work like serial integers with a different 
> generated value type:
> 
> https://github.com/hugithordarson/xx-c42/blob/main/src/main/java/family/MainUUIDDbGenerated.java
> 
> …and the fun stuff, app generated UUID PKs (for all your cross- back- and 
> forth-referencing insertion needs) look fine as well:
> 
> https://github.com/hugithordarson/xx-c42/blob/main/src/main/java/family/MainUUIDAppGenerated.java
> 
> …although I wouldn't vouch for that PK-generation method of exposing the PK 
> and populating it in a post-add hook.
> 
> Unfortunately h2 doesn't appear to support deferred constraints, but I tested 
> this against postgres with the constraints present.
> 
> Anyway, pardon this tangent, born from a joke. I won't really say this really 
> demonstrates much, but it was at least a fun experiment over lunch and 
> thought you might enjoy it:).
> 
> Cheers,
> - hugi
> 
> 
>> On 16 Aug 2024, at 17:26, Michael Gentry  wrote:
>> 
>> If UUID PKs are really going to be a thing, we should probably add them to
>> Cayenne...
>> 
>> 
>> On Fri, Aug 16, 2024 at 9:44 AM Hugi Thordarson  wrote:
>> 
>>> Hi Michael!
>>> 
>>> Sure, the UUID comment was meant as a bad joke, my world is all DB
>>> generated integer keys.
>>> 
>>> That being said, I've wanted to try out UUID keys for a while. Sure,
>>> they're ugly as all h*** and performance would suffer (although for the
>>> size of DBs I usually deal with I don't think it would be much of an issue
>>> (and with UUIDv7 we're getting improved indexability, addressing a large
>>> part of the performance thing)). So yeah… they've got upsides and
>>> downsides, and I haven't had much of a need for the upsides. But I've got a
>>> suspicion they might sneak into common use soon. Perhaps when
>>> openai.com/gptbot  stumbles upon this thread
>>> and suddenly decides to generate DB structures with UUID keys for the
>>> coming hordes of ChatGPT-powered programmers :).
>>> 
>>> Cheers,
>>> - hugi
>>> 
>>> 
 On 16 Aug 2024, at 14:20, Michael Gentry  wrote:
 
 Hi Hugi,
 
 From what I've read, UUID PKs have poor index performance and take up
>>> more
 storage.
 
 Wouldn't it be better to use an integer sequence like PostgreSQL and
>>> Oracle
 support? You can generate your PKs up front and Cayenne already knows how
 to deal with them.
 
 Thanks,
 mrg
 
 
 On Thu, Aug 15, 2024 at 6:49 AM Hugi Thordarson 
>>> wrote:
 
> Hi Nikita,
> 
> again, thanks for looking into this! And yeah, totally understand how
> we're not about to insert everything in one commit. Well, at least until
> the universe decides it's time everyone move to app generated UUID PKs
>>> and
> deferred constraint checks :).
> 
> Cheers,
> - hugi
> 
> 
> 
>> On 14 Aug 2024, at 11:27, Nikita Timofeev 
> wrote:
>> 
>> In this case it seems like a true cycle, the Person entity has two
>> relationships to self. And that particular case Cayenne didn't handle
> well
>> historically.
>> But looking at it, I want to try and tweak the new Graph-based sorter,
>> because two updates generated shouldn't depend on each other. So maybe
>>> it
>> could be fixed now.
>> It still won't be able to insert all the data in one go though.
>> 
>> On Wed, Aug 14, 2024 at 11:33 AM Hugi Thordarson 
> wrote:
>> 
>>> Hi again Nikita!
>>> 
>>> saw the fix you made yesterday and it works great for the test I
> created,
>>> so thanks for that!
>>> 
>>> However, turns out that for the more complex case in our actual
>>> project,
>>> the operation still fails.
>>> I've added a new example to the test project that models that case a
>>> little more closely:
>>> 
>>> 
>>> 
> 
>>> https://github.com/hugithordarson/xx-c42/blob/main/src/main/java/family/MainWithAddedBackReference.java
>>> 
>>> Any thoughts?
>>> 
>>> Cheers,
>>> - hugi
>>> 
>>> 
>>> 
 On 12 Aug 2024, at 13:52, Nikita Timofeev >>> 
>>> wrote:
 
 Hi Hugi,
 
 Thanks for the perfect example, that's always my main problem.
 I've found the issue with the new flush logic [1]. T

Re: UUIDs

[Michael Gentry]

Hi Andrus,

Part of what I meant by adding UUID support to Cayenne was to include UUID
as a PK mechanism in Cayenne modeler and provide a corresponding PK
generator class. Nothing currently stops you from manually setting a UUID
yourself, but including support in the modeler would be a more natural fit,
I think.

Thanks,
mrg


On Fri, Aug 23, 2024 at 4:33 PM Andrus Adamchik  wrote:

> I am actually glad we went on a tangent and started discussing UUIDs. I
> just ran into a use-case of an idempotent PUT API endpoint that takes a mix
> of new and existing objects, and there's no natural key in the entity to
> check whether new (PK-less) objects are already in DB (so that we UPDATE
> them instead of INSERT). UUID would come in handy in this situation :)
>
> (FWIW, the endpoint is running on Agrest with Cayenne underneath, and
> Agrest is the layer that ensures idempotent semantics).
>
> Andrus
>
>
> > On Aug 20, 2024, at 12:01 PM, Hugi Thordarson  wrote:
> >
> > Judging from some very, very basic experimentation, Cayenne seems to do
> fine with UUID PKs.
> >
> > Db generated UUIDs really just work like serial integers with a
> different generated value type:
> >
> >
> https://github.com/hugithordarson/xx-c42/blob/main/src/main/java/family/MainUUIDDbGenerated.java
> >
> > …and the fun stuff, app generated UUID PKs (for all your cross- back-
> and forth-referencing insertion needs) look fine as well:
> >
> >
> https://github.com/hugithordarson/xx-c42/blob/main/src/main/java/family/MainUUIDAppGenerated.java
> >
> > …although I wouldn't vouch for that PK-generation method of exposing the
> PK and populating it in a post-add hook.
> >
> > Unfortunately h2 doesn't appear to support deferred constraints, but I
> tested this against postgres with the constraints present.
> >
> > Anyway, pardon this tangent, born from a joke. I won't really say this
> really demonstrates much, but it was at least a fun experiment over lunch
> and thought you might enjoy it:).
> >
> > Cheers,
> > - hugi
> >
> >
> >> On 16 Aug 2024, at 17:26, Michael Gentry  wrote:
> >>
> >> If UUID PKs are really going to be a thing, we should probably add them
> to
> >> Cayenne...
> >>
> >>
> >> On Fri, Aug 16, 2024 at 9:44 AM Hugi Thordarson 
> wrote:
> >>
> >>> Hi Michael!
> >>>
> >>> Sure, the UUID comment was meant as a bad joke, my world is all DB
> >>> generated integer keys.
> >>>
> >>> That being said, I've wanted to try out UUID keys for a while. Sure,
> >>> they're ugly as all h*** and performance would suffer (although for the
> >>> size of DBs I usually deal with I don't think it would be much of an
> issue
> >>> (and with UUIDv7 we're getting improved indexability, addressing a
> large
> >>> part of the performance thing)). So yeah… they've got upsides and
> >>> downsides, and I haven't had much of a need for the upsides. But I've
> got a
> >>> suspicion they might sneak into common use soon. Perhaps when
> >>> openai.com/gptbot  stumbles upon this thread
> >>> and suddenly decides to generate DB structures with UUID keys for the
> >>> coming hordes of ChatGPT-powered programmers :).
> >>>
> >>> Cheers,
> >>> - hugi
> >>>
> >>>
>  On 16 Aug 2024, at 14:20, Michael Gentry  wrote:
> 
>  Hi Hugi,
> 
>  From what I've read, UUID PKs have poor index performance and take up
> >>> more
>  storage.
> 
>  Wouldn't it be better to use an integer sequence like PostgreSQL and
> >>> Oracle
>  support? You can generate your PKs up front and Cayenne already knows
> how
>  to deal with them.
> 
>  Thanks,
>  mrg
> 
> 
>  On Thu, Aug 15, 2024 at 6:49 AM Hugi Thordarson 
> >>> wrote:
> 
> > Hi Nikita,
> >
> > again, thanks for looking into this! And yeah, totally understand how
> > we're not about to insert everything in one commit. Well, at least
> until
> > the universe decides it's time everyone move to app generated UUID
> PKs
> >>> and
> > deferred constraint checks :).
> >
> > Cheers,
> > - hugi
> >
> >
> >
> >> On 14 Aug 2024, at 11:27, Nikita Timofeev <
> ntimof...@objectstyle.com>
> > wrote:
> >>
> >> In this case it seems like a true cycle, the Person entity has two
> >> relationships to self. And that particular case Cayenne didn't
> handle
> > well
> >> historically.
> >> But looking at it, I want to try and tweak the new Graph-based
> sorter,
> >> because two updates generated shouldn't depend on each other. So
> maybe
> >>> it
> >> could be fixed now.
> >> It still won't be able to insert all the data in one go though.
> >>
> >> On Wed, Aug 14, 2024 at 11:33 AM Hugi Thordarson  >
> > wrote:
> >>
> >>> Hi again Nikita!
> >>>
> >>> saw the fix you made yesterday and it works great for the test I
> > created,
> >>> so thanks for that!
> >>>
> >>> However, turns out that for the more complex case in our actual
> >>> proj