hermez-node/db/migrations/0001.sql
db/migrations/0001.sqlRaw
1-- +migrate Up
2
3-- NOTE: We use "DECIMAL(78,0)" to encode go *big.Int types. All the *big.Int
4-- that we deal with represent a value in the SNARK field, which is an integer
5-- of 256 bits. `log(2**256, 10) = 77.06`: that is, a 256 bit number can have
6-- at most 78 digits, so we use this value to specify the precision in the
7-- PostgreSQL DECIMAL guaranteeing that we will never lose precision.
8
9-- History
10CREATE TABLE block (
11 eth_block_num BIGINT PRIMARY KEY,
12 timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL,
13 hash BYTEA NOT NULL
14);
15
16CREATE TABLE coordinator (
17 item_id SERIAL PRIMARY KEY,
18 bidder_addr BYTEA NOT NULL,
19 forger_addr BYTEA NOT NULL,
20 eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
21 url BYTEA NOT NULL
22);
23
24CREATE TABLE batch (
25 item_id SERIAL PRIMARY KEY,
26 batch_num BIGINT UNIQUE NOT NULL,
27 eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
28 forger_addr BYTEA NOT NULL, -- fake foreign key for coordinator
29 fees_collected BYTEA NOT NULL,
30 fee_idxs_coordinator BYTEA NOT NULL,
31 state_root DECIMAL(78,0) NOT NULL,
32 num_accounts BIGINT NOT NULL,
33 last_idx BIGINT NOT NULL,
34 exit_root DECIMAL(78,0) NOT NULL,
35 forge_l1_txs_num BIGINT,
36 slot_num BIGINT NOT NULL,
37 total_fees_usd NUMERIC
38);
39
40CREATE TABLE bid (
41 item_id SERIAL PRIMARY KEY,
42 slot_num BIGINT NOT NULL,
43 bid_value DECIMAL(78,0) NOT NULL,
44 eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
45 bidder_addr BYTEA NOT NULL -- fake foreign key for coordinator
46);
47
48CREATE TABLE token (
49 item_id SERIAL PRIMARY KEY,
50 token_id INT UNIQUE NOT NULL,
51 eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
52 eth_addr BYTEA UNIQUE NOT NULL,
53 name VARCHAR(20) NOT NULL,
54 symbol VARCHAR(10) NOT NULL,
55 decimals INT NOT NULL,
56 usd NUMERIC, -- value of a normalized token (1 token = 10^decimals units)
57 usd_update TIMESTAMP WITHOUT TIME ZONE
58);
59
60-- Add ETH as TokenID 0
61INSERT INTO block (
62 eth_block_num,
63 timestamp,
64 hash
65) VALUES (
66 0,
67 '2015-07-30 03:26:13',
68 '\xd4e56740f876aef8c010b86a40d5f56745a118d0906a34e69aec8c0db1cb8fa3'
69); -- info from https://etherscan.io/block/0
70
71INSERT INTO token (
72 token_id,
73 eth_block_num,
74 eth_addr,
75 name,
76 symbol,
77 decimals
78) VALUES (
79 0,
80 0,
81 '\x0000000000000000000000000000000000000000',
82 'Ether',
83 'ETH',
84 18
85);
86
87
88-- +migrate StatementBegin
89CREATE FUNCTION hez_idx(BIGINT, VARCHAR)
90 RETURNS VARCHAR
91AS
92$BODY$
93BEGIN
94 RETURN 'hez:' || $2 || ':' || $1;
95END;
96$BODY$
97LANGUAGE plpgsql;
98-- +migrate StatementEnd
99
100CREATE TABLE account (
101 item_id SERIAL,
102 idx BIGINT PRIMARY KEY,
103 token_id INT NOT NULL REFERENCES token (token_id) ON DELETE CASCADE,
104 batch_num BIGINT NOT NULL REFERENCES batch (batch_num) ON DELETE CASCADE,
105 bjj BYTEA NOT NULL,
106 eth_addr BYTEA NOT NULL
107);
108
109CREATE TABLE account_update (
110 item_id SERIAL,
111 eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
112 batch_num BIGINT NOT NULL REFERENCES batch (batch_num) ON DELETE CASCADE,
113 idx BIGINT NOT NULL REFERENCES account (idx) ON DELETE CASCADE,
114 nonce BIGINT NOT NULL,
115 balance DECIMAL(78,0) NOT NULL
116);
117
118CREATE TABLE exit_tree (
119 item_id SERIAL PRIMARY KEY,
120 batch_num BIGINT REFERENCES batch (batch_num) ON DELETE CASCADE,
121 account_idx BIGINT REFERENCES account (idx) ON DELETE CASCADE,
122 merkle_proof BYTEA NOT NULL,
123 balance DECIMAL(78,0) NOT NULL,
124 instant_withdrawn BIGINT REFERENCES block (eth_block_num) ON DELETE SET NULL,
125 delayed_withdraw_request BIGINT REFERENCES block (eth_block_num) ON DELETE SET NULL,
126 owner BYTEA,
127 token BYTEA,
128 delayed_withdrawn BIGINT REFERENCES block (eth_block_num) ON DELETE SET NULL
129);
130
131-- +migrate StatementBegin
132CREATE FUNCTION set_token_usd_update()
133 RETURNS TRIGGER
134AS
135$BODY$
136BEGIN
137 IF NEW."usd" IS NOT NULL AND NEW."usd_update" IS NULL THEN
138 NEW."usd_update" = timezone('utc', now());
139 END IF;
140 RETURN NEW;
141END;
142$BODY$
143LANGUAGE plpgsql;
144-- +migrate StatementEnd
145CREATE TRIGGER trigger_token_usd_update BEFORE UPDATE OR INSERT ON token
146FOR EACH ROW EXECUTE PROCEDURE set_token_usd_update();
147
148CREATE SEQUENCE tx_item_id;
149
150-- important note about "amount_success" and "deposit_amount_success" (only relevant to L1 user txs):
151-- The behaviour should be:
152-- When tx is not forged: amount_success = false, deposit_amount_success = false
153-- When tx is forged:
154-- amount_success = false if the "effective amount" is 0, else true
155-- deposit_amount_success = false if the "effective deposit amount" is 0, else true
156--
157-- However, in order to reduce the amount of updates, by default amount_success and deposit_amount_success will be set to true (when tx is unforged)
158-- whne they should be false. This can be worked around at a query level by checking if "batch_num IS NULL" (which indicates that the tx is unforged).
159CREATE TABLE tx (
160 -- Generic TX
161 item_id INTEGER PRIMARY KEY DEFAULT nextval('tx_item_id'),
162 is_l1 BOOLEAN NOT NULL,
163 id BYTEA,
164 type VARCHAR(40) NOT NULL,
165 position INT NOT NULL,
166 from_idx BIGINT,
167 effective_from_idx BIGINT REFERENCES account (idx) ON DELETE SET NULL,
168 from_eth_addr BYTEA,
169 from_bjj BYTEA,
170 to_idx BIGINT NOT NULL,
171 to_eth_addr BYTEA,
172 to_bjj BYTEA,
173 amount DECIMAL(78,0) NOT NULL,
174 amount_success BOOLEAN NOT NULL DEFAULT true,
175 amount_f NUMERIC NOT NULL,
176 token_id INT NOT NULL REFERENCES token (token_id),
177 amount_usd NUMERIC, -- Value of the amount in USD at the moment the tx was inserted in the DB
178 batch_num BIGINT REFERENCES batch (batch_num) ON DELETE SET NULL, -- Can be NULL in the case of L1 txs that are on the queue but not forged yet.
179 eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
180 -- L1
181 to_forge_l1_txs_num BIGINT,
182 user_origin BOOLEAN,
183 deposit_amount DECIMAL(78,0),
184 deposit_amount_success BOOLEAN NOT NULL DEFAULT true,
185 deposit_amount_f NUMERIC,
186 deposit_amount_usd NUMERIC,
187 -- L2
188 fee INT,
189 fee_usd NUMERIC,
190 nonce BIGINT
191);
192
193-- +migrate StatementBegin
194CREATE FUNCTION fee_percentage(NUMERIC)
195 RETURNS NUMERIC
196AS
197$BODY$
198DECLARE perc NUMERIC;
199BEGIN
200 SELECT CASE
201 WHEN $1 = 000 THEN 0.000000e+00
202 WHEN $1 = 001 THEN 2.675309e-18
203 WHEN $1 = 002 THEN 8.251782e-18
204 WHEN $1 = 003 THEN 2.545198e-17
205 WHEN $1 = 004 THEN 7.850462e-17
206 WHEN $1 = 005 THEN 2.421414e-16
207 WHEN $1 = 006 THEN 7.468660e-16
208 WHEN $1 = 007 THEN 2.303650e-15
209 WHEN $1 = 008 THEN 7.105427e-15
210 WHEN $1 = 009 THEN 2.191613e-14
211 WHEN $1 = 010 THEN 6.759860e-14
212 WHEN $1 = 011 THEN 2.085026e-13
213 WHEN $1 = 012 THEN 6.431099e-13
214 WHEN $1 = 013 THEN 1.983622e-12
215 WHEN $1 = 014 THEN 6.118327e-12
216 WHEN $1 = 015 THEN 1.887150e-11
217 WHEN $1 = 016 THEN 5.820766e-11
218 WHEN $1 = 017 THEN 1.795370e-10
219 WHEN $1 = 018 THEN 5.537677e-10
220 WHEN $1 = 019 THEN 1.708053e-09
221 WHEN $1 = 020 THEN 5.268356e-09
222 WHEN $1 = 021 THEN 1.624983e-08
223 WHEN $1 = 022 THEN 5.012133e-08
224 WHEN $1 = 023 THEN 1.545953e-07
225 WHEN $1 = 024 THEN 4.768372e-07
226 WHEN $1 = 025 THEN 1.470767e-06
227 WHEN $1 = 026 THEN 4.536465e-06
228 WHEN $1 = 027 THEN 1.399237e-05
229 WHEN $1 = 028 THEN 4.315837e-05
230 WHEN $1 = 029 THEN 1.331186e-04
231 WHEN $1 = 030 THEN 4.105940e-04
232 WHEN $1 = 031 THEN 1.266445e-03
233 WHEN $1 = 032 THEN 3.906250e-03
234 WHEN $1 = 033 THEN 4.044004e-03
235 WHEN $1 = 034 THEN 4.186615e-03
236 WHEN $1 = 035 THEN 4.334256e-03
237 WHEN $1 = 036 THEN 4.487103e-03
238 WHEN $1 = 037 THEN 4.645340e-03
239 WHEN $1 = 038 THEN 4.809158e-03
240 WHEN $1 = 039 THEN 4.978752e-03
241 WHEN $1 = 040 THEN 5.154328e-03
242 WHEN $1 = 041 THEN 5.336095e-03
243 WHEN $1 = 042 THEN 5.524272e-03
244 WHEN $1 = 043 THEN 5.719085e-03
245 WHEN $1 = 044 THEN 5.920768e-03
246 WHEN $1 = 045 THEN 6.129563e-03
247 WHEN $1 = 046 THEN 6.345722e-03
248 WHEN $1 = 047 THEN 6.569503e-03
249 WHEN $1 = 048 THEN 6.801176e-03
250 WHEN $1 = 049 THEN 7.041019e-03
251 WHEN $1 = 050 THEN 7.289320e-03
252 WHEN $1 = 051 THEN 7.546378e-03
253 WHEN $1 = 052 THEN 7.812500e-03
254 WHEN $1 = 053 THEN 8.088007e-03
255 WHEN $1 = 054 THEN 8.373230e-03
256 WHEN $1 = 055 THEN 8.668512e-03
257 WHEN $1 = 056 THEN 8.974206e-03
258 WHEN $1 = 057 THEN 9.290681e-03
259 WHEN $1 = 058 THEN 9.618316e-03
260 WHEN $1 = 059 THEN 9.957505e-03
261 WHEN $1 = 060 THEN 1.030866e-02
262 WHEN $1 = 061 THEN 1.067219e-02
263 WHEN $1 = 062 THEN 1.104854e-02
264 WHEN $1 = 063 THEN 1.143817e-02
265 WHEN $1 = 064 THEN 1.184154e-02
266 WHEN $1 = 065 THEN 1.225913e-02
267 WHEN $1 = 066 THEN 1.269144e-02
268 WHEN $1 = 067 THEN 1.313901e-02
269 WHEN $1 = 068 THEN 1.360235e-02
270 WHEN $1 = 069 THEN 1.408204e-02
271 WHEN $1 = 070 THEN 1.457864e-02
272 WHEN $1 = 071 THEN 1.509276e-02
273 WHEN $1 = 072 THEN 1.562500e-02
274 WHEN $1 = 073 THEN 1.617601e-02
275 WHEN $1 = 074 THEN 1.674646e-02
276 WHEN $1 = 075 THEN 1.733702e-02
277 WHEN $1 = 076 THEN 1.794841e-02
278 WHEN $1 = 077 THEN 1.858136e-02
279 WHEN $1 = 078 THEN 1.923663e-02
280 WHEN $1 = 079 THEN 1.991501e-02
281 WHEN $1 = 080 THEN 2.061731e-02
282 WHEN $1 = 081 THEN 2.134438e-02
283 WHEN $1 = 082 THEN 2.209709e-02
284 WHEN $1 = 083 THEN 2.287634e-02
285 WHEN $1 = 084 THEN 2.368307e-02
286 WHEN $1 = 085 THEN 2.451825e-02
287 WHEN $1 = 086 THEN 2.538289e-02
288 WHEN $1 = 087 THEN 2.627801e-02
289 WHEN $1 = 088 THEN 2.720471e-02
290 WHEN $1 = 089 THEN 2.816408e-02
291 WHEN $1 = 090 THEN 2.915728e-02
292 WHEN $1 = 091 THEN 3.018551e-02
293 WHEN $1 = 092 THEN 3.125000e-02
294 WHEN $1 = 093 THEN 3.235203e-02
295 WHEN $1 = 094 THEN 3.349292e-02
296 WHEN $1 = 095 THEN 3.467405e-02
297 WHEN $1 = 096 THEN 3.589682e-02
298 WHEN $1 = 097 THEN 3.716272e-02
299 WHEN $1 = 098 THEN 3.847326e-02
300 WHEN $1 = 099 THEN 3.983002e-02
301 WHEN $1 = 100 THEN 4.123462e-02
302 WHEN $1 = 101 THEN 4.268876e-02
303 WHEN $1 = 102 THEN 4.419417e-02
304 WHEN $1 = 103 THEN 4.575268e-02
305 WHEN $1 = 104 THEN 4.736614e-02
306 WHEN $1 = 105 THEN 4.903651e-02
307 WHEN $1 = 106 THEN 5.076577e-02
308 WHEN $1 = 107 THEN 5.255603e-02
309 WHEN $1 = 108 THEN 5.440941e-02
310 WHEN $1 = 109 THEN 5.632815e-02
311 WHEN $1 = 110 THEN 5.831456e-02
312 WHEN $1 = 111 THEN 6.037102e-02
313 WHEN $1 = 112 THEN 6.250000e-02
314 WHEN $1 = 113 THEN 6.470406e-02
315 WHEN $1 = 114 THEN 6.698584e-02
316 WHEN $1 = 115 THEN 6.934809e-02
317 WHEN $1 = 116 THEN 7.179365e-02
318 WHEN $1 = 117 THEN 7.432544e-02
319 WHEN $1 = 118 THEN 7.694653e-02
320 WHEN $1 = 119 THEN 7.966004e-02
321 WHEN $1 = 120 THEN 8.246924e-02
322 WHEN $1 = 121 THEN 8.537752e-02
323 WHEN $1 = 122 THEN 8.838835e-02
324 WHEN $1 = 123 THEN 9.150536e-02
325 WHEN $1 = 124 THEN 9.473229e-02
326 WHEN $1 = 125 THEN 9.807301e-02
327 WHEN $1 = 126 THEN 1.015315e-01
328 WHEN $1 = 127 THEN 1.051121e-01
329 WHEN $1 = 128 THEN 1.088188e-01
330 WHEN $1 = 129 THEN 1.126563e-01
331 WHEN $1 = 130 THEN 1.166291e-01
332 WHEN $1 = 131 THEN 1.207420e-01
333 WHEN $1 = 132 THEN 1.250000e-01
334 WHEN $1 = 133 THEN 1.294081e-01
335 WHEN $1 = 134 THEN 1.339717e-01
336 WHEN $1 = 135 THEN 1.386962e-01
337 WHEN $1 = 136 THEN 1.435873e-01
338 WHEN $1 = 137 THEN 1.486509e-01
339 WHEN $1 = 138 THEN 1.538931e-01
340 WHEN $1 = 139 THEN 1.593201e-01
341 WHEN $1 = 140 THEN 1.649385e-01
342 WHEN $1 = 141 THEN 1.707550e-01
343 WHEN $1 = 142 THEN 1.767767e-01
344 WHEN $1 = 143 THEN 1.830107e-01
345 WHEN $1 = 144 THEN 1.894646e-01
346 WHEN $1 = 145 THEN 1.961460e-01
347 WHEN $1 = 146 THEN 2.030631e-01
348 WHEN $1 = 147 THEN 2.102241e-01
349 WHEN $1 = 148 THEN 2.176376e-01
350 WHEN $1 = 149 THEN 2.253126e-01
351 WHEN $1 = 150 THEN 2.332582e-01
352 WHEN $1 = 151 THEN 2.414841e-01
353 WHEN $1 = 152 THEN 2.500000e-01
354 WHEN $1 = 153 THEN 2.588162e-01
355 WHEN $1 = 154 THEN 2.679434e-01
356 WHEN $1 = 155 THEN 2.773924e-01
357 WHEN $1 = 156 THEN 2.871746e-01
358 WHEN $1 = 157 THEN 2.973018e-01
359 WHEN $1 = 158 THEN 3.077861e-01
360 WHEN $1 = 159 THEN 3.186402e-01
361 WHEN $1 = 160 THEN 3.298770e-01
362 WHEN $1 = 161 THEN 3.415101e-01
363 WHEN $1 = 162 THEN 3.535534e-01
364 WHEN $1 = 163 THEN 3.660214e-01
365 WHEN $1 = 164 THEN 3.789291e-01
366 WHEN $1 = 165 THEN 3.922920e-01
367 WHEN $1 = 166 THEN 4.061262e-01
368 WHEN $1 = 167 THEN 4.204482e-01
369 WHEN $1 = 168 THEN 4.352753e-01
370 WHEN $1 = 169 THEN 4.506252e-01
371 WHEN $1 = 170 THEN 4.665165e-01
372 WHEN $1 = 171 THEN 4.829682e-01
373 WHEN $1 = 172 THEN 5.000000e-01
374 WHEN $1 = 173 THEN 5.176325e-01
375 WHEN $1 = 174 THEN 5.358867e-01
376 WHEN $1 = 175 THEN 5.547847e-01
377 WHEN $1 = 176 THEN 5.743492e-01
378 WHEN $1 = 177 THEN 5.946036e-01
379 WHEN $1 = 178 THEN 6.155722e-01
380 WHEN $1 = 179 THEN 6.372803e-01
381 WHEN $1 = 180 THEN 6.597540e-01
382 WHEN $1 = 181 THEN 6.830201e-01
383 WHEN $1 = 182 THEN 7.071068e-01
384 WHEN $1 = 183 THEN 7.320428e-01
385 WHEN $1 = 184 THEN 7.578583e-01
386 WHEN $1 = 185 THEN 7.845841e-01
387 WHEN $1 = 186 THEN 8.122524e-01
388 WHEN $1 = 187 THEN 8.408964e-01
389 WHEN $1 = 188 THEN 8.705506e-01
390 WHEN $1 = 189 THEN 9.012505e-01
391 WHEN $1 = 190 THEN 9.330330e-01
392 WHEN $1 = 191 THEN 9.659363e-01
393 WHEN $1 = 192 THEN 1.000000e+00
394 WHEN $1 = 193 THEN 2.000000e+00
395 WHEN $1 = 194 THEN 4.000000e+00
396 WHEN $1 = 195 THEN 8.000000e+00
397 WHEN $1 = 196 THEN 1.600000e+01
398 WHEN $1 = 197 THEN 3.200000e+01
399 WHEN $1 = 198 THEN 6.400000e+01
400 WHEN $1 = 199 THEN 1.280000e+02
401 WHEN $1 = 200 THEN 2.560000e+02
402 WHEN $1 = 201 THEN 5.120000e+02
403 WHEN $1 = 202 THEN 1.024000e+03
404 WHEN $1 = 203 THEN 2.048000e+03
405 WHEN $1 = 204 THEN 4.096000e+03
406 WHEN $1 = 205 THEN 8.192000e+03
407 WHEN $1 = 206 THEN 1.638400e+04
408 WHEN $1 = 207 THEN 3.276800e+04
409 WHEN $1 = 208 THEN 6.553600e+04
410 WHEN $1 = 209 THEN 1.310720e+05
411 WHEN $1 = 210 THEN 2.621440e+05
412 WHEN $1 = 211 THEN 5.242880e+05
413 WHEN $1 = 212 THEN 1.048576e+06
414 WHEN $1 = 213 THEN 2.097152e+06
415 WHEN $1 = 214 THEN 4.194304e+06
416 WHEN $1 = 215 THEN 8.388608e+06
417 WHEN $1 = 216 THEN 1.677722e+07
418 WHEN $1 = 217 THEN 3.355443e+07
419 WHEN $1 = 218 THEN 6.710886e+07
420 WHEN $1 = 219 THEN 1.342177e+08
421 WHEN $1 = 220 THEN 2.684355e+08
422 WHEN $1 = 221 THEN 5.368709e+08
423 WHEN $1 = 222 THEN 1.073742e+09
424 WHEN $1 = 223 THEN 2.147484e+09
425 WHEN $1 = 224 THEN 4.294967e+09
426 WHEN $1 = 225 THEN 8.589935e+09
427 WHEN $1 = 226 THEN 1.717987e+10
428 WHEN $1 = 227 THEN 3.435974e+10
429 WHEN $1 = 228 THEN 6.871948e+10
430 WHEN $1 = 229 THEN 1.374390e+11
431 WHEN $1 = 230 THEN 2.748779e+11
432 WHEN $1 = 231 THEN 5.497558e+11
433 WHEN $1 = 232 THEN 1.099512e+12
434 WHEN $1 = 233 THEN 2.199023e+12
435 WHEN $1 = 234 THEN 4.398047e+12
436 WHEN $1 = 235 THEN 8.796093e+12
437 WHEN $1 = 236 THEN 1.759219e+13
438 WHEN $1 = 237 THEN 3.518437e+13
439 WHEN $1 = 238 THEN 7.036874e+13
440 WHEN $1 = 239 THEN 1.407375e+14
441 WHEN $1 = 240 THEN 2.814750e+14
442 WHEN $1 = 241 THEN 5.629500e+14
443 WHEN $1 = 242 THEN 1.125900e+15
444 WHEN $1 = 243 THEN 2.251800e+15
445 WHEN $1 = 244 THEN 4.503600e+15
446 WHEN $1 = 245 THEN 9.007199e+15
447 WHEN $1 = 246 THEN 1.801440e+16
448 WHEN $1 = 247 THEN 3.602880e+16
449 WHEN $1 = 248 THEN 7.205759e+16
450 WHEN $1 = 249 THEN 1.441152e+17
451 WHEN $1 = 250 THEN 2.882304e+17
452 WHEN $1 = 251 THEN 5.764608e+17
453 WHEN $1 = 252 THEN 1.152922e+18
454 WHEN $1 = 253 THEN 2.305843e+18
455 WHEN $1 = 254 THEN 4.611686e+18
456 WHEN $1 = 255 THEN 9.223372e+18
457 END INTO perc;
458 RETURN perc;
459END;
460$BODY$
461LANGUAGE plpgsql;
462-- +migrate StatementEnd
463
464-- +migrate StatementBegin
465CREATE FUNCTION set_tx()
466 RETURNS TRIGGER
467AS
468$BODY$
469DECLARE
470 _value NUMERIC;
471 _usd_update TIMESTAMP;
472 _tx_timestamp TIMESTAMP;
473BEGIN
474 IF NEW.is_l1 THEN
475 -- Validate L1 Tx
476 IF NEW.user_origin IS NULL OR
477 NEW.from_eth_addr IS NULL OR
478 NEW.from_bjj IS NULL OR
479 NEW.deposit_amount IS NULL OR
480 NEW.deposit_amount_f IS NULL OR
481 (NOT NEW.user_origin AND NEW.batch_num IS NULL) THEN -- If is Coordinator L1, must include batch_num
482 RAISE EXCEPTION 'Invalid L1 tx: %', NEW;
483 END IF;
484 ELSE
485 -- Validate L2 Tx
486 IF NEW.batch_num IS NULL OR NEW.nonce IS NULL THEN
487 RAISE EXCEPTION 'Invalid L2 tx: %', NEW;
488 END IF;
489 -- Set fee if it's null
490 IF NEW.fee IS NULL THEN
491 NEW.fee = (SELECT 0);
492 END IF;
493 -- Set token_id
494 NEW."token_id" = (SELECT token_id FROM account WHERE idx = NEW."from_idx");
495 -- Set from_{eth_addr,bjj}
496 SELECT INTO NEW."from_eth_addr", NEW."from_bjj" eth_addr, bjj FROM account WHERE idx = NEW.from_idx;
497 END IF;
498 -- Set USD related
499 SELECT INTO _value, _usd_update, _tx_timestamp
500 usd / POWER(10, decimals), usd_update, timestamp FROM token INNER JOIN block on token.eth_block_num = block.eth_block_num WHERE token_id = NEW.token_id;
501 IF _usd_update - interval '24 hours' < _usd_update AND _usd_update + interval '24 hours' > _usd_update THEN
502 IF _value > 0.0 THEN
503 IF NEW."amount_f" > 0.0 THEN
504 NEW."amount_usd" = (SELECT _value * NEW."amount_f");
505 IF NOT NEW."is_l1" AND NEW."fee" > 0 THEN
506 NEW."fee_usd" = (SELECT NEW."amount_usd" * fee_percentage(NEW.fee::NUMERIC));
507 END IF;
508 END IF;
509 IF NEW."is_l1" AND NEW."deposit_amount_f" > 0.0 THEN
510 NEW."deposit_amount_usd" = (SELECT _value * NEW.deposit_amount_f);
511 END IF;
512 END IF;
513 END IF;
514 -- Set to_{eth_addr,bjj}
515 IF NEW."to_idx" > 255 THEN
516 SELECT INTO NEW."to_eth_addr", NEW."to_bjj" eth_addr, bjj FROM account WHERE idx = NEW."to_idx";
517 END IF;
518 RETURN NEW;
519END;
520$BODY$
521LANGUAGE plpgsql;
522-- +migrate StatementEnd
523CREATE TRIGGER trigger_set_tx BEFORE INSERT ON tx
524FOR EACH ROW EXECUTE PROCEDURE set_tx();
525
526-- +migrate StatementBegin
527CREATE FUNCTION forge_l1_user_txs()
528 RETURNS TRIGGER
529AS
530$BODY$
531BEGIN
532 IF NEW.forge_l1_txs_num IS NOT NULL THEN
533 UPDATE tx
534 SET item_id = upd.item_id, batch_num = NEW.batch_num
535 FROM (
536 SELECT id, nextval('tx_item_id') FROM tx
537 WHERE user_origin AND NEW.forge_l1_txs_num = to_forge_l1_txs_num
538 ORDER BY position
539 FOR UPDATE
540 ) as upd (id, item_id)
541 WHERE tx.id = upd.id;
542 END IF;
543 RETURN NEW;
544END;
545$BODY$
546LANGUAGE plpgsql;
547-- +migrate StatementEnd
548CREATE TRIGGER trigger_forge_l1_txs AFTER INSERT ON batch
549FOR EACH ROW EXECUTE PROCEDURE forge_l1_user_txs();
550
551CREATE TABLE rollup_vars (
552 eth_block_num BIGINT PRIMARY KEY REFERENCES block (eth_block_num) ON DELETE CASCADE,
553 fee_add_token DECIMAL(78,0) NOT NULL,
554 forge_l1_timeout BIGINT NOT NULL,
555 withdrawal_delay BIGINT NOT NULL,
556 buckets BYTEA NOT NULL,
557 safe_mode BOOLEAN NOT NULL
558);
559
560CREATE TABLE bucket_update (
561 item_id SERIAL PRIMARY KEY,
562 eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
563 num_bucket BIGINT NOT NULL,
564 block_stamp BIGINT NOT NULL,
565 withdrawals DECIMAL(78,0) NOT NULL
566);
567
568CREATE TABLE token_exchange (
569 item_id SERIAL PRIMARY KEY,
570 eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
571 eth_addr BYTEA NOT NULL,
572 value_usd BIGINT NOT NULL
573);
574
575CREATE TABLE escape_hatch_withdrawal (
576 item_id SERIAL PRIMARY KEY,
577 eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
578 who_addr BYTEA NOT NULL,
579 to_addr BYTEA NOT NULL,
580 token_addr BYTEA NOT NULL,
581 amount DECIMAL(78,0) NOT NULL
582);
583
584CREATE TABLE auction_vars (
585 eth_block_num BIGINT PRIMARY KEY REFERENCES block (eth_block_num) ON DELETE CASCADE,
586 donation_address BYTEA NOT NULL,
587 boot_coordinator BYTEA NOT NULL,
588 boot_coordinator_url BYTEA NOT NULL,
589 default_slot_set_bid BYTEA NOT NULL,
590 default_slot_set_bid_slot_num BIGINT NOT NULL, -- slot_num after which the new default_slot_set_bid applies
591 closed_auction_slots INT NOT NULL,
592 open_auction_slots INT NOT NULL,
593 allocation_ratio VARCHAR(200),
594 outbidding INT NOT NULL,
595 slot_deadline INT NOT NULL
596);
597
598CREATE TABLE wdelayer_vars (
599 eth_block_num BIGINT PRIMARY KEY REFERENCES block (eth_block_num) ON DELETE CASCADE,
600 gov_address BYTEA NOT NULL,
601 emg_address BYTEA NOT NULL,
602 withdrawal_delay BIGINT NOT NULL,
603 emergency_start_block BIGINT NOT NULL,
604 emergency_mode BOOLEAN NOT NULL
605);
606
607-- L2
608CREATE TABLE tx_pool (
609 tx_id BYTEA PRIMARY KEY,
610 from_idx BIGINT NOT NULL,
611 effective_from_eth_addr BYTEA,
612 effective_from_bjj BYTEA,
613 to_idx BIGINT,
614 to_eth_addr BYTEA,
615 to_bjj BYTEA,
616 effective_to_eth_addr BYTEA,
617 effective_to_bjj BYTEA,
618 token_id INT NOT NULL REFERENCES token (token_id) ON DELETE CASCADE,
619 amount DECIMAL(78,0) NOT NULL,
620 amount_f NUMERIC NOT NULL,
621 fee SMALLINT NOT NULL,
622 nonce BIGINT NOT NULL,
623 state CHAR(4) NOT NULL,
624 info VARCHAR,
625 signature BYTEA NOT NULL,
626 timestamp TIMESTAMP WITHOUT TIME ZONE DEFAULT timezone('utc', now()),
627 batch_num BIGINT,
628 rq_from_idx BIGINT,
629 rq_to_idx BIGINT,
630 rq_to_eth_addr BYTEA,
631 rq_to_bjj BYTEA,
632 rq_token_id INT,
633 rq_amount DECIMAL(78,0),
634 rq_fee SMALLINT,
635 rq_nonce BIGINT,
636 tx_type VARCHAR(40) NOT NULL,
637 client_ip VARCHAR,
638 external_delete BOOLEAN NOT NULL DEFAULT false
639);
640
641-- +migrate StatementBegin
642CREATE FUNCTION set_pool_tx()
643 RETURNS TRIGGER
644AS
645$BODY$
646BEGIN
647 SELECT INTO NEW."effective_from_eth_addr", NEW."effective_from_bjj" eth_addr, bjj FROM account WHERE idx = NEW."from_idx";
648 -- Set to_{eth_addr,bjj}
649 IF NEW.to_idx > 255 THEN
650 SELECT INTO NEW."effective_to_eth_addr", NEW."effective_to_bjj" eth_addr, bjj FROM account WHERE idx = NEW."to_idx";
651 ELSE
652 NEW."effective_to_eth_addr" = NEW."to_eth_addr";
653 NEW."effective_to_bjj" = NEW."to_bjj";
654 END IF;
655 RETURN NEW;
656END;
657$BODY$
658LANGUAGE plpgsql;
659-- +migrate StatementEnd
660CREATE TRIGGER trigger_set_pool_tx BEFORE INSERT ON tx_pool
661FOR EACH ROW EXECUTE PROCEDURE set_pool_tx();
662
663CREATE TABLE account_creation_auth (
664 eth_addr BYTEA PRIMARY KEY,
665 bjj BYTEA NOT NULL,
666 signature BYTEA NOT NULL,
667 timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT timezone('utc', now())
668);
669
670CREATE TABLE node_info (
671 item_id SERIAL PRIMARY KEY,
672 state BYTEA, -- object returned by GET /state
673 config BYTEA, -- Node config
674 -- max_pool_txs BIGINT, -- L2DB config
675 -- min_fee NUMERIC, -- L2DB config
676 constants BYTEA -- info of the network that is constant
677);
678INSERT INTO node_info(item_id) VALUES (1); -- Always have a single row that we will update
679
680-- +migrate Down
681-- triggers
682DROP TRIGGER IF EXISTS trigger_token_usd_update ON token;
683DROP TRIGGER IF EXISTS trigger_set_tx ON tx;
684DROP TRIGGER IF EXISTS trigger_forge_l1_txs ON batch;
685DROP TRIGGER IF EXISTS trigger_set_pool_tx ON tx_pool;
686-- functions
687DROP FUNCTION IF EXISTS hez_idx;
688DROP FUNCTION IF EXISTS set_token_usd_update;
689DROP FUNCTION IF EXISTS fee_percentage;
690DROP FUNCTION IF EXISTS set_tx;
691DROP FUNCTION IF EXISTS forge_l1_user_txs;
692DROP FUNCTION IF EXISTS set_pool_tx;
693-- drop tables IF EXISTS
694DROP TABLE IF EXISTS node_info;
695DROP TABLE IF EXISTS account_creation_auth;
696DROP TABLE IF EXISTS tx_pool;
697DROP TABLE IF EXISTS auction_vars;
698DROP TABLE IF EXISTS rollup_vars;
699DROP TABLE IF EXISTS escape_hatch_withdrawal;
700DROP TABLE IF EXISTS bucket_update;
701DROP TABLE IF EXISTS token_exchange;
702DROP TABLE IF EXISTS wdelayer_vars;
703DROP TABLE IF EXISTS tx;
704DROP TABLE IF EXISTS exit_tree;
705DROP TABLE IF EXISTS account_update;
706DROP TABLE IF EXISTS account;
707DROP TABLE IF EXISTS token;
708DROP TABLE IF EXISTS bid;
709DROP TABLE IF EXISTS batch;
710DROP TABLE IF EXISTS coordinator;
711DROP TABLE IF EXISTS block;
712-- sequences
713DROP SEQUENCE IF EXISTS tx_item_id;