Changeset 781 in code for trunk/db_postgres.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_postgres.go (modified) (3 diffs)

trunk/db_postgres.go

@@ -78 +78 @@
         UNIQUE(network, target, client)
 );
+
+CREATE TABLE "ReadReceipt" (
+        id SERIAL PRIMARY KEY,
+        network INTEGER NOT NULL REFERENCES "Network"(id) ON DELETE CASCADE,
+        target VARCHAR(255) NOT NULL,
+        timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
+        UNIQUE(network, target)
+);
 `
 
@@ -89 +97 @@
                         TYPE sasl_mechanism
                         USING sasl_mechanism::sasl_mechanism;
+        `,
+        `
+                CREATE TABLE "ReadReceipt" (
+                        id SERIAL PRIMARY KEY,
+                        network INTEGER NOT NULL REFERENCES "Network"(id) ON DELETE CASCADE,
+                        target VARCHAR(255) NOT NULL,
+                        timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
+                        UNIQUE(network, target)
+                );
         `,
 }
@@ -501 +518 @@
         return tx.Commit()
 }
+
+func (db *PostgresDB) GetReadReceipt(ctx context.Context, networkID int64, name string) (*ReadReceipt, error) {
+        ctx, cancel := context.WithTimeout(ctx, postgresQueryTimeout)
+        defer cancel()
+
+        receipt := &ReadReceipt{
+                Target: name,
+        }
+
+        row := db.db.QueryRowContext(ctx,
+                `SELECT id, timestamp FROM "ReadReceipt" WHERE network = $1 AND target = $2`,
+                networkID, name)
+        if err := row.Scan(&receipt.ID, &receipt.Timestamp); err != nil {
+                if err == sql.ErrNoRows {
+                        return nil, nil
+                }
+                return nil, err
+        }
+        return receipt, nil
+}
+
+func (db *PostgresDB) StoreReadReceipt(ctx context.Context, networkID int64, receipt *ReadReceipt) error {
+        ctx, cancel := context.WithTimeout(ctx, postgresQueryTimeout)
+        defer cancel()
+
+        var err error
+        if receipt.ID != 0 {
+                _, err = db.db.ExecContext(ctx, `
+                        UPDATE "ReadReceipt"
+                        SET timestamp = $1
+                        WHERE id = $2`,
+                        receipt.Timestamp, receipt.ID)
+        } else {
+                err = db.db.QueryRowContext(ctx, `
+                        INSERT INTO "ReadReceipt" (network, target, timestamp)
+                        VALUES ($1, $2, $3)
+                        RETURNING id`,
+                        networkID, receipt.Target, receipt.Timestamp).Scan(&receipt.ID)
+        }
+        return err
+}