SPB Git

Repository Public

ValidatorTests.swift 10.2 KB 234 lines swift

1// =============================================================================2//                  SP BOUCHER | SOFTWARE & RESEARCH3//             ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project          : ORIX Sheet9// File             : Packages/SheetIntelligence/Tests/SheetIntelligenceTests/ValidatorTests.swift10// Module           : SheetIntelligenceTests11// 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 SheetIntelligence9192@Suite struct ValidatorTests {93    let wb: Workbook94    let cell = CellRef(a1: "H2")!95    init() { wb = Fixtures.sales().workbook }9697    func check(_ text: String, at: String = "H2") -> FormulaValidation {98        FormulaValidator.validate(text, workbook: wb, sheet: 0, at: CellRef(a1: at)!)99    }100101    @Test func validFormulaIsEvaluatedByTheEngine() {102        let v = check("=SUM(C2:C21)")103        let expected = (0..<20).reduce(0.0) { $0 + Double(Fixtures.units($1)) }104        #expect(v.isValid)105        #expect(v.formula == "=SUM(C2:C21)")106        #expect(v.value == .number(expected))107        #expect(v.preview == Value.number(expected).description)108        #expect(v.warnings.isEmpty)109    }110111    @Test func conditionalAggregatesWork() {112        #expect(check("=SUMIF(A2:A21,\"North\",E2:E21)").value == .number((0..<20).filter { $0 % 4 == 0 }.reduce(0.0) { $0 + Fixtures.revenue($1) }))113        #expect(check("=COUNTIF(B2:B21,\"Gizmo\")").value == .number(6))114        #expect(check("=E2*TaxRate").value == .number(Fixtures.revenue(0) * 0.15))115        #expect(check("=Params!B1").value == .number(0.15))116    }117118    @Test func markdownFencesAndProseAreStripped() {119        #expect(check("```excel\n=SUM(C2:C21)\n```").formula == "=SUM(C2:C21)")120        #expect(check("Here is the formula:\n\n`=MAX(E2:E21)`\n\nIt returns the largest revenue.").formula == "=MAX(E2:E21)")121        #expect(check("Formula: =MIN(D2:D21).").formula == "=MIN(D2:D21)")122        #expect(check("**=AVERAGE(C2:C21)**").formula == "=AVERAGE(C2:C21)")123        #expect(check("SUM(C2:C21)").formula == "=SUM(C2:C21)")124        #expect(check("=IF(C2>10,\u{201C}high\u{201D},\u{201C}low\u{201D})").value == .text("low"))125        #expect(check("  =sum(c2:c21)  ").formula == "=SUM(C2:C21)")126    }127128    @Test func localisedSeparatorsAreConverted() {129        let v = check("=IF(C2>10;1,5;2,5)")130        #expect(v.isValid)131        #expect(v.formula == "=IF(C2>10,1.5,2.5)")132        #expect(v.value == .number(2.5))133        #expect(v.wasNormalised)134        #expect(v.warnings.contains { $0.contains("Separators") })135    }136137    @Test func syntaxErrorsAreRejected() {138        for bad in ["=AVERAGEIF(B2:B20, <>0)", "=SUM(C2:C21", "=C2 +* D2", "=IF(C2>10,\"a\",)(", "=\"unterminated", "=SUM(C2:C21))"] {139            let v = check(bad)140            #expect(!v.isValid, "\(bad)")141            #expect(v.failedStage == .parse, "\(bad)")142            #expect(v.value == nil)143        }144    }145146    @Test func unknownFunctionsAreRejected() {147        let v = check("=TOTALIZE(C2:C21)+SUMX(E2:E21)")148        #expect(!v.isValid)149        #expect(v.failedStage == .functions)150        #expect(v.reason?.contains("TOTALIZE") == true && v.reason?.contains("SUMX") == true)151        #expect(v.formula == "=TOTALIZE(C2:C21)+SUMX(E2:E21)")152    }153154    @Test func wrongArgumentCountIsRejected() {155        let v = check("=SUMIF(A2:A21)")156        #expect(!v.isValid && v.failedStage == .functions)157        #expect(v.reason?.contains("SUMIF") == true)158        #expect(check("=IF(C2>1)").failedStage == .functions)159    }160161    @Test func referencesMustResolve() {162        #expect(check("=SUM(Budget!A1:A9)").failedStage == .references)163        #expect(check("=SUM(Budget!A1:A9)").reason?.contains("Budget") == true)164        #expect(check("=E2*VatRate").failedStage == .references)165        #expect(check("=IF(C2>10,Yes,No)").failedStage == .references)166        #expect(check("=SUM(Orders[Amount])").failedStage == .references)167        #expect(check("=SUM([@Units])").failedStage == .references)168    }169170    @Test func selfReferenceIsRejected() {171        let v = check("=SUM(H1:H9)")172        #expect(!v.isValid && v.failedStage == .references)173        #expect(v.reason?.contains("circular") == true)174        #expect(check("=SUM(C:C)", at: "C30").failedStage == .references)175    }176177    @Test func letAndLambdaNamesAreNotUnknown() {178        let v = check("=LET(total,SUM(C2:C21),n,COUNT(C2:C21),total/n)")179        #expect(v.isValid)180        #expect(v.value == check("=AVERAGE(C2:C21)").value)181    }182183    @Test func errorResultsAndEmptyReferencesAreFlagged() {184        let div = check("=C2/Z99")185        #expect(div.isValid)186        #expect(div.evaluatesToError)187        #expect(div.warnings.contains { $0.contains("#DIV/0!") })188        #expect(div.warnings.contains { $0.contains("Z99") })189        #expect(check("=SUM(K2:K21)").warnings.contains { $0.contains("K2:K21") })190    }191192    @Test func garbageAndHostileOutputIsRejected() {193        let hostile = [194            "", "   ", "I cannot help with that.", "DROP TABLE users; --", "=", "==SUM(1)", "<script>alert(1)</script>",195            "=SUM(C2:C21); os.system('rm -rf /')", "=HYPERLINK(\"http://evil.example\",\"x\")&WEBSERVICE(\"http://evil.example\")",196            "=CALL(\"kernel32\",\"x\")", "=SEQUENCE(1048576,16384)", "=REPT(\"x\",99999999)", String(repeating: "=SUM(", count: 400),197            "=" + String(repeating: "1+", count: 600) + "1", String(repeating: "A", count: 9000), "=SUM(\u{0000})", "=1\n=2\nrm -rf", "{\"formula\": \"=SUM(C2:C21)\"}",198            "=INDIRECT(\"'[other.xlsx]S'!A1\")+EXEC(\"calc\")",199        ]200        for text in hostile {201            let v = check(text)202            if text == "=1\n=2\nrm -rf" { #expect(v.formula == "=1"); continue }   // first formula line only; the rest is never used203            #expect(!v.isValid, "should be rejected: \(text.prefix(40))")204            #expect(v.value == nil)205        }206    }207208    @Test func validationNeverTouchesTheLiveWorkbook() {209        let (wb, engine) = Fixtures.sales()210        let before = engine.changeCount211        var cells = 0212        wb.sheets[0].cells.forEachAll { _, _, _ in cells += 1 }213        _ = FormulaValidator.validate("=SUM(C2:C21)*2", workbook: wb, sheet: 0, at: CellRef(a1: "H2")!)214        var after = 0215        wb.sheets[0].cells.forEachAll { _, _, _ in after += 1 }216        #expect(engine.changeCount == before)217        #expect(cells == after)218        #expect(wb.sheets[0].cell(1, 7) == nil)219        #expect(!engine.canUndo || engine.changeCount == before)220    }221222    @Test func invalidTargetIsRejected() {223        #expect(!FormulaValidator.validate("=1", workbook: wb, sheet: 9, at: cell).isValid)224        #expect(!FormulaValidator.validate("=1", workbook: wb, sheet: 0, at: CellRef(-1, 0)).isValid)225    }226227    @Test func previewDescribesArraysAndText() {228        #expect(FormulaValidator.preview(.text("abc")) == "\"abc\"")229        #expect(FormulaValidator.preview(.blank) == "(empty)")230        #expect(FormulaValidator.preview(.array(ArrayValue(rows: 2, cols: 1, items: [.number(4), .number(5)]))) == "array 2×1 starting with 4")231        #expect(FormulaValidator.preview(.error(.div0)) == "#DIV/0!")232    }233}234
Files main
Menu

Theme