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/EngineTests.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 EngineTests {94 @Test func parametersAndBuiltInsAreSubstituted() async throws {95 let dir = TestEnv.tempDir()96 TestEnv.salesCSV(dir)97 let params = [WorkflowParameter(name: "source", kind: .file), WorkflowParameter(name: "label", defaultValue: "Report"),98 WorkflowParameter(name: "out", kind: .file, defaultValue: "{{source.folder}}/{{source.basename}}-{{label}}.csv")]99 let (run, wb) = try await TestEnv.run([100 .importFile(ImportFileSettings(path: "{{source}}", sheet: "{{label}}")),101 .setCell(SetCellSettings(sheet: "{{label}}", cell: "E1", input: "{{label}} {{year}} from {{source.name}} into {{workbook}}")),102 .exportFile(ExportFileSettings(path: "{{out}}")),103 ], parameters: params, values: ["source": "sales.csv"], in: dir)104 expectSucceeded(run)105 #expect(run.parameterValues["label"] == "Report")106 let year = Calendar.current.component(.year, from: run.startedAt)107 #expect(TestEnv.value(try #require(wb), "Report", "E1") == .text("Report \(year) from sales.csv into Workbook"))108 #expect(TestEnv.exists(dir.appendingPathComponent("sales-Report.csv")))109 #expect(run.outputFiles == [dir.appendingPathComponent("sales-Report.csv").path])110 }111112 @Test func invalidWorkflowDoesNotRun() async throws {113 let dir = TestEnv.tempDir()114 let wf = Workflow(name: "Bad", [.setCell(SetCellSettings(cell: "A1", input: "x")), .exportFile(ExportFileSettings(path: "{{nope}}.csv"))])115 await #expect(throws: WorkflowError.self) { _ = try await WorkflowEngine(host: TestEnv.host(dir)).run(wf) }116 #expect(try FileManager.default.contentsOfDirectory(atPath: dir.path).isEmpty)117 }118119 @Test func stopPolicyLeavesNoOutputAndReturnsNoWorkbook() async throws {120 let dir = TestEnv.tempDir()121 let original = TestEnv.modelWorkbook()122 let (run, wb) = try await TestEnv.run([123 .setCell(SetCellSettings(sheet: "Data", cell: "B2", input: "5")),124 .exportFile(ExportFileSettings(path: "first.xlsx")),125 .sort(SortSettings(range: "Data!*", keys: ["missing column"])),126 .exportFile(ExportFileSettings(path: "second.xlsx")),127 ], workbook: original.clone(), in: dir)128 #expect(run.status == .failed && wb == nil)129 #expect(run.stepResults.map(\.status) == [.succeeded, .succeeded, .failed])130 #expect(run.message.hasPrefix("Step 3 “Sort range” failed: Column “missing column” was not found"))131 #expect(try FileManager.default.contentsOfDirectory(atPath: dir.path).isEmpty)132 #expect(run.outputFiles.isEmpty)133 #expect(TestEnv.value(original, "Data", "B2") == .number(1200))134 }135136 @Test func continuePolicyKeepsGoing() async throws {137 let dir = TestEnv.tempDir()138 let steps: [WorkflowStep] = [139 .sort(SortSettings(range: "Data!*", keys: ["missing column"])),140 .setCell(SetCellSettings(sheet: "Summary", cell: "D1", input: "done")),141 .exportFile(ExportFileSettings(path: "kept.xlsx")),142 ]143 let (run, wb) = try await TestEnv.run(steps, workbook: TestEnv.modelWorkbook(), in: dir, onError: .continue)144 #expect(run.status == .completedWithErrors)145 #expect(run.stepResults.map(\.status) == [.failed, .succeeded, .succeeded])146 #expect(TestEnv.value(try #require(wb), "Summary", "D1") == .text("done"))147 #expect(TestEnv.exists(dir.appendingPathComponent("kept.xlsx")))148 #expect(run.message.contains("1 step failed"))149150 // per-step override in both directions151 var wf = Workflow(name: "Mixed", steps, onError: .stop)152 wf.steps[0].onError = .continue153 let (r2, _) = try await WorkflowEngine(host: TestEnv.host(dir)).run(wf, workbook: TestEnv.modelWorkbook())154 #expect(r2.status == .completedWithErrors)155 var wf2 = Workflow(name: "Mixed", steps, onError: .continue)156 wf2.steps[0].onError = .stop157 let (r3, w3) = try await WorkflowEngine(host: TestEnv.host(dir)).run(wf2, workbook: TestEnv.modelWorkbook())158 #expect(r3.status == .failed && w3 == nil && r3.stepResults.count == 1)159 }160161 @Test func cancellationBetweenSteps() async throws {162 let dir = TestEnv.tempDir()163 let original = TestEnv.modelWorkbook()164 let engine = WorkflowEngine(host: TestEnv.host(dir))165 let wf = Workflow(name: "Cancel me", [166 .setCell(SetCellSettings(sheet: "Data", cell: "B2", input: "5")),167 .exportFile(ExportFileSettings(path: "a.xlsx")),168 .exportFile(ExportFileSettings(path: "b.csv", sheet: "Data")),169 .exportFile(ExportFileSettings(path: "c.ods")),170 ])171 let (run, wb) = try await engine.run(wf, workbook: original.clone()) { progress in172 if case .finished = progress.phase, progress.path == [1] { engine.cancel() }173 }174 #expect(run.status == .cancelled && wb == nil)175 #expect(run.stepResults.count == 2 || run.stepResults.last?.status == .cancelled)176 #expect(!run.stepResults.contains { $0.path == [3] })177 #expect(try FileManager.default.contentsOfDirectory(atPath: dir.path).isEmpty) // a.xlsx was produced but never moved into place178 #expect(run.outputFiles.isEmpty)179 #expect(TestEnv.value(original, "Data", "B2") == .number(1200))180 // the engine is reusable after a cancellation181 let (again, _) = try await engine.run(wf, workbook: original.clone())182 expectSucceeded(again)183 #expect(try FileManager.default.contentsOfDirectory(atPath: dir.path).sorted() == ["a.xlsx", "b.csv", "c.ods"])184 }185186 @Test func cancellationInterruptsARunningQuery() async throws {187 let dir = TestEnv.tempDir()188 let engine = WorkflowEngine(host: TestEnv.host(dir))189 let wf = Workflow(name: "Long", [190 .exportFile(ExportFileSettings(path: "early.xlsx")),191 .runSQL(RunSQLSettings(sql: "SELECT count(*) AS n FROM range(40000000000) a(i) WHERE i % 7 = 3 AND hash(i) % 3 = 1", output: .dataset("big"))),192 .exportFile(ExportFileSettings(path: "late.xlsx")),193 ])194 let started = Date()195 let canceller = Task { try? await Task.sleep(nanoseconds: 400_000_000); engine.cancel() }196 let (run, wb) = try await engine.run(wf, workbook: TestEnv.modelWorkbook())197 canceller.cancel()198 #expect(run.status == .cancelled && wb == nil)199 #expect(run.stepResults.last?.status == .cancelled && run.stepResults.last?.kind == .runSQL)200 #expect(Date().timeIntervalSince(started) < 20)201 #expect(try FileManager.default.contentsOfDirectory(atPath: dir.path).isEmpty)202 }203204 @Test func batchOverAFolder() async throws {205 let dir = TestEnv.tempDir()206 let inbox = dir.appendingPathComponent("in")207 try FileManager.default.createDirectory(at: inbox, withIntermediateDirectories: true)208 for name in ["b.csv", "a.csv", "c.CSV", "notes.txt", ".hidden.csv"] { TestEnv.salesCSV(inbox, name: name) }209 let batch = ForEachFileSettings(folder: "in", pattern: "*.csv", steps: [210 WorkflowItem(.openWorkbook(OpenWorkbookSettings(path: "{{input}}"))),211 WorkflowItem(.setCell(SetCellSettings(cell: "E1", input: "{{index}}:{{input.name}}"))),212 WorkflowItem(.exportFile(ExportFileSettings(path: "out/{{input.basename}}.xlsx"))),213 ])214 let progress = Collector()215 let wf = Workflow(name: "Batch", [.forEachFile(batch)])216 let (run, wb) = try await WorkflowEngine(host: TestEnv.host(dir)).run(wf) { p in217 if case .detail(let d) = p.phase { progress.add(d) }218 }219 expectSucceeded(run)220 #expect(wb == nil) // isolated iterations leave no workbook behind221 #expect(try FileManager.default.contentsOfDirectory(atPath: dir.appendingPathComponent("out").path).sorted() == ["a.xlsx", "b.xlsx", "c.xlsx"])222 #expect(TestEnv.value(try TestEnv.load(dir.appendingPathComponent("out/b.xlsx")), "b", "E1") == .text("2:b.csv"))223 #expect(run.stepResults.count == 10) // 3 files × 3 steps + the batch step224 #expect(run.stepResults.filter { $0.path.count == 2 }.map(\.iteration).prefix(3) == ["a.csv", "a.csv", "a.csv"])225 #expect(run.stepResults.last?.message == "3 of 3 files processed.")226 #expect(progress.items == ["1 of 3: a.csv", "2 of 3: b.csv", "3 of 3: c.CSV"])227 #expect(run.outputFiles.count == 3)228 }229230 @Test func batchSkipsAFailingFileUnderContinueAndStopsUnderStop() async throws {231 let dir = TestEnv.tempDir()232 TestEnv.salesCSV(dir, name: "1.csv")233 try Data("only,one,line\n".utf8).write(to: dir.appendingPathComponent("2.csv"))234 TestEnv.salesCSV(dir, name: "3.csv")235 let steps = [236 WorkflowItem(.openWorkbook(OpenWorkbookSettings(path: "{{input}}"))),237 WorkflowItem(.sort(SortSettings(range: "*", keys: ["amount desc"]))),238 WorkflowItem(.exportFile(ExportFileSettings(path: "out/{{input.basename}}.xlsx"))),239 ]240 var wf = Workflow(name: "Batch", steps: [WorkflowItem(.forEachFile(ForEachFileSettings(folder: ".", pattern: "?.csv", steps: steps)), onError: .continue)])241 let (run, _) = try await WorkflowEngine(host: TestEnv.host(dir)).run(wf)242 #expect(run.status == .completedWithErrors)243 #expect(try FileManager.default.contentsOfDirectory(atPath: dir.appendingPathComponent("out").path).sorted() == ["1.xlsx", "3.xlsx"])244 #expect(run.stepResults.last?.issues == ["Skipped 2.csv after a failure."])245 #expect(run.stepResults.first { $0.status == .failed }?.iteration == "2.csv")246 try FileManager.default.removeItem(at: dir.appendingPathComponent("out"))247 wf.steps[0].onError = nil248 let (stopped, _) = try await WorkflowEngine(host: TestEnv.host(dir)).run(wf)249 #expect(stopped.status == .failed && stopped.message.contains("(2.csv)"))250 #expect(!TestEnv.exists(dir.appendingPathComponent("out"))) // not even the file converted before the failure251 }252253 @Test func batchCanAccumulateIntoOneWorkbook() async throws {254 let dir = TestEnv.tempDir()255 for name in ["jan.csv", "feb.csv"] { try Data("k,v\n\(name),1\n".utf8).write(to: dir.appendingPathComponent(name)) }256 let (run, wb) = try await TestEnv.run([257 .newWorkbook(NewWorkbookSettings(sheetName: "Index")),258 .forEachFile(ForEachFileSettings(folder: ".", pattern: "*.csv", isolated: false, steps: [WorkflowItem(.importFile(ImportFileSettings(path: "{{input}}")))])),259 .forEachFile(ForEachFileSettings(folder: ".", pattern: "*.csv", isolated: true, steps: [WorkflowItem(.addSheet(SheetNameSettings(name: "temp")))])),260 ], in: dir)261 expectSucceeded(run)262 #expect(try #require(wb).sheets.map(\.name) == ["Index", "feb", "jan"])263 let (empty, _) = try await TestEnv.run([.forEachFile(ForEachFileSettings(folder: ".", pattern: "*.parquet", steps: [WorkflowItem(.openWorkbook(OpenWorkbookSettings(path: "{{input}}")))]))], in: dir)264 #expect(empty.status == .failed && empty.message.contains("No file matches"))265 }266267 @Test func filesOutsideAuthorisedFoldersAreRefused() async throws {268 let dir = TestEnv.tempDir(), other = TestEnv.tempDir("other")269 TestEnv.salesCSV(other)270 try FileManager.default.createSymbolicLink(at: dir.appendingPathComponent("link"), withDestinationURL: other)271 let host = TestEnv.host(dir)272 for step in [WorkflowStep.importFile(ImportFileSettings(path: other.appendingPathComponent("sales.csv").path)),273 .importFile(ImportFileSettings(path: "../\(other.lastPathComponent)/sales.csv")),274 .importFile(ImportFileSettings(path: "link/sales.csv")),275 .importDataset(ImportDatasetSettings(path: "link/sales.csv"))] {276 let errors = WorkflowValidator.validate(Workflow(name: "x", [step]), host: host).filter { $0.severity == .error }277 #expect(errors.contains { $0.message.contains("not allowed") }, "\(step.summary)")278 }279 for path in [other.appendingPathComponent("x.xlsx").path, "link/x.xlsx", "../x.xlsx", "/etc/x.xlsx"] {280 let errors = WorkflowValidator.validate(Workflow(name: "x", [.exportFile(ExportFileSettings(path: path))]), host: host, hasWorkbook: true)281 #expect(errors.contains { $0.message.contains("not allowed") }, "\(path)")282 }283 // a path only known at run time is checked when the step runs284 let wf = Workflow(name: "Dynamic", [.exportFile(ExportFileSettings(path: "ok.csv", sheet: "Data")), .exportFile(ExportFileSettings(path: "{{last.path.folder}}/link/leak.xlsx"))])285 let (run, _) = try await WorkflowEngine(host: host).run(wf, workbook: TestEnv.modelWorkbook())286 #expect(run.status == .failed && run.message.contains("is not allowed"))287 #expect(try FileManager.default.contentsOfDirectory(atPath: other.path) == ["sales.csv"])288 // read-only folders can be read but not written289 let readOnly = StandardWorkflowHost(baseDirectory: dir, readable: [other], writable: [dir])290 #expect(readOnly.isAuthorized(other.appendingPathComponent("sales.csv"), for: .read) && !readOnly.isAuthorized(other.appendingPathComponent("o.csv"), for: .write))291 #expect(readOnly.isAuthorized(dir.appendingPathComponent("o.csv"), for: .read))292 }293294 @Test func outputsReplaceExistingFilesOnlyOnSuccess() async throws {295 let dir = TestEnv.tempDir()296 let target = dir.appendingPathComponent("report.csv")297 try Data("old content\n".utf8).write(to: target)298 let (failed, _) = try await TestEnv.run([.exportFile(ExportFileSettings(path: "report.csv", sheet: "Data")), .deleteSheet(SheetNameSettings(name: "Nope"))],299 workbook: TestEnv.modelWorkbook(), in: dir)300 #expect(failed.status == .failed)301 #expect(try String(contentsOf: target, encoding: .utf8) == "old content\n")302 let (ok, _) = try await TestEnv.run([.exportFile(ExportFileSettings(path: "report.csv", sheet: "Data"))], workbook: TestEnv.modelWorkbook(), in: dir)303 expectSucceeded(ok)304 #expect(try String(contentsOf: target, encoding: .utf8).hasPrefix("category,amount\n"))305 #expect(try FileManager.default.contentsOfDirectory(atPath: dir.path) == ["report.csv"]) // no temporary file left beside it306 }307}308309final class Collector: @unchecked Sendable {310 private let lock = NSLock()311 private var stored: [String] = []312 func add(_ s: String) { lock.withLock { stored.append(s) } }313 var items: [String] { lock.withLock { stored } }314}315