Database schema

To obtain the schema for the database generated by this tool run:
BitcoinDatabaseGenerator.exe /ShowDbSchema

Remember that you can use the redirect syntax to redirect the output to a file:
BitcoinDatabaseGenerator.exe /ShowDbSchema > schema.txt

or the pipe syntax to redirect the output to the clipboard:
BitcoinDatabaseGenerator.exe /ShowDbSchema | clip

The schema for the database generated by this tool is:

-- ==========================================================================
-- Note about all hashes:
-- The hashes are stored in reverse order from what is the normal result 
-- of hashing. This is done to be consistent with sites like blockchain.info 
-- and blockexporer that display hashes in 'big endian' format.
-- ==========================================================================

-- ==========================================================================
-- TABLE: BlockchainFile
-- Contains information about the blockchain files that were processed.
-- ==========================================================================
CREATE TABLE BlockchainFile (
    BlockchainFileId                     INT PRIMARY KEY            NOT NULL,
    BlockchainFileName                   NVARCHAR (300)             NOT NULL
);

ALTER TABLE BlockchainFile ADD CONSTRAINT BlockchainFile_BlockchainFileName UNIQUE([BlockchainFileName])


-- ==========================================================================
-- TABLE: Block
-- Contains information about the Bitcoin blocks.
-- ==========================================================================
CREATE TABLE Block (
    BlockId                         BIGINT PRIMARY KEY              NOT NULL,
    BlockchainFileId                INT                             NOT NULL,
    BlockVersion                    INT                             NOT NULL,

    -- Note: hash is in reverse order.
    BlockHash                       VARBINARY (32)                  NOT NULL,

    -- Note: hash is in reverse order.
    PreviousBlockHash               VARBINARY (32)                  NOT NULL,

    BlockTimestamp                  DATETIME                        NOT NULL
);


-- ==========================================================================
-- TABLE: BitcoinTransaction
-- Contains information about the Bitcoin transactions.
-- ==========================================================================
CREATE TABLE BitcoinTransaction (
    BitcoinTransactionId            BIGINT PRIMARY KEY              NOT NULL,
    BlockId                         BIGINT                          NOT NULL,

    -- Note: hash is in reverse order.
    TransactionHash                 VARBINARY (32)                  NOT NULL,

    TransactionVersion              INT                             NOT NULL,
    TransactionLockTime             INT                             NOT NULL
);

CREATE INDEX IX_BitcoinTransaction_TransactionHash ON BitcoinTransaction(TransactionHash)
CREATE INDEX IX_BitcoinTransaction_BlockId ON BitcoinTransaction(BlockId)


-- ==========================================================================
-- TABLE: TransactionInput
-- Contains information about the Bitcoin transaction inputs.
-- ==========================================================================
CREATE TABLE TransactionInput (
    TransactionInputId              BIGINT PRIMARY KEY              NOT NULL,
    BitcoinTransactionId            BIGINT                          NOT NULL,

    -- This is the Id of the source transaction output.
    -- Set to NULL when this input has no corresponding output.
    -- Set to -1 in initial stages of the data transfer before the data from 
    -- TransactionInputSource is processed.
    -- The values in this column are calculated based on the links presented in
    -- the original blockchain and that are saved in table TransactionInputSource.
    -- This column is provided as a way to optimize queries where a join is
    -- required between an input and its corresponding output. 
    SourceTransactionOutputId       BIGINT                          NULL
);

CREATE INDEX IX_TransactionInput_BitcoinTransactionId ON TransactionInput(BitcoinTransactionId)
CREATE INDEX IX_TransactionInput_SourceTransactionOutputId ON TransactionInput(SourceTransactionOutputId)


