Repository Public
1import Foundation2import Testing3import SheetCore4import SheetData5import SheetUI6@testable import FeatureData78@MainActor @Suite struct PivotSpecEditingTests {9 func spec() -> PivotSpec { PivotSpec(source: .dataset("t"), values: []) }1011 @Test func addingFieldsUsesDefaultsForTheirType() {12 var s = spec()13 s.add("region", type: .string, to: .rows)14 s.add("day", type: .date, to: .columns)15 s.add("amount", type: .double, to: .values)16 s.add("label", type: .string, to: .values)17 s.add("region", type: .string, to: .filters)18 s.add("region", type: .string, to: .rows) // the same field twice on an axis is ignored19 s.add("region", type: .string, to: .filters)20 #expect(s.rows == [PivotField("region")])21 #expect(s.columns == [PivotField("day", grouping: .datePart(.year))])22 #expect(s.values.map(\.aggregation) == [.sum, .count])23 #expect(s.filters == [PivotFilter("region", .exclude([]))])24 #expect(PivotZone.allCases.map { s.count(in: $0) } == [1, 1, 2, 1])25 #expect(s.axisFieldTitles == ["region", "day (Year)"])26 }2728 @Test func removingAValueRepairsSortsAndTopFilters() {29 var s = spec()30 s.values = [PivotValue("a"), PivotValue("b"), PivotValue("c")]31 s.rows = [PivotField("r", sort: .value(index: 2, ascending: false)), PivotField("q", sort: .value(index: 0, ascending: true))]32 s.columns = [PivotField("k", sort: .value(index: 1, ascending: true))]33 s.filters = [PivotFilter("r", .top(count: 5, valueIndex: 2, bottom: false)), PivotFilter("q", .top(count: 3, valueIndex: 1, bottom: true)), PivotFilter("x", .include([.int(1)]))]34 s.remove(.values, at: 1)35 #expect(s.values.map(\.field) == ["a", "c"])36 #expect(s.rows[0].sort == .value(index: 1, ascending: false)) // followed "c"37 #expect(s.rows[1].sort == .value(index: 0, ascending: true))38 #expect(s.columns[0].sort == .label(ascending: true)) // its value is gone39 #expect(s.filters == [PivotFilter("r", .top(count: 5, valueIndex: 1, bottom: false)), PivotFilter("x", .include([.int(1)]))])40 }4142 @Test func movingFlippingAndCalculatedFields() {43 var s = spec()44 s.rows = [PivotField("a"), PivotField("b"), PivotField("c")]45 s.move(.rows, from: 0, by: 1)46 #expect(s.rows.map(\.name) == ["b", "a", "c"])47 s.move(.rows, from: 2, by: 1) // out of range: unchanged48 #expect(s.rows.map(\.name) == ["b", "a", "c"])49 s.flip(.rows, at: 0)50 #expect(s.rows.map(\.name) == ["a", "c"] && s.columns.map(\.name) == ["b"])51 s.flip(.columns, at: 0)52 #expect(s.rows.map(\.name) == ["a", "c", "b"] && s.columns.isEmpty)5354 s.addCalculatedField(); s.addCalculatedField()55 #expect(s.calculatedFields.map(\.name) == ["Field1", "Field2"])56 s.add("Field1", type: nil, to: .values)57 s.add("Field1", type: nil, to: .rows) // a calculated field cannot be an axis58 #expect(s.values.map(\.field) == ["Field1"] && s.rows.count == 3)59 s.renameCalculatedField(at: 0, to: "Margin")60 #expect(s.values.map(\.field) == ["Margin"] && s.calculatedFields[0].name == "Margin")61 s.removeCalculatedField(at: 0)62 #expect(s.values.isEmpty && s.calculatedFields.map(\.name) == ["Field2"])63 }6465 @Test func showAsAndFilterChoicesRoundTrip() {66 let modes: [PivotShowAs] = [.none, .percentOfGrandTotal, .percentOfRowTotal, .percentOfColumnTotal, .runningTotal(field: "Region"),67 .differenceFrom(field: "Region", base: .previous), .percentDifferenceFrom(field: "Region", base: .previous)]68 for mode in modes { #expect(ShowAsChoice(mode).make(field: ShowAsChoice.field(of: mode) ?? "") == mode) }69 #expect(Set(modes.map { ShowAsChoice($0) }) == Set(ShowAsChoice.allCases))70 #expect(FilterChoice.exclude.convert(.include([.int(1), .int(2)])) == .exclude([.int(1), .int(2)]))71 #expect(FilterChoice.condition.convert(.include([.int(1), .int(2)])) == .condition(.greaterOrEqual, [.int(1)]))72 #expect(FilterChoice.bottom.convert(.top(count: 4, valueIndex: 1, bottom: false)) == .top(count: 4, valueIndex: 1, bottom: true))73 #expect(FilterChoice.top.convert(.exclude([])) == .top(count: 10, valueIndex: 0, bottom: false))74 for kind in [PivotFilterKind.include([]), .exclude([]), .condition(.equal, []), .top(count: 1, valueIndex: 0, bottom: false), .top(count: 1, valueIndex: 0, bottom: true)] {75 #expect(FilterChoice(kind).convert(kind) == kind || FilterChoice(kind) == .condition)76 }77 }7879 @Test func aNewEmptyExcludeFilterKeepsEveryRow() async throws {80 let session = makeSession()81 let range = fillSales(session)82 let controller = PivotController.of(session)83 controller.debounce = .milliseconds(5)84 let object = try await controller.create(source: .range(sheetID: session.sheet.id, range: range, hasHeader: true, dateColumns: []), destination: .cell(CellRef(0, 6)))85 let before = session.sheet.value(4, 7)86 controller.edit(object.id) { $0.add("Product", type: .string, to: .filters) }87 await controller.settle(object.id)88 #expect(controller.errors[object.id] == nil)89 #expect(session.sheet.value(4, 7) == before)90 controller.edit(object.id) { $0.filters[0].kind = .include([.string("Widget")]) }91 await controller.settle(object.id)92 #expect(session.sheet.value(4, 7) != before)93 controller.edit(object.id) { $0.values = [] } // an unusable spec is reported, the sheet keeps the last result94 await controller.settle(object.id)95 #expect(controller.errors[object.id] != nil)96 controller.discardDraft(object.id)97 #expect(controller.spec(for: object)?.values.isEmpty == false)98 }99}100101@Suite struct SmallLogicTests {102 @Test func sqlHighlighterClassifiesTokens() {103 let sql = "SELECT \"Unit Price\", 'it''s' -- note\nFROM t WHERE x >= 12.5 /* c */"104 let tokens = SQLHighlighter.tokens(sql)105 let ns = sql as NSString106 let seen = tokens.map { (ns.substring(with: $0.range), $0.token) }107 #expect(seen.contains { $0.0 == "SELECT" && $0.1 == .keyword })108 #expect(seen.contains { $0.0 == "\"Unit Price\"" && $0.1 == .identifier })109 #expect(seen.contains { $0.0 == "'it''s'" && $0.1 == .string })110 #expect(seen.contains { $0.0 == "-- note" && $0.1 == .comment })111 #expect(seen.contains { $0.0 == "12.5" && $0.1 == .number })112 #expect(seen.contains { $0.0 == "/* c */" && $0.1 == .comment })113 #expect(!seen.contains { $0.0 == "t" || $0.0 == "x" })114 #expect(SQLHighlighter.tokens("select 'unterminated").last?.token == .string)115 #expect(SQLHighlighter.tokens("").isEmpty)116 }117118 @Test func staticProviderServesWindows() throws {119 let table = DataTable(columns: [DataColumn(name: "n", storage: .int64(Array(0..<25))), DataColumn(name: "s", storage: .string((0..<25).map { "v\($0)" }), valid: (0..<25).map { $0 != 3 })])120 let provider = StaticPageProvider(table: table)121 #expect(try provider.count(GridQuery(), schema: []) == 25)122 #expect(try provider.schema(of: .rows(UUID())).map(\.name) == ["n", "s"])123 let page = try provider.page(GridQuery(), schema: [], offset: 20, limit: 10)124 #expect(page.rowCount == 5 && page.value(row: 4, column: 0) == .int(24))125 #expect(try provider.page(GridQuery(), schema: [], offset: 0, limit: 5).value(row: 3, column: 1) == .null)126 #expect(try provider.page(GridQuery(), schema: [], offset: 40, limit: 5).rowCount == 0)127 #expect(!GridSource.rows(UUID()).isQueryable && GridSource.dataset("t").isQueryable)128 #expect(DatasetGridModel.tsv(table.slice(0..<2), includeHeader: true) == "n\ts\n0\tv0\n1\tv1")129 }130131 @Test func cellTextAndSummaries() {132 #expect(Fmt.cell(.double(48829418.52012345)) == "48829418.520123")133 #expect(Fmt.cell(.double(2.5)) == "2.5")134 #expect(Fmt.cell(.double(3)) == "3")135 #expect(Fmt.cell(.int(1_000_000)) == "1000000")136 #expect(Fmt.cell(.string("x")) == "x")137 #expect(Fmt.plain(12.5) == "12.5" && Fmt.plain(10) == "10")138 for step in StepDraftTests.samples { #expect(!StepSummary.detail(step).isEmpty || step == .filter(FilterGroup(.all, []))) }139 #expect(StepSummary.detail(.renameColumn(from: "a", to: "b")) == "a → b")140 #expect(StepSummary.detail(.sort([SortSpec("x", ascending: false)])) == "x ↓")141 }142143 /// Every English key used with `L(…)` in the sources has a Canadian French translation with the same placeholders.144 @Test func everyStringHasAFrenchTranslation() throws {145 let sources = URL(fileURLWithPath: #filePath).deletingLastPathComponent().deletingLastPathComponent().deletingLastPathComponent().appendingPathComponent("Sources/FeatureData")146 let catalogData = try Data(contentsOf: sources.appendingPathComponent("Resources/Localizable.xcstrings"))147 let parsed = try JSONSerialization.jsonObject(with: catalogData)148 let catalog = try #require(parsed as? [String: Any])149 let strings = try #require(catalog["strings"] as? [String: Any])150 func french(_ key: String) -> String? {151 (((strings[key] as? [String: Any])?["localizations"] as? [String: Any])?["fr-CA"] as? [String: Any]).flatMap { ($0["stringUnit"] as? [String: Any])?["value"] as? String }152 }153 let call = try NSRegularExpression(pattern: #"(?<![A-Za-z0-9_.])L\("((?:[^"\\]|\\.)*)""#)154 let placeholder = try NSRegularExpression(pattern: "%[@d]")155 var keys = Set<String>()156 let files = FileManager.default.enumerator(at: sources, includingPropertiesForKeys: nil)!.compactMap { $0 as? URL }.filter { $0.pathExtension == "swift" }157 #expect(files.count > 20)158 for file in files {159 let text = try String(contentsOf: file, encoding: .utf8)160 for match in call.matches(in: text, range: NSRange(text.startIndex..., in: text)) {161 let raw = (text as NSString).substring(with: match.range(at: 1))162 keys.insert(raw.replacingOccurrences(of: "\\\"", with: "\"").replacingOccurrences(of: "\\n", with: "\n").replacingOccurrences(of: "\\\\", with: "\\"))163 }164 }165 #expect(keys.count > 400)166 for key in keys.sorted() {167 guard let translation = french(key) else { Issue.record("no fr-CA translation for “\(key)”"); continue }168 func placeholders(_ s: String) -> [String] { placeholder.matches(in: s, range: NSRange(s.startIndex..., in: s)).map { (s as NSString).substring(with: $0.range) } }169 #expect(placeholders(key) == placeholders(translation), "placeholders differ for “\(key)”")170 #expect(!translation.isEmpty)171 }172 #expect(Set(strings.keys).subtracting(keys).isEmpty, "the catalog has unused keys")173 }174}175176@MainActor @Suite struct TransformStudioTests {177 func setUp() async throws -> (WorkbookSession, TransformStudioModel, URL) {178 let dir = TestFiles.directory()179 let session = makeSession()180 let ws = DataWorkspace.of(session)181 try await ws.importFile(ImportRequest(url: TestFiles.ordersCSV(600, in: dir), name: "orders", format: .csv))182 try await ws.importFile(ImportRequest(url: TestFiles.ordersCSV(40, in: dir, name: "more.csv"), name: "more", format: .csv))183 return (session, TransformStudioModel.of(session), dir)184 }185186 @Test func validatesPreviewsAndPersistsPipelines() async throws {187 let (session, model, dir) = try await setUp()188 defer { try? FileManager.default.removeItem(at: dir) }189 model.newPipeline(dataset: "orders")190 await waitUntil("source") { model.isSettled && !model.diagnostics.isEmpty }191 #expect(model.sourceBadge == .ok(columns: 6))192 #expect(model.preview.totalRows == 600)193 #expect(TransformStore.all(in: session.workbook).map(\.name) == ["orders pipeline"])194195 model.addStep(.filter)196 model.addStep(.calculatedColumn)197 model.addStep(.groupBy)198 await waitUntil("steps") { model.isSettled }199 #expect(model.current?.steps.count == 3 && model.selectedStep == 2)200 #expect((0..<3).map { model.badge(forStep: $0) } == [.ok(columns: 6), .ok(columns: 7), .ok(columns: 2)])201 #expect(model.isValid)202 #expect(model.inputSchema(forStep: 2).count == 7) // the form of step 3 sees the calculated column203 #expect(model.stepSQL?.contains("s3") == true && model.fullSQL == model.stepSQL)204 #expect(TransformStore.all(in: session.workbook).first?.steps.count == 3)205206 // editing through the form: a broken expression is refused by the engine's scanner and the step is kept207 model.select(step: 1)208 var draft = try #require(model.draft)209 draft.values["expression"] = .text("quantity * (amount")210 model.update(draft)211 #expect(model.draftError != nil)212 #expect(model.current?.steps[1] == TransformStore.all(in: session.workbook).first?.steps[1])213 // an expression that parses but does not bind is attributed to its step; later steps are blocked214 draft.values["expression"] = .text("quantity * no_such_column")215 model.update(draft)216 await waitUntil("invalid") { model.isSettled && model.badge(forStep: 1) != .ok(columns: 7) }217 guard case .error(let message) = model.badge(forStep: 1) else { Issue.record("step 2 should be in error"); return }218 #expect(message.contains("no_such_column"))219 #expect(model.badge(forStep: 0) == .ok(columns: 6) && model.badge(forStep: 2) == .blocked)220 #expect(!model.isValid && model.previewProblem?.contains("no_such_column") == true)221 model.select(step: 0) // the step before the error still previews222 await waitUntil("preview before the error") { model.isSettled && model.previewProblem == nil }223 #expect(model.stepSQL != nil && model.fullSQL == nil)224225 // reorder and delete226 model.removeStep(1)227 await waitUntil("removed") { model.isSettled }228 #expect(model.isValid && model.current?.steps.map { StepKind($0) } == [.filter, .groupBy])229 model.moveStep(0, by: 1)230 await waitUntil("moved") { model.isSettled }231 #expect(model.current?.steps.map { StepKind($0) } == [.groupBy, .filter])232 #expect(TransformStore.all(in: session.workbook).first?.steps.map { StepKind($0) } == [.groupBy, .filter])233 }234235 @Test func rangeAndFileSourcesAndOutputs() async throws {236 let (session, model, dir) = try await setUp()237 defer { try? FileManager.default.removeItem(at: dir) }238 let range = fillSales(session)239 session.select(range)240 model.newPipelineFromSelection()241 await waitUntil("range source") { model.isSettled && !model.diagnostics.isEmpty }242 #expect(model.sourceBadge == .ok(columns: 4))243 #expect(model.preview.totalRows == 24)244 #expect(model.sourceDescription(model.current!.source).title == "Sheet1!A1:D25")245 model.addStep(.limit)246 var draft = try #require(model.draft)247 draft.values["count"] = .text("5")248 model.update(draft)249 await waitUntil("limit") { model.isSettled && model.preview.totalRows == 5 }250251 // the worksheet changed: the source is re-read only on Refresh252 session.perform("Edit") { $0.set(sheet: 0, row: 1, col: 0, .value(.text("Changed"))) }253 model.refreshSource()254 await waitUntil("refreshed") { model.isSettled }255 _ = model.preview.value(row: 0, column: 0)256 await waitUntil("page") { model.preview.isLoaded(row: 0) }257 #expect(model.preview.value(row: 0, column: 0) == .string("Changed"))258259 // outputs: dataset and worksheet260 model.beginSaveDataset()261 model.saveName = "top5"262 model.commitSaveDataset()263 await waitUntil("saved") { model.workspace.rowCount("top5") == 5 }264 #expect(model.workspace.catalog["top5"] != nil)265 let placed = try await model.workspace.materialize(sql: try #require(model.fullSQL), destination: .newSheet(name: "Out"), label: "Insert Data")266 #expect(placed.range.rows == 6 && session.workbook.sheets[placed.sheet].value(1, 0) == .text("Changed"))267268 // a file source is registered as a view of the file269 model.newPipeline(source: .file(url: dir.appendingPathComponent("more.csv"), format: .csv, options: .init()), name: "file")270 await waitUntil("file source") { model.isSettled && !model.diagnostics.isEmpty }271 #expect(model.sourceBadge == .ok(columns: 6) && model.preview.totalRows == 40)272 #expect(model.pipelines.count == 2)273 model.deleteCurrent()274 #expect(model.pipelines.count == 1 && TransformStore.all(in: session.workbook).count == 1)275276 // a missing dataset source is reported on the source row277 model.newPipeline(dataset: "nowhere")278 await waitUntil("missing source") { model.isSettled && !model.diagnostics.isEmpty }279 guard case .error = model.sourceBadge else { Issue.record("the source should be in error"); return }280 #expect(!model.isValid)281 }282}283284@MainActor @Suite struct DataStudioModelTests {285 @Test func sqlConsoleRunsSelectsScriptsAndReportsErrors() async throws {286 let dir = TestFiles.directory()287 defer { try? FileManager.default.removeItem(at: dir) }288 let session = makeSession()289 let ws = DataWorkspace.of(session)290 try await ws.importFile(ImportRequest(url: TestFiles.ordersCSV(900, in: dir), name: "orders", format: .csv))291 let studio = DataStudioModel.of(session)292 studio.select("orders")293 #expect(studio.currentSQL() == nil) // schema still loading294 await waitUntil("grid") { !studio.grid.isLoading }295 #expect(studio.currentSQL() == "SELECT * FROM \"orders\"")296297 studio.mode = .sql298 studio.sqlText = "SELECT region, count(*) AS n FROM orders GROUP BY region ORDER BY region;"299 studio.runSQL()300 await waitUntil("select") { !studio.sqlRunning && !studio.results.isLoading }301 #expect(studio.sqlError == nil && studio.hasResult && studio.results.totalRows == 4)302 #expect(studio.results.schema.map(\.name) == ["region", "n"])303 #expect(studio.resultSQL != nil && studio.history.count == 1 && studio.history[0].rows == 4 && !studio.history[0].failed)304 #expect(!ws.datasetNames.contains(DataStudioModel.resultRelation)) // the result table is not listed as a dataset305 studio.results.setFilter(ColumnFilter(column: "region", kind: .contains, text: "th"))306 await waitUntil("filtered") { !studio.results.isLoading && studio.results.totalRows == 2 }307 studio.beginSaveResult()308 studio.saveName = "north_south"309 studio.commitSaveResult()310 await waitUntil("saved") { ws.rowCount("north_south") == 2 }311 guard case .query(let stored)? = ws.catalog["north_south"]?.origin else { Issue.record("a query link is expected"); return }312 #expect(stored.contains("FROM orders GROUP BY region") && !stored.contains(DataStudioModel.resultRelation))313314 studio.sqlText = "CREATE TABLE small AS SELECT * FROM orders LIMIT 7; SELECT count(*) AS c FROM small"315 studio.runSQL()316 await waitUntil("script") { !studio.sqlRunning && !studio.results.isLoading }317 #expect(studio.sqlError == nil && studio.resultSQL == nil && studio.results.totalRows == 1)318 _ = studio.results.value(row: 0, column: 0)319 await waitUntil("static page") { studio.results.isLoaded(row: 0) }320 #expect(studio.results.value(row: 0, column: 0) == .int(7))321 #expect(ws.datasetNames.contains("small")) // tables created by SQL appear in the list322 #expect(studio.currentSQL() == nil)323324 studio.sqlText = "SELECT nope FROM orders"325 studio.runSQL()326 await waitUntil("error") { !studio.sqlRunning }327 #expect(studio.sqlError?.contains("nope") == true)328 #expect(studio.history.count == 3 && studio.history[0].failed)329 studio.sqlText = "SELECT * FROM read_csv('/etc/hosts')" // file access stays blocked by the engine330 studio.runSQL()331 await waitUntil("blocked") { !studio.sqlRunning }332 #expect(studio.sqlError != nil)333 }334335 @Test func insertCardMeasuresFirstAndRefusesWhatDoesNotFit() async throws {336 let session = makeSession()337 let ws = DataWorkspace.of(session)338 try await ws.run("make") { try $0.execute("CREATE TABLE big AS SELECT i AS n FROM range(1100000) t(i)") }339 await ws.refreshDatasets()340 let studio = DataStudioModel.of(session)341 studio.select("big")342 await waitUntil("grid") { !studio.grid.isLoading && !studio.grid.schema.isEmpty }343 studio.beginInsert(target: .newSheet)344 await waitUntil("measured") { studio.insertDraft?.rows != nil }345 var card = try #require(studio.insertDraft)346 var plan = try #require(studio.plan(for: card))347 #expect(plan.dataRows == 1_100_000 && plan.columns == 1 && plan.refusal != nil)348 studio.confirmInsert() // refused: nothing happens349 #expect(session.workbook.sheets.count == 1 && studio.insertDraft != nil)350351 studio.insertDraft = nil352 studio.grid.setFilter(ColumnFilter(column: "n", kind: .range, text: "10", upper: "19"))353 await waitUntil("filtered") { !studio.grid.isLoading && studio.grid.totalRows == 10 }354 session.select(cell: CellRef(2, 2))355 studio.beginInsert(target: .selection)356 await waitUntil("measured") { studio.insertDraft?.rows != nil }357 card = try #require(studio.insertDraft)358 plan = try #require(studio.plan(for: card))359 #expect(plan.dataRows == 10 && plan.anchor == CellRef(2, 2) && plan.refusal == nil)360 session.studioPane = "data"361 studio.confirmInsert()362 await waitUntil("inserted") { studio.insertDraft == nil }363 #expect(session.sheet.value(2, 2) == .text("n") && session.sheet.value(12, 2) == .number(19))364 #expect(session.studioPane == nil) // back on the sheet, at the inserted range365 session.undo()366 #expect(session.sheet.cells.usedRange() == nil)367 }368369 @Test func importDraftBuildsEngineOptions() {370 var draft = ImportDraft(url: URL(fileURLWithPath: "/tmp/a.csv"), format: .csv, name: " sales ")371 draft.delimiter = .semicolon; draft.header = .no; draft.allText = true; draft.lazy = true372 #expect(draft.options.delimiter == ";" && draft.options.hasHeader == false && draft.options.allText)373 #expect(draft.request.name == "sales" && draft.request.lazy)374 draft.delimiter = .custom; draft.customDelimiter = "~"; draft.header = .auto375 #expect(draft.options.delimiter == "~" && draft.options.hasHeader == nil)376 var db = ImportDraft(url: URL(fileURLWithPath: "/tmp/a.sqlite"), format: .sqlite, name: "t")377 db.table = "invoices"; db.lazy = true; db.delimiter = .comma378 #expect(db.options.sourceTable == "invoices" && db.options.delimiter == nil && !db.request.lazy && db.isDatabase && !db.supportsLazy)379 }380}381