You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Lon Varscsak <lo...@gmail.com> on 2017/08/01 22:37:45 UTC
Order of insert/update/delete operations
Hey guys, so Cayenne does db operations in insert/update/delete order…which
makes sense in most cases. But in Sybase I don’t have deferred constraints
(it’s not an option) and if I delete an object and then recreate it will
the same primary key I end up with a database error because it tries to
insert a duplicate row into the database.
Originally I had changed the order of operations in my fork to
update/delete/Insert which solves this problems and works in all cases
(that I use) except for one. That’s where you need to insert a row to get
it’s id (identity) which would then be used in an update operation for a FK.
It’s possible I’m the only one that uses Sybase, but just looking for some
thoughts on how I could go about solving this. I can’t think of a
straightforward case.
Thanks,
Lon
Re: Order of insert/update/delete operations
Posted by Lon Varscsak <lo...@gmail.com>.
I don’t really have that as an option…the fact that this is going on is
kind of hidden deeper down in my framework. I learned that WOnder handles
this as a special case by looking at deletes that are also in inserts and
changing the order. I’m not sure how I’d pull something like this off in
Cayenne.
Andrus originally had suggested a pretty fancy idea (from an email thread a
year ago that I had forgotten about), but that’s way above my pay grade. :P
-Lon
On Tue, Aug 1, 2017 at 8:41 PM, John Huss <jo...@gmail.com> wrote:
> In this case, I'd do your work inside an explicitly started transaction
> (don't remember the API offhand).
>
> 1) Start transaction
> 2) Perform your deletes (or whatever you need to happen first)
> 3) Commit the ObjectContext
> 4) Perform your other work
> 5) Commit the ObjectContext
> 6) Finish the transaction
>
>
> On Tue, Aug 1, 2017 at 7:40 PM Lon Varscsak <lo...@gmail.com>
> wrote:
>
> > Unfortunately, no…legacy structures.
> >
> > On Tue, Aug 1, 2017 at 4:33 PM, Aristedes Maniatis <ar...@maniatis.org>
> > wrote:
> >
> > > On 2/8/17 9:12AM, Lon Varscsak wrote:
> > > > In this case, because it’s an object with the same PK as a previously
> > > > deleted object, technically I’d need deletes to happen for this row
> > > > *first* before
> > > > doing the update.
> > >
> > > Are you able to change the schema so you don't have meaningful primary
> > > keys here?
> > >
> > > Ari
> > >
> > >
> > > --
> > > -------------------------->
> > > Aristedes Maniatis
> > > GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
> > >
> >
>
Re: Order of insert/update/delete operations
Posted by John Huss <jo...@gmail.com>.
In this case, I'd do your work inside an explicitly started transaction
(don't remember the API offhand).
1) Start transaction
2) Perform your deletes (or whatever you need to happen first)
3) Commit the ObjectContext
4) Perform your other work
5) Commit the ObjectContext
6) Finish the transaction
On Tue, Aug 1, 2017 at 7:40 PM Lon Varscsak <lo...@gmail.com> wrote:
> Unfortunately, no…legacy structures.
>
> On Tue, Aug 1, 2017 at 4:33 PM, Aristedes Maniatis <ar...@maniatis.org>
> wrote:
>
> > On 2/8/17 9:12AM, Lon Varscsak wrote:
> > > In this case, because it’s an object with the same PK as a previously
> > > deleted object, technically I’d need deletes to happen for this row
> > > *first* before
> > > doing the update.
> >
> > Are you able to change the schema so you don't have meaningful primary
> > keys here?
> >
> > Ari
> >
> >
> > --
> > -------------------------->
> > Aristedes Maniatis
> > GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
> >
>
Re: Order of insert/update/delete operations
Posted by Lon Varscsak <lo...@gmail.com>.
Unfortunately, no…legacy structures.
On Tue, Aug 1, 2017 at 4:33 PM, Aristedes Maniatis <ar...@maniatis.org> wrote:
> On 2/8/17 9:12AM, Lon Varscsak wrote:
> > In this case, because it’s an object with the same PK as a previously
> > deleted object, technically I’d need deletes to happen for this row
> > *first* before
> > doing the update.
>
> Are you able to change the schema so you don't have meaningful primary
> keys here?
>
> Ari
>
>
> --
> -------------------------->
> Aristedes Maniatis
> GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
>
Re: Order of insert/update/delete operations
Posted by Aristedes Maniatis <ar...@maniatis.org>.
On 2/8/17 9:12AM, Lon Varscsak wrote:
> In this case, because it’s an object with the same PK as a previously
> deleted object, technically I’d need deletes to happen for this row
> *first* before
> doing the update.
Are you able to change the schema so you don't have meaningful primary keys here?
Ari
--
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
Re: Order of insert/update/delete operations
Posted by Lon Varscsak <lo...@gmail.com>.
After my email, I stumbled across that and I realized that doesn’t solve my
problem (and I probably would have had this same problem in EOF). Because
it’s not necessarily (at least in this case) about the order of the Entity,
but the order of the operation (Ashwood seems to be doing a good job of
doing the entities in the right order).
In this case, because it’s an object with the same PK as a previously
deleted object, technically I’d need deletes to happen for this row
*first* before
doing the update. Changing globally deletes to happen first causes FK
violations. Updates, then Deletes, then inserts solves this problem (and
works in most cases), but then causes a problem where to get the ID for an
object (so that you can set the FK values) you need to insert a row first
(identity columns), then do the update.
Hmm, more complex than I was originally thinking.
On Tue, Aug 1, 2017 at 3:54 PM, John Huss <jo...@gmail.com> wrote:
> You're looking for the ashwood entity sorter
> On Tue, Aug 1, 2017 at 5:40 PM Lon Varscsak <lo...@gmail.com>
> wrote:
>
> > I think in EOF I handled this with a custom ordering of operations,
> where I
> > had a master list of entities in the order I wanted operations to perform
> > (and then reversed them for deletes). Maybe there’s some way to do this
> > with Cayenne, but it’s not immediately evident to me where.
> >
> > -Lon
> >
> > On Tue, Aug 1, 2017 at 3:37 PM, Lon Varscsak <lo...@gmail.com>
> > wrote:
> >
> > > Hey guys, so Cayenne does db operations in insert/update/delete
> > > order…which makes sense in most cases. But in Sybase I don’t have
> > deferred
> > > constraints (it’s not an option) and if I delete an object and then
> > > recreate it will the same primary key I end up with a database error
> > > because it tries to insert a duplicate row into the database.
> > >
> > > Originally I had changed the order of operations in my fork to
> > > update/delete/Insert which solves this problems and works in all cases
> > > (that I use) except for one. That’s where you need to insert a row to
> > get
> > > it’s id (identity) which would then be used in an update operation for
> a
> > FK.
> > >
> > > It’s possible I’m the only one that uses Sybase, but just looking for
> > some
> > > thoughts on how I could go about solving this. I can’t think of a
> > > straightforward case.
> > >
> > > Thanks,
> > >
> > > Lon
> > >
> >
>
Re: Order of insert/update/delete operations
Posted by John Huss <jo...@gmail.com>.
You're looking for the ashwood entity sorter
On Tue, Aug 1, 2017 at 5:40 PM Lon Varscsak <lo...@gmail.com> wrote:
> I think in EOF I handled this with a custom ordering of operations, where I
> had a master list of entities in the order I wanted operations to perform
> (and then reversed them for deletes). Maybe there’s some way to do this
> with Cayenne, but it’s not immediately evident to me where.
>
> -Lon
>
> On Tue, Aug 1, 2017 at 3:37 PM, Lon Varscsak <lo...@gmail.com>
> wrote:
>
> > Hey guys, so Cayenne does db operations in insert/update/delete
> > order…which makes sense in most cases. But in Sybase I don’t have
> deferred
> > constraints (it’s not an option) and if I delete an object and then
> > recreate it will the same primary key I end up with a database error
> > because it tries to insert a duplicate row into the database.
> >
> > Originally I had changed the order of operations in my fork to
> > update/delete/Insert which solves this problems and works in all cases
> > (that I use) except for one. That’s where you need to insert a row to
> get
> > it’s id (identity) which would then be used in an update operation for a
> FK.
> >
> > It’s possible I’m the only one that uses Sybase, but just looking for
> some
> > thoughts on how I could go about solving this. I can’t think of a
> > straightforward case.
> >
> > Thanks,
> >
> > Lon
> >
>
Re: Order of insert/update/delete operations
Posted by Lon Varscsak <lo...@gmail.com>.
I think in EOF I handled this with a custom ordering of operations, where I
had a master list of entities in the order I wanted operations to perform
(and then reversed them for deletes). Maybe there’s some way to do this
with Cayenne, but it’s not immediately evident to me where.
-Lon
On Tue, Aug 1, 2017 at 3:37 PM, Lon Varscsak <lo...@gmail.com> wrote:
> Hey guys, so Cayenne does db operations in insert/update/delete
> order…which makes sense in most cases. But in Sybase I don’t have deferred
> constraints (it’s not an option) and if I delete an object and then
> recreate it will the same primary key I end up with a database error
> because it tries to insert a duplicate row into the database.
>
> Originally I had changed the order of operations in my fork to
> update/delete/Insert which solves this problems and works in all cases
> (that I use) except for one. That’s where you need to insert a row to get
> it’s id (identity) which would then be used in an update operation for a FK.
>
> It’s possible I’m the only one that uses Sybase, but just looking for some
> thoughts on how I could go about solving this. I can’t think of a
> straightforward case.
>
> Thanks,
>
> Lon
>