71
72

BlockchainSQL.io: query the Bitcoin blockchain in SQL free of charge (blockchainsql.io)

HermanSchoenfeldredditor for 2 months が 投稿

全 34 件のコメント
並べ替え:
ベスト
トップ新着論争中古い順Q&A

[–]Dude-Lebowski 7ポイント8ポイント  (1子コメント)

UPDATE doesn't seem to work.

[–]dsterry 6ポイント7ポイント  (0子コメント)

It does. You just have to try multiple times until you get the right hash.

[–]Satoshi- 7ポイント8ポイント  (0子コメント)

Time to do an SQL Injection into the Blockchain and steal some Bitcoin muhahaha.

lol

[–]alcio 2ポイント3ポイント  (1子コメント)

The site won't load for me :(

[–]HermanSchoenfeldredditor for 2 months[S] 0ポイント1ポイント  (0子コメント)

Sorry, had some load problems. Am tuning server slowly, it should be better now.

[–]blockonomics_co 1ポイント2ポイント  (2子コメント)

Nice project. I would like to use this, but not working. Tried
select count(*) from block
Gave me error No column name was specified for column 1 of '__UNPAGED_RESULT'.

Few more suggestions

  • Would be good to have some example queries
  • Also I see you haven't indexed addresses, would be good to support queries like find address with largest number of bitcoins

[–]HermanSchoenfeldredditor for 2 months[S] 2ポイント3ポイント  (1子コメント)

The queries run in a CTE, so just do

select count(*) as Result from Block

You can use "Load Template" to load query templates.

[–]blockonomics_co 0ポイント1ポイント  (0子コメント)

Thanks ... templates weren't loading when I tried. Working now. Will play around!

[–]kaibakker 1ポイント2ポイント  (5子コメント)

Wow cool, I though about making something similar about a year ago. Great that you managed to do so. I think it can be really valuable for many different use cases. Make sure it keeps running and that it is save against most attacks.

[–]HermanSchoenfeldredditor for 2 months[S] 0ポイント1ポイント  (4子コメント)

Thanks. It took a while and was a learning exercise for me, parsing bitcoin at the raw byte level. I hope to provide this service free of charge for good.

[–]fusillini [スコア非表示]  (2子コメント)

Consider publishing this as a payable api in the 21 marketplace :) great work!

[–]dsterry [スコア非表示]  (0子コメント)

This makes total sense. An API about the blockchain, paid for with mining, payments tracked off-chain. Only fear is that he'll corner the market on coins mined by Bitcoin Computers.

[–]HermanSchoenfeldredditor for 2 months[S] [スコア非表示]  (0子コメント)

Thanks for the tip.

[–]openvpn_squidredditor for 1 month 0ポイント1ポイント  (0子コメント)

Amazing work. Thank you.

[–]teaearlgraycold 1ポイント2ポイント  (1子コメント)

Is it possible to get a connection string so that we can query the dB without a web interface?

[–]HermanSchoenfeldredditor for 2 months[S] [スコア非表示]  (0子コメント)

No :-). You'd need to run your own for that (commercially available).

[–]dsterry 1ポイント2ポイント  (1子コメント)

How much storage space does it take to host this? How long to build the database? Can others host their own?

[–]HermanSchoenfeldredditor for 2 months[S] [スコア非表示]  (0子コメント)

It's big and depends on the DBMS you choose. If you trim out script data, it's 4 - 5 x the Bitcoin Core's blockchain. If you have all the data, it's 10 x (500+ GB right now).

I sell the server product for people and organisations who want their own version of this.

[–]loserkids 1ポイント2ポイント  (1子コメント)

Wow this is a great tool. Have a beer on me /u/changetip

[–]changetip 0ポイント1ポイント  (0子コメント)

HermanSchoenfeld received a tip for a beer (5,388 bits/$3.49).

what is ChangeTip?

[–]pinhead26 1ポイント2ポイント  (1子コメント)

This is so very cool. Playing around with:

Blocks that are exactly 1 MB:

SELECT * from Block where size=1000000

Blocks that signal Classic + CSV (version 0x30000001):

SELECT * from Block where version=805306369 ORDER BY TimeStampUnix

I think one thing that might be cool is in the results list if the rows returned were links to the block explorer so you could go down some rabbit holes.

Also, what's the difference between block Height and ID? Are you keeping track of orphans as well?

[–]HermanSchoenfeldredditor for 2 months[S] [スコア非表示]  (0子コメント)

Nice. You can save your query too, and share your unique URL.

Will update hash column as link for explorer, good idea.

[–]RoqueNE 0ポイント1ポイント  (0子コメント)

That is a great idea! Thank you!

[–]DaGojiraredditor for 2 months [スコア非表示]  (1子コメント)

This is great, thanks so much!

select count(*) as count from block where FeesBTC>1

To get a count of especially lucrative blocks.

[–]yo7nyy899nu9un98n [スコア非表示]  (0子コメント)

0000000000000000042450AD2BE4F2B6439ED39F70716A7575440D462CF165D9

Seems to be the block with highest fees mined to date.

Fee: 291.53275103 BTC

Amazing.

[–]lpersona [スコア非表示]  (1子コメント)

Nice tool! How do you refer to Transaction.Index field in a query?

[–]HermanSchoenfeldredditor for 2 months[S] [スコア非表示]  (0子コメント)

Because "Index" is a reserved word in SQL Server, you just wrap it in square parenthesis [ ].

Transaction.[Index]

Check out templates for guides on common queries.

[–]gibboncub [スコア非表示]  (1子コメント)

Very cool! Which database engine is backing this?

Also is it possible to add a limit clause?

[–]HermanSchoenfeldredditor for 2 months[S] [スコア非表示]  (0子コメント)

The product runs on SQL Server, MySQL, Oracle, Sqlite.

No need for limit clauses, it only will fetch 10 records at a time (query runs inside CTE).

[–]Fiach_Dubh [スコア非表示]  (0子コメント)

have you considered integrating this with 21.co to charge satoshi per http request?

[–]C0INX4 [スコア非表示]  (0子コメント)

Can't query more than 10 results at once in the free version?

I wished it was at least 100 ;(

[–]rockingBit [スコア非表示]  (2子コメント)

Fantastic work. I have a few questions...

  1. Is it SELECT only or INSERT/UPDATE/DELETE works?

  2. Is the DB continuously being updated with new blockchain data?

[–]gerikson [スコア非表示]  (0子コメント)

How exactly would you update, insert or delete immutable data?

[–]HermanSchoenfeldredditor for 2 months[S] [スコア非表示]  (0子コメント)

  1. Only SELECT :-)
  2. Yes, realtime update.