Files
meshcore-analyzer/cmd/server/preparable_schema_test.go
ZachandClaude Opus 5.5 900bffb782 refactor(schema): give observers an explicit idx primary key (#2407)
## Summary

`observations.observer_idx` points at the **implicit rowid** of
`observers`, whose declared key is `id TEXT`. Implicit rowids are not
stable:

- a dump-and-reload (`sqlite3 .dump`, `iterdump`) renumbers them, which
silently re-links observations to the wrong observer;
- PostgreSQL has no rowid at all.

This change makes the key explicit. Split out of #2396 at review's
request, so SQLite installs get it independently of the Postgres work.

## What changes

- **One-time rebuild, in the ingestor.** On first start, `observers` is
rebuilt as `idx INTEGER PRIMARY KEY AUTOINCREMENT`, copying every rowid
into `idx` in one transaction. It fails closed on unknown columns or
NULL ids.
- **Rollback-safe.** In SQLite an `INTEGER PRIMARY KEY` *is* the rowid,
so `rowid == idx` forever. Older binaries that still join on
`observers.rowid` keep working, so both upgrade and rollback are safe.
- **No inherited orphans.** `AUTOINCREMENT` is seeded past
`MAX(observations.observer_idx)`, so a new observer can never inherit a
deleted observer's leftover rows.
- Fresh installs create the new shape directly, and `AssertReady`
requires `observers.idx` as the primary key (a plain column named `idx`
does not pass).
- **Observer writes update first.** With `AUTOINCREMENT`, SQLite
advances `sqlite_sequence` for every INSERT it attempts, even one `ON
CONFLICT` turns into an update. The per-packet observer upsert and the
clock-skew upsert now run an `UPDATE` and fall back to the unchanged
`INSERT ... ON CONFLICT` only for a new observer. So the sequence moves
only when an observer is added, and the next new observer gets `max(idx)
+ 1`.
- `packets_v` now has a single definition
(`dbschema.EnsurePacketsView`).
- Every server and ingestor query, the fixture capture script and the
test schemas use `idx`. Guard tests fail if `observers.rowid` comes
back.

## Operator impact

Every SQLite install gets this on its next ingestor start, with no
config change. The rebuild is one transaction over `observers`, which
holds one row per observer (typically hundreds), so it's quick.

**When the rebuild refuses.** It refuses rather than lose data in two
cases, and then the ingestor stops on every start until the cause is
fixed:

- `observers` has a column CoreScope doesn't know (one added locally).
The rebuild would drop it. Save its data, then drop or rename it: `ALTER
TABLE observers DROP COLUMN <name>`.
- A row has a NULL `id` (the legacy `TEXT PRIMARY KEY` allows one). Find
it with `SELECT rowid, * FROM observers WHERE id IS NULL`, then delete
or fix it.

The error message names the column or the row count and these steps.
Starting the previous version also works: nothing was changed, and
`packets_v` stays readable for external SQLite readers in the meantime.

**A refusal stops the server too, not only ingest.** The server checks
the schema at startup (`AssertReady`) and exits when `observers.idx` is
not the primary key, so the whole site is down until the cause above is
fixed or the previous version is started again. Check for a locally
added column or a NULL `id` before upgrading if you are unsure.

**Ingest cost.** Measured with `BenchmarkUpsertObserverAtExisting`
(`cmd/ingestor`): 20000 `UpsertObserverAt` calls over 500 existing
observers, on a store opened by `OpenStore` with its own pragmas (WAL,
`synchronous=FULL`), Ryzen 3700X in Docker. A plain upsert on the new
table writes 5.23 WAL frames per call; update-first writes 4.23, one
frame less. The pre-PR table was not measured. Time per call is 676 to
726 µs before and 665 to 668 µs after; it is bound by fsync, so the
difference is within noise.

## Testing

- `internal/dbschema/observers_idx_test.go`: the rebuild keeps every
observation linked to the same observer, refuses an unknown column or a
NULL id, never reuses an `idx` (including one only orphaned observations
still reference), is idempotent, and handles the full legacy table.
- `TestObserversIdxPreservesLinksOnE2EFixture`: on a copy of
`test-fixtures/e2e-fixture.db`, 499/499 observations keep the same
observer.
- Refusals and rollback: a refused rebuild leaves the table, its rows,
`foreign_keys` and a readable `packets_v` as they were; a failure after
the old table is dropped rolls all of it back, leaves no
`observers_new`, and the next start completes the rebuild
(`TestObserversIdxRollsBackMidRebuild`,
`TestPacketsViewFollowsObserversShape`).
- Guards: `TestNoObserversRowidInSource` (ingestor) and
`TestNoObserversRowidInServerSources` fail if a query joins on
`observers.rowid` again. `TestFreshObserversMatchesRebuiltShape` pins
that a fresh install and a rebuilt one end up identical.
- `TestUpsertObserverAtLeavesSequenceAlone` and
`TestRecordNaiveSkewLeavesSequenceAlone`: 1000 calls on an existing
observer leave the sequence alone (on the previous head both moved it 1
-> 1001), and a new observer then gets `max(idx) + 1`.
`TestUpdateObserverMatchesUpsert` and `TestUpdateNaiveSkewMatchesUpsert`
check that the update path writes the same row as the upsert (COALESCE
of empty fields, `last_seen` never going back, the `can_relay`
tri-state, the 24 h skew counter).
- `TestAssertReadyRequiresObserversIdxPrimaryKey`: the rebuilt shape
passes; a plain `idx` column or none fails.
`TestOpenStoreRefusesUnconvertibleObservers`: through `OpenStore`, an
unknown column or a NULL id fails the open and leaves the table, its
rows and `packets_v` as they were.
- `go vet` and `go test` pass on all modules (`scripts/allmod.sh`), with
gofmt clean.

🤖 Generated with [Claude Code](https://claude.com/claude-code)



---

## Merge order

These four PRs were split out of #2396 and are **independent — they can
merge in any order.** Verified: every pair is conflict-free, and all
four merged together pass `go vet`, `go test ./...` (all 16 modules) and
`sh test-all.sh`.

Suggested order, none of it urgent:

1. **#2408** — never lose rows during startup / a failed poll.
2. **#2407** — explicit `observers.idx` primary key.
3. **#2409** — content-hash migration moved into the ingestor.
4. **#2410** — pin musl thread stacks at 8 MiB. Released images are not
affected (zig already links them with 8 MiB thread stacks); it fixes
native Alpine builds.

After these land, #2396 (Postgres) will be rebased onto master.

---------

Co-authored-by: Claude Opus 5.5 <noreply@anthropic.com>
2026-11-21 22:27:09 +01:00

78 lines
3.2 KiB
Go

package main
import (
"database/sql"
"strings"
"testing"
)
// The statements compiled by prepareStatements reference a set of tables and
// columns. Under modernc.org/sqlite that never mattered for test fixtures:
// newStmt only stored the SQL string and compiled it lazily on first use, so a
// fixture could omit half the schema and still open. github.com/mattn/go-sqlite3
// calls sqlite3_prepare_v2 inside Prepare, so OpenDB now fails on an incomplete
// schema — which is the production behaviour we want (#1901), but it means
// fixtures have to declare what they are prepared against.
//
// ensurePreparable is the seam. Fixtures that deliberately build a partial
// schema (to exercise schema detection, say) call it on the writable connection
// just before OpenDB, and it fills in whatever is missing without disturbing the
// shapes the test actually cares about.
var preparableTables = []string{
`CREATE TABLE IF NOT EXISTS transmissions (id INTEGER PRIMARY KEY, hash TEXT)`,
`CREATE TABLE IF NOT EXISTS observations (id INTEGER PRIMARY KEY, timestamp TEXT)`,
`CREATE TABLE IF NOT EXISTS nodes (public_key TEXT PRIMARY KEY)`,
`CREATE TABLE IF NOT EXISTS observers (idx INTEGER PRIMARY KEY AUTOINCREMENT, id TEXT NOT NULL UNIQUE)`,
}
// preparableColumns are added when absent. A fixture that already declared the
// table keeps its own definition, so only the missing columns get appended.
var preparableColumns = []struct{ table, column, decl string }{
{"transmissions", "hash", "TEXT"},
{"observations", "timestamp", "TEXT"},
{"nodes", "name", "TEXT"},
{"nodes", "role", "TEXT"},
{"nodes", "last_seen", "TEXT"},
{"observers", "inactive", "INTEGER"},
}
// ensurePreparable makes conn's schema sufficient for prepareStatements.
// It is idempotent and never overwrites an existing table or column.
func ensurePreparable(tb testing.TB, conn *sql.DB) {
tb.Helper()
for _, ddl := range preparableTables {
if _, err := conn.Exec(ddl); err != nil {
tb.Fatalf("ensurePreparable: %v\nSQL: %s", err, ddl)
}
}
for _, c := range preparableColumns {
_, err := conn.Exec("ALTER TABLE " + c.table + " ADD COLUMN " + c.column + " " + c.decl)
// "duplicate column name" means the fixture already declared it.
if err != nil && !strings.Contains(err.Error(), "duplicate column name") {
tb.Fatalf("ensurePreparable: add %s.%s: %v", c.table, c.column, err)
}
}
}
// TestEnsurePreparableMatchesPrepareStatements keeps the helper honest. If a new
// prepared statement references a table or column ensurePreparable does not
// create, it fails here rather than as a confusing OpenDB error in whichever
// fixture happens to be thinnest.
func TestEnsurePreparableMatchesPrepareStatements(t *testing.T) {
conn, err := sql.Open("sqlite3", ":memory:")
if err != nil {
t.Fatal(err)
}
defer conn.Close()
// prepareStatements compiles against a pooled connection; pin the pool so
// the in-memory database the DDL landed in is the one it prepares against.
conn.SetMaxOpenConns(1)
ensurePreparable(t, conn)
db := &DB{conn: conn}
if err := db.prepareStatements(); err != nil {
t.Fatalf("prepareStatements against the minimal preparable schema: %v\n"+
"Add the missing table/column to preparableTables or preparableColumns.", err)
}
}