Developing Time-Oriented Applications in SQL | Richard T. Snodgrass

# TRANSACTION-TIME STATE TABLES

## Microsoft SQL Server Implementation Examples

#

## Contents at a Glance

Transaction-Time State Tables
Defining a Transaction-Time State Table
Maintaining the Transaction-Time Table

> Indirectly Via Triggers
> Directly Via Rewritten Modifications

Queries
Partitioning

> Current and Archival Stores
> Queries
> Utilizing the Primary Key

Vacuuming

##

##

## About the work

All of the code fragments in chapters 9 are easily converted to Transact-SQL, the SQL language for Microsoft SQL Server. Most of them can be directly converted whereas just a few of them require minimal alterations. In particular, triggers are different from the standard and the OLD.x values and the NEW.x values are not provided. However, temporary tables, termed deleted table and inserted table, exist during the life span of the trigger. The deleted and inserted data are stored in these tables before the transaction is committed. As a result, OLD.x values and NEW.x values can be obtained from the deleted and inserted tables respectively.

Small data sets are provided for testing purposes. The data is inserted into the tables on the assumption that all of the tables have been created and are empty. Triggers are also supposed to exist before the inserting transaction is executed. To emulate past modifications to the data, extra code is added to the inserting transaction just to override the undesired modifications made by the active triggers.

##

##

## Defining a Transaction-Time State Table

The schema of a transaction-time state table comprises the column of the monitored table, along with two timestamp columns denoting the period of presence. Its key is simply the primary key of the monitored table and the start timestamp column.

Code Fragment 9.1 Creating the transaction-time table.

##

##

## Maintaining the Transaction-Time Table

Two ways of maintaining a transaction-time state table are presented in this section-- Using triggers on the monitored table or mapping modifications on the monitored table into modifications on the state table.

> ### Indirectly Via Triggers

> Triggers on the monitored table can be used to maintain a transaction-time state table.

> Code Fragment 9.2 Triggers for maintaining the P_TT table.

> Query files are considered batch files by Microsof SQL Server. A Trigger is accepted in a batch query file only if it is the first SQL statement on the file. Hence only one trigger is allowed per query file. Code fragment 9.2 must be split into three in order to accomodate at most one trigger per query file.

> ### Directly Via Rewritten Modifications

> A transaction-time state table may also be maintained directly by replacing modifications of the monitored table with modifications of the state table. The monitored table may then be expressed as a view on the state table.

> Maintaining the state table via direct modifications obviates the need for a materialized monitored table, decreasing the space overhead for capturing changes over time.

> Code Fragment 9.3 Reconstruct the PROJECTIONS table as of now, as a view.

Before creating the view PROJECTIONS, the previously existing table PROJECTIONS must be dropped. Otherwise, a name conflict will arise. Dropping the PROJECTIONS table will also eliminate the corresponding triggers.

Code Fragment 9.4 Insert a projection with and ID of 6.
Code Fragment 9.5 Delete projection 2.
Code Fragment 9.6 Change the type of projection 1 to 43.

##

##

## Queries

Reconstruction is easy to express on a state table, though only states in the past should be requested.

Sequenced queries over transaction-time state tables are expressed identically to such queries over a valid-time state tables. However, their semantics is in terms of "when was it recorded that."

Code Fragment 9.7 Reconstruct the PROJECTIONS table as of April 1, 1996
Code Fragment 9.8 When was it recorded that a projection had a type of 17?
Code Fragment 9.9 Give the change history for projections having a type of 12 or 18?
Code Fragment 9.10 When was it recorded that two projections had the same type?
Code Fragment 9.11 When was the type of a projection erroneously changed to be identical to that of an existing projection?
Code Fragment 9.12 Extract before images from a transaction-time state table.
Code Fragment 9.13 Extract after images from a transaction-time state table.
Code Fragment 9.14 Extract backlog from a transaction-time state table.

##

##

## Partitioning

> ### Current and Archival Stores

> A transaction-time state table may be represented with two tables, a current and an archival store, and maintained with triggers on the current store.

> Code Fragment 9.15 Create a temporally-partitioned transaction-time state table.
> Code Fragment 9.16 Reconstruct the PROJECTIONS table as of now, as a view, on a temporally partitioned table.

