Files
faunosteo/packages/supabase/scripts/r5-jsonb-fingerprint.mjs

71 lines
2.7 KiB
JavaScript

#!/usr/bin/env node
/**
* D-R5 study (R5.1): does a PostgreSQL `jsonb` round trip keep each candidate
* fingerprint of the sheet document? `jsonb` drops the key order and the
* whitespace and re-prints the numbers, so a fingerprint over the file bytes
* cannot be recomputed from `anatomical_sheet_versions.content`; a canonical
* one (RFC 8785) can if the values survive. PGlite 0.3 = PostgreSQL 17,
* no Docker. Read-only on the repository.
*
* pnpm --filter @faunosteo/supabase exec node scripts/r5-jsonb-fingerprint.mjs
*/
import { PGlite } from '@electric-sql/pglite'
import { createHash } from 'node:crypto'
import { readFileSync } from 'node:fs'
import { dirname, resolve } from 'node:path'
import { fileURLToPath } from 'node:url'
const REPO_ROOT = resolve(dirname(fileURLToPath(import.meta.url)), '../../..')
const SEED = 'supabase/seed/anatomical-sheets/equine'
const FILES = [
`${SEED}/draft/document.json`,
`${SEED}/legacy-import/document.json`,
]
const sha256 = (text) => createHash('sha256').update(text).digest('hex')
/** RFC 8785 canonical form (the same function as scripts/r5-fingerprints.mjs). */
function canonicalize(value) {
if (value === null || typeof value !== 'object') return JSON.stringify(value)
if (Array.isArray(value)) return `[${value.map(canonicalize).join(',')}]`
const keys = Object.keys(value).sort((a, b) => (a < b ? -1 : a > b ? 1 : 0))
const members = keys.map(
(k) => `${JSON.stringify(k)}:${canonicalize(value[k])}`,
)
return `{${members.join(',')}}`
}
async function roundTrip(db, file) {
const text = readFileSync(resolve(REPO_ROOT, file), 'utf8')
const value = JSON.parse(text)
await db.query('delete from t')
await db.query('insert into t values ($1::jsonb)', [text])
const { rows } = await db.query('select d::text as text from t')
const back = JSON.parse(rows[0].text)
return {
file: file.slice(SEED.length + 1),
today: sha256(text),
today_after_jsonb: sha256(rows[0].text),
a_before: sha256(JSON.stringify(value, null, 2)),
a_after_jsonb: sha256(JSON.stringify(back, null, 2)),
b_before: sha256(canonicalize(value)),
b_after_jsonb: sha256(canonicalize(back)),
}
}
const db = new PGlite()
await db.query('create table t (d jsonb)')
const results = []
for (const file of FILES) results.push(await roundTrip(db, file))
await db.close()
console.log(
'| File | Today = after jsonb | A = after jsonb | B (RFC 8785) = after jsonb |',
)
console.log('|---|---|---|---|')
for (const r of results) {
console.log(
`| ${r.file} | ${r.today === r.today_after_jsonb ? 'yes' : 'no'} | ${r.a_before === r.a_after_jsonb ? 'yes' : 'no'} | ${r.b_before === r.b_after_jsonb ? 'yes' : 'no'} (${r.b_before.slice(0, 16)}…) |`,
)
}