Skip to content

Latest commit

 

History

History
181 lines (129 loc) · 9.79 KB

index.mdx

File metadata and controls

181 lines (129 loc) · 9.79 KB

export const title = "Query the MM2 SQLite Database"; export const description = "This guide describes how to query the MM2 SQLite database.";

How to Query the MM2 SQLite Database

The Komodo DeFi Framework API stores historical information such as swaps and orders within an SQLite database, located under the DB user data folder with each wallet having its own subfolder represented by a hexideciaml string. This string is shown in the runtime logs of the kdf binary as Public key hash when it starts up.

30 08:39:23, mm2:269] AtomicDEX API 2.1.0-beta_a81f2a101 DT 2024-07-26T23:24:52+03:00
30 08:39:23, mm2_main::mm2::lp_native_dex:500] INFO Version: 2.1.0-beta_a81f2a101 DT 2024-07-26T23:24:52+03:00
30 08:39:23, crypto::crypto_ctx:324] INFO Public key hash: 7d6cbdd91788df3b764247721fe12853ce36b03d

The location of this folder for each operating system is:

  • Linux: $HOME/.kdf/DB/{'{wallet identifying hex string}'}/MM2.db'
  • MacOS: $HOME/Library/Application Support/kdf/DB/{'{wallet identifying hex string}'}/MM2.db'
  • Windows: %APPDATA%\kdf\DB{'{wallet identifying hex string}'\MM2.db'

There are a variety of methods to query sqlite databases. Examples below show how to do a sqlite query in Linux terminal, but first you might need to install sqlite with sudo apt install sqlite3.

The tables and columns available to query are as follows:

my_swaps

This table keeps a record of all swaps successfully performed in this pubkey's MM2.db

ID Name Type Description
0 id INTEGER Primary Key
1 my_coin VARCHAR(255) Coin sent
2 other_coin VARCHAR(255) Coin received
3 uuid VARCHAR(255) Swap UUID
4 started_at INTEGER Timestamp

Query:

sqlite3 ${PATH_TO_MM2_DB_FILE} "SELECT * FROM my_swaps WHERE id=2 LIMIT 1"

Response:

2|MARTY|DOC|7086bc8e-bdaa-44b0-ac9b-01aa8760b62b|1636956829

stats_swaps

This table keeps a detailed record of all swaps performed (including failed) in this pubkey's MM2.db

ID Name Type Description
0 id INTEGER Primary key
1 maker_coin VARCHAR(255) Maker coin
2 taker_coin VARCHAR(255) Taker coin
3 uuid VARCHAR(255) Swap UUID
4 started_at INTEGER Timestamp
5 finished_at INTEGER Timestamp
6 maker_amount DECIMAL Maker coin
7 taker_amount DECIMAL Taker coin
8 is_success INTEGER 1 for successful, 0 for failed
9 maker_coin_ticker VARCHAR(255) Maker coin ticker
10 maker_coin_platform VARCHAR(255) Maker coin platform
11 taker_coin_ticker VARCHAR(255) Taker coin ticker
12 taker_coin_platform VARCHAR(255) Taker coin platform
13 maker_coin_usd_price DECIMAL USD price of maker coin at the time of the swap
14 taker_coin_usd_price DECIMAL USD price of taker coin at the time of the swap
15 taker_pubkey DECIMAL Taker pubkey
16 maker_pubkey DECIMAL Maker pubkey
17 maker_gui VARCHAR(255) Maker application
18 taker_gui VARCHAR(255) Taker application
19 maker_version VARCHAR(255) Maker KDF binary version
20 taker_version VARCHAR(255) Taker KDF binary version

Query:

sqlite3 ${PATH_TO_MM2_DB_FILE} "SELECT * FROM stats_swaps WHERE taker_coin = 'DOGE' and maker_coin = 'DGB' ORDER BY finished_at DESC LIMIT 1;"

Response:

8|DGB|DOGE|c9515636-f5a4-4767-a0af-c69e59086899|1678815183|1678815631|200|28|1|DGB||DOGE||0.0108|0.07673|02d8064eece4fa5c0f8dc0267f68cee9bdd527f9e88f3594a323428718c391ecc2|03a93f666b9030958f282edd2904f0a33278c0c676ae132d2094840fe722f011c3|mm2_777|web_dex web|2.1.0-beta_c5e0e00|2.1.0-beta_af571608c

my_orders

This table keeps a detailed record of all orders placed in this pubkey's MM2.db

ID Name Type Description
0 id INTEGER Primary Key
1 uuid VARCHAR(255) Order UUID
2 type VARCHAR(255) Order Type
3 initial_action VARCHAR(255) Buy or Sell. Setprice maker orders are Sell
4 base VARCHAR(255) Base Coin
5 rel VARCHAR(255) Rel Coin
6 price DECIMAL Order Price
7 volume DECIMAL Order Volume
8 created_at INTEGER Timestamp
9 last_updated INTEGER Timestamp
10 was_taker INTEGER 1 if taker, 2 if maker
11 status VARCHAR(255) Order status

Query:

sqlite3 ${PATH_TO_MM2_DB_FILE} "SELECT * FROM my_orders WHERE base = 'DOC' and rel= 'MARTY' LIMIT 6"

Response:

154|6053016b-e1ba-490f-9501-eafb69b4d3a7|Taker|Buy|DOC|MARTY|0.03|1|1640159991278|1640160021808|0|TimedOut
266|77d79265-da87-48bb-aee3-7cc87f442a55|Maker|Buy|DOC|MARTY|0.0505|3|1640857934304|1640874662778|1|InsufficientBalance
267|4c6341d6-1e89-4c3b-8612-a930754701f2|Taker|Sell|DOC|MARTY|1|2|1640872463330|1640872467129|0|Fulfilled
290|57c2b270-ee73-4a21-8fa4-4b8c2d76fc02|Maker|Buy|DOC|MARTY|0.1|0.1|1641539601576|1641539631823|1|ToMaker
291|9cba3b40-2426-4fbf-80c8-2a65c8661eed|Maker|Sell|DOC|MARTY|1|1|1641539652421|1641539813001|0|Cancelled
294|fedcc1e0-a059-47c6-bbfc-3a61454f1208|Maker|Sell|DOC|MARTY|1|12|1641546891912|1641546891912|0|Created

nodes

This table stores a record of all nodes added for stats collection in this pubkey's MM2.db

ID Name Type Description
0 id INTEGER Primary Key
1 name VARCHAR(255) Node name
2 address VARCHAR(255) Node IP
3 peer_id VARCHAR(255) Node PeerID

Query:

sqlite3 ${PATH_TO_MM2_DB_FILE} "SELECT * FROM nodes WHERE name = 'dragonhound_DEV'"

Respose:

37|dragonhound_DEV|104.238.221.61|12D3KooWEnrvbqvtTowYMR8FnBeKtryTj9RcXGx8EPpFZHou2ruP

Coin tables

Additional tables are created for each coin to store supplementary details such as block headers and transaction history. Using KMD as an example, these tables are listed below:

KMD_block_headers_cache

ID Name Type Description
0 id INTEGER Primary Key

KMD_tx_address

1|ecfb45cc5d5fdf34dcc70b0db2a333b143f0b98f9a8470097e3a256c1760b6ff|RUYJYSTuCKm9gouWzQN1LirHFEYThwzA2d

ID Name Type Description
0 id INTEGER Primary Key
1 internal_id VARCHAR(255) A hex string, representative of the address
2 address VARCHAR(255) The coin's wallet address

KMD_tx_cache

tx_hash|tx_hex

ID Name Type Description
0 tx_hash VARCHAR(255) A transaction hash
1 tx_hex VARCHAR(255) Raw hex for transaction

KMD_tx_history

id|tx_hash|internal_id|block_height|confirmation_status|token_id|details_json

ID Name Type Description
0 id INTEGER Primary Key
1 tx_hash VARCHAR(255) A transaction hash
2 internal_id VARCHAR(255) A hex string, representative of the transaction
3 block_height INTEGER Block height of transaction
4 confirmation_status BOOLEAN True if transaction has completed, False if it is pending
5 token_id INTEGER Number representing the coin type
6 details_json VARCHAR(255) Transaction details in JSON format