SPB Git

Repository Public

TransformTests.swift 29.0 KB 398 lines swift

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

Theme