diff options
| -rw-r--r-- | sql/dawn.sql | 60 |
1 files changed, 50 insertions, 10 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index d17e6627..d942bfbe 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1,37 +1,77 @@ -- -*- mode: sql; sql-product: postgres; -*- -CREATE TYPE bonds_strat AS ENUM('M_STR_MAV', 'M_STR_SMEZZ', 'CSO_TRANCH', +CREATE TYPE bond_strat AS ENUM('M_STR_MAV', 'M_STR_SMEZZ', 'CSO_TRANCH', 'M_CLO_BB20', 'M_CLO_AAA', 'M_CLO_BBB', 'M_MTG_IO', 'M_MTG_THRU', 'M_MTG_GOOD', 'M_MTG_B4PR', 'M_MTG_RW'); CREATE TYPE asset_class AS ENUM('CSO', 'Subprime', 'CLO', 'Tranches', 'Futures', 'Cash', 'FX', 'Cleared'); -CREATE TABLE counterparties(code text primary key, +CREATE TYPE action AS ENUM('NEW', 'UPDATE', 'CANCEL'); +CREATE TYPE currency AS ENUM('USD', 'CAD', 'EUR', 'YEN'); +CREATE TYPE bbg_type AS ENUM('Mtge', 'Corp'); + +CREATE TABLE counterparties(code varchar(12) primary key, name text, + city text, + state varchar(2), location text, dtc_number integer, sales_contact text, - email1 text, - phone text, + sales_email text, + sales_phone text, valuation_contact1 text, valuation_email1 text, valuation_contact2 text, valuation_email2 text, valuation_contact3 text, valuation_email3 text, + valuation_contact3 text, + valuation_email3 text, + valuation_contact4 = text, + valuation_email4 = text, notes text); CREATE INDEX ON counterparties(name); CREATE TABLE bonds(id serial primary key, + deal_id varchar(28), + lastupdate timestamp, + action action, + folder bond_strat, + custodian varchar(12), + cashaccount varchar(10), + cp_code varchar(12) references counterparties(code), trade_date date, settle_date date, cusip varchar(9), isin varchar(12), - description text, + description varchar(32), + faceamount float, + price float, + accrued float, + asset_class asset_class, + ticket text); + + +CREATE TABLE position(date date, + isin varchar(12), + cusip varchar(9), + identifier varchar(12), + description varchar(32), notional float, + face_amount float, + coupon float, + currency currency, + factor float, price float, - counterparty text references counterparties(code), - strategy bonds_start, - account text - acc_int float, - asset_class ); + market_value_local float, + market_value_usd float, + accrued float, + days_accrued float, + start_accrued_date date, + factor_pay_date date, + paydown float, + writedown float, + bbg_type bbg_type, + strategy bond_strat, + asset_class asset_class, + PRIMARY KEY(identifier, date)); |