«Par ma foi! il y a plus de quarante ans que je dis de la prose sans que j'en susse rien…»
Molière, Le Bourgeois Gentilhomme
“By my faith! For more than forty years I have been speaking prose without knowing anything about it…”
Molière, The Middle Class Gentleman
Because you've probably been using temporal databases all your life. I know I have. Every database where you record a history of past data or the scheduling of future events, is a temporal database. Every table where you add datetime columns that annotate the entire row, as in marking when an entity first appeared or when it was removed, is a temporal table.
Common RDBMSs do not offer any support for defining and manipulating temporal data. Although temporal data can be defined and manipulated using plain SQL, its amount and complexity is an order of magnitude more than for non-temporal data. What's more, supporting tools like ORM and report generators do not support temporal data either, which means that developers have to do everything on their own.
Temporal databases are mostly absent from a developer's education. And, because adding some datetime columns does not seem so big a deal, it is very easy to move into temporal databases without acknowledging the difficulties and the pitfalls. And get it all wrong. No integrity checking, no guidelines to implement updates correctly, lots of procedural code that manipulates data in memory, and lots of code to overcome inconsistent data that, invariably, appear in the database.
It is far better to do it correctly from the beginning. Pandamator (πανδαμάτωρ, ancient Greek for all-subduing, an adjective commonly attached to time), is a project aiming to enable handling of temporal databases using common RDBMSs lacking temporal support. It consists of:
- Predetermined decisions as to what kinds of temporal data can be accommodated and their representation in SQL
- Definitions of tables, views, functions and stored procedures that together provide a supporting infrastructure for defining and using temporal tables
- An SQL generator that generates triggers for maintaining temporal referential integrity and other procedural code
- Run-time support to create UPDATE and DELETE scripts in user programs
- Prepare the database
- Run
CREATE VALIDTIME
to define the metadata tables, supporting data and code
- Run
- Create your schema
- As usual, but adding the extra columns needed
- Declare temporal annotations on the schema
- For example
ALTER TABLE X ADD VALIDTIME(STATE); ALTER TABLE X ADD VALIDTIME CONSTRAINT PK PRIMARY KEY(IDCOL);
- For example
- Generate SQL for integrity constraints
- Run
ALTER VALIDTIME
- Run
- Create temporal-aware SQL in your code
- For example
VALIDTIME SELECT A.X FROM A INNER JOIN B ON A.BID=B.ID
- For example