Configuring a History Policy

If you are using a non-Oracle database platform (see "Configuring a Relational Database Platform at the Session Level") and you want to use historical client sessions (see "Historical Client Sessions") to execute historical queries (see "Historical Queries") against a historical schema of your own design, configure your descriptors with a TopLink HistoryPolicy that describes your historical schema.

Note: If you are using an Oracle database platform for Oracle9i Database Server (or later), you can query the historical versions of objects automatically maintained by the Oracle database without the need for a history policy. For more information, see "Configuring Historical Client Sessions Using an Oracle Platform".

Table 28-34 summarizes which descriptors support history policy configuration.

Table 28-34 Descriptor Support for History Policy Configuration

Descriptor	Using TopLink Workbench	Using Java
Relational Descriptors
Object-Relational Descriptors
EIS Descriptors
XML Descriptors

There are many ways to configure a historical database schema. TopLink supports several historical schema configurations that you can describe with a HistoryPolicy (see "Historical Client Session Limitations").

Example Historical Schema

As shown in Table 28-35 and Table 28-36, a common approach is to define a special history table to store past versions of an object: one history table for each regular table that requires historical persistence. The history table typically has the same fields as the corresponding regular table plus fields (such as row start and end) used to define an interval that represents the life time of a particular version.

Note: TopLink assumes that the current version of an object corresponds to the historical table row whose row end field is NULL.

TopLink will include the history tables described by a HistoryPolicy when you execute a historical query.

Table 28-35 shows the schema for an EMPLOYEE table. The table currently contains one EMPLOYEE instance.

Table 28-35 Example Table for EMPLOYEE

EMP_ID	F_NAME	L_NAME	SALARY
1	Jane	Doe	55000

Table 28-36 shows one possible history table EMPLOYEE_HIST that stores historical versions of employees. The table contains the current EMPLOYEE (the version with a ROW_END value of NULL) and one historical version.

Table 28-36 Example History Table EMPLOYEE_HIST

EMP_ID	F_NAME	L_NAME	SALARY	ROW_START	ROW_END
1	Jane	Doe	50000	29/08/2004	31/08/2004
1	Jane	Doe	55000	31/08/2004	NULL

Because every record has a start and end interval, the history table can store multiple versions of the same object (with the same primary key). The unique identifier of a particular version is given by the existing primary key, plus the value of the start field. For example, in Table 28-36, the unique identifier of the current version is given by (EMP_ID, START) = (1, 31/08/2004).

Using Java

Example 28-23 shows how to describe the schema shown in Table 28-35 and Table 28-36 using the TopLink HistoryPolicy:

Example 28-23 HistoryPolicy for One Table

HistoryPolicy policy = new HistoryPolicy();
policy.addStartFieldName("ROW_START");
policy.addEndFieldName("ROW_END");
policy.addHistoryTableName("EMPLOYEE", "EMPLOYEE_HIST");
// Assuming database triggers or stored procedures update history tables
policy.setShouldHandleWrites(false);

employeeDescriptor.setHistoryPolicy(policy);

You can specify more than one table with a HistoryPolicy as shown in Example 28-24. In this example, all history tables have a start field named ROW_START but the EMPLOYEE_HIST and SALARY_HIST tables have different end fields. To avoid ambiguity, the end field names are prefixed with their respective history table names.

Example 28-24 HistoryPolicy for Multiple Tables

HistoryPolicy policy = new HistoryPolicy();
policy.addStartFieldName("ROW_START");
policy.addEndFieldName("EMPLOYEE_HIST.ROW_END");
policy.addEndFieldName("SALARY_HIST.VALID_UNTIL");
policy.addHistoryTableName("EMPLOYEE", "EMPLOYEE_HIST");
policy.addHistoryTableName("SALARY", "SALARY_HIST");
// Assuming database triggers or stored procedures update history tables
policy.setShouldHandleWrites(false);

employeeDescriptor.setHistoryPolicy(policy);

Configuring Write Responsibility

Use HistoryPolicy method setShouldHandleWrites to specify whether or not TopLink is responsible for writing data to history tables. By default, setShouldHandleWrites is set to true.

Either the database or TopLink can be responsible for writing data to the history tables.

Typically, the database is responsible for writing data to history tables by way of triggers or stored procedures that customize create, insert, and delete operations to modify both the regular table and the history table appropriately.

Alternatively, you can make TopLink responsible by customizing insert, update, and delete queries using the DescriptorQueryManager ("Configuring Default Query Implementations").