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 28. This schema is include for reference purposes and cannot be used to 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.
Database schema
1-- Schema version 28.
2-- This schema is provided for reference purposes only.
3--
4-- Using it to create an ElectrumSV wallet is not supported, and will not work.
5
6BEGIN TRANSACTION;
7
8CREATE TABLE "AccountTransactions" (
9 tx_hash BLOB NOT NULL,
10 account_id INTEGER NOT NULL,
11 flags INTEGER NOT NULL DEFAULT 0,
12 description TEXT DEFAULT NULL,
13 date_created INTEGER NOT NULL,
14 date_updated INTEGER NOT NULL,
15 FOREIGN KEY (account_id) REFERENCES Accounts (account_id),
16 FOREIGN KEY (tx_hash) REFERENCES Transactions (tx_hash)
17);
18
19CREATE TABLE Accounts (
20 account_id INTEGER PRIMARY KEY,
21 default_masterkey_id INTEGER DEFAULT NULL,
22 default_script_type INTEGER NOT NULL,
23 account_name TEXT NOT NULL,
24 date_created INTEGER NOT NULL,
25 date_updated INTEGER NOT NULL,
26 FOREIGN KEY(default_masterkey_id) REFERENCES MasterKeys (masterkey_id)
27);
28
29CREATE TABLE Invoices (
30 invoice_id INTEGER PRIMARY KEY,
31 account_id INTEGER NOT NULL,
32 tx_hash BLOB DEFAULT NULL,
33 payment_uri TEXT NOT NULL,
34 description TEXT NULL,
35 invoice_flags INTEGER NOT NULL,
36 value INTEGER NOT NULL,
37 invoice_data BLOB NOT NULL,
38 date_expires INTEGER DEFAULT NULL,
39 date_created INTEGER NOT NULL,
40 date_updated INTEGER NOT NULL,
41 FOREIGN KEY (account_id) REFERENCES Accounts (account_id),
42 FOREIGN KEY (tx_hash) REFERENCES Transactions (tx_hash)
43);
44
45CREATE TABLE KeyInstanceScripts (
46 keyinstance_id INTEGER NOT NULL,
47 script_type INTEGER NOT NULL,
48 script_hash BLOB NOT NULL,
49 date_created INTEGER NOT NULL,
50 date_updated INTEGER NOT NULL,
51 FOREIGN KEY (keyinstance_id) REFERENCES KeyInstances (keyinstance_id)
52);
53
54CREATE TABLE "KeyInstances" (
55 keyinstance_id INTEGER PRIMARY KEY,
56 account_id INTEGER NOT NULL,
57 masterkey_id INTEGER DEFAULT NULL,
58 derivation_type INTEGER NOT NULL,
59 derivation_data BLOB NOT NULL,
60 derivation_data2 BLOB DEFAULT NULL,
61 flags INTEGER NOT NULL,
62 description TEXT DEFAULT NULL,
63 date_created INTEGER NOT NULL,
64 date_updated INTEGER NOT NULL,
65 FOREIGN KEY(account_id) REFERENCES Accounts (account_id) FOREIGN KEY(masterkey_id) REFERENCES MasterKeys (masterkey_id)
66);
67
68CREATE TABLE MasterKeys (
69 masterkey_id INTEGER PRIMARY KEY,
70 parent_masterkey_id INTEGER DEFAULT NULL,
71 derivation_type INTEGER NOT NULL,
72 derivation_data BLOB NOT NULL,
73 date_created INTEGER NOT NULL,
74 date_updated INTEGER NOT NULL,
75 FOREIGN KEY(parent_masterkey_id) REFERENCES MasterKeys (masterkey_id)
76);
77
78CREATE TABLE PaymentRequests (
79 paymentrequest_id INTEGER PRIMARY KEY,
80 keyinstance_id INTEGER NOT NULL,
81 state INTEGER NOT NULL,
82 description TEXT DEFAULT NULL,
83 expiration INTEGER DEFAULT NULL,
84 value INTEGER DEFAULT NULL,
85 date_created INTEGER NOT NULL,
86 date_updated INTEGER NOT NULL,
87 FOREIGN KEY(keyinstance_id) REFERENCES KeyInstances (keyinstance_id)
88);
89
90CREATE TABLE ServerAccounts (
91 server_type INTEGER NOT NULL,
92 url TEXT NOT NULL,
93 account_id INTEGER NOT NULL,
94 encrypted_api_key TEXT DEFAULT NULL,
95 fee_quote_json TEXT DEFAULT NULL,
96 date_last_connected INTEGER DEFAULT 0,
97 date_last_tried INTEGER DEFAULT 0,
98 date_created INTEGER NOT NULL,
99 date_updated INTEGER NOT NULL,
100 FOREIGN KEY (server_type, url) REFERENCES Servers (server_type, url),
101 FOREIGN KEY (account_id) REFERENCES Accounts (account_id)
102);
103
104CREATE TABLE Servers (
105 server_type INTEGER NOT NULL,
106 url TEXT NOT NULL,
107 encrypted_api_key TEXT DEFAULT NULL,
108 flags INTEGER NOT NULL DEFAULT 0,
109 fee_quote_json TEXT DEFAULT NULL,
110 date_last_connected INTEGER DEFAULT 0,
111 date_last_tried INTEGER DEFAULT 0,
112 date_created INTEGER NOT NULL,
113 date_updated INTEGER NOT NULL
114);
115
116CREATE TABLE TransactionInputs (
117 tx_hash BLOB NOT NULL,
118 txi_index INTEGER NOT NULL,
119 spent_tx_hash BLOB NOT NULL,
120 spent_txo_index INTEGER NOT NULL,
121 sequence INTEGER NOT NULL,
122 flags INTEGER NOT NULL,
123 script_offset INTEGER,
124 script_length INTEGER,
125 date_created INTEGER NOT NULL,
126 date_updated INTEGER NOT NULL,
127 FOREIGN KEY (tx_hash) REFERENCES Transactions (tx_hash)
128);
129
130CREATE TABLE "TransactionOutputs" (
131 tx_hash BLOB NOT NULL,
132 txo_index INTEGER NOT NULL,
133 value INTEGER NOT NULL,
134 keyinstance_id INTEGER DEFAULT NULL,
135 flags INTEGER NOT NULL,
136 script_type INTEGER DEFAULT 0,
137 script_hash BLOB NOT NULL DEFAULT x '',
138 script_offset INTEGER DEFAULT 0,
139 script_length INTEGER DEFAULT 0,
140 spending_tx_hash BLOB NULL,
141 spending_txi_index INTEGER NULL,
142 date_created INTEGER NOT NULL,
143 date_updated INTEGER NOT NULL,
144 FOREIGN KEY (tx_hash) REFERENCES Transactions (tx_hash),
145 FOREIGN KEY (keyinstance_id) REFERENCES KeyInstances (keyinstance_id)
146);
147
148CREATE TABLE Transactions (
149 tx_hash BLOB PRIMARY KEY,
150 tx_data BLOB DEFAULT NULL,
151 proof_data BLOB DEFAULT NULL,
152 block_height INTEGER DEFAULT NULL,
153 block_position INTEGER DEFAULT NULL,
154 fee_value INTEGER DEFAULT NULL,
155 flags INTEGER NOT NULL DEFAULT 0,
156 description TEXT DEFAULT NULL,
157 date_created INTEGER NOT NULL,
158 date_updated INTEGER NOT NULL,
159 locktime INTEGER DEFAULT NULL,
160 version INTEGER DEFAULT NULL,
161 block_hash BLOB DEFAULT NULL
162);
163
164CREATE TABLE WalletData (
165 key TEXT NOT NULL,
166 value TEXT NOT NULL,
167 date_created INTEGER NOT NULL,
168 date_updated INTEGER NOT NULL
169);
170
171CREATE TABLE WalletEvents (
172 event_id INTEGER PRIMARY KEY,
173 event_type INTEGER NOT NULL,
174 event_flags INTEGER NOT NULL,
175 account_id INTEGER,
176 date_created INTEGER NOT NULL,
177 date_updated INTEGER NOT NULL,
178 FOREIGN KEY(account_id) REFERENCES Accounts (account_id)
179);
180
181CREATE UNIQUE INDEX idx_WalletData_unique ON WalletData(key);
182
183CREATE UNIQUE INDEX idx_Invoices_unique ON Invoices(payment_uri);
184
185CREATE UNIQUE INDEX idx_AccountTransactions_unique ON AccountTransactions(tx_hash, account_id);
186
187CREATE UNIQUE INDEX idx_TransactionOutputs_unique ON TransactionOutputs(tx_hash, txo_index);
188
189CREATE UNIQUE INDEX idx_TransactionInputs_unique ON TransactionInputs(tx_hash, txi_index);
190
191CREATE UNIQUE INDEX idx_KeyInstanceScripts_unique ON KeyInstanceScripts(keyinstance_id, script_type);
192
193CREATE VIEW TransactionReceivedValues (account_id, tx_hash, keyinstance_id, value) AS
194SELECT
195 ATX.account_id,
196 ATX.tx_hash,
197 TXO.keyinstance_id,
198 TXO.value
199FROM
200 AccountTransactions ATX
201 INNER JOIN TransactionOutputs TXO ON TXO.tx_hash = ATX.tx_hash
202 INNER JOIN KeyInstances KI ON KI.keyinstance_id = TXO.keyinstance_id
203WHERE
204 TXO.keyinstance_id IS NOT NULL
205 AND KI.account_id = ATX.account_id;
206
207CREATE VIEW TransactionSpentValues (account_id, tx_hash, keyinstance_id, value) AS
208SELECT
209 ATX.account_id,
210 ATX.tx_hash,
211 PTXO.keyinstance_id,
212 PTXO.value
213FROM
214 AccountTransactions ATX
215 INNER JOIN TransactionInputs TXI ON TXI.tx_hash = ATX.tx_hash
216 INNER JOIN TransactionOutputs PTXO ON PTXO.tx_hash = TXI.spent_tx_hash
217 AND PTXO.txo_index = TXI.spent_txo_index
218 INNER JOIN KeyInstances KI ON KI.keyinstance_id = PTXO.keyinstance_id
219WHERE
220 PTXO.keyinstance_id IS NOT NULL
221 AND KI.account_id = ATX.account_id;
222
223CREATE VIEW TransactionValues (account_id, tx_hash, keyinstance_id, value) AS
224SELECT
225 account_id,
226 tx_hash,
227 keyinstance_id,
228 value
229FROM
230 TransactionReceivedValues
231UNION
232ALL
233SELECT
234 account_id,
235 tx_hash,
236 keyinstance_id,
237 - value
238FROM
239 TransactionSpentValues;
240
241CREATE UNIQUE INDEX idx_Servers_unique ON Servers(server_type, url);
242
243CREATE UNIQUE INDEX idx_ServerAccounts_unique ON ServerAccounts(server_type, url, account_id);
244
245COMMIT;