Only delete those referenced entities that are no longer present in an aggregate. Use an upsert for all others [DATAJDBC-224]

[spring-projects-issues]

trafficstars

When updating an aggregate we should not delete all referenced entities in the database. Instead we should only delete those no longer present in the database and use an upsert/merge on the others.

There are a couple of things to watch out for.

1. Support for upsert/merge statements varies among databases. We might need a fallback.
2. entities might have composite ids, in which case a simple in ( ... ) might not work. We need to figure out how we might construct an equivalent statement for the supported databases.
3. Databases and drivers will have various limits for length of SQL statements. We should at least know about these and take them into consideration.

This is a subtask of #437

Jens Schauder opened this as DATAJDBC-224

[spring-projects-issues]

Tyler K Van Gorder commented

I have implemented this is a past life by: 

 

1. Insert/Update Aggregate
2. Insert/Update Referenced Entities (using the current, in-memory list)
3. Delete orphans where the referenced entity's parent key is aggregate ID and reference entity's ID is not in the list of IDs from the current in-memory list)

There is one "gotcha" with step 3: If the list of referenced entities is above a certain threshold (and its database dependent) then your in-clause will have too many values. I remember Oracle complaining when the number of values exceeded 1000.

You can work around this by ordering the list of in-memory Ids, using a batch size (say 100 elements?) and bounding your delete by the first and last values of each batch:

DELETE FROM <CHILD TABLE>
WHERE
PARENT_ID = :AGGREGATE_ID AND
CHILD_ID NOT in (:id1, :id2, ......:id_last) AND
CHILD_ID BETWEEN :id1 AND :id_last

This works if your IDs are numbers...I think it would also work for IDs that were strings, so long as your ordering algorithm in Java matches that of the database

[spring-projects-issues]

clgraf commented

I don't know if is it ok to ask this question here, but I really don't understand yet how the process works... this issue is really important to my project and I realized it is on the sprint right now... so It would be released soon?

[spring-projects-issues]

Jens Schauder commented

Hi clgraf, sorry the sprint information was misleading. For Neumann we are busy with some changes that will break some API (nothing to drastic, but still). Since the changes break the API they must all go into Neumann and this issue isn't one of it, so it is more likely to come in Neumann+1

[Husan]

Tyler K Van Gorder commented

I have implemented this is a past life by: 

 

1. Insert/Update Aggregate
2. Insert/Update Referenced Entities (using the current, in-memory list)
3. Delete orphans where the referenced entity's parent key is aggregate ID and reference entity's ID is not in the list of IDs from the current in-memory list)

There is one "gotcha" with step 3: If the list of referenced entities is above a certain threshold (and its database dependent) then your in-clause will have too many values. I remember Oracle complaining when the number of values exceeded 1000.

You can work around this by ordering the list of in-memory Ids, using a batch size (say 100 elements?) and bounding your delete by the first and last values of each batch:

DELETE FROM <CHILD TABLE>
WHERE
PARENT_ID = :AGGREGATE_ID AND
CHILD_ID NOT in (:id1, :id2, ......:id_last) AND
CHILD_ID BETWEEN :id1 AND :id_last

This works if your IDs are numbers...I think it would also work for IDs that were strings, so long as your ordering algorithm in Java matches that of the database

1. Use upserts on Bs and only delete those not longer present
2. have function or attribute carrying about change type information

I think that the 1 st option must be default behavior or may be switched on by some annotation on Aggregate Root. 2 nd option may be must be behavior when developer define which row must be updated which insert and which must be deleted. May be similar to isNew function must be changeOperation(or changeType) function in child aggregate which must be implemented from some interfays(like Persistable) and return values as "INSERT", "UPDATE", "DELETE", "NONE"(means non operation must be performent on reference collection element). Example:

class AgregateRoot implements Persistable{
     .........
     @UpdateStrategy("UPSERT")
     Set<ChileAggregate> aggregate;
}

class ChildAggregate implements SomeInterface{
       .......
      public changeType(){
              return "UPDATE";
      }
}