diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 13 |
1 files changed, 7 insertions, 6 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 03e0bf6c..76175b18 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -606,10 +606,11 @@ CREATE TABLE securities(identifier varchar(12) PRIMARY KEY, ALTER TABLE securities OWNER TO dawn_user; CREATE TABLE marks(date date, - identifier varchar(12) REFERENCES securities(identifier) ON DELETE CASCADE ON UPDATE CASCADE, + identifier varchar(12) REFERENCES securities(figi) ON DELETE CASCADE ON UPDATE CASCADE, price float, PRIMARY KEY(identifier, date)); + CREATE TABLE external_marks_deriv(date date NOT NULL, identifier text NOT NULL, local_nav float, --trade ccy nav @@ -625,7 +626,7 @@ CREATE OR REPLACE VIEW id_mappings AS 'TRANCHE' FROM cds WHERE attach IS NOT NULL ORDER BY trade_date; CREATE TABLE cashflow_history( - identifier varchar(12) REFERENCES securities ON UPDATE CASCADE, + identifier varchar(12) REFERENCES securities(figi) ON UPDATE CASCADE, date date, principal_bal float, principal float, @@ -895,7 +896,7 @@ BEGIN principal_payment, accrued_payment, sum(faceamount*(2*buysell::int-1) ) OVER (PARTITION BY bond_trades.identifier) notional FROM bond_trades WHERE trade_date<=$1 AND fund=$3) - SELECT DISTINCT ON (temp.identifier) temp.identifier, securities.description, notional, folder, + SELECT DISTINCT ON (temp.identifier) securities.figi, securities.description, notional, folder, securities.coupon, start_accrued_date, settle_date, temp.principal_payment, temp.accrued_payment, securities.currency, securities.day_count, securities.bbg_type FROM temp LEFT JOIN securities USING (identifier) @@ -1592,7 +1593,7 @@ CREATE MATERIALIZED VIEW factors_history AS temp.losses / securities.face_amount * 100::float AS losses, COALESCE(temp.coupon, securities.coupon) AS coupon FROM temp - JOIN securities USING (identifier); + JOIN securities ON temp.identifier=securities.figi; CREATE UNIQUE INDEX factors_history_pkey ON factors_history(prev_cpn_date, identifier); @@ -1949,7 +1950,7 @@ CREATE TABLE swaption_marks( CREATE TABLE subprime_risk( date date, - cusip varchar(9), + figi varchar(12) NOT NULL, pv1 float, pv2 float, pv3 float, @@ -1977,7 +1978,7 @@ CREATE TABLE subprime_risk( bond_yield float, hy_equiv float, delta_ir float, - PRIMARY KEY (date, cusip)) + PRIMARY KEY (date, figi)) CREATE TABLE beta( date date, |