forked from byteball/ocore
-
Notifications
You must be signed in to change notification settings - Fork 0
/
byteball.sql
698 lines (596 loc) · 28.5 KB
/
byteball.sql
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
CREATE TABLE units (
unit CHAR(44) BINARY NOT NULL PRIMARY KEY, -- sha256 in base64
creation_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
version VARCHAR(10) NOT NULL DEFAULT '1.0',
alt VARCHAR(3) NOT NULL DEFAULT '1',
witness_list_unit CHAR(44) BINARY NULL,
last_ball_unit CHAR(44) BINARY NULL,
content_hash CHAR(44) NULL,
headers_commission INT NOT NULL,
payload_commission INT NOT NULL,
is_free TINYINT NOT NULL DEFAULT 1,
is_on_main_chain TINYINT NOT NULL DEFAULT 0,
main_chain_index INT NULL, -- when it first appears
latest_included_mc_index INT NULL, -- latest MC ball that is included in this ball (excluding itself)
level INT NULL,
witnessed_level INT NULL,
is_stable TINYINT NOT NULL DEFAULT 0,
sequence ENUM('good','temp-bad','final-bad') NOT NULL DEFAULT 'good',
best_parent_unit CHAR(44) BINARY NULL,
KEY byMainChain(is_on_main_chain),
KEY byMcIndex(main_chain_index),
KEY byLimci(latest_included_mc_index),
KEY byLevel(level),
KEY byFree(is_free),
KEY byStableMci(is_stable, main_chain_index),
KEY byDate(creation_date),
CONSTRAINT unitsByLastBallUnit FOREIGN KEY byLastBallUnit(last_ball_unit) REFERENCES units(unit),
FOREIGN KEY byBestParentUnit(best_parent_unit) REFERENCES units(unit),
CONSTRAINT unitsByWitnessListUnit FOREIGN KEY byWitnessList(witness_list_unit) REFERENCES units(unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE balls (
ball CHAR(44) BINARY NOT NULL PRIMARY KEY, -- sha256 in base64
creation_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
unit CHAR(44) BINARY NOT NULL UNIQUE, -- sha256 in base64
-- count_witnesses TINYINT NOT NULL DEFAULT 0,
count_paid_witnesses TINYINT NULL,
KEY byCountPaidWitnesses(count_paid_witnesses),
FOREIGN KEY byUnit(unit) REFERENCES units(unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE skiplist_units (
unit CHAR(44) BINARY NOT NULL,
skiplist_unit CHAR(44) BINARY NOT NULL, -- only for MC units with MCI divisible by 10: previous MC units divisible by 10
PRIMARY KEY (unit, skiplist_unit),
FOREIGN KEY byUnit(unit) REFERENCES units(unit),
FOREIGN KEY bySkiplistUnit(skiplist_unit) REFERENCES units(unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- must be sorted by parent_unit
CREATE TABLE parenthoods (
child_unit CHAR(44) BINARY NOT NULL,
parent_unit CHAR(44) BINARY NOT NULL,
PRIMARY KEY (parent_unit, child_unit),
CONSTRAINT parenthoodsByChild FOREIGN KEY byChildUnit(child_unit) REFERENCES units(unit),
CONSTRAINT parenthoodsByParent FOREIGN KEY byParentUnit(parent_unit) REFERENCES units(unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE definitions (
definition_chash CHAR(32) NOT NULL PRIMARY KEY,
definition TEXT NOT NULL,
has_references TINYINT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- current list of all known from-addresses
CREATE TABLE addresses (
address CHAR(32) NOT NULL PRIMARY KEY,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- must be sorted by address
CREATE TABLE unit_authors (
unit CHAR(44) BINARY NOT NULL,
address CHAR(32) NOT NULL,
definition_chash CHAR(32) NULL, -- only with 1st ball from this address, and with next ball after definition change
_mci INT NULL,
PRIMARY KEY (unit, address),
FOREIGN KEY byUnit(unit) REFERENCES units(unit),
CONSTRAINT unitAuthorsByAddress FOREIGN KEY byAddress(address) REFERENCES addresses(address),
KEY unitAuthorsIndexByAddressDefinitionChash (address, definition_chash),
KEY unitAuthorsIndexByAddressMci (address, _mci),
FOREIGN KEY byDefinition(definition_chash) REFERENCES definitions(definition_chash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE authentifiers (
unit CHAR(44) BINARY NOT NULL,
address CHAR(32) NOT NULL,
path VARCHAR(40) NOT NULL,
authentifier VARCHAR(4096) NOT NULL,
PRIMARY KEY (unit, address, path),
FOREIGN KEY byUnit(unit) REFERENCES units(unit),
CONSTRAINT authentifiersByAddress FOREIGN KEY byAddress(address) REFERENCES addresses(address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- must be sorted by address
CREATE TABLE unit_witnesses (
unit CHAR(44) BINARY NOT NULL,
address VARCHAR(32) NOT NULL,
PRIMARY KEY (unit, address),
KEY byAddress(address), -- no foreign key as the address might not be used yet
FOREIGN KEY byUnit(unit) REFERENCES units(unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE witness_list_hashes (
witness_list_unit CHAR(44) BINARY NOT NULL PRIMARY KEY,
witness_list_hash CHAR(44) NOT NULL UNIQUE,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY byUnit(witness_list_unit) REFERENCES units(unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- if this ball wins headers commission from at least one of the included balls, how it is distributed
-- required if more than one author
-- if one author, all commission goes to the author by default
CREATE TABLE earned_headers_commission_recipients (
unit CHAR(44) BINARY NOT NULL,
address VARCHAR(32) NOT NULL,
earned_headers_commission_share INT NOT NULL, -- percentage
PRIMARY KEY (unit, address),
KEY byAddress(address), -- no foreign key as the address might not be used yet
FOREIGN KEY byUnit(unit) REFERENCES units(unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE messages (
unit CHAR(44) BINARY NOT NULL,
message_index TINYINT NOT NULL,
app VARCHAR(30) NOT NULL,
payload_location ENUM('inline','uri','none') NOT NULL,
payload_hash VARCHAR(44) NOT NULL,
payload TEXT NULL,
payload_uri_hash VARCHAR(44) NULL,
payload_uri VARCHAR(500) NULL,
PRIMARY KEY (unit, message_index),
FOREIGN KEY byUnit(unit) REFERENCES units(unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- must be sorted by spend_proof
CREATE TABLE spend_proofs (
unit CHAR(44) BINARY NOT NULL,
message_index TINYINT NOT NULL,
spend_proof_index TINYINT NOT NULL,
spend_proof CHAR(44) NOT NULL,
address CHAR(32) NOT NULL,
PRIMARY KEY (unit, message_index, spend_proof_index),
UNIQUE KEY bySpendProof(spend_proof, unit),
FOREIGN KEY byUnit(unit) REFERENCES units(unit),
CONSTRAINT spendProofsByAddress FOREIGN KEY byAddress(address) REFERENCES addresses(address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- -------------------------
-- Specific message types
CREATE TABLE address_definition_changes (
unit CHAR(44) BINARY NOT NULL,
message_index TINYINT NOT NULL,
address CHAR(32) NOT NULL,
definition_chash VARCHAR(32) NOT NULL, -- might not be defined in definitions yet (almost always, it is not defined)
PRIMARY KEY (unit, message_index),
UNIQUE KEY byAddressUnit(address, unit),
FOREIGN KEY byUnit(unit) REFERENCES units(unit),
CONSTRAINT addressDefinitionChangesByAddress FOREIGN KEY byAddress(address) REFERENCES addresses(address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE data_feeds (
unit CHAR(44) BINARY NOT NULL,
message_index TINYINT NOT NULL,
feed_name VARCHAR(64) BINARY NOT NULL,
-- type ENUM('string', 'number') NOT NULL,
`value` VARCHAR(64) BINARY NULL,
`int_value` BIGINT NULL,
PRIMARY KEY (unit, feed_name),
KEY byNameStringValue(feed_name, `value`),
KEY byNameIntValue(feed_name, `int_value`),
FOREIGN KEY byUnit(unit) REFERENCES units(unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE polls (
unit CHAR(44) BINARY NOT NULL PRIMARY KEY,
message_index TINYINT NOT NULL,
question VARCHAR(4096) NOT NULL,
FOREIGN KEY byUnit(unit) REFERENCES units(unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE poll_choices (
unit CHAR(44) BINARY NOT NULL,
choice_index TINYINT NOT NULL,
choice VARCHAR(32) BINARY NOT NULL,
PRIMARY KEY (unit, choice_index),
UNIQUE KEY (unit, choice),
FOREIGN KEY byPoll(unit) REFERENCES polls(unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE votes (
unit CHAR(44) BINARY NOT NULL,
message_index TINYINT NOT NULL,
poll_unit CHAR(44) BINARY NOT NULL,
choice VARCHAR(32) BINARY NOT NULL,
PRIMARY KEY (unit, message_index),
UNIQUE KEY (unit, choice),
CONSTRAINT votesByChoice FOREIGN KEY byChoice(poll_unit, choice) REFERENCES poll_choices(unit, choice),
FOREIGN KEY byUnit(unit) REFERENCES units(unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE attestations (
unit CHAR(44) BINARY NOT NULL,
message_index TINYINT NOT NULL,
attestor_address VARCHAR(32) NOT NULL,
address VARCHAR(32) NOT NULL,
-- name VARCHAR(44) NOT NULL,
PRIMARY KEY (unit, message_index),
KEY byAddress(address),
CONSTRAINT attestationsByAttestorAddress FOREIGN KEY byAttestorAddress(attestor_address) REFERENCES addresses(address),
FOREIGN KEY byUnit(unit) REFERENCES units(unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE assets (
unit CHAR(44) BINARY NOT NULL PRIMARY KEY,
message_index TINYINT NOT NULL,
cap BIGINT NULL,
is_private TINYINT NOT NULL,
is_transferrable TINYINT NOT NULL,
auto_destroy TINYINT NOT NULL,
fixed_denominations TINYINT NOT NULL,
issued_by_definer_only TINYINT NOT NULL,
cosigned_by_definer TINYINT NOT NULL,
spender_attested TINYINT NOT NULL, -- must subsequently publish and update the list of trusted attestors
issue_condition TEXT NULL,
transfer_condition TEXT NULL,
FOREIGN KEY byUnit(unit) REFERENCES units(unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE asset_denominations (
asset CHAR(44) BINARY NOT NULL,
denomination INT NOT NULL,
count_coins BIGINT NULL,
max_issued_serial_number BIGINT NOT NULL DEFAULT 0,
PRIMARY KEY (asset, denomination),
FOREIGN KEY byAsset(asset) REFERENCES assets(unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE asset_attestors (
unit CHAR(44) BINARY NOT NULL,
message_index TINYINT NOT NULL,
asset CHAR(44) BINARY NOT NULL, -- in the initial attestor list: same as unit
attestor_address CHAR(32) NOT NULL,
PRIMARY KEY (unit, message_index),
UNIQUE KEY byAssetAttestorUnit(asset, attestor_address, unit),
FOREIGN KEY byUnit(unit) REFERENCES units(unit),
CONSTRAINT assetAttestorsByAsset FOREIGN KEY byAsset(asset) REFERENCES assets(unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- -------------------------
-- Payments
CREATE TABLE inputs (
unit CHAR(44) BINARY NOT NULL,
message_index TINYINT NOT NULL,
input_index TINYINT NOT NULL,
asset CHAR(44) BINARY NULL,
denomination INT NOT NULL DEFAULT 1,
is_unique TINYINT NULL DEFAULT 1,
type ENUM('transfer','headers_commission','witnessing','issue') NOT NULL,
src_unit CHAR(44) BINARY NULL, -- transfer
src_message_index TINYINT NULL, -- transfer
src_output_index TINYINT NULL, -- transfer
from_main_chain_index INT NULL, -- witnessing/hc
to_main_chain_index INT NULL, -- witnessing/hc
serial_number BIGINT NULL, -- issue
amount BIGINT NULL, -- issue
address CHAR(32) NOT NULL,
PRIMARY KEY (unit, message_index, input_index),
UNIQUE KEY bySrcOutput(src_unit, src_message_index, src_output_index, is_unique), -- UNIQUE guarantees there'll be no double spend for type=transfer
UNIQUE KEY byIndexAddress(type, from_main_chain_index, address, is_unique), -- UNIQUE guarantees there'll be no double spend for type=hc/witnessing
UNIQUE KEY byAssetDenominationSerialAddress(asset, denomination, serial_number, address, is_unique), -- UNIQUE guarantees there'll be no double issue
KEY byAssetType(asset, type),
KEY byAddressTypeToMci(address, type, to_main_chain_index),
FOREIGN KEY byUnit(unit) REFERENCES units(unit),
CONSTRAINT inputsBySrcUnit FOREIGN KEY bySrcUnit(src_unit) REFERENCES units(unit),
CONSTRAINT inputsByAddress FOREIGN KEY byAddress(address) REFERENCES addresses(address),
CONSTRAINT inputsByAsset FOREIGN KEY byAsset(asset) REFERENCES assets(unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE outputs (
output_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
unit CHAR(44) BINARY NOT NULL,
message_index TINYINT NOT NULL,
output_index TINYINT NOT NULL,
asset CHAR(44) BINARY NULL,
denomination INT NOT NULL DEFAULT 1,
address VARCHAR(32) NULL, -- NULL if hidden by output_hash
amount BIGINT NOT NULL,
blinding CHAR(16) NULL,
output_hash CHAR(44) NULL,
is_serial TINYINT NULL, -- NULL if not stable yet
is_spent TINYINT NOT NULL DEFAULT 0,
UNIQUE KEY (unit, message_index, output_index),
KEY byAddressSpent(address, is_spent),
KEY bySerial(is_serial),
FOREIGN KEY byUnit(unit) REFERENCES units(unit),
CONSTRAINT outputsByAsset FOREIGN KEY byAsset(asset) REFERENCES assets(unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- ------------
-- Commissions
-- updated immediately after main chain is updated
CREATE TABLE headers_commission_contributions (
unit CHAR(44) BINARY NOT NULL, -- parent unit that pays commission
address CHAR(32) NOT NULL, -- address of the commission receiver: author of child unit or address named in earned_headers_commission_recipients
amount BIGINT NOT NULL,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (unit, address),
KEY byAddress(address),
FOREIGN KEY byUnit(unit) REFERENCES units(unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE headers_commission_outputs (
main_chain_index INT NOT NULL,
address CHAR(32) NOT NULL, -- address of the commission receiver
amount BIGINT NOT NULL,
is_spent TINYINT NOT NULL DEFAULT 0,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (main_chain_index, address),
UNIQUE (address, main_chain_index),
UNIQUE (address, is_spent, main_chain_index)
-- KEY byAddressSpent(address, is_spent)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE paid_witness_events (
unit CHAR(44) BINARY NOT NULL,
address CHAR(32) NOT NULL, -- witness address
-- witnessed_in_ball CHAR(44) NOT NULL, -- if expired, MC ball next after expiry. Or NULL?
delay TINYINT NULL, -- NULL if expired
PRIMARY KEY (unit, address),
FOREIGN KEY byUnit(unit) REFERENCES units(unit),
FOREIGN KEY byWitnessAddress(address) REFERENCES addresses(address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE witnessing_outputs (
main_chain_index INT NOT NULL,
address CHAR(32) NOT NULL,
amount BIGINT NOT NULL,
is_spent TINYINT NOT NULL DEFAULT 0,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (main_chain_index, address),
UNIQUE (address, main_chain_index),
UNIQUE (address, is_spent, main_chain_index),
-- KEY byWitnessAddressSpent(address, is_spent),
FOREIGN KEY byWitnessAddress(address) REFERENCES addresses(address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- ---------------------------------------
-- Networking
CREATE TABLE dependencies (
unit CHAR(44) BINARY NOT NULL,
depends_on_unit CHAR(44) BINARY NULL,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY (depends_on_unit, unit),
KEY byUnit(unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE unhandled_joints (
unit CHAR(44) BINARY NOT NULL PRIMARY KEY,
peer VARCHAR(100) NOT NULL,
json LONGTEXT NOT NULL,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE archived_joints (
unit CHAR(44) BINARY NOT NULL PRIMARY KEY,
reason ENUM('uncovered', 'voided') NOT NULL,
-- is_retrievable TINYINT NOT NULL,
json LONGTEXT NOT NULL,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE known_bad_joints (
joint CHAR(44) BINARY NULL UNIQUE,
unit CHAR(44) BINARY NULL UNIQUE,
json LONGTEXT NOT NULL,
error TEXT NOT NULL,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE joints (
unit CHAR(44) BINARY NOT NULL PRIMARY KEY,
json LONGTEXT NOT NULL,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE unhandled_private_payments (
unit CHAR(44) BINARY NOT NULL,
message_index TINYINT NOT NULL,
output_index TINYINT NOT NULL,
json LONGTEXT NOT NULL,
peer VARCHAR(100) NOT NULL,
linked TINYINT NOT NULL DEFAULT 0,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (unit, message_index, output_index)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- ------------------
-- Catching up
CREATE TABLE hash_tree_balls (
ball_index INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -- in increasing level order
ball CHAR(44) BINARY NOT NULL UNIQUE,
unit CHAR(44) BINARY NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE catchup_chain_balls (
member_index INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -- in increasing level order
ball CHAR(44) BINARY NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- ------------------------
-- Peers
CREATE TABLE peer_hosts (
peer_host VARCHAR(100) NOT NULL PRIMARY KEY, -- domain or IP
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
count_new_good_joints INT NOT NULL DEFAULT 0,
count_invalid_joints INT NOT NULL DEFAULT 0,
count_nonserial_joints INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE peers (
peer VARCHAR(100) NOT NULL PRIMARY KEY, -- wss:// address
peer_host VARCHAR(100) NOT NULL, -- domain or IP
learnt_from_peer_host VARCHAR(100) NULL, -- domain or IP
is_self TINYINT NOT NULL DEFAULT 0,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY byLearntFromPeerHost(learnt_from_peer_host) REFERENCES peer_hosts(peer_host),
FOREIGN KEY byPeerHost(peer_host) REFERENCES peer_hosts(peer_host)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- INSERT INTO peer_hosts SET peer_host='127.0.0.1';
-- INSERT INTO peers SET peer_host='127.0.0.1', peer='ws://127.0.0.1:8081';
CREATE TABLE peer_events (
peer_host VARCHAR(100) NOT NULL, -- domain or IP
event_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
event ENUM('new_good', 'invalid', 'nonserial', 'known_good', 'known_bad') NOT NULL,
FOREIGN KEY byPeerHost(peer_host) REFERENCES peer_hosts(peer_host)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- self advertised urls
-- only inbound peers can advertise their urls
CREATE TABLE peer_host_urls (
peer_host VARCHAR(100) NOT NULL, -- IP
url VARCHAR(100) NOT NULL,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
is_active TINYINT NULL DEFAULT 1,
revocation_date TIMESTAMP NULL,
UNIQUE KEY byHostActive(peer_host, is_active),
FOREIGN KEY byPeerHost(peer_host) REFERENCES peer_hosts(peer_host)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- -----------------------
-- wallet tables
-- wallets composed of BIP44 keys, the keys live on different devices, each device knows each other's extended public key
CREATE TABLE wallets (
wallet CHAR(44) NOT NULL PRIMARY KEY,
account INT NOT NULL,
definition_template TEXT NOT NULL,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
full_approval_date TIMESTAMP NULL, -- when received xpubkeys from all members
ready_date TIMESTAMP NULL -- when all members notified me that they saw the wallet fully approved
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- BIP44 addresses. Coin type and account are fixed and stored in credentials in localstorage.
-- derivation path is m/44'/0'/account'/is_change/address_index
CREATE TABLE my_addresses (
address CHAR(32) NOT NULL PRIMARY KEY,
wallet CHAR(44) NOT NULL,
is_change TINYINT NOT NULL,
address_index INT NOT NULL,
definition TEXT NOT NULL,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY byWalletPath(wallet, is_change, address_index),
FOREIGN KEY byWallet(wallet) REFERENCES wallets(wallet)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE my_witnesses (
address VARCHAR(32) NOT NULL PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- --------------------
-- hub tables
CREATE TABLE devices (
device_address CHAR(33) NOT NULL PRIMARY KEY,
pubkey CHAR(44) NOT NULL,
temp_pubkey_package TEXT NULL, -- temporary pubkey signed by the permanent pubkey
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE device_messages (
message_hash CHAR(44) NOT NULL PRIMARY KEY,
device_address CHAR(33) NOT NULL, -- the device this message is addressed to
message LONGTEXT NOT NULL,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY byAddress(device_address) REFERENCES devices(device_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- --------------------
-- hub client tables
CREATE TABLE correspondent_devices (
device_address CHAR(33) NOT NULL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
pubkey CHAR(44) NOT NULL,
hub VARCHAR(100) NOT NULL, -- domain name of the hub this address is subscribed to
is_confirmed TINYINT NOT NULL DEFAULT 0,
is_indirect TINYINT NOT NULL DEFAULT 0,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE pairing_secrets (
pairing_secret VARCHAR(40) NOT NULL PRIMARY KEY,
is_permanent TINYINT NOT NULL DEFAULT 0,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
expiry_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP -- DEFAULT for newer mysql versions (never used)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE extended_pubkeys (
wallet CHAR(44) NOT NULL, -- no FK because xpubkey may arrive earlier than the wallet is approved by the user and written to the db
extended_pubkey CHAR(112) NULL, -- base58 encoded, see bip32, NULL while pending
device_address CHAR(33) NOT NULL,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
approval_date TIMESTAMP NULL,
member_ready_date TIMESTAMP NULL, -- when this member notified us that he has collected all member xpubkeys
PRIMARY KEY (wallet, device_address)
-- own address is not present in correspondents
-- FOREIGN KEY byDeviceAddress(device_address) REFERENCES correspondent_devices(device_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE wallet_signing_paths (
wallet CHAR(44) NOT NULL, -- no FK because xpubkey may arrive earlier than the wallet is approved by the user and written to the db
signing_path VARCHAR(255) NULL, -- NULL if xpubkey arrived earlier than the wallet was approved by the user
device_address CHAR(33) NOT NULL,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY byWalletSigningPath(wallet, signing_path),
FOREIGN KEY byWallet(wallet) REFERENCES wallets(wallet)
-- own address is not present in correspondents
-- FOREIGN KEY byDeviceAddress(device_address) REFERENCES correspondent_devices(device_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- addresses composed of several other addresses (such as ["and", [["address", "ADDRESS1"], ["address", "ADDRESS2"]]]),
-- member addresses live on different devices, member addresses themselves may be composed of several keys
CREATE TABLE shared_addresses (
shared_address CHAR(32) NOT NULL PRIMARY KEY,
definition TEXT NOT NULL,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE pending_shared_addresses (
definition_template_chash CHAR(32) NOT NULL PRIMARY KEY,
definition_template TEXT NOT NULL,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE pending_shared_address_signing_paths (
definition_template_chash CHAR(32) NOT NULL,
device_address CHAR(33) NOT NULL,
signing_path VARCHAR(255) NOT NULL, -- path from root to member address
address CHAR(32) NULL, -- member address
device_addresses_by_relative_signing_paths TEXT NULL, -- json
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
approval_date TIMESTAMP NULL,
PRIMARY KEY (definition_template_chash, signing_path),
-- own address is not present in correspondents
-- FOREIGN KEY byDeviceAddress(device_address) REFERENCES correspondent_devices(device_address),
FOREIGN KEY byTemplate(definition_template_chash) REFERENCES pending_shared_addresses(definition_template_chash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE shared_address_signing_paths (
shared_address CHAR(32) NOT NULL,
signing_path VARCHAR(255) NULL, -- full path to signing key which is a member of the member address
address CHAR(32) NOT NULL, -- member address
member_signing_path VARCHAR(255) NULL, -- path to signing key from root of the member address
device_address CHAR(33) NOT NULL, -- where this signing key lives or is reachable through
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY bySharedAddressSigningPath(shared_address, signing_path),
FOREIGN KEY bySharedAddress(shared_address) REFERENCES shared_addresses(shared_address)
-- own address is not present in correspondents
-- FOREIGN KEY byDeviceAddress(device_address) REFERENCES correspondent_devices(device_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE outbox (
message_hash CHAR(44) NOT NULL PRIMARY KEY,
`to` CHAR(33) NOT NULL, -- the device this message is addressed to, no FK because of pairing case
message LONGTEXT NOT NULL,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_error TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- light clients
CREATE TABLE watched_light_addresses (
peer VARCHAR(100) NOT NULL,
address CHAR(32) NOT NULL,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (peer, address),
KEY byAddress(address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `units` ADD INDEX `bySequence` (`sequence`);
DROP TABLE IF EXISTS paid_witness_events;
CREATE TABLE IF NOT EXISTS push_registrations (
registrationId VARCHAR(200),
device_address CHAR(33) NOT NULL,
PRIMARY KEY (device_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE chat_messages (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
correspondent_address CHAR(33) NOT NULL, -- the device this message is came from
message LONGTEXT NOT NULL,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
is_incoming TINYINT NOT NULL,
type CHAR(15) NOT NULL DEFAULT 'text',
FOREIGN KEY byAddress(correspondent_address) REFERENCES correspondent_devices(device_address) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE INDEX chatMessagesIndexByDeviceAddress ON chat_messages(correspondent_address, id);
ALTER TABLE correspondent_devices ADD COLUMN my_record_pref INTEGER DEFAULT 1;
ALTER TABLE correspondent_devices ADD COLUMN peer_record_pref INTEGER DEFAULT 1;
CREATE TABLE watched_light_units (
peer VARCHAR(100) NOT NULL,
unit CHAR(44) NOT NULL,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (peer, unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE INDEX wlabyUnit ON watched_light_units(unit);
CREATE TABLE bots (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
rank INTEGER NOT NULL DEFAULT 0,
name VARCHAR(100) NOT NULL UNIQUE,
pairing_code VARCHAR(200) NOT NULL,
description LONGTEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE asset_metadata (
asset CHAR(44) BINARY NOT NULL PRIMARY KEY,
metadata_unit CHAR(44) BINARY NOT NULL,
registry_address CHAR(32) NULL, -- filled only on the hub
suffix VARCHAR(20) NULL, -- added only if the same name is registered by different registries for different assets, equal to registry name
name VARCHAR(20) NULL,
decimals TINYINT NULL,
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE byNameRegistry(name, registry_address),
FOREIGN KEY byAsset(asset) REFERENCES assets(unit),
FOREIGN KEY byMetadataUnit(metadata_unit) REFERENCES units(unit)
-- FOREIGN KEY byRegistryAddress(registry_address) REFERENCES addresses(address) -- addresses is not always filled on light
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE sent_mnemonics (
unit CHAR(44) NOT NULL,
address CHAR(32) NOT NULL,
mnemonic VARCHAR(107) NOT NULL,
textAddress VARCHAR(120) NOT NULL,
FOREIGN KEY (unit) REFERENCES units(unit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE INDEX sentByAddress ON sent_mnemonics(address);