Map element columns are added to audit table's primary key unnecessarily.

Description

This is based on an additional problem notated in HHH-11841.

Lets assume the following example:

This is mapped using

where ValueType is a CompositeUserType implementation.

When ORM generates this mapping, only the owner Category and Item primary keys are used to generate the association table's primary key constraint; however, Envers generates its audit table primary key including the number and text columns, which is problematic for storing nullable properties in this use case.

There is also predicates which the audit reader adds when querying such a mapping that doesn't use property is null but instead uses property = null.

Environment

None

Activity

Show:
Chris Cranford
September 6, 2017, 3:38 PM

When I map the collection using a CompositeUserType, the query works and I can navigate the association (but then I get a problem with nullable properties in the composite user type, because Envers creates a SQL where clause with checks for "property=null" instead of "property is null".

When you do this, are you manually creating the database schema?

It seems Envers places the number and text fields as part of the primary key and therefore the not-null="false" is omitted and therefore the Envers schema gets created as follows:

While the ORM created table is as follows:

Based on your mappings, we should likely be doing the same with the Envers audit table and creating the primary key as:

and marking the number and text fields as nullable per the not-null="false" mapping.

Chris Cranford
October 19, 2017, 5:24 AM

After doing a bit more research, I see why Envers elected to use the map-values as part of the PK.

Lets consider the following element collection mapping:

We would ultimately have a middle table that would look something like this (where fields marked with * represent the PK):

SomeEntity_id *

theMap_KEY *

theMap *

REV *

REVTYPE

Let us assume our owning entity, SomeEntity has a PK of 1 and our SomeEnum has a value-set of A and B. If we insert into the above map OtherEntity with PK of 2 with SomeEnum.A, the table would consist of the following state:

SomeEntity_id *

theMap_KEY *

theMap *

REV *

REVTYPE

1

2

A

1

0

In the next transaction, we decide to change the associated SomeEnum value with OtherEntity#2 from A to B.

SomeEntity_id *

theMap_KEY *

theMap *

REV *

REVTYPE

1

2

A

1

0

1

2

A

2

2

1

2

B

2

1

One possible solution here may be to make use of REVTYPE to drive the difference in PK for the two rows rather than theMap map-value. We'll need to make some tests to verify this doesn't break any existing functionality and determine a strategy for how best to implement dealing with the schema change.

Assignee

Chris Cranford

Reporter

Andreas Haumer

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure