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

# MAINTAINING STATE TABLES

## Sybase SQL Server Implementation Examples

#

## Contents at a Glance

> Defining State Tables

> > Initial Schema
> > Adding History
> > Temporal Keys
> > Handling Now
> > Keys, Reexamined
> > Referential Integrity

> Querying State Tables

> > Extracting the Current State
> > Extracting Prior States
> > Sequenced Queries
> > Sequenced Join
> > Nonsequenced Join
> > Duplicate Elimination

> Modifying State Tables

> > Current Modifications
> > Sequenced Modifications
> > Nonsequenced Modifications
> > Modifications that Mention Other Tables

> Appendix : Creating Sybase Sample Tables

#

## Detailed Contents

This document outlines implementation experiments using the Sybase DBMS. It follows the general outline of the TDB book and is arranged in the following manner.

> Defining State Tables

> > Initial Schema
> > Adding History
> > Temporal Keys
> > Handling Now
> > Keys, Reexamined
> > Referential Integrity

> Querying State Tables

> > Extracting the Current State
> > Extracting Prior States
> > Sequenced Queries
> > Sequenced Join
> > Nonsequenced Join
> > Duplicate Elimination

> Modifying State Tables

> > Current Modifications

> > > Current Insertions
> > > Current Deletions
> > > Current Updates

> > Sequenced Modifications

> > > Sequenced Insertions
> > > Sequenced Deletions
> > > Sequenced Updates

> > Nonsequenced Modifications
> > Modifications that Mention Other Tables

> > > Complex Current Modifications
> > > Complex Sequenced Modifications

> Appendix : Creating Sybase Sample Tables

##

## Defining State Tables

### Initial Schema

The following code fragments are Sybase examples for the corresponding SQL query in the book. Please refer to the book for the purposes of each query. These code fragments were tested for Sybase SQL server 10.0? under batch mode. Please see the appendix I f or more information.

> Examples

> > code fragment 5.1
> > code fragment 5.2
> > code fragment 5.3

##

##

## Adding History

##

##

## Temporal Keys

In Sybase, 'ASSERTION' is not a create option. Trigger on the other hand can be used to ensure the constraint as used in the following code fragments.
In the codes, we also used "syb_identity" to ensure that two rows aren't the exact same row, since ISQL has "auto identity" function to identify rows. To use this function, be sure to set this option on (sp_dboption database_name, "auto identity", "true").

> Examples

> > code fragment 5.4
> > code fragment 5.5

##

##

## Handling Now

##

##

## Keys, Reexamined

> Examples

> > code fragment 5.6
> > code fragment 5.7
> > code fragment 5.8
> > code fragment 5.9
> > code fragment 5.10

##

##

## Referential Integrity

Again, as in section 5.3, because in Sybase, 'ASSERTION' is not a create option, Trigger is commonly used to ensure referential integrity.

> Examples

> > code fragment 5.11
> > code fragment 5.12
> > code fragment 5.13
> > code fragment 5.14
> > code fragment 5.15
> > code fragment 5.16
> > code fragment 5.17

##

##

## Querying State Tables

### Extracting the Current State

In queries of this chapter, the table POSITIONS sometimes refers to snapshot table, sometimes refers to temporal table. I the code fragments below, CPOSITION refers to the snapshot table, while POSITION refers to the temporal table.

> Examples

> > code fragment 6.1
> > code fragment 6.2
> > code fragment 6.3
> > code fragment 6.4

##

## Extracting Prior States

> Examples

> > code fragment 6.5

##

## Sequenced Queries

> Examples

> > code fragment 6.6
> > code fragment 6.7
> > code fragment 6.8
> > code fragment 6.9
> > code fragment 6.10

##

## Sequenced Join

### Temporal Join

> #### Union

> This approach uses the basic UNION operator, standard in SQL. This approach is more general, but slower because it automatically eliminates duplicates and sorts the results.

> > Sybase SQLPLUS code (Tjoin.SQL)
> > Result (TJoin.result)

> #### Union All

> Uses Union-All to bypass the duplicate elimination and sorting operations performed by the ordinary Sybase UNION operator. Logically, sorting and duplicate elimination should not be a by-product of a join, so this approach seems more correct. However, without elimination of duplicates, a special case were there are tuples with exactly the same periods would need to be considered. See the last select statement, and pay close attention to the compound condition added to it.

> > Sybase SQLPLUS code (Tjoin.SQL)
> > Result (TJoin.result)

> Conclusion: From the performance graph it seems that UNION and UNIONALL operators give the same performance

##

## Nonsequenced Variants

> Examples

> > code fragment 6.13
> > code fragment 6.14

##

## Duplicate Elimination

### Coalescing While Removing Duplicates

> #### Coalescing

> > #### Coalesce via a Cursor

> > Uses cursors to go through every record, comparing period boundaries. Garnered the best results from the performance experiments made. Problem with this approach, though, is that it is not SQL at all and relies on a programming language extension for SQL from Sybase.

> > > Sybase SQLPLUS code (CursorBased.SQL)
> > > Result (CursorBased.result)

#

## Modifying State Tables

### Current Modifications

Because in Sybase isql, subquery after where is not supported, we used transaction instead to do the job. When using transaction, one important thing must be noted is that, we must use the proper transaction mode, and we shouldn't mix the mode. In the following sample codes, "chained on" mode is used.

> Current Insertions

> > code fragment 7.1
> > code fragment 7.2
> > code fragment 7.3
> > code fragment 7.4
> > code fragment 7.5
> > code fragment 7.6

> Current Deletion

> > code fragment 7.7
> > code fragment 7.8

> Current Updates

> > code fragment 7.9
> > code fragment 7.10
> > code fragment 7.11

##

##

## Sequenced Modifications

> Sequenced Insertions

> > code fragment 7.12
> > code fragment 7.13
> > code fragment 7.14

> Sequenced Deletions

> > code fragment 7.15

> Sequenced Updates

> > code fragment 7.16
> > code fragment 7.17
> > code fragment 7.18

##

##

## Nonsequenced Modifications

> Examples

> > code fragment 7.19

##

##

## Modifications that Mention Other Tables

> Complex Current Modifications

> > code fragment 7.20
> > code fragment 7.21
> > code fragment 7.22

> Complex Sequenced Modifications

> > code fragment 7.23

## Appendix : Creating Sybase Sample Tables

There are two kinds of tables used for the example code fragments. One is "snapshot table", the other is "Temporal Table".
The following two code fragments are used to create these two sample tables. create_snap_table.sql for snapshot table,
create_tempo_table.sql for temporal table.
These two codes are executed in batch mode using command

> isql -U(user name) -P(password) -i(input file) [-o(output file)]

to create the actual sample tables. In the second code fragment, we didn't set any constraints such as foreign key etc on purpose. Some of these constraints are added using code fragments later.

####

#### HTML Credits

Jose Alvin G. Gendrano, Department of Computer Science, University of Arizona (jag@cs.arizona.edu)
Wenmin Chen, Department of Computer Science, University of Arizona (wenmin@cs.arizona.edu)
Rachana Shah, Department of Computer Science, University of Arizona (rachana@cs.arizona.edu)
Jian Yang, Department of Computer Science, University of Arizona (yangjian@cs.arizona.edu)
February 4, 1998 (Last Update)

#### SYBASE Specific Code Credits

Wenmin Chen, Department of Computer Science, University of Arizona (wenmin@cs.arizona.edu)
December 17, 1997 (Last Update)