The wallet database
Each wallet is stored in it’s own SQLite database. The current version of ElectrumSV at the time of writing, 1.4.0b1, uses the database schema version 29. This schema is include for reference purposes and cannot be used to a create a working wallet.
Each version of ElectrumSV includes migration code that applies any needed changes to older versions of the wallet. This database format is pretty solid at this point, but it is a work in progress. There are many other things ElectrumSV will need to support in the future.
Transactions and atomicity
Between how SQLite works, how the Python sqlite3 module works and how ElectrumSV builds upon both of these some elaboration is needed.
We pass the isolation_level=None
parameter to the Python sqlite3 function that opens a
database connection. This overrides the custom way the Python sqlite3 module overrides how
SQLite works and returns it to the autocommit mode. This mode means that statements that
modify the database take effect immediately. Use of a BEGIN
and SAVEPOINT
statement
takes Sqlite out of autocommit mode, and the outermost COMMIT
, ROLLBACK
or RELEASE
statement returns Sqlite to autocommit mode.
ElectrumSV does all of it’s database writes in custom transactions starting with the BEGIN
statement, disabling the autocommit mode, and bundling the writes into groups with the
ability to commit them all or roll them all back. Additionally as SQLite does not allow
multiple connections to do
concurrent writes,
ElectrumSV takes a well known approach of having a sequential writer thread. All writes happen
in a dedicated writer thread one after the other as managed transactions.
The following logic is used to wrap each ElectrumSV transaction:
def __call__(self, db: sqlite3.Connection) -> None:
if not self._future.set_running_or_notify_cancel():
return
db.execute("BEGIN")
try:
result = self._fn(db, *self._args, **self._kwargs)
except BaseException as exc:
db.execute("ROLLBACK")
self._future.set_exception(exc)
# Break a reference cycle with the exception 'exc'
self = None # type: ignore
else:
db.execute("COMMIT")
self._future.set_result(result)
Synchronous writes
The Python concurrent.futures
module is used in synchronous logic to do database writes
in a non-blocking manner. The calling thread can block until the write is complete by calling
the Future.result
method. Or the calling thread can request a callback through the use of the
Future.add_done_callback.
Caution
Futures catch and log exceptions in their callbacks, preventing ElectrumSV from catching and reporting them. This means that the Future callbacks need to be certain they know about all possible exceptions and to catch and handle them all. Developers should be very sure they understand the code they are calling.
Synchronous database calls are performed in this manner:
def on_db_call_done(future: concurrent.futures.Future[bool]) -> None:
# Skip if the operation was cancelled.
if future.cancelled():
return
# Raise any exception if it errored or get the result if completed successfully.
future.result()
self.events.trigger_callback(WalletEvent.TRANSACTION_DELETED, self._id, tx_hash)
future = db_functions.remove_transaction(self.get_db_context(), tx_hash)
future.add_done_callback(on_db_call_done)
Asynchronous writes
How ElectrumSV wraps asynchronous calls is done in the DatabaseContext.run_in_thread_async
method. If you wish to see how it works, you can look in the sqlite_support.py
file.
Asynchronous database calls are performed in this manner:
if await update_transaction_flags_async(db_context, [
(TxFlags.MASK_STATELESS, TxFlags.STATE_SETTLED, tx_hash) ]):
...
Database schema
This is version 29 of our database schema. It should be correct for the ElectrumSV version this documentation is intended for, but if it is not, please let us know.
1BEGIN TRANSACTION;
2
3CREATE TABLE IF NOT EXISTS "MasterKeys" (
4 "masterkey_id" INTEGER,
5 "parent_masterkey_id" INTEGER DEFAULT NULL,
6 "derivation_type" INTEGER NOT NULL,
7 "derivation_data" BLOB NOT NULL,
8 "date_created" INTEGER NOT NULL,
9 "date_updated" INTEGER NOT NULL,
10 "flags" INTEGER NOT NULL DEFAULT 0,
11 FOREIGN KEY("parent_masterkey_id") REFERENCES "MasterKeys"("masterkey_id"),
12 PRIMARY KEY("masterkey_id")
13);
14
15CREATE TABLE IF NOT EXISTS "Accounts" (
16 "account_id" INTEGER,
17 "default_masterkey_id" INTEGER DEFAULT NULL,
18 "default_script_type" INTEGER NOT NULL,
19 "account_name" TEXT NOT NULL,
20 "date_created" INTEGER NOT NULL,
21 "date_updated" INTEGER NOT NULL,
22 "flags" INTEGER NOT NULL DEFAULT 0,
23 FOREIGN KEY("default_masterkey_id") REFERENCES "MasterKeys"("masterkey_id"),
24 PRIMARY KEY("account_id")
25);
26
27CREATE TABLE IF NOT EXISTS "Transactions" (
28 "tx_hash" BLOB,
29 "tx_data" BLOB DEFAULT NULL,
30 "proof_data" BLOB DEFAULT NULL,
31 "block_height" INTEGER DEFAULT NULL,
32 "block_position" INTEGER DEFAULT NULL,
33 "fee_value" INTEGER DEFAULT NULL,
34 "flags" INTEGER NOT NULL DEFAULT 0,
35 "description" TEXT DEFAULT NULL,
36 "date_created" INTEGER NOT NULL,
37 "date_updated" INTEGER NOT NULL,
38 "locktime" INTEGER DEFAULT NULL,
39 "version" INTEGER DEFAULT NULL,
40 "block_hash" BLOB DEFAULT NULL,
41 PRIMARY KEY("tx_hash")
42);
43
44CREATE TABLE IF NOT EXISTS "WalletData" (
45 "key" TEXT NOT NULL,
46 "value" TEXT NOT NULL,
47 "date_created" INTEGER NOT NULL,
48 "date_updated" INTEGER NOT NULL
49);
50
51CREATE TABLE IF NOT EXISTS "WalletEvents" (
52 "event_id" INTEGER,
53 "event_type" INTEGER NOT NULL,
54 "event_flags" INTEGER NOT NULL,
55 "account_id" INTEGER,
56 "date_created" INTEGER NOT NULL,
57 "date_updated" INTEGER NOT NULL,
58 FOREIGN KEY("account_id") REFERENCES "Accounts"("account_id"),
59 PRIMARY KEY("event_id")
60);
61
62CREATE TABLE IF NOT EXISTS "Invoices" (
63 "invoice_id" INTEGER,
64 "account_id" INTEGER NOT NULL,
65 "tx_hash" BLOB DEFAULT NULL,
66 "payment_uri" TEXT NOT NULL,
67 "description" TEXT,
68 "invoice_flags" INTEGER NOT NULL,
69 "value" INTEGER NOT NULL,
70 "invoice_data" BLOB NOT NULL,
71 "date_expires" INTEGER DEFAULT NULL,
72 "date_created" INTEGER NOT NULL,
73 "date_updated" INTEGER NOT NULL,
74 FOREIGN KEY("tx_hash") REFERENCES "Transactions"("tx_hash"),
75 FOREIGN KEY("account_id") REFERENCES "Accounts"("account_id"),
76 PRIMARY KEY("invoice_id")
77);
78
79CREATE TABLE IF NOT EXISTS "AccountTransactions" (
80 "tx_hash" BLOB NOT NULL,
81 "account_id" INTEGER NOT NULL,
82 "flags" INTEGER NOT NULL DEFAULT 0,
83 "description" TEXT DEFAULT NULL,
84 "date_created" INTEGER NOT NULL,
85 "date_updated" INTEGER NOT NULL,
86 FOREIGN KEY("account_id") REFERENCES "Accounts"("account_id"),
87 FOREIGN KEY("tx_hash") REFERENCES "Transactions"("tx_hash")
88);
89
90CREATE TABLE IF NOT EXISTS "TransactionOutputs" (
91 "tx_hash" BLOB NOT NULL,
92 "txo_index" INTEGER NOT NULL,
93 "value" INTEGER NOT NULL,
94 "keyinstance_id" INTEGER DEFAULT NULL,
95 "flags" INTEGER NOT NULL,
96 "script_type" INTEGER DEFAULT 0,
97 "script_hash" BLOB NOT NULL DEFAULT x '',
98 "script_offset" INTEGER DEFAULT 0,
99 "script_length" INTEGER DEFAULT 0,
100 "spending_tx_hash" BLOB,
101 "spending_txi_index" INTEGER,
102 "date_created" INTEGER NOT NULL,
103 "date_updated" INTEGER NOT NULL,
104 FOREIGN KEY("keyinstance_id") REFERENCES "KeyInstances"("keyinstance_id"),
105 FOREIGN KEY("tx_hash") REFERENCES "Transactions"("tx_hash")
106);
107
108CREATE TABLE IF NOT EXISTS "TransactionInputs" (
109 "tx_hash" BLOB NOT NULL,
110 "txi_index" INTEGER NOT NULL,
111 "spent_tx_hash" BLOB NOT NULL,
112 "spent_txo_index" INTEGER NOT NULL,
113 "sequence" INTEGER NOT NULL,
114 "flags" INTEGER NOT NULL,
115 "script_offset" INTEGER,
116 "script_length" INTEGER,
117 "date_created" INTEGER NOT NULL,
118 "date_updated" INTEGER NOT NULL,
119 FOREIGN KEY("tx_hash") REFERENCES "Transactions"("tx_hash")
120);
121
122CREATE TABLE IF NOT EXISTS "KeyInstances" (
123 "keyinstance_id" INTEGER,
124 "account_id" INTEGER NOT NULL,
125 "masterkey_id" INTEGER DEFAULT NULL,
126 "derivation_type" INTEGER NOT NULL,
127 "derivation_data" BLOB NOT NULL,
128 "derivation_data2" BLOB DEFAULT NULL,
129 "flags" INTEGER NOT NULL,
130 "description" TEXT DEFAULT NULL,
131 "date_created" INTEGER NOT NULL,
132 "date_updated" INTEGER NOT NULL,
133 FOREIGN KEY("masterkey_id") REFERENCES "MasterKeys"("masterkey_id"),
134 FOREIGN KEY("account_id") REFERENCES "Accounts"("account_id"),
135 PRIMARY KEY("keyinstance_id")
136);
137
138CREATE TABLE IF NOT EXISTS "KeyInstanceScripts" (
139 "keyinstance_id" INTEGER NOT NULL,
140 "script_type" INTEGER NOT NULL,
141 "script_hash" BLOB NOT NULL,
142 "date_created" INTEGER NOT NULL,
143 "date_updated" INTEGER NOT NULL,
144 FOREIGN KEY("keyinstance_id") REFERENCES "KeyInstances"("keyinstance_id")
145);
146
147CREATE TABLE IF NOT EXISTS "MAPIBroadcastCallbacks" (
148 "tx_hash" BLOB,
149 "peer_channel_id" VARCHAR(1024) NOT NULL,
150 "broadcast_date" INTEGER NOT NULL,
151 "encrypted_private_key" BLOB NOT NULL,
152 "server_id" INTEGER NOT NULL,
153 "status_flags" INTEGER NOT NULL,
154 PRIMARY KEY("tx_hash")
155);
156
157CREATE TABLE IF NOT EXISTS "Servers" (
158 "server_id" INTEGER,
159 "server_type" INTEGER NOT NULL,
160 "url" TEXT NOT NULL,
161 "account_id" INTEGER DEFAULT NULL,
162 "server_flags" INTEGER NOT NULL DEFAULT 0,
163 "api_key_template" TEXT DEFAULT NULL,
164 "encrypted_api_key" TEXT DEFAULT NULL,
165 "fee_quote_json" TEXT DEFAULT NULL,
166 "date_last_connected" INTEGER DEFAULT 0,
167 "date_last_tried" INTEGER DEFAULT 0,
168 "date_created" INTEGER NOT NULL,
169 "date_updated" INTEGER NOT NULL,
170 FOREIGN KEY("account_id") REFERENCES "Accounts"("account_id"),
171 PRIMARY KEY("server_id")
172);
173
174CREATE TABLE IF NOT EXISTS "PaymentRequests" (
175 "paymentrequest_id" INTEGER,
176 "keyinstance_id" INTEGER NOT NULL,
177 "state" INTEGER NOT NULL,
178 "description" TEXT,
179 "expiration" INTEGER,
180 "value" INTEGER,
181 "script_type" INTEGER NOT NULL,
182 "pushdata_hash" BLOB NOT NULL,
183 "date_created" INTEGER NOT NULL,
184 "date_updated" INTEGER NOT NULL,
185 FOREIGN KEY("keyinstance_id") REFERENCES "KeyInstances"("keyinstance_id"),
186 PRIMARY KEY("paymentrequest_id")
187);
188
189CREATE UNIQUE INDEX IF NOT EXISTS "idx_WalletData_unique" ON "WalletData" ("key");
190
191CREATE UNIQUE INDEX IF NOT EXISTS "idx_Invoices_unique" ON "Invoices" ("payment_uri");
192
193CREATE UNIQUE INDEX IF NOT EXISTS "idx_AccountTransactions_unique" ON "AccountTransactions" ("tx_hash", "account_id");
194
195CREATE UNIQUE INDEX IF NOT EXISTS "idx_TransactionOutputs_unique" ON "TransactionOutputs" ("tx_hash", "txo_index");
196
197CREATE UNIQUE INDEX IF NOT EXISTS "idx_TransactionInputs_unique" ON "TransactionInputs" ("tx_hash", "txi_index");
198
199CREATE UNIQUE INDEX IF NOT EXISTS "idx_KeyInstanceScripts_unique" ON "KeyInstanceScripts" ("keyinstance_id", "script_type");
200
201CREATE UNIQUE INDEX IF NOT EXISTS "idx_Servers_unique" ON "Servers" ("server_type", "url", "account_id");
202
203CREATE VIEW TransactionReceivedValues (account_id, tx_hash, keyinstance_id, value) AS
204SELECT
205 ATX.account_id,
206 ATX.tx_hash,
207 TXO.keyinstance_id,
208 TXO.value
209FROM
210 AccountTransactions ATX
211 INNER JOIN TransactionOutputs TXO ON TXO.tx_hash = ATX.tx_hash
212 INNER JOIN KeyInstances KI ON KI.keyinstance_id = TXO.keyinstance_id
213WHERE
214 TXO.keyinstance_id IS NOT NULL
215 AND KI.account_id = ATX.account_id;
216
217CREATE VIEW TransactionSpentValues (account_id, tx_hash, keyinstance_id, value) AS
218SELECT
219 ATX.account_id,
220 ATX.tx_hash,
221 PTXO.keyinstance_id,
222 PTXO.value
223FROM
224 AccountTransactions ATX
225 INNER JOIN TransactionInputs TXI ON TXI.tx_hash = ATX.tx_hash
226 INNER JOIN TransactionOutputs PTXO ON PTXO.tx_hash = TXI.spent_tx_hash
227 AND PTXO.txo_index = TXI.spent_txo_index
228 INNER JOIN KeyInstances KI ON KI.keyinstance_id = PTXO.keyinstance_id
229WHERE
230 PTXO.keyinstance_id IS NOT NULL
231 AND KI.account_id = ATX.account_id;
232
233CREATE VIEW TransactionValues (account_id, tx_hash, keyinstance_id, value) AS
234SELECT
235 account_id,
236 tx_hash,
237 keyinstance_id,
238 value
239FROM
240 TransactionReceivedValues
241UNION
242ALL
243SELECT
244 account_id,
245 tx_hash,
246 keyinstance_id,
247 - value
248FROM
249 TransactionSpentValues;
250
251COMMIT;
Details
For now various details about the database schema are kept below, but as we flesh it out it should end up being restructured.
Transaction table
block_hash
This column stores the block hash for the block the transaction was mined in. It is expected there is a matching row to the transaction hash and block hash in the TransactionProofs table.
block_height / block_position
These columns are intended to track the block height and block position of a transaction, once it has been mined, into the long term future. In theory, it is possible to map the block hash and transaction hash to the TransactionProofs table obtain this information. In practice, there are two reasons we may not want to do this.
We may want to delete proofs for transactions once coins have been spent.
We may not have the proof for older transactions, which have unspent coins from before proofs were retained. If these are unspent and present in migrated wallets, we will need to obtain the proofs to do an SPV payment.
flags
STATE_SIGNED
A fully signed transaction that is expected to not have been shared with external parties.
STATE_DISPATCHED
A transaction that has been shared with external parties, but is not expected to have been broadcast to the P2P network.
If this is determined to have been broadcast, then additional as yet implemented handling should be done to reconcile how to react to this event.
STATE_RECEIVED
A transaction that an external party has shared, but is not expected to have been broadcast to the P2P network.
If this is determined to have been broadcast, then additional as yet implemented handling should be done to reconcile how to react to this event.
STATE_CLEARED
A cleared transaction is one that is known to have been broadcast to the P2P network.
Nuances:
block_hash will be NULL for transactions that have been broadcast. block_position and proof_data will also be NULL.
block_hash will be non-NULL to represent knowledge that it has been mined (via non-MAPI channels) and that we should fetch a merkle proof to verify it is in a given block. block_position and proof_data will be NULL.
block_hash, block_position and proof_data will all have valid unprocessed values if the application headers do not include the given block height yet.
STATE_SETTLED
A settled transaction is one that is known to have been mined in a block, and has been verified as being in that block through checking the merkle proof.
Nuances:
- block_hash will NULL for transactions that the legacy ElectrumX proof data was not retained
for. These will need to be obtained, if they contain unspent coins (UTXOs).
block_hash will be non non-NULL for transactions that have been mined and which we have the proof for, block_height will be the height of the block with the given hash and block_position will be the index of the transaction in the block. There will be a proof row in the TransactionProofs table mapped to the tx_hash and block_hash columns of the Transactions table.