Created
February 16, 2018 18:10
-
-
Save faisalmahmud/9faef050a09c1faa658522c99bd4f700 to your computer and use it in GitHub Desktop.
Initial table and column creation script for MDA
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE DATABASE millistream; | |
| \c millistream | |
| CREATE TABLE corporateactions | |
| ( | |
| "id" SERIAL, | |
| "insref" numeric(20) NOT NULL, | |
| "type" smallint NOT NULL, | |
| "subtype" smallint, | |
| "dividend" double precision, | |
| "netdividend" double precision, | |
| "adjustmentfactor" double precision, | |
| "numberofshares" numeric(20), | |
| "numberofprefshares" numeric(20), | |
| "numberofsharesdelta" numeric(20), | |
| "newshares" double precision, | |
| "oldshares" double precision, | |
| "subscriptionprice" double precision, | |
| "redemptionprice" double precision, | |
| "period" character varying(19), | |
| "fiscalperiod" character varying(19), | |
| "nominalvalue" double precision, | |
| "recorddate" date, | |
| "paymentdate" date, | |
| "announcementdate" date, | |
| "tid" double precision, | |
| "closeprice" double precision, | |
| "issuecurrency" character(3), | |
| "tradecurrency" character(3), | |
| "freetextcomment1" character varying(255), | |
| "freetextcomment2" character varying(255), | |
| "freetextcomment3" character varying(255), | |
| "freetextcomment4" character varying(255), | |
| "freetextcomment5" character varying(255), | |
| "source" character(15) NOT NULL default 'Millistream', | |
| "underlyingid" character varying(255), | |
| "symbol" character varying(255), | |
| "name" character varying(255), | |
| "isin" character(12), | |
| "boardlot" integer, | |
| "marketplace" numeric(20), | |
| "list" character varying(255), | |
| "votingpowerprc" double precision, | |
| "capitalprc" double precision, | |
| "mcap" double precision, | |
| "eventlink" character(36), | |
| "eventlinklanguages" character varying(255), | |
| "date" date NOT NULL, | |
| "time" time without time zone NOT NULL, | |
| CONSTRAINT corporateactions_pkey PRIMARY KEY ("insref","date","time") | |
| ); | |
| CREATE INDEX idx_corporateactionsindex1 ON corporateactions ("insref","type","date"); | |
| CREATE INDEX idx_corporateactionsindex2 ON corporateactions ("insref","type","subtype","date"); | |
| CREATE INDEX idx_corporateactionsindex3 ON corporateactions ("insref","date"); | |
| CREATE TABLE instruments | |
| ( | |
| "id" SERIAL, | |
| "insref" numeric(20) NOT NULL, | |
| "name" character varying(255), | |
| "symbol" character varying(255), | |
| "isin" character(12), | |
| "boardlot" integer, | |
| "instrumenttype" smallint, | |
| "instrumentsubtype" smallint, | |
| "instrumentclass" smallint, | |
| "instrumentsubclass" character varying(255), | |
| "instrumentsubsubclass" character varying(255), | |
| "derivativeindicator" smallint, | |
| "exercisetype" smallint, | |
| "issuecurrency" character(3), | |
| "tradecurrency" character(3), | |
| "basecurrency" character(3), | |
| "quotecurrency" character(3), | |
| "issuedate" date, | |
| "strikedate" date, | |
| "strikeprice" double precision, | |
| "underlyingid" character varying(255), | |
| "marketplace" numeric(20), | |
| "primarymarketplace" numeric(20), | |
| "list" character varying(255), | |
| "company" numeric(20), | |
| "fundppmcode" integer, | |
| "fundpmicode" character varying(255), | |
| "fundcompany" numeric(20), | |
| "country" character(2), | |
| "freetextcomment" character varying(255), | |
| "numberofshares" numeric(20), | |
| "numberofprefshares" numeric(20), | |
| "nominalvalue" double precision, | |
| "mic" character(5), | |
| "sector" numeric(20), | |
| "tradestate" smallint, | |
| "chairman" character varying(255), | |
| "ceo" character varying(255), | |
| "website" character varying(255), | |
| "orgnum" character varying(20), | |
| "cik" numeric(10), | |
| "shortdescription" character varying(512), | |
| "description" text, | |
| "genderceo" character(1), | |
| "genderchairman" character(1), | |
| "birthyearceo" smallint, | |
| "birthyearchairman" smallint, | |
| "address" character varying(255), | |
| "postalcode" character varying(255), | |
| "city" character varying(255), | |
| "telephone" character varying(255), | |
| "fax" character varying(255), | |
| "email" character varying(255), | |
| "importantevents" text, | |
| "contractsize" double precision, | |
| "baseratio" double precision, | |
| "sourceid" character varying(255), | |
| "issuer" character varying(255), | |
| "issuername" character varying(255), | |
| "ongoingcharge" double precision, | |
| "fundyearlymgmtfee" double precision, | |
| "fundppmfee" double precision, | |
| "fundleverage" double precision, | |
| "funddirection" character varying(255), | |
| "fundbenchmark" character varying(255), | |
| "fundbenchmarkinsref" numeric(20), | |
| "fundppmtype" character varying(255), | |
| "fundrisk" smallint, | |
| "prospectus" text, | |
| "geofocusregion" character varying(255), | |
| "geofocuscountry" character varying(255), | |
| "openinterest" double precision, | |
| "geniumid" integer, | |
| "cusip" character varying(9), | |
| "wkn" character varying(6), | |
| "ucits" smallint, | |
| "inceptiondate" date, | |
| "barrierprice" double precision, | |
| "ceoadmissiondate" date, | |
| "chairmanadmissiondate" date, | |
| "coupondate" date, | |
| "couponrate" double precision, | |
| "fundmcap" double precision, | |
| "fundminadditionalamount" double precision, | |
| "fundminstartamount" double precision, | |
| "fundminsubscriptionamount" double precision, | |
| "fundperformancefee" double precision, | |
| "purchasefee" double precision, | |
| "salesfee" double precision, | |
| "totalfee" double precision, | |
| "morningstarrating" smallint, | |
| "tradedthroughdate" date, | |
| "dividendtype" smallint, | |
| "dividendfrequency" smallint, | |
| "pricingfrequency" smallint, | |
| "eusipa" integer, | |
| "convertfromdate" date, | |
| "converttodate" date, | |
| "conversionprice" double precision, | |
| "specialcondition" integer, | |
| "settlementtype" smallint, | |
| "votingpower" double precision, | |
| "cap" double precision, | |
| "pricetype" smallint, | |
| "volumedimension" smallint, | |
| "quotingtype" smallint, | |
| "asiantailstart" date, | |
| "asiantailend" date, | |
| "logotype" text, | |
| "ticktable" numeric(20), | |
| "contractvalue" double precision, | |
| "financinglevel" double precision, | |
| "unitlinkcompany" character varying(255), | |
| "participationrate" double precision, | |
| "issueprice" double precision, | |
| "fiinstitutenumber" numeric(20), | |
| "kiid" text, | |
| "cfi" character(6), | |
| "maxlevel" double precision, | |
| "outstandingamount" double precision, | |
| "interestrate" double precision, | |
| "marketmaker" character varying(255), | |
| "marketopendays" integer, | |
| "marketopen" time without time zone, | |
| "marketclose" time without time zone, | |
| "marketearlyclose" time without time zone, | |
| "legalstructure" character varying(255), | |
| "productcode" character varying(255), | |
| "latestyearendreport" character varying(4), | |
| "csr" double precision, | |
| "time" time without time zone, | |
| "date" date, | |
| CONSTRAINT instruments_pk PRIMARY KEY ("insref") | |
| ); | |
| CREATE TABLE listmappings | |
| ( | |
| "id" SERIAL, | |
| "list" numeric(20) NOT NULL, | |
| "insref" numeric(20) NOT NULL, | |
| CONSTRAINT listmappings_pk PRIMARY KEY ("list","insref") | |
| ); | |
| CREATE INDEX idx_listmappings ON listmappings ("insref"); | |
| CREATE TABLE news | |
| ( | |
| "id" SERIAL, | |
| "newsref" numeric(20) NOT NULL, | |
| "headline" character varying(255) NOT NULL, | |
| "text" text, | |
| "newstype" smallint, | |
| "subject" smallint, | |
| "regulatory" smallint, | |
| "language" character(2), | |
| "newsid" character(36) NOT NULL, | |
| "originalcode" character varying(255), | |
| "isin" character varying(255), | |
| "company" character varying(255), | |
| "rank" smallint, | |
| "time" time without time zone NOT NULL, | |
| "date" date NOT NULL, | |
| CONSTRAINT news_pk PRIMARY KEY ("newsid") | |
| ); | |
| CREATE INDEX idx_news ON news ("newsref","date"); | |
| CREATE TABLE orderbooks | |
| ( | |
| "id" SERIAL, | |
| "insref" numeric(20) NOT NULL, | |
| "price" double precision NOT NULL, | |
| "quantity" double precision, | |
| "side" character(1) NOT NULL, | |
| "numorders" smallint, | |
| "counterparties" character varying(255), | |
| "time" time without time zone NOT NULL, | |
| "date" date NOT NULL, | |
| CONSTRAINT orderbook_pk PRIMARY KEY ("insref","price","side") | |
| ); | |
| CREATE TABLE pricehistory | |
| ( | |
| "id" SERIAL, | |
| "insref" numeric(20) NOT NULL, | |
| "type" smallint, | |
| "closebidprice" double precision, | |
| "closeaskprice" double precision, | |
| "closetradeprice" double precision, | |
| "closeprice" double precision, | |
| "closedayhighprice" double precision, | |
| "closedaylowprice" double precision, | |
| "closequantity" numeric(20,0), | |
| "closeinternalquantity" numeric(20,0), | |
| "closeoffbookquantity" numeric(20,0), | |
| "closedarkquantity" numeric(20,0), | |
| "closeturnover" numeric(38,7), | |
| "closeinternalturnover" numeric(38,7), | |
| "closeoffbookturnover" numeric(38,7), | |
| "closedarkturnover" numeric(38,7), | |
| "openprice" double precision, | |
| "numtrades" numeric(20), | |
| "openyield" double precision, | |
| "closebidyield" double precision, | |
| "closeaskyield" double precision, | |
| "closeyield" double precision, | |
| "closedayhighyield" double precision, | |
| "closedaylowyield" double precision, | |
| "closenav" double precision, | |
| "closetis" double precision, | |
| "closevwap" double precision, | |
| "mcap" numeric(38,7), | |
| "settlementprice" double precision, | |
| "time" time without time zone NOT NULL, | |
| "date" date NOT NULL, | |
| CONSTRAINT pricehistory_pk PRIMARY KEY ("insref", "date") | |
| ); | |
| CREATE TABLE adjustedpricehistory | |
| ( | |
| "id" SERIAL, | |
| "insref" numeric(20) NOT NULL, | |
| "type" smallint, | |
| "closebidprice" double precision, | |
| "closeaskprice" double precision, | |
| "closetradeprice" double precision, | |
| "closeprice" double precision, | |
| "closedayhighprice" double precision, | |
| "closedaylowprice" double precision, | |
| "closequantity" numeric(20,0), | |
| "closeinternalquantity" numeric(20,0), | |
| "closeoffbookquantity" numeric(20,0), | |
| "closedarkquantity" numeric(20,0), | |
| "closeturnover" numeric(38,7), | |
| "closeinternalturnover" numeric(38,7), | |
| "closeoffbookturnover" numeric(38,7), | |
| "closedarkturnover" numeric(38,7), | |
| "openprice" double precision, | |
| "numtrades" numeric(20), | |
| "openyield" double precision, | |
| "closebidyield" double precision, | |
| "closeaskyield" double precision, | |
| "closeyield" double precision, | |
| "closedayhighyield" double precision, | |
| "closedaylowyield" double precision, | |
| "closenav" double precision, | |
| "closetis" double precision, | |
| "closevwap" double precision, | |
| "settlementprice" double precision, | |
| "mcap" numeric(38,7), | |
| "time" time without time zone NOT NULL, | |
| "date" date NOT NULL, | |
| CONSTRAINT adjustedpricehistory_pk PRIMARY KEY ("insref", "date") | |
| ); | |
| CREATE TABLE quotes | |
| ( | |
| "id" SERIAL, | |
| "insref" numeric(20) NOT NULL, | |
| "bidprice" double precision, | |
| "bidquantity" double precision, | |
| "askprice" double precision, | |
| "askquantity" double precision, | |
| "lastprice" double precision, | |
| "dayhighprice" double precision, | |
| "daylowprice" double precision, | |
| "quantity" numeric(20,0), | |
| "internalquantity" numeric(20,0), | |
| "offbookquantity" numeric(20,0), | |
| "darkquantity" numeric(20,0), | |
| "turnover" numeric(38,7), | |
| "internalturnover" numeric(38,7), | |
| "offbookturnover" numeric(38,7), | |
| "darkturnover" numeric(38,7), | |
| "numtrades" numeric(20,0), | |
| "openprice" double precision, | |
| "bidyield" double precision, | |
| "askyield" double precision, | |
| "lastyield" double precision, | |
| "openyield" double precision, | |
| "dayhighyield" double precision, | |
| "daylowyield" double precision, | |
| "nav" double precision, | |
| "tis" double precision, | |
| "unchangedpaid" integer, | |
| "pluspaid" integer, | |
| "minuspaid" integer, | |
| "vwap" double precision, | |
| "duration" double precision, | |
| "time" time without time zone, | |
| "date" date, | |
| CONSTRAINT quotes_pk PRIMARY KEY ("insref") | |
| ); | |
| CREATE TABLE stats | |
| ( | |
| "id" SERIAL, | |
| "name" character varying(255) NOT NULL, | |
| "value" character varying(255) NOT NULL, | |
| CONSTRAINT stats_pk PRIMARY KEY ("name") | |
| ); | |
| CREATE TABLE trades | |
| ( | |
| "id" SERIAL, | |
| "insref" numeric(20) NOT NULL, | |
| "tradeprice" double precision, | |
| "tradeyield" double precision, | |
| "tradequantity" double precision, | |
| "tradereference" character varying(255) NOT NULL, | |
| "tradecode" integer NOT NULL, | |
| "mmt" character(14), | |
| "tradetype" character varying(10), | |
| "buyer" character varying(64), | |
| "seller" character varying(64), | |
| "executedside" character(1), | |
| "canceltime" time without time zone, | |
| "agreementtime" time without time zone, | |
| "agreementdate" date, | |
| "executionvenue" character varying(255), | |
| "currency" character varying(3), | |
| "time" time without time zone NOT NULL, | |
| "date" date NOT NULL, | |
| CONSTRAINT trades_pk PRIMARY KEY ("insref","date","tradereference") | |
| ); | |
| CREATE TABLE fundamentals ( | |
| "id" SERIAL, | |
| "insref" numeric(20) NOT NULL, | |
| "sales" double precision, | |
| "ebit" double precision, | |
| "ebita" double precision, | |
| "ebitda" double precision, | |
| "financialincome" double precision, | |
| "financialcost" double precision, | |
| "ptp" double precision, | |
| "np" double precision, | |
| "eps" double precision, | |
| "dilutedeps" double precision, | |
| "netfinancialincome" double precision, | |
| "equityratio" double precision, | |
| "numberofshares" double precision, | |
| "operatingcashflow" double precision, | |
| "gp" double precision, | |
| "intangibleasset" double precision, | |
| "goodwill" double precision, | |
| "fixedasset" double precision, | |
| "financialasset" double precision, | |
| "noncurrentasset" double precision, | |
| "inventory" double precision, | |
| "othercurrentasset" double precision, | |
| "accountsreceivable" double precision, | |
| "otherreceivables" double precision, | |
| "shortterminv" double precision, | |
| "cce" double precision, | |
| "currentassets" double precision, | |
| "totalassets" double precision, | |
| "shequity" double precision, | |
| "minorityinterest" double precision, | |
| "provisions" double precision, | |
| "ltliabilities" double precision, | |
| "curliabilities" double precision, | |
| "totsheqliabilities" double precision, | |
| "nibl" double precision, | |
| "ibl" double precision, | |
| "cashflowbwc" double precision, | |
| "cashflowawc" double precision, | |
| "cashflowia" double precision, | |
| "cashflowfa" double precision, | |
| "cashflowtotal" double precision, | |
| "interestincome" double precision, | |
| "otherfinancialincome" double precision, | |
| "interestexpense" double precision, | |
| "otherfinancialexpense" double precision, | |
| "minorityinterestres" double precision, | |
| "accountspayable" double precision, | |
| "eventlink" character(36), | |
| "eventlinklanguages" character varying(255), | |
| "numemployees" double precision, | |
| "dividend" double precision, | |
| "period" character varying(7) NOT NULL, | |
| "fiscalperiod" character(19), | |
| "currency" character(3) NOT NULL, | |
| "adjustmentfactor" double precision, | |
| "source" character(15) NOT NULL default 'Millistream', | |
| "date" date NOT NULL, | |
| CONSTRAINT fundamentals_pk PRIMARY KEY ("insref","period","currency","source") | |
| ); | |
| CREATE INDEX idx_fundamentals ON fundamentals ("insref","period"); | |
| CREATE TABLE estimates ( | |
| "id" SERIAL, | |
| "insref" numeric(20) NOT NULL, | |
| "period" character varying(7) NOT NULL, | |
| "field" character varying(30) NOT NULL, | |
| "aspect" character varying(10) NOT NULL, | |
| "type" character varying(20) NOT NULL, | |
| "unit" character varying(255), | |
| "average" double precision, | |
| "min" double precision, | |
| "max" double precision, | |
| "count" integer, | |
| "currency" character(3) NOT NULL, | |
| "source" character(15) NOT NULL, | |
| "date" date NOT NULL, | |
| "time" time NOT NULL, | |
| CONSTRAINT estimates_pk PRIMARY KEY ("insref","period","field","aspect","type","currency","source") | |
| ); | |
| CREATE TABLE estimateshistory ( | |
| "id" SERIAL, | |
| "insref" numeric(20) NOT NULL, | |
| "period" character varying(7) NOT NULL, | |
| "field" character varying(30) NOT NULL, | |
| "aspect" character varying(10) NOT NULL, | |
| "type" character varying(20) NOT NULL, | |
| "unit" character varying(255), | |
| "average" double precision, | |
| "min" double precision, | |
| "max" double precision, | |
| "count" integer, | |
| "currency" character(3) NOT NULL, | |
| "source" character(15) NOT NULL, | |
| "date" date NOT NULL, | |
| "time" time NOT NULL, | |
| CONSTRAINT estimateshistory_pk PRIMARY KEY ("insref","date","period","field","aspect","type","currency","source") | |
| ); | |
| CREATE TABLE performance ( | |
| "id" SERIAL, | |
| "insref" numeric(20) NOT NULL, | |
| "closebidprice1d" double precision, | |
| "closebidprice1w" double precision, | |
| "closeprice1d" double precision, | |
| "closeprice1w" double precision, | |
| "closeprice2w" double precision, | |
| "closeprice1m" double precision, | |
| "closeprice3m" double precision, | |
| "closeprice6m" double precision, | |
| "closeprice9m" double precision, | |
| "closeprice1y" double precision, | |
| "closeprice2y" double precision, | |
| "closeprice3y" double precision, | |
| "closeprice5y" double precision, | |
| "closeprice10y" double precision, | |
| "closepricewtd" double precision, | |
| "closepricemtd" double precision, | |
| "closepriceqtd" double precision, | |
| "closepriceytd" double precision, | |
| "closepricepytd" double precision, | |
| "closepriceld" double precision, | |
| "closebidyield1d" double precision, | |
| "closebidyield1w" double precision, | |
| "closeyield1d" double precision, | |
| "closeyield1w" double precision, | |
| "closeyield2w" double precision, | |
| "closeyield1m" double precision, | |
| "closeyield3m" double precision, | |
| "closeyield6m" double precision, | |
| "closeyield9m" double precision, | |
| "closeyield1y" double precision, | |
| "closeyield2y" double precision, | |
| "closeyield3y" double precision, | |
| "closeyield5y" double precision, | |
| "closeyield10y" double precision, | |
| "closeyieldwtd" double precision, | |
| "closeyieldmtd" double precision, | |
| "closeyieldqtd" double precision, | |
| "closeyieldytd" double precision, | |
| "closeyieldpytd" double precision, | |
| "closeyieldld" double precision, | |
| "ath" double precision, | |
| "atl" double precision, | |
| "highprice1y" double precision, | |
| "lowprice1y" double precision, | |
| "highpriceytd" double precision, | |
| "lowpriceytd" double precision, | |
| "athdate" date, | |
| "atldate" date, | |
| "highprice1ydate" date, | |
| "lowprice1ydate" date, | |
| "highpriceytddate" date, | |
| "lowpriceytddate" date, | |
| "athyield" double precision, | |
| "atlyield" double precision, | |
| "highyield1y" double precision, | |
| "lowyield1y" double precision, | |
| "highyieldytd" double precision, | |
| "lowyieldytd" double precision, | |
| "athyielddate" date, | |
| "atlyielddate" date, | |
| "highyield1ydate" date, | |
| "lowyield1ydate" date, | |
| "highyieldytddate" date, | |
| "lowyieldytddate" date, | |
| "diffbid1d" double precision, | |
| "diffbid1w" double precision, | |
| "diff1d" double precision, | |
| "diff1w" double precision, | |
| "diff2w" double precision, | |
| "diff1m" double precision, | |
| "diff3m" double precision, | |
| "diff6m" double precision, | |
| "diff9m" double precision, | |
| "diff1y" double precision, | |
| "diff2y" double precision, | |
| "diff3y" double precision, | |
| "diff5y" double precision, | |
| "diff10y" double precision, | |
| "diffld" double precision, | |
| "diffwtd" double precision, | |
| "diffmtd" double precision, | |
| "diffqtd" double precision, | |
| "diffytd" double precision, | |
| "diffpytd" double precision, | |
| "diffbid1dprc" double precision, | |
| "diffbid1wprc" double precision, | |
| "diff1dprc" double precision, | |
| "diff1wprc" double precision, | |
| "diff2wprc" double precision, | |
| "diff1mprc" double precision, | |
| "diff3mprc" double precision, | |
| "diff6mprc" double precision, | |
| "diff9mprc" double precision, | |
| "diff1yprc" double precision, | |
| "diff2yprc" double precision, | |
| "diff3yprc" double precision, | |
| "diff5yprc" double precision, | |
| "diff10yprc" double precision, | |
| "diffldprc" double precision, | |
| "diffwtdprc" double precision, | |
| "diffmtdprc" double precision, | |
| "diffqtdprc" double precision, | |
| "diffytdprc" double precision, | |
| "diffpytdprc" double precision, | |
| "diffbidyield1d" double precision, | |
| "diffbidyield1w" double precision, | |
| "diffyield1d" double precision, | |
| "diffyield1w" double precision, | |
| "diffyield2w" double precision, | |
| "diffyield1m" double precision, | |
| "diffyield3m" double precision, | |
| "diffyield6m" double precision, | |
| "diffyield9m" double precision, | |
| "diffyield1y" double precision, | |
| "diffyield2y" double precision, | |
| "diffyield3y" double precision, | |
| "diffyield5y" double precision, | |
| "diffyield10y" double precision, | |
| "diffyieldld" double precision, | |
| "diffyieldwtd" double precision, | |
| "diffyieldmtd" double precision, | |
| "diffyieldqtd" double precision, | |
| "diffyieldytd" double precision, | |
| "diffyieldpytd" double precision, | |
| "diffbidyield1dprc" double precision, | |
| "diffbidyield1wprc" double precision, | |
| "diffyield1dprc" double precision, | |
| "diffyield1wprc" double precision, | |
| "diffyield2wprc" double precision, | |
| "diffyield1mprc" double precision, | |
| "diffyield3mprc" double precision, | |
| "diffyield6mprc" double precision, | |
| "diffyield9mprc" double precision, | |
| "diffyield1yprc" double precision, | |
| "diffyield2yprc" double precision, | |
| "diffyield3yprc" double precision, | |
| "diffyield5yprc" double precision, | |
| "diffyield10yprc" double precision, | |
| "diffyieldldprc" double precision, | |
| "diffyieldwtdprc" double precision, | |
| "diffyieldmtdprc" double precision, | |
| "diffyieldqtdprc" double precision, | |
| "diffyieldytdprc" double precision, | |
| "diffyieldpytdprc" double precision, | |
| "annualizedreturn1y" double precision, | |
| "annualizedreturn2y" double precision, | |
| "annualizedreturn3y" double precision, | |
| "annualizedreturn4y" double precision, | |
| "annualizedreturn5y" double precision, | |
| "annualizedreturn10y" double precision, | |
| "standarddeviation3y" double precision, | |
| "sharperatio3y" double precision, | |
| "normanamount" double precision, | |
| "time" time without time zone, | |
| "date" date, | |
| CONSTRAINT performance_pk PRIMARY KEY ("insref") | |
| ); | |
| CREATE TABLE keyratios ( | |
| "id" SERIAL, | |
| "insref" numeric(20) NOT NULL, | |
| "dividendyield" double precision, | |
| "per" double precision, | |
| "psr" double precision, | |
| "pricetocashflow" double precision, | |
| "pricetoadjustedequity" double precision, | |
| "time" time without time zone, | |
| "date" date, | |
| CONSTRAINT keyratios_pk PRIMARY KEY ("insref") | |
| ); | |
| CREATE TABLE newscoding ( | |
| "id" SERIAL, | |
| "newsref" numeric(20) NOT NULL, | |
| "newsid" character(36) NOT NULL, | |
| "isin" character(12) NOT NULL, | |
| "company" numeric(20) NOT NULL, | |
| "date" date, | |
| CONSTRAINT newscoding_pk PRIMARY KEY ("newsid","newsref","isin","company") | |
| ); | |
| CREATE INDEX idx_newscoding ON newscoding ("isin","newsid"); | |
| CREATE INDEX idx_newscoding2 ON newscoding ("company","newsid"); | |
| CREATE TABLE noii ( | |
| "id" SERIAL, | |
| "insref" numeric(20) NOT NULL, | |
| "bidprice" double precision, | |
| "askprice" double precision, | |
| "bidquantity" double precision, | |
| "askquantity" double precision, | |
| "lastprice" double precision, | |
| "quantity" double precision, | |
| "imbalance" double precision, | |
| "direction" smallint NULL, | |
| "type" smallint NULL, | |
| "time" time without time zone, | |
| "date" date, | |
| CONSTRAINT noii_pk PRIMARY KEY ("insref") | |
| ); | |
| CREATE TABLE constituents ( | |
| "id" SERIAL, | |
| "insref" numeric(20) NOT NULL, | |
| "constituent" numeric(20) NOT NULL, | |
| "weight" double precision, | |
| CONSTRAINT constituents_pk PRIMARY KEY ("insref","constituent") | |
| ); | |
| CREATE INDEX idx_constituents ON constituents ("constituent"); | |
| CREATE TABLE ticksize ( | |
| "id" SERIAL, | |
| "insref" numeric(20) NOT NULL, | |
| "threshold" double precision NOT NULL, | |
| "increment" double precision NOT NULL, | |
| CONSTRAINT ticksize_pk PRIMARY KEY ("insref","threshold") | |
| ); | |
| CREATE TABLE l10n ( | |
| "id" SERIAL, | |
| "insref" numeric(20) NOT NULL, | |
| "language" character(2) NOT NULL, | |
| "name" character varying(255), | |
| "shortdescription" character varying(512), | |
| "description" text, | |
| "importantevents" text, | |
| "prospectus" text, | |
| "kiid" text, | |
| "sustaindescription" text, | |
| "sustaininvesting1" text, | |
| "sustaininvesting2" text, | |
| "sustaindivesting1" text, | |
| "sustaindivesting2" text, | |
| "sustaininfluence" text, | |
| "sustainmonitoring1" text, | |
| "sustainmonitoring2" text, | |
| "sustaincomment" text, | |
| "Comprehension_Alert_Portfolio" character(1), | |
| "Intended_target_market_retail_investor_Portfolio" character varying(750), | |
| "Investment_objective_Portfolio" character varying(2500), | |
| "Risk_narrative_Portfolio" character varying(300), | |
| "Other_materially_relevant_risk_narrative_Portfolio" character varying(200), | |
| "Type_of_underlying_Investment_Option" character varying(300), | |
| "Capital_Guarantee" character(1), | |
| "Capital_Guarantee_Level" double precision, | |
| "Capital_Guarantee_Limitations" character varying(300), | |
| "Capital_Guarantee_Early_Exit_Conditions" date, | |
| "Capital_guarantee_Portfolio" character varying(2500), | |
| "Possible_maximum_loss_Portfolio" double precision, | |
| "Portfolio_Performance_Fees_Narrative" character varying(300), | |
| "Portolio_Carried_Interest_Narrative" character varying(300), | |
| "Other_comment" text default NULL, | |
| "date" date, | |
| CONSTRAINT l10n_pk PRIMARY KEY ("insref","language") | |
| ); | |
| CREATE TABLE brokers ( | |
| "id" SERIAL, | |
| "insref" numeric(20) NOT NULL, | |
| "symbol" character varying(255) NOT NULL, | |
| "name" character varying(255) NOT NULL, | |
| CONSTRAINT brokers_pk PRIMARY KEY ("insref","symbol") | |
| ); | |
| CREATE TABLE ci ( | |
| "id" SERIAL, | |
| "insref" numeric(20) NOT NULL, | |
| "type" smallint NOT NULL, | |
| "subtype" smallint, | |
| "sequence" integer NOT NULL, | |
| "s1" character varying(255), | |
| "s2" character varying(255), | |
| "s3" character varying(255), | |
| "s4" character varying(255), | |
| "s5" character varying(255), | |
| "s6" character varying(255), | |
| "s7" character varying(255), | |
| "s8" character varying(255), | |
| "s9" character varying(255), | |
| "s10" character varying(255), | |
| "s11" character varying(255), | |
| "n1" double precision, | |
| "n2" double precision, | |
| "n3" double precision, | |
| "n4" double precision, | |
| "n5" double precision, | |
| "i1" integer, | |
| "i2" integer, | |
| "i3" integer, | |
| "i4" integer, | |
| "i5" integer, | |
| "d1" date, | |
| "d2" date, | |
| "d3" date, | |
| CONSTRAINT ci_pk PRIMARY KEY ("insref","type","sequence") | |
| ); | |
| CREATE TABLE cihistory ( | |
| "id" SERIAL, | |
| "insref" numeric(20) NOT NULL, | |
| "type" smallint NOT NULL, | |
| "subtype" smallint, | |
| "sequence" integer NOT NULL, | |
| "date" date NOT NULL, | |
| "s1" character varying(255), | |
| "s2" character varying(255), | |
| "s3" character varying(255), | |
| "s4" character varying(255), | |
| "s5" character varying(255), | |
| "s6" character varying(255), | |
| "s7" character varying(255), | |
| "s8" character varying(255), | |
| "s9" character varying(255), | |
| "s10" character varying(255), | |
| "s11" character varying(255), | |
| "n1" double precision, | |
| "n2" double precision, | |
| "n3" double precision, | |
| "n4" double precision, | |
| "n5" double precision, | |
| "i1" integer, | |
| "i2" integer, | |
| "i3" integer, | |
| "i4" integer, | |
| "i5" integer, | |
| "d1" date, | |
| "d2" date, | |
| "d3" date, | |
| CONSTRAINT cihistory_pk PRIMARY KEY ("insref","type","sequence","date") | |
| ); | |
| CREATE TABLE "priip" ( | |
| id SERIAL, | |
| insref numeric(20) NOT NULL, | |
| Portfolio_Issuer_Name character varying(255), | |
| Portfolio_Guarantor_Name character varying(255), | |
| Portfolio_Identifying_Data character varying(255), | |
| Type_Of_Identification_Code_For_The_Fund_Share_Or_Portfolio smallint, | |
| Portfolio_Name character varying(255), | |
| Share_Class_Currency character(3), | |
| Reference_Date date, | |
| Portfolio_PRIIPS_Category smallint, | |
| Fund_CIC_code character(4), | |
| EOS_portfolio character(1), | |
| Valuation_Frequency smallint, | |
| Portfolio_VEV_Reference double precision, | |
| IS_Flexible character(1), | |
| Flex_VEV_Historical double precision, | |
| Flex_VEV_Ref_Asset_Allocation double precision, | |
| IS_Risk_Limit_Relevant character(1), | |
| Flex_VEV_Risk_Limit double precision, | |
| Existing_Credit_Risk character(1), | |
| SRI smallint, | |
| MRM smallint, | |
| CRM smallint, | |
| Recommended_Holding_Period double precision, | |
| Maturity_Date date, | |
| Liquidity_Risk character(1), | |
| Portfolio_return_unfavorable_scenario_1_year double precision, | |
| Portfolio_return_unfavorable_scenario_half_RHP double precision, | |
| Portfolio_return_unfavorable_scenario_RHP double precision, | |
| Portfolio_return_moderate_scenario_1_year double precision, | |
| Portfolio_return_moderate_scenario_half_RHP double precision, | |
| Portfolio_return_moderate_scenario_RHP double precision, | |
| Portfolio_return_favorable_scenario_1_year double precision, | |
| Portfolio_return_favorable_scenario_half_RHP double precision, | |
| Portfolio_return double precision, | |
| Portfolio_return_stress_scenario_1_year double precision, | |
| Portfolio_return_stress_scenario_half_RHP double precision, | |
| Portfolio_return_stress_scenario_RHP double precision, | |
| Portfolio_number_of_observed_return_M0 double precision, | |
| Portfolio_mean_observed_returns_M1 double precision, | |
| Portfolio_observed_Sigma double precision, | |
| Portfolio_observed_Skewness double precision, | |
| Portfolio_observed_Excess_Kurtosis double precision, | |
| Portfolio_observed_Stressed_Volatility double precision, | |
| One_off_cost_Portfolio_entry_cost double precision, | |
| One_off_cost_Portfolio_entry_cost_Acquired double precision, | |
| One_off_costs_Portfolio_exit_cost_at_RHP double precision, | |
| One_off_costs_Portfolio_exit_cost_at_1_year double precision, | |
| One_off_costs_Portfolio_exit_cost_at_half_RHP double precision, | |
| One_off_costs_Portfolio_sliding_exit_cost_Indicator character(1), | |
| Ongoing_costs_Portfolio_other_costs double precision, | |
| Ongoing_costs_Portfolio_management_costs double precision, | |
| Ongoing_costs_Portfolio_transaction_costs double precision, | |
| Existing_performance_fees character(1), | |
| Incidental_costs_Portfolio_performance_fees double precision, | |
| Existing_carried_interest_fees character(1), | |
| Incidental_costs_Portfolio_carried_interest double precision, | |
| Reference_Language character(2), | |
| Comprehension_Alert_Portfolio character(1), | |
| Intended_target_market_retail_investor_Portfolio character varying(750), | |
| Investment_objective_Portfolio character varying(2500), | |
| Risk_narrative_Portfolio character varying(300), | |
| Other_materially_relevant_risk_narrative_Portfolio character varying(200), | |
| Type_of_underlying_Investment_Option character varying(300), | |
| Capital_Guarantee character(1), | |
| Capital_Guarantee_Level double precision, | |
| Capital_Guarantee_Limitations character varying(300), | |
| Capital_Guarantee_Early_Exit_Conditions date, | |
| Capital_guarantee_Portfolio character varying(2500), | |
| Possible_maximum_loss_Portfolio double precision, | |
| Portfolio_Performance_Fees_Narrative character varying(300), | |
| Portolio_Carried_Interest_Narrative character varying(300), | |
| Other_comment text default NULL, | |
| PRIIP_data_delivery character(1), | |
| UCITS_data_delivery character(1), | |
| Portfolio_UCITS_SRRI smallint, | |
| Portfolio_UCITS_Vol double precision, | |
| Ongoing_costs_Portfolio_other_costs_UCITS double precision, | |
| Ongoing_costs_Portfolio_transaction_costs_UCITS double precision, | |
| Transactions_costs_methodology character varying(255), | |
| Incidental_costs_Portfolio_performance_fees_UCITS double precision, | |
| Incidental_costs_Portfolio_carried_interest_UCITS double precision, | |
| UCITS_KID_Web_Address text default NULL, | |
| Bonds_Weight double precision, | |
| Annualized_Return_Volatility double precision, | |
| Duration_Bonds double precision, | |
| Existing_Capital_Preservation character(1), | |
| Capital_Preservation_Level double precision, | |
| Time_Interval_Maximum_Loss character varying(10), | |
| Uses_PI character(1), | |
| Multiplier_PI double precision, | |
| Total_cost_1_year double precision, | |
| RIY_1_year double precision, | |
| Total_cost_half_RHP double precision, | |
| RIY_half_RHP double precision, | |
| Total_cost_RHP double precision, | |
| RIY_RHP double precision, | |
| One_off_costs_Portfolio_entry_cost_RIY double precision, | |
| One_off_costs_Portfolio_exit_cost_RIY double precision, | |
| Ongoing_costs_Portfolio_transaction_costs_RIY double precision, | |
| Ongoing_costs_Other_ongoing_costs_RIY double precision, | |
| Incidental_costs_Portfolio_performance_fees_RIY double precision, | |
| Incidental_costs_Portfolio_carried_interests_RIY double precision, | |
| CONSTRAINT priip_pk PRIMARY KEY ("insref") | |
| ); | |
| CREATE TABLE "mifid" ( | |
| id SERIAL, | |
| insref numeric(20) NOT NULL, | |
| fisn character varying(35), | |
| lei character(20), | |
| Financial_Instrument_Identifying_Data character varying(255), | |
| Type_Of_Identification_Code_For_The_Financial_Instrument smallint, | |
| Financial_Instrument_Name character varying(255), | |
| Financial_Instrument_Currency character(3), | |
| Reporting_Date date, | |
| Financial_Instrument_Legal_Structure character varying(2), | |
| Financial_Instrument_Issuer_Name character varying(255), | |
| Financial_Instrument_Guarantor_Name character varying(255), | |
| Product_Category_or_Nature character varying(255), | |
| Leveraged_Financial_Instrument_or_Contingent_LiabilityInstrumen character(1), | |
| Investor_Type_Retail character(1), | |
| Investor_Type_Professional character(1), | |
| Investor_Type_Eligible_Counterparty character(1), | |
| Basic_Investor character(1), | |
| Informed_Investor character(1), | |
| Advanced_Investor character(1), | |
| Expert_Investor_Germany character(1), | |
| No_Capital_Loss character(1), | |
| Limited_Capital_Loss character(1), | |
| Limited_Capital_Loss_Level double precision, | |
| No_Capital_Guarantee character(1), | |
| Loss_Beyond_Capital character(1), | |
| Risk_Tolerance_PRIIPS_Methodology smallint, | |
| Risk_Tolerance_UCITS_Metholodology smallint, | |
| Risk_Tolerance_Internal_Methodology_For_Non_PRIIPS_and_NonUCITS character(1), | |
| Risk_Tolerance_For_Non_PRIIPS_and_Non_UCITS_Spain smallint, | |
| Not_For_Investors_With_The_Lowest_Risk_Tolerance_Germany character(1), | |
| Return_Profile_Preservation character(1), | |
| Return_Profile_Growth character(1), | |
| Return_Profile_Income character(1), | |
| Return_Profile_Hedging character(1), | |
| Option_or_Leveraged_Return_Profile character(1), | |
| Return_Profile_Other character(1), | |
| Return_Profile_Pension_Scheme_Germany character(1), | |
| Time_Horizon1 character(1), | |
| Time_Horizon2 double precision, | |
| Maturity_Date date, | |
| May_Be_Terminated_Early character(1), | |
| Specific_Investment_Need character(1), | |
| Execution_Only character(1), | |
| Execution_With_Appropriateness_Test_Or_Non_Advised_Services character(1), | |
| Investment_Advice character(1), | |
| Portfolio_Management character(1), | |
| Structured_Securities_Quotation character(1), | |
| OO_Financial_Instrument_entry_cost double precision, | |
| OO_Financial_Instrument_maximum_entry_cost_fixed_amount_Italy double precision, | |
| OO_Financial_Instrument_maximum_entry_cost_acquired double precision, | |
| OO_Financial_Instrument_maximum_exit_cost double precision, | |
| OO_Financial_Instrument_maximum_exit_cost_fixed_amount_Italy double precision, | |
| OO_Financial_Instrument_maximum_exit_cost_acquired double precision, | |
| OO_Financial_Instrument_Typical_exit_cost double precision, | |
| OO_Financial_Instrument_exit_cost_structrd_securities_prior_RHP double precision, | |
| Financial_Instrument_Ongoing_costs double precision, | |
| Financial_Instrument_Management_fee double precision, | |
| Financial_Instrument_Distribution_fee double precision, | |
| Financial_Instrument_Transaction_costs_ex_ante double precision, | |
| Financial_Instrument_Incidental_costs_ex_ante double precision, | |
| OO_Structured_Securities_entry_cost_ex_post double precision, | |
| OO_Structured_Securities_exit_cost_ex_post double precision, | |
| Financial_Instrument_Ongoing_costs_ex_post double precision, | |
| Structured_Securities_Ongoing_costs_ex_post_accumulated double precision, | |
| Financial_Instrument_Management_fee_ex_post double precision, | |
| Financial_Instrument_Distribution_fee_ex_post double precision, | |
| Financial_Instrument_Transaction_costs_ex_post double precision, | |
| Financial_Instrument_Incidental_costs_ex_post double precision, | |
| Beginning_Of_Calculation_Period date, | |
| End_Of_Calculation_Period date, | |
| CONSTRAINT mifid_pk PRIMARY KEY ("insref") | |
| ); | |
| CREATE TABLE "underlyings" ( | |
| "id" SERIAL, | |
| "insref" numeric(20) NOT NULL, | |
| "isin" character varying(255) NOT NULL, | |
| CONSTRAINT underlyings_pk PRIMARY KEY ("insref","isin") | |
| ); | |
| CREATE INDEX idx_underlyings ON underlyings ("isin"); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment