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