--
-- ER/Studio Data Architect 9.0 SQL Code Generation
-- Company :      Berkeley Seismological Laboratory
-- Project :      CISN/ANSSt Application
-- Author :       Stephane Zuzlewski
--
-- Date Created : Thursday, January 31, 2013 15:12:29
-- Target DBMS : Oracle 10g
--

DROP TABLE alarm_action CASCADE CONSTRAINTS
;
DROP TABLE appchannels CASCADE CONSTRAINTS
;
DROP TABLE applications CASCADE CONSTRAINTS
;
DROP TABLE assoc_region_group CASCADE CONSTRAINTS
;
DROP TABLE assocnte CASCADE CONSTRAINTS
;
DROP TABLE autoposter CASCADE CONSTRAINTS
;
DROP TABLE channelmap_ampparms CASCADE CONSTRAINTS
;
DROP TABLE channelmap_codaparms CASCADE CONSTRAINTS
;
DROP TABLE config_param CASCADE CONSTRAINTS
;
DROP TABLE credit CASCADE CONSTRAINTS
;
DROP TABLE credit_alias CASCADE CONSTRAINTS
;
DROP TABLE epochtimebase CASCADE CONSTRAINTS
;
DROP TABLE eventmatchpassingscore CASCADE CONSTRAINTS
;
DROP TABLE eventpriorityparams CASCADE CONSTRAINTS
;
DROP TABLE gazetteer_region_group CASCADE CONSTRAINTS
;
DROP TABLE gazetteerbigtown CASCADE CONSTRAINTS
;
DROP TABLE gazetteerfault CASCADE CONSTRAINTS
;
DROP TABLE gazetteerline CASCADE CONSTRAINTS
;
DROP TABLE gazetteerpt CASCADE CONSTRAINTS
;
DROP TABLE gazetteerquake CASCADE CONSTRAINTS
;
DROP TABLE gazetteerquarry CASCADE CONSTRAINTS
;
DROP TABLE gazetteertown CASCADE CONSTRAINTS
;
DROP TABLE gazetteertype CASCADE CONSTRAINTS
;
DROP TABLE jasieventlock CASCADE CONSTRAINTS
;
DROP TABLE leap_seconds CASCADE CONSTRAINTS
;
DROP TABLE magprefpriority CASCADE CONSTRAINTS
;
DROP TABLE mcaparms CASCADE CONSTRAINTS
;
DROP TABLE messagetext CASCADE CONSTRAINTS
;
DROP TABLE nettrig CASCADE CONSTRAINTS
;
DROP TABLE orgprefpriority CASCADE CONSTRAINTS
;
DROP TABLE pcs_signal CASCADE CONSTRAINTS
;
DROP TABLE pcs_state CASCADE CONSTRAINTS
;
DROP TABLE pcs_transition CASCADE CONSTRAINTS
;
DROP TABLE peer_system_status CASCADE CONSTRAINTS
;
DROP TABLE request_card CASCADE CONSTRAINTS
;
DROP TABLE rt_role CASCADE CONSTRAINTS
;
DROP TABLE stacorrections CASCADE CONSTRAINTS
;
DROP TABLE stamapping CASCADE CONSTRAINTS
;
DROP TABLE subnet CASCADE CONSTRAINTS
;
DROP TABLE subnet_channel CASCADE CONSTRAINTS
;
DROP TABLE swarm_events CASCADE CONSTRAINTS
;
DROP TABLE swarm_state CASCADE CONSTRAINTS
;
DROP TABLE system_status CASCADE CONSTRAINTS
;
DROP TABLE trig_channel CASCADE CONSTRAINTS
;
DROP TABLE unassocamp CASCADE CONSTRAINTS
;
-- 
-- TABLE: alarm_action 
--

