Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetAnalytics/Tests/WhatIfTests/SandboxTests.swift10// Module : WhatIfTests11// 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 SheetCore90import AnalyticsTestSupport91@testable import WhatIf9293@Suite struct SandboxTests {94 @Test func modelIsWhatTheTestsAssume() {95 let m = Model()96 #expect(isClose(m.num("B4"), annuity(300_000, 0.06, 360), rel: 1e-13))97 #expect(isClose(m.num("B4"), 1798.6515754582708, rel: 1e-12)) // textbook: 300k, 6 %, 30 y98 #expect(m.num("B7", sheet: 1) == 2000)99 #expect(isClose(m.num("B5", sheet: 2), -1000 + 600 / 1.1 + 600 / 1.21, rel: 1e-13))100 #expect(m.num("B6", sheet: 2) == 4000)101 #expect(m.num("E2", sheet: 2) == 4)102 }103104 @Test func cloneIsDeepAndFaithful() {105 let m = Model()106 m.wb.sheets[1].meta.tables.append(TableDef(id: 1, name: "T", range: RangeRef(a1: "A1:B7")!, columns: [TableColumn(name: "a"), TableColumn(name: "b")]))107 m.wb.attachments["x"] = Data([1, 2, 3])108 let copy = WorkbookCloner.clone(m.wb)109 let ce = CalcEngine(workbook: copy, trustCachedValues: true)110 #expect(snapshot(ce) == snapshot(m.engine))111 #expect(copy.sheets.map(\.id) == m.wb.sheets.map(\.id) && copy.sheets.map(\.name) == ["Loan", "Profit", "DCF"])112 #expect(copy.definedName("Disc")?.formula == "DCF!$B$1" && copy.sheets[1].meta.tables.first?.name == "T" && copy.attachments["x"] == Data([1, 2, 3]))113 #expect(ce.pendingCount == 0)114 // formula objects are not shared115 #expect(copy.sheets[0].cell(3, 1)!.formula !== m.wb.sheets[0].cell(3, 1)!.formula)116 // editing the clone recalculates there (names, cross-sheet references and spills included) and leaves the original alone117 let before = snapshot(m.engine)118 ce.set(sheet: 2, "B1", .value(.number(0.2)))119 ce.set(sheet: 1, "B1", .value(.number(30)))120 ce.set(sheet: 2, "D1", .formula(try! FormulaParser.parse("={5,6;7,8}")))121 ce.recalculate()122 #expect(isClose(copy.sheets[2].value(4, 1).numberValue!, -1000 + 600 / 1.2 + 600 / 1.44, rel: 1e-13))123 #expect(copy.sheets[2].value(5, 1) == .number(14000) && copy.sheets[2].value(1, 4) == .number(8))124 #expect(snapshot(m.engine) == before)125 // a full recalculation of the clone from scratch gives the same values as the cached ones126 let fresh = CalcEngine(workbook: WorkbookCloner.clone(m.wb))127 #expect(snapshot(fresh) == before)128 }129130 @Test func sandboxRestoresInBothModes() throws {131 for isolation in WhatIfIsolation.allCases {132 let m = Model()133 m.seedHistory()134 let profit = try expectUntouched(m, isolation, "edit") {135 try ModelSandbox.run(on: m.engine, isolation: isolation) { box -> Double in136 try box.set(m.cell("B1", sheet: 1), number: 30)137 try box.set(m.cell("B7", sheet: 1), .value(.text("overwritten formula")))138 #expect(try box.value(m.cell("B7", sheet: 1)) == .text("overwritten formula"))139 box.reset(m.cell("B7", sheet: 1))140 #expect(try box.isFormula(m.cell("B7", sheet: 1)))141 try box.set(m.cell("H9", sheet: 1), number: 1) // a cell that did not exist142 return try box.number(m.cell("B7", sheet: 1))143 }144 }145 #expect(profit == 30 * 1000 - 15 * 1000 - 8000)146 // a throwing body leaves no trace either147 struct Boom: Error {}148 #expect(throws: Boom.self) {149 try expectUntouched(m, isolation, "throwing body") {150 try ModelSandbox.run(on: m.engine, isolation: isolation) { box in151 try box.set(m.cell("B3"), number: 1)152 try box.set(m.cell("B4"), .value(.number(99)))153 throw Boom()154 }155 }156 }157 // read-only use changes nothing at all, in either mode158 let v = try expectUntouched(m, isolation, edits: false, "read only") {159 try ModelSandbox.run(on: m.engine, isolation: isolation) { try $0.number(m.cell("B4")) }160 }161 #expect(isClose(v, annuity(300_000, 0.06, 360), rel: 1e-13))162 #expect(throws: WhatIfError.self) { try ModelSandbox.run(on: m.engine, isolation: isolation) { try $0.set(ModelCell(sheet: 9, row: 0, col: 0), number: 1) } }163 }164 }165166 @Test func cloneOfAModelWithPendingWorkStillCalculates() throws {167 let m = Model()168 m.engine.set(sheet: 1, "B2", .value(.number(2000))) // not recalculated yet169 #expect(m.engine.pendingCount > 0)170 let v = try ModelSandbox.run(on: m.engine) { try $0.number(m.cell("B7", sheet: 1)) }171 #expect(v == 25 * 2000 - 15 * 2000 - 8000)172 }173174 @Test func modelCellBasics() {175 let m = Model()176 #expect(ModelCell(a1: "B3") == ModelCell(sheet: 0, row: 2, col: 1))177 #expect(ModelCell(a1: "not a cell") == nil)178 #expect(m.cell("B7", sheet: 1).label(in: m.wb) == "Profit!B7")179 #expect(ModelCell(sheet: 7, row: 0, col: 0).label(in: m.wb) == "#REF!A1")180 _ = m.engine.renameSheet(at: 1, to: "P & L")181 #expect(m.cell("B7", sheet: 1).label(in: m.wb) == "'P & L'!B7")182 }183}184