1
Introduction

The Oracle8 Time Series Cartridge is an extension to Oracle8 that provides storage and retrieval of timestamped data through object types. The cartridge is a building block for applications rather than being an end-user application in itself. It consists of datatypes along with related functions for managing and processing time series data.

For example, applications can use this cartridge to process historical data derived from financial market transactions, such as trades of stocks, bonds, and mutual fund shares. In such applications, the functions included with the Time Series cartridge let you conveniently perform operations ranging from the simple to the complex, such as:

finding the opening, closing, low, and high prices for a stock on a specific date
calculating monthly volumes for a stock for a specific year
deriving the 30-day moving average for a stock over a year

Time series applications have certain distinct requirements and some degree of commonality. The time series datatypes accommodate the commonality and support extensions that address application-specific requirements. With this cartridge, time series data can be managed more conveniently and efficiently than is possible using only traditional datatypes and user-defined functions.

With the Time Series cartridge, you can use or adapt existing tables for time series applications, or you can create new tables. You can also extend the capabilities of the cartridge to add or modify functions and to create customized calendars.

1.1 Data Cartridges

Within the Oracle Network Computing Architecture^TM (NCA), data cartridges facilitate the storage and retrieval of complex datatypes required by nontraditional database applications, such as geographic information systems, imaging, workflow, document management, and digital libraries. These applications are built using components or modules that support the capture, input, processing, analysis, storage, retrieval, and display of the complex datatypes.

A data cartridge is the mechanism by which clients, application-specific servers, and database servers can be easily and reliably extended. The Oracle8 Time Series Cartridge provides support for time series domain-specific types, functions, and interfaces. The Time Series cartridge focuses on a set of time series data representation and access mechanisms sufficient to support many applications and the development of more specialized cartridges.

1.2 Object Relational Technology

The objects option makes Oracle8 an object-relational database management system, which means that users can define additional kinds of data -- specifying both the structure of the data and the ways of operating on it -- and use these types within the relational model. This approach adds value to the data stored in a database.

Oracle8 with the objects option stores structured business data in its natural form and allows applications to retrieve it that way. For that reason it works efficiently with applications developed using object-oriented programming techniques.

1.3 Storing and Accessing Data

The Time Series cartridge can store time series data in the database under transactional control.

Once stored in the database, this data can be queried and retrieved by finding a row in a table that contains the primary key (which includes the timestamp) using the various alphanumeric columns (attributes) of the table. Typical queries might include the following:

Select the closing price from a stock market data table where the ticker (stock symbol) is XYZ and the date is 30-May-1997.
Select the 30-day moving average of stock XYZ for the month of May 1997.

Applications access and manipulate time series data using SQL or PL/SQL^TM. See the Oracle8 SQL Reference manual for information on SQL syntax.

1.4 Installing the Kit

The exact procedure for installing the Time Series cartridge is platform-dependent. For more detailed information, see the README.txt file for your platform which can be found either in $ORACLE_HOME/ord/ts/admin (UNIX systems) or $ORACLE_HOME\ord80\ts\admin (Windows NT systems). On many platforms, automated default installation is available using Oracle Installer; however, on some platforms manual installation of the database objects is required.

This section describes the steps required to manually install the Time Series cartridge. It is intended for users who want to customize the database installation or who simply want a better understanding of the Time Series installation process.

To use the Time Series cartridge, at least the following software components must be installed: Oracle8 Enterprise Edition (RDBMS), PL/SQL (on platforms on which it is a separate installation option), and the Oracle8 Time Series Cartridge. These components can be installed all at once, or the Oracle8 Time Series Cartridge can be added to an existing Oracle8 Enterprise Edition release 8.0.4 installation that includes PL/SQL.

Follow these steps to perform a manual installation of the Time Series cartridge:

If the ORDSYS user does not already exist, decide on a password.
ORDSYS is the standard schema for Oracle-supplied cartridges, and it has special privileges for data cartridges.
Create and start the database.
For detailed information about database creation and startup, see the Oracle8 Installation and Configuration Guide for your operating system, the Oracle8 Administrator's Guide, and the Oracle8 Concepts manual.
Install the UTLREF package, which is needed by the Time Series cartridge:
```
SVRMGR> @$ORACLE_HOME/rdbms/admin/utlrefld.sql
```
If the ORDSYS account does not already exist, create it:
```
SVRMGR> CREATE USER ORDSYS IDENTIFIED BY <password>;
```
The <password> is the password you chose in step 1.

Set the privileges for ORDSYS user:

SVRMGR> GRANT connect,resource,create library TO ORDSYS;

