Document store schema and methods

[waynesbrain]

I did a little experiment today to see if I could load up whole JSON documents into TinyBase and here's what I came up with.

It's not a big leap from this code to be able to make a JS Proxy object to listen and write changes to the correct TinyBase table using something like valtio, mutative, etc...

TLDR; I'm loading up the following original document/object into TinyBase (for editing) and pulling it back out, by mapping the whole object to tables (the code should also work with array documents)...

{
  id: "f33df4c3",
  title: "Thank you email",
  subject: "Thank you {customer.name}",
  to: "{customer.email}",
  text: [
    "Hello {customer.name},",
    "",
    "  Thanks for your business.",
    "",
    "Your pals at Happy-Co",
  ],
  options: {
    foo: { bar: "baz", yada: null },
    qux: [{ id: "one" }, { id: "two" }, { id: "three" }],
  },
}

doc_store.ts

import {
  Table,
  TablesSchema,
  ValuesSchema,
  createMergeableStore,
  createIndexes,
  createRelationships,
} from "tinybase/with-schemas";

export const DocStoreSchemas = {
  tables: {
    /** Object or array based documents. */
    docs: {
      /** Document (file) name. */
      name: { type: "string" },
      /** Document type `"object"|"array"`. */
      type: { type: "string" },
    },
    /** Root field->val pointers for each document. */
    doc_flds: {
      /** An id from `docs`. */
      i: { type: "string" },
      /** An id from `doc_vals`. */
      v: { type: "string" },
    },
    /** Value fields within each document. */
    doc_vals: {
      /** Key of field or number of array element. */
      k: { type: "string" },
      /** Type of value `"s"|"n"|"b"|"o"|"a"|""`
       * e.g. `string|number|boolean|object|array|null` */
      t: { type: "string" },
      /** Parent `doc_vals` object/array id, if any. */
      p: { type: "string" },
      /** String value. */
      s: { type: "string" },
      /** Boolean value. */
      b: { type: "boolean" },
      /** Number value. */
      n: { type: "number" },
    },
  } as const satisfies TablesSchema,
  values: {
    // No values yet.
  } as const satisfies ValuesSchema,
} as const;
export type DocStoreTables = (typeof DocStoreSchemas)["tables"];
export type DocStoreValues = (typeof DocStoreSchemas)["values"];
export type DocStoreSchemas = [DocStoreTables, DocStoreValues];

export function createDocStore(uniqueId?: string) {
  const store = createMergeableStore(uniqueId).setSchema(
    DocStoreSchemas.tables,
    DocStoreSchemas.values,
  );
  const related = createRelationships(store)
    // Documents have root fields.
    .setRelationshipDefinition("docFields", "doc_flds", "docs", "i")
    // Document root fields point to value fields.
    .setRelationshipDefinition("docVals", "doc_flds", "doc_vals", "v")
    // Document value fields are nested in other value fields.
    .setRelationshipDefinition("docValsParent", "doc_vals", "doc_vals", "p");

  const indexes = createIndexes(store)
    .setIndexDefinition("fldsByDoc", "doc_flds", "i")
    .setIndexDefinition("valsByParent", "doc_vals", "p");

  const queries = {
    /** Gets `doc_vals` rows from a document's root fields in `doc_flds`. */
    getDocumentFields(docId: string) {
      const flds = indexes.getSliceRowIds("fldsByDoc", docId);
      const tbl = flds.reduce(
        (tbl, fld) => {
          const valId = store.getCell("doc_flds", fld, "v")!;
          tbl[valId] = store.getRow("doc_vals", valId);
          return tbl;
        },
        {} as Table<DocStoreTables, "doc_vals", true>,
      );
      return tbl;
    },
    /** Gets `doc_vals` rows for the given parent id. */
    getDocumentValues(parentId: string) {
      const valIds = indexes.getSliceRowIds("valsByParent", parentId);
      const tbl = valIds.reduce(
        (tbl, valId) => {
          tbl[valId] = store.getRow("doc_vals", valId);
          return tbl;
        },
        {} as Table<DocStoreTables, "doc_vals", true>,
      );
      return tbl;
    },
  };

  return { store, queries, related };
}

try_doc_store.ts

import { Row } from "tinybase/with-schemas";
import { DocStoreTables, createDocStore } from "./doc_store";

const primitives = ["s", "b", "n"] as const; // string, boolean, number

// Test

const { store, queries } = createDocStore();

/** Original document to be loaded into TinyBase and rebuilt from TinyBase... */
const original = {
  id: "f33df4c3",
  title: "Thank you email",
  subject: "Thank you {customer.name}",
  to: "{customer.email}",
  text: [
    "Hello {customer.name},",
    "",
    "  Thanks for your business.",
    "",
    "Your pals at Happy-Co",
  ],
  options: {
    foo: { bar: "baz", yada: null },
    qux: [{ id: "one" }, { id: "two" }, { id: "three" }],
  },
};

const docId = createDocument("f33df4c3.json", original);

console.log("Documents");
console.dir(store.getTable("docs"), { depth: null });
console.log("Fields");
console.dir(store.getTable("doc_flds"), { depth: null });
console.log("Field Values");
console.dir(store.getTable("doc_vals"), { depth: null });

const rebuilt = getDocument(docId);
console.log("REBUILT");
console.dir(rebuilt, { depth: null });

