dataPage

tcmts.cli.data_pages.dataPage · #bn3qqf7w · term · runs on the server

uses node:util

A page of a table's rows for a viewer: searched (anywhere in the row), filtered (fields equal to values), sorted (by a field, the id or when it was written; newest first by default) and paged in SQLite. Each row's cells (strings as they are, dates as ISO, small things inline, objects as one short line), and the row in full, and as JSON.

import type { Codebase } from "#codebase/tcmts/cli/codebase";
import { decode } from "#codebase/tcmts/cli/codec";
import { declaredTables, rowSummary, toJson, withStore } from "#codebase/tcmts/cli/data_pages";
import { inspect } from "node:util";

export function dataPage(
  cb: Codebase,
  table: string,
  q: {
    offset: number;
    limit: number;
    search?: string;
    sort?: { field: string; desc?: boolean };
    /** fields equal to these, as `find` has it (a number, true or false, null, or else the text) */
    where?: Record<string, string>;
  },
): {
  total: number;
  fields: { name: string; type: string }[];
  rows: {
    id: string;
    updated: number;
    older: boolean;
    cells: Record<string, string>;
    detail: string;
    summary: string;
    /** the row as JSON (`toJson`) */
    json: unknown;
  }[];
} {
  const FIELD = /^[A-Za-z_$][\w$]*$/;
  const cell = (v: unknown): string =>
    v === undefined || v === null
      ? ""
      : typeof v === "string"
        ? v
        : v instanceof Date
          ? v.toISOString()
          : typeof v === "object"
            ? rowSummary(v)
            : String(v);
  const d = declaredTables(cb).get(table);
  return (
    withStore(cb, (db) => {
      // (a field's value as json_extract gives it: a number, true as 1, null, or the text)
      // (a string field's is the text given, whatever it looks like)
      const text = (f: string) =>
        /^string\??$/.test(d?.fields.find((x) => x.name === f)?.type ?? "");
      const as = (v: string, f: string): string | number | null =>
        text(f)
          ? v
          : v === "null"
            ? null
            : v === "true"
              ? 1
              : v === "false"
                ? 0
                : /^-?\d+(\.\d+)?$/.test(v)
                  ? Number(v)
                  : v;
      const eq = Object.entries(q.where ?? {}).filter(([f]) => FIELD.test(f));
      const where = [
        "tbl = ?",
        ...(q.search ? ["instr(lower(data), lower(?)) > 0"] : []),
        ...eq.map(([f, v]) =>
          as(v, f) === null
            ? `json_extract(data, '$."${f}"') IS NULL`
            : `json_extract(data, '$."${f}"') = ?`,
        ),
      ].join(" AND ");
      const args: (string | number)[] = [
        table,
        ...(q.search ? [q.search] : []),
        ...eq.flatMap(([f, v]) => (as(v, f) === null ? [] : [as(v, f) as string | number])),
      ];
      const total = (
        db.prepare(`SELECT count(*) AS n FROM rows WHERE ${where}`).get(...args) as { n: number }
      ).n;
      const f = q.sort?.field;
      const by =
        f === "id"
          ? "id"
          : f === "updated" || !f || !FIELD.test(f)
            ? "updated"
            : `json_extract(data, '$."${f}"')`;
      const dir = f ? (q.sort?.desc ? "DESC" : "ASC") : "DESC";
      const rows = db
        .prepare(
          `SELECT id, data, schema_hash, updated FROM rows WHERE ${where} ORDER BY ${by} ${dir}, id LIMIT ? OFFSET ?`,
        )
        .all(...args, q.limit, q.offset) as {
        id: string;
        data: string;
        schema_hash: string;
        updated: number;
      }[];
      const values = rows.map((r) => decode(r.data) as Record<string, unknown>);
      // an undeclared table's columns are whatever its rows have
      const fields = d?.fields.length
        ? d.fields
        : [
            ...new Set(values.flatMap((v) => (v && typeof v === "object" ? Object.keys(v) : []))),
          ].map((name) => ({
            name,
            type: "",
          }));
      return {
        total,
        fields,
        rows: rows.map((r, i) => {
          const v = values[i]!;
          return {
            id: r.id,
            updated: r.updated,
            older: !!d && r.schema_hash !== d.schema,
            cells: Object.fromEntries(
              fields.map((fl) => [
                fl.name,
                cell(v && typeof v === "object" ? v[fl.name] : undefined),
              ]),
            ),
            detail: inspect(v, { depth: null, breakLength: 80 }),
            json: toJson(v),
            summary: rowSummary(v),
          };
        }),
      };
    }) ?? { total: 0, fields: d?.fields ?? [], rows: [] }
  );
}

uses

used by