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