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
|
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(target string, since time.Time, num, offset int) (history []core.Message, err error) {
rows, err := db.Query(
`SELECT source, target, timestamp, content FROM messages
WHERE target = ? AND timestamp > ? LIMIT ? OFFSET ?`,
target, since.Unix(), num, offset,
)
return db.getHistory(rows, err)
}
func (db *Database) GetHistoryUser(user1, user2 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 (source = ? OR source = ? ) AND (target = ? OR target = ?) AND timestamp > ? LIMIT ? OFFSET ?`,
user1, user2, user1, user2, since.Unix(), num, offset,
)
return db.getHistory(rows, err)
}
func (db *Database) getHistory(rows *sql.Rows, err error) ([]core.Message, error) {
var history []core.Message
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
}
|
