diff options
| -rw-r--r-- | sql/dawn.sql | 2 | ||||
| -rw-r--r-- | sql/serenitasdb.sql | 63 |
2 files changed, 42 insertions, 23 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 3332b305..a356b89f 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -2194,6 +2194,8 @@ CREATE TYPE curr AS ENUM('USD', 'EUR', 'JPY', 'GBP', 'CAD'); IMPORT FOREIGN SCHEMA public LIMIT TO (index_desc, index_version, + index_factors, + index_maturities, index_version_markit, index_maturity, index_maturity_markit, diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 5db7674e..192ffddb 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -26,19 +26,41 @@ CREATE TABLE tranche_data ( GRANT ALL ON tranche_data TO serenitas_user;
-CREATE TABLE index_version(
- BasketID serial PRIMARY KEY,
- Index index_type NOT NULL,
- Series smallint NOT NULL,
- Version smallint NOT NULL,
- IndexFactor float DEFAULT 100,
- CumulativeLoss float DEFAULT 0.,
- lastdate date DEFAULT 'infinity',
- redindexcode text,
- description text,
- PRIMARY KEY(BasketID),
- UNIQUE (index, series, version)
-);
+CREATE TABLE index_series(
+index_id int4 GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+index index_type NOT NULL,
+series SMALLINT NOT NULL,
+description TEXT,
+issue_date date,
+UNIQUE (index, series));
+
+CREATE TABLE index_factors(
+basketid int4 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
+index_id int4 NOT NULL REFERENCES index_series,
+VERSION SMALLINT,
+indexfactor double precision DEFAULT 100,
+cumulativeloss double precision DEFAULT 0.0,
+lastdate date DEFAULT 'infinity',
+redindexcode TEXT);
+
+CREATE OR REPLACE VIEW index_version AS
+SELECT basketid, INDEX, series, VERSION, indexfactor, cumulativeloss, lastdate, redindexcode, description
+FROM index_series
+LEFT JOIN index_factors USING (index_id);
+
+CREATE TABLE index_maturities(
+maturity_id int4 GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+index_id int4 NOT NULL REFERENCES index_series,
+tenor tenor,
+maturity date,
+coupon int4,
+bbg_id TEXT);
+
+CREATE OR REPLACE VIEW index_desc AS
+SELECT basketid, INDEX, series, VERSION, indexfactor, cumulativeloss, lastdate, redindexcode, tenor, maturity, coupon, issue_date
+FROM index_series
+LEFT JOIN index_factors USING (index_id)
+LEFT JOIN index_maturities USING (index_id);
CREATE TYPE INDEXFAMILY AS ENUM('ITRAXX-Asian', 'LCDXNA', 'MCDXNA',
'ITRAXX-SOVX', 'ITRAXX-SDI', 'ITRAXX-L', 'CDX', 'ITRAXX-European');
@@ -62,15 +84,10 @@ CREATE TABLE index_version_markit( GRANT ALL ON index_version TO serenitas_users;
-CREATE TABLE index_maturity(
- Index index_type,
- series smallint,
- tenor tenor,
- maturity date,
- coupon integer,
- issue_date date,
- PRIMARY KEY(index, series, tenor)
-);
+CREATE OR REPLACE VIEW index_maturity AS
+SELECT INDEX, series, tenor, maturity, coupon, issue_date, bbg_id
+FROM index_series
+LEFT JOIN index_maturities USING (index_id);
CREATE TABLE index_maturity_markit(
redindexcode text REFERENCES index_version_markit,
@@ -385,7 +402,7 @@ CREATE INDEX IF NOT EXISTS cds_quotes_date_index ON cds_quotes(date); CREATE TABLE markit_tranche_quotes(
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
quotedate date,
- basketid integer REFERENCES index_version,
+ basketid integer REFERENCES index_factors,
tenor tenor,
attach smallint,
detach smallint,
|