diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/serenitasdb.sql | 52 |
1 files changed, 51 insertions, 1 deletions
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 2722d877..df6dec39 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -830,7 +830,8 @@ FROM tranche_risk JOIN tranche_quotes ON tranche_id=tranche_quotes.id;
-CREATE TABLE tranche_risk(
+-- deprecated, renamed to tranche_risk_prev
+CREATE TABLE tranche_risk_prev(
id serial PRIMARY KEY,
tranche_id integer UNIQUE REFERENCES tranche_quotes(id) ON DELETE CASCADE,
index_price float,
@@ -852,6 +853,55 @@ CREATE TABLE tranche_risk( GRANT ALL ON tranche_risk to serenitas_user;
+CREATE TABLE tranche_risk_index(
+ id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+ quoteset integer UNIQUE NOT NULL REFERENCES tranche_quotes_ref ON DELETE CASCADE,
+ price float,
+ basis float,
+ expected_loss float,
+ duration float,
+ theta float);
+
+CREATE TABLE tranche_risk_tranches(
+ id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+ quoteset integer NOT NULL REFERENCES tranche_quotes_ref ON DELETE CASCASDE,
+ tranche_id integer UNIQUE NOT NULL REFERENCES tranche_quotes_tranches(id) ON DELETE CASCADE,
+ corr_at_detach float,
+ delta float,
+ fwd_delta float,
+ gamma float,
+ theta float,
+ corr01 float,
+ duration float,
+ spread float,
+ expected_loss float,
+ quote_price float,
+ calibrated_price float);
+
+CREATE OR REPLACE VIEW tranche_risk AS (
+ SELECT t1.id,
+ t1.tranche_id,
+ t2.price AS index_price,
+ t2.basis AS index_basis,
+ t2.expected_loss AS index_expected_loss,
+ t2.duration AS index_duration,
+ t2.theta AS index_theta,
+ t1.corr_at_detach,
+ t1.delta,
+ t1.fwd_delta,
+ t1.gamma,
+ t1.theta,
+ t1.corr01,
+ t1.duration,
+ t1.spread,
+ t1.expected_loss,
+ t1.quote_price,
+ t1.calibrated_price
+ FROM tranche_risk_tranches t1
+ LEFT JOIN tranche_risk_index t2 USING (quoteset)
+);
+
+
CREATE TABLE markit_tranche_risk(
id integer GENERATED BY DEFAULT PRIMARY KEY,
tranche_id integer UNIQUE REFERENCES markit_tranche_quotes(id) ON DELETE CASCADE,
|