CREATE TABLE alarm_action(
    event_id        NUMBER(15, 0)    NOT NULL,
    alarm_action    VARCHAR2(15)     NOT NULL,
    action_state    VARCHAR2(15)     NOT NULL,
    modcount        NUMBER(8, 0)     NOT NULL,
    mod_time        DATE,
    CONSTRAINT alarm_actionkey01 PRIMARY KEY (event_id, alarm_action, action_state, modcount)
)
;



-- 
-- TABLE: appchannels 
--

CREATE TABLE appchannels(
    progid      NUMBER(8, 0)    NOT NULL,
    net         VARCHAR2(8)     NOT NULL,
    sta         VARCHAR2(6)     NOT NULL,
    seedchan    VARCHAR2(3)     NOT NULL,
    location    VARCHAR2(2)     NOT NULL,
    config      VARCHAR2(64),
    ondate      DATE            NOT NULL,
    offdate     DATE,
    lddate      DATE            DEFAULT (SYSDATE),
    CONSTRAINT config_chankey01_1 PRIMARY KEY (progid, net, sta, seedchan, location, ondate)
)
;



-- 
-- TABLE: applications 
--

CREATE TABLE applications(
    progid    NUMBER(8, 0)    NOT NULL,
    name      VARCHAR2(16)    NOT NULL,
    lddate    DATE            DEFAULT (SYSDATE),
    CONSTRAINT progkey01_1 PRIMARY KEY (progid)
)
;



-- 
-- TABLE: assoc_region_group 
--

CREATE TABLE assoc_region_group(
    grpid       VARCHAR2(4)     NOT NULL,
    name        VARCHAR2(24)    NOT NULL,
    auth        VARCHAR2(15)    NOT NULL,
    rg_order    NUMBER(2, 0)    NOT NULL,
    CONSTRAINT pk_assoc_reggrp PRIMARY KEY (grpid, name)
)
;



-- 
-- TABLE: assocnte 
--

CREATE TABLE assocnte(
    ntid         NUMBER(15, 0)    NOT NULL,
    evid         NUMBER(15, 0)    NOT NULL,
    auth         VARCHAR2(15)     NOT NULL,
    subsource    VARCHAR2(8),
    rflag        VARCHAR2(2),
    lddate       DATE             DEFAULT (SYSDATE),
    wfflag       NUMBER(1, 0),
    CONSTRAINT assocntekey01 PRIMARY KEY (ntid, evid)
)
;



-- 
-- TABLE: autoposter 
--

CREATE TABLE autoposter(
    instancename    VARCHAR2(30)    NOT NULL,
    controlgroup    VARCHAR2(20)    NOT NULL,
    sourcetable     VARCHAR2(20)    NOT NULL,
    state           VARCHAR2(20)    NOT NULL,
    rank            NUMBER          NOT NULL,
    result          NUMBER,
    acton           VARCHAR2(6),
    eventtype       VARCHAR2(2),
    lastid          NUMBER,
    lastlddate      DATE,
    lddate          DATE,
    resulttext      VARCHAR2(80),
    CONSTRAINT pk3_1 PRIMARY KEY (instancename)
)
;



-- 
-- TABLE: channelmap_ampparms 
--

CREATE TABLE channelmap_ampparms(
    net           VARCHAR2(8)      NOT NULL,
    sta           VARCHAR2(6)      NOT NULL,
    seedchan      VARCHAR2(3)      NOT NULL,
    location      VARCHAR2(2)      NOT NULL,
    ondate        DATE             NOT NULL,
    offdate       DATE             NOT NULL,
    channel       VARCHAR2(3),
    channelsrc    VARCHAR2(8),
    clip          NUMBER(14, 2),
    lddate        DATE             DEFAULT (SYSDATE),
    CONSTRAINT cm_amp_key PRIMARY KEY (net, sta, seedchan, location, ondate)
)
;



-- 
-- TABLE: channelmap_codaparms 
--

