8000 Save bisq aggregate exchange rates in the database for each new block by nymkappa · Pull Request #1723 · mempool/mempool · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

Save bisq aggregate exchange rates in the database for each new block #1723

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Merged
merged 4 commits into from
May 31, 2022
Merged

Save bisq aggregate exchange rates in the database for each new block #1723

merged 4 commits into from
May 31, 2022

Conversation

nymkappa
Copy link
Member
@nymkappa nymkappa commented May 25, 2022 •
edited
Loading

Context #1520. This PR only saves the exchanges rates, it does not consume the data yet.

Database migration version increased to 19.

  • I ended up storing all fiat currencies (for a future currency switch feature) which are using Bisq-Aggregate provider as a JSON object in the database. We insert one new entry for each new block.
  • Past blocks don't have historical price.
  • We also do not insert prices unless we at least fetched them once (we don't insert zeroed prices).

Testing

  • Run the node backend with this PR
  • Wait for a new block
  • Run the following db query to confirm prices are properly inserted
SELECT height, JSON_EXTRACT(bisq_rates, '$.USD') as USD,
JSON_EXTRACT(bisq_rates, '$.KRW') as KRW, bisq_rates FROM rates;

@nymkappa nymkappa requested review from wiz and softsimon May 25, 2022 09:09
@cla-bot cla-bot bot added the cla-signed label May 25, 2022
wiz
wiz requested changes May 31, 2022
View reviewed changes
Copy link
Member
@wiz wiz left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Tested NACK @ v2.4.0-dev [0ecc03e4]

Mainnet works fine:

mempool@localhost [mempool]> SELECT height, JSON_EXTRACT(bisq_rates, '$.USD') as USD, JSON_EXTRACT(bisq_rates, '$.KRW') as KRW FROM rates;
+--------+----------+-------------+
| height | USD      | KRW         |
+--------+----------+-------------+
| 738688 | 31596.2  | 39456642.91 |
| 738689 | 31596.2  | 39456642.91 |
| 738690 | 31596.2  | 39456642.91 |
| 738691 | 31643.08 | 39458757.23 |
| 738692 | 31633.39 | 39462231.84 |
| 738693 | 31633.39 | 39462231.84 |
| 738694 | 31633.39 | 39462231.84 |
| 738695 | 31633.39 | 39462231.84 |
+--------+----------+-------------+
8 rows in set (0.000 sec)

However other networks do not, seems it's a typical case of forgetting we have other networks :P

May 31 08:34:39 node201.fra  mempool[62498]: INFO <liquid> Database connection established.
May 31 08:34:39 node201.fra  mempool[62733]: INFO <signet> Database connection established.
May 31 08:34:39 node201.fra  mempool[62615]: INFO <liquid> Database connection established.
May 31 08:34:39 node201.fra  mempool[62550]: INFO <testnet> Database connection established.
May 31 08:34:39 node201.fra  mempool[62498]: INFO <liquid> Starting statistics service
May 31 08:34:39 node201.fra  mempool[62498]: WARN <liquid> ./icons.json does not exist. No Liquid icons loaded.
May 31 08:34:39 node201.fra  mempool[62498]: INFO <liquid> Starting currency rates service
May 31 08:34:39 node201.fra  mempool[62498]: INFO <liquid> Currency rates service will be queried over clearnet using https://price.bisq.wiz.biz/getAllMarketPrices
May 31 08:34:39 node201.fra  mempool[62498]: NOTICE <liquid> Mempool Server is running on port 8994
May 31 08:34:39 node201.fra  mempool[62615]: INFO <liquid> Starting statistics service
May 31 08:34:39 node201.fra  mempool[62615]: INFO <liquid> Starting currency rates service
May 31 08:34:39 node201.fra  mempool[62615]: INFO <liquid> Currency rates service will be queried over clearnet using https://price.bisq.wiz.biz/getAllMarketPrices
May 31 08:34:39 node201.fra  mempool[62733]: INFO <signet> Starting statistics service
May 31 08:34:39 node201.fra  mempool[62733]: INFO <signet> Starting currency rates service
May 31 08:34:39 node201.fra  mempool[62733]: INFO <signet> Currency rates service will be queried over clearnet using https://price.bisq.wiz.biz/getAllMarketPrices
May 31 08:34:39 node201.fra  mempool[62615]: NOTICE <liquid> Mempool Server is running on port 8998
May 31 08:34:39 node201.fra  mempool[62733]: NOTICE <signet> Mempool Server is running on port 8995
May 31 08:34:39 node201.fra  mempool[62733]: INFO <signet> Updating latest mining pools from Github
May 31 08:34:39 node201.fra  mempool[62733]: INFO <signet> List of public pools will be queried over clearnet
May 31 08:34:39 node201.fra  mempool[62550]: INFO <testnet> Starting statistics service
May 31 08:34:39 node201.fra  mempool[62550]: INFO <testnet> Starting currency rates service
May 31 08:34:39 node201.fra  mempool[62550]: INFO <testnet> Currency rates service will be queried over clearnet using https://price.bisq.wiz.biz/getAllMarketPrices
May 31 08:34:39 node201.fra  mempool[62550]: NOTICE <testnet> Mempool Server is running on port 8997
May 31 08:34:39 node201.fra  mempool[62550]: INFO <testnet> Updating latest mining pools from Github
May 31 08:34:39 node201.fra  mempool[62550]: INFO <testnet> List of public pools will be queried over clearnet
May 31 08:34:39 node201.fra  mempool[62615]: NOTICE <liquid> The mempool is now in sync!
May 31 08:34:39 node201.fra  mempool[62498]: NOTICE <liquid> The mempool is now in sync!
May 31 08:34:39 node201.fra  mempool[62960]: INFO <bisq> Downloading external asset pools.json over clearnet...
May 31 08:34:39 node201.fra  mempool[62960]: INFO <bisq> External asset pools.json saved to ./pools.json
May 31 08:34:40 node201.fra  mempool[62850]: INFO Restoring mempool and blocks data from disk cache
May 31 08:34:40 node201.fra  mempool[62850]: INFO Database connection established.
May 31 08:34:40 node201.fra  mempool[62850]: INFO Starting statistics service
May 31 08:34:40 node201.fra  mempool[62850]: INFO Starting currency rates service
May 31 08:34:40 node201.fra  mempool[62850]: INFO Currency rates service will be queried over clearnet using https://price.bisq.wiz.biz/getAllMarketPrices
May 31 08:34:40 node201.fra  mempool[62850]: NOTICE Mempool Server is running on port 8999
May 31 08:34:40 node201.fra  mempool[62850]: INFO Updating latest mining pools from Github
May 31 08:34:40 node201.fra  mempool[62850]: INFO List of public pools will be queried over clearnet
May 31 08:34:40 node201.fra  mempool[62960]: INFO <bisq> Restoring mempool and blocks data from disk cache
May 31 08:34:40 node201.fra  mempool[62960]: INFO <bisq> Starting currency rates service
May 31 08:34:40 node201.fra  mempool[62960]: INFO <bisq> Currency rates service will be queried over clearnet using https://price.bisq.wiz.biz/getAllMarketPrices
May 31 08:34:40 node201.fra  mempool[62960]: NOTICE <bisq> Mempool Server is running on port 8996
May 31 08:34:40 node201.fra  mempool[62733]: NOTICE <signet> The mempool is now in sync!
May 31 08:34:41 node201.fra  mempool[62550]: NOTICE <testnet> The mempool is now in sync!
May 31 08:34:42 node201.fra  mempool[62733]: INFO <signet> Indexed 0 blocks
May 31 08:34:42 node201.fra  mempool[62733]: INFO <signet> 92723 blocks hash validated in 375 ms
May 31 08:34:53 node201.fra  mempool[62850]: INFO Indexed 0 blocks
May 31 08:34:54 node201.fra  mempool[62850]: INFO 738687 blocks hash validated in 1675 ms
May 31 08:35:02 node201.fra  mempool[62960]: WARN <bisq> Bitcoin block height (#738687) has diverged from the latest Bisq block height (#738669). Restarting watchers...
May 31 08:35:05 node201.fra  mempool[62550]: INFO <testnet> Indexed 0 blocks
May 31 08:35:10 node201.fra  mempool[62550]: INFO <testnet> 2252769 blocks hash validated in 5252 ms
May 31 08:35:23 node201.fra  mempool[62850]: NOTICE The mempool is now in sync!
May 31 08:35:23 node201.fra  mempool[62960]: NOTICE <bisq> The mempool is now in sync!
May 31 08:40:27 node201.fra  mempool[26699]: INFO <liquid> Downloading external asset pools.json over clearnet...
May 31 08:40:27 node201.fra  mempool[26916]: INFO <signet> Downloading external asset pools.json over clearnet...
May 31 08:40:27 node201.fra  mempool[26831]: INFO Downloading external asset pools.json over clearnet...
May 31 08:40:27 node201.fra  mempool[26916]: INFO <signet> External asset pools.json saved to ./pools.json
May 31 08:40:27 node201.fra  mempool[27166]: INFO <liquid> Downloading external asset icons.json over clearnet...
May 31 08:40:27 node201.fra  mempool[26699]: INFO <liquid> External asset pools.json saved to ./pools.json
May 31 08:40:27 node201.fra  mempool[26916]: INFO <signet> Restoring mempool and blocks data from disk cache
May 31 08:40:27 node201.fra  mempool[26699]: INFO <liquid> Restoring mempool and blocks data from disk cache
May 31 08:40:27 node201.fra  mempool[26831]: INFO External asset pools.json saved to ./pools.json
May 31 08:40:27 node201.fra  mempool[26916]: INFO <signet> Database connection established.
May 31 08:40:27 node201.fra  mempool[27486]: INFO <testnet> Downloading external asset pools.json over clearnet...
May 31 08:40:27 node201.fra  mempool[26699]: INFO <liquid> Database connection established.
May 31 08:40:27 node201.fra  mempool[26699]: NOTICE <liquid> MIGRATIONS: Upgrading database schema
May 31 08:40:27 node201.fra  mempool[27486]: INFO <testnet> External asset pools.json saved to ./pools.json
May 31 08:40:27 node201.fra  mempool[27486]: INFO <testnet> Restoring mempool and blocks data from disk cache
May 31 08:40:27 node201.fra  mempool[27486]: WARN <testnet> Failed to parse mempoool and blocks cache. Skipping.
May 31 08:40:27 node201.fra  mempool[26916]: NOTICE <signet> MIGRATIONS: Upgrading database schema
May 31 08:40:27 node201.fra  mempool[26916]: NOTICE <signet> MIGRATIONS: OK. Database schema have been migrated from version 18 to 19 (latest version)
May 31 08:40:27 node201.fra  mempool[26699]: NOTICE <liquid> MIGRATIONS: OK. Database schema have been migrated from version 18 to 19 (latest version)
May 31 08:40:27 node201.fra  mempool[26699]: INFO <liquid> Starting statistics service
May 31 08:40:27 node201.fra  mempool[26699]: WARN <liquid> ./icons.json does not exist. No Liquid icons loaded.
May 31 08:40:27 node201.fra  mempool[26699]: INFO <liquid> Starting currency rates service
May 31 08:40:27 node201.fra  mempool[26699]: INFO <liquid> Currency rates service will be queried over clearnet using https://price.bisq.wiz.biz/getAllMarketPrices
May 31 08:40:27 node201.fra  mempool[26699]: NOTICE <liquid> Mempool Server is running on port 8994
May 31 08:40:27 node201.fra  mempool[26916]: INFO <signet> Starting statistics service
May 31 08:40:27 node201.fra  mempool[26916]: INFO <signet> Starting currency rates service
May 31 08:40:27 node201.fra  mempool[26916]: INFO <signet> Currency rates service will be queried over clearnet using https://price.bisq.wiz.biz/getAllMarketPrices
May 31 08:40:27 node201.fra  mempool[26916]: NOTICE <signet> Mempool Server is running on port 8995
May 31 08:40:27 node201.fra  mempool[27486]: INFO <testnet> Database connection established.
May 31 08:40:27 node201.fra  mempool[26916]: INFO <signet> Updating latest mining pools from Github
May 31 08:40:27 node201.fra  mempool[26916]: INFO <signet> List of public pools will be queried over clearnet
May 31 08:40:27 node201.fra  mempool[27667]: INFO <bisq> Downloading external asset pools.json over clearnet...
May 31 08:40:27 node201.fra  mempool[27486]: NOTICE <testnet> MIGRATIONS: Upgrading database schema
May 31 08:40:27 node201.fra  mempool[27667]: INFO <bisq> External asset pools.json saved to ./pools.json
May 31 08:40:27 node201.fra  mempool[27486]: NOTICE <testnet> MIGRATIONS: OK. Database schema have been migrated from version 18 to 19 (latest version)
May 31 08:40:27 node201.fra  mempool[27486]: INFO <testnet> Starting statistics service
May 31 08:40:27 node201.fra  mempool[27486]: INFO <testnet> Starting currency rates service
May 31 08:40:27 node201.fra  mempool[27486]: INFO <testnet> Currency rates service will be queried over clearnet using https://price.bisq.wiz.biz/getAllMarketPrices
May 31 08:40:27 node201.fra  mempool[27486]: NOTICE <testnet> Mempool Server is running on port 8997
May 31 08:40:27 node201.fra  mempool[27486]: INFO <testnet> Updating latest mining pools from Github
May 31 08:40:27 node201.fra  mempool[27486]: INFO <testnet> List of public pools will be queried over clearnet
May 31 08:40:27 node201.fra  mempool[27166]: INFO <liquid> External asset icons.json saved to ./icons.json
May 31 08:40:27 node201.fra  mempool[27166]: INFO <liquid> Restoring mempool and blocks data from disk cache
May 31 08:40:27 node201.fra  mempool[26699]: NOTICE <liquid> The mempool is now in sync!
May 31 08:40:27 node201.fra  mempool[26831]: INFO Restoring mempool and blocks data from disk cache
May 31 08:40:27 node201.fra  mempool[27166]: INFO <liquid> Database connection established.
May 31 08:40:27 node201.fra  mempool[27166]: NOTICE <liquid> MIGRATIONS: Upgrading database schema
May 31 08:40:27 node201.fra  mempool[26831]: INFO Database connection established.
May 31 08:40:27 node201.fra  mempool[27166]: NOTICE <liquid> MIGRATIONS: OK. Database schema have been migrated from version 18 to 19 (latest version)
May 31 08:40:27 node201.fra  mempool[27166]: INFO <liquid> Starting statistics service
May 31 08:40:27 node201.fra  mempool[27166]: INFO <liquid> Starting currency rates service
May 31 08:40:27 node201.fra  mempool[27166]: INFO <liquid> Currency rates service will be queried over clearnet using https://price.bisq.wiz.biz/getAllMarketPrices
May 31 08:40:27 node201.fra  mempool[27166]: NOTICE <liquid> Mempool Server is running on port 8998
May 31 08:40:27 node201.fra  mempool[26831]: NOTICE MIGRATIONS: Upgrading database schema
May 31 08:40:27 node201.fra  mempool[26831]: NOTICE MIGRATIONS: OK. Database schema have been migrated from version 18 to 19 (latest version)
May 31 08:40:27 node201.fra  mempool[27166]: NOTICE <liquid> The mempool is now in sync!
May 31 08:40:27 node201.fra  mempool[26831]: INFO Starting statistics service
May 31 08:40:27 node201.fra  mempool[26831]: INFO Starting currency rates service
May 31 08:40:27 node201.fra  mempool[26831]: INFO Currency rates service will be queried over clearnet using https://price.bisq.wiz.biz/getAllMarketPrices
May 31 08:40:27 node201.fra  mempool[26831]: NOTICE Mempool Server is running on port 8999
May 31 08:40:27 node201.fra  mempool[26831]: INFO Updating latest mining pools from Github
May 31 08:40:27 node201.fra  mempool[26831]: INFO List of public pools will be queried over clearnet
May 31 08:40:27 node201.fra  mempool[27667]: INFO <bisq> Restoring mempool and blocks data from disk cache
May 31 08:40:27 node201.fra  mempool[27667]: INFO <bisq> Starting currency rates service
May 31 08:40:27 node201.fra  mempool[27667]: INFO <bisq> Currency rates service will be queried over clearnet using https://price.bisq.wiz.biz/getAllMarketPrices
May 31 08:40:27 node201.fra  mempool[27667]: NOTICE <bisq> Mempool Server is running on port 8996
May 31 08:40:28 node201.fra  mempool[26916]: NOTICE <signet> The mempool is now in sync!
May 31 08:40:29 node201.fra  mempool[27486]: NOTICE <testnet> The mempool is now in sync!
May 31 08:40:29 node201.fra  mempool[26916]: INFO <signet> Indexed 0 blocks
May 31 08:40:29 node201.fra  mempool[26916]: INFO <signet> 92724 blocks hash validated in 253 ms
May 31 08:40:38 node201.fra  mempool[26831]: INFO Indexed 0 blocks
May 31 08:40:38 node201.fra  mempool[27667]: ERR <bisq> Cannot save exchange rate into db for block 738688 Reason: Table 'mempool_bisq.rates' doesn't exist
May 31 08:40:39 node201.fra  mempool[26831]: INFO 738689 blocks hash validated in 1585 ms
May 31 08:40:40 node201.fra  mempool[27166]: ERR <liquid> Cannot save exchange rate into db for block 1857017 Reason: Table 'mempool_liquid.rates' doesn't exist
May 31 08:40:43 node201.fra  mempool[27667]: ERR <bisq> Cannot save exchange rate into db for block 738688 Reason: Table 'mempool_bisq.rates' doesn't exist
May 31 08:40:43 node201.fra  mempool[27667]: WARN <bisq> runMainLoop error: Table 'mempool_bisq.rates' doesn't exist. Retrying in 10 sec.
May 31 08:40:45 node201.fra  mempool[27166]: ERR <liquid> Cannot save exchange rate into db for block 1857017 Reason: Table 'mempool_liquid.rates' doesn't exist
May 31 08:40:45 node201.fra  mempool[27166]: WARN <liquid> runMainLoop error: Table 'mempool_liquid.rates' doesn't exist. Retrying in 10 sec.
May 31 08:40:48 node201.fra  mempool[26831]: NOTICE The mempool is now in sync!
May 31 08:40:51 node201.fra  mempool[27486]: INFO <testnet> Indexed 0 blocks
May 31 08:40:53 node201.fra  mempool[27667]: ERR <bisq> Cannot save exchange rate into db for block 738688 Reason: Table 'mempool_bisq.rates' doesn't exist
May 31 08:40:53 node201.fra  mempool[27667]: WARN <bisq> runMainLoop error: Table 'mempool_bisq.rates' doesn't exist. Retrying in 20 sec.
May 31 08:40:55 node201.fra  mempool[27166]: ERR <liquid> Cannot save exchange rate into db for block 1857017 Reason: Table 'mempool_liquid.rates' doesn't exist
May 31 08:40:55 node201.fra  mempool[27166]: WARN <liquid> runMainLoop error: Table 'mempool_liquid.rates' doesn't exist. Retrying in 20 sec.
May 31 08:40:56 node201.fra  mempool[27486]: INFO <testnet> 2252769 blocks hash validated in 5098 ms
May 31 08:41:08 node201.fra  mempool[26699]: ERR <liquid> Cannot save exchange rate into db for block 366402 Reason: Table 'mempool_liquidtestnet.rates' doesn't exist
May 31 08:41:13 node201 syslogd: last message repeated 1 times
May 31 08:41:13 node201.fra  mempool[26699]: WARN <liquid> runMainLoop error: Table 'mempool_liquidtestnet.rates' doesn't exist. Retrying in 10 sec.
May 31 08:41:13 node201.fra  mempool[27667]: ERR <bisq> Cannot save exchange rate into db for block 738688 Reason: Table 'mempool_bisq.rates' doesn't exist
May 31 08:41:13 node201.fra  mempool[27667]: WARN <bisq> runMainLoop error: Table 'mempool_bisq.rates' doesn't exist. Retrying in 40 sec.
May 31 08:41:15 node201.fra  mempool[27166]: ERR <liquid> Cannot save exchange rate into db for block 1857017 Reason: Table 'mempool_liquid.rates' doesn't exist
May 31 08:41:15 node201.fra  mempool[27166]: WARN <liquid> runMainLoop error: Table 'mempool_liquid.rates' doesn't exist. Retrying in 40 sec.
May 31 08:41:23 node201.fra  mempool[26699]: ERR <liquid> Cannot save exchange rate into db for block 366402 Reason: Table 'mempool_liquidtestnet.rates' doesn't exist
May 31 08:41:23 node201.fra  mempool[26699]: WARN <liquid> runMainLoop error: Table 'mempool_liquidtestnet.rates' doesn't exist. Retrying in 20 sec.
May 31 08:41:43 node201.fra  mempool[26699]: ERR <liquid> Cannot save exchange rate into db for block 366402 Reason: Table 'mempool_liquidtestnet.rates' doesn't exist
May 31 08:41:43 node201.fra  mempool[26699]: WARN <liquid> runMainLoop error: Table 'mempool_liquidtestnet.rates' doesn't exist. Retrying in 40 sec.
May 31 08:41:53 node201.fra  mempool[27667]: ERR <bisq> Cannot save exchange rate into db for block 738688 Reason: Table 'mempool_bisq.rates' doesn't exist
May 31 08:41:53 node201.fra  mempool[27667]: WARN <bisq> runMainLoop error: Table 'mempool_bisq.rates' doesn't exist. Retrying in 60 sec.
May 31 08:41:55 node201.fra  mempool[27166]: ERR <liquid> Cannot save exchange rate into db for block 1857017 Reason: Table 'mempool_liquid.rates' doesn't exist
May 31 08:41:55 node201.fra  mempool[27166]: WARN <liquid> runMainLoop error: Table 'mempool_liquid.rates' doesn't exist. Retrying in 60 sec.
May 31 08:42:23 node201.fra  mempool[26699]: ERR <liquid> Cannot save exchange rate into db for block 366402 Reason: Table 'mempool_liquidtestnet.rates' doesn't exist
May 31 08:42:23 node201.fra  mempool[26699]: WARN <liquid> runMainLoop error: Table 'mempool_liquidtestnet.rates' doesn't exist. Retrying in 60 sec.
May 31 08:42:55 node201.fra  mempool[27166]: ERR <liquid> Cannot save exchange rate into db for block 1857017 Reason: Table 'mempool_liquid.rates' doesn't exist
May 31 08:42:55 node201.fra  mempool[27166]: WARN <liquid> runMainLoop error: Table 'mempool_liquid.rates' doesn't exist. Retrying in 60 sec.
May 31 08:42:55 node201.fra  mempool[27667]: ERR <bisq> Cannot save exchange rate into db for block 738688 Reason: Table 'mempool_bisq.rates' doesn't exist
May 31 08:42:55 node201.fra  mempool[27667]: WARN <bisq> runMainLoop error: Table 'mempool_bisq.rates' doesn't exist. Retrying in 60 sec.
May 31 08:43:23 node201.fra  mempool[26699]: ERR <liquid> Cannot save exchange rate into db for block 366402 Reason: Table 'mempool_liquidtestnet.rates' doesn't exist

@nymkappa
Copy link
Member Author

@wiz Yes my bad I did not create the table on non bitcoin network. Fixed it.

wiz
wiz approved these changes May 31, 2022
View reviewed changes
Copy link
Member
@wiz wiz left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Tested ACK @ v2.4.0-dev [ce49dca7]

@wiz wiz merged commit 6a3df95 into master May 31, 2022
@wiz wiz deleted the nymkappa/feature/historical-rates branch May 31, 2022 15:14
@nymkappa nymkappa mentioned this pull request Jun 8, 2022
Merged
6 tasks
@wiz wiz mentioned this pull request Jul 7, 2022
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers

@wiz wiz wiz approved these changes

@softsimon softsimon Awaiting requested review from softsimon softsimon is a code owner

Assignees
No one assigned
Labels
cla-signed
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.
2 participants
@nymkappa @wiz
0