spb/note-spboucher-ai
Public
1/** Row loading + computed values (relations, rollups, formulas) + filter/sort/paginate. */2import { and, eq, inArray, isNull, sql as dsql, type SQL } from "drizzle-orm";3import { db, schema } from "@/db";4import type { Property, FilterNode, FilterRule, FilterGroup, Sort, RowOut, RelationRef, QueryInput, QueryResult } from "./types";5import { isGroup } from "./types";6import { evaluate, type FVal } from "./formula";7import { displayValue, formulaValue, sortKey, isEmpty, numberOf, dateOf, optionOf } from "./values";89export interface Raw { id: string; pageId: string; databaseId: string; values: Record<string, unknown>; position: number; createdAt: Date; updatedAt: Date; title: string; icon: string | null; pageUpdatedAt: Date }10const rawCols = { id: schema.databaseRows.id, pageId: schema.databaseRows.pageId, databaseId: schema.databaseRows.databaseId, values: schema.databaseRows.values, position: schema.databaseRows.position, createdAt: schema.databaseRows.createdAt, updatedAt: schema.databaseRows.updatedAt, title: schema.pages.title, icon: schema.pages.icon, pageUpdatedAt: schema.pages.updatedAt };11export async function loadRaw(where: SQL[]): Promise<Raw[]> {12 return db.select(rawCols).from(schema.databaseRows).innerJoin(schema.pages, eq(schema.pages.id, schema.databaseRows.pageId)).where(and(isNull(schema.databaseRows.deletedAt), isNull(schema.pages.deletedAt), ...where)).orderBy(schema.databaseRows.position, schema.databaseRows.createdAt) as Promise<Raw[]>;13}14export const loadProps = async (dbId: string): Promise<Property[]> => (await db.select().from(schema.databaseProperties).where(eq(schema.databaseProperties.databaseId, dbId)).orderBy(schema.databaseProperties.position, schema.databaseProperties.createdAt)) as Property[];1516/** Relations of a set of rows for the given relation properties: Map<rowId, Map<propId, rowId[]>>. */17export async function loadRelations(props: Property[], rowIds: string[]) {18 const out = new Map<string, Map<string, string[]>>(); if (!rowIds.length) return out;19 const fwd = props.filter((p) => p.type === "relation" && !p.config.syncedPropertyId); const syn = props.filter((p) => p.type === "relation" && p.config.syncedPropertyId);20 const push = (row: string, prop: string, to: string) => { let m = out.get(row); if (!m) out.set(row, (m = new Map())); let a = m.get(prop); if (!a) m.set(prop, (a = [])); a.push(to); };21 if (fwd.length) for (const r of await db.select().from(schema.relationsTable).where(and(inArray(schema.relationsTable.propertyId, fwd.map((p) => p.id)), inArray(schema.relationsTable.fromRowId, rowIds))).orderBy(schema.relationsTable.createdAt)) push(r.fromRowId, r.propertyId, r.toRowId);22 if (syn.length) { const by = new Map(syn.map((p) => [p.config.syncedPropertyId as string, p.id])); for (const r of await db.select().from(schema.relationsTable).where(and(inArray(schema.relationsTable.propertyId, [...by.keys()]), inArray(schema.relationsTable.toRowId, rowIds))).orderBy(schema.relationsTable.createdAt)) push(r.toRowId, by.get(r.propertyId)!, r.fromRowId); }23 return out;24}2526/** Attach relations, rollups, formulas and timestamps. */27export async function hydrate(props: Property[], raws: Raw[]): Promise<RowOut[]> {28 if (!raws.length) return [];29 const ids = raws.map((r) => r.id); const rels = await loadRelations(props, ids);30 const targetIds = new Set<string>(); for (const m of rels.values()) for (const a of m.values()) a.forEach((x) => targetIds.add(x));31 const targets = new Map<string, Raw>(); if (targetIds.size) for (const t of await loadRaw([inArray(schema.databaseRows.id, [...targetIds])])) targets.set(t.id, t);32 // properties of target databases (for rollups over typed values)33 const rollups = props.filter((p) => p.type === "rollup"); const tdb = new Set<string>(); for (const t of targets.values()) tdb.add(t.databaseId);34 const tprops = new Map<string, Property[]>(); if (rollups.length) for (const d of tdb) tprops.set(d, d === props[0]?.databaseId ? props : await loadProps(d));35 const byName = new Map(props.map((p) => [p.name, p]));36 return raws.map((r) => {37 const values: Record<string, unknown> = { ...r.values };38 for (const p of props) {39 if (p.type === "title") values[p.id] = r.title;40 else if (p.type === "created_time") values[p.id] = r.createdAt.toISOString();41 else if (p.type === "last_edited_time") values[p.id] = (r.pageUpdatedAt > r.updatedAt ? r.pageUpdatedAt : r.updatedAt).toISOString();42 else if (p.type === "relation") values[p.id] = (rels.get(r.id)?.get(p.id) ?? []).map((id) => targets.get(id)).filter(Boolean).map((t): RelationRef => ({ rowId: t!.id, pageId: t!.pageId, title: t!.title, icon: t!.icon }));43 }44 for (const p of rollups) values[p.id] = rollup(p, props, values, targets, tprops);45 const memo = new Map<string, FVal>(); const visiting = new Set<string>();46 const ctx = { prop: (name: string): FVal => { const q = byName.get(name); if (!q) throw new Error(`Unknown property "${name}"`); if (q.type !== "formula") return formulaValue(q, values[q.id]); if (memo.has(q.id)) return memo.get(q.id)!; if (visiting.has(q.id)) throw new Error(`Circular formula via "${name}"`); visiting.add(q.id); const v = evaluate(String(q.config.expression ?? ""), ctx); visiting.delete(q.id); const fv = formulaValue(q, v); memo.set(q.id, fv); return fv; } };47 for (const p of props) if (p.type === "formula") values[p.id] = p.config.expression ? evaluate(String(p.config.expression), ctx) : null;48 return { id: r.id, pageId: r.pageId, title: r.title, icon: r.icon, values, position: r.position, createdAt: r.createdAt.toISOString(), updatedAt: r.updatedAt.toISOString() };49 });50}51function rollup(p: Property, props: Property[], values: Record<string, unknown>, targets: Map<string, Raw>, tprops: Map<string, Property[]>): unknown {52 const rel = props.find((x) => x.id === p.config.relationPropertyId); if (!rel) return { error: "No relation" };53 const refs = (values[rel.id] as RelationRef[] | undefined) ?? []; const agg = p.config.aggregate ?? "count";54 if (agg === "count") return refs.length;55 const tp = p.config.targetPropertyId ? [...tprops.values()].flat().find((x) => x.id === p.config.targetPropertyId) : undefined; if (!tp) return agg === "count_values" ? 0 : { error: "No target property" };56 const vals = refs.map((ref) => { const t = targets.get(ref.rowId); if (!t) return null; if (tp.type === "title") return t.title; if (tp.type === "created_time") return t.createdAt.toISOString(); if (tp.type === "last_edited_time") return t.updatedAt.toISOString(); return t.values[tp.id] ?? null; }).filter((v) => !isEmpty(v));57 if (agg === "count_values") return vals.length;58 if (agg === "show") return vals.map((v) => displayValue(tp, v));59 if (agg === "unique") return [...new Set(vals.map((v) => displayValue(tp, v)))];60 if (tp.type === "date" || tp.type === "created_time" || tp.type === "last_edited_time") { const ds = vals.map(dateOf).filter(Boolean) as Date[]; if (!ds.length) return null; if (agg === "min") return new Date(Math.min(...ds.map((d) => d.getTime()))).toISOString(); if (agg === "max") return new Date(Math.max(...ds.map((d) => d.getTime()))).toISOString(); return { error: `${agg} needs numbers` }; }61 const ns = vals.map(tp.type === "checkbox" ? (v) => (v ? 1 : 0) : numberOf).filter((n): n is number => n != null); if (!ns.length) return null;62 return agg === "sum" ? ns.reduce((a, b) => a + b, 0) : agg === "avg" ? ns.reduce((a, b) => a + b, 0) / ns.length : agg === "min" ? Math.min(...ns) : Math.max(...ns);63}6465// ---------- filters66const str = (p: Property, v: unknown) => displayValue(p, v).toLowerCase();67const filterDate = (v: unknown) => (v === "today" ? new Date() : dateOf(v));68const day = (d: Date) => new Date(d.getFullYear(), d.getMonth(), d.getDate()).getTime();69export function matchRule(rule: FilterRule, p: Property | undefined, values: Record<string, unknown>): boolean {70 if (!p) return true; const v = values[p.id]; const fv = rule.value;71 switch (rule.op) {72 case "empty": return isEmpty(v); case "not_empty": return !isEmpty(v);73 case "eq": case "neq": {74 let r: boolean;75 if (p.type === "checkbox") r = Boolean(v) === (fv === true || fv === "true" || fv === 1);76 else if (p.type === "select" || p.type === "status") r = !isEmpty(v) && (v === fv || optionOf(p, v)?.name === fv);77 else if (p.type === "multi_select") r = Array.isArray(v) && v.some((x) => x === fv || optionOf(p, x)?.name === fv);78 else if (p.type === "relation") r = Array.isArray(v) && (v as RelationRef[]).some((x) => x.rowId === fv || x.title === fv);79 else if (p.type === "number" || (p.type === "formula" && typeof v === "number")) r = numberOf(v) === numberOf(fv);80 else if (p.type === "date") { const a = dateOf(v), b = filterDate(fv); r = !!a && !!b && day(a) === day(b); }81 else r = str(p, v) === String(fv ?? "").toLowerCase();82 return rule.op === "eq" ? r : !r;83 }84 case "contains": return p.type === "multi_select" ? Array.isArray(v) && v.some((x) => x === fv || optionOf(p, x)?.name === fv) : p.type === "relation" ? Array.isArray(v) && (v as RelationRef[]).some((x) => x.rowId === fv || x.title.toLowerCase().includes(String(fv ?? "").toLowerCase())) : str(p, v).includes(String(fv ?? "").toLowerCase());85 case "not_contains": return !matchRule({ ...rule, op: "contains" }, p, values);86 case "starts_with": return str(p, v).startsWith(String(fv ?? "").toLowerCase()); case "ends_with": return str(p, v).endsWith(String(fv ?? "").toLowerCase());87 case "gt": case "gte": case "lt": case "lte": case "before": case "after": case "on": {88 const isDateType = p.type === "date" || p.type === "created_time" || p.type === "last_edited_time" || rule.op === "before" || rule.op === "after" || rule.op === "on";89 let a: number | null, b: number | null;90 if (isDateType) { const x = dateOf(v), y = filterDate(fv); a = x ? day(x) : null; b = y ? day(y) : null; } else { a = numberOf(v); b = numberOf(fv); }91 if (a == null || b == null) return false;92 return rule.op === "gt" || rule.op === "after" ? a > b : rule.op === "gte" ? a >= b : rule.op === "lt" || rule.op === "before" ? a < b : rule.op === "lte" ? a <= b : a === b;93 }94 }95 return true;96}97export function matchFilter(f: FilterNode | null | undefined, props: Map<string, Property>, values: Record<string, unknown>): boolean {98 if (!f) return true;99 if (!isGroup(f)) return matchRule(f, props.get(f.property), values);100 if (f.or) return f.or.length === 0 || f.or.some((n) => matchFilter(n, props, values));101 return (f.and ?? []).every((n) => matchFilter(n, props, values));102}103/** SQL pushdown when every rule is simple (title/text/url/select/status/ai/checkbox); null otherwise. */104function sqlFilter(f: FilterNode, props: Map<string, Property>): SQL | null {105 if (isGroup(f)) { const parts = (f.or ?? f.and ?? []).map((n) => sqlFilter(n, props)); if (parts.some((x) => !x)) return null; if (!parts.length) return dsql`true`; return dsql.join(parts as SQL[], f.or ? dsql` or ` : dsql` and `); }106 const p = props.get(f.property); if (!p) return dsql`true`;107 const col = p.type === "title" ? dsql`${schema.pages.title}` : dsql`(${schema.databaseRows.values}->>${p.id})`;108 if (["title", "text", "url", "select", "status", "ai"].includes(p.type)) {109 const v = String(f.value ?? "");110 if (f.op === "empty") return p.type === "title" ? dsql`(${schema.pages.title} = '' or ${schema.pages.title} = 'Untitled')` : dsql`coalesce(${col}, '') = ''`;111 if (f.op === "not_empty") return p.type === "title" ? dsql`(${schema.pages.title} <> '' and ${schema.pages.title} <> 'Untitled')` : dsql`coalesce(${col}, '') <> ''`;112 if ((p.type === "select" || p.type === "status") && (f.op === "eq" || f.op === "neq")) { const o = optionOf(p, v); const id = o?.id ?? v; return f.op === "eq" ? dsql`${col} = ${id}` : dsql`coalesce(${col}, '') <> ${id}`; }113 if (p.type !== "select" && p.type !== "status") {114 if (f.op === "eq") return dsql`lower(coalesce(${col}, '')) = ${v.toLowerCase()}`; if (f.op === "neq") return dsql`lower(coalesce(${col}, '')) <> ${v.toLowerCase()}`;115 if (f.op === "contains") return dsql`${col} ilike ${"%" + v.replace(/[%_\\]/g, "\\$&") + "%"}`; if (f.op === "not_contains") return dsql`coalesce(${col}, '') not ilike ${"%" + v.replace(/[%_\\]/g, "\\$&") + "%"}`;116 if (f.op === "starts_with") return dsql`${col} ilike ${v.replace(/[%_\\]/g, "\\$&") + "%"}`; if (f.op === "ends_with") return dsql`${col} ilike ${"%" + v.replace(/[%_\\]/g, "\\$&")}`;117 }118 }119 if (p.type === "checkbox" && (f.op === "eq" || f.op === "neq")) { const t = f.value === true || f.value === "true" || f.value === 1; return (f.op === "eq") === t ? dsql`coalesce((${schema.databaseRows.values}->>${p.id})::boolean, false) = true` : dsql`coalesce((${schema.databaseRows.values}->>${p.id})::boolean, false) = false`; }120 return null;121}122export function sortRows(rows: RowOut[], sorts: Sort[], props: Map<string, Property>) {123 if (!sorts.length) return rows;124 const keyed = rows.map((r) => ({ r, k: sorts.map((s) => { const p = props.get(s.property); return p ? sortKey(p, r.values[p.id]) : null; }) }));125 keyed.sort((a, b) => { for (let i = 0; i < sorts.length; i++) { const x = a.k[i], y = b.k[i]; if (x === y) continue; if (x == null) return 1; if (y == null) return -1; const c = typeof x === "number" && typeof y === "number" ? x - y : String(x).localeCompare(String(y)); if (c) return sorts[i].dir === "desc" ? -c : c; } return a.r.position - b.r.position; });126 return keyed.map((k) => k.r);127}128export async function queryRows(dbId: string, props: Property[], input: QueryInput & { ids?: string[] }): Promise<QueryResult> {129 const pm = new Map(props.map((p) => [p.id, p]));130 const where: SQL[] = [eq(schema.databaseRows.databaseId, dbId)]; if (input.ids) where.push(inArray(schema.databaseRows.id, input.ids.length ? input.ids : ["00000000-0000-0000-0000-000000000000"]));131 const pushed = input.filter ? sqlFilter(input.filter, pm) : null; if (pushed) where.push(pushed);132 let rows = await hydrate(props, await loadRaw(where));133 if (input.filter && !pushed) rows = rows.filter((r) => matchFilter(input.filter, pm, r.values));134 if (input.search?.trim()) { const q = input.search.trim().toLowerCase(); rows = rows.filter((r) => r.title.toLowerCase().includes(q) || props.some((p) => p.type !== "title" && displayValue(p, r.values[p.id]).toLowerCase().includes(q))); }135 rows = sortRows(rows, input.sorts ?? [], pm);136 const total = rows.length; const offset = Math.max(0, input.offset ?? 0); const limit = Math.min(1000, Math.max(1, input.limit ?? 200));137 return { rows: rows.slice(offset, offset + limit), total, limit, offset };138}139export const resolveView = (view: { config: FilterGroup | any } | undefined, input: QueryInput): QueryInput => ({ ...input, filter: input.filter === undefined ? (view?.config?.filter ?? null) : input.filter, sorts: input.sorts ?? view?.config?.sorts ?? [] });140