CREATE TABLE channelmap_codaparms(
    net           VARCHAR2(8)     NOT NULL,
    sta           VARCHAR2(6)     NOT NULL,
    seedchan      VARCHAR2(3)     NOT NULL,
    location      VARCHAR2(2)     NOT NULL,
    ondate        DATE            NOT NULL,
    offdate       DATE            NOT NULL,
    channel       VARCHAR2(3),
    channelsrc    VARCHAR2(8),
    cutoff        NUMBER(8, 2),
    gain_corr     NUMBER(6, 3),
    lddate        DATE            DEFAULT (SYSDATE),
    CONSTRAINT cm_coda_key PRIMARY KEY (net, sta, seedchan, location, ondate)
)
;



-- 
-- TABLE: config_param 
--

CREATE TABLE config_param(
    progid       NUMBER(15, 0)    NOT NULL,
    parameter    VARCHAR2(32),
    value        VARCHAR2(64),
    lddate       DATE             DEFAULT (SYSDATE),
    CONSTRAINT configparamkey01 PRIMARY KEY (progid)
)
;



-- 
-- TABLE: credit 
--

CREATE TABLE credit(
    id       NUMBER(15, 0)    NOT NULL,
    tname    VARCHAR2(30)     NOT NULL,
    refer    VARCHAR2(80)     NOT NULL,
    CONSTRAINT pk_credit PRIMARY KEY (id, tname)
)
;



-- 
-- TABLE: credit_alias 
--

CREATE TABLE credit_alias(
    alias    VARCHAR2(32)    NOT NULL,
    keyid    VARCHAR2(32)    NOT NULL,
    CONSTRAINT pk_alias PRIMARY KEY (alias, keyid)
)
;



-- 
-- TABLE: epochtimebase 
--

CREATE TABLE epochtimebase(
    base       VARCHAR2(1)    NOT NULL,
    ondate     DATE           NOT NULL,
    offdate    DATE,
    CONSTRAINT pk5 PRIMARY KEY (base, ondate)
)
;



-- 
-- TABLE: eventmatchpassingscore 
--

CREATE TABLE eventmatchpassingscore(
    auth              VARCHAR2(15)    NOT NULL,
    picktimediff      NUMBER          NOT NULL,
    origintimediff    NUMBER          NOT NULL,
    horizdiff         NUMBER          NOT NULL,
    CONSTRAINT eventmatchpasskey01 PRIMARY KEY (auth)
)
;



-- 
-- TABLE: eventpriorityparams 
--

CREATE TABLE eventpriorityparams(
    magfactor     NUMBER    NOT NULL,
    agefactor     NUMBER    NOT NULL,
    qualfactor    NUMBER    NOT NULL,
    CONSTRAINT eppkey01 PRIMARY KEY (magfactor, agefactor, qualfactor)
)
;



-- 
-- TABLE: gazetteer_region_group 
--

CREATE TABLE gazetteer_region_group(
    grpid          VARCHAR2(4)     NOT NULL,
    description    VARCHAR2(80)    NOT NULL,
    CONSTRAINT pk_gazreggrp PRIMARY KEY (grpid)
)
;



-- 
-- TABLE: gazetteerbigtown 
--

CREATE TABLE gazetteerbigtown(
    gazid     NUMBER(16, 0)    NOT NULL,
    pop       NUMBER(10, 0),
    remark    VARCHAR2(80),
    lddate    DATE             DEFAULT (SYSDATE),
    CONSTRAINT gazbigtown_pk_gazid PRIMARY KEY (gazid)
)
;



-- 
-- TABLE: gazetteerfault 
--

CREATE TABLE gazetteerfault(
    gazid     NUMBER          NOT NULL,
    remark    VARCHAR2(80),
    lddate    DATE            DEFAULT (SYSDATE),
    CONSTRAINT gazfault_pk_gazid PRIMARY KEY (gazid)
)
;



-- 
-- TABLE: gazetteerline 
--

