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 state_root BYTEA NOT NULL,
25 num_accounts BIGINT NOT NULL,
26 last_idx BIGINT NOT NULL,
27 exit_root BYTEA NOT NULL,
28 forge_l1_txs_num BIGINT,
29 slot_num BIGINT NOT NULL,
30 total_fees_usd NUMERIC
31);
32
33CREATE TABLE bid (
34 slot_num BIGINT NOT NULL,
35 bid_value BYTEA NOT NULL,
36 eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
37 bidder_addr BYTEA NOT NULL, -- fake foreign key for coordinator
38 PRIMARY KEY (slot_num, bid_value)
39);
40
41CREATE TABLE token (
42 item_id SERIAL PRIMARY KEY,
43 token_id INT UNIQUE NOT NULL,
44 eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
45 eth_addr BYTEA UNIQUE NOT NULL,
46 name VARCHAR(20) NOT NULL,
47 symbol VARCHAR(10) NOT NULL,
48 decimals INT NOT NULL,
49 usd NUMERIC,
50 usd_update TIMESTAMP WITHOUT TIME ZONE
51);
52
53
54-- +migrate StatementBegin
55CREATE FUNCTION hez_idx(BIGINT, VARCHAR)
56 RETURNS VARCHAR
57AS
58$BODY$
59BEGIN
60 IF $1 = 1 THEN
61 RETURN 'hez:EXIT:1';
62 END IF;
63 RETURN 'hez:' || $2 || ':' || $1;
64END;
65$BODY$
66LANGUAGE plpgsql;
67-- +migrate StatementEnd
68
69CREATE TABLE account (
70 idx BIGINT PRIMARY KEY,
71 token_id INT NOT NULL REFERENCES token (token_id) ON DELETE CASCADE,
72 batch_num BIGINT NOT NULL REFERENCES batch (batch_num) ON DELETE CASCADE,
73 bjj BYTEA NOT NULL,
74 eth_addr BYTEA NOT NULL
75);
76
77CREATE TABLE exit_tree (
78 item_id SERIAL PRIMARY KEY,
79 batch_num BIGINT REFERENCES batch (batch_num) ON DELETE CASCADE,
80 account_idx BIGINT REFERENCES account (idx) ON DELETE CASCADE,
81 merkle_proof BYTEA NOT NULL,
82 balance BYTEA NOT NULL,
83 instant_withdrawn BIGINT REFERENCES batch (batch_num) ON DELETE SET NULL,
84 delayed_withdraw_request BIGINT REFERENCES batch (batch_num) ON DELETE SET NULL,
85 delayed_withdrawn BIGINT REFERENCES batch (batch_num) ON DELETE SET NULL
86);
87
88-- +migrate StatementBegin
89CREATE FUNCTION set_token_usd_update()
90 RETURNS TRIGGER
91AS
92$BODY$
93BEGIN
94 IF NEW."usd" IS NOT NULL AND NEW."usd_update" IS NULL THEN
95 NEW."usd_update" = timezone('utc', now());
96 END IF;
97 RETURN NEW;
98END;
99$BODY$
100LANGUAGE plpgsql;
101-- +migrate StatementEnd
102CREATE TRIGGER trigger_token_usd_update BEFORE UPDATE OR INSERT ON token
103FOR EACH ROW EXECUTE PROCEDURE set_token_usd_update();
104
105CREATE SEQUENCE tx_item_id;
106
107CREATE TABLE tx (
108 -- Generic TX
109 item_id INTEGER PRIMARY KEY DEFAULT nextval('tx_item_id'),
110 is_l1 BOOLEAN NOT NULL,
111 id BYTEA,
112 type VARCHAR(40) NOT NULL,
113 position INT NOT NULL,
114 from_idx BIGINT,
115 from_eth_addr BYTEA,
116 from_bjj BYTEA,
117 to_idx BIGINT NOT NULL,
118 to_eth_addr BYTEA,
119 to_bjj BYTEA,
120 amount BYTEA NOT NULL,
121 amount_f NUMERIC NOT NULL,
122 token_id INT NOT NULL REFERENCES token (token_id),
123 amount_usd NUMERIC, -- Value of the amount in USD at the moment the tx was inserted in the DB
124 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.
125 eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
126 -- L1
127 to_forge_l1_txs_num BIGINT,
128 user_origin BOOLEAN,
129 load_amount BYTEA,
130 load_amount_f NUMERIC,
131 load_amount_usd NUMERIC,
132 -- L2
133 fee INT,
134 fee_usd NUMERIC,
135 nonce BIGINT
136);
137
138-- +migrate StatementBegin
139CREATE FUNCTION fee_percentage(NUMERIC)
140 RETURNS NUMERIC
141AS
142$BODY$
143DECLARE perc NUMERIC;
144BEGIN
145 SELECT CASE
146 WHEN $1 = 0 THEN 0
147 WHEN $1 = 1 THEN 3.162278e-24
148 WHEN $1 = 2 THEN 1.000000e-23
149 WHEN $1 = 3 THEN 3.162278e-23
150 WHEN $1 = 4 THEN 1.000000e-22
151 WHEN $1 = 5 THEN 3.162278e-22
152 WHEN $1 = 6 THEN 1.000000e-21
153 WHEN $1 = 7 THEN 3.162278e-21
154 WHEN $1 = 8 THEN 1.000000e-20
155 WHEN $1 = 9 THEN 3.162278e-20
156 WHEN $1 = 10 THEN 1.000000e-19
157 WHEN $1 = 11 THEN 3.162278e-19
158 WHEN $1 = 12 THEN 1.000000e-18
159 WHEN $1 = 13 THEN 3.162278e-18
160 WHEN $1 = 14 THEN 1.000000e-17
161 WHEN $1 = 15 THEN 3.162278e-17
162 WHEN $1 = 16 THEN 1.000000e-16
163 WHEN $1 = 17 THEN 3.162278e-16
164 WHEN $1 = 18 THEN 1.000000e-15
165 WHEN $1 = 19 THEN 3.162278e-15
166 WHEN $1 = 20 THEN 1.000000e-14
167 WHEN $1 = 21 THEN 3.162278e-14
168 WHEN $1 = 22 THEN 1.000000e-13
169 WHEN $1 = 23 THEN 3.162278e-13
170 WHEN $1 = 24 THEN 1.000000e-12
171 WHEN $1 = 25 THEN 3.162278e-12
172 WHEN $1 = 26 THEN 1.000000e-11
173 WHEN $1 = 27 THEN 3.162278e-11
174 WHEN $1 = 28 THEN 1.000000e-10
175 WHEN $1 = 29 THEN 3.162278e-10
176 WHEN $1 = 30 THEN 1.000000e-09
177 WHEN $1 = 31 THEN 3.162278e-09
178 WHEN $1 = 32 THEN 1.000000e-08
179 WHEN $1 = 33 THEN 1.100694e-08
180 WHEN $1 = 34 THEN 1.211528e-08
181 WHEN $1 = 35 THEN 1.333521e-08
182 WHEN $1 = 36 THEN 1.467799e-08
183 WHEN $1 = 37 THEN 1.615598e-08
184 WHEN $1 = 38 THEN 1.778279e-08
185 WHEN $1 = 39 THEN 1.957342e-08
186 WHEN $1 = 40 THEN 2.154435e-08
187 WHEN $1 = 41 THEN 2.371374e-08
188 WHEN $1 = 42 THEN 2.610157e-08
189 WHEN $1 = 43 THEN 2.872985e-08
190 WHEN $1 = 44 THEN 3.162278e-08
191 WHEN $1 = 45 THEN 3.480701e-08
192 WHEN $1 = 46 THEN 3.831187e-08
193 WHEN $1 = 47 THEN 4.216965e-08
194 WHEN $1 = 48 THEN 4.641589e-08
195 WHEN $1 = 49 THEN 5.108970e-08
196 WHEN $1 = 50 THEN 5.623413e-08
197 WHEN $1 = 51 THEN 6.189658e-08
198 WHEN $1 = 52 THEN 6.812921e-08
199 WHEN $1 = 53 THEN 7.498942e-08
200 WHEN $1 = 54 THEN 8.254042e-08
201 WHEN $1 = 55 THEN 9.085176e-08
202 WHEN $1 = 56 THEN 1.000000e-07
203 WHEN $1 = 57 THEN 1.100694e-07
204 WHEN $1 = 58 THEN 1.211528e-07
205 WHEN $1 = 59 THEN 1.333521e-07
206 WHEN $1 = 60 THEN 1.467799e-07
207 WHEN $1 = 61 THEN 1.615598e-07
208 WHEN $1 = 62 THEN 1.778279e-07
209 WHEN $1 = 63 THEN 1.957342e-07
210 WHEN $1 = 64 THEN 2.154435e-07
211 WHEN $1 = 65 THEN 2.371374e-07
212 WHEN $1 = 66 THEN 2.610157e-07
213 WHEN $1 = 67 THEN 2.872985e-07
214 WHEN $1 = 68 THEN 3.162278e-07
215 WHEN $1 = 69 THEN 3.480701e-07
216 WHEN $1 = 70 THEN 3.831187e-07
217 WHEN $1 = 71 THEN 4.216965e-07
218 WHEN $1 = 72 THEN 4.641589e-07
219 WHEN $1 = 73 THEN 5.108970e-07
220 WHEN $1 = 74 THEN 5.623413e-07
221 WHEN $1 = 75 THEN 6.189658e-07
222 WHEN $1 = 76 THEN 6.812921e-07
223 WHEN $1 = 77 THEN 7.498942e-07
224 WHEN $1 = 78 THEN 8.254042e-07
225 WHEN $1 = 79 THEN 9.085176e-07
226 WHEN $1 = 80 THEN 1.000000e-06
227 WHEN $1 = 81 THEN 1.100694e-06
228 WHEN $1 = 82 THEN 1.211528e-06
229 WHEN $1 = 83 THEN 1.333521e-06
230 WHEN $1 = 84 THEN 1.467799e-06
231 WHEN $1 = 85 THEN 1.615598e-06
232 WHEN $1 = 86 THEN 1.778279e-06
233 WHEN $1 = 87 THEN 1.957342e-06
234 WHEN $1 = 88 THEN 2.154435e-06
235 WHEN $1 = 89 THEN 2.371374e-06
236 WHEN $1 = 90 THEN 2.610157e-06
237 WHEN $1 = 91 THEN 2.872985e-06
238 WHEN $1 = 92 THEN 3.162278e-06
239 WHEN $1 = 93 THEN 3.480701e-06
240 WHEN $1 = 94 THEN 3.831187e-06
241 WHEN $1 = 95 THEN 4.216965e-06
242 WHEN $1 = 96 THEN 4.641589e-06
243 WHEN $1 = 97 THEN 5.108970e-06
244 WHEN $1 = 98 THEN 5.623413e-06
245 WHEN $1 = 99 THEN 6.189658e-06
246 WHEN $1 = 100 THEN 6.812921e-06
247 WHEN $1 = 101 THEN 7.498942e-06
248 WHEN $1 = 102 THEN 8.254042e-06
249 WHEN $1 = 103 THEN 9.085176e-06
250 WHEN $1 = 104 THEN 1.000000e-05
251 WHEN $1 = 105 THEN 1.100694e-05
252 WHEN $1 = 106 THEN 1.211528e-05
253 WHEN $1 = 107 THEN 1.333521e-05
254 WHEN $1 = 108 THEN 1.467799e-05
255 WHEN $1 = 109 THEN 1.615598e-05
256 WHEN $1 = 110 THEN 1.778279e-05
257 WHEN $1 = 111 THEN 1.957342e-05
258 WHEN $1 = 112 THEN 2.154435e-05
259 WHEN $1 = 113 THEN 2.371374e-05
260 WHEN $1 = 114 THEN 2.610157e-05
261 WHEN $1 = 115 THEN 2.872985e-05
262 WHEN $1 = 116 THEN 3.162278e-05
263 WHEN $1 = 117 THEN 3.480701e-05
264 WHEN $1 = 118 THEN 3.831187e-05
265 WHEN $1 = 119 THEN 4.216965e-05
266 WHEN $1 = 120 THEN 4.641589e-05
267 WHEN $1 = 121 THEN 5.108970e-05
268 WHEN $1 = 122 THEN 5.623413e-05
269 WHEN $1 = 123 THEN 6.189658e-05
270 WHEN $1 = 124 THEN 6.812921e-05
271 WHEN $1 = 125 THEN 7.498942e-05
272 WHEN $1 = 126 THEN 8.254042e-05
273 WHEN $1 = 127 THEN 9.085176e-05
274 WHEN $1 = 128 THEN 1.000000e-04
275 WHEN $1 = 129 THEN 1.100694e-04
276 WHEN $1 = 130 THEN 1.211528e-04
277 WHEN $1 = 131 THEN 1.333521e-04
278 WHEN $1 = 132 THEN 1.467799e-04
279 WHEN $1 = 133 THEN 1.615598e-04
280 WHEN $1 = 134 THEN 1.778279e-04
281 WHEN $1 = 135 THEN 1.957342e-04
282 WHEN $1 = 136 THEN 2.154435e-04
283 WHEN $1 = 137 THEN 2.371374e-04
284 WHEN $1 = 138 THEN 2.610157e-04
285 WHEN $1 = 139 THEN 2.872985e-04
286 WHEN $1 = 140 THEN 3.162278e-04
287 WHEN $1 = 141 THEN 3.480701e-04
288 WHEN $1 = 142 THEN 3.831187e-04
289 WHEN $1 = 143 THEN 4.216965e-04
290 WHEN $1 = 144 THEN 4.641589e-04
291 WHEN $1 = 145 THEN 5.108970e-04
292 WHEN $1 = 146 THEN 5.623413e-04
293 WHEN $1 = 147 THEN 6.189658e-04
294 WHEN $1 = 148 THEN 6.812921e-04
295 WHEN $1 = 149 THEN 7.498942e-04
296 WHEN $1 = 150 THEN 8.254042e-04
297 WHEN $1 = 151 THEN 9.085176e-04
298 WHEN $1 = 152 THEN 1.000000e-03
299 WHEN $1 = 153 THEN 1.100694e-03
300 WHEN $1 = 154 THEN 1.211528e-03
301 WHEN $1 = 155 THEN 1.333521e-03
302 WHEN $1 = 156 THEN 1.467799e-03
303 WHEN $1 = 157 THEN 1.615598e-03
304 WHEN $1 = 158 THEN 1.778279e-03
305 WHEN $1 = 159 THEN 1.957342e-03
306 WHEN $1 = 160 THEN 2.154435e-03
307 WHEN $1 = 161 THEN 2.371374e-03
308 WHEN $1 = 162 THEN 2.610157e-03
309 WHEN $1 = 163 THEN 2.872985e-03
310 WHEN $1 = 164 THEN 3.162278e-03
311 WHEN $1 = 165 THEN 3.480701e-03
312 WHEN $1 = 166 THEN 3.831187e-03
313 WHEN $1 = 167 THEN 4.216965e-03
314 WHEN $1 = 168 THEN 4.641589e-03
315 WHEN $1 = 169 THEN 5.108970e-03
316 WHEN $1 = 170 THEN 5.623413e-03
317 WHEN $1 = 171 THEN 6.189658e-03
318 WHEN $1 = 172 THEN 6.812921e-03
319 WHEN $1 = 173 THEN 7.498942e-03
320 WHEN $1 = 174 THEN 8.254042e-03
321 WHEN $1 = 175 THEN 9.085176e-03
322 WHEN $1 = 176 THEN 1.000000e-02
323 WHEN $1 = 177 THEN 1.100694e-02
324 WHEN $1 = 178 THEN 1.211528e-02
325 WHEN $1 = 179 THEN 1.333521e-02
326 WHEN $1 = 180 THEN 1.467799e-02
327 WHEN $1 = 181 THEN 1.615598e-02
328 WHEN $1 = 182 THEN 1.778279e-02
329 WHEN $1 = 183 THEN 1.957342e-02
330 WHEN $1 = 184 THEN 2.154435e-02
331 WHEN $1 = 185 THEN 2.371374e-02
332 WHEN $1 = 186 THEN 2.610157e-02
333 WHEN $1 = 187 THEN 2.872985e-02
334 WHEN $1 = 188 THEN 3.162278e-02
335 WHEN $1 = 189 THEN 3.480701e-02
336 WHEN $1 = 190 THEN 3.831187e-02
337 WHEN $1 = 191 THEN 4.216965e-02
338 WHEN $1 = 192 THEN 4.641589e-02
339 WHEN $1 = 193 THEN 5.108970e-02
340 WHEN $1 = 194 THEN 5.623413e-02
341 WHEN $1 = 195 THEN 6.189658e-02
342 WHEN $1 = 196 THEN 6.812921e-02
343 WHEN $1 = 197 THEN 7.498942e-02
344 WHEN $1 = 198 THEN 8.254042e-02
345 WHEN $1 = 199 THEN 9.085176e-02
346 WHEN $1 = 200 THEN 1.000000e-01
347 WHEN $1 = 201 THEN 1.100694e-01
348 WHEN $1 = 202 THEN 1.211528e-01
349 WHEN $1 = 203 THEN 1.333521e-01
350 WHEN $1 = 204 THEN 1.467799e-01
351 WHEN $1 = 205 THEN 1.615598e-01
352 WHEN $1 = 206 THEN 1.778279e-01
353 WHEN $1 = 207 THEN 1.957342e-01
354 WHEN $1 = 208 THEN 2.154435e-01
355 WHEN $1 = 209 THEN 2.371374e-01
356 WHEN $1 = 210 THEN 2.610157e-01
357 WHEN $1 = 211 THEN 2.872985e-01
358 WHEN $1 = 212 THEN 3.162278e-01
359 WHEN $1 = 213 THEN 3.480701e-01
360 WHEN $1 = 214 THEN 3.831187e-01
361 WHEN $1 = 215 THEN 4.216965e-01
362 WHEN $1 = 216 THEN 4.641589e-01
363 WHEN $1 = 217 THEN 5.108970e-01
364 WHEN $1 = 218 THEN 5.623413e-01
365 WHEN $1 = 219 THEN 6.189658e-01
366 WHEN $1 = 220 THEN 6.812921e-01
367 WHEN $1 = 221 THEN 7.498942e-01
368 WHEN $1 = 222 THEN 8.254042e-01
369 WHEN $1 = 223 THEN 9.085176e-01
370 WHEN $1 = 224 THEN 1.000000e+00
371 WHEN $1 = 225 THEN 1.000000e+01
372 WHEN $1 = 226 THEN 1.000000e+02
373 WHEN $1 = 227 THEN 1.000000e+03
374 WHEN $1 = 228 THEN 1.000000e+04
375 WHEN $1 = 229 THEN 1.000000e+05
376 WHEN $1 = 230 THEN 1.000000e+06
377 WHEN $1 = 231 THEN 1.000000e+07
378 WHEN $1 = 232 THEN 1.000000e+08
379 WHEN $1 = 233 THEN 1.000000e+09
380 WHEN $1 = 234 THEN 1.000000e+10
381 WHEN $1 = 235 THEN 1.000000e+11
382 WHEN $1 = 236 THEN 1.000000e+12
383 WHEN $1 = 237 THEN 1.000000e+13
384 WHEN $1 = 238 THEN 1.000000e+14
385 WHEN $1 = 239 THEN 1.000000e+15
386 WHEN $1 = 240 THEN 1.000000e+16
387 WHEN $1 = 241 THEN 1.000000e+17
388 WHEN $1 = 242 THEN 1.000000e+18
389 WHEN $1 = 243 THEN 1.000000e+19
390 WHEN $1 = 244 THEN 1.000000e+20
391 WHEN $1 = 245 THEN 1.000000e+21
392 WHEN $1 = 246 THEN 1.000000e+22
393 WHEN $1 = 247 THEN 1.000000e+23
394 WHEN $1 = 248 THEN 1.000000e+24
395 WHEN $1 = 249 THEN 1.000000e+25
396 WHEN $1 = 250 THEN 1.000000e+26
397 WHEN $1 = 251 THEN 1.000000e+27
398 WHEN $1 = 252 THEN 1.000000e+28
399 WHEN $1 = 253 THEN 1.000000e+29
400 WHEN $1 = 254 THEN 1.000000e+30
401 WHEN $1 = 255 THEN 1.000000e+31
402 END INTO perc;
403 RETURN perc;
404END;
405$BODY$
406LANGUAGE plpgsql;
407-- +migrate StatementEnd
408
409-- +migrate StatementBegin
410CREATE FUNCTION set_tx()
411 RETURNS TRIGGER
412AS
413$BODY$
414DECLARE
415 _value NUMERIC;
416 _usd_update TIMESTAMP;
417 _tx_timestamp TIMESTAMP;
418BEGIN
419 IF NEW.is_l1 THEN
420 -- Validate
421 IF NEW.user_origin IS NULL OR
422 NEW.from_eth_addr IS NULL OR
423 NEW.from_bjj IS NULL OR
424 NEW.load_amount IS NULL OR
425 NEW.load_amount_f IS NULL OR
426 (NOT NEW.user_origin AND NEW.batch_num IS NULL) THEN -- If is Coordinator L1, must include batch_num
427 RAISE EXCEPTION 'Invalid L1 tx.';
428 END IF;
429 ELSE
430 -- Validate
431 IF NEW.batch_num IS NULL OR NEW.nonce IS NULL THEN
432 RAISE EXCEPTION 'Invalid L2 tx.';
433 END IF;
434 -- Set fee if it's null
435 IF NEW.fee IS NULL THEN
436 NEW.fee = (SELECT 0);
437 END IF;
438 -- Set token_id
439 NEW."token_id" = (SELECT token_id FROM account WHERE idx = NEW."from_idx");
440 -- Set from_{eth_addr,bjj}
441 SELECT INTO NEW."from_eth_addr", NEW."from_bjj" eth_addr, bjj FROM account WHERE idx = NEW.from_idx;
442 END IF;
443 -- Set USD related
444 SELECT INTO _value, _usd_update, _tx_timestamp
445 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;
446 IF _tx_timestamp - interval '24 hours' < _usd_update AND _tx_timestamp + interval '24 hours' > _usd_update THEN
447 NEW."amount_usd" = (SELECT _value * NEW.amount_f);
448 IF NOT NEW.is_l1 THEN
449 NEW."fee_usd" = (SELECT NEW."amount_usd" * fee_percentage(NEW.fee::NUMERIC));
450 ELSE
451 NEW."load_amount_usd" = (SELECT _value * NEW.load_amount_f);
452 END IF;
453 END IF;
454 -- Set to_{eth_addr,bjj}
455 IF NEW.to_idx > 255 THEN
456 SELECT INTO NEW."to_eth_addr", NEW."to_bjj" eth_addr, bjj FROM account WHERE idx = NEW.to_idx;
457 END IF;
458 RETURN NEW;
459END;
460$BODY$
461LANGUAGE plpgsql;
462-- +migrate StatementEnd
463CREATE TRIGGER trigger_set_tx BEFORE INSERT ON tx
464FOR EACH ROW EXECUTE PROCEDURE set_tx();
465
466-- +migrate StatementBegin
467CREATE FUNCTION forge_l1_user_txs()
468 RETURNS TRIGGER
469AS
470$BODY$
471BEGIN
472 IF NEW.forge_l1_txs_num IS NOT NULL THEN
473 UPDATE tx
474 SET item_id = nextval('tx_item_id'), batch_num = NEW.batch_num
475 WHERE id IN (
476 SELECT id FROM tx
477 WHERE user_origin AND NEW.forge_l1_txs_num = to_forge_l1_txs_num
478 ORDER BY position
479 FOR UPDATE
480 );
481 END IF;
482 RETURN NEW;
483END;
484$BODY$
485LANGUAGE plpgsql;
486-- +migrate StatementEnd
487CREATE TRIGGER trigger_forge_l1_txs AFTER INSERT ON batch
488FOR EACH ROW EXECUTE PROCEDURE forge_l1_user_txs();
489
490CREATE TABLE rollup_vars (
491 eth_block_num BIGINT PRIMARY KEY REFERENCES block (eth_block_num) ON DELETE CASCADE,
492 forge_l1_timeout BYTEA NOT NULL,
493 fee_l1_user_tx BYTEA NOT NULL,
494 fee_add_token BYTEA NOT NULL,
495 tokens_hez BYTEA NOT NULL,
496 governance BYTEA NOT NULL
497);
498
499CREATE TABLE consensus_vars (
500 eth_block_num BIGINT PRIMARY KEY REFERENCES block (eth_block_num) ON DELETE CASCADE,
501 slot_deadline INT NOT NULL,
502 close_auction_slots INT NOT NULL,
503 open_auction_slots INT NOT NULL,
504 min_bid_slots VARCHAR(200) NOT NULL,
505 outbidding INT NOT NULL,
506 donation_address BYTEA NOT NULL,
507 governance_address BYTEA NOT NULL,
508 allocation_ratio VARCHAR(200)
509);
510
511-- L2
512CREATE TABLE tx_pool (
513 tx_id BYTEA PRIMARY KEY,
514 from_idx BIGINT NOT NULL,
515 from_eth_addr BYTEA,
516 from_bjj BYTEA,
517 to_idx BIGINT,
518 to_eth_addr BYTEA,
519 to_bjj BYTEA,
520 token_id INT NOT NULL REFERENCES token (token_id) ON DELETE CASCADE,
521 amount BYTEA NOT NULL,
522 amount_f NUMERIC NOT NULL,
523 fee SMALLINT NOT NULL,
524 nonce BIGINT NOT NULL,
525 state CHAR(4) NOT NULL,
526 signature BYTEA NOT NULL,
527 timestamp TIMESTAMP WITHOUT TIME ZONE DEFAULT timezone('utc', now()),
528 batch_num BIGINT,
529 rq_from_idx BIGINT,
530 rq_to_idx BIGINT,
531 rq_to_eth_addr BYTEA,
532 rq_to_bjj BYTEA,
533 rq_token_id INT,
534 rq_amount BYTEA,
535 rq_fee SMALLINT,
536 rq_nonce BIGINT,
537 tx_type VARCHAR(40) NOT NULL
538);
539
540-- +migrate StatementBegin
541CREATE FUNCTION set_pool_tx()
542 RETURNS TRIGGER
543AS
544$BODY$
545BEGIN
546 SELECT INTO NEW."from_eth_addr", NEW."from_bjj" eth_addr, bjj FROM account WHERE idx = NEW."from_idx";
547 -- Set to_{eth_addr,bjj}
548 IF NEW.to_idx > 255 THEN
549 SELECT INTO NEW."to_eth_addr", NEW."to_bjj" eth_addr, bjj FROM account WHERE idx = NEW."to_idx";
550 END IF;
551 RETURN NEW;
552END;
553$BODY$
554LANGUAGE plpgsql;
555-- +migrate StatementEnd
556CREATE TRIGGER trigger_set_pool_tx BEFORE INSERT ON tx_pool
557FOR EACH ROW EXECUTE PROCEDURE set_pool_tx();
558
559CREATE TABLE account_creation_auth (
560 eth_addr BYTEA PRIMARY KEY,
561 bjj BYTEA NOT NULL,
562 signature BYTEA NOT NULL,
563 timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT timezone('utc', now())
564);
565
566-- +migrate Down
567DROP TABLE account_creation_auth;
568DROP TABLE tx_pool;
569DROP TABLE consensus_vars;
570DROP TABLE rollup_vars;
571DROP TABLE account;
572DROP TABLE tx;
573DROP TABLE token;
574DROP TABLE bid;
575DROP TABLE exit_tree;
576DROP TABLE batch;
577DROP TABLE coordinator;
578DROP TABLE block;