Re: Historical Data Question

[Tomasz Ostrowski <tometzky@xxxxxxxxxxxxx>]

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