mirror of
https://github.com/Kpa-clawbot/meshcore-analyzer.git
synced 2026-11-22 07:47:04 +00:00
## 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>
225 lines
8.9 KiB
Go
225 lines
8.9 KiB
Go
package main
|
|
|
|
import (
|
|
"context"
|
|
"fmt"
|
|
"strings"
|
|
"testing"
|
|
"time"
|
|
|
|
"github.com/gorilla/mux"
|
|
)
|
|
|
|
// The Scope Audit had no region selector, unlike every other page. With
|
|
// ?region=<IATA,...> both views count only forwarding heard by observers in
|
|
// those regions: "as heard by", the same meaning region= has on /api/nodes
|
|
// and /api/packets, not "located in".
|
|
|
|
// setupScopeRegionServer is setupScopeAuditServer with observers and the v3
|
|
// observations.observer_idx column, so a hop can be attributed to the
|
|
// observer that heard it.
|
|
func setupScopeRegionServer(t *testing.T) (*Server, *mux.Router) {
|
|
t.Helper()
|
|
srv, router := setupScopeAuditServer(t)
|
|
for _, ddl := range []string{
|
|
`CREATE TABLE observers (idx INTEGER PRIMARY KEY AUTOINCREMENT, id TEXT NOT NULL UNIQUE, name TEXT, iata TEXT)`,
|
|
`ALTER TABLE observations ADD COLUMN observer_idx INTEGER`,
|
|
`INSERT INTO observers (idx, id, name, iata) VALUES (1, 'obs-sfo', 'San Francisco', 'SFO'), (2, 'obs-bru', 'Brussels', 'BRU')`,
|
|
} {
|
|
if _, err := srv.db.conn.Exec(ddl); err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
}
|
|
if err := srv.db.detectSchema(context.Background(), srv.db.conn); err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
if !srv.db.isV3 {
|
|
t.Fatal("fixture must be v3 (observer_idx) or the test exercises nothing")
|
|
}
|
|
return srv, router
|
|
}
|
|
|
|
var heardCounter int
|
|
|
|
// seedHeard inserts one flood transmission forwarded by hop, heard by the
|
|
// observer with the given rowid.
|
|
func seedHeard(t *testing.T, srv *Server, hop string, seed scopeSeed, observerRowid int) {
|
|
t.Helper()
|
|
heardCounter++
|
|
res, err := srv.db.conn.Exec(`INSERT INTO transmissions (raw_hex, hash, first_seen, route_type, payload_type, code1, code2, scope_name)
|
|
VALUES ('AA', ?, ?, ?, 1, ?, '00', ?)`, fmt.Sprintf("heard%d", heardCounter),
|
|
time.Now().UTC().Add(-time.Minute).Format(time.RFC3339), RouteFlood, seed.code1, seed.scopeName)
|
|
if err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
id, _ := res.LastInsertId()
|
|
path := fmt.Sprintf(`["%s"]`, strings.ToUpper(hop))
|
|
if observerRowid == 0 { // v2 fixture: the caller attributes observer_id itself
|
|
_, err = srv.db.conn.Exec(`INSERT INTO observations (transmission_id, path_json, timestamp) VALUES (?, ?, ?)`, id, path, time.Now().Unix())
|
|
} else {
|
|
_, err = srv.db.conn.Exec(`INSERT INTO observations (transmission_id, path_json, timestamp, observer_idx) VALUES (?, ?, ?, ?)`, id, path, time.Now().Unix(), observerRowid)
|
|
}
|
|
if err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
}
|
|
|
|
// A declared "be", heard carrying #be by San Francisco and #de by Brussels.
|
|
func seedRegionalForwarding(t *testing.T, srv *Server) {
|
|
t.Helper()
|
|
insertDeclared(t, srv, testFullPubkeyA, time.Now().UTC().Format(time.RFC3339), "be", 0)
|
|
addRepeater(t, srv, testFullPubkeyA, "Alpha")
|
|
addRepeater(t, srv, testFullPubkeyB, "Bravo")
|
|
seedHeard(t, srv, testFullPubkeyA[:4], scopeMatched("#be"), 1)
|
|
seedHeard(t, srv, testFullPubkeyA[:4], scopeMatched("#de"), 2)
|
|
seedHeard(t, srv, testFullPubkeyB[:4], scopeMatched("#fr"), 2)
|
|
}
|
|
|
|
func TestScopeAudit_RegionCountsOnlyHopsHeardThere(t *testing.T) {
|
|
srv, router := setupScopeRegionServer(t)
|
|
seedRegionalForwarding(t, srv)
|
|
|
|
all := findScopeAuditRow(t, getScopeAudit(t, router, "").Repeaters, testFullPubkeyA)
|
|
if len(all.UndeclaredObserved) != 1 || all.UndeclaredObserved[0].Scope != "de" {
|
|
t.Fatalf("without a region, #de heard in Brussels should be undeclared-observed: %+v", all.UndeclaredObserved)
|
|
}
|
|
sfo := getScopeAudit(t, router, "?region=SFO")
|
|
row := findScopeAuditRow(t, sfo.Repeaters, testFullPubkeyA)
|
|
if len(row.UndeclaredObserved) != 0 {
|
|
t.Errorf("region=SFO must not count #de heard only in Brussels: %+v", row.UndeclaredObserved)
|
|
}
|
|
if len(row.NotObserved) != 0 {
|
|
t.Errorf("region=SFO: #be was heard in San Francisco, notObserved = %v", row.NotObserved)
|
|
}
|
|
if sfo.Region != "SFO" {
|
|
t.Errorf("region = %q, want the normalised codes echoed", sfo.Region)
|
|
}
|
|
}
|
|
|
|
func TestScopeTransport_RegionListsOnlyRepeatersHeardThere(t *testing.T) {
|
|
srv, router := setupScopeRegionServer(t)
|
|
seedRegionalForwarding(t, srv)
|
|
got := getScopeTransport(t, router, "®ion=bru")
|
|
if got.Region != "BRU" {
|
|
t.Errorf("region = %q, want BRU", got.Region)
|
|
}
|
|
a := transportRow(t, got.Repeaters, testFullPubkeyA)
|
|
if len(a.Transported) != 1 || a.Transported[0].Scope != "de" {
|
|
t.Errorf("A as heard in Brussels: transported = %+v, want only de", a.Transported)
|
|
}
|
|
transportRow(t, got.Repeaters, testFullPubkeyB)
|
|
sfo := getScopeTransport(t, router, "®ion=SFO")
|
|
for _, r := range sfo.Repeaters {
|
|
if r.PublicKey == testFullPubkeyB {
|
|
t.Fatal("B was only heard in Brussels and must not be listed for region=SFO")
|
|
}
|
|
}
|
|
}
|
|
|
|
func TestScopeAudit_RegionAllMeansNoFilter(t *testing.T) {
|
|
srv, router := setupScopeRegionServer(t)
|
|
seedRegionalForwarding(t, srv)
|
|
got := getScopeAudit(t, router, "?region=All")
|
|
row := findScopeAuditRow(t, got.Repeaters, testFullPubkeyA)
|
|
if len(row.UndeclaredObserved) != 1 || got.Region != "" {
|
|
t.Fatalf("region=All must behave like no region: undeclared=%+v region=%q", row.UndeclaredObserved, got.Region)
|
|
}
|
|
}
|
|
|
|
func TestScopeAudit_RegionsAreCachedSeparately(t *testing.T) {
|
|
srv, router := setupScopeRegionServer(t)
|
|
seedRegionalForwarding(t, srv)
|
|
getScopeAudit(t, router, "?region=SFO") // fills the SFO entry first
|
|
row := findScopeAuditRow(t, getScopeAudit(t, router, "").Repeaters, testFullPubkeyA)
|
|
if len(row.UndeclaredObserved) != 1 {
|
|
t.Fatalf("the unfiltered view must not be served from the SFO cache entry: %+v", row.UndeclaredObserved)
|
|
}
|
|
tr := getScopeTransport(t, router, "®ion=SFO")
|
|
if len(transportRow(t, tr.Repeaters, testFullPubkeyA).Transported) != 1 {
|
|
t.Fatal("transport view for SFO should count only #be")
|
|
}
|
|
}
|
|
|
|
func TestScopeAudit_RegionWithNoObserversMatchesNothing(t *testing.T) {
|
|
srv, router := setupScopeRegionServer(t)
|
|
seedRegionalForwarding(t, srv)
|
|
got := getScopeTransport(t, router, "®ion=ZZZ")
|
|
if len(got.Repeaters) != 0 {
|
|
t.Fatalf("a region with no observers must match no forwarding, got %d rows", len(got.Repeaters))
|
|
}
|
|
}
|
|
|
|
// On the v2 schema an observation names its observer by observers.id in
|
|
// observer_id rather than by rowid; the filter must follow it.
|
|
func TestScopeTransport_RegionOnV2Schema(t *testing.T) {
|
|
srv, router := setupScopeAuditServer(t)
|
|
for _, ddl := range []string{
|
|
`CREATE TABLE observers (idx INTEGER PRIMARY KEY AUTOINCREMENT, id TEXT NOT NULL UNIQUE, name TEXT, iata TEXT)`,
|
|
`ALTER TABLE observations ADD COLUMN observer_id TEXT`,
|
|
`INSERT INTO observers (id, name, iata) VALUES ('obs-sfo', 'San Francisco', 'SFO'), ('obs-bru', 'Brussels', 'BRU')`,
|
|
} {
|
|
if _, err := srv.db.conn.Exec(ddl); err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
}
|
|
if err := srv.db.detectSchema(context.Background(), srv.db.conn); err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
if srv.db.isV3 {
|
|
t.Fatal("fixture must be v2")
|
|
}
|
|
addRepeater(t, srv, testFullPubkeyA, "Alpha")
|
|
addRepeater(t, srv, testFullPubkeyB, "Bravo")
|
|
seedHeard(t, srv, testFullPubkeyA[:4], scopeMatched("#be"), 0)
|
|
seedHeard(t, srv, testFullPubkeyB[:4], scopeMatched("#fr"), 0)
|
|
if _, err := srv.db.conn.Exec(`UPDATE observations SET observer_id = CASE WHEN path_json LIKE ? THEN 'obs-sfo' ELSE 'obs-bru' END`,
|
|
`%`+strings.ToUpper(testFullPubkeyA[:4])+`%`); err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
got := getScopeTransport(t, router, "®ion=SFO")
|
|
if len(got.Repeaters) != 1 || got.Repeaters[0].PublicKey != testFullPubkeyA {
|
|
t.Fatalf("v2 region=SFO should list only A, got %+v", got.Repeaters)
|
|
}
|
|
}
|
|
|
|
// With an index on observations(observer_idx), as every real database has,
|
|
// SQLite drives "o.observer_idx IN (...)" from that index: every observation
|
|
// the region's observers ever made, then the window check per row. On a
|
|
// live-shaped database that was 13.4s for one region over 24h against 0.25s when
|
|
// the scan starts from the first_seen window as the unfiltered scan does.
|
|
func TestScopeAudit_RegionScanKeepsTheTimeIndex(t *testing.T) {
|
|
srv, _ := setupScopeRegionServer(t)
|
|
for _, ddl := range []string{
|
|
`CREATE INDEX IF NOT EXISTS idx_transmissions_first_seen ON transmissions(first_seen)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_observations_transmission_id ON observations(transmission_id)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_observations_observer_idx ON observations(observer_idx)`,
|
|
} {
|
|
if _, err := srv.db.conn.Exec(ddl); err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
}
|
|
clause, args, err := srv.db.observerRegionClause(srv.db.conn, []string{"SFO"})
|
|
if err != nil || clause == "" {
|
|
t.Fatalf("clause=%q err=%v", clause, err)
|
|
}
|
|
rows, err := srv.db.conn.Query("EXPLAIN QUERY PLAN "+scopeAuditForwarderScanQuery+clause,
|
|
append([]interface{}{"2000-01-01T00:00:00Z"}, args...)...)
|
|
if err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
defer rows.Close()
|
|
var plan []string
|
|
for rows.Next() {
|
|
var id, parent, notused int
|
|
var detail string
|
|
if err := rows.Scan(&id, &parent, ¬used, &detail); err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
plan = append(plan, detail)
|
|
}
|
|
joined := strings.Join(plan, " | ")
|
|
if strings.Contains(joined, "idx_observations_observer_idx") || !strings.Contains(joined, "idx_transmissions_first_seen") {
|
|
t.Fatalf("the region scan must start from the first_seen window, not the observer index: %s", joined)
|
|
}
|
|
}
|