You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Laurent Marchal <lm...@smaeur.com> on 2008/07/18 16:10:21 UTC

Queries order on commit.

Hi !

I would like to do some DELETEs and INSERTs in one transaction, but 
since DELETE is made after the INSERTs, it deletes the fresh inserted 
values...
    Is there a way to force cayenne to do DELETE  before all others 
operations when a commit occurs ?
    What means the "snapshot version changed, don't know what to do" 
messages ?

Thanks.
Laurent Marchal.

#
# LOG
#

--- will run 2 queries.
--- transaction started.
INSERT INTO dbo.SNAME_AUX (SAFC, SASEQNO, SAVALUE, SKDID) VALUES (?, ?, 
?, ?)
[batch bind: 1->SAFC:112, 2->SASEQNO:2, 3->SAVALUE:'1', 4->SKDID:505]
[batch bind: 1->SAFC:112, 2->SASEQNO:1, 3->SAVALUE:'352', 4->SKDID:505]
[batch bind: 1->SAFC:112, 2->SASEQNO:0, 3->SAVALUE:'517', 4->SKDID:505]
=== updated 3 rows.
DELETE FROM dbo.SNAME_AUX WHERE SAFC = ? AND SASEQNO = ? AND SKDID = ?
[batch bind: 1->SAFC:112, 2->SASEQNO:1, 3->SKDID:505]
=== updated 2 rows.

snapshot version changed, don't know what to do... Old: 
org.apache.cayenne.DataRow@fe8153[values={SASEQNO=0, SAVALUE=517, 
SAFC=112, SKDID=505}, version=-9223372036854712970, 
replaces=-9223372036854775808], New: 
org.apache.cayenne.DataRow@dc8f6d[values={SASEQNO=0, SAVALUE=517, 
SAFC=112, SKDID=505}, version=-9223372036854712775, 
replaces=-9223372036854775808]

snapshot version changed, don't know what to do... Old: 
org.apache.cayenne.DataRow@4c72e3[values={SASEQNO=2, SAVALUE=1, 
SAFC=112, SKDID=505}, version=-9223372036854712971, 
replaces=-9223372036854775808], New: 
org.apache.cayenne.DataRow@10aca93[values={SASEQNO=2, SAVALUE=1, 
SAFC=112, SKDID=505}, version=-9223372036854712777, 
replaces=-9223372036854775808]

postSnapshotsChangeEvent: [SnapshotEvent] source: 
org.apache.cayenne.access.DataRowStore@1f0cbfb, deleted 1 id(s), 
indirectly modified 1 id(s)

Re: Queries order on commit.

Posted by Laurent Marchal <lm...@smaeur.com>.
Ok i will try to modify the object directly, and see the results.
I will post code if this doesn't work.

Andrus Adamchik wrote:
>
> On Jul 18, 2008, at 5:10 PM, Laurent Marchal wrote:
>
>> Hi !
>>
>> I would like to do some DELETEs and INSERTs in one transaction, but 
>> since DELETE is made after the INSERTs, it deletes the fresh inserted 
>> values...
>
> Yeah, weird. Could you post some sample code? Looking at your log, you 
> delete a dependent object from a relationship, and then readd a new 
> one? IMO the solution should be modifying the existing object instead 
> of doing delete/insert.
>
>
>>   Is there a way to force cayenne to do DELETE  before all others 
>> operations when a commit occurs ?
>
> The ordering algorithm is based on the changed objects graph 
> dependency analysis, to provide ordering consistent with FK 
> constraints. It does not address a scenario of object identity (PK) 
> disappearing and then reappearing again. There is probably a way to 
> customize it (although likely not an easy one), but I still feel like 
> this is not a valid case.
>
> Andrus
>
>
>

Re: Queries order on commit.

Posted by Laurent Marchal <lm...@smaeur.com>.
Ok i will try to do it in a smarter way, but clear that is hard on 
Monday morning ;)

Andrus Adamchik wrote:
>
>> From the example in your message, you do not the *exiting* 
>> MasterScheduleAux objects, you still delete the old ones, and 
>> reinsert them back.
>
> This of course should read "modify the existing MasterScheduleAux 
> objects". Monday morning, tough time :-)
>
> Andrus
>
>
> On Jul 21, 2008, at 10:26 AM, Andrus Adamchik wrote:
>
>>
>> On Jul 18, 2008, at 7:03 PM, Laurent Marchal wrote:
>>
>>> I managed to modify directly the object, removing the relation from 
>>> MasterSchedule->MasterScheduleAux after deleting
>>> the MasterScheduleAux object.
>>
>> From the example in your message, you do not the *exiting* 
>> MasterScheduleAux objects, you still delete the old ones, and 
>> reinsert them back. I suggest that in 'createAux' method you first 
>> check if there is an existing matching MasterScheduleAux object 
>> attached to "this" MasterSchedule for a given compound FK, and use 
>> this object if it exists, instead of creating a new one.
>>
>> Andrus
>
>
>
>

Re: Queries order on commit.

