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/CLITests.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 PDFKit90import SheetCore91import SheetIO92@testable import SheetAutomation9394/// End-to-end tests: the built `orix-sheet` binary is run as a subprocess.95@Suite struct CLITests {96 struct Result { var status: Int32; var out: String; var err: String }9798 static let binary: URL = {99 // the test bundle sits next to the built products; fall back to the package's build folders100 let fm = FileManager.default101 var candidates = [Bundle(for: BundleMarker.self).bundleURL.deletingLastPathComponent().appendingPathComponent("orix-sheet")]102 let package = URL(fileURLWithPath: #filePath).deletingLastPathComponent().deletingLastPathComponent().deletingLastPathComponent()103 candidates += [".build/debug/orix-sheet", ".build/out/Products/Debug/orix-sheet"].map { package.appendingPathComponent($0) }104 return candidates.first { fm.isExecutableFile(atPath: $0.path) } ?? candidates[0]105 }()106107 func cli(_ args: String..., cwd: URL) throws -> Result {108 _ = TestEnv.support109 let p = Process()110 p.executableURL = Self.binary111 p.arguments = args112 p.currentDirectoryURL = cwd113 var env = ProcessInfo.processInfo.environment114 env["ORIX_SHEET_SUPPORT_DIR"] = TestEnv.support.appendingPathComponent("cli").path115 p.environment = env116 let out = Pipe(), err = Pipe()117 p.standardOutput = out; p.standardError = err118 p.standardInput = FileHandle.nullDevice119 try p.run()120 let o = out.fileHandleForReading.readDataToEndOfFile(), e = err.fileHandleForReading.readDataToEndOfFile()121 p.waitUntilExit()122 return Result(status: p.terminationStatus, out: String(decoding: o, as: UTF8.self), err: String(decoding: e, as: UTF8.self))123 }124125 @Test func binaryExists() { #expect(FileManager.default.isExecutableFile(atPath: Self.binary.path)) }126127 @Test func helpVersionAndUsageErrors() throws {128 let dir = TestEnv.tempDir()129 let help = try cli("help", cwd: dir)130 #expect(help.status == 0)131 for c in ["convert", "recalc", "eval", "info", "audit", "sql", "run", "functions", "version"] {132 #expect(help.out.contains(" \(c) "), "\(c)")133 let h = try cli(c, "--help", cwd: dir)134 #expect(h.status == 0 && h.out.hasPrefix("Usage: orix-sheet \(c)"), "\(c)")135 }136 let version = try cli("version", cwd: dir)137 #expect(version.status == 0 && version.out.hasPrefix("orix-sheet 1.") && version.out.contains("worksheet functions"))138 #expect(try cli(cwd: dir).status == 64)139 let unknown = try cli("frobnicate", cwd: dir)140 #expect(unknown.status == 64 && unknown.err.contains("Unknown command"))141 let badOption = try cli("info", "x.xlsx", "--nope", cwd: dir)142 #expect(badOption.status == 64 && badOption.err.contains("Unknown option --nope"))143 #expect(try cli("convert", "only-one", cwd: dir).status == 64)144 let missing = try cli("info", "absent.xlsx", cwd: dir)145 #expect(missing.status == 1 && missing.err.contains("Cannot open absent.xlsx") && missing.out.isEmpty)146 }147148 @Test func convertBetweenFormats() throws {149 let dir = TestEnv.tempDir()150 TestEnv.modelXLSX(dir)151 let csv = try cli("convert", "model.xlsx", "data.csv", "--sheet", "Data", cwd: dir)152 #expect(csv.status == 0 && csv.out.contains("[loss] Sheets") && csv.out.contains("Wrote"))153 #expect(try String(contentsOf: dir.appendingPathComponent("data.csv"), encoding: .utf8) == "category,amount\nHousing,1200\nFood,450.25\nTransport,89.9\n")154 let summary = try cli("convert", "model.xlsx", "summary.tsv", "--sheet", "2", cwd: dir)155 #expect(summary.status == 0 && summary.out.contains("[loss] Formulas"))156 #expect(try String(contentsOf: dir.appendingPathComponent("summary.tsv"), encoding: .utf8) == "Total\t1740.15\nRows\t3\n")157 for target in ["a.ods", "a.json", "a.orixsheet", "b.xlsx"] {158 #expect(try cli("convert", "data.csv", target, cwd: dir).status == 0, "\(target)")159 #expect(try TestEnv.load(dir.appendingPathComponent(target)).sheets[0].value(3, 1) == .number(89.9), "\(target)")160 }161 let strict = try cli("convert", "model.xlsx", "strict.csv", "--fail-on-loss", cwd: dir)162 #expect(strict.status == 2 && strict.err.contains("nothing was written") && !TestEnv.exists(dir.appendingPathComponent("strict.csv")))163 #expect(try cli("convert", "data.csv", "ok.xlsx", "--fail-on-loss", cwd: dir).status == 0)164 let bad = try cli("convert", "model.xlsx", "out.numbers", cwd: dir)165 #expect(bad.status == 1 && bad.err.contains("cannot be written"))166 #expect(try cli("convert", "model.xlsx", "x.csv", "--sheet", "Nope", cwd: dir).status == 1)167 }168169 @Test func convertToPDFWorksFromACommandLineProcess() throws {170 let dir = TestEnv.tempDir()171 TestEnv.modelXLSX(dir)172 let r = try cli("convert", "model.xlsx", "model.pdf", cwd: dir)173 #expect(r.status == 0 && r.out.contains("(pdf, 2 pages)"), "\(r.err)")174 let pdf = try #require(PDFDocument(url: dir.appendingPathComponent("model.pdf")))175 #expect(pdf.pageCount == 2 && (pdf.string ?? "").contains("Housing") && (pdf.string ?? "").contains("Total"))176 let one = try cli("convert", "model.xlsx", "summary.pdf", "--sheet", "Summary", cwd: dir)177 #expect(one.status == 0 && PDFDocument(url: dir.appendingPathComponent("summary.pdf"))?.pageCount == 1)178 }179180 @Test func recalcEvalInfo() throws {181 let dir = TestEnv.tempDir()182 // a workbook whose stored values are stale: edit the data without recalculating183 let wb = TestEnv.modelWorkbook()184 wb.sheets[0].cells.set(row: 1, col: 1, Cell(value: .number(2000)))185 wb.sheets[1].cells.set(row: 4, col: 0, Cell(formula: Formula(expr: try FormulaParser.parse("=1/0"))))186 wb.sheets[1].cells.set(row: 5, col: 0, Cell(formula: Formula(expr: try FormulaParser.parse("=MYSTERY(1)+UPPER(\"a\")"))))187 try WorkbookWriter.save(wb, to: dir.appendingPathComponent("stale.xlsx"), format: .xlsx)188 #expect(try cli("eval", "stale.xlsx", "Summary!B1", cwd: dir).out == "1740.15\n")189 #expect(try cli("eval", "stale.xlsx", "Summary!B1", "--recalc", cwd: dir).out == "2540.15\n")190 let recalc = try cli("recalc", "stale.xlsx", "--out", "fresh.xlsx", cwd: dir)191 #expect(recalc.status == 0 && recalc.out.contains("Formulas: 4") && recalc.out.contains("Error values: 2") && recalc.out.contains("Summary!A5 #DIV/0!"))192 #expect(try cli("eval", "fresh.xlsx", "Summary!B1", cwd: dir).out == "2540.15\n")193 #expect(try cli("eval", "stale.xlsx", "Summary!B1", cwd: dir).out == "1740.15\n") // --out left the source alone194 #expect(try cli("recalc", "stale.xlsx", cwd: dir).status == 0) // in place195 #expect(try cli("eval", "stale.xlsx", "B1", "--sheet", "Summary", cwd: dir).out == "2540.15\n")196197 #expect(try cli("eval", "fresh.xlsx", "=SUM(Data!B:B)*2", cwd: dir).out == "5080.3\n")198 #expect(try cli("eval", "fresh.xlsx", "Data!A1:B2", cwd: dir).out == "category\tamount\nHousing\t2000\n")199 #expect(try cli("eval", "fresh.xlsx", "=SEQUENCE(2,2)", cwd: dir).out == "1\t2\n3\t4\n")200 let json = try cli("eval", "fresh.xlsx", "Data!A2:B3", "--json", cwd: dir)201 let parsed = try #require(try JSONSerialization.jsonObject(with: Data(json.out.utf8)) as? [String: Any])202 #expect(parsed["expression"] as? String == "Data!A2:B3")203 #expect((parsed["values"] as? [[Any]])?.count == 2 && ((parsed["values"] as? [[Any]])?[1][1] as? Double) == 450.25)204 let single = try cli("eval", "fresh.xlsx", "Summary!A5", "--json", cwd: dir)205 #expect(single.out.contains("\"value\" : \"#DIV/0!\""))206 #expect(try cli("eval", "fresh.xlsx", "=SUM(", cwd: dir).status == 1)207208 let info = try cli("info", "fresh.xlsx", cwd: dir)209 #expect(info.status == 0)210 for needle in ["Sheets: 2", "Data A1:B4", "Formulas: 4", "SUM×1", "Unsupported functions: MYSTERY", "Macros: no", "External links: no"] {211 #expect(info.out.contains(needle), "missing “\(needle)” in:\n\(info.out)")212 }213 let infoJSON = try #require(try JSONSerialization.jsonObject(with: Data(try cli("info", "fresh.xlsx", "--json", cwd: dir).out.utf8)) as? [String: Any])214 #expect(infoJSON["formulas"] as? Int == 4 && infoJSON["unsupportedFunctions"] as? [String] == ["MYSTERY"] && infoJSON["hasMacros"] as? Bool == false)215 #expect((infoJSON["sheets"] as? [[String: Any]])?.map { $0["name"] as? String } == ["Data", "Summary"])216 }217218 @Test func auditExitCodes() throws {219 let dir = TestEnv.tempDir()220 TestEnv.modelXLSX(dir, name: "clean.xlsx")221 let wb = TestEnv.modelWorkbook()222 let engine = CalcEngine(workbook: wb)223 engine.setFormula(sheet: 1, row: 4, col: 0, "=1/0")224 engine.recalculate()225 try WorkbookWriter.save(wb, to: dir.appendingPathComponent("errors.xlsx"), format: .xlsx)226 let md = try cli("audit", "errors.xlsx", cwd: dir)227 #expect(md.status == 0 && md.out.contains("Error value"))228 let failing = try cli("audit", "errors.xlsx", "--fail-on", "error", cwd: dir)229 #expect(failing.status == 2 && failing.err.contains("severity error or worse"))230 #expect(try cli("audit", "clean.xlsx", "--fail-on", "error", cwd: dir).status == 0)231 let json = try cli("audit", "errors.xlsx", "--format", "json", cwd: dir)232 let report = try #require(try JSONSerialization.jsonObject(with: Data(json.out.utf8)) as? [String: Any])233 #expect((report["findings"] as? [[String: Any]])?.contains { $0["rule"] as? String == "errorValue" } == true)234 #expect(try cli("audit", "errors.xlsx", "--recalc", "--fail-on", "warning", cwd: dir).status == 2)235 #expect(try cli("audit", "clean.xlsx", "--format", "xml", cwd: dir).status == 64)236 #expect(try cli("audit", "clean.xlsx", "--fail-on", "info", cwd: dir).status == 64)237 }238239 @Test func sqlOverADataFile() throws {240 let dir = TestEnv.tempDir()241 TestEnv.salesCSV(dir)242 let r = try cli("sql", "sales.csv", "SELECT trim(category) AS c, count(*) AS n FROM data WHERE amount > 200 GROUP BY 1 ORDER BY 1", cwd: dir)243 #expect(r.status == 0, "\(r.err)")244 #expect(r.out == "c n\nFood 3\nHousing 3\nLeisure 4\nTransport 4\n")245 let json = try cli("sql", "sales.csv", "SELECT count(*) AS rows, max(amount) AS top FROM data", "--json", cwd: dir)246 let rows = try #require(try JSONSerialization.jsonObject(with: Data(json.out.utf8)) as? [[String: Any]])247 #expect(rows.first?["rows"] as? Int == 26 && rows.first?["top"] as? Double == 330.5)248 let out = try cli("sql", "sales.csv", "SELECT * FROM data WHERE amount IS NOT NULL ORDER BY amount DESC LIMIT 5", "--out", "top.parquet", cwd: dir)249 #expect(out.status == 0 && out.out.contains("Wrote 5 rows"))250 #expect(try cli("sql", "top.parquet", "SELECT count(*) FROM data", cwd: dir).out.hasSuffix("5\n"))251 let limited = try cli("sql", "sales.csv", "SELECT amount FROM data", "--limit", "3", cwd: dir)252 #expect(limited.out.split(separator: "\n").count == 4 && limited.err.contains("first 3 rows"))253 let bad = try cli("sql", "sales.csv", "SELECT nope FROM data", cwd: dir)254 #expect(bad.status == 1 && bad.err.contains("nope"))255 // the sandboxed engine cannot reach other files or the network256 let escape = try cli("sql", "sales.csv", "SELECT * FROM read_csv('/etc/passwd')", cwd: dir)257 #expect(escape.status == 1)258 #expect(try cli("sql", "sales.csv", "SELECT 1", "--out", "x.txt", cwd: dir).status == 64)259 }260261 @Test func functionsCatalogue() throws {262 let dir = TestEnv.tempDir()263 let all = try cli("functions", cwd: dir)264 #expect(all.status == 0 && all.out.contains("VLOOKUP") && all.out.contains("IFERROR") && all.out.split(separator: "\n").count > 400)265 let financial = try cli("functions", "--category", "financial", "--json", cwd: dir)266 let list = try #require(try JSONSerialization.jsonObject(with: Data(financial.out.utf8)) as? [[String: Any]])267 #expect(list.count > 20 && list.allSatisfy { $0["category"] as? String == "Financial" })268 #expect(list.contains { $0["name"] as? String == "PMT" && ($0["signature"] as? String)?.hasPrefix("PMT(") == true })269 #expect(try cli("functions", "--category", "astrology", cwd: dir).status == 64)270 }271272 @Test func runWorkflowEndToEnd() throws {273 let dir = TestEnv.tempDir()274 TestEnv.salesCSV(dir)275 #expect(try cli("templates", "--export", "CSV report", "--out", "report.orixflow", cwd: dir).status == 0)276 let check = try cli("run", "report.orixflow", "--validate-only", cwd: dir)277 #expect(check.status == 1 && check.err.contains("Parameter “CSV file” needs a value"))278 #expect(try cli("run", "report.orixflow", "--param", "source=sales.csv", "--validate-only", cwd: dir).status == 0)279280 let run = try cli("run", "report.orixflow", "--param", "source=sales.csv", "--param", "output=out/report.pdf", "--json", cwd: dir)281 #expect(run.status == 0, "\(run.err)")282 #expect(run.err.contains("[1/9] New workbook…") && run.err.contains("[9/9] Export PDF: succeeded"))283 let summary = try #require(try JSONSerialization.jsonObject(with: Data(run.out.utf8)) as? [String: Any])284 #expect(summary["status"] as? String == "succeeded" && (summary["stepResults"] as? [[String: Any]])?.count == 9)285 #expect((summary["parameterValues"] as? [String: String])?["category"] == "category")286 #expect(PDFDocument(url: dir.appendingPathComponent("out/report.pdf"))?.string?.contains("Sum of amount") == true)287288 // a failing run: exit 1, a message, and no output289 let failed = try cli("run", "report.orixflow", "--param", "source=sales.csv", "--param", "value=nope", "--param", "output=bad.pdf", cwd: dir)290 #expect(failed.status == 1 && failed.out.contains("CSV report: failed") && !TestEnv.exists(dir.appendingPathComponent("bad.pdf")))291 #expect(try cli("run", "report.orixflow", "--param", "broken", cwd: dir).status == 64)292 #expect(try cli("run", "absent.orixflow", cwd: dir).status == 1)293294 // history was recorded in the redirected support folder, not in the user's library295 let history = TestEnv.support.appendingPathComponent("cli/workflow-history.jsonl")296 #expect((try? String(contentsOf: history, encoding: .utf8))?.contains("\"status\":\"succeeded\"") == true)297 }298299 @Test func runOnAWorkbookWithBaseDirAndContinue() throws {300 let dir = TestEnv.tempDir(), elsewhere = TestEnv.tempDir("cwd")301 TestEnv.modelXLSX(dir)302 let wf = Workflow(name: "On workbook", parameters: [WorkflowParameter(name: "note", defaultValue: "checked")], steps: [303 WorkflowItem(.setCell(SetCellSettings(sheet: "Summary", cell: "D1", input: "{{note}}"))),304 WorkflowItem(.sort(SortSettings(range: "Data!*", keys: ["ghost"])), onError: .continue),305 WorkflowItem(.exportFile(ExportFileSettings(path: "exports/{{workbook}}-{{note}}.xlsx"))),306 ])307 try WorkflowStore.exportFile(wf, to: dir.appendingPathComponent("wb.orixflow"))308 let r = try cli("run", dir.appendingPathComponent("wb.orixflow").path, "--workbook", dir.appendingPathComponent("model.xlsx").path, "--base-dir", dir.path, "--no-history", cwd: elsewhere)309 #expect(r.status == 2, "\(r.err)") // finished, with a failed step310 #expect(r.out.contains("completedWithErrors") && r.out.contains("exports/model-checked.xlsx"))311 #expect(TestEnv.value(try TestEnv.load(dir.appendingPathComponent("exports/model-checked.xlsx")), "Summary", "D1") == .text("checked"))312 #expect(try FileManager.default.contentsOfDirectory(atPath: elsewhere.path).isEmpty)313 let standalone = try cli("run", dir.appendingPathComponent("wb.orixflow").path, "--base-dir", dir.path, cwd: elsewhere)314 #expect(standalone.status == 1 && standalone.err.contains("needs a workbook"))315 }316}317318final class BundleMarker {}319