478 lines
14 KiB
SQL
478 lines
14 KiB
SQL
SHOW CREATE TABLE oversite.accounts
|
|
"CREATE TABLE IF NOT EXISTS "oversite"."accounts" (
|
|
"user_id" TEXT NOT NULL,
|
|
"account_type" TEXT NOT NULL,
|
|
"account_number" TEXT NOT NULL,
|
|
"apikey" TEXT,
|
|
"customunits" INTEGER,
|
|
PRIMARY KEY ("user_id", "account_type", "account_number")
|
|
)
|
|
CLUSTERED INTO 14 SHARDS
|
|
WITH (
|
|
"allocation.max_retries" = 5,
|
|
"blocks.metadata" = false,
|
|
"blocks.read" = false,
|
|
"blocks.read_only" = false,
|
|
"blocks.read_only_allow_delete" = false,
|
|
"blocks.write" = false,
|
|
codec = 'default',
|
|
column_policy = 'strict',
|
|
"mapping.total_fields.limit" = 1000,
|
|
max_ngram_diff = 1,
|
|
max_shingle_diff = 3,
|
|
number_of_replicas = '2-3',
|
|
"routing.allocation.enable" = 'all',
|
|
"routing.allocation.total_shards_per_node" = -1,
|
|
"store.type" = 'fs',
|
|
"translog.durability" = 'REQUEST',
|
|
"translog.flush_threshold_size" = 536870912,
|
|
"translog.sync_interval" = 5000,
|
|
"unassigned.node_left.delayed_timeout" = 60000,
|
|
"write.wait_for_active_shards" = '1'
|
|
)"
|
|
;
|
|
SHOW CREATE TABLE oversite.chronicle_history
|
|
"CREATE TABLE IF NOT EXISTS "oversite"."chronicle_history" (
|
|
"tpair" TEXT NOT NULL,
|
|
"cmd_date" TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
"dictum" TEXT,
|
|
"glimpse" TEXT,
|
|
PRIMARY KEY ("tpair", "cmd_date")
|
|
)
|
|
CLUSTERED INTO 14 SHARDS
|
|
WITH (
|
|
"allocation.max_retries" = 5,
|
|
"blocks.metadata" = false,
|
|
"blocks.read" = false,
|
|
"blocks.read_only" = false,
|
|
"blocks.read_only_allow_delete" = false,
|
|
"blocks.write" = false,
|
|
codec = 'default',
|
|
column_policy = 'strict',
|
|
"mapping.total_fields.limit" = 1000,
|
|
max_ngram_diff = 1,
|
|
max_shingle_diff = 3,
|
|
number_of_replicas = '2-3',
|
|
"routing.allocation.enable" = 'all',
|
|
"routing.allocation.total_shards_per_node" = -1,
|
|
"store.type" = 'fs',
|
|
"translog.durability" = 'REQUEST',
|
|
"translog.flush_threshold_size" = 536870912,
|
|
"translog.sync_interval" = 5000,
|
|
"unassigned.node_left.delayed_timeout" = 60000,
|
|
"write.wait_for_active_shards" = '1'
|
|
)"
|
|
;
|
|
SHOW CREATE TABLE oversite.dictum
|
|
"CREATE TABLE IF NOT EXISTS "oversite"."dictum" (
|
|
"tpair" TEXT NOT NULL,
|
|
"dictum" TEXT,
|
|
"dictum_date" TIMESTAMP WITH TIME ZONE,
|
|
PRIMARY KEY ("tpair")
|
|
)
|
|
CLUSTERED BY ("tpair") INTO 14 SHARDS
|
|
WITH (
|
|
"allocation.max_retries" = 5,
|
|
"blocks.metadata" = false,
|
|
"blocks.read" = false,
|
|
"blocks.read_only" = false,
|
|
"blocks.read_only_allow_delete" = false,
|
|
"blocks.write" = false,
|
|
codec = 'default',
|
|
column_policy = 'strict',
|
|
"mapping.total_fields.limit" = 1000,
|
|
max_ngram_diff = 1,
|
|
max_shingle_diff = 3,
|
|
number_of_replicas = '2-3',
|
|
"routing.allocation.enable" = 'all',
|
|
"routing.allocation.total_shards_per_node" = -1,
|
|
"store.type" = 'fs',
|
|
"translog.durability" = 'REQUEST',
|
|
"translog.flush_threshold_size" = 536870912,
|
|
"translog.sync_interval" = 5000,
|
|
"unassigned.node_left.delayed_timeout" = 60000,
|
|
"write.wait_for_active_shards" = '1'
|
|
)"
|
|
;
|
|
SHOW CREATE TABLE oversite.logs
|
|
"CREATE TABLE IF NOT EXISTS "oversite"."logs" (
|
|
"log_date" TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
"message_type" TEXT NOT NULL,
|
|
"message" OBJECT(DYNAMIC) AS (
|
|
"msg" TEXT
|
|
),
|
|
PRIMARY KEY ("message_type", "log_date")
|
|
)
|
|
CLUSTERED INTO 14 SHARDS
|
|
WITH (
|
|
"allocation.max_retries" = 5,
|
|
"blocks.metadata" = false,
|
|
"blocks.read" = false,
|
|
"blocks.read_only" = false,
|
|
"blocks.read_only_allow_delete" = false,
|
|
"blocks.write" = false,
|
|
codec = 'default',
|
|
column_policy = 'strict',
|
|
"mapping.total_fields.limit" = 1000,
|
|
max_ngram_diff = 1,
|
|
max_shingle_diff = 3,
|
|
number_of_replicas = '0-1',
|
|
"routing.allocation.enable" = 'all',
|
|
"routing.allocation.total_shards_per_node" = -1,
|
|
"store.type" = 'fs',
|
|
"translog.durability" = 'REQUEST',
|
|
"translog.flush_threshold_size" = 536870912,
|
|
"translog.sync_interval" = 5000,
|
|
"unassigned.node_left.delayed_timeout" = 60000,
|
|
"write.wait_for_active_shards" = '1'
|
|
)"
|
|
;
|
|
SHOW CREATE TABLE oversite.order_log
|
|
"CREATE TABLE IF NOT EXISTS "oversite"."order_log" (
|
|
"order_date" TIMESTAMP WITH TIME ZONE,
|
|
"order_body" TEXT
|
|
)
|
|
CLUSTERED INTO 14 SHARDS
|
|
WITH (
|
|
"allocation.max_retries" = 5,
|
|
"blocks.metadata" = false,
|
|
"blocks.read" = false,
|
|
"blocks.read_only" = false,
|
|
"blocks.read_only_allow_delete" = false,
|
|
"blocks.write" = false,
|
|
codec = 'default',
|
|
column_policy = 'strict',
|
|
"mapping.total_fields.limit" = 1000,
|
|
max_ngram_diff = 1,
|
|
max_shingle_diff = 3,
|
|
number_of_replicas = '0-1',
|
|
"routing.allocation.enable" = 'all',
|
|
"routing.allocation.total_shards_per_node" = -1,
|
|
"store.type" = 'fs',
|
|
"translog.durability" = 'REQUEST',
|
|
"translog.flush_threshold_size" = 536870912,
|
|
"translog.sync_interval" = 5000,
|
|
"unassigned.node_left.delayed_timeout" = 60000,
|
|
"write.wait_for_active_shards" = '1'
|
|
)"
|
|
;
|
|
SHOW CREATE TABLE oversite.orders
|
|
"CREATE TABLE IF NOT EXISTS "oversite"."orders" (
|
|
"order_id" INTEGER NOT NULL,
|
|
"account_id" INTEGER NOT NULL,
|
|
"tpair" TEXT,
|
|
"order_type" TEXT,
|
|
"order_ref" INTEGER,
|
|
"order_reason" TEXT,
|
|
"quantity" INTEGER,
|
|
"order_date" TIMESTAMP WITH TIME ZONE,
|
|
"order_data" OBJECT(DYNAMIC) AS (
|
|
"accountID" TEXT,
|
|
"requestID" TEXT,
|
|
"siteID" BIGINT,
|
|
"id" TEXT,
|
|
"time" TEXT,
|
|
"divisionID" BIGINT,
|
|
"batchID" TEXT,
|
|
"type" TEXT,
|
|
"accountNumber" BIGINT,
|
|
"userID" BIGINT,
|
|
"accountUserID" BIGINT,
|
|
"homeCurrency" TEXT,
|
|
"marginRate" TEXT,
|
|
"alias" TEXT,
|
|
"amount" TEXT,
|
|
"fundingReason" TEXT,
|
|
"accountBalance" TEXT,
|
|
"reason" TEXT,
|
|
"instrument" TEXT,
|
|
"units" TEXT,
|
|
"timeInForce" TEXT,
|
|
"positionFill" TEXT,
|
|
"guaranteedExecutionFee" TEXT,
|
|
"fullPrice" OBJECT(DYNAMIC) AS (
|
|
"bids" ARRAY(OBJECT(DYNAMIC) AS (
|
|
"price" TEXT,
|
|
"liquidity" TEXT
|
|
)),
|
|
"closeoutBid" TEXT,
|
|
"closeoutAsk" TEXT,
|
|
"asks" ARRAY(OBJECT(DYNAMIC) AS (
|
|
"price" TEXT,
|
|
"liquidity" TEXT
|
|
)),
|
|
"timestamp" TEXT
|
|
),
|
|
"homeConversionCost" TEXT,
|
|
"baseFinancingHomeConversionCost" TEXT,
|
|
"price" TEXT,
|
|
"financing" TEXT,
|
|
"commission" TEXT,
|
|
"tradeOpened" OBJECT(DYNAMIC) AS (
|
|
"halfSpreadCost" TEXT,
|
|
"initialMarginRequired" TEXT,
|
|
"price" TEXT,
|
|
"quoteGuaranteedExecutionFee" TEXT,
|
|
"guaranteedExecutionFee" TEXT,
|
|
"units" TEXT,
|
|
"tradeID" TEXT,
|
|
"guaranteedExecutionFeeHomeConversionCost" TEXT
|
|
),
|
|
"gainQuoteHomeConversionFactor" TEXT,
|
|
"plHomeConversionCost" TEXT,
|
|
"lossQuoteHomeConversionFactor" TEXT,
|
|
"guaranteedExecutionFeeHomeConversionCost" TEXT,
|
|
"halfSpreadCost" TEXT,
|
|
"requestedUnits" TEXT,
|
|
"quotePL" TEXT,
|
|
"orderID" TEXT,
|
|
"fullVWAP" TEXT,
|
|
"homeConversionFactors" OBJECT(DYNAMIC) AS (
|
|
"lossBaseHome" OBJECT(DYNAMIC) AS (
|
|
"factor" TEXT
|
|
),
|
|
"gainBaseHome" OBJECT(DYNAMIC) AS (
|
|
"factor" TEXT
|
|
),
|
|
"gainQuoteHome" OBJECT(DYNAMIC) AS (
|
|
"factor" TEXT
|
|
),
|
|
"lossQuoteHome" OBJECT(DYNAMIC) AS (
|
|
"factor" TEXT
|
|
)
|
|
),
|
|
"baseFinancing" TEXT,
|
|
"quoteGuaranteedExecutionFee" TEXT,
|
|
"pl" TEXT,
|
|
"triggerCondition" TEXT,
|
|
"rejectReason" TEXT,
|
|
"triggerMode" TEXT,
|
|
"tradeID" TEXT,
|
|
"replacedByOrderID" TEXT,
|
|
"cancellingTransactionID" TEXT,
|
|
"replacesOrderID" TEXT,
|
|
"tradeClose" OBJECT(DYNAMIC) AS (
|
|
"tradeID" TEXT,
|
|
"units" TEXT
|
|
),
|
|
"tradesClosed" ARRAY(OBJECT(DYNAMIC) AS (
|
|
"halfSpreadCost" TEXT,
|
|
"guaranteedExecutionFee" TEXT,
|
|
"units" TEXT,
|
|
"realizedPL" TEXT,
|
|
"homeConversionCost" TEXT,
|
|
"baseFinancingHomeConversionCost" TEXT,
|
|
"baseFinancing" TEXT,
|
|
"price" TEXT,
|
|
"financing" TEXT,
|
|
"quoteGuaranteedExecutionFee" TEXT,
|
|
"plHomeConversionCost" TEXT,
|
|
"tradeID" TEXT,
|
|
"guaranteedExecutionFeeHomeConversionCost" TEXT
|
|
)),
|
|
"closedTradeID" TEXT,
|
|
"tradeCloseTransactionID" TEXT,
|
|
"distance" TEXT,
|
|
"positionFinancings" ARRAY(OBJECT(DYNAMIC) AS (
|
|
"homeConversionCost" TEXT,
|
|
"homeConversionFactors" OBJECT(DYNAMIC) AS (
|
|
"lossBaseHome" OBJECT(DYNAMIC) AS (
|
|
"factor" TEXT
|
|
),
|
|
"gainBaseHome" OBJECT(DYNAMIC) AS (
|
|
"factor" TEXT
|
|
)
|
|
),
|
|
"baseFinancing" TEXT,
|
|
"financing" TEXT,
|
|
"instrument" TEXT,
|
|
"baseHomeConversionCost" TEXT,
|
|
"accountFinancingMode" TEXT,
|
|
"openTradeFinancings" ARRAY(OBJECT(DYNAMIC) AS (
|
|
"financing" TEXT,
|
|
"homeConversionCost" TEXT,
|
|
"baseHomeConversionCost" TEXT,
|
|
"financingRate" TEXT,
|
|
"baseFinancing" TEXT,
|
|
"tradeID" TEXT
|
|
))
|
|
)),
|
|
"baseHomeConversionCost" TEXT,
|
|
"longPositionCloseout" OBJECT(DYNAMIC) AS (
|
|
"instrument" TEXT,
|
|
"units" TEXT
|
|
),
|
|
"stopLossOnFill" OBJECT(DYNAMIC) AS (
|
|
"timeInForce" TEXT,
|
|
"triggerMode" TEXT,
|
|
"price" TEXT,
|
|
"distance" TEXT
|
|
),
|
|
"shortPositionCloseout" OBJECT(DYNAMIC) AS (
|
|
"instrument" TEXT,
|
|
"units" TEXT
|
|
),
|
|
"tradeReduced" OBJECT(DYNAMIC) AS (
|
|
"halfSpreadCost" TEXT,
|
|
"guaranteedExecutionFee" TEXT,
|
|
"units" TEXT,
|
|
"realizedPL" TEXT,
|
|
"homeConversionCost" TEXT,
|
|
"baseFinancingHomeConversionCost" TEXT,
|
|
"baseFinancing" TEXT,
|
|
"price" TEXT,
|
|
"financing" TEXT,
|
|
"quoteGuaranteedExecutionFee" TEXT,
|
|
"plHomeConversionCost" TEXT,
|
|
"tradeID" TEXT,
|
|
"guaranteedExecutionFeeHomeConversionCost" TEXT
|
|
),
|
|
"takeProfitOnFill" OBJECT(DYNAMIC) AS (
|
|
"distance" TEXT,
|
|
"timeInForce" TEXT
|
|
),
|
|
"trailingStopLossOnFill" OBJECT(DYNAMIC) AS (
|
|
"distance" TEXT,
|
|
"timeInForce" TEXT,
|
|
"triggerMode" TEXT
|
|
),
|
|
"intendedReplacesOrderID" TEXT
|
|
),
|
|
PRIMARY KEY ("account_id", "order_id")
|
|
)
|
|
CLUSTERED INTO 14 SHARDS
|
|
WITH (
|
|
"allocation.max_retries" = 5,
|
|
"blocks.metadata" = false,
|
|
"blocks.read" = false,
|
|
"blocks.read_only" = false,
|
|
"blocks.read_only_allow_delete" = false,
|
|
"blocks.write" = false,
|
|
codec = 'default',
|
|
column_policy = 'strict',
|
|
"mapping.total_fields.limit" = 1000,
|
|
max_ngram_diff = 1,
|
|
max_shingle_diff = 3,
|
|
number_of_replicas = '2-3',
|
|
"routing.allocation.enable" = 'all',
|
|
"routing.allocation.total_shards_per_node" = -1,
|
|
"store.type" = 'fs',
|
|
"translog.durability" = 'REQUEST',
|
|
"translog.flush_threshold_size" = 536870912,
|
|
"translog.sync_interval" = 5000,
|
|
"unassigned.node_left.delayed_timeout" = 60000,
|
|
"write.wait_for_active_shards" = '1'
|
|
)"
|
|
;
|
|
SHOW CREATE TABLE oversite.osapi_state
|
|
"CREATE TABLE IF NOT EXISTS "oversite"."osapi_state" (
|
|
"state_date" TIMESTAMP WITH TIME ZONE,
|
|
"account_state" TEXT
|
|
)
|
|
CLUSTERED INTO 14 SHARDS
|
|
WITH (
|
|
"allocation.max_retries" = 5,
|
|
"blocks.metadata" = false,
|
|
"blocks.read" = false,
|
|
"blocks.read_only" = false,
|
|
"blocks.read_only_allow_delete" = false,
|
|
"blocks.write" = false,
|
|
codec = 'default',
|
|
column_policy = 'strict',
|
|
"mapping.total_fields.limit" = 1000,
|
|
max_ngram_diff = 1,
|
|
max_shingle_diff = 3,
|
|
number_of_replicas = '0-1',
|
|
"routing.allocation.enable" = 'all',
|
|
"routing.allocation.total_shards_per_node" = -1,
|
|
"store.type" = 'fs',
|
|
"translog.durability" = 'REQUEST',
|
|
"translog.flush_threshold_size" = 536870912,
|
|
"translog.sync_interval" = 5000,
|
|
"unassigned.node_left.delayed_timeout" = 60000,
|
|
"write.wait_for_active_shards" = '1'
|
|
)"
|
|
;
|
|
SHOW CREATE TABLE oversite.pricing
|
|
"CREATE TABLE IF NOT EXISTS "oversite"."pricing" (
|
|
"instrument" TEXT NOT NULL,
|
|
"price_data" OBJECT(DYNAMIC) AS (
|
|
"closeoutBid" TEXT,
|
|
"closeoutAsk" TEXT,
|
|
"asks" ARRAY(OBJECT(DYNAMIC) AS (
|
|
"price" TEXT,
|
|
"liquidity" BIGINT
|
|
)),
|
|
"bids" ARRAY(OBJECT(DYNAMIC) AS (
|
|
"price" TEXT,
|
|
"liquidity" BIGINT
|
|
)),
|
|
"tradeable" BOOLEAN,
|
|
"instrument" TEXT,
|
|
"time" TEXT,
|
|
"type" TEXT,
|
|
"status" TEXT
|
|
),
|
|
"tick" TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
"bid" TEXT,
|
|
"ask" TEXT,
|
|
"spread" TEXT,
|
|
PRIMARY KEY ("instrument", "tick")
|
|
)
|
|
CLUSTERED INTO 14 SHARDS
|
|
WITH (
|
|
"allocation.max_retries" = 5,
|
|
"blocks.metadata" = false,
|
|
"blocks.read" = false,
|
|
"blocks.read_only" = false,
|
|
"blocks.read_only_allow_delete" = false,
|
|
"blocks.write" = false,
|
|
codec = 'default',
|
|
column_policy = 'strict',
|
|
"mapping.total_fields.limit" = 1000,
|
|
max_ngram_diff = 1,
|
|
max_shingle_diff = 3,
|
|
number_of_replicas = '1-2',
|
|
"routing.allocation.enable" = 'all',
|
|
"routing.allocation.total_shards_per_node" = -1,
|
|
"store.type" = 'fs',
|
|
"translog.durability" = 'REQUEST',
|
|
"translog.flush_threshold_size" = 536870912,
|
|
"translog.sync_interval" = 5000,
|
|
"unassigned.node_left.delayed_timeout" = 60000,
|
|
"write.wait_for_active_shards" = '1'
|
|
)"
|
|
;
|
|
SHOW CREATE TABLE oversite.users
|
|
"CREATE TABLE IF NOT EXISTS "oversite"."users" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT,
|
|
"email" TEXT,
|
|
"passwd" TEXT,
|
|
PRIMARY KEY ("id")
|
|
)
|
|
CLUSTERED BY ("id") INTO 14 SHARDS
|
|
WITH (
|
|
"allocation.max_retries" = 5,
|
|
"blocks.metadata" = false,
|
|
"blocks.read" = false,
|
|
"blocks.read_only" = false,
|
|
"blocks.read_only_allow_delete" = false,
|
|
"blocks.write" = false,
|
|
codec = 'default',
|
|
column_policy = 'strict',
|
|
"mapping.total_fields.limit" = 1000,
|
|
max_ngram_diff = 1,
|
|
max_shingle_diff = 3,
|
|
number_of_replicas = '2-3',
|
|
"routing.allocation.enable" = 'all',
|
|
"routing.allocation.total_shards_per_node" = -1,
|
|
"store.type" = 'fs',
|
|
"translog.durability" = 'REQUEST',
|
|
"translog.flush_threshold_size" = 536870912,
|
|
"translog.sync_interval" = 5000,
|
|
"unassigned.node_left.delayed_timeout" = 60000,
|
|
"write.wait_for_active_shards" = '1'
|
|
)"
|
|
;
|