commit e2ab3f7c357e4a40a2891afece9f3ee0adb66008
from: Oliver Lowe <o@olowe.co>
date: Mon Apr 25 14:11:16 2022 UTC

mailmux: use builtin sqlite datetime functions to set modtime

Better to use the functionality provided by sqlite since we've decided
to bring on the dependency. Might as well use it.

commit - 8b1fbea42df752ceccc0ef9c67b19654193a40c9
commit + e2ab3f7c357e4a40a2891afece9f3ee0adb66008
blob - a3aff8f8efcfa8adc7aadb3b6a76af020240e542
blob + f758b9d19b99f720b74565c76c14045f46a3db1e
--- alias.go
+++ alias.go
@@ -74,7 +74,7 @@ CREATE TABLE IF NOT EXISTS aliases (
 	destination TEXT NOT NULL,
 	expiry INTEGER NOT NULL,
 	note TEXT NOT NULL,
-	modtime INTEGER NOT NULL
+	modtime INTEGER NOT NULL DEFAULT (unixepoch())
 );`
 	_, err = db.Exec(stmt)
 	if err != nil {
@@ -109,11 +109,11 @@ func (db *AliasDB) Put(a Alias) error {
 	}
 	var q string
 	if errors.Is(err, errRecipientNotExist) {
-		q = "INSERT INTO aliases (recipient, destination, expiry, note, modtime) VALUES (?, ?, ?, ?, ?)"
-		_, err = db.Exec(q, a.Recipient, a.Destination, a.Expiry.Unix(), a.Note, time.Now().Unix())
+		q = "INSERT INTO aliases (recipient, destination, expiry, note) VALUES (?, ?, ?, ?)"
+		_, err = db.Exec(q, a.Recipient, a.Destination, a.Expiry.Unix(), a.Note)
 	} else if err == nil {
-		q = "UPDATE aliases SET recipient = ?, destination = ?, expiry = ?, note = ?, modtime = ? WHERE recipient = ?"
-		_, err = db.Exec(q, a.Recipient, a.Destination, a.Expiry.Unix(), a.Note, time.Now().Unix(), a.Recipient)
+		q = "UPDATE aliases SET recipient = ?, destination = ?, expiry = ?, note = ?, modtime = unixepoch() WHERE recipient = ?"
+		_, err = db.Exec(q, a.Recipient, a.Destination, a.Expiry.Unix(), a.Note, a.Recipient)
 	}
 	return err
 }
blob - 6fc8bb0f4d22cd170287c17377a96dfd1ee42cdb
blob + c11252d89e1561853f49e9cbabd54d25986d8328
--- userdb.go
+++ userdb.go
@@ -7,7 +7,6 @@ import (
 	"net/mail"
 	"os"
 	"path"
-	"time"
 
 	_ "github.com/mattn/go-sqlite3"
 	"golang.org/x/crypto/bcrypt"
@@ -60,7 +59,7 @@ func initialiseUserDB(db *sql.DB) error {
 	stmt := `CREATE TABLE IF NOT EXISTS users (
 	username TEXT PRIMARY KEY,
 	password BLOB NOT NULL,
-	modtime INTEGER NOT NULL
+	modtime INTEGER NOT NULL DEFAULT (unixepoch())
 );`
 	_, err := db.Exec(stmt)
 	return err
@@ -94,7 +93,7 @@ func (db *UserDB) add(username string, pw Password) er
 	if err != nil {
 		return fmt.Errorf("add %s: %w", username, err)
 	}
-	_, err = db.Exec("INSERT INTO users (username, password, modtime) VALUES (?, ?, ?)", username, hashed, time.Now().Unix())
+	_, err = db.Exec("INSERT INTO users (username, password) VALUES (?, ?)", username, hashed)
 	if err != nil {
 		return fmt.Errorf("add %s: %w", username, err)
 	}
@@ -117,7 +116,8 @@ func (db *UserDB) Change(name string, new Password) er
 	if err != nil {
 		return fmt.Errorf("generate hash: %w", err)
 	}
-	_, err = db.Exec("UPDATE users SET password = ?, modtime = ? WHERE username = ?", hashed, time.Now().Unix(), name)
+	q := "UPDATE users SET password = ?, modtime = unixepoch() WHERE username = ?"
+	_, err = db.Exec(q, hashed, name)
 	return err
 }