Repository Public
1# SheetData23The large-data engine of ORIX Sheet: an embedded DuckDB engine per open workbook (in memory in the 1.0 app, see below), a visual data-preparation pipeline compiled to SQL, and a pivot engine.45The rule the package enforces: **the worksheet grid and the analytical engine are separate execution environments.** A 10-million-row file is imported into (or scanned by) DuckDB, browsed through a virtualized window, queried and aggregated there. Only bounded results cross into a worksheet, and a result that does not fit 1,048,576 rows × 16,384 columns is refused with an error that says so.67- Swift 6 language mode, macOS 14+, no SwiftUI/AppKit.8- Dependencies: `../SheetCore`, the vendored DuckDB 2.0.1 C library (`CDuckDB` binary target), the system `sqlite3`.9- Measured numbers: [BENCHMARKS.md](BENCHMARKS.md).1011## Layout1213| Path | Contents |14|---|---|15| `Sources/SheetData/Engine` | `DataEngine` (open, security, query/stream/execute, interrupt, progress), `DuckConnection` (C API wrapper), `ColumnBuilder` (vectors → typed columns), `DataValue`/`DataTable`, `SQL` (quoting, expression scanner), filters, catalog/preview/profile/export, worksheet bridge |16| `Sources/SheetData/Import` | file formats, import options, staging, SQLite reader |17| `Sources/SheetData/Transform` | `TransformPipeline`/`TransformStep`, `TransformCompiler` (CTE chain), `TransformRunner`, `TransformStore` |18| `Sources/SheetData/Pivot` | `PivotSpec`, `PivotSQL` (GROUPING SETS query), `PivotEngine`/`PivotResult`, rendering |19| `Sources/orix-data-bench` | benchmark CLI |20| `Tests/SheetDataTests` | tests, `Generators/generate_fixtures.py` (pandas reference), `Fixtures/` |2122## ORIX Data Core2324```swift25let engine = try DataEngine() // in-memory, spills to a private temp directory26let info = try engine.importFile(url, as: "orders") // csv/tsv/parquet/json/jsonl/sqlite/duckdb27let page = try engine.preview("orders", offset: 5_000_000, limit: 200,28 orderBy: [SortSpec("amount", ascending: false)])29let top = try engine.query("SELECT region, sum(amount) FROM orders WHERE status = ? GROUP BY 1", parameters: ["paid"])30try engine.materialize(top, into: calcEngine, sheet: 0, at: CellRef(0, 0), includeHeader: true)31```3233- **One engine per open workbook.** The package offers two modes: `DataEngine()` (path `nil`) gives an in-memory database whose larger-than-memory operators spill to `spillDirectory`, and `DataEngine(path:)` opens a persistent DuckDB file. **The ORIX Sheet 1.0 app only uses the in-memory mode** (`FeatureData.DataWorkspace.engine()` calls `DataEngine()`): no DuckDB file is stored in or next to the workbook. Datasets are linked, not embedded: the workbook records how to rebuild each one (`DatasetCatalog`: a file with its import options, a worksheet range, or the SQL of a stored query) and they are re-imported from their recorded sources when the workbook is reopened; a source that has moved is reported as missing. The persistent mode is implemented and tested (`persistentDatabaseReopens`) but has no caller in the app. `memory_limit` defaults to half of the physical RAM.34- **Threading.** Every public method is thread-safe. Statements run one at a time on a main connection (recursive lock). `preview`, `datasets`, `rowCount` and `schema(of:)` use a second connection, so the grid keeps scrolling during an import. `interrupt()` and `progress` never block; an interrupted statement throws `DataError.interrupted`, and an interrupt while idle is a no-op.35- **Results** are column-oriented (`DataTable` → `DataColumn.Storage`): bool, int64, double, decimal (exact text + double), string, date (days since 1970-01-01), timestamp (µs since epoch, UTC), time (µs since midnight), blob, with a validity mask.36 - All integer widths up to 64 bits map to int64. `HUGEINT`/`UBIGINT`/`UHUGEINT` (e.g. `sum()` of integers) are int64 when every value fits, otherwise exact decimal text.37 - `TIMESTAMP_S/MS/NS` and `TIMESTAMP WITH TIME ZONE` are converted to microseconds; enums to strings.38 - Other types (lists, structs, maps, UUID, intervals…) are cast to `VARCHAR` by DuckDB: the statement is re-run with those columns cast, which only works for a single `SELECT` with distinct column names. Otherwise such a column comes back as NULLs.39- `query(_:parameters:limit:)` streams and stops at `limit` (sets `truncated`); `stream` delivers chunks of about `chunkRows` rows; scripts of several statements are allowed, parameters bind to the last one.40- `preview` pages are stable: with `orderBy`, ties are broken by `rowid` (tables) or by the remaining columns (views); without it, tables are read in insertion order.41- `profile` computes count, nulls, approximate distinct count, min, max, mean, sample standard deviation and exact quartiles in one aggregation pass, plus one grouped query per column for the top values.42- `datasets()` lists every table and view of the main schema (including tables created by plain SQL). The engine's own working relations (`orix_src_*`, `orix_pivot_*`) are hidden unless `includeInternal` is set.4344### Worksheet bridge4546- `importRange` copies a range into a table through an appender. Per column: only numbers → `BIGINT` if all whole, else `DOUBLE`; only booleans → `BOOLEAN`; columns the caller lists in `dateColumns` → `DATE`, or `TIMESTAMP` if any value has a time fraction; anything else → `VARCHAR`. Blank cells and error values become NULL. The range is clipped to the used area. The workbook is read without locking: call it from the thread that owns the workbook.47- `materialize` / `materializeQuery` / `PivotEngine.write` write in **one undoable transaction** and throw `DataError.exceedsSheetLimits` *before* writing anything. `materializeQuery` fetches at most the rows that fit plus one, so a refusal costs one sheet's worth of rows, never the whole result.48- Dates are written as serial numbers (honouring `Workbook.date1904`) with a number format; timestamps are written as their UTC wall-clock time.4950## Security posture of the embedded DuckDB5152DuckDB is configured before any caller SQL runs, and the configuration is then locked (`lock_configuration = true`), so no SQL statement can undo it.5354| Risk | Setting | Effect |55|---|---|---|56| Extension download/install | `autoinstall_known_extensions = false`, `allow_community_extensions = false`, `allow_unsigned_extensions = false`, `extension_directory` = an empty private directory | `INSTALL` fails; nothing is fetched from the network |57| Extension loading | `autoload_known_extensions = false`, `enable_external_access = false` | `LOAD` fails; only the statically linked `core_functions`, `parquet`, `json`, `icu`, `autocomplete` exist |58| Network / remote files | `enable_external_access = false` | `http(s)://`, `s3://` and friends are rejected |59| Reading or writing arbitrary files | `enable_external_access = false` + `allowed_directories = [stagingDirectory]` (DuckDB appends its `temp_directory`, i.e. `spillDirectory`, by itself: the effective list is `[stagingDirectory, spillDirectory]`) | `read_csv`, `read_text`, `glob`, `COPY … TO`, `EXPORT DATABASE`, `ATTACH` only work inside the engine's staging directory and its spill directory, both private to the engine; larger-than-memory operators spill without any further setting (test `spillingWorksAndTheAllowListIsStagingPlusSpill`) |60| Secrets on disk | `allow_persistent_secrets = false` | |6162Why this matters: with DuckDB's defaults, calling an unknown function such as `sqlite_scan` silently downloads and loads an extension. That was observed while probing the vendored library and is exactly what these settings prevent.6364**How "only files the user explicitly chose" is enforced.** DuckDB can touch nothing outside `stagingDirectory` and its own `spillDirectory` (which holds only DuckDB's temporary files). `importFile` / `registerView` receive a URL from the app (i.e. from an open panel or a drop) and copy that file into the staging directory under a neutral name (`<uuid>/data.<ext>`). On APFS the copy is a clone: instant, no extra disk space. DuckDB reads the staged copy. Exports are written into the staging directory and then moved to the chosen URL. Consequences:6566- A user's file name never appears in SQL text or in DuckDB glob expansion.67- Symlinks do not get around the allow-list (DuckDB 2.0.1 resolves them), which is why files are staged rather than linked.68- A lazy view reads a **snapshot** of the file as of registration; call `registerView` again (or `TransformRunner.refresh`) to pick up changes.69- Across volumes the staging copy is a real copy.70- For a persistent database (`DataEngine(path:)`, not used by the 1.0 app) the staging directory is `<database>.orix-files/` next to it, so views survive a reopen. With the in-memory engine the staging directory is temporary and is rebuilt by the re-import.7172**SQL built from user data.** Identifiers go through `SQL.ident`, constants through `SQL.literal` or bound parameters. Free-form expressions (calculated columns, pivot calculated fields) are checked by `SQL.expressionProblem` (balanced delimiters, terminated quotes, no `;`, no comments, no top-level comma, no `E'…'` or dollar-quoted strings) and wrapped in parentheses. Caller SQL embedded in `COPY` or `CREATE TABLE AS` must be exactly one `SELECT`. These are covered by injection tests.7374**What is not restricted.** `query`/`execute` accept arbitrary SQL *inside the sandbox*: a user can drop or alter their own datasets, including the `orix_meta` catalog table. SQLite files are opened read-only by the system SQLite library with `trusted_schema` off.7576## Formats7778| Format | Import (copy) | Lazy view | Notes |79|---|---|---|---|80| CSV / TSV | yes | yes | DuckDB sniffer; options for delimiter, header, quote, escape, NULL strings, date/timestamp format, decimal separator, per-column types, all-text, skip rows, sample size, ignore errors |81| Parquet | yes | yes | projection and filter pushdown (row-group statistics) |82| JSON / JSON Lines | yes | yes | array or newline-delimited; nested values come back as text |83| SQLite | yes | no | DuckDB's `sqlite_scanner` is **not** statically linked in the vendored library, so rows are read with the system `sqlite3` and appended. Column types come from the storage classes actually present (one aggregate scan), not from the declared types; declared `DATE`/`DATETIME` text columns become temporal when every value converts. A file with several tables needs `ImportOptions.sourceTable`. |84| DuckDB database | yes | no | staged, attached read-only, one table copied, detached |85| Arrow IPC / Feather | **no** | no | needs DuckDB's arrow extension, which is not in the library and is never downloaded; reported as `unsupportedFormat` |86| XLSX | no | no | out of scope (the app's file layer loads workbooks) |8788Export (`exportQuery`, `exportDataset`): CSV, Parquet (zstd by default), JSON (array or lines).8990## ORIX Transform9192A `TransformPipeline` is a source (dataset, file, or worksheet range) plus an ordered list of `TransformStep`s. `TransformCompiler.sql` compiles it into one statement: a chain of CTEs `s0 … sN`, where `sN` is the output of step N. Cutting the chain after any step gives that step's preview.9394- **Validation.** After each step the compiler asks DuckDB to bind the chain so far (`DESCRIBE`), so unknown columns, unknown functions and type mismatches are attributed to the step that causes them. `validate` returns one diagnostic per step (`ok` with the schema after it, `error` with a message, `blocked` after an error). Steps can be edited, removed or reordered freely; validation says which one breaks.95- **Row order.** A `sort` step sets an active ordering that later steps honour: `limit` keeps the first rows in that order, `fillMissing(.previous/.next)` and `removeDuplicates` follow it, `first`/`last` aggregate in it, and it is re-applied to the final result. Joins, group-bys, pivots, unpivots, appends and samples reset it. Without a sort, "previous/next/first" follow the natural scan order.96- **Semantics worth knowing.**97 - `notEqual`, `notContains`, `notIn` keep NULL rows (spreadsheet/pandas behaviour), unlike raw SQL.98 - Join keys that are NULL never match.99 - `stddev`/`variance` are sample statistics.100 - A `pivot` step reads the distinct values of the pivot column at compile time (max 2,000) so that its output columns are explicit.101 - `changeType`/`parseDate` take an error policy: `null` (`TRY_CAST`) or `fail`.102- **Sources.** File sources are registered as lazy views (CSV/TSV/Parquet/JSON) or imported (SQLite/DuckDB); range sources are copied with `importRange`. `TransformRunner.loadSource`/`refresh` re-read them.103- **Outputs.** `run` (table), `refresh(into:)` (dataset), `export`, `materialize` (worksheet).104- **Storage.** `TransformStore` keeps pipelines in `Workbook.attachments["transforms/<id>.json"]`.105106## Pivot engine107108`PivotEngine.compute` runs one DuckDB query: a filtered projection of the source, optional top-N CTEs, then `GROUP BY GROUPING SETS` over every (row-field prefix, column-field prefix) pair. The percent-of show-as modes are window functions over that grouped result. DuckDB returns only the non-empty groups; Swift arranges them into ordered axes and a dense value matrix (capped at 20 million cells).109110- **Sources.** A dataset, or a worksheet range. A range is copied once into an engine table `orix_pivot_<id>`, which is kept for drill-down and refresh; call `PivotEngine.releaseSource` when the pivot is deleted.111- **Aggregations.** sum, count (non-empty), countNumbers, average, min, max, sample/population standard deviation and variance, median, distinctCount, product. Results are doubles.112- **Grouping.** Date parts (year, quarter, month, day) and numeric bins. The same column can be used several times with different groupings.113- **Filters.** Include/exclude sets, conditions, and top/bottom N on a row or column field. Top-N ranks the field's items globally (not within each parent), and totals reflect the kept items only.114- **Show-as.** % of grand total, % of row total, % of column total (SQL window functions); running total, difference and % difference from the previous or a named item (computed in Swift on the laid-out matrix, because they depend on the display order). Totals that do not involve the base field keep their plain value. `PivotResult.raw` always holds the plain aggregates.115- **Calculated fields** are evaluated **after aggregation**: each field reference stands for the *sum* of that field over the cell's records, so `revenue / quantity` is `sum(revenue) / sum(quantity)` in every cell, subtotal and grand total. A value that uses a calculated field ignores its own aggregation.116- **Drill-down.** `result.drillDown(row:col:)` returns a `PivotDrillQuery` (SQL + bound keys) selecting the source records behind a cell, with the pivot's filters applied; `PivotEngine.records` runs it. `PivotGrid.dataCoordinates` maps a rendered cell back to those coordinates.117- **Rendering.** `render` returns `[[Value]]`; `renderGrid` adds a role per cell (corner, header, value, subtotal, grand total), header sizes and number formats. Layouts: tabular (one label column per row field, subtotal rows below) and compact (one indented label column, group rows carry the subtotals).118- **Storage.** `PivotSpec.payload()` is the payload for `SheetObject(kind: "pivot")`; refresh = `compute` again.119120## Embedding DuckDB in the app121122What the package links against, as verified with `swift build` / `swift test` / `otool`:123124- `Vendor/duckdb/CDuckDB.xcframework` (built by `Scripts/fetch-duckdb.sh`) wraps the official `libduckdb.dylib`: universal (arm64 + x86_64, about 65 MB per slice), minimum macOS 11.0, install name `@rpath/libduckdb.dylib`.125- SwiftPM copies the dylib next to the built products and the binaries get an `@loader_path` rpath, so command-line builds and tests run with no further setup. No change to the xcframework or the script was needed.126- `sqlite3` is the system `/usr/lib/libsqlite3.dylib`: nothing to embed.127128What the app must do (not verified here, there is no app target in this package):1291301. **Embed** `libduckdb.dylib` in `ORIX Sheet.app/Contents/Frameworks/`. Xcode normally does this for a binary target of a linked package; check the built app rather than assume it.1312. **Rpath**: the app executable needs `@executable_path/../Frameworks` in its runpath search paths (Xcode's default for apps).1323. **Re-sign** the dylib with the app's identity. The vendored file carries DuckDB's own signature (Team ID `7NCTQWA3HA`, hardened runtime); a hardened-runtime app with library validation will refuse a library signed by another team. "Embed & Sign" re-signs it; notarization then covers it.1334. Optionally **thin** it to the shipped architecture (`lipo -thin arm64`) before signing to save about 68 MB.1345. Verify: `otool -L` on the app binary shows `@rpath/libduckdb.dylib`; `codesign --verify --deep --strict` passes; `codesign -dv Contents/Frameworks/libduckdb.dylib` shows the app's Team ID.135136App Sandbox: URLs from open/save panels are security-scoped. Call `startAccessingSecurityScopedResource()` around `importFile`, `registerView` and `exportQuery`; staging and spill directories live in the app's temporary directory. No network entitlement is needed.137138## Tests139140`swift test` runs 62 tests (plus one gated by `ORIX_DATA_LARGE_TESTS=1`, 1,000,000 rows). The 10M benchmark is CLI-only.141142Reference method: `Tests/SheetDataTests/Generators/generate_fixtures.py` (Python 3, pandas, numpy, pyarrow; **no DuckDB**) generates seeded synthetic fixtures (CSV, Parquet, JSON Lines, SQLite, Arrow, CSV dialects, hostile column names) and writes `expected.json`: aggregates, profiles, the expected table of every transform scenario, joins of each kind, and pivot cubes computed with `groupby` at every (row prefix, column prefix) level, show-as variants, sort orders and drill-down row sets. The Swift tests compare the engine's output with those references cell by cell (relative tolerance 1e-9 for tables, 1e-8 for pivots). Regenerate with:143144```sh145/opt/homebrew/bin/python3 Tests/SheetDataTests/Generators/generate_fixtures.py146```147148## Limitations149150- **SheetCore name clash.** SheetCore exports a struct named `SheetData`, the same as this module. Code importing both cannot write `SheetData.DataEngine`; unqualified names work.151- **Views are snapshots** of the file at registration (see security); they are not live.152- **Arrow IPC is unsupported**; SQLite and DuckDB files cannot be lazy views.153- **Non-native DuckDB types** are text (see results). JSON column type overrides are not supported; use a `changeType` step.154- **Pivot.** Values are doubles (min/max of dates or text are not supported). Top-N is global, not per parent. Running totals and differences are computed in Swift, not in SQL. There is no "show values as rank/index" and no per-field subtotal switch (subtotals are all on or all off).155- **Transform.** `splitColumn(.delimiter)` splits on every occurrence. The regex dialect is RE2, so `\w` is ASCII-only. A pivot step runs a `DISTINCT` query over the pipeline prefix at compile time.156- **Grid writes are per cell** through `CalcEngine.set` (about 0.86 M cells/s measured); SheetCore has no bulk write.157- **`importRange` reads the workbook unlocked**, and maps error cells to NULL.158- **Timestamps with time zone** are exposed as UTC instants; no time-zone conversion is offered on the way to the grid.159- **Lifetime of working tables.** `orix_src_*` relations of file/range pipelines stay until dropped (`dropDataset`); nothing garbage-collects them when a pipeline is deleted.160