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