Re: Historical Data Question

On Thu, 14 Jun 2007, Lza wrote:

> Does anyone have any suggestions on how to store historical
> information in databases?

-- I have tables:

create table history_columns (
column_id smallint primary key,
column_name varchar(63) not null,
table_name varchar(63) not null,
unique (column_name, table_name)
);

create table history (
column_id smallint not null references history_columns,
id int not null,
time_of_change timestamp with time zone not null,
primary key (column_id,id,time_of_change),
user_id smallint not null references users,
value varchar(10000)
);

--------------------------------------------------------

-- Utility function:

create or replace function column_id(column_name varchar(63), table_name varchar(63))
returns smallint
language sql stable strict
as $column_id$
select column_id from history_columns where column_name=$1 and table_name=$2;
$column_id$;

--------------------------------------------------------

-- Every data table is like this:

create table table1 (
table1_id int primary_key,
column1 varchar,
-- ... repeat for every column
id_zmieniajacego_table1 smallint not null references users,
time_of_change_table1 timestamp with time zone not null
)

--------------------------------------------------------

-- An on every table there's a trigger:

create or replace function process_history_table1() returns trigger as
$$
declare
changed boolean;
begin
if (tg_op = 'DELETE') then
insert into history values (
column_id('table1_id','table1'), OLD.table1_id,
current_timestamp,
session_user_id(),
OLD.table1_id );
if (char_length(OLD.column1)>0) then insert into history values (
column_id('column1','table1'), OLD.id_table1,
OLD.time_of_change_table1,OLD.id_zmieniajacego_table1,OLD.column1::text
-- ... repeat for every column
return OLD;
elsif (tg_op = 'UPDATE') then
changed = false;
if (OLD.column1<>NEW.column1) then insert into history values (
column_id('column1','table1'), OLD.id_table1,
OLD.time_of_change_table1,OLD.id_zmieniajacego_table1,OLD.column1::text
-- ... repeat for every column
if (changed) then
NEW.id_zmieniajacego_table1=session_user_id();
NEW.time_of_change_table1=current_timestamp;
return NEW;
else
return null;
end if;
end if;
end;
$$ language plpgsql volatile;

create trigger process_history_table1
before update or delete on table1
for each row execute procedure process_history_table1();

--------------------------------------------------------

When I need to show a table values for $some_id at $some_date in
the past I'll just get actual values and process history table back
in time
select column_name, value from history
where
table_name='table1'
and id=$some_id
and time_of_change>=$some_date
order by time_of_change desc
changing values in relevant columns.

I can show a list of who, when made a change and what has changed
using history table.

I can easily delete/archive history table records older than some
date when I don't need it anymore.

It can be made secure making process_history_* tables "security
definer" and allowing changes to history table only to its owner.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh