diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 53 |
1 files changed, 48 insertions, 5 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 878947bf..e16e101b 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -6,7 +6,12 @@ CREATE TYPE bond_strat AS ENUM('M_STR_MAV', 'M_STR_SMEZZ', 'CSO_TRANCH', CREATE TYPE cds_strat AS ENUM('HEDGE_CSO', 'HEDGE_CLO', 'HEDGE_MAC', 'HEDGE_MBS', 'SER_IGSNR', 'SER_IGMEZ', 'SER_IGEQY', 'SER_IGINX', 'SER_HYSNR', - 'SER_HYMEZ', 'SER_HYEQY', 'SER_HYINX', 'SER_IGCURVE', 'MBSCDS'); + 'SER_HYMEZ', 'SER_HYEQY', 'SER_HYINX', 'SER_IGCURVE', 'MBSCDS', + 'IGOPTDEL', 'HYOPTDEL'); + +CREATE TYPE swaption_strat AS ENUM('IGPAYER', 'IGREC', 'HYPAYER', 'HYREC'); + +CREATE TYPE repo_strat AS ENUM(''); CREATE TYPE asset_class AS ENUM('CSO', 'Subprime', 'CLO', 'Tranches', 'Futures', 'Cash', 'FX', 'Cleared'); @@ -18,10 +23,14 @@ CREATE type bus_day_convention AS ENUM('Modified Following', 'Following', 'Modif 'Second-Day-After', 'End-of-Month'); CREATE type index_type AS ENUM('IG', 'HY', 'EU', 'LCDX', 'XO'); CREATE TYPE tenor AS ENUM('6mo', '1yr', '2yr', '3yr', '4yr', '5yr', '7yr', '10yr'); + CREATE TYPE swap_type AS ENUM('CD_INDEX', 'CD_INDEX_TRANCHE', 'CD_BASKET_TRANCHE', 'ABS_CDS'); -CREATE type isda AS ENUM('ISDA2014', 'ISDA2003Cred'); -CREATE type protection AS ENUM('Buyer', 'Seller'); CREATE TYPE repo_type AS ENUM('REPO', 'REVERSE REPO'); +CREATE TYPE swaption_type AS ENUM('PAYER', 'RECEIVER'); + +CREATE TYPE isda AS ENUM('ISDA2014', 'ISDA2003Cred'); +CREATE TYPE protection AS ENUM('Buyer', 'Seller'); + CREATE TYPE call_notice AS ENUM('24H', '48H', '3D', '4D', '5D', '6D', '1W', '8D', '9D', '10D', '2W', '1M', '2M'); @@ -61,6 +70,7 @@ CREATE TABLE bonds(id serial primary key, isin varchar(12), identifier varchar(12), description varchar(32) NOT NULL, + buysell bool NOT NULL, faceamount float NOT NULL, price float NOT NULL, accrued float NOT NULL, @@ -135,7 +145,7 @@ CREATE TABLE repo(id serial primary key, folder bond_strat NOT NULL, custodian varchar(12) NOT NULL, cashaccount varchar(10) NOT NULL, - cp_code varchar(12) NOT NULL REFERENCES counterparties(code), + cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, trade_date date NOT NULL, settle_date date NOT NULL, cusip varchar(9), @@ -161,7 +171,7 @@ CREATE TABLE repo(id serial primary key, CREATE OR REPLACE FUNCTION auto_repo_dealid() RETURNS TRIGGER AS $$ BEGIN - UPDATE cds SET dealid = 'SC_REP' ||id WHERE cds.id = NEW.id AND dealid is Null; + UPDATE repo SET dealid = 'SC_REP' ||id WHERE repo.id = NEW.id AND dealid is Null; RETURN NEW; END; $$ language plpgsql; @@ -171,6 +181,39 @@ FOR EACH ROW EXECUTE PROCEDURE auto_repo_dealid(); ALTER TABLE repo OWNER TO dawn_user; +CREATE TABLE swaptions(id serial PRIMARY KEY, + dealid varchar(28), + lastupdate timestamp DEFAULT now(), + action action, + folder swaption_strat NOT NULL, + custodian varchar(12) NOT NULL, + cashaccount varchar(10) NOT NULL, + cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, + trade_date date NOT NULL, + settle_date date NOT NULL, + buysell bool NOT NULL, + notional float NOT NULL, + swaption_type swaption_type NOT NULL, + price float NOT NULL, + expiration_date date NOT NULL, + security_id varchar(12) NOT NULL, + security_desc varchar(32) NOT NULL, + maturity date NOT NULL, + fixed_rate float NOT NULL); + +CREATE OR REPLACE FUNCTION auto_swaption_dealid() + RETURNS TRIGGER AS $$ + BEGIN + UPDATE swaptions SET dealid = 'SWPTN' ||id WHERE swaptions.id = NEW.id AND dealid is Null; + RETURN NEW; + END; + $$ language plpgsql; + +CREATE TRIGGER swaptions_dealid AFTER INSERT ON swaptions +FOR EACH ROW EXECUTE PROCEDURE auto_swaption_dealid(); + +ALTER TABLE swaptions OWNER TO dawn_user; + CREATE TABLE securities(identifier varchar(12) PRIMARY KEY, cusip varchar(9), isin varchar(12), |