Repository Public
1// SP Notes Core2// File: Sources/SPNotesFeatures/Work/Database/DatabaseModel.swift3// Description: View model of a database: definition, the six view kinds, an editable draft of filter / sorts / grouping4// (a local preview until saved in the view, which needs the server), rows asked to the server when it can5// be reached (computed values included) and evaluated on the device otherwise (flagged approximate when a6// relation, rollup or formula is involved), online row / property / view writes, multi-selection with bulk7// actions and undoable deletion.8//9// Author: Simon-Pierre Boucher10// Contact: contact@spboucher.ai11import Foundation12import Observation13import SPNotesAPI14import SPNotesDocument15import SPNotesSync1617@Observable @MainActor18public final class DatabaseModel {19 public enum Inspector: Equatable, Sendable {20 case none21 /// Filter / sort / group builders and the structure (properties).22 case builder23 case row(String)24 }2526 public struct CellRef: Equatable, Hashable, Sendable {27 public var rowId: String28 public var propertyId: String29 public init(rowId: String, propertyId: String) { self.rowId = rowId; self.propertyId = propertyId }30 }3132 // MARK: state3334 /// The database id or the id of its host page.35 public let databaseId: String36 public private(set) var database: LocalDatabase?37 public private(set) var rows: [DatabaseRowValues] = []38 public private(set) var groups: [DatabaseGroup] = []39 public private(set) var total = 040 public private(set) var source: DatabaseRowsResult.Source?41 /// False when the device could not apply a rule or a sort (server-computed property): the list is approximate.42 public private(set) var isExact = true43 public private(set) var notEvaluated: [String] = []44 public private(set) var phase: LoadPhase = .idle45 public let freshness: Freshness? = nil46 public var actionFailure: FeatureFailure?47 /// The server could not be reached at the last attempt: reading continues from the device, writes are disabled.48 public private(set) var isOffline = false49 public private(set) var isWorking = false50 public private(set) var selectedViewId: String?51 public var draft = DatabaseQueryDraft()52 public var search = ""53 public var selection: Set<String> = []54 public var isSelecting = false55 public var inspector: Inspector = .none56 public var editingCell: CellRef?57 /// Rows deleted by the last deletion, restorable.58 public private(set) var lastDeleted: [String] = []59 public var calendarMonth: DatabaseMonth60 public var calendarSelectedDay: String?61 public var newRowTitle = ""6263 private let repo: DatabasesRepository64 private let repositories: NotesRepositories65 private weak var router: (any FeatureRouter)?66 private let now: @Sendable () -> Date67 private var fetchGeneration = 06869 public init(repositories: NotesRepositories, databaseId: String, router: (any FeatureRouter)? = nil, now: @escaping @Sendable () -> Date = { Date() }) {70 self.repositories = repositories71 repo = repositories.databases72 self.databaseId = databaseId73 self.router = router74 self.now = now75 calendarMonth = DatabaseMonth(now(), timeZone: DatabaseEvaluator.serverTimeZone)76 }7778 private struct Fetched {79 var rows: [DatabaseRowValues]80 var total: Int81 var source: DatabaseRowsResult.Source82 var isExact: Bool83 var notEvaluated: [String]8485 init(rows: [DatabaseRowValues], total: Int, source: DatabaseRowsResult.Source, isExact: Bool, notEvaluated: [String]) {86 self.rows = rows; self.total = total; self.source = source; self.isExact = isExact; self.notEvaluated = notEvaluated87 }8889 init(_ result: DatabaseRowsResult) {90 self.init(rows: result.rows, total: result.total, source: result.source, isExact: result.isExact, notEvaluated: result.notEvaluated)91 }92 }9394 // MARK: derived9596 public var properties: [DatabaseProperty] { database?.properties ?? [] }97 public var views: [DatabaseView] { database?.views ?? [] }98 public var currentView: DatabaseView? { views.first { $0.id == selectedViewId } ?? database?.defaultView }99 public var viewType: String { currentView?.type ?? DatabaseViewType.table }100 public var viewConfig: DatabaseViewConfig { currentView?.typedConfig ?? DatabaseViewConfig() }101 public var titleProperty: DatabaseProperty? { properties.first { $0.type == DatabasePropertyType.title } }102 public var canWrite: Bool { !isOffline && database != nil }103 public var todayDay: String { WorkDay.string(now(), timeZone: DatabaseEvaluator.serverTimeZone) }104105 private var savedDraft: DatabaseQueryDraft { DatabaseQueryDraft(config: currentView.flatMap { try? JSONValue(data: $0.config.data) }) }106107 /// True when the builders differ from the saved view: what is shown is a preview.108 public var isDraftDirty: Bool { !draft.isSameQuery(as: savedDraft) }109110 /// The properties shown by the current view, in its order, without the hidden ones.111 public var visibleProperties: [DatabaseProperty] {112 let config = viewConfig113 let hidden = Set(config.hidden ?? [])114 var list = properties.filter { !hidden.contains($0.id) }115 if let order = config.order, !order.isEmpty {116 let rank = Dictionary(order.enumerated().map { ($1, $0) }, uniquingKeysWith: { a, _ in a })117 list = list.enumerated().sorted { a, b in118 let ra = rank[a.element.id] ?? (order.count + a.offset), rb = rank[b.element.id] ?? (order.count + b.offset)119 return ra < rb120 }.map(\.element)121 }122 return list123 }124125 /// The first properties worth showing under a title in a card (no title, nothing empty-prone first).126 public var cardProperties: [DatabaseProperty] { Array(visibleProperties.filter { $0.type != DatabasePropertyType.title }.prefix(4)) }127128 public func property(_ id: String?) -> DatabaseProperty? { id.flatMap { id in properties.first { $0.id == id } } }129 public func row(_ id: String) -> DatabaseRowValues? { rows.first { $0.id == id } }130131 /// The property the board (or a grouped view) groups by: the draft's, else the first status / select.132 public var groupProperty: DatabaseProperty? {133 if let id = draft.groupBy, let property = property(id) { return property }134 guard viewType == DatabaseViewType.board else { return nil }135 return properties.first { $0.type == DatabasePropertyType.status } ?? properties.first { $0.type == DatabasePropertyType.select }136 }137138 /// The date property of the calendar: the view's, else the first date.139 public var dateProperty: DatabaseProperty? {140 property(viewConfig.dateProperty) ?? properties.first { $0.type == DatabasePropertyType.date }141 }142143 public var timelineProperties: (start: DatabaseProperty, end: DatabaseProperty?)? {144 let dates = properties.filter { $0.type == DatabasePropertyType.date }145 guard let start = property(viewConfig.startProperty) ?? property(viewConfig.dateProperty) ?? dates.first else { return nil }146 let end = property(viewConfig.endProperty) ?? dates.first { $0.id != start.id }147 return (start, end)148 }149150 public var rowsByDay: [String: [DatabaseRowValues]] {151 dateProperty.map { DatabaseCalendarLayout.rowsByDay(rows, property: $0) } ?? [:]152 }153154 public var timeline: DatabaseTimelineLayout.Result? {155 timelineProperties.map { DatabaseTimelineLayout.layout(rows, start: $0.start, end: $0.end) }156 }157158 /// Whether a cell can be edited here (stored types, online).159 public func isEditable(_ property: DatabaseProperty) -> Bool {160 canWrite && !property.isReadOnly && ![DatabasePropertyType.relation, DatabasePropertyType.file].contains(property.type)161 }162163 public var subtitle: String {164 guard database != nil else { return "" }165 let count = total > 1 ? LF("%lld lignes", total) : LF("%lld ligne", total)166 switch source {167 case .local?: return LF("%@ · copie de l'appareil", count)168 default: return count169 }170 }171172 // MARK: loading173174 public func load() async {175 if database == nil { phase = .loading }176 do {177 database = try await repo.refresh(id: databaseId)178 isOffline = false179 } catch {180 let failure = FeatureFailure(error)181 guard failure.isOffline else { phase = .failed(failure); return }182 isOffline = true183 do { database = try await repo.database(id: databaseId) } catch { phase = .failed(FeatureFailure(error)); return }184 guard database != nil else { phase = .failed(FeatureFailure(RepositoryError.notCached)); return }185 }186 if selectedViewId == nil || !views.contains(where: { $0.id == selectedViewId }) {187 selectedViewId = database?.defaultView?.id188 draft = savedDraft189 }190 await fetchRows()191 }192193 public func selectView(_ id: String) async {194 guard id != selectedViewId else { return }195 selectedViewId = id196 draft = savedDraft197 selection = []198 editingCell = nil199 await fetchRows()200 }201202 /// Re-evaluates after a change of the draft or of the search.203 public func applyDraft() async { await fetchRows() }204205 public func resetDraft() async {206 draft = savedDraft207 await fetchRows()208 }209210 private func infos() -> [DatabasePropertyInfo] {211 properties.map { property in212 DatabasePropertyInfo(id: property.id, name: property.name, type: property.type, options: property.options.map { .init(id: $0.id, name: $0.name, color: $0.color) },213 format: property.typedConfig.format, includeTime: property.typedConfig.includeTime ?? false, position: property.position ?? 0)214 }215 }216217 private func fetchRows() async {218 guard let database else { return }219 fetchGeneration += 1220 let generation = fetchGeneration221 let id = database.info.id222 let trimmed = search.trimmingCharacters(in: .whitespacesAndNewlines)223 let searchText: String? = trimmed.isEmpty ? nil : trimmed224 let dirty = isDraftDirty225 var result: Fetched?226 var failure: FeatureFailure?227 do {228 if dirty {229 result = try await adHocRows(databaseId: id, search: searchText)230 } else {231 result = Fetched(try await repo.rows(databaseId: id, viewId: selectedViewId, search: searchText, limit: 500))232 }233 } catch {234 failure = FeatureFailure(error)235 }236 guard generation == fetchGeneration else { return }237 if let result {238 rows = result.rows239 total = result.total240 source = result.source241 isExact = result.isExact242 notEvaluated = result.notEvaluated243 isOffline = result.source == .local244 regroup()245 selection = selection.intersection(Set(rows.map(\.id)))246 phase = isOffline ? .failed(FeatureFailure(kind: .offline, message: L("Hors ligne : copie de l'appareil. Les modifications reprendront quand le serveur sera joignable."))) : .loaded247 } else if let failure {248 phase = .failed(failure)249 }250 }251252 /// The draft asked to the server as an ad hoc query (exact, computed values included); offline, evaluated on the device.253 private func adHocRows(databaseId: String, search: String?) async throws -> Fetched {254 var query = draft.query255 query.search = search256 query.limit = 500257 do {258 let filter: PatchValue<DatabaseFilter> = try draft.filter.filter.map { .value(try RawJSON(data: $0.topLevelJSON.canonicalData()).decode(DatabaseFilter.self)) } ?? .null259 let sorts = draft.sorts.map { DatabaseSort(property: $0.property, descending: $0.descending) }260 let api = repositories.context.api261 let answer = try await repositories.context.online { try await api.queryDatabase(id: databaseId, DatabaseQuery(filter: filter, sorts: sorts, search: search, limit: 500)) }262 let titleId = titleProperty?.id263 let rows = answer.rows.map { row -> DatabaseRowValues in264 let values = (try? JSONValue(data: row.values.data))?.objectValue ?? [:]265 return DatabaseRowValues(id: row.id, pageId: row.pageId, title: row.title ?? titleId.flatMap { values[$0]?.stringValue } ?? "", values: values,266 position: row.position ?? 0, createdAt: row.createdAt, updatedAt: row.updatedAt)267 }268 return Fetched(rows: rows, total: answer.total, source: .server, isExact: true, notEvaluated: [])269 } catch {270 guard RepositoryError.from(error) == .offline else { throw error }271 return Fetched(try await repo.evaluateLocally(databaseId: databaseId, query: query))272 }273 }274275 /// Groups the rows on the device (the server never groups), keeping the order of `rows` inside each group.276 private func regroup() {277 guard let property = groupProperty, !property.isComputed, property.type != DatabasePropertyType.relation else { groups = []; return }278 let order = Dictionary(rows.enumerated().map { ($1.id, $0) }, uniquingKeysWith: { a, _ in a })279 let evaluated = DatabaseEvaluator.evaluate(properties: infos(), rows: rows, query: DatabaseViewQuery(groupBy: property.id, limit: 1000)).groups ?? []280 groups = evaluated.map { group in281 var group = group282 group.rows.sort { (order[$0.id] ?? 0) < (order[$1.id] ?? 0) }283 if group.id == "true" { group.label = L("Oui") } else if group.id == "false" { group.label = L("Non") }284 return group285 }286 }287288 // MARK: writes (online only)289290 /// Saves the draft in the current view. Needs the server.291 @discardableResult292 public func saveView() async -> Bool {293 guard let database, let view = currentView else { return false }294 let config = draft.merged(into: try? JSONValue(data: view.config.data))295 return await perform { [self] in296 _ = try await repo.updateView(databaseId: database.info.id, viewId: view.id, DatabaseViewChanges(config: RawJSON(data: config.canonicalData())))297 self.database = try await repo.database(id: database.info.id) ?? self.database298 }299 }300301 @discardableResult302 public func createView(type: String, name: String? = nil) async -> Bool {303 guard let database else { return false }304 var created: DatabaseView?305 let done = await perform { [self] in306 created = try await repo.createView(databaseId: database.info.id, DatabaseViewDraft(name: name ?? DatabaseCellFormat.viewTypeLabel(type), type: type))307 self.database = try await repo.database(id: database.info.id) ?? self.database308 }309 if done, let created { await selectView(created.id) }310 return done311 }312313 @discardableResult314 public func deleteCurrentView() async -> Bool {315 guard let database, let view = currentView, views.count > 1 else { return false }316 return await perform { [self] in317 try await repo.deleteView(databaseId: database.info.id, viewId: view.id)318 self.database = try await repo.database(id: database.info.id) ?? self.database319 selectedViewId = self.database?.defaultView?.id320 draft = savedDraft321 }322 }323324 @discardableResult325 public func createProperty(name: String, type: String) async -> Bool {326 guard let database else { return false }327 let trimmed = name.trimmingCharacters(in: .whitespacesAndNewlines)328 guard !trimmed.isEmpty else {329 actionFailure = FeatureFailure(kind: .other, message: L("Donnez un nom à la propriété."))330 return false331 }332 return await perform { [self] in333 _ = try await repo.createProperty(databaseId: database.info.id, DatabasePropertyDraft(name: trimmed, type: type))334 self.database = try await repo.database(id: database.info.id) ?? self.database335 }336 }337338 @discardableResult339 public func createRow(title: String? = nil) async -> Bool {340 guard let database else { return false }341 let text = (title ?? newRowTitle).trimmingCharacters(in: .whitespacesAndNewlines)342 guard !text.isEmpty else { return false }343 let done = await perform { [self] in _ = try await repo.createRow(databaseId: database.info.id, DatabaseRowDraft(title: text)) }344 if done { newRowTitle = "" }345 return done346 }347348 /// Writes one cell (`nil` clears it).349 @discardableResult350 public func setValue(_ value: JSONValue?, rowId: String, propertyId: String) async -> Bool {351 guard let database else { return false }352 editingCell = nil353 let values = JSONValue.object([propertyId: value ?? .null])354 return await perform { [self] in _ = try await repo.updateRow(databaseId: database.info.id, rowId: rowId, values: RawJSON(data: values.canonicalData())) }355 }356357 /// Parses what the user typed for a property and writes it. An unreadable number or date is refused, nothing is sent.358 @discardableResult359 public func setText(_ text: String, rowId: String, property: DatabaseProperty) async -> Bool {360 let trimmed = text.trimmingCharacters(in: .whitespacesAndNewlines)361 if trimmed.isEmpty, property.type != DatabasePropertyType.title { return await setValue(nil, rowId: rowId, propertyId: property.id) }362 switch property.type {363 case DatabasePropertyType.number:364 guard let number = Double(trimmed.replacingOccurrences(of: ",", with: ".").filter { !$0.isWhitespace && $0 != "$" && $0 != "%" }) else {365 actionFailure = FeatureFailure(kind: .other, message: LF("« %@ » n'est pas un nombre.", trimmed))366 editingCell = nil367 return false368 }369 return await setValue(number == number.rounded() && abs(number) < 1e15 ? .int(Int64(number)) : .double(number), rowId: rowId, propertyId: property.id)370 case DatabasePropertyType.date:371 guard DatabaseCellFormat.isBareDay(trimmed), WorkDay.date(trimmed, timeZone: WorkDay.utc) != nil else {372 actionFailure = FeatureFailure(kind: .other, message: L("Écrivez la date sous la forme AAAA-MM-JJ."))373 editingCell = nil374 return false375 }376 return await setValue(.string(trimmed), rowId: rowId, propertyId: property.id)377 case DatabasePropertyType.title:378 guard !trimmed.isEmpty else { editingCell = nil; return false }379 return await setValue(.string(trimmed), rowId: rowId, propertyId: property.id)380 default:381 return await setValue(.string(trimmed), rowId: rowId, propertyId: property.id)382 }383 }384385 /// Adds or removes one option of a multi-select cell.386 @discardableResult387 public func toggleOption(_ optionId: String, rowId: String, property: DatabaseProperty) async -> Bool {388 var current = (row(rowId)?.values[property.id]?.arrayValue ?? []).compactMap(\.stringValue)389 if let index = current.firstIndex(of: optionId) { current.remove(at: index) } else { current.append(optionId) }390 return await setValue(.array(current.map { .string($0) }), rowId: rowId, propertyId: property.id)391 }392393 /// Moves a card to another column of the board (sets the grouping property).394 @discardableResult395 public func move(rowId: String, toGroup groupId: String) async -> Bool {396 guard let property = groupProperty else { return false }397 switch property.type {398 case DatabasePropertyType.checkbox: return await setValue(.bool(groupId == "true"), rowId: rowId, propertyId: property.id)399 case DatabasePropertyType.multiSelect: return await setValue(groupId.isEmpty ? nil : .array([.string(groupId)]), rowId: rowId, propertyId: property.id)400 default: return await setValue(groupId.isEmpty ? nil : .string(groupId), rowId: rowId, propertyId: property.id)401 }402 }403404 // MARK: selection and bulk actions405406 public func toggleSelection(_ id: String) {407 if selection.contains(id) { selection.remove(id) } else { selection.insert(id) }408 }409410 public func selectAll() { selection = Set(rows.map(\.id)) }411412 public func endSelection() {413 selection = []414 isSelecting = false415 }416417 @discardableResult418 public func deleteRows(_ ids: [String]) async -> Bool {419 guard let database, !ids.isEmpty else { return false }420 let done = await perform { [self] in try await repo.deleteRows(databaseId: database.info.id, ids: ids) }421 if done {422 lastDeleted = ids423 selection.subtract(ids)424 if case .row(let id) = inspector, ids.contains(id) { inspector = .none }425 }426 return done427 }428429 @discardableResult430 public func deleteSelection() async -> Bool { await deleteRows(Array(selection).sorted()) }431432 /// Restores the rows of the last deletion.433 @discardableResult434 public func undoDelete() async -> Bool {435 guard let database, !lastDeleted.isEmpty else { return false }436 let ids = lastDeleted437 let done = await perform { [self] in try await repo.restoreRows(databaseId: database.info.id, ids: ids) }438 if done { lastDeleted = [] }439 return done440 }441442 public func dismissUndo() { lastDeleted = [] }443444 /// Sets one property on every selected row.445 @discardableResult446 public func bulkSet(_ value: JSONValue?, propertyId: String) async -> Bool {447 guard let database, !selection.isEmpty else { return false }448 let ids = Array(selection).sorted()449 let values = RawJSON(data: JSONValue.object([propertyId: value ?? .null]).canonicalData())450 return await perform { [self] in451 for id in ids { _ = try await repo.updateRow(databaseId: database.info.id, rowId: id, values: values) }452 }453 }454455 /// The properties a bulk action can set (a choice among options, or a checkbox).456 public var bulkProperties: [DatabaseProperty] {457 properties.filter { [DatabasePropertyType.select, DatabasePropertyType.status, DatabasePropertyType.checkbox].contains($0.type) }458 }459460 // MARK: navigation461462 public func openRow(_ row: DatabaseRowValues) {463 guard let pageId = row.pageId else { return }464 router?.openPage(id: pageId, blockId: nil)465 }466467 public func openURL(_ text: String) {468 guard let url = URL(string: text), url.scheme != nil else { return }469 router?.openURL(url)470 }471472 @discardableResult473 private func perform(_ work: @escaping () async throws -> Void) async -> Bool {474 isWorking = true475 defer { isWorking = false }476 do {477 try await work()478 actionFailure = nil479 await fetchRows()480 return true481 } catch {482 let failure = FeatureFailure(error)483 actionFailure = failure484 if failure.isOffline { isOffline = true }485 return false486 }487 }488}489