Repository Public
1import Foundation2import Testing3import SheetCore4import SheetData5import SheetUI6@testable import FeatureData78@MainActor @Suite struct PivotPayloadTests {9 func spec() -> PivotSpec {10 PivotSpec(name: "Sales", source: .range(sheetID: 1, range: RangeRef(r0: 0, c0: 0, r1: 24, c1: 3), hasHeader: true, dateColumns: [2]),11 rows: [PivotField("Region", sort: .value(index: 0, ascending: false)), PivotField("Day", grouping: .datePart(.quarter))],12 columns: [PivotField("Units", grouping: .bin(start: 0, size: 2), caption: "Units band")],13 values: [PivotValue("Revenue", .sum, showAs: .percentOfRowTotal, numberFormat: "0.0%", name: "Share"), PivotValue("Margin", .average)],14 filters: [PivotFilter("Product", .include([.string("Widget"), .null])), PivotFilter("Region", .top(count: 3, valueIndex: 0, bottom: false)),15 PivotFilter("Units", .condition(.greater, [.int(1)]))],16 calculatedFields: [PivotCalculatedField(name: "Margin", expression: "Revenue / Units")],17 showSubtotals: false, showRowGrandTotals: true, showColumnGrandTotals: false, layout: .compact)18 }1920 @Test func payloadRoundTripsSpecAnchorAndExtent() throws {21 let payload = PivotPayload(spec: spec(), anchor: CellRef(4, 7), rows: 12, columns: 5, refreshedAt: Date(timeIntervalSince1970: 1_700_000_000))22 let decoded = try PivotPayload(payload: try payload.encoded())23 #expect(decoded == payload)24 #expect(decoded.extent == RangeRef(r0: 4, c0: 7, r1: 15, c1: 11))25 #expect(PivotPayload(spec: spec(), anchor: CellRef(0, 0)).extent == nil)2627 let object = SheetObject(kind: PivotSpec.objectKind, name: "Sales", x: 0, y: 0, width: 16, height: 16, payload: try payload.encoded())28 #expect(PivotPayload(object: object) == payload)29 #expect(PivotPayload(object: SheetObject(kind: "chart", name: "c", x: 0, y: 0, width: 1, height: 1, payload: try payload.encoded())) == nil)30 #expect(PivotPayload(object: SheetObject(kind: PivotSpec.objectKind, name: "bad", x: 0, y: 0, width: 1, height: 1, payload: Data("{}".utf8))) == nil)31 }3233 @Test func acceptsABareSpecPayload() throws {34 let bare = spec()35 let decoded = try PivotPayload(payload: try bare.payload())36 #expect(decoded.spec == bare)37 #expect(decoded.anchor == CellRef(0, 0) && decoded.extent == nil)38 }39}4041@MainActor @Suite struct PivotWritingTests {42 func rangeSource(_ session: WorkbookSession, _ range: RangeRef) -> PivotSource {43 .range(sheetID: session.workbook.sheets[0].id, range: range, hasHeader: true, dateColumns: [])44 }4546 func text(_ session: WorkbookSession, _ sheet: Int, _ a1: String) -> String {47 let c = CellRef(a1: a1)!48 return session.workbook.sheets[sheet].value(c.row, c.col).description49 }5051 @Test func createWritesRealCellsWithASuggestedLayout() async throws {52 let session = makeSession()53 let range = fillSales(session)54 let controller = PivotController.of(session)55 let object = try await controller.create(source: rangeSource(session, range), destination: .newSheet)56 #expect(session.workbook.sheets.count == 2 && session.sheetIndex == 1)57 let sheet = session.workbook.sheets[1]58 let stored = try #require(sheet.meta.objects.first)59 let payload = try #require(PivotPayload(object: stored))60 #expect(object.kind == "pivot" && object.name == "PivotTable1")61 #expect(payload.spec.rows.map(\.name) == ["Region"])62 #expect(payload.spec.values.map(\.field) == ["Units"])63 #expect(payload.anchor == CellRef(0, 0))64 #expect(payload.extent == RangeRef(r0: 0, c0: 0, r1: 4, c1: 1)) // header, 3 regions, grand total65 #expect(text(session, 1, "A1") == "Region" && text(session, 1, "B1") == "Sum of Units")66 #expect([text(session, 1, "A2"), text(session, 1, "A3"), text(session, 1, "A4"), text(session, 1, "A5")] == ["East", "North", "South", "Grand Total"])67 #expect(sheet.value(4, 1) == .number(Double((0..<24).map { $0 % 5 + 1 }.reduce(0, +))))68 // header and total styling are real cell styles69 let header = session.workbook.styles[sheet.styleID(0, 0)], total = session.workbook.styles[sheet.styleID(4, 1)]70 #expect(header.bold && header.fill != nil && header.borders.bottom != nil)71 #expect(total.bold && total.borders.top != nil)72 #expect(session.workbook.styles[sheet.styleID(1, 1)].numberFormat == "#,##0.00")73 // the object is a small handle inside the top-left output cell74 #expect(object.width <= 16 && object.height <= 16 && object.x < 80 && object.y < 20)75 #expect(session.selectedObjectID == object.id)76 #expect(controller.fields[object.id]?.map(\.name) == ["Region", "Product", "Units", "Revenue"])7778 session.undo() // one step: sheet, cells and object79 #expect(session.workbook.sheets.count == 1)80 #expect(session.workbook.sheets[0].meta.objects.isEmpty)81 #expect(controller.uniquePivotName() == "PivotTable1")82 }8384 @Test func editingReplacesThePreviousOutputAndUndoRestoresIt() async throws {85 let session = makeSession()86 let range = fillSales(session)87 let controller = PivotController.of(session)88 controller.debounce = .milliseconds(10)89 let object = try await controller.create(source: rangeSource(session, range), destination: .cell(CellRef(1, 6)))90 let id = object.id91 #expect(text(session, 0, "G2") == "Region" && text(session, 0, "G6") == "Grand Total")92 let steps = session.engine.changeCount9394 // wider: a column field → the output grows to the right95 controller.edit(id) { $0.columns = [PivotField("Product")]; $0.values = [PivotValue("Revenue", .sum)] }96 #expect(controller.spec(for: object)?.columns.count == 1) // the builder shows the edit immediately97 await controller.settle(id)98 #expect(controller.errors[id] == nil && controller.drafts[id] == nil)99 var payload = try #require(controller.locate(id)?.payload)100 #expect(payload.extent == RangeRef(r0: 1, c0: 6, r1: 5, c1: 9))101 #expect(text(session, 0, "H2") == "Gadget" && text(session, 0, "I2") == "Widget" && text(session, 0, "J2") == "Grand Total")102 #expect(session.engine.changeCount == steps + 1) // one undoable step per change103104 // smaller: no row field, no totals → the old extent is cleared, nothing stale remains105 controller.edit(id) { $0.rows = []; $0.columns = []; $0.showColumnGrandTotals = false; $0.showRowGrandTotals = false }106 await controller.settle(id)107 payload = try #require(controller.locate(id)?.payload)108 let extent = try #require(payload.extent)109 #expect(extent.rows <= 2 && extent.cols <= 2)110 var stale = 0111 session.sheet.cells.forEach(in: RangeRef(r0: 1, c0: 6, r1: 5, c1: 9)) { r, c, cell in if cell.hasContent, !extent.contains(row: r, col: c) { stale += 1 } }112 #expect(stale == 0)113 #expect(session.workbook.styles[session.sheet.styleID(5, 9)] == .default) // styles of the old extent are gone too114115 session.undo() // back to the 4-column pivot, payload included116 #expect(controller.locate(id)?.payload.extent == RangeRef(r0: 1, c0: 6, r1: 5, c1: 9))117 #expect(text(session, 0, "J2") == "Grand Total")118 #expect(controller.locate(id)?.payload.spec.columns.map(\.name) == ["Product"])119 session.undo()120 #expect(controller.locate(id)?.payload.extent == RangeRef(r0: 1, c0: 6, r1: 5, c1: 7))121 session.undo() // the insertion itself122 #expect(controller.locate(id) == nil)123 #expect(session.sheet.cells.usedRange() == range)124 }125126 @Test func refusesToOverwriteOtherCellsAndChangesNothing() async throws {127 let session = makeSession()128 let range = fillSales(session)129 let controller = PivotController.of(session)130 controller.debounce = .milliseconds(10)131 // creation on top of the source data is refused132 do {133 try await controller.create(source: rangeSource(session, range), destination: .cell(CellRef(2, 2)))134 Issue.record("creation over existing cells should be refused")135 } catch let e as PivotWriteError {136 guard case .wouldOverwrite(let first, let count, _) = e else { Issue.record("unexpected \(e)"); return }137 #expect(first == CellRef(2, 2) && count == 10)138 #expect(e.errorDescription?.contains("C3") == true)139 }140 #expect(session.sheet.meta.objects.isEmpty)141142 let object = try await controller.create(source: rangeSource(session, range), destination: .cell(CellRef(1, 6)))143 session.perform("Note") { $0.set(sheet: 0, row: 3, col: 8, .value(.text("keep me"))) }144 let before = session.engine.changeCount145 controller.edit(object.id) { $0.columns = [PivotField("Product")] } // would grow over I4146 await controller.settle(object.id)147 #expect(controller.errors[object.id]?.contains("I4") == true)148 #expect(session.engine.changeCount == before) // nothing written149 #expect(text(session, 0, "I4") == "keep me")150 #expect(controller.locate(object.id)?.payload.extent == RangeRef(r0: 1, c0: 6, r1: 5, c1: 7))151 #expect(controller.drafts[object.id]?.columns.count == 1) // the edit is kept for the user to resolve152 // clearing the obstacle and refreshing applies the pending edit153 session.perform("Clear") { $0.clear(sheet: 0, range: RangeRef(CellRef(3, 8))) }154 controller.refresh(object.id)155 await controller.settle(object.id)156 #expect(controller.errors[object.id] == nil)157 #expect(controller.locate(object.id)?.payload.extent == RangeRef(r0: 1, c0: 6, r1: 5, c1: 9))158 }159160 @Test func checkCoversLimitsAndThePreviousExtent() throws {161 let session = makeSession()162 fillSales(session, rows: 3)163 // a 2 × 2 grid: header + one total row164 let engine = try DataEngine()165 try engine.execute("CREATE TABLE t AS SELECT 1 AS v")166 let tiny = PivotSpec(source: .dataset("t"), values: [PivotValue("v", .sum)])167 let grid = PivotEngine.renderGrid(try PivotEngine.compute(tiny, workbook: nil, data: engine), spec: tiny)168 #expect(grid.rowCount == 2 && grid.columnCount == 2)169 #expect(throws: PivotWriteError.exceedsSheet(rows: 2, columns: 2, anchor: CellRef(1_048_575, 0))) {170 try PivotWriter.check(grid: grid, anchor: CellRef(1_048_575, 0), previous: nil, sheet: session.sheet)171 }172 #expect(throws: PivotWriteError.self) { try PivotWriter.check(grid: grid, anchor: CellRef(0, 0), previous: nil, sheet: session.sheet) }173 try PivotWriter.check(grid: grid, anchor: CellRef(0, 0), previous: RangeRef(r0: 0, c0: 0, r1: 3, c1: 3), sheet: session.sheet) // its own cells174 try PivotWriter.check(grid: grid, anchor: CellRef(10, 10), previous: nil, sheet: session.sheet)175 #expect(PivotWriter.conflicts(sheet: session.sheet, target: RangeRef(r0: 0, c0: 3, r1: 9, c1: 5), previous: RangeRef(r0: 0, c0: 3, r1: 0, c1: 3))?.count == 3)176 }177178 @Test func refreshPicksUpSourceChangesAndRefreshAllCoversEveryPivot() async throws {179 let session = makeSession()180 let range = fillSales(session)181 let controller = PivotController.of(session)182 let a = try await controller.create(source: rangeSource(session, range), destination: .cell(CellRef(0, 6)))183 let b = try await controller.create(source: rangeSource(session, range), destination: .cell(CellRef(10, 6)))184 let total = session.sheet.value(4, 7)185 session.perform("Edit") { $0.set(sheet: 0, row: 1, col: 2, .value(.number(1_001))) } // North, Units was 1186 #expect(session.sheet.value(4, 7) == total) // output cells are values, not live187 controller.refreshAll()188 await controller.settle(a.id); await controller.settle(b.id)189 guard case .number(let before) = total, case .number(let after) = session.sheet.value(4, 7), case .number(let other) = session.sheet.value(14, 7) else {190 Issue.record("totals should be numbers"); return191 }192 #expect(after == before + 1_000 && other == after)193 }194195 @Test func datasetSourceAndDeleteWithOutput() async throws {196 let dir = TestFiles.directory()197 defer { try? FileManager.default.removeItem(at: dir) }198 let session = makeSession()199 let ws = DataWorkspace.of(session)200 try await ws.importFile(ImportRequest(url: TestFiles.ordersCSV(2_000, in: dir), name: "orders", format: .csv))201 let controller = PivotController.of(session)202 let spec = PivotSpec(name: "ByRegion", source: .dataset("orders"), rows: [PivotField("region")], columns: [PivotField("order_date", grouping: .datePart(.quarter))],203 values: [PivotValue("amount", .sum, numberFormat: "#,##0")])204 let object = try await controller.create(source: spec.source, destination: .cell(CellRef(0, 0)), spec: spec)205 let extent = try #require(controller.locate(object.id)?.payload.extent)206 #expect(extent == RangeRef(r0: 0, c0: 0, r1: 5, c1: 5)) // 4 regions × 4 quarters + totals207 #expect(session.sheet.value(0, 1) == .text("Q1"))208 controller.delete(object.id, clearOutput: true)209 #expect(session.sheet.meta.objects.isEmpty && session.sheet.cells.usedRange() == nil)210 session.undo() // one step brings back object and cells211 #expect(controller.locate(object.id) != nil && session.sheet.value(5, 0) == .text("Grand Total"))212 controller.delete(object.id, clearOutput: false) // keep the values213 #expect(session.sheet.meta.objects.isEmpty && session.sheet.value(5, 0) == .text("Grand Total"))214 }215216 @Test func showDetailsInsertsTheUnderlyingRecordsAsANewSheet() async throws {217 let session = makeSession()218 let range = fillSales(session)219 let controller = PivotController.of(session)220 let spec = PivotSpec(name: "P", source: rangeSource(session, range), rows: [PivotField("Region")], columns: [PivotField("Product")], values: [PivotValue("Revenue", .sum)])221 try await controller.create(source: spec.source, destination: .cell(CellRef(0, 6)), spec: spec)222 // G1:J5 — rows East/North/South, columns Gadget/Widget; I3 = North × Widget223 #expect(text(session, 0, "G3") == "North" && text(session, 0, "I1") == "Widget")224 session.select(cell: CellRef(a1: "G3")!)225 await #expect(throws: DataError.self) { try await controller.showDetails() } // a label, not a value226 session.select(cell: CellRef(a1: "A1")!)227 await #expect(throws: DataError.self) { try await controller.showDetails() } // outside any pivot228229 session.select(cell: CellRef(a1: "I3")!)230 let expected = (0..<24).filter { $0 % 3 == 0 && $0 % 2 == 0 }231 let steps = session.engine.changeCount232 let placed = try await controller.showDetails()233 #expect(session.workbook.sheets.count == 2 && placed.sheet == 1 && session.sheetIndex == 1)234 #expect(session.workbook.sheets[1].name == "Details")235 let details = session.workbook.sheets[1]236 #expect(placed.range == RangeRef(r0: 0, c0: 0, r1: expected.count, c1: 3))237 #expect((0..<4).map { details.value(0, $0).description } == ["Region", "Product", "Units", "Revenue"])238 for r in 1...expected.count { #expect(details.value(r, 0) == .text("North") && details.value(r, 1) == .text("Widget")) }239 var sum = 0.0240 for r in 1...expected.count { if case .number(let n) = details.value(r, 3) { sum += n } }241 #expect(Value.number(sum) == session.workbook.sheets[0].value(2, 8)) // the records add up to the cell242 #expect(session.engine.changeCount == steps + 1)243 session.undo()244 #expect(session.workbook.sheets.count == 1)245246 // a grand total drills into every record247 session.select(cell: CellRef(a1: "J5")!, extend: false)248 let all = try await controller.showDetails()249 #expect(all.range.rows == 25)250 }251252 @Test func suggestedLayoutPrefersLabelsAndMeasures() {253 func col(_ n: String, _ t: DataType) -> ColumnInfo { ColumnInfo(name: n, type: t, declaredType: "") }254 var s = PivotController.suggestedLayout([col("id", .integer), col("region", .string), col("amount", .double)])255 #expect(s.rows.map(\.name) == ["region"] && s.values.map(\.field) == ["amount"] && s.values.first?.aggregation == .sum)256 s = PivotController.suggestedLayout([col("day", .date), col("n", .integer)])257 #expect(s.rows.first?.grouping == .datePart(.year) && s.values.map(\.field) == ["n"])258 s = PivotController.suggestedLayout([col("a", .string), col("b", .string)])259 #expect(s.values.first?.aggregation == .count)260 #expect(PivotController.suggestedLayout([]).values.isEmpty)261 }262}263