Posted by Andrus Adamchik <an...@objectstyle.org>.
> From the example in your message, you do not the *exiting*  
> MasterScheduleAux objects, you still delete the old ones, and  
> reinsert them back.

This of course should read "modify the existing MasterScheduleAux  
objects". Monday morning, tough time :-)

Andrus


On Jul 21, 2008, at 10:26 AM, Andrus Adamchik wrote:

>
> On Jul 18, 2008, at 7:03 PM, Laurent Marchal wrote:
>
>> I managed to modify directly the object, removing the relation from  
>> MasterSchedule->MasterScheduleAux after deleting
>> the MasterScheduleAux object.
>
> From the example in your message, you do not the *exiting*  
> MasterScheduleAux objects, you still delete the old ones, and  
> reinsert them back. I suggest that in 'createAux' method you first  
> check if there is an existing matching MasterScheduleAux object  
> attached to "this" MasterSchedule for a given compound FK, and use  
> this object if it exists, instead of creating a new one.
>
> Andrus


Re: Queries order on commit.

Posted by Andrus Adamchik <an...@objectstyle.org>.
On Jul 18, 2008, at 7:03 PM, Laurent Marchal wrote:

> I managed to modify directly the object, removing the relation from  
> MasterSchedule->MasterScheduleAux after deleting
> the MasterScheduleAux object.

 From the example in your message, you do not the *exiting*  
MasterScheduleAux objects, you still delete the old ones, and reinsert  
them back. I suggest that in 'createAux' method you first check if  
there is an existing matching MasterScheduleAux object attached to  
"this" MasterSchedule for a given compound FK, and use this object if  
it exists, instead of creating a new one.

Andrus 

Re: Queries order on commit.

Posted by Laurent Marchal <lm...@smaeur.com>.
I managed to modify directly the object, removing the relation from 
MasterSchedule->MasterScheduleAux after deleting
the MasterScheduleAux object.

But the transaction do the DELETE after the INSERTs, so some fresh 
inserted items are deleted.
And i still have "snapshot version changed, don't know what to do" messages.

#
# CODE
#
    private AuxManager<MasterScheduleAux> _auxManager = new 
AuxManager<MasterScheduleAux>() {

        protected MasterScheduleAux createAux(ILookupFieldCodes 
fieldCode, Short seqNo, String value) {
            MasterScheduleAux aux = 
MasterSchedule.this.getObjectContext().newObject(MasterScheduleAux.class);
            aux.setSafc(fieldCode.getID());
            aux.setSaseqno(seqNo);
            aux.setSavalue(value);
            aux.setScheduleId(MasterSchedule.this.getScheduleId());
            aux.setRelatedMasterSchedule(MasterSchedule.this);
           
            return aux;
        }

        protected List<MasterScheduleAux> fetchAuxList() throws 
OpconException {
            return MasterSchedule.this.getRelatedMasterScheduleAux();
        }

        protected void deleteAux(MasterScheduleAux toDelete) throws 
OpconException {
            MasterSchedule.this.getObjectContext().deleteObject(toDelete);
            
MasterSchedule.this.removeFromRelatedMasterScheduleAux(toDelete);
        }
    };

What i do is foreach MasterScheduleAux (SNAME_AUX table) matching the 
SAFC=112 i delete it using deleteAux() then i recreate
the needed ones with createAux().


#
# XML
#
    <db-entity name="SNAME" schema="dbo" catalog="opconxps4">
        <db-attribute name="SKDID" type="INTEGER" isPrimaryKey="true" 
isMandatory="true" length="10"/>
        <db-attribute name="SKDNAME" type="VARCHAR" isMandatory="true" 
length="255"/>
        <db-attribute name="SKDSAM" type="SMALLINT" isMandatory="true" 
length="5"/>
        <db-attribute name="SKDSTART" type="REAL" isMandatory="true" 
length="24"/>
        <db-attribute name="SKDWKDAYS" type="SMALLINT" 
isMandatory="true" length="5"/>
    </db-entity>
    <db-entity name="SNAME_AUX" schema="dbo" catalog="opconxps4">
        <db-attribute name="SAFC" type="SMALLINT" isPrimaryKey="true" 
isMandatory="true" length="5"/>
        <db-attribute name="SASEQNO" type="SMALLINT" isPrimaryKey="true" 
isMandatory="true" length="5"/>
        <db-attribute name="SAVALUE" type="VARCHAR" isMandatory="true" 
length="4000"/>
        <db-attribute name="SKDID" type="INTEGER" isPrimaryKey="true" 
isMandatory="true" length="10"/>
    </db-entity>

    <obj-entity name="MasterSchedule" 
className="com.sma.core.api.master.MasterSchedule" dbEntityName="SNAME" 
superClassName="com.sma.core.api.DataAccessObject">
        <obj-attribute name="scheduleId" type="java.lang.Integer" 
db-attribute-path="SKDID"/>
        <obj-attribute name="scheduleName" type="java.lang.String" 
db-attribute-path="SKDNAME"/>
        <obj-attribute name="scheduleSam" type="java.lang.Short" 
