Repository Public
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