> Before creating the view PROJECTIONS, any other existing object named PROJECTIONS must be dropped. Otherwise, a name conflict will arise.

> Code Fragment 9.17 Triggers for maintaining the P_TT table.

> ### Queries

> Both the monitored table and the transaction-time state table can be defined as views on the two stores.

> Code Fragment 9.18 Reconstruct the PROJECTIONS table as of April 1, 1996.
> Code Fragment 9.19 Define the state table as a view.

> Before creating the view P_TT, the previously existing table P_TT must be dropped from the active database. Otherwise, a name conflict will arise. Dropping the P_TT table will also eliminate the corresponding triggers.

> ### Utilizing the Primary Key

> In a tri-partitioned table, the current store consists of just the primary key and the start date. The current store and the archival store can be maintained via triggers on the monitored table, thereby obviating any changes to legacy code.

> Code Fragment 9.20 Define a truncated current store.
> Code Fragment 9.21 Triggers for maintaining a tri-partitioned state table.

> Query files are considered batch files by Microsof SQL Server. A Trigger is accepted in a batch query file only if it is the first SQL statement on the file. Hence only one trigger is allowed per query file. Code fragment 9.21 must be split into three in order to accomodate at most one trigger per query file.

> Code Fragment 9.22 Define the state table as a view.

> Before creating the view P_TT, any previously existing object named P_TT must be dropped from the active database. Otherwise, a name conflict will arise.

##

##

## Vacuuming

The archival store can be reduced in size by purging information on invalid entities.

Code Fragment 9.23 Entity vacuum the archival store.
Code Fragment 9.24 Temporally vacuum the archival store.

The DBA may wish to vacuum the archival store based on a combination of criteria.

Code Fragment 9.25 Temporally vacuum the archival store.

The vacuum log, which indicates the meaning of queries on the state table, shoul be maintained automatically.

Code Fragment 9.26 Log the vacuuming operations.

##

##

## Loading the testing data

In this section we provide some of transactions used to populate the tables.

Table 9.1 A Transaction-Time State Table, P_TT.

This transaction inserts and modifies data on the PROJECTIONS table. As the PROJECTIONS table is being modified, the triggers in code fragment 9.2 are activated. As a result, table P_TT is populated. The effect of the triggers on table P_TT will set the Stop_Date column of the new rows in P_TT to the current date. To eliminate this effect in emulating past modifications, extra code is added on the transaction to modify the P_TT table and set the time stamp columns to the desired value.

Table 9.2 A Temporally Partitioned Transaction-Time State Table, P_TT_PAST and P_TT_Current.

In this specific transaction triggers cannot be override as they modify the table they are defined on. Hence, this transaction must be executed before the triggers in code fragment 9.17 are created.

A tri-partitioned table

This transaction inserts and modifies data on the PROJECTIONS table. As the PROJECTIONS table is being modified, the triggers in code fragment 9.21 are activated. As a result, tables P_TT_CURRENT and P_TT_PAST are populated. Again, the time stamps columns on those tables are incorrectly set to the current date and extra code has to be added to the transaction to eliminate this undesired behavior.

##

##

## How to Run these Queries in Microsof SQL Server

> Create a Database using the Microsoft SQL Enterprise Manager. Any name you choose will be fine.

> Set your new database as the active database.

> Use the SQL Query Tool provided in the SQL Enterprise Manager to load an run the queries on the presented order.

> It will display the results, as well as the rows affected.

##

#### HTML Credits

Jose Alvin G. Gendrano, Department of Computer Science, University of Arizona (jag@cs.arizona.edu)
Rachana R. Shah, Department of Computer Science, University of Arizona (rachana@cs.arizona.edu)
Ines Fernando Vega Lopez, Department of Computer Science, University of Arizona (ifvega@cs.arizona.edu)
Jian Yang, Department of Computer Science, University of Arizona (yangjian@cs.arizona.edu)
April 7, 1999 (Last Update)

#### Microsoft SQL Server Specific Code Credits:

Ines Fernando Vega Lopez, Department of Computer Science, University of Arizona (ifvega@cs.arizona.edu)
April 7, 1999 (Last Update)

##