CREATE TABLE `SRAnalytics`.`MsgRootDefinition` (
`root_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
`root_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
`root_tk` VARCHAR(12) NOT NULL DEFAULT '',
`ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'master underlying (can be a stock/product group; eg. @ES)',
`ticker_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'master underlying (can be a stock/product group; eg. @ES)',
`ticker_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'master underlying (can be a stock/product group; eg. @ES)',
`osiRoot` VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'long version of the root. the short version is used in the TickerKey (for example RYAAY1, not RYAA1)',
`ccode_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
`ccode_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
`ccode_tk` VARCHAR(12) NOT NULL DEFAULT '',
`uPrcDriverKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT '(optional) option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverKey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT '(optional) option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverKey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT '(optional) option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(optional) option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(optional) option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(optional) option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None' COMMENT 'Stock or Future (note: if Future and uPrcDriverKey does not have an expiry month then FrontMonth will be used)',
`uPrcDriverKey2_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT '(optional) alternate option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverKey2_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT '(optional) alternate option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverKey2_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT '(optional) alternate option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverKey2_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(optional) alternate option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverKey2_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(optional) alternate option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverKey2_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(optional) alternate option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverType2` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None' COMMENT 'Stock or Future (note: if Future and uPrcDriverKey does not have an expiry month then FrontMonth will be used)',
`uPrcBoundCCode` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'if Yes and if a future exists with ccode=CCode and futExpiry = optExpiry the use this future as a pricing bound',
`expirationMap` ENUM('None','ExactMatch','UnderlierMap') NOT NULL DEFAULT 'None' COMMENT 'determines the underlying future (if any)',
`underlierMode` ENUM('None','Actual','FrontMonth','UPrcAdj') NOT NULL DEFAULT 'None',
`optionType` ENUM('None','Equity','Index','Future','Binary','Warrant','Flex','MapError') NOT NULL DEFAULT 'None' COMMENT 'indicator for option type',
`multihedge` ENUM('None','Simple','Complex','AllCash','Binary','Fragment') NOT NULL DEFAULT 'None' COMMENT 'indicates type of multihedge (None = standard root; all other root types are not None)',
`exerciseTime` ENUM('None','PM','AM') NOT NULL DEFAULT 'None' COMMENT 'Exercise time type',
`exerciseType` ENUM('None','American','European','Asian','Cliquet') NOT NULL DEFAULT 'None' COMMENT 'Exercise style',
`timeMetric` ENUM('None','D252','D365','SPX','WK1','WK2','WK3','WK4') NOT NULL DEFAULT 'None' COMMENT 'trading time metric - 252 or 365 trading days or a weekly cycle type',
`pricingModel` ENUM('None','Equity','FutureApprox','FutureExact','NormalApprox','NormalExact') NOT NULL DEFAULT 'None',
`moneynessType` ENUM('None','PctStd','LogStd','NormStd') NOT NULL DEFAULT 'None' COMMENT 'moneyness (xAxis) convention: PctStd = (K / fUPrc - 1) / (axisVol * RT), LogStd = LOG(K/fUPrc) / (axisVol * RT), NormStd = (K - fUPrc) / (axisVol * RT)',
`priceQuoteType` ENUM('None','Price','Vol') NOT NULL DEFAULT 'None' COMMENT 'quoting style for the option series on the exchange, price (standard price quote) or volatility quoted (vol points)',
`volumeTier` ENUM('None','Top50') NOT NULL DEFAULT 'None',
`positionLimit` INT NOT NULL DEFAULT 0 COMMENT 'max contract limit',
`exchanges` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'exchange codes',
`tickValue` FLOAT NOT NULL DEFAULT 0 COMMENT '$NLV value of a single tick change in display premium (pointValue = tickValue / tickSize)',
`pointValue` FLOAT NOT NULL DEFAULT 0 COMMENT '$NLV value of a single point change in display premium (pointValue = tickValue / tickSize)',
`pointCurrency` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX') NOT NULL DEFAULT 'None',
`strikeScale` DOUBLE NOT NULL DEFAULT 0 COMMENT 'manual strike price adjustment multiplier (used for some CME products if set, otherwise displayFactor is used) (okey_xx = strikePrice * manualStrikeScale)',
`strikeRatio` FLOAT NOT NULL DEFAULT 0 COMMENT 'note: effective strike = strike * strikeRatio - cashOnExercise',
`cashOnExercise` FLOAT NOT NULL DEFAULT 0 COMMENT 'note: cashOnExercise is positive if it decreases the effective strike price',
`underliersPerCn` INT NOT NULL DEFAULT 0 COMMENT 'note: always 100 if underlying list is in use',
`premiumMult` DOUBLE NOT NULL DEFAULT 0 COMMENT 'note: OCC premium/strike multiplier (usually 100)',
`symbolRatio` FLOAT NOT NULL DEFAULT 0 COMMENT 'note: currently used when AdjConvention is None, value of 0 implies symbolRatio is 1',
`adjConvention` ENUM('None','Original','OSI','SpcOnly','OSIAlt') NOT NULL DEFAULT 'None',
`optPriceInc` ENUM('None','PartPenny','PartNickle','FullPenny') NOT NULL DEFAULT 'None',
`priceFormat` ENUM('None','N0','N1','N2','N3','N4','N5','N6','N7','F4','F8','Q8','F16','F32','H32','Q32','F64','H64','FullPenny','PartPenny','PartNickle','EQT','V1','V2','V3','V4','V5','V6','V7','V8','V9','V10','V11','V12','V13','V14','V15','A0','A1','A2','A3','A4','A5','A6','A7','E32') NOT NULL DEFAULT 'None' COMMENT 'price display format',
`tradeCurr` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX') NOT NULL DEFAULT 'None',
`settleCurr` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX') NOT NULL DEFAULT 'None',
`strikeCurr` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX') NOT NULL DEFAULT 'None',
`defaultSurfaceRoot_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'fallback ticker to use for option surfaces if no native surfaces are available',
`defaultSurfaceRoot_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'fallback ticker to use for option surfaces if no native surfaces are available',
`defaultSurfaceRoot_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'fallback ticker to use for option surfaces if no native surfaces are available',
`ricRoot` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'RIC Root',
`timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
`ExchangeList` JSON NOT NULL DEFAULT JSON_OBJECT() CHECK(JSON_VALID(ExchangeList)),
`UnderlyingList` JSON NOT NULL DEFAULT JSON_OBJECT() CHECK(JSON_VALID(UnderlyingList)),
PRIMARY KEY USING HASH (`root_tk`,`root_at`,`root_ts`),
KEY `CCodeIndex` (`ccode_tk`) USING HASH,
KEY `OSIRootIndex` (`osiRoot`) USING HASH,
KEY `TickerIndex` (`ticker_tk`) USING HASH
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='RootDefinition records are sourced from the listing exchange for future options and from OCC for US equity options. Records are updated as SpiderRock receives changes.';