textmachine/backend/internal/store/sqlgate_test.go

package store

import (
	"database/sql"
	"fmt"
	"go/ast"
	"go/parser"
	"go/token"
	"path/filepath"
	"slices"
	"strconv"
	"strings"
	"testing"
)

// sqlgate: every SQL statement this package can execute is COMPILED BY SQLITE against the schema the
// migration chain builds, on every battery run.
//
// The class it closes is "the migration and the query drifted apart" — a column renamed in a migration
// while the SQL that reads it was not, which SQLite reports only when something executes that path. A
// behavioural test finds it only if it happens to run that statement, so the coverage of this class was
// previously the coverage of the test suite, which is not the same thing and nobody was tracking the
// difference. Here every statement is compiled whether or not any test reaches it.
//
// The schema is raised by running the product's OWN migration chain into a temporary file, so the gate
// checks against exactly what a real project converges to rather than against a copy of the DDL that
// could itself drift. That also makes the chain self-checking: a malformed migration fails this test
// before any statement is looked at. Nothing external is required — SQLite's schema is a file, so the
// gate holds on a clean machine with no stand, no environment and no manual setup.
//
// Compilation rather than execution: db.Prepare resolves every table, column and function name against
// the schema and reports what is missing, while leaving `?` parameters unbound. Executing instead would
// force the gate to invent values, and inventing them is how a gate starts asserting about types nobody
// wrote down — and here it would also mean writing to the database it is inspecting.
//
// WHAT IT DOES NOT PROVE. That a statement is the RIGHT statement: a query naming only existing columns
// passes here however wrong its logic, its joins or its ordering. That the values bound to `?` have the
// right types — preparation leaves them unknown on purpose. That a statement's result is scanned into
// the right Go fields, which is the ordinary business of the behavioural tests. And it says nothing
// about paths a query takes at RUNTIME beyond the variants folded below: a statement this gate cannot
// read is reported rather than skipped, so the boundary of what it sees is asserted, not assumed.
func TestEverySQLStatementPreparesAgainstTheMigratedSchema(t *testing.T) {
	stmts := collectSQL(t)
	// A floor on the extractor itself. Without it, an extractor that quietly stopped finding call sites
	// would turn this gate into a green light over nothing. The number is the measured population minus
	// a small margin for ordinary churn: 70 statements resolve today, and a drop below 65 is a collapse
	// rather than an edit.
	if len(stmts) < 65 {
		t.Fatalf("the extractor resolved %d statements: it has stopped seeing this package, and a gate that checks nothing passes", len(stmts))
	}
	db := migratedSchema(t)
	for _, st := range stmts {
		if err := compiles(db, st.sql); err != nil {
			t.Errorf("%s: SQLite cannot compile this statement against the migrated schema: %v\n%s",
				st.where, err, indentSQL(st.sql))
		}
	}
	t.Logf("%d statements compiled against the schema at v%d", len(stmts), SchemaHead())
}

// compiles hands one statement to SQLite for compilation and throws the result away. The prepared
// statement is released here rather than at the end of the run: the population is the whole package,
// and holding every one of them open would make the gate's own footprint grow with the thing it reads.
func compiles(db *sql.DB, query string) error {
	stmt, err := db.Prepare(query)
	if err != nil {
		return err
	}
	defer stmt.Close()
	return nil
}

// migratedSchema builds a project database with the product's own chain and hands back a connection to
// it. Open is used rather than a hand-rolled sequence of CREATEs precisely so that the thing under
// inspection is the schema the engine really produces.
func migratedSchema(t *testing.T) *sql.DB {
	t.Helper()
	path := filepath.Join(t.TempDir(), "schema.db")
	s, err := Open(path)
	if err != nil {
		t.Fatalf("building the schema to check against: %v", err)
	}
	t.Cleanup(func() { s.Close() })
	// The migration chain is executed here as whole multi-statement scripts, which is why its own SQL is
	// not compiled statement by statement below: running it IS the check, and a chain that cannot build
	// its schema fails the line above. Asserting the version closes the gap between "Open returned" and
	// "the chain actually ran to head".
	var v int
	if err := s.r.QueryRow(`SELECT COALESCE(MAX(version), 0) FROM schema_version`).Scan(&v); err != nil {
		t.Fatalf("reading the schema version of the database this gate checks against: %v", err)
	}
	if v != SchemaHead() {
		t.Fatalf("the gate's database is at v%d, the binary's head is v%d: the statements below would be checked against a schema no run of this build produces", v, SchemaHead())
	}
	return s.r
}

