Description john.vandale 2010-07-08 10:38:11 EDT

Build Identifier: 

Our history support does not work with bi-directional 1-1s.  When we perform
the update after the shallow insert we do not include the correct values in
the row.  This issue is that it is an insert query, but actually did an
update in the main table and the history policy was expecting an insert row.
Also the logical delete is not done and needs to be otherwise there will be
two active rows.

>>> from forums
Hi All,

I have a HistoryPolicy set up in my "User" descriptor as follows:
HistoryPolicy policy = new HistoryPolicy();
policy.addHistoryTableName("USER", "USER_HIST");
policy.addStartFieldName("ROW_EFFECTIVE_AT");
policy.addEndFieldName("ROW_EXPIRES_AT");
setHistoryPolicy(policy);

However, my USER table contains NOT NULL INITIALLY DEFERRED columns for
foreign key (FK) relationships to other objects. Upon inserting a new User
object, I'm seeing a corresponding insert into the history table with the
relationship FKs set to null (as expected). Then when TopLink circles around
and updates the User object with the FKs from the newly inserted, related
objects, I'm seeing another insert into the history table. Only this time,
only the FKs are being inserted, which causes contraint violations on other
columns within the history table. FWIW, This is all within a single
transaction.

What am I missing? What do I need to do to get the History Policy to do an
update instead of the secondary insert? Or... other suggestions are welcomed.

USER table is:
create table USER
(
OID NUMBER(10,0) NOT NULL,
CREATED_AT TIMESTAMP NOT NULL,
CREATED_BY VARCHAR2(80) NOT NULL,
UPDATED_AT TIMESTAMP NOT NULL,
UPDATED_BY VARCHAR2(80) NOT NULL,
IS_DELETED CHAR(1) NOT NULL,
AUDIT_HISTORY_OID NUMBER(10,0) NULL,
USER_ID VARCHAR2(50) NOT NULL,
PASSWORD VARCHAR2(50) NULL,
EMPLOYEE_ID VARCHAR2(50) NULL,
FIRST_NAME VARCHAR2(50) NULL,
MIDDLE_NAME VARCHAR2(50) NULL,
LAST_NAME VARCHAR2(50) NULL,
IS_ACTIVE CHAR(1) NULL,
SERVICE_DATE DATE NULL,
BIRTH_DATE DATE NULL,
SSN_LAST_FOUR VARCHAR2(4) NULL,
TYPE_INDICATOR VARCHAR2(80) NOT NULL INITIALLY DEFERRED,
ORGANIZATION_UNIT_OID NUMBER(10,0) NULL,
USER_PREFERENCES_OID NUMBER(10,0) NULL,
constraint USER_PK PRIMARY KEY (OID)
);

HISTORY table is:
create table USER_HIST
(
OID NUMBER(10,0) NOT NULL INITIALLY DEFERRED,
CREATED_AT TIMESTAMP NOT NULL,
CREATED_BY VARCHAR2(80) NOT NULL,
UPDATED_AT TIMESTAMP NOT NULL,
UPDATED_BY VARCHAR2(80) NOT NULL,
IS_DELETED CHAR(1) NOT NULL,
AUDIT_HISTORY_OID NUMBER(10,0) NULL,
USER_ID VARCHAR2(50) NOT NULL,
PASSWORD VARCHAR2(50) NULL,
EMPLOYEE_ID VARCHAR2(50) NULL,
FIRST_NAME VARCHAR2(50) NULL,
MIDDLE_NAME VARCHAR2(50) NULL,
LAST_NAME VARCHAR2(50) NULL,
IS_ACTIVE CHAR(1) NULL,
SERVICE_DATE DATE NULL,
BIRTH_DATE DATE NULL,
SSN_LAST_FOUR VARCHAR2(4) NULL,
TYPE_INDICATOR VARCHAR2(80) NOT NULL INITIALLY DEFERRED,
ORGANIZATION_UNIT_OID NUMBER(10,0) NULL,
USER_PREFERENCES_OID NUMBER(10,0) NULL,
ROW_EFFECTIVE_AT TIMESTAMP NOT NULL,
ROW_EXPIRES_AT TIMESTAMP NULL,
constraint USER_HIST_PK PRIMARY KEY (OID, ROW_EFFECTIVE_AT)
);