CREATE TABLE gazetteerline(
    gazid     NUMBER          NOT NULL,
    type      NUMBER(3, 0)    NOT NULL,
    name      VARCHAR2(80)    NOT NULL,
    line      NUMBER          NOT NULL,
    format    VARCHAR2(16),
    points    BLOB,
    lddate    DATE            DEFAULT (SYSDATE),
    CONSTRAINT gazline_pk_gazid PRIMARY KEY (gazid)
)
;



-- 
-- TABLE: gazetteerpt 
--

CREATE TABLE gazetteerpt(
    gazid     NUMBER(16, 0)    NOT NULL,
    type      NUMBER(3, 0)     NOT NULL,
    lat       NUMBER(9, 7)     NOT NULL,
    lon       NUMBER(10, 7)    NOT NULL,
    z         NUMBER(7, 0),
    name      VARCHAR2(48)     NOT NULL,
    state     VARCHAR2(2),
    county    VARCHAR2(32),
    map       VARCHAR2(48),
    datumh    VARCHAR2(8),
    datumv    VARCHAR2(8),
    lddate    DATE             DEFAULT (SYSDATE),
    CONSTRAINT gazpt_pk_gazid PRIMARY KEY (gazid)
)
;



-- 
-- TABLE: gazetteerquake 
--

CREATE TABLE gazetteerquake(
    gazid     NUMBER(16, 0)    NOT NULL,
    remark    VARCHAR2(80)     NOT NULL,
    lddate    DATE             DEFAULT (SYSDATE),
    CONSTRAINT gazeq_pk_gazid PRIMARY KEY (gazid)
)
;



-- 
-- TABLE: gazetteerquarry 
--

CREATE TABLE gazetteerquarry(
    gazid     NUMBER(16, 0)    NOT NULL,
    remark    VARCHAR2(80)     NOT NULL,
    lddate    DATE             DEFAULT (SYSDATE),
    CONSTRAINT gazqry_pk_gazid PRIMARY KEY (gazid)
)
;



-- 
-- TABLE: gazetteertown 
--

CREATE TABLE gazetteertown(
    gazid     NUMBER(16, 0)    NOT NULL,
    pop       NUMBER(10, 0),
    remark    VARCHAR2(80),
    lddate    DATE             DEFAULT (SYSDATE),
    CONSTRAINT gaztown_pk_gazid PRIMARY KEY (gazid)
)
;



-- 
-- TABLE: gazetteertype 
--

CREATE TABLE gazetteertype(
    code      NUMBER(3, 0)    NOT NULL,
    name      VARCHAR2(32)    NOT NULL,
    lddate    DATE            DEFAULT (SYSDATE),
    CONSTRAINT gaztype_pk_code PRIMARY KEY (code)
)
;



-- 
-- TABLE: jasieventlock 
--

CREATE TABLE jasieventlock(
    evid           NUMBER          NOT NULL,
    hostname       VARCHAR2(40),
    application    VARCHAR2(20),
    username       VARCHAR2(20),
    lddate         DATE,
    CONSTRAINT PK3 PRIMARY KEY (evid)
)
;



-- 
-- TABLE: leap_seconds 
--

CREATE TABLE leap_seconds(
    s_nominal    NUMBER(38, 0),
    e_nominal    NUMBER(38, 0),
    s_true       NUMBER(38, 0),
    e_true       NUMBER(38, 0),
    ls_count     NUMBER(38, 0)
)
;



-- 
-- TABLE: magprefpriority 
--

CREATE TABLE magprefpriority(
    region_name       VARCHAR2(24)    NOT NULL,
    datetime_on       NUMBER          NOT NULL,
    datetime_off      NUMBER          NOT NULL,
    priority          NUMBER          NOT NULL,
    magtype           VARCHAR2(6),
    auth              VARCHAR2(15),
    subsource         VARCHAR2(8),
    magalgo           VARCHAR2(15),
    minmag            NUMBER,
    maxmag            NUMBER,
    minreadings       NUMBER,
    maxuncertainty    NUMBER,
    quality           NUMBER,
    lddate            DATE            DEFAULT (SYS_EXTRACT_UTC(SYSTIMESTAMP)),
    CONSTRAINT magpref_pk PRIMARY KEY (region_name, datetime_on, priority)
)
;



