forked from ElementsProject/lightning
-
Notifications
You must be signed in to change notification settings - Fork 0
/
db.c
1483 lines (1332 loc) · 46.9 KB
/
db.c
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
#include "db.h"
#include <ccan/array_size/array_size.h>
#include <ccan/tal/str/str.h>
#include <common/node_id.h>
#include <common/onionreply.h>
#include <common/version.h>
#include <inttypes.h>
#include <lightningd/lightningd.h>
#include <lightningd/log.h>
#include <lightningd/plugin_hook.h>
#include <wallet/db_common.h>
#define NSEC_IN_SEC 1000000000
struct migration {
const char *sql;
void (*func)(struct lightningd *ld, struct db *db);
};
static void migrate_pr2342_feerate_per_channel(struct lightningd *ld, struct db *db);
/* Do not reorder or remove elements from this array, it is used to
* migrate existing databases from a previous state, based on the
* string indices */
static struct migration dbmigrations[] = {
{SQL("CREATE TABLE version (version INTEGER)"), NULL},
{SQL("INSERT INTO version VALUES (1)"), NULL},
{SQL("CREATE TABLE outputs ("
" prev_out_tx BLOB"
", prev_out_index INTEGER"
", value BIGINT"
", type INTEGER"
", status INTEGER"
", keyindex INTEGER"
", PRIMARY KEY (prev_out_tx, prev_out_index));"),
NULL},
{SQL("CREATE TABLE vars ("
" name VARCHAR(32)"
", val VARCHAR(255)"
", PRIMARY KEY (name)"
");"),
NULL},
{SQL("CREATE TABLE shachains ("
" id BIGSERIAL"
", min_index BIGINT"
", num_valid BIGINT"
", PRIMARY KEY (id)"
");"),
NULL},
{SQL("CREATE TABLE shachain_known ("
" shachain_id BIGINT REFERENCES shachains(id) ON DELETE CASCADE"
", pos INTEGER"
", idx BIGINT"
", hash BLOB"
", PRIMARY KEY (shachain_id, pos)"
");"),
NULL},
{SQL("CREATE TABLE peers ("
" id BIGSERIAL"
", node_id BLOB UNIQUE" /* pubkey */
", address TEXT"
", PRIMARY KEY (id)"
");"),
NULL},
{SQL("CREATE TABLE channels ("
" id BIGSERIAL," /* chan->id */
" peer_id BIGINT REFERENCES peers(id) ON DELETE CASCADE,"
" short_channel_id TEXT,"
" channel_config_local BIGINT,"
" channel_config_remote BIGINT,"
" state INTEGER,"
" funder INTEGER,"
" channel_flags INTEGER,"
" minimum_depth INTEGER,"
" next_index_local BIGINT,"
" next_index_remote BIGINT,"
" next_htlc_id BIGINT,"
" funding_tx_id BLOB,"
" funding_tx_outnum INTEGER,"
" funding_satoshi BIGINT,"
" funding_locked_remote INTEGER,"
" push_msatoshi BIGINT,"
" msatoshi_local BIGINT," /* our_msatoshi */
/* START channel_info */
" fundingkey_remote BLOB,"
" revocation_basepoint_remote BLOB,"
" payment_basepoint_remote BLOB,"
" htlc_basepoint_remote BLOB,"
" delayed_payment_basepoint_remote BLOB,"
" per_commit_remote BLOB,"
" old_per_commit_remote BLOB,"
" local_feerate_per_kw INTEGER,"
" remote_feerate_per_kw INTEGER,"
/* END channel_info */
" shachain_remote_id BIGINT,"
" shutdown_scriptpubkey_remote BLOB,"
" shutdown_keyidx_local BIGINT,"
" last_sent_commit_state BIGINT,"
" last_sent_commit_id INTEGER,"
" last_tx BLOB,"
" last_sig BLOB,"
" closing_fee_received INTEGER,"
" closing_sig_received BLOB,"
" PRIMARY KEY (id)"
");"),
NULL},
{SQL("CREATE TABLE channel_configs ("
" id BIGSERIAL,"
" dust_limit_satoshis BIGINT,"
" max_htlc_value_in_flight_msat BIGINT,"
" channel_reserve_satoshis BIGINT,"
" htlc_minimum_msat BIGINT,"
" to_self_delay INTEGER,"
" max_accepted_htlcs INTEGER,"
" PRIMARY KEY (id)"
");"),
NULL},
{SQL("CREATE TABLE channel_htlcs ("
" id BIGSERIAL,"
" channel_id BIGINT REFERENCES channels(id) ON DELETE CASCADE,"
" channel_htlc_id BIGINT,"
" direction INTEGER,"
" origin_htlc BIGINT,"
" msatoshi BIGINT,"
" cltv_expiry INTEGER,"
" payment_hash BLOB,"
" payment_key BLOB,"
" routing_onion BLOB,"
" failuremsg BLOB,"
" malformed_onion INTEGER,"
" hstate INTEGER,"
" shared_secret BLOB,"
" PRIMARY KEY (id),"
" UNIQUE (channel_id, channel_htlc_id, direction)"
");"),
NULL},
{SQL("CREATE TABLE invoices ("
" id BIGSERIAL,"
" state INTEGER,"
" msatoshi BIGINT,"
" payment_hash BLOB,"
" payment_key BLOB,"
" label TEXT,"
" PRIMARY KEY (id),"
" UNIQUE (label),"
" UNIQUE (payment_hash)"
");"),
NULL},
{SQL("CREATE TABLE payments ("
" id BIGSERIAL,"
" timestamp INTEGER,"
" status INTEGER,"
" payment_hash BLOB,"
" direction INTEGER,"
" destination BLOB,"
" msatoshi BIGINT,"
" PRIMARY KEY (id),"
" UNIQUE (payment_hash)"
");"),
NULL},
/* Add expiry field to invoices (effectively infinite). */
{SQL("ALTER TABLE invoices ADD expiry_time BIGINT;"), NULL},
{SQL("UPDATE invoices SET expiry_time=9223372036854775807;"), NULL},
/* Add pay_index field to paid invoices (initially, same order as id). */
{SQL("ALTER TABLE invoices ADD pay_index BIGINT;"), NULL},
{SQL("CREATE UNIQUE INDEX invoices_pay_index ON invoices(pay_index);"),
NULL},
{SQL("UPDATE invoices SET pay_index=id WHERE state=1;"),
NULL}, /* only paid invoice */
/* Create next_pay_index variable (highest pay_index). */
{SQL("INSERT INTO vars(name, val)"
" VALUES('next_pay_index', "
" COALESCE((SELECT MAX(pay_index) FROM invoices WHERE state=1), 0) "
"+ 1"
" );"),
NULL},
/* Create first_block field; initialize from channel id if any.
* This fails for channels still awaiting lockin, but that only applies to
* pre-release software, so it's forgivable. */
{SQL("ALTER TABLE channels ADD first_blocknum BIGINT;"), NULL},
{SQL("UPDATE channels SET first_blocknum=1 WHERE short_channel_id IS NOT NULL;"),
NULL},
{SQL("ALTER TABLE outputs ADD COLUMN channel_id BIGINT;"), NULL},
{SQL("ALTER TABLE outputs ADD COLUMN peer_id BLOB;"), NULL},
{SQL("ALTER TABLE outputs ADD COLUMN commitment_point BLOB;"), NULL},
{SQL("ALTER TABLE invoices ADD COLUMN msatoshi_received BIGINT;"), NULL},
/* Normally impossible, so at least we'll know if databases are ancient. */
{SQL("UPDATE invoices SET msatoshi_received=0 WHERE state=1;"), NULL},
{SQL("ALTER TABLE channels ADD COLUMN last_was_revoke INTEGER;"), NULL},
/* We no longer record incoming payments: invoices cover that.
* Without ALTER_TABLE DROP COLUMN support we need to do this by
* rename & copy, which works because there are no triggers etc. */
{SQL("ALTER TABLE payments RENAME TO temp_payments;"), NULL},
{SQL("CREATE TABLE payments ("
" id BIGSERIAL,"
" timestamp INTEGER,"
" status INTEGER,"
" payment_hash BLOB,"
" destination BLOB,"
" msatoshi BIGINT,"
" PRIMARY KEY (id),"
" UNIQUE (payment_hash)"
");"),
NULL},
{SQL("INSERT INTO payments SELECT id, timestamp, status, payment_hash, "
"destination, msatoshi FROM temp_payments WHERE direction=1;"),
NULL},
{SQL("DROP TABLE temp_payments;"), NULL},
/* We need to keep the preimage in case they ask to pay again. */
{SQL("ALTER TABLE payments ADD COLUMN payment_preimage BLOB;"), NULL},
/* We need to keep the shared secrets to decode error returns. */
{SQL("ALTER TABLE payments ADD COLUMN path_secrets BLOB;"), NULL},
/* Create time-of-payment of invoice, default already-paid
* invoices to current time. */
{SQL("ALTER TABLE invoices ADD paid_timestamp BIGINT;"), NULL},
{SQL("UPDATE invoices"
" SET paid_timestamp = CURRENT_TIMESTAMP()"
" WHERE state = 1;"),
NULL},
/* We need to keep the route node pubkeys and short channel ids to
* correctly mark routing failures. We separate short channel ids
* because we cannot safely save them as blobs due to byteorder
* concerns. */
{SQL("ALTER TABLE payments ADD COLUMN route_nodes BLOB;"), NULL},
{SQL("ALTER TABLE payments ADD COLUMN route_channels BLOB;"), NULL},
{SQL("CREATE TABLE htlc_sigs (channelid INTEGER REFERENCES channels(id) ON "
"DELETE CASCADE, signature BLOB);"),
NULL},
{SQL("CREATE INDEX channel_idx ON htlc_sigs (channelid)"), NULL},
/* Get rid of OPENINGD entries; we don't put them in db any more */
{SQL("DELETE FROM channels WHERE state=1"), NULL},
/* Keep track of db upgrades, for debugging */
{SQL("CREATE TABLE db_upgrades (upgrade_from INTEGER, lightning_version "
"TEXT);"),
NULL},
/* We used not to clean up peers when their channels were gone. */
{SQL("DELETE FROM peers WHERE id NOT IN (SELECT peer_id FROM channels);"),
NULL},
/* The ONCHAIND_CHEATED/THEIR_UNILATERAL/OUR_UNILATERAL/MUTUAL are now one
*/
{SQL("UPDATE channels SET STATE = 8 WHERE state > 8;"), NULL},
/* Add bolt11 to invoices table*/
{SQL("ALTER TABLE invoices ADD bolt11 TEXT;"), NULL},
/* What do we think the head of the blockchain looks like? Used
* primarily to track confirmations across restarts and making
* sure we handle reorgs correctly. */
{SQL("CREATE TABLE blocks (height INT, hash BLOB, prev_hash BLOB, "
"UNIQUE(height));"),
NULL},
/* ON DELETE CASCADE would have been nice for confirmation_height,
* so that we automatically delete outputs that fall off the
* blockchain and then we rediscover them if they are included
* again. However, we have the their_unilateral/to_us which we
* can't simply recognize from the chain without additional
* hints. So we just mark them as unconfirmed should the block
* die. */
{SQL("ALTER TABLE outputs ADD COLUMN confirmation_height INTEGER "
"REFERENCES blocks(height) ON DELETE SET NULL;"),
NULL},
{SQL("ALTER TABLE outputs ADD COLUMN spend_height INTEGER REFERENCES "
"blocks(height) ON DELETE SET NULL;"),
NULL},
/* Create a covering index that covers both fields */
{SQL("CREATE INDEX output_height_idx ON outputs (confirmation_height, "
"spend_height);"),
NULL},
{SQL("CREATE TABLE utxoset ("
" txid BLOB,"
" outnum INT,"
" blockheight INT REFERENCES blocks(height) ON DELETE CASCADE,"
" spendheight INT REFERENCES blocks(height) ON DELETE SET NULL,"
" txindex INT,"
" scriptpubkey BLOB,"
" satoshis BIGINT,"
" PRIMARY KEY(txid, outnum));"),
NULL},
{SQL("CREATE INDEX short_channel_id ON utxoset (blockheight, txindex, "
"outnum)"),
NULL},
/* Necessary index for long rollbacks of the blockchain, otherwise we're
* doing table scans for every block removed. */
{SQL("CREATE INDEX utxoset_spend ON utxoset (spendheight)"), NULL},
/* Assign key 0 to unassigned shutdown_keyidx_local. */
{SQL("UPDATE channels SET shutdown_keyidx_local=0 WHERE "
"shutdown_keyidx_local = -1;"),
NULL},
/* FIXME: We should rename shutdown_keyidx_local to final_key_index */
/* -- Payment routing failure information -- */
/* BLOB if failure was due to unparseable onion, NULL otherwise */
{SQL("ALTER TABLE payments ADD failonionreply BLOB;"), NULL},
/* 0 if we could theoretically retry, 1 if PERM fail at payee */
{SQL("ALTER TABLE payments ADD faildestperm INTEGER;"), NULL},
/* Contents of routing_failure (only if not unparseable onion) */
{SQL("ALTER TABLE payments ADD failindex INTEGER;"),
NULL}, /* erring_index */
{SQL("ALTER TABLE payments ADD failcode INTEGER;"), NULL}, /* failcode */
{SQL("ALTER TABLE payments ADD failnode BLOB;"), NULL}, /* erring_node */
{SQL("ALTER TABLE payments ADD failchannel TEXT;"),
NULL}, /* erring_channel */
{SQL("ALTER TABLE payments ADD failupdate BLOB;"),
NULL}, /* channel_update - can be NULL*/
/* -- Payment routing failure information ends -- */
/* Delete route data for already succeeded or failed payments */
{SQL("UPDATE payments"
" SET path_secrets = NULL"
" , route_nodes = NULL"
" , route_channels = NULL"
" WHERE status <> 0;"),
NULL}, /* PAYMENT_PENDING */
/* -- Routing statistics -- */
{SQL("ALTER TABLE channels ADD in_payments_offered INTEGER DEFAULT 0;"), NULL},
{SQL("ALTER TABLE channels ADD in_payments_fulfilled INTEGER DEFAULT 0;"), NULL},
{SQL("ALTER TABLE channels ADD in_msatoshi_offered BIGINT DEFAULT 0;"), NULL},
{SQL("ALTER TABLE channels ADD in_msatoshi_fulfilled BIGINT DEFAULT 0;"), NULL},
{SQL("ALTER TABLE channels ADD out_payments_offered INTEGER DEFAULT 0;"), NULL},
{SQL("ALTER TABLE channels ADD out_payments_fulfilled INTEGER DEFAULT 0;"), NULL},
{SQL("ALTER TABLE channels ADD out_msatoshi_offered BIGINT DEFAULT 0;"), NULL},
{SQL("ALTER TABLE channels ADD out_msatoshi_fulfilled BIGINT DEFAULT 0;"), NULL},
{SQL("UPDATE channels"
" SET in_payments_offered = 0, in_payments_fulfilled = 0"
" , in_msatoshi_offered = 0, in_msatoshi_fulfilled = 0"
" , out_payments_offered = 0, out_payments_fulfilled = 0"
" , out_msatoshi_offered = 0, out_msatoshi_fulfilled = 0"
" ;"),
NULL},
/* -- Routing statistics ends --*/
/* Record the msatoshi actually sent in a payment. */
{SQL("ALTER TABLE payments ADD msatoshi_sent BIGINT;"), NULL},
{SQL("UPDATE payments SET msatoshi_sent = msatoshi;"), NULL},
/* Delete dangling utxoset entries due to Issue #1280 */
{SQL("DELETE FROM utxoset WHERE blockheight IN ("
" SELECT DISTINCT(blockheight)"
" FROM utxoset LEFT OUTER JOIN blocks on (blockheight = "
"blocks.height) "
" WHERE blocks.hash IS NULL"
");"),
NULL},
/* Record feerate range, to optimize onchaind grinding actual fees. */
{SQL("ALTER TABLE channels ADD min_possible_feerate INTEGER;"), NULL},
{SQL("ALTER TABLE channels ADD max_possible_feerate INTEGER;"), NULL},
/* https://bitcoinfees.github.io/#1d says Dec 17 peak was ~1M sat/kb
* which is 250,000 sat/Sipa */
{SQL("UPDATE channels SET min_possible_feerate=0, "
"max_possible_feerate=250000;"),
NULL},
/* -- Min and max msatoshi_to_us -- */
{SQL("ALTER TABLE channels ADD msatoshi_to_us_min BIGINT;"), NULL},
{SQL("ALTER TABLE channels ADD msatoshi_to_us_max BIGINT;"), NULL},
{SQL("UPDATE channels"
" SET msatoshi_to_us_min = msatoshi_local"
" , msatoshi_to_us_max = msatoshi_local"
" ;"),
NULL},
/* -- Min and max msatoshi_to_us ends -- */
/* Transactions we are interested in. Either we sent them ourselves or we
* are watching them. We don't cascade block height deletes so we don't
* forget any of them by accident.*/
{SQL("CREATE TABLE transactions ("
" id BLOB"
", blockheight INTEGER REFERENCES blocks(height) ON DELETE SET NULL"
", txindex INTEGER"
", rawtx BLOB"
", PRIMARY KEY (id)"
");"),
NULL},
/* -- Detailed payment failure -- */
{SQL("ALTER TABLE payments ADD faildetail TEXT;"), NULL},
{SQL("UPDATE payments"
" SET faildetail = 'unspecified payment failure reason'"
" WHERE status = 2;"),
NULL}, /* PAYMENT_FAILED */
/* -- Detailed payment faiure ends -- */
{SQL("CREATE TABLE channeltxs ("
/* The id serves as insertion order and short ID */
" id BIGSERIAL"
", channel_id BIGINT REFERENCES channels(id) ON DELETE CASCADE"
", type INTEGER"
", transaction_id BLOB REFERENCES transactions(id) ON DELETE CASCADE"
/* The input_num is only used by the txo_watch, 0 if txwatch */
", input_num INTEGER"
/* The height at which we sent the depth notice */
", blockheight INTEGER REFERENCES blocks(height) ON DELETE CASCADE"
", PRIMARY KEY(id)"
");"),
NULL},
/* -- Set the correct rescan height for PR #1398 -- */
/* Delete blocks that are higher than our initial scan point, this is a
* no-op if we don't have a channel. */
{SQL("DELETE FROM blocks WHERE height > (SELECT MIN(first_blocknum) FROM "
"channels);"),
NULL},
/* Now make sure we have the lower bound block with the first_blocknum
* height. This may introduce a block with NULL height if we didn't have any
* blocks, remove that in the next. */
{SQL("INSERT INTO blocks (height) VALUES ((SELECT "
"MIN(first_blocknum) FROM channels)) "
"ON CONFLICT(height) DO NOTHING;"),
NULL},
{SQL("DELETE FROM blocks WHERE height IS NULL;"), NULL},
/* -- End of PR #1398 -- */
{SQL("ALTER TABLE invoices ADD description TEXT;"), NULL},
/* FIXME: payments table 'description' is really a 'label' */
{SQL("ALTER TABLE payments ADD description TEXT;"), NULL},
/* future_per_commitment_point if other side proves we're out of date -- */
{SQL("ALTER TABLE channels ADD future_per_commitment_point BLOB;"), NULL},
/* last_sent_commit array fix */
{SQL("ALTER TABLE channels ADD last_sent_commit BLOB;"), NULL},
/* Stats table to track forwarded HTLCs. The values in the HTLCs
* and their states are replicated here and the entries are not
* deleted when the HTLC entries or the channel entries are
* deleted to avoid unexpected drops in statistics. */
{SQL("CREATE TABLE forwarded_payments ("
" in_htlc_id BIGINT REFERENCES channel_htlcs(id) ON DELETE SET NULL"
", out_htlc_id BIGINT REFERENCES channel_htlcs(id) ON DELETE SET NULL"
", in_channel_scid BIGINT"
", out_channel_scid BIGINT"
", in_msatoshi BIGINT"
", out_msatoshi BIGINT"
", state INTEGER"
", UNIQUE(in_htlc_id, out_htlc_id)"
");"),
NULL},
/* Add a direction for failed payments. */
{SQL("ALTER TABLE payments ADD faildirection INTEGER;"),
NULL}, /* erring_direction */
/* Fix dangling peers with no channels. */
{SQL("DELETE FROM peers WHERE id NOT IN (SELECT peer_id FROM channels);"),
NULL},
{SQL("ALTER TABLE outputs ADD scriptpubkey BLOB;"), NULL},
/* Keep bolt11 string for payments. */
{SQL("ALTER TABLE payments ADD bolt11 TEXT;"), NULL},
/* PR #2342 feerate per channel */
{SQL("ALTER TABLE channels ADD feerate_base INTEGER;"), NULL},
{SQL("ALTER TABLE channels ADD feerate_ppm INTEGER;"), NULL},
{NULL, migrate_pr2342_feerate_per_channel},
{SQL("ALTER TABLE channel_htlcs ADD received_time BIGINT"), NULL},
{SQL("ALTER TABLE forwarded_payments ADD received_time BIGINT"), NULL},
{SQL("ALTER TABLE forwarded_payments ADD resolved_time BIGINT"), NULL},
{SQL("ALTER TABLE channels ADD remote_upfront_shutdown_script BLOB;"),
NULL},
/* PR #2524: Add failcode into forward_payment */
{SQL("ALTER TABLE forwarded_payments ADD failcode INTEGER;"), NULL},
/* remote signatures for channel announcement */
{SQL("ALTER TABLE channels ADD remote_ann_node_sig BLOB;"), NULL},
{SQL("ALTER TABLE channels ADD remote_ann_bitcoin_sig BLOB;"), NULL},
/* Additional information for transaction tracking and listing */
{SQL("ALTER TABLE transactions ADD type BIGINT;"), NULL},
/* Not a foreign key on purpose since we still delete channels from
* the DB which would remove this. It is mainly used to group payments
* in the list view anyway, e.g., show all close and htlc transactions
* as a single bundle. */
{SQL("ALTER TABLE transactions ADD channel_id BIGINT;"), NULL},
/* Convert pre-Adelaide short_channel_ids */
{SQL("UPDATE channels"
" SET short_channel_id = REPLACE(short_channel_id, ':', 'x')"
" WHERE short_channel_id IS NOT NULL;"), NULL },
{SQL("UPDATE payments SET failchannel = REPLACE(failchannel, ':', 'x')"
" WHERE failchannel IS NOT NULL;"), NULL },
/* option_static_remotekey is nailed at creation time. */
{SQL("ALTER TABLE channels ADD COLUMN option_static_remotekey INTEGER"
" DEFAULT 0;"), NULL },
{SQL("ALTER TABLE vars ADD COLUMN intval INTEGER"), NULL},
{SQL("ALTER TABLE vars ADD COLUMN blobval BLOB"), NULL},
{SQL("UPDATE vars SET intval = CAST(val AS INTEGER) WHERE name IN ('bip32_max_index', 'last_processed_block', 'next_pay_index')"), NULL},
{SQL("UPDATE vars SET blobval = CAST(val AS BLOB) WHERE name = 'genesis_hash'"), NULL},
{SQL("CREATE TABLE transaction_annotations ("
/* Not making this a reference since we usually filter the TX by
* walking its inputs and outputs, and only afterwards storing it in
* the DB. Having a reference here would point into the void until we
* add the matching TX. */
" txid BLOB"
", idx INTEGER" /* 0 when location is the tx, the index of the output or input otherwise */
", location INTEGER" /* The transaction itself, the output at idx, or the input at idx */
", type INTEGER"
", channel BIGINT REFERENCES channels(id)"
", UNIQUE(txid, idx)"
");"), NULL},
{SQL("ALTER TABLE channels ADD shutdown_scriptpubkey_local BLOB;"),
NULL},
/* See https://github.com/ElementsProject/lightning/issues/3189 */
{SQL("UPDATE forwarded_payments SET received_time=0 WHERE received_time IS NULL;"),
NULL},
{SQL("ALTER TABLE invoices ADD COLUMN features BLOB DEFAULT '';"), NULL},
/* We can now have multiple payments in progress for a single hash, so
* add two fields; combination of payment_hash & partid is unique. */
{SQL("ALTER TABLE payments RENAME TO temp_payments;"), NULL},
{SQL("CREATE TABLE payments ("
" id BIGSERIAL"
", timestamp INTEGER"
", status INTEGER"
", payment_hash BLOB"
", destination BLOB"
", msatoshi BIGINT"
", payment_preimage BLOB"
", path_secrets BLOB"
", route_nodes BLOB"
", route_channels BLOB"
", failonionreply BLOB"
", faildestperm INTEGER"
", failindex INTEGER"
", failcode INTEGER"
", failnode BLOB"
", failchannel TEXT"
", failupdate BLOB"
", msatoshi_sent BIGINT"
", faildetail TEXT"
", description TEXT"
", faildirection INTEGER"
", bolt11 TEXT"
", total_msat BIGINT"
", partid BIGINT"
", PRIMARY KEY (id)"
", UNIQUE (payment_hash, partid))"), NULL},
{SQL("INSERT INTO payments ("
"id"
", timestamp"
", status"
", payment_hash"
", destination"
", msatoshi"
", payment_preimage"
", path_secrets"
", route_nodes"
", route_channels"
", failonionreply"
", faildestperm"
", failindex"
", failcode"
", failnode"
", failchannel"
", failupdate"
", msatoshi_sent"
", faildetail"
", description"
", faildirection"
", bolt11)"
"SELECT id"
", timestamp"
", status"
", payment_hash"
", destination"
", msatoshi"
", payment_preimage"
", path_secrets"
", route_nodes"
", route_channels"
", failonionreply"
", faildestperm"
", failindex"
", failcode"
", failnode"
", failchannel"
", failupdate"
", msatoshi_sent"
", faildetail"
", description"
", faildirection"
", bolt11 FROM temp_payments;"), NULL},
{SQL("UPDATE payments SET total_msat = msatoshi;"), NULL},
{SQL("UPDATE payments SET partid = 0;"), NULL},
{SQL("DROP TABLE temp_payments;"), NULL},
{SQL("ALTER TABLE channel_htlcs ADD partid BIGINT;"), NULL},
{SQL("UPDATE channel_htlcs SET partid = 0;"), NULL},
{SQL("CREATE TABLE channel_feerates ("
" channel_id BIGINT REFERENCES channels(id) ON DELETE CASCADE,"
" hstate INTEGER,"
" feerate_per_kw INTEGER,"
" UNIQUE (channel_id, hstate)"
");"),
NULL},
/* Cast old-style per-side feerates into most likely layout for statewise
* feerates. */
/* If we're funder (LOCAL=0):
* Then our feerate is set last (SENT_ADD_ACK_REVOCATION = 4) */
{SQL("INSERT INTO channel_feerates(channel_id, hstate, feerate_per_kw)"
" SELECT id, 4, local_feerate_per_kw FROM channels WHERE funder = 0;"),
NULL},
/* If different, assume their feerate is in state SENT_ADD_COMMIT = 1 */
{SQL("INSERT INTO channel_feerates(channel_id, hstate, feerate_per_kw)"
" SELECT id, 1, remote_feerate_per_kw FROM channels WHERE funder = 0 and local_feerate_per_kw != remote_feerate_per_kw;"),
NULL},
/* If they're funder (REMOTE=1):
* Then their feerate is set last (RCVD_ADD_ACK_REVOCATION = 14) */
{SQL("INSERT INTO channel_feerates(channel_id, hstate, feerate_per_kw)"
" SELECT id, 14, remote_feerate_per_kw FROM channels WHERE funder = 1;"),
NULL},
/* If different, assume their feerate is in state RCVD_ADD_COMMIT = 11 */
{SQL("INSERT INTO channel_feerates(channel_id, hstate, feerate_per_kw)"
" SELECT id, 11, local_feerate_per_kw FROM channels WHERE funder = 1 and local_feerate_per_kw != remote_feerate_per_kw;"),
NULL},
/* FIXME: Remove now-unused local_feerate_per_kw and remote_feerate_per_kw from channels */
{SQL("INSERT INTO vars (name, intval) VALUES ('data_version', 0);"), NULL},
};
/* Leak tracking. */
#if DEVELOPER
static void db_assert_no_outstanding_statements(struct db *db)
{
struct db_stmt *stmt;
stmt = list_top(&db->pending_statements, struct db_stmt, list);
if (stmt)
db_fatal("Unfinalized statement %s", stmt->location);
}
#else
static void db_assert_no_outstanding_statements(struct db *db)
{
}
#endif
static void db_stmt_free(struct db_stmt *stmt)
{
if (!stmt->executed)
fatal("Freeing an un-executed statement from %s: %s",
stmt->location, stmt->query->query);
if (stmt->inner_stmt)
stmt->db->config->stmt_free_fn(stmt);
assert(stmt->inner_stmt == NULL);
}
struct db_stmt *db_prepare_v2_(const char *location, struct db *db,
const char *query_id)
{
struct db_stmt *stmt = tal(db, struct db_stmt);
size_t num_slots;
stmt->query = NULL;
/* Normalize query_id paths, because unit tests are compiled with this
* prefix. */
if (strncmp(query_id, "./", 2) == 0)
query_id += 2;
if (!db->in_transaction)
db_fatal("Attempting to prepare a db_stmt outside of a "
"transaction: %s", location);
/* Look up the query by its ID */
for (size_t i = 0; i < db->config->num_queries; i++) {
if (streq(query_id, db->config->queries[i].name)) {
stmt->query = &db->config->queries[i];
break;
}
}
if (stmt->query == NULL)
fatal("Could not resolve query %s", query_id);
num_slots = stmt->query->placeholders;
/* Allocate the slots for placeholders/bindings, zeroed next since
* that sets the type to DB_BINDING_UNINITIALIZED for later checks. */
stmt->bindings = tal_arr(stmt, struct db_binding, num_slots);
for (size_t i=0; i<num_slots; i++)
stmt->bindings[i].type = DB_BINDING_UNINITIALIZED;
stmt->location = location;
stmt->error = NULL;
stmt->db = db;
stmt->executed = false;
stmt->inner_stmt = NULL;
tal_add_destructor(stmt, db_stmt_free);
list_add(&db->pending_statements, &stmt->list);
return stmt;
}
#define db_prepare_v2(db,query) \
db_prepare_v2_(__FILE__ ":" stringify(__LINE__), db, query)
bool db_step(struct db_stmt *stmt)
{
assert(stmt->executed);
return stmt->db->config->step_fn(stmt);
}
u64 db_column_u64(struct db_stmt *stmt, int col)
{
if (db_column_is_null(stmt, col)) {
log_broken(stmt->db->log, "Accessing a null column %d in query %s", col, stmt->query->query);
return 0;
}
return stmt->db->config->column_u64_fn(stmt, col);
}
int db_column_int_or_default(struct db_stmt *stmt, int col, int def)
{
if (db_column_is_null(stmt, col))
return def;
else
return db_column_int(stmt, col);
}
int db_column_int(struct db_stmt *stmt, int col)
{
if (db_column_is_null(stmt, col)) {
log_broken(stmt->db->log, "Accessing a null column %d in query %s", col, stmt->query->query);
return 0;
}
return stmt->db->config->column_int_fn(stmt, col);
}
size_t db_column_bytes(struct db_stmt *stmt, int col)
{
if (db_column_is_null(stmt, col)) {
log_broken(stmt->db->log, "Accessing a null column %d in query %s", col, stmt->query->query);
return 0;
}
return stmt->db->config->column_bytes_fn(stmt, col);
}
int db_column_is_null(struct db_stmt *stmt, int col)
{
return stmt->db->config->column_is_null_fn(stmt, col);
}
const void *db_column_blob(struct db_stmt *stmt, int col)
{
if (db_column_is_null(stmt, col)) {
log_broken(stmt->db->log, "Accessing a null column %d in query %s", col, stmt->query->query);
return NULL;
}
return stmt->db->config->column_blob_fn(stmt, col);
}
const unsigned char *db_column_text(struct db_stmt *stmt, int col)
{
if (db_column_is_null(stmt, col)) {
log_broken(stmt->db->log, "Accessing a null column %d in query %s", col, stmt->query->query);
return NULL;
}
return stmt->db->config->column_text_fn(stmt, col);
}
size_t db_count_changes(struct db_stmt *stmt)
{
assert(stmt->executed);
return stmt->db->config->count_changes_fn(stmt);
}
u64 db_last_insert_id_v2(struct db_stmt *stmt TAKES)
{
u64 id;
assert(stmt->executed);
id = stmt->db->config->last_insert_id_fn(stmt);
if (taken(stmt))
tal_free(stmt);
return id;
}
static void destroy_db(struct db *db)
{
db_assert_no_outstanding_statements(db);
if (db->config->teardown_fn)
db->config->teardown_fn(db);
}
/* We expect min changes (ie. BEGIN TRANSACTION): report if more.
* Optionally add "final" at the end (ie. COMMIT). */
static void db_report_changes(struct db *db, const char *final, size_t min)
{
assert(db->changes);
assert(tal_count(db->changes) >= min);
/* Having changes implies that we have a dirty TX. The opposite is
* currently not true, e.g., the postgres driver doesn't record
* changes yet. */
assert(!tal_count(db->changes) || db->dirty);
if (tal_count(db->changes) > min)
plugin_hook_db_sync(db);
db->changes = tal_free(db->changes);
}
static void db_prepare_for_changes(struct db *db)
{
assert(!db->changes);
db->changes = tal_arr(db, const char *, 0);
}
bool db_in_transaction(struct db *db)
{
return db->in_transaction;
}
void db_begin_transaction_(struct db *db, const char *location)
{
bool ok;
if (db->in_transaction)
db_fatal("Already in transaction from %s", db->in_transaction);
/* No writes yet. */
db->dirty = false;
db_prepare_for_changes(db);
ok = db->config->begin_tx_fn(db);
if (!ok)
db_fatal("Failed to start DB transaction: %s", db->error);
db->in_transaction = location;
}
/* By making the update conditional on the current value we expect we
* are implementing an optimistic lock: if the update results in
* changes on the DB we know that the data_version did not change
* under our feet and no other transaction ran in the meantime.
*
* Notice that this update effectively locks the row, so that other
* operations attempting to change this outside the transaction will
* wait for this transaction to complete. The external change will
* ultimately fail the changes test below, it'll just delay its abort
* until our transaction is committed.
*/
static void db_data_version_incr(struct db *db)
{
struct db_stmt *stmt = db_prepare_v2(
db, SQL("UPDATE vars "
"SET intval = intval + 1 "
"WHERE name = 'data_version'"
" AND intval = ?"));
db_bind_int(stmt, 0, db->data_version);
db_exec_prepared_v2(stmt);
if (db_count_changes(stmt) != 1)
fatal("Optimistic lock on the database failed. There may be a "
"concurrent access to the database. Aborting since "
"concurrent access is unsafe.");
tal_free(stmt);
db->data_version++;
}
void db_commit_transaction(struct db *db)
{
bool ok;
assert(db->in_transaction);
db_assert_no_outstanding_statements(db);
/* Increment before reporting changes to an eventual plugin. */
if (db->dirty)
db_data_version_incr(db);
db_report_changes(db, NULL, 0);
ok = db->config->commit_tx_fn(db);
if (!ok)
db_fatal("Failed to commit DB transaction: %s", db->error);
db->in_transaction = NULL;
db->dirty = false;
}
static struct db_config *db_config_find(const char *dsn)
{
size_t num_configs;
struct db_config **configs = autodata_get(db_backends, &num_configs);
const char *sep, *driver_name;
sep = strstr(dsn, "://");
if (!sep)
db_fatal("%s doesn't look like a valid data-source name (missing \"://\" separator.", dsn);
driver_name = tal_strndup(tmpctx, dsn, sep - dsn);
for (size_t i=0; i<num_configs; i++) {
if (streq(driver_name, configs[i]->name)) {
tal_free(driver_name);
return configs[i];
}
}
tal_free(driver_name);
return NULL;
}
/**
* db_open - Open or create a sqlite3 database
*/
static struct db *db_open(const tal_t *ctx, char *filename)
{
struct db *db;
db = tal(ctx, struct db);
db->filename = tal_strdup(db, filename);
list_head_init(&db->pending_statements);
if (!strstr(db->filename, "://"))
db_fatal("Could not extract driver name from \"%s\"", db->filename);
db->config = db_config_find(db->filename);
if (!db->config)
db_fatal("Unable to find DB driver for %s", db->filename);
tal_add_destructor(db, destroy_db);
db->in_transaction = NULL;
db->changes = NULL;
/* This must be outside a transaction, so catch it */
assert(!db->in_transaction);
db_prepare_for_changes(db);
if (db->config->setup_fn && !db->config->setup_fn(db))
fatal("Error calling DB setup: %s", db->error);
db_report_changes(db, NULL, 0);
return db;
}
/**
* db_get_version - Determine the current DB schema version
*
* Will attempt to determine the current schema version of the
* database @db by querying the `version` table. If the table does not
* exist it'll return schema version -1, so that migration 0 is
* applied, which should create the `version` table.
*/
static int db_get_version(struct db *db)
{
int res = -1;
struct db_stmt *stmt = db_prepare_v2(db, SQL("SELECT version FROM version LIMIT 1"));
/*
* Tentatively execute a query, but allow failures. Some databases
* like postgres will terminate the DB transaction if there is an
* error during the execution of a query, e.g., trying to access a
* table that doesn't exist yet, so we need to terminate and restart
* the DB transaction.
*/
if (!db_query_prepared(stmt)) {
db_commit_transaction(stmt->db);
db_begin_transaction(stmt->db);
tal_free(stmt);
return res;
}
if (db_step(stmt))
res = db_column_int(stmt, 0);
tal_free(stmt);
return res;
}
/**
* db_migrate - Apply all remaining migrations from the current version
*/
static void db_migrate(struct lightningd *ld, struct db *db)
{
/* Attempt to read the version from the database */
int current, orig, available;
struct db_stmt *stmt;
orig = current = db_get_version(db);
available = ARRAY_SIZE(dbmigrations) - 1;
if (current == -1)
log_info(db->log, "Creating database");
else if (available < current)
db_fatal("Refusing to migrate down from version %u to %u",
current, available);
else if (current != available)
log_info(db->log, "Updating database from version %u to %u",
current, available);
while (current < available) {
current++;
if (dbmigrations[current].sql) {
struct db_stmt *stmt =
db_prepare_v2(db, dbmigrations[current].sql);
db_exec_prepared_v2(stmt);
tal_free(stmt);
}
if (dbmigrations[current].func)
dbmigrations[current].func(ld, db);
}
/* Finally update the version number in the version table */
stmt = db_prepare_v2(db, SQL("UPDATE version SET version=?;"));
db_bind_int(stmt, 0, available);
db_exec_prepared_v2(stmt);
tal_free(stmt);
/* Annotate that we did upgrade, if any. */
if (current != orig) {
stmt = db_prepare_v2(
db, SQL("INSERT INTO db_upgrades VALUES (?, ?);"));
db_bind_int(stmt, 0, orig);
db_bind_text(stmt, 1, version());
db_exec_prepared_v2(stmt);
tal_free(stmt);
}
}
u32 db_data_version_get(struct db *db)
{
struct db_stmt *stmt;
u32 version;
stmt = db_prepare_v2(db, SQL("SELECT intval FROM vars WHERE name = 'data_version'"));
db_query_prepared(stmt);
db_step(stmt);
version = db_column_int(stmt, 0);