mirror of
https://github.com/tiennm99/vngeoguessr.git
synced 2026-10-14 06:18:03 +00:00
tiennm99
6569f095c9
- panoramas + pano_provinces tables replace 28MB bundled JSON; pano-index.js now draws via cached COUNT + ORDER BY id OFFSET with rejection sampling, composite (province,id)/(district,id) indexes support the skip - scripts/seed-pano-db.mjs validates pipeline artifacts (the old real-data vitest invariants, extracted to scripts/lib/pano-artifacts.mjs), stages into panoramas_next, verifies, renames into place in one transaction, keeps panoramas_old as backup; --province reseeds in place; --check validates only - pipeline writes gitignored data-build/panos/; pano barrel removed - tests run against PGlite mocked in at the @neondatabase/serverless boundary, mirroring the fake-upstash pattern; fixtures replace real data - infrastructure errors rethrow instead of reading as missing coverage; session ids via crypto.randomUUID (uuid package dropped)
43 lines
1.7 KiB
JavaScript
43 lines
1.7 KiB
JavaScript
// The panorama tables, shared by scripts/seed-pano-db.mjs (against Neon) and
|
|
// tests/pano-fixtures.js (against the in-memory Postgres fake). One copy so the
|
|
// tables the tests exercise cannot drift from the ones production queries.
|
|
//
|
|
// `district` is NULL for a panorama that fell outside every district polygon
|
|
// and belongs to the province only -- the same meaning `d: undefined` had in
|
|
// the old JSON index.
|
|
|
|
/**
|
|
* DDL for a panoramas table under any name.
|
|
*
|
|
* Parameterised by name because the seed script builds `panoramas_next` and
|
|
* swaps it in, and index names are schema-unique so each table needs its own.
|
|
* @param {string} table Table name, e.g. 'panoramas' or 'panoramas_next'.
|
|
* @returns {string[]} Statements to run in order.
|
|
*/
|
|
export function panoramasDdl(table) {
|
|
return [
|
|
`CREATE TABLE IF NOT EXISTS ${table} (
|
|
id text PRIMARY KEY,
|
|
province text NOT NULL,
|
|
district text,
|
|
lat double precision NOT NULL,
|
|
lng double precision NOT NULL
|
|
)`,
|
|
// Composite with id: the draw query is WHERE <region> ORDER BY id OFFSET n
|
|
// LIMIT 1, and (province, id) turns that OFFSET into an index-only skip
|
|
// instead of sorting the whole partition (225,966 rows for Ha Noi).
|
|
`CREATE INDEX IF NOT EXISTS ${table}_province_id_idx ON ${table} (province, id)`,
|
|
`CREATE INDEX IF NOT EXISTS ${table}_district_id_idx ON ${table} (district, id)`,
|
|
];
|
|
}
|
|
|
|
/** DDL for the per-province metadata table. */
|
|
export const PANO_PROVINCES_DDL = [
|
|
`CREATE TABLE IF NOT EXISTS pano_provinces (
|
|
code text PRIMARY KEY,
|
|
count integer NOT NULL,
|
|
generated_at timestamptz,
|
|
assigned_at timestamptz
|
|
)`,
|
|
];
|