public async Task SaveTrades(IStorageTransaction transaction, ICollection <MarketTrade> trades)
{
var context = (HistorianDbContext)transaction.GetContext();
using (var cmd = context.Database.GetDbConnection().CreateCommand())
{
cmd.Transaction = context.Database.CurrentTransaction.GetDbTransaction();
var sql = @"insert ignore into `exchange_trade`
(`exchange_id`,
`symbol_id`,
`timestamp`,
`order_side_id`,
`price`,
`volume`,
`source_trade_id`) values ";
sql += string.Join(",", trades.Select(trade => $"({(int)trade.Exchange}," +
$"{(int)trade.SymbolCode}," +
$"{trade.Epoch.TimestampMilliseconds}," +
$"{(trade.Side.HasValue ? ((int)trade.Side).ToString() : "NULL")}," +
$"{trade.Price}," +
$"{trade.Volume}," +
$"{(trade.SourceTradeId != null ? trade.SourceTradeId : "NULL")})"));
cmd.CommandText = sql;
await cmd.ExecuteNonQueryAsync();
}
}
public async Task SetLastTradeStatId(IStorageTransaction transaction, ExchangeEnum exchange, SymbolCodeEnum symbolCode, long lastTradeStatId)
{
var context = (HistorianDbContext)transaction.GetContext();
var history = await context.HistorianExchangeSymbol.FindAsync((int)exchange, (int)symbolCode);
if (history == null)
{
history = new HistorianExchangeSymbolEntity
{
ExchangeId = (int)exchange,
SymbolId = (int)symbolCode
};
await context.HistorianExchangeSymbol.AddAsync(history);
}
history.LastTradeStatId = lastTradeStatId;
}
public async Task SetTradeFilter(IStorageTransaction transaction, ExchangeEnum exchange, SymbolCodeEnum symbolCode, string filter)
{
var context = (HistorianDbContext)transaction.GetContext();
var entity = await context.HistorianExchangeSymbol.FindAsync((int)exchange, (int)symbolCode);
if (entity == null)
{
entity = new HistorianExchangeSymbolEntity
{
ExchangeId = (int)exchange,
SymbolId = (int)symbolCode,
TradeFilter = filter
};
await context.HistorianExchangeSymbol.AddAsync(entity);
}
else
{
entity.TradeFilter = filter;
context.Update(entity);
}
}
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();
}
}