--
--
-- SQL Script  : Create_Schema_WF.sql
--
-- Description : This SQL script creates the Waveform Schema
--               version 1.2.1 in the Oracle RDBMS.
--
--


--
-- Delete the existing tables
--
DROP TABLE Waveform		CASCADE CONSTRAINTS;
DROP TABLE AssocWaE		CASCADE CONSTRAINTS;
DROP TABLE Filename		CASCADE CONSTRAINTS;

--
-- Create the new schema
--
CREATE SCHEMA AUTHORIZATION ncedcdba;

CREATE TABLE Waveform (
wfid		NUMBER(10)	NOT NULL,
net             VARCHAR2(8),
sta		VARCHAR2(6),
auth		VARCHAR2(15),
subsource	VARCHAR2(8),
channel		VARCHAR2(3),
channelsrc	VARCHAR2(8),
seedchan	VARCHAR2(3),
location	VARCHAR2(2),
archive		VARCHAR2(8),
datetime_on	NUMBER(25,10),
datetime_off	NUMBER(25,10),
samprate	NUMBER(12,8),
wavetype	VARCHAR2(1),
fileid		NUMBER(10),
foff		NUMBER(12),
nbytes		NUMBER(12),
traceoff	NUMBER(12),
tracelen	NUMBER(12),
status		VARCHAR2(1),
wave_fmt	NUMBER(2),
format_id	NUMBER(2),
wordorder	NUMBER(1),
recordsize	NUMBER(5),
locevid		VARCHAR2(12),
lddate		DATE		DEFAULT (SYSDATE),
CONSTRAINT	WF00		PRIMARY KEY (wfid)
)
PARTITION BY RANGE (datetime_on) (
PARTITION Wf1986  VALUES LESS THAN (536457613)
			TABLESPACE PWF1986,
PARTITION Wf1987  VALUES LESS THAN (567993614)
			TABLESPACE PWF1987,
PARTITION Wf1988  VALUES LESS THAN (599616014)
			TABLESPACE PWF1988,
PARTITION Wf1989  VALUES LESS THAN (631152015)
			TABLESPACE PWF1989,
PARTITION Wf1990  VALUES LESS THAN (662688016)
			TABLESPACE PWF1990,
PARTITION Wf1991  VALUES LESS THAN (694224016)
			TABLESPACE PWF1991,
PARTITION Wf1992  VALUES LESS THAN (725846417)
			TABLESPACE PWF1992,
PARTITION Wf1993  VALUES LESS THAN (757382418)
			TABLESPACE PWF1993,
PARTITION Wf1994  VALUES LESS THAN (788918419)
			TABLESPACE PWF1994,
PARTITION Wf1995  VALUES LESS THAN (820454420)
			TABLESPACE PWF1995,
PARTITION Wf1996  VALUES LESS THAN (852076820)
			TABLESPACE PWF1996,
PARTITION Wf1997  VALUES LESS THAN (883612821)
			TABLESPACE PWF1997,
PARTITION Wf1998  VALUES LESS THAN (915148821)
			TABLESPACE PWF1998,
PARTITION Wf1999  VALUES LESS THAN (946684821)
			TABLESPACE PWF1999
				 ) TABLESPACE      IWF001;

CREATE TABLE AssocWaE (
wfid		NUMBER(10)	NOT NULL,
evid		NUMBER(10)	NOT NULL,
datetime_on     NUMBER(25,10),
datetime_off    NUMBER(25,10),
lddate		DATE		DEFAULT (SYSDATE),
CONSTRAINT	AWE00		PRIMARY KEY (wfid, evid)
) TABLESPACE	WF001;

CREATE TABLE Filename (
fileid		NUMBER(10)	NOT NULL,
dfile		VARCHAR2(32)	NOT NULL,
datetime_on	NUMBER(25,10),
datetime_off	NUMBER(25,10),
nbytes		NUMBER(12),
lddate		DATE		DEFAULT (SYSDATE),
CONSTRAINT	Fil00		PRIMARY KEY (fileid)
) TABLESPACE	WF001
  PCTFREE	5;


--
-- Create the sequence numbers
--
DROP SEQUENCE	waseq;
DROP SEQUENCE	fiseq;

CREATE SEQUENCE waseq	INCREMENT BY 1  START WITH 1;
CREATE SEQUENCE fiseq	INCREMENT BY 1  START WITH 1;


--
-- Create the indexes
--
CREATE	INDEX wfdonind	ON Waveform (datetime_on)
	TABLESPACE	IWF001;
CREATE	INDEX wfdoffind	ON Waveform (datetime_off)
	TABLESPACE	IWF001;


COMMIT;
EXIT;
