1 files changed, 41 insertions, 1 deletions
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql
index 4a024f2e..2722d877 100644
--- a/sql/serenitasdb.sql
+++ b/sql/serenitasdb.sql
@@ -134,7 +134,8 @@ CREATE TABLE quotes (
        QuoteSource varchar(4)
 );
 
-CREATE TABLE tranche_quotes (
+-- deprecated, renamed to tranche_quotes_prev 2023-05-17
+CREATE TABLE tranche_quotes_prev (
        id serial PRIMARY KEY,
        QuoteDate timestamptz,
        Index index_type,
@@ -162,6 +163,36 @@ CREATE TABLE tranche_quotes (
        UNIQUE (QuoteDate, Index, Series, Version, Tenor, Attach, Detach, QuoteSource, trancheupfrontmid)
 );
 
+CREATE TABLE tranche_quotes_ref(
+    quoteset integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
+    quotedate timestamptz,
+    "index" index_type,
+    series SMALLINT,
+    "version" SMALLINT,
+    tenor "tenor",
+    ref_price numeric(7, 4),
+    ref_spread SMALLINT,
+    quotesource varchar(4),
+    UNIQUE (quotedate, "index", series, "version", tenor, quotesource));
+
+CREATE TABLE tranche_quotes_tranches(
+	id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+	quoteset integer NOT NULL REFERENCES tranche_quotes_ref ON DELETE CASCADE,
+	attach SMALLINT NOT NULL,
+	detach SMALLINT NOT NULL,
+	upfront_bid float,
+    upfront_mid float,
+    upfront_ask float,
+    running_bid float,
+    running_mid float,
+    running_ask float,
+	delta numeric(5, 3),
+    corr_at_detachment REAL,
+    markit_id integer UNIQUE,
+    deleted bool NOT NULL DEFAULT FALSE);
+
+CREATE INDEX ON tranche_quotes_tranches USING hash(quoteset);
+
 CREATE TABLE tranche_skew
 (id integer GENERATED ALWAYS AS IDENTITY,
  date date NOT NULL,
@@ -174,6 +205,15 @@ CREATE TABLE tranche_skew
  c bytea NOT NULL,
  UNIQUE (date, INDEX, series, VERSION, tenor));
 
+CREATE OR REPLACE VIEW tranche_quotes AS (
+SELECT id, quoteset, quotedate, index, series, version, tenor, ATTACH, DETACH, upfront_bid AS trancheupfrontbid, upfront_mid AS trancheupfrontmid,
+upfront_ask AS trancheupfrontask, running_bid AS trancherunningbid,
+running_mid AS trancherunningmid, running_ask AS trancherunningask,
+ref_price AS indexrefprice, ref_spread AS indexrefspread,
+corr_at_detachment AS corratdetachment,
+markit_id, quotesource, deleted FROM tranche_quotes_ref LEFT JOIN tranche_quotes_tranches USING (quoteset));
+
+
 -- partial index constraint, didn't clean the db further back
 CREATE UNIQUE INDEX
 ON tranche_quotes(quotedate, index, series, version, tenor, attach, detach, quotesource, trancherunningmid)