// statement is one SQL string this package can execute, and where it comes from.
type statement struct {
	where string
	sql   string
}

// sqlCalls maps a call whose argument is SQL to the index of that argument. queryAll is the package's
// own read helper; it is listed rather than excused, because ten statements reach the driver through it
// and a gate that skipped them would skip most of the read models.
//
// Prepare and PrepareContext carry no SQL in this package today. They are listed anyway, because the
// point of the map is to name EVERY database/sql entry point through which SQL can reach the driver: an
// unlisted one is not a failure, it is silence, and the floor below cannot see a statement that was
// never counted in the first place.
var sqlCalls = map[string]int{
	"ExecContext": 1, "QueryContext": 1, "QueryRowContext": 1, "PrepareContext": 1,
	"Exec": 0, "Query": 0, "QueryRow": 0, "Prepare": 0,
	"queryAll": 1,
}

// sqlHelpers are the functions in this package that take SQL as a PARAMETER. Their bodies execute
// whatever a caller hands them, so the body is not a site of its own — the call sites are. Skipped
// bodies are counted, and the count is asserted, so the skip cannot become the hole it exists to avoid.
var sqlHelpers = map[string]bool{"queryAll": true}

// unresolvable is the call site whose SQL genuinely cannot be folded from source, with the shape it
// takes at runtime. It is a TABLE and not a skip: an entry has to be written by hand, so a second such
// site is somebody's deliberate decision rather than a hole that opened quietly.
//
// BackupSQLite builds `VACUUM INTO '<path>'` around a path chosen at runtime. The path is data, not
// schema — the statement names no table and no column — so what the entry preserves is the only part a
// schema gate can speak about: that the statement itself still compiles.
//
// ⚠ Keyed by the enclosing FUNCTION, not by a line number: a line number moves whenever somebody edits
// the file above it, and a different statement would then inherit the entry and be checked against text
// that is not its own. An entry covers exactly ONE site.
//
// ⚠ An entry is ANCHORED to its function's source: some string literal of that function must appear in
// the entry. A hand-written string is the one thing here that cannot drift with the code it describes,
// and without the anchor a statement could be rewritten while the gate went on compiling the old text
// and reporting it green.
var unresolvable = map[string]string{
	"BackupSQLite": `VACUUM INTO '/tmp/tm-sqlgate-representative.db'`,
}

// migrationExecutors are the functions whose SQL is the migration chain itself. They are named here, and
// their presence is asserted, because their argument is a whole multi-statement script: compiling it as
// one statement is not possible, and it needs no compiling — migratedSchema runs the chain, so a chain
// that does not build fails this test before a single query is read.
var migrationExecutors = map[string]bool{"applyStep": true}

