public async Task SaveTradeAggregates(IStorageTransaction transaction, ExchangeEnum exchange, SymbolCodeEnum symbolCode, ICollection <MarketTrade> trades)
{
var min = trades.Min(t => t.Epoch);
var max = trades.Max(t => t.Epoch);
var expanded = new List <TradeCartesian>();
foreach (var group in IntervalFactory.ListGroups())
{
foreach (var ik in IntervalFactory.ListIntervalKeys(group.IntervalGroup))
{
foreach (var trade in trades)
{
var period = IntervalFactory.GenerateIntervals(ik, trade.Epoch, trade.Epoch).FirstOrDefault();
expanded.Add(new TradeCartesian
{
IntervalKey = period.IntervalKey,
IntervalEpoch = period.From,
Exchange = trade.Exchange,
SymbolCode = trade.SymbolCode,
Epoch = trade.Epoch,
TradeId = trade.TradeId,
Price = trade.Price,
Volume = trade.Volume,
Side = trade.Side
});
}
}
}
var context = (HistorianDbContext)transaction.GetContext();
using (var cmd = context.Database.GetDbConnection().CreateCommand())
{
cmd.Transaction = context.Database.CurrentTransaction.GetDbTransaction();
var sql = @"insert into `exchange_trade_aggregate`
(`exchange_id`,
`symbol_id`,
`interval_key`,
`timestamp`,
`open`,
`open_timestamp`,
`high`,
`low`,
`close`,
`close_timestamp`,
`buy_volume`,
`sell_volume`,
`total_volume`,
`buy_count`,
`sell_count`,
`total_count`) values ";
sql += string.Join(",\r\n", expanded.Select(t => $"(" +
$"{(int)t.Exchange}," +
$"{(int)t.SymbolCode}," +
$"'{t.IntervalKey.Key}'," +
$"{t.IntervalEpoch.TimestampMilliseconds}," +
$"{t.Price}," +
$"{t.Epoch.TimestampMilliseconds}," +
$"{t.Price}," +
$"{t.Price}," +
$"{t.Price}," +
$"{t.Epoch.TimestampMilliseconds}," +
$"{(t.Side == OrderSideEnum.Buy ? t.Volume.ToString() : "NULL")}," +
$"{(t.Side == OrderSideEnum.Sell ? t.Volume.ToString() : "NULL")}," +
$"{t.Volume}," +
$"{(t.Side == OrderSideEnum.Buy ? "1" : "NULL")}," +
$"{(t.Side == OrderSideEnum.Sell ? "1" : "NULL")}," +
$"1)"));
sql += @"
on duplicate key update
`open` = case when values(`open_timestamp`) < `open_timestamp` then values(`open`) else `open` end,
`open_timestamp` = case when values(`open_timestamp`) < `open_timestamp` then values(`open_timestamp`) else `open_timestamp` end,
`high` = case when values(`high`) > `high` then values(`high`) else `high` end,
`low` = case when values(`low`) < `low` then values(`low`) else `low` end,
`close` = case when values(`close_timestamp`) > `close_timestamp` then values(`close`) else `close` end,
`close_timestamp` = case when values(`close_timestamp`) > `close_timestamp` then values(`close_timestamp`) else `close_timestamp` end,
`buy_volume` = case when values(`buy_volume`) is not null then ifnull(`buy_volume`, 0) + values(`buy_volume`) else `buy_volume` end,
`sell_volume` = case when values(`sell_volume`) is not null then ifnull(`sell_volume`, 0) + values(`sell_volume`) else `sell_volume` end,
`total_volume` = `total_volume` + values(`total_volume`),
`buy_count` = case when values(`buy_count`) is not null then ifnull(`buy_count`, 0) + values(`buy_count`) else `buy_count` end,
`sell_count` = case when values(`sell_count`) is not null then ifnull(`sell_count`, 0) + values(`sell_count`) else `sell_count` end,
`total_count` = `total_count` + values(`total_count`)";
cmd.CommandText = sql;
await cmd.ExecuteNonQueryAsync();
}
}
public async Task SaveTradeStatAggregates(IStorageTransaction transaction, ExchangeEnum exchange, SymbolCodeEnum symbolCode, ICollection <MarketTradeStat> tradeStats)
{
var min = tradeStats.Min(t => t.Epoch);
var max = tradeStats.Max(t => t.Epoch);
var expanded = new List <TradeStatCartesian>();
foreach (var group in IntervalFactory.ListGroups())
{
foreach (var ik in IntervalFactory.ListIntervalKeys(group.IntervalGroup))
{
foreach (var tradeStat in tradeStats)
{
var period = IntervalFactory.GenerateIntervals(ik, tradeStat.Epoch, tradeStat.Epoch).FirstOrDefault();
expanded.Add(new TradeStatCartesian
{
IntervalKey = period.IntervalKey,
IntervalEpoch = period.From,
Exchange = tradeStat.Exchange,
SymbolCode = symbolCode,
StatKey = tradeStat.StatKey,
Epoch = tradeStat.Epoch,
Value = tradeStat.Value
});
}
}
}
using (var context = ContextFactory.CreateDbContext(null))
{
using (var cmd = context.Database.GetDbConnection().CreateCommand())
{
var sql = @"insert into `exchange_trade_aggregate`
(`exchange_id`,
`symbol_id`,
`interval_key`,
`timestamp`,
`open`,
`open_timestamp`,
`high`,
`low`,
`close`,
`close_timestamp`,
`total_count`) values ";
sql += string.Join(",\r\n", expanded.Select(t => $"(" +
$"{(int)t.Exchange}," +
$"{(int)t.SymbolCode}," +
$"'{t.IntervalKey.Key}'," +
$"{t.IntervalEpoch.TimestampMilliseconds}," +
$"{t.Value}," +
$"{t.Epoch.TimestampMilliseconds}," +
$"{t.Value}," +
$"{t.Value}," +
$"{t.Value}," +
$"{t.Epoch.TimestampMilliseconds}," +
$"1)"));
sql += @"
on duplicate key update
`open` = case when values(`open_timestamp`) < `open_timestamp` then values(`open`) else `open` end,
`open_timestamp` = case when values(`open_timestamp`) < `open_timestamp` then values(`open_timestamp`) else `open_timestamp` end,
`high` = case when values(`high`) > `high` then values(`high`) else `high` end,
`low` = case when values(`low`) < `low` then values(`low`) else `low` end,
`close` = case when values(`close_timestamp`) > `close_timestamp` then values(`close`) else `close` end,
`close_timestamp` = case when values(`close_timestamp`) > `close_timestamp` then values(`close_timestamp`) else `close_timestamp` end,
`total_count` = `total_count` + values(`total_count`)" ;
cmd.CommandText = sql;
await cmd.Connection.OpenAsync();
await cmd.ExecuteNonQueryAsync();
cmd.Connection.Close();
}
}
}
public async Task <bool> Run(ILogger logger)
{
logger.LogInformation("Running repository bootstrapper");
try
{
foreach (var currency in CurrencyFactory.List())
{
await CurrencyRepository.Add(currency);
}
foreach (var symbol in SymbolFactory.List())
{
await SymbolRepository.Add(symbol);
}
foreach (var group in IntervalFactory.ListGroups())
{
foreach (var ik in IntervalFactory.ListIntervalKeys(group.IntervalGroup))
{
await IntervalRepository.Add(ik);
}
}
foreach (var exchange in ExchangeFactory.List())
{
await ExchangeRepository.Add(exchange);
var httpClient = exchange.GetHttpClient();
foreach (var symbolCode in exchange.Symbol)
{
var symbol = SymbolFactory.Get(symbolCode);
await SymbolRepository.Add(symbol);
await ExchangeRepository.AddSymbol(exchange.Name, symbolCode);
var tradeFilter = await HistorianRepository.GetTradeFilter(exchange.Name, symbolCode);
if (tradeFilter == null)
{
using (var transaction = await StorageTransactionFactory.Begin())
{
await HistorianRepository.SetTradeFilter(transaction, exchange.Name, symbolCode, httpClient.InitialTradeFilter);
await transaction.Commit();
}
}
}
}
foreach (var orderSide in OrderSideFactory.List())
{
await OrderSideRepository.Add(orderSide);
}
return(true);
}
catch (Exception ex)
{
logger.LogCritical(ex, "Unable to run repository bootstrapper");
return(false);
}
}