SPB Git

Repository Public

CLITests.swift 19.5 KB 319 lines swift

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
Files main
Menu

Theme