--
-- ER/Studio 8.0 SQL Code Generation
-- Company :      Berkeley Seismological Laboratory
-- Project :      CISN Waveform Schema
-- Author :       Stephane Zuzlewski
--
-- Date Created : Wednesday, July 21, 2010 12:07:46
-- Target DBMS : Oracle 10g
--

DROP TABLE AssocWaE CASCADE CONSTRAINTS
;
DROP TABLE Filename CASCADE CONSTRAINTS
;
DROP TABLE Subdir CASCADE CONSTRAINTS
;
DROP TABLE Waveform CASCADE CONSTRAINTS
;
DROP TABLE Waveroots CASCADE CONSTRAINTS
;
DROP SEQUENCE fiseq
;
DROP SEQUENCE sdseq
;
DROP SEQUENCE waseq
;
-- 
-- SEQUENCE: fiseq 
--

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

-- 
-- SEQUENCE: sdseq 
--

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

-- 
-- 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: 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        FLOAT(126)        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),
    qc_level        CHAR(1)           DEFAULT ('D') NOT NULL,
    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
LOGGING,
PARTITION Wf1987
VALUES LESS THAN (567993614)
TABLESPACE PWF1987
LOGGING,
PARTITION Wf1988
VALUES LESS THAN (599616014)
TABLESPACE PWF1988
LOGGING,
PARTITION Wf1989
VALUES LESS THAN (631152015)
TABLESPACE PWF1989
LOGGING,
PARTITION Wf1990
VALUES LESS THAN (662688016)
TABLESPACE PWF1990
LOGGING,
PARTITION Wf1991
VALUES LESS THAN (694224016)
TABLESPACE PWF1991
LOGGING,
PARTITION Wf1992
VALUES LESS THAN (725846417)
TABLESPACE PWF1992
LOGGING,
PARTITION Wf1993
VALUES LESS THAN (757382418)
TABLESPACE PWF1993
LOGGING,
PARTITION Wf1994
VALUES LESS THAN (788918419)
TABLESPACE PWF1994
LOGGING,
PARTITION Wf1995
VALUES LESS THAN (820454420)
TABLESPACE PWF1995
LOGGING,
PARTITION Wf1996
VALUES LESS THAN (852076820)
TABLESPACE PWF1996
LOGGING,
PARTITION Wf1997
VALUES LESS THAN (883612821)
TABLESPACE PWF1997
LOGGING,
PARTITION Wf1998
VALUES LESS THAN (915148821)
TABLESPACE PWF1998
LOGGING,
PARTITION Wf1999
VALUES LESS THAN (946684821)
TABLESPACE PWF1999
LOGGING
)
;



-- 
-- TABLE: Waveroots 
--

CREATE TABLE Waveroots(
    archive         VARCHAR2(8)       NOT NULL,
    wavetype        VARCHAR2(2)       NOT NULL,
    status          VARCHAR2(2)       NOT NULL,
    net             VARCHAR2(8)       NOT NULL,
    wave_fmt        NUMBER(2, 0)      NOT NULL,
    datetime_on     NUMBER(25, 10)    NOT NULL,
    datetime_off    NUMBER(25, 10)    NOT NULL,
    wcopy           NUMBER(10, 0)     NOT NULL,
    fileroot        VARCHAR2(255)     NOT NULL,
    CONSTRAINT PK5 PRIMARY KEY (archive, wavetype, status, net, wave_fmt, datetime_on, datetime_off, wcopy)
)
TABLESPACE WF_DATA
;



-- 
-- 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)
;


