Logging and rollback of edits

In some kingdom-state there was a formidable king. And the king had a lot of boyars that they were preparing day-to-day reports: how many troops in the service, and if the treasury was great, if the wheat was born, this year, and how many cows give milk.

The king wanted it so that in the reports against each tsiferka the name of the boyar was reflected, who counted her. Yes, he ordered the carpenters to build a mecha- nism according to overseas drawings, so that if one of the boyars makes a mistake, he will be on the rack, and the tsiferki will return his lying backwards.

Turning carpenters in the hands of drawings that Oracle Flashback are called, but doubted. Surely the UNDO_RETENTION lever, set by default to 3 minutes, can be screwed up without a year’s consequences, nothing is lost and drowning extra data in gigabytes. We decided to assemble our mechanism.
')
Fairy tales are over, further harsh implementation of the mechanism.


The main table is wide, includes a record ID, a hundred information fields not related to each other, and service information: the date of creation or modification of the line, the identifier of the boyar who created or edited the line through the web interface. At no time, each boyar fills in a row with data known to him, or he edits fields already filled by other boyars.

create table MAIN_TABLE (
ID number primary key ,
INFO_FIELD1 number,
INFO_FIELD2 varchar2(100),
INFO_FIELD3 date ,
…
CREATE_DATE date default sysdate,
CREATE_USER_ID number NOT NULL ,
UPDATE_DATE date ,
UPDATE_USER_ID number
)
partition by range ( CREATE_DATE ) …
;



The edit history table almost completely repeats the structure of the main table, unless it is devoid of the fields CREATE_DATE, CREATE_USER_ID - these fields remain unchanged, it makes no sense to keep history on them. In addition, the historical table does not participate in state processes, it does not need keys. Partitioning is also moved from the date the row was created to the date of the change, this will allow you to move or delete sections with outdated edits.

create table HISTORY_TABLE (
ID number,
INFO_FIELD1 number,
INFO_FIELD2 varchar2(100),
INFO_FIELD3 date ,
…
UPDATE_DATE date ,
UPDATE_USER_ID number
)
partition by range ( UPDATE_DATE ) …
;



Now, when changing the main table, it is necessary to keep the old value of the edited field (s) in the history table, creating a new row. Thus, the current information is currently stored in the main table. In the historical table, for each ID row from the main table there are several rows with the same ID, which differ in UPDATE_DATE.

Corollary 1 : the number of rows in the historical table is equal to the number of revisions to the main one.
Corollary 2 : if the row is created in the main table, but has never been edited, its ID is not in the historical table.

To fill the history table, carpenters used the trigger in the most reliable way. To highlight the changed field among all others, they began to compare the old and the new value of each field. There are no changes - we write NULL. This has a positive effect on the volume: no need to duplicate data that has not changed since the previous revision.

But bad luck, bad boyars can erase already filled fields. Such a case must be processed separately, you cannot write NULL - this is the absence of changes. Instead of NULL, they began to substitute the logically impossible value into history. For example, not a single treasurer in the world will of his own free will indicate in his report to the king a negative treasury value, and the web interface will not allow it. If we read the value of -99 (01/01/1970 for fields of type date) as an operation to erase a field, harmony is restored.

create or replace trigger TRG_MAIN_BEFORE_UPD
before update on MAIN_TABLE
for each row
declare
HIST HISTORY_TABLE %ROWTYPE;
begin

HIST.ID := : OLD .ID;

if (: OLD .UPDATE_DATE is null ) then
-- -

HIST.UPDATE_DATE := : OLD .CREATE_DATE;
HIST.UPDATE_USER_ID := : OLD .CREATE_USER_ID;

HIST.INFO_FIELD1 := : OLD .INFO_FIELD1;
HIST.INFO_FIELD2 := : OLD .INFO_FIELD2;
HIST.INFO_FIELD3 := : OLD .INFO_FIELD3;

else
-- - ,
-- , NULL
-- NULL,

HIST.UPDATE_DATE := : OLD .UPDATE_DATE;
HIST.UPDATE_USER_ID := : OLD .UPDATE_USER_ID;

if nvl(: NEW .INFO_FIELD1, -99) <> nvl(: OLD .INFO_FIELD1, -99) then
HIST.INFO_FIELD1 := nvl(: OLD .INFO_FIELD1, -99);
end if ;

if nvl(: NEW .INFO_FIELD2, '-99' ) <> nvl(: OLD .INFO_FIELD2, '-99' ) then
HIST.INFO_FIELD2 := nvl(: OLD .INFO_FIELD2, '-99' );
end if ;

if nvl(: NEW .INFO_FIELD3, to_date( '01.01.1970' , 'dd.mm.yyyy' )) <>
nvl(: OLD .INFO_FIELD3, to_date( '01.01.1970' , 'dd.mm.yyyy' )) then
HIST.INFO_FIELD3 :=
nvl(: OLD .INFO_FIELD3, to_date( '01.01.1970' , 'dd.mm.yyyy' ));
end if ;

