source: code/trunk/db.go@ 444

Last change on this file since 444 was 434, checked in by delthas, 4 years ago

Introduce Channel.{RelayDetached,ReattachOn,DetachAfter,DetachOn}

This adds several fields to the channel database schema and struct.
These fields will be used to add support for customizable message
relaying through BouncerServ, auto-reattaching, auto-detaching.

  • RelayDetached is a filter for which notices to relay through BouncerServ for detached channels.
  • ReattachOn is a filter for which messages to trigger a channel reattach on.
  • DetachAfter is the duration after which to automatically detach a channel if no matching messages are received.
  • DetachOn is a filter for which messages will reset the auto-detach timer.
File size: 13.6 KB
Line 
1package soju
2
3import (
4 "database/sql"
5 "fmt"
6 "math"
7 "strings"
8 "sync"
9 "time"
10
11 _ "github.com/mattn/go-sqlite3"
12)
13
14type User struct {
15 ID int64
16 Username string
17 Password string // hashed
18 Admin bool
19}
20
21type SASL struct {
22 Mechanism string
23
24 Plain struct {
25 Username string
26 Password string
27 }
28
29 // TLS client certificate authentication.
30 External struct {
31 // X.509 certificate in DER form.
32 CertBlob []byte
33 // PKCS#8 private key in DER form.
34 PrivKeyBlob []byte
35 }
36}
37
38type Network struct {
39 ID int64
40 Name string
41 Addr string
42 Nick string
43 Username string
44 Realname string
45 Pass string
46 ConnectCommands []string
47 SASL SASL
48}
49
50func (net *Network) GetName() string {
51 if net.Name != "" {
52 return net.Name
53 }
54 return net.Addr
55}
56
57type MessageFilter int
58
59const (
60 // TODO: use customizable user defaults for FilterDefault
61 FilterDefault MessageFilter = iota
62 FilterNone
63 FilterHighlight
64 FilterMessage
65)
66
67func parseFilter(filter string) (MessageFilter, error) {
68 switch filter {
69 case "default":
70 return FilterDefault, nil
71 case "none":
72 return FilterNone, nil
73 case "highlight":
74 return FilterHighlight, nil
75 case "message":
76 return FilterMessage, nil
77 }
78 return 0, fmt.Errorf("unknown filter: %q", filter)
79}
80
81type Channel struct {
82 ID int64
83 Name string
84 Key string
85 Detached bool
86
87 RelayDetached MessageFilter
88 ReattachOn MessageFilter
89 DetachAfter time.Duration
90 DetachOn MessageFilter
91}
92
93const schema = `
94CREATE TABLE User (
95 id INTEGER PRIMARY KEY,
96 username VARCHAR(255) NOT NULL UNIQUE,
97 password VARCHAR(255),
98 admin INTEGER NOT NULL DEFAULT 0
99);
100
101CREATE TABLE Network (
102 id INTEGER PRIMARY KEY,
103 name VARCHAR(255),
104 user INTEGER NOT NULL,
105 addr VARCHAR(255) NOT NULL,
106 nick VARCHAR(255) NOT NULL,
107 username VARCHAR(255),
108 realname VARCHAR(255),
109 pass VARCHAR(255),
110 connect_commands VARCHAR(1023),
111 sasl_mechanism VARCHAR(255),
112 sasl_plain_username VARCHAR(255),
113 sasl_plain_password VARCHAR(255),
114 sasl_external_cert BLOB DEFAULT NULL,
115 sasl_external_key BLOB DEFAULT NULL,
116 FOREIGN KEY(user) REFERENCES User(id),
117 UNIQUE(user, addr, nick),
118 UNIQUE(user, name)
119);
120
121CREATE TABLE Channel (
122 id INTEGER PRIMARY KEY,
123 network INTEGER NOT NULL,
124 name VARCHAR(255) NOT NULL,
125 key VARCHAR(255),
126 detached INTEGER NOT NULL DEFAULT 0,
127 relay_detached INTEGER NOT NULL DEFAULT 0,
128 reattach_on INTEGER NOT NULL DEFAULT 0,
129 detach_after INTEGER NOT NULL DEFAULT 0,
130 detach_on INTEGER NOT NULL DEFAULT 0,
131 FOREIGN KEY(network) REFERENCES Network(id),
132 UNIQUE(network, name)
133);
134`
135
136var migrations = []string{
137 "", // migration #0 is reserved for schema initialization
138 "ALTER TABLE Network ADD COLUMN connect_commands VARCHAR(1023)",
139 "ALTER TABLE Channel ADD COLUMN detached INTEGER NOT NULL DEFAULT 0",
140 "ALTER TABLE Network ADD COLUMN sasl_external_cert BLOB DEFAULT NULL",
141 "ALTER TABLE Network ADD COLUMN sasl_external_key BLOB DEFAULT NULL",
142 "ALTER TABLE User ADD COLUMN admin INTEGER NOT NULL DEFAULT 0",
143 `
144 CREATE TABLE UserNew (
145 id INTEGER PRIMARY KEY,
146 username VARCHAR(255) NOT NULL UNIQUE,
147 password VARCHAR(255),
148 admin INTEGER NOT NULL DEFAULT 0
149 );
150 INSERT INTO UserNew SELECT rowid, username, password, admin FROM User;
151 DROP TABLE User;
152 ALTER TABLE UserNew RENAME TO User;
153 `,
154 `
155 CREATE TABLE NetworkNew (
156 id INTEGER PRIMARY KEY,
157 name VARCHAR(255),
158 user INTEGER NOT NULL,
159 addr VARCHAR(255) NOT NULL,
160 nick VARCHAR(255) NOT NULL,
161 username VARCHAR(255),
162 realname VARCHAR(255),
163 pass VARCHAR(255),
164 connect_commands VARCHAR(1023),
165 sasl_mechanism VARCHAR(255),
166 sasl_plain_username VARCHAR(255),
167 sasl_plain_password VARCHAR(255),
168 sasl_external_cert BLOB DEFAULT NULL,
169 sasl_external_key BLOB DEFAULT NULL,
170 FOREIGN KEY(user) REFERENCES User(id),
171 UNIQUE(user, addr, nick),
172 UNIQUE(user, name)
173 );
174 INSERT INTO NetworkNew
175 SELECT Network.id, name, User.id as user, addr, nick,
176 Network.username, realname, pass, connect_commands,
177 sasl_mechanism, sasl_plain_username, sasl_plain_password,
178 sasl_external_cert, sasl_external_key
179 FROM Network
180 JOIN User ON Network.user = User.username;
181 DROP TABLE Network;
182 ALTER TABLE NetworkNew RENAME TO Network;
183 `,
184 `
185 ALTER TABLE Channel ADD COLUMN relay_detached INTEGER NOT NULL DEFAULT 0;
186 ALTER TABLE Channel ADD COLUMN reattach_on INTEGER NOT NULL DEFAULT 0;
187 ALTER TABLE Channel ADD COLUMN detach_after INTEGER NOT NULL DEFAULT 0;
188 ALTER TABLE Channel ADD COLUMN detach_on INTEGER NOT NULL DEFAULT 0;
189 `,
190}
191
192type DB struct {
193 lock sync.RWMutex
194 db *sql.DB
195}
196
197func OpenSQLDB(driver, source string) (*DB, error) {
198 sqlDB, err := sql.Open(driver, source)
199 if err != nil {
200 return nil, err
201 }
202
203 db := &DB{db: sqlDB}
204 if err := db.upgrade(); err != nil {
205 return nil, err
206 }
207
208 return db, nil
209}
210
211func (db *DB) Close() error {
212 db.lock.Lock()
213 defer db.lock.Unlock()
214 return db.db.Close()
215}
216
217func (db *DB) upgrade() error {
218 db.lock.Lock()
219 defer db.lock.Unlock()
220
221 var version int
222 if err := db.db.QueryRow("PRAGMA user_version").Scan(&version); err != nil {
223 return fmt.Errorf("failed to query schema version: %v", err)
224 }
225
226 if version == len(migrations) {
227 return nil
228 } else if version > len(migrations) {
229 return fmt.Errorf("soju (version %d) older than schema (version %d)", len(migrations), version)
230 }
231
232 tx, err := db.db.Begin()
233 if err != nil {
234 return err
235 }
236 defer tx.Rollback()
237
238 if version == 0 {
239 if _, err := tx.Exec(schema); err != nil {
240 return fmt.Errorf("failed to initialize schema: %v", err)
241 }
242 } else {
243 for i := version; i < len(migrations); i++ {
244 if _, err := tx.Exec(migrations[i]); err != nil {
245 return fmt.Errorf("failed to execute migration #%v: %v", i, err)
246 }
247 }
248 }
249
250 // For some reason prepared statements don't work here
251 _, err = tx.Exec(fmt.Sprintf("PRAGMA user_version = %d", len(migrations)))
252 if err != nil {
253 return fmt.Errorf("failed to bump schema version: %v", err)
254 }
255
256 return tx.Commit()
257}
258
259func toNullString(s string) sql.NullString {
260 return sql.NullString{
261 String: s,
262 Valid: s != "",
263 }
264}
265
266func (db *DB) ListUsers() ([]User, error) {
267 db.lock.RLock()
268 defer db.lock.RUnlock()
269
270 rows, err := db.db.Query("SELECT id, username, password, admin FROM User")
271 if err != nil {
272 return nil, err
273 }
274 defer rows.Close()
275
276 var users []User
277 for rows.Next() {
278 var user User
279 var password sql.NullString
280 if err := rows.Scan(&user.ID, &user.Username, &password, &user.Admin); err != nil {
281 return nil, err
282 }
283 user.Password = password.String
284 users = append(users, user)
285 }
286 if err := rows.Err(); err != nil {
287 return nil, err
288 }
289
290 return users, nil
291}
292
293func (db *DB) GetUser(username string) (*User, error) {
294 db.lock.RLock()
295 defer db.lock.RUnlock()
296
297 user := &User{Username: username}
298
299 var password sql.NullString
300 row := db.db.QueryRow("SELECT id, password, admin FROM User WHERE username = ?", username)
301 if err := row.Scan(&user.ID, &password, &user.Admin); err != nil {
302 return nil, err
303 }
304 user.Password = password.String
305 return user, nil
306}
307
308func (db *DB) StoreUser(user *User) error {
309 db.lock.Lock()
310 defer db.lock.Unlock()
311
312 password := toNullString(user.Password)
313
314 var err error
315 if user.ID != 0 {
316 _, err = db.db.Exec("UPDATE User SET password = ?, admin = ? WHERE username = ?",
317 password, user.Admin, user.Username)
318 } else {
319 var res sql.Result
320 res, err = db.db.Exec("INSERT INTO User(username, password, admin) VALUES (?, ?, ?)",
321 user.Username, password, user.Admin)
322 if err != nil {
323 return err
324 }
325 user.ID, err = res.LastInsertId()
326 }
327
328 return err
329}
330
331func (db *DB) DeleteUser(id int64) error {
332 db.lock.Lock()
333 defer db.lock.Unlock()
334
335 tx, err := db.db.Begin()
336 if err != nil {
337 return err
338 }
339 defer tx.Rollback()
340
341 _, err = tx.Exec(`DELETE FROM Channel
342 WHERE id IN (
343 SELECT Channel.id
344 FROM Channel
345 JOIN Network ON Channel.network = Network.id
346 WHERE Network.user = ?
347 )`, id)
348 if err != nil {
349 return err
350 }
351
352 _, err = tx.Exec("DELETE FROM Network WHERE user = ?", id)
353 if err != nil {
354 return err
355 }
356
357 _, err = tx.Exec("DELETE FROM User WHERE id = ?", id)
358 if err != nil {
359 return err
360 }
361
362 return tx.Commit()
363}
364
365func (db *DB) ListNetworks(userID int64) ([]Network, error) {
366 db.lock.RLock()
367 defer db.lock.RUnlock()
368
369 rows, err := db.db.Query(`SELECT id, name, addr, nick, username, realname, pass,
370 connect_commands, sasl_mechanism, sasl_plain_username, sasl_plain_password,
371 sasl_external_cert, sasl_external_key
372 FROM Network
373 WHERE user = ?`,
374 userID)
375 if err != nil {
376 return nil, err
377 }
378 defer rows.Close()
379
380 var networks []Network
381 for rows.Next() {
382 var net Network
383 var name, username, realname, pass, connectCommands sql.NullString
384 var saslMechanism, saslPlainUsername, saslPlainPassword sql.NullString
385 err := rows.Scan(&net.ID, &name, &net.Addr, &net.Nick, &username, &realname,
386 &pass, &connectCommands, &saslMechanism, &saslPlainUsername, &saslPlainPassword,
387 &net.SASL.External.CertBlob, &net.SASL.External.PrivKeyBlob)
388 if err != nil {
389 return nil, err
390 }
391 net.Name = name.String
392 net.Username = username.String
393 net.Realname = realname.String
394 net.Pass = pass.String
395 if connectCommands.Valid {
396 net.ConnectCommands = strings.Split(connectCommands.String, "\r\n")
397 }
398 net.SASL.Mechanism = saslMechanism.String
399 net.SASL.Plain.Username = saslPlainUsername.String
400 net.SASL.Plain.Password = saslPlainPassword.String
401 networks = append(networks, net)
402 }
403 if err := rows.Err(); err != nil {
404 return nil, err
405 }
406
407 return networks, nil
408}
409
410func (db *DB) StoreNetwork(userID int64, network *Network) error {
411 db.lock.Lock()
412 defer db.lock.Unlock()
413
414 netName := toNullString(network.Name)
415 netUsername := toNullString(network.Username)
416 realname := toNullString(network.Realname)
417 pass := toNullString(network.Pass)
418 connectCommands := toNullString(strings.Join(network.ConnectCommands, "\r\n"))
419
420 var saslMechanism, saslPlainUsername, saslPlainPassword sql.NullString
421 if network.SASL.Mechanism != "" {
422 saslMechanism = toNullString(network.SASL.Mechanism)
423 switch network.SASL.Mechanism {
424 case "PLAIN":
425 saslPlainUsername = toNullString(network.SASL.Plain.Username)
426 saslPlainPassword = toNullString(network.SASL.Plain.Password)
427 network.SASL.External.CertBlob = nil
428 network.SASL.External.PrivKeyBlob = nil
429 case "EXTERNAL":
430 // keep saslPlain* nil
431 default:
432 return fmt.Errorf("soju: cannot store network: unsupported SASL mechanism %q", network.SASL.Mechanism)
433 }
434 }
435
436 var err error
437 if network.ID != 0 {
438 _, err = db.db.Exec(`UPDATE Network
439 SET name = ?, addr = ?, nick = ?, username = ?, realname = ?, pass = ?, connect_commands = ?,
440 sasl_mechanism = ?, sasl_plain_username = ?, sasl_plain_password = ?,
441 sasl_external_cert = ?, sasl_external_key = ?
442 WHERE id = ?`,
443 netName, network.Addr, network.Nick, netUsername, realname, pass, connectCommands,
444 saslMechanism, saslPlainUsername, saslPlainPassword,
445 network.SASL.External.CertBlob, network.SASL.External.PrivKeyBlob,
446 network.ID)
447 } else {
448 var res sql.Result
449 res, err = db.db.Exec(`INSERT INTO Network(user, name, addr, nick, username,
450 realname, pass, connect_commands, sasl_mechanism, sasl_plain_username,
451 sasl_plain_password, sasl_external_cert, sasl_external_key)
452 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
453 userID, netName, network.Addr, network.Nick, netUsername, realname, pass, connectCommands,
454 saslMechanism, saslPlainUsername, saslPlainPassword, network.SASL.External.CertBlob,
455 network.SASL.External.PrivKeyBlob)
456 if err != nil {
457 return err
458 }
459 network.ID, err = res.LastInsertId()
460 }
461 return err
462}
463
464func (db *DB) DeleteNetwork(id int64) error {
465 db.lock.Lock()
466 defer db.lock.Unlock()
467
468 tx, err := db.db.Begin()
469 if err != nil {
470 return err
471 }
472 defer tx.Rollback()
473
474 _, err = tx.Exec("DELETE FROM Channel WHERE network = ?", id)
475 if err != nil {
476 return err
477 }
478
479 _, err = tx.Exec("DELETE FROM Network WHERE id = ?", id)
480 if err != nil {
481 return err
482 }
483
484 return tx.Commit()
485}
486
487func (db *DB) ListChannels(networkID int64) ([]Channel, error) {
488 db.lock.RLock()
489 defer db.lock.RUnlock()
490
491 rows, err := db.db.Query(`SELECT id, name, key, detached, relay_detached, reattach_on, detach_after, detach_on
492 FROM Channel
493 WHERE network = ?`, networkID)
494 if err != nil {
495 return nil, err
496 }
497 defer rows.Close()
498
499 var channels []Channel
500 for rows.Next() {
501 var ch Channel
502 var key sql.NullString
503 var detachAfter int64
504 if err := rows.Scan(&ch.ID, &ch.Name, &key, &ch.Detached, &ch.RelayDetached, &ch.ReattachOn, &detachAfter, &ch.DetachOn); err != nil {
505 return nil, err
506 }
507 ch.Key = key.String
508 ch.DetachAfter = time.Duration(detachAfter) * time.Second
509 channels = append(channels, ch)
510 }
511 if err := rows.Err(); err != nil {
512 return nil, err
513 }
514
515 return channels, nil
516}
517
518func (db *DB) StoreChannel(networkID int64, ch *Channel) error {
519 db.lock.Lock()
520 defer db.lock.Unlock()
521
522 key := toNullString(ch.Key)
523 detachAfter := int64(math.Ceil(ch.DetachAfter.Seconds()))
524
525 var err error
526 if ch.ID != 0 {
527 _, err = db.db.Exec(`UPDATE Channel
528 SET network = ?, name = ?, key = ?, detached = ?, relay_detached = ?, reattach_on = ?, detach_after = ?, detach_on = ?
529 WHERE id = ?`,
530 networkID, ch.Name, key, ch.Detached, ch.RelayDetached, ch.ReattachOn, detachAfter, ch.DetachOn, ch.ID)
531 } else {
532 var res sql.Result
533 res, err = db.db.Exec(`INSERT INTO Channel(network, name, key, detached, relay_detached, reattach_on, detach_after, detach_on)
534 VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
535 networkID, ch.Name, key, ch.Detached, ch.RelayDetached, ch.ReattachOn, detachAfter, ch.DetachOn)
536 if err != nil {
537 return err
538 }
539 ch.ID, err = res.LastInsertId()
540 }
541 return err
542}
543
544func (db *DB) DeleteChannel(id int64) error {
545 db.lock.Lock()
546 defer db.lock.Unlock()
547
548 _, err := db.db.Exec("DELETE FROM Channel WHERE id = ?", id)
549 return err
550}
Note: See TracBrowser for help on using the repository browser.