@electrumsv is on PowPing!

PowPing is a place where you can earn Bitcoin simply by socializing, for FREE.
Never tried Bitcoin? It's OK! Just come, socialize, and earn Bitcoin.
Check out electrumsv's activities
Total Economy: 0 USD

Transaction values in the database

ElectrumSV creates an SQLite database to store a wallet and it's related information. One useful piece of information we ask the database for, is the value of a transaction. This might be defined as the sum of the received coins within the transaction less the sum of the spent coins within that transaction. 

Current approach

Transaction deltas

This is the current way in which we track transaction values. Whenever ElectrumSV processes the spending or receiving of a coin in a transaction, it adds a transaction delta entry for each involved key.

New approach

Received coins

A received coin is an output of the transaction that is associated with keys that the account is paying attention to. In most cases, this will be the private keys that can spend the received coin, although it is possible the account will be a "watching" account that does not have the private keys.

CREATE VIEW TransactionReceivedValues (account_id, tx_hash, keyinstance_id, value_delta) AS
    SELECT ATX.account_id, ATX.tx_hash, TXO.keyinstance_id, TXO.value
    FROM AccountTransactions ATX
    INNER JOIN TransactionOutputs TXO ON TXO.tx_hash=ATX.tx_hash
    WHERE TXO.keyinstance_id IS NOT NULL

In our case, we use keyinstance_id to link to the key associated with a received coin. Transaction outputs that do not have a value for this column, are not associated with any account. In the longer term this field might be abstracted out to handle types of script that involve multiple keys from different sources, whether accounts or contact identities.

Spent coins

A spent coin is an output of a parent transaction that is associated with the keys that the account is paying attention to, and is referenced by an input of the transaction.

CREATE VIEW TransactionSpentValues (account_id, tx_hash, keyinstance_id, value) AS 
    SELECT ATX.account_id, ATX.tx_hash, PTXO.keyinstance_id, PTXO.value 
    FROM AccountTransactions ATX 
    INNER JOIN TransactionInputs TXI ON TXI.tx_hash=ATX.tx_hash 
    INNER JOIN TransactionOutputs PTXO ON PTXO.tx_hash=TXI.spent_tx_hash 
    WHERE PTXO.keyinstance_id IS NOT NULL

Again we use keyinstance_id to link to the key associated with the spent coin.  

All coins

The most common usage of transaction values, is to see the effect of a transaction on the running balance of a sequence of transactions. We use this in the history tab to show the list of transactions. And we use it in the key dialog for the keys tab, where you can see a list of transactions that use a given key. If you are wondering why we include keyinstance_id, it is for this reason.

CREATE VIEW TransactionValues (account_id, tx_hash, keyinstance_id, value) AS 
    SELECT account_id, tx_hash, keyinstance_id, value FROM TransactionReceivedValues 
    SELECT account_id, tx_hash, keyinstance_id, -value FROM TransactionSpentValues

Running balance

We can now query the transactions in an account and the amount they affect the account balance by. It is a simple matter to pair it with the Transactions table, and bring in whatever additional data we need to flesh out either the history list in the History tab, or it's inbred cousin history export.

SELECT tx_hash, TOTAL(value)
FROM TransactionValues
WHERE account_id=?
GROUP BY tx_hash

Summing up

The main advantage of switching from manually populated transaction deltas to calculating the values as they are used, is that we can remove all the manual book-keeping. By doing so we remove complication from the wallet source code, and it becomes a nuance of the database logic.

If it turns out that this is prohibitively slow, we can make TransactionValues a table and populate it manually again. But it is very likely that any slowness perceived by the user comes from the user interface and not the database.

All SQL above is completely untested.

-- rt12

powered by powpress
link Tip