source: code/trunk/db_sqlite.go@ 568

Last change on this file since 568 was 568, checked in by contact, 4 years ago

Add per-user realname setting

This allows users to set a default realname used if the per-network
realname isn't set.

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