diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 101 |
1 files changed, 93 insertions, 8 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index a8a28141..f8d20507 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -80,9 +80,10 @@ CREATE TABLE counterparties(code varchar(12) PRIMARY KEY, CREATE INDEX ON counterparties(name); CREATE TABLE bonds(id serial primary key, - dealid varchar(28), + dealid varchar(28) UNIQUE, + fund fund NOT NULL DEFAULT 'SERCGMAST'; lastupdate timestamp DEFAULT now(), - action action, + action action NOT NULL, folder bond_strat NOT NULL, custodian varchar(12) NOT NULL, cashaccount varchar(10) NOT NULL, @@ -110,7 +111,8 @@ CREATE TRIGGER dealid CREATE TABLE cds(id serial primary key, - dealid varchar(28), + dealid varchar(28) UNIQUE, + fund fund NOT NULL DEFAULT 'SERCGMAST', lastupdate timestamptz DEFAULT now(), action action NOT NULL, portfolio portfolio NOT NULL, @@ -160,9 +162,10 @@ CREATE TRIGGER cds_dealid AFTER INSERT ON cds FOR EACH ROW EXECUTE PROCEDURE auto_dealid(); CREATE TABLE repo(id serial primary key, - dealid varchar(28), + dealid varchar(28) UNIQUE, + fund fund NOT NULL DEFAULT 'SERCGMAST', lastupdate timestamptz DEFAULT now(), - action action, + action action NOT NULL, folder bond_strat NOT NULL, custodian varchar(12) NOT NULL, cashaccount varchar(10) NOT NULL, @@ -196,8 +199,9 @@ ALTER TABLE repo OWNER TO dawn_user; CREATE TABLE swaptions(id serial PRIMARY KEY, dealid varchar(28) UNIQUE, + fund fund NOT NULL DEFAULT 'SERCGMAST', lastupdate timestamptz DEFAULT now(), - action action, + action action NOT NULL, portfolio portfolio NOT NULL, folder swaption_strat NOT NULL, custodian varchar(12) NOT NULL, @@ -223,11 +227,15 @@ CREATE TABLE swaptions(id serial PRIMARY KEY, termination_amount float, termination_cp varchar(12) REFERENCES counterparties(code) ON UPDATE CASCADE); +CREATE TRIGGER swaptions_dealid AFTER INSERT ON swaptions +FOR EACH ROW EXECUTE PROCEDURE auto_dealid(); + CREATE TABLE capfloors ( id serial PRIMARY KEY, - dealid varchar(28), + fund fund NOT NULL DEFAULT 'SERCGMAST', + dealid varchar(28) UNIQUE, lastupdate timestamp NULL DEFAULT now(), - "action" action, + "action" action NOT NULL, folder swaption_strat NOT NULL, custodian varchar(12) NOT NULL, cashaccount varchar(10) NOT NULL, @@ -260,6 +268,81 @@ CREATE TABLE capfloors ( termination_cp varchar(12) REFERENCES counterparties(code) ON UPDATE CASCADE, cpty_id text, ) +CREATE TRIGGER dealid AFTER INSERT ON capfloors +FOR EACH ROW EXECUTE PROCEDURE auto_dealid(); + +CREATE TABLE futures ( + id serial NOT NULL, + dealid varchar(28), + lastupdate timestamp NULL DEFAULT now(), + "action" action NOT NULL, + folder future_strat NOT NULL, + custodian varchar(12) NOT NULL, + cashaccount varchar(10) NOT NULL, + cp_code varchar(12) NOT NULL, + trade_date date NOT NULL, + settle_date date NOT NULL, + buysell bool NOT NULL, + bbg_ticker varchar(32) NOT NULL, + quantity float8 NOT NULL, + price float8 NOT NULL, + commission float8 NULL, + swap_type future_type NOT NULL, + security_desc varchar(32) NOT NULL, + maturity date NOT NULL, + currency currency NOT NULL, + exchange varchar(3) NOT NULL, + fund fund NOT NULL DEFAULT 'SERCGMAST'::fund, + CONSTRAINT futures_dealid_key UNIQUE (dealid), + CONSTRAINT futures_pkey PRIMARY KEY (id), + CONSTRAINT futures_cp_code_fkey FOREIGN KEY (cp_code) REFERENCES counterparties(code) +); +ALTER table futures OWNER TO dawn_user; +CREATE trigger dealid after insert on futures for each row execute procedure auto_dealid(); + +CREATE TABLE wires ( + id serial NOT NULL, + dealid varchar(28) NULL, + lastupdate timestamp NULL DEFAULT now(), + "action" action NOT NULL, + folder cash_strat NOT NULL, + code varchar(5) NOT NULL, + amount float8 NOT NULL, + currency currency NOT NULL, + trade_date date NOT NULL, + CONSTRAINT wires_pkey PRIMARY KEY (id), + CONSTRAINT wires_code_fkey FOREIGN KEY (code) REFERENCES accounts(code) +); +ALTER table wires OWNER TO dawn_user; +CREATE trigger dealid after insert on wires for each row execute procedure auto_dealid(); + +CREATE TABLE spots ( + id serial NOT NULL, + fund fund NOT NULL DEFAULT 'SERCGMAST'::fund, + dealid varchar(28) NULL, + lastupdate timestamp NULL DEFAULT now(), + "action" action NOT NULL, + folder spot_strat NOT NULL, + custodian varchar(12) NOT NULL, + cashaccount varchar(10) NOT NULL, + cp_code varchar(12) NOT NULL, + trade_date date NOT NULL, + settle_date date NOT NULL, + spot_rate float8 NOT NULL, + buy_currency currency NOT NULL, + buy_amount float8 NOT NULL, + sell_currency currency NOT NULL, + sell_amount float8 NOT NULL, + commission_currency currency NULL, + commission float8 NULL, + CONSTRAINT spots_pkey PRIMARY KEY (id), + CONSTRAINT spots_cp_code_fkey FOREIGN KEY (cp_code) REFERENCES counterparties(code) +); + +create + trigger dealid after insert + on + spots for each row execute procedure auto_dealid();; CREATE OR REPLACE FUNCTION auto_dealid() RETURNS TRIGGER AS $$ @@ -286,6 +369,8 @@ identifier = COALESCE(identifier, cusip, isin)', NEW.id); stub := 'SCCSH'; WHEN 'capfloors' THEN stub := 'CAP'; + WHEN 'spots' THEN + stub := 'SCFX'; END CASE; sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id); END IF; |