Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetData/Tests/SheetDataTests/TransformTests.swift10// Module : SheetDataTests11// 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 SheetData9192/// Every transform step, checked against the pandas reference tables of `expected.json`.93@Suite struct TransformTests {94 let e: DataEngine9596 init() throws { e = try Fixtures.engine() }9798 private func run(_ source: String, _ steps: [TransformStep]) throws -> DataTable {99 try TransformRunner.run(TransformPipeline(name: "t", source: .dataset(source), steps: steps), engine: e)100 }101102 @Test func filters() throws {103 expectTable(try run("sales", [.filter(FilterGroup(.all, [FilterCondition("region", .isIn, ["North", "South"]), FilterCondition("quantity", .greater, 5)]))]), matches: "t_filter_all")104 expectTable(try run("sales", [.filter(FilterGroup(.any, [FilterCondition("category", .equal, "Tools"), FilterCondition("unit_price", .between, [100, 200])]))]), matches: "t_filter_any")105 expectTable(try run("sales", [.filter(FilterGroup(FilterCondition("product", .contains, "PRO", caseInsensitive: true)))]), matches: "t_filter_text")106 expectTable(try run("sales", [.filter(FilterGroup(FilterCondition("quantity", .notEqual, 3)))]), matches: "t_filter_ne")107 expectTable(try run("sales", [.filter(FilterGroup(FilterCondition("region", .notIn, ["North", "East"])))]), matches: "t_filter_notin")108 expectTable(try run("sales", [.filter(FilterGroup(.all, [FilterCondition("note", .isNull), FilterCondition("discount", .isNotNull)]))]), matches: "t_filter_null")109 expectTable(try run("sales", [.filter(FilterGroup(.all, [FilterCondition("product", .startsWith, "H"), FilterCondition("note", .endsWith, "e")]))]), matches: "t_filter_starts")110 expectTable(try run("sales", [.filter(FilterGroup(FilterCondition("product", .matchesRegex, "^[HS]a")))]), matches: "t_filter_regex")111 // two filter steps compose like one AND112 expectTable(try run("sales", [.filter(FilterGroup(FilterCondition("region", .isIn, ["North", "South"]))), .filter(FilterGroup(FilterCondition("quantity", .greater, 5)))]), matches: "t_filter_all")113 }114115 @Test func sortLimitAndColumnSteps() throws {116 expectTable(try run("sales", [.sort([SortSpec("unit_price", ascending: false), SortSpec("order_id")]), .limit(25)]), matches: "t_sort_limit", ordered: true)117 expectTable(try run("sales", [.renameColumn(from: "unit_price", to: "price"), .removeColumns(["note", "discount"]),118 .reorderColumns(["region", "order_id"]), .limit(200)]), matches: "t_columns")119 expectTable(try run("sales", [.selectColumns(["product", "order_id"]), .limit(50)]), matches: "t_select")120 }121122 @Test func changeTypePolicies() throws {123 let steps: [TransformStep] = [124 .selectColumns(["id", "amount_text", "date_text"]),125 .changeType(column: "amount_text", to: .double, onError: .null, format: nil),126 .renameColumn(from: "amount_text", to: "amount"),127 .calculatedColumn(name: "id_text", expression: "id"),128 .changeType(column: "id_text", to: .string, onError: .fail, format: nil),129 .changeType(column: "date_text", to: .date, onError: .null, format: "%d/%m/%Y"),130 .renameColumn(from: "date_text", to: "d"),131 .selectColumns(["id", "amount", "id_text", "d"]),132 ]133 let t = try run("messy", steps)134 expectTable(t, matches: "t_changetype")135 #expect(t.schema.map(\.type) == [.integer, .double, .string, .date])136 // "fail" policy: the pipeline is valid (types bind) but running it reports the first bad value137 let strict = TransformPipeline(name: "strict", source: .dataset("messy"), steps: [.changeType(column: "amount_text", to: .double, onError: .fail, format: nil)])138 #expect(TransformCompiler.validate(strict, engine: e).allSatisfy { $0.status == .ok })139 do { _ = try TransformRunner.run(strict, engine: e); Issue.record("expected a conversion error") } catch let DataError.sql(message, _) {140 #expect(message.contains("n/a") || message.contains("abc"))141 }142 let strictDate = TransformPipeline(name: "strict", source: .dataset("messy"), steps: [.parseDate(column: "date_text", format: "%d/%m/%Y", asTimestamp: false, onError: .fail)])143 #expect(throws: DataError.self) { _ = try TransformRunner.run(strictDate, engine: e) }144 // decimal and boolean targets145 let d = try run("messy", [.changeType(column: "score", to: .decimal(precision: 10, scale: 1), onError: .null, format: nil),146 .calculatedColumn(name: "flag", expression: "CASE WHEN grp = 'a' THEN 'true' WHEN grp = 'b' THEN 'no' END"),147 .changeType(column: "flag", to: .boolean, onError: .null, format: nil), .selectColumns(["score", "flag"])])148 #expect(d.schema.map(\.declaredType) == ["DECIMAL(10,1)", "BOOLEAN"])149 }150151 @Test func splitMergeExtract() throws {152 expectTable(try run("messy", [.splitColumn(column: "code", by: .delimiter("-"), into: ["p1", "p2", "p3"], keepOriginal: false), .selectColumns(["id", "p1", "p2", "p3"])]), matches: "t_split_delim")153 expectTable(try run("messy", [.splitColumn(column: "code", by: .positions([2, 3]), into: ["a", "b", "c"], keepOriginal: true), .selectColumns(["id", "a", "b", "c"])]), matches: "t_split_positions")154 expectTable(try run("messy", [.splitColumn(column: "full_name", by: .regex("^([^,]+), (.+)$"), into: ["last", "first"], keepOriginal: false), .selectColumns(["id", "last", "first"])]), matches: "t_split_regex")155 // the parts take the place of the split column156 #expect(try run("messy", [.splitColumn(column: "code", by: .delimiter("-"), into: ["p1", "p2"], keepOriginal: false)]).columnNames.prefix(4) == ["id", "full_name", "p1", "p2"])157 expectTable(try run("sales", [.mergeColumns(columns: ["region", "category"], separator: " / ", into: "rc", keepOriginals: false), .selectColumns(["order_id", "rc"]), .limit(300)]), matches: "t_merge")158 expectTable(try run("messy", [159 .extractText(column: "code", operation: .regex(pattern: "-(\\d+)", group: 1), into: "digits"),160 .extractText(column: "code", operation: .before("-"), into: "before"),161 .extractText(column: "code", operation: .after("-"), into: "after"),162 .extractText(column: "code", operation: .substring(start: 1, length: 3), into: "sub"),163 .extractText(column: "full_name", operation: .length, into: "length"),164 .extractText(column: "padded", operation: .trim, into: "trimmed"),165 .extractText(column: "padded", operation: .uppercase, into: "upper"),166 .extractText(column: "padded", operation: .lowercase, into: nil),167 .renameColumn(from: "padded", to: "lower"),168 .selectColumns(["id", "digits", "before", "after", "sub", "length", "trimmed", "upper", "lower"]),169 ]), matches: "t_extract")170 }171172 @Test func joinsOfEachKind() throws {173 func join(_ kind: JoinKind) throws -> DataTable {174 try run("sales", [.selectColumns(["order_id", "customer_id", "region", "quantity"]),175 .join(dataset: "customers", kind: kind, keys: [JoinKey("customer_id")], suffix: "_c")])176 }177 for kind in JoinKind.allCases {178 let t = try join(kind)179 expectTable(t, matches: "t_join_\(kind.rawValue)")180 }181 // row counts differ by kind, as the fixture intends (unmatched rows on both sides)182 let counts = try JoinKind.allCases.map { try join($0).rowCount }183 #expect(Set(counts).count >= 5)184 // NULL keys never match (SQL semantics), and differently named keys work185 try e.execute("CREATE TABLE lk AS SELECT * FROM (VALUES (1, 'a'), (NULL, 'b'), (3, 'c')) t(k, l); CREATE TABLE rk AS SELECT * FROM (VALUES (1, 'x'), (NULL, 'y'), (4, 'z')) t(key, r);")186 let inner = try run("lk", [.join(dataset: "rk", kind: .inner, keys: [JoinKey("k", "key")], suffix: "_r")])187 #expect(inner.rows == [[.int(1), "a", "x"]])188 #expect(try run("lk", [.join(dataset: "rk", kind: .full, keys: [JoinKey("k", "key")], suffix: "_r")]).rowCount == 5)189 #expect(try run("lk", [.join(dataset: "rk", kind: .anti, keys: [JoinKey("k", "key")], suffix: "_r")]).rowCount == 2)190 }191192 @Test func groupByAggregations() throws {193 let t = try run("sales", [.groupBy(keys: ["region", "category"], aggregations: [194 Aggregation(.sum, "quantity", as: "sum_q"), Aggregation(.count, "quantity", as: "cnt_q"), Aggregation(.average, "unit_price", as: "avg_p"),195 Aggregation(.min, "unit_price", as: "min_p"), Aggregation(.max, "unit_price", as: "max_p"), Aggregation(.stddev, "unit_price", as: "sd_p"),196 Aggregation(.variance, "unit_price", as: "var_p"), Aggregation(.median, "unit_price", as: "med_p"),197 Aggregation(.distinctCount, "customer_id", as: "dc"), Aggregation(.countRows, as: "n"),198 ])])199 expectTable(t, matches: "t_groupby")200 expectTable(try run("sales", [.sort([SortSpec("order_id")]), .groupBy(keys: ["category"], aggregations: [Aggregation(.first, "product", as: "first_p"), Aggregation(.last, "product", as: "last_p")])]), matches: "t_groupby_firstlast")201 // first/last follow the active sort, whatever it is202 let reversed = try run("sales", [.sort([SortSpec("order_id", ascending: false)]), .groupBy(keys: ["category"], aggregations: [Aggregation(.last, "product", as: "first_p"), Aggregation(.first, "product", as: "last_p")])])203 expectTable(reversed, matches: "t_groupby_firstlast")204 expectTable(try run("sales", [.groupBy(keys: [], aggregations: [Aggregation(.sum, "quantity", as: "total"), Aggregation(.countRows, as: "n")])]), matches: "t_groupby_global")205 #expect(try run("sales", [.groupBy(keys: ["region"], aggregations: [Aggregation(.sum, "quantity")])]).columnNames == ["region", "sum_quantity"])206 }207208 @Test func pivotAndUnpivot() throws {209 expectTable(try run("sales", [.filter(FilterGroup(FilterCondition("region", .isNotNull))),210 .pivot(rowKeys: ["region"], column: "category", value: "quantity", aggregation: .sum)]), matches: "t_pivot")211 expectTable(try run("sales", [.selectColumns(["order_id", "quantity", "unit_price"]), .limit(40),212 .unpivot(columns: ["quantity", "unit_price"], nameColumn: "metric", valueColumn: "val")]), matches: "t_unpivot")213 // pivot then unpivot gets back to the grouped long form214 let long = try run("sales", [.filter(FilterGroup(FilterCondition("region", .isNotNull))),215 .pivot(rowKeys: ["region"], column: "category", value: "quantity", aggregation: .sum),216 .unpivot(columns: ["Garden", "Kitchen", "Office", "Tools", "Toys"], nameColumn: "category", valueColumn: "q"),217 .sort([SortSpec("region"), SortSpec("category")])])218 let direct = try e.query("SELECT region, category, CAST(sum(quantity) AS HUGEINT) AS q FROM sales WHERE region IS NOT NULL GROUP BY ALL ORDER BY 1, 2")219 #expect(long.rows.map { $0.map(Canon.init) } == direct.rows.map { $0.map(Canon.init) })220 }221222 @Test func duplicatesAndMissingValues() throws {223 expectTable(try run("messy", [.removeDuplicates(columns: ["full_name"])]), matches: "t_dedupe_cols")224 expectTable(try run("messy", [.selectColumns(["grp", "code"]), .removeDuplicates(columns: [])]), matches: "t_dedupe_all")225 expectTable(try run("messy", [.fillMissing(columns: ["score"], strategy: .constant(-1.0)), .fillMissing(columns: ["grp"], strategy: .constant("?")), .selectColumns(["id", "score", "grp"])]), matches: "t_fill_const")226 expectTable(try run("messy", [.fillMissing(columns: ["score"], strategy: .previous), .selectColumns(["id", "score"])]), matches: "t_fill_prev")227 expectTable(try run("messy", [.fillMissing(columns: ["score"], strategy: .next), .selectColumns(["id", "score"])]), matches: "t_fill_next")228 expectTable(try run("messy", [.fillMissing(columns: ["score"], strategy: .mean), .selectColumns(["id", "score"])]), matches: "t_fill_mean")229 expectTable(try run("messy", [.fillMissing(columns: ["score"], strategy: .median), .selectColumns(["id", "score"])]), matches: "t_fill_median")230 expectTable(try run("messy", [.sort([SortSpec("grp"), SortSpec("id")]), .fillMissing(columns: ["score"], strategy: .previous), .selectColumns(["id", "grp", "score"])]),231 matches: "t_fill_prev_sorted", ordered: true)232 expectTable(try run("messy", [.dropMissing(columns: ["score", "grp"], mode: .any)]), matches: "t_drop_any")233 expectTable(try run("messy", [.dropMissing(columns: ["score", "grp"], mode: .all)]), matches: "t_drop_all")234 expectTable(try run("messy", [.dropMissing(columns: [], mode: .any)]), matches: "t_drop_everything")235 // a mean cannot fill a text column: reported at the step236 let bad = TransformPipeline(name: "bad", source: .dataset("messy"), steps: [.fillMissing(columns: ["grp"], strategy: .mean)])237 #expect(TransformCompiler.validate(bad, engine: e).last?.status == .error)238 }239240 @Test func replaceParseCalculateAppendSample() throws {241 expectTable(try run("sales", [.replaceValues(column: "region", replacements: [Replacement("North", "N"), Replacement("South", "S")], matchSubstring: false),242 .selectColumns(["order_id", "region"]), .limit(300)]), matches: "t_replace_whole")243 expectTable(try run("sales", [.replaceValues(column: "product", replacements: [Replacement("Pro", "Professional"), Replacement(" ", "_")], matchSubstring: true),244 .selectColumns(["order_id", "product"]), .limit(300)]), matches: "t_replace_sub")245 // replacing NULL itself246 let nulls = try run("sales", [.replaceValues(column: "region", replacements: [Replacement(.null, "Unknown")], matchSubstring: false), .filter(FilterGroup(FilterCondition("region", .equal, "Unknown")))])247 #expect(nulls.rowCount == 25)248 let parsed = try run("messy", [.parseDate(column: "date_text", format: "%d/%m/%Y", asTimestamp: false, onError: .null), .selectColumns(["id", "date_text"])])249 expectTable(parsed, matches: "t_parsedate")250 #expect(parsed.schema[1].type == .date)251 #expect(try run("messy", [.parseDate(column: "date_text", format: "%d/%m/%Y", asTimestamp: true, onError: .null)]).column("date_text")?.type == .timestamp)252 expectTable(try run("sales", [.calculatedColumn(name: "revenue", expression: "quantity * unit_price * (1 - coalesce(discount, 0))"),253 .selectColumns(["order_id", "revenue"]), .limit(400)]), matches: "t_calc")254 // a calculated column may replace an existing one, in place255 let replaced = try run("sales", [.calculatedColumn(name: "quantity", expression: "quantity * 2"), .limit(1)])256 #expect(replaced.columnNames == (try e.schema(of: "sales").map(\.name)))257 expectTable(try run("sales", [.filter(FilterGroup(FilterCondition("region", .equal, "West"))), .selectColumns(["order_id", "region", "quantity"]),258 .appendDataset("sales_extra")]), matches: "t_append")259 // sampling: right size, reproducible, a subset of the source260 let s1 = try run("sales", [.sample(.rows(100), seed: 42)]), s2 = try run("sales", [.sample(.rows(100), seed: 42)])261 #expect(s1.rowCount == 100)262 #expect(s1.rows.map { $0[0] } == s2.rows.map { $0[0] })263 #expect(Set(s1.rows.map { $0[0] }).count == 100)264 let pct = try run("sales", [.sample(.percent(10), seed: 7)])265 #expect(pct.rowCount > 100 && pct.rowCount < 320)266 }267268 @Test func multiStepPipelineAndPreviewAtAnyStep() throws {269 let p = TransformPipeline(name: "Online revenue", source: .dataset("sales"), steps: [270 .filter(FilterGroup(.all, [FilterCondition("is_online", .equal, true), FilterCondition("quantity", .isNotNull)])),271 .calculatedColumn(name: "revenue", expression: "quantity * unit_price * (1 - coalesce(discount, 0))"),272 .groupBy(keys: ["region", "category"], aggregations: [Aggregation(.sum, "revenue", as: "rev"), Aggregation(.count, "order_id", as: "orders")]),273 .sort([SortSpec("rev", ascending: false), SortSpec("region"), SortSpec("category")]),274 .limit(7),275 ])276 expectTable(try TransformRunner.run(p, engine: e), matches: "t_multi", ordered: true)277 // schema after each step278 #expect(try TransformCompiler.schema(after: -1, of: p, engine: e).count == 11)279 #expect(try TransformCompiler.schema(after: 1, of: p, engine: e).last?.name == "revenue")280 #expect(try TransformCompiler.schema(after: 2, of: p, engine: e).map(\.name) == ["region", "category", "rev", "orders"])281 // preview at any step282 #expect(try TransformRunner.preview(p, upTo: 0, limit: 5, engine: e).columnCount == 11)283 #expect(try TransformRunner.preview(p, upTo: 3, offset: 0, limit: 1000, engine: e).rowCount > 7)284 let full = try TransformRunner.preview(p, upTo: 4, offset: 0, limit: 1000, engine: e)285 let page = try TransformRunner.preview(p, upTo: 4, offset: 3, limit: 2, engine: e)286 #expect(page.rows == Array(full.rows[3..<5])) // the sort order survives windowing287 // the compiled SQL is one statement: a CTE chain288 let sql = try TransformCompiler.sql(for: p, engine: e)289 #expect(sql.hasPrefix("WITH s0 AS (") && sql.contains("s5 AS ("))290 #expect(try e.query(sql).rowCount == 7)291 // outputs: dataset, file, and the result equals the run292 let info = try TransformRunner.refresh(p, into: "online_rev", engine: e)293 #expect(info.rowCount == 7 && info.kind == .table)294 let dir = Fixtures.temporaryDirectory()295 defer { try? FileManager.default.removeItem(at: dir) }296 #expect(try TransformRunner.export(p, to: dir.appendingPathComponent("r.parquet"), format: .parquet, engine: e) == 7)297 }298299 @Test func validationPinpointsTheBrokenStep() throws {300 var p = TransformPipeline(name: "v", source: .dataset("sales"), steps: [301 .renameColumn(from: "unit_price", to: "price"),302 .filter(FilterGroup(FilterCondition("price", .greater, 100))),303 .groupBy(keys: ["region"], aggregations: [Aggregation(.sum, "price", as: "total")]),304 .sort([SortSpec("total")]),305 ])306 var d = TransformCompiler.validate(p, engine: e)307 #expect(d.map(\.status) == [.ok, .ok, .ok, .ok, .ok])308 #expect(d.map(\.step) == [-1, 0, 1, 2, 3])309 #expect(d[1].schema?.contains { $0.name == "price" } == true)310 #expect(d[3].schema?.map(\.name) == ["region", "total"])311 // deleting the rename breaks the filter (step 0 now), and everything after is blocked312 p.steps.remove(at: 0)313 d = TransformCompiler.validate(p, engine: e)314 #expect(d.map(\.status) == [.ok, .error, .blocked, .blocked])315 #expect(d[1].message?.contains("price") == true)316 #expect(throws: DataError.self) { _ = try TransformCompiler.sql(for: p, engine: e) }317 // the valid prefix can still be previewed318 #expect(try TransformRunner.preview(p, upTo: 0, limit: 3, engine: e).rowCount == 3)319 // reordering: sorting before the aggregation refers to a column that does not exist yet320 p = TransformPipeline(name: "v", source: .dataset("sales"), steps: [321 .groupBy(keys: ["region"], aggregations: [Aggregation(.sum, "quantity", as: "total")]),322 .sort([SortSpec("total")]),323 ])324 #expect(TransformCompiler.validate(p, engine: e).allSatisfy { $0.status == .ok })325 p.moveStep(from: 1, to: 0)326 d = TransformCompiler.validate(p, engine: e)327 #expect(d[1].status == .error && d[2].status == .blocked)328 // type mismatches are caught by DuckDB's binder and attributed to the step329 let mismatch = TransformPipeline(name: "m", source: .dataset("sales"), steps: [330 .limit(10), .calculatedColumn(name: "x", expression: "region + 1"), .limit(1),331 ])332 d = TransformCompiler.validate(mismatch, engine: e)333 #expect(d.map(\.status) == [.ok, .ok, .error, .blocked])334 let unknownFunction = TransformPipeline(name: "m", source: .dataset("sales"), steps: [.calculatedColumn(name: "x", expression: "no_such_fn(region)")])335 #expect(TransformCompiler.validate(unknownFunction, engine: e).last?.status == .error)336 // structural errors337 for step: TransformStep in [.renameColumn(from: "region", to: "category"), .removeColumns(try e.schema(of: "sales").map(\.name)),338 .join(dataset: "nope", kind: .inner, keys: [JoinKey("customer_id")], suffix: "_r"),339 .join(dataset: "customers", kind: .left, keys: [JoinKey("customer_id", "nope")], suffix: "_r"),340 .join(dataset: "customers", kind: .left, keys: [JoinKey("customer_id")], suffix: ""),341 .appendDataset("nope"), .splitColumn(column: "product", by: .positions([3]), into: ["a", "b", "c"], keepOriginal: true),342 .groupBy(keys: ["region"], aggregations: [Aggregation(.sum, "quantity", as: "region")]), .limit(-1)] {343 let bad = TransformPipeline(name: "b", source: .dataset("sales"), steps: [step])344 #expect(TransformCompiler.validate(bad, engine: e).last?.status == .error, "\(step.title)")345 }346 let missing = TransformPipeline(name: "b", source: .dataset("nope"), steps: [.limit(1)])347 #expect(TransformCompiler.validate(missing, engine: e).map(\.status) == [.error, .blocked])348 }349350 @Test func fileSourceRefreshAndStore() throws {351 let dir = Fixtures.temporaryDirectory()352 defer { try? FileManager.default.removeItem(at: dir) }353 let file = dir.appendingPathComponent("data.csv")354 try "k,v\na,1\nb,2\n".write(to: file, atomically: true, encoding: .utf8)355 let p = TransformPipeline(name: "file", source: .file(url: file, format: nil, options: ImportOptions()), steps: [356 .groupBy(keys: [], aggregations: [Aggregation(.sum, "v", as: "total")]),357 ])358 // not loaded yet: validation says so, running loads the source on demand359 #expect(TransformCompiler.validate(p, engine: e).first?.status == .error)360 #expect(try TransformRunner.run(p, engine: e).value(row: 0, column: 0).doubleValue == 3)361 #expect(try e.dataset(p.sourceRelationName).kind == .view) // lazy scan, not a copy362 // the file changes; a refresh re-reads it363 try "k,v\na,1\nb,2\nc,10\n".write(to: file, atomically: true, encoding: .utf8)364 #expect(try TransformRunner.run(p, engine: e).value(row: 0, column: 0).doubleValue == 3) // snapshot until refreshed365 let info = try TransformRunner.refresh(p, into: "file_total", engine: e)366 #expect(info.rowCount == 1)367 #expect(try e.query("SELECT total FROM file_total").value(row: 0, column: 0).doubleValue == 13)368 // pipelines are stored as workbook attachments369 let wb = Workbook(sheetNames: ["Sheet1"])370 try TransformStore.save(p, in: wb)371 #expect(wb.attachments.keys.contains("transforms/\(p.id.uuidString).json"))372 #expect(try TransformStore.load(p.id, from: wb) == p)373 var every = TransformPipeline(name: "every step", source: .range(sheetID: 1, range: RangeRef(r0: 0, c0: 0, r1: 9, c1: 3), hasHeader: true, dateColumns: [2]), steps: [])374 every.steps = [375 .filter(FilterGroup(.any, [FilterCondition("a", .between, [1, .double(2.5)]), FilterCondition("b", .isIn, ["x", .null, .date(year: 2024, month: 1, day: 2)])])),376 .sort([SortSpec("a", ascending: false, nullsFirst: true)]), .renameColumn(from: "a", to: "b"), .removeColumns(["a"]), .selectColumns(["a"]),377 .reorderColumns(["a"]), .changeType(column: "a", to: .decimal(precision: 10, scale: 2), onError: .fail, format: "%Y"),378 .splitColumn(column: "a", by: .positions([1, 2]), into: ["x", "y", "z"], keepOriginal: true),379 .splitColumn(column: "a", by: .regex("(a)(b)"), into: ["x", "y"], keepOriginal: false),380 .mergeColumns(columns: ["a", "b"], separator: "-", into: "c", keepOriginals: true),381 .join(dataset: "d", kind: .full, keys: [JoinKey("a", "b")], suffix: "_r"),382 .groupBy(keys: ["a"], aggregations: AggregateFunction.allCases.map { Aggregation($0, "b") }),383 .pivot(rowKeys: ["a"], column: "b", value: "c", aggregation: .median), .unpivot(columns: ["a"], nameColumn: "n", valueColumn: "v"),384 .removeDuplicates(columns: ["a"]), .fillMissing(columns: ["a"], strategy: .constant(.timestamp(1))), .fillMissing(columns: [], strategy: .previous),385 .dropMissing(columns: [], mode: .all), .replaceValues(column: "a", replacements: [Replacement(.null, 1)], matchSubstring: false),386 .parseDate(column: "a", format: "%d", asTimestamp: true, onError: .null), .extractText(column: "a", operation: .substring(start: 1, length: nil), into: "b"),387 .extractText(column: "a", operation: .regex(pattern: "x", group: 0), into: nil), .calculatedColumn(name: "x", expression: "1 + 1"),388 .limit(5), .sample(.percent(12.5), seed: 3), .sample(.rows(5), seed: 3), .appendDataset("other"),389 ]390 try TransformStore.save(every, in: wb)391 #expect(try TransformStore.load(every.id, from: wb) == every) // Codable round trip of every step392 #expect(TransformStore.all(in: wb).map(\.name) == ["every step", "file"])393 #expect(every.steps.allSatisfy { !$0.title.isEmpty })394 TransformStore.delete(p.id, from: wb)395 #expect(TransformStore.all(in: wb).count == 1)396 }397}398