Skip to content

armgit / lyftr

Public Imported from GitHub
0

Self-hosted fitness tracker. Log workouts, track nutrition, and monitor progress. Docker deployment, mobile-friendly.

9.3 KB
Raw Blame
package stores

import (
	"database/sql"
	"errors"
	"reflect"
	"testing"
)

func mustExec(t *testing.T, conn *sql.DB, q string, args ...any) int64 {
	t.Helper()
	res, err := conn.Exec(q, args...)
	if err != nil {
		t.Fatalf("exec %q: %v", q, err)
	}
	id, _ := res.LastInsertId()
	return id
}

// seedAccount inserts a user with n workouts (each with one exercise and two sets), n food
// logs, n weight logs, n saved foods, one active session and one program with a day,
// an exercise and a set. Case variants require the nocase index to be absent.
func seedAccount(t *testing.T, conn *sql.DB, email string, n int) int64 {
	t.Helper()
	uid := mustExec(t, conn, `INSERT INTO users (email, password_hash) VALUES (?, 'x')`, email)
	mustExec(t, conn, `INSERT INTO user_settings (user_id) VALUES (?)`, uid)
	mustExec(t, conn, `INSERT OR IGNORE INTO exercises (name) VALUES ('Squat')`)
	var ex int64
	if err := conn.QueryRow(`SELECT id FROM exercises WHERE name = 'Squat'`).Scan(&ex); err != nil {
		t.Fatalf("exercise: %v", err)
	}
	for i := 0; i < n; i++ {
		w := mustExec(t, conn, `INSERT INTO workouts (user_id, name) VALUES (?, 'w')`, uid)
		we := mustExec(t, conn, `INSERT INTO workout_exercises (workout_id, exercise_id) VALUES (?, ?)`, w, ex)
		mustExec(t, conn, `INSERT INTO sets (workout_exercise_id) VALUES (?)`, we)
		mustExec(t, conn, `INSERT INTO sets (workout_exercise_id) VALUES (?)`, we)
		mustExec(t, conn, `INSERT INTO food_logs (user_id, name, logged_on) VALUES (?, 'f', '2026-01-01')`, uid)
		mustExec(t, conn, `INSERT INTO weight_logs (user_id, weight, logged_on) VALUES (?, 180, '2026-01-01')`, uid)
		mustExec(t, conn, `INSERT INTO saved_foods (user_id, name, brand) VALUES (?, ?, 'b')`, uid, "food"+string(rune('a'+i)))
	}
	mustExec(t, conn, `INSERT INTO active_sessions (user_id, data) VALUES (?, '{}')`, uid)
	p := mustExec(t, conn, `INSERT INTO programs (user_id, name) VALUES (?, 'p')`, uid)
	d := mustExec(t, conn, `INSERT INTO program_days (program_id) VALUES (?)`, p)
	pe := mustExec(t, conn, `INSERT INTO program_exercises (program_id, exercise_id, program_day_id) VALUES (?, ?, ?)`, p, ex, d)
	mustExec(t, conn, `INSERT INTO program_sets (program_exercise_id) VALUES (?)`, pe)
	return uid
}

func dropNocaseIndex(t *testing.T, conn *sql.DB) {
	t.Helper()
	mustExec(t, conn, `DROP INDEX IF EXISTS idx_users_email_nocase`)
}

