public async Task DeleteLastBlockchainFileAsync()
{
const string deleteFromTransactionOutput = @"
DELETE TransactionOutput FROM TransactionOutput
INNER JOIN BitcoinTransaction ON BitcoinTransaction.BitcoinTransactionId = TransactionOutput.BitcoinTransactionId
INNER JOIN Block ON Block.BlockId = BitcoinTransaction.BlockId
WHERE Block.BlockchainFileId >= @MaxBlockchainFileId";
const string deleteFromTransactionInput = @"
DELETE TransactionInput FROM TransactionInput
INNER JOIN BitcoinTransaction ON BitcoinTransaction.BitcoinTransactionId = TransactionInput.BitcoinTransactionId
INNER JOIN Block ON Block.BlockId = BitcoinTransaction.BlockId
WHERE Block.BlockchainFileId >= @MaxBlockchainFileId";
const string deleteFromBitcoinTransaction = @"
DELETE BitcoinTransaction FROM BitcoinTransaction
INNER JOIN Block ON Block.BlockId = BitcoinTransaction.BlockId
WHERE Block.BlockchainFileId >= @MaxBlockchainFileId";
const string deleteFromBlock = @" DELETE Block FROM Block WHERE Block.BlockchainFileId >= @MaxBlockchainFileId";
const string deleteFromBlockchainFile = "DELETE FROM BlockchainFile WHERE BlockchainFile.BlockchainFileId >= @MaxBlockchainFileId";
int lastBlockchainFileId = AdoNetLayer.ConvertDbValue <int>(this.adoNetLayer.ExecuteScalar("SELECT MAX(BlockchainFileId) from BlockchainFile"));
await this.adoNetLayer.ExecuteStatementNoResultAsync(deleteFromTransactionOutput, AdoNetLayer.CreateInputParameter("@MaxBlockchainFileId", SqlDbType.Int, lastBlockchainFileId));
await this.adoNetLayer.ExecuteStatementNoResultAsync(deleteFromTransactionInput, AdoNetLayer.CreateInputParameter("@MaxBlockchainFileId", SqlDbType.Int, lastBlockchainFileId));
await this.adoNetLayer.ExecuteStatementNoResultAsync(deleteFromBitcoinTransaction, AdoNetLayer.CreateInputParameter("@MaxBlockchainFileId", SqlDbType.Int, lastBlockchainFileId));
await this.adoNetLayer.ExecuteStatementNoResultAsync(deleteFromBlock, AdoNetLayer.CreateInputParameter("@MaxBlockchainFileId", SqlDbType.Int, lastBlockchainFileId));
await this.adoNetLayer.ExecuteStatementNoResultAsync(deleteFromBlockchainFile, AdoNetLayer.CreateInputParameter("@MaxBlockchainFileId", SqlDbType.Int, lastBlockchainFileId));
}
public void AddBlockchainFile(BlockchainFile blockchainFile)
{
this.adoNetLayer.ExecuteStatementNoResult(
"INSERT INTO BlockchainFile(BlockchainFileId, BlockchainFileName) VALUES (@BlockchainFileId, @BlockchainFileName)",
AdoNetLayer.CreateInputParameter("@BlockchainFileId", SqlDbType.Int, blockchainFile.BlockchainFileId),
AdoNetLayer.CreateInputParameter("@BlockchainFileName", SqlDbType.NVarChar, blockchainFile.BlockchainFileName));
}
public ValidationDataSetInfo <ValidationBlockDataSet> GetValidationBlockSampleDataSet(long maxBlockchainFileId, int sampleRatio)
{
const string sqlCommandText = @"
SELECT
BlockId,
BlockchainFileId,
BlockVersion,
BlockHash,
PreviousBlockHash,
BlockTimestamp,
TransactionCount,
TransactionInputCount,
TotalInputBtc,
TransactionOutputCount,
TotalOutputBtc,
TransactionFeeBtc,
TotalUnspentOutputBtc
FROM View_BlockAggregated
WHERE
BlockId <= (SELECT MAX(BlockId) FROM Block WHERE BlockchainFileId <= @MaxBlockchainFileId)
AND BlockId % @SampleRatio = 0
ORDER BY BlockId";
return(this.GetValidationDataSetInfo <ValidationBlockDataSet>(
sqlCommandText,
AdoNetLayer.CreateInputParameter("@MaxBlockchainFileId", SqlDbType.BigInt, maxBlockchainFileId),
AdoNetLayer.CreateInputParameter("@SampleRatio", SqlDbType.Int, sampleRatio)));
}
public ValidationDataSetInfo <ValidationTransactionOutputDataSet> GetValidationTransactionOutputSampleDataSet(int maxBlockchainFileId, int sampleRatio)
{
const string sqlCommandText = @"
SELECT
TransactionOutput.TransactionOutputId,
TransactionOutput.BitcoinTransactionId,
TransactionOutput.OutputIndex,
TransactionOutput.OutputValueBtc,
TransactionOutput.OutputScript,
CASE
WHEN EXISTS (SELECT * FROM TransactionInput WHERE SourceTransactionOutputId = TransactionOutput.OutputIndex)
THEN 1
ELSE 0
END
AS IsSpent
FROM TransactionOutput
WHERE
TransactionOutput.TransactionOutputId <= (
SELECT MAX(TransactionOutputId)
FROM TransactionOutput
INNER JOIN BitcoinTransaction ON BitcoinTransaction.BitcoinTransactionId = TransactionOutput.BitcoinTransactionId
INNER JOIN Block ON Block.BlockId = BitcoinTransaction.BlockId
WHERE Block.BlockchainFileId <= @MaxBlockchainFileId)
AND TransactionOutput.TransactionOutputId % @SampleRatio = 0
ORDER BY TransactionOutput.TransactionOutputId";
return(this.GetValidationDataSetInfo <ValidationTransactionOutputDataSet>(
sqlCommandText,
AdoNetLayer.CreateInputParameter("@MaxBlockchainFileId", SqlDbType.BigInt, maxBlockchainFileId),
AdoNetLayer.CreateInputParameter("@SampleRatio", SqlDbType.Int, sampleRatio)));
}
public ValidationDataSetInfo <ValidationTransactionDataSet> GetValidationTransactionSampleDataSet(int maxBlockchainFileId, int sampleRatio)
{
const string sqlCommandText = @"
SELECT
BitcoinTransactionId,
BlockId,
TransactionHash,
TransactionVersion,
TransactionLockTime,
TransactionInputCount,
TotalInputBtc,
TransactionOutputCount,
TotalOutputBtc,
TransactionFeeBtc,
TotalUnspentOutputBtc
FROM View_TransactionAggregated
WHERE
BitcoinTransactionId <= (
SELECT MAX(BitcoinTransactionId)
FROM BitcoinTransaction
INNER JOIN Block ON Block.BlockId = BitcoinTransaction.BlockId
WHERE Block.BlockchainFileId <= @MaxBlockchainFileId)
AND BitcoinTransactionId % @SampleRatio = 0
ORDER BY BitcoinTransactionId";
return(this.GetValidationDataSetInfo <ValidationTransactionDataSet>(
sqlCommandText,
AdoNetLayer.CreateInputParameter("@MaxBlockchainFileId", SqlDbType.BigInt, maxBlockchainFileId),
AdoNetLayer.CreateInputParameter("@SampleRatio", SqlDbType.Int, sampleRatio)));
}
public void UpdateTransactionOutputAddressId()
{
const string sqlGetFirstTXOutUnsetAddressId = @"
SELECT TOP 1 TransactionOutputId
FROM TransactionOutput
WHERE TransactionOutputId > @lastTransactionOutputId
AND OutputAddressId = -1
AND OutputAddress != '0'";
const string sqlUpdateAddressId = @"
UPDATE TransactionOutput
SET OutputAddressId = @mOutputAddressId
WHERE TransactionOutputId >= @currentTransactionOutputId
AND OutputAddress = (
SELECT OutputAddress
FROM TransactionOutput
WHERE TransactionOutputId = @currentTransactionOutputId)";
const string sqlGetMaxOutputAddressId = @"
SELECT MAX(OutputAddressId)
FROM TransactionOutput";
long MaxOutputAddressId = AdoNetLayer.ConvertDbValue <long>(
this.adoNetLayer.ExecuteScalar(sqlGetMaxOutputAddressId));
long OutputAddressId = MaxOutputAddressId + 1;
//得到TransactionOutput的数量
long MaxTransactionOutputId = AdoNetLayer.ConvertDbValue <long>(
this.adoNetLayer.ExecuteScalar(
@"select TOP 1 TransactionOutputId
from TransactionOutput
order by TransactionOutputId desc"));
long TransactionOutputCount = -1;
long SolveCount = 0;
Console.WriteLine("开始为地址编号......耗时也许会很久");
while (TransactionOutputCount < MaxTransactionOutputId)
{
//找到第一个OutputAddressId=-1 的 TxOutId即第一个未被编号的地址。
TransactionOutputCount = AdoNetLayer.ConvertDbValue <long>(this.adoNetLayer.ExecuteScalar(sqlGetFirstTXOutUnsetAddressId,
AdoNetLayer.CreateInputParameter("@lastTransactionOutputId", SqlDbType.BigInt, TransactionOutputCount)));
this.adoNetLayer.ExecuteStatementNoResult(sqlUpdateAddressId,
AdoNetLayer.CreateInputParameter("mOutputAddressId", SqlDbType.BigInt, OutputAddressId),
AdoNetLayer.CreateInputParameter("currentTransactionOutputId", SqlDbType.BigInt, TransactionOutputCount));
SolveCount++;
if (SolveCount % 100000 == 0)
{
Console.Write("\r已经处理了{0}个地址", SolveCount);
}
OutputAddressId++;
}
}
public SqlDataReader GetTransactionGraphEdgeReader(long bottomBound, long topBound)
{
string sqlGetEdge = @"
SELECT Edge.SourceId, Edge.TargetId, Edge.Value
FROM Edge
WHERE EdgeId >= @BottomBound AND EdgeId < @TopBound";
return(this.adoNetLayer.ExecuteStatementReader(sqlGetEdge,
AdoNetLayer.CreateInputParameter("@BottomBound", SqlDbType.BigInt, bottomBound),
AdoNetLayer.CreateInputParameter("@TopBound", SqlDbType.BigInt, topBound)));
}
public bool DatabaseExists()
{
string connectionString = this.databaseConnection.MasterConnectionString;
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
AdoNetLayer adoNetLayer = new AdoNetLayer(sqlConnection);
return(AdoNetLayer.ConvertDbValue <int>(adoNetLayer.ExecuteScalar(
"SELECT CASE WHEN EXISTS (SELECT * FROM sys.databases WHERE [Name] = @DatabaseName) THEN 1 ELSE 0 END AS DatabaseExists",
AdoNetLayer.CreateInputParameter("@DatabaseName", SqlDbType.NVarChar, this.databaseConnection.DatabaseName))) == 1);
}
}
public void GenerateEdgeByBatch()
{
const int maxBatchSize = 10000000;
Stopwatch updateTransactionSourceOutputWatch = new Stopwatch();
updateTransactionSourceOutputWatch.Start();
long BitcoinTransactionNum = this.GetMaxTransactionNum() + 1;
long batchSize = BitcoinTransactionNum / 20;
batchSize = batchSize >= 1 ? batchSize : 1;
batchSize = batchSize <= maxBatchSize ? batchSize : maxBatchSize;
string sqlCommandGenerateEdge = @"INSERT INTO Edge (SourceId, TargetId, Value)
SELECT BitcoinTransaction.BitcoinTransactionId AS SourceId, T1.BitcoinTransactionId AS TargetId, TransactionOutput.OutputValueBtc AS Value
FROM TransactionOutput
INNER JOIN BitcoinTransaction ON BitcoinTransaction.BitcoinTransactionId = TransactionOutput.BitcoinTransactionId
INNER JOIN TransactionInput ON TransactionInput.SourceTransactionHash = BitcoinTransaction.TransactionHash
AND TransactionOutput.OutputIndex = TransactionInput.SourceTransactionOutputIndex
INNER JOIN (
SELECT
BitcoinTransaction.BitcoinTransactionId
FROM BitcoinTransaction
WHERE BitcoinTransaction.BitcoinTransactionId >= @leftBoundary and BitcoinTransaction.BitcoinTransactionId < @rightBoundary
) AS T1 ON T1.BitcoinTransactionId = TransactionInput.BitcoinTransactionId
order by TargetId" ;
long leftBoundary = 0;
long rightBoundary = leftBoundary + batchSize;
Console.Write("\rGenerateing Edges(this may take a long time)... {0}%", 100 * (rightBoundary - batchSize) / BitcoinTransactionNum);
while (leftBoundary < BitcoinTransactionNum)
{
this.adoNetLayer.ExecuteStatementNoResult(sqlCommandGenerateEdge,
AdoNetLayer.CreateInputParameter("@leftBoundary", SqlDbType.BigInt, leftBoundary),
AdoNetLayer.CreateInputParameter("@rightBoundary", SqlDbType.BigInt, rightBoundary));
leftBoundary += batchSize;
rightBoundary = leftBoundary + batchSize;
Console.Write("\rGenerateing Edges(this may take a long time)... {0}%", 100 * (rightBoundary - batchSize) / BitcoinTransactionNum);
}
updateTransactionSourceOutputWatch.Stop();
Console.WriteLine("\rGenerate Graphic completed in {0:0.000} seconds. ", updateTransactionSourceOutputWatch.Elapsed.TotalSeconds);
}
/// <summary>
/// Applied after a series of blocks were deleted.
/// This method will update the block IDs so that they are forming a consecutive sequence.
/// </summary>
/// <param name="blocksDeleted">
/// The list of IDs for blocks that were deleted.
/// </param>
public void CompactBlockIds(IEnumerable <long> blocksDeleted)
{
const string sqlCommandUpdateBlockBlockIdSection = @"UPDATE Block SET BlockId = BlockId - @DecrementAmount WHERE BlockId BETWEEN @BlockId1 AND @BlockId2";
const string sqlCommandUpdateTransactionBlockIdSection = @"UPDATE BitcoinTransaction SET BlockId = BlockId - @DecrementAmount WHERE BlockId BETWEEN @BlockId1 AND @BlockId2";
const string sqlCommandUpdateBlockBlockIdLastSection = @"UPDATE Block SET BlockId = BlockId - @DecrementAmount WHERE BlockId > @BlockId";
const string sqlCommandUpdateTransactionBlockIdLastSection = @"UPDATE BitcoinTransaction SET BlockId = BlockId - @DecrementAmount WHERE BlockId > @BlockId";
List <long> orderedBlocksDeleted = blocksDeleted.OrderBy(id => id).ToList();
int decrementAmount = 1;
for (int i = 0; i < orderedBlocksDeleted.Count - 1; i++)
{
long blockId1 = orderedBlocksDeleted[i];
long blockId2 = orderedBlocksDeleted[i + 1];
this.adoNetLayer.ExecuteStatementNoResult(
sqlCommandUpdateBlockBlockIdSection,
AdoNetLayer.CreateInputParameter("@DecrementAmount", SqlDbType.Int, decrementAmount),
AdoNetLayer.CreateInputParameter("@BlockId1", SqlDbType.BigInt, blockId1),
AdoNetLayer.CreateInputParameter("@BlockId2", SqlDbType.BigInt, blockId2));
this.adoNetLayer.ExecuteStatementNoResult(
sqlCommandUpdateTransactionBlockIdSection,
AdoNetLayer.CreateInputParameter("@DecrementAmount", SqlDbType.Int, decrementAmount),
AdoNetLayer.CreateInputParameter("@BlockId1", SqlDbType.BigInt, blockId1),
AdoNetLayer.CreateInputParameter("@BlockId2", SqlDbType.BigInt, blockId2));
decrementAmount++;
}
long blockId = orderedBlocksDeleted[orderedBlocksDeleted.Count - 1];
this.adoNetLayer.ExecuteStatementNoResult(
sqlCommandUpdateBlockBlockIdLastSection,
AdoNetLayer.CreateInputParameter("@DecrementAmount", SqlDbType.BigInt, decrementAmount),
AdoNetLayer.CreateInputParameter("@BlockId", SqlDbType.BigInt, blockId));
this.adoNetLayer.ExecuteStatementNoResult(
sqlCommandUpdateTransactionBlockIdLastSection,
AdoNetLayer.CreateInputParameter("@DecrementAmount", SqlDbType.BigInt, decrementAmount),
AdoNetLayer.CreateInputParameter("@BlockId", SqlDbType.BigInt, blockId));
}
public ValidationDataSetInfo <ValidationBlockchainDataSet> GetValidationBlockchainDataSet(int maxBlockchainFileId)
{
const string sqlCommandText = @"
SELECT
COUNT(1) AS BlockCount,
SUM(TransactionCount) AS TransactionCount,
SUM(TransactionInputCount) AS TransactionInputCount,
SUM(TotalInputBtc) AS TotalInputBtc,
SUM(TransactionOutputCount) AS TransactionOutputCount,
SUM(TotalOutputBtc) AS TotalOutputBtc,
SUM(TransactionFeeBtc) AS TransactionFeeBtc,
SUM(TotalUnspentOutputBtc) AS TotalUnspentOutputBtc
FROM View_BlockAggregated
WHERE BlockchainFileId <= @MaxBlockchainFileId";
return(this.GetValidationDataSetInfo <ValidationBlockchainDataSet>(
sqlCommandText,
AdoNetLayer.CreateInputParameter("@MaxBlockchainFileId", SqlDbType.Int, maxBlockchainFileId)));
}
public void UpdateTransactionOutputAddressId()
{
const string sqlGetFirstTXOutUnsetAddressId = @"
SELECT TOP 1 TransactionOutputId
FROM TransactionOutput
WHERE TransactionOutputId > @lastTransactionOutputId
AND OutputAddressId = -1
AND OutputAddress != '0'";
const string sqlUpdateAddressId = @"
UPDATE TransactionOutput
SET OutputAddressId = @mOutputAddressId
WHERE TransactionOutputId >= @currentTransactionOutputId
AND OutputAddress = (
SELECT OutputAddress
FROM TransactionOutput
WHERE TransactionOutputId = @currentTransactionOutputId)";
long OutputAddressId = 0;
long MaxTransactionOutputId = AdoNetLayer.ConvertDbValue <long>(
this.adoNetLayer.ExecuteScalar(
@"select TOP 1 TransactionOutputId
from TransactionOutput
order by TransactionOutputId desc"));
long TransactionOutputCount = -1;
while (TransactionOutputCount < MaxTransactionOutputId)
{
TransactionOutputCount = AdoNetLayer.ConvertDbValue <long>(this.adoNetLayer.ExecuteScalar(sqlGetFirstTXOutUnsetAddressId,
AdoNetLayer.CreateInputParameter("@lastTransactionOutputId", SqlDbType.BigInt, TransactionOutputCount)));
this.adoNetLayer.ExecuteStatementNoResult(sqlUpdateAddressId,
AdoNetLayer.CreateInputParameter("mOutputAddressId", SqlDbType.BigInt, OutputAddressId),
AdoNetLayer.CreateInputParameter("currentTransactionOutputId", SqlDbType.BigInt, TransactionOutputCount));
OutputAddressId++;
}
}
