SPB Git

Repository Public

NotesStore+Entities.swift 22.5 KB 388 lines swift

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

Theme