2

I'm using EclipseLink's History feature.

As far as I understand there is currently no support for actually creating the shadow table. I'm using JPA 2.1 automated schema generation for all my base tables. Works fine.

Now I'm looking for a way to automate the process of creating the shadow tables. For each base table there should be a shadow table with a few extra columns. Otherwise the shadow table has the exact structure of the table it is shadowing.

I have more than 50 entities in the model and with a bit of many-to-many relations thrown in it all maps to around 55 physical tables. I wouldn't want to maintain DDL create scripts for all the shadow tables.

Right now the project is database agnostic (at least for the usual suspects) and I would like to keep it that way. Being tied to EclipseLink is not a problem so an EclipseLink specific solution is ok.

So, how to create them shadow tables ?

It would be ok to have a sub-class for representing each history table. Say if the base class is Car, then:

@Entity
@Table(name="CAR_HIST")
public class ClassHist extends Car {
   ...
   // some extra audit fields here
}

and with an @Inheritance(strategy=InheritanceType.TABLE_PER_CLASS) annotation on Car it will actually do the job with JPA schema generation. So, almost there with this. Except for many-to-many relationship tables where there's no entity I can get my hands on, so cannot get a shadow table generated for the relationship table.

UPDATE

Turns out using inheritance to create the shadow tables isn't such a great idea. The reason is that JPA JPQL queries are polymorphic. This means that a query like SELECT a FROM Car will actually go into the CAR_HIST table as well because CarHist is just a sub-class of Car. You get the UNION of the SELECT from those tables. Obviously not want I want. The other reason why it is not a great idea is that the sub-class used for history will inherit the primary key of its parent. That is also just plain wrong.

UPDATE 2

I've found there's an RFE for this feature in EclipseLink.

peterh
  • 18,404
  • 12
  • 87
  • 115
  • Are you sure that this is what you want? For many problem domains, object graphs modeled into a well-normalized database will result in information that is distributed to many tables. Shadow tables such as these will not be simple to represent as an audit history. What you want is to create an audit history trail, is it not? – scottb Feb 18 '16 at 05:12
  • @scottb. Yes, correct, what I want is to create an audit trail. I'm not interested in point-in-time restore from such tables or what not. It's an audit trail and it will only ever be used for human inspection and it will be purged regularly. – peterh Feb 18 '16 at 08:49
  • Since you're already using JPA, you could go one step further and use Java EE for container management. The interceptor facility in Java EE would enable you to provide an AOP-like mechanism for generating shadow table records at commit time. I would hesitate to use entity inheritance for the shadow table as it means that the primary key must be used for both tables whereas in actuality in the shadow table it becomes a foreign key. – scottb Feb 18 '16 at 19:27
  • @scottb. Actually I'm in a Java SE environment, not Java EE. Anyway you are right about the shadow table having the same primary key as the one it is shadowing which is why this approach will fail, not on the first change to an entity but the *second* time you attempt to make a change to an entity. Didn't think of that. Arghh. Don't think I can use the inheritance path unless there's some way *not* to inherit the `@Id` annotation. – peterh Feb 19 '16 at 09:31

0 Answers0