Simple method to add historic feature to SQLite

Abstract: Make a database which can be store historic modification is often considered a “secondary” activity, but bad design leads to databases that are difficult to optimize and often not very understandable. In this article we illustrate a simple method that respects the dictates of relational theory & is easy to understand. As a plus we will show it on SQLite, a small but powerful database system.

Authors: Ernesto Ghetti (idea), Giovanni Giorgi (Exhibition and demo trigger)

Let’s suppose we have a table called “CUSTOMER” and we want to log any changes made to its fields, for example on a daily basis.

It is usually tempting to add a couple of START & END DATE columns, only to realize that querying this table to find the current data becomes a nightmare, especially if there are other related tables.  Another approach is to use a CUSTOMER_HISTORY table, but duplicating all the tables and leading to a cumbersome system of updating a group of related tables.

Lets start with a E-R model like this:

 

We are going to create a CUSTOMER table with a logical primary key NDG and a physical primary key XD. Then we define a relationship with the same table using XDS (iDentifer hiStory) as a foreign key. Let’s add some data (like customer name) and a couple of dates to register when the record is inserted / updated, as well as the author (editor) of the change in question:

create table CUSTOMER(
    xd number not null, 
    NDG number not null,
    xds number,
    d_start timestamp,
    d_end timestamp,
    editor varchar2(40),
    name varchar2(80),
    Primary key (xd),
 CONSTRAINT FK_HISTORY_CUSTOMER
    FOREIGN KEY (XDS)
    REFERENCES CUSTOMER (XD)
);

-- Forbids different CUSTOMERS with same NDG:
create unique index CUSTOMER_UQ_NDG ON CUSTOMER( case when xd=xds then NDG END );

-- View on active data
create view VCUSTOMER as 
 select * from CUSTOMER where xd=xds;

Then let’s define a couple of rules:

  1. The current record is the one with XD = XDS.
    It could have END_DATE = null
  2. A historicized record always has XD> XDS and must have defined the END_DATE
  3. Also, we want to avoid different customers have the same logical primary key, called NDG (line 16 above).

As you see is possible to define logical constraints (3) without much hassle.

Creating a new record is easy:

-- Insert record example (unique id are fixed for simplicity)
INSERT INTO CUSTOMER(XD,XDS,d_start,d_end,ndg,name,editor) values(1 ,1,date('now'),null,100,'Zeno Parisi','BOT');

select * from customer order by XD desc;
+----+-----+-----+------------+-------+--------+-------------+
| xd | NDG | xds |  d_start   | d_end | editor |    name     |
+----+-----+-----+------------+-------+--------+-------------+
| 1  | 100 | 1   | 2021-03-31 |       | BOT    | Zeno Parisi |
+----+-----+-----+------------+-------+--------+-------------+

Now the tricky part: how we update a record?

We use a “shrimp” strategy. First of all we create a copy of the current record, and we let point it to the new one with XD pointer.

-- Copy old record XD > XDS
-- On SQLITE you got a rowid special unique id column for every row: we use it here to make
-- the copy
INSERT INTO CUSTOMER(XD,XDS,d_start,d_end,editor,name,ndg)
select rowid+1 /*trick to get a new one */, XDS,d_start,date('now'),editor,name,ndg
from CUSTOMER where xd=xds and NDG=100;

-- Update XD=XDS
update CUSTOMER set name='Zeno Paris', editor='GG', d_start=date('now') where xd=xds and ndg=100;

Then we update the current record with the new value we want to insert (line 7 above).
The result is below:

-- After update
select * from customer order by XD desc;
+----+-----+-----+------------+------------+--------+-------------+
| xd | NDG | xds |  d_start   |   d_end    | editor |    name     |
+----+-----+-----+------------+------------+--------+-------------+
| 2  | 100 | 1   | 2021-03-31 | 2021-03-31 | BOT    | Zeno Parisi |
| 1  | 100 | 1   | 2021-03-31 |            | GG     | Zeno Paris  |
+----+-----+-----+------------+------------+--------+-------------+

External relation support

This strategy supports external relations too, as you can see in the owned_service table model. 

 

Trigger support

-- New record (see below)
INSERT INTO customer(XD,XDS,d_start,d_end,ndg,name,editor) 
select max(rowid)+1,max(rowid)+1, date('now'),null,200,'Scott Tiger','BOT'
from customer;


/* Trigger support for trasparent updates on vcustomer
 * 
 */
create trigger auto_history_customer_update
instead of update on vcustomer
FOR EACH ROW
begin
 INSERT INTO CUSTOMER(XD,XDS,d_start,d_end,editor,name,ndg)
    select max(rowid)+1 /*trick to get a new one */, XDS,d_start,date('now'),editor,name,ndg
    from CUSTOMER where xd=xds and NDG =OLD.ndg;
 update CUSTOMER set name=NEW.name, editor=NEW.editor, d_start=date('now') where xd=xds and xd=OLD.XD;
end;

/* This trigger only manage rowids */

create trigger auto_history_customer_insert
instead of insert on vcustomer
FOR EACH ROW
begin
 INSERT INTO CUSTOMER(XD,XDS,          d_start,     d_end,    editor,     name,     ndg)
  select    max(rowid)+1,max(rowid)+1, NEW.d_start, NEW.d_end,NEW.editor, NEW.name, NEW.NDG
  from customer;
end;



-- Trigger support example

update vcustomer  set editor='Changed via trigger' where NDG=200;
-- select * from customer order by xd desc;
select * from  vcustomer;
+----+-----+-----+------------+-------+---------------------+-------------+
| xd | NDG | xds |  d_start   | d_end |       editor        |    name     |
+----+-----+-----+------------+-------+---------------------+-------------+
| 1  | 100 | 1   | 2021-03-31 |       | GG                  | Zeno Paris  |
| 3  | 200 | 3   | 2021-03-31 |       | Changed via trigger | Scott Tiger |
+----+-----+-----+------------+-------+---------------------+-------------+
.echo off
Real table data:
+----+-----+-----+------------+------------+---------------------+-------------+
| xd | NDG | xds |  d_start   |   d_end    |       editor        |    name     |
+----+-----+-----+------------+------------+---------------------+-------------+
| 1  | 100 | 1   | 2021-03-31 |            | GG                  | Zeno Paris  |
| 2  | 100 | 1   | 2021-03-31 | 2021-03-31 | BOT                 | Zeno Parisi |
| 3  | 200 | 3   | 2021-03-31 |            | Changed via trigger | Scott Tiger |
| 4  | 200 | 3   | 2021-03-31 | 2021-03-31 | BOT                 | Scott Tiger |
+----+-----+-----+------------+------------+---------------------+-------------+

 

This entry was posted in English Content, IT Featured, Knowledgebase, Structured Query Language (SQL) and tagged , . Bookmark the permalink.