SPB Git

Repository Public

SheetBridgeTests.swift 19.7 KB 330 lines swift

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
Files main
Menu

Theme