When generating the DDLs for the JPA view using mvn -Pgenerate-sql -f view/jpa as stated in the docs the primary keys of the collection tables of the element collections are unfortunate in some cases.
Current Behaviour
Usually for JPA providers the primary key for every collection table (@CollectionTable) for an element collection (@ElementCollection) consists of all the fields specified in the joinColums as well as every field in the @Embeddable.
See also JPA Wikibooks. This can cause problems for complex embeddables because the index for the primary key might become too large. For example, for MariaDB (with InnoDB) the maximum size of an index is 767bytes. Source.
This means, that the combination of task_id, path and value in plf_task_payload_attributes cannot exceed this limit, thereby greatly limiting the possible size of the value.
There also seems to be no validation regarding the length of the value in Polyflow when writing to the plf_task_payload_attribute table. An error is visible in the log but of course the update does not happen.
Wanted Behaviour
It would be nice to allow for larger values. This could be achieved by converting the element collection to a one-to-many relationship and specifying the primary key manually as the combination of task_id and pathsource. Although this would hurt the performance because the value would not be part of any index anymore and specifying another index with all three fields faces the same limitations as the index for the PK. Maybe an index on the task_id and path would be enough to quickly find the right row.
Maybe something like a full-text index could solve this issue
S-Tim
commented
1 year ago
Scenario
mvn -Pgenerate-sql -f view/jpaas stated in the docs the primary keys of the collection tables of the element collections are unfortunate in some cases.Current Behaviour
Usually for JPA providers the primary key for every collection table (
@CollectionTable) for an element collection (@ElementCollection) consists of all the fields specified in thejoinColumsas well as every field in the@Embeddable. See also JPA Wikibooks. This can cause problems for complex embeddables because the index for the primary key might become too large. For example, for MariaDB (with InnoDB) the maximum size of an index is 767bytes. Source. This means, that the combination oftask_id,pathandvalueinplf_task_payload_attributescannot exceed this limit, thereby greatly limiting the possible size of thevalue.There also seems to be no validation regarding the length of the
valuein Polyflow when writing to theplf_task_payload_attributetable. An error is visible in the log but of course the update does not happen.Wanted Behaviour
It would be nice to allow for larger values. This could be achieved by converting the element collection to a one-to-many relationship and specifying the primary key manually as the combination of
task_idandpathsource. Although this would hurt the performance because the value would not be part of any index anymore and specifying another index with all three fields faces the same limitations as the index for the PK. Maybe an index on thetask_idandpathwould be enough to quickly find the right row. Maybe something like a full-text index could solve this issue