>

TSQL2 and SQL3 Interactions

Various members of the temporal database research community have worked to transfer some of the constructs and insights of TSQL2 into SQL3. The first step was to propose a new part to SQL3, termed SQL/Temporal. This new part was accepted at the Ottawa meeting in January, 1995 as Part 7 of the SQL3 specification. A modification of TSQL2's PERIOD data type is included in that part.

Discussions then commenced on adding valid-time and transaction-time support to SQL/Temporal. Two change proposals, ANSI-96-501 and ANSI-96-502, were unanimously accepted by ANSI and forwarded to ISO. A discussion of these proposals may be found in "Transitioning Temporal Support in TSQL2 to SQL3," by R. T. Snodgrass, M. H. Bohlen, C. S. Jensen, and A. Steiner, in Temporal Databases: Research and Practice, O. Etzion, S. Jajodia, and S. Sripada (eds.), Springer, pp. 150-194, 1998. Due to disagreements within the ISO committee as to where temporal support in SQL should go, the project responsible for temporal support was canceled near the end of 2001. Hence, the working draft, "Part 7, SQL/Temporal" is in limbo.

A prototype implementation of the proposed constructs has been developed by Andreas Steiner and Michael Boehlen: TimeDB. Enabling the commercial realization of temporal databases has been supported by NSF. Concepts and constructs from SQL/Temporal have been implemented in Oracle (see below) and other products have also included temporal support (see the list below). These ideas have also made their way into design patterns for things that change with time.

Implementation in Oracle

Oracle 9i included support for transaction time. Its flashback queries [an overview, [white paper] allow the application to access prior transaction-time states of their database; they are transaction timeslice queries. Database modifications and conventional queries are temporally upward compatible.

Oracle 10g extended flashBack queries [slides, technical paper] to retrieve all the versions of a row between two transaction times (a key-transaction-time-range query) and allowed tables and databases to be rolled back to a previous transaction time, discarding all changes after that time.

The Oracle 10g Workspace Manager [white paper] includes the period data type, valid-time support, transaction-time support, support for bitemporal tables, and support for sequenced primary keys, sequenced uniqueness, sequenced referential integrity, and sequenced selection and projection, in a manner quite similar to that proposed in SQL/Temporal.

These facilities permit tracing of actions on data as well as the ability to perform database forensics, as elaborated in the book "Oracle Forensics: Oracle Security Best Practices", by Paul M. Wright.

Other Products Providing Temporal Support

LogExplorer from Lumigent provides an analysis tool for Microsoft SQLServer logs, to allow one to view how rows change over time (a nonsequenced transaction-time query) and then to selectively back out and replay changes, on both relational data and the schema (it effectively treats the schema as a transaction-versioned schema).

aTempo's Time Navigator is a data replication tool for DB2, Oracle, Microsoft SQL Server and Sybase that extracts information from a database to build a slice repository, thereby enabling image-based restoration of a past slice; these are transaction time-slice queries.

IBM's DataPropagator can use data replication of a DB2 log to create both before and after images of every row modification to create a transaction-time database that can be later queried.

MarkLogic's MarkLogic Server product stores XML documents as a transaction-time database and supports transaction timeslice queries in XQuery (termed "point-in-time queries"), almost identically to that proposed in SQL/Temporal and applied to XQuery in τXQuery.

ANSI and ISO Documents Related to SQL/Temporal

ANSI and ISO documents have the following prefixes.

The general procedure is that change proposals are first directed towards a national committee (i.e., ANSI X3H2), which then decides whether to forward the proposal to the international committee (ISO/IEC JTC 1/SC 21/WG 3 DBL).

The .pdf files are Adobe PDF; they can be read via the 3.0 reader, accessible at http://www.adobe.com/prodindex/acrobat/readstep.html. The .ps files are postscript files.

ANSI documents

ISO documents

More information on this effort may be requested from Richard Snodgrass, rts@cs.arizona.edu.