map-services / gps-routes-api / 38790099194

54%

push

github

perf(sqlite): bbox lat/lng B-tree pre-filter + CI targets the SQLite implementation (#182)

## Summary
Targets the SQLite + FTS5 + Spatialite build as the CI-published image,
and adds a lat/lng B-tree range pre-filter to SQLite bbox queries so the
planner seeks the existing `idx_routes_latitude` /
`idx_routes_longitude` indexes instead of `SCAN routes`.

## Changes (vs `main`)
- **`.github/workflows/build.yml`** — `docker-publish` now builds `file:
Dockerfile.sqlite` (SQLite+FTS5+Spatialite) instead of the default
Postgres `Dockerfile`. `build-and-test` still validates **both**
backends (Testcontainers Postgres + the real `libsqlite3-mod-spatialite`
SQLite/Spatialite path).
- **`Dockerfile.sqlite`** (new) — glibc multi-stage build,
`CGO_ENABLED=1`, build tags `sqlite,sqlite_fts5`, installs
`libsqlite3-mod-spatialite` at build+runtime, seeds `data/routes.db`
from `data/backup/` at build (avoids the `no such table: routes` 500 at
cold start).
- **`.dockerignore`** — re-include the tracked `data/backup/` for the
sqlite build; keep gitignored `data/routes.db*` excluded.
- **`db/query_builder.go`** — SQLite bbox now emits `latitude BETWEEN ?
AND ? AND longitude BETWEEN ? AND ? AND ST_Within(_geoloc, BuildMbr(?,
?, ?, ?, 4326))` (8 textual-order params); the Postgres `else` branch is
byte-identical (no gate regression).
- **`db/query_builder_test.go`** — 8-param SQLite bbox assertion +
facet/pagination param-ordering regression test.
- **`repositories/sqlite_test.go`** — regression coverage / doc
alignment.

## Verification
- Default Postgres gate: `go build ./...` OK ; `go vet ./...` OK
- SQLite+Spatialite matrix (`-tags sqlite,sqlite_fts5`, real
`mod_spatialite`): `db`, `repositories`, `cmds` OK
- Docker image (`Dockerfile.sqlite`, Docker Desktop arm64): built,
`/healthz` -> 200; Lake District bbox -> `total:10` + facets populated;
far box `[10,60,12,62]` -> `total:0`. `EXPLAIN QUERY PLAN` shows the
index seek (`SEARCH routes USING INDEX idx_routes_longit... (continued)

26 of 26 new or added lines in 1 file covered. (100.0%)

625 of 1753 relevant lines covered (35.65%)

0.36 hits per line