feat: add sql query to obtain balance #3446
Conversation
Replaced the `get_balance` function containing multiple sql queries with a single raw sql query.
| let balance_query_result = if let Some(val) = tip { | ||
| let balance_query = sql_query( | ||
| "SELECT coalesce(sum(value), 0) as amount, 'available_balance' as category \ | ||
| FROM outputs WHERE status = ? \ |
There was a problem hiding this comment.
Should take the mined heights into account when the tip is specified
| FROM outputs WHERE status = ? \ | |
| FROM outputs WHERE status = ? and mined_height < $val and deleted_at_height > $val\ |
There was a problem hiding this comment.
I think this is a job for the validation code though, I think it is cleaner that this function is based purely on the status.
| let balance_query_result = if let Some(val) = tip { | ||
| let balance_query = sql_query( | ||
| "SELECT coalesce(sum(value), 0) as amount, 'available_balance' as category \ | ||
| FROM outputs WHERE status = ? \ |
There was a problem hiding this comment.
I think this is a job for the validation code though, I think it is cleaner that this function is based purely on the status.
| @@ -1047,6 +1036,104 @@ impl OutputSql { | |||
| .first::<OutputSql>(conn)?) | |||
| } | |||
|
|
|||
| /// Return the available, time locked, pending incoming and pending outgoing balance | |||
| pub fn get_balance(tip: Option<u64>, conn: &SqliteConnection) -> Result<Balance, OutputManagerStorageError> { | |||
There was a problem hiding this comment.
It will be clearer what the tip argument is for if we rename it to something like tip_for_timelock_calculation. Currently it is not clear what the tip is for and that it actually affects whether the timelock balance is returned or not. The comment should also make the behaviour of the function clear when the tip is provided or not.
There was a problem hiding this comment.
Will make work these two comments into a new small PR after the merge.
| #[sql_type = "diesel::sql_types::Text"] | ||
| category: String, | ||
| } | ||
| let balance_query_result = if let Some(val) = tip { |
There was a problem hiding this comment.
the generic val here makes reading the code difficult below.
Description --- Fix confusing names in `get_balance` functions Motivation and Context --- Comments as per PR #3446 How Has This Been Tested? --- cargo clippy cargo formal all
* development: fix: fix confusing names in get_balance functions (tari-project#3447) feat: add sql query to obtain balance (tari-project#3446) fix: u64->i64->u64 conversion; chain split height as u64 (tari-project#3442)
Description
get_balancefunction containing multiple sql queries with a single raw sql query.fn fetch_pending_outgoing_outputs) as a result of this change.async fn test_txo_validation(), access to the backend to obtain pending incoming transactions is needed viafn fetch_pending_incoming_outputs, although that function is not used in production code anymore. If it is also removed other methods will have to be added to the backend to obtain the data for testing. Retaining the current function was chosen in lieu of adding other code.Motivation and Context
Get balance used a lot of RAM and was really slow due to multiple database interactions.
Comparison of old vs. new query time performance for a wallet with 251,000 UTXOs in the database shown below:
How Has This Been Tested?