Changeset 781 in code for trunk/db_sqlite.go

Timestamp:

Feb 11, 2022, 6:41:46 PM (3 years ago)

Author:

delthas

Message:

Add support for the wip soju.im/read capability and READ command

READ lets downstream clients share information between each other about
what messages have been read by other downstreams.

Each target/entity has an optional corresponding read receipt, which is
stored as a timestamp.

• When a downstream sends: READ #chan timestamp=2020-01-01T01:23:45.000Z the read receipt for that target is set to that date
• soju sends READ to downstreams:
  • on JOIN, if the client uses the soju.im/read capability
  • when the read receipt timestamp is set by any downstream

The read receipt date is clamped by the previous receipt date and the
current time.

File:

• trunk/db_sqlite.go (modified) (5 diffs)

trunk/db_sqlite.go

@@ -70 +70 @@
         FOREIGN KEY(network) REFERENCES Network(id),
         UNIQUE(network, target, client)
+);
+
+CREATE TABLE ReadReceipt (
+        id INTEGER PRIMARY KEY,
+        network INTEGER NOT NULL,
+        target TEXT NOT NULL,
+        timestamp TEXT NOT NULL,
+        FOREIGN KEY(network) REFERENCES Network(id),
+        UNIQUE(network, target)
 );
 `
@@ -171 +180 @@
                 ALTER TABLE NetworkNew RENAME TO Network;
         `,
+        `
+                CREATE TABLE ReadReceipt (
+                        id INTEGER PRIMARY KEY,
+                        network INTEGER NOT NULL,
+                        target TEXT NOT NULL,
+                        timestamp TEXT NOT NULL,
+                        FOREIGN KEY(network) REFERENCES Network(id),
+                        UNIQUE(network, target)
+                );
+        `,
 }
 
@@ -378 +397 @@
                         FROM DeliveryReceipt
                         JOIN Network ON DeliveryReceipt.network = Network.id
+                        WHERE Network.user = ?
+                )`, id)
+        if err != nil {
+                return err
+        }
+
+        _, err = tx.ExecContext(ctx, `DELETE FROM ReadReceipt
+                WHERE id IN (
+                        SELECT ReadReceipt.id
+                        FROM ReadReceipt
+                        JOIN Network ON ReadReceipt.network = Network.id
                         WHERE Network.user = ?
                 )`, id)
@@ -546 +576 @@
         }
 
+        _, err = tx.ExecContext(ctx, "DELETE FROM ReadReceipt WHERE network = ?", id)
+        if err != nil {
+                return err
+        }
+
         _, err = tx.ExecContext(ctx, "DELETE FROM Channel WHERE network = ?", id)
         if err != nil {
@@ -720 +755 @@
         return tx.Commit()
 }
+
+func (db *SqliteDB) GetReadReceipt(ctx context.Context, networkID int64, name string) (*ReadReceipt, error) {
+        db.lock.RLock()
+        defer db.lock.RUnlock()
+
+        ctx, cancel := context.WithTimeout(ctx, sqliteQueryTimeout)
+        defer cancel()
+
+        receipt := &ReadReceipt{
+                Target: name,
+        }
+
+        row := db.db.QueryRowContext(ctx, `
+                SELECT id, timestamp FROM ReadReceipt WHERE network = :network AND target = :target`,
+                sql.Named("network", networkID),
+                sql.Named("target", name),
+        )
+        var timestamp string
+        if err := row.Scan(&receipt.ID, &timestamp); err != nil {
+                if err == sql.ErrNoRows {
+                        return nil, nil
+                }
+                return nil, err
+        }
+        if t, err := time.Parse(serverTimeLayout, timestamp); err != nil {
+                return nil, err
+        } else {
+                receipt.Timestamp = t
+        }
+        return receipt, nil
+}
+
+func (db *SqliteDB) StoreReadReceipt(ctx context.Context, networkID int64, receipt *ReadReceipt) error {
+        db.lock.Lock()
+        defer db.lock.Unlock()
+
+        ctx, cancel := context.WithTimeout(ctx, sqliteQueryTimeout)
+        defer cancel()
+
+        args := []interface{}{
+                sql.Named("id", receipt.ID),
+                sql.Named("timestamp", receipt.Timestamp.UTC().Format(serverTimeLayout)),
+                sql.Named("network", networkID),
+                sql.Named("target", receipt.Target),
+        }
+
+        var err error
+        if receipt.ID != 0 {
+                _, err = db.db.ExecContext(ctx, `
+                        UPDATE ReadReceipt SET timestamp = :timestamp WHERE id = :id`,
+                        args...)
+        } else {
+                var res sql.Result
+                res, err = db.db.ExecContext(ctx, `
+                        INSERT INTO
+                        ReadReceipt(network, target, timestamp)
+                        VALUES (:network, :target, :timestamp)`,
+                        args...)
+                if err != nil {
+                        return err
+                }
+                receipt.ID, err = res.LastInsertId()
+        }
+
+        return err
+}