Temporal Entity Example/docs/wiki/TemporalEntity.mediawiki

{{bug|361016}}
__NOTOC__
== EclipseLink JPA Futures Example ==

This example is intended to illustrate how entity versions can be stored and queried in a database. Unlike the history functionality of EclipseLink this example is focused on creating versions of an entity that may take effect in the future. 

'''Requirements'''
* Optimize queries against current version. Queries against future versions 
* Allow versions for the future to be created with a time they are expected to become the current
* When a future version is promoted to current its temporal storage is not required (no history remains)
* Allow multiple future versions and support changing when a future version takes effect
* Support defining a new entity that does not yet exist but may exist in the future

== Software ==

This example is being built using EclipseLink 2.3.1. To access the source of the example check it out of SVN at:

/svnroot/rt/org.eclipse.persistence/trunk/examples/org.eclipse.persistence.example.jpa.futures

== How it Works ==

Coming Soon...

== Examples ==

The following examples are from test cases.

=== Schema Generation ===

At the start of each test suite the schema is replaced. The resulting CREATE TABLE operations are:

<source lang="sql">
CREATE TABLE TPERSON (OID NUMBER(10) NOT NULL, 
                      END_TS NUMBER(19) NULL, 
                      F_NAME VARCHAR2(255) NULL, 
                      L_NAME VARCHAR2(255) NULL, 
                      START_TS NUMBER(19) NULL, 
                      CID NUMBER(10) NULL, 
                      ADDRESS_OID NUMBER(10) NULL, 
                      PRIMARY KEY (OID))

CREATE TABLE TADDRESS (OID NUMBER(10) NOT NULL, 
                       CITY VARCHAR2(255) NULL, 
                       END_TS NUMBER(19) NULL, 
                       START_TS NUMBER(19) NULL, 
                       STATE VARCHAR2(255) NULL, 
                       STREET VARCHAR2(255) NULL, 
                       CID NUMBER(10) NULL, 
                       PRIMARY KEY (OID))

ALTER TABLE TPERSON ADD CONSTRAINT FK_TPERSON_ADDRESS_OID FOREIGN KEY (ADDRESS_OID) REFERENCES TADDRESS (OID)

ALTER TABLE TPERSON ADD CONSTRAINT FK_TPERSON_CID FOREIGN KEY (CID) REFERENCES TPERSON (OID)

ALTER TABLE TADDRESS ADD CONSTRAINT FK_TADDRESS_CID FOREIGN KEY (CID) REFERENCES TADDRESS (OID)

</source>

=== Create Current Person ===

<source lang="java">
Person p = new Person();
p.setFirstName("Doug");
p.setLastName("Clarke");

Address a = new Address();
a.setStreet("45 O'Connor Street");
a.setCity("Ottawa");
a.setState("Ontario");

p.setAddress(a);
em.persist(a);
em.persist(p);
</source>

Resulting SQL:
<source lang="sql">
[EL Fine]: INSERT INTO TADDRESS (OID, CITY, END_TS, START_TS, STATE, STREET, CID) VALUES (?, ?, ?, ?, ?, ?, ?)
	bind => [1, Ottawa, 9223372036854775807, 0, Ontario, 45 OConnor Street, null]
[EL Fine]: UPDATE TADDRESS SET STATE = ?, STREET = ?, CID = ?, CITY = ? WHERE (OID = ?)
	bind => [Ontario, 45 OConnor Street, 1, Ottawa, 1]
[EL Fine]: INSERT INTO TPERSON (OID, END_TS, F_NAME, L_NAME, START_TS, CID, ADDRESS_OID) VALUES (?, ?, ?, ?, ?, ?, ?)
	bind => [2, 9223372036854775807, Doug, Clarke, 0, null, null]
[EL Fine]: UPDATE TPERSON SET CID = ?, ADDRESS_OID = ?, F_NAME = ?, L_NAME = ? WHERE (OID = ?)
	bind => [2, 1, Doug, Clarke, 2]
</source>

''Note: The INSERT followed by the UPDATE statement are to populate the self-referencing FK values. Optimized solution being investigated''

=== Create Person Edition ===

This example creates an edition of a current Person (id={currentPersonId}) which is set to take effect at T2 and be deleted or replaced at T4. The current version of the person is updated accordingly to end at T2.

<source lang="java">
Person currentPerson = em.find(Person.class, currentPersonId);

Person p = TemporalHelper.createEdition(em, currentPerson);
p.setFirstName("Dougie");
p.setStart(T2);
currentPerson.setEnd(T2);
p.setEnd(T4);
em.persist(p);
</source>

SQL:
<source lang="sql">
...
</source>

=== Create Future Person ===

This creates a new Person that does not currently exist but will exist as of T3.

<source lang="java">
Person p = new Person();
p.setFirstName("John");
p.setLastName("Doe");
p.setStart(T3);
em.persist(p);
</source>

SQL:
<source lang="sql">
[EL Fine]: INSERT INTO TPERSON (OID, END_TS, F_NAME, L_NAME, START_TS, CID, ADDRESS_OID) VALUES (?, ?, ?, ?, ?, ?, ?)
	bind => [1, 9223372036854775807, John, Doe, 3, null, null]