// collectSQL folds every SQL string in the package's non-test source.
//
// Folding rather than executing: the statements are literals, named constants, concatenations, one
// table name ranging over a literal list and one query assembled by conditional appends. All of it is
// decided at compile time, which is why a static reader can see all of it while a runtime one would see
// only what the tests happened to run.
func collectSQL(t *testing.T) []statement {
	t.Helper()
	sources, err := filepath.Glob("*.go")
	if err != nil {
		t.Fatal(err)
	}
	usedException := map[string]int{}
	usedMigrationExecutor := map[string]int{}
	t.Cleanup(func() {
		// Every entry of the exception table has to be USED. An entry for a function that no longer has
		// unfoldable SQL is an exception standing over a statement nobody checks any more.
		for name := range unresolvable {
			if usedException[name] == 0 {
				t.Errorf("`unresolvable` still excuses %s, which no longer needs it: the exception now covers nothing and hides whatever moves under it", name)
			}
		}
		for name := range migrationExecutors {
			if usedMigrationExecutor[name] == 0 {
				t.Errorf("`migrationExecutors` still names %s, which no longer executes the chain: the schema it stands for is now checked by nothing", name)
			}
		}
	})
	fset := token.NewFileSet()
	var files []*ast.File
	for _, path := range sources {
		if strings.HasSuffix(path, "_test.go") {
			continue
		}
		f, err := parser.ParseFile(fset, path, nil, 0)
		if err != nil {
			t.Fatalf("%s: %v", path, err)
		}
		files = append(files, f)
	}
	// ⚠ TOP-LEVEL declarations only. Walking a whole file would descend into every function body, where
	// short names like `q` belong to a dozen different statements, and a query could then be checked
	// against another query's text and pass.
	pkgConst := map[string]ast.Expr{}
	for _, f := range files {
		for _, decl := range f.Decls {
			if g, ok := decl.(*ast.GenDecl); ok {
				collectConsts(g, pkgConst)
			}
		}
	}
	var out []statement
	helperCalls := map[string]int{}
	helperBodies := 0
	for _, f := range files {
		for _, decl := range f.Decls {
			fn, ok := decl.(*ast.FuncDecl)
			if !ok || fn.Body == nil {
				continue
			}
			if sqlHelpers[fn.Name.Name] {
				helperBodies++
				continue
			}
			if migrationExecutors[fn.Name.Name] {
				usedMigrationExecutor[fn.Name.Name]++
				continue
			}
			// A name declared inside the function wins over a package-level one of the same name, so
			// the narrow scope is consulted first — short names like `q` are reused across this
			// package, and resolving one against another file's constant would check the wrong text.
			scope := map[string]ast.Expr{}
			collectConsts(fn, scope)
			env := &foldEnv{
				scopes:   []map[string]ast.Expr{scope, pkgConst},
				ranged:   rangeStrings(fn),
				appended: appendedStrings(t, fset, fn),
			}
			ast.Inspect(fn, func(n ast.Node) bool {
				call, ok := n.(*ast.CallExpr)
				if !ok {
					return true
				}
				name := calleeName(call)
				at, ok := sqlCalls[name]
				if !ok || at >= len(call.Args) {
					return true
				}
				if sqlHelpers[name] {
					helperCalls[name]++
				}
				where := shortPos(fset, call.Pos())
				variants, ok := env.foldAll(call.Args[at])
				if !ok {
					if known, listed := unresolvable[fn.Name.Name]; listed {
						if !anchoredInSource(fn, known) {
							t.Errorf("%s: the entry in `unresolvable` for %s no longer shares any literal with the function's source, so it describes a statement that is no longer there",
								where, fn.Name.Name)
							return true
						}
						usedException[fn.Name.Name]++
						if usedException[fn.Name.Name] > 1 {
							t.Errorf("%s: %s holds a second statement this gate cannot read, and the entry in `unresolvable` describes the first: checking it against that string would prove nothing about it",
								where, fn.Name.Name)
							return true
						}
						out = append(out, statement{where: where, sql: known})
						return true
					}
					// NOT skipped. A statement this gate cannot read is a statement nothing checks, and
					// the whole point is that there is no such thing by accident.
					t.Errorf("%s: the SQL handed to %s cannot be folded from source, so nothing checks it against the schema; make it a constant expression, or list it in `unresolvable` with the string it takes",
						where, name)
					return true
				}
				for i, sql := range variants {
					at := where
					if len(variants) > 1 {
						at = fmt.Sprintf("%s#%d", where, i+1)
					}
					out = append(out, statement{where: at, sql: sql})
				}
				return true
			})
		}
	}
	for name := range sqlHelpers {
		if helperCalls[name] == 0 {
			t.Errorf("no call site of %s was found, and its body is skipped: the statements it executes are checked by nothing", name)
		}
	}
	if helperBodies == 0 {
		t.Error("no helper body was skipped: this gate no longer matches the package it reads")
	}
	slices.SortFunc(out, func(a, b statement) int { return strings.Compare(a.where, b.where) })
	return out
}

// foldEnv is everything needed to turn one function's SQL expressions into the set of strings they can
// take: named constants in scope, loop variables bound to literal lists, and locally assembled strings.
type foldEnv struct {
	scopes   []map[string]ast.Expr
	ranged   map[string][]string
	appended map[string][]string
}

