-
Notifications
You must be signed in to change notification settings - Fork 0
/
sqlite.go
152 lines (127 loc) · 2.99 KB
/
sqlite.go
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
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
package db
import (
"database/sql"
"fmt"
_ "github.com/mattn/go-sqlite3"
"github.com/tatsster/albion_killboard/config"
)
func NewSqliteHandler() (*sql.DB, error) {
db, err := sql.Open("sqlite3", config.SQLITE_PATH)
if err != nil {
fmt.Println("fail to init database: ", err)
return nil, err
}
err = setupTables(db)
if err != nil {
fmt.Println("fail to setup tables: ", err)
return nil, err
}
return db, nil
}
func setupTables(db *sql.DB) error {
_, err := db.Exec(`CREATE TABLE IF NOT EXISTS members (
id TEXT PRIMARY KEY,
name TEXT,
last_kill DATETIME,
last_death DATETIME)`,
)
if err != nil {
return err
}
return nil
}
func UpdateMembers(db *sql.DB, members config.MemberInfo) error {
// Insert multiple values in a single transaction
tx, err := db.Begin()
if err != nil {
return err
}
stmt, err := tx.Prepare(`INSERT OR IGNORE INTO members (id, name) VALUES (?, ?)`)
if err != nil {
return err
}
defer stmt.Close()
for _, member := range members {
_, err = stmt.Exec(member.ID, member.Name)
if err != nil {
return err
}
}
err = tx.Commit()
if err != nil {
return err
}
return nil
}
func GetAllMemberID(db *sql.DB) ([]string, error) {
var (
memberIDs = make([]string, 0)
)
rows, err := db.Query("SELECT id FROM members ORDER BY id")
if err != nil {
return memberIDs, err
}
defer rows.Close()
for rows.Next() {
var memberID string
err := rows.Scan(&memberID)
if err != nil {
return memberIDs, err
}
memberIDs = append(memberIDs, memberID)
}
return memberIDs, rows.Err()
}
func GetMemberByID(db *sql.DB, ID string) (config.Member, error) {
var (
member config.Member
)
rows, err := db.Query("SELECT * FROM members WHERE id = ?", ID)
if err != nil {
return member, err
}
defer rows.Close()
if rows.Next() {
err = rows.Scan(&member.ID, &member.Name, &member.LastKill, &member.LastDeath)
if err != nil {
return member, err
}
} else {
return member, fmt.Errorf("no member found with ID: %s", ID)
}
return member, rows.Err()
}
func UpdateKillTime(db *sql.DB, kill config.Event) error {
sql := "UPDATE members SET last_kill = ? WHERE id = ?"
tx, err := db.Begin()
if err != nil {
return fmt.Errorf("start transaction error: %v", err)
}
updateKill, err := tx.Prepare(sql)
if err != nil {
return fmt.Errorf("prepare statement error: %v", err)
}
_, err = updateKill.Exec(kill.TimeStamp, kill.Killer.ID)
if err != nil {
return fmt.Errorf("execute sql error: %v", err)
}
updateKill.Close()
return tx.Commit()
}
func UpdatDeathTime(db *sql.DB, death config.Event) error {
sql := "UPDATE members SET last_death = ? WHERE id = ?"
tx, err := db.Begin()
if err != nil {
return fmt.Errorf("start transaction error: %v", err)
}
updateKill, err := tx.Prepare(sql)
if err != nil {
return fmt.Errorf("prepare statement error: %v", err)
}
_, err = updateKill.Exec(death.TimeStamp, death.Victim.ID)
if err != nil {
return fmt.Errorf("execute sql error: %v", err)
}
updateKill.Close()
return tx.Commit()
}