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