Repository Public
1// SP Notes Core2// File: Sources/SPNotesSync/Repositories/DatabaseEvaluation.swift3// Description: Local evaluation of a database view over the cached rows — filter tree, search, multi-key sort, grouping,4// pagination — mirroring the server's engine (`src/lib/databases/query.ts`, `values.ts`) for the stored5// property types. Relations, rollups and formulas are computed by the server only: a rule or a sort on6// them is not applied and the result says so (`isExact == false`); ask the server when online.7//8// Author: Simon-Pierre Boucher9// Contact: contact@spboucher.ai10import Foundation11import SPNotesDocument1213/// A property of a database, as the evaluator needs it.14public struct DatabasePropertyInfo: Sendable, Equatable, Identifiable {15 public struct Option: Sendable, Equatable, Identifiable {16 public var id: String17 public var name: String18 public var color: String?19 public init(id: String, name: String, color: String? = nil) { self.id = id; self.name = name; self.color = color }20 }2122 public var id: String23 public var name: String24 /// `title text number date checkbox status select multi_select url file relation rollup formula created_time last_edited_time ai`.25 public var type: String26 public var options: [Option]27 /// Number format: `number` (default), `integer`, `percent`, `currency`.28 public var format: String?29 public var includeTime: Bool30 public var position: Double3132 public init(id: String, name: String, type: String, options: [Option] = [], format: String? = nil, includeTime: Bool = false, position: Double = 0) {33 self.id = id; self.name = name; self.type = type; self.options = options; self.format = format; self.includeTime = includeTime; self.position = position34 }3536 /// Builds the property from its row (`{id, name, type, config: {options, format, includeTime}, position}`).37 public init?(json: JSONValue) {38 guard let id = json["id"]?.stringValue, let type = json["type"]?.stringValue else { return nil }39 let config = json["config"]40 let options = (config?["options"]?.arrayValue ?? []).compactMap { option -> Option? in41 guard let id = option["id"]?.stringValue else { return nil }42 return Option(id: id, name: option["name"]?.stringValue ?? id, color: option["color"]?.stringValue)43 }44 self.init(id: id, name: json["name"]?.stringValue ?? "", type: type, options: options, format: config?["format"]?.stringValue,45 includeTime: config?["includeTime"]?.boolValue ?? false, position: json["position"]?.doubleValue ?? 0)46 }4748 func option(_ value: JSONValue?) -> Option? {49 guard let text = value?.stringValue else { return nil }50 return options.first { $0.id == text || $0.name == text }51 }5253 /// Computed by the server only: not evaluable on the device.54 public var isServerComputed: Bool { ["relation", "rollup", "formula"].contains(type) }55}5657/// A row of a database with its stored values (keyed by property id).58public struct DatabaseRowValues: Sendable, Equatable, Identifiable {59 public var id: String60 public var pageId: String?61 /// The title of the row's page.62 public var title: String63 public var values: [String: JSONValue]64 public var position: Double65 public var createdAt: Date?66 public var updatedAt: Date?6768 public init(id: String, pageId: String? = nil, title: String, values: [String: JSONValue] = [:], position: Double = 0, createdAt: Date? = nil, updatedAt: Date? = nil) {69 self.id = id; self.pageId = pageId; self.title = title; self.values = values; self.position = position; self.createdAt = createdAt; self.updatedAt = updatedAt70 }71}7273/// A filter tree: `{and: […]}` / `{or: […]}` groups of rules `{property, op, value}`.74public indirect enum DatabaseViewFilter: Sendable, Equatable {75 case rule(property: String, op: String, value: JSONValue?)76 case all([DatabaseViewFilter])77 case any([DatabaseViewFilter])7879 /// Parses the `filter` of a view config. As on the server, a group carrying both keys is an `or`.80 public init?(json: JSONValue?) {81 guard let json, case .object(let object) = json else { return nil }82 if let property = object["property"]?.stringValue {83 self = .rule(property: property, op: object["op"]?.stringValue ?? "", value: object["value"])84 } else if let or = object["or"]?.arrayValue {85 self = .any(or.compactMap { DatabaseViewFilter(json: $0) })86 } else {87 self = .all((object["and"]?.arrayValue ?? []).compactMap { DatabaseViewFilter(json: $0) })88 }89 }9091 /// The JSON the server expects.92 public var json: JSONValue {93 switch self {94 case .rule(let property, let op, let value):95 var object: [String: JSONValue] = ["property": .string(property), "op": .string(op)]96 if let value { object["value"] = value }97 return .object(object)98 case .all(let nodes): return .object(["and": .array(nodes.map(\.json))])99 case .any(let nodes): return .object(["or": .array(nodes.map(\.json))])100 }101 }102103 /// The JSON of a `filter` field (view config, `POST /api/databases/<id>/query`): the server only accepts a GROUP104 /// at the top level — a bare rule there is silently dropped and every row matches — so a rule is wrapped.105 public var topLevelJSON: JSONValue {106 if case .rule = self { return DatabaseViewFilter.all([self]).json }107 return json108 }109}110111public struct DatabaseViewSort: Sendable, Equatable {112 public var property: String113 public var descending: Bool114 public init(property: String, descending: Bool = false) { self.property = property; self.descending = descending }115116 public static func list(json: JSONValue?) -> [DatabaseViewSort] {117 (json?.arrayValue ?? []).compactMap { item in118 guard let property = item["property"]?.stringValue else { return nil }119 return DatabaseViewSort(property: property, descending: item["dir"]?.stringValue == "desc")120 }121 }122123 public var json: JSONValue { .object(["property": .string(property), "dir": .string(descending ? "desc" : "asc")]) }124}125126/// What to evaluate: the fields of a view config, or an ad hoc query.127public struct DatabaseViewQuery: Sendable, Equatable {128 public var filter: DatabaseViewFilter?129 public var sorts: [DatabaseViewSort]130 public var search: String?131 /// Property id to group by (`groupBy` of the view config; the server never groups: this is a client feature on the web too).132 public var groupBy: String?133 public var limit: Int?134 public var offset: Int135136 public init(filter: DatabaseViewFilter? = nil, sorts: [DatabaseViewSort] = [], search: String? = nil, groupBy: String? = nil, limit: Int? = nil, offset: Int = 0) {137 self.filter = filter; self.sorts = sorts; self.search = search; self.groupBy = groupBy; self.limit = limit; self.offset = offset138 }139140 /// From the `config` of a view (`filter, sorts, groupBy`).141 public init(viewConfig: JSONValue?) {142 self.init(filter: DatabaseViewFilter(json: viewConfig?["filter"]), sorts: DatabaseViewSort.list(json: viewConfig?["sorts"]), groupBy: viewConfig?["groupBy"]?.stringValue)143 }144}145146public struct DatabaseGroup: Sendable, Equatable, Identifiable {147 /// The option id, `"true"` / `"false"` for a checkbox, the display value otherwise; `""` = no value.148 public var id: String149 public var label: String150 public var color: String?151 public var rows: [DatabaseRowValues]152}153154public struct LocalDatabaseResult: Sendable, Equatable {155 /// The page of rows asked for (`offset`, `limit`), filtered and sorted.156 public var rows: [DatabaseRowValues]157 /// Rows matching before pagination.158 public var total: Int159 /// The groups of ALL matching rows when `groupBy` is set (a multi-select row appears in each of its options).160 public var groups: [DatabaseGroup]?161 /// False when a rule or a sort could not be evaluated on the device (server-computed property): the rule was not162 /// applied / the sort key ignored. The list is then a superset in another order — ask the server when online.163 public var isExact: Bool164 /// The names of the properties that made the result inexact.165 public var notEvaluated: [String]166}167168/// The server's filter / sort semantics, on the device.169public enum DatabaseEvaluator {170 /// The time zone the server computes calendar days in (`TZ` of the backend).171 public static let serverTimeZone = TimeZone(identifier: "America/Toronto") ?? .current172173 public static func evaluate(properties: [DatabasePropertyInfo], rows: [DatabaseRowValues], query: DatabaseViewQuery = DatabaseViewQuery(),174 timeZone: TimeZone = serverTimeZone, now: Date = Date()) -> LocalDatabaseResult {175 let context = Context(properties: Dictionary(properties.map { ($0.id, $0) }, uniquingKeysWith: { a, _ in a }), timeZone: timeZone, now: now)176 var skipped = Set<String>()177 // Rows come in position order (ties: creation date), like `loadRaw`.178 var kept = rows.sorted { ($0.position, $0.createdAt ?? .distantPast, $0.id) < ($1.position, $1.createdAt ?? .distantPast, $1.id) }179 if let filter = query.filter { kept = kept.filter { context.matches(filter, $0, &skipped) } }180 if let search = query.search?.trimmingCharacters(in: .whitespacesAndNewlines).lowercased(), !search.isEmpty {181 kept = kept.filter { row in182 row.title.lowercased().contains(search) || properties.contains { $0.type != "title" && context.display($0, context.value($0, row)).lowercased().contains(search) }183 }184 }185 if !query.sorts.isEmpty {186 let keyed = kept.map { row in (row, query.sorts.map { sort -> SortKey? in187 guard let property = context.properties[sort.property] else { return nil }188 if property.isServerComputed { skipped.insert(property.name); return nil }189 if property.type == "last_edited_time" { skipped.insert(property.name) }190 return context.sortKey(property, context.value(property, row))191 }) }192 kept = keyed.enumerated().sorted { a, b in193 for (index, sort) in query.sorts.enumerated() {194 let x = a.element.1[index], y = b.element.1[index]195 if x == y { continue }196 guard let x else { return false } // empty last, whatever the direction197 guard let y else { return true }198 let ascending = SortKey.less(x, y)199 return sort.descending ? !ascending : ascending200 }201 return a.offset < b.offset // stable: ties keep the position order202 }.map(\.element.0)203 }204 var groups: [DatabaseGroup]?205 if let id = query.groupBy, let property = context.properties[id] {206 if property.isServerComputed { skipped.insert(property.name) } else { groups = context.groups(property, kept) }207 }208 let total = kept.count209 let offset = max(0, query.offset)210 let page = Array(kept.dropFirst(offset).prefix(min(1000, max(1, query.limit ?? 200))))211 return LocalDatabaseResult(rows: page, total: total, groups: groups, isExact: skipped.isEmpty, notEvaluated: skipped.sorted())212 }213214 /// The display string of a stored value (what text operators and the search compare), for the stored types.215 public static func display(_ property: DatabasePropertyInfo, _ value: JSONValue?, timeZone: TimeZone = serverTimeZone) -> String {216 Context(properties: [:], timeZone: timeZone, now: Date()).display(property, value)217 }218219 enum SortKey: Equatable {220 case number(Double)221 case text(String)222223 static func less(_ a: SortKey, _ b: SortKey) -> Bool {224 switch (a, b) {225 case (.number(let x), .number(let y)): return x < y226 default: return a.string.compare(b.string, locale: Locale(identifier: "en_CA")) == .orderedAscending227 }228 }229 var string: String {230 switch self {231 case .number(let n): return DatabaseEvaluator.plainNumber(n)232 case .text(let s): return s233 }234 }235 }236237 static func plainNumber(_ n: Double) -> String {238 n == n.rounded() && abs(n) < 1e15 ? String(Int64(n)) : String(n)239 }240241 struct Context {242 var properties: [String: DatabasePropertyInfo]243 var timeZone: TimeZone244 var now: Date245246 /// The value the server would see for a property of a row (title and creation time come from the row itself).247 func value(_ property: DatabasePropertyInfo, _ row: DatabaseRowValues) -> JSONValue? {248 switch property.type {249 case "title": return .string(row.title)250 case "created_time": return row.createdAt.map { .string(Self.iso($0)) }251 case "last_edited_time": return row.updatedAt.map { .string(Self.iso($0)) }252 default: return row.values[property.id]253 }254 }255256 static func iso(_ date: Date) -> String {257 let formatter = ISO8601DateFormatter()258 formatter.formatOptions = [.withInternetDateTime, .withFractionalSeconds]259 return formatter.string(from: date)260 }261262 func isEmpty(_ value: JSONValue?) -> Bool {263 guard let value else { return true }264 switch value {265 case .null: return true266 case .string(let s): return s.isEmpty267 case .array(let a): return a.isEmpty268 case .object(let o): return o["pending"]?.boolValue == true || o["error"]?.stringValue != nil // AI cell queued or failed269 default: return false270 }271 }272273 func number(_ value: JSONValue?) -> Double? {274 guard let value else { return nil }275 switch value {276 case .int(let n): return Double(n)277 case .double(let d): return d.isFinite ? d : nil278 case .bool(let b): return b ? 1 : 0279 case .string(let s):280 guard !s.trimmingCharacters(in: .whitespaces).isEmpty else { return nil }281 let cleaned = s.filter { !$0.isWhitespace && $0 != "," && $0 != "$" && $0 != "%" }282 guard let n = Double(cleaned), n.isFinite else { return nil }283 return n284 default: return nil285 }286 }287288 /// `toDate`: a bare `YYYY-MM-DD` is local midnight (server time zone); anything else is an instant.289 func date(_ value: JSONValue?) -> Date? {290 guard let value else { return nil }291 switch value {292 case .int(let ms): return Date(timeIntervalSince1970: Double(ms) / 1000)293 case .double(let ms): return Date(timeIntervalSince1970: ms / 1000)294 case .string(let text):295 guard !text.isEmpty else { return nil }296 if text.range(of: #"^\d{4}-\d{2}-\d{2}$"#, options: .regularExpression) != nil {297 var calendar = Calendar(identifier: .gregorian)298 calendar.timeZone = timeZone299 let parts = text.split(separator: "-").compactMap { Int($0) }300 return calendar.date(from: DateComponents(year: parts[0], month: parts[1], day: parts[2]))301 }302 return Self.parseInstant(text)303 default: return nil304 }305 }306307 static func parseInstant(_ text: String) -> Date? {308 let formatter = ISO8601DateFormatter()309 for options: ISO8601DateFormatter.Options in [[.withInternetDateTime, .withFractionalSeconds], [.withInternetDateTime]] {310 formatter.formatOptions = options311 if let date = formatter.date(from: text) { return date }312 }313 return nil314 }315316 func filterDate(_ value: JSONValue?) -> Date? { value?.stringValue == "today" ? now : date(value) }317318 /// The calendar day of an instant, in the server's time zone.319 func day(_ date: Date) -> Int {320 var calendar = Calendar(identifier: .gregorian)321 calendar.timeZone = timeZone322 let c = calendar.dateComponents([.year, .month, .day], from: date)323 return (c.year ?? 0) * 10_000 + (c.month ?? 0) * 100 + (c.day ?? 0)324 }325326 func display(_ property: DatabasePropertyInfo, _ value: JSONValue?) -> String {327 guard let value, !value.isNull else { return "" }328 if case .object(let object) = value {329 if object["pending"]?.boolValue == true { return "…" }330 if let error = object["error"]?.stringValue { return "#ERR \(error)" }331 }332 if case .string(let s) = value, s.isEmpty { return "" }333 switch property.type {334 case "number": return number(value).map { formatNumber($0, property.format) } ?? ""335 case "date", "created_time", "last_edited_time":336 guard let date = date(value) else { return "" }337 let text = value.stringValue ?? ""338 let hasTime = property.includeTime || property.type != "date" || (text.contains("T") && text.range(of: #"T00:00(:00)?"#, options: .regularExpression) == nil)339 let formatter = DateFormatter()340 formatter.locale = Locale(identifier: "en_CA")341 formatter.timeZone = timeZone342 formatter.dateStyle = .medium343 formatter.timeStyle = hasTime ? .short : .none344 return formatter.string(from: date)345 case "checkbox": return truthy(value) ? "Yes" : "No"346 case "select", "status": return property.option(value)?.name ?? scalar(value)347 case "multi_select": return (value.arrayValue ?? [value]).map { property.option($0)?.name ?? scalar($0) }.joined(separator: ", ")348 case "file": return (value.arrayValue ?? []).compactMap { $0["name"]?.stringValue }.joined(separator: ", ")349 case "relation": return (value.arrayValue ?? []).compactMap { $0["title"]?.stringValue }.joined(separator: ", ")350 default:351 if let array = value.arrayValue { return array.map(scalar).joined(separator: ", ") }352 return scalar(value)353 }354 }355356 func scalar(_ value: JSONValue) -> String {357 switch value {358 case .string(let s): return s359 case .int(let n): return String(n)360 case .double(let d): return DatabaseEvaluator.plainNumber(d)361 case .bool(let b): return b ? "true" : "false"362 case .null: return ""363 default: return value.canonicalString()364 }365 }366367 func truthy(_ value: JSONValue?) -> Bool {368 guard let value else { return false }369 switch value {370 case .null: return false371 case .bool(let b): return b372 case .int(let n): return n != 0373 case .double(let d): return d != 0374 case .string(let s): return !s.isEmpty375 default: return true376 }377 }378379 func formatNumber(_ n: Double, _ format: String?) -> String {380 let formatter = NumberFormatter()381 formatter.locale = Locale(identifier: "en_CA")382 switch format {383 case "integer": formatter.numberStyle = .decimal; formatter.maximumFractionDigits = 0; formatter.usesGroupingSeparator = false384 case "percent": formatter.numberStyle = .percent; formatter.maximumFractionDigits = 1; return formatter.string(from: NSNumber(value: n / 100)) ?? ""385 case "currency": formatter.numberStyle = .currency; formatter.currencyCode = "CAD"; formatter.maximumFractionDigits = 2386 default: formatter.numberStyle = .decimal; formatter.maximumFractionDigits = 6; formatter.usesGroupingSeparator = abs(n) >= 10_000387 }388 return formatter.string(from: NSNumber(value: n)) ?? ""389 }390391 func sortKey(_ property: DatabasePropertyInfo, _ value: JSONValue?) -> SortKey? {392 guard !isEmpty(value) else { return nil }393 switch property.type {394 case "number": return number(value).map(SortKey.number)395 case "date", "created_time", "last_edited_time": return date(value).map { .number($0.timeIntervalSince1970) }396 case "checkbox": return .number(truthy(value) ? 1 : 0)397 case "select", "status":398 let index = property.options.firstIndex { $0.id == value?.stringValue } ?? property.options.count399 return .number(Double(index))400 default: return .text(display(property, value).lowercased())401 }402 }403404 func matches(_ filter: DatabaseViewFilter, _ row: DatabaseRowValues, _ skipped: inout Set<String>) -> Bool {405 switch filter {406 case .any(let nodes):407 if nodes.isEmpty { return true }408 var any = false409 for node in nodes where matches(node, row, &skipped) { any = true }410 return any411 case .all(let nodes):412 var all = true413 for node in nodes where !matches(node, row, &skipped) { all = false }414 return all415 case .rule(let id, let op, let filterValue):416 guard let property = properties[id] else { return true } // unknown property: matches everything, like the server417 if property.isServerComputed { skipped.insert(property.name); return true } // not evaluable here418 if property.type == "last_edited_time" { skipped.insert(property.name) } // the page's own edit date is not in the row419 return matchRule(op, property, value(property, row), filterValue)420 }421 }422423 func matchRule(_ op: String, _ property: DatabasePropertyInfo, _ value: JSONValue?, _ filterValue: JSONValue?) -> Bool {424 func text() -> String { display(property, value).lowercased() }425 func needle() -> String { (filterValue.map(scalar) ?? "").lowercased() }426 func same(_ candidate: JSONValue) -> Bool {427 guard let filterValue, !filterValue.isNull else { return false }428 return candidate == filterValue || (property.option(candidate)?.name).map { JSONValue.string($0) == filterValue } ?? false429 }430 switch op {431 case "empty": return isEmpty(value)432 case "not_empty": return !isEmpty(value)433 case "eq", "neq":434 let result: Bool435 switch property.type {436 case "checkbox": result = truthy(value) == (filterValue == .bool(true) || filterValue == .string("true") || filterValue == .int(1))437 case "select", "status": result = !isEmpty(value) && value.map(same) == true438 case "multi_select": result = (value?.arrayValue ?? []).contains(where: same)439 case "number": result = number(value) == number(filterValue)440 case "date":441 if let a = date(value), let b = filterDate(filterValue) { result = day(a) == day(b) } else { result = false }442 default: result = text() == needle()443 }444 return op == "eq" ? result : !result445 case "contains", "not_contains":446 let result = property.type == "multi_select" ? (value?.arrayValue ?? []).contains(where: same) : text().contains(needle())447 return op == "contains" ? result : !result448 case "starts_with": return text().hasPrefix(needle())449 case "ends_with": return text().hasSuffix(needle())450 case "gt", "gte", "lt", "lte", "before", "after", "on":451 let byDay = ["date", "created_time", "last_edited_time"].contains(property.type) || ["before", "after", "on"].contains(op)452 let a: Double?, b: Double?453 if byDay { a = date(value).map { Double(day($0)) }; b = filterDate(filterValue).map { Double(day($0)) } } else { a = number(value); b = number(filterValue) }454 guard let a, let b else { return false }455 switch op {456 case "gt", "after": return a > b457 case "gte": return a >= b458 case "lt", "before": return a < b459 case "lte": return a <= b460 default: return a == b461 }462 default: return true // unknown operator: matches everything, like the server463 }464 }465466 func groups(_ property: DatabasePropertyInfo, _ rows: [DatabaseRowValues]) -> [DatabaseGroup] {467 var buckets: [String: [DatabaseRowValues]] = [:]468 var labels: [String: String] = [:]469 var order: [String] = []470 func add(_ key: String, _ label: String, _ row: DatabaseRowValues) {471 if buckets[key] == nil { order.append(key); labels[key] = label }472 buckets[key, default: []].append(row)473 }474 switch property.type {475 case "select", "status", "multi_select":476 for option in property.options { order.append(option.id); labels[option.id] = option.name; buckets[option.id] = [] }477 for row in rows {478 let raw = value(property, row)479 let values = property.type == "multi_select" ? (raw?.arrayValue ?? []) : (isEmpty(raw) ? [] : [raw!])480 if values.isEmpty { add("", "", row); continue }481 for item in values {482 if let option = property.option(item) { buckets[option.id, default: []].append(row) } else { let text = scalar(item); add(text, text, row) }483 }484 }485 case "checkbox":486 order = ["true", "false"]; labels = ["true": "Yes", "false": "No"]; buckets = ["true": [], "false": []]487 for row in rows { buckets[truthy(value(property, row)) ? "true" : "false", default: []].append(row) }488 default:489 for row in rows {490 let text = display(property, value(property, row))491 add(text, text, row)492 }493 order.sort { a, b in a.isEmpty ? false : (b.isEmpty ? true : a.compare(b, locale: Locale(identifier: "en_CA")) == .orderedAscending) }494 }495 if let index = order.firstIndex(of: ""), index != order.count - 1 { order.remove(at: index); order.append("") } // "no value" last496 return order.map { key in497 DatabaseGroup(id: key, label: labels[key] ?? key, color: property.options.first { $0.id == key }?.color, rows: buckets[key] ?? [])498 }499 }500 }501}502