diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 94 |
1 files changed, 94 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 77f78ad8..482826af 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -3195,4 +3195,98 @@ CREATE TABLE public.citco_instruments ( commited bool NOT NULL default False, CONSTRAINT swaption UNIQUE (expiration, strike, underlying_security_id, serenitas_product, callput), CONSTRAINT tranche UNIQUE (serenitas_product, underlying_security_id, attach, detach) +); + + +CREATE TABLE public.citco_tranche ( + id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY, + dealid text NULL GENERATED ALWAYS AS ('CDS_'::text || id::text) STORED, + instrument_type public.instrument_type NOT NULL DEFAULT 'CDS'::instrument_type, + underlying_id_source public.id_source NOT NULL DEFAULT 'RED'::id_source, + underlying_security_id text NOT NULL, + birth_date date NULL, + death_date date NULL, + active bool NULL DEFAULT true, + "attach" int8 NOT NULL, + "detach" int8 NOT NULL, + "committed" bool NOT NULL DEFAULT false, + CONSTRAINT citco_tranche_pkey PRIMARY KEY (id), + CONSTRAINT citco_tranche_un UNIQUE (underlying_security_id, attach, detach) +); + + + +CREATE TABLE public.citco_swaption ( + id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY, + dealid varchar(28) NULL GENERATED ALWAYS AS (((( + CASE + WHEN instrument_type = 'SWPO'::instrument_type THEN 'SWPO'::text + WHEN instrument_type = 'BNDO'::instrument_type THEN 'BNDO'::text + ELSE NULL::text + END) || id::text))) STORED, + instrument_type public.instrument_type NOT NULL, + underlying_id_source public.id_source NOT NULL, + underlying_security_id text NOT NULL, + birth_date date NOT NULL, + death_date date NOT NULL, + active bool NOT NULL DEFAULT true, + strike float8 NOT NULL, + expiration date NOT NULL, + callput bool NULL, + commited bool NOT NULL DEFAULT false, + CONSTRAINT citco_swaption_pkey PRIMARY KEY (id), + CONSTRAINT citco_swaption_un UNIQUE (instrument_type, underlying_security_id, strike, expiration, callput) +); + +CREATE TABLE public.citco_irs ( + id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, + dealid text NULL GENERATED ALWAYS AS ('IRS'::text || id::text) STORED, + instrument_type "instrument_type" NOT NULL default 'IRS', + birth_date date NOT NULL, + death_date date NOT NULL, + active bool NOT NULL default True, + payreceive bool NOT NULL, + fixed_rate float8 NOT NULL, + fixed_daycount public."day_count" NOT NULL, + fixed_payment_freq public."frequency" NOT NULL, + fixed_bdc public."bus_day_convention" NOT NULL, + notional float8 NOT NULL, + float_index public."cash_rate" NOT NULL, + float_daycount public."day_count" NOT NULL, + float_payment_freq public."frequency" NOT NULL, + float_bdc public."bus_day_convention" NOT NULL, + float_arrears bool NOT NULL, + "currency" public."currency" NOT NULL, + float_fixing_freq public."frequency" NOT NULL, + pay_interest_calc_method public."interest_calc_method" NOT NULL, + commited bool NOT NULL default False, +); + + +CREATE TABLE public.citco_trs ( + id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, + dealid text NULL GENERATED ALWAYS AS ('TRS'::text || id::text) STORED, + instrument_type "instrument_type" NOT NULL default 'TRS', + birth_date date NOT NULL, + death_date date NOT NULL, + active bool NOT NULL default True, + funding_index public."cash_rate" NOT NULL DEFAULT 'SOFRRATE'::cash_rate, + buysell bool NOT NULL, + underlying_security varchar(32) NOT NULL, + price numeric(9, 5) NOT NULL, + accrued float8 NOT NULL, + funding_freq public."frequency" NOT NULL, + funding_daycount public."day_count" NULL, + funding_payment_roll_convention public."bus_day_convention" NOT NULL, + funding_arrears bool NOT NULL, + asset_freq public."frequency" NOT NULL, + asset_daycount public."day_count" NULL, + asset_payment_roll_convention public."bus_day_convention" NOT NULL, + initial_margin_percentage float8 NOT NULL, + notional float8 NOT NULL, + "currency" public."currency" NOT NULL DEFAULT 'USD'::currency, + "interest_calc_method" public."interest_calc_method" NOT NULL, + compound_avg_frequency public."frequency" NOT NULL, + fixing_frequency public."frequency" NOT NULL, + commited bool NOT NULL default False );
\ No newline at end of file |