--
-- ER/Studio 7.1 SQL Code Generation
-- Company :      Berkeley Seismological Laboratory
-- Project :      NCEDC/TriNet Waveform Schema
-- Author :       Stephane Zuzlewski
--
-- Date Created : Tuesday, February 20, 2007 10:50:07
-- Target DBMS : Oracle 9i
--

DROP TABLE AssocWaE CASCADE CONSTRAINTS
;
DROP TABLE Filename CASCADE CONSTRAINTS
;
DROP TABLE Pathname CASCADE CONSTRAINTS
;
DROP TABLE Subdir CASCADE CONSTRAINTS
;
DROP TABLE Waveform CASCADE CONSTRAINTS
;
-- 
-- SEQUENCE: fiseq 
--

DROP SEQUENCE fiseq
;

CREATE SEQUENCE FISEQ
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    CACHE 20
    ORDER
;

-- 
-- SEQUENCE: sdseq 
--

DROP SEQUENCE sdseq
;

CREATE SEQUENCE SDSEQ
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    CACHE 20
    ORDER
;

-- 
-- SEQUENCE: waseq 
--

DROP SEQUENCE waseq
;

CREATE SEQUENCE WASEQ
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    CACHE 20
    ORDER
;

-- 
-- TABLE: AssocWaE 
--

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



-- 
-- TABLE: Filename 
--

CREATE TABLE Filename(
    fileid          NUMBER(15, 0)     NOT NULL,
    dfile           VARCHAR2(32)      NOT NULL,
    datetime_on     NUMBER(25, 10),
    datetime_off    NUMBER(25, 10),
    nbytes          NUMBER(10, 0),
    subdirid        NUMBER(15, 0)     NOT NULL,
    lddate          DATE              DEFAULT (SYSDATE) NOT NULL,
    CONSTRAINT Fil00 PRIMARY KEY (fileid)
)
TABLESPACE WF_DATA
;



-- 
-- TABLE: Pathname 
--

CREATE TABLE Pathname(
    pathname    VARCHAR2(255)    NOT NULL,
    net         VARCHAR2(8)      NOT NULL,
    wave_fmt    NUMBER(2, 0)     NOT NULL,
    CONSTRAINT PK5 PRIMARY KEY (pathname, net, wave_fmt)
)
TABLESPACE WF_DATA
;



-- 
-- TABLE: Subdir 
--

CREATE TABLE Subdir(
    subdirid      NUMBER(15, 0)    NOT NULL,
    subdirname    VARCHAR2(64)     NOT NULL,
    lddate        DATE             DEFAULT (SYSDATE) NOT NULL,
    CONSTRAINT Sub00 PRIMARY KEY (subdirid)
)
TABLESPACE WF_DATA
;



-- 
-- TABLE: Waveform 
--

CREATE TABLE Waveform(
    wfid            NUMBER(15, 0)     NOT NULL,
    net             VARCHAR2(8)       NOT NULL,
    sta             VARCHAR2(6)       NOT NULL,
    auth            VARCHAR2(15)      NOT NULL,
    subsource       VARCHAR2(8),
    channel         VARCHAR2(8),
    channelsrc      VARCHAR2(8),
    seedchan        VARCHAR2(3)       NOT NULL,
    location        VARCHAR2(2),
    archive         VARCHAR2(8)       NOT NULL,
    datetime_on     NUMBER(25, 10)    NOT NULL,
    datetime_off    NUMBER(25, 10)    NOT NULL,
    samprate        NUMBER(12, 8)     NOT NULL,
    wavetype        VARCHAR2(1),
    fileid          NUMBER(15, 0)     NOT NULL,
    foff            NUMBER(12, 0),
    nbytes          NUMBER(10, 0),
    traceoff        NUMBER(10, 0),
    tracelen        NUMBER(10, 0),
    status          VARCHAR2(1)       NOT NULL,
    wave_fmt        NUMBER(2, 0),
    format_id       NUMBER(2, 0),
    wordorder       NUMBER(1, 0),
    recordsize      NUMBER(5, 0),
    locevid         VARCHAR2(12),
    lddate          DATE              DEFAULT (SYSDATE) NOT NULL,
    CONSTRAINT WF00 PRIMARY KEY (wfid)
)
TABLESPACE WF_DATA
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
)
;



-- 
-- INDEX: wffileind 
--

DROP INDEX wffileind
;

CREATE INDEX wffileind ON Filename(dfile)
;
-- 
-- INDEX: wfdonind 
--

DROP INDEX wfdonind
;

CREATE INDEX wfdonind ON Waveform(datetime_on)
LOCAL
;
-- 
-- INDEX: wfdoffind 
--

DROP INDEX wfdoffind
;

CREATE INDEX wfdoffind ON Waveform(datetime_off)
LOCAL
;
-- 
-- TABLE: AssocWaE 
--

ALTER TABLE AssocWaE ADD CONSTRAINT Waveform_Event 
    FOREIGN KEY (wfid)
    REFERENCES Waveform(wfid)
;


-- 
-- TABLE: Filename 
--

ALTER TABLE Filename ADD CONSTRAINT Filename_PartialPath 
    FOREIGN KEY (subdirid)
    REFERENCES Subdir(subdirid)
;


-- 
-- TABLE: Waveform 
--

ALTER TABLE Waveform ADD CONSTRAINT Waveform_Filename 
    FOREIGN KEY (fileid)
    REFERENCES Filename(fileid)
;