func TestGetByExactEmail(t *testing.T) {
	conn := testDB(t)
	dropNocaseIndex(t, conn)
	s := NewUserStore(conn)

	a := seedAccount(t, conn, "[email protected]", 0)
	b := seedAccount(t, conn, "[email protected]", 0)
	solo := seedAccount(t, conn, "[email protected]", 0)
	mustExec(t, conn, `INSERT INTO users (email, password_hash) VALUES ('[email protected]', 'x'), ('[email protected]', 'x')`)

	t.Run("exact spelling among variants", func(t *testing.T) {
		u, sp, err := s.GetByExactEmail("[email protected]")
		if err != nil || sp != nil || u.ID != b {
			t.Fatalf("got id %d, %v, %v; want %d", u.ID, sp, err, b)
		}
		u, _, err = s.GetByExactEmail("[email protected]")
		if err != nil || u.ID != a {
			t.Fatalf("got id %d, %v; want %d", u.ID, err, a)
		}
	})
	t.Run("single variant is not resolved", func(t *testing.T) {
		u, sp, err := s.GetByExactEmail("[email protected]")
		if !errors.Is(err, ErrInexactEmail) || u.ID != 0 || !reflect.DeepEqual(sp, []string{"[email protected]"}) {
			t.Fatalf("got %d, %v, %v; want ErrInexactEmail with [email protected]", u.ID, sp, err)
		}
		_ = solo
	})
	t.Run("several variants, none exact", func(t *testing.T) {
		_, sp, err := s.GetByExactEmail("[email protected]")
		if !errors.Is(err, ErrInexactEmail) || !reflect.DeepEqual(sp, []string{"[email protected]", "[email protected]"}) {
			t.Fatalf("got %v, %v", sp, err)
		}
	})
	t.Run("absent", func(t *testing.T) {
		_, sp, err := s.GetByExactEmail("[email protected]")
		if !errors.Is(err, ErrNoSuchUser) || sp != nil {
			t.Fatalf("got %v, %v", sp, err)
		}
	})
}

func TestCountDataCountsOnlyThatAccount(t *testing.T) {
	conn := testDB(t)
	s := NewUserStore(conn)
	a := seedAccount(t, conn, "[email protected]", 3)
	b := seedAccount(t, conn, "[email protected]", 1)

	got, err := s.CountData(a)
	if err != nil {
		t.Fatal(err)
	}
	want := AccountData{Workouts: 3, Sets: 6, FoodLogs: 3, WeightLogs: 3, Programs: 1, SavedFoods: 3, ActiveSessions: 1}
	if got != want {
		t.Errorf("a: got %+v, want %+v", got, want)
	}
	got, _ = s.CountData(b)
	want = AccountData{Workouts: 1, Sets: 2, FoodLogs: 1, WeightLogs: 1, Programs: 1, SavedFoods: 1, ActiveSessions: 1}
	if got != want {
		t.Errorf("b: got %+v, want %+v", got, want)
	}
}

// CountData lists tables by hand. This fails when a table gains a foreign key to users
// that the summary does not know about.
func TestCountDataCoversEveryUserOwnedTable(t *testing.T) {
	conn := testDB(t)
	rows, err := conn.Query(`SELECT m.name FROM sqlite_master m JOIN pragma_foreign_key_list(m.name) f
		WHERE m.type = 'table' AND f."table" = 'users' ORDER BY m.name`)
	if err != nil {
		t.Fatal(err)
	}
	var got []string
	for rows.Next() {
		var n string
		if err := rows.Scan(&n); err != nil {
			t.Fatal(err)
		}
		got = append(got, n)
	}
	rows.Close()
	want := []string{"active_sessions", "food_logs", "programs", "saved_foods", "user_settings", "weight_logs", "workouts"}
	if !reflect.DeepEqual(got, want) {
		t.Fatalf("user-owned tables = %v, want %v; add any new table to UserStore.CountData", got, want)
	}
}

func TestDeleteCascadesEveryChildTable(t *testing.T) {
	conn := testDB(t)
	s := NewUserStore(conn)
	a := seedAccount(t, conn, "[email protected]", 2)
	b := seedAccount(t, conn, "[email protected]", 2)
	before, _ := s.CountData(b)

	if err := s.Delete(a); err != nil {
		t.Fatal(err)
	}

	owned := map[string]string{
		"users":             `SELECT COUNT(*) FROM users WHERE id = ?`,
		"user_settings":     `SELECT COUNT(*) FROM user_settings WHERE user_id = ?`,
		"active_sessions":   `SELECT COUNT(*) FROM active_sessions WHERE user_id = ?`,
		"workouts":          `SELECT COUNT(*) FROM workouts WHERE user_id = ?`,
		"food_logs":         `SELECT COUNT(*) FROM food_logs WHERE user_id = ?`,
		"weight_logs":       `SELECT COUNT(*) FROM weight_logs WHERE user_id = ?`,
		"saved_foods":       `SELECT COUNT(*) FROM saved_foods WHERE user_id = ?`,
		"programs":          `SELECT COUNT(*) FROM programs WHERE user_id = ?`,
		"workout_exercises": `SELECT COUNT(*) FROM workout_exercises WHERE workout_id IN (SELECT id FROM workouts WHERE user_id = ?)`,
	}
	for name, q := range owned {
		var n int
		if err := conn.QueryRow(q, a).Scan(&n); err != nil || n != 0 {
			t.Errorf("%s: %d rows left for the deleted account (%v)", name, n, err)
		}
	}
	// Grandchildren have no user_id; with a's rows gone only b's must remain.
	for table, want := range map[string]int{
		"workout_exercises": 2, "sets": 4, "program_days": 1, "program_exercises": 1, "program_sets": 1, "exercises": 1,
	} {
		var n int
		if err := conn.QueryRow(`SELECT COUNT(*) FROM ` + table).Scan(&n); err != nil || n != want {
			t.Errorf("%s: %d rows, want %d (%v)", table, n, want, err)
		}
	}
	after, _ := s.CountData(b)
	if after != before {
		t.Errorf("other account changed: %+v -> %+v", before, after)
	}
}

