diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 33 |
1 files changed, 31 insertions, 2 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 5f2df87a..fe03e1ce 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -48,7 +48,7 @@ CREATE TABLE bonds(id serial primary key, folder bond_strat NOT NULL, custodian varchar(12) NOT NULL, cashaccount varchar(10) NOT NULL, - cp_code varchar(12) REFERENCES counterparties(code), + cp_code varchar(12) NOT NULL REFERENCES counterparties(code), trade_date date NOT NULL, settle_date date NOT NULL, cusip varchar(9), @@ -64,6 +64,21 @@ CREATE TABLE bonds(id serial primary key, accrued_payment float, CONSTRAINT bonds2_check CHECK (cusip IS NOT NULL OR isin IS NOT NULL)); +CREATE OR REPLACE FUNCTION mydealid() + RETURNS TRIGGER AS $$ + BEGIN + UPDATE bonds SET dealid = 'SC_' ||upper(left(asset_class::text,3))||id, + identifier = COALESCE(identifier, cusip, isin) WHERE bonds.id = NEW.id; + RETURN NEW; + END; + $$ language plpgsql; + +CREATE TRIGGER dealid + AFTER INSERT ON bonds + FOR EACH ROW + EXECUTE PROCEDURE mydealid(); + + CREATE TABLE cds(id serial primary key, dealid varchar(28), lastupdate timestamp DEFAULT now(), @@ -71,7 +86,7 @@ CREATE TABLE cds(id serial primary key, folder cds_strat NOT NULL, custodian varchar(12) NOT NULL, cashaccount varchar(10) NOT NULL, - cp_code varchar(12) NOT NULL, + cp_code varchar(12) NOT NULL REFERENCES counterparties(code), trade_date date NOT NULL, effective_date date NOT NULL, maturity date NOT NULL, @@ -94,6 +109,18 @@ CREATE TABLE cds(id serial primary key, CONSTRAINT tranche_check CHECK (swap_type != 'CD_INDEX_TRANCHE' OR (attach IS NOT NULL AND detach IS NOT NULL))); +ALTER TABLE cds OWNER TO dawn_user; +CREATE OR REPLACE FUNCTION auto_cds_dealid() + RETURNS TRIGGER AS $$ + BEGIN + UPDATE cds SET dealid = 'SCCDS' ||id WHERE bonds.id = NEW.id AND dealid is Null; + RETURN NEW; + END; + $$ language plpgsql; + +CREATE TRIGGER cds_dealid AFTER INSERT ON cds +FOR EACH ROW EXECUTE PROCEDURE mydealid(); + CREATE TABLE securities(identifier varchar(12) PRIMARY KEY, cusip varchar(9), isin varchar(12), @@ -113,6 +140,8 @@ CREATE TABLE securities(identifier varchar(12) PRIMARY KEY, paid_down date default 'Infinity', start_accrued_date date); +ALTER TABLE securities OWNER TO dawn_user; + CREATE TABLE marks(date date, identifier varchar(12) REFERENCES securities(identifier), price float, |