-
Notifications
You must be signed in to change notification settings - Fork 0
/
db.go
95 lines (77 loc) · 2.01 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
package db
import (
"database/sql"
"time"
_ "github.com/mattn/go-sqlite3"
)
var ConnectionString string
var db *sql.DB
var insertAccessStatement *sql.Stmt
var upsertAnnounceStatement *sql.Stmt
var announceStatement *sql.Stmt
func prepare() error {
if db != nil {
return nil
}
var err error
db, err = sql.Open("sqlite3", ConnectionString)
if err != nil {
return err
}
_, err = db.Exec("CREATE TABLE IF NOT EXISTS access (fob TEXT NOT NULL, door TEXT NOT NULL, ts BIGINT NOT NULL, allowed BOOL NOT NULL)")
if err != nil {
return err
}
_, err = db.Exec("CREATE TABLE IF NOT EXISTS announce_fobs (fob TEXT NOT NULL PRIMARY KEY, nickname TEXT NOT NULL, announce BOOL NOT NULL)")
if err != nil {
return err
}
insertAccessStatement, err = db.Prepare("INSERT INTO access (fob, door, ts, allowed) VALUES (?, ?, ?, ?)")
if err != nil {
return err
}
upsertAnnounceStatement, err = db.Prepare("INSERT INTO announce_fobs (fob, nickname, announce) VALUES (?, ?, ?) ON CONFLICT (fob) DO UPDATE SET announce = ?, nickname = ?")
if err != nil {
return err
}
announceStatement, err = db.Prepare("SELECT announce, nickname FROM announce_fobs WHERE fob = ?")
if err != nil {
return err
}
return nil
}
func Stop() {
_ = db.Close()
}
func InsertAccess(door string, fob string, time time.Time, granted bool) error {
if err := prepare(); err != nil {
return err
}
if _, err := insertAccessStatement.Exec(fob, door, time.UnixMilli(), granted); err != nil {
return err
}
return nil
}
func UpsertAnnounce(fob string, announce bool, nickname string) error {
if err := prepare(); err != nil {
return err
}
if _, err := upsertAnnounceStatement.Exec(fob, announce, nickname, announce, nickname); err != nil {
return err
}
return nil
}
func IsAnnounceEnabled(fob string) (bool, string, error) {
if err := prepare(); err != nil {
return false, "", err
}
r := announceStatement.QueryRow(fob)
var b bool
var s string
err := r.Scan(&b, &s)
if err != nil {
return false, "", err
} else {
return b, s, nil
}
}