ElementCollection causes unfortunate primary keys in CollectionTable

[S-Tim]

Scenario

• camunda-bpm-taskpool version: any
• Camunda BPM version: any
• 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 path source. 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]

relates to #794. Won't fix for now