SPB Git

Repository Public License

DataTests.swift 49.0 KB 648 lines swift

1// ORIX Convert — Packages/EngineData/Tests/EngineDataTests/DataTests.swift2// Description: Arrow conformance, databases (tables, views, hostile names), Parquet options, nested values,3//              malformed inputs for every reader, the DuckDB sandbox, cancellation, memory limits and the engine's4//              declared contract.5// Author: Simon-Pierre Boucher — contact@spboucher.ai6import Foundation7import Testing8import SQLite39import ConvertCore10@testable import EngineData1112func resource(_ name: String) throws -> URL {13    let base = try #require(Bundle.module.resourceURL)14    return base.appendingPathComponent("Fixtures").appendingPathComponent(name)15}1617final class Canceller: @unchecked Sendable {18    private let lock = NSLock()19    private var action: (@Sendable () -> Void)?20    private var fired = false21    func arm(_ a: @escaping @Sendable () -> Void) { lock.lock(); action = a; let now = fired; lock.unlock(); if now { a() } }22    func fire() { lock.lock(); fired = true; let a = action; lock.unlock(); a?() }23}2425func sqliteExec(_ url: URL, _ statements: [String]) throws {26    var db: OpaquePointer?27    guard sqlite3_open(url.path, &db) == SQLITE_OK else { throw ConversionError.writeFailed("sqlite open") }28    defer { sqlite3_close(db) }29    for s in statements { guard sqlite3_exec(db, s, nil, nil, nil) == SQLITE_OK else { throw ConversionError.writeFailed(String(cString: sqlite3_errmsg(db))) } }30}3132@Suite("Data formats")33struct DataTests {34    // MARK: Engine contract3536    @Test func declaredPathsAreConsistent() {37        let engine = DataEngine()38        let paths = engine.paths39        #expect(Set(paths.map(\.id)).count == paths.count)40        #expect(paths.count == (libreOfficeAvailable ? 219 : 191))41        #expect(paths.allSatisfy { $0.engine == "data" && $0.operation == nil && $0.status == .supported && !$0.acceptsMultipleSources })42        #expect(!paths.contains { $0.source == .xlsx && $0.destination == .xlsx } && !paths.contains { $0.source == .sqlite && $0.destination == .sqlite })43        #expect(paths.first { $0.source == .parquet && $0.destination == .duckdb }?.fidelity == .lossless)44        #expect(paths.first { $0.source == .json && $0.destination == .yaml }?.fidelity == .lossless)45        #expect(paths.first { $0.source == .xlsx && $0.destination == .csv }?.fidelity == .partial)46        #expect(paths.first { $0.source == .xlsx && $0.destination == .ods }?.preserves == [.tables, .multipleSheets])47        #expect(paths.filter { $0.fidelity != .lossless }.allSatisfy { !$0.limitations.isEmpty }, "every path that is not lossless says what it loses")48        #expect(engine.inspectableFormats == Set(FormatClass.all))49        #expect(makeEngines().map(\.id) == ["data"])50        for p in paths {51            let options = engine.options(for: p)52            #expect(Set(options.map(\.id)).count == options.count, "\(p.id): duplicate option keys")53            #expect(options.allSatisfy { $0.id == $0.id.lowercased() && !$0.label.isEmpty })54            // Every default the UI would send back is accepted.55            let request = ConversionRequest(source: URL(fileURLWithPath: "/x"), sourceFormat: p.source, destinationFormat: p.destination, options: .init(Dictionary(uniqueKeysWithValues: options.map { ($0.id, $0.defaultValue) })))56            #expect(throws: Never.self) { _ = try DataOptions(request) }57        }58        let csvToParquet = Set(engine.options(for: DataEngine.path(.csv, .parquet)).map(\.id))59        #expect(csvToParquet.isSuperset(of: ["encoding.input", "csv.delimiter.input", "header", "types.detect", "types.columns", "numbers.decimal.comma", "nulls.input", "parquet.compression", "parquet.row.group.size", "memory.limit.mb", "threads"]))60        #expect(!csvToParquet.contains("sheet") && !csvToParquet.contains("csv.preset"))61    }6263    @Test func unsupportedRequestsAreRefused() async throws {64        let s = Scratch("contract")65        let src = try await Canonical.fixture(.csv, in: s)66        let engine = DataEngine()67        let context = ConversionContext(workDirectory: s.dir("w"))68        await #expect(throws: ConversionError.unsupportedPath(source: .csv, destination: .png)) { _ = try await engine.convert(ConversionRequest(source: src, sourceFormat: .csv, destinationFormat: .png), context: context) }69        await #expect(throws: ConversionError.unsupportedPath(source: .xlsx, destination: .xlsx)) { _ = try await engine.convert(ConversionRequest(source: src, sourceFormat: .xlsx, destinationFormat: .xlsx), context: context) }70        await #expect(throws: ConversionError.self) { _ = try await engine.convert(ConversionRequest(source: src, sourceFormat: .csv, destinationFormat: .json, operation: "merge"), context: context) }71        await #expect(throws: ConversionError.self) { _ = try await engine.convert(ConversionRequest(source: s.file("missing.csv"), sourceFormat: .csv, destinationFormat: .json), context: context) }72        await #expect(throws: ConversionError.self) { _ = try await runConversion(src, .csv, to: .parquet, in: s, options: ["parquet.compression": .string("magic")]) }73        // Preflight never throws: it reports.74        #expect(engine.preflight(ConversionRequest(source: s.file("missing.csv"), sourceFormat: .csv, destinationFormat: .json), inspection: nil).map(\.code) == ["preflight-failed"])75    }7677    // MARK: Arrow7879    @Test(arguments: ["py_plain.arrow", "py_lz4.feather", "py_stream.arrows"])80    func readsFilesWrittenByArrowCpp(_ name: String) async throws {81        // Fixtures written by pyarrow 24 (Arrow C++): uncompressed file, LZ4-compressed Feather v2, IPC stream.82        let url = try resource(name)83        let p = try await DataPreview.preview(url: url, format: .arrow)84        #expect(p.columns.map(\.name) == ["i8", "u64", "f32", "f64", "s", "ls", "b", "bin", "d32", "ts", "tsz", "t64", "dec", "dict", "list", "struct", "nested"])85        #expect(p.columns.map(\.type) == ["BIGINT", "UBIGINT", "DOUBLE", "DOUBLE", "VARCHAR", "VARCHAR", "BOOLEAN", "BLOB", "DATE", "TIMESTAMP", "TIMESTAMP WITH TIME ZONE", "TIME", "DECIMAL(18,3)", "VARCHAR", "VARCHAR", "VARCHAR", "VARCHAR"])86        #expect(p.totalRows == 3)87        #expect(p.rows[0] == ["1", "18446744073709551615", "0.1", "1.5", "héllo 😀", "a", "true", "AAE=", "2024-01-02", "2024-01-02T03:04:05.123456", "2024-01-02T03:04:05Z", "03:04:05.25", "-12.340", "x", "[1,2]", "{\"a\":1,\"b\":\"q\"}", "[{\"k\":[1.5]}]"])88        #expect(p.rows[1] == [nil, "0", nil, "nan", nil, "b", nil, nil, nil, nil, nil, nil, nil, "y", nil, nil, "[]"])89        #expect(p.rows[2] == ["-3", nil, "2.5", nil, "", nil, "false", "", "1969-12-31", "1960-01-01T00:00:00", "1960-01-01T00:00:00Z", "00:00:00", "0.001", "x", "[]", "{\"a\":null,\"b\":\"z\"}", nil])90        #expect(Set(p.notes.map(\.code)).isSuperset(of: ["timezone-normalized", "sub-microsecond-dropped", "nested-stringified"]))91        let info = try await DataEngine().inspect(url, format: .arrow)92        #expect(info.rowCount == 3 && info.columnCount == 17)93        #expect(info.fields.first { $0.key == "Variant" }?.value == (name.hasSuffix("s") ? "IPC stream" : "IPC file (Feather v2)"))94        #expect(info.fields.first { $0.key == "11. tsz" }?.value == "timestamp[ns, tz=America/Toronto]")95        #expect(info.fields.first { $0.key == "14. dict" }?.value == "string (dictionary-encoded)")96    }9798    @Test func refusesWhatItCannotDecode() async throws {99        do { _ = try await DataPreview.preview(url: try resource("py_zstd.arrow"), format: .arrow); Issue.record("ZSTD accepted") } catch let e as ConversionError {100            guard case .malformedInput(let m) = e else { Issue.record("unexpected \(e)"); return }101            #expect(m.contains("ZSTD"))102        }103        let s = Scratch("arrow")104        let good = try Data(contentsOf: try resource("py_plain.arrow"))105        for (name, data) in [("cut-half", good.prefix(good.count / 2)), ("cut-tail", good.dropLast(7)), ("no-footer", good.prefix(600)), ("garbage", Data((0..<400).map { UInt8(truncatingIfNeeded: $0 &* 31) })), ("flipped", { var d = good; for i in stride(from: 16, to: 300, by: 3) { d[i] ^= 0xA5 }; return d }())] {106            let u = s.file("\(name).arrow")107            try data.write(to: u)108            await #expect(throws: ConversionError.self, "\(name) was accepted") { _ = try await runConversion(u, .arrow, to: .csv, in: s) }109        }110        let fea1 = s.file("v1.feather")111        try Data("FEA1\0\0\0\0rest of a feather v1 file FEA1".utf8).write(to: fea1)112        await #expect(throws: ConversionError.self) { _ = try await DataPreview.preview(url: fea1, format: .arrow) }113    }114115    @Test func writtenFilesFollowTheIpcLayout() throws {116        let s = Scratch("arrow")117        let cols: [TableColumn] = [.init(name: "n", kind: .integer), .init(name: "s", kind: .string)]118        for format in [ArrowWriter.Format.file, .stream] {119            let u = s.file("layout-\(format.rawValue).arrow")120            let w = try ArrowWriter(url: u, columns: cols, format: format)121            try w.write(rows: [[.int(7), .string("abc")], [.null, .null], [.int(-1), .string("")]])122            try w.finish()123            let d = [UInt8](try Data(contentsOf: u))124            func i32(_ o: Int) -> Int32 { d[o..<(o + 4)].withUnsafeBytes { $0.loadUnaligned(as: Int32.self) } }125            func i64(_ o: Int) -> Int64 { d[o..<(o + 8)].withUnsafeBytes { $0.loadUnaligned(as: Int64.self) } }126            var o = 0127            if format == .file {128                // Magic "ARROW1" padded to 8 bytes at the start, repeated at the very end after the footer length.129                #expect(Array(d[0..<8]) == Array("ARROW1".utf8) + [0, 0] && Array(d[(d.count - 6)...]) == Array("ARROW1".utf8))130                o = 8131            }132            // Encapsulated messages: 0xFFFFFFFF, metadata length (multiple of 8), flatbuffer, body (multiple of 8).133            var kinds: [UInt8] = []134            var recordBodyOffset = 0, recordBodyLength = 0135            while true {136                #expect(i32(o) == -1, "continuation marker at \(o)")137                let len = Int(i32(o + 4))138                if len == 0 { o += 8; break }139                #expect(len % 8 == 0 && (o + 8) % 8 == 0)140                let fb = FBBuffer(bytes: Array(d[(o + 8)..<(o + 8 + len)]))141                let root = try fb.root()142                #expect(try root.i16(0) == 4, "metadata version V5")143                kinds.append(try root.u8(1))144                let body = Int(try root.i64(3))145                #expect(body % 8 == 0)146                if try root.u8(1) == 3 {147                    recordBodyOffset = o + 8 + len; recordBodyLength = body148                    let rb = try #require(try root.table(2))149                    #expect(try rb.i64(0) == 3)150                    let nodes = try #require(try rb.vector(1, elementSize: 16)), buffers = try #require(try rb.vector(2, elementSize: 16))151                    #expect(nodes.count == 2 && buffers.count == 5, "int64: validity + data; utf8: validity + offsets + data")152                    #expect(try fb.i64(nodes.start) == 3 && fb.i64(nodes.start + 8) == 1)153                    for k in 0..<buffers.count { #expect(try fb.i64(buffers.start + 16 * k) % 8 == 0, "buffer \(k) is 8-byte aligned") }154                }155                o += 8 + len + body156            }157            #expect(kinds == [1, 3], "schema, then one record batch")158            // Body: validity 0b101, three int64 (7, 0, -1), validity, offsets (0,3,3,3), bytes "abc".159            let body = Array(d[recordBodyOffset..<(recordBodyOffset + recordBodyLength)])160            #expect(body[0] == 0b101 && i64(recordBodyOffset + 8) == 7 && i64(recordBodyOffset + 24) == -1)161            #expect(body[32] == 0b101 && [0, 3, 3, 3].enumerated().allSatisfy { i32(recordBodyOffset + 40 + 4 * $0.offset) == Int32($0.element) })162            #expect(Array(body[56..<59]) == Array("abc".utf8))163            if format == .file {164                let footerLength = Int(i32(d.count - 10))165                let footer = try FBBuffer(bytes: Array(d[(d.count - 10 - footerLength)..<(d.count - 10)])).root()166                let blocks = try #require(try footer.vector(3, elementSize: 24))167                #expect(blocks.count == 1, "one record-batch block in the footer")168                #expect(d.count - 10 - footerLength == o, "the footer starts right after the end-of-stream marker")169            } else { #expect(o == d.count) }170            let r = try ArrowReader(url: u)171            #expect(r.isFileFormat == (format == .file) && r.fields.map(\.typeDescription) == ["int64", "string"])172            #expect(try r.countRows() == 3)173        }174    }175176    @Test func arrowBatchesAndWideValues() async throws {177        let s = Scratch("arrow")178        let big = s.file("big.csv")179        try ("n,s\n" + (0..<200_000).map { "\($0),row \($0)" }.joined(separator: "\n") + "\n").write(to: big, atomically: true, encoding: .utf8)180        let a = try await runConversion(big, .csv, to: .arrow, in: s, options: ["arrow.format": .string("stream")])181        #expect(a.output.facts["rows"] == "200000")182        #expect(try ArrowReader(url: a.file).isFileFormat == false)183        let back = try await runConversion(a.file, .arrow, to: .csv, in: s, name: "back")184        #expect(try Data(contentsOf: back.file) == Data(contentsOf: big), "CSV → Arrow stream → CSV is byte-identical")185        let wide = s.file("wide.csv")186        try "h,d\n123456789012345678901234567890,0.12345678901234567890\n-5,1.5\n".write(to: wide, atomically: true, encoding: .utf8)187        let w = try await runConversion(wide, .csv, to: .arrow, in: s)188        let r = try ArrowReader(url: w.file)189        #expect(r.fields.map(\.typeDescription) == ["decimal128(38, 0)", "decimal128(38, 20)"])190        let p = try await DataPreview.preview(url: w.file, format: .arrow)191        #expect(p.rows == [["123456789012345678901234567890", "0.12345678901234567890"], ["-5", "1.50000000000000000000"]])192    }193194    // MARK: Databases195196    @Test func sqliteTablesViewsAndDeclaredTypes() async throws {197        let s = Scratch("sqlite")198        let db = s.file("app.sqlite")199        try sqliteExec(db, [200            "PRAGMA page_size = 8192", "PRAGMA user_version = 7",201            "CREATE TABLE people (id INTEGER PRIMARY KEY, name TEXT NOT NULL, active BOOLEAN, born DATE, seen DATETIME, score REAL, photo BLOB, anything)",202            "INSERT INTO people VALUES (1, 'Ada', 1, '1815-12-10', '2024-02-29 13:45:10', 9.5, x'00ff10', 'text'), (2, 'Bob ''; DROP TABLE people;--', 0, NULL, '2024-02-29T13:45:10Z', NULL, NULL, 42), (3, 'Ünï', NULL, 'not a date', NULL, 1e300, x'', 2.5)",203            "CREATE TABLE \"weird \"\"name\"\"; DROP TABLE people;--\" (\"select\" TEXT, \"a'b\" INT)",204            "INSERT INTO \"weird \"\"name\"\"; DROP TABLE people;--\" VALUES ('x', 1)",205            "CREATE VIEW v AS SELECT name FROM people", "CREATE INDEX i ON people(name)",206            "CREATE TRIGGER t AFTER INSERT ON people BEGIN DELETE FROM people; END",207        ])208        let info = try await DataEngine().inspect(db, format: .sqlite)209        func field(_ g: String, _ k: String) -> String { info.fields.first { $0.group == g && $0.key == k }?.value ?? "" }210        #expect(field("Database", "User version") == "7" && field("Database", "Page size") == "8192" && field("Database", "Text encoding") == "UTF-8")211        #expect(field("Tables", "people") == "3 rows × 8 columns" && field("Tables", "v") == "view (not converted)")212        #expect(field("Columns of people", "active") == "BOOLEAN" && field("Columns of people", "anything") == "(no declared type)")213        #expect(info.sheetNames == ["people", "weird \"name\"; DROP TABLE people;--"])214        let p = try await DataPreview.preview(url: db, format: .sqlite)215        #expect(p.columns.map(\.type) == ["BIGINT", "VARCHAR", "BOOLEAN", "VARCHAR", "VARCHAR", "DOUBLE", "BLOB", "VARCHAR"])216        #expect(p.rows[0] == ["1", "Ada", "true", "1815-12-10", "2024-02-29T13:45:10", "9.5", "AP8Q", "text"])217        #expect(p.rows[1] == ["2", "Bob '; DROP TABLE people;--", "false", nil, "2024-02-29T13:45:10Z", nil, nil, "42"])218        #expect(p.rows[2] == ["3", "Ünï", nil, "not a date", nil, "1e+300", "", "2.5"])219        #expect(p.notes.contains { $0.code == "mixed-types" && $0.message.contains("“born”") && $0.message.contains("“anything”") })220        #expect(p.totalRows == 3)221        // All tables into a workbook, a DuckDB file and Parquet files; names survive verbatim where the target allows.222        let duck = try await runConversion(db, .sqlite, to: .duckdb, in: s)223        #expect(duck.warnings.codes.contains("views-not-converted"))224        #expect(try await DataEngine().inspect(duck.file, format: .duckdb).sheetNames == ["people", "weird \"name\"; DROP TABLE people;--"])225        let odd = try await DataPreview.preview(url: duck.file, format: .duckdb, table: "weird \"name\"; DROP TABLE people;--")226        #expect(odd.columns.map(\.name) == ["select", "a'b"] && odd.rows == [["x", "1"]])227        let book = try await runConversion(db, .sqlite, to: .xlsx, in: s)228        #expect(book.warnings.codes.contains("sheets-renamed"))229        #expect(try XLSXReader(url: book.file).sheets.map(\.name) == ["people", "weird \"name\"; DROP TABLE people"])230        let files = try await runConversion(db, .sqlite, to: .parquet, in: s, options: ["sheets.all": .bool(true)], name: "app")231        #expect(files.output.files.map(\.lastPathComponent) == ["app-people.parquet", "app-weird \"name\"; DROP TABLE people;--.parquet"])232        await #expect(throws: ConversionError.self) { _ = try await runConversion(db, .sqlite, to: .csv, in: s, options: ["sheet": .string("v")]) }233        // The source is untouched: the trigger never ran, nothing was dropped.234        #expect(try SQLiteSource(url: db).rowCount(table: "people") == 3)235    }236237    @Test func sqliteSourcesAreOpenedReadOnlyAndHardened() throws {238        let s = Scratch("sqlite")239        let db = s.file("ro.sqlite")240        try sqliteExec(db, ["CREATE TABLE t (a)", "INSERT INTO t VALUES (1)", "CREATE VIEW danger AS SELECT a FROM t"])241        let before = try Data(contentsOf: db)242        let src = try SQLiteSource(url: db)243        // The connection cannot write, and views are disabled on it (stored SQL does not run).244        #expect(throws: ConversionError.self) { _ = try src.scalar("DELETE FROM t RETURNING 1") }245        #expect(throws: ConversionError.self) { _ = try src.scalar("SELECT count(*) FROM danger") }246        #expect(throws: ConversionError.self) { _ = try src.scalar("SELECT load_extension('x')") }247        #expect(throws: ConversionError.self) { _ = try src.textScalar("ATTACH '\(s.file("other.sqlite").path)' AS o") }248        #expect(try src.tables().map(\.name) == ["t", "danger"])249        #expect(try Data(contentsOf: db) == before)250        #expect(!FileManager.default.fileExists(atPath: s.file("other.sqlite").path))251    }252253    @Test func duckdbFilesAndTables() async throws {254        let s = Scratch("duck")255        let db = s.file("w.duckdb")256        do {257            let ws = try Workspace(parent: s.url)258            let session = try Pipeline.makeSession(workspace: ws, sources: [], extraDirectories: [s.url], options: DataOptions(), control: nil)259            try session.execute("ATTACH \(try SQL.path(db)) AS out")260            try session.execute("CREATE TABLE out.main.typed AS SELECT 1::TINYINT t, 2::UBIGINT u, 170141183460469231731687303715884105727::HUGEINT h, 1.5::FLOAT f, 12.340::DECIMAL(18,3) d, 'e1'::VARCHAR s, '\\x00\\xFF'::BLOB b, DATE '2024-02-29' dt, TIME '13:45:10.5' tm, TIMESTAMP '2024-02-29 13:45:10.123456' ts, TIMESTAMPTZ '2024-02-29 13:45:10+02' tz, '00000000-0000-0000-0000-000000000000'::UUID id, INTERVAL 3 DAY iv, [1, 2] l, {'a': 1, 'b': {'c': 'x'}} st, MAP {'k': 1} m")261            try session.execute("CREATE SCHEMA out.other")262            try session.execute("CREATE TABLE out.other.second AS SELECT range AS n FROM range(5)")263            try session.execute("CREATE VIEW out.main.vw AS SELECT * FROM out.main.typed")264            try session.execute("DETACH out")265        }266        let info = try await DataEngine().inspect(db, format: .duckdb)267        #expect(info.sheetNames == ["typed", "other.second"] && info.rowCount == 1)268        #expect(info.columnCount == 17, "16 columns, the struct shown flattened into two")269        #expect(info.fields.first { $0.key == "Read with" }?.value == "DuckDB v2.0.1")270        #expect(info.fields.first { $0.key == "other.second" }?.value == "5 rows × 1 columns")271        let p = try await DataPreview.preview(url: db, format: .duckdb)272        #expect(p.tableNames == ["typed", "other.second"] && p.totalRows == 1)273        #expect(p.rows[0].prefix(13) == ["1", "2", "170141183460469231731687303715884105727", "1.5", "12.340", "e1", "AP8=", "2024-02-29", "13:45:10.5", "2024-02-29T13:45:10.123456", "2024-02-29T11:45:10Z", "00000000-0000-0000-0000-000000000000", "3 days"])274        #expect(p.columns.map(\.name).suffix(4) == ["l", "st.a", "st.b.c", "m"], "structs are flattened into dotted columns for a flat target")275        #expect(p.rows[0].suffix(4) == ["[1,2]", "1", "x", "{\"k\":1}"])276        // Every type survives a trip through each streamed target, as text where the target has no such type.277        for target in [FormatID.xlsx, .ods, .sqlite, .arrow, .csv, .jsonl, .yaml] {278            let c = try await runConversion(db, .duckdb, to: target, in: s, options: ["sheet": .string("typed")])279            let back = try await DataPreview.preview(url: c.file, format: target)280            let row = Dictionary(uniqueKeysWithValues: zip(back.columns.map(\.name), back.rows[0]))281            if target == .jsonl {282                // The digits are in the file; DuckDB's JSON reader, used to read it back, has 64-bit floats for them.283                #expect(try String(contentsOf: c.file, encoding: .utf8).contains("\"h\":170141183460469231731687303715884105727,"))284            } else {285                #expect(row["h"] == "170141183460469231731687303715884105727", "\(target): 128-bit integer")286            }287            #expect(Validator.equivalent(row["d"] ?? nil, "12.340") && row["s"] == "e1" && row["dt"] == "2024-02-29", "\(target)")288            #expect(row["id"] == "00000000-0000-0000-0000-000000000000" && row["iv"] == "3 days", "\(target)")289            #expect(row["tm"] == "13:45:10.5", "\(target): time")290            if target != .xlsx { #expect(row["ts"] == "2024-02-29T13:45:10.123456", "\(target): microseconds") } else {291                #expect(row["ts"] == "2024-02-29T13:45:10.123" && c.warnings.codes.contains("sub-millisecond-dropped"))292            }293            if FormatClass.workbooks.contains(target) { #expect(c.warnings.codes.contains("timezone-dropped") && row["tz"] == "2024-02-29T11:45:10") } else { #expect(row["tz"] == "2024-02-29T11:45:10Z", "\(target)") }294            for check in try await DataEngine().validate(c.output, request: c.request, source: nil) { #expect(check.passed, "\(target) \(check.name): \(check.detail)") }295        }296        let pq = try await runConversion(db, .duckdb, to: .parquet, in: s, options: ["sheet": .string("typed")])297        let nested = try await DataPreview.preview(url: pq.file, format: .parquet, options: .init(["nested": .string("native")]))298        // The largest 128-bit integer has 39 digits: one more than Parquet's widest exact number, so it goes as text.299        #expect(nested.columns.first { $0.name == "st" }?.type == "STRUCT(a INTEGER, b STRUCT(c VARCHAR))" && nested.columns.first { $0.name == "h" }?.type == "VARCHAR")300        #expect(nested.rows[0][2] == "170141183460469231731687303715884105727")301        #expect(pq.warnings.codes.contains("int128-as-text") && pq.warnings.codes.contains("views-not-converted"))302        let small = s.file("small.duckdb")303        do {304            let ws = try Workspace(parent: s.url)305            let session = try Pipeline.makeSession(workspace: ws, sources: [], extraDirectories: [s.url], options: DataOptions(), control: nil)306            try session.execute("ATTACH \(try SQL.path(small)) AS out")307            try session.execute("CREATE TABLE out.main.t AS SELECT 99999999999999999999999999999999999999::HUGEINT h UNION ALL SELECT -1")308            try session.execute("DETACH out")309        }310        for target in [FormatID.parquet, .arrow] {311            let c = try await runConversion(small, .duckdb, to: target, in: s)312            #expect(c.warnings.codes.contains("int128-as-decimal"), "\(target)")313            let back = try await DataPreview.preview(url: c.file, format: target)314            #expect(back.columns.map(\.type) == ["DECIMAL(38,0)"] && back.rows == [["99999999999999999999999999999999999999"], ["-1"]], "\(target)")315        }316        let notDuck = s.file("fake.duckdb")317        try Data("this is not a database".utf8).write(to: notDuck)318        await #expect(throws: ConversionError.self) { _ = try await runConversion(notDuck, .duckdb, to: .csv, in: s) }319    }320321    // MARK: Parquet and nested values322323    @Test func parquetOptionsAndMetadata() async throws {324        let s = Scratch("pq")325        let src = s.file("n.csv")326        try ("n,s\n" + (0..<50_000).map { "\($0),value \($0 % 97)" }.joined(separator: "\n") + "\n").write(to: src, atomically: true, encoding: .utf8)327        var sizes: [String: Int] = [:]328        for codec in ["zstd", "snappy", "gzip", "lz4", "uncompressed"] {329            let c = try await runConversion(src, .csv, to: .parquet, in: s, options: ["parquet.compression": .string(codec), "parquet.row.group.size": .int(10_000)], name: codec)330            let info = try await DataEngine().inspect(c.file, format: .parquet)331            func field(_ k: String) -> String { info.fields.first { $0.group == "Parquet" && $0.key == k }?.value ?? "" }332            #expect(field("Compression") == (codec == "lz4" ? "LZ4_RAW" : codec.uppercased()), "\(codec)")333            #expect(field("Created by").hasPrefix("DuckDB version v2.0.1") && info.rowCount == 50_000 && info.columnCount == 2)334            #expect((Int(field("Row groups")) ?? 0) >= 3, "\(codec): row groups \(field("Row groups"))")335            #expect(info.fields.first { $0.group == "Parquet schema" && $0.key == "n" }?.value.hasPrefix("INT64") == true)336            sizes[codec] = (try FileManager.default.attributesOfItem(atPath: c.file.path)[.size] as? Int) ?? 0337        }338        #expect((sizes["zstd"] ?? 0) < (sizes["uncompressed"] ?? 0))339        let c = try await runConversion(src, .csv, to: .parquet, in: s)340        let back = try await runConversion(c.file, .parquet, to: .csv, in: s, name: "back")341        #expect(try Data(contentsOf: back.file) == Data(contentsOf: src))342        // Broken footers and truncated files.343        let good = try Data(contentsOf: c.file)344        for (name, data) in [("truncated", good.prefix(good.count - 9)), ("half", good.prefix(good.count / 2)), ("bad-footer", { var d = good; let n = Int(d[(d.count - 8)..<(d.count - 4)].withUnsafeBytes { $0.loadUnaligned(as: UInt32.self) }); for i in (d.count - 8 - n)..<(d.count - 8 - n + min(n, 96)) { d[i] ^= 0x5A }; return d }()), ("magic-only", Data("PAR1PAR1".utf8))] {345            let u = s.file("\(name).parquet")346            try data.write(to: u)347            await #expect(throws: ConversionError.self, "\(name) accepted") { _ = try await runConversion(u, .parquet, to: .csv, in: s) }348            await #expect(throws: ConversionError.self, "\(name) inspected") { _ = try await DataEngine().inspect(u, format: .parquet) }349        }350    }351352    @Test func nestedJsonHandling() async throws {353        let s = Scratch("nested")354        let u = s.file("n.jsonl")355        try """356        {"id":1,"user":{"name":"Ada","address":{"city":"Paris","zip":"75001"}},"tags":["a","b"],"mixed":1}357        {"id":2,"user":{"name":"Bob","address":{"city":"Québec","zip":"G1R"}},"tags":[],"mixed":"x","extra":true}358        {"id":3,"user":null,"tags":null,"mixed":null}359        """.write(to: u, atomically: true, encoding: .utf8)360        let flat = try await runConversion(u, .jsonl, to: .csv, in: s)361        #expect(flat.warnings.codes.isSuperset(of: ["nested-flattened", "nested-stringified"]))362        let text = try String(contentsOf: flat.file, encoding: .utf8)363        #expect(text.hasPrefix("id,user.name,user.address.city,user.address.zip,tags,mixed,extra\n1,Ada,Paris,75001,\"[\"\"a\"\",\"\"b\"\"]\","))364        let asJSON = try await runConversion(u, .jsonl, to: .csv, in: s, options: ["nested": .string("json")], name: "j")365        #expect(try String(contentsOf: asJSON.file, encoding: .utf8).contains("1,\"{\"\"name\"\":\"\"Ada\"\",\"\"address\"\":{\"\"city\"\":\"\"Paris\"\",\"\"zip\"\":\"\"75001\"\"}}\""))366        // Parquet keeps the nested types; going back to JSON Lines restores the structure.367        let pq = try await runConversion(u, .jsonl, to: .parquet, in: s)368        #expect(!pq.warnings.codes.contains("nested-flattened"))369        let native = try await DataPreview.preview(url: pq.file, format: .parquet, options: .init(["nested": .string("native")]))370        #expect(native.columns.map(\.type).prefix(3) == ["BIGINT", "STRUCT(\"name\" VARCHAR, address STRUCT(city VARCHAR, zip VARCHAR))", "VARCHAR[]"])371        let again = try await runConversion(pq.file, .parquet, to: .jsonl, in: s, name: "again")372        let lines = try String(contentsOf: again.file, encoding: .utf8).split(separator: "\n")373        #expect(lines.count == 3 && lines[0].hasPrefix("{\"id\":1,\"user\":{\"name\":\"Ada\",\"address\":{\"city\":\"Paris\",\"zip\":\"75001\"}},\"tags\":[\"a\",\"b\"]"))374        // A document whose records sit under a key, next to other content.375        let doc = s.file("doc.json")376        try #"{"meta":{"page":1},"data":{"items":[{"a":1,"b":{"c":2}},{"a":3}]},"total":2}"#.write(to: doc, atomically: true, encoding: .utf8)377        let d = try await runConversion(doc, .json, to: .csv, in: s)378        #expect(try String(contentsOf: d.file, encoding: .utf8) == "a,b.c\n1,2\n3,\n")379        #expect(d.warnings.all.first { $0.code == "content-outside-records" }?.message.contains("“data.items”") == true)380        #expect(d.warnings.all.first { $0.code == "content-outside-records" }?.severity == .dataLoss)381        let chosen = try await runConversion(doc, .json, to: .csv, in: s, options: ["records.path": .string("meta")], name: "meta")382        #expect(try String(contentsOf: chosen.file, encoding: .utf8) == "page\n1\n")383        await #expect(throws: ConversionError.self) { _ = try await runConversion(doc, .json, to: .csv, in: s, options: ["records.path": .string("nope.x")]) }384        // XML records with attributes, typed by detection.385        let xml = s.file("r.xml")386        try "<orders><order id=\"007\"><qty>2</qty><price>9.50</price><when>2024-02-29</when></order><order id=\"008\"><qty>10</qty><price>1</price><when>2024-03-01</when></order></orders>".write(to: xml, atomically: true, encoding: .utf8)387        let x = try await DataPreview.preview(url: xml, format: .xml)388        #expect(x.columns.map(\.name) == ["@id", "qty", "price", "when"] && x.columns.map(\.type) == ["VARCHAR", "BIGINT", "DOUBLE", "DATE"])389        #expect(x.rows == [["007", "2", "9.5", "2024-02-29"], ["008", "10", "1.0", "2024-03-01"]])390    }391392    @Test func jsonNumbersAndNonFiniteValues() async throws {393        let s = Scratch("num")394        let u = s.file("n.arrow")395        let w = try ArrowWriter(url: u, columns: [.init(name: "a", kind: .double), .init(name: "b", kind: .integer)], format: .file)396        try w.write(rows: [[.double(.nan), .int(9_007_199_254_740_993)], [.double(.infinity), .int(1)], [.double(1.5), .int(2)]])397        try w.finish()398        let j = try await runConversion(u, .arrow, to: .json, in: s)399        #expect(try String(contentsOf: j.file, encoding: .utf8).contains("{\"a\":null,\"b\":9007199254740993}"))400        #expect(j.warnings.all.first { $0.code == "non-finite-dropped" }?.message.hasPrefix("2 ") == true)401        #expect(j.warnings.codes.contains("json-number-precision"))402        #expect(DataEngine().preflight(j.request, inspection: nil).contains { $0.code == "json-number-precision" })403    }404405    // MARK: Malformed inputs406407    @Test(arguments: [FormatID.xlsx, .ods, .sqlite, .jsonl, .json, .csv])408    func damagedFilesAreRejectedCleanly(_ format: FormatID) async throws {409        let s = Scratch("bad")410        let good = try await Canonical.fixture(format, in: s)411        let bytes = try Data(contentsOf: good)412        var variants: [(String, Data)] = [("empty", Data()), ("garbage", Data((0..<2048).map { UInt8(truncatingIfNeeded: ($0 &* 197) ^ 0x5C) }))]413        switch format {414        case .xlsx, .ods:415            variants.append(("truncated", bytes.prefix(bytes.count * 2 / 3)))416            variants.append(("corrupted", { var d = bytes; for i in stride(from: 60, to: min(d.count, 1200), by: 2) { d[i] ^= 0x77 }; return d }()))417            variants.append(("ole", Data([0xD0, 0xCF, 0x11, 0xE0, 0xA1, 0xB1, 0x1A, 0xE1]) + Data(count: 600)))418            variants.append(("plain-zip", try { let u = s.file("p.zip"); let z = try ZipWriter(url: u); try z.add("hello.txt", "hi"); try z.finish(); return try Data(contentsOf: u) }()))419        case .sqlite:420            variants.append(("truncated", bytes.prefix(bytes.count / 2)))421            variants.append(("corrupted", { var d = bytes; for i in 100..<min(d.count, 4000) { d[i] = 0xFF }; return d }()))422        case .jsonl: variants = [("garbage", Data("{\"a\":1}\n{\"a\":\n".utf8)), ("not-json", Data("hello\nworld\n".utf8))]423        case .json: variants = [("cut", Data("[{\"a\":1},{\"a\":".utf8)), ("not-json", Data("[hello".utf8))]424        default: variants = [("empty", Data())]425        }426        for (name, data) in variants {427            let u = s.file("\(name).\(FormatCatalog.format(format)?.preferredExtension ?? format.rawValue)")428            try data.write(to: u)429            do {430                _ = try await runConversion(u, format, to: format == .csv ? .json : .csv, in: s)431                Issue.record("\(format) \(name): accepted")432            } catch let e as ConversionError {433                switch e {434                case .malformedInput, .unreadableInput, .encryptedInput, .limitExceeded: break435                default: Issue.record("\(format) \(name): unexpected error \(e)")436                }437                #expect(!(e.errorDescription ?? "").isEmpty)438            }439            #expect(DataEngine().preflight(ConversionRequest(source: u, sourceFormat: format, destinationFormat: .parquet), inspection: nil).contains { $0.code == "preflight-failed" }, "\(format) \(name): preflight")440            // A zero-byte file is a valid, empty SQLite database: it can be inspected but holds nothing to convert.441            if !(format == .sqlite && name == "empty") {442                await #expect(throws: (any Error).self, "\(format) \(name): inspect") { _ = try await DataEngine().inspect(u, format: format) }443            }444        }445    }446447    @Test func badSheetXmlInsideAValidPackage() async throws {448        let s = Scratch("bad")449        for (name, sheet) in [("unclosed", "<worksheet><sheetData><row r=\"1\"><c r=\"A1\"><v>1</v></c>"), ("doctype", "<!DOCTYPE x [<!ENTITY a \"b\">]><worksheet><sheetData><row><c><v>&a;</v></c></row></sheetData></worksheet>"), ("bad-ref", "<worksheet><sheetData><row r=\"1\"><c r=\"A1\" t=\"s\"><v>99</v></c></row></sheetData></worksheet>"), ("row-too-far", "<worksheet><sheetData><row r=\"99999999\"><c><v>1</v></c></row></sheetData></worksheet>"), ("column-too-far", "<worksheet><sheetData><row r=\"1\"><c r=\"ZZZ1\"><v>1</v></c></row></sheetData></worksheet>")] {450            let u = s.file("\(name).xlsx")451            let z = try ZipWriter(url: u)452            try z.add("[Content_Types].xml", "<Types/>")453            try z.add("xl/workbook.xml", "<workbook xmlns:r=\"r\"><sheets><sheet name=\"S\" sheetId=\"1\" r:id=\"rId1\"/></sheets></workbook>")454            try z.add("xl/_rels/workbook.xml.rels", "<Relationships><Relationship Id=\"rId1\" Type=\"http://x/worksheet\" Target=\"worksheets/sheet1.xml\"/></Relationships>")455            try z.add("xl/worksheets/sheet1.xml", sheet)456            try z.finish()457            await #expect(throws: ConversionError.self, "\(name) accepted") { _ = try await runConversion(u, .xlsx, to: .csv, in: s) }458        }459    }460461    // MARK: Hostile names462463    @Test func hostileFileSheetAndColumnNames() async throws {464        let s = Scratch("names")465        let hostile = "it's \"x\"; DROP TABLE t; -- [1]*?{a,b}"466        let src = s.file("\(hostile).csv")467        let c1 = "a\"b", c2 = "c'd; DROP TABLE x;--", c3 = "select", c4 = "tab\there", c5 = "$(rm -rf ~)", c6 = "e\\'f", c7 = "<tag attr=\"1\">"468        let header = [c1, c2, c3, c4, c5, c6, c7]469        try (header.map { Canonical.csvField($0, delimiter: ",") }.joined(separator: ",") + "\n1,2,3,4,5,6,7\n'; DELETE FROM t; --,\"x\"\"y\",z,w,v,u,t\n").write(to: src, atomically: true, encoding: .utf8)470        // A decoy next to the source: glob characters in the name must never match another file.471        try "decoy\n9\n".write(to: s.file("it's \"x\"; DROP TABLE t; -- 1Xb.csv"), atomically: true, encoding: .utf8)472        for target in [FormatID.parquet, .duckdb, .sqlite, .xlsx, .ods, .arrow, .jsonl, .json, .tsv, .yaml, .plist] {473            let c = try await runConversion(src, .csv, to: target, in: s)474            #expect(c.file.deletingPathExtension().lastPathComponent == hostile, "\(target): output name")475            let p = try await DataPreview.preview(url: c.file, format: target)476            if target == .plist { #expect(Set(p.columns.map(\.name)) == Set(header), "\(target)") } else { #expect(p.columns.map(\.name) == header, "\(target): column names") }477            #expect(p.rows.count == 2, "\(target)")478            let first = Dictionary(uniqueKeysWithValues: zip(p.columns.map(\.name), p.rows[1]))479            #expect(first[c1] == "'; DELETE FROM t; --" && first[c2] == "x\"y", "\(target): values")480            for check in try await DataEngine().validate(c.output, request: c.request, source: nil) { #expect(check.passed, "\(target) \(check.name): \(check.detail)") }481            if target == .sqlite || target == .duckdb {482                #expect(try await DataEngine().inspect(c.file, format: target).sheetNames == [hostile], "\(target): table name")483            }484        }485        // As XML the names are adapted (and reported), never interpreted.486        let x = try await runConversion(src, .csv, to: .xml, in: s)487        #expect(x.warnings.codes.contains("xml-names-adapted"))488        #expect(try XMLTreeCodec.parse(Data(contentsOf: x.file)).notes.elements == 17)489        // A custom table name with quotes.490        let named = try await runConversion(src, .csv, to: .sqlite, in: s, options: ["table.name": .string("x\"; DROP TABLE y; --")], name: "named")491        #expect(try SQLiteSource(url: named.file).tables().map(\.name) == ["x\"; DROP TABLE y; --"])492        #expect(try SQL.literal("a'b") == "'a''b'" && SQL.ident("a\"b") == "\"a\"\"b\"")493        #expect(throws: ConversionError.self) { _ = try SQL.literal("nul\0byte") }494        #expect(throws: ConversionError.self) { _ = try SQL.ident("nul\0byte") }495    }496497    // MARK: Sandbox, limits, cancellation498499    @Test func duckdbIsLockedDown() throws {500        let s = Scratch("sandbox")501        let ws = try Workspace(parent: s.url)502        let allowed = s.file("allowed.csv"), secret = s.file("secret.csv")503        try "a\n1\n".write(to: allowed, atomically: true, encoding: .utf8)504        try "password\nhunter2\n".write(to: secret, atomically: true, encoding: .utf8)505        let d = try Pipeline.makeSession(workspace: ws, sources: [allowed], extraDirectories: [], options: DataOptions(), control: nil)506        #expect(try d.query("SELECT * FROM read_csv(\(try SQL.path(allowed.resolvingSymlinksInPath())))").rows == [[.int(1)]])507        for sql in [508            "SELECT * FROM read_csv(\(try SQL.path(secret)))", "SELECT * FROM read_csv('/etc/passwd', all_varchar = true)", "SELECT * FROM glob('/etc/*')",509            "SELECT * FROM read_text('/etc/hosts')", "COPY (SELECT 1) TO '\(s.url.path)/escape.csv'", "COPY (SELECT 1) TO '/tmp/orix-data-escape-\(UUID().uuidString).csv'",510            "ATTACH '\(s.url.path)/other.duckdb' AS o", "INSTALL httpfs", "LOAD httpfs", "INSTALL spatial FROM community", "SELECT * FROM read_csv('https://example.com/x.csv')",511            "SELECT * FROM read_parquet('s3://bucket/x.parquet')", "SET enable_external_access = true", "SET allowed_directories = ['/']", "SET lock_configuration = false",512            "SET autoinstall_known_extensions = true", "SET autoload_known_extensions = true", "RESET enable_external_access", "SET temp_directory = '/tmp'",513            "SELECT * FROM sqlite_scan('x.db', 't')", "SELECT * FROM read_xlsx('x.xlsx')", "EXPORT DATABASE '/tmp/orix-export'", "SELECT getenv('HOME')",514        ] {515            #expect(throws: (any Error).self, "allowed: \(sql)") { _ = try d.query(sql) }516        }517        #expect(!FileManager.default.fileExists(atPath: s.file("escape.csv").path))518        let settings = Dictionary(uniqueKeysWithValues: try d.query("SELECT name, value FROM duckdb_settings() WHERE name IN ('enable_external_access', 'lock_configuration', 'autoinstall_known_extensions', 'autoload_known_extensions', 'TimeZone')").rows.map { ($0[0].text ?? "", $0[1].text ?? "") })519        #expect(settings == ["enable_external_access": "false", "lock_configuration": "true", "autoinstall_known_extensions": "false", "autoload_known_extensions": "false", "TimeZone": "UTC"])520        // Only the functionality built into the bundled library is used.521        #expect(try d.query("SELECT typeof(json('[1]')), strftime(TIMESTAMPTZ '2024-01-01 00:00:00+00', '%Y')").rows == [[.string("JSON"), .string("2024")]])522        #expect(DuckDBSession.libraryVersion == "v2.0.1")523    }524525    @Test func memoryLimitIsEnforcedAndSpillsToTheJobFolder() async throws {526        let s = Scratch("mem")527        let ws = try Workspace(parent: s.url)528        var tight = DataOptions(); tight.memoryLimitMB = 64; tight.threads = 2529        let d = try Pipeline.makeSession(workspace: ws, sources: [], extraDirectories: [], options: tight, control: nil)530        #expect(try d.query("SELECT current_setting('memory_limit'), current_setting('threads')").rows.first?.map(\.text) == ["61.0 MiB", "2"])531        // A query that cannot spill (one giant list) hits the limit and is reported as such.532        do { _ = try d.query("SELECT list(range) FROM range(60000000)"); Issue.record("no memory error") } catch let e as ConversionError {533            guard case .limitExceeded(let m) = e else { Issue.record("unexpected \(e)"); return }534            #expect(m.contains("memory"))535        }536        // A sort far larger than the limit spills to the workspace's temp directory and succeeds.537        #expect(try d.scalarInt("SELECT count(*) FROM (SELECT range, md5(CAST(range AS VARCHAR)) h FROM range(3000000) ORDER BY h)") == 3_000_000)538        let temp = ws.directory.appendingPathComponent("duckdb-tmp")539        #expect(FileManager.default.fileExists(atPath: temp.path), "spill files live inside the job's workspace")540        // Through the engine, with the smallest accepted limit.541        let src = s.file("m.csv")542        try ("id,text\n" + (0..<400_000).map { "\($0),some text value number \($0)" }.joined(separator: "\n") + "\n").write(to: src, atomically: true, encoding: .utf8)543        let c = try await runConversion(src, .csv, to: .parquet, in: s, options: ["memory.limit.mb": .int(256), "threads": .int(2)])544        #expect(c.output.facts["rows"] == "400000")545        for check in try await DataEngine().validate(c.output, request: c.request, source: nil) { #expect(check.passed, "\(check.name): \(check.detail)") }546    }547548    @Test(arguments: [FormatID.xlsx, .parquet, .sqlite, .arrow, .yaml])549    func cancellationStopsMidStream(_ target: FormatID) async throws {550        let s = Scratch("cancel")551        let src = s.file("big.csv")552        let line = "0123456789,some text that makes the row longer,2024-02-29T13:45:10,1234.5678,true\n"553        let block = String(repeating: line, count: 20_000)554        FileManager.default.createFile(atPath: src.path, contents: Data("a,b,c,d,e\n".utf8))555        let h = try FileHandle(forWritingTo: src)556        try h.seekToEnd()557        for _ in 0..<60 { try h.write(contentsOf: Data(block.utf8)) }558        try h.close()559        let work = s.dir("work")560        let engine = DataEngine()561        let request = ConversionRequest(source: src, sourceFormat: .csv, destinationFormat: target)562        let bag = WarningBag()563        let canceller = Canceller()564        // Cancel from inside the first measured progress report: the conversion is then demonstrably under way,565        // and the test does not depend on how the cooperative pool is scheduled.566        let task = Task {567            try await engine.convert(request, context: ConversionContext(workDirectory: work, progress: { update in568                bag.progress(update)569                if let f = update.fraction, f > 0, f < 1 { canceller.fire() }570            }))571        }572        canceller.arm { task.cancel() }573        do {574            let out = try await task.value575            Issue.record("\(target): the conversion finished (\(out.facts["rows"] ?? "?") rows) instead of being cancelled")576        } catch is CancellationError {} catch { Issue.record("\(target): unexpected error \(error)") }577        let reached = bag.progressValues.compactMap { $0 }.max() ?? 0578        #expect(reached < 1, "\(target): cancelled mid-stream, at \(reached)")579        // The private workspace (DuckDB database, spill files) is gone; no engine thread keeps writing.580        let left = try FileManager.default.contentsOfDirectory(atPath: work.path)581        #expect(!left.contains { $0.hasPrefix(".orix-data-") }, "\(target): leftovers \(left)")582        // The engine is still usable afterwards.583        let small = try await Canonical.fixture(.csv, in: s)584        #expect(try await runConversion(small, .csv, to: target, in: s).output.facts["rows"] == "4")585    }586587    @Test func alreadyCancelledTasksDoNothing() async throws {588        let s = Scratch("cancel")589        let src = try await Canonical.fixture(.csv, in: s)590        let work = s.dir("w")591        let task = Task {592            withUnsafeCurrentTask { $0?.cancel() }593            return try await DataEngine().convert(ConversionRequest(source: src, sourceFormat: .csv, destinationFormat: .parquet), context: ConversionContext(workDirectory: work))594        }595        await #expect(throws: CancellationError.self) { _ = try await task.value }596    }597598    // MARK: Preview, validation599600    @Test func previewReturnsSchemaRowsAndCounts() async throws {601        let s = Scratch("preview")602        for format in FormatClass.all where format != .xls || libreOfficeAvailable {603            let u = try await Canonical.fixture(format, in: s)604            let p = try await DataPreview.preview(url: u, format: format, rows: 2)605            #expect(p.rows.count == 2, "\(format)")606            #expect(p.totalRows == 4 || p.totalRows == nil, "\(format): total \(String(describing: p.totalRows))")607            #expect(Set(p.columns.map(\.name)) == Set(Canonical.names), "\(format)")608            #expect(p.columns.first { $0.name == "zip" }?.kind == .string, "\(format): identifiers with leading zeros stay text")609            #expect(p.columns.first { $0.name == "id" }?.kind == .integer, "\(format)")610            #expect(p.rows[0][p.columns.firstIndex { $0.name == "name" } ?? 0] == "héllo wörld", "\(format)")611        }612        #expect(DataPreview.formats.count == 15)613        // Only the requested rows are read from a large file.614        let big = s.file("big.csv")615        try ("n\n" + String(repeating: "1\n", count: 3_000_000)).write(to: big, atomically: true, encoding: .utf8)616        let p = try await DataPreview.preview(url: big, format: .csv, rows: 10)617        #expect(p.rows.count == 10 && p.totalRows == 3_000_000)618    }619620    @Test func validationCatchesAWrongOutput() async throws {621        let s = Scratch("validate")622        let src = try await Canonical.fixture(.parquet, in: s)623        let c = try await runConversion(src, .parquet, to: .csv, in: s)624        let engine = DataEngine()625        #expect(try await engine.validate(c.output, request: c.request, source: nil).allSatisfy(\.passed))626        // One row removed after the fact.627        var lines = try String(contentsOf: c.file, encoding: .utf8).components(separatedBy: "\n")628        lines.remove(at: 2)629        try lines.joined(separator: "\n").write(to: c.file, atomically: true, encoding: .utf8)630        let checks = try await engine.validate(c.output, request: c.request, source: nil)631        #expect(checks.first { $0.name == "row-count" }?.passed == false)632        // A lossless path with one altered value fails the value check.633        let d = try await runConversion(src, .parquet, to: .duckdb, in: s)634        #expect(try await engine.validate(d.output, request: d.request, source: nil).allSatisfy(\.passed))635        let other = s.file("other.csv")636        try Canonical.delimited(",").replacingOccurrences(of: "02134", with: "02135").write(to: other, atomically: true, encoding: .utf8)637        let altered = try await runConversion(other, .csv, to: .duckdb, in: s, name: d.file.deletingPathExtension().lastPathComponent)638        let forged = ConversionOutput(file: altered.file, facts: d.output.facts)639        let verdict = try await engine.validate(forged, request: d.request, source: nil)640        #expect(verdict.first { $0.name == "sample-values" }?.passed == false)641        #expect(verdict.first { $0.name == "sample-values" }?.detail.contains("“zip”") == true)642        // An output that is not what it claims.643        try Data("not a parquet file".utf8).write(to: c.file)644        let broken = try await engine.validate(ConversionOutput(file: c.file, facts: ["rows": "4"]), request: ConversionRequest(source: src, sourceFormat: .parquet, destinationFormat: .parquet), source: nil)645        #expect(broken.first?.name == "reopen" && broken.first?.passed == false)646    }647}648
Files main
Menu

Theme