SheetData
The 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.
The 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.
- Swift 6 language mode, macOS 14+, no SwiftUI/AppKit.
- Dependencies:
../SheetCore, the vendored DuckDB 2.0.1 C library (CDuckDBbinary target), the systemsqlite3. - Measured numbers: BENCHMARKS.md.
Layout
| Path | Contents |
|---|---|
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 |
Sources/SheetData/Import |
file formats, import options, staging, SQLite reader |
Sources/SheetData/Transform |
TransformPipeline/TransformStep, TransformCompiler (CTE chain), TransformRunner, TransformStore |
Sources/SheetData/Pivot |
PivotSpec, PivotSQL (GROUPING SETS query), PivotEngine/PivotResult, rendering |
Sources/orix-data-bench |
benchmark CLI |
Tests/SheetDataTests |
tests, Generators/generate_fixtures.py (pandas reference), Fixtures/ |
ORIX Data Core
let engine = try DataEngine() // in-memory, spills to a private temp directory
let info = try engine.importFile(url, as: "orders") // csv/tsv/parquet/json/jsonl/sqlite/duckdb
let page = try engine.preview("orders", offset: 5_000_000, limit: 200,
orderBy: [SortSpec("amount", ascending: false)])
let top = try engine.query("SELECT region, sum(amount) FROM orders WHERE status = ? GROUP BY 1", parameters: ["paid"])
try engine.materialize(top, into: calcEngine, sheet: 0, at: CellRef(0, 0), includeHeader: true)- One engine per open workbook. The package offers two modes:
DataEngine()(pathnil) gives an in-memory database whose larger-than-memory operators spill tospillDirectory, andDataEngine(path:)opens a persistent DuckDB file. The ORIX Sheet 1.0 app only uses the in-memory mode (FeatureData.DataWorkspace.engine()callsDataEngine()): 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_limitdefaults to half of the physical RAM. - Threading. Every public method is thread-safe. Statements run one at a time on a main connection (recursive lock).
preview,datasets,rowCountandschema(of:)use a second connection, so the grid keeps scrolling during an import.interrupt()andprogressnever block; an interrupted statement throwsDataError.interrupted, and an interrupt while idle is a no-op. - 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.- 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. TIMESTAMP_S/MS/NSandTIMESTAMP WITH TIME ZONEare converted to microseconds; enums to strings.- Other types (lists, structs, maps, UUID, intervals…) are cast to
VARCHARby DuckDB: the statement is re-run with those columns cast, which only works for a singleSELECTwith distinct column names. Otherwise such a column comes back as NULLs.
- All integer widths up to 64 bits map to int64.
query(_:parameters:limit:)streams and stops atlimit(setstruncated);streamdelivers chunks of aboutchunkRowsrows; scripts of several statements are allowed, parameters bind to the last one.previewpages are stable: withorderBy, ties are broken byrowid(tables) or by the remaining columns (views); without it, tables are read in insertion order.profilecomputes 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.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 unlessincludeInternalis set.
Worksheet bridge
importRangecopies a range into a table through an appender. Per column: only numbers →BIGINTif all whole, elseDOUBLE; only booleans →BOOLEAN; columns the caller lists indateColumns→DATE, orTIMESTAMPif 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.materialize/materializeQuery/PivotEngine.writewrite in one undoable transaction and throwDataError.exceedsSheetLimitsbefore writing anything.materializeQueryfetches at most the rows that fit plus one, so a refusal costs one sheet's worth of rows, never the whole result.- Dates are written as serial numbers (honouring
Workbook.date1904) with a number format; timestamps are written as their UTC wall-clock time.
Security posture of the embedded DuckDB
DuckDB is configured before any caller SQL runs, and the configuration is then locked (lock_configuration = true), so no SQL statement can undo it.
| Risk | Setting | Effect |
|---|---|---|
| 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 |
| Extension loading | autoload_known_extensions = false, enable_external_access = false |
LOAD fails; only the statically linked core_functions, parquet, json, icu, autocomplete exist |
| Network / remote files | enable_external_access = false |
http(s)://, s3:// and friends are rejected |
| 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) |
| Secrets on disk | allow_persistent_secrets = false |
Why 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.
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:
- A user's file name never appears in SQL text or in DuckDB glob expansion.
- Symlinks do not get around the allow-list (DuckDB 2.0.1 resolves them), which is why files are staged rather than linked.
- A lazy view reads a snapshot of the file as of registration; call
registerViewagain (orTransformRunner.refresh) to pick up changes. - Across volumes the staging copy is a real copy.
- 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.
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.
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.
Formats
| Format | Import (copy) | Lazy view | Notes |
|---|---|---|---|
| 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 |
| Parquet | yes | yes | projection and filter pushdown (row-group statistics) |
| JSON / JSON Lines | yes | yes | array or newline-delimited; nested values come back as text |
| 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. |
| DuckDB database | yes | no | staged, attached read-only, one table copied, detached |
| Arrow IPC / Feather | no | no | needs DuckDB's arrow extension, which is not in the library and is never downloaded; reported as unsupportedFormat |
| XLSX | no | no | out of scope (the app's file layer loads workbooks) |
Export (exportQuery, exportDataset): CSV, Parquet (zstd by default), JSON (array or lines).
ORIX Transform
A TransformPipeline is a source (dataset, file, or worksheet range) plus an ordered list of TransformSteps. 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.
- 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.validatereturns one diagnostic per step (okwith the schema after it,errorwith a message,blockedafter an error). Steps can be edited, removed or reordered freely; validation says which one breaks. - Row order. A
sortstep sets an active ordering that later steps honour:limitkeeps the first rows in that order,fillMissing(.previous/.next)andremoveDuplicatesfollow it,first/lastaggregate 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. - Semantics worth knowing.
notEqual,notContains,notInkeep NULL rows (spreadsheet/pandas behaviour), unlike raw SQL.- Join keys that are NULL never match.
stddev/varianceare sample statistics.- A
pivotstep reads the distinct values of the pivot column at compile time (max 2,000) so that its output columns are explicit. changeType/parseDatetake an error policy:null(TRY_CAST) orfail.
- Sources. File sources are registered as lazy views (CSV/TSV/Parquet/JSON) or imported (SQLite/DuckDB); range sources are copied with
importRange.TransformRunner.loadSource/refreshre-read them. - Outputs.
run(table),refresh(into:)(dataset),export,materialize(worksheet). - Storage.
TransformStorekeeps pipelines inWorkbook.attachments["transforms/<id>.json"].
Pivot engine
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).
- 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; callPivotEngine.releaseSourcewhen the pivot is deleted. - Aggregations. sum, count (non-empty), countNumbers, average, min, max, sample/population standard deviation and variance, median, distinctCount, product. Results are doubles.
- Grouping. Date parts (year, quarter, month, day) and numeric bins. The same column can be used several times with different groupings.
- 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.
- 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.rawalways holds the plain aggregates. - Calculated fields are evaluated after aggregation: each field reference stands for the sum of that field over the cell's records, so
revenue / quantityissum(revenue) / sum(quantity)in every cell, subtotal and grand total. A value that uses a calculated field ignores its own aggregation. - Drill-down.
result.drillDown(row:col:)returns aPivotDrillQuery(SQL + bound keys) selecting the source records behind a cell, with the pivot's filters applied;PivotEngine.recordsruns it.PivotGrid.dataCoordinatesmaps a rendered cell back to those coordinates. - Rendering.
renderreturns[[Value]];renderGridadds 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). - Storage.
PivotSpec.payload()is the payload forSheetObject(kind: "pivot"); refresh =computeagain.
Embedding DuckDB in the app
What the package links against, as verified with swift build / swift test / otool:
Vendor/duckdb/CDuckDB.xcframework(built byScripts/fetch-duckdb.sh) wraps the officiallibduckdb.dylib: universal (arm64 + x86_64, about 65 MB per slice), minimum macOS 11.0, install name@rpath/libduckdb.dylib.- SwiftPM copies the dylib next to the built products and the binaries get an
@loader_pathrpath, so command-line builds and tests run with no further setup. No change to the xcframework or the script was needed. sqlite3is the system/usr/lib/libsqlite3.dylib: nothing to embed.
What the app must do (not verified here, there is no app target in this package):
- Embed
libduckdb.dylibinORIX Sheet.app/Contents/Frameworks/. Xcode normally does this for a binary target of a linked package; check the built app rather than assume it. - Rpath: the app executable needs
@executable_path/../Frameworksin its runpath search paths (Xcode's default for apps). - 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. - Optionally thin it to the shipped architecture (
lipo -thin arm64) before signing to save about 68 MB. - Verify:
otool -Lon the app binary shows@rpath/libduckdb.dylib;codesign --verify --deep --strictpasses;codesign -dv Contents/Frameworks/libduckdb.dylibshows the app's Team ID.
App 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.
Tests
swift test runs 62 tests (plus one gated by ORIX_DATA_LARGE_TESTS=1, 1,000,000 rows). The 10M benchmark is CLI-only.
Reference 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:
/opt/homebrew/bin/python3 Tests/SheetDataTests/Generators/generate_fixtures.pyLimitations
- SheetCore name clash. SheetCore exports a struct named
SheetData, the same as this module. Code importing both cannot writeSheetData.DataEngine; unqualified names work. - Views are snapshots of the file at registration (see security); they are not live.
- Arrow IPC is unsupported; SQLite and DuckDB files cannot be lazy views.
- Non-native DuckDB types are text (see results). JSON column type overrides are not supported; use a
changeTypestep. - 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).
- Transform.
splitColumn(.delimiter)splits on every occurrence. The regex dialect is RE2, so\wis ASCII-only. A pivot step runs aDISTINCTquery over the pipeline prefix at compile time. - Grid writes are per cell through
CalcEngine.set(about 0.86 M cells/s measured); SheetCore has no bulk write. importRangereads the workbook unlocked, and maps error cells to NULL.- Timestamps with time zone are exposed as UTC instants; no time-zone conversion is offered on the way to the grid.
- Lifetime of working tables.
orix_src_*relations of file/range pipelines stay until dropped (dropDataset); nothing garbage-collects them when a pipeline is deleted.