public bool TryReadTransaction(TxKey txKey, out Transaction transaction)
{
using (var conn = this.OpenReadConnection())
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT TxHash, TxBytes
FROM BlockTransactions
WHERE BlockHash = @blockHash AND TxIndex = @txIndex";
cmd.Parameters.SetValue("@blockHash", DbType.Binary, 32).Value = txKey.BlockHash.ToDbByteArray();
cmd.Parameters.SetValue("@txIndex", DbType.Int32).Value = txKey.TxIndex.ToIntChecked();
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
var txHash = reader.GetUInt256(0);
var txBytes = reader.GetBytes(1);
transaction = StorageEncoder.DecodeTransaction(txBytes.ToMemoryStream(), txHash);
return(true);
}
else
{
transaction = default(Transaction);
return(false);
}
}
}
}
public bool TryReadValue(UInt256 blockHash, out ImmutableArray <Transaction> blockTransactions)
{
using (var conn = this.OpenReadConnection())
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT TxHash, TxBytes
FROM BlockTransactions
WHERE BlockHash = @blockHash
ORDER BY TxIndex ASC";
cmd.Parameters.SetValue("@blockHash", DbType.Binary, 32).Value = blockHash.ToDbByteArray();
using (var reader = cmd.ExecuteReader())
{
var blockTransactionsBuilder = ImmutableArray.CreateBuilder <Transaction>();
while (reader.Read())
{
var txHash = reader.GetUInt256(0);
var txBytes = reader.GetBytes(1);
blockTransactionsBuilder.Add(StorageEncoder.DecodeTransaction(txBytes.ToMemoryStream(), txHash));
}
blockTransactions = blockTransactionsBuilder.ToImmutable();
return(blockTransactions.Length > 0);
}
}
}
public bool TryReadValue(UInt256 blockHash, out BlockHeader blockHeader)
{
using (var conn = this.OpenConnection())
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT HeaderBytes
FROM BlockHeaders
WHERE BlockHash = @blockHash";
cmd.Parameters.SetValue("@blockHash", FbDbType.Char, FbCharset.Octets, 32).Value = blockHash.ToDbByteArray();
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
var headerBytes = reader.GetCharBytes(0);
blockHeader = StorageEncoder.DecodeBlockHeader(headerBytes.ToMemoryStream(), blockHash);
return(true);
}
else
{
blockHeader = default(BlockHeader);
return(false);
}
}
}
}
public bool TryReadValue(UInt256 txHash, out Transaction transaction)
{
using (var conn = this.OpenConnection())
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT TxBytes
FROM BlockTransactions
WHERE TxHash = @txHash";
cmd.Parameters.SetValue("@txHash", FbDbType.Char, FbCharset.Octets, 32).Value = txHash.ToDbByteArray();
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
var txBytes = reader.GetBytes(0);
transaction = StorageEncoder.DecodeTransaction(txBytes.ToMemoryStream(), txHash);
return(true);
}
else
{
transaction = default(Transaction);
return(false);
}
}
}
}
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 <BlockHeader> > > values)
{
using (var conn = this.OpenConnection())
using (var trans = conn.BeginTransaction())
using (var cmd = conn.CreateCommand())
{
cmd.Transaction = trans;
cmd.Parameters.Add(new FbParameter {
ParameterName = "@blockHash", FbDbType = FbDbType.Char, Charset = FbCharset.Octets, Size = 32
});
cmd.Parameters.Add(new FbParameter {
ParameterName = "@headerBytes", FbDbType = FbDbType.Char, Charset = FbCharset.Octets, Size = 80
});
cmd.CommandText = CREATE_QUERY;
foreach (var keyPair in values.Where(x => x.Value.IsCreate))
{
var blockHeader = keyPair.Value.Value;
var blockBytes = StorageEncoder.EncodeBlockHeader(blockHeader);
cmd.Parameters["@blockHash"].Value = blockHeader.Hash.ToDbByteArray();
cmd.Parameters["@headerBytes"].Value = blockBytes;
cmd.ExecuteNonQuery();
}
cmd.CommandText = UPDATE_QUERY;
foreach (var keyPair in values.Where(x => !x.Value.IsCreate))
{
var blockHeader = keyPair.Value.Value;
var blockBytes = StorageEncoder.EncodeBlockHeader(blockHeader);
cmd.Parameters["@blockHash"].Value = blockHeader.Hash.ToDbByteArray();
cmd.Parameters["@headerBytes"].Value = blockBytes;
cmd.ExecuteNonQuery();
}
trans.Commit();
return(true);
}
}
public IEnumerable <KeyValuePair <UInt256, BlockHeader> > ReadAllValues()
{
using (var conn = this.OpenReadConnection())
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT BlockHash, HeaderBytes
FROM BlockHeaders";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var blockHash = reader.GetUInt256(0);
var headerBytes = reader.GetBytes(1);
yield return(new KeyValuePair <UInt256, BlockHeader>(blockHash, StorageEncoder.DecodeBlockHeader(headerBytes.ToMemoryStream(), blockHash)));
}
}
}
}
public bool TryWriteValues(IEnumerable <KeyValuePair <UInt256, WriteValue <BlockHeader> > > values)
{
try
{
using (var conn = this.OpenConnection())
using (var trans = conn.BeginTransaction())
using (var cmd = 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();
}
cmd.Transaction = trans;
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@blockHash", SqlDbType = SqlDbType.Binary, Size = 32
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "@headerBytes", SqlDbType = SqlDbType.Binary, Size = 80
});
cmd.CommandText = CREATE_QUERY;
foreach (var keyPair in values.Where(x => x.Value.IsCreate))
{
var blockHeader = keyPair.Value.Value;
var blockBytes = StorageEncoder.EncodeBlockHeader(blockHeader);
cmd.Parameters["@blockHash"].Value = blockHeader.Hash.ToDbByteArray();
cmd.Parameters["@headerBytes"].Value = blockBytes;
cmd.ExecuteNonQuery();
}
cmd.CommandText = UPDATE_QUERY;
foreach (var keyPair in values.Where(x => !x.Value.IsCreate))
{
var blockHeader = keyPair.Value.Value;
var blockBytes = StorageEncoder.EncodeBlockHeader(blockHeader);
cmd.Parameters["@blockHash"].Value = blockHeader.Hash.ToDbByteArray();
cmd.Parameters["@headerBytes"].Value = blockBytes;
cmd.ExecuteNonQuery();
}
trans.Commit();
return(true);
}
}
catch (SqlException e)
{
if (e.IsDeadlock() || e.IsTimeout())
{
return(false);
}
else
{
throw;
}
}
}
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()));
}
}
