-
Notifications
You must be signed in to change notification settings - Fork 0
/
sqlite.go
111 lines (93 loc) · 1.93 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
package karma
import (
"database/sql"
"os"
"path/filepath"
_ "github.com/mattn/go-sqlite3" // sqlite db driver
)
// InitDB initializes the db and tables that we require
func InitDB(dataSourceName string) (*sql.DB, error) {
db, err := createDB(dataSourceName)
if err != nil {
return nil, err
}
err = schema(db)
if err != nil {
return nil, err
}
return db, nil
}
func createDB(dataSourceName string) (*sql.DB, error) {
// create the directories required for the database file
dir := filepath.Dir(dataSourceName)
err := os.MkdirAll(dir, os.ModeDir|0755)
if err != nil {
return nil, err
}
db, err := sql.Open("sqlite3", dataSourceName)
if err != nil {
return nil, err
}
return db, nil
}
func schema(db *sql.DB) error {
// karma table
if err := schemaKarma(db); err != nil {
return err
}
// usage table
if err := schemaUsage(db); err != nil {
return err
}
// daily usage table
if err := schemaDailyUsage(db); err != nil {
return err
}
return nil
}
func schemaKarma(db *sql.DB) error {
_, err := db.Exec(`
CREATE TABLE IF NOT EXISTS karma (
id INTEGER PRIMARY KEY,
team TEXT,
user TEXT,
karma INTEGER,
created_at TEXT,
updated_at TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_karma_team_user ON karma (team, user);
`)
return err
}
func schemaUsage(db *sql.DB) error {
_, err := db.Exec(`
CREATE TABLE IF NOT EXISTS usage (
command TEXT,
text TEXT,
enterprise TEXT,
team TEXT,
channel TEXT,
user TEXT,
created_at TEXT,
response TEXT,
response_type TEXT,
attachments TEXT
);
`)
return err
}
func schemaDailyUsage(db *sql.DB) error {
_, err := db.Exec(`
CREATE TABLE IF NOT EXISTS daily_usage (
team TEXT,
user TEXT,
daily TEXT,
usage INTEGER,
created_at TEXT,
updated_at TEXT,
PRIMARY KEY (team, user, daily)
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_daily_usage_team_user_daily ON daily_usage (team, user, daily);
`)
return err
}