public bool TryWriteValues(IEnumerable <KeyValuePair <UInt256, WriteValue <ImmutableArray <Transaction> > > > values)
{
using (var conn = this.OpenConnection())
using (var trans = conn.BeginTransaction())
using (var cmd = conn.CreateCommand())
{
cmd.Transaction = trans;
cmd.CommandText = @"
MERGE INTO BlockTransactions
USING (SELECT CAST(@blockHash AS CHAR(32) CHARACTER SET OCTETS) AS BlockHash, CAST(@txIndex AS INTEGER) AS TxIndex FROM RDB$DATABASE) AS Param
ON (BlockTransactions.BlockHash = Param.BlockHash AND BlockTransactions.TxIndex = Param.TxIndex)
WHEN NOT MATCHED THEN
INSERT ( BlockHash, TxIndex, TxHash, TxBytes )
VALUES ( @blockHash, @txIndex, @txHash, @txBytes );" ;
cmd.Parameters.Add(new FbParameter {
ParameterName = "@blockHash", FbDbType = FbDbType.Char, Charset = FbCharset.Octets, Size = 32
});
cmd.Parameters.Add(new FbParameter {
ParameterName = "@txIndex", FbDbType = FbDbType.Integer
});
cmd.Parameters.Add(new FbParameter {
ParameterName = "@txHash", FbDbType = FbDbType.Char, Charset = FbCharset.Octets, Size = 32
});
cmd.Parameters.Add(new FbParameter {
ParameterName = "@txBytes", FbDbType = FbDbType.Binary
});
foreach (var keyPair in values)
{
var blockHash = keyPair.Key;
cmd.Parameters["@blockHash"].Value = blockHash.ToDbByteArray();
for (var txIndex = 0; txIndex < keyPair.Value.Value.Length; txIndex++)
{
var tx = keyPair.Value.Value[txIndex];
var txBytes = StorageEncoder.EncodeTransaction(tx);
cmd.Parameters["@txIndex"].Value = txIndex;
cmd.Parameters["@txHash"].Value = tx.Hash.ToDbByteArray();
cmd.Parameters["@txBytes"].Size = txBytes.Length;
cmd.Parameters["@txBytes"].Value = txBytes;
cmd.ExecuteNonQuery();
}
}
trans.Commit();
return(true);
}
}
public bool TryWriteValues(IEnumerable <KeyValuePair <UInt256, WriteValue <ImmutableArray <Transaction> > > > values)
{
using (var conn = this.OpenConnection())
using (var trans = conn.BeginTransaction())
using (var cmd = conn.CreateCommand())
using (var deleteCmd = conn.CreateCommand())
{
// give writes low deadlock priority, a flush can always be retried
using (var deadlockCmd = conn.CreateCommand())
{
deadlockCmd.Transaction = trans;
deadlockCmd.CommandText = "SET DEADLOCK_PRIORITY LOW";
deadlockCmd.ExecuteNonQuery();
}
deleteCmd.Transaction = trans;
cmd.Transaction = trans;
deleteCmd.CommandText = @"
DELETE FROM BlockTransactionsChunked
WHERE BlockHash = @blockHash";
deleteCmd.Parameters.Add(new SqlParameter {
ParameterName = "@blockHash", DbType = DbType.Binary, Size = 32
});
cmd.CommandText = @"
INSERT
INTO BlockTransactionsChunked ( BlockHash, MinTxIndex, MaxTxIndex, TxChunkBytes )
VALUES ( @blockHash, @minTxIndex, @maxTxIndex, @txChunkBytes );" ;
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@blockHash", DbType = DbType.Binary, Size = 32
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@minTxIndex", DbType = DbType.Int32
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@maxTxIndex", DbType = DbType.Int32
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@txChunkBytes", DbType = DbType.Binary
});
var chunkSize = 100.THOUSAND();
var maxChunkSize = 1.MILLION();
var chunk = new byte[maxChunkSize];
foreach (var keyPair in values)
{
var blockHash = keyPair.Key;
deleteCmd.Parameters["@blockHash"].Value = blockHash.ToDbByteArray();
deleteCmd.ExecuteNonQuery();
cmd.Parameters["@blockHash"].Value = blockHash.ToDbByteArray();
var minTxIndex = 0;
var maxTxIndex = 0;
var chunkOffset = 0;
for (var txIndex = 0; txIndex < keyPair.Value.Value.Length; txIndex++)
{
var tx = keyPair.Value.Value[txIndex];
var txBytes = StorageEncoder.EncodeTransaction(tx);
if (txBytes.Length > maxChunkSize)
{
throw new Exception();
}
if (chunkOffset + txBytes.Length > chunkSize && chunkOffset > 0)
{
var dbChunk1 = new byte[chunkOffset];
Buffer.BlockCopy(chunk, 0, dbChunk1, 0, chunkOffset);
cmd.Parameters["@minTxIndex"].Value = minTxIndex;
cmd.Parameters["@maxTxIndex"].Value = maxTxIndex;
cmd.Parameters["@txChunkBytes"].Size = dbChunk1.Length;
cmd.Parameters["@txChunkBytes"].Value = dbChunk1;
cmd.ExecuteNonQuery();
chunkOffset = 0;
minTxIndex = txIndex;
}
maxTxIndex = txIndex;
Buffer.BlockCopy(txBytes, 0, chunk, chunkOffset, txBytes.Length);
chunkOffset += txBytes.Length;
}
var dbChunk2 = new byte[chunkOffset];
Buffer.BlockCopy(chunk, 0, dbChunk2, 0, chunkOffset);
cmd.Parameters["@minTxIndex"].Value = minTxIndex;
cmd.Parameters["@maxTxIndex"].Value = maxTxIndex;
cmd.Parameters["@txChunkBytes"].Size = dbChunk2.Length;
cmd.Parameters["@txChunkBytes"].Value = dbChunk2;
cmd.ExecuteNonQuery();
}
trans.Commit();
return(true);
}
}
public bool TryWriteValues(IEnumerable <KeyValuePair <UInt256, WriteValue <ImmutableArray <Transaction> > > > values)
{
var stopwatch = new Stopwatch();
var count = 0;
try
{
using (var conn = this.OpenConnection())
using (var trans = conn.BeginTransaction())
using (var cmd = conn.CreateCommand())
{
cmd.Transaction = trans;
cmd.CommandText = @"
MERGE BlockTransactions AS target
USING (SELECT @blockHash, @txIndex) AS source (BlockHash, TxIndex)
ON (target.BlockHash = source.BlockHash AND target.TxIndex = source.TxIndex)
WHEN NOT MATCHED THEN
INSERT (BlockHash, TxIndex, TxHash, TxBytes)
VALUES (@blockHash, @txIndex, @txHash, @txBytes);" ;
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@blockHash", DbType = DbType.Binary, Size = 32
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@txIndex", DbType = DbType.Int32
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@txHash", DbType = DbType.Binary, Size = 32
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@txBytes", DbType = DbType.Binary
});
foreach (var keyPair in values)
{
var blockHash = keyPair.Key;
cmd.Parameters["@blockHash"].Value = blockHash.ToDbByteArray();
for (var txIndex = 0; txIndex < keyPair.Value.Value.Length; txIndex++)
{
var tx = keyPair.Value.Value[txIndex];
var txBytes = StorageEncoder.EncodeTransaction(tx);
cmd.Parameters["@txIndex"].Value = txIndex;
cmd.Parameters["@txHash"].Value = tx.Hash.ToDbByteArray();
cmd.Parameters["@txBytes"].Size = txBytes.Length;
cmd.Parameters["@txBytes"].Value = txBytes;
count++;
cmd.ExecuteNonQuery();
}
}
stopwatch.Start();
trans.Commit();
stopwatch.Stop();
return(true);
}
}
finally
{
//Debug.WriteLine("flushed {0,5}: {1:#,##0.000000}s @ {2:#,##0.000}/s".Format2(count, stopwatch.ElapsedSecondsFloat(), count / stopwatch.ElapsedSecondsFloat()));
}
}