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