Bitcoin Forum
December 09, 2016, 01:59:34 PM *
News: To be able to use the next phase of the beta forum software, please ensure that your email address is correct/functional.
 
   Home   Help Search Donate Login Register  
Pages: [1]
  Print  
Author Topic: Bitcoin network database dump in postgresql (download link inside)  (Read 7247 times)
genjix
Legendary
*
expert
Offline Offline

Activity: 1232


View Profile
August 20, 2011, 01:13:28 AM
 #1

Using libbitcoin'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
1481291974
Hero Member
*
Offline Offline

Posts: 1481291974

View Profile Personal Message (Offline)

Ignore
1481291974
Reply with quote  #2

1481291974
Report to moderator
1481291974
Hero Member
*
Offline Offline

Posts: 1481291974

View Profile Personal Message (Offline)

Ignore
1481291974
Reply with quote  #2

1481291974
Report to moderator
1481291974
Hero Member
*
Offline Offline

Posts: 1481291974

View Profile Personal Message (Offline)

Ignore
1481291974
Reply with quote  #2

1481291974
Report to moderator
Advertised sites are not endorsed by the Bitcoin Forum. They may be unsafe, untrustworthy, or illegal in your jurisdiction. Advertise here.
1481291974
Hero Member
*
Offline Offline

Posts: 1481291974

View Profile Personal Message (Offline)

Ignore
1481291974
Reply with quote  #2

1481291974
Report to moderator
1481291974
Hero Member
*
Offline Offline

Posts: 1481291974

View Profile Personal Message (Offline)

Ignore
1481291974
Reply with quote  #2

1481291974
Report to moderator
1481291974
Hero Member
*
Offline Offline

Posts: 1481291974

View Profile Personal Message (Offline)

Ignore
1481291974
Reply with quote  #2

1481291974
Report to moderator
jackjack
Hero Member
*****
Offline Offline

Activity: 882


May Bitcoin be touched by his Noodly Appendage


View Profile
August 20, 2011, 01:15:26 AM
 #2

404, but that looks awesome

Own address: 19QkqAza7BHFTuoz9N8UQkryP4E9jHo4N3 - Pywallet support: 1AQDfx22pKGgXnUZFL1e4UKos3QqvRzNh5 - Bitcointalk++ script support: 1Pxeccscj1ygseTdSV1qUqQCanp2B2NMM2
Pywallet: instructions. Encrypted wallet support, export/import keys/addresses, backup wallets, export/import CSV data from/into wallet, merge wallets, delete/import addresses and transactions, recover altcoins sent to bitcoin addresses, sign/verify messages and files with Bitcoin addresses, recover deleted wallets, etc.
genjix
Legendary
*
expert
Offline Offline

Activity: 1232


View Profile
August 20, 2011, 01:17:51 AM
 #3

404, but that looks awesome

reload
genjix
Legendary
*
expert
Offline Offline

Activity: 1232


View Profile
August 20, 2011, 04:49:11 AM
 #4

I just committed a postgres function to calculate difficulty:

Code:
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
genjix
Legendary
*
expert
Offline Offline

Activity: 1232


View Profile
October 23, 2011, 02:20:46 AM
 #5

updated dump above:

- more efficient db format
- 150256 blocks
- faster (uses bytea for hashes, compacted scripts)
btc_artist
Full Member
***
Offline Offline

Activity: 154


Bitcoin!


View Profile WWW
November 30, 2011, 06:51:24 PM
 #6

libbitcoin.org seems to be unreachable?

BTC: 1CDCLDBHbAzHyYUkk1wYHPYmrtDZNhk8zf
LTC: LMS7SqZJnqzxo76iDSEua33WCyYZdjaQoE
zellfaze
Full Member
***
Offline Offline

Activity: 142


Security Enthusiast


View Profile WWW
November 30, 2011, 06:59:02 PM
 #7

Anyone feel like porting this to MySQL?  Not everyone uses postgresql.  The more formats available the better.

A+, CCENT, CCNA
Security Enthusiast
PHP Coder

Not that I expect anyone to, but should you like my post, please donate:
Donate: 1BRbfqii6Sm9tEUE8A16H7QeDmYFjyBZ7V
grondilu
Legendary
*
Offline Offline

Activity: 1134


View Profile
June 21, 2012, 11:51:46 AM
 #8

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, 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.

Code:

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
Pages: [1]
  Print  
 
Jump to:  

Sponsored by , a Bitcoin-accepting VPN.
Powered by MySQL Powered by PHP Powered by SMF 1.1.19 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!