Bitcoin block chain SQL schema

gistfile1.sql

// original found : https://bitcointalk.org/index.php?topic=38246.0

CREATE TABLE blocks (
    hash                char(32) binary primary key,

    version             integer,
    hashPrev            char(32) binary not null,
    hashMerkleRoot      char(32) binary not null,
    nTime               integer unsigned not null,
    nBits               integer unsigned not null,
    nNonce              integer unsigned not null,

    key (hashMerkleRoot),
    key (hashPrev)
);

CREATE TABLE transactions (
    hash                char(32) binary primary key,
    version             integer,
    lockTime            integer unsigned,
);

CREATE TABLE tx_in (
    hash                char(32) binary,
    prevout_hash        char(32) binary,
    prevout_n           integer unsigned,
    scriptSig           blob,
    sequence            integer unsigned,

    primary key (hash, prevout_hash, prevout_n),
    key(hash)
)

CREATE TABLE tx_out (
    tx_out_id           integer unsigned primary key auto_increment,
    hash                char(32) binary,
    value               integer,
    scriptPubKey        blob,

    key (hash)
);

CREATE TABLE Merkle_trees (
    root                char(32) binary not null,
    hash		char(32) binary,
    idx                 integer unsigned not null,
    primary key (root, idx),
    key (root)
);

CREATE TABLE block_tree (
    leaf        char(32) binary,
    node        char(32) binary,
    length      integer unsigned,
    PRIMARY KEY (leaf, node)
);

CREATE VIEW view_blocks AS
SELECT 
HEX(hash) as hash,
version,
HEX(hashPrev) as hashPrev,
HEX(hashMerkleRoot) as hashMerkleRoot,
nTime,
nBits,
nNonce
FROM blocks;

CREATE VIEW orphan_blocks AS
SELECT a.*
FROM blocks a LEFT JOIN blocks b
ON a.hashPrev = b.hash
WHERE b.hash IS NULL;

CREATE VIEW view_orphan_blocks AS
SELECT view_blocks.*
FROM view_blocks INNER JOIN orphan_blocks
ON view_blocks.hash = HEX(orphan_blocks.hash);

CREATE VIEW view_Merkle_trees AS
SELECT HEX(root) as root, HEX(hash) as hash, idx
FROM Merkle_trees;

CREATE VIEW view_block_tree AS
SELECT HEX(leaf) as leaf, HEX(node) as node, length
FROM block_tree;

CREATE VIEW chain_length AS
SELECT leaf, length FROM block_tree WHERE leaf = node;

CREATE VIEW chain_weight AS
SELECT leaf, SUM(work(target(b.nBits))) as weight
FROM block_tree INNER JOIN blocks
ON block_tree.node = blocks.hash
GROUP BY leaf;

CREATE VIEW longest_chain AS
SELECT leaf, max(length) as length
FROM chain_length;

CREATE VIEW heaviest_chain AS
SELECT leaf, max(weight) as weight
FROM chain_weight;

CREATE FUNCTION target (bits float)
RETURNS REAL DETERMINISTIC
RETURN mod(bits, 0x1000000) * pow( 256, bits div 0x1000000 - 3 );

CREATE TRIGGER update_block_tree AFTER INSERT ON blocks 
FOR EACH ROW BEGIN
    INSERT INTO block_tree (leaf, node, length)
    SELECT new.hash, new.hash, length+1
    FROM block_tree WHERE leaf = new.hashPrev;

    UPDATE block_tree
    SET leaf=new.hash
    WHERE leaf=new.hashPrev;

END;

# vim: ft=mysql