Hi,

I'm looking at a good data versionning design pattern featuring this
single requirement : It must preserve the original ID for each record
in the Database. In other words : I want to be able to walk through
each modification/insertion in the database. It looks like recreating
the firebird MGA into the Database.

Here is the solution I choosed :

My database has only 4 tables, it is used to import data from an
external provider, add my own data supplements and export an XML file
to my customers. So I don't need this model to be able to handle
multi-users accesses in write mode.

The main table where I import provider data looks like this :

CREATE TABLE PROVIDER (
CODE CHAR(5) PRIMARY KEY,
NAME VARCHAR(50),
PRICE NUMERIC(10,4),
FAMILLY VARCHAR(50)
);

Mine looks like :

CREATE TABLE FAMILIES (
ID INTEGER PRIMARY KEY,
NAME VARCHAR(50)
);

CREATE ARTICLES (
CODE CHAR(5) PRIMARY KEY,
NAME VARCHAR(50),
FAMILY integer,
PRICE NUMERIC(10,4)
BARCODE VARCHAR(13)
);

So I choosed to add a VERSION field and a VERSIONS table :

CREATE TABLE VERSIONS (
ID integer primary key,
TS TIMESTAMP default 'now',
COMMENT VARCHAR(32000)
);


CREATE TABLE PROVIDER (
CODE CHAR(5),
VERSION INTEGER,
NAME VARCHAR(50),
PRICE NUMERIC(10,4),
FAMILLY VARCHAR(50),
constraint PROVIDER_PK primary key (CODE, VERSION)
);

CREATE TABLE FAMILIES (
ID INTEGER,
VERSION integer,
NAME VARCHAR(50),
constraint FAMILIES_PK primary key (ID, VERSION)
);

CREATE ARTICLES (
CODE CHAR(5),
VERSION integer,
NAME VARCHAR(50),
FAMILLY integer,
PRICE NUMERIC(10,4)
BARCODE VARCHAR(13),
constraint ARTICLES_PL primary key (ID, VERSION)
);

I have a bunch of stored procedures that create news version, manage
insertion of new versions :

create procedure PS_CREATE_NEW_VERSION(COMMENT varchar(32000))
returns ( VERSION integer )
as
begin
VERSION = gen_id(GEN_VERSION_ID,1);
insert into VERSIONS(ID, COMMENT)
values (:VERSION, :COMMENT);
suspend;
end;

create procedure PS_SYNC_FAMILLY(VERSION integer, ID integer, NAME
varchar(50))
as
declare variable L_ID integer;
declare variable L_NAME varchar(50);
begin
L_ID = null;

select first 1 ID, NAME from FAMILIES
where VERSION < :VERSION
order by VERSION desc
into :L_ID, :L_NAME;

if (
(L_ID is null)
or (
(L_ID is not null) and
(L_NAME <> NAME)
)
) then
begin
insert into FAMILIES(VERSION, ID, NAME)
values (:VERSION, :ID, :NAME);
end;
end;

Each time a record has to be inserted or updated into the TABLES, I
have to call the PS_CREATE_NEW_VERSION followed by one or more calls
to PS_SYNC_[TABLE_NAME].

To read the last version for an entier table I have such procedures :

CREATE OR ALTER PROCEDURE PS_GET_FAMILIES (FOR_VERSION Integer)
returns (ID Integer,
VERSION Integer,
NAME Varchar(50))
AS
begin
if (FOR_VERSION is null) then
select VERSION from PS_GET_HEAD_VERSION
into :FOR_VERSION;

for
select
f.ID,
f.VERSION,
f.NAME,
from FAMILIES f
where f.VERSION = (
select max(VERSION)
from FAMILIES
where ID=f.ID and VERSION <= :FOR_VERSION)
into
:ID,
:VERSION,
:NAME,
do
suspend;
end

What do you think of this model, do you have better ideas to handle
"Data versionning" inside the Database ?

Many thanks in advance, best regards.

--
Pierre Y.