-- 
-- TABLE: mcaparms 
--

CREATE TABLE mcaparms(
    net           VARCHAR2(8)      NOT NULL,
    sta           VARCHAR2(6)      NOT NULL,
    seedchan      VARCHAR2(3)      NOT NULL,
    channel       VARCHAR2(8),
    channelsrc    VARCHAR2(8),
    location      VARCHAR2(2)      DEFAULT '00' NOT NULL,
    neq           NUMBER(6, 0)     NOT NULL,
    afix          NUMBER(7, 4)     NOT NULL,
    qfix          NUMBER(7, 4)     NOT NULL,
    afree         NUMBER(7, 4)     NOT NULL,
    qfree         NUMBER(7, 4)     NOT NULL,
    slope         NUMBER(7, 4)     NOT NULL,
    clip          NUMBER(10, 0),
    cutoff        NUMBER(6, 0),
    ondate        DATE             NOT NULL,
    offdate       DATE,
    lddate        DATE             DEFAULT SYSDATE,
    CONSTRAINT mcaparms_key01 PRIMARY KEY (net, sta, seedchan, location, ondate)
)
;



-- 
-- TABLE: messagetext 
--

CREATE TABLE messagetext(
    srcnet      VARCHAR2(2)      NOT NULL,
    texttype    VARCHAR2(40)     NOT NULL,
    lineno      NUMBER(8, 0)     NOT NULL
                CHECK (LineNo > 0),
    text        VARCHAR2(132)    NOT NULL,
    CONSTRAINT pk11 PRIMARY KEY (srcnet, texttype, lineno)
)
;



-- 
-- TABLE: nettrig 
--

CREATE TABLE nettrig(
    ntid           NUMBER(15, 0)     NOT NULL,
    auth           VARCHAR2(15)      NOT NULL,
    subsource      VARCHAR2(8),
    datetime       NUMBER(25, 10)    NOT NULL,
    algorithm      VARCHAR2(15),
    loctrigid      VARCHAR2(12),
    ntrig          NUMBER(5, 0)      NOT NULL,
    allchan        NUMBER(1, 0)      NOT NULL,
    savestart      NUMBER(25, 10),
    saveend        NUMBER(25, 10),
    rflag          VARCHAR2(2),
    lddate         DATE              DEFAULT (SYSDATE),
    subnet_code    VARCHAR2(20),
    CONSTRAINT nettrigkey01 PRIMARY KEY (ntid)
)
;



-- 
-- TABLE: orgprefpriority 
--

CREATE TABLE orgprefpriority(
    region_name     VARCHAR2(24)      NOT NULL,
    datetime_on     NUMBER(25, 10)    NOT NULL,
    datetime_off    NUMBER(25, 10)    NOT NULL,
    priority        NUMBER(5, 0)      NOT NULL,
    type            VARCHAR2(2),
    auth            VARCHAR2(15),
    subsource       VARCHAR2(8),
    algorithm       VARCHAR2(15),
    min_obs         NUMBER(3, 0),
    max_gap         NUMBER(4, 1),
    max_erhor       NUMBER(7, 3),
    max_dist        NUMBER(7, 3),
    max_depth       NUMBER(7, 3),
    max_rms         NUMBER(5, 3),
    min_qual        NUMBER(3, 2),
    lddate          DATE              DEFAULT (SYS_EXTRACT_UTC(SYSTIMESTAMP)),
    CONSTRAINT originpref_pk PRIMARY KEY (region_name, datetime_on, priority)
)
;



-- 
-- TABLE: pcs_signal 
--

