pf_app/ui/src/duckServerHandler.js

// SPIKE (Option C): a Perspective VirtualServerHandler that runs in the browser
// but executes its generated SQL on a SERVER-SIDE DuckDB (routes/perspective.js).
//
// Perspective's GenericSQLVirtualServerModel (WASM) turns each view interaction
// into a SQL string; we POST that SQL to /api/perspective/sql and feed the
// result back. Metadata queries come back as JSON; viewGetData comes back as
// Arrow IPC and is handed straight to the data slice. Only generated SQL and
// the aggregated viewport cross the wire.
//
// Adapted from @perspective-dev/client/src/ts/virtual_servers/duckdb.ts
// (which targets in-browser duckdb-wasm). Same SQL model, different transport.

const NUMBER_AGGS = [
    'sum', 'count', 'any_value', 'arbitrary', 'array_agg', 'avg', 'bit_and',
    'bit_or', 'bit_xor', 'bitstring_agg', 'bool_and', 'bool_or', 'countif',
    'favg', 'fsum', 'geomean', 'kahan_sum', 'last', 'max', 'min', 'product',
    'string_agg', 'sumkahan',
]
const STRING_AGGS = [
    'count', 'any_value', 'arbitrary', 'first', 'countif', 'last', 'string_agg',
]
const FILTER_OPS = [
    '==', '!=', 'LIKE', 'IS DISTINCT FROM', 'IS NOT DISTINCT FROM',
    '>=', '<=', '>', '<',
]

function duckdbTypeToPsp(name) {
    name = String(name).toLowerCase()
    if (name === 'varchar' || name === 'utf8') return 'string'
    if (name === 'double' || name === 'bigint' || name === 'hugeint' ||
        name === 'float64' || name.startsWith('decimal')) return 'float'
    if (name.startsWith('int')) return 'integer'
    if (name.startsWith('date')) return 'date'
    if (name.startsWith('bool')) return 'boolean'
    if (name.startsWith('timestamp')) return 'datetime'
    if (name.startsWith('json') || name.startsWith('struct')) return 'string'
    console.warn(`[duck-server] unknown type '${name}'`)
    return 'string'
}

// Build a VirtualServerHandler bound to the given perspective module (for the
// SQL generator) and server SQL endpoint. onTiming(ms, bytes) is called for
// every viewGetData round trip so the UI can show interaction latency.
export function makeDuckServerHandler(mod, { sqlUrl = '/api/perspective/sql', onTiming } = {}) {
    let sqlBuilder = null
    // The SQL generator must come from an INITIALIZED perspective wasm module.
    // The <perspective-viewer> element's static __wasm_module__ is initialized
    // once the element is registered; the bare client default export is not, so
    // `new perspective.GenericSQLVirtualServerModel()` throws on its wasm glue.
    // Mirror @perspective-dev's reference DuckDBHandler and resolve from the viewer.
    function wasmModule() {
        const viewerClass = typeof customElements !== 'undefined'
            && customElements.get('perspective-viewer')
        if (viewerClass && viewerClass.__wasm_module__) return viewerClass.__wasm_module__
        return mod
    }
    const builder = () => (sqlBuilder ||= new (wasmModule().GenericSQLVirtualServerModel)())

    async function postSql(sql, wantArrow) {
        const r = await fetch(sqlUrl, {
            method: 'POST',
            headers: { 'Content-Type': 'application/json' },
            body: JSON.stringify({ sql, arrow: !!wantArrow }),
        })
        if (!r.ok) {
            let msg = r.statusText
            try { msg = (await r.json()).error } catch {}
            throw new Error(`[duck-server] ${msg}`)
        }
        return wantArrow ? new Uint8Array(await r.arrayBuffer()) : r.json()
    }

    return {
        getFeatures() {
            return {
                group_by: true,
                split_by: true,
                sort: true,
                expressions: true,
                group_rollup_mode: ['rollup', 'flat', 'total'],
                filter_ops: {
                    integer: FILTER_OPS, float: FILTER_OPS, string: FILTER_OPS,
                    boolean: FILTER_OPS, date: FILTER_OPS, datetime: FILTER_OPS,
                },
                aggregates: {
                    integer: NUMBER_AGGS, float: NUMBER_AGGS, string: STRING_AGGS,
                    boolean: STRING_AGGS, date: STRING_AGGS, datetime: STRING_AGGS,
                },
            }
        },

        async getHostedTables() {
            const rows = await postSql(builder().getHostedTables())
            return rows.map(r => `${r.database || 'memory'}.${r.name}`)
        },

        async tableSchema(tableId) {
            const rows = await postSql(builder().tableSchema(tableId))
            const schema = {}
            for (const r of rows) {
                if (!String(r.column_name).startsWith('__')) {
                    schema[r.column_name] = duckdbTypeToPsp(r.column_type)
                }
            }
            return schema
        },

        async tableSize(tableId) {
            const rows = await postSql(builder().tableSize(tableId))
            return Number(rows[0]['count_star()'])
        },

        async tableMakeView(tableId, viewId, config) {
            await postSql(builder().tableMakeView(tableId, viewId, config))
        },

        async tableValidateExpression(tableId, expression) {
            const rows = await postSql(builder().tableValidateExpression(tableId, expression))
            return duckdbTypeToPsp(rows[0].column_type)
        },

        async viewColumnSize(viewId, config) {
            const rows = await postSql(builder().viewColumnSize(viewId))
            const count = Number(Object.values(rows[0])[0])
            const gs = config?.group_by?.length || 0
            const isFlat = config?.group_rollup_mode === 'flat'
            return count - (gs === 0 ? 0 : isFlat ? gs : gs + 1)
        },

        async viewSize(viewId) {
            const rows = await postSql(builder().viewSize(viewId))
            return Number(Object.values(rows[0])[0])
        },

        async viewDelete(viewId) {
            await postSql(builder().viewDelete(viewId))
        },

        async viewGetMinMax(viewId, columnName, config) {
            const rows = await postSql(builder().viewGetMinMax(viewId, columnName, config))
            let [min, max] = Object.values(rows[0])
            if (typeof min === 'bigint') min = Number(min)
            if (typeof max === 'bigint') max = Number(max)
            return { min: min ?? null, max: max ?? null }
        },

        async viewGetData(viewId, config, schema, viewport, dataSlice) {
            const t0 = performance.now()
            const ipc = await postSql(builder().viewGetData(viewId, config, viewport, schema), true)
            onTiming?.(performance.now() - t0, ipc.length)
            dataSlice.fromArrowIpc(ipc)
        },
    }
}