-- ==========================================================================
-- TABLE: TransactionInputSource
-- Contains information about the source of the Bitcoin transaction inputs.
-- This table contains links between transaction inputs and their
-- corresponding source outputs. The data here is obtained from the blockchain
-- files that are transferred in the database. However, the transfer includes 
-- a stage where data from this table is processed and a more direct link is 
-- calculated and saved in TransactionInput.SourceTransactionOutputId. Any 
-- queries where a join is required between an input and its corresponding 
-- output should use TransactionInput.SourceTransactionOutputId.
-- ==========================================================================
CREATE TABLE TransactionInputSource (
    TransactionInputId              BIGINT PRIMARY KEY              NOT NULL,

    -- The hash of the transaction that contains the output that is the source
    -- of this input.
    -- Note: hash is in reverse order.
    SourceTransactionHash           VARBINARY (32)                  NOT NULL,

    -- The index of the output that will be consumed by this input.
    -- The index is a zero based index in the list of outputs of the 
    -- transaction that it belongs to.
    -- Set to -1 to indicate that this input refers to no previous output.
    SourceTransactionOutputIndex    INT                             NULL,
);


-- ==========================================================================
-- TABLE: TransactionOutput
-- Contains information about the Bitcoin transaction outputs.
-- ==========================================================================
CREATE TABLE TransactionOutput (
    TransactionOutputId             BIGINT PRIMARY KEY              NOT NULL,
    BitcoinTransactionId            BIGINT                          NOT NULL,
    OutputIndex                     INT                             NOT NULL,
    OutputValueBtc                  NUMERIC(20,8)                   NOT NULL,
    OutputScript                    VARBINARY (MAX)                 NOT NULL
);

CREATE INDEX IX_TransactionOutput_BitcoinTransactionId ON TransactionOutput(BitcoinTransactionId)


-- ==========================================================================
-- TABLE: BtcDbSettings
-- System reserved. Key-value pairs containing system data.
-- ==========================================================================
CREATE TABLE BtcDbSettings (
    PropertyName                    NVARCHAR (32)                   NOT NULL,
    PropertyValue                   NVARCHAR (MAX)                  NOT NULL
)
GO

INSERT INTO BtcDbSettings(PropertyName, PropertyValue) VALUES('DB-VERSION', 1)
GO

--=============================================================================
-- VIEW View_TransactionAggregated
-- Use this view retrieve aggregated data for a transaction including the 
-- total input, output and transaction fees. 
-- Example: 
--      SELECT * FROM View_TransactionAggregated WHERE BitcoinTransactionId = 914224
--=============================================================================
CREATE VIEW View_TransactionAggregated AS 
SELECT 
    BitcoinTransactionId,
    BlockId,
    TransactionHash,
    TransactionVersion,
    TransactionLockTime,
    ISNULL(TransactionInputCount, 0) AS TransactionInputCount,
    ISNULL(TotalInputBtc, 0) AS TotalInputBtc,
    ISNULL(TransactionOutputCount, 0) AS TransactionOutputCount,
    ISNULL(TotalOutputBtc, 0) AS TotalOutputBtc,
    ISNULL(TotalInputBtc - TotalOutputBtc, 0) AS TransactionFeeBtc,
    ISNULL(TotalUnspentOutputBtc, 0) AS TotalUnspentOutputBtc