CREATE TABLE pcs_signal(
    sigid         NUMBER(15, 0)    NOT NULL,
    pcs_group     VARCHAR2(20)     NOT NULL,
    pcs_thread    VARCHAR2(20)     NOT NULL,
    pcs_state     VARCHAR2(20)     NOT NULL,
    algorithm     VARCHAR2(16)     NOT NULL,
    dbname        VARCHAR2(9)      NOT NULL,
    dbuser        VARCHAR2(30)     NOT NULL,
    userhost      VARCHAR2(16)     NOT NULL,
    username      VARCHAR2(20)     NOT NULL,
    startdate     DATE             NOT NULL,
    stopdate      DATE,
    message       VARCHAR2(132),
    CONSTRAINT pk_sig PRIMARY KEY (sigid)
)
;



-- 
-- TABLE: pcs_state 
--

CREATE TABLE pcs_state(
    controlgroup            VARCHAR2(20)    NOT NULL,
    sourcetable             VARCHAR2(20)    NOT NULL,
    id                      NUMBER          NOT NULL,
    state                   VARCHAR2(20)    NOT NULL,
    rank                    NUMBER          NOT NULL,
    result                  NUMBER,
    secondaryid             NUMBER,
    secondarysourcetable    VARCHAR2(20),
    auth                    VARCHAR2(15),
    subsource               VARCHAR2(8),
    lddate                  DATE,
    CONSTRAINT pk14 PRIMARY KEY (controlgroup, sourcetable, id, state)
)
;



-- 
-- TABLE: pcs_transition 
--

CREATE TABLE pcs_transition(
    groupold     VARCHAR2(20)    NOT NULL,
    sourceold    VARCHAR2(20)    NOT NULL,
    stateold     VARCHAR2(20)    NOT NULL,
    resultold    NUMBER          NOT NULL,
    groupnew     VARCHAR2(20)    DEFAULT 'null' NOT NULL,
    sourcenew    VARCHAR2(20)    DEFAULT 'null' NOT NULL,
    statenew     VARCHAR2(20)    DEFAULT 'null' NOT NULL,
    ranknew      NUMBER,
    resultnew    NUMBER,
    auth         VARCHAR2(15),
    subsource    VARCHAR2(8),
    lddate       DATE            DEFAULT SYSDATE,
    CONSTRAINT pcs_state_pk PRIMARY KEY (groupold, sourceold, stateold, resultold, groupnew, sourcenew, statenew)
)
;



-- 
-- TABLE: peer_system_status 
--

CREATE TABLE peer_system_status(
    status      VARCHAR2(15),
    mod_time    DATE
)
;



-- 
-- TABLE: request_card 
--

CREATE TABLE request_card(
    evid            NUMBER(15, 0),
    auth            VARCHAR2(15)      NOT NULL,
    subsource       VARCHAR2(8)       NOT NULL,
    net             VARCHAR2(8)       NOT NULL,
    sta             VARCHAR2(8)       NOT NULL,
    seedchan        VARCHAR2(8)       NOT NULL,
    staauth         VARCHAR2(15)      NOT NULL,
    channel         VARCHAR2(8)       NOT NULL,
    datetime_on     NUMBER(25, 10)    NOT NULL,
    datetime_off    NUMBER(25, 10)    NOT NULL,
    request_type    VARCHAR2(1)       NOT NULL,
    lddate          DATE              DEFAULT (SYSDATE),
    rcid            NUMBER(15, 0)     NOT NULL,
    location        VARCHAR2(2)       NOT NULL,
    retry           NUMBER(38, 0),
    lastretry       DATE,
    priority        NUMBER,
    CONSTRAINT request_cardkey01 PRIMARY KEY (rcid)
)
;



-- 
-- TABLE: rt_role 
--

CREATE TABLE rt_role(
    primary_system       VARCHAR2(5),
    modification_time    DATE           DEFAULT (SYSDATE)
)
;



-- 
-- TABLE: stacorrections 
--

