Repository Public
1// SP Notes Core2// File: Sources/SPNotesStore/NotesStore+Entities.swift3// Description: The generic synced entities of 2.0 (tasks, databases, properties, views, rows, edges, references,4// concepts): typed queries, application of the entity feed, per-kind cursors, local edits, and the5// ordered outbox in which an unsupported or refused operation is kept and surfaced, never dropped.6//7// Author: Simon-Pierre Boucher8// Contact: contact@spboucher.ai9import Foundation1011extension NotesStore {12 // MARK: - Reading1314 private static let entityColumns = "kind, id, rev, json, deleted, updated_at, scope, ref, sort_key, typeof(sort_key), dirty, remote_json"1516 private static func decodeEntity(_ r: Row) -> EntityRecord {17 var sort: EntitySort?18 switch r.string(9) {19 case "integer", "real": sort = .number(r.double(8))20 case "text": sort = .text(r.string(8))21 default: sort = nil22 }23 return EntityRecord(kind: EntityKind(r.string(0)), id: r.string(1), rev: r.int(2), json: r.data(3), deleted: r.bool(4), updatedAt: r.date(5),24 index: EntityIndex(scope: r.optString(6), ref: r.optString(7), sort: sort), dirty: r.bool(10), remoteJSON: r.optData(11))25 }2627 private static func sortValue(_ sort: EntitySort?) -> SQLValue {28 switch sort {29 case .number(let n)?: return .double(n)30 case .text(let t)?: return .text(t)31 case nil: return .null32 }33 }3435 public func entity(kind: EntityKind, id: String) throws -> EntityRecord? {36 try db.first("SELECT \(Self.entityColumns) FROM entities WHERE kind=? AND id=?", [kind.rawValue, id], Self.decodeEntity)37 }3839 /// Entities of a kind, optionally restricted to a scope (owner id) and / or a ref (second id), ordered by their40 /// sort key (rows without one last), then id. Deleted rows are excluded unless asked.41 public func entities(kind: EntityKind, scope: String? = nil, ref: String? = nil, includeDeleted: Bool = false,42 limit: Int = 1000, offset: Int = 0) throws -> [EntityRecord] {43 var sql = "SELECT \(Self.entityColumns) FROM entities WHERE kind=?"44 var params: [any SQLBindable] = [kind.rawValue]45 if let scope { sql += " AND scope=?"; params.append(scope) }46 if let ref { sql += " AND ref=?"; params.append(ref) }47 if !includeDeleted { sql += " AND deleted=0" }48 sql += " ORDER BY sort_key IS NULL, sort_key, id LIMIT ? OFFSET ?"49 params.append(max(limit, 0)); params.append(max(offset, 0))50 return try db.query(sql, params, Self.decodeEntity)51 }5253 public func entityCount(kind: EntityKind, scope: String? = nil) throws -> Int {54 if let scope { return Int(try db.scalar("SELECT count(*) FROM entities WHERE kind=? AND scope=? AND deleted=0", [kind.rawValue, scope])) }55 return Int(try db.scalar("SELECT count(*) FROM entities WHERE kind=? AND deleted=0", [kind.rawValue]))56 }5758 /// Live row counts per kind (kinds without rows are absent).59 public func entityCounts() throws -> [EntityKind: Int] {60 var out: [EntityKind: Int] = [:]61 _ = try db.query("SELECT kind, count(*) FROM entities WHERE deleted=0 GROUP BY kind") { out[EntityKind($0.string(0))] = Int($0.int(1)) }62 return out63 }6465 // Typed helpers over the kinds of SPEC §3.1.6667 /// Tasks, all or those attached to a page, ordered by due date (undated last).68 public func tasks(pageId: String? = nil) throws -> [EntityRecord] { try entities(kind: .task, scope: pageId) }69 public func databases() throws -> [EntityRecord] { try entities(kind: .database) }70 public func databaseProperties(databaseId: String) throws -> [EntityRecord] { try entities(kind: .databaseProperty, scope: databaseId) }71 public func databaseViews(databaseId: String) throws -> [EntityRecord] { try entities(kind: .databaseView, scope: databaseId) }72 public func databaseRows(databaseId: String, limit: Int = 1000, offset: Int = 0) throws -> [EntityRecord] {73 try entities(kind: .databaseRow, scope: databaseId, limit: limit, offset: offset)74 }75 /// Graph edges leaving `sourceId`.76 public func edges(from sourceId: String) throws -> [EntityRecord] { try entities(kind: .edge, scope: sourceId) }77 /// Graph edges arriving at `targetId` (backlinks).78 public func edges(to targetId: String) throws -> [EntityRecord] { try entities(kind: .edge, ref: targetId) }79 /// Graph edges touching `nodeId` on either end, without duplicates.80 public func edges(touching nodeId: String) throws -> [EntityRecord] {81 let out = try edges(from: nodeId)82 let seen = Set(out.map(\.id))83 return out + (try edges(to: nodeId)).filter { !seen.contains($0.id) }84 }85 public func references(projectId: String? = nil) throws -> [EntityRecord] { try entities(kind: .reference, scope: projectId) }86 public func concepts() throws -> [EntityRecord] { try entities(kind: .concept) }8788 // MARK: - Cursors8990 /// The feed cursor of one entity kind (0 = never synced).91 public func entityCursor(kind: EntityKind) throws -> Int64 {92 try db.first("SELECT cursor FROM sync_cursors WHERE kind=?", [kind.rawValue]) { $0.int(0) } ?? 093 }9495 public func entityCursors() throws -> [EntityKind: Int64] {96 var out: [EntityKind: Int64] = [:]97 _ = try db.query("SELECT kind, cursor FROM sync_cursors") { out[EntityKind($0.string(0))] = $0.int(1) }98 return out99 }100101 public func setEntityCursor(kind: EntityKind, _ cursor: Int64) throws {102 try write { try putEntityCursor(kind, cursor) }103 }104105 private func putEntityCursor(_ kind: EntityKind, _ cursor: Int64) throws {106 try db.run("INSERT INTO sync_cursors(kind, cursor, updated_at) VALUES(?, ?, ?) ON CONFLICT(kind) DO UPDATE SET cursor=excluded.cursor, updated_at=excluded.updated_at",107 [kind.rawValue, cursor, nowMillis()])108 }109110 /// For a server `reset`: the cursors of `kinds` (all when nil) go back to 0 and the stored `rev`s are zeroed so111 /// that the whole feed applies again. No row and no outbox entry is removed.112 public func resetEntitySync(kinds: [EntityKind]? = nil) throws {113 try write {114 if let kinds {115 for kind in kinds {116 try db.run("DELETE FROM sync_cursors WHERE kind=?", [kind.rawValue])117 try db.run("UPDATE entities SET rev=0 WHERE kind=? AND rev<>0", [kind.rawValue])118 }119 } else {120 try db.run("DELETE FROM sync_cursors")121 try db.run("UPDATE entities SET rev=0 WHERE rev<>0")122 try db.run("UPDATE canvases SET rev=0 WHERE rev<>0")123 }124 }125 }126127 /// After a complete re-download of a kind from 0: removes the CLEAN rows whose id is not in `ids` (deleted on128 /// the server while this device missed the tombstones). Rows with pending local changes are kept.129 @discardableResult130 public func removeSyncedEntities(kind: EntityKind, notIn ids: Set<String>) throws -> [String] {131 try write {132 let candidates = try db.query("SELECT id FROM entities WHERE kind=? AND dirty=0", [kind.rawValue]) { $0.string(0) }133 let gone = candidates.filter { !ids.contains($0) }134 for id in gone { try db.run("DELETE FROM entities WHERE kind=? AND id=?", [kind.rawValue, id]) }135 if !gone.isEmpty { pending.entities[kind.rawValue, default: []].formUnion(gone) }136 return gone137 }138 }139140 // MARK: - Applying the feed141142 /// Applies one window of the entity feed atomically, with its cursors. A row whose stored `rev` is not lower is143 /// skipped (re-applying a window changes nothing). A row with pending local changes keeps its JSON: the server144 /// copy goes to `remoteJSON` and the row is reported in `keptLocal`. A tombstone removes a clean row; on a dirty145 /// row it is recorded (`deleted` set) but the local JSON and the outbox are kept.146 @discardableResult147 public func applyRemoteEntities(_ rows: [RemoteEntity], tombstones: [EntityTombstone] = [], cursors: [EntityKind: Int64] = [:]) throws -> EntityApplySummary {148 try write {149 var summary = EntityApplySummary()150 let now = nowMillis()151 for row in rows {152 let existing = try db.first("SELECT rev, dirty FROM entities WHERE kind=? AND id=?", [row.kind.rawValue, row.id]) { ($0.int(0), $0.bool(1)) }153 if let existing, existing.0 >= row.rev, row.rev > 0 { summary.stale += 1; continue }154 let index = row.index ?? EntityIndex.extract(kind: row.kind, json: row.json)155 let updated = row.updatedAt?.millis ?? now156 if let existing, existing.1 {157 try db.run("UPDATE entities SET rev=?, remote_json=? WHERE kind=? AND id=?", [row.rev, row.json, row.kind.rawValue, row.id])158 summary.keptLocal.append(EntityTombstone(kind: row.kind, id: row.id))159 } else {160 try db.run("""161 INSERT INTO entities(kind, id, rev, json, deleted, updated_at, scope, ref, sort_key, dirty, remote_json)162 VALUES(?, ?, ?, ?, 0, ?, ?, ?, ?, 0, NULL)163 ON CONFLICT(kind, id) DO UPDATE SET rev=excluded.rev, json=excluded.json, deleted=0, updated_at=excluded.updated_at,164 scope=excluded.scope, ref=excluded.ref, sort_key=excluded.sort_key, remote_json=NULL165 """, [row.kind.rawValue, row.id, row.rev, row.json, updated, index.scope, index.ref, Self.sortValue(index.sort)])166 summary.applied += 1167 }168 pending.entities[row.kind.rawValue, default: []].insert(row.id)169 }170 for stone in tombstones {171 guard let dirty = try db.first("SELECT dirty FROM entities WHERE kind=? AND id=?", [stone.kind.rawValue, stone.id], { $0.bool(0) }) else { continue }172 if dirty {173 try db.run("UPDATE entities SET deleted=1, remote_json=NULL WHERE kind=? AND id=?", [stone.kind.rawValue, stone.id])174 summary.keptLocal.append(stone)175 } else {176 try db.run("DELETE FROM entities WHERE kind=? AND id=?", [stone.kind.rawValue, stone.id])177 summary.removed += 1178 }179 pending.entities[stone.kind.rawValue, default: []].insert(stone.id)180 }181 for (kind, cursor) in cursors { try putEntityCursor(kind, cursor) }182 return summary183 }184 }185186 // MARK: - Local edits187188 /// Writes a local edit of an entity AND its outbox entry in one transaction: the row becomes `dirty` with the189 /// new JSON (`deleted` for a delete), and (`kind`, `id`, `op`, `payload`, `baseVersion`) joins the outbox.190 /// `json` nil keeps the stored JSON (a delete, or an action that does not change the row).191 @discardableResult192 public func saveLocalEntity(kind: EntityKind, id: String, json: Data?, op: String, payload: Data? = nil, baseVersion: Int? = nil,193 index: EntityIndex? = nil) throws -> EntityOp {194 guard !id.isEmpty, !op.isEmpty, !kind.rawValue.isEmpty else { throw StoreError.invalid("empty entity kind, id or op") }195 return try write {196 let now = nowMillis()197 let exists = try db.scalar("SELECT count(*) FROM entities WHERE kind=? AND id=?", [kind.rawValue, id]) > 0198 let deleted = op == EntityOpVerb.delete199 if let json {200 let lookup = index ?? EntityIndex.extract(kind: kind, json: json)201 try db.run("""202 INSERT INTO entities(kind, id, rev, json, deleted, updated_at, scope, ref, sort_key, dirty)203 VALUES(?, ?, 0, ?, ?, ?, ?, ?, ?, 1)204 ON CONFLICT(kind, id) DO UPDATE SET json=excluded.json, deleted=excluded.deleted, updated_at=excluded.updated_at,205 scope=excluded.scope, ref=excluded.ref, sort_key=excluded.sort_key, dirty=1206 """, [kind.rawValue, id, json, deleted, now, lookup.scope, lookup.ref, Self.sortValue(lookup.sort)])207 } else if exists {208 try db.run("UPDATE entities SET dirty=1, deleted=?, updated_at=? WHERE kind=? AND id=?", [deleted, now, kind.rawValue, id])209 } else {210 throw StoreError.notFound("\(kind.rawValue) \(id)")211 }212 pending.entities[kind.rawValue, default: []].insert(id)213 return try insertEntityOp(kind: kind, entityId: id, op: op, payload: payload, baseVersion: baseVersion, now: now)214 }215 }216217 /// After the outbox entries of an entity went through: clears `dirty` when no entry for it remains (a deleted218 /// row is then removed), optionally storing the server's answer (`json`, `rev`). Returns whether it is clean.219 @discardableResult220 public func markEntitySynced(kind: EntityKind, id: String, json: Data? = nil, rev: Int64? = nil) throws -> Bool {221 try write {222 guard let row = try entity(kind: kind, id: id) else { return true }223 let waiting = try db.scalar("SELECT count(*) FROM entity_ops WHERE kind=? AND entity_id=?", [kind.rawValue, id]) > 0224 if waiting {225 if let rev { try db.run("UPDATE entities SET rev=max(rev, ?) WHERE kind=? AND id=?", [rev, kind.rawValue, id]) }226 return false227 }228 if row.deleted {229 try db.run("DELETE FROM entities WHERE kind=? AND id=?", [kind.rawValue, id])230 } else if let json {231 let lookup = EntityIndex.extract(kind: kind, json: json)232 try db.run("UPDATE entities SET json=?, rev=max(rev, ?), dirty=0, remote_json=NULL, scope=?, ref=?, sort_key=? WHERE kind=? AND id=?",233 [json, rev ?? row.rev, lookup.scope, lookup.ref, Self.sortValue(lookup.sort), kind.rawValue, id])234 } else {235 try db.run("UPDATE entities SET rev=max(rev, ?), dirty=0, remote_json=NULL WHERE kind=? AND id=?", [rev ?? row.rev, kind.rawValue, id])236 }237 pending.entities[kind.rawValue, default: []].insert(id)238 return true239 }240 }241242 /// Entities with local changes waiting.243 public func dirtyEntities(kind: EntityKind? = nil) throws -> [EntityRecord] {244 if let kind { return try db.query("SELECT \(Self.entityColumns) FROM entities WHERE dirty=1 AND kind=? ORDER BY kind, id", [kind.rawValue], Self.decodeEntity) }245 return try db.query("SELECT \(Self.entityColumns) FROM entities WHERE dirty=1 ORDER BY kind, id", [], Self.decodeEntity)246 }247248 // MARK: - Outbox249250 private static let entityOpColumns = "id, kind, entity_id, op, payload, base_version, idem_key, created_at, attempts, next_attempt_at, last_error, state"251252 private static func decodeEntityOp(_ r: Row) -> EntityOp {253 EntityOp(id: r.int(0), kind: EntityKind(r.string(1)), entityId: r.string(2), op: r.string(3), payload: r.optData(4),254 baseVersion: r.optInt(5).map(Int.init), idempotencyKey: r.string(6), createdAt: r.date(7), attempts: Int(r.int(8)),255 nextAttemptAt: r.date(9), lastError: r.optString(10), state: EntityOp.State(rawValue: r.string(11)) ?? .unsupported)256 }257258 private func insertEntityOp(kind: EntityKind, entityId: String, op: String, payload: Data?, baseVersion: Int?, now: Int64) throws -> EntityOp {259 let key = UUID().uuidString.replacingOccurrences(of: "-", with: "").lowercased()260 try db.run("INSERT INTO entity_ops(kind, entity_id, op, payload, base_version, idem_key, created_at) VALUES(?, ?, ?, ?, ?, ?, ?)",261 [kind.rawValue, entityId, op, payload, baseVersion, key, now])262 pending.entityQueue = true263 return try requireEntityOp(db.lastInsertRowID)264 }265266 private func requireEntityOp(_ id: Int64) throws -> EntityOp {267 guard let op = try db.first("SELECT \(Self.entityOpColumns) FROM entity_ops WHERE id=?", [id], Self.decodeEntityOp) else {268 throw StoreError.notFound("entity op \(id)")269 }270 return op271 }272273 /// Appends an operation to the entity outbox with a fresh idempotency key, without touching the entity row274 /// (`saveLocalEntity` does both).275 @discardableResult276 public func enqueueEntityOp(kind: EntityKind, entityId: String, op: String, payload: Data? = nil, baseVersion: Int? = nil) throws -> EntityOp {277 guard !entityId.isEmpty, !op.isEmpty, !kind.rawValue.isEmpty else { throw StoreError.invalid("empty entity kind, id or op") }278 return try write { try insertEntityOp(kind: kind, entityId: entityId, op: op, payload: payload, baseVersion: baseVersion, now: nowMillis()) }279 }280281 /// The operations that can be sent now, in outbox order. The order of one entity is never broken: an operation282 /// is held back while an older one of the same (`kind`, `entityId`) is in backoff, unsupported or rejected.283 /// Nothing is leased: the caller completes, fails, rejects or marks each one before asking again.284 public func nextEntityOps(limit: Int = 50, now: Date = Date()) throws -> [EntityOp] {285 try db.query("""286 SELECT \(Self.entityOpColumns) FROM entity_ops o287 WHERE o.state='pending' AND o.next_attempt_at<=?1288 AND NOT EXISTS(SELECT 1 FROM entity_ops e WHERE e.kind=o.kind AND e.entity_id=o.entity_id AND e.id<o.id289 AND (e.state<>'pending' OR e.next_attempt_at>?1))290 ORDER BY o.id LIMIT ?2291 """, [now.millis, max(limit, 0)], Self.decodeEntityOp)292 }293294 /// Every outbox entry in order, or those in one state (UI, diagnostics).295 public func entityOps(state: EntityOp.State? = nil) throws -> [EntityOp] {296 if let state { return try db.query("SELECT \(Self.entityOpColumns) FROM entity_ops WHERE state=? ORDER BY id", [state.rawValue], Self.decodeEntityOp) }297 return try db.query("SELECT \(Self.entityOpColumns) FROM entity_ops ORDER BY id", [], Self.decodeEntityOp)298 }299300 public func entityOps(kind: EntityKind, entityId: String) throws -> [EntityOp] {301 try db.query("SELECT \(Self.entityOpColumns) FROM entity_ops WHERE kind=? AND entity_id=? ORDER BY id", [kind.rawValue, entityId], Self.decodeEntityOp)302 }303304 public func entityOpCount(state: EntityOp.State? = nil) throws -> Int {305 if let state { return Int(try db.scalar("SELECT count(*) FROM entity_ops WHERE state=?", [state.rawValue])) }306 return Int(try db.scalar("SELECT count(*) FROM entity_ops"))307 }308309 /// Removes an operation the server acknowledged. Unknown ids are ignored.310 public func completeEntityOp(id: Int64) throws {311 try write {312 if try db.run("DELETE FROM entity_ops WHERE id=?", [id]) > 0 { pending.entityQueue = true }313 }314 }315316 /// Records a failed attempt that may succeed later: `attempts + 1`, next attempt after `backoffDelay`.317 @discardableResult318 public func failEntityOp(id: Int64, error: String, now: Date = Date()) throws -> EntityOp? {319 try write {320 guard let op = try db.first("SELECT \(Self.entityOpColumns) FROM entity_ops WHERE id=?", [id], Self.decodeEntityOp) else { return nil }321 let attempts = op.attempts + 1322 let next = now.millis + Int64(Self.backoffDelay(attempts: attempts) * 1000)323 try db.run("UPDATE entity_ops SET attempts=?, next_attempt_at=?, last_error=? WHERE id=?", [attempts, next, error, id])324 pending.entityQueue = true325 return try requireEntityOp(id)326 }327 }328329 /// Marks an operation this build has no handler for: it stays in the outbox (state `unsupported`), is never330 /// handed out again by this build, and holds back the later operations of the same entity.331 public func markEntityOpUnsupported(id: Int64, reason: String) throws {332 try setEntityOpState(id, .unsupported, reason)333 }334335 /// Marks an operation the server refused for good (state `rejected`): kept and surfaced until `retryEntityOp`336 /// or `discardEntityOp`.337 public func rejectEntityOp(id: Int64, error: String) throws {338 try setEntityOpState(id, .rejected, error)339 }340341 private func setEntityOpState(_ id: Int64, _ state: EntityOp.State, _ message: String) throws {342 try write {343 if try db.run("UPDATE entity_ops SET state=?, last_error=? WHERE id=?", [state.rawValue, message, id]) > 0 { pending.entityQueue = true }344 }345 }346347 /// Puts unsupported / rejected operations back to `pending`: one of them, or every one in `state` (both when nil)348 /// whose (`kind`, `op`) passes `supported` (what an updated build can now execute). Returns how many were released.349 @discardableResult350 public func retryEntityOps(id: Int64? = nil, state: EntityOp.State? = nil, where supported: (@Sendable (EntityKind, String) -> Bool)? = nil) throws -> Int {351 try write {352 let blocked = try db.query("SELECT \(Self.entityOpColumns) FROM entity_ops WHERE state<>'pending' ORDER BY id", [], Self.decodeEntityOp)353 var released = 0354 for op in blocked where (id == nil || id == op.id) && (state == nil || state == op.state) && (supported?(op.kind, op.op) ?? true) {355 try db.run("UPDATE entity_ops SET state='pending', next_attempt_at=0 WHERE id=?", [op.id])356 released += 1357 }358 if released > 0 { pending.entityQueue = true }359 return released360 }361 }362363 /// Ends the backoff of the pending operations (one of them, or all): they are sent by the next cycle. For an364 /// explicit "retry now" of the user, or when the reason of the wait is known to be gone. Returns how many moved.365 @discardableResult366 public func expediteEntityOps(id: Int64? = nil) throws -> Int {367 try write {368 let moved: Int369 if let id { moved = try db.run("UPDATE entity_ops SET next_attempt_at=0 WHERE state='pending' AND next_attempt_at>0 AND id=?", [id]) }370 else { moved = try db.run("UPDATE entity_ops SET next_attempt_at=0 WHERE state='pending' AND next_attempt_at>0") }371 if moved > 0 { pending.entityQueue = true }372 return moved373 }374 }375376 /// Removes an operation on the user's explicit decision (the only way an unsent operation leaves the outbox).377 /// Returns the removed entry so that the caller can show or export what was abandoned.378 @discardableResult379 public func discardEntityOp(id: Int64) throws -> EntityOp? {380 try write {381 guard let op = try db.first("SELECT \(Self.entityOpColumns) FROM entity_ops WHERE id=?", [id], Self.decodeEntityOp) else { return nil }382 try db.run("DELETE FROM entity_ops WHERE id=?", [id])383 pending.entityQueue = true384 return op385 }386 }387}388