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
|
package storage
import (
"database/sql"
"log"
"time"
"go.rctt.net/solec/core"
)
type Database struct {
*sql.DB
}
const initSql = `
CREATE TABLE IF NOT EXISTS messages
(id INTEGER NOT NULL PRIMARY KEY, source STRING, target STRING, timestamp INT, content STRING);
CREATE TABLE IF NOT EXISTS users
(name STRING NOT NULL PRIMARY KEY, pass STRING);
CREATE TABLE IF NOT EXISTS permissions
(user STRING, channel STRING, read INT, write INT, PRIMARY KEY (user, channel));
`
func InitDb(path string) (*Database, error) {
db, err := sql.Open("sqlite3", path)
if err != nil {
return nil, err
}
_, err = db.Exec(initSql)
return &Database{db}, nil
}
func (db *Database) AddMessage(msg core.Message) (err error) {
_, err = db.Exec(
"INSERT INTO messages (source, target, timestamp, content) VALUES (?, ?, ?, ?);",
msg.Source, msg.Target, msg.Timestamp.Unix(), msg.Content,
)
return err
}
func (db *Database) GetHistory(channel string, since time.Time, num int, offset int) (history []core.Message, err error) {
rows, err := db.Query(
`SELECT source, target, timestamp, content FROM messages WHERE target = ? AND timestamp > ? LIMIT ? OFFSET ?`,
channel, since.Unix(), num, offset,
)
defer func() {
if rows == nil {
return
}
if err := rows.Close(); err != nil {
log.Println("cannot close database row:", err)
}
}()
if err != nil {
return history, err
}
for rows.Next() {
var msg core.Message
var timestamp int64
if err := rows.Scan(&msg.Source, &msg.Target, ×tamp, &msg.Content); err != nil {
return history, err
}
msg.Timestamp = time.Unix(timestamp, 0)
history = append(history, msg)
}
return history, nil
}
func (db *Database) SetUser(user core.UserData) error {
_, err := db.Exec(
"INSERT OR REPLACE INTO users (name, pass) VALUES (?, ?);",
user.Name, user.Pass,
)
return err
}
func (db *Database) DelUser(name string) error {
_, err := db.Exec("DELETE FROM users WHERE name = ?", name)
return err
}
func (db *Database) GetUserPass(name string) (string, error) {
var pass string
err := db.QueryRow("SELECT pass FROM users WHERE name = ?", name).Scan(&pass)
if err != nil {
return "", err
}
return pass, nil
}
func (db *Database) SetPermission(perm core.PermissionData) error {
_, err := db.Exec(
"INSERT OR REPLACE INTO permissions (user, channel, read, write) VALUES (?, ?, ?, ?);",
perm.User, perm.Channel, perm.Read, perm.Write, perm.User, perm.Channel,
)
return err
}
func (db *Database) GetPermission(user, channel string) (core.PermissionData, error) {
var read, write int
err := db.QueryRow(
"SELECT read, write FROM permissions WHERE user = ? AND channel = ?", user, channel).
Scan(&read, &write)
if err != nil {
return core.PermissionData{}, err
}
return core.PermissionData{
User: user,
Channel: channel,
Read: itob(read),
Write: itob(write),
}, nil
}
func itob(v int) bool {
if v == 1 {
return true
}
return false
}
|
