Bitcoin Blockchain SQL Schema for MySQL – Crypto.BI Toolbox

This SQL schema was created for Crypto.BI Toolbox and can be used by anyone exploring Bitcoin with the help of a MySQL RDBMS.

How the data is inserted/retrieved can be checked out at our Github repository, under the db/ subtree.

The cryptobitoolbox_bitcoinDB name is not mandatory.

You can choose any other schema, just remember to configure Toolbox to use a different name.

drop database cryptobitoolbox_bitcoin;

-- Crypto.BI Toolbox (https://Crypto.BI/)
-- Author: José Fonseca 2019-10-03 (https://zefonseca.com)
-- Copyright (C) 2019 - José Fonseca
-- See LICENSE for licensing details.

-- Bitcoin-specific blockchain schema

create database if not exists cryptobitoolbox_bitcoin;

use cryptobitoolbox_bitcoin;

create table cb_blockchain(
	table_seq bigint NOT NULL AUTO_INCREMENT,
    n_version int not null default 0,
    hash_this_block char(32) BINARY,
    hash_prev_block char(32) BINARY not null default X'0000000000000000000000000000000000000000000000000000000000000000',
    hash_merkle_root char(32) BINARY UNIQUE not null default X'0000000000000000000000000000000000000000000000000000000000000000',
    hash_next_block char(32) BINARY default null, -- not standard, optional
    n_time int not null default 0,
    n_bits int not null default 0,
    nonce int not null default 0,
    block_height int not null default 0,
    PRIMARY KEY by_table_seq USING BTREE (table_seq),
    KEY by_hash_this_block USING HASH (hash_this_block),
    KEY by_hash_prev_block USING HASH (hash_prev_block),
    KEY by_hash_merkle_root USING HASH (hash_merkle_root),
    KEY by_hash_next_block USING HASH (hash_next_block),
    KEY by_n_time USING BTREE (n_time),
    KEY by_n_bits USING BTREE (n_bits),
    KEY by_nonce USING BTREE (nonce),
    KEY by_n_version USING BTREE (n_version),
    KEY by_block_height USING BTREE (block_height)
) CHARACTER SET binary ENGINE=MyISAM;

create table cb_blockchain_files(
	table_seq bigint NOT NULL AUTO_INCREMENT,
    hash_this_block char(32) BINARY,
    filename varchar(256) not null,
    byte_offset int default null,
    PRIMARY KEY by_table_seq USING BTREE (table_seq),
    KEY by_hash_this_block USING HASH (hash_this_block),
    KEY by_filename USING HASH (filename)
) CHARACTER SET binary ENGINE=MyISAM;

create table cb_tx(
	table_seq bigint NOT NULL AUTO_INCREMENT,
    hash_this_tx char(32) BINARY,
    n_version int not null default 1,
    has_witness tinyint(1) not null,
    in_counter int not null,
    out_counter int not null,
    lock_time int not null default 0,
    block_order int not null default 0, -- order of this tx within the block
    witness_hash char(32) BINARY not null default X'0000000000000000000000000000000000000000000000000000000000000000',
    hash_block char(32) BINARY not null default X'0000000000000000000000000000000000000000000000000000000000000000',
    PRIMARY KEY by_table_seq USING BTREE (table_seq),
    KEY by_hash_this_tx USING HASH (hash_this_tx),
    KEY by_has_witness USING HASH (has_witness),
    KEY by_witness_hash USING HASH (witness_hash),
    KEY by_hash_block USING HASH (hash_block),
    KEY by_block_order USING BTREE (block_order),
    KEY by_lock_time USING BTREE (lock_time),
    KEY by_n_version USING BTREE (n_version)
) CHARACTER SET binary ENGINE=MyISAM;

create table cb_tx_in(
	table_seq bigint NOT NULL AUTO_INCREMENT,
    txid char(32) BINARY, -- the vout TX
    n_vout int not null,
    hash_this_tx char(32) BINARY, -- the TX this vin is on
    n_sequence bigint not null,
    PRIMARY KEY by_table_seq USING BTREE (table_seq),
    KEY by_hashTxN USING HASH (hash_this_tx, n_sequence),
    KEY by_n_sequence USING BTREE (n_sequence),
    KEY by_txid(txid),
	KEY by_nvout(n_vout)
)CHARACTER SET binary ENGINE=MyISAM;

create table cb_tx_out(
	table_seq bigint NOT NULL AUTO_INCREMENT,
    satoshis bigint not null,
    n_vout int not null,
    hash_this_tx char(32) BINARY, -- the TX this vout is on
    hash_tx_spent char(32) BINARY DEFAULT NULL, -- if not null, tx is spent at hash_tx_spent (optimization)
    script_req_sigs int not null default 1,
    script_type tinyint, -- 0 pubkeyhash, 1 scripthash, 2 nonstandard
    PRIMARY KEY by_table_seq USING BTREE (table_seq),
    KEY by_hash_tx_prevout USING HASH (hash_this_tx, n_vout),
    KEY by_satoshis USING BTREE (satoshis),
    KEY by_hash_tx_spent USING HASH (hash_tx_spent),
    KEY by_script_req_sigs USING BTREE (script_req_sigs),
    KEY by_script_type USING BTREE (script_type)
)CHARACTER SET binary ENGINE=MyISAM;

create table cb_tx_out_addresses(
	table_seq bigint NOT NULL AUTO_INCREMENT,
    n_vout int not null,
    hash_tx char(32) BINARY, -- the TX this vout is on
    address varchar(90) NOT NULL,
    script_req_sigs int not null default 1,
    script_type tinyint, -- 0 pubkeyhash, 1 scripthash, 2 nonstandard, 3 nulldata
    PRIMARY KEY by_table_seq USING BTREE (table_seq),
    KEY by_hashTxPrevout USING HASH (hash_tx, n_vout),
    KEY by_address USING HASH (address),
    KEY by_script_req_sigs USING BTREE(script_req_sigs),
    KEY by_script_type USING HASH(script_type)
)CHARACTER SET binary ENGINE=MyISAM;

create table cb_address_graph(
    cag_id bigint NOT NULL AUTO_INCREMENT,
    n_vout int not null,
    tx_from char(32) BINARY, -- the TX the vout is in
    n_vin int not null,
    tx_to char(32) BINARY, -- the TX the vin is in
    address_from varchar(90) NOT NULL, -- 90 char max bech32 address len
    address_to varchar(90) NOT NULL,
    satoshis bigint not null,
    n_time int not null default 0, -- copied from the block
    PRIMARY KEY by_cag_id USING BTREE (cag_id),
    KEY by_satoshis USING BTREE (satoshis),
    KEY by_n_time USING BTREE (n_time),
    KEY by_adress_from USING HASH (address_from),
    KEY by_address_to USING HASH (address_to),
    KEY by_tx_from  USING HASH (tx_from),
    KEY by_tx_to  USING HASH (tx_to),
    KEY by_n_vout USING BTREE (n_vout),
    KEY by_n_vin USING BTREE (n_vin)
) CHARACTER SET binary ENGINE=MyISAM;

create table cb_address_balance(
     cab_id bigint NOT NULL AUTO_INCREMENT,
     address varchar(90) NOT NULL, -- 90 char max bech32 address len
     satoshis bigint not null,
     PRIMARY KEY by_cag_id USING BTREE (cab_id),
     KEY by_satoshis USING BTREE (satoshis),
     KEY by_adress USING HASH (address)
) CHARACTER SET binary ENGINE=MyISAM;

-- this is where we save the information we have
-- about certain nodes (tx, blocks, etc)
create table cb_info_nodes(
    cin_id bigint NOT NULL AUTO_INCREMENT,
    block_hash char(32) BINARY CHARACTER SET BINARY,
    tx_hash char(32) BINARY CHARACTER SET BINARY,
    address varchar(90) CHARACTER SET BINARY,
    content text,
    PRIMARY KEY by_cin_id USING BTREE (cin_id),
    KEY by_block_hash USING HASH (block_hash),
    KEY by_tx_hash USING HASH (tx_hash),
    KEY by_address USING HASH (address),
    KEY by_content USING BTREE (content(1024))
)  CHARACTER SET utf8 ENGINE=InnoDB;

--
--
-- MANUALLY INSERT GENESIS BLOCK
-- (GENESIS WAS PREVIOUSLY INSERTED BY HAND.)
-- LEFT HERE FOR REFERENCE.
--
-- INSERT INTO cb_blockchain(n_version, hash_this_block, hash_prev_block,hash_merkle_root,hash_next_block, n_time, n_bits, nonce, block_height)
-- VALUES(1, X'000000000019d6689c085ae165831e934ff763ae46a2a6c172b3f1b60a8ce26f', X'0000000000000000000000000000000000000000000000000000000000000000',
--       X'4a5e1e4baab89f3a32518a88c31bc87f618f76673e2cc77ab2127b7afdeda33b', X'00000000839a8e6886ab5951d76f411475428afc90947ee320161bbf18eb6048',
--       1231006505, 486604799, 2083236893, 0);
--
--

Links

libbitcoin-perl MySQL Schema

How to store Bitcoin values in a MySQL database? floats are bad… right?

Store bitcoin transactions in mysql

How to store private key in MySQL database?

how to store bitcoin and other currencies in mysql database

How to store cryptocurrency balance in mysql?

Best way to store bitcoin values in MySql?

How to calculate and store Bitcoin balances with PHP and MySql?

Why are bitcoin transactions stored in a blockchain database and not in a more mature database like MySQL?

Comments

Meta