Exploring counterparty.db using a Sqlite GUI

Support FAQ
1
  • Get Sqlite Explorer/Studio from SQLiteStudio or Sqlite Browser (http://sqlitebrowser.org)
  • Make a copy of counterparty.db and open it in Sqlite Explorer (we haven’t used the Browser for this how-to)
  • In Sqlite Explorer, go to Tools > Open SQL Editor

Example: Find all addresses with LTBCOIN

SELECT address FROM balances WHERE asset='LTBCOIN';

Example: Find asset ownership of particular address

This allows one to check his recent asset balance:

SELECT quantity,address FROM balances WHERE asset='LTBCOIN' AND address='1126tkRhAyYDhw3V45oWs5eWU7DY4WnBoF';

Result:

Balance Address
147	1126tkRhAyYDhw3V45oWs5eWU7DY4WnBoF

To find all token balances for an address:

SELECT quantity,address FROM balances WHERE address='1126tkRhAyYDhw3V45oWs5eWU7DY4WnBoF'

Example: Explore the claim that BTC_Pay didn’t work for many Counterwallet users

We can decide what we’re looking for (for example, expired matches which usually happened because people logged off and forgot about Step 2 in BTC_Pay):

SELECT * FROM btcpays where status = 'invalid: order match expired';

From Bitcoin (BTC) Block 315,845 — SoChain we know no expired match in BTC Pay’s happened since mid August 2014 (soon after that BTC_Pay was removed from Counterwallet).

By graphing the results in Excel we can also display the amount of each failed BTC_Pay:

We can see that except 4 orders, all others were tiny. A loud individual (or four) managed to shut down BTC_Pay. How much in DEx transactions happened in BTC?

SELECT SUM(btc_amount) FROM btcpays;

Result:

SUM(btc_amount)
30452050546

Divided by 100 million (satoshis) that is 3 BTC. So it could be said that BTC_Pay wasn’t widely used to begin with, but some users who did use it weren’t happy with the way it worked.

Note:

  • There is a handful of other invalid types in transaction history - maybe those users were unhappy as well
  • We cannot attribute all expired matches to the users’ ignorance - sometimes the exchange rate moved against the BTC seller and they’d abandon the transaction rather than lose more on it

Example: Export Assets & Asset ID’s for later processing in Excel:

Select a table, right-click, pick Export Table, choose CSV and Unicode 2.0 UTF-8.

Tips

  • SQL Statements from the GUI can be saved for convenient execution at a later time
  • Save often, this GUI crashes fairly frequently (Sqlite Studio)
  • For more demanding use, save the DB to SSD or Flash drive
  • Sqlite Browser has a “browse data” feature which is convenient if your disk is fast and CPU powerful

Screenshot (Sqlite Studio)

Pasted image974×655 54.3 KB
S

1 Like
2

##Example: Chart DEx Orders

  • In SQLite Studio, go to Tools > Export. Select “Query results”.

  • Create a query. In this case, it’s a query that dumps the entire orders table. To get the bare minimum for the chart below, use select block_index from orders;

  • Pick export format, destination, encoding…

  • Open your CSV file in Excel or other tool, add an index (first column), select index and block_index column (which shows bitcoin blocks which carry DEx orders) and go to Insert > Scatter.

Pasted image779×441 6.08 KB

Counterparty appeared between block height 250,000 and 300,000, so prior to that there were no DEx orders, which explains why the line starts in the middle of the chart. We can see that so far close to 30,000 orders were placed on the DEx.

This process could be automated from the CLI and the chart could be better formatted (example below; the straight red line represents linear trend), but this should be enough to get you started.

Pasted image790×452 8.43 KB

1 Like