FROM (
    SELECT 
        BitcoinTransaction.BitcoinTransactionId,
        BitcoinTransaction.BlockId,
        BitcoinTransaction.TransactionHash,
        BitcoinTransaction.TransactionVersion,
        BitcoinTransaction.TransactionLockTime,
        (   SELECT COUNT(1) 
            FROM TransactionInput
            WHERE BitcoinTransaction.BitcoinTransactionId = TransactionInput.BitcoinTransactionId 
        ) AS TransactionInputCount,
        (   SELECT SUM(TransactionOutput.OutputValueBtc)
            FROM TransactionInput 
            INNER JOIN TransactionOutput ON TransactionOutput.TransactionOutputId = TransactionInput.SourceTransactionOutputId
            WHERE TransactionInput.BitcoinTransactionId = BitcoinTransaction.BitcoinTransactionId
        ) AS TotalInputBtc,
        (   SELECT COUNT(1) 
            FROM TransactionOutput
            WHERE BitcoinTransaction.BitcoinTransactionId = TransactionOutput.BitcoinTransactionId 
        ) AS TransactionOutputCount,
        (   SELECT SUM(TransactionOutput.OutputValueBtc)
            FROM TransactionOutput
            WHERE TransactionOutput.BitcoinTransactionId = BitcoinTransaction.BitcoinTransactionId
        ) AS TotalOutputBtc,
        (   SELECT SUM(TransactionOutput.OutputValueBtc)
            FROM TransactionOutput
            LEFT OUTER JOIN TransactionInput ON TransactionInput.SourceTransactionOutputId = TransactionOutput.TransactionOutputId
            WHERE 
                TransactionOutput.BitcoinTransactionId = BitcoinTransaction.BitcoinTransactionId
                AND TransactionInput.TransactionInputId IS NULL
        ) AS TotalUnspentOutputBtc
    FROM BitcoinTransaction) AS TransactionAggregated

GO

--=============================================================================
-- VIEW View_BlockAggregated
-- Use this view retrieve aggregated data for a block including the 
-- total input, output and transaction fees. 
-- Example: 
--      SELECT * FROM View_BlockAggregated WHERE BlockId = 134181
--=============================================================================
CREATE VIEW View_BlockAggregated AS 
SELECT 
    Block.BlockId,
    Block.BlockchainFileId,
    Block.BlockVersion,
    Block.BlockHash,
    Block.PreviousBlockHash,
    Block.BlockTimestamp,
    BlockAggregated.TransactionCount,
    BlockAggregated.TransactionInputCount,
    BlockAggregated.TotalInputBtc,
    BlockAggregated.TransactionOutputCount,
    BlockAggregated.TotalOutputBtc,
    BlockAggregated.TransactionFeeBtc,
    BlockAggregated.TotalUnspentOutputBtc
FROM Block
INNER JOIN (
    SELECT 
        Block.BlockId,
        SUM(1) 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 Block
    INNER JOIN View_TransactionAggregated ON Block.BlockId = View_TransactionAggregated.BlockId
    GROUP BY Block.BlockId
    ) AS BlockAggregated ON BlockAggregated.BlockId = Block.BlockId

GO

--=============================================================================
-- VIEW View_BlockchainFileCounts
-- Use this view retrieve data about a blockchain file.
-- Example: 
--      SELECT * FROM View_BlockchainFileCounts WHERE BlockchainFileId = 100
--=============================================================================
CREATE VIEW View_BlockchainFileCounts AS 
SELECT 
    BlockchainFile.BlockchainFileId,
    BlockchainFileName,
    ( SELECT COUNT(1) FROM Block WHERE Block.BlockchainFileId = BlockchainFile.BlockchainFileId ) AS BlockCount,
    ( SELECT COUNT(1) 
      FROM BitcoinTransaction 
      INNER JOIN Block ON Block.BlockId = BitcoinTransaction.BlockId
      WHERE Block.BlockchainFileId = BlockchainFile.BlockchainFileId 
    ) AS TransactionCount,
    ( SELECT COUNT(1) 
      FROM TransactionInput
      INNER JOIN BitcoinTransaction ON BitcoinTransaction.BitcoinTransactionId = TransactionInput.BitcoinTransactionId
      INNER JOIN Block ON Block.BlockId = BitcoinTransaction.BlockId
      WHERE Block.BlockchainFileId = BlockchainFile.BlockchainFileId 
    ) AS TransactionInputCount,
    ( SELECT COUNT(1) 
      FROM TransactionOutput
      INNER JOIN BitcoinTransaction ON BitcoinTransaction.BitcoinTransactionId = TransactionOutput.BitcoinTransactionId
      INNER JOIN Block ON Block.BlockId = BitcoinTransaction.BlockId
      WHERE Block.BlockchainFileId = BlockchainFile.BlockchainFileId 
    ) AS TransactionOutputCount
FROM BlockchainFile
GO