SPB Git

Repository Public

README.md 19.2 KB

# 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 (CDuckDB binary target), the system sqlite3.
  • 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

swift
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() (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.
  • 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.
  • 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/NS and TIMESTAMP WITH TIME ZONE are converted to microseconds; enums to strings.
    • 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.
  • 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.
  • 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.
  • 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.
  • 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.

# Worksheet bridge

  • 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.
  • 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.
  • 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 registerView again (or TransformRunner.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. 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.
  • 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.
  • Semantics worth knowing.
    • notEqual, notContains, notIn keep NULL rows (spreadsheet/pandas behaviour), unlike raw SQL.
    • Join keys that are NULL never match.
    • stddev/variance are sample statistics.
    • A pivot step reads the distinct values of the pivot column at compile time (max 2,000) so that its output columns are explicit.
    • changeType/parseDate take an error policy: null (TRY_CAST) or fail.
  • 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.
  • Outputs. run (table), refresh(into:) (dataset), export, materialize (worksheet).
  • Storage. TransformStore keeps pipelines in Workbook.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; call PivotEngine.releaseSource when 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.raw always 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 / quantity is sum(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 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.
  • 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).
  • Storage. PivotSpec.payload() is the payload for SheetObject(kind: "pivot"); refresh = compute again.

# Embedding DuckDB in the app

What the package links against, as verified with swift build / swift test / otool:

  • 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.
  • 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.
  • sqlite3 is 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):

  1. 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.
  2. Rpath: the app executable needs @executable_path/../Frameworks in its runpath search paths (Xcode's default for apps).
  3. 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.
  4. Optionally thin it to the shipped architecture (lipo -thin arm64) before signing to save about 68 MB.
  5. 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.

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:

sh
/opt/homebrew/bin/python3 Tests/SheetDataTests/Generators/generate_fixtures.py

# Limitations

  • SheetCore name clash. SheetCore exports a struct named SheetData, the same as this module. Code importing both cannot write SheetData.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 changeType step.
  • 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 \w is ASCII-only. A pivot step runs a DISTINCT query 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.
  • importRange reads 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.
Files main
Menu

Theme