This SQL schema was created for Crypto.BI Toolbox.
The same, or similar, SQL schema can be used by anyone exploring Bitcoin with the help of a MySQL RDBMS. Although we didn’t test this SQL with other RDBMS’, it should be fully compatible with MariaDB and any other MySQL-derived system.
How the data is inserted/retrieved to/from this schema can be checked out at our Github repository, under the db/ subtree.
Note: The cryptobitoolbox_bitcoinDB name is not mandatory. To use this SQL schema with crypto.bi Toolbox, you can choose any other schema name. Just remember to configure Toolbox accordingly.
drop database cryptobitoolbox_bitcoin;
-- Crypto.BI Toolbox (https://Crypto.BI/)
-- 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);
--
--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?