forked from akrennmair/activitylog
/
db.go
291 lines (244 loc) · 8.05 KB
/
db.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
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
package main
import (
"bytes"
"code.google.com/p/go.crypto/pbkdf2"
"crypto/rand"
"crypto/sha256"
"database/sql"
"log"
"strconv"
)
type ActivityTypesGetter interface {
GetActivityTypesForUser(user_id int64) []ActivityType
}
type CredentialsVerifierActivityTypesGetter interface {
ActivityTypesGetter
VerifyCredentials(username, password string) (user_id int64, authenticated bool)
}
type ActivityAdder interface {
AddActivity(type_id int64, description string, user_id int64, is_public bool, latitude, longitude string) error
}
type ActivityTypeAdder interface {
AddActivityType(typename string, user_id int64, time_period bool) (ActivityType, error)
}
type ActivityTypeRenamer interface {
RenameActivityType(typename string, user_id int64, activity_type_id int64) error
}
type ActivityTypeDeleter interface {
DeleteActivityType(user_id int64, activity_type_id int64) error
}
type UserRegistrar interface {
RegisterUser(username, password string) error
}
type Database struct {
conn *sql.DB
flagNameToIdMapping map[string]int64
flagIdToNameMapping map[int64]string
}
const (
FLAG_TIME_PERIOD = "time_period"
FLAG_POINT_IN_TIME = "point_in_time"
)
func NewDatabase(conn *sql.DB) *Database {
db := &Database{conn: conn}
db.flagNameToIdMapping = make(map[string]int64)
db.flagIdToNameMapping = make(map[int64]string)
rows, err := db.conn.Query("SELECT id, name FROM flags")
if err != nil {
log.Fatalf("NewDatabase: loading flag names failed: %v", err)
}
for rows.Next() {
var id int64
var name string
if err = rows.Scan(&id, &name); err == nil {
db.flagNameToIdMapping[name] = id
db.flagIdToNameMapping[id] = name
} else {
log.Printf("NewDatabase: loading flag name in rows.Scan failed: %v", err)
}
}
return db
}
func (db *Database) RenameActivityType(typename string, user_id int64, activity_type_id int64) error {
_, err := db.conn.Exec("UPDATE activity_types SET name = ? WHERE user_id = ? AND id = ?", typename, user_id, activity_type_id)
if err != nil {
log.Printf("db.conn.Exec failed: %v", err)
}
return err
}
func (db *Database) AddActivityType(typename string, user_id int64, time_period bool) (activity_type ActivityType, err error) {
activity_type = ActivityType{Name: typename}
txn, err := db.conn.Begin()
if err != nil {
log.Printf("starting transaction failed: %v")
return activity_type, err
}
result, err := txn.Exec("INSERT INTO activity_types (name, user_id, active) VALUES (?, ?, 1)", typename, user_id)
if err != nil {
log.Printf("db.conn.Exec failed: %v", err)
txn.Rollback()
return activity_type, err
}
activity_type.Id, _ = result.LastInsertId()
// set flag time_period/point_in_time depending on time_period argument
flag_id := db.flagNameToIdMapping[FLAG_POINT_IN_TIME]
if time_period {
flag_id = db.flagNameToIdMapping[FLAG_TIME_PERIOD]
}
result, err = txn.Exec("INSERT INTO activity_type_flags (type_id, flag_id) VALUES (?, ?)", activity_type.Id, flag_id)
if err != nil {
log.Printf("inserting flag failed: %v", err)
txn.Rollback()
return activity_type, err
}
activity_type.TimePeriod = time_period
err = txn.Commit()
if err != nil {
log.Printf("committing transaction failed: %v", err)
}
return activity_type, err
}
func (db *Database) AddActivity(type_id int64, description string, user_id int64, is_public bool, lat, long string) error {
var latitude sql.NullFloat64
var longitude sql.NullFloat64
var err error
if latitude.Float64, err = strconv.ParseFloat(lat, 64); err != nil {
latitude.Valid = false
} else {
latitude.Valid = true
}
if longitude.Float64, err = strconv.ParseFloat(long, 64); err != nil {
longitude.Valid = false
} else {
longitude.Valid = true
}
public := 0
if is_public {
public = 1
}
txn, err := db.conn.Begin()
if err != nil {
log.Printf("Starting transaction failed: %v", err)
return err
}
row := txn.QueryRow("SELECT count(1) FROM activity_type_flags WHERE type_id = ? AND flag_id = ?", type_id, db.flagNameToIdMapping[FLAG_POINT_IN_TIME])
var point_in_time_count int64
if err = row.Scan(&point_in_time_count); err != nil {
log.Printf("row.Scan failed: %v", err)
txn.Rollback()
return err
}
result, err := txn.Exec("INSERT INTO activities (type_id, timestamp, description, user_id, public, latitude, longitude) VALUES (?, NOW(), ?, ?, ?, ?, ?)", type_id, description, user_id, public, latitude, longitude)
if err == nil {
activity_id, _ := result.LastInsertId()
if point_in_time_count != 0 {
_, err = txn.Exec("UPDATE activities SET end_timestamp = NOW() WHERE id = ?", activity_id)
if err != nil {
log.Printf("db.conn.Exec failed: %v", err)
}
}
}
if err != nil {
txn.Rollback()
} else {
err = txn.Commit()
if err != nil {
log.Printf("committing transaction failed: %v", err)
}
}
return err
}
func (db *Database) GetActivityTypesForUser(user_id int64) (activities []ActivityType) {
activities = []ActivityType{}
rows, err := db.conn.Query("SELECT id, name FROM activity_types WHERE user_id = ? AND active = 1", user_id)
if err != nil {
log.Printf("db.conn.Query failed: %v", err)
return
}
for rows.Next() {
var type_id int64
var name string
if err = rows.Scan(&type_id, &name); err == nil {
time_period := false
if rows_flags, err := db.conn.Query("SELECT flag_id FROM activity_type_flags WHERE type_id = ?", type_id); err == nil {
for rows_flags.Next() {
var flag_id int64
if err = rows.Scan(&flag_id); err == nil {
switch flag_id {
case db.flagNameToIdMapping[FLAG_TIME_PERIOD]:
time_period = true
case db.flagNameToIdMapping[FLAG_POINT_IN_TIME]:
time_period = false
}
}
}
}
activities = append(activities, ActivityType{Id: type_id, Name: name, TimePeriod: time_period})
}
}
return
}
func (db *Database) GetActivitiesForUser(user_id int64, limit uint, offset uint) ([]Activity, error) {
rows, err := db.conn.Query("SELECT type_id, timestamp, description, latitude, longitude FROM activities WHERE user_id = ? ORDER BY timestamp DESC LIMIT ? OFFSET ?", user_id, limit, offset)
if err != nil {
log.Printf("GetActivitiesForUser: %v", err)
return nil, err
}
activities := []Activity{}
for rows.Next() {
var type_id int64
var timestamp string
var description string
var longitude, latitude string
if err = rows.Scan(&type_id, ×tamp, &description, &latitude, &longitude); err == nil {
activity := Activity{TypeId: type_id, Timestamp: timestamp, Description: description}
if latitude != "" {
activity.Latitude = new(float64)
*activity.Latitude, _ = strconv.ParseFloat(latitude, 64)
}
if longitude != "" {
activity.Longitude = new(float64)
*activity.Longitude, _ = strconv.ParseFloat(longitude, 64)
}
activities = append(activities, activity)
} else {
log.Printf("rows.Scan failed: %v", err)
}
}
return activities, nil
}
func (db *Database) RegisterUser(username, password string) error {
salt, err := GenerateSalt()
if err != nil {
return err
}
password_hash := HashPassword([]byte(password), salt)
_, err = db.conn.Exec("INSERT INTO users (login, pwhash, salt) VALUES (?, ?, ?)", username, password_hash, salt)
if err != nil {
log.Printf("RegisterUser: %v", err)
}
return err
}
func (db *Database) VerifyCredentials(username, password string) (user_id int64, authenticated bool) {
row := db.conn.QueryRow("SELECT id, pwhash, salt FROM users WHERE login = ? LIMIT 1", username)
var db_hash []byte
var salt []byte
if err := row.Scan(&user_id, &db_hash, &salt); err != nil {
log.Printf("VerifyCredentials: %v", err)
return 0, false
}
password_hash := HashPassword([]byte(password), salt)
return user_id, bytes.Equal(password_hash, db_hash)
}
func (db *Database) DeleteActivityType(user_id int64, activity_type_id int64) error {
_, err := db.conn.Exec("UPDATE activity_types SET active = 0 WHERE user_id = ? AND id = ?", user_id, activity_type_id)
return err
}
func GenerateSalt() (data []byte, err error) {
data = make([]byte, 8)
_, err = rand.Read(data)
return
}
func HashPassword(password, salt []byte) []byte {
return pbkdf2.Key([]byte(password), salt, PBKDF2_ROUNDS, PBKDF2_SIZE, sha256.New)
}