SPB Git

Repository Public

DatabaseEvaluationTests.swift 12.5 KB 176 lines swift

1// SP Notes Core2// File: Tests/SPNotesSyncTests/DatabaseEvaluationTests.swift3// Description: The local evaluation of database views follows the server's rules (query.ts / values.ts): operators per4//              property type, the filter tree, search, multi-key sort with empty values last, grouping, pagination, and5//              the honest "not exact" answer for server-computed properties.6//7// Author: Simon-Pierre Boucher8// Contact: contact@spboucher.ai9import Foundation10import SPNotesDocument11import Testing12@testable import SPNotesSync1314@Suite("Database views evaluated locally (2.0)")15struct DatabaseEvaluationTests {16    typealias P = DatabasePropertyInfo17    let title = P(id: "t", name: "Name", type: "title")18    let year = P(id: "y", name: "Année", type: "number", position: 1)19    let status = P(id: "s", name: "État", type: "status", options: [.init(id: "o1", name: "À lire"), .init(id: "o2", name: "En cours"), .init(id: "o3", name: "Lu")], position: 2)20    let tags = P(id: "g", name: "Thèmes", type: "multi_select", options: [.init(id: "m1", name: "garch"), .init(id: "m2", name: "var")], position: 3)21    let done = P(id: "c", name: "Fait", type: "checkbox", position: 4)22    let due = P(id: "d", name: "Échéance", type: "date", position: 5)23    let note = P(id: "n", name: "Note", type: "text", position: 6)24    let link = P(id: "r", name: "Lié à", type: "relation", position: 7)25    let created = P(id: "ct", name: "Créée", type: "created_time", position: 8)26    var properties: [P] { [title, year, status, tags, done, due, note, link, created] }2728    func row(_ id: String, _ name: String, _ position: Double, _ values: [String: JSONValue] = [:], created: Double = 0) -> DatabaseRowValues {29        DatabaseRowValues(id: id, pageId: "p" + id, title: name, values: values, position: position, createdAt: Date(timeIntervalSince1970: 1_780_000_000 + created * 86_400))30    }3132    var rows: [DatabaseRowValues] {33        [row("1", "Engle (1982)", 1, ["y": 1982, "s": "o3", "g": ["m1"], "c": true, "d": "2026-11-21", "n": "ARCH original"]),34         row("2", "Bollerslev (1986)", 2, ["y": 1986, "s": "o2", "g": ["m1", "m2"], "c": false, "d": "2026-11-25T15:30:00.000Z", "n": "GARCH"], created: 1),35         row("3", "Sims (1980)", 3, ["y": "1,980", "s": "o1", "g": ["m2"], "d": "2026-12-02"], created: 2),36         row("4", "Brouillon", 4, ["s": "", "g": [], "c": false, "n": ""], created: 3),37         row("5", "Nelson (1991)", 5, ["y": 1991.5, "s": "Lu", "g": ["m1"], "c": 1, "n": ["pending": true, "at": "2026-11-19T00:00:00Z"]], created: 4)]38    }3940    func ids(_ filter: DatabaseViewFilter?, sorts: [DatabaseViewSort] = [], search: String? = nil) -> [String] {41        DatabaseEvaluator.evaluate(properties: properties, rows: rows, query: DatabaseViewQuery(filter: filter, sorts: sorts, search: search),42                                   now: Date(timeIntervalSince1970: 1_795_200_000)).rows.map(\.id)43    }44    func rule(_ property: String, _ op: String, _ value: JSONValue? = nil) -> DatabaseViewFilter { .rule(property: property, op: op, value: value) }4546    @Test("no filter: every row in position order")47    func positionOrder() {48        #expect(ids(nil) == ["1", "2", "3", "4", "5"])49        let shuffled = DatabaseEvaluator.evaluate(properties: properties, rows: rows.reversed())50        #expect(shuffled.rows.map(\.id) == ["1", "2", "3", "4", "5"] && shuffled.total == 5 && shuffled.isExact)51    }5253    @Test("empty / not_empty on any type; pending AI values count as empty")54    func emptiness() {55        #expect(ids(rule("y", "empty")) == ["4"])56        #expect(ids(rule("g", "empty")) == ["4"])57        #expect(ids(rule("s", "not_empty")) == ["1", "2", "3", "5"])58        #expect(ids(rule("n", "empty")) == ["3", "4", "5"], "missing, empty string, and a pending cell")59        #expect(ids(rule("c", "empty")) == ["3"])60    }6162    @Test("eq / neq per type: checkbox, select by option id or name, multi-select membership, number, date by calendar day, text case-insensitive")63    func equality() {64        #expect(ids(rule("c", "eq", true)) == ["1", "5"])65        #expect(ids(rule("c", "eq", "true")) == ["1", "5"])66        #expect(ids(rule("c", "neq", true)) == ["2", "3", "4"])67        #expect(ids(rule("s", "eq", "o3")) == ["1"])68        #expect(ids(rule("s", "eq", "Lu")) == ["1", "5"], "by option name: the stored id o3 and a stored name")69        #expect(ids(rule("s", "neq", "Lu")) == ["2", "3", "4"])70        #expect(ids(rule("g", "eq", "garch")) == ["1", "2", "5"])71        #expect(ids(rule("y", "eq", 1980)) == ["3"], "a number stored as text with a separator")72        #expect(ids(rule("y", "eq", "1986")) == ["2"])73        #expect(ids(rule("d", "eq", "2026-11-25")) == ["2"], "same calendar day in the server's time zone")74        #expect(ids(rule("t", "eq", "engle (1982)")) == ["1"])75        #expect(ids(rule("n", "neq", "garch")) == ["1", "3", "4", "5"])76    }7778    @Test("text operators work on the display string; contains on a multi-select is membership")79    func textOperators() {80        #expect(ids(rule("t", "contains", "19")) == ["1", "2", "3", "5"])81        #expect(ids(rule("t", "not_contains", "19")) == ["4"])82        #expect(ids(rule("t", "starts_with", "b")) == ["2", "4"])83        #expect(ids(rule("t", "ends_with", "(1980)")) == ["3"])84        #expect(ids(rule("g", "contains", "m2")) == ["2", "3"])85        #expect(ids(rule("g", "not_contains", "var")) == ["1", "4", "5"])86        #expect(ids(rule("s", "contains", "cours")) == ["2"], "the option name, not its id")87        #expect(DatabaseEvaluator.display(year, 12345.5) == "12,345.5" && DatabaseEvaluator.display(year, 1982) == "1982")88        #expect(DatabaseEvaluator.display(done, true) == "Yes" && DatabaseEvaluator.display(tags, ["m1", "m2"]) == "garch, var")89        #expect(DatabaseEvaluator.display(due, "2026-11-21") == "Nov 21, 2026")90    }9192    @Test("comparisons: numbers; dates by calendar day; before / after / on; the literal today")93    func comparisons() {94        #expect(ids(rule("y", "gt", 1982)) == ["2", "5"])95        #expect(ids(rule("y", "gte", 1982)) == ["1", "2", "5"])96        #expect(ids(rule("y", "lt", 1982)) == ["3"])97        #expect(ids(rule("y", "lte", "1982")) == ["1", "3"])98        #expect(ids(rule("d", "before", "2026-11-25")) == ["1"])99        #expect(ids(rule("d", "after", "2026-11-25")) == ["3"])100        #expect(ids(rule("d", "on", "2026-11-25")) == ["2"])101        #expect(ids(rule("d", "gte", "2026-11-25")) == ["2", "3"])102        // 1 795 200 000 = 2026-11-20 20:00 UTC = 2026-11-20 in Toronto103        #expect(ids(rule("d", "after", "today")) == ["1", "2", "3"])104        #expect(ids(rule("ct", "on", "2026-05-28")) == ["1"], "created_time comes from the row")105        #expect(ids(rule("y", "gt", "abc")) == [], "an unreadable bound matches nothing")106    }107108    @Test("the filter tree: and / or, nesting, an empty or, both keys → or; unknown property or operator matches everything")109    func tree() {110        #expect(ids(.all([rule("g", "contains", "garch"), rule("c", "eq", true)])) == ["1", "5"])111        #expect(ids(.any([rule("y", "lt", 1981), rule("s", "eq", "o2")])) == ["2", "3"])112        #expect(ids(.all([rule("g", "contains", "garch"), .any([rule("y", "lt", 1985), rule("y", "gt", 1990)])])) == ["1", "5"])113        #expect(ids(.any([])) == ["1", "2", "3", "4", "5"])114        #expect(ids(rule("zz", "eq", 1)) == ["1", "2", "3", "4", "5"])115        #expect(ids(rule("y", "between", 1)) == ["1", "2", "3", "4", "5"])116        let both = DatabaseViewFilter(json: ["and": [["property": "y", "op": "eq", "value": 1982]], "or": [["property": "y", "op": "eq", "value": 1986]]])117        #expect(ids(both) == ["2"], "with both keys the server evaluates the or")118        let parsed = DatabaseViewFilter(json: ["and": [["property": "g", "op": "contains", "value": "garch"], ["or": [["property": "c", "op": "eq", "value": true]]]]])119        #expect(parsed == .all([rule("g", "contains", "garch"), .any([rule("c", "eq", true)])]))120        #expect(DatabaseViewFilter(json: parsed?.json) == parsed, "round trip to the JSON the server expects")121    }122123    @Test("search: substring over the title and every property's display value")124    func search() {125        #expect(ids(nil, search: "garch") == ["1", "2", "5"])126        #expect(ids(nil, search: " EN COURS ") == ["2"])127        #expect(ids(nil, search: "1991") == ["5"])128        #expect(ids(rule("c", "eq", true), search: "garch") == ["1", "5"])129    }130131    @Test("sorts: multi-key, numbers, dates, select by option order, empty values last in both directions, ties by position")132    func sorting() {133        #expect(ids(nil, sorts: [.init(property: "y")]) == ["3", "1", "2", "5", "4"])134        #expect(ids(nil, sorts: [.init(property: "y", descending: true)]) == ["5", "2", "1", "3", "4"], "empty stays last")135        #expect(ids(nil, sorts: [.init(property: "s")]) == ["3", "2", "1", "5", "4"], "option order; a stored name that is not an id goes after the options")136        #expect(ids(nil, sorts: [.init(property: "d", descending: true)]) == ["3", "2", "1", "4", "5"])137        #expect(ids(nil, sorts: [.init(property: "c"), .init(property: "t", descending: true)]) == ["4", "2", "5", "1", "3"])138        #expect(ids(nil, sorts: [.init(property: "t")]) == ["2", "4", "1", "5", "3"])139        #expect(ids(nil, sorts: [.init(property: "zz")]) == ["1", "2", "3", "4", "5"], "unknown property: position order")140        #expect(DatabaseViewSort.list(json: [["property": "y", "dir": "desc"], ["property": "t", "dir": "asc"]]) == [.init(property: "y", descending: true), .init(property: "t")])141    }142143    @Test("grouping: options in their order, no value last, a multi-select row in each of its groups, checkbox, free values")144    func grouping() {145        let byStatus = DatabaseEvaluator.evaluate(properties: properties, rows: rows, query: DatabaseViewQuery(groupBy: "s")).groups ?? []146        #expect(byStatus.map(\.label) == ["À lire", "En cours", "Lu", ""])147        #expect(byStatus.map { $0.rows.map(\.id) } == [["3"], ["2"], ["1", "5"], ["4"]])148        let byTag = DatabaseEvaluator.evaluate(properties: properties, rows: rows, query: DatabaseViewQuery(groupBy: "g")).groups ?? []149        #expect(byTag.map { $0.rows.map(\.id) } == [["1", "2", "5"], ["2", "3"], ["4"]])150        let byDone = DatabaseEvaluator.evaluate(properties: properties, rows: rows, query: DatabaseViewQuery(groupBy: "c")).groups ?? []151        #expect(byDone.map(\.id) == ["true", "false"] && byDone.map { $0.rows.map(\.id) } == [["1", "5"], ["2", "3", "4"]])152        let filtered = DatabaseEvaluator.evaluate(properties: properties, rows: rows, query: DatabaseViewQuery(filter: rule("c", "eq", true), groupBy: "s")).groups ?? []153        #expect(filtered.map { $0.rows.count } == [0, 0, 2], "groups hold the filtered rows; empty option groups are kept (board columns)")154        let fromConfig = DatabaseViewQuery(viewConfig: ["groupBy": "s", "sorts": [["property": "y", "dir": "desc"]], "filter": ["and": [["property": "y", "op": "not_empty"]]], "hidden": ["n"]])155        #expect(fromConfig.groupBy == "s" && fromConfig.sorts.count == 1 && fromConfig.filter == .all([rule("y", "not_empty")]))156    }157158    @Test("pagination after filter and sort; total counts every match")159    func pagination() {160        let page = DatabaseEvaluator.evaluate(properties: properties, rows: rows, query: DatabaseViewQuery(sorts: [.init(property: "y")], limit: 2, offset: 1))161        #expect(page.rows.map(\.id) == ["1", "2"] && page.total == 5)162        #expect(DatabaseEvaluator.evaluate(properties: properties, rows: rows, query: DatabaseViewQuery(limit: 2, offset: 9)).rows.isEmpty)163    }164165    @Test("server-computed properties: the rule is not applied, the sort key ignored, and the result says it is not exact")166    func serverComputed() {167        let filtered = DatabaseEvaluator.evaluate(properties: properties, rows: rows, query: DatabaseViewQuery(filter: .all([rule("r", "contains", "x"), rule("c", "eq", true)])))168        #expect(filtered.rows.map(\.id) == ["1", "5"] && !filtered.isExact && filtered.notEvaluated == ["Lié à"])169        let sorted = DatabaseEvaluator.evaluate(properties: properties, rows: rows, query: DatabaseViewQuery(sorts: [.init(property: "r"), .init(property: "y")]))170        #expect(sorted.rows.map(\.id) == ["3", "1", "2", "5", "4"] && !sorted.isExact)171        #expect(DatabaseEvaluator.evaluate(properties: properties, rows: rows, query: DatabaseViewQuery(filter: rule("c", "eq", true))).isExact)172        #expect(P(json: ["id": "x", "name": "N", "type": "select", "config": ["options": [["id": "a", "name": "A", "color": "blue"]], "format": "integer"], "position": 2])173                == P(id: "x", name: "N", type: "select", options: [.init(id: "a", name: "A", color: "blue")], format: "integer", position: 2))174    }175}176
Files main
Menu

Theme