diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/serenitasdb.sql | 59 |
1 files changed, 59 insertions, 0 deletions
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index da7a3b41..2660eab1 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -134,6 +134,65 @@ GRANT ALL ON tranche_quotes TO serenitas_user; -- 162308 U+500
-- All other tranches should have a unique trancheid for a given basketid, attach, detach combination
+CREATE TYPE entitytype AS ENUM('Corp', 'Sov', 'State', 'StatBody', 'Supra', 'Insurer',
+ 'Monoline', 'Index', 'Muni');
+CREATE TYPE depthlevel AS ENUM('high', 'med', 'low', 'High', 'Med', 'Low', 'HIGH', 'MED', 'LOW');
+
+CREATE TABLE IF NOT EXISTS RefEntity(
+ referenceentity text NOT NULL,
+ shortname text NOT NULL,
+ ticker text NOT NULL,
+ redentitycode varchar(6) NOT NULL PRIMARY KEY,
+ entitycusip text NOT NULL,
+ lei text,
+ entitytype text NOT NULL,
+ jurisdiction text NOT NULL,
+ depthlevel depthlevel,
+ markitsector text,
+ isdatradingdefinition text,
+ recorddate date,
+ ratings text[],
+ entityform text,
+ companynumber jsonb,
+ alternativenames text,
+ isdatransactiontypes jsonb,
+ validto date,
+ validfrom date,
+ events jsonb);
+
+CREATE TABLE IF NOT EXISTS RefObligation(
+ id serial PRIMARY KEY,
+ obligationname text NOT NULL,
+ prospectusinfo jsonb,
+ refentities text[],
+ type text NOT NULL,
+ isconvert bool NOT NULL,
+ isperp bool NOT NULL,
+ coupontype text NOT NULL,
+ ccy varchar(3) NOT NULL,
+ maturity date,
+ issuedate date,
+ coupon numeric(8,6) NOT NULL,
+ isin varchar(12) NOT NULL,
+ cusip varchar(9),
+ event text);
+
+CREATE TABLE IF NOT EXISTS RedPairMapping(
+ redpaircode varchar(9) PRIMARY KEY,
+ role text NOT NULL,
+ referenceentity text NOT NULL,
+ redentitycode text NOT NULL REFERENCES RefEntity,
+ tier text NOT NULL,
+ pairiscurrent boolean,
+ pairvalidfrom date,
+ pairvalidto date,
+ ticker text NOT NULL,
+ ispreferred boolean,
+ preferreddate date,
+ indexconstituents text[],
+ recorddate date NOT NULL,
+ publiccomments text,
+ myticker text);
CREATE TYPE curr AS ENUM('USD', 'EUR', 'JPY', 'GBP', 'CAD');
CREATE TYPE sen AS ENUM('Senior', 'Subordinated');
|