Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetAutomation/Tests/SheetAutomationTests/ValidatorStoreTests.swift10// Module : SheetAutomationTests11// 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 SheetIO91@testable import SheetAutomation9293@Suite struct ValidatorTests {94 func errors(_ steps: [WorkflowStep], parameters: [WorkflowParameter] = [], values: [String: String] = [:], hasWorkbook: Bool = false, dir: URL? = nil) -> [String] {95 let d = dir ?? TestEnv.tempDir()96 return WorkflowValidator.validate(Workflow(name: "V", parameters: parameters, steps), parameters: values, host: TestEnv.host(d), hasWorkbook: hasWorkbook)97 .filter { $0.severity == .error }.map(\.message)98 }99100 @Test func templatesAreStructurallyValid() {101 let dir = TestEnv.tempDir()102 for t in WorkflowTemplates.all {103 let d = WorkflowValidator.validate(t, host: TestEnv.host(dir), requireValues: false)104 #expect(d.filter { $0.severity == .error }.isEmpty, "\(t.name): \(d.map(\.message))")105 // without values the required parameters are reported106 #expect(WorkflowValidator.validate(t, host: TestEnv.host(dir)).contains { $0.message.contains("needs a value") })107 }108 }109110 @Test func unknownAndMisplacedReferences() {111 let e = errors([.newWorkbook(NewWorkbookSettings()), .setCell(SetCellSettings(cell: "A1", input: "{{ghost}} {{input}} {{last.color}} {{bad name}} {{date.shout}}"))])112 #expect(e.contains { $0.contains("Unknown parameter “{{ghost}}”") })113 #expect(e.contains { $0.contains("“{{input}}” is only available inside") })114 #expect(e.contains { $0.contains("{{last.color}}") })115 #expect(e.contains { $0.contains("“{{bad name}}” is not a valid parameter reference") })116 #expect(e.contains { $0.contains("unknown modifier “.shout”") })117 let ok = errors([.newWorkbook(NewWorkbookSettings()), .forEachFile(ForEachFileSettings(folder: ".", steps: [WorkflowItem(.setCell(SetCellSettings(cell: "A1", input: "{{input.name}} {{index}} {{date}}")))]))])118 #expect(ok.isEmpty, "\(ok)")119 }120121 @Test func parameterProblems() {122 let params = [WorkflowParameter(name: "n", kind: .number), WorkflowParameter(name: "flag", kind: .bool), WorkflowParameter(name: "mode", kind: .choice, choices: ["a", "b"]),123 WorkflowParameter(name: "n"), WorkflowParameter(name: "2bad"), WorkflowParameter(name: "date"), WorkflowParameter(name: "opt", required: false)]124 let d = WorkflowValidator.validate(Workflow(name: "P", parameters: params, [.newWorkbook(NewWorkbookSettings())]),125 parameters: ["n": "abc", "flag": "maybe", "mode": "z", "extra": "1"], host: TestEnv.host(TestEnv.tempDir()))126 let e = d.filter { $0.severity == .error }.map(\.message)127 #expect(e.contains { $0.contains("“abc” is not a number") } && e.contains { $0.contains("must be true or false") } && e.contains { $0.contains("is not one of a, b") })128 #expect(e.contains { $0.contains("Two parameters are named “n”") } && e.contains { $0.contains("“2bad”") } && e.contains { $0.contains("“date”") })129 #expect(!e.contains { $0.contains("“opt”") })130 #expect(d.contains { $0.severity == .warning && $0.message.contains("“extra” is not a parameter") })131 }132133 @Test func workbookRequirement() {134 #expect(errors([.setCell(SetCellSettings(cell: "A1", input: "1"))]).contains { $0.contains("needs a workbook") })135 #expect(errors([.setCell(SetCellSettings(cell: "A1", input: "1"))], hasWorkbook: true).isEmpty)136 #expect(errors([.newWorkbook(NewWorkbookSettings())], hasWorkbook: true).contains { $0.contains("Run it standalone") })137 #expect(errors([.runSQL(RunSQLSettings(sql: "SELECT 1", output: .dataset("d")))]).isEmpty)138 #expect(errors([.runSQL(RunSQLSettings(sql: "SELECT 1", output: .sheet("S")))]).contains { $0.contains("needs a workbook") })139 // inside an isolated batch a workbook opened per file is fine even when running on an open workbook140 let batch = WorkflowStep.forEachFile(ForEachFileSettings(folder: ".", steps: [WorkflowItem(.openWorkbook(OpenWorkbookSettings(path: "{{input}}"))), WorkflowItem(.recalculate(RecalculateSettings()))]))141 #expect(errors([batch], hasWorkbook: true).isEmpty)142 #expect(errors([batch, .recalculate(RecalculateSettings())]).contains { $0.contains("needs a workbook") })143 }144145 @Test func fieldSyntax() {146 let e = errors([147 .sort(SortSettings(range: "Data!A1:", keys: [])),148 .setCell(SetCellSettings(sheet: "bad/name", cell: "1A", input: "=SUM(")),149 .setFormulaRange(SetFormulaRangeSettings(range: "A1:A3", formula: "=1+")),150 .formatRange(FormatRangeSettings(range: "A1", fill: "blue")),151 .createChart(CreateChartSettings(range: "A1:B3", kind: "spiral")),152 .createPivot(CreatePivotSettings(values: ["mode(x)"])),153 .createPivot(CreatePivotSettings(source: .dataset, dataset: "nowhere", rows: ["a"], values: ["sum(x)"])),154 .validate(ValidateSettings(noErrorValues: false)),155 .runSQL(RunSQLSettings(sql: "DROP TABLE x", output: .dataset("9lives"))),156 .importDataset(ImportDatasetSettings(path: "", name: "data")),157 ], hasWorkbook: true)158 for needle in ["“Data!A1:” is not a valid range", "“Sort by” is empty", "“bad/name” is not a valid sheet name", "“1A” is not a valid cell", "Set cell: “=SUM(” is not a valid formula",159 "Fill formula: “=1+” is not a valid formula", "“blue” is not a colour", "“spiral” is not a valid “Chart type”", "“mode(x)” is not a valid value",160 "no earlier step creates the dataset “nowhere”", "Validate: no rule is selected", "must be a single SELECT", "“9lives” is not a valid dataset name", "“Data file” is empty"] {161 #expect(e.contains { $0.contains(needle) }, "missing: \(needle) in \(e)")162 }163 }164165 @Test func filesAndFormats() throws {166 let dir = TestEnv.tempDir()167 TestEnv.salesCSV(dir)168 try Data("x".utf8).write(to: dir.appendingPathComponent("old.xls"))169 try Data("x".utf8).write(to: dir.appendingPathComponent("data.feather"))170 let e = errors([171 .openWorkbook(OpenWorkbookSettings(path: "missing.xlsx")),172 .openWorkbook(OpenWorkbookSettings(path: "old.xls")),173 .importDataset(ImportDatasetSettings(path: "data.feather")),174 .importDataset(ImportDatasetSettings(path: "sales.docx")),175 .exportFile(ExportFileSettings(path: "out.pdf")),176 .exportFile(ExportFileSettings(path: "out.numbers")),177 .exportFile(ExportFileSettings(path: "out.xyz")),178 .saveWorkbook(SaveWorkbookSettings(path: "doc.xlsx")),179 .forEachFile(ForEachFileSettings(folder: "nowhere", pattern: "a/*.csv", steps: [])),180 .forEachFile(ForEachFileSettings(folder: "sales.csv", steps: [])),181 ], dir: dir)182 for needle in ["file not found: \(dir.path)/missing.xlsx", "Excel 97-2003 workbook (.xls) files cannot be opened", "Arrow IPC", "the format of “sales.docx” is not supported",183 "Export file does not write PDF", "Apple Numbers document cannot be written", "“xyz” is not a writable format", "must end in .orixsheet",184 "folder not found", "cannot contain “/”", "is not a folder"] {185 #expect(e.contains { $0.contains(needle) }, "missing: \(needle) in \(e)")186 }187 // a file produced by an earlier step is not "missing"188 let chained = errors([.exportFile(ExportFileSettings(path: "tmp.csv")), .importDataset(ImportDatasetSettings(path: "tmp.csv"))], hasWorkbook: true, dir: dir)189 #expect(chained.isEmpty, "\(chained)")190 let warnings = WorkflowValidator.validate(Workflow(name: "W", [.exportPDF(ExportPDFSettings(path: "report.txt")), .exportChart(ExportChartSettings(path: "c.jpg"))]), host: TestEnv.host(dir), hasWorkbook: true)191 #expect(warnings.filter { $0.severity == .warning }.count == 2 && warnings.filter { $0.severity == .error }.isEmpty)192 }193194 @Test func diagnosticsCarryStepPaths() {195 let dir = TestEnv.tempDir()196 let wf = Workflow(name: "Paths", [.newWorkbook(NewWorkbookSettings()),197 .forEachFile(ForEachFileSettings(folder: ".", steps: [WorkflowItem(.recalculate(RecalculateSettings())), WorkflowItem(.setCell(SetCellSettings(cell: "ZZZZ1", input: "x")))]))])198 let d = WorkflowValidator.validate(wf, host: TestEnv.host(dir))199 #expect(d.map(\.path) == [[1, 1]])200 #expect(Workflow(name: "Empty").steps.isEmpty && WorkflowValidator.validate(Workflow(name: "Empty"), host: TestEnv.host(dir)).map(\.severity) == [.warning])201 }202203 @Test func fileAccessListForTheTrustConfirmation() {204 let dir = TestEnv.tempDir()205 TestEnv.salesCSV(dir)206 let accesses = WorkflowValidator.fileAccesses(WorkflowTemplates.csvReport, parameters: ["source": dir.appendingPathComponent("sales.csv").path], host: TestEnv.host(dir))207 #expect(accesses.map(\.access) == [.read, .write])208 #expect(accesses[0].path == dir.appendingPathComponent("sales.csv").path && !accesses[0].isDynamic)209 #expect(accesses[1].path == dir.appendingPathComponent("sales-report.pdf").path)210 let batch = WorkflowValidator.fileAccesses(WorkflowTemplates.batchConvert, parameters: ["folder": dir.path], host: TestEnv.host(dir))211 #expect(batch.contains { $0.access == .read && $0.path == dir.path })212 #expect(batch.contains { $0.access == .write && $0.isDynamic && $0.path.contains("{{input.basename}}.xlsx") })213 }214}215216@Suite(.serialized) struct StoreTests {217 @Test func workbookAttachmentsRoundTripThroughTheNativeFormat() throws {218 let dir = TestEnv.tempDir()219 let wb = TestEnv.modelWorkbook()220 let a = WorkflowTemplates.csvReport.duplicated(name: "Zeta"), b = WorkflowTemplates.batchConvert.duplicated(name: "Alpha")221 try WorkflowStore.save(a, to: wb)222 try WorkflowStore.save(b, to: wb)223 wb.attachments["workflows/broken.json"] = Data("{".utf8)224 wb.attachments["python/other.json"] = Data("{}".utf8)225 #expect(WorkflowStore.load(from: wb) == [b, a])226 let url = dir.appendingPathComponent("doc.orixsheet")227 try WorkbookWriter.save(wb, to: url, format: .orixsheet)228 let back = try WorkbookReader.load(url: url).workbook229 #expect(WorkflowStore.load(from: back) == [b, a])230 WorkflowStore.remove(a.id, from: back)231 #expect(WorkflowStore.load(from: back) == [b])232 #expect(wb.attachments[WorkflowStore.key(a.id)] != nil)233 }234235 @Test func libraryAndFiles() throws {236 _ = TestEnv.support237 let wf = WorkflowTemplates.monthlyReport.duplicated(name: "Library copy")238 try WorkflowStore.saveToLibrary(wf)239 #expect(WorkflowStore.library().contains(wf))240 #expect(TestEnv.exists(WorkflowPaths.library.appendingPathComponent("\(wf.id.uuidString).orixflow")))241 var edited = wf242 edited.summary = "Changed"243 try WorkflowStore.saveToLibrary(edited)244 #expect(WorkflowStore.library().filter { $0.id == wf.id } == [edited])245 WorkflowStore.removeFromLibrary(wf.id)246 #expect(!WorkflowStore.library().contains { $0.id == wf.id })247248 let dir = TestEnv.tempDir()249 let file = dir.appendingPathComponent("shared.orixflow")250 try WorkflowStore.exportFile(wf, to: file)251 #expect(try WorkflowStore.importFile(file) == wf)252 try Data("{\"name\": 3}".utf8).write(to: file)253 #expect(throws: WorkflowError.self) { try WorkflowStore.importFile(file) }254 #expect(throws: WorkflowError.self) { try WorkflowStore.importFile(dir.appendingPathComponent("absent.orixflow")) }255 }256257 @Test func historyIsBoundedAndNewestFirst() async throws {258 _ = TestEnv.support259 WorkflowHistory.clear()260 let dir = TestEnv.tempDir()261 let (real, _) = try await TestEnv.run([.exportFile(ExportFileSettings(path: "x.csv", sheet: "Data"))], workbook: TestEnv.modelWorkbook(), in: dir)262 WorkflowHistory.append(real)263 let back = try #require(WorkflowHistory.recent(limit: 5).first)264 #expect(back.id == real.id && back.status == .succeeded && back.stepResults == real.stepResults && back.outputFiles == real.outputFiles)265 #expect(abs(back.startedAt.timeIntervalSince(real.startedAt)) < 1)266 let other = UUID()267 for i in 0..<(WorkflowHistory.maxRuns + 20) {268 WorkflowHistory.append(WorkflowRun(id: UUID(), workflowID: other, workflowName: "n\(i)", startedAt: Date(), finishedAt: Date(), status: i % 2 == 0 ? .failed : .cancelled,269 parameterValues: ["i": "\(i)"], stepResults: [], message: "m"))270 }271 let all = WorkflowHistory.recent(limit: 10_000)272 #expect(all.count == WorkflowHistory.maxRuns)273 #expect(all.first?.workflowName == "n\(WorkflowHistory.maxRuns + 19)" && all.last?.workflowName == "n20")274 #expect(WorkflowHistory.recent(limit: 3).count == 3)275 #expect(WorkflowHistory.recent(workflowID: real.workflowID).isEmpty) // pushed out by newer runs276 #expect(WorkflowHistory.recent(limit: 2, workflowID: other).count == 2)277 let mode = try FileManager.default.attributesOfItem(atPath: WorkflowPaths.history.path)[.posixPermissions] as? Int278 #expect(mode == 0o600)279 WorkflowHistory.clear()280 #expect(WorkflowHistory.recent().isEmpty)281 }282283 @Test func trustIsBoundToTheExactText() {284 _ = TestEnv.support285 var wf = WorkflowTemplates.csvReport.duplicated(name: "Trust me")286 #expect(!WorkflowTrust.isTrusted(wf))287 WorkflowTrust.approve(wf)288 #expect(WorkflowTrust.isTrusted(wf))289 #expect(WorkflowTrust.hash(wf).count == 64 && WorkflowTrust.hash(wf) == WorkflowTrust.hash(try! Workflow.decode(try! wf.jsonData())))290 let approved = wf291 // any change — a path, a step, a parameter default — makes it untrusted again292 if case .exportPDF(var s) = wf.steps[wf.steps.count - 1].step { s.path = "/tmp/elsewhere.pdf"; wf.steps[wf.steps.count - 1].step = .exportPDF(s) }293 #expect(!WorkflowTrust.isTrusted(wf))294 #expect(WorkflowTrust.isTrusted(approved))295 WorkflowTrust.revoke(approved)296 #expect(!WorkflowTrust.isTrusted(approved))297 #expect(!WorkflowTrust.records().contains { $0.hash == WorkflowTrust.hash(approved) })298 }299300 @Test func loadingAWorkbookNeverRunsOrTrustsItsWorkflows() throws {301 let dir = TestEnv.tempDir()302 let wb = TestEnv.modelWorkbook()303 let hostile = Workflow(name: "Surprise", [.exportFile(ExportFileSettings(path: dir.appendingPathComponent("surprise.csv").path))])304 try WorkflowStore.save(hostile, to: wb)305 let url = dir.appendingPathComponent("received.orixsheet")306 try WorkbookWriter.save(wb, to: url, format: .orixsheet)307 let opened = try WorkbookReader.load(url: url).workbook308 let found = WorkflowStore.load(from: opened)309 #expect(found.map(\.name) == ["Surprise"])310 #expect(!WorkflowTrust.isTrusted(found[0]))311 #expect(!TestEnv.exists(dir.appendingPathComponent("surprise.csv")))312 }313}314