SPB Git

Repository Public

PivotTests.swift 17.1 KB 263 lines swift

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

Theme