db-attribute-path="SKDSAM"/>
        <obj-attribute name="scheduleStart" type="java.lang.Float" 
db-attribute-path="SKDSTART"/>
        <obj-attribute name="scheduleWorkingDays" type="java.lang.Short" 
db-attribute-path="SKDWKDAYS"/>
    </obj-entity>
    <obj-entity name="MasterScheduleAux" 
className="com.sma.core.api.auxs.MasterScheduleAux" 
dbEntityName="SNAME_AUX" superClassName="com.sma.core.api.DataAccessObject">
        <obj-attribute name="ScheduleId" type="java.lang.Integer" 
db-attribute-path="SKDID"/>
        <obj-attribute name="safc" type="java.lang.Short" 
db-attribute-path="SAFC"/>
        <obj-attribute name="saseqno" type="java.lang.Short" 
db-attribute-path="SASEQNO"/>
        <obj-attribute name="savalue" type="java.lang.String" 
db-attribute-path="SAVALUE"/>
    </obj-entity>

    <db-relationship name="toMasterScheduleAux" source="SNAME" 
target="SNAME_AUX" toDependentPK="true" toMany="true">
        <db-attribute-pair source="SKDID" target="SKDID"/>
    </db-relationship>
    <db-relationship name="toMasterSchedule" source="SNAME_AUX" 
target="SNAME" toMany="false">
        <db-attribute-pair source="SKDID" target="SKDID"/>
    </db-relationship>

#
# LOG
#

--- will run 2 queries.
--- transaction started.
INSERT INTO dbo.SNAME_AUX (SAFC, SASEQNO, SAVALUE, SKDID) VALUES (?, ?, 
?, ?)
[batch bind: 1->SAFC:112, 2->SASEQNO:2, 3->SAVALUE:'1', 4->SKDID:505]
[batch bind: 1->SAFC:112, 2->SASEQNO:1, 3->SAVALUE:'456', 4->SKDID:505]
[batch bind: 1->SAFC:112, 2->SASEQNO:0, 3->SAVALUE:'517', 4->SKDID:505]
=== updated 3 rows.
DELETE FROM dbo.SNAME_AUX WHERE SAFC = ? AND SASEQNO = ? AND SKDID = ?
[batch bind: 1->SAFC:112, 2->SASEQNO:1, 3->SKDID:505]
[batch bind: 1->SAFC:112, 2->SASEQNO:0, 3->SKDID:505]
=== updated 4 rows.
snapshot version changed, don't know what to do... Old: 
org.apache.cayenne.DataRow@1f6c2a9[values={SASEQNO=2, SAVALUE=1, 
SAFC=112, SKDID=505}, version=-9223372036854774826, 
replaces=-9223372036854775808], New: 
org.apache.cayenne.DataRow@165f5a4[values={SASEQNO=2, SAVALUE=1, 
SAFC=112, SKDID=505}, version=-9223372036854774632, 
replaces=-9223372036854775808]
postSnapshotsChangeEvent: [SnapshotEvent] source: 
org.apache.cayenne.access.DataRowStore@367013, deleted 2 id(s), 
indirectly modified 1 id(s)
+++ transaction 
committed.                                                                                                                    



Andrus Adamchik wrote:
>
> On Jul 18, 2008, at 5:10 PM, Laurent Marchal wrote:
>
>> Hi !
>>
>> I would like to do some DELETEs and INSERTs in one transaction, but 
>> since DELETE is made after the INSERTs, it deletes the fresh inserted 
>> values...
>
> Yeah, weird. Could you post some sample code? Looking at your log, you 
> delete a dependent object from a relationship, and then readd a new 
> one? IMO the solution should be modifying the existing object instead 
> of doing delete/insert.
>
>
>>   Is there a way to force cayenne to do DELETE  before all others 
>> operations when a commit occurs ?
>
> The ordering algorithm is based on the changed objects graph 
> dependency analysis, to provide ordering consistent with FK 
> constraints. It does not address a scenario of object identity (PK) 
> disappearing and then reappearing again. There is probably a way to 
> customize it (although likely not an easy one), but I still feel like 
> this is not a valid case.
>
> Andrus
>
>
>

Re: Queries order on commit.

Posted by Andrus Adamchik <an...@objectstyle.org>.
On Jul 18, 2008, at 5:10 PM, Laurent Marchal wrote:

> Hi !
>
> I would like to do some DELETEs and INSERTs in one transaction, but  
> since DELETE is made after the INSERTs, it deletes the fresh  
> inserted values...

Yeah, weird. Could you post some sample code? Looking at your log, you  
delete a dependent object from a relationship, and then readd a new  
one? IMO the solution should be modifying the existing object instead  
of doing delete/insert.


>   Is there a way to force cayenne to do DELETE  before all others  
> operations when a commit occurs ?

The ordering algorithm is based on the changed objects graph  
dependency analysis, to provide ordering consistent with FK  
constraints. It does not address a scenario of object identity (PK)  
disappearing and then reappearing again. There is probably a way to  
customize it (although likely not an easy one), but I still feel like  
this is not a valid case.

Andrus