mirror of
https://github.com/Kpa-clawbot/meshcore-analyzer.git
synced 2026-11-22 03:17: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>
152 lines
5.7 KiB
Go
152 lines
5.7 KiB
Go
package main
|
|
|
|
import (
|
|
"encoding/json"
|
|
"net/http"
|
|
"net/http/httptest"
|
|
"net/url"
|
|
"reflect"
|
|
"sort"
|
|
"testing"
|
|
"time"
|
|
)
|
|
|
|
// #667: reception comparisons must match observer and direction on the same
|
|
// observation, not on the transmission's globally selected display observation.
|
|
func TestPacketsExcludeTx(t *testing.T) {
|
|
for _, mode := range []string{"memory", "sql-v2", "sql-v3", "fallback"} {
|
|
t.Run(mode, func(t *testing.T) {
|
|
db := setupTestDB(t)
|
|
if mode == "sql-v2" {
|
|
db.Close()
|
|
db = setupTestDBV2(t)
|
|
}
|
|
t.Cleanup(func() { db.Close() })
|
|
now := time.Now().UTC().Truncate(time.Second)
|
|
seen := now.Add(-time.Hour)
|
|
const node = "aabbccdd11223344"
|
|
mustExec := func(query string, args ...any) {
|
|
t.Helper()
|
|
if _, err := db.conn.Exec(query, args...); err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
}
|
|
mustExec("INSERT INTO nodes (public_key, name, role, last_seen) VALUES (?, 'Fixture node', 'repeater', ?)", node, seen.Format(time.RFC3339))
|
|
mustExec("INSERT INTO observers (id, name, iata) VALUES ('a', 'Observer A', 'AAA'), ('b', 'Observer B', 'BBB')")
|
|
for _, hash := range []string{"a-tx-b-rx", "a-rx-b-tx", "a-tx-and-rx", "legacy-empty", "legacy-null", "tx-only"} {
|
|
mustExec(`INSERT INTO transmissions (hash, raw_hex, first_seen, route_type, payload_type, decoded_json, from_pubkey)
|
|
VALUES (?, '1200', ?, 1, 4, '{"pubKey":"aabbccdd11223344","type":"ADVERT"}', ?)`, hash, seen.Format(time.RFC3339), node)
|
|
}
|
|
addObs := func(tx int, observer string, direction any, path string) {
|
|
t.Helper()
|
|
if db.isV3 {
|
|
mustExec(`INSERT INTO observations (transmission_id, observer_idx, direction, path_json, timestamp)
|
|
VALUES (?, (SELECT idx FROM observers WHERE id = ?), ?, ?, ?)`, tx, observer, direction, path, seen.Unix())
|
|
} else {
|
|
mustExec(`INSERT INTO observations (transmission_id, observer_id, direction, path_json, timestamp)
|
|
VALUES (?, ?, ?, ?, ?)`, tx, observer, direction, path, seen.Unix())
|
|
}
|
|
}
|
|
addObs(1, "a", "tx", `[]`)
|
|
addObs(1, "b", "rx", `["11","22"]`) // representative RX must not count as A's reception
|
|
addObs(2, "a", "rx", `[]`)
|
|
addObs(2, "b", "tx", `["11","22"]`) // representative TX must not hide A's reception
|
|
addObs(3, "a", "tx", `["11"]`)
|
|
addObs(3, "a", "rx", `[]`)
|
|
addObs(4, "a", "", `[]`)
|
|
addObs(5, "a", nil, `[]`)
|
|
addObs(6, "a", "tx", `[]`)
|
|
|
|
srv := NewServer(db, &Config{}, NewHub())
|
|
suffix := ""
|
|
if mode == "memory" || mode == "fallback" {
|
|
store := NewPacketStore(db, nil)
|
|
if err := store.Load(); err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
if !store.WaitIndexesReady(5 * time.Second) {
|
|
t.Fatal("packet indexes did not become ready")
|
|
}
|
|
store.oldestLoaded = ""
|
|
if mode == "fallback" {
|
|
store.oldestLoaded = seen.Format(time.RFC3339)
|
|
suffix = "&since=" + url.QueryEscape(now.Add(-2*time.Hour).Format(time.RFC3339))
|
|
}
|
|
srv.store = store
|
|
}
|
|
|
|
all := []string{"a-tx-b-rx", "a-rx-b-tx", "a-tx-and-rx", "legacy-empty", "legacy-null", "tx-only"}
|
|
rxA := []string{"a-rx-b-tx", "a-tx-and-rx", "legacy-empty", "legacy-null"}
|
|
for _, tc := range []struct {
|
|
name, query string
|
|
want []string
|
|
total int
|
|
}{
|
|
{"default preserves TX", "observer=a", all, 6},
|
|
{"false preserves TX", "observer=a&excludeTx=false", all, 6},
|
|
{"observer reception", "observer=a&excludeTx=true", rxA, 4},
|
|
{"other observer", "observer=b&excludeTx=true", []string{"a-tx-b-rx"}, 1},
|
|
{"multiple observers", "observer=a,b&excludeTx=true", all[:5], 5},
|
|
{"unscoped", "excludeTx=true", all[:5], 5},
|
|
{"hash shortcut", "hash=tx-only&excludeTx=true", []string{}, 0},
|
|
{"node shortcut", "node=" + node + "&excludeTx=true", all[:5], 5},
|
|
{"pagination after exclusion", "observer=a&excludeTx=true&limit=2&offset=1", rxA[1:3], 4},
|
|
{"grouped default warms cache", "observer=a&groupByHash=true", all, 6},
|
|
{"grouped reception isolates cache", "observer=a&groupByHash=true&excludeTx=true", rxA, 4},
|
|
{"grouped default unchanged", "observer=a&groupByHash=true", all, 6},
|
|
} {
|
|
t.Run(tc.name, func(t *testing.T) {
|
|
w := httptest.NewRecorder()
|
|
srv.handlePackets(w, httptest.NewRequest(http.MethodGet, "/api/packets?order=asc&"+tc.query+suffix, nil))
|
|
if w.Code != http.StatusOK {
|
|
t.Fatalf("status %d: %s", w.Code, w.Body.String())
|
|
}
|
|
var got struct {
|
|
Packets []struct{ Hash string }
|
|
Total int
|
|
}
|
|
if err := json.Unmarshal(w.Body.Bytes(), &got); err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
hashes := make([]string, 0, len(got.Packets))
|
|
for _, p := range got.Packets {
|
|
hashes = append(hashes, p.Hash)
|
|
}
|
|
want := append([]string{}, tc.want...)
|
|
sort.Strings(hashes)
|
|
sort.Strings(want)
|
|
if !reflect.DeepEqual(hashes, want) || got.Total != tc.total {
|
|
t.Errorf("packets = %v (total %d), want %v (total %d)", hashes, got.Total, tc.want, tc.total)
|
|
}
|
|
})
|
|
}
|
|
})
|
|
}
|
|
}
|
|
|
|
func TestPacketsExcludeTxGroupedCacheCannotAliasRadio(t *testing.T) {
|
|
srv, router := setupTestServer(t)
|
|
t.Cleanup(func() { srv.db.Close() })
|
|
for _, tc := range []struct {
|
|
query string
|
|
total int
|
|
}{
|
|
{"", 3},
|
|
{"&radio=%7CexcludeTx", 0},
|
|
{"&excludeTx=true", 3},
|
|
} {
|
|
w := httptest.NewRecorder()
|
|
router.ServeHTTP(w, httptest.NewRequest(http.MethodGet, "/api/packets?groupByHash=true&observer=obs1"+tc.query, nil))
|
|
if w.Code != http.StatusOK {
|
|
t.Fatalf("status %d: %s", w.Code, w.Body.String())
|
|
}
|
|
var got PacketResult
|
|
if err := json.Unmarshal(w.Body.Bytes(), &got); err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
if got.Total != tc.total || len(got.Packets) != tc.total {
|
|
t.Errorf("query %q: got %d packets (total %d), want %d; filters must have separate cache entries", tc.query, len(got.Packets), got.Total, tc.total)
|
|
}
|
|
}
|
|
}
|