diff options
| -rw-r--r-- | sql/serenitasdb.sql | 42 |
1 files changed, 29 insertions, 13 deletions
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 8b5b03d8..25b70504 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -269,6 +269,7 @@ 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', 'CMAN');
CREATE TYPE DocClause AS ENUM('No Restructuring', 'Modified Modified Restructurin', 'Full Restructuring');
+CREATE TYPE ShortCode AS ENUM('CR14', 'XR14', 'MM14');
CREATE TYPE tenor AS ENUM('6mo', '1yr', '2yr', '3yr', '4yr', '5yr', '7yr', '10yr');
CREATE TYPE index_type AS ENUM('IG', 'HY', 'EU', 'LCDX', 'XO');
CREATE TYPE BBG_CC AS ENUM('OC');
@@ -296,8 +297,8 @@ CREATE TYPE cds_issuers AS ( ticker text,
currency curr,
seniority sen,
- doc_clause DocClause,
- cds_curve text[8] UNIQUE,
+ short_code ShortCode,
+ cds_curve text[8],
index_list integer[],
markit_ticker text,
markit_tier tier,
@@ -315,17 +316,20 @@ CREATE TABLE bbg_issuers( doc_clause DocClause,
cds_curve text[8] UNIQUE,
index_list integer[],
- PRIMARY KEY(company_id));
+ short_code ShortCode,
+ PRIMARY KEY(company_id, seniority));
GRANT ALL ON bbg_issuers to serenitas_user;
CREATE TABLE bbg_markit_mapping(
date date,
- company_id integer REFERENCES bbg_Issuers,
+ company_id integer,
+ seniority sen,
markit_ticker text,
markit_tier tier,
spread integer,
- PRIMARY KEY(company_id, date));
+ FOREIGN KEY (company_id, seniority) REFERENCES bbg_issuers,
+ PRIMARY KEY (date, company_id, seniority));
GRANT ALL ON bbg_markit_mapping to serenitas_user;
@@ -424,21 +428,30 @@ CREATE OR REPLACE FUNCTION historical_cds_quotes(date, bbgSource) RETURNS SETOF CREATE OR REPLACE FUNCTION historical_cds_issuers(date DEFAULT current_date)
RETURNS SETOF cds_issuers AS $$
BEGIN
- RETURN QUERY SELECT bbg_issuers.*,
+ RETURN QUERY SELECT bbg_issuers.name,
+ bbg_issuers.company_id,
+ bbg_issuers.ticker,
+ bbg_issuers.currency,
+ bbg_issuers.seniority,
+ bbg_issuers.short_code,
+ bbg_issuers.cds_curve,
+ bbg_issuers.index_list,
map.markit_ticker,
map.markit_tier,
map.spread,
event_date
FROM
(SELECT b.*
- FROM (SELECT min(c.date) AS latestdate, c.company_id
+ FROM (SELECT min(c.date) AS latestdate, c.company_id, c.seniority
FROM bbg_markit_mapping c
- WHERE c.date>=$1 GROUP BY c.company_id) a
+ WHERE c.date>=$1 GROUP BY c.company_id, c.seniority) a
JOIN bbg_markit_mapping b
- ON a.company_id = b.company_id AND a.latestdate=b.date) map
- JOIN bbg_issuers USING (company_id)
+ ON a.company_id = b.company_id
+ AND a.seniority = b.seniority
+ AND a.latestdate=b.date) map
+ JOIN bbg_issuers USING (company_id, seniority)
LEFT JOIN (SELECT * FROM defaulted WHERE event_date <= $1 ) c
- ON map.company_id = id;
+ ON map.company_id = id AND map.seniority = c.seniority;
END;
$$ LANGUAGE plpgsql;
@@ -967,7 +980,10 @@ CREATE OR REPLACE VIEW public.on_the_run AS CREATE TABLE defaulted(
- id integer REFERENCES bbg_issuers(company_id) PRIMARY KEY,
+ id integer,
event_date date,
auction_date date,
- recovery float)
+ recovery float,
+ seniority sen,
+ PRIMARY KEY (id, seniority)
+ FOREIGN KEY (id, seniority) REFERENCES bbg_issuers)
|