Suppressed and unavailable cells

Every row whose value_status isn’t 'reported', for a chosen source — proving value reads as NULL for every one of them, never a number (see Concepts › Suppression).

BUNDLE_URL = "https://basemap.cancerdatasci.org/data/latest/cancerbasemap.duckdb"

// Standard USPS state FIPS codes — public, static, and needed for a
// selector before any connection to the release exists, so it's a plain array
// here rather than a live query. geo_id in the release is 'state:<fips>'.
US_STATES = [
  ["01","Alabama"],["02","Alaska"],["04","Arizona"],["05","Arkansas"],["06","California"],
  ["08","Colorado"],["09","Connecticut"],["10","Delaware"],["11","District of Columbia"],
  ["12","Florida"],["13","Georgia"],["15","Hawaii"],["16","Idaho"],["17","Illinois"],
  ["18","Indiana"],["19","Iowa"],["20","Kansas"],["21","Kentucky"],["22","Louisiana"],
  ["23","Maine"],["24","Maryland"],["25","Massachusetts"],["26","Michigan"],["27","Minnesota"],
  ["28","Mississippi"],["29","Missouri"],["30","Montana"],["31","Nebraska"],["32","Nevada"],
  ["33","New Hampshire"],["34","New Jersey"],["35","New Mexico"],["36","New York"],
  ["37","North Carolina"],["38","North Dakota"],["39","Ohio"],["40","Oklahoma"],["41","Oregon"],
  ["42","Pennsylvania"],["44","Rhode Island"],["45","South Carolina"],["46","South Dakota"],
  ["47","Tennessee"],["48","Texas"],["49","Utah"],["50","Vermont"],["51","Virginia"],
  ["53","Washington"],["54","West Virginia"],["55","Wisconsin"],["56","Wyoming"]
].map(([fips, name]) => ({ fips, name }))

// Incrementing this mutable is the ONLY thing that rebuilds dbState below.
// Selector changes on the page never touch it — they only re-run that
// page's own queryState, reusing the same connection.
mutable dbGeneration = 0

retryButton = {
  const btn = html`<button>Retry connection</button>`;
  btn.onclick = () => { mutable dbGeneration = dbGeneration + 1; };
  return btn;
}

// Loads a pinned duckdb-wasm build (Quarto's own OJS stdlib DuckDBClient is
// an older bundled client that cannot ATTACH a remote database file) and
// attaches the latest release as `cbm`, as the playground does. Depends only
// on dbGeneration, so it runs once per page load and again only on Retry.
dbState = {
  dbGeneration; // reactive dependency — Retry increments this to rebuild the instance
  const t0 = performance.now();
  try {
    const duckdb = await import(
      "https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.33.1-dev57.0/dist/duckdb-browser.mjs"
    );
    const bundle = await duckdb.selectBundle(duckdb.getJsDelivrBundles());
    const tBundle = performance.now();
    const workerUrl = URL.createObjectURL(
      new Blob([`importScripts("${bundle.mainWorker}");`], { type: "text/javascript" })
    );
    const worker = new Worker(workerUrl);
    const db = new duckdb.AsyncDuckDB(new duckdb.ConsoleLogger(duckdb.LogLevel.WARNING), worker);
    await db.instantiate(bundle.mainModule, bundle.pthreadWorker);
    URL.revokeObjectURL(workerUrl);
    // Read byte ranges only; by default DuckDB-Wasm downloads each whole
    // Parquet file. All three flags are needed (see playground.qmd).
    await db.open({
      filesystem: { reliableHeadRequests: true, allowFullHTTPReads: false, forceFullHTTPReads: false }
    });
    const conn = await db.connect();
    const tInstantiate = performance.now();
    await conn.query(`ATTACH '${BUNDLE_URL}' AS cbm (READ_ONLY)`);
    const tAttach = performance.now();
    // Tear this instance down fully before the cell is ever replaced (a
    // future Retry, or navigating away) — never leave a stale worker + WASM
    // heap running alongside a new one.
    invalidation.then(async () => {
      try { await conn.close(); } catch {}
      try { await db.terminate(); } catch {}
    });
    return {
      status: "ready",
      conn,
      timings: {
        instantiate: tInstantiate - tBundle,
        attach: tAttach - tInstantiate,
        total: tAttach - t0
      }
    };
  } catch (error) {
    return { status: "error", error };
  }
}

function rowsFromArrow(table) {
  const fields = table.schema.fields.map((f) => f.name);
  return table.toArray().map((r) => {
    const o = {};
    for (const f of fields) {
      let v = r[f];
      if (typeof v === "bigint") v = Number(v);
      o[f] = v;
    }
    return o;
  });
}

// Shared status line: "connecting" / a query-failed or release-unreachable
// callout with Retry / a ready line with timings. Every recipe page calls
// this from its own display cell as renderStatus(dbState, queryState).
function renderStatus(dbState, queryState) {
  if (dbState.status === "error")
    return html`<p class="status-error"><strong>Release unreachable:</strong> ${dbState.error?.message ?? dbState.error}.
      Large-table reads can fail intermittently in a long-lived tab
      (<a href="https://github.com/seandavi/cancer-basemap/issues/120">cancer-basemap#120</a>) —
      try Retry, or a hard refresh if Retry doesn't help.</p><p>${retryButton}</p>`;
  if (dbState.status !== "ready")
    return html`<p class="recipe-status"><em>Connecting to the release and loading DuckDB-WASM…</em></p>`;
  if (queryState.status === "error")
    return html`<p class="status-error"><strong>Query failed:</strong> ${queryState.error?.message ?? queryState.error}</p><p>${retryButton}</p>`;
  if (queryState.status !== "ready")
    return html`<p class="recipe-status"><em>Running query…</em></p>`;
  return html`<p class="recipe-status">DuckDB-WASM ready in ${dbState.timings.total.toFixed(0)} ms
    (ATTACH ${dbState.timings.attach.toFixed(0)} ms).
    This query: ${queryState.ms.toFixed(0)} ms, ${queryState.rows?.length ?? 0} rows.</p>`;
}

SOURCES = ["SCP", "PLACES", "SVI", "AHRF", "FARA", "RUCC", "RUCA", "SDWIS", "HPSA"]

viewof sourceChoice = Inputs.select(SOURCES, { label: "Source", value: "PLACES" })

sql = `SELECT source_release, measure_id, geo_id, value_status, value
FROM cbm.measure.observation
WHERE source = '${sourceChoice}' AND value_status != 'reported'
ORDER BY source_release, measure_id, geo_id
LIMIT 500`

queryState = {
  sql; // reactive dependency
  if (dbState.status !== "ready") return dbState;
  const t0 = performance.now();
  try {
    const result = await dbState.conn.query(sql);
    return { status: "ready", rows: rowsFromArrow(result), ms: performance.now() - t0 };
  } catch (error) {
    return { status: "error", error };
  }
}

renderStatus(dbState, queryState)

queryState.status === "ready"
  ? (queryState.rows.length
      ? html`${Inputs.table(queryState.rows, { columns: ["source_release", "measure_id", "geo_id", "value_status", "value"] })}
         <p class="recipe-status">${queryState.rows.filter((r) => r.value !== null).length} of ${queryState.rows.length} rows shown have a non-null value — should always be 0.</p>`
      : html`<p><em>No suppressed or unavailable rows for this source — every row is 'reported'.</em></p>`)
  : html`<p><em>Table will render once the query returns.</em></p>`

SQL run for this table

html`<pre><code>${sql}</code></pre>`