CREATE TABLE stacorrections(
    net           VARCHAR2(8)     NOT NULL,
    sta           VARCHAR2(6)     NOT NULL,
    seedchan      VARCHAR2(3)     NOT NULL,
    channel       VARCHAR2(8),
    channelsrc    VARCHAR2(8),
    location      VARCHAR2(2)     NOT NULL,
    auth          VARCHAR2(15),
    corr          FLOAT(126)      NOT NULL,
    corr_flag     VARCHAR2(1),
    corr_type     VARCHAR2(3)     NOT NULL,
    ondate        DATE            NOT NULL,
    offdate       DATE            NOT NULL,
    lddate        DATE            DEFAULT (SYSDATE),
    CONSTRAINT stacoo PRIMARY KEY (net, sta, seedchan, location, corr_type, ondate)
)
;



-- 
-- TABLE: stamapping 
--

CREATE TABLE stamapping(
    net          VARCHAR2(8)     NOT NULL,
    sta          VARCHAR2(6)     NOT NULL,
    stanumber    NUMBER(8, 0)    NOT NULL,
    lddate       DATE,
    locdescr     VARCHAR2(40),
    CONSTRAINT stamapkey01 PRIMARY KEY (net, sta)
)
;



-- 
-- TABLE: subnet 
--

CREATE TABLE subnet(
    subid          NUMBER(15, 0)    NOT NULL,
    code           VARCHAR2(3)      NOT NULL,
    description    VARCHAR2(80),
    mintrig        NUMBER(15, 0)    NOT NULL,
    lddate         DATE
)
;



-- 
-- TABLE: subnet_channel 
--

CREATE TABLE subnet_channel(
    subid       NUMBER(15, 0)    NOT NULL,
    sta         VARCHAR2(6)      NOT NULL,
    net         VARCHAR2(8)      NOT NULL,
    seedchan    VARCHAR2(3)      NOT NULL,
    location    VARCHAR2(2),
    voter       VARCHAR2(1)      NOT NULL,
    lddate      DATE
)
;



-- 
-- TABLE: swarm_events 
--

CREATE TABLE swarm_events(
    evid        NUMBER(15, 0)     NOT NULL,
    region      VARCHAR2(24)      NOT NULL,
    datetime    NUMBER(25, 10)    NOT NULL,
    CONSTRAINT swarm_eventskey01 PRIMARY KEY (evid, region)
)
;



-- 
-- TABLE: swarm_state 
--

CREATE TABLE swarm_state(
    region            VARCHAR2(24)     NOT NULL,
    in_progress       VARCHAR2(1)      NOT NULL,
    last_notify       NUMBER(15, 0),
    last_rerate       NUMBER(15, 0),
    rate_threshold    NUMBER(7, 3)     NOT NULL,
    CONSTRAINT swarm_statekey01 PRIMARY KEY (region)
)
;



-- 
-- TABLE: system_status 
--

CREATE TABLE system_status(
    status      VARCHAR2(15),
    mod_time    DATE
)
;



-- 
-- TABLE: trig_channel 
--

CREATE TABLE trig_channel(
    trigid        NUMBER(15, 0)     NOT NULL,
    ntid          NUMBER(15, 0)     NOT NULL,
    auth          VARCHAR2(15)      NOT NULL,
    subsource     VARCHAR2(8),
    sta           VARCHAR2(6)       NOT NULL,
    net           VARCHAR2(8),
    channel       VARCHAR2(8),
    channelsrc    VARCHAR2(8),
    seedchan      VARCHAR2(3),
    location      VARCHAR2(2),
    trigflag      VARCHAR2(1),
    datetime      NUMBER(25, 10)    NOT NULL,
    savestart     NUMBER(25, 10)    NOT NULL,
    saveend       NUMBER(25, 10)    NOT NULL,
    rflag         VARCHAR2(2),
    lddate        DATE              DEFAULT (SYSDATE),
    CONSTRAINT trigchankey01 PRIMARY KEY (trigid)
)
;



