SPB Git

Repository Public

DatabaseEvaluation.swift 26.4 KB 502 lines swift

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

Theme