SPB Git

Repository Public

RulesValidatorTests.swift 19.7 KB 355 lines swift

1// =============================================================================2//                  SP BOUCHER | SOFTWARE & RESEARCH3//             ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project          : ORIX Sheet9// File             : Packages/SheetAnalytics/Tests/AuditTests/RulesValidatorTests.swift10// Module           : AuditTests11// 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 Audit9192@Suite struct ValidatorTests {93    func book(_ rules: [DataValidation]) -> (RulesTestBook, Validator) {94        let b = RulesTestBook()95        b.engine.editMeta(sheet: 0) { $0.validations = rules }96        return (b, Validator(engine: b.engine))97    }98    func dv(_ range: String, _ kind: DataValidation.Kind, _ op: String = "between", _ f1: String? = nil, _ f2: String? = nil) -> DataValidation {99        DataValidation(ranges: [RangeRef(a1: range)!], kind: kind, op: op, formula1: f1, formula2: f2)100    }101    func ok(_ v: Validator, _ value: Value, _ a1: String = "A1") -> Bool {102        let r = CellRef(a1: a1)!103        return v.validate(value, sheet: 0, row: r.row, col: r.col).isValid104    }105106    @Test func wholeNumberBetween() {107        let (_, v) = book([dv("A1:A5", .whole, "between", "1", "10")])108        for good in [1.0, 5, 10] { #expect(ok(v, .number(good))) }109        for bad: Value in [.number(0), .number(11), .number(5.5), .text("abc"), .text("5"), .bool(true), .error(.na)] { #expect(!ok(v, bad), "\(bad)") }110        let o = v.validate(.number(11), sheet: 0, row: 0, col: 0)111        #expect(o.style == .stop && !o.canOverride)112        #expect(o.reason == "Enter a whole number between 1 and 10.")113        #expect(o.message == o.reason)114        #expect(o.title == "Invalid entry")115        #expect(v.validate(.number(2.5), sheet: 0, row: 0, col: 0).reason == "Enter a whole number.")116        #expect(v.validate(.error(.div0), sheet: 0, row: 0, col: 0).reason == "The value is an error (#DIV/0!).")117        // outside the rule's range, or on another sheet index, anything goes118        #expect(ok(v, .text("anything"), "B1"))119        #expect(ok(v, .text("anything"), "A6"))120        #expect(v.validate(.text("x"), sheet: 9, row: 0, col: 0).isValid)121        #expect(v.rule(sheet: 0, row: 0, col: 0)?.kind == .whole)122        #expect(v.rule(sheet: 0, row: 9, col: 0) == nil)123    }124125    @Test func everyOperatorOnDecimals() {126        // (op, formula1, formula2, accepted, rejected)127        let cases: [(String, String, String?, [Double], [Double])] = [128            ("between", "2", "5", [2, 3.5, 5], [1.99, 5.01]),129            ("between", "5", "2", [2, 3.5, 5], [1.99, 5.01]),130            ("notBetween", "2", "5", [1.99, 5.01, -3], [2, 3.5, 5]),131            ("equal", "5", nil, [5], [4.999, 6]),132            ("notEqual", "5", nil, [4.999, 6], [5]),133            ("greaterThan", "5", nil, [5.001, 100], [5, 0]),134            ("lessThan", "5", nil, [4.999, -1], [5, 9]),135            ("greaterThanOrEqual", "5", nil, [5, 7], [4.999]),136            ("lessThanOrEqual", "5", nil, [5, -7], [5.001]),137        ]138        for (op, f1, f2, good, bad) in cases {139            let (_, v) = book([dv("A1", .decimal, op, f1, f2)])140            for x in good { #expect(ok(v, .number(x)), "\(op) should accept \(x)") }141            for x in bad { #expect(!ok(v, .number(x)), "\(op) should reject \(x)") }142        }143        let (_, v) = book([dv("A1", .decimal, "greaterThanOrEqual", "0.5")])144        #expect(v.validate(.number(0.25), sheet: 0, row: 0, col: 0).reason == "Enter a number greater than or equal to 0.5.")145        #expect(v.validate(.text("x"), sheet: 0, row: 0, col: 0).reason == "Enter a number.")146        let (_, bad) = book([dv("A1", .decimal, "sameAs", "1")])147        #expect(!ok(bad, .number(1)))148        #expect(bad.validate(.number(1), sheet: 0, row: 0, col: 0).reason == "The validation criteria of this cell could not be evaluated.")149        let (_, missing) = book([dv("A1", .decimal, "between", "1", nil)])150        #expect(!ok(missing, .number(1)))151        let (_, garbage) = book([dv("A1", .decimal, "equal", "\"abc\"")])152        #expect(!ok(garbage, .number(1)))153    }154155    @Test func datesAndTimes() {156        let start = DateSerial.serial(year: 2026, month: 1, day: 1), end = DateSerial.serial(year: 2026, month: 12, day: 31)157        let (_, v) = book([dv("A1", .date, "between", String(Int(start)), String(Int(end))),158                           dv("B1", .time, "between", "0.375", "0.75"),159                           dv("C1", .date, "greaterThan", "DATE(2026,6,30)")])        // DATE is not registered in the core registry160        #expect(ok(v, .number(DateSerial.serial(year: 2026, month: 7, day: 14))))161        #expect(ok(v, .number(start)) && ok(v, .number(end)))162        #expect(!ok(v, .number(DateSerial.serial(year: 2025, month: 12, day: 31))))163        #expect(!ok(v, .number(DateSerial.serial(year: 2027, month: 1, day: 1))))164        #expect(!ok(v, .text("2026-07-14")))165        #expect(v.validate(.text("soon"), sheet: 0, row: 0, col: 0).reason == "Enter a date.")166        #expect(ok(v, .number(0.5), "B1"))               // 12:00167        #expect(ok(v, .number(0.375), "B1"))             // 09:00168        #expect(!ok(v, .number(0.8), "B1"))              // 19:12169        #expect(!ok(v, .text("noon"), "B1"))170        #expect(v.validate(.text("noon"), sheet: 0, row: 0, col: 1).reason == "Enter a time.")171        // a bound that cannot be evaluated rejects the entry with an explicit reason172        #expect(v.validate(.number(end), sheet: 0, row: 0, col: 2).reason == "The validation criteria of this cell could not be evaluated.")173    }174175    @Test func textLength() {176        let (_, v) = book([dv("A1", .textLength, "lessThanOrEqual", "5"), dv("B1", .textLength, "between", "2", "3"), dv("C1", .textLength, "equal", "4")])177        #expect(ok(v, .text("hello")))178        #expect(!ok(v, .text("hello!")))179        #expect(ok(v, .number(12345)))180        #expect(!ok(v, .number(123456)))                 // six characters as displayed181        #expect(ok(v, .text("héé")))                      // characters, not bytes182        #expect(!ok(v, .text("a"), "B1"))183        #expect(ok(v, .text("ab"), "B1") && ok(v, .text("abc"), "B1"))184        #expect(!ok(v, .text("abcd"), "B1"))185        #expect(ok(v, .bool(true), "C1"))                 // "TRUE"186        #expect(v.validate(.text("abcdef"), sheet: 0, row: 0, col: 0).reason == "Enter text whose length is less than or equal to 5.")187    }188189    @Test func literalLists() {190        let (_, v) = book([dv("A1", .list, "between", "\"Red,Green, Blue\""), dv("B1", .list, "between", "\"1,2,3\""), dv("C1", .list, "between", "Yes,No"),191                           dv("D1", .list, "between", "\"He said \"\"hi\"\",Bye\"")])192        #expect(ok(v, .text("Red")) && ok(v, .text("Green")) && ok(v, .text("Blue")))     // items are trimmed193        #expect(!ok(v, .text("green")))                   // literal lists are case-sensitive, as in Excel194        #expect(!ok(v, .text("Purple")))195        #expect(!ok(v, .number(1)))196        #expect(v.validate(.text("Purple"), sheet: 0, row: 0, col: 0).reason == "Choose a value from the list.")197        #expect(v.dropdownItems(sheet: 0, row: 0, col: 0) == [.text("Red"), .text("Green"), .text("Blue")])198        #expect(ok(v, .number(2), "B1") && ok(v, .text("3"), "B1"))                       // numeric items match numbers199        #expect(!ok(v, .number(4), "B1"))200        #expect(ok(v, .text("No"), "C1"))                 // unquoted comma list is tolerated201        #expect(v.dropdownItems(sheet: 0, row: 0, col: 2) == [.text("Yes"), .text("No")])202        #expect(v.dropdownItems(sheet: 0, row: 0, col: 3) == [.text("He said \"hi\""), .text("Bye")])203        #expect(v.dropdownItems(sheet: 0, row: 5, col: 5) == nil)204    }205206    @Test func listsFromRangesAndNames() {207        var named = dv("B1:B3", .list, "between", "Fruits")208        named.showDropdown = false209        let (b, v) = book([dv("A1:A3", .list, "between", "$E$1:$E$4"), named, dv("C1:C3", .list, "between", "=F1:F2"), dv("D1", .list, "between", "$G$1")])210        b.column("E1", ["Apple", "Pear", "", "7"])211        b.column("F1", ["x", "y", "z", "w"])212        b.set("G1", "only")213        b.engine.setDefinedName(DefinedName(name: "Fruits", formula: "Sheet1!$E$1:$E$2"))214        #expect(v.dropdownItems(sheet: 0, row: 0, col: 0) == [.text("Apple"), .text("Pear"), .number(7)])     // blanks are skipped215        #expect(ok(v, .text("apple")) && ok(v, .text("PEAR")))                             // lists read from cells are case-insensitive216        #expect(ok(v, .number(7)) && ok(v, .text("7")))217        #expect(!ok(v, .text("Plum")) && !ok(v, .number(8)))218        #expect(v.dropdownItems(sheet: 0, row: 1, col: 1) == [.text("Apple"), .text("Pear")])219        #expect(ok(v, .text("Pear"), "B2") && !ok(v, .number(7), "B2"))220        #expect(v.rule(sheet: 0, row: 1, col: 1)?.showDropdown == false)221        // a relative source moves with the cell: C1 → F1:F2, C3 → F3:F4222        #expect(v.dropdownItems(sheet: 0, row: 0, col: 2) == [.text("x"), .text("y")])223        #expect(v.dropdownItems(sheet: 0, row: 2, col: 2) == [.text("z"), .text("w")])224        #expect(ok(v, .text("w"), "C3") && !ok(v, .text("w"), "C1"))225        #expect(v.dropdownItems(sheet: 0, row: 0, col: 3) == [.text("only")])226        // the list follows the cells227        b.set("E3", "Fig")228        #expect(ok(v, .text("fig")))229        #expect(v.dropdownItems(sheet: 0, row: 0, col: 0)?.count == 4)230    }231232    @Test func blanksAndAllowBlank() {233        var strict = dv("B1", .whole, "between", "1", "10")234        strict.allowBlank = false235        var strictList = dv("C1", .list, "between", "\"a,b\"")236        strictList.allowBlank = false237        var strictLength = dv("D1", .textLength, "lessThan", "3")238        strictLength.allowBlank = false239        var strictBound = dv("F1", .decimal, "greaterThan", "$Z$1")240        strictBound.allowBlank = false241        let (_, v) = book([dv("A1", .whole, "between", "1", "10"), strict, strictList, strictLength, dv("E1", .decimal, "greaterThan", "$Z$1"), strictBound])242        #expect(ok(v, .blank, "A1"))243        #expect(!ok(v, .blank, "B1"))244        #expect(!ok(v, .blank, "C1"))245        #expect(ok(v, .blank, "D1"))                      // length 0 < 3246        // a bound that refers to an empty cell: ignored with allowBlank, read as 0 without247        #expect(ok(v, .number(-5), "E1"))248        #expect(!ok(v, .number(-5), "F1"))249        #expect(ok(v, .number(5), "F1"))250    }251252    @Test func boundsReadFromCellsShiftWithTheCell() {253        let (b, v) = book([dv("B1:B3", .decimal, "lessThanOrEqual", "A1"), dv("C1:C3", .whole, "between", "$A$1", "$A$3")])254        b.column("A1", ["10", "20", "30"])255        #expect(ok(v, .number(10), "B1") && !ok(v, .number(11), "B1"))256        #expect(ok(v, .number(20), "B2") && !ok(v, .number(21), "B2"))257        #expect(ok(v, .number(30), "B3") && !ok(v, .number(31), "B3"))258        #expect(ok(v, .number(30), "C3") && !ok(v, .number(31), "C3") && !ok(v, .number(9), "C2"))259        #expect(v.validate(.number(21), sheet: 0, row: 1, col: 1).reason == "Enter a number less than or equal to 20.")260        b.set("A2", "25")261        #expect(ok(v, .number(21), "B2"))262    }263264    @Test func customFormulaSeesTheProposedValueWithoutEditingTheWorkbook() {265        let (b, v) = book([dv("A1:A5", .custom, "between", "=A1>$C$1"), dv("B1:B5", .custom, "between", "=IF(B1>0,B1<A1,FALSE)"),266                           dv("D1", .custom, "between", "=D1<>\"bad\""), dv("E1", .custom, "between", "=SUM(E1,"), dv("F1", .custom, "between", "=Sheet1!F1*2=8")])267        b.set("C1", "10")268        b.column("A1", ["50", "60", "70"])269        let before = (b.engine.changeCount, b.engine.undoLabel, b.engine.canRedo)270        #expect(ok(v, .number(11), "A1") && !ok(v, .number(9), "A1"))271        #expect(ok(v, .number(11), "A3") && !ok(v, .number(10), "A3"))                     // the reference shifted to A3 and was substituted272        #expect(ok(v, .text("abc"), "A4"))                                                  // text sorts above numbers: "abc" > 10273        #expect(ok(v, .number(40), "B1") && !ok(v, .number(50), "B1") && !ok(v, .number(-1), "B1"))274        #expect(ok(v, .number(65), "B3") && !ok(v, .number(65), "B2"))275        #expect(ok(v, .text("good"), "D1") && !ok(v, .text("BAD"), "D1"))276        #expect(!ok(v, .number(1), "E1"))277        #expect(v.validate(.number(1), sheet: 0, row: 0, col: 4).reason == "The validation formula of this cell could not be parsed.")278        #expect(ok(v, .number(4), "F1") && !ok(v, .number(5), "F1"))                       // sheet-qualified self reference279        #expect(v.validate(.number(9), sheet: 0, row: 0, col: 0).reason == "The value does not satisfy the validation formula.")280        // nothing was written: values, undo history and change counter are untouched281        #expect(b.sheet.value(0, 0) == .number(50) && b.sheet.value(2, 0) == .number(70))282        #expect(b.sheet.cell(0, 1) == nil && b.sheet.cell(0, 3) == nil)283        #expect((b.engine.changeCount, b.engine.undoLabel, b.engine.canRedo) == before)284    }285286    @Test func alertStylesAndCustomMessages() {287        var warn = dv("A1", .whole, "greaterThan", "0")288        warn.errorStyle = "warning"289        warn.errorTitle = "Careful"290        warn.errorMessage = "Quantities are normally positive."291        var info = dv("A2", .whole, "greaterThan", "0")292        info.errorStyle = "information"293        var odd = dv("A3", .whole, "greaterThan", "0")294        odd.errorStyle = "somethingElse"295        odd.errorTitle = ""296        let (_, v) = book([warn, info, odd, dv("A4", .any)])297        let w = v.validate(.number(-1), sheet: 0, row: 0, col: 0)298        #expect(!w.isValid && w.style == .warning && w.canOverride)299        #expect(w.title == "Careful" && w.message == "Quantities are normally positive.")300        #expect(w.reason == "Enter a whole number greater than 0.")301        let i = v.validate(.number(-1), sheet: 0, row: 1, col: 0)302        #expect(i.style == .information && i.canOverride && i.message == i.reason)303        let o = v.validate(.number(-1), sheet: 0, row: 2, col: 0)304        #expect(o.style == .stop && o.title == "Invalid entry")305        #expect(v.validate(.error(.na), sheet: 0, row: 3, col: 0) == .valid)                // kind `any` accepts everything306        #expect(v.validate(.number(3), sheet: 0, row: 0, col: 0) == .valid)307        #expect(ValidationOutcome.valid.canOverride)308    }309310    @Test func typedInputIsParsedFirst() {311        let (b, v) = book([dv("A1", .whole, "between", "1", "10"), dv("B1", .decimal, "lessThan", "1"), dv("C1", .date, "greaterThan", "40000")])312        b.set("D1", "3")313        func typed(_ text: String, _ a1: String) -> ValidationOutcome {314            let r = CellRef(a1: a1)!315            return v.validate(input: text, sheet: 0, row: r.row, col: r.col, parser: b.parser)316        }317        #expect(typed("7", "A1").isValid)318        #expect(!typed("70", "A1").isValid)319        #expect(!typed("seven", "A1").isValid)320        #expect(typed("", "A1").isValid)321        #expect(typed("=D1+4", "A1").isValid)              // the formula's result (7) is validated322        #expect(!typed("=D1*4", "A1").isValid)323        #expect(typed("=D1+", "A1").title == "Invalid formula")324        #expect(typed("50%", "B1").isValid)325        #expect(!typed("150%", "B1").isValid)326        #expect(typed("2026-03-01", "C1").isValid)         // parsed to a date serial327        #expect(!typed("1999-03-01", "C1").isValid)328        #expect(b.sheet.cell(0, 0) == nil)329    }330331    @Test func invalidCellsOfExistingData() {332        var noBlank = dv("C1:C3", .list, "between", "\"a,b\"")333        noBlank.allowBlank = false334        let (b, v) = book([dv("A1:A6", .whole, "between", "1", "10"), dv("B1:B6", .textLength, "lessThan", "4"), noBlank, dv("D1:D6", .any)])335        b.column("A1", ["5", "50", "2.5", "x", "", "=3*3"])336        b.column("B1", ["abc", "abcd", "12", "12345"])337        b.column("C1", ["a", "z"])338        b.column("D1", ["anything"])339        let bad = v.invalidCells(sheet: 0, in: RangeRef(a1: "A1:D6")!)340        #expect(bad.keys.sorted().map(\.a1) == ["A2", "B2", "C2", "A3", "A4", "B4"])341        #expect(bad[CellRef(a1: "A2")!]?.reason == "Enter a whole number between 1 and 10.")342        #expect(bad[CellRef(a1: "A3")!]?.reason == "Enter a whole number.")343        #expect(bad[CellRef(a1: "C2")!]?.reason == "Choose a value from the list.")344        #expect(v.invalidCells(sheet: 0, in: RangeRef(a1: "A5:A6")!).isEmpty)              // blank allowed, 9 valid345        #expect(v.invalidCells(sheet: 3, in: RangeRef(a1: "A1:D6")!).isEmpty)346    }347348    @Test func firstMatchingRuleWins() {349        let (_, v) = book([dv("A1:A3", .whole, "lessThan", "5"), dv("A1:B9", .whole, "greaterThan", "100")])350        #expect(ok(v, .number(1), "A2") && !ok(v, .number(200), "A2"))351        #expect(ok(v, .number(200), "A5") && !ok(v, .number(1), "A5"))352        #expect(ok(v, .number(200), "B1"))353    }354}355
Files main
Menu

Theme