= BtPgsql Design --- BtPgsql is designed to be a layer of abstraction between the programmer and PostgreSQL (http://www.postgresql.org), allowing the programmer to deal with simple objects while the BtPgsql module handles the details of generating the sql responsible to affect bi-temporal manipulation of the database - in this was it can be said that BtPgsql _emulates_ a bi-temporal database. This relationship may be visualized as : _____________________________________________________________________ | | | User Application | | * _______________________________________________________| | * | | | *********** BtPgsql ********************************** | | | * * | | | * __________________________________*____________| | | * | * | | | * | PostgreSQL * | | | * | * | | | * | ______________________________*____________| | | * | | * | | | * | | User Database ____________*__ | | | * | | | * | | | | * | | | BtPgsql * | | | | * | | | System * | | | | * | | ____________ | Relations * | | | | * | | | | | * | | | | * | | | User | | * | | | | * | | | Relations | | * | | | | * | | | | --------------| | | | ************************| | | | | | | | | | | | | |__________ | | |____________|________|___|__________________________________________| As this diagram shows, BtPgsql requires the existence of certain system relations to exist in order to know which user relations are to be considered bi-temporal ones and to interpret the relationships between them. Recall that the normal RDBMS referential integrity constructs, primary key and foreign key, will not work to maintain bi-temporal relationships! If it is not clear at this point why this is so it is reccomended that the Problem Domain section of the README be reviewed. In a traditional RDBMS the primary key and foreign key syntax instructs the database to interpret relationships between relations in a certain way. Generally, sql syntax is a shortcut for making entries into system tables where information is stored such as : * which user relations exist * which fields of the relations are to be considered the primary keys * which fields of relations refer to the primary keys of other relations - foreign key definitions * actions to take on deletion or update of tuples where foreign keys are concerned - for example _cascade_ or _restrict_ BtPgsql requires similar tables to do it's work. These relations serve the same purpose as the traditional RDBMS system table, but are interpreted with bi-temporal semantics. BtPgsql defines the following bi-temporal system relations : --- *btrel*:: enumerate the bi-temporal relations of this database *btfield*:: enumerate the fields, and their positions, of each bi-temporal relation in this database *btpk*:: enumerate which of the declared fields, if any, of the named bi-temporal relations are the primary key field(s) *btfkactions*:: what action to take on delete or update to bi-temporal relations where foreign keys are concerned (_cascade_ and _restrict_ are supported) *btfks*:: enumerate the foreign keys of the bi-temporal relationships along with their update and delete actions --- A _psql_ script, which may be run against your own PostgreSQL database via : psql -d $your_database_name -f btschema.sql in order to set up the correct tables, is included in the sql subdirectory of the distribution in the file link:../sql/btschema.sql. It is actually a very minimal and easy to understand schema. Please take a moment to review link:../sql/btschema now. Assuming that the bi-temporal system relations have been set up (link:../sql/btschema.sql), all that remains to allow BtPgsql to do it's work is to ensure that each relation which should be a bi-temporal one meet the following criteria : 1. no primary key or foreign key modifiers in the create table statement 2. the following fields *must* be *ammended* to each create table statement vt_begin timestamp(0) without time zone not null:: the inclusive valid time beginning vt_end timestamp(0) without time zone not null:: the exclusive valid time ending tt_start timestamp(0) without time zone not null:: the inclusive transaction time start tt_stop timestamp(0) without time zone not null:: the exclusive transaction time stop btpk int not null :: the bi-temporal primary key btid serial unique not null :: the bi-temporal id So, for example, to bi-temporalize these _normal_ relations : create table parent ( pid int, desc text, primary key (pid) ); create table child ( cid int, pid int, desc text, primary key (cid), foreign key (pid) references parent (pid) ); one would first convert the create table statements to : create table parent ( pid int, desc text, -- fields required for btpgsql vt_begin timestamp(0) without time zone not null, vt_end timestamp(0) without time zone not null, tt_start timestamp(0) without time zone not null, tt_stop timestamp(0) without time zone not null, btpk int not null, btid serial unique not null ); create table child ( cid int, pid int, desc text, -- fields required for btpgsql vt_begin timestamp(0) without time zone not null, vt_end timestamp(0) without time zone not null, tt_start timestamp(0) without time zone not null, tt_stop timestamp(0) without time zone not null, btpk int not null, btid serial unique not null ); and then insert the following tuples to in order to define the relationships in such a way that BtPgsql can understand them : -- define bt tables insert into btrel values ('parent'); insert into btrel values ('child'); -- define bt tables' fields insert into btfield values ('parent','pid', 0); insert into btfield values ('parent','description', 1); insert into btfield values ('parent','vt_begin', 2); insert into btfield values ('parent','vt_end', 3); insert into btfield values ('parent','tt_start', 4); insert into btfield values ('parent','tt_stop', 5); insert into btfield values ('parent','btpk', 6); insert into btfield values ('parent','btid', 7); -- insert into btfield values ('child','cid', 0); insert into btfield values ('child','pid', 1); insert into btfield values ('child','description', 2); insert into btfield values ('child','vt_begin', 3); insert into btfield values ('child','vt_end', 4); insert into btfield values ('child','tt_start', 5); insert into btfield values ('child','tt_stop', 6); insert into btfield values ('child','btpk', 7); insert into btfield values ('child','btid', 8); --define bt tables' pks insert into btpk values ('parent','pid'); -- insert into btpk values ('child','cid'); insert into btpk values ('child','pid'); --define bt tables' fks insert into btfk values ('child','pid','parent','pid', 0,'cascade','restrict'); Admittedly this is not a quick and easy step, but it is a largely mechanical one and this work could be automated with a small amount of effort. Two of the above bi-temporal support fields deserve special mention : *btpk*:: this field is calculated by BtPgsql as a unique identifier for all tuples in a bi-temporal relation which share the same primary key fields regardless of the time they apply to, for example these tuples would share a btpk (42) : stations ---------------------------------------------------------------------------------------- sid | elevation | description | vt_begin | vt_end | tt_start | tt_stop | btpk --- | | | | | | | ---------------------------------------------------------------------------------------- | | | | | | KDEN | 1600 | DENVER INTL | epoch | 1980 | 1971 | infinity | 42 KDEN | 1656 | DENVER INTL | 1980 | infinity | 1971 | infinity | 42 | | | | | | *btid*:: this field is simply a unique identifier for each tuple in a bi-temporal relation, typically defined as a serial type, used internally by the BtPgsql API == Summary --- If certain relations in a database are required to be bi-temporal ones, three steps must by taken if they are to be managed by BtPgsql : 1) Bi-temporal system relations must be set up using link:../sql/btschema.sql 2) Additional fields, required by BtPgsql, must be ammended to each bi-temporal relation 2) The relationships between these bi-temporal relations must be described in the bi-temporal system relations Once these steps are completed, the BtPgsql API, specifically that of the BtPgsql::Relation class, may be used to manipulate the relations directly from Ruby programs without the requirement to generate the extremely complex sql statements required to correctly maintain bi-temporal relations. Refer to the TUTORIAL and the doccumentation of BtPgsql::Relation for details of the interface.