void IStorage.CommitAddedMessages(long transId, IStorageTransaction storageTrans)
{
var sql =
"Update Messages SET State = @State, TransactionId = NULL, TransactionAction = NULL where TransactionId = @TranId and TransactionAction = @TranAction;";
_con.Execute(sql, transaction: (storageTrans as InternalTransaction)?.SqliteTransaction, param: new { State = MessageState.Active.Value, TranId = transId, TranAction = TransactionAction.Add.Value });
}
public async Task AddTrades(IStorageTransaction transaction, ILogger logger, TradeResult tradeResult)
{
try
{
var trades = tradeResult.Trades
.Select(trade => new MarketTrade
{
Epoch = trade.Epoch,
Exchange = tradeResult.Exchange,
SymbolCode = tradeResult.SymbolCode,
Side = trade.Side.HasValue ? trade.Side : null,
Price = trade.Price,
Volume = trade.Volume,
SourceTradeId = trade.SourceTradeId
}).OrderBy(t => t.Epoch.TimestampMilliseconds).ToList();
if (trades.Count > 0)
{
await MarketRepository.SaveTrades(transaction, trades);
}
}
catch (Exception ex)
{
logger.LogError(ex, $"Unable to save trades into the repository.");
}
}
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();
}
}
void IStorage.CommitPulledMessages(long transId, IStorageTransaction storageTrans, DateTime commitDateTime)
{
var sql =
"Update Messages SET State = @State, TransactionId = NULL, TransactionAction = NULL, CloseDateTime = @CloseDateTime where TransactionId = @TranId and TransactionAction = @TranAction;";
_con.Execute(sql, transaction: (storageTrans as InternalTransaction)?.SqliteTransaction,
param: new
{
State = MessageState.Processed.Value,
TranId = transId,
TranAction = TransactionAction.Pull.Value,
CloseDateTime = commitDateTime
});
}
public void ChangeState(Job job, IState state, IStorageTransaction transaction)
{
var now = DateTime.UtcNow;
if (state.ExpiresAfter != null)
{
job.ExpiresAt = now.Add(state.ExpiresAfter.Value);
}
else
{
job.ExpiresAt = null;
}
job.StateName = state.Name;
state.Apply(job, transaction);
transaction.UpdateJob(job);
}
public void ChangeState(PublishedMessage message, IState state, IStorageTransaction transaction)
{
var now = DateTime.Now;
if (state.ExpiresAfter != null)
{
message.ExpiresAt = now.Add(state.ExpiresAfter.Value);
}
else
{
message.ExpiresAt = null;
}
message.StatusName = state.Name;
state.Apply(message, transaction);
transaction.UpdateMessage(message);
}
/// <inheritdoc/>
public async ValueTask UpdateTransactionState(IStorageTransaction storageTrans, long transId, TransactionState state, string?endReason = null, DateTime?endDateTime = null)
{
await this.ExecuteAsync(
async (connection) =>
{
const string sql = "UPDATE transactions SET end_datetime = @EndDateTime, state = @State, end_reason = @EndReason WHERE id = @Id;";
await connection.ExecuteAsync(
sql,
new
{
Id = transId,
State = state,
EndDateTime = endDateTime,
EndReason = endReason
},
storageTrans.NpgsqlTransaction());
},
storageTrans.NpgsqlTransaction().Connection);
}
/// <inheritdoc/>
public async ValueTask UpdateTransactionState(IStorageTransaction storageTrans, long transId, TransactionState state, string?endReason = null, DateTime?endDateTime = null)
{
await this.ExecuteAsync(
async (connection) =>
{
const string sql = "UPDATE Transactions SET EndDateTime = @EndDateTime, State = @State, EndReason = @EndReason WHERE ID = @Id;";
await connection.ExecuteAsync(
sql,
new
{
Id = transId,
State = state,
EndDateTime = endDateTime?.ToUnixEpoch(),
EndReason = endReason
},
storageTrans.SqliteTransaction());
},
storageTrans.SqliteTransaction().Connection);
}
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;
}
/// <inheritdoc/>
public async ValueTask <int> DeleteAddedMessages(IStorageTransaction storageTrans, long transId)
{
const string sql = "DELETE FROM messages where transaction_id = @TransId AND transaction_action = @TransactionAction AND state = @MessageState;";
var sqliteConnection = storageTrans.NpgsqlTransaction().Connection;
return(await this.ExecuteAsync <int>(
async (connection) =>
{
return await connection.ExecuteAsync(
sql,
transaction: (storageTrans as DbTransaction)?.NpgsqlTransaction,
param: new
{
TransId = transId,
TransactionAction = TransactionAction.Add,
MessageState = MessageState.InTransaction
});
},
sqliteConnection));
}
/// <inheritdoc/>
public async ValueTask <int> UpdateMessageAttemptCount(IStorageTransaction storageTrans, long transId, TransactionAction transactionAction, MessageState messageState)
{
const string sql = "Update messages set attempts = attempts + 1 " +
"where transaction_id = @TransId AND transaction_action = @TransactionAction AND state = @MessageState;";
var sqliteConnection = storageTrans.NpgsqlTransaction().Connection;
return(await this.ExecuteAsync <int>(
async (connection) =>
{
return await connection.ExecuteAsync(
sql,
transaction: (storageTrans as DbTransaction)?.NpgsqlTransaction,
param: new
{
TransId = transId,
TransactionAction = transactionAction,
MessageState = messageState
});
},
sqliteConnection));
}
void IStorage.AddMessage(long transId, IStorageTransaction storageTrans, long nextId, long queueId, byte[] compressedMessage, DateTime addDateTime, string metaData = "", int priority = 0, int maxRetries = 3, DateTime?expiryDateTime = null, int correlation = 0, string groupName = "")
{
var sql = "INSERT INTO Messages (Id, QueueId, TransactionId, TransactionAction, State, AddDateTime, Priority, MaxRetries, Retries, ExpiryDate, Data, CorrelationId, GroupName, Metadata) VALUES " +
"(@Id, @QueueId, @TransactionId, @TransactionAction, @State, @AddDateTime, @Priority, @MaxRetries, 0, @ExpiryDate, @Data, @CorrelationId, @GroupName, @Metadata);";
_con.Execute(sql, transaction: (storageTrans as InternalTransaction)?.SqliteTransaction, param: new
{
Id = nextId,
QueueId = queueId,
TransactionId = transId,
TransactionAction = TransactionAction.Pull.Value,
State = MessageState.InTransaction.Value,
AddDateTime = addDateTime,
Priority = priority,
MaxRetries = maxRetries,
ExpiryDate = expiryDateTime ?? DateTime.MaxValue,
Data = compressedMessage,
CorrelationId = correlation,
GroupName = groupName,
Metadata = metaData
});
}
/// <inheritdoc/>
public async ValueTask <int> DeleteAddedMessagesInExpiredTrans(IStorageTransaction storageTrans, DateTime currentDateTime)
{
const string sql =
"Delete from messages where TransactionAction = @TransactionAction AND State = @MessageState AND " +
"TransactionId in (select ID from Transactions Where ExpiryDateTime <= @CurrentDateTime);";
var sqliteConnection = storageTrans.SqliteTransaction().Connection;
return(await this.ExecuteAsync <int>(
async (connection) =>
{
return await connection.ExecuteAsync(
sql,
transaction: (storageTrans as DbTransaction)?.SqliteTransaction,
param: new
{
TransactionAction = TransactionAction.Add,
MessageState = MessageState.InTransaction,
CurrentDateTime = currentDateTime.ToUnixEpoch()
});
},
sqliteConnection));
}
/// <inheritdoc/>
public async ValueTask <int> DeleteAddedMessagesInExpiredTrans(IStorageTransaction storageTrans, DateTime currentDateTime)
{
const string sql =
"Delete from messages where transaction_action = @TransactionAction AND state = @MessageState AND " +
"transaction_id in (select id from transactions Where expiry_datetime <= @CurrentDateTime);";
var sqliteConnection = storageTrans.NpgsqlTransaction().Connection;
return(await this.ExecuteAsync <int>(
async (connection) =>
{
return await connection.ExecuteAsync(
sql,
transaction: (storageTrans as DbTransaction)?.NpgsqlTransaction,
param: new
{
TransactionAction = TransactionAction.Add,
MessageState = MessageState.InTransaction,
CurrentDateTime = currentDateTime
});
},
sqliteConnection));
}
public async Task SaveTradeStats(IStorageTransaction transaction, ICollection <MarketTradeStat> tradeStats)
{
using (var context = ContextFactory.CreateDbContext(null))
{
using (var cmd = context.Database.GetDbConnection().CreateCommand())
{
cmd.CommandText = $@"insert ignore into `exchange_trade_stat`
(`exchange_id`,
`symbol_id`,
`stat_key_id`,
`timestamp`,
`value`)
values {string.Join(",\r\n", tradeStats.Select(t => $"({(int)t.Exchange},{(int)t.SymbolCode},{(int)t.StatKey},{t.Epoch.TimestampMilliseconds},{t.Value})"))}
on duplicate key update `value` = values(`value`)";
await cmd.Connection.OpenAsync();
await cmd.ExecuteNonQueryAsync();
cmd.Connection.Close();
}
}
}
/// <inheritdoc/>
public async ValueTask <int> ExpireTransactions(IStorageTransaction storageTrans, DateTime currentDateTime)
{
const string sql =
"Update transactions SET state = @NewTransactionState, end_datetime = @CurrentDateTime, end_reason = 'Expired' " +
"Where expiry_datetime <= @CurrentDateTime AND state = @OldTransactionState;";
var sqliteConnection = storageTrans.NpgsqlTransaction().Connection;
return(await this.ExecuteAsync <int>(
async (connection) =>
{
return await connection.ExecuteAsync(
sql,
transaction: (storageTrans as DbTransaction)?.NpgsqlTransaction,
param: new
{
NewTransactionState = TransactionState.Expired,
MessageState = MessageState.InTransaction,
CurrentDateTime = currentDateTime,
OldTransactionState = TransactionState.Active
});
},
sqliteConnection));
}
public async Task <TradeResult> ReceiveTradesHttp(IStorageTransaction transaction, ILogger logger, ExchangeEnum exchange, ISymbol symbol, IExchangeHttpClient httpClient, int limit, string lastTradeFilter)
{
logger.LogInformation($"Requesting trades from filter '{lastTradeFilter}'");
var response = await httpClient.GetTrades(symbol, limit, lastTradeFilter);
var tradeResult = response.Data;
if (response.StatusCode != WrappedResponseStatusCode.Ok)
{
var errorCode = !string.IsNullOrEmpty(response.ErrorCode) ? $"Error Code: {response.ErrorCode} Message: " : "";
logger.LogWarning($"Unable to get trades: {errorCode}{response.ErrorMessage}");
return(null);
}
if (tradeResult.Trades.Count > 0)
{
await AddTrades(transaction, logger, tradeResult);
}
return(tradeResult);
}
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);
}
}
/// <inheritdoc/>
public async ValueTask <int> UpdateMessageAttemptsInExpiredTrans(IStorageTransaction storageTrans, DateTime currentDateTime)
{
const string sql =
"Update messages set attempts = attempts + 1, transaction_action = 0, transaction_id = null, state = @NewMessageState " +
"where transaction_action = @TransactionAction AND state = @MessageState AND " +
"transaction_id in (select id from transactions Where expiry_datetime <= @CurrentDateTime);";
var sqliteConnection = storageTrans.NpgsqlTransaction().Connection;
return(await this.ExecuteAsync <int>(
async (connection) =>
{
return await connection.ExecuteAsync(
sql,
transaction: (storageTrans as DbTransaction)?.NpgsqlTransaction,
param: new
{
TransactionAction = TransactionAction.Pull,
MessageState = MessageState.InTransaction,
CurrentDateTime = currentDateTime,
NewMessageState = MessageState.Active // Return to active for now. We won't worry about closing it here, if it needs to be.
});
},
sqliteConnection));
}
/// <inheritdoc/>
public async ValueTask <int> UpdateMessages(IStorageTransaction storageTrans, long transId, TransactionAction transactionAction, MessageState oldMessageState, MessageState newMessageState, DateTime?closeDateTime)
{
const string sql = "Update messages set transaction_id = null, transaction_action = 0, " +
"state = @NewMessageState, close_datetime = @CloseDateTime " +
"where transaction_id = @TransId AND transaction_action = @TransactionAction AND state = @OldMessageState;";
var sqliteConnection = storageTrans.NpgsqlTransaction().Connection;
return(await this.ExecuteAsync <int>(
async (connection) =>
{
return await connection.ExecuteAsync(
sql,
transaction: (storageTrans as DbTransaction)?.NpgsqlTransaction,
param: new
{
TransId = transId,
TransactionAction = transactionAction,
OldMessageState = oldMessageState,
NewMessageState = newMessageState,
CloseDateTime = closeDateTime
});
},
sqliteConnection));
}
public void Apply(Job job, IStorageTransaction transaction)
{
transaction.EnqueueJob(job);
}
public void Apply(CapReceivedMessage message, IStorageTransaction transaction)
{
}
public void Apply(CapPublishedMessage message, IStorageTransaction transaction)
{
}
public void Apply(Job job, IStorageTransaction transaction)
{
}
/// <summary>
/// Converts the IStorageTransaction to a SqliteTransaction.
/// </summary>
/// <param name="iTrans">The IStorageTransaction to convert.</param>
/// <returns>SqliteTransaction.</returns>
public static SqliteTransaction SqliteTransaction(this IStorageTransaction iTrans)
{
return((iTrans as DbTransaction).SqliteTransaction);
}
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 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();
}
}
void IStorage.UpdateRetriesOnRollbackedMessages(long transId, IStorageTransaction storageTrans)
{
var sql = "UPDATE Messages SET State = @State, TransactionId = NULL, TransactionAction = NULL, Retries = Retries + 1 WHERE TransactionId = @tranId and TransactionAction = @TranAction;";
_con.Execute(sql, new { State = MessageState.Active, transId, TranAction = TransactionAction.Pull.Value }, (storageTrans as InternalTransaction)?.SqliteTransaction);
}
void IStorage.CommitMessageTransaction(long transId, IStorageTransaction storageTrans, DateTime commitDateTime)
{
var sql = "UPDATE Transactions SET Active = 0, EndDateTime = @EndDateTime WHERE Id = @TranId;";
_con.Execute(sql, transaction: (storageTrans as InternalTransaction)?.SqliteTransaction, param: new { TranId = transId, EndDateTime = commitDateTime });
}
void IStorage.DeleteQueue(long id, IStorageTransaction storageTrans)
{
var sql = "DELETE FROM Queues WHERE Id = @Id;";
_con.Execute(sql, transaction: (storageTrans as InternalTransaction)?.SqliteTransaction, param: new { Id = id });
}
