Repository Public
1// SP Notes Core2// File: Sources/SPNotesSync/Repositories/DatabasesRepository.swift3// Description: Databases for the apps: definitions, properties, views and rows read offline from the entity copy (with local filter / sort / group evaluation), written online only — the server's answer updates the local copy, and offline a write throws RepositoryError.offline (nothing is queued).4//5// Author: Simon-Pierre Boucher6// Contact: contact@spboucher.ai7import Foundation8import SPNotesAPI9import SPNotesDocument10import SPNotesStore1112/// A database as the device holds it.13public struct LocalDatabase: Sendable, Equatable, Identifiable {14 public var info: DatabaseInfo15 public var properties: [DatabaseProperty]16 public var views: [DatabaseView]17 public var id: String { info.id }18 public var defaultView: DatabaseView? { views.first { $0.isDefault == true } ?? views.first }19}2021/// Rows of a view, and where they come from.22public struct DatabaseRowsResult: Sendable, Equatable {23 public enum Source: Sendable, Equatable {24 /// Computed by the server (formulas, rollups and relations included).25 case server26 /// Evaluated on the device over the cached rows (stored values only).27 case local28 }29 public var rows: [DatabaseRowValues]30 public var total: Int31 public var groups: [DatabaseGroup]?32 public var source: Source33 /// False when the local evaluation could not apply a rule or a sort (server-computed property): see `notEvaluated`.34 public var isExact: Bool35 public var notEvaluated: [String]36}3738public struct DatabasesRepository: Sendable {39 let context: RepositoryContext40 public init(context: RepositoryContext) { self.context = context }4142 // MARK: reading (local, works offline)4344 /// The databases of the local copy, by name.45 public func list() async throws -> [DatabaseInfo] {46 do {47 return context.decodeEntities(DatabaseInfo.self, try await context.store.databases()).filter { $0.deletedAt == nil }48 .sorted { $0.name.localizedCaseInsensitiveCompare($1.name) == .orderedAscending }49 } catch { throw RepositoryError.from(error) }50 }5152 /// A database with its properties and views from the local copy. `id` = the database id or its host page id.53 public func database(id: String) async throws -> LocalDatabase? {54 do {55 guard let info = context.decodeEntities(DatabaseInfo.self, try await context.store.databases()).first(where: { $0.id == id || $0.pageId == id }) else { return nil }56 let properties = context.decodeEntities(DatabaseProperty.self, try await context.store.databaseProperties(databaseId: info.id))57 .sorted { a, b in a.type == "title" ? b.type != "title" : (b.type == "title" ? false : (a.position ?? 0) < (b.position ?? 0)) }58 let views = context.decodeEntities(DatabaseView.self, try await context.store.databaseViews(databaseId: info.id)).sorted { ($0.position ?? 0) < ($1.position ?? 0) }59 return LocalDatabase(info: info, properties: properties, views: views)60 } catch { throw RepositoryError.from(error) }61 }6263 /// The cached rows of a database (stored values; the title is the row's own title value).64 public func localRows(databaseId: String) async throws -> [DatabaseRowValues] {65 do {66 guard let database = try await database(id: databaseId) else { return [] }67 let titleId = database.properties.first { $0.type == "title" }?.id68 var out: [DatabaseRowValues] = []69 var offset = 070 while true {71 let batch = try await context.store.databaseRows(databaseId: database.info.id, limit: 1000, offset: offset)72 for row in context.decodeEntities(DatabaseRow.self, batch) where row.deletedAt == nil { out.append(Self.values(row, titleId: titleId)) }73 if batch.count < 1000 { break }74 offset += 100075 }76 return out77 } catch { throw RepositoryError.from(error) }78 }7980 static func values(_ row: DatabaseRow, titleId: String?) -> DatabaseRowValues {81 let values = (try? JSONValue(data: row.values.data))?.objectValue ?? [:]82 let title = row.title ?? titleId.flatMap { values[$0]?.stringValue } ?? ""83 return DatabaseRowValues(id: row.id, pageId: row.pageId, title: title, values: values, position: row.position ?? 0, createdAt: row.createdAt, updatedAt: row.updatedAt)84 }8586 static func info(_ property: DatabaseProperty) -> DatabasePropertyInfo {87 let config = property.typedConfig88 return DatabasePropertyInfo(id: property.id, name: property.name, type: property.type,89 options: property.options.map { .init(id: $0.id, name: $0.name, color: $0.color) },90 format: (try? JSONValue(data: property.config.data))?["format"]?.stringValue, includeTime: config.includeTime ?? false, position: property.position ?? 0)91 }9293 /// Evaluates a view (or an ad hoc query) on the device over the cached rows — the server's rules for the stored94 /// property types (`DatabaseEvaluator`). `query` nil = the config of `viewId` (the default view when nil).95 public func evaluateLocally(databaseId: String, viewId: String? = nil, query: DatabaseViewQuery? = nil) async throws -> DatabaseRowsResult {96 guard let database = try await database(id: databaseId) else { throw RepositoryError.notFound("database \(databaseId)") }97 let view = viewId.flatMap { id in database.views.first { $0.id == id } } ?? database.defaultView98 let resolved = query ?? DatabaseViewQuery(viewConfig: view.flatMap { try? JSONValue(data: $0.config.data) })99 let result = DatabaseEvaluator.evaluate(properties: database.properties.map(Self.info), rows: try await localRows(databaseId: database.info.id), query: resolved)100 return DatabaseRowsResult(rows: result.rows, total: result.total, groups: result.groups, source: .local, isExact: result.isExact, notEvaluated: result.notEvaluated)101 }102103 /// The rows of a view: asked to the server when it can be reached (computed values included, rows cached),104 /// evaluated locally otherwise. Grouping is always done on the device (the server never groups).105 public func rows(databaseId: String, viewId: String? = nil, search: String? = nil, limit: Int? = nil, offset: Int? = nil) async throws -> DatabaseRowsResult {106 do {107 let answer = try await context.api.queryDatabase(id: databaseId, DatabaseQuery(viewId: viewId, search: search, limit: limit, offset: offset))108 let database = try await self.database(id: databaseId)109 let titleId = database?.properties.first { $0.type == "title" }?.id110 let rows = answer.rows.map { Self.values($0, titleId: titleId) }111 var groups: [DatabaseGroup]?112 if let database, let view = viewId.flatMap({ id in database.views.first { $0.id == id } }) ?? database.defaultView,113 let groupBy = (try? JSONValue(data: view.config.data))?["groupBy"]?.stringValue {114 groups = DatabaseEvaluator.evaluate(properties: database.properties.map(Self.info), rows: rows, query: DatabaseViewQuery(groupBy: groupBy, limit: 1000)).groups115 // The evaluator orders by position; inside a group the rows keep the order the server gave (the view's sorts).116 let order = Dictionary(rows.enumerated().map { ($1.id, $0) }, uniquingKeysWith: { first, _ in first })117 if var sorted = groups {118 for index in sorted.indices { sorted[index].rows.sort { (order[$0.id] ?? .max) < (order[$1.id] ?? .max) } }119 groups = sorted120 }121 }122 return DatabaseRowsResult(rows: rows, total: answer.total, groups: groups, source: .server, isExact: true, notEvaluated: [])123 } catch {124 guard RepositoryError.from(error) == .offline else { throw RepositoryError.from(error) }125 var local = try await evaluateLocally(databaseId: databaseId, viewId: viewId)126 if search != nil || limit != nil || offset != nil, let database = try await database(id: databaseId) {127 let view = viewId.flatMap { id in database.views.first { $0.id == id } } ?? database.defaultView128 var query = DatabaseViewQuery(viewConfig: view.flatMap { try? JSONValue(data: $0.config.data) })129 query.search = search; query.limit = limit; query.offset = offset ?? 0130 local = try await evaluateLocally(databaseId: databaseId, query: query)131 }132 return local133 }134 }135136 // MARK: online reads that refresh the local copy137138 /// Reads the database from the server (definition, properties, views) and refreshes the local copy. The first139 /// opening of a database page creates the database object on the server.140 @discardableResult141 public func refresh(id: String) async throws -> LocalDatabase {142 let info = try await context.online { try await context.api.database(id: id) }143 await cache(info)144 return LocalDatabase(info: info, properties: info.properties ?? [], views: info.views ?? [])145 }146147 private func cache(_ info: DatabaseInfo) async {148 var bare = info149 bare.properties = nil; bare.views = nil150 await context.cacheEntity(kind: .database, id: info.id, row: bare, rev: info.rev)151 for property in info.properties ?? [] { await cache(property) }152 for view in info.views ?? [] { await cache(view) }153 }154 private func cache(_ property: DatabaseProperty) async { await context.cacheEntity(kind: .databaseProperty, id: property.id, row: property, rev: property.rev) }155 private func cache(_ view: DatabaseView) async { await context.cacheEntity(kind: .databaseView, id: view.id, row: view, rev: view.rev) }156 private func cache(_ row: DatabaseRow, databaseId: String) async {157 var stored = row158 if stored.databaseId == nil { stored.databaseId = databaseId }159 await context.cacheEntity(kind: .databaseRow, id: row.id, row: stored, rev: row.rev, index: EntityIndex(scope: stored.databaseId, ref: row.pageId, sort: row.position.map { .number($0) }))160 }161162 // MARK: writing (online only; offline → RepositoryError.offline)163164 @discardableResult165 public func update(id: String, _ changes: DatabaseChanges) async throws -> DatabaseInfo {166 let info = try await context.online { try await context.api.updateDatabase(id: id, changes) }167 await cache(info)168 return info169 }170171 @discardableResult172 public func createProperty(databaseId: String, _ draft: DatabasePropertyDraft) async throws -> DatabaseProperty {173 var draft = draft174 if draft.id == nil { draft.id = NotesAPI.newID() }175 let sent = draft176 let property = try await context.online { try await context.api.createDatabaseProperty(databaseID: databaseId, sent) }177 await cache(property)178 return property179 }180181 @discardableResult182 public func updateProperty(databaseId: String, propertyId: String, _ changes: DatabasePropertyChanges) async throws -> DatabaseProperty {183 let property = try await context.online { try await context.api.updateDatabaseProperty(databaseID: databaseId, propertyID: propertyId, changes) }184 await cache(property)185 return property186 }187188 public func deleteProperty(databaseId: String, propertyId: String) async throws {189 _ = try await context.online { try await context.api.deleteDatabaseProperty(databaseID: databaseId, propertyID: propertyId) }190 await context.uncacheEntity(kind: .databaseProperty, id: propertyId)191 }192193 @discardableResult194 public func createView(databaseId: String, _ draft: DatabaseViewDraft) async throws -> DatabaseView {195 var draft = draft196 if draft.id == nil { draft.id = NotesAPI.newID() }197 let sent = draft198 let view = try await context.online { try await context.api.createDatabaseView(databaseID: databaseId, sent) }199 await cache(view)200 return view201 }202203 /// Changes a view (name, type, filter, sorts, grouping… are saved with the view, like on the web).204 @discardableResult205 public func updateView(databaseId: String, viewId: String, _ changes: DatabaseViewChanges) async throws -> DatabaseView {206 let view = try await context.online { try await context.api.updateDatabaseView(databaseID: databaseId, viewID: viewId, changes) }207 await cache(view)208 return view209 }210211 public func deleteView(databaseId: String, viewId: String) async throws {212 _ = try await context.online { try await context.api.deleteDatabaseView(databaseID: databaseId, viewID: viewId) }213 await context.uncacheEntity(kind: .databaseView, id: viewId)214 }215216 /// Creates a row (and its page) under client-chosen ids: a retry after a lost answer returns the same row.217 @discardableResult218 public func createRow(databaseId: String, _ draft: DatabaseRowDraft) async throws -> DatabaseRow {219 var draft = draft220 if draft.id == nil { draft.id = NotesAPI.newID() }221 if draft.pageId == nil { draft.pageId = NotesAPI.newID() }222 let sent = draft223 let row = try await context.online { try await context.api.createDatabaseRow(databaseID: databaseId, sent) }224 await cache(row, databaseId: databaseId)225 return row226 }227228 /// Changes cell values of a row (`values` = JSON object keyed by property id; only the given keys change).229 @discardableResult230 public func updateRow(databaseId: String, rowId: String, values: RawJSON) async throws -> DatabaseRow {231 let row = try await context.online { try await context.api.updateDatabaseRow(databaseID: databaseId, rowID: rowId, values: values) }232 await cache(row, databaseId: databaseId)233 return row234 }235236 public func deleteRows(databaseId: String, ids: [String]) async throws {237 _ = try await context.online { try await context.api.deleteDatabaseRows(databaseID: databaseId, ids: ids) }238 for id in ids { await context.uncacheEntity(kind: .databaseRow, id: id) }239 }240241 /// Undoes `deleteRows` (the rows come back through the next cycle, requested here).242 public func restoreRows(databaseId: String, ids: [String]) async throws {243 _ = try await context.online { try await context.api.restoreDatabaseRows(databaseID: databaseId, ids: ids) }244 await context.scheduleSync()245 }246}247