source: code/trunk/db_sqlite.go@ 593

Last change on this file since 593 was 589, checked in by hubert, 4 years ago

Use NULL-tolerant comparison for DeliveryReceipts

Since NULL = NULL is always FALSE, this query needs to use IS instead.
This should fix the flood of DeliveryReceipts in the DB.

See ​https://www.sqlite.org/lang_expr.html

The IS and IS NOT operators work like = and != except when one or both
of the operands are NULL. In this case, if both operands are NULL,
then the IS operator evaluates to 1 (true) and the IS NOT operator
evaluates to 0 (false). If one operand is NULL and the other is not,
then the IS operator evaluates to 0 (false) and the IS NOT operator is
1 (true). It is not possible for an IS or IS NOT expression to
evaluate to NULL.
File size: 15.1 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
14const sqliteSchema = `
15CREATE TABLE User (
16 id INTEGER PRIMARY KEY,
17 username VARCHAR(255) NOT NULL UNIQUE,
18 password VARCHAR(255),
19 admin INTEGER NOT NULL DEFAULT 0,
20 realname VARCHAR(255)
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,
38 enabled INTEGER NOT NULL DEFAULT 1,
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)",
136 "ALTER TABLE Network ADD COLUMN enabled INTEGER NOT NULL DEFAULT 1",
137 "ALTER TABLE User ADD COLUMN realname VARCHAR(255)",
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 sqlSqliteDB.Close()
154 return nil, err
155 }
156
157 return db, nil
158}
159
160func (db *SqliteDB) Close() error {
161 db.lock.Lock()
162 defer db.lock.Unlock()
163 return db.db.Close()
164}
165
166func (db *SqliteDB) upgrade() error {
167 db.lock.Lock()
168 defer db.lock.Unlock()
169
170 var version int
171 if err := db.db.QueryRow("PRAGMA user_version").Scan(&version); err != nil {
172 return fmt.Errorf("failed to query schema version: %v", err)
173 }
174
175 if version == len(sqliteMigrations) {
176 return nil
177 } else if version > len(sqliteMigrations) {
178 return fmt.Errorf("soju (version %d) older than schema (version %d)", len(sqliteMigrations), version)
179 }
180
181 tx, err := db.db.Begin()
182 if err != nil {
183 return err
184 }
185 defer tx.Rollback()
186
187 if version == 0 {
188 if _, err := tx.Exec(sqliteSchema); err != nil {
189 return fmt.Errorf("failed to initialize schema: %v", err)
190 }
191 } else {
192 for i := version; i < len(sqliteMigrations); i++ {
193 if _, err := tx.Exec(sqliteMigrations[i]); err != nil {
194 return fmt.Errorf("failed to execute migration #%v: %v", i, err)
195 }
196 }
197 }
198
199 // For some reason prepared statements don't work here
200 _, err = tx.Exec(fmt.Sprintf("PRAGMA user_version = %d", len(sqliteMigrations)))
201 if err != nil {
202 return fmt.Errorf("failed to bump schema version: %v", err)
203 }
204
205 return tx.Commit()
206}
207
208func toNullString(s string) sql.NullString {
209 return sql.NullString{
210 String: s,
211 Valid: s != "",
212 }
213}
214
215func (db *SqliteDB) ListUsers() ([]User, error) {
216 db.lock.RLock()
217 defer db.lock.RUnlock()
218
219 rows, err := db.db.Query("SELECT id, username, password, admin FROM User")
220 if err != nil {
221 return nil, err
222 }
223 defer rows.Close()
224
225 var users []User
226 for rows.Next() {
227 var user User
228 var password sql.NullString
229 if err := rows.Scan(&user.ID, &user.Username, &password, &user.Admin); err != nil {
230 return nil, err
231 }
232 user.Password = password.String
233 users = append(users, user)
234 }
235 if err := rows.Err(); err != nil {
236 return nil, err
237 }
238
239 return users, nil
240}
241
242func (db *SqliteDB) GetUser(username string) (*User, error) {
243 db.lock.RLock()
244 defer db.lock.RUnlock()
245
246 user := &User{Username: username}
247
248 var password, realname sql.NullString
249 row := db.db.QueryRow("SELECT id, password, admin, realname FROM User WHERE username = ?", username)
250 if err := row.Scan(&user.ID, &password, &user.Admin, &realname); err != nil {
251 return nil, err
252 }
253 user.Password = password.String
254 user.Realname = realname.String
255 return user, nil
256}
257
258func (db *SqliteDB) StoreUser(user *User) error {
259 db.lock.Lock()
260 defer db.lock.Unlock()
261
262 password := toNullString(user.Password)
263 realname := toNullString(user.Realname)
264
265 var err error
266 if user.ID != 0 {
267 _, err = db.db.Exec("UPDATE User SET password = ?, admin = ?, realname = ? WHERE username = ?",
268 password, user.Admin, realname, user.Username)
269 } else {
270 var res sql.Result
271 res, err = db.db.Exec("INSERT INTO User(username, password, admin, realname) VALUES (?, ?, ?, ?)",
272 user.Username, password, user.Admin, realname)
273 if err != nil {
274 return err
275 }
276 user.ID, err = res.LastInsertId()
277 }
278
279 return err
280}
281
282func (db *SqliteDB) DeleteUser(id int64) error {
283 db.lock.Lock()
284 defer db.lock.Unlock()
285
286 tx, err := db.db.Begin()
287 if err != nil {
288 return err
289 }
290 defer tx.Rollback()
291
292 _, err = tx.Exec(`DELETE FROM Channel
293 WHERE id IN (
294 SELECT Channel.id
295 FROM Channel
296 JOIN Network ON Channel.network = Network.id
297 WHERE Network.user = ?
298 )`, id)
299 if err != nil {
300 return err
301 }
302
303 _, err = tx.Exec("DELETE FROM Network WHERE user = ?", id)
304 if err != nil {
305 return err
306 }
307
308 _, err = tx.Exec("DELETE FROM User WHERE id = ?", id)
309 if err != nil {
310 return err
311 }
312
313 return tx.Commit()
314}
315
316func (db *SqliteDB) ListNetworks(userID int64) ([]Network, error) {
317 db.lock.RLock()
318 defer db.lock.RUnlock()
319
320 rows, err := db.db.Query(`SELECT id, name, addr, nick, username, realname, pass,
321 connect_commands, sasl_mechanism, sasl_plain_username, sasl_plain_password,
322 sasl_external_cert, sasl_external_key, enabled
323 FROM Network
324 WHERE user = ?`,
325 userID)
326 if err != nil {
327 return nil, err
328 }
329 defer rows.Close()
330
331 var networks []Network
332 for rows.Next() {
333 var net Network
334 var name, username, realname, pass, connectCommands sql.NullString
335 var saslMechanism, saslPlainUsername, saslPlainPassword sql.NullString
336 err := rows.Scan(&net.ID, &name, &net.Addr, &net.Nick, &username, &realname,
337 &pass, &connectCommands, &saslMechanism, &saslPlainUsername, &saslPlainPassword,
338 &net.SASL.External.CertBlob, &net.SASL.External.PrivKeyBlob, &net.Enabled)
339 if err != nil {
340 return nil, err
341 }
342 net.Name = name.String
343 net.Username = username.String
344 net.Realname = realname.String
345 net.Pass = pass.String
346 if connectCommands.Valid {
347 net.ConnectCommands = strings.Split(connectCommands.String, "\r\n")
348 }
349 net.SASL.Mechanism = saslMechanism.String
350 net.SASL.Plain.Username = saslPlainUsername.String
351 net.SASL.Plain.Password = saslPlainPassword.String
352 networks = append(networks, net)
353 }
354 if err := rows.Err(); err != nil {
355 return nil, err
356 }
357
358 return networks, nil
359}
360
361func (db *SqliteDB) StoreNetwork(userID int64, network *Network) error {
362 db.lock.Lock()
363 defer db.lock.Unlock()
364
365 netName := toNullString(network.Name)
366 netUsername := toNullString(network.Username)
367 realname := toNullString(network.Realname)
368 pass := toNullString(network.Pass)
369 connectCommands := toNullString(strings.Join(network.ConnectCommands, "\r\n"))
370
371 var saslMechanism, saslPlainUsername, saslPlainPassword sql.NullString
372 if network.SASL.Mechanism != "" {
373 saslMechanism = toNullString(network.SASL.Mechanism)
374 switch network.SASL.Mechanism {
375 case "PLAIN":
376 saslPlainUsername = toNullString(network.SASL.Plain.Username)
377 saslPlainPassword = toNullString(network.SASL.Plain.Password)
378 network.SASL.External.CertBlob = nil
379 network.SASL.External.PrivKeyBlob = nil
380 case "EXTERNAL":
381 // keep saslPlain* nil
382 default:
383 return fmt.Errorf("soju: cannot store network: unsupported SASL mechanism %q", network.SASL.Mechanism)
384 }
385 }
386
387 var err error
388 if network.ID != 0 {
389 _, err = db.db.Exec(`UPDATE Network
390 SET name = ?, addr = ?, nick = ?, username = ?, realname = ?, pass = ?, connect_commands = ?,
391 sasl_mechanism = ?, sasl_plain_username = ?, sasl_plain_password = ?,
392 sasl_external_cert = ?, sasl_external_key = ?, enabled = ?
393 WHERE id = ?`,
394 netName, network.Addr, network.Nick, netUsername, realname, pass, connectCommands,
395 saslMechanism, saslPlainUsername, saslPlainPassword,
396 network.SASL.External.CertBlob, network.SASL.External.PrivKeyBlob, network.Enabled,
397 network.ID)
398 } else {
399 var res sql.Result
400 res, err = db.db.Exec(`INSERT INTO Network(user, name, addr, nick, username,
401 realname, pass, connect_commands, sasl_mechanism, sasl_plain_username,
402 sasl_plain_password, sasl_external_cert, sasl_external_key, enabled)
403 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
404 userID, netName, network.Addr, network.Nick, netUsername, realname, pass, connectCommands,
405 saslMechanism, saslPlainUsername, saslPlainPassword, network.SASL.External.CertBlob,
406 network.SASL.External.PrivKeyBlob, network.Enabled)
407 if err != nil {
408 return err
409 }
410 network.ID, err = res.LastInsertId()
411 }
412 return err
413}
414
415func (db *SqliteDB) DeleteNetwork(id int64) error {
416 db.lock.Lock()
417 defer db.lock.Unlock()
418
419 tx, err := db.db.Begin()
420 if err != nil {
421 return err
422 }
423 defer tx.Rollback()
424
425 _, err = tx.Exec("DELETE FROM Channel WHERE network = ?", id)
426 if err != nil {
427 return err
428 }
429
430 _, err = tx.Exec("DELETE FROM Network WHERE id = ?", id)
431 if err != nil {
432 return err
433 }
434
435 return tx.Commit()
436}
437
438func (db *SqliteDB) ListChannels(networkID int64) ([]Channel, error) {
439 db.lock.RLock()
440 defer db.lock.RUnlock()
441
442 rows, err := db.db.Query(`SELECT
443 id, name, key, detached, detached_internal_msgid,
444 relay_detached, reattach_on, detach_after, detach_on
445 FROM Channel
446 WHERE network = ?`, networkID)
447 if err != nil {
448 return nil, err
449 }
450 defer rows.Close()
451
452 var channels []Channel
453 for rows.Next() {
454 var ch Channel
455 var key, detachedInternalMsgID sql.NullString
456 var detachAfter int64
457 if err := rows.Scan(&ch.ID, &ch.Name, &key, &ch.Detached, &detachedInternalMsgID, &ch.RelayDetached, &ch.ReattachOn, &detachAfter, &ch.DetachOn); err != nil {
458 return nil, err
459 }
460 ch.Key = key.String
461 ch.DetachedInternalMsgID = detachedInternalMsgID.String
462 ch.DetachAfter = time.Duration(detachAfter) * time.Second
463 channels = append(channels, ch)
464 }
465 if err := rows.Err(); err != nil {
466 return nil, err
467 }
468
469 return channels, nil
470}
471
472func (db *SqliteDB) StoreChannel(networkID int64, ch *Channel) error {
473 db.lock.Lock()
474 defer db.lock.Unlock()
475
476 key := toNullString(ch.Key)
477 detachAfter := int64(math.Ceil(ch.DetachAfter.Seconds()))
478
479 var err error
480 if ch.ID != 0 {
481 _, err = db.db.Exec(`UPDATE Channel
482 SET network = ?, name = ?, key = ?, detached = ?, detached_internal_msgid = ?, relay_detached = ?, reattach_on = ?, detach_after = ?, detach_on = ?
483 WHERE id = ?`,
484 networkID, ch.Name, key, ch.Detached, toNullString(ch.DetachedInternalMsgID), ch.RelayDetached, ch.ReattachOn, detachAfter, ch.DetachOn, ch.ID)
485 } else {
486 var res sql.Result
487 res, err = db.db.Exec(`INSERT INTO Channel(network, name, key, detached, detached_internal_msgid, relay_detached, reattach_on, detach_after, detach_on)
488 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
489 networkID, ch.Name, key, ch.Detached, toNullString(ch.DetachedInternalMsgID), ch.RelayDetached, ch.ReattachOn, detachAfter, ch.DetachOn)
490 if err != nil {
491 return err
492 }
493 ch.ID, err = res.LastInsertId()
494 }
495 return err
496}
497
498func (db *SqliteDB) DeleteChannel(id int64) error {
499 db.lock.Lock()
500 defer db.lock.Unlock()
501
502 _, err := db.db.Exec("DELETE FROM Channel WHERE id = ?", id)
503 return err
504}
505
506func (db *SqliteDB) ListDeliveryReceipts(networkID int64) ([]DeliveryReceipt, error) {
507 db.lock.RLock()
508 defer db.lock.RUnlock()
509
510 rows, err := db.db.Query(`SELECT id, target, client, internal_msgid
511 FROM DeliveryReceipt
512 WHERE network = ?`, networkID)
513 if err != nil {
514 return nil, err
515 }
516 defer rows.Close()
517
518 var receipts []DeliveryReceipt
519 for rows.Next() {
520 var rcpt DeliveryReceipt
521 var client sql.NullString
522 if err := rows.Scan(&rcpt.ID, &rcpt.Target, &client, &rcpt.InternalMsgID); err != nil {
523 return nil, err
524 }
525 rcpt.Client = client.String
526 receipts = append(receipts, rcpt)
527 }
528 if err := rows.Err(); err != nil {
529 return nil, err
530 }
531
532 return receipts, nil
533}
534
535func (db *SqliteDB) StoreClientDeliveryReceipts(networkID int64, client string, receipts []DeliveryReceipt) error {
536 db.lock.Lock()
537 defer db.lock.Unlock()
538
539 tx, err := db.db.Begin()
540 if err != nil {
541 return err
542 }
543 defer tx.Rollback()
544
545 _, err = tx.Exec("DELETE FROM DeliveryReceipt WHERE network = ? AND client IS ?",
546 networkID, toNullString(client))
547 if err != nil {
548 return err
549 }
550
551 for i := range receipts {
552 rcpt := &receipts[i]
553
554 res, err := tx.Exec("INSERT INTO DeliveryReceipt(network, target, client, internal_msgid) VALUES (?, ?, ?, ?)",
555 networkID, rcpt.Target, toNullString(client), rcpt.InternalMsgID)
556 if err != nil {
557 return err
558 }
559 rcpt.ID, err = res.LastInsertId()
560 if err != nil {
561 return err
562 }
563 }
564
565 return tx.Commit()
566}
Note: See TracBrowser for help on using the repository browser.