diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 128 |
1 files changed, 75 insertions, 53 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 3d29bdab..1b612c44 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -182,22 +182,14 @@ folder strategy PRIMARY KEY, portfolio portfolio NOT NULL valid boolean NOT NULL); -CREATE OR REPLACE FUNCTION update_portf() -RETURNS TRIGGER -AS $$ -BEGIN -NEW.portfolio = ( -CASE WHEN NEW.folder::text LIKE '%MTG%' THEN 'MORTGAGES' -WHEN NEW.folder::TEXT LIKE '%CLO%' THEN 'CLO' -WHEN NEW.folder::TEXT LIKE '%CRT%' THEN 'MORTGAGES' -WHEN NEW.folder::TEXT LIKE '%STR%' THEN 'STRUCTURED' -WHEN NEW.folder::TEXT='M_CSH_CASH' THEN 'CASH' -WHEN NEW.folder::TEXT='TCSH' THEN 'TRANCHE' -WHEN NEW.folder::TEXT='SER_ITRXCURVE' THEN 'CURVE' -END)::portfolio; -RETURN NEW; -END -$$ LANGUAGE plpgsql; +CREATE OR REPLACE TRIGGER portf BEFORE +INSERT OR UPDATE OF + folder +ON + bonds +FOR EACH ROW +EXECUTE PROCEDURE update_portf2(); + CREATE OR REPLACE FUNCTION update_portf2() RETURNS TRIGGER @@ -208,31 +200,8 @@ RETURN NEW; END $$ LANGUAGE plpgsql; -CREATE TRIGGER portf2 BEFORE -INSERT OR UPDATE OF - folder -ON - wires -FOR EACH ROW -EXECUTE PROCEDURE update_portf2(); - -CREATE TRIGGER portf BEFORE -INSERT OR UPDATE OF - folder -ON - bonds -FOR EACH ROW -EXECUTE PROCEDURE update_portf(); - -CREATE TRIGGER fx_portf BEFORE -INSERT OR UPDATE OF - folder -ON - spots -FOR EACH ROW -EXECUTE PROCEDURE update_portf(); -CREATE TRIGGER bond_notify BEFORE +CREATE OR REPLACE TRIGGER bond_notify BEFORE INSERT OR UPDATE OF identifier, cusip, @@ -241,7 +210,8 @@ INSERT OR UPDATE OF price, trade_date, settle_date, - accrued + accrued, + asset_class ON bonds FOR EACH ROW @@ -419,8 +389,7 @@ CREATE TABLE swaptions(id serial PRIMARY KEY, action action NOT NULL, portfolio portfolio NOT NULL, folder swaption_strat NOT NULL, - custodian varchar(12) NOT NULL, - cashaccount varchar(12) NOT NULL, + cash_account text NOT NULL REFERENCES accounts(cash_account), cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, swap_type swaption_type NOT NULL, trade_date date NOT NULL, @@ -450,6 +419,26 @@ CREATE TABLE swaptions(id serial PRIMARY KEY, CREATE TRIGGER swaptions_dealid AFTER INSERT ON swaptions FOR EACH ROW EXECUTE PROCEDURE auto_dealid(); +CREATE OR REPLACE FUNCTION update_account() +RETURNS TRIGGER +AS $$ +BEGIN +SELECT cash_account INTO STRICT NEW.cash_account FROM accounts2 LEFT JOIN account_counterparty ON accounts2.id=account_id WHERE trade_type='ISDA' AND fund=NEW.fund AND account_counterparty.cp_code=NEW.cp_code; +RETURN NEW; +END +$$ LANGUAGE plpgsql; + +CREATE TRIGGER cash_account BEFORE +INSERT OR UPDATE OF + fund, + cp_code +ON + swaptions +FOR EACH ROW +EXECUTE PROCEDURE update_account(); + + + CREATE TABLE capfloors ( id serial PRIMARY KEY, fund fund NOT NULL DEFAULT 'SERCGMAST', @@ -494,8 +483,9 @@ CREATE TABLE futures ( dealid varchar(28), lastupdate timestamp NULL DEFAULT now(), "action" action NOT NULL, + portfolio portfolio NOT NULL, folder future_strat NOT NULL, - account_code varchar(12) NOT NULL REFERENCES accounts(code), + cash_account text NOT NULL REFERENCES accounts2(cash_account), trade_date date NOT NULL, settle_date date NOT NULL, buysell bool NOT NULL, @@ -513,8 +503,19 @@ CREATE TABLE futures ( CONSTRAINT futures_pkey PRIMARY KEY (id), ); -ALTER table futures OWNER TO dawn_user; -CREATE trigger dealid after insert on futures for each row execute procedure auto_dealid(); +ALTER TABLE futures OWNER TO dawn_user; +CREATE TRIGGER dealid AFTER +INSERT ON futures +FOR EACH ROW +EXECUTE PROCEDURE auto_dealid(); + +CREATE TRIGGER portf BEFORE +INSERT OR UPDATE OF + folder +ON + futures +FOR EACH ROW +EXECUTE PROCEDURE update_portf2(); CREATE TABLE wires ( id serial NOT NULL, @@ -523,16 +524,29 @@ CREATE TABLE wires ( lastupdate timestamp NULL DEFAULT now(), "action" action NOT NULL, folder cash_strat NOT NULL, - portfolio portfolio, - code varchar(5) NOT NULL, + portfolio portfolio NOT NULL, + cash_account text NOT NULL REFERENCES accounts2(cash_account), 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 TRIGGER dealid AFTER +INSERT ON wires +FOR EACH ROW +EXECUTE PROCEDURE auto_dealid(); + +CREATE TRIGGER portf2 BEFORE +INSERT OR UPDATE OF + folder +ON + wires +FOR EACH ROW +EXECUTE PROCEDURE update_portf2(); + CREATE TABLE spots ( @@ -543,8 +557,8 @@ CREATE TABLE spots ( "action" action NOT NULL, portfolio portfolio NOT NULL, folder spot_strat NOT NULL, - account_code varchar(12) NOT NULL REFERENCES accounts(cash_account), - cp_code + cash_account text NOT NULL REFERENCES accounts2(cash_account), + cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, trade_date date NOT NULL, settle_date date NOT NULL, spot_rate float8 NOT NULL, @@ -557,10 +571,18 @@ CREATE TABLE spots ( initial_margin_percentage float8, cpty_id text, globeop_id text, - CONSTRAINT spots_pkey PRIMARY KEY (id), ); +CREATE TRIGGER fx_portf BEFORE +INSERT OR UPDATE OF + folder +ON + wires +FOR EACH ROW +EXECUTE PROCEDURE update_portf2(); + + CREATE TABLE fx_swaps ( id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, dealid varchar(28) NOT NULL GENERATED ALWAYS AS ((('FXSWAP_'::text || id::text))) STORED, |