diff options
| -rw-r--r-- | sql/dawn.sql | 38 |
1 files changed, 34 insertions, 4 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index c5bcc879..86d5c5ec 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -58,12 +58,17 @@ CREATE TYPE settlement_type AS ENUM('Delivery', 'Cash'); CREATE TYPE beta_type AS ENUM('IG', 'CRT', 'EU', 'SUBPRIME', 'XO'); +CREATE TYPE account_type AS ENUM('Cash', 'Brokerage', 'Fcm', 'Future'); + CREATE TABLE accounts( code varchar(5) PRIMARY KEY, name text, - custodian text, - cash_account text, - counterparty varchar(12) REFERENCES counterparties(code)) + custodian text NOT NULL, + cash_account text NOT NULL, + counterparty varchar(12) REFERENCES counterparties(code), + fund fund NOT NULL, + account_type account_type NOT NULL, + active boolean NOT NULL); CREATE TABLE counterparties(code varchar(12) PRIMARY KEY, name text, @@ -87,7 +92,8 @@ CREATE TABLE counterparties(code varchar(12) PRIMARY KEY, CREATE INDEX ON counterparties(name); -CREATE TABLE bonds(id serial primary key, +-- deprecated +CREATE TABLE bond_old(id serial primary key, dealid varchar(28) UNIQUE, fund fund NOT NULL DEFAULT 'SERCGMAST'; lastupdate timestamp DEFAULT now(), @@ -112,6 +118,30 @@ CREATE TABLE bonds(id serial primary key, accrued_payment float, CONSTRAINT bonds2_check CHECK (cusip IS NOT NULL OR isin IS NOT NULL)); +CREATE TABLE bonds(id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + dealid varchar(28) GENERATED ALWAYS AS ('SC_'||CASE WHEN asset_class ='Subprime' THEN 'SUB' + WHEN asset_class='CLO' THEN 'CLO' + WHEN asset_class='CSO' THEN 'CSO' END + ||id::text) STORED, + folder bond_strat NOT NULL, + cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, + trade_date date NOT NULL, + settle_date date NOT NULL, + cusip varchar(9), + isin varchar(12), + identifier varchar(12), + description varchar(32) NOT NULL, + buysell bool NOT NULL, + faceamount float NOT NULL, + price float NOT NULL, + accrued float, + asset_class asset_class, + ticket text, + principal_payment float, + accrued_payment float, + current_face float, + CONSTRAINT bonds_check CHECK (cusip IS NOT NULL OR isin IS NOT NULL)); + CREATE TRIGGER dealid AFTER INSERT ON bonds FOR EACH ROW |