-- 
-- TABLE: unassocamp 
--

CREATE TABLE unassocamp(
    ampid         NUMBER(15, 0)       NOT NULL,
    commid        NUMBER(15, 0),
    datetime      NUMBER(25, 10)      NOT NULL,
    sta           VARCHAR2(6)         NOT NULL,
    net           VARCHAR2(8),
    auth          VARCHAR2(15)        NOT NULL,
    subsource     VARCHAR2(8),
    channel       VARCHAR2(8),
    channelsrc    VARCHAR2(8),
    seedchan      VARCHAR2(3),
    location      VARCHAR2(2),
    iphase        VARCHAR2(8),
    amplitude     DOUBLE PRECISION    NOT NULL,
    amptype       VARCHAR2(8),
    units         VARCHAR2(4)         NOT NULL,
    ampmeas       VARCHAR2(1),
    eramp         NUMBER(5, 3),
    flagamp       VARCHAR2(4),
    per           NUMBER(10, 4),
    snr           DOUBLE PRECISION,
    tau           NUMBER(9, 4),
    quality       NUMBER(2, 1),
    rflag         VARCHAR2(2),
    cflag         VARCHAR2(2),
    wstart        DOUBLE PRECISION    NOT NULL,
    duration      DOUBLE PRECISION    NOT NULL,
    lddate        DATE                DEFAULT (SYSDATE),
    fileid        NUMBER(15, 0),
    CONSTRAINT unassocampkey01 PRIMARY KEY (ampid)
)
;



-- 
-- INDEX: req03 
--

DROP INDEX req03
;

CREATE UNIQUE INDEX req03 ON request_card(auth, subsource, net, sta, seedchan, location, staauth, datetime_on, datetime_off, request_type)
;
-- 
-- TABLE: appchannels 
--

ALTER TABLE appchannels ADD CONSTRAINT Refapplications12 
    FOREIGN KEY (progid)
    REFERENCES applications(progid)
;


-- 
-- TABLE: assoc_region_group 
--

ALTER TABLE assoc_region_group ADD CONSTRAINT fk_reg_grpid 
    FOREIGN KEY (grpid)
    REFERENCES gazetteer_region_group(grpid)
;


-- 
-- TABLE: gazetteerbigtown 
--

ALTER TABLE gazetteerbigtown ADD CONSTRAINT gazbigtown_fk_gazid 
    FOREIGN KEY (gazid)
    REFERENCES gazetteerpt(gazid)
;


-- 
-- TABLE: gazetteerfault 
--

ALTER TABLE gazetteerfault ADD CONSTRAINT gazfault_fk_gazid 
    FOREIGN KEY (gazid)
    REFERENCES gazetteerline(gazid)
;


-- 
-- TABLE: gazetteerline 
--

ALTER TABLE gazetteerline ADD CONSTRAINT gazline_fk_type 
    FOREIGN KEY (type)
    REFERENCES gazetteertype(code)
;


-- 
-- TABLE: gazetteerpt 
--

ALTER TABLE gazetteerpt ADD CONSTRAINT gazpt_fk_type 
    FOREIGN KEY (type)
    REFERENCES gazetteertype(code)
;


-- 
-- TABLE: gazetteerquake 
--

ALTER TABLE gazetteerquake ADD CONSTRAINT gazeq_fk_gazid 
    FOREIGN KEY (gazid)
    REFERENCES gazetteerpt(gazid)
;


-- 
-- TABLE: gazetteerquarry 
--

ALTER TABLE gazetteerquarry ADD CONSTRAINT gazqry_fk_gazid 
    FOREIGN KEY (gazid)
    REFERENCES gazetteerpt(gazid)
;


-- 
-- TABLE: gazetteertown 
--

ALTER TABLE gazetteertown ADD CONSTRAINT gaztown_fk_gazid 
    FOREIGN KEY (gazid)
    REFERENCES gazetteerpt(gazid)
;


