source: code/trunk/db_sqlite.go@ 710

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

Make Network.Nick optional

Make Network.Nick optional, default to the user's username. This
will allow adding a global setting to set the nickname in the
future, just like we have for the real name.

References: ​https://todo.sr.ht/~emersion/soju/110
File size: 19.2 KB
Line 
1package soju
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7 "math"
8 "strings"
9 "sync"
10 "time"
11
12 _ "github.com/mattn/go-sqlite3"
13)
14
15const sqliteQueryTimeout = 5 * time.Second
16
17const sqliteSchema = `
18CREATE TABLE User (
19 id INTEGER PRIMARY KEY,
20 username TEXT NOT NULL UNIQUE,
21 password TEXT,
22 admin INTEGER NOT NULL DEFAULT 0,
23 realname TEXT
24);
25
26CREATE TABLE Network (
27 id INTEGER PRIMARY KEY,
28 name TEXT,
29 user INTEGER NOT NULL,
30 addr TEXT NOT NULL,
31 nick TEXT,
32 username TEXT,
33 realname TEXT,
34 pass TEXT,
35 connect_commands TEXT,
36 sasl_mechanism TEXT,
37 sasl_plain_username TEXT,
38 sasl_plain_password TEXT,
39 sasl_external_cert BLOB,
40 sasl_external_key BLOB,
41 enabled INTEGER NOT NULL DEFAULT 1,
42 FOREIGN KEY(user) REFERENCES User(id),
43 UNIQUE(user, addr, nick),
44 UNIQUE(user, name)
45);
46
47CREATE TABLE Channel (
48 id INTEGER PRIMARY KEY,
49 network INTEGER NOT NULL,
50 name TEXT NOT NULL,
51 key TEXT,
52 detached INTEGER NOT NULL DEFAULT 0,
53 detached_internal_msgid TEXT,
54 relay_detached INTEGER NOT NULL DEFAULT 0,
55 reattach_on INTEGER NOT NULL DEFAULT 0,
56 detach_after INTEGER NOT NULL DEFAULT 0,
57 detach_on INTEGER NOT NULL DEFAULT 0,
58 FOREIGN KEY(network) REFERENCES Network(id),
59 UNIQUE(network, name)
60);
61
62CREATE TABLE DeliveryReceipt (
63 id INTEGER PRIMARY KEY,
64 network INTEGER NOT NULL,
65 target TEXT NOT NULL,
66 client TEXT,
67 internal_msgid TEXT NOT NULL,
68 FOREIGN KEY(network) REFERENCES Network(id),
69 UNIQUE(network, target, client)
70);
71`
72
73var sqliteMigrations = []string{
74 "", // migration #0 is reserved for schema initialization
75 "ALTER TABLE Network ADD COLUMN connect_commands VARCHAR(1023)",
76 "ALTER TABLE Channel ADD COLUMN detached INTEGER NOT NULL DEFAULT 0",
77 "ALTER TABLE Network ADD COLUMN sasl_external_cert BLOB DEFAULT NULL",
78 "ALTER TABLE Network ADD COLUMN sasl_external_key BLOB DEFAULT NULL",
79 "ALTER TABLE User ADD COLUMN admin INTEGER NOT NULL DEFAULT 0",
80 `
81 CREATE TABLE UserNew (
82 id INTEGER PRIMARY KEY,
83 username VARCHAR(255) NOT NULL UNIQUE,
84 password VARCHAR(255),
85 admin INTEGER NOT NULL DEFAULT 0
86 );
87 INSERT INTO UserNew SELECT rowid, username, password, admin FROM User;
88 DROP TABLE User;
89 ALTER TABLE UserNew RENAME TO User;
90 `,
91 `
92 CREATE TABLE NetworkNew (
93 id INTEGER PRIMARY KEY,
94 name VARCHAR(255),
95 user INTEGER NOT NULL,
96 addr VARCHAR(255) NOT NULL,
97 nick VARCHAR(255) NOT NULL,
98 username VARCHAR(255),
99 realname VARCHAR(255),
100 pass VARCHAR(255),
101 connect_commands VARCHAR(1023),
102 sasl_mechanism VARCHAR(255),
103 sasl_plain_username VARCHAR(255),
104 sasl_plain_password VARCHAR(255),
105 sasl_external_cert BLOB DEFAULT NULL,
106 sasl_external_key BLOB DEFAULT NULL,
107 FOREIGN KEY(user) REFERENCES User(id),
108 UNIQUE(user, addr, nick),
109 UNIQUE(user, name)
110 );
111 INSERT INTO NetworkNew
112 SELECT Network.id, name, User.id as user, addr, nick,
113 Network.username, realname, pass, connect_commands,
114 sasl_mechanism, sasl_plain_username, sasl_plain_password,
115 sasl_external_cert, sasl_external_key
116 FROM Network
117 JOIN User ON Network.user = User.username;
118 DROP TABLE Network;
119 ALTER TABLE NetworkNew RENAME TO Network;
120 `,
121 `
122 ALTER TABLE Channel ADD COLUMN relay_detached INTEGER NOT NULL DEFAULT 0;
123 ALTER TABLE Channel ADD COLUMN reattach_on INTEGER NOT NULL DEFAULT 0;
124 ALTER TABLE Channel ADD COLUMN detach_after INTEGER NOT NULL DEFAULT 0;
125 ALTER TABLE Channel ADD COLUMN detach_on INTEGER NOT NULL DEFAULT 0;
126 `,
127 `
128 CREATE TABLE DeliveryReceipt (
129 id INTEGER PRIMARY KEY,
130 network INTEGER NOT NULL,
131 target VARCHAR(255) NOT NULL,
132 client VARCHAR(255),
133 internal_msgid VARCHAR(255) NOT NULL,
134 FOREIGN KEY(network) REFERENCES Network(id),
135 UNIQUE(network, target, client)
136 );
137 `,
138 "ALTER TABLE Channel ADD COLUMN detached_internal_msgid VARCHAR(255)",
139 "ALTER TABLE Network ADD COLUMN enabled INTEGER NOT NULL DEFAULT 1",
140 "ALTER TABLE User ADD COLUMN realname VARCHAR(255)",
141 `
142 CREATE TABLE NetworkNew (
143 id INTEGER PRIMARY KEY,
144 name TEXT,
145 user INTEGER NOT NULL,
146 addr TEXT NOT NULL,
147 nick TEXT,
148 username TEXT,
149 realname TEXT,
150 pass TEXT,
151 connect_commands TEXT,
152 sasl_mechanism TEXT,
153 sasl_plain_username TEXT,
154 sasl_plain_password TEXT,
155 sasl_external_cert BLOB,
156 sasl_external_key BLOB,
157 enabled INTEGER NOT NULL DEFAULT 1,
158 FOREIGN KEY(user) REFERENCES User(id),
159 UNIQUE(user, addr, nick),
160 UNIQUE(user, name)
161 );
162 INSERT INTO NetworkNew
163 SELECT id, name, user, addr, nick, username, realname, pass,
164 connect_commands, sasl_mechanism, sasl_plain_username,
165 sasl_plain_password, sasl_external_cert, sasl_external_key,
166 enabled
167 FROM Network;
168 DROP TABLE Network;
169 ALTER TABLE NetworkNew RENAME TO Network;
170 `,
171}
172
173type SqliteDB struct {
174 lock sync.RWMutex
175 db *sql.DB
176}
177
178func OpenSqliteDB(source string) (Database, error) {
179 sqlSqliteDB, err := sql.Open("sqlite3", source)
180 if err != nil {
181 return nil, err
182 }
183
184 db := &SqliteDB{db: sqlSqliteDB}
185 if err := db.upgrade(); err != nil {
186 sqlSqliteDB.Close()
187 return nil, err
188 }
189
190 return db, nil
191}
192
193func (db *SqliteDB) Close() error {
194 db.lock.Lock()
195 defer db.lock.Unlock()
196 return db.db.Close()
197}
198
199func (db *SqliteDB) upgrade() error {
200 db.lock.Lock()
201 defer db.lock.Unlock()
202
203 var version int
204 if err := db.db.QueryRow("PRAGMA user_version").Scan(&version); err != nil {
205 return fmt.Errorf("failed to query schema version: %v", err)
206 }
207
208 if version == len(sqliteMigrations) {
209 return nil
210 } else if version > len(sqliteMigrations) {
211 return fmt.Errorf("soju (version %d) older than schema (version %d)", len(sqliteMigrations), version)
212 }
213
214 tx, err := db.db.Begin()
215 if err != nil {
216 return err
217 }
218 defer tx.Rollback()
219
220 if version == 0 {
221 if _, err := tx.Exec(sqliteSchema); err != nil {
222 return fmt.Errorf("failed to initialize schema: %v", err)
223 }
224 } else {
225 for i := version; i < len(sqliteMigrations); i++ {
226 if _, err := tx.Exec(sqliteMigrations[i]); err != nil {
227 return fmt.Errorf("failed to execute migration #%v: %v", i, err)
228 }
229 }
230 }
231
232 // For some reason prepared statements don't work here
233 _, err = tx.Exec(fmt.Sprintf("PRAGMA user_version = %d", len(sqliteMigrations)))
234 if err != nil {
235 return fmt.Errorf("failed to bump schema version: %v", err)
236 }
237
238 return tx.Commit()
239}
240
241func (db *SqliteDB) Stats(ctx context.Context) (*DatabaseStats, error) {
242 db.lock.RLock()
243 defer db.lock.RUnlock()
244
245 ctx, cancel := context.WithTimeout(ctx, sqliteQueryTimeout)
246 defer cancel()
247
248 var stats DatabaseStats
249 row := db.db.QueryRowContext(ctx, `SELECT
250 (SELECT COUNT(*) FROM User) AS users,
251 (SELECT COUNT(*) FROM Network) AS networks,
252 (SELECT COUNT(*) FROM Channel) AS channels`)
253 if err := row.Scan(&stats.Users, &stats.Networks, &stats.Channels); err != nil {
254 return nil, err
255 }
256
257 return &stats, nil
258}
259
260func toNullString(s string) sql.NullString {
261 return sql.NullString{
262 String: s,
263 Valid: s != "",
264 }
265}
266
267func (db *SqliteDB) ListUsers(ctx context.Context) ([]User, error) {
268 db.lock.RLock()
269 defer db.lock.RUnlock()
270
271 ctx, cancel := context.WithTimeout(ctx, sqliteQueryTimeout)
272 defer cancel()
273
274 rows, err := db.db.QueryContext(ctx,
275 "SELECT id, username, password, admin, realname FROM User")
276 if err != nil {
277 return nil, err
278 }
279 defer rows.Close()
280
281 var users []User
282 for rows.Next() {
283 var user User
284 var password, realname sql.NullString
285 if err := rows.Scan(&user.ID, &user.Username, &password, &user.Admin, &realname); err != nil {
286 return nil, err
287 }
288 user.Password = password.String
289 user.Realname = realname.String
290 users = append(users, user)
291 }
292 if err := rows.Err(); err != nil {
293 return nil, err
294 }
295
296 return users, nil
297}
298
299func (db *SqliteDB) GetUser(ctx context.Context, username string) (*User, error) {
300 db.lock.RLock()
301 defer db.lock.RUnlock()
302
303 ctx, cancel := context.WithTimeout(ctx, sqliteQueryTimeout)
304 defer cancel()
305
306 user := &User{Username: username}
307
308 var password, realname sql.NullString
309 row := db.db.QueryRowContext(ctx,
310 "SELECT id, password, admin, realname FROM User WHERE username = ?",
311 username)
312 if err := row.Scan(&user.ID, &password, &user.Admin, &realname); err != nil {
313 return nil, err
314 }
315 user.Password = password.String
316 user.Realname = realname.String
317 return user, nil
318}
319
320func (db *SqliteDB) StoreUser(ctx context.Context, user *User) error {
321 db.lock.Lock()
322 defer db.lock.Unlock()
323
324 ctx, cancel := context.WithTimeout(ctx, sqliteQueryTimeout)
325 defer cancel()
326
327 args := []interface{}{
328 sql.Named("username", user.Username),
329 sql.Named("password", toNullString(user.Password)),
330 sql.Named("admin", user.Admin),
331 sql.Named("realname", toNullString(user.Realname)),
332 }
333
334 var err error
335 if user.ID != 0 {
336 _, err = db.db.ExecContext(ctx, `
337 UPDATE User SET password = :password, admin = :admin,
338 realname = :realname WHERE username = :username`,
339 args...)
340 } else {
341 var res sql.Result
342 res, err = db.db.ExecContext(ctx, `
343 INSERT INTO
344 User(username, password, admin, realname)
345 VALUES (:username, :password, :admin, :realname)`,
346 args...)
347 if err != nil {
348 return err
349 }
350 user.ID, err = res.LastInsertId()
351 }
352
353 return err
354}
355
356func (db *SqliteDB) DeleteUser(ctx context.Context, id int64) error {
357 db.lock.Lock()
358 defer db.lock.Unlock()
359
360 ctx, cancel := context.WithTimeout(ctx, sqliteQueryTimeout)
361 defer cancel()
362
363 tx, err := db.db.Begin()
364 if err != nil {
365 return err
366 }
367 defer tx.Rollback()
368
369 _, err = tx.ExecContext(ctx, `DELETE FROM DeliveryReceipt
370 WHERE id IN (
371 SELECT DeliveryReceipt.id
372 FROM DeliveryReceipt
373 JOIN Network ON DeliveryReceipt.network = Network.id
374 WHERE Network.user = ?
375 )`, id)
376 if err != nil {
377 return err
378 }
379
380 _, err = tx.ExecContext(ctx, `DELETE FROM Channel
381 WHERE id IN (
382 SELECT Channel.id
383 FROM Channel
384 JOIN Network ON Channel.network = Network.id
385 WHERE Network.user = ?
386 )`, id)
387 if err != nil {
388 return err
389 }
390
391 _, err = tx.ExecContext(ctx, "DELETE FROM Network WHERE user = ?", id)
392 if err != nil {
393 return err
394 }
395
396 _, err = tx.ExecContext(ctx, "DELETE FROM User WHERE id = ?", id)
397 if err != nil {
398 return err
399 }
400
401 return tx.Commit()
402}
403
404func (db *SqliteDB) ListNetworks(ctx context.Context, userID int64) ([]Network, error) {
405 db.lock.RLock()
406 defer db.lock.RUnlock()
407
408 ctx, cancel := context.WithTimeout(ctx, sqliteQueryTimeout)
409 defer cancel()
410
411 rows, err := db.db.QueryContext(ctx, `
412 SELECT id, name, addr, nick, username, realname, pass,
413 connect_commands, sasl_mechanism, sasl_plain_username, sasl_plain_password,
414 sasl_external_cert, sasl_external_key, enabled
415 FROM Network
416 WHERE user = ?`,
417 userID)
418 if err != nil {
419 return nil, err
420 }
421 defer rows.Close()
422
423 var networks []Network
424 for rows.Next() {
425 var net Network
426 var name, nick, username, realname, pass, connectCommands sql.NullString
427 var saslMechanism, saslPlainUsername, saslPlainPassword sql.NullString
428 err := rows.Scan(&net.ID, &name, &net.Addr, &nick, &username, &realname,
429 &pass, &connectCommands, &saslMechanism, &saslPlainUsername, &saslPlainPassword,
430 &net.SASL.External.CertBlob, &net.SASL.External.PrivKeyBlob, &net.Enabled)
431 if err != nil {
432 return nil, err
433 }
434 net.Name = name.String
435 net.Nick = nick.String
436 net.Username = username.String
437 net.Realname = realname.String
438 net.Pass = pass.String
439 if connectCommands.Valid {
440 net.ConnectCommands = strings.Split(connectCommands.String, "\r\n")
441 }
442 net.SASL.Mechanism = saslMechanism.String
443 net.SASL.Plain.Username = saslPlainUsername.String
444 net.SASL.Plain.Password = saslPlainPassword.String
445 networks = append(networks, net)
446 }
447 if err := rows.Err(); err != nil {
448 return nil, err
449 }
450
451 return networks, nil
452}
453
454func (db *SqliteDB) StoreNetwork(ctx context.Context, userID int64, network *Network) error {
455 db.lock.Lock()
456 defer db.lock.Unlock()
457
458 ctx, cancel := context.WithTimeout(ctx, sqliteQueryTimeout)
459 defer cancel()
460
461 var saslMechanism, saslPlainUsername, saslPlainPassword sql.NullString
462 if network.SASL.Mechanism != "" {
463 saslMechanism = toNullString(network.SASL.Mechanism)
464 switch network.SASL.Mechanism {
465 case "PLAIN":
466 saslPlainUsername = toNullString(network.SASL.Plain.Username)
467 saslPlainPassword = toNullString(network.SASL.Plain.Password)
468 network.SASL.External.CertBlob = nil
469 network.SASL.External.PrivKeyBlob = nil
470 case "EXTERNAL":
471 // keep saslPlain* nil
472 default:
473 return fmt.Errorf("soju: cannot store network: unsupported SASL mechanism %q", network.SASL.Mechanism)
474 }
475 }
476
477 args := []interface{}{
478 sql.Named("name", toNullString(network.Name)),
479 sql.Named("addr", network.Addr),
480 sql.Named("nick", toNullString(network.Nick)),
481 sql.Named("username", toNullString(network.Username)),
482 sql.Named("realname", toNullString(network.Realname)),
483 sql.Named("pass", toNullString(network.Pass)),
484 sql.Named("connect_commands", toNullString(strings.Join(network.ConnectCommands, "\r\n"))),
485 sql.Named("sasl_mechanism", saslMechanism),
486 sql.Named("sasl_plain_username", saslPlainUsername),
487 sql.Named("sasl_plain_password", saslPlainPassword),
488 sql.Named("sasl_external_cert", network.SASL.External.CertBlob),
489 sql.Named("sasl_external_key", network.SASL.External.PrivKeyBlob),
490 sql.Named("enabled", network.Enabled),
491
492 sql.Named("id", network.ID), // only for UPDATE
493 sql.Named("user", userID), // only for INSERT
494 }
495
496 var err error
497 if network.ID != 0 {
498 _, err = db.db.ExecContext(ctx, `
499 UPDATE Network
500 SET name = :name, addr = :addr, nick = :nick, username = :username,
501 realname = :realname, pass = :pass, connect_commands = :connect_commands,
502 sasl_mechanism = :sasl_mechanism, sasl_plain_username = :sasl_plain_username, sasl_plain_password = :sasl_plain_password,
503 sasl_external_cert = :sasl_external_cert, sasl_external_key = :sasl_external_key,
504 enabled = :enabled
505 WHERE id = :id`, args...)
506 } else {
507 var res sql.Result
508 res, err = db.db.ExecContext(ctx, `
509 INSERT INTO Network(user, name, addr, nick, username, realname, pass,
510 connect_commands, sasl_mechanism, sasl_plain_username,
511 sasl_plain_password, sasl_external_cert, sasl_external_key, enabled)
512 VALUES (:user, :name, :addr, :nick, :username, :realname, :pass,
513 :connect_commands, :sasl_mechanism, :sasl_plain_username,
514 :sasl_plain_password, :sasl_external_cert, :sasl_external_key, :enabled)`,
515 args...)
516 if err != nil {
517 return err
518 }
519 network.ID, err = res.LastInsertId()
520 }
521 return err
522}
523
524func (db *SqliteDB) DeleteNetwork(ctx context.Context, id int64) error {
525 db.lock.Lock()
526 defer db.lock.Unlock()
527
528 ctx, cancel := context.WithTimeout(ctx, sqliteQueryTimeout)
529 defer cancel()
530
531 tx, err := db.db.Begin()
532 if err != nil {
533 return err
534 }
535 defer tx.Rollback()
536
537 _, err = tx.ExecContext(ctx, "DELETE FROM DeliveryReceipt WHERE network = ?", id)
538 if err != nil {
539 return err
540 }
541
542 _, err = tx.ExecContext(ctx, "DELETE FROM Channel WHERE network = ?", id)
543 if err != nil {
544 return err
545 }
546
547 _, err = tx.ExecContext(ctx, "DELETE FROM Network WHERE id = ?", id)
548 if err != nil {
549 return err
550 }
551
552 return tx.Commit()
553}
554
555func (db *SqliteDB) ListChannels(ctx context.Context, networkID int64) ([]Channel, error) {
556 db.lock.RLock()
557 defer db.lock.RUnlock()
558
559 ctx, cancel := context.WithTimeout(ctx, sqliteQueryTimeout)
560 defer cancel()
561
562 rows, err := db.db.QueryContext(ctx, `SELECT
563 id, name, key, detached, detached_internal_msgid,
564 relay_detached, reattach_on, detach_after, detach_on
565 FROM Channel
566 WHERE network = ?`, networkID)
567 if err != nil {
568 return nil, err
569 }
570 defer rows.Close()
571
572 var channels []Channel
573 for rows.Next() {
574 var ch Channel
575 var key, detachedInternalMsgID sql.NullString
576 var detachAfter int64
577 if err := rows.Scan(&ch.ID, &ch.Name, &key, &ch.Detached, &detachedInternalMsgID, &ch.RelayDetached, &ch.ReattachOn, &detachAfter, &ch.DetachOn); err != nil {
578 return nil, err
579 }
580 ch.Key = key.String
581 ch.DetachedInternalMsgID = detachedInternalMsgID.String
582 ch.DetachAfter = time.Duration(detachAfter) * time.Second
583 channels = append(channels, ch)
584 }
585 if err := rows.Err(); err != nil {
586 return nil, err
587 }
588
589 return channels, nil
590}
591
592func (db *SqliteDB) StoreChannel(ctx context.Context, networkID int64, ch *Channel) error {
593 db.lock.Lock()
594 defer db.lock.Unlock()
595
596 ctx, cancel := context.WithTimeout(ctx, sqliteQueryTimeout)
597 defer cancel()
598
599 args := []interface{}{
600 sql.Named("network", networkID),
601 sql.Named("name", ch.Name),
602 sql.Named("key", toNullString(ch.Key)),
603 sql.Named("detached", ch.Detached),
604 sql.Named("detached_internal_msgid", toNullString(ch.DetachedInternalMsgID)),
605 sql.Named("relay_detached", ch.RelayDetached),
606 sql.Named("reattach_on", ch.ReattachOn),
607 sql.Named("detach_after", int64(math.Ceil(ch.DetachAfter.Seconds()))),
608 sql.Named("detach_on", ch.DetachOn),
609
610 sql.Named("id", ch.ID), // only for UPDATE
611 }
612
613 var err error
614 if ch.ID != 0 {
615 _, err = db.db.ExecContext(ctx, `UPDATE Channel
616 SET network = :network, name = :name, key = :key, detached = :detached,
617 detached_internal_msgid = :detached_internal_msgid, relay_detached = :relay_detached,
618 reattach_on = :reattach_on, detach_after = :detach_after, detach_on = :detach_on
619 WHERE id = :id`, args...)
620 } else {
621 var res sql.Result
622 res, err = db.db.ExecContext(ctx, `INSERT INTO Channel(network, name, key, detached, detached_internal_msgid, relay_detached, reattach_on, detach_after, detach_on)
623 VALUES (:network, :name, :key, :detached, :detached_internal_msgid, :relay_detached, :reattach_on, :detach_after, :detach_on)`, args...)
624 if err != nil {
625 return err
626 }
627 ch.ID, err = res.LastInsertId()
628 }
629 return err
630}
631
632func (db *SqliteDB) DeleteChannel(ctx context.Context, id int64) error {
633 db.lock.Lock()
634 defer db.lock.Unlock()
635
636 ctx, cancel := context.WithTimeout(ctx, sqliteQueryTimeout)
637 defer cancel()
638
639 _, err := db.db.ExecContext(ctx, "DELETE FROM Channel WHERE id = ?", id)
640 return err
641}
642
643func (db *SqliteDB) ListDeliveryReceipts(ctx context.Context, networkID int64) ([]DeliveryReceipt, error) {
644 db.lock.RLock()
645 defer db.lock.RUnlock()
646
647 ctx, cancel := context.WithTimeout(ctx, sqliteQueryTimeout)
648 defer cancel()
649
650 rows, err := db.db.QueryContext(ctx, `
651 SELECT id, target, client, internal_msgid
652 FROM DeliveryReceipt
653 WHERE network = ?`, networkID)
654 if err != nil {
655 return nil, err
656 }
657 defer rows.Close()
658
659 var receipts []DeliveryReceipt
660 for rows.Next() {
661 var rcpt DeliveryReceipt
662 var client sql.NullString
663 if err := rows.Scan(&rcpt.ID, &rcpt.Target, &client, &rcpt.InternalMsgID); err != nil {
664 return nil, err
665 }
666 rcpt.Client = client.String
667 receipts = append(receipts, rcpt)
668 }
669 if err := rows.Err(); err != nil {
670 return nil, err
671 }
672
673 return receipts, nil
674}
675
676func (db *SqliteDB) StoreClientDeliveryReceipts(ctx context.Context, networkID int64, client string, receipts []DeliveryReceipt) error {
677 db.lock.Lock()
678 defer db.lock.Unlock()
679
680 ctx, cancel := context.WithTimeout(ctx, sqliteQueryTimeout)
681 defer cancel()
682
683 tx, err := db.db.Begin()
684 if err != nil {
685 return err
686 }
687 defer tx.Rollback()
688
689 _, err = tx.ExecContext(ctx, "DELETE FROM DeliveryReceipt WHERE network = ? AND client IS ?",
690 networkID, toNullString(client))
691 if err != nil {
692 return err
693 }
694
695 for i := range receipts {
696 rcpt := &receipts[i]
697
698 res, err := tx.ExecContext(ctx, `
699 INSERT INTO DeliveryReceipt(network, target, client, internal_msgid)
700 VALUES (:network, :target, :client, :internal_msgid)`,
701 sql.Named("network", networkID),
702 sql.Named("target", rcpt.Target),
703 sql.Named("client", toNullString(client)),
704 sql.Named("internal_msgid", rcpt.InternalMsgID))
705 if err != nil {
706 return err
707 }
708 rcpt.ID, err = res.LastInsertId()
709 if err != nil {
710 return err
711 }
712 }
713
714 return tx.Commit()
715}
Note: See TracBrowser for help on using the repository browser.