Simplify temporal delete of non-existing periods

[PhilippSalvisberg]

Deleting a non-existing period in version 0.2 is a bit cumbersome. The delete should be simplified as shown in section "Proposed solution" below using an UPDATEstatement. For that the column IS_DELETED$ must be part of the history view (_hv). However, it does not need to be visible.

Furthermore an undelete operation could be achieved by setting the IS_DELETED$column to 0 (to ensure a change is detected). Technically the value must be converted to NULL by the API to honor the column constraint.

Start

SELECT * FROM dept_hv WHERE deptno = 40;

HIST_ID	VT_START	VT_END	DEPTNO	DNAME	LOC
7			40	OPERATIONS	BOSTON

Goal

HIST_ID	VT_START	VT_END	DEPTNO	DNAME	LOC
7		2018-01-01	40	OPERATIONS	BOSTON

Existing option a) - del call

DECLARE
   l_old dept_ot := NEW dept_ot();
BEGIN
   SELECT dept_ot(NULL, DATE '2018-01-01', vt_end, NULL, deptno, dname, loc)
     INTO l_old
     FROM dept_hv
    WHERE vt_end IS NULL
      AND deptno = 40;
   dept_api.del(l_old);
END;
/

Existing option b) - ins call

BEGIN
   dept_api.ins(dept_ot(NULL, DATE '2018-01-01', NULL, 1, 40, 'OPERATIONS', 'BOSTON'));
END;
/

Existing option c) - UPDATE and DELETE

UPDATE dept_hv 
   SET vt_start = DATE '2018-01-01', 
       dname = dname || '.' 
 WHERE deptno = 40 AND vt_end IS NULL;

DELETE dept_hv
 WHERE deptno = 40 AND vt_end IS NULL;

Proposed solution

UPDATE dept_hv 
   SET vt_start = DATE '2018-01-01', 
       is_deleted$ = 1 
 WHERE deptno = 40 AND vt_end IS NULL;