diff options
Diffstat (limited to 'sql/mlpdb.sql')
| -rw-r--r-- | sql/mlpdb.sql | 227 |
1 files changed, 0 insertions, 227 deletions
diff --git a/sql/mlpdb.sql b/sql/mlpdb.sql deleted file mode 100644 index 601b9dfa..00000000 --- a/sql/mlpdb.sql +++ /dev/null @@ -1,227 +0,0 @@ -CREATE TABLE tranche_data (
- QuoteDate date,
- TrancheId integer,
- BasketId integer,
- Maturity date,
- Tenor varchar(4),
- RefBasketPrice float,
- BPS varchar(1),
- BasketDuration float,
- TQC varchar(1),
- TrancheDuration float,
- TrancheDelta float,
- CorrAtDetachment float,
- Basis float,
- QuoteSource varchar(2),
- Index varchar(4),
- Series smallint,
- Upfront float,
- Running float,
- Attach smallint,
- Detach smallint,
- IndexFactor float,
- CumulativeLoss float
-);
-
-GRANT ALL ON tranche_data TO mlpdb_user;
-
-CREATE TABLE index_version(
- BasketID serial,
- Index index_type,
- Series smallint,
- Version smallint,
- IndexFactor float,
- CumulativeLoss float,
- PRIMARY KEY(BasketID)
-);
-
-CREATE TABLE index_maturity(
- Index index_type,
- series smallint,
- tenor tenor,
- maturity date,
- PRIMARY KEY(index, series, tenor)
-);
-
-CREATE OR REPLACE VIEW index_desc AS
- SELECT b.basketid, b.index, b.series, b.version, a.tenor, a.maturity, b.indexfactor, b.cumulativeloss, b.lastdate
- FROM index_maturity a
- JOIN index_version b ON a.index = b.index AND a.series = b.series;
-
-GRANT ALL ON index_maturity TO mlpdb_user;
-GRANT ALL ON index_version TO mlpdb_user;
-GRANT ALL ON index_desc TO mlpdb_user;
-
-
-CREATE TABLE quotes (
- QuoteDate timestamp,
- Index index_type,
- Series smallint,
- Version smallint,
- tenor tenor,
- attach smallint,
- detach smallint,
- RefBasketPrice float,
- Upfront float,
- Running float,
- BasketDuration float,
- TrancheDuration float,
- TrancheDelta float,
- CorrAtDetachment float,
- Basis float,
- QuoteSource varchar(4)
-);
-
-CREATE TABLE tranche_quotes (
- QuoteDate timestamp,
- Index index_type,
- Series smallint,
- Version smallint,
- Tenor tenor,
- Attach smallint,
- Detach smallint,
- TrancheUpfrontBid float,
- TrancheUpfrontMid float,
- TrancheUpfrontAsk float,
- TrancheRunningBid float,
- TrancheRunningMid float,
- TrancheRunningAsk float,
- IndexRefPrice real,
- IndexRefSpread smallint,
- IndexDuration real,
- TrancheDuration real,
- TrancheDelta real,
- CorrAtDetachment real,
- Basis real,
- QuoteSource varchar(4),
- PRIMARY KEY(QuoteDate, Index, Series, Version, Tenor, Attach, Detach, QuoteSource)
-);
-
-GRANT ALL ON quotes TO mlpdb_user;
-GRANT ALL ON tranche_quotes TO mlpdb_user;
-
--- Idiosyncracies:
-
--- for Itraxx 9 index:
-
--- tr.id coupon type
--- 3-6 98155 all running
--- 98154 U+500
--- 6-9 98160 all running
--- 166395 U+300
--- 98159 U+500
-
--- For IG 9 index:
-
--- tr.id coupon type
--- 3-7 162290 all running
--- 162291 U+500
--- 7-10 162298 all running
--- 162300 U+500
--- 10-15 162301 all running
--- 162304 U+500
--- 15-30 162305 all running
--- 162306 U+500
--- 30-100 162307 all running
--- 162308 U+500
-
--- All other tranches should have a unique trancheid for a given basketid, attach, detach combination
-
-CREATE TYPE curr AS ENUM('USD', 'EUR', 'JPY', 'GBP', 'CAD');
-CREATE TYPE sen AS ENUM('Senior', 'Subordinated');
-CREATE TYPE tier AS ENUM('SNRFOR', 'SECDOM', 'SUBLT2', 'PREFT1', 'JRSUBUT2');
-CREATE TYPE bbgSource AS ENUM('MSG1', 'CBIN', 'CBGN', 'MKIT');
-CREATE TYPE DocClause AS ENUM('No Restructuring', 'Modified Modified Restructurin', 'Full Restructuring');
-CREATE TYPE tenor AS ENUM('6mo', '1yr', '2yr', '3yr', '4yr', '5yr', '7yr', '10yr');
-
-CREATE TABLE CDS_Issuers(
- Name text,
- company_id integer,
- ticker text,
- currency curr,
- seniority sen,
- doc_clause DocClause,
- cds_curve text[8],
- index_list integer[],
- markit_ticket text,
- markit_tier tier,
- PRIMARY KEY(company_id));
-
-GRANT ALL ON CDS_Issuers to mlpdb_user;
-
-CREATE TABLE cds_quotes(
- Date Date,
- curve_ticker text,
- UpfrontBid float,
- UpfrontAsk float,
- RunningBid float,
- RunningAsk float,
- Source bbgSource,
- Recovery float,
- PRIMARY KEY(curve_ticker, Date));
-
-GRANT ALL ON cds_quotes to mlpdb_user;
-
-CREATE OR REPLACE FUNCTION nameToBasketID(index_name varchar(4), p_date date) RETURNS integer AS $$
- DECLARE
- p_index index_type;
- p_series smallint;
- p_basketid integer;
- BEGIN
- p_index := upper(left(index_name, 2))::index_type;
- p_series := right(index_name, -2);
- SELECT MIN(basketid) INTO p_basketid FROM index_version WHERE Index=p_index and
- Series=p_series and lastdate>=p_date;
- RETURN p_basketid;
- END;
- $$ LANGUAGE plpgsql;
-
-CREATE OR REPLACE FUNCTION index_members(index_name varchar(4), p_date date)
- RETURNS SETOF CDS_Issuers AS $$
- DECLARE
- basketid integer;
- BEGIN
- SELECT nameToBasketID(index_name, p_date) INTO basketid;
- RETURN QUERY SELECT * FROM CDS_Issuers WHERE index_list @> Array[basketid];
- END;
- $$ LANGUAGE plpgsql;
-
-CREATE OR REPLACE FUNCTION historical_cds_quotes(date) RETURNS SETOF cds_quotes AS $$
- BEGIN
- RETURN QUERY SELECT b.*
- FROM (SELECT max(c.Date) AS latestdate, c.curve_ticker FROM cds_quotes c
- WHERE c.Date<=$1 GROUP BY c.curve_ticker) a
- JOIN cds_quotes b ON a.curve_ticker = b.curve_ticker AND a.latestdate=b.Date;
- END;
- -- simpler query but slower
- -- BEGIN
- -- RETURN QUERY SELECT DISTINCT ON (curve_ticker) * from cds_quotes where date<=$1 ORDER BY
- -- curve_ticker, DATE desc;
- -- END;
- $$ LANGUAGE plpgsql;
-
-CREATE OR REPLACE FUNCTION curve_quotes(varchar(4), date) RETURNS
- TABLE(cds_ticker text, date date, spread_curve float[], upfront_curve float[], recovery_curve float[]) AS $$
- BEGIN
- RETURN QUERY SELECT max(markit_ticker) AS t, max(a.date),
- array_agg((a.runningbid+a.runningask)/2 ORDER BY tenor),
- array_agg((a.upfrontbid+a.upfrontask)/2 ORDER BY tenor),
- array_agg(a.Recovery ORDER BY tenor) FROM historical_cds_quotes($2) a RIGHT JOIN
- (SELECT generate_series(1, 8) AS tenor, unnest(cds_curve) AS curve_ticker, markit_ticker, company_id
- FROM index_members($1, $2)) b
- ON b.curve_ticker = a.curve_ticker GROUP by company_id ORDER BY t;
- END;
- $$ LANGUAGE plpgsql;
-
-CREATE OR REPLACE FUNCTION curve_quotes_fmt(varchar(4), date) RETURNS
- TABLE(cds_ticker text, date date, spread_curve text, upfront_curve text, recovery_curve text) AS $$
- BEGIN
- RETURN QUERY SELECT max(markit_ticker) AS t, max(a.date),
- string_agg(to_char((a.runningbid+a.runningask)/2, 'FM999'), ',' ORDER BY tenor),
- string_agg(to_char((a.upfrontbid+a.upfrontask)/2, 'FM99D99'), ',' ORDER BY tenor),
- string_agg(to_char(a.Recovery, 'FM0D99'), ',' ORDER BY tenor) FROM historical_cds_quotes($2) a RIGHT JOIN
- (SELECT generate_series(1, 8) AS tenor, unnest(cds_curve) AS curve_ticker, markit_ticker, company_id
- FROM index_members($1, $2)) b
- ON b.curve_ticker = a.curve_ticker GROUP by company_id ORDER BY t;
- END;
- $$ LANGUAGE plpgsql;
|