[EL Fine]: UPDATE TPERSON SET CID = ?, START_TS = ?, F_NAME = ?, L_NAME = ? WHERE (OID = ?)
	bind => [1, 3, John, Doe, 1]
</source>

=== Query for Current Person ===

<source lang="java">
EntityManager em = createEntityManager();

Person person = em.createQuery("SELECT p From Person p WHERE p.id = " + currentPersonId, Person.class).getSingleResult();
Address address = person.getAddress();

Assert.assertNotNull(person);

System.out.println("FIND CURRENT: " + person);
</source>

OUTPUT:
<source lang="sql">
[EL Fine]: SELECT OID, END_TS, F_NAME, L_NAME, START_TS, CID, ADDR_ID FROM TPERSON WHERE ((OID = ?) AND (START_TS = ?))
	bind => [2, 0]
[EL Fine]: SELECT OID, CITY, END_TS, START_TS, STATE, STREET, CID FROM TADDRESS WHERE ((OID = ?) AND (START_TS = ?))
	bind => [1, 0]
FIND CURRENT: Person(2)::Doug @ 0-2
</source>

=== Query for Current Person Joining Address ===

<source lang="java">
EntityManager em = createEntityManager();

Person person = em.createQuery("SELECT p From Person p JOIN FETCH p.address WHERE p.id = " + currentPersonId, Person.class).getSingleResult();
Address address = person.getAddress();

System.out.println("FIND CURRENT: " + person);
</source>

OUTPUT:
<source lang="sql">
[EL Fine]: SELECT t1.OID, t1.END_TS, t1.F_NAME, t1.L_NAME, t1.START_TS, t1.CID, t1.ADDR_ID, t0.OID, t0.CITY, t0.END_TS, t0.START_TS, t0.STATE, t0.STREET, t0.CID 
                  FROM TADDRESS t0, TPERSON t1 
                  WHERE (((t1.OID = ?) AND (t1.START_TS = ?)) AND ((t0.OID = t1.ADDR_ID) AND (t0.START_TS = ?)))
	bind => [2, 0, 0]
FIND CURRENT: Person(2)::Doug @ 0-2
</source>

=== Find Current Person ===

<source lang="java">
Person person = em.find(Person.class, currentPersonId);
System.out.println("FIND CURRENT: " + person);
</source>

OUTPUT:
<source lang="sql">
[EL Fine]: SELECT OID, CITY, END_TS, START_TS, STATE, STREET, CID FROM TADDRESS WHERE ((OID = ?) AND (START_TS = ?))
	bind => [1, 0]
FIND CURRENT: model.Person(2)::Doug @ 0-2
</source>

=== Query all Current Persons ===
<source lang="java">
List<Person> results = em.createQuery("SELECT p From Person p", Person.class).getResultList();
</source>

LOG:
<source lang="sql">
[EL Fine]: SELECT OID, END_TS, F_NAME, L_NAME, START_TS, CID, ADDRESS_OID FROM TPERSON WHERE (START_TS = ?)
	bind => [0]
</source>

=== Find Person edition @ T3 ===

<source lang="java">
Map<String, Object> props = new HashMap<String, Object>();
props.put("EFF_TS", T3);
EntityManager em = emf.createEntityManager(props);

Person personEdition = em.createQuery("SELECT p From PersonEdition p WHERE p.cid = " + currentPersonId, Person.class).getSingleResult();

System.out.println("QUERY EFFECTIVE @ T3: " + personEdition);
</source>

Log Output:
<source lang="sql">
[EL Fine]: SELECT OID, END_TS, F_NAME, L_NAME, START_TS, CID, ADDRESS_OID FROM TPERSON WHERE ((CID = ?) AND ((? >= START_TS) AND (? < END_TS)))
	bind => [2, 3, 3]
QUERY EFFECTIVE @ T3:
Person(3)::Dougie @ 2-4
</source>

=== Query Future Edition Person @ T2 with Address Joined ===

<source lang="java">
EntityManager em = createEntityManager();
em.setProperty("EFF_TS", T2);

Person person = em.createQuery("SELECT p From PersonEdition p JOIN FETCH p.address WHERE p.cid = " + currentPersonId, Person.class).getSingleResult();
Address address = pEdition.getAddress();

System.out.println("QUERY EDITION @ T2: " + person);
System.out.println("\t> " + address);
</source>

OUTPUT:
<source lang="sql">
[EL Fine]: SELECT t1.OID, t1.END_TS, t1.F_NAME, t1.L_NAME, t1.START_TS, t1.CID, t1.ADDR_ID, t0.OID, t0.CITY, t0.END_TS, t0.START_TS, t0.STATE, t0.STREET, t0.CID 
                  FROM TADDRESS t0, TPERSON t1 
                  WHERE (((t1.CID = ?) AND ((? >= t1.START_TS) AND (? < t1.END_TS))) AND ((t0.CID = t1.ADDR_ID) AND ((? >= t0.START_TS) AND (? < t0.END_TS))))
	bind => [2, 2, 2, 2, 2]
QUERY EDITION @ T2: model.PersonEdition(3 @ 2::4)
	> model.AddressEdition(4 @ 2::9223372036854775807)
</source>