The Pyrrho Database Management System

Malcolm Crowe, University of the West of Scotland

Temporal Tables

Pyrrho supports a simple notion of "temporal table". This is defined more precisely below. A temporal table corresponds loosely to a history of some sort, such that the last component of the primary key is a date D (or otherwise discriminates earlier from later entries). There should be no redundant dates, that is, if the primary key is used to order the rows in the table, successive rows always differ by more than just the D column.

For such tables T there are derived columns T.NEXT and T.LAST, of the same type as D (T.NEXT may be null), and a predicate CURRENT(D), which is equivalent to D=T.LAST . There is also a TEMPORAL JOIN construct on temporal tables, defined in terms of two low-level operations called FOLD and INTERLEAVE. For details see below.

Note that there is no need to declare a table as temporal. If any of these operations are invoked on a non-temporal table, SQLSTATE 42128 "Illegal aggregation operation" will result.

A table in which the last component of the primary key is of scalar type with a natural ordering, such as a date or timestamp, is called a semitemporal base table. The last component TK of the primary key PK is said to be the default temporal column of T.

A semitemporal table is a table that is either a semitemporal base table or the result of FOLD or INTERLEAVE. FOLD removes "unnecessary" rows in T, which differ from existing rows only in CT, while INTERLEAVE adds such unnecessary rows.

A temporal table is a semitemporal table T without unnecessary rows, i.e. such that T = T FOLD(CT) . In a temporal table T, the derived columns NEXT and LAST are defined. As with ordinary columns, NEXT and LAST may be prefixed by correlation or table names to indicate their table.

The TEMPORAL JOIN operation is available for performing a sort of natural join on two temporal tables whose default temporal columns have matching types, which are combined in the temporal join operation to provide a default temporal column in the result. For the purposes of this rule, date-time types shall not match if their specified or implied <interval qualifier>s do not match. The temporal join has a natural primary key consisting of the union of the non-temporal columns of the primary keys of the two temporal tables, together with the new temporal column.

The temporal predicate CURRENT(TK) is equivalent to TK=LAST, and TK AT V is equivalent to (TK<=V and TK=LAST or NEXT>V) .

Format

<datetime type> ::= DATE [<interval qualifier>]
| TIME [<interval qualifier>] [ <left paren> <time precision> <right paren> ] [ <with or without time zone> ]
| TIMESTAMP [<interval qualifier>] [ <left paren> <timestamp precision> <right paren> ][ <with or without time zone> ]

Syntax Rules

Change rule 32) to If DATE is specified but an <interval qualifier> is not specified, the implicit interval qualifier shall be YEAR TO DAY. If TIME is specified but an <interval qualifier> is not specified, the implicit interval qualifier shall be HOUR TO SECOND. If TIMESTAMP is specified but an <interval qualifier is not specified, the implicit interval qualifier shall be YEAR TO SECOND.

General Rules

Change rule 4) to For a <datetime type> the <primary datetime field>s contained shall be specified by the specified or implicit <interval qualifier> .

Format

Syntax Rules

9) If NEXT or LAST is specified, then the <column referemce shall be contained in a <query specification> QS. whose <table expression> is a temporal table TR, BIC shall identify a temporal table T . If the <column reference> is contained in a <query specification> QS whose from clause contains only one table T, then BIC can be omitted, and the identification of T is implicit.

General Rules

a) Let the columns of TR be (CL₁,…,CL_n), reordering them if necessary so that the primary key of TR is (CL₁,..,CL_k). Then CR=CL_k .Let CL=(CL₁,..,CL_k-1).

MAX(CR) OVER (PARTITION BY CL ORDER BY CR ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)

NOTE: (a) The use of MAX here is a no-operation since there is at most one row in the window frame. It is required syntactically in this expression.

(b) NEXT and LAST are not derived columns in the sense used elsewhere in the standard, since they are computed in the table they relate to, ignoring any WHERE clasuses etc in the query specification in which they occur.

Format ...

<table primary> ::= …
| <table primary> FOLD
| <table primary> INTERLEAVE WITH <left paren> <query primary> <right paren>

Syntax Rules

26) If FOLD or INTERLEAVE is specified, let PTR be the <table primary> specified as the argument to the FOLD or INTERLEAVE operation, and let TR be the <table primary> specified by the FOLD or INTERLEAVE operation.

a) PTR shall be a semitemporal table. Let CT be its default temporal column. Let DT be the data type of CT.

b) If INTERLEAVE is specified, let QP be the <query primary>. The result TT of QP shall be a table with a single column TC of the type DT.

General Rules

6) If FOLD is specified, let the semitemporal table PTR have columns S₁,…,S_n such that the primary key CL is S₁,..,S_k, with S_k=CT . Let SL be the list SL₁,..,SL_n-1.

Then let PTR be ordered by CL. Let NCL=S₁,..,S_k-1, the non-temporal components of CL. Partition PTR by NCL. Each window TP in the partition has identical values of NCL, and is ordered by distinct values of CT. Let TP = {TP_ij : i=1,..,m; j=1,..,n } be such a window, and let N = { i : TP_ij = TP_{i-1 j}for all j such that k<j<=n }.

Then the result TR is the union of all such TS. Its columns will be the columns of PTR with CT renamed as CN. It is a temporal table with the primary key consisting of NCL together with CN.

7) If INTERLEAVE is specified, let the semitemporal table PTR have columns S₁,…,S_n such that S_n=CT . Let SL be the list SL₁,..,SL_n-1. Let TT be the result of QP and let TC be its single column. Then

TR = SELECT SL,CT FROM PTR
UNION DISTINCT
SELECT SL,TC AS CN FROM PTR TN, TT WHERE TN.CT AT TC

Format

Syntax Rules

In the opening sentence of rules 6 and 7 replace NATURAL by NATURAL or TEMPORAL .

13) If TEMPORAL is specified, TR₁ and TR₂ shall be temporal tables with primary keys whose non-temporal columns are NK₁ and NK₂, and default temporal columns CT₁ and CT₂ respectively. Let NK be NK₁ È NK₂. Let CN be the name of CT₁. Let TRL be the column list of TR₂ with the name of CT₂ replaced by CN.

((TR₁ INTERLEAVE WITH (SELECT CT₂ AS CN FROM TR₂))
NATURAL JOIN
(TR₂ TRL INTERLEAVE WITH (SELECT CT₁ FROM TR₁)))
FOLD

The result is a temporal table whose primary key is NK together with CN, and which can be referred to in an enclosing context by the <identifier> if specified.

Format

General Rules

Function

Format

Syntax Rules

1) The <column reference>s specified in the AT predicate, or in the <column reference list> of CURRENT, shall be default temporal columns in their respective tables.

General Rules

1) CURRENT(CL₁,..,CL_n) shall be equivalent to CL₁=LAST(CL₁) AND … CL_n=LAST(CL_n).

Function

Syntax Rules

Change 2) to If TO is specified, then <start field> shall be more significant than <end field>.