diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/mlpdb.sql | 68 |
1 files changed, 36 insertions, 32 deletions
diff --git a/sql/mlpdb.sql b/sql/mlpdb.sql index 8e2e3873..b0b668b9 100644 --- a/sql/mlpdb.sql +++ b/sql/mlpdb.sql @@ -25,47 +25,51 @@ CREATE TABLE tranche_data ( GRANT ALL ON tranche_data TO mlpdb_user;
-CREATE TABLE index_desc(
- BasketID integer,
- Index varchar(4),
+CREATE TABLE index_version(
+ BasketID serial,
+ Index index_type,
Series smallint,
- Maturity date,
- Tenor varchar(4),
+ Version smallint,
IndexFactor float,
CumulativeLoss float,
- PRIMARY KEY(BasketID, Tenor)
+ PRIMARY KEY(BasketID)
);
-GRANT ALL ON index_desc TO mlpdb_user;
-
-CREATE TABLE tranche_desc(
- TrancheID integer,
- BasketID integer,
- Tenor varchar(4),
- Attach smallint,
- Detach smallint,
- Foreign Key(BasketID, Tenor) REFERENCES index_desc(BasketID, Tenor),
- Primary Key(TrancheID, Tenor)
+CREATE TABLE index_maturity(
+ Index index_type,
+ series smallint,
+ tenor tenor,
+ maturity date
+ PRIMARY KEY(index, series, tenor)
);
-GRANT ALL ON tranche_desc TO mlpdb_user;
+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 date,
- TrancheID integer,
- BasketID integer,
+ 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),
- Upfront float,
- Running float,
- Tenor varchar(4),
- FOREIGN KEY(BasketID, Tenor) REFERENCES index_desc(BasketID, Tenor),
- FOREIGN KEY(TrancheID, Tenor) REFERENCES tranche_desc(TrancheID, Tenor)
+ QuoteSource varchar(4)
);
GRANT ALL ON quotes TO mlpdb_user;
@@ -100,7 +104,7 @@ GRANT ALL ON quotes TO mlpdb_user; CREATE TYPE curr AS ENUM('USD', 'EUR', 'JPY', 'GBP', 'CAD');
CREATE TYPE sen AS ENUM('Senior', 'Subordinated');
-CREATE TYPE bbgSource AS ENUM('MSG1', 'CBIN', 'CBGN');
+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');
@@ -130,25 +134,25 @@ CREATE TABLE cds_quotes( GRANT ALL ON cds_quotes to mlpdb_user;
-CREATE OR REPLACE FUNCTION nameToBasketID(index_name varchar(4)) RETURNS integer AS $$
+CREATE OR REPLACE FUNCTION nameToBasketID(index_name varchar(4), p_date date) RETURNS integer AS $$
DECLARE
- p_index varchar(2);
+ p_index index_type;
p_series smallint;
p_basketid integer;
BEGIN
- p_index := left(index_name, 2);
+ p_index := left(index_name, 2)::index_type;
p_series := right(index_name, -2);
SELECT MAX(basketid) INTO p_basketid FROM index_desc WHERE Index=p_index and Series=p_series;
RETURN p_basketid;
END;
$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION index_members(index_name varchar(4))
+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) INTO basketid;
+ SELECT nameToBasketID(index_name, p_date) INTO basketid;
RETURN QUERY SELECT * FROM CDS_Issuers WHERE index_list @> Array[basketid];
END;
$$ LANGUAGE plpgsql;
|