func TestChangeEmail(t *testing.T) {
	setup := func(t *testing.T) (*sql.DB, *UserStore) {
		conn := testDB(t)
		return conn, NewUserStore(conn)
	}
	hashOf := func(t *testing.T, conn *sql.DB, uid int64) string {
		var h string
		if err := conn.QueryRow(`SELECT password_hash FROM users WHERE id = ?`, uid).Scan(&h); err != nil {
			t.Fatal(err)
		}
		return h
	}

	t.Run("another account in another case is taken", func(t *testing.T) {
		conn, s := setup(t)
		seedAccount(t, conn, "[email protected]", 0)
		me := seedAccount(t, conn, "[email protected]", 0)
		if _, err := s.ChangeEmail(me, "x", "[email protected]"); !errors.Is(err, ErrEmailTaken) {
			t.Fatalf("err = %v, want ErrEmailTaken", err)
		}
	})

	t.Run("own case change", func(t *testing.T) {
		conn, s := setup(t)
		me := seedAccount(t, conn, "[email protected]", 0)
		before, _ := s.TokenVersion(me)
		u, err := s.ChangeEmail(me, "x", "[email protected]")
		if err != nil {
			t.Fatal(err)
		}
		if u.Email != "[email protected]" || u.CreatedAt.IsZero() {
			t.Errorf("user = %+v", u)
		}
		if after, _ := s.TokenVersion(me); after != before || u.TokenVersion != before {
			t.Errorf("a case-only change moved token_version %d -> %d (returned %d)", before, after, u.TokenVersion)
		}
	})

	t.Run("stale hash", func(t *testing.T) {
		conn, s := setup(t)
		me := seedAccount(t, conn, "[email protected]", 0)
		if _, err := s.ChangeEmail(me, "stale", "[email protected]"); !errors.Is(err, ErrPasswordChanged) {
			t.Fatalf("err = %v, want ErrPasswordChanged", err)
		}
		var e string
		if err := conn.QueryRow(`SELECT email FROM users WHERE id = ?`, me).Scan(&e); err != nil || e != "[email protected]" {
			t.Errorf("email = %q, %v; want unchanged", e, err)
		}
	})

	t.Run("index absent", func(t *testing.T) {
		conn, s := setup(t)
		dropNocaseIndex(t, conn)
		seedAccount(t, conn, "[email protected]", 0)
		me := seedAccount(t, conn, "[email protected]", 0)
		if _, err := s.ChangeEmail(me, "x", "[email protected]"); !errors.Is(err, ErrEmailTaken) {
			t.Fatalf("err = %v, want ErrEmailTaken", err)
		}
	})

	t.Run("token_version bumped", func(t *testing.T) {
		conn, s := setup(t)
		me := seedAccount(t, conn, "[email protected]", 0)
		before, _ := s.TokenVersion(me)
		u, err := s.ChangeEmail(me, hashOf(t, conn, me), "[email protected]")
		if err != nil {
			t.Fatal(err)
		}
		after, _ := s.TokenVersion(me)
		if after != before+1 {
			t.Errorf("token_version %d -> %d, want +1", before, after)
		}
		if u.TokenVersion != after {
			t.Errorf("returned version %d, stored %d", u.TokenVersion, after)
		}
	})
}