Install the Time Series cartridge datatypes and stored procedures:
```
SVRMGR> CONNECT ORDSYS/<password>
SVRMGR> @$ORACLE_HOME/ord/ts/admin/ordtinst.sql
```
The user group PUBLIC is granted execute privilege on all Time Series cartridge datatypes and packages.

1.5 Creating Public Synonyms for Time Series Packages

All Time Series cartridge packages and datatypes are installed under the ORDSYS schema, and all users must include the ORDSYS schema name when referring to these packages and datatypes. However, to simplify references to packages, you can define public synonyms for the Calendar and TimeSeries packages.

To create public synonyms, run the ordtsyn.sql file supplied with the Time Series cartridge in the admin directory. The ordtsyn.sql file creates the following public synonyms:

CREATE PUBLIC SYNONYM TimeSeries for ORDSYS.TimeSeries;
CREATE PUBLIC SYNONYM Calendar for ORDSYS.Calendar;

1.6 Time Series Cartridge Demos (Demonstrations)

Table 1-1 shows the demos (SQL scripts and related files that demonstrate capabilities) included with the Time Series cartridge. This table includes a description of each demo and the default directory in which its files are installed. (The exact location and directory syntax are platform-dependent.)

Table 1-1 Time Series Cartridge Demos

Description	Directory
Usage demo for end users and product developers who want to use existing cartridge feature	demo/usage
Advanced-developer demo for those who want to extend the cartridge features	demo/extend
OCI demo showing how to call cartridge functions using the Oracle Call Interface	demo/oci
PROC/C++ demo showing how to call cartridge functions in applications created using the Oracle ProC/C++ Precompiler	demo/proc
Developer/2000^TM demo showing how to call cartridge functions in an Oracle Forms^TM application	demo/dev2k

Each demo is described in a README file in its directory.

The usage demo, described in this section, is a working example of the usage of the cartridge. The example models a historical database of stock pricing and provides sample queries over this stock database.

The usage demo is designed to guide you through the cartridge in a step-by-step fashion. It includes example code for creating and populating tables and calendars, constructing security views, constructing views to synthesize the interface to time series functions, and running some example queries.

1.6.1 Running the Usage Demo

After the cartridge has been installed, you can run the usage demo by going to the appropriate directory (see Table 1-1) and invoking the demo.sql procedure, as follows:

% svrmgrl 
SVRMGR> @demo

1.6.2 Usage Demo Files

The usage demo files include examples of bulk and incremental loading, defining tables, calendars, and views for the time series cartridge, and running example queries. These files are listed in Table 1-2.

Table 1-2 Time Series Cartridge Usage Demo Files

File	Description
demo.sql	Main procedure file
stockdat.ctl	SQL*Loader® control file
stockdat.dat	SQL*Loader data file for time series data
tables.sql	DDL for tables
popcal.sql	Defines calendars and populates calendar table
securevw.sql	Example security view
refvw.sql	Example reference-based view
queries.sql	Example time series queries (SQL)
queplsql.sql	Example time series queries (PL/SQL)
calqueries.sql	Example calendar queries (PL/SQL)
incload.sql	Incremental load script
stockinc.ctl	SQL*Loader control file for incremental load
stockinc.dat	SQL*Loader data file for incremental time series data
cleanup.sql	Deletes database objects created by the demo

1.6.3 Tables and Views in the Usage Demo

The stock database consists of three tables:

stockdemo stores historical time series pricing data.
stockdemo_calendars stores instances of calendars.
stockdemo_metadata maintains mapping between time series (here, for tickers) and calendars. This metadata table would not be needed if all tickers were associated with a private calendar that is named by the ticker or if a single calendar were shared by all tickers. The stockdemo_metadata table allows, for example, five tickers to be associated with one calendar, two tickers with another calendar, and so on.

To maintain time series consistency and provide a collection-based interface for time series functions, two views are constructed using these tables.

stockdemo_sv is a security view. A security view should be used for any insert, update, and delete operations to time series data.
stockdemo_ts is a reference-based view. A reference-based view provides an object model of a time series, and it can be used for efficient read-only access using Time Series cartridge functions.

The security view ensures that insert, update, and delete operations maintain a time series that is consistent with the associated calendar. (Time series consistency is explained in Section 2.6.) The security view and the reference-based view access the three underlying tables. The reference-based view synthesizes references to collections. (Reference-based views are explained in Section 2.5.2.)

Figure 1-1 shows the layering and interdependencies between underlying tables and the security and reference-based views.

1 Introduction