function buildValue(
  t: string,
  vals: ReturnType<(typeof queries)["getDocumentValues"]>,
): any {
  // console.log("VALS");
  // console.dir(vals, { depth: null });
  if (t === "a") {
    const arr: any[] = Array<any>(Object.keys(vals).length).fill(undefined);
    for (const id in vals) {
      const row = vals[id] as Required<(typeof vals)[string]>;
      const i = parseInt(row.k);
      switch (row.t) {
        case "s":
          arr[i] = row.s;
          break;
        case "n":
          arr[i] = row.n;
          break;
        case "b":
          arr[i] = row.b;
          break;
        case "a":
        case "o":
          arr[i] = getDocumentValues(id, row.t);
          break;
        default:
          arr[i] = null;
      }
    }
    return arr;
  }
  const obj: any = {};
  for (const id in vals) {
    const row = vals[id] as Required<(typeof vals)[string]>;
    switch (row.t) {
      case "s":
        obj[row.k] = row.s;
        break;
      case "n":
        obj[row.k] = row.n;
        break;
      case "b":
        obj[row.k] = row.b;
        break;
      case "a":
      case "o":
        obj[row.k] = getDocumentValues(id, row.t);
        break;
      default:
        obj[row.k] = null;
    }
  }
  return obj;
}

function createDocument(name: string, obj: Record<string, any> | Array<any>) {
  const asArray = Array.isArray(obj);
  const docId = store.addRow("docs", {
    name,
    type: asArray ? "array" : "object",
  });
  if (!docId) throw new Error(`Expected docId.`);
  if (asArray) {
    const { length } = obj;
    for (let i = 0; i < length; i++) {
      setDocumentValue(docId, i, obj[i]);
    }
  } else if (obj) {
    for (const key in obj) {
      setDocumentValue(docId, key, obj[key]);
    }
  }
  return docId;
}

function setDocumentValue(
  docId: string,
  key: string | number,
  val: any,
  parentId?: string,
) {
  key = "" + key;
  const tv = typeof val;
  const t =
    tv === "string"
      ? "s"
      : tv === "number"
        ? "n"
        : tv === "boolean"
          ? "b"
          : val === null
            ? ""
            : Array.isArray(val)
              ? "a"
              : tv === "object"
                ? "o"
                : undefined;
  if (t === undefined) throw new Error(`Invalid value type ${tv} ` + val);
  const row: Row<DocStoreTables, "doc_vals", true> = {
    k: "" + key,
    t,
  };
  if (parentId) {
    row.p = parentId;
  }
  if (primitives.includes(t as any)) {
    (row as any)[t] = val;
  }
  const valId = store.addRow("doc_vals", row)!;
  if (!parentId) {
    store.addRow("doc_flds", {
      i: docId,
      v: valId,
    });
  }
  if (t === "o") {
    for (const key in val) {
      setDocumentValue(docId, key, val[key], valId);
    }
  } else if (t === "a") {
    const { length } = val;
    for (let i = 0; i < length; i++) {
      setDocumentValue(docId, i, val[i], valId);
    }
  }
}

function getDocument(docId: string) {
  const doc = store.getRow("docs", docId)!;
  const vals = queries.getDocumentFields(docId);
  // console.log("VALS");
  // console.dir(vals, { depth: null });
  if (doc.type === "array") {
    return buildValue("a", vals);
  }
  return buildValue("o", vals);
}

function getDocumentValues(parentId: string, t: string): any {
  const vals = queries.getDocumentValues(parentId);
  return buildValue(t, vals);
}

Running npm run build && node ./dist/try_doc_store.js outputs the following:

and the final result:

[waynesbrain]

NOTE: Instead of 3 tables, I could have done this with only 2 tables by storing the document id directly in the doc_vals table instead of using a join table (doc_flds) like I did here, but I thought it would be a waste of storage to have the doc id repeated for all the field values nested within the document so I chose just to store the documents root fields in doc_flds.

[jamesgpearce]

WOW

[waynesbrain]

I was just trying this out to see how hard it would be and I'm going to stop here but I'll leave my notes.

Using a proxy watcher such as on-change (or you could use fast-json-patch .observe()) I started working on a function to provide a watchable document that would put changes back into the store and that works fine. Lookup up doc_vals by path is not hard to do.

However, I am not sure that I love the idea of using either of those libraries mentioned above because I'm not sure how airtight they are at capturing changes based on some issues I'm seeing on their repos.

If I really want this functionality in the future I might try to use mutative / enablePatches method to watch the document and get changes into TinyBase.

To make a function to get the full property-path of a deeply nested doc_vals row I used the getLinkedRowIds similar to this discussion. This function isn't that useful for watching changes to a proxied document but it might be useful for generating patches to a document when the table changes...

function getDocumentValuePath(
  valId: string,
  row: Row<DocStoreTables, "doc_vals", true>,
) {
  const linked = related.getLinkedRowIds("docValsParent", valId);
  const parts: string[] = [];
  for (let i = 0; i < linked.length; i++) {
    const id = linked[i];
    if (id === valId) {
      parts.push(row.k!);
    } else {
      parts.push(store.getCell("doc_vals", id!, "k")!);
    }
  }
  return parts.reverse().join(".");
}

One more useful note I added on the table schemas used here before I put this away:

// TODO: Go back to 2 tables, but instead of putting docId in `doc_vals` just
// remove `type` from `docs` and move `doc_flds.v` to `docs` pointing to root
// object|array value... (then get rid of the doc_flds table)