SPB Git

Repository Public

SandboxTests.swift 8.6 KB 184 lines swift

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

Theme