Output is as follows:
INSERT INTO USER (OID, BIRTH_DATE, SSN_LAST_FOUR, PASSWORD, IS_DELETED,
CREATED_BY, IS_ACTIVE, CREATED_AT, SERVICE_DATE, UPDATED_BY, USER_ID,
EMPLOYEE_ID, UPDATED_AT, ORGANIZATION_UNIT_OID, USER_PREFERENCES_OID,
AUDIT_HISTORY_OID, MIDDLE_NAME, FIRST_NAME, LAST_NAME, TYPE_INDICATOR) VALUES
(51, {ts '1985-08-10 16:44:20.488'}, '2345', 'unencryptedPassword', 'N',
'pst', 'Y', {ts '2007-08-10 16:44:30.715'}, {ts '2005-08-10 16:44:20.489'},
'pst', 'empM', 'empM', {ts '2007-08-10 16:44:30.715'}, NULL, NULL, NULL,
'fakeMiddle', 'fakeFirst', 'fakeLast', 'Employee')

INSERT INTO USER_HIST (OID, BIRTH_DATE, SSN_LAST_FOUR, PASSWORD, IS_DELETED,
CREATED_BY, IS_ACTIVE, CREATED_AT, SERVICE_DATE, UPDATED_BY, USER_ID,
EMPLOYEE_ID, UPDATED_AT, ORGANIZATION_UNIT_OID, USER_PREFERENCES_OID,
AUDIT_HISTORY_OID, MIDDLE_NAME, FIRST_NAME, LAST_NAME, TYPE_INDICATOR,
ROW_EFFECTIVE_AT) VALUES (51, {ts '1985-08-10 16:44:20.488'}, '2345',
'unencryptedPassword', 'N', 'pst', 'Y', {ts '2007-08-10 16:44:30.715'}, {ts
'2005-08-10 16:44:20.489'}, 'pst', 'empM', 'empM', {ts '2007-08-10
16:44:30.715'}, NULL, NULL, NULL, 'fakeMiddle', 'fakeFirst', 'fakeLast',
'Employee', {ts '2007-08-10 16:44:30.719'})

INSERT INTO AUDIT_HISTORY (OID, OWNING_AUDITABLE_OBJECT_OID, TYPE_INDICATOR)
VALUES (96, 51, 'Employee')

UPDATE USER SET AUDIT_HISTORY_OID = 96, LAST_NAME = 'fakeLast', MIDDLE_NAME =
'fakeMiddle', FIRST_NAME = 'fakeFirst', CREATED_AT = {ts '2007-08-10
16:44:30.715'}, CREATED_BY = 'pst', SERVICE_DATE = {ts '2005-08-10
16:44:20.489'}, EMPLOYEE_ID = 'empM', PASSWORD = 'unencryptedPassword',
USER_ID = 'empM', UPDATED_AT = {ts '2007-08-10 16:44:30.715'}, IS_ACTIVE =
'Y', USER_PREFERENCES_OID = 51, BIRTH_DATE = {ts '1985-08-10 16:44:20.488'},
SSN_LAST_FOUR = '2345', UPDATED_BY = 'pst' WHERE (OID = 51)

INSERT INTO USER_HIST (AUDIT_HISTORY_OID, LAST_NAME, MIDDLE_NAME, FIRST_NAME,
CREATED_AT, CREATED_BY, SERVICE_DATE, EMPLOYEE_ID, PASSWORD, USER_ID,
UPDATED_AT, IS_ACTIVE, USER_PREFERENCES_OID, BIRTH_DATE, SSN_LAST_FOUR,
UPDATED_BY, ROW_EFFECTIVE_AT) VALUES (96, 'fakeLast', 'fakeMiddle',
'fakeFirst', {ts '2007-08-10 16:44:30.715'}, 'pst', {ts '2005-08-10
16:44:20.489'}, 'empM', 'unencryptedPassword', 'empM', {ts '2007-08-10
16:44:30.715'}, 'Y', 51, {ts '1985-08-10 16:44:20.488'}, '2345', 'pst', {ts
'2007-08-10 16:44:30.752'})

Internal Exception: java.sql.SQLException: ORA-01400: cannot insert NULL into
("MBA_DEV"."USER_HIST"."OID")
[java] Error Code: 1400

Reproducible: Always

Steps to Reproduce:
1. Setup a project with entities that have a bi-directional 1 - 1 relationship and a history policy established.

2. In a UOW instantiate, register and commit new objects initialized with this relationship.  For example BABY has a FK that is the BABY_MONITOR PK. BABY_MONITOR has a FK that is the BABY PK.  Both have a history policy defined:

        UnitOfWork uow = dbSession.acquireUnitOfWork();
        Baby baby = new Baby();
        baby.setId(new BigDecimal(1056));
        baby.setName("Foo");
        BabyMonitor monitor = new BabyMonitor();
        monitor.setId(new BigDecimal(1156));
        baby.setBabyMonitor(monitor);
        monitor.setBaby(baby);
        uow.registerObject(baby);
        uow.commit();

3.  If the UoW is acquired from a non-historical session this executes successfully.  If its acquired from a historical session it results in the java.sql.SQLException: ORA-01400: cannot insert NULL ... from an attempt to INSERT a null PK into BABY_MONITOR_HIST.