// foldAll returns every string an expression can evaluate to. A set rather than a single value, because
// a statement built around a loop variable or a conditional append is genuinely several statements, and
// checking one of them would leave the others as unguarded as before this gate existed.
func (e *foldEnv) foldAll(x ast.Expr) ([]string, bool) {
	switch v := x.(type) {
	case *ast.BasicLit:
		if v.Kind != token.STRING {
			return nil, false
		}
		s, err := strconv.Unquote(v.Value)
		if err != nil {
			return nil, false
		}
		return []string{s}, true
	case *ast.ParenExpr:
		return e.foldAll(v.X)
	case *ast.BinaryExpr:
		if v.Op != token.ADD {
			return nil, false
		}
		left, okl := e.foldAll(v.X)
		right, okr := e.foldAll(v.Y)
		if !okl || !okr {
			return nil, false
		}
		var out []string
		for _, l := range left {
			for _, r := range right {
				out = append(out, l+r)
			}
		}
		return out, true
	case *ast.Ident:
		if vals, ok := e.ranged[v.Name]; ok {
			return vals, true
		}
		if vals, ok := e.appended[v.Name]; ok {
			return vals, true
		}
		for _, scope := range e.scopes {
			if def, ok := scope[v.Name]; ok {
				return e.foldAll(def)
			}
		}
	}
	return nil, false
}

// rangeStrings binds a loop variable to the literal list it ranges over — `for _, table := range
// []string{"a", "b"}`. Every element is a statement of its own: a table renamed in a migration breaks
// exactly one of them, and a gate that folded only the first would report the other three as fine.
func rangeStrings(fn *ast.FuncDecl) map[string][]string {
	out := map[string][]string{}
	ast.Inspect(fn, func(n ast.Node) bool {
		rng, ok := n.(*ast.RangeStmt)
		if !ok || rng.Value == nil {
			return true
		}
		name, ok := rng.Value.(*ast.Ident)
		if !ok {
			return true
		}
		lit, ok := rng.X.(*ast.CompositeLit)
		if !ok {
			return true
		}
		var vals []string
		for _, elt := range lit.Elts {
			bl, ok := elt.(*ast.BasicLit)
			if !ok || bl.Kind != token.STRING {
				return true
			}
			s, err := strconv.Unquote(bl.Value)
			if err != nil {
				return true
			}
			vals = append(vals, s)
		}
		if len(vals) == 0 {
			return true
		}
		if prior, seen := out[name.Name]; seen && !slices.Equal(prior, vals) {
			out[name.Name] = nil // two lists under one name: neither is safe to attribute to a call site
			return true
		}
		if _, blanked := out[name.Name]; !blanked || out[name.Name] != nil {
			out[name.Name] = vals
		}
		return true
	})
	for name, vals := range out {
		if vals == nil {
			delete(out, name)
		}
	}
	return out
}

// anchoredInSource reports whether the function still contains a string literal that the hand-written
// exception is built around. Short literals are ignored: a lone quote or a format verb would anchor
// anything to anything.
func anchoredInSource(fn *ast.FuncDecl, entry string) bool {
	found := false
	ast.Inspect(fn, func(n ast.Node) bool {
		lit, ok := n.(*ast.BasicLit)
		if !ok || lit.Kind != token.STRING {
			return true
		}
		s, err := strconv.Unquote(lit.Value)
		if err != nil || len(s) < 8 {
			return true
		}
		if strings.Contains(entry, s) {
			found = true
		}
		return true
	})
	return found
}

