diff options
| -rw-r--r-- | sql/dawn.sql | 115 |
1 files changed, 82 insertions, 33 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index cb0f6b33..fecd9445 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -145,17 +145,52 @@ CREATE TABLE bonds(id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, allocated boolean NOT NULL DEFAULT false, CONSTRAINT bonds_check CHECK (cusip IS NOT NULL OR isin IS NOT NULL)); -create table allocation( +CREATE OR REPLACE FUNCTION notify_id() +RETURNS trigger +AS $$ +BEGIN +PERFORM pg_notify(TG_TABLE_NAME, NEW.id::text); +RETURN NEW; +END +$$ LANGUAGE plpgsql; + + +CREATE TRIGGER bond_notify AFTER +INSERT OR UPDATE OF + identifier, + faceamount, + price, + settle_date +ON + bonds +FOR EACH ROW EXECUTE PROCEDURE notify_id(); + +CREATE OR REPLACE FUNCTION update_identifier() +RETURNS trigger +AS $$ +BEGIN +EXECUTE 'UPDATE bonds SET identifier = COALESCE(identifier, cusip, isin) WHERE id=$1' USING NEW.id; +RETURN NEW; +END +$$ LANGUAGE plpgsql; + +CREATE TABLE allocation( id integer generated always as identity primary key, - tradeid integer not null references bonds3, + tradeid integer not null references bonds, notional float not null, code text not null references accounts, - submitted boolean default False); + submitted boolean default False, + UNIQUE (tradeid, code)); -CREATE TRIGGER dealid - AFTER INSERT ON bonds - FOR EACH ROW - EXECUTE PROCEDURE auto_dealid(); +CREATE TYPE status AS ENUM('Pending', 'Processed', 'Submitted', 'Aknowledged'); + +CREATE TABLE submission( + id integer generated always as identity primary key, + allocation_id integer not null references allocation on delete cascade, + "action" action not null, + submit_date = timestamptz DEFAULT now(), + status status NOT NULL DEFAULT 'PENDING' +); CREATE TABLE cds(id serial primary key, @@ -209,6 +244,46 @@ ALTER TABLE cds OWNER TO dawn_user; CREATE TRIGGER cds_dealid AFTER INSERT ON cds FOR EACH ROW EXECUTE PROCEDURE auto_dealid(); + +CREATE TRIGGER cds_notify AFTER +INSERT + OR +UPDATE + OF security_id, + maturity, + trade_date, + upfront_settle_date, + "ref", + notional, + protection ON + cds FOR EACH ROW EXECUTE FUNCTION notify_id(); + + +CREATE OR REPLACE function update_attach() +RETURNS TRIGGER AS $$ +DECLARE + factor float; + cum_loss float; +BEGIN +IF NEW.orig_attach IS NULL AND NEW.orig_detach IS NULL THEN +RETURN NEW; +ELSE +SELECT indexfactor, cumulativeloss INTO factor, cum_loss + FROM index_version WHERE redindexcode=NEW.security_id; +UPDATE cds + SET attach=factor*LEAST(GREATEST((NEW.orig_attach - cum_loss)/factor, 0), 1), + detach=factor*LEAST(GREATEST((NEW.orig_detach - cum_loss)/factor, 0), 1) +WHERE id=NEW.id; +RETURN NEW; +END IF; +END +$$ LANGUAGE plpgsql; + +CREATE TRIGGER cds_attach AFTER +INSERT OR UPDATE OF orig_attach, orig_detach, security_id +ON cds +FOR EACH ROW EXECUTE PROCEDURE update_attach(); + CREATE TABLE repo(id serial primary key, dealid varchar(28) UNIQUE, fund fund NOT NULL DEFAULT 'SERCGMAST', @@ -442,35 +517,9 @@ identifier = COALESCE(identifier, cusip, isin)', NEW.id); END; $$ language plpgsql; -CREATE OR REPLACE function update_attach() -RETURNS TRIGGER AS $$ -DECLARE - factor float; - cum_loss float; -BEGIN -IF NEW.orig_attach IS NULL AND NEW.orig_detach IS NULL THEN -RETURN NEW; -ELSE -SELECT indexfactor, cumulativeloss INTO factor, cum_loss - FROM index_version WHERE redindexcode=NEW.security_id; -UPDATE cds - SET attach=factor*LEAST(GREATEST((NEW.orig_attach - cum_loss)/factor, 0), 1), - detach=factor*LEAST(GREATEST((NEW.orig_detach - cum_loss)/factor, 0), 1) -WHERE id=NEW.id; -RETURN NEW; -END IF; -END -$$ LANGUAGE plpgsql; - - CREATE TRIGGER dealid AFTER INSERT ON capfloors FOR EACH ROW EXECUTE PROCEDURE auto_dealid() ; -CREATE TRIGGER cds_attach AFTER -INSERT OR UPDATE OF orig_attach, orig_detach, security_id -ON cds -FOR EACH ROW EXECUTE PROCEDURE update_attach(); - CREATE TRIGGER swaptions_dealid AFTER INSERT ON swaptions FOR EACH ROW EXECUTE PROCEDURE auto_dealid(); |