end if ;

--
insert into HISTORY_TABLE (ID, UPDATE_DATE, UPDATE_USER_ID,
INFO_FIELD1, INFO_FIELD2, INFO_FIELD3)
values (HIST.ID, HIST.UPDATE_DATE, HIST.UPDATE_USER_ID,
HIST.INFO_FIELD1, INFO_FIELD2, HIST.INFO_FIELD3);

end TRG_MAIN_BEFORE_UPD;

* This source code was highlighted with Source Code Highlighter .

Choosing from the history table, you can immediately see who made what contribution:

select *
from HISTORY_TABLE
where ID = 1
oder by UPDATE_DATE;


---------------------------------------------------------------------------
ID | INFO_FIELD1 | INFO_FIELD2 | INFO_FIELD3 | UPDATE_DATE | UPDATE_USER_ID
---------------------------------------------------------------------------
1 12 AAA 05.11.2010 1
1 -99 01.11.2010 06.11.2010 2
1 BBB 07.11.2010 3



User 1, when creating a record, filled the first two information fields, ignoring (leaving empty) the third. User 2 next day erased the value of the first field (apparently considered incorrect) and filled the third. A day later, user 3 fixed the second field, the first one remained unfilled. We do not forget that these are historical changes, which means that the 07/11/2010 edition is not the last. The main table stores the current version of this record, which may be completely different from previous versions.

Obviously, the search by ID and date of change is the most popular in the history table. It is necessary to create local indexes for these fields.

In order to satisfy the needs of the king, the ability to restore data for any period of time was added to the mechanism. For this, the holes (NULLs) in the history table are replaced with the value that is relevant for that period of time. And the impossible values ​​of -99 are replaced by NULLs. Carpenters made a presentation for convenience:

create or replace view V_HISTORY_RESTORE (
ID,
INFO_FIELD1,
INFO_FIELD2,
INFO_FIELD3,

UPDATE_DATE,
UPDATE_USER_ID
)
as
select
ID,

case
when LAST_VALUE(INFO_FIELD1 ignore NULLS) over (partition by ID order by UPDATE_DATE) = -99
then NULL
else
LAST_VALUE(INFO_FIELD1 ignore NULLS) over (partition by ID order by UPDATE_DATE)
end as INFO_FIELD1,

case
when LAST_VALUE(INFO_FIELD2 ignore NULLS) over (partition by ID order by UPDATE_DATE) = '-99'
then NULL
else
LAST_VALUE(INFO_FIELD2 ignore NULLS) over (partition by ID order by UPDATE_DATE)
end as INFO_FIELD2,

case
when LAST_VALUE(INFO_FIELD3 ignore NULLS) over (partition by ID order by UPDATE_DATE) = to_date( '01.01.1970' , 'dd.mm.yyyy' )
then NULL
else
LAST_VALUE(INFO_FIELD3 ignore NULLS) over (partition by ID order by UPDATE_DATE)
end as INFO_FIELD3,

UPDATE_DATE,
UPDATE_USER_ID

from HISTORY_TABLE;

* This source code was highlighted with Source Code Highlighter .

Quite cumbersome, but it works quickly. Here we use the analytical function LAST_VALUE with the construction ignore NULLS, which takes for each row the field value from the previous (in sorting by date) row if it is not NULL. If in the previous line it is NULL, we move higher until we stumble upon a non-empty value.

Selecting rows from the view to restore the history, you can see the state of the main table at one time or another:

select *
from V_HISTORY_RESTORE
where ID = 1
oder by UPDATE_DATE;


---------------------------------------------------------------------------
ID | INFO_FIELD1 | INFO_FIELD2 | INFO_FIELD3 | UPDATE_DATE | UPDATE_USER_ID
---------------------------------------------------------------------------
1 12 AAA 05.11.2010 1
1 AAA 01.11.2010 06.11.2010 2
1 BBB 01.11.2010 07.11.2010 3



Now, to restore the row, it is enough to select only the date of editing and make an update for the main table. Since the main table goes update, it turns out another row in the history table. So, in the future, you can return to the moment before the return to the past begins (if the phrase sounds contradictory, review the film “Back to the Future”).

To anathematize the contribution of guilty slaves, it is enough to remove their rows from the history table, and in the main table for the rows containing bad edits, restore the previous state of the row.

That's the end of a fairy tale. The king was pleased.
The mechanism works, it is flexibly adjusted, which fields, which tables to store. The story becomes more manageable. If necessary, you can delete unnecessary edits of lines certified by the king, freeing up space in the repository.
One thing is bad - the carpenters are tired of cutting it all. And there is a danger that the left-hander will come, add fields to the main table, and forget about the story. Against the curves of the hand there is no protection.

For a snack article versioning and data history

ps My bike. I do not pretend to be innovative, but I didn’t reveal analogs. The picture for the article is on the Internet.