Title: Bitcoin network database dump in postgresql (download link inside)
Post by: genjix on August 20, 2011, 01:13:28 AM
Using libbitcoin (https://bitcointalk.org/index.php?topic=30646.0)'s poller program, I've downloaded all of the bitcoin network database in postgresql for anyone interested.
Here is a dump of the bitcoin database (all blocks, transactions, scripts, ...): http://libbitcoin.org/bitcoin-sql.tar.bz2
Title: Re: Bitcoin network database dump in postgresql (download link inside)
Post by: jackjack on August 20, 2011, 01:15:26 AM
404, but that looks awesome
Title: Re: Bitcoin network database dump in postgresql (download link inside)
Post by: genjix on August 20, 2011, 01:17:51 AM
404, but that looks awesome
reload
Title: Re: Bitcoin network database dump in postgresql (download link inside)
Post by: genjix on August 20, 2011, 04:49:11 AM
I just committed a postgres function to calculate difficulty: DROP DOMAIN IF EXISTS target_type CASCADE; CREATE DOMAIN target_type AS NUMERIC(68, 0) CHECK (VALUE <= 26959535291011309493156476344723991336010898738574164086137773096960 AND VALUE >= 0);
CREATE OR REPLACE FUNCTION extract_target(bits_head INT, bits_body INT) RETURNS target_type AS $$ BEGIN RETURN bits_body * (2^(8*(CAST(bits_head AS target_type) - 3))); END; $$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION difficulty(bits_head INT, bits_body INT) RETURNS target_type AS $$ BEGIN RETURN extract_target(CAST(x'1d' AS INT), CAST(x'00ffff' AS INT)) / extract_target(bits_head, bits_body); END; $$ LANGUAGE plpgsql;
So if you want to calculate the total difficulty of the block chain you can do: SELECT SUM(difficulty(bits_head, bits_body)) FROM blocks WHERE depth IS NOT NULL; Total blockchain difficulty is: 20077745448 You can play around with that syntax to select certain amounts of blocks: SELECT SUM(difficulty(bits_head, bits_body)) FROM blocks WHERE depth > 400 AND depth <= 500; .etc have fun
Title: Re: Bitcoin network database dump in postgresql (download link inside)
Post by: genjix on October 23, 2011, 02:20:46 AM
updated dump above:
- more efficient db format - 150256 blocks - faster (uses bytea for hashes, compacted scripts)
Title: Re: Bitcoin network database dump in postgresql (download link inside)
Post by: btc_artist on November 30, 2011, 06:51:24 PM
libbitcoin.org seems to be unreachable?
Title: Re: Bitcoin network database dump in postgresql (download link inside)
Post by: zellfaze on November 30, 2011, 06:59:02 PM
Anyone feel like porting this to MySQL? Not everyone uses postgresql. The more formats available the better.
Title: Re: Bitcoin network database dump in postgresql (download link inside)
Post by: grondilu on June 21, 2012, 11:51:46 AM
Anyone feel like porting this to MySQL? Not everyone uses postgresql. The more formats available the better.
I am working on it for my perl library. It's quite different from genjix's approach, though. In particular, I want to use triggers to create the chain tree structure. First I wanted to use ideas such as this one (http://www.codeproject.com/Articles/8355/Trees-in-SQL-databases), but I soon realized that with a long chain it would cost a lot of memory. For instance with 200,000 chained blocks, the tree table would have 40 billion records. With at least 64 bytes per records, that would be about 1 Po, which is silly. So I'm still thinking of better structures, since I don't quite understand Genjix's. 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
|