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
|
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
(addr 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 (addr, pass) VALUES (?, ?);",
user.Name, user.Pass,
)
return err
}
func (db *Database) DelUser(addr string) error {
_, err := db.Exec("DELETE FROM users WHERE addr = ?", addr)
return err
}
func (db *Database) GetUserPass(addr string) (string, error) {
var pass string
err := db.QueryRow("SELECT pass FROM users WHERE addr = ?", addr).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 (db *Database) GetChannelUsers(channel string) (users []string, err error) {
rows, err := db.Query("SELECT user FROM permissions WHERE channel = ? AND write = 1;", channel)
defer func() {
if rows == nil {
return
}
if err := rows.Close(); err != nil {
log.Println("cannot close database row:", err)
}
}()
if err != nil {
return users, err
}
for rows.Next() {
var user string
if err := rows.Scan(&user); err != nil {
return users, err
}
users = append(users, user)
}
return users, nil
}
func itob(v int) bool {
if v == 1 {
return true
}
return false
}
|
