aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--python/Dawn/models.py29
-rw-r--r--python/process_queue.py4
-rw-r--r--sql/dawn.sql101
3 files changed, 114 insertions, 20 deletions
diff --git a/python/Dawn/models.py b/python/Dawn/models.py
index 45ea6a21..ae3e336e 100644
--- a/python/Dawn/models.py
+++ b/python/Dawn/models.py
@@ -36,6 +36,8 @@ class Accounts(db.Model):
cash_account = db.Column(db.String)
counterparty = db.Column(db.String(12), db.ForeignKey('counterparties.code'))
+FUND = ENUM('SERCGMAST', 'BRINKER', name='fund')
+
PORTFOLIO = ENUM('OPTIONS', 'IR', 'MORTGAGES', 'IG', 'HY', 'CURVE', 'TRANCHE',
'CLO', 'HEDGE_MAC', 'STRUCTURED', name='portfolio')
@@ -106,6 +108,7 @@ BEGIN_OR_END = ENUM('B', 'E', name='begin_or_end')
class BondDeal(db.Model):
__tablename__ = 'bonds'
id = db.Column('id', db.Integer, primary_key=True)
+ fund = db.Column(FUND, nullable=False, default='SERCGMAST')
dealid = db.Column(db.String(28))
lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now())
action = db.Column(ACTION, nullable=False)
@@ -142,6 +145,7 @@ class BondDeal(db.Model):
class CDSDeal(db.Model):
__tablename__ = 'cds'
id = db.Column('id', db.Integer, primary_key=True)
+ fund = db.Column(FUND, nullable=False, default='SERCGMAST')
dealid = db.Column(db.String(28))
lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now())
action = db.Column(ACTION, nullable=False)
@@ -200,8 +204,9 @@ class CDSDeal(db.Model):
class RepoDeal(db.Model):
__tablename__ = 'repo'
id = db.Column('id', db.Integer, primary_key=True)
+ fund = db.Column(FUND, nullable=False, default='SERCGMAST')
lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now())
- action = db.Column(ACTION)
+ action = db.Column(ACTION, nullable=False)
folder = db.Column(CDS_STRAT, nullable=False)
custodian = db.Column(db.String(12), default='SGFCM', nullable=False)
cashaccount = db.Column(db.String(10), default='V0NSCLMAMB', nullable=False)
@@ -238,10 +243,11 @@ class RepoDeal(db.Model):
class SwaptionDeal(db.Model):
__tablename__ = 'swaptions'
id = db.Column('id', db.Integer, primary_key=True)
- dealid = db.Column(db.String(28))
+ fund = db.Column(FUND, nullable=False, default='SERCGMAST')
+ dealid = db.Column(db.String(28), nullable=False)
lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now())
- action = db.Column(ACTION)
- portfolio = db.Column(PORTFOLIO)
+ action = db.Column(ACTION, nullable=False)
+ portfolio = db.Column(PORTFOLIO, nullable=False)
folder = db.Column(SWAPTION_STRAT, nullable=False)
custodian = db.Column(db.String(12), default='NONE', nullable=False)
cashaccount = db.Column(db.String(10), default='V0NSCLMAMB', nullable=False)
@@ -279,9 +285,10 @@ class SwaptionDeal(db.Model):
class FutureDeal(db.Model):
__tablename__ = 'futures'
id = db.Column('id', db.Integer, primary_key=True)
- dealid = db.Column(db.String(28))
+ fund = db.Column(FUND, nullable=False)
+ dealid = db.Column(db.String(28), nullable=False)
lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now())
- action = db.Column(ACTION)
+ action = db.Column(ACTION, nullable=False)
folder = db.Column(FUTURE_STRAT, nullable=False)
custodian = db.Column(db.String(12), default='INTBR', nullable=False)
cashaccount = db.Column(db.String(10), default='IANSCLMAFU', nullable=False)
@@ -321,9 +328,10 @@ class CashFlowDeal(db.Model):
class SpotDeal(db.Model):
__tablename__ = "spots"
id = db.Column('id', db.Integer, primary_key=True)
- dealid = db.Column(db.String(28))
+ fund = db.Column(FUND, nullable=False, default='SERCGMAST')
+ dealid = db.Column(db.String(28), nullable=False)
lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now())
- action = db.Column(ACTION)
+ action = db.Column(ACTION, nullable=False)
folder = db.Column(SPOT_STRAT, nullable=False)
custodian = db.Column(db.String(12), default='INTBR', nullable=False)
cashaccount = db.Column(db.String(10), default='IANSCLMAFU', nullable=False)
@@ -344,9 +352,10 @@ class SpotDeal(db.Model):
class CapFloorDeal(db.Model):
__tablename__ = 'capfloors'
id = db.Column('id', db.Integer, primary_key=True)
- dealid = db.Column(db.String(28))
+ fund = db.Column(FUND, nullable=False, default='SERCGMAST')
+ dealid = db.Column(db.String(28), nullable=False)
lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now())
- action = db.Column(ACTION)
+ action = db.Column(ACTION, nullable=False)
folder = db.Column(SWAPTION_STRAT, nullable=False)
custodian = db.Column(db.String(12), default='NONE', nullable=False)
cashaccount = db.Column(db.String(10), default='V0NSCLMAMB', nullable=False)
diff --git a/python/process_queue.py b/python/process_queue.py
index 5a76ce1a..3e639560 100644
--- a/python/process_queue.py
+++ b/python/process_queue.py
@@ -144,9 +144,9 @@ def build_termination(obj):
def build_line(obj, queue_name='bond_trades'):
obj['Client'] = 'Serenitas'
- obj['Fund'] = 'SERCGMAST'
obj['State'] = 'Valid'
- rename_cols = {'action': 'Action',
+ rename_cols = {'fund': 'Fund',
+ 'action': 'Action',
'dealid': 'Deal Id',
'folder': 'Folder',
'custodian': 'Custodian',
diff --git a/sql/dawn.sql b/sql/dawn.sql
index a8a28141..f8d20507 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -80,9 +80,10 @@ CREATE TABLE counterparties(code varchar(12) PRIMARY KEY,
CREATE INDEX ON counterparties(name);
CREATE TABLE bonds(id serial primary key,
- dealid varchar(28),
+ dealid varchar(28) UNIQUE,
+ fund fund NOT NULL DEFAULT 'SERCGMAST';
lastupdate timestamp DEFAULT now(),
- action action,
+ action action NOT NULL,
folder bond_strat NOT NULL,
custodian varchar(12) NOT NULL,
cashaccount varchar(10) NOT NULL,
@@ -110,7 +111,8 @@ CREATE TRIGGER dealid
CREATE TABLE cds(id serial primary key,
- dealid varchar(28),
+ dealid varchar(28) UNIQUE,
+ fund fund NOT NULL DEFAULT 'SERCGMAST',
lastupdate timestamptz DEFAULT now(),
action action NOT NULL,
portfolio portfolio NOT NULL,
@@ -160,9 +162,10 @@ CREATE TRIGGER cds_dealid AFTER INSERT ON cds
FOR EACH ROW EXECUTE PROCEDURE auto_dealid();
CREATE TABLE repo(id serial primary key,
- dealid varchar(28),
+ dealid varchar(28) UNIQUE,
+ fund fund NOT NULL DEFAULT 'SERCGMAST',
lastupdate timestamptz DEFAULT now(),
- action action,
+ action action NOT NULL,
folder bond_strat NOT NULL,
custodian varchar(12) NOT NULL,
cashaccount varchar(10) NOT NULL,
@@ -196,8 +199,9 @@ ALTER TABLE repo OWNER TO dawn_user;
CREATE TABLE swaptions(id serial PRIMARY KEY,
dealid varchar(28) UNIQUE,
+ fund fund NOT NULL DEFAULT 'SERCGMAST',
lastupdate timestamptz DEFAULT now(),
- action action,
+ action action NOT NULL,
portfolio portfolio NOT NULL,
folder swaption_strat NOT NULL,
custodian varchar(12) NOT NULL,
@@ -223,11 +227,15 @@ CREATE TABLE swaptions(id serial PRIMARY KEY,
termination_amount float,
termination_cp varchar(12) REFERENCES counterparties(code) ON UPDATE CASCADE);
+CREATE TRIGGER swaptions_dealid AFTER INSERT ON swaptions
+FOR EACH ROW EXECUTE PROCEDURE auto_dealid();
+
CREATE TABLE capfloors (
id serial PRIMARY KEY,
- dealid varchar(28),
+ fund fund NOT NULL DEFAULT 'SERCGMAST',
+ dealid varchar(28) UNIQUE,
lastupdate timestamp NULL DEFAULT now(),
- "action" action,
+ "action" action NOT NULL,
folder swaption_strat NOT NULL,
custodian varchar(12) NOT NULL,
cashaccount varchar(10) NOT NULL,
@@ -260,6 +268,81 @@ CREATE TABLE capfloors (
termination_cp varchar(12) REFERENCES counterparties(code) ON UPDATE CASCADE,
cpty_id text,
)
+CREATE TRIGGER dealid AFTER INSERT ON capfloors
+FOR EACH ROW EXECUTE PROCEDURE auto_dealid();
+
+CREATE TABLE futures (
+ id serial NOT NULL,
+ dealid varchar(28),
+ lastupdate timestamp NULL DEFAULT now(),
+ "action" action NOT NULL,
+ folder future_strat NOT NULL,
+ custodian varchar(12) NOT NULL,
+ cashaccount varchar(10) NOT NULL,
+ cp_code varchar(12) NOT NULL,
+ trade_date date NOT NULL,
+ settle_date date NOT NULL,
+ buysell bool NOT NULL,
+ bbg_ticker varchar(32) NOT NULL,
+ quantity float8 NOT NULL,
+ price float8 NOT NULL,
+ commission float8 NULL,
+ swap_type future_type NOT NULL,
+ security_desc varchar(32) NOT NULL,
+ maturity date NOT NULL,
+ currency currency NOT NULL,
+ exchange varchar(3) NOT NULL,
+ fund fund NOT NULL DEFAULT 'SERCGMAST'::fund,
+ CONSTRAINT futures_dealid_key UNIQUE (dealid),
+ CONSTRAINT futures_pkey PRIMARY KEY (id),
+ CONSTRAINT futures_cp_code_fkey FOREIGN KEY (cp_code) REFERENCES counterparties(code)
+);
+ALTER table futures OWNER TO dawn_user;
+CREATE trigger dealid after insert on futures for each row execute procedure auto_dealid();
+
+CREATE TABLE wires (
+ id serial NOT NULL,
+ dealid varchar(28) NULL,
+ lastupdate timestamp NULL DEFAULT now(),
+ "action" action NOT NULL,
+ folder cash_strat NOT NULL,
+ code varchar(5) NOT NULL,
+ amount float8 NOT NULL,
+ currency currency NOT NULL,
+ trade_date date NOT NULL,
+ CONSTRAINT wires_pkey PRIMARY KEY (id),
+ CONSTRAINT wires_code_fkey FOREIGN KEY (code) REFERENCES accounts(code)
+);
+ALTER table wires OWNER TO dawn_user;
+CREATE trigger dealid after insert on wires for each row execute procedure auto_dealid();
+
+CREATE TABLE spots (
+ id serial NOT NULL,
+ fund fund NOT NULL DEFAULT 'SERCGMAST'::fund,
+ dealid varchar(28) NULL,
+ lastupdate timestamp NULL DEFAULT now(),
+ "action" action NOT NULL,
+ folder spot_strat NOT NULL,
+ custodian varchar(12) NOT NULL,
+ cashaccount varchar(10) NOT NULL,
+ cp_code varchar(12) NOT NULL,
+ trade_date date NOT NULL,
+ settle_date date NOT NULL,
+ spot_rate float8 NOT NULL,
+ buy_currency currency NOT NULL,
+ buy_amount float8 NOT NULL,
+ sell_currency currency NOT NULL,
+ sell_amount float8 NOT NULL,
+ commission_currency currency NULL,
+ commission float8 NULL,
+ CONSTRAINT spots_pkey PRIMARY KEY (id),
+ CONSTRAINT spots_cp_code_fkey FOREIGN KEY (cp_code) REFERENCES counterparties(code)
+);
+
+create
+ trigger dealid after insert
+ on
+ spots for each row execute procedure auto_dealid();;
CREATE OR REPLACE FUNCTION auto_dealid()
RETURNS TRIGGER AS $$
@@ -286,6 +369,8 @@ identifier = COALESCE(identifier, cusip, isin)', NEW.id);
stub := 'SCCSH';
WHEN 'capfloors' THEN
stub := 'CAP';
+ WHEN 'spots' THEN
+ stub := 'SCFX';
END CASE;
sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id);
END IF;
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2025-12-24 00:12:28 +0000