Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetData/Tests/SheetDataTests/SheetBridgeTests.swift10// Module : SheetDataTests11// Repository : https://git.spboucher.ai/orix-sheet12// Created : 2026-11-2113// Last Modified : 2026-11-2114//15// =============================================================================16// AUTHOR & PROFESSIONAL AFFILIATION17// =============================================================================18//19// Author : Simon-Pierre Boucher20// Position : Professor of Finance21// Institution : Université du Québec en Outaouais (UQO)22// Department : Département des sciences administratives23// Organization : SP Boucher24//25// Website : https://spboucher.ai26// Git : https://git.spboucher.ai27//28// =============================================================================29// OFFICIAL CONTACT DIRECTORY30// =============================================================================31//32// General : info@spboucher.ai33// Professional : contact@spboucher.ai34// Direct : simon@spboucher.ai35// Research : research@spboucher.ai36// Development : dev@spboucher.ai37// Support : support@spboucher.ai38// Security/Admin : admin@spboucher.ai39// Notifications : notifications@spboucher.ai40//41// Preferred Contact: support@spboucher.ai42//43// =============================================================================44// COPYRIGHT & INTELLECTUAL PROPERTY45// =============================================================================46//47// Copyright (c) 202648// Simon-Pierre Boucher49//50// All rights reserved where applicable.51//52// This file is governed by the applicable project license,53// copyright notices, institutional agreements and relevant law.54//55// Unless expressly authorized by the applicable license or56// rights holder, copying, redistribution, sublicensing,57// publication and commercial exploitation are not permitted.58//59// Existing third-party rights and notices remain unaffected.60//61// =============================================================================62// CONFIDENTIALITY & SECURITY63// =============================================================================64//65// Classification : Public66// Distribution : Public repository; use is governed by the project license67// Security Contact : admin@spboucher.ai68//69// Access to this source does not independently grant permission70// to redistribute or disclose confidential materials.71//72// Do not embed credentials, private keys, access tokens,73// certificates or confidential personal information.74//75// =============================================================================76// LICENSE & MAINTENANCE77// =============================================================================78//79// License : None specified (no license file in this repository)80// Maintainer : Simon-Pierre Boucher81// Technical Contact: dev@spboucher.ai82//83// =============================================================================84// END OF SP BOUCHER SOURCE HEADER85// =============================================================================8687import Foundation88import Testing89import SheetCore90@testable import SheetData9192/// The two doors between the worksheet grid and the analytical engine.93@Suite struct SheetBridgeTests {94 private func workbook() -> (Workbook, CalcEngine) {95 let wb = Workbook(sheetNames: ["Data", "Out"])96 return (wb, CalcEngine(workbook: wb))97 }9899 private func fill(_ calc: CalcEngine, sheet: Int = 0, at: CellRef = CellRef(0, 0), _ rows: [[Value]]) {100 for (r, row) in rows.enumerated() {101 for (c, v) in row.enumerated() where !v.isBlank { calc.set(sheet: sheet, row: at.row + r, col: at.col + c, .value(v)) }102 }103 }104105 @Test func rangeImportInfersTypes() throws {106 let (wb, calc) = workbook()107 let d1 = DateSerial.serial(year: 2024, month: 2, day: 29), d2 = DateSerial.serial(year: 1999, month: 12, day: 31)108 fill(calc, [109 [.text("Name"), .text("Qty"), .text("Price"), .text("Active"), .text("When"), .text("Stamp"), .text("Mixed"), .blank, .text("Qty")],110 [.text("Ann"), .number(3), .number(1.5), .bool(true), .number(d1), .number(d1 + 0.75), .number(7), .blank, .number(1)],111 [.text("Bob"), .blank, .number(2), .bool(false), .number(d2), .number(d2), .text("x"), .blank, .blank],112 [.text("Ćiro \"Q\""), .number(-4), .error(.div0), .blank, .blank, .blank, .bool(true), .blank, .number(2.5)],113 ])114 _ = calc.setFormula(sheet: 0, row: 4, col: 1, "=SUM(B2:B4)*2") // formulas contribute their computed value115 calc.set(sheet: 0, row: 4, col: 0, .value(.text("Total")))116 _ = calc.recalculate()117 let e = try DataEngine()118 let info = try e.importRange(wb, sheet: 0, range: RangeRef(a1: "A:I")!, as: "r", hasHeader: true, dateColumns: [4, 5])119 #expect(info.rowCount == 4) // whole-column reference clipped to the used rows120 #expect(info.columns.map(\.name) == ["Name", "Qty", "Price", "Active", "When", "Stamp", "Mixed", "Column8", "Qty_2"])121 #expect(info.columns.map(\.declaredType) == ["VARCHAR", "BIGINT", "DOUBLE", "BOOLEAN", "DATE", "TIMESTAMP", "VARCHAR", "VARCHAR", "DOUBLE"])122 let rows = try e.query("SELECT * FROM r").rows123 let day: Int64 = 86_400_000_000124 let stamp1 = DataValue.timestamp(Int64(DateSerial.daysFromCivil(2024, 2, 29)) * day + 64_800_000_000)125 let stamp2 = DataValue.timestamp(Int64(DateSerial.daysFromCivil(1999, 12, 31)) * day)126 let row0: [DataValue] = [.string("Ann"), .int(3), .double(1.5), .bool(true), .date(year: 2024, month: 2, day: 29), stamp1, .string("7"), .null, .double(1)]127 let row1: [DataValue] = [.string("Bob"), .null, .double(2), .bool(false), .date(year: 1999, month: 12, day: 31), stamp2, .string("x"), .null, .null]128 let row2: [DataValue] = [.string("Ćiro \"Q\""), .int(-4), .null, .null, .null, .null, .string("TRUE"), .null, .double(2.5)] // error cell → NULL129 #expect(rows[0] == row0)130 #expect(rows[1] == row1)131 #expect(rows[2] == row2)132 #expect(rows[3][0] == "Total" && rows[3][1] == -2)133 // no header: column letters, sub-range only134 let part = try e.importRange(wb, sheet: 0, range: RangeRef(a1: "B2:C3")!, as: "part", hasHeader: false)135 #expect(part.columns.map(\.name) == ["B", "C"] && part.rowCount == 2)136 let partSums: [Double?] = try e.query("SELECT sum(B), sum(C) FROM part").row(0).map { $0.doubleValue }137 #expect(partSums == [3.0, 3.5])138 // an empty range gives an empty table with one text column per requested column139 let empty = try e.importRange(wb, sheet: 1, range: RangeRef(a1: "A1:C10")!, as: "empty", hasHeader: true)140 #expect(empty.rowCount == 0 && empty.columns.count == 1)141 #expect(throws: DataError.self) { _ = try e.importRange(wb, sheet: 9, range: RangeRef(a1: "A1")!, as: "x", hasHeader: false) }142 #expect(try e.dataset("r").sourceURL == nil)143 }144145 @Test func materializeWritesOneUndoableTransaction() throws {146 let (wb, calc) = workbook()147 let e = try DataEngine()148 let table = try e.query("""149 SELECT * FROM (VALUES (1, 'a', 1.5, true, DATE '2024-02-29', TIMESTAMP '2024-02-29 18:00:00', TIME '06:00:00', 12.50::DECIMAL(9,2)),150 (2, NULL, NULL, false, NULL, NULL, NULL, NULL)) t(i, s, d, b, dt, ts, tm, dec) ORDER BY i151 """)152 calc.set(sheet: 1, row: 2, col: 1, .value(.text("old")))153 let undoDepthBefore = calc.canUndo154 let range = try e.materialize(table, into: calc, sheet: 1, at: CellRef(a1: "B2")!, includeHeader: true)155 #expect(range.a1 == "B2:I4")156 let s = wb.sheets[1]157 #expect((0..<8).map { s.value(1, 1 + $0) } == ["i", "s", "d", "b", "dt", "ts", "tm", "dec"].map(Value.text))158 let serial = DateSerial.serial(year: 2024, month: 2, day: 29)159 let line1: [Value] = [.number(1), .text("a"), .number(1.5), .bool(true), .number(serial), .number(serial + 0.75), .number(0.25), .number(12.5)]160 let line2: [Value] = [.number(2), .blank, .blank, .bool(false), .blank, .blank, .blank, .blank]161 #expect((0..<8).map { s.value(2, 1 + $0) } == line1)162 #expect((0..<8).map { s.value(3, 1 + $0) } == line2)163 #expect(wb.styles[s.styleID(2, 5)].numberFormat == "yyyy-mm-dd")164 #expect(wb.styles[s.styleID(2, 6)].numberFormat == "yyyy-mm-dd hh:mm:ss")165 #expect(wb.styles[s.styleID(2, 7)].numberFormat == "hh:mm:ss")166 // one undo step restores everything, including the overwritten cell167 #expect(undoDepthBefore && calc.undoLabel == "Insert Data")168 calc.undo()169 #expect(s.value(2, 1) == .text("old") && s.value(1, 1) == .blank && s.value(3, 4) == .blank)170 calc.redo()171 #expect(s.value(3, 4) == .bool(false))172 // without header173 let r2 = try e.materialize(table, into: calc, sheet: 0, at: CellRef(0, 0), includeHeader: false, formatDates: false)174 #expect(r2.a1 == "A1:H2" && wb.sheets[0].value(0, 1) == .text("a"))175 }176177 @Test func sheetLimitsAreEnforcedAndReported() throws {178 let (wb, calc) = workbook()179 let e = try DataEngine()180 let small = try e.query("SELECT i, i * 2 AS d FROM range(10) t(i)")181 // rows: 10 data rows + header do not fit when starting 10 rows from the bottom182 do {183 _ = try e.materialize(small, into: calc, sheet: 0, at: CellRef(Limits.maxRows - 10, 0), includeHeader: true)184 Issue.record("expected a sheet-limit error")185 } catch let DataError.exceedsSheetLimits(rows, cols, at) {186 #expect(rows == 11 && cols == 2 && at.row == Limits.maxRows - 10)187 }188 #expect(try e.materialize(small, into: calc, sheet: 0, at: CellRef(Limits.maxRows - 10, 0), includeHeader: false).r1 == Limits.maxRows - 1)189 // columns190 #expect(throws: DataError.self) { _ = try e.materialize(small, into: calc, sheet: 0, at: CellRef(0, Limits.maxCols - 1), includeHeader: false) }191 // a query larger than a sheet: nothing is written, the error carries the real size, and only limit+1 rows were fetched192 calc.clearUndoHistory()193 do {194 _ = try e.materializeQuery("SELECT i FROM range(3000000) t(i)", into: calc, sheet: 1, at: CellRef(0, 0), includeHeader: true)195 Issue.record("expected a sheet-limit error")196 } catch let error as DataError {197 guard case .exceedsSheetLimits(let rows, let cols, _) = error else { Issue.record("wrong error \(error)"); return }198 #expect(rows == 3_000_001 && cols == 1)199 #expect(error.description.contains("1048576") && error.description.contains("16384"))200 }201 #expect(wb.sheets[1].cells.count == 0 && !calc.canUndo)202 // too many columns for a sheet203 let wideSQL = "SELECT " + (0..<(Limits.maxCols + 1)).map { "\($0) AS c\($0)" }.joined(separator: ", ")204 #expect(throws: DataError.self) { _ = try e.materializeQuery(wideSQL, into: calc, sheet: 1, at: CellRef(0, 0), includeHeader: false) }205 // an empty result has nothing to write206 #expect(throws: DataError.self) { _ = try e.materializeQuery("SELECT 1 WHERE false", into: calc, sheet: 1, at: CellRef(0, 0), includeHeader: false) }207 #expect(try e.materializeQuery("SELECT 1 AS x WHERE false", into: calc, sheet: 1, at: CellRef(0, 0), includeHeader: true).a1 == "A1")208 }209210 @Test func roundTripRangeTableRange() throws {211 let (wb, calc) = workbook()212 let serial = DateSerial.serial(year: 2025, month: 7, day: 4)213 var rows: [[Value]] = [[.text("id"), .text("label"), .text("amount"), .text("flag"), .text("day"), .text("ratio")]]214 for i in 0..<500 {215 rows.append([.number(Double(i)), i % 7 == 0 ? .blank : .text("item \(i) é\"'"), .number(Double(i) * 1.25 - 100), .bool(i % 2 == 0),216 .number(serial + Double(i)), i % 5 == 0 ? .blank : .number(1.0 / Double(i + 3))])217 }218 fill(calc, rows)219 let e = try DataEngine()220 let source = RangeRef(r0: 0, c0: 0, r1: 500, c1: 5)221 try e.importRange(wb, sheet: 0, range: source, as: "rt", hasHeader: true, dateColumns: [4])222 #expect(try e.rowCount(of: "rt") == 500)223 let out = try e.materializeQuery("SELECT * FROM rt", into: calc, sheet: 1, at: CellRef(0, 0), includeHeader: true)224 #expect(out == source)225 for r in 0...500 { for c in 0...5 {226 let a = wb.sheets[0].value(r, c), b = wb.sheets[1].value(r, c)227 if a != b { Issue.record("round trip differs at \(CellRef(r, c).a1): \(a) vs \(b)"); return }228 } }229 // and through a query: the engine computes what the grid would230 let agg = try e.query("SELECT sum(amount), count(label), count(*) FILTER (WHERE flag), min(day), avg(ratio) FROM rt").row(0)231 var wantSum = 0.0232 for i in 0..<500 { wantSum += Double(i) * 1.25 - 100 }233 #expect(agg[0].doubleValue == wantSum)234 #expect(agg[1].doubleValue == Double(500 - 72))235 #expect(agg[2].doubleValue == 250)236 #expect(agg[3] == .date(year: 2025, month: 7, day: 4))237 // 1904 date system238 wb.date1904 = true239 try e.importRange(wb, sheet: 0, range: RangeRef(a1: "E1:E3")!, as: "d1904", hasHeader: true, dateColumns: [0])240 let first = try e.query("SELECT day FROM d1904 LIMIT 1").value(row: 0, column: 0)241 let c = DateSerial.components(serial, date1904: true)242 #expect(first == .date(year: c.year, month: c.month, day: c.day))243 #expect(DataEngine.cellValue(first, date1904: true) == .number(serial))244 }245246 @Test func pivotFromWorksheetRangeAndBack() throws {247 let (wb, calc) = workbook()248 fill(calc, [249 [.text("Region"), .text("Item"), .text("Amount")],250 [.text("N"), .text("a"), .number(1)], [.text("N"), .text("b"), .number(4)], [.text("S"), .text("a"), .number(8)],251 [.text("S"), .text("a"), .number(2)], [.text("S"), .text("c"), .number(16)],252 ])253 let e = try DataEngine()254 let spec = PivotSpec(source: .range(sheetID: wb.sheets[0].id, range: RangeRef(a1: "A1:C6")!, hasHeader: true, dateColumns: []),255 rows: [PivotField("Region")], columns: [PivotField("Item")], values: [PivotValue("Amount", .sum)])256 let r = try PivotEngine.compute(spec, workbook: wb, data: e)257 let grid = PivotEngine.renderGrid(r, spec: spec)258 let n = Value.number, t = Value.text259 #expect(grid.cells == [260 [t("Region"), t("a"), t("b"), t("c"), t("Grand Total")],261 [t("N"), n(1), n(4), .blank, n(5)],262 [t("S"), n(10), .blank, n(16), n(26)],263 [t("Grand Total"), n(11), n(4), n(16), n(31)],264 ])265 // the range was loaded once, into one engine table, kept for drill-down266 #expect(try e.datasets(includeInternal: true).map(\.name) == [spec.sourceRelationName])267 #expect(try e.datasets().isEmpty) // working relations are hidden from the user's dataset list268 let drilled = try PivotEngine.records(try r.drillDown(row: 1, col: 0), data: e)269 let drilledRows: [[DataValue]] = [[.string("S"), .string("a"), .int(8)], [.string("S"), .string("a"), .int(2)]]270 #expect(drilled.rows == drilledRows)271 // write it, then refresh after the data changes272 let range = try PivotEngine.write(grid, into: calc, sheet: 1, at: CellRef(a1: "B2")!)273 #expect(range.a1 == "B2:F5" && wb.sheets[1].value(3, 2) == .number(10))274 #expect(wb.styles[wb.sheets[1].styleID(1, 1)].bold && wb.styles[wb.sheets[1].styleID(4, 3)].bold && !wb.styles[wb.sheets[1].styleID(2, 2)].bold)275 #expect(calc.undoLabel == "Pivot Table")276 calc.set(sheet: 0, row: 1, col: 2, .value(.number(100)))277 let refreshed = try PivotEngine.compute(spec, workbook: wb, data: e)278 #expect(refreshed.value(row: 0, column: 0) == 100)279 #expect(throws: DataError.self) { _ = try PivotEngine.write(grid, into: calc, sheet: 1, at: CellRef(Limits.maxRows - 2, 0)) }280 #expect(throws: DataError.self) { _ = try PivotEngine.compute(spec, workbook: nil, data: e) } // a range source needs its workbook281 PivotEngine.releaseSource(spec, data: e)282 #expect(try e.datasets(includeInternal: true).isEmpty)283 }284285 @Test func transformFromRangeToRange() throws {286 let (wb, calc) = workbook()287 fill(calc, [[.text("k"), .text("v")], [.text("a"), .number(1)], [.text("b"), .number(2)], [.text("a"), .number(3)]])288 let e = try DataEngine()289 let p = TransformPipeline(name: "range", source: .range(sheetID: wb.sheets[0].id, range: RangeRef(a1: "A1:B4")!, hasHeader: true, dateColumns: []), steps: [290 .groupBy(keys: ["k"], aggregations: [Aggregation(.sum, "v", as: "total")]), .sort([SortSpec("k")]),291 ])292 let out = try TransformRunner.materialize(p, engine: e, into: calc, sheet: 1, at: CellRef(0, 0))293 #expect(out.a1 == "A1:B3")294 let written: [Value] = [wb.sheets[1].value(1, 0), wb.sheets[1].value(1, 1), wb.sheets[1].value(2, 1)]295 #expect(written == [Value.text("a"), Value.number(4), Value.number(2)])296 // refresh re-reads the worksheet297 calc.set(sheet: 0, row: 4, col: 0, .value(.text("b")))298 calc.set(sheet: 0, row: 4, col: 1, .value(.number(40)))299 var grown = p300 grown.source = .range(sheetID: wb.sheets[0].id, range: RangeRef(a1: "A1:B5")!, hasHeader: true, dateColumns: [])301 try TransformRunner.loadSource(grown, engine: e, workbook: wb)302 let grownRows: [[DataValue]] = [[.string("a"), .int(4)], [.string("b"), .int(42)]]303 #expect(try TransformRunner.run(grown, engine: e).rows == grownRows)304 #expect(throws: DataError.self) { try TransformRunner.loadSource(grown, engine: e, workbook: nil) }305 }306307 /// Medium-scale check, off by default: `ORIX_DATA_LARGE_TESTS=1 swift test --filter millionRow`.308 @Test(.enabled(if: ProcessInfo.processInfo.environment["ORIX_DATA_LARGE_TESTS"] == "1"))309 func millionRowPipeline() throws {310 let e = try DataEngine()311 let dir = Fixtures.temporaryDirectory()312 defer { try? FileManager.default.removeItem(at: dir) }313 try e.execute("CREATE TABLE gen AS SELECT i AS id, 'c' || (i % 100) AS cat, DATE '2024-01-01' + CAST(i % 365 AS INTEGER) AS d, (i % 1000) / 10.0 AS amount FROM range(1000000) t(i)")314 let csv = dir.appendingPathComponent("gen.csv")315 #expect(try e.exportDataset("gen", to: csv, format: .csv) == 1_000_000)316 let info = try e.importFile(csv, as: "big")317 #expect(info.rowCount == 1_000_000)318 let spec = PivotSpec(source: .dataset("big"), rows: [PivotField("cat")], columns: [PivotField("d", grouping: .datePart(.month))], values: [PivotValue("amount", .sum)])319 let r = try PivotEngine.compute(spec, workbook: nil, data: e)320 #expect(r.rowItems.count == 101 && r.columnItems.count == 13)321 var wantTotal = 0.0322 for i in 0..<1000 { wantTotal += Double(i) / 10 }323 #expect(close(r.value(row: 100, column: 12)!, wantTotal * 1000))324 // a million rows do not fit under a header: the grid refuses, the engine keeps the data325 let (_, calc) = workbook()326 #expect(throws: DataError.self) { _ = try e.materializeQuery("SELECT * FROM big", into: calc, sheet: 0, at: CellRef(48_577, 0), includeHeader: true) }327 #expect(try e.preview("big", offset: 999_990, limit: 100).rowCount == 10)328 }329}330