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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
|
package main
import (
"database/sql"
"fmt"
"log"
"github.com/kisielk/sqlstruct"
)
const ORDER_PENDING = 0
const ORDER_CANCELLED = 1
type User struct {
Id int `sql:"id"`
Username string `sql:"username"`
Balance float64 `sql:"balance"`
}
type Order struct {
Id int `sql:"id"`
Value float64 `sql:"value"`
ReservedFee float64 `sql:"reserved_fee"`
Status int `sql:"status"`
}
func cancelOrder(id int, db *sql.DB) (err error) {
tx, err := db.Begin()
if err != nil {
return
}
var order Order
var user User
sql := fmt.Sprintf(`
SELECT %s, %s
FROM orders AS o
INNER JOIN users AS u ON o.buyer_id = u.id
WHERE o.id = ?
FOR UPDATE`,
sqlstruct.ColumnsAliased(order, "o"),
sqlstruct.ColumnsAliased(user, "u"))
// fetch order to cancel
rows, err := tx.Query(sql, id)
if err != nil {
tx.Rollback()
return
}
defer rows.Close()
// no rows, nothing to do
if !rows.Next() {
tx.Rollback()
return
}
// read order
err = sqlstruct.ScanAliased(&order, rows, "o")
if err != nil {
tx.Rollback()
return
}
// ensure order status
if order.Status != ORDER_PENDING {
tx.Rollback()
return
}
// read user
err = sqlstruct.ScanAliased(&user, rows, "u")
if err != nil {
tx.Rollback()
return
}
rows.Close() // manually close before other prepared statements
// refund order value
sql = "UPDATE users SET balance = balance + ? WHERE id = ?"
refundStmt, err := tx.Prepare(sql)
if err != nil {
tx.Rollback()
return
}
defer refundStmt.Close()
_, err = refundStmt.Exec(order.Value+order.ReservedFee, user.Id)
if err != nil {
tx.Rollback()
return
}
// update order status
order.Status = ORDER_CANCELLED
sql = "UPDATE orders SET status = ?, updated = NOW() WHERE id = ?"
orderUpdStmt, err := tx.Prepare(sql)
if err != nil {
tx.Rollback()
return
}
defer orderUpdStmt.Close()
_, err = orderUpdStmt.Exec(order.Status, order.Id)
if err != nil {
tx.Rollback()
return
}
return tx.Commit()
}
func main() {
// @NOTE: the real connection is not required for tests
db, err := sql.Open("mysql", "root:@/orders")
if err != nil {
log.Fatal(err)
}
defer db.Close()
err = cancelOrder(1, db)
if err != nil {
log.Fatal(err)
}
}
|
