Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "EclipseLink/Development/JPA 2.0/ordered lists"
(→Non Contiguous Index values) |
(→Persisting Order) |
||
(9 intermediate revisions by the same user not shown) | |||
Line 8: | Line 8: | ||
to an index. | to an index. | ||
− | See JPA 2.0 ED section 10.1. | + | See JPA 2.0 ED section 10.1.39 for details. |
Additional requirements | Additional requirements | ||
Line 90: | Line 90: | ||
</source> | </source> | ||
− | ===Open | + | ==Implementation== |
+ | ===Extensions=== | ||
+ | ====Handling invalid order list==== | ||
+ | Eclipselink proprietary OrderCorrection annotation uses OrderCorrectionType value | ||
+ | to define what should be done if the list order read from data base is invalid: | ||
+ | has nulls, duplicates, negative values, values greater/equal to list size. | ||
+ | Either the list order is repaired (default behavior) or an exception is thrown. | ||
+ | See extensive comment in org.eclipse.persistence.annotations.OrderCorrectionType | ||
+ | <source lang="java"> | ||
+ | public enum OrderCorrectionType { | ||
+ | // repair read list | ||
+ | READ, | ||
+ | // repair read list; when write out the list, repair it in the db, too. (default) | ||
+ | READ_WRITE, | ||
+ | // throw exception | ||
+ | EXCEPTION | ||
+ | } | ||
+ | </source> | ||
+ | Example: | ||
+ | <source lang="java"> | ||
+ | @OrderColumn(name="ORDER_COLUMN") | ||
+ | @OrderCorrection(EXCEPTION) | ||
+ | List<String> designations; | ||
+ | </source> | ||
+ | =====Custom repair===== | ||
+ | Order list repair method implemented by Eclipselink could be overridden by user: | ||
+ | override correctOrderList method in OrderedListContainerPolicy class; | ||
+ | using customized set the overridden container policy into the mapping: | ||
+ | <source lang="java"> | ||
+ | collectionMapping.setContainerPolicy(new MyOrderedListContainerPolicy(mapping.getContainerPolicy().getContainerClass())); | ||
+ | </source> | ||
+ | ====Order list column may be of non-numeric type in the data base==== | ||
+ | As long as jdbc can read/write the value as an integer the actual column type doesn't matter. | ||
+ | |||
+ | ===Limitations=== | ||
+ | ====OneToMany target row updated twice==== | ||
+ | Currently columns explicitly mapped to the object can't be updated in the same statement | ||
+ | with the columns that are not (such as order column). | ||
+ | |||
+ | Adding or removing element from a list mapped with bidirectional OneToMany always causes two updates: | ||
+ | <source lang="java"> | ||
+ | UPDATE OL_EMPLOYEE SET MANAGER_ID = null WHERE (EMP_ID = 51) | ||
+ | UPDATE OL_EMPLOYEE SET MANAGED_ORDER = null WHERE (EMP_ID = 51) | ||
+ | </source> | ||
+ | In unidirectional case two updates are issued only in case the added or removed object has changed: | ||
+ | <source lang="java"> | ||
+ | UPDATE OL_CHILD SET L_NAME = 'Doe' WHERE (CHILD_ID = 51) | ||
+ | UPDATE OL_CHILD SET PARENT_ID = null, CHILDREN_ORDER = null WHERE ((PARENT_ID = 1) AND (CHILD_ID = 51)) | ||
+ | </source> | ||
+ | ====No duplicate support==== | ||
+ | Duplicate support is available only for ElementCollection of basic type (Strings etc.), | ||
+ | it doesn't work with joining. | ||
+ | ====No null support==== | ||
+ | Nulls supported only for ElementCollection of basic type (Strings etc.), | ||
+ | in case of joining a list containing a single element which is null is read as an empty list | ||
+ | |||
+ | === Implementation in Core === | ||
+ | CollectionMapping is the main class in order field support that defines methods allowing to set it up. | ||
+ | Order list field is supported for the following collection mappings: | ||
+ | OneToManyMapping, UnidirectionalOneToManyMapping, ManyToManyMapping, AggregateCollectionMapping, DirectCollectionMapping; | ||
+ | not supported for DirectMapMapping. | ||
+ | <source lang="java"> | ||
+ | // list order field supported for a collectionMapping if and only if the following returns true: | ||
+ | collectionMapping.isListOrderFieldSupported() | ||
+ | </source> | ||
+ | |||
+ | To set list order field on a collectionMapping: | ||
+ | <source lang="java"> | ||
+ | DatabaseField orderField = new DatabaseField("MY_ORDER_FIELD"); | ||
+ | collectionMapping.setListOrderField(orderField); | ||
+ | </source> | ||
+ | Order field must always be in the same table with target foreign key(s), therefore user doesn't need to specify order field's table (though still may do that). | ||
+ | |||
+ | To specify OrderCorrectionType: | ||
+ | <source lang="java"> | ||
+ | collectionMapping.setOrderCorrectionType(OrderCorrectionType.EXCEPTION); | ||
+ | </source> | ||
+ | By default order correction type is READ_WRITE for list class assignable from IndirectList; READ otherwise. | ||
+ | |||
+ | ==Open issues== | ||
+ | ==== Target optimistic locking ==== | ||
+ | If the target of OrderColumn-annotated list uses OptimisticLocking - should we change version of the target object each time the order value is changed? | ||
+ | |||
+ | UnidirectionalOneToManyMapping is a similar case - by default we change the target version each time it changes its collection; also there are two boolean flags defined in the mapping which allow to alter that behaviour: shouldIncrementTargetLockValueOnAddOrRemoveTarget and shouldIncrementTargetLockValueOnDeleteSource. | ||
+ | |||
+ | ==Appendix: issues raised during design== | ||
==== Non Contiguous Index values ==== | ==== Non Contiguous Index values ==== | ||
Line 130: | Line 215: | ||
The approach is not perfect: it doesn't do anything when the last object is added or removed. Also it's incompatible with OptimisticLocking on the target. | The approach is not perfect: it doesn't do anything when the last object is added or removed. Also it's incompatible with OptimisticLocking on the target. | ||
+ | |||
+ | ===== Non Contiguous Index values - a new approach===== | ||
+ | Use several modes to validate lists for possible damage in their order. | ||
+ | <source lang="java"> | ||
+ | /** | ||
+ | * OrderColumnValidationMode used only in case listOrderField != null. | ||
+ | * Indicates whether validation of listOrderField values should be | ||
+ | * performed when the objects are read from the db: | ||
+ | * there should be no rows with listOrderField set to null, no "holes" in order. | ||
+ | */ | ||
+ | public static enum OrderColumnValidationMode { | ||
+ | /** Don't validate */ | ||
+ | NONE, | ||
+ | /** Validate, if validation fails set a flag in IndirectList (must use IndirectList as container class) */ | ||
+ | CORRECTION, | ||
+ | /** Validate, if validation fails throw exception */ | ||
+ | EXCEPTION | ||
+ | } | ||
+ | </source> | ||
+ | # NONE. Always could be used, fastest: no attempt is made neither to detect "broken" list on read, nor to detect (or correct) "broken" list on write. Use this if there is absolute confidence that all lists have correct order in the db. | ||
+ | # CORRECTION could be only used if the attribute value is IndirectList - when the "broken" list is read in the "broken" flag in IndirectionList is set, which forces updating of all indexes when any change to the list occur. Use this if "the show must go" is the most important requirement and the order in the list is less important: there are many ways to repair a broken order in the db - the way CORRECTION does repair is not necessarily the one the user wants. The list members read using ORDER BY, CORRECTION option imposes this order back on to the data base. That means correction could be different on different db platforms - ORDER BY on Oracle reads null last, on MySQL first (or vise versa?). | ||
+ | #EXCEPTION indicates that exception should be thrown when the "broken" list is read in. That is required in a lot of case - to know that the read list is correct in the data base. If the order is broken then the user catches exception and before proceeding any further fixes the list in the db. | ||
+ | |||
+ | Define a new Eclipselink annotation | ||
+ | <source lang="java"> | ||
+ | @Target({METHOD, FIELD}) @Retention(RUNTIME) | ||
+ | public @interface OrderColumnValidation { | ||
+ | OrderColumnValidationMode value default NONE; | ||
+ | } | ||
+ | </source> | ||
==== Indexing new elements in un-instantiated IndirectLists ==== | ==== Indexing new elements in un-instantiated IndirectLists ==== | ||
Line 135: | Line 250: | ||
When an IndirectList has new items added it does not force the entire list to be loaded. This means that during commit you need to assign new index values without necessarily having all of the contents of the list loaded. In my [[EclipseLink/Examples/JPA/Collectionordering]] example for EclipseLink 1.1 i am addressing this with a combination of a query for the max index value as well as forcing an optimistic locking update of the parent object if it supports it. | When an IndirectList has new items added it does not force the entire list to be loaded. This means that during commit you need to assign new index values without necessarily having all of the contents of the list loaded. In my [[EclipseLink/Examples/JPA/Collectionordering]] example for EclipseLink 1.1 i am addressing this with a combination of a query for the max index value as well as forcing an optimistic locking update of the parent object if it supports it. | ||
− | == | + | ==== OrderColumn table ==== |
− | # | + | OrderColumn annotation defines a table in which the order column is located. When the table is not specified, by default the table used is either join table (if exists) or otherwise the first table of the target entity. |
− | # | + | Which tables explicitly specified in OrderColumn annotation we should support? |
− | # | + | # The secondary table of an Entity (i.g. SALARY table in Employee example) - should be supported. |
− | # | + | # In case the join table exists - should we support target table being specified (i.g. ManyToMany Employee -> Project, should support ORDER field in PROJECT table - though it's EMP_PROJ join table by default)? |
− | + | ## Note that this may cause essential variation in behaviour, for instance duplicated could be only supported if the order field is in join table). | |
− | + | # User specified a table which is neither join table nor part of this entity (nor CollectionTable in ElementCollection). We should NOT support this, ask the spec. to explicitly prohibit this case. | |
− | + | ||
− | + | ==== Duplicate support ==== | |
+ | # Should duplicate support be part of this feature? | ||
+ | # Should it be done only for ManyToMany or for all cases when it's possible (all cases with join table)? | ||
+ | # Note that duplicate support complicates privately owned case - until now the removal of privately owned child meant that it should be deleted, now we'll have to verify whether there's a suplicate of the removed child still left in the collection - in that case the removed child should NOT be deleted. | ||
+ | |||
+ | ==== "Two-way" order support ==== | ||
+ | Should we (could we?) support two OrderFields used by two ManyTopMany mappings pointing at each other (Employee.projects and Projects.employees; say add ORDER_EMP and ORDER_PROJ fields to the join table)? | ||
+ | |||
+ | ==== Contraints in the DB - is that possible? ==== | ||
+ | Is it possible to set index-like constraint on the db that would enforce uniqueness of the target with particular source and order value (combination of ORDER and FK_EMP_ID should be unique). If it's possible implementation will be more difficult. | ||
+ | |||
+ | ==== Target optimistic locking ==== | ||
+ | If the target of OrderColumn-annotated list uses OptimisticLocking - should we change version of the target object each time the order value is changed? | ||
+ | |||
+ | UnidirectionalOneToManyMapping is a similar case - by default we change the target version each time it changes its collection; also there are two boolean flags defined in the mapping which allow to alter that behaviour: shouldIncrementTargetLockValueOnAddOrRemoveTarget and shouldIncrementTargetLockValueOnDeleteSource. |
Latest revision as of 18:07, 22 June 2009
Contents
- 1 Persisting Order
- 1.1 Issue Summary
- 1.2 General Solution
- 1.3 Requirements
- 1.4 Implementation
- 1.5 Open issues
- 1.6 Appendix: issues raised during design
Persisting Order
Issue Summary
JPA 2.0 specification has added functionality requiring the provider to persist list item indexes when mapped by the user. The provider must also track changes to an index.
See JPA 2.0 ED section 10.1.39 for details.
Additional requirements
- Solution should enable the storage of duplicates in ManyToMany lists. See bug 256978.
- Must offer flexibility in the naming and type of the database index columns to ensure users implementing their own work-arounds can easily upgrade to this implementation
General Solution
Ordered List support is currently available in EclipseLink. Order of collection items is tracked and updates and merges occur as a result of changes. What this feature requires is that an additional field be managed by EclipseLink that is not mapped in the object model, similar to the Uni-directional OneToMany mappings. This additional field will store the index of each item in the collection.
As all collection mappings will need to support this functionality the best approach is to make CollectionMapping able to write indexes. ChangeRecords are already aware of order changes but the mappings are unable to write the changes.
It would be favourable to be able to restrict the number of updates of the target row to one even if the target object has other changes. This may be possible by allowing the mapping to force the write of the target object and add index to the query.
There is a potential enhancement that allows the provider to fabricate artificial indexes to allow for efficient insertion. For example if there were 3 items in the list EclipseLink could assign index 1, 3, and 9 to these objects. Order would be maintained and should another instance be inserted into the list only one row update would be required instead of the minimal two. This should only be considered as a future enhancement to this feature.
Requirements
Main use case
The Collection used in ToMany relationship should be a List.
public class Employee { private List<Dealer> dealers; ... @OneToMany(cascade={PERSIST, MERGE}) @JoinColumn(name="FK_EMP_ID") @OrderColumn(name="ORDER") public List<Dealer> getDealers() { return dealers; } ... }
List members saved together with their indexes in the List:
Employee employee = new Employee(); Dealer dealerA = new Dealer("A"); employee.getDealers().add(dealerA); Dealer dealerB = new Dealer("B"); employee.getDealers().add(dealerB); Dealer dealerC = new Dealer("C"); employee.getDealers().add(dealerC); em.persist(employee);
DEALER_ID FK_EMP_ID NAME ORDER 33 5 A 0 34 5 B 1 35 5 C 2
The indexes updated if the List member(s) removed:
employee.getDealers().remove(dealerB);
DEALER_ID FK_EMP_ID NAME ORDER 33 5 A 0 35 5 C 1
the order changed:
employee.getDealers().remove(dealerA); employee.getDealers().add(dealerA);
DEALER_ID FK_EMP_ID NAME ORDER 33 5 A 1 35 5 C 0
new member(s) added:
Dealer dealerD = new Dealer("D"); employee.getDealers().add(dealerD);
DEALER_ID FK_EMP_ID NAME ORDER 33 5 A 1 35 5 C 0 35 5 D 2
Implementation
Extensions
Handling invalid order list
Eclipselink proprietary OrderCorrection annotation uses OrderCorrectionType value to define what should be done if the list order read from data base is invalid: has nulls, duplicates, negative values, values greater/equal to list size. Either the list order is repaired (default behavior) or an exception is thrown. See extensive comment in org.eclipse.persistence.annotations.OrderCorrectionType
public enum OrderCorrectionType { // repair read list READ, // repair read list; when write out the list, repair it in the db, too. (default) READ_WRITE, // throw exception EXCEPTION }
Example:
@OrderColumn(name="ORDER_COLUMN") @OrderCorrection(EXCEPTION) List<String> designations;
Custom repair
Order list repair method implemented by Eclipselink could be overridden by user: override correctOrderList method in OrderedListContainerPolicy class; using customized set the overridden container policy into the mapping:
collectionMapping.setContainerPolicy(new MyOrderedListContainerPolicy(mapping.getContainerPolicy().getContainerClass()));
Order list column may be of non-numeric type in the data base
As long as jdbc can read/write the value as an integer the actual column type doesn't matter.
Limitations
OneToMany target row updated twice
Currently columns explicitly mapped to the object can't be updated in the same statement with the columns that are not (such as order column).
Adding or removing element from a list mapped with bidirectional OneToMany always causes two updates:
UPDATE OL_EMPLOYEE SET MANAGER_ID = null WHERE (EMP_ID = 51) UPDATE OL_EMPLOYEE SET MANAGED_ORDER = null WHERE (EMP_ID = 51)
In unidirectional case two updates are issued only in case the added or removed object has changed:
UPDATE OL_CHILD SET L_NAME = 'Doe' WHERE (CHILD_ID = 51) UPDATE OL_CHILD SET PARENT_ID = null, CHILDREN_ORDER = null WHERE ((PARENT_ID = 1) AND (CHILD_ID = 51))
No duplicate support
Duplicate support is available only for ElementCollection of basic type (Strings etc.), it doesn't work with joining.
No null support
Nulls supported only for ElementCollection of basic type (Strings etc.), in case of joining a list containing a single element which is null is read as an empty list
Implementation in Core
CollectionMapping is the main class in order field support that defines methods allowing to set it up. Order list field is supported for the following collection mappings: OneToManyMapping, UnidirectionalOneToManyMapping, ManyToManyMapping, AggregateCollectionMapping, DirectCollectionMapping; not supported for DirectMapMapping.
// list order field supported for a collectionMapping if and only if the following returns true: collectionMapping.isListOrderFieldSupported()
To set list order field on a collectionMapping:
DatabaseField orderField = new DatabaseField("MY_ORDER_FIELD"); collectionMapping.setListOrderField(orderField);
Order field must always be in the same table with target foreign key(s), therefore user doesn't need to specify order field's table (though still may do that).
To specify OrderCorrectionType:
collectionMapping.setOrderCorrectionType(OrderCorrectionType.EXCEPTION);
By default order correction type is READ_WRITE for list class assignable from IndirectList; READ otherwise.
Open issues
Target optimistic locking
If the target of OrderColumn-annotated list uses OptimisticLocking - should we change version of the target object each time the order value is changed?
UnidirectionalOneToManyMapping is a similar case - by default we change the target version each time it changes its collection; also there are two boolean flags defined in the mapping which allow to alter that behaviour: shouldIncrementTargetLockValueOnAddOrRemoveTarget and shouldIncrementTargetLockValueOnDeleteSource.
Appendix: issues raised during design
Non Contiguous Index values
What if the collection is altered directly (not through Eclipselink) in the db so that the indexes are no longer a contiguous sequence of numbers from 0 to size-1?
Some elements removed from the beginning or the middle:
DEALER_ID FK_EMP_ID NAME ORDER 35 5 C 0 35 5 D 2
Some elements' order is null;
DEALER_ID FK_EMP_ID NAME ORDER 35 5 C 35 5 D
Some elements have equal ordered id values
DEALER_ID FK_EMP_ID NAME ORDER 35 5 C 3 35 5 D 3
How do we read back such "damaged' lists into Java app.?
- Should we throw an exception?
- Try to cure the list by assigning (sometimes randomly) the indexes?
- Insert null element into the List that corresponds to missing index (note that then the list size will be greater than the real number of objects)?
One possible approach is to verify the previous order value each time the order value is changed, for instance, in case the order value should be changed drom to to three, instead of simply assigning the new value:
UPDATE DEALER SET ORDER = 3 WHERE DEALER_ID = 35
verify that the old value was correct:
UPDATE DEALER SET ORDER = 3 WHERE DEALER_ID = 35 AND ORDER = 2
In case either no rows or more than one rows were updated - update all the rows for all Dealers referencing this Employee.
The approach is not perfect: it doesn't do anything when the last object is added or removed. Also it's incompatible with OptimisticLocking on the target.
Non Contiguous Index values - a new approach
Use several modes to validate lists for possible damage in their order.
/** * OrderColumnValidationMode used only in case listOrderField != null. * Indicates whether validation of listOrderField values should be * performed when the objects are read from the db: * there should be no rows with listOrderField set to null, no "holes" in order. */ public static enum OrderColumnValidationMode { /** Don't validate */ NONE, /** Validate, if validation fails set a flag in IndirectList (must use IndirectList as container class) */ CORRECTION, /** Validate, if validation fails throw exception */ EXCEPTION }
- NONE. Always could be used, fastest: no attempt is made neither to detect "broken" list on read, nor to detect (or correct) "broken" list on write. Use this if there is absolute confidence that all lists have correct order in the db.
- CORRECTION could be only used if the attribute value is IndirectList - when the "broken" list is read in the "broken" flag in IndirectionList is set, which forces updating of all indexes when any change to the list occur. Use this if "the show must go" is the most important requirement and the order in the list is less important: there are many ways to repair a broken order in the db - the way CORRECTION does repair is not necessarily the one the user wants. The list members read using ORDER BY, CORRECTION option imposes this order back on to the data base. That means correction could be different on different db platforms - ORDER BY on Oracle reads null last, on MySQL first (or vise versa?).
- EXCEPTION indicates that exception should be thrown when the "broken" list is read in. That is required in a lot of case - to know that the read list is correct in the data base. If the order is broken then the user catches exception and before proceeding any further fixes the list in the db.
Define a new Eclipselink annotation
@Target({METHOD, FIELD}) @Retention(RUNTIME) public @interface OrderColumnValidation { OrderColumnValidationMode value default NONE; }
Indexing new elements in un-instantiated IndirectLists
When an IndirectList has new items added it does not force the entire list to be loaded. This means that during commit you need to assign new index values without necessarily having all of the contents of the list loaded. In my EclipseLink/Examples/JPA/Collectionordering example for EclipseLink 1.1 i am addressing this with a combination of a query for the max index value as well as forcing an optimistic locking update of the parent object if it supports it.
OrderColumn table
OrderColumn annotation defines a table in which the order column is located. When the table is not specified, by default the table used is either join table (if exists) or otherwise the first table of the target entity. Which tables explicitly specified in OrderColumn annotation we should support?
- The secondary table of an Entity (i.g. SALARY table in Employee example) - should be supported.
- In case the join table exists - should we support target table being specified (i.g. ManyToMany Employee -> Project, should support ORDER field in PROJECT table - though it's EMP_PROJ join table by default)?
- Note that this may cause essential variation in behaviour, for instance duplicated could be only supported if the order field is in join table).
- User specified a table which is neither join table nor part of this entity (nor CollectionTable in ElementCollection). We should NOT support this, ask the spec. to explicitly prohibit this case.
Duplicate support
- Should duplicate support be part of this feature?
- Should it be done only for ManyToMany or for all cases when it's possible (all cases with join table)?
- Note that duplicate support complicates privately owned case - until now the removal of privately owned child meant that it should be deleted, now we'll have to verify whether there's a suplicate of the removed child still left in the collection - in that case the removed child should NOT be deleted.
"Two-way" order support
Should we (could we?) support two OrderFields used by two ManyTopMany mappings pointing at each other (Employee.projects and Projects.employees; say add ORDER_EMP and ORDER_PROJ fields to the join table)?
Contraints in the DB - is that possible?
Is it possible to set index-like constraint on the db that would enforce uniqueness of the target with particular source and order value (combination of ORDER and FK_EMP_ID should be unique). If it's possible implementation will be more difficult.
Target optimistic locking
If the target of OrderColumn-annotated list uses OptimisticLocking - should we change version of the target object each time the order value is changed?
UnidirectionalOneToManyMapping is a similar case - by default we change the target version each time it changes its collection; also there are two boolean flags defined in the mapping which allow to alter that behaviour: shouldIncrementTargetLockValueOnAddOrRemoveTarget and shouldIncrementTargetLockValueOnDeleteSource.