SPB Git

Repository Public

data-engine.md 29.0 KB 346 lines markdown

1# Data engine23ORIX Sheet keeps two execution environments apart. The worksheet grid is evaluated by the calculation engine (see [formula-engine.md](formula-engine.md) and [calculation-graph.md](calculation-graph.md)). Tabular data that is too large, or too relational, for a grid lives in an embedded DuckDB database, one per open workbook. Rows cross between the two only through two explicit, bounded operations: a range is copied into a table, and a result is written into a range.45Two packages implement this:67| Package | Role | Imports |8|---|---|---|9| `Packages/SheetData` | `DataEngine` (DuckDB wrapper), file import, transform compiler, pivot engine, worksheet bridge. No AppKit, no SwiftUI | `SheetCore`, the `CDuckDB` binary target, system `sqlite3` |10| `Packages/FeatureData` | `DataWorkspace` (per-workbook engine owner and work queue), Data Studio, Transform studio, pivot inspector, virtualized dataset grid | `SheetCore`, `SheetUI`, `SheetData` |1112Source: `Packages/SheetData/Package.swift`, `Packages/FeatureData/Package.swift`.1314<!-- diagram: data-engine-overview -->15```mermaid16flowchart TD17    FILE["User-chosen file<br/>CSV, TSV, Parquet, JSON, SQLite, DuckDB"] -->|staged copy| STAGE["Staging directory<br/>uuid/data.ext"]18    STAGE -->|read_csv etc.| DUCK["DuckDB database<br/>in memory, per workbook"]19    RANGE["Worksheet range"] -->|importRange| DUCK20    DUCK -->|200-row pages| GRID["Dataset grid<br/>Data Studio"]21    DUCK -->|SQL| RESULT["Bounded result (DataTable)<br/>transform SQL, pivot SQL, query"]22    RESULT -->|size check| SHEET["Worksheet cells<br/>CalcEngine.set, one undo step"]23    DUCK -->|COPY, then move| EXPORT["Exported file<br/>CSV, Parquet, JSON"]24```25SVG fallback: ![Data engine overview](../assets/diagrams/data-engine-overview.svg)2627## Embedding DuckDB2829DuckDB is not built from source. `Scripts/fetch-duckdb.sh` downloads the official universal shared library `duckdb-shared-libs-osx-universal.tar.gz` for the pinned version **v2.0.1**, verifies a pinned SHA-256, and wraps `libduckdb.dylib` and `duckdb.h` into `Vendor/duckdb/CDuckDB.xcframework`. The binary is listed in `.gitignore` and is not kept in the repository. `SheetData` declares it as a SwiftPM `binaryTarget` and calls the C API directly (`Engine/DuckConnection.swift`); there is no Swift DuckDB package in between.3031What the app must do to ship the library (embedding, re-signing with the app's identity) is described in [../release/signing.md](../release/signing.md). The package README states that these app-level steps were "not verified here" from inside the package; they are a property of the app build, not of `SheetData`.3233SQLite files are read with the system `/usr/lib/libsqlite3.dylib` (`linkerSettings: [.linkedLibrary("sqlite3")]`), because DuckDB's `sqlite_scanner` extension is not part of the vendored library and extensions are never downloaded (see below).3435## One engine per workbook3637`DataEngine(path:options:)` opens either a database file or, when `path` is `nil`, an in-memory database. **The application always uses the in-memory form**: `DataWorkspace.engine()` calls `try DataEngine()` with no arguments, lazily, the first time a data feature needs it. The file-backed mode exists in the package and is covered by its API, but nothing in `FeatureData` opens a persistent database.3839Consequences of the in-memory choice, stated in the `DataWorkspace` source comment ("Datasets are linked, not embedded"):4041- The rows of a dataset are **not saved in the workbook**. The workbook stores a recipe, `DatasetCatalog`, in the attachment `data/datasets.json`: for each dataset its name and origin (`file` with path, bookmark, format, import options and the lazy flag; `range` with sheet id, range, header flag and date columns; or `query` with the SQL text).42- When a workbook with a catalog is opened, `DataWorkspace.of(session)` starts `restoreAll()`, which re-imports each dataset from its source. A file that cannot be found (the bookmark is tried first, then the recorded path) puts the dataset in the `missingSource` state; the user can reconnect it to another file.43- Closing the document calls `DataWorkspace.close(session)` (registered in `App/Sources/Features.swift` as a close handler), which interrupts running work and releases the engine. The engine's `deinit` closes DuckDB and removes its private temporary directory.4445### Working directories4647For an in-memory engine the constructor creates a unique root `orix-data-<UUID>` in the user's temporary directory with three sub-directories:4849| Directory | Purpose |50|---|---|51| `spill/` | DuckDB `temp_directory`: spill files of larger-than-memory operators |52| `extensions-disabled/` | DuckDB `extension_directory`: an empty directory, so that no previously installed extension can be found |53| `files/` | the **staging directory**: the only place DuckDB may read and write user data files (the spill directory is the other allowed location, for DuckDB's own temporary files) |5455Source: `Packages/SheetData/Sources/SheetData/Engine/DataEngine.swift` (`init`).5657For a file-backed database the staging directory is `<database path>.orix-files/` next to the database, so that lazy views survive a reopen. This path is not exercised by the app.5859### Memory and threads6061`memory_limit` defaults to half of the physical memory, with a floor of 64 MiB (`max(options.memoryLimitBytes ?? physical / 2, 64 << 20)`). `threads` is left to DuckDB (all cores) unless `DataEngineOptions.threads` is set. `max_temp_directory_size` is DuckDB's default unless set. The app does not override any of these.6263## Security configuration6465The configuration is applied in two stages, in this order, before any caller SQL can run.6667At `duckdb_open_ext` time (configuration object):6869| Setting | Value |70|---|---|71| `memory_limit` | half of physical RAM (see above) |72| `temp_directory` | the spill directory |73| `autoinstall_known_extensions` | `false` |74| `autoload_known_extensions` | `false` |75| `allow_community_extensions` | `false` |76| `allow_unsigned_extensions` | `false` |77| `extension_directory` | the empty `extensions-disabled` directory |78| `allow_persistent_secrets` | `false` |7980Then, on the main connection:8182| Statement | Effect |83|---|---|84| `SET allowed_directories = ['<staging directory>']` | the allow-list must be set while external access is still enabled. DuckDB appends its `temp_directory` (the engine's `spillDirectory`) to the list by itself, so the effective value is `[stagingDirectory, spillDirectory]` |85| `SET enable_external_access = false` | no file access outside the allow-list, no remote file systems |86| `SET lock_configuration = true` | none of the above can be changed by a later SQL statement |8788Source: `Packages/SheetData/Sources/SheetData/Engine/DataEngine.swift` (`init`), lines building `settings` and the three `SET` statements.8990The package README explains the reason for the extension settings: with DuckDB's defaults, calling an unknown function such as `sqlite_scan` was observed to download and load an extension. With these settings `INSTALL` and `LOAD` fail. The README also lists the extensions statically linked into the vendored library (`core_functions`, `parquet`, `json`, `icu`, `autocomplete`); that list comes from the README and was not re-measured for this document.9192Things the engine does **not** restrict: `query` and `execute` accept arbitrary SQL inside this sandbox. The SQL console of Data Studio therefore lets a user drop or alter their own datasets, including the engine's catalog table `orix_meta.datasets`.9394The threat model for untrusted data files is summarised in [../security.md](../security.md).9596## The staging directory9798DuckDB can open nothing outside the staging directory and its own spill directory (test `spillingWorksAndTheAllowListIsStagingPlusSpill`), so every user-chosen file is first **staged**: `DataEngine.stage(_:)` copies it to `<staging>/<UUID>/data.<ext>` with `FileManager.copyItem`. The extension is filtered to letters and digits. The effects:99100- The user's file name never appears in SQL text or in a DuckDB glob pattern; the SQL contains only the neutral staged path, quoted with `SQL.string`.101- For an **import** (`importFile`), the staged copy is removed as soon as the `CREATE TABLE … AS SELECT * FROM read_…(…)` statement has finished (`defer { unstage(staged) }`).102- For a **lazy view** (`registerView`), the staged copy stays for as long as the view exists; its path is recorded in `orix_meta.datasets.staged_path` and removed when the dataset is dropped or replaced. A lazy view therefore reads a **snapshot** of the file as of registration, not the live file. Refreshing the dataset stages the file again.103- `unstage` only deletes directories whose path starts with the staging directory.104- **Exports** go the other way: `exportQuery` has DuckDB write into the staging directory and then moves the file to the destination URL.105106The README states that on APFS `copyItem` produces a clone (no extra disk space) and that across volumes it is a real copy; the code relies on `FileManager.copyItem` and does not itself check which of the two happened.107108`DataWorkspace` calls `startAccessingSecurityScopedResource()` around file imports. The app is not sandboxed (`ENABLE_APP_SANDBOX: NO` in `project.yml`), so this is a no-op today.109110## Threading111112Every public `DataEngine` method is thread-safe. The engine holds **two DuckDB connections** to the same database:113114| Connection | Lock | Used by |115|---|---|---|116| `main` | `NSRecursiveLock` | `query`, `stream`, `execute`, imports, exports, transforms, pivots |117| `reader` | `NSLock` | `preview`, `datasets`, `rowCount`, `schema(of:)` |118119Because page fetches use the second connection, the dataset grid keeps scrolling while an import or a long query runs on the first. `interrupt()` and `progress` take no engine lock: `interrupt()` calls `duckdb_interrupt` on the main connection and also sets a flag that the non-DuckDB loops (SQLite import, worksheet import) check every 16,384 rows.120121`DataWorkspace` (main actor) adds two serial dispatch queues on top:122123- `workQueue` (`ai.spboucher.orix.sheet.data.work`) — `run(title) { engine in … }` executes one piece of engine work at a time off the main thread, records an `Activity` (title, progress) that the UI shows, and supports `cancel()`. Progress is polled every 120 ms from `DataEngine.progress`.124- `pageQueue` (`ai.spboucher.orix.sheet.data.pages`) — light reads through the reader connection.125126One operation deliberately runs on the main thread: `importRange` reads the workbook without locking, so `DataWorkspace.importRange` executes it synchronously on the main actor and **refuses it while the engine is busy** ("The data engine is busy…") rather than block the interface behind a long statement.127128Where this fits in the application's overall threading model is described in [overview.md](overview.md).129130## Datasets and sheets131132A **dataset** is a DuckDB table or view in the `main` schema. `DataEngine.datasets()` lists all of them, including tables created by plain SQL; relations whose names start with `orix_src_` (pipeline sources) or `orix_pivot_` (pivot range sources) are the engine's own and are hidden unless `includeInternal` is set.133134| | Dataset | Worksheet |135|---|---|---|136| Storage | DuckDB, columnar, typed columns | sparse cell store of `SheetCore` |137| Size limit | memory limit plus spill directory | 1,048,576 rows × 16,384 columns |138| Saved in the `.orixsheet` file | no — only the recipe (`data/datasets.json`) | yes |139| Viewed through | 200-row pages (`DatasetGridModel`, 24 cached pages) | the grid (see [grid-engine.md](grid-engine.md)) |140| Recalculated by | explicit refresh | the calculation graph |141142Source: `Packages/FeatureData/Sources/FeatureData/Core/DatasetDescriptor.swift`, `DatasetGridModel.swift` (`pageSize: Int = 200, cachedPages: Int = 24`); sheet limits from `SheetCore` `Limits` as used in `DataEngine+Sheet.swift`.143144### Import formats145146| Format | Import (copy into a table) | Lazy view | How |147|---|---|---|---|148| CSV, TSV | yes | yes | `read_csv` on the staged file; options below |149| Parquet | yes | yes | `read_parquet` |150| JSON, JSON Lines | yes | yes | `read_json` with `format = 'auto'` or `'newline_delimited'` |151| SQLite | yes | no | system `sqlite3`, opened `SQLITE_OPEN_READONLY`, `PRAGMA trusted_schema = OFF`, rows appended through the DuckDB appender |152| DuckDB database | yes | no | staged, `ATTACH … (READ_ONLY)`, one table copied, `DETACH` |153| Arrow IPC / Feather | **no** | no | detected, then refused with `unsupportedFormat` (needs DuckDB's arrow extension, which is never downloaded) |154| XLSX | no | no | workbooks are opened by `SheetIO`, see [../compatibility/formats.md](../compatibility/formats.md) |155156Source: `Packages/SheetData/Sources/SheetData/Import/DataEngine+Import.swift`, `ImportOptions.swift`, `SQLiteImport.swift`.157158Format detection (`DataFileFormat.detect`) uses the file extension first (`csv`/`txt`, `tsv`/`tab`, `parquet`/`pq`, `json`, `jsonl`/`ndjson`, `sqlite`/`sqlite3`, `duckdb`/`ddb`, `arrow`/`feather`/`ipc`/`arrows`), then the first 16 bytes (`PAR1`, `SQLite format 3`, `ARROW1`, `DUCK` at offset 8).159160CSV options exposed by `ImportOptions`: delimiter, header, quote, escape, NULL strings, date and timestamp formats, decimal separator, per-column types, all-text, skip rows, sniffer sample size, ignore errors. Every string option reaches SQL through `SQL.string`.161162Export (`exportQuery`, `exportDataset`): CSV, Parquet (`zstd` by default; `snappy`, `gzip`, `lz4`, `uncompressed`; any other value falls back to `zstd`), JSON (one array or newline-delimited).163164### Result types165166Results are column-oriented (`DataTable` → `DataColumn`). The logical types are boolean, integer (int64), double, decimal (exact text plus a double), string, date (days since 1970-01-01), timestamp (microseconds since the epoch, UTC), time (microseconds since midnight) and blob. `DataType(duckDBTypeName:)` maps every DuckDB integer width, including `HUGEINT` and the unsigned types, to `integer`; any type it does not know becomes `string`. Details of the widening rules (for example `HUGEINT` values that do not fit in 64 bits) are in the package README.167168## The worksheet bridge169170### Range to table: `importRange`171172`DataEngine.importRange(workbook, sheet:range:as:hasHeader:dateColumns:)` creates a table with one column per range column and fills it through the DuckDB appender.173174- The range is clipped to the sheet's used area, so a whole-column reference costs only the used rows.175- Column names come from the header row (or `Column1…`, or the column letter when there is no header), made unique case-insensitively.176- Column type, inferred over the **computed** values of the data rows:177178| Content of the column | DuckDB type |179|---|---|180| only numbers, all whole and below 9.007 × 10^15 in magnitude | `BIGINT` |181| only numbers, some fractional or very large | `DOUBLE` |182| only numbers, column listed in `dateColumns`, no time fraction | `DATE` |183| only numbers, column listed in `dateColumns`, some time fraction | `TIMESTAMP` |184| only booleans | `BOOLEAN` |185| any text, or a mix | `VARCHAR` (numbers in General form, `TRUE`/`FALSE`) |186187Source: `Packages/SheetData/Sources/SheetData/Engine/DataEngine+Sheet.swift` (`importRange`).188189- Blank cells, **error values** and lambdas become `NULL`. An error cell is therefore indistinguishable from a blank cell once in the engine.190- Date serials are converted with `DateSerial` honouring `Workbook.date1904` (see [../mathematics/numerical-precision.md](../mathematics/numerical-precision.md)).191- `dateColumns` is supplied by the caller. `FeatureData` derives it with `RangeInspector.dateColumns`: a column is a date column when every number among its first 21 data rows carries a date/time number format (only the first 2,001 columns of the range are inspected).192193### Table to range: `materialize`194195`DataEngine.write(table, into:sheet:at:includeHeader:formatDates:)` is the only path from the engine into cells.1961971. **Size check first.** If `at.row + rows > 1,048,576` or `at.col + cols > 16,384`, it throws `DataError.exceedsSheetLimits` and writes nothing.1982. The write happens inside `engine.transaction("Insert Data")`: one undo step.1993. Each value goes through `CalcEngine.set` **one cell at a time**. There is no bulk write in `SheetCore`; this is the slow door of the system (measured below).2004. Dates and timestamps are written as serial numbers with the number formats `yyyy-mm-dd`, `yyyy-mm-dd hh:mm:ss` or `hh:mm:ss`. Timestamps are written as their UTC wall-clock time; no time-zone conversion is offered. `NULL` becomes a blank cell, a blob becomes the text `[blob N bytes]`, a decimal becomes a number when it parses as a double and text otherwise.201202`materializeQuery` (and `DataWorkspace.materialize(sql:)` in the app) never fetches a whole oversized result: it asks for `room + 1` rows, where `room` is the number of rows that fit below the anchor. If more than `room` rows come back, it runs a `count(*)` to report the real size in the error and writes nothing. A refusal therefore costs at most one sheet's worth of rows.203204The app layer adds `MaterializePlan`: before inserting, the "Insert into Sheet" card shows the row and column counts, an estimated duration (`cells / 850,000` seconds) and marks inserts above 400,000 cells as large.205206### Other limits at the boundary207208| Limit | Value | Where |209|---|---|---|210| Rows fetched by the SQL console into its result grid | 100,000 | `DataStudioModel.swift` (`engine.query(sql, limit: 100_000)`) |211| Rows copied to the clipboard from the dataset grid | 200,000 | `DatasetGrid.swift` (`copyLimit`) |212| Dense pivot matrix | 20,000,000 cells | `PivotEngine.maxCells` |213| Output columns of a transform `pivot` step | 2,000 distinct values | `TransformCompiler` (`maxPivotColumns`) |214| Dataset name | non-empty, no NUL, at most 255 UTF-8 bytes | `DataEngine.validateDatasetName` |215216Source: the files named in the last column, under `Packages/SheetData/Sources/SheetData` and `Packages/FeatureData/Sources/FeatureData`.217218## SQL built from user data219220All SQL text that includes user-provided names or constants is built with `SQL` (`Engine/SQL.swift`):221222- `SQL.ident` double-quotes identifiers and doubles embedded quotes; NUL characters are stripped.223- `SQL.string` and `SQL.literal` produce typed literals (`CAST(… AS BIGINT)`, `DATE '…'`, `from_hex('…')` for blobs). A decimal literal is emitted only if it consists of digits, sign, dot and exponent characters; otherwise it becomes `NULL`.224- Free-form expressions (calculated columns in a pipeline, calculated fields in a pivot) are checked by `SQL.expressionProblem` and then wrapped in parentheses. The scanner rejects: an empty expression, unbalanced `()[]{}`, an unterminated quote, `;`, `--` and `/*` comments, a top-level comma, `E'…'` and `U&'…'` strings, dollar-quoted strings and NUL.225- Caller SQL embedded in `COPY` or `CREATE TABLE AS` must be exactly one statement of type `SELECT` (`requireSingleSelect`, which asks DuckDB to count and classify the statements).226227`expressionProblem` is a lexical guard, not a SQL parser: it guarantees that an expression cannot close the surrounding parenthesis or start another statement. Anything it lets through is still bound and type-checked by DuckDB, inside the sandbox described above.228229## Transforms compiled to SQL230231A `TransformPipeline` is a source plus an ordered list of `TransformStep` values. `TransformCompiler.sql(for:upTo:engine:)` compiles it into **one SQL statement**: a chain of common table expressions `s0, s1, … sN`, where `s0` is the source and `sK` is the output of step K. Cutting the chain after step K gives the preview of that step, which is how the Transform studio shows the data at any point of the pipeline.232233<!-- diagram: data-engine-transform -->234```mermaid235flowchart LR236    SRC["Source<br/>dataset, file or range"] --> S0["CTE s0"]237    S0 --> S1["CTE s1<br/>step 1"]238    S1 --> S2["CTE s2<br/>step 2"]239    S2 --> SN["CTE sN<br/>last step"]240    S1 -.->|DESCRIBE| DIAG["Per-step diagnostics<br/>ok, error, blocked"]241    S2 -.->|DESCRIBE| DIAG242    SN --> OUT["Preview, dataset,<br/>export or worksheet"]243```244SVG fallback: ![Transform pipeline compiled to a chain of CTEs](../assets/diagrams/data-engine-transform.svg)245246The 23 step kinds, one `TransformStep` case each:247248| Group | Steps |249|---|---|250| Rows | `filter`, `sort`, `limit`, `sample`, `removeDuplicates`, `dropMissing` |251| Columns | `renameColumn`, `removeColumns`, `selectColumns`, `reorderColumns`, `changeType`, `calculatedColumn` |252| Text and values | `splitColumn`, `mergeColumns`, `replaceValues`, `parseDate`, `extractText`, `fillMissing` |253| Shape | `groupBy`, `pivot`, `unpivot` |254| Several datasets | `join` (inner, left, right, full, semi, anti), `appendDataset` |255256Source: `Packages/SheetData/Sources/SheetData/Transform/TransformModel.swift` (`enum TransformStep`, `enum JoinKind`).257258Points an engineer should know:259260- **Validation by binding.** After each step the compiler asks DuckDB to `DESCRIBE` the chain so far. An unknown column, an unknown function or a type mismatch is therefore attributed to the step that causes it; the following steps are reported as `blocked`.261- **Row order.** A `sort` step sets an active ordering that later order-sensitive steps honour (`limit`, `fillMissing` previous/next, `removeDuplicates`, the `first`/`last` aggregates) and that is re-applied to the final result. Joins, group-bys, pivots, unpivots, appends and samples reset it.262- **Null semantics follow spreadsheets, not SQL**, for the negative filters: `notEqual`, `notContains` and `notIn` keep `NULL` rows. Join keys that are `NULL` never match.263- `stddev` and `variance` aggregates are the sample statistics (`stddev_samp`, `var_samp`).264- A `pivot` step runs a `DISTINCT` query over the pipeline prefix **at compile time** to make its output columns explicit, and fails above 2,000 distinct values.265- `changeType` and `parseDate` take an error policy: `null` (`TRY_CAST`) or `fail`.266267Sources: a dataset is read directly; a file source is registered as a lazy view (CSV, TSV, Parquet, JSON) or imported (SQLite, DuckDB) under an `orix_src_…` name; a range source is copied with `importRange`. For a range source the Transform studio hops to the main thread (`DispatchQueue.main.sync`) to read the workbook.268269Outputs: `TransformRunner.preview` (a page of any step), `run` (a `DataTable`), `refresh(into:)` (a table dataset), `export` (file) and `materialize` (worksheet, through `materializeQuery`).270271Storage: pipelines are saved as JSON in the workbook attachments `transforms/<id>.json` (`TransformStore`). Attachments are not part of the undo history; a source comment in `TransformStudioModel.swift` notes this explicitly.272273Known gap: the `orix_src_*` relations of file and range pipelines are dropped when the pipeline is deleted from the studio (`TransformStudioModel.delete`), but nothing garbage-collects them otherwise; they disappear with the in-memory engine when the document closes.274275## Pivot tables276277`PivotEngine.compute` runs **one DuckDB query** per pivot:2782791. a filtered projection of the source (include/exclude sets, conditions),2802. optional CTEs for top-N / bottom-N filters,2813. `GROUP BY GROUPING SETS` over every pair (prefix of the row fields, prefix of the column fields), which yields the detail cells, every subtotal level and the grand totals in a single pass,2824. window functions over that grouped result for the percent-of-total show-as modes.283284DuckDB returns only the non-empty groups. Swift then orders the axes and lays the values out in a dense matrix, refused above 20,000,000 cells (`PivotEngine.maxCells`).285286| Capability | Implemented |287|---|---|288| Aggregations | sum, count (non-empty), count numbers, average, min, max, sample and population standard deviation and variance, median, distinct count, product |289| Grouping | date parts (year, quarter, month, day), numeric bins; the same column may be used several times |290| Filters | include/exclude sets, conditions, top/bottom N on a row or column field |291| Show values as | % of grand total, % of row total, % of column total (SQL); running total, difference and % difference from the previous or a named item (computed in Swift on the laid-out matrix) |292| Calculated fields | evaluated after aggregation: a field reference means the **sum** of that field over the cell's records |293| Drill-down | `PivotResult.drillDown(row:col:)` returns SQL plus bound keys selecting the source records of a cell |294| Layouts | tabular and compact |295296Source: `Packages/SheetData/README.md` (section "Pivot engine"), `Packages/SheetData/Sources/SheetData/Pivot/PivotSpec.swift`, `PivotSQL.swift`, `PivotEngine.swift`.297298Deviations from Excel pivot tables that the package documents: values are doubles (min/max of dates or text are not supported); top-N ranks items globally, not within each parent; subtotals are all on or all off; there is no "show values as rank/index". These are listed with the other Excel differences in [../compatibility/excel.md](../compatibility/excel.md).299300A pivot is stored in the worksheet as a `SheetObject` of kind `pivot` whose payload is the `PivotSpec`; its **output is ordinary cells** written by `PivotEngine.write` (same size check and single undo step as `materialize`). The object renderer registered by `DataFeature` only draws a small handle on the top-left cell. A pivot over a worksheet range copies the range once into `orix_pivot_<id>`, kept for drill-down and refresh. Refreshing is explicit ("Refresh All Pivot Tables" or the inspector); a pivot does not recalculate when its source cells change.301302## Measured performance303304The package ships a benchmark tool, `orix-data-bench`. The numbers below are **one run on one machine** as recorded in `Packages/SheetData/BENCHMARKS.md`; they are not guarantees and were not re-run for this document.305306Setup: 2026-11-21, Mac17,6 (Apple M5 Max, 18 cores, 48 GiB), macOS 27.0 (26A5425a), DuckDB v2.0.1, 18 threads, `memory_limit` 24 GiB, release build, 10,000,000 synthetic rows × 8 columns, CSV 559.9 MB, Parquet 108.1 MB, files in the OS page cache.307308| Step | Median (s) | Best (s) |309|---|---:|---:|310| Register CSV as a lazy view | 0.230 | |311| Aggregate straight from the CSV view (`GROUP BY region`) | 0.138 | 0.129 |312| Import CSV into a table (10,000,000 rows) | 0.300 | |313| Filtered aggregation, 20 groups | 0.014 | 0.012 |314| Pivot region × category by year, sum + % of grand total | 0.038 | 0.037 |315| Join with a 100,000-row table + `GROUP BY` (transform pipeline) | 0.088 | 0.065 |316| Preview window, offset 5,000,000, limit 200 | 0.005 | 0.003 |317| Preview window sorted by `amount DESC`, offset 1,000, limit 200 | 0.033 | 0.032 |318| Profile of the 8 columns (+ top 3 values each) | 1.127 | |319| Pivot computed, rendered and written to a sheet (171 × 14 cells) | 0.146 | |320| Materialize 100,000 rows × 8 columns into a sheet (800,008 cells) | 0.925 | |321| Materialize the whole table into a sheet | refused | |322| Parquet, pushdown filter on `id` (10,001 rows matched) | 0.004 | 0.004 |323324Source: `Packages/SheetData/BENCHMARKS.md` (queries run three times, one-off steps once).325326Peak resident memory of that whole benchmark process was 2,941 MiB (1,628 MiB after the import alone). The same file records the spread between runs (for example 0.297 s and 0.364 s for the import in two earlier runs).327328The materialization figure works out to about 0.86 million cells per second through `CalcEngine.set`, against tens of millions of rows per second inside DuckDB. This asymmetry is the reason for the design rule at the top of this document. All benchmark tables of the project are collected in [../development/performance.md](../development/performance.md).329330## Tests331332`Packages/SheetData/Tests` contains 63 `@Test` functions, one of which (`millionRow`, 1,000,000 rows) only runs when `ORIX_DATA_LARGE_TESTS=1`. `Packages/FeatureData/Tests` contains 57 `@Test` functions. These are counts of test functions in the source (`grep` on 2026-11-21); executed-test totals are in `docs/reports/tests.json` when the lead has generated it.333334The reference values are produced without DuckDB: `Tests/SheetDataTests/Generators/generate_fixtures.py` uses pandas, numpy and pyarrow to generate seeded fixtures and `expected.json` (aggregates, profiles, the expected table of each transform scenario, joins, pivot cubes at every prefix level, show-as variants, drill-down row sets). The Swift tests compare cell by cell with a relative tolerance of 1e-9 for tables and 1e-8 for pivots, as stated in the package README. The method is described with the other reference sources in [../mathematics/reference-tests.md](../mathematics/reference-tests.md) and [../development/testing.md](../development/testing.md).335336## Not implemented337338- No persistent database in the app: datasets are re-imported at every open; a large source file that has moved or changed gives a different (or missing) dataset.339- No live link between a lazy view and its file; views are snapshots.340- No Arrow IPC import; no lazy views over SQLite or DuckDB files.341- No remote sources of any kind (HTTP, S3, databases over the network): external access is disabled and no extension can be loaded.342- No bulk cell write; materialization is per cell.343- No automatic refresh of pivots or datasets when source cells change.344- No time-zone conversion for `TIMESTAMP WITH TIME ZONE` on the way to the grid.345- Module-name clash: `SheetCore` exports a struct named `SheetData`, the same name as the module, so `SheetData.DataEngine` cannot be written in code that imports both; unqualified names work.346
Files main
Menu

Theme