steve
293532f021
Build image / build-and-push (push) Successful in 5s
Co-authored-by: Steve Dudenhoeffer <[email protected]>
233 lines
7.5 KiB
Go
233 lines
7.5 KiB
Go
package store
|
|
|
|
import (
|
|
"context"
|
|
"errors"
|
|
"path/filepath"
|
|
"strings"
|
|
"testing"
|
|
|
|
"gitea.stevedudenhoeffer.com/steve/pansy/internal/domain"
|
|
)
|
|
|
|
// openTestDB opens a fresh file-backed DB in a temp dir and migrates it.
|
|
func openTestDB(t *testing.T) *DB {
|
|
t.Helper()
|
|
db, err := Open(filepath.Join(t.TempDir(), "test.db"))
|
|
if err != nil {
|
|
t.Fatalf("Open: %v", err)
|
|
}
|
|
t.Cleanup(func() { db.Close() })
|
|
if err := db.Migrate(context.Background()); err != nil {
|
|
t.Fatalf("Migrate: %v", err)
|
|
}
|
|
return db
|
|
}
|
|
|
|
func TestMigrateCreatesSchema(t *testing.T) {
|
|
db := openTestDB(t)
|
|
|
|
want := []string{
|
|
"users", "sessions", "gardens", "garden_shares",
|
|
"garden_objects", "plants", "plantings", "schema_migrations",
|
|
}
|
|
for _, table := range want {
|
|
var name string
|
|
err := db.SQL().QueryRow(
|
|
`SELECT name FROM sqlite_master WHERE type='table' AND name=?`, table,
|
|
).Scan(&name)
|
|
if err != nil {
|
|
t.Errorf("expected table %q to exist: %v", table, err)
|
|
}
|
|
}
|
|
|
|
// Derive the expected head version from the embedded files so adding a
|
|
// migration doesn't break this test.
|
|
migs, err := loadMigrations()
|
|
if err != nil {
|
|
t.Fatalf("loadMigrations: %v", err)
|
|
}
|
|
wantVersion := migs[len(migs)-1].version
|
|
var version int
|
|
if err := db.SQL().QueryRow(`SELECT max(version) FROM schema_migrations`).Scan(&version); err != nil {
|
|
t.Fatalf("read schema_migrations: %v", err)
|
|
}
|
|
if version != wantVersion {
|
|
t.Errorf("schema version = %d, want %d", version, wantVersion)
|
|
}
|
|
}
|
|
|
|
func TestMigrateIsIdempotent(t *testing.T) {
|
|
db := openTestDB(t)
|
|
|
|
// A second Migrate must be a no-op and leave exactly one row per migration.
|
|
if err := db.Migrate(context.Background()); err != nil {
|
|
t.Fatalf("second Migrate: %v", err)
|
|
}
|
|
migs, err := loadMigrations()
|
|
if err != nil {
|
|
t.Fatalf("loadMigrations: %v", err)
|
|
}
|
|
var count int
|
|
if err := db.SQL().QueryRow(`SELECT count(*) FROM schema_migrations`).Scan(&count); err != nil {
|
|
t.Fatalf("count schema_migrations: %v", err)
|
|
}
|
|
if count != len(migs) {
|
|
t.Errorf("schema_migrations rows = %d, want %d", count, len(migs))
|
|
}
|
|
}
|
|
|
|
func TestForeignKeysEnforced(t *testing.T) {
|
|
db := openTestDB(t)
|
|
|
|
// The DSN sets foreign_keys(1) per connection; a planting referencing a
|
|
// nonexistent object must be rejected. This guards the pragma actually
|
|
// reaching the app's pooled connections, not just the sqlite3 CLI.
|
|
_, err := db.SQL().Exec(
|
|
`INSERT INTO plantings (object_id, plant_id, x_cm, y_cm, radius_cm) VALUES (999, 999, 0, 0, 10)`,
|
|
)
|
|
if err == nil {
|
|
t.Fatal("expected foreign-key violation, got nil")
|
|
}
|
|
}
|
|
|
|
func TestDeletePlantForeignKeyBackstop(t *testing.T) {
|
|
db := openTestDB(t)
|
|
ctx := context.Background()
|
|
|
|
// Seed a user → garden → object → custom plant, then a planting referencing
|
|
// the plant. DeletePlant must surface the ON DELETE RESTRICT FK as
|
|
// ErrPlantInUse (not a raw 500-worthy error) — this pins isForeignKeyViolation's
|
|
// string-match so a driver-message change is caught here rather than in prod.
|
|
// The service normally blocks this via a count pre-check; here we hit the
|
|
// store backstop directly (the lost-race path).
|
|
insert := func(q string, args ...any) int64 {
|
|
res, err := db.SQL().ExecContext(ctx, q, args...)
|
|
if err != nil {
|
|
t.Fatalf("seed exec %q: %v", q, err)
|
|
}
|
|
id, err := res.LastInsertId()
|
|
if err != nil {
|
|
t.Fatalf("last insert id: %v", err)
|
|
}
|
|
return id
|
|
}
|
|
uid := insert(`INSERT INTO users (email, display_name) VALUES ('[email protected]', 'A')`)
|
|
gid := insert(`INSERT INTO gardens (owner_id, name, width_cm, height_cm) VALUES (?, 'G', 100, 100)`, uid)
|
|
oid := insert(`INSERT INTO garden_objects (garden_id, kind, x_cm, y_cm, width_cm, height_cm) VALUES (?, 'bed', 0, 0, 10, 10)`, gid)
|
|
pid := insert(`INSERT INTO plants (owner_id, name, category, spacing_cm, color, icon) VALUES (?, 'Basil', 'herb', 25, '#4a7c3f', '🌿')`, uid)
|
|
insert(`INSERT INTO plantings (object_id, plant_id, x_cm, y_cm, radius_cm) VALUES (?, ?, 0, 0, 10)`, oid, pid)
|
|
|
|
if err := db.DeletePlant(ctx, pid); !errors.Is(err, domain.ErrPlantInUse) {
|
|
t.Fatalf("DeletePlant on referenced plant = %v, want ErrPlantInUse", err)
|
|
}
|
|
|
|
// Clearing the reference lets the delete succeed.
|
|
insert(`DELETE FROM plantings WHERE plant_id = ?`, pid)
|
|
if err := db.DeletePlant(ctx, pid); err != nil {
|
|
t.Errorf("DeletePlant after clearing reference: %v", err)
|
|
}
|
|
}
|
|
|
|
func TestCheckConstraintRejectsBadEnum(t *testing.T) {
|
|
db := openTestDB(t)
|
|
|
|
// Seed a user + garden so the FK is satisfied and only the CHECK can fail.
|
|
res, err := db.SQL().Exec(
|
|
`INSERT INTO users (email, display_name) VALUES ('[email protected]', 'A')`)
|
|
if err != nil {
|
|
t.Fatalf("insert user: %v", err)
|
|
}
|
|
uid, _ := res.LastInsertId()
|
|
res, err = db.SQL().Exec(
|
|
`INSERT INTO gardens (owner_id, name, width_cm, height_cm) VALUES (?, 'G', 100, 100)`, uid)
|
|
if err != nil {
|
|
t.Fatalf("insert garden: %v", err)
|
|
}
|
|
gid, _ := res.LastInsertId()
|
|
|
|
// kind='invalid' violates the CHECK constraint.
|
|
_, err = db.SQL().Exec(
|
|
`INSERT INTO garden_objects (garden_id, kind, x_cm, y_cm, width_cm, height_cm)
|
|
VALUES (?, 'invalid', 0, 0, 10, 10)`, gid)
|
|
if err == nil {
|
|
t.Fatal("expected CHECK constraint violation for bad kind, got nil")
|
|
}
|
|
}
|
|
|
|
func TestForeignKeysEnforcedForFileURIDSN(t *testing.T) {
|
|
// A file: URI (or any path with query params) must still get foreign_keys(1):
|
|
// the earlier passthrough silently dropped the pragmas for these DSNs.
|
|
p := filepath.Join(t.TempDir(), "uri.db")
|
|
db, err := Open("file:" + p + "?_pragma=synchronous(1)")
|
|
if err != nil {
|
|
t.Fatalf("Open file: URI: %v", err)
|
|
}
|
|
t.Cleanup(func() { db.Close() })
|
|
if err := db.Migrate(context.Background()); err != nil {
|
|
t.Fatalf("Migrate: %v", err)
|
|
}
|
|
_, err = db.SQL().Exec(
|
|
`INSERT INTO plantings (object_id, plant_id, x_cm, y_cm, radius_cm) VALUES (999, 999, 0, 0, 10)`,
|
|
)
|
|
if err == nil {
|
|
t.Fatal("expected foreign-key violation for file: URI DSN, got nil")
|
|
}
|
|
}
|
|
|
|
func TestBuildDSNAlwaysIncludesPragmas(t *testing.T) {
|
|
for _, in := range []string{
|
|
"./pansy.db",
|
|
"/data/pansy.db",
|
|
"file:/data/pansy.db",
|
|
"file:/data/pansy.db?cache=shared",
|
|
"/tmp/weird?name.db",
|
|
} {
|
|
dsn, _ := buildDSN(in)
|
|
for _, want := range []string{"busy_timeout", "journal_mode", "foreign_keys"} {
|
|
if !strings.Contains(dsn, want) {
|
|
t.Errorf("buildDSN(%q) = %q, missing %s pragma", in, dsn, want)
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
func TestBuildDSNDoesNotDuplicateUserPragma(t *testing.T) {
|
|
// A user-supplied busy_timeout must not be duplicated by our default.
|
|
dsn, _ := buildDSN("file:/data/x.db?_pragma=busy_timeout(9000)")
|
|
if strings.Count(dsn, "busy_timeout") != 1 {
|
|
t.Errorf("buildDSN kept both user and default busy_timeout: %q", dsn)
|
|
}
|
|
}
|
|
|
|
func TestBuildDSNMemoryDetection(t *testing.T) {
|
|
if _, mem := buildDSN(":memory:"); !mem {
|
|
t.Error(":memory: not detected as in-memory")
|
|
}
|
|
if _, mem := buildDSN("file:x.db?mode=memory"); !mem {
|
|
t.Error("mode=memory not detected as in-memory")
|
|
}
|
|
if _, mem := buildDSN("/var/lib/pansy/pansy.db"); mem {
|
|
t.Error("file path wrongly detected as in-memory")
|
|
}
|
|
}
|
|
|
|
func TestInMemoryDBIsCoherentAcrossQueries(t *testing.T) {
|
|
// A bare ":memory:" DSN gives each connection its own DB; Open must pin the
|
|
// pool so the migrated schema is visible to subsequent queries.
|
|
db, err := Open(":memory:")
|
|
if err != nil {
|
|
t.Fatalf("Open(:memory:): %v", err)
|
|
}
|
|
defer db.Close()
|
|
if err := db.Migrate(context.Background()); err != nil {
|
|
t.Fatalf("Migrate: %v", err)
|
|
}
|
|
var name string
|
|
if err := db.SQL().QueryRow(
|
|
`SELECT name FROM sqlite_master WHERE type='table' AND name='gardens'`,
|
|
).Scan(&name); err != nil {
|
|
t.Fatalf("in-memory schema not visible: %v", err)
|
|
}
|
|
}
|