diff options
| -rw-r--r-- | python/Dawn/models.py | 1 | ||||
| -rw-r--r-- | sql/dawn.sql | 65 |
2 files changed, 58 insertions, 8 deletions
diff --git a/python/Dawn/models.py b/python/Dawn/models.py index ef686dd3..7eb76888 100644 --- a/python/Dawn/models.py +++ b/python/Dawn/models.py @@ -348,6 +348,7 @@ class CapFloorDeal(db.Model): termination_cp = db.Column(db.String(12), db.ForeignKey('counterparties.code'), info={'choices': [(None, '')], 'label': 'termination counterparty'}) + cpty_id = db.Column(db.Text) counterparty = db.relationship(Counterparties, foreign_keys=[cp_code]) termination_counterparty = db.relationship(Counterparties, foreign_keys=[termination_cp]) diff --git a/sql/dawn.sql b/sql/dawn.sql index 185e028f..fa9d5f8b 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -214,6 +214,43 @@ CREATE TABLE swaptions(id serial PRIMARY KEY, termination_amount float, termination_cp varchar(12) REFERENCES counterparties(code) ON UPDATE CASCADE); +CREATE TABLE capfloors ( + id serial PRIMARY KEY, + dealid varchar(28), + lastupdate timestamp NULL 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, + "comments" varchar(100), + floating_rate_index varchar(12) NOT NULL, + floating_rate_index_desc varchar(32), + buysell bool NOT NULL, + cap_or_floor cap_or_floor NOT NULL, + strike float8 NOT NULL, + value_date date NOT NULL, + expiration_date date NOT NULL, + premium_percent float8 NOT NULL, + pricing_type pricing_type NOT NULL, + payment_frequency frequency NOT NULL, + fixing_frequency frequency NOT NULL, + day_count_counvention day_count NULL, + bdc_convention bus_day_convention NULL, + payment_mode payment_mode NOT NULL, + payment_at_beginning_or_end begin_or_end NOT NULL, + initial_margin_percentage float8 NULL, + initial_margin_currency currency NULL, + amount float8 NOT NULL, + trade_date date NOT NULL, + swap_type capfloor_type NOT NULL, + reset_lag int4, + trade_confirm varchar, + termination_date date, + termination_amount float8, + termination_cp varchar(12) REFERENCES counterparties(code) ON UPDATE CASCADE, + cpty_id text, +) CREATE OR REPLACE FUNCTION auto_dealid() RETURNS TRIGGER AS $$ @@ -226,14 +263,21 @@ CREATE OR REPLACE FUNCTION auto_dealid() stub := 'SC_'; sqlstr := format(sqlstr, 'dealid = $1||upper(left(asset_class::text,3))||id, identifier = COALESCE(identifier, cusip, isin)', NEW.id); - ELSIF (TG_TABLE_NAME = 'cds') THEN - stub := 'SCCDS'; - sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id); - ELSIF (TG_TABLE_NAME = 'repo') THEN - stub := 'SC_REP'; - sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id); - ELSIF (TG_TABLE_NAME = 'swaptions') THEN - stub := 'SWPTN'; + ELSE + CASE TG_TABLE_NAME + WHEN 'cds' THEN + stub := 'SCCDS'; + WHEN 'repo' THEN + stub := 'SC_REP'; + WHEN 'swaptions' THEN + stub := 'SWPTN'; + WHEN 'futures' THEN + stub := 'SCFUT'; + WHEN 'wires' THEN + stub := 'SCCSH'; + WHEN 'capfloors' THEN + stub := 'CAP'; + END CASE; sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id); END IF; EXECUTE sqlstr USING stub; @@ -241,6 +285,11 @@ identifier = COALESCE(identifier, cusip, isin)', NEW.id); END; $$ language plpgsql; +create + trigger dealid after insert + on + capfloors for each row execute procedure auto_dealid() ; + CREATE TRIGGER swaptions_dealid AFTER INSERT ON swaptions FOR EACH ROW EXECUTE PROCEDURE auto_dealid(); |