-
Notifications
You must be signed in to change notification settings - Fork 31
/
V9__Schema.sql
320 lines (285 loc) · 11.2 KB
/
V9__Schema.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
CREATE TABLE node_headers
(
id VARCHAR(64) PRIMARY KEY,
parent_id VARCHAR(64) NOT NULL,
version SMALLINT NOT NULL,
height INTEGER NOT NULL,
n_bits BIGINT NOT NULL,
difficulty NUMERIC NOT NULL,
timestamp BIGINT NOT NULL,
state_root VARCHAR(66) NOT NULL,
ad_proofs_root VARCHAR(64) NOT NULL,
transactions_root VARCHAR(64) NOT NULL,
extension_hash VARCHAR(64) NOT NULL,
miner_pk VARCHAR NOT NULL,
w VARCHAR NOT NULL,
n VARCHAR NOT NULL,
d VARCHAR NOT NULL,
votes VARCHAR NOT NULL,
main_chain BOOLEAN NOT NULL
);
CREATE INDEX "node_headers__parent_id" ON node_headers (parent_id);
CREATE INDEX "node_headers__height" ON node_headers (height);
CREATE INDEX "node_headers__ts" ON node_headers (timestamp);
CREATE INDEX "node_headers__d" ON node_headers (d);
CREATE TABLE node_extensions
(
header_id VARCHAR(64) PRIMARY KEY REFERENCES node_headers (id),
digest VARCHAR(64) NOT NULL,
fields JSON NOT NULL
);
CREATE TABLE node_ad_proofs
(
header_id VARCHAR(64) PRIMARY KEY REFERENCES node_headers (id),
proof_bytes VARCHAR NOT NULL,
digest VARCHAR NOT NULL
);
/* Block stats
*/
CREATE TABLE blocks_info
(
header_id VARCHAR(64) PRIMARY KEY REFERENCES node_headers (id),
timestamp BIGINT NOT NULL,
height INTEGER NOT NULL,
difficulty BIGINT NOT NULL,
block_size INTEGER NOT NULL,
block_coins BIGINT NOT NULL,
block_mining_time BIGINT,
txs_count INTEGER NOT NULL,
txs_size INTEGER NOT NULL,
miner_address VARCHAR NOT NULL,
miner_reward BIGINT NOT NULL,
miner_revenue BIGINT NOT NULL,
block_fee BIGINT NOT NULL,
block_chain_total_size BIGINT NOT NULL,
total_txs_count BIGINT NOT NULL,
total_coins_issued BIGINT NOT NULL,
total_mining_time BIGINT NOT NULL,
total_fees BIGINT NOT NULL,
total_miners_reward BIGINT NOT NULL,
total_coins_in_txs BIGINT NOT NULL,
max_tx_gix BIGINT NOT NULL,
max_box_gix BIGINT NOT NULL,
main_chain BOOLEAN NOT NULL
);
/* Stats table indexes. By height and ts.
*/
CREATE INDEX "blocks_info__height" ON blocks_info (height);
CREATE INDEX "blocks_info__ts" ON blocks_info (timestamp);
CREATE TABLE node_transactions
(
id VARCHAR(64) NOT NULL,
header_id VARCHAR(64) REFERENCES node_headers (id),
inclusion_height INTEGER NOT NULL,
coinbase BOOLEAN NOT NULL,
timestamp BIGINT NOT NULL,
size INTEGER NOT NULL,
index INTEGER NOT NULL,
global_index BIGINT NOT NULL,
main_chain BOOLEAN NOT NULL,
PRIMARY KEY (id, header_id)
);
CREATE INDEX "node_transactions__header_id" ON node_transactions (header_id);
CREATE INDEX "node_transactions__timestamp" ON node_transactions (timestamp);
CREATE INDEX "node_transactions__inclusion_height" ON node_transactions (inclusion_height);
/* Table that represents inputs in ergo transactions.
* Has tx_id field that point to the tx where this input was spent.
*/
CREATE TABLE node_inputs
(
box_id VARCHAR(64) NOT NULL,
tx_id VARCHAR(64) NOT NULL,
header_id VARCHAR(64) NOT NULL,
proof_bytes VARCHAR,
extension JSON NOT NULL,
index INTEGER NOT NULL,
main_chain BOOLEAN NOT NULL,
PRIMARY KEY (box_id, header_id)
);
CREATE INDEX "node_inputs__tx_id" ON node_inputs (tx_id);
CREATE INDEX "node_inputs__box_id" ON node_inputs (box_id);
CREATE INDEX "node_inputs__header_id" ON node_inputs (header_id);
CREATE TABLE node_data_inputs
(
box_id VARCHAR(64) NOT NULL,
tx_id VARCHAR(64) NOT NULL,
header_id VARCHAR(64) NOT NULL,
index INTEGER NOT NULL,
main_chain BOOLEAN NOT NULL,
PRIMARY KEY (box_id, tx_id, header_id)
);
CREATE INDEX "node_data_inputs__tx_id" ON node_data_inputs (tx_id);
CREATE INDEX "node_data_inputs__box_id" ON node_data_inputs (box_id);
CREATE INDEX "node_data_inputs__header_id" ON node_data_inputs (header_id);
/* Table that represents outputs in ergo transactions.
* Has tx_id field pointing to the tx which created this output.
*/
CREATE TABLE node_outputs
(
box_id VARCHAR(64) NOT NULL,
tx_id VARCHAR(64) NOT NULL,
header_id VARCHAR(64) NOT NULL,
value BIGINT NOT NULL,
creation_height INTEGER NOT NULL,
settlement_height INTEGER NOT NULL,
index INTEGER NOT NULL,
global_index BIGINT NOT NULL,
ergo_tree VARCHAR NOT NULL,
ergo_tree_template_hash VARCHAR(64) NOT NULL,
address VARCHAR NOT NULL,
additional_registers JSON NOT NULL,
timestamp BIGINT NOT NULL,
main_chain BOOLEAN NOT NULL,
PRIMARY KEY (box_id, header_id)
);
CREATE INDEX "node_outputs__box_id" ON node_outputs (box_id);
CREATE INDEX "node_outputs__tx_id" ON node_outputs (tx_id);
CREATE INDEX "node_outputs__header_id" ON node_outputs (header_id);
CREATE INDEX "node_outputs__ergo_tree" ON node_outputs using hash (ergo_tree)
WHERE NOT ergo_tree = '1005040004000e36100204a00b08cd0279be667ef9dcbbac55a06295ce870b07029bfcdb2dce28d959f2815b16f81798ea02d192a39a8cc7a701730073011001020402d19683030193a38cc7b2a57300000193c2b2a57301007473027303830108cdeeac93b1a57304';
CREATE INDEX "node_outputs__ergo_tree_template_hash" ON node_outputs (ergo_tree_template_hash)
WHERE NOT ergo_tree_template_hash = 'd19683030193a38cc7b2a57300000193c2b2a57301007473027303830108cdeeac93b1a57304';
CREATE INDEX "node_outputs__timestamp" ON node_outputs (timestamp);
CREATE TABLE node_assets
(
token_id VARCHAR(64) NOT NULL,
box_id VARCHAR(64) NOT NULL,
header_id VARCHAR(64) NOT NULL,
index INTEGER NOT NULL,
value BIGINT NOT NULL,
PRIMARY KEY (index, token_id, box_id, header_id)
);
CREATE INDEX "node_assets__box_id" ON node_assets (box_id);
CREATE INDEX "node_assets__token_id" ON node_assets (token_id);
CREATE INDEX "node_assets__header_id" ON node_assets (header_id);
CREATE TABLE box_registers
(
id VARCHAR(2) NOT NULL,
box_id VARCHAR(64) NOT NULL,
value_type VARCHAR(128) NOT NULL,
serialized_value VARCHAR NOT NULL,
rendered_value VARCHAR NOT NULL,
PRIMARY KEY (id, box_id)
);
CREATE INDEX "box_registers__id" ON box_registers (id);
CREATE INDEX "box_registers__box_id" ON box_registers (box_id);
/* Uncomment this is you want to search by box registers */
/* CREATE INDEX "box_registers__rendered_value" ON box_registers (rendered_value); */
CREATE TABLE script_constants
(
index INTEGER NOT NULL,
box_id VARCHAR(64) NOT NULL,
value_type VARCHAR(128) NOT NULL,
serialized_value VARCHAR NOT NULL,
rendered_value VARCHAR NOT NULL,
PRIMARY KEY (index, box_id)
);
CREATE INDEX "script_constants__box_id" ON script_constants (box_id);
/* Uncomment this is you want to search by constants */
/* CREATE INDEX "script_constants__rendered_value" ON script_constants using hash (rendered_value); */
/* Unconfirmed transactions.
*/
CREATE TABLE node_u_transactions
(
id VARCHAR(64) PRIMARY KEY,
creation_timestamp BIGINT NOT NULL,
size INTEGER NOT NULL
);
/* Inputs containing in unconfirmed transactions.
*/
CREATE TABLE node_u_inputs
(
box_id VARCHAR(64) NOT NULL,
tx_id VARCHAR(64) NOT NULL REFERENCES node_u_transactions (id) ON DELETE CASCADE,
index INTEGER NOT NULL,
proof_bytes VARCHAR,
extension JSON NOT NULL,
PRIMARY KEY (box_id, tx_id)
);
CREATE INDEX "node_u_inputs__tx_id" ON node_u_inputs (tx_id);
CREATE INDEX "node_u_inputs__box_id" ON node_u_inputs (box_id);
/* Data inputs containing in unconfirmed transactions.
*/
CREATE TABLE node_u_data_inputs
(
box_id VARCHAR(64) NOT NULL,
tx_id VARCHAR(64) NOT NULL REFERENCES node_u_transactions (id) ON DELETE CASCADE,
index INTEGER NOT NULL,
PRIMARY KEY (box_id, tx_id)
);
CREATE INDEX "node_u_data_inputs__tx_id" ON node_u_data_inputs (tx_id);
CREATE INDEX "node_u_data_inputs__box_id" ON node_u_data_inputs (box_id);
/* Outputs containing in unconfirmed transactions.
*/
CREATE TABLE node_u_outputs
(
box_id VARCHAR(64) PRIMARY KEY,
tx_id VARCHAR(64) NOT NULL REFERENCES node_u_transactions (id) ON DELETE CASCADE,
value BIGINT NOT NULL,
creation_height INTEGER NOT NULL,
index INTEGER NOT NULL,
ergo_tree VARCHAR NOT NULL,
ergo_tree_template_hash VARCHAR(64) NOT NULL,
address VARCHAR,
additional_registers JSON NOT NULL
);
CREATE INDEX "node_u_outputs__box_id" ON node_u_outputs (box_id);
CREATE INDEX "node_u_outputs__tx_id" ON node_u_outputs (tx_id);
CREATE INDEX "node_u_outputs__ergo_tree" ON node_u_outputs (ergo_tree);
CREATE INDEX "node_u_outputs__ergo_tree_template_hash" ON node_u_outputs (ergo_tree_template_hash);
/* Inputs containing in unconfirmed outputs.
*/
CREATE TABLE node_u_assets
(
token_id VARCHAR(64) NOT NULL,
box_id VARCHAR(64) NOT NULL REFERENCES node_u_outputs (box_id) ON DELETE CASCADE,
index INTEGER NOT NULL,
value BIGINT NOT NULL,
PRIMARY KEY (index, token_id, box_id)
);
CREATE INDEX "node_u_assets__box_id" ON node_u_assets (box_id);
/* Verbose names for known miners.
*/
CREATE TABLE known_miners
(
miner_address VARCHAR PRIMARY KEY,
miner_name VARCHAR NOT NULL
);
CREATE TABLE tokens
(
token_id VARCHAR(64) PRIMARY KEY,
box_id VARCHAR(64) NOT NULL,
emission_amount BIGINT NOT NULL,
name VARCHAR,
description VARCHAR,
type VARCHAR,
decimals INTEGER
);
CREATE INDEX "tokens__box_id" ON tokens (box_id);
CREATE TABLE genuine_tokens
(
token_id VARCHAR(64) PRIMARY KEY,
token_name VARCHAR NOT NULL,
unique_name BOOLEAN NOT NULL,
issuer VARCHAR
);
CREATE TABLE blocked_tokens
(
token_id VARCHAR(64) PRIMARY KEY,
token_name VARCHAR NOT NULL
);
CREATE TABLE epochs_parameters
(
id INTEGER PRIMARY KEY,
height INTEGER NOT NULL,
storage_fee_factor INTEGER NOT NULL,
min_value_per_byte INTEGER NOT NULL,
max_block_size INTEGER NOT NULL,
max_block_cost INTEGER NOT NULL,
block_version SMALLINT NOT NULL,
token_access_cost INTEGER NOT NULL,
input_cost INTEGER NOT NULL,
data_input_cost INTEGER NOT NULL,
output_cost INTEGER NOT NULL
);
CREATE INDEX "epochs_parameters_height" ON epochs_parameters (height);