// appendedStrings folds a query assembled by `q := "…"` followed by `q += "…"`, and returns every form
// the finished string can take.
//
// An append guarded by an `if` is OPTIONAL, so the variable takes two values at that point and both are
// checked: the filter clause of such a query names real columns, and it is the branch a caller reaches
// only sometimes that a gate is most useful for. Anything else that writes to the variable — an append
// inside a loop, whose repetition count is not a compile-time fact, or a value that is not foldable —
// makes the variable UNREADABLE rather than half-read, and the site is then reported by the caller.
func appendedStrings(t *testing.T, fset *token.FileSet, fn *ast.FuncDecl) map[string][]string {
	t.Helper()
	built := map[string][]string{}
	poisoned := map[string]bool{}
	defined := map[string]int{}
	env := &foldEnv{scopes: []map[string]ast.Expr{{}}}

	var walk func(n ast.Node, conditional bool)
	walk = func(n ast.Node, conditional bool) {
		switch v := n.(type) {
		case *ast.AssignStmt:
			if len(v.Lhs) != 1 || len(v.Rhs) != 1 {
				return
			}
			name, ok := v.Lhs[0].(*ast.Ident)
			if !ok {
				return
			}
			vals, folded := env.foldAll(v.Rhs[0])
			switch v.Tok {
			case token.DEFINE, token.ASSIGN:
				if v.Tok == token.DEFINE {
					// A name declared twice in one function is a shadow or a reuse, and either way the
					// fold can no longer say which declaration a given call site sees. Attributing the
					// wrong string to a statement is worse than admitting the statement is unreadable.
					defined[name.Name]++
					if defined[name.Name] > 1 {
						poisoned[name.Name] = true
						return
					}
				}
				if !folded {
					// Only a string-shaped assignment can poison: a variable that never held SQL is not
					// this function's business.
					if _, tracked := built[name.Name]; tracked {
						poisoned[name.Name] = true
					}
					return
				}
				if conditional {
					poisoned[name.Name] = true // a whole statement chosen in a branch is not an append
					return
				}
				built[name.Name] = vals
			case token.ADD_ASSIGN:
				base, tracked := built[name.Name]
				if !tracked {
					return
				}
				if !folded {
					poisoned[name.Name] = true
					return
				}
				var next []string
				if conditional {
					next = append(next, base...) // the branch not taken
				}
				for _, b := range base {
					for _, add := range vals {
						next = append(next, b+add)
					}
				}
				built[name.Name] = next
			}
		case *ast.IfStmt:
			for _, stmt := range v.Body.List {
				walk(stmt, true)
			}
			if v.Else != nil {
				walk(v.Else, true)
			}
			return
		case *ast.RangeStmt, *ast.ForStmt, *ast.SwitchStmt, *ast.TypeSwitchStmt, *ast.SelectStmt:
			// A body that may run any number of times cannot be folded into a finite set of strings.
			ast.Inspect(n, func(inner ast.Node) bool {
				as, ok := inner.(*ast.AssignStmt)
				if !ok || len(as.Lhs) != 1 {
					return true
				}
				if id, ok := as.Lhs[0].(*ast.Ident); ok {
					if _, tracked := built[id.Name]; tracked {
						poisoned[id.Name] = true
					}
				}
				return true
			})
			return
		case *ast.BlockStmt:
			for _, stmt := range v.List {
				walk(stmt, conditional)
			}
			return
		}
	}
	for _, stmt := range fn.Body.List {
		walk(stmt, false)
	}
	for name := range poisoned {
		delete(built, name)
	}
	return built
}

// collectConsts records every string const and var whose value is a constant expression.
func collectConsts(n ast.Node, into map[string]ast.Expr) {
	ast.Inspect(n, func(n ast.Node) bool {
		decl, ok := n.(*ast.GenDecl)
		if !ok || (decl.Tok != token.CONST && decl.Tok != token.VAR) {
			return true
		}
		for _, spec := range decl.Specs {
			vs, ok := spec.(*ast.ValueSpec)
			if !ok {
				continue
			}
			for i, name := range vs.Names {
				if i < len(vs.Values) {
					into[name.Name] = vs.Values[i]
				}
			}
		}
		return true
	})
}

// calleeName is the identifier a call names, whether it is a method, a plain function or a generic one
// written with explicit type arguments.
func calleeName(call *ast.CallExpr) string {
	switch fn := call.Fun.(type) {
	case *ast.SelectorExpr:
		return fn.Sel.Name
	case *ast.Ident:
		return fn.Name
	case *ast.IndexExpr:
		if id, ok := fn.X.(*ast.Ident); ok {
			return id.Name
		}
	case *ast.IndexListExpr:
		if id, ok := fn.X.(*ast.Ident); ok {
			return id.Name
		}
	}
	return ""
}

func shortPos(fset *token.FileSet, p token.Pos) string {
	pos := fset.Position(p)
	return fmt.Sprintf("%s:%d", filepath.Base(pos.Filename), pos.Line)
}

func indentSQL(sql string) string {
	return "\t" + strings.ReplaceAll(strings.TrimSpace(sql), "\n", "\n\t")
}