/
xfer.sql
31 lines (19 loc) · 1.16 KB
/
xfer.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
BEGIN
-- save-columns: @sender_token, @country
SELECT c_token, country FROM customers WHERE id=@sender_id -- 2
-- save-columns: @sender_balance_token
SELECT b_token FROM balances WHERE c_token=@sender_token LIMIT 1 -- 3
-- save-columns: @receiver_token
SELECT c_token FROM customers WHERE id BETWEEN @receiver_id AND @receiver_id+100 AND country=@country LIMIT 1 -- 4
-- save-columns: @receiver_balance_token
SELECT b_token FROM balances WHERE c_token=@receiver_token LIMIT 1 -- 5
-- save-insert-id: @xfer_id
INSERT INTO xfers VALUES (NULL, @x_token, 100, 'USD', @sender_balance_token, @receiver_balance_token, 1, @c1, @c2, @c3, NOW(), NULL, NULL, 1, 0, NOW(), NOW()) -- 6
-- save-columns: @sender_balance_id, _
SELECT id, version FROM balances WHERE b_token=@sender_balance_token FOR UPDATE -- 7
UPDATE balances SET cents=cents-100, version=version+1 WHERE id=@sender_balance_id -- 8
-- save-columns: @receiver_balance_id, _
SELECT id, version FROM balances WHERE b_token=@receiver_balance_token FOR UPDATE -- 9
UPDATE balances SET cents=cents+100, version=version+1 WHERE id=@receiver_balance_id -- 10
UPDATE xfers SET t2=NOW(), c3='DONE' WHERE id=@xfer_id -- 11
COMMIT