SPB Git

Repository Public

NotesStore+Queue.swift 16.9 KB 324 lines swift

1// SP Notes Core2// File: Sources/SPNotesStore/NotesStore+Queue.swift3// Description: The persistent FIFO queue of action-type operations (leases, backoff, coalescing) and the4//              attachments table with its local file state.5//6// Author: Simon-Pierre Boucher7// Contact: contact@spboucher.ai8import Foundation910extension NotesStore {11    // MARK: - Ops queue1213    private static let opColumns = "seq, kind, target_id, payload, idem_key, created_at, attempts, next_attempt_at, last_error"1415    static func decodeOp(_ r: Row) -> PendingOp {16        PendingOp(id: r.int(0), kind: r.string(1), targetId: r.optString(2), payload: r.optData(3), idempotencyKey: r.string(4),17                  createdAt: r.date(5), attempts: Int(r.int(6)), nextAttemptAt: r.date(7), lastError: r.optString(8))18    }1920    /// Delay before retry number `attempts` (1-based): 5 s, 10 s, 20 s … capped at 15 minutes.21    public static func backoffDelay(attempts: Int) -> TimeInterval {22        let exponent = min(max(attempts - 1, 0), 20)23        return min(900, 5 * pow(2, Double(exponent)))24    }2526    @discardableResult27    func insertOp(kind: String, targetId: String?, payload: Data?, coalescable: Bool, now: Int64) throws -> Int64 {28        let key = UUID().uuidString.replacingOccurrences(of: "-", with: "").lowercased()29        try db.run("INSERT INTO ops(kind, target_id, payload, idem_key, created_at, next_attempt_at, coalescable) VALUES(?, ?, ?, ?, ?, 0, ?)",30                   [kind, targetId, payload, key, now, coalescable])31        pending.queue = true32        return db.lastInsertRowID33    }3435    /// Appends an operation to the queue with a fresh idempotency key (32 characters of `[0-9a-f]`).36    @discardableResult37    public func enqueueOp(kind: String, targetId: String? = nil, payload: Data? = nil) throws -> PendingOp {38        guard !kind.isEmpty else { throw StoreError.invalid("empty op kind") }39        return try write {40            let seq = try insertOp(kind: kind, targetId: targetId, payload: payload, coalescable: false, now: nowMillis())41            return try requireOp(seq)42        }43    }4445    private func requireOp(_ id: Int64) throws -> PendingOp {46        guard let op = try db.first("SELECT \(Self.opColumns) FROM ops WHERE seq=?", [id], Self.decodeOp) else {47            throw StoreError.notFound("op \(id)")48        }49        return op50    }5152    /// Hands out the ops that are due, oldest first, and LEASES them for `lease` seconds: a leased op is not53    /// returned again until `completeOp`, `failOp`, `releaseOp` or the end of the lease. An op is held back54    /// while an older op with the same target is in backoff or leased (per-target order is never broken).55    public func nextOps(limit: Int = 20, now: Date = Date(), lease: TimeInterval = 120) throws -> [PendingOp] {56        try write {57            let ms = now.millis58            let ops = try db.query("""59            SELECT \(Self.opColumns) FROM ops o60            WHERE o.next_attempt_at<=?1 AND o.leased_until<=?161              AND NOT EXISTS(SELECT 1 FROM ops_parked k WHERE k.seq=o.seq)62              AND NOT EXISTS(SELECT 1 FROM ops e WHERE e.target_id=o.target_id AND e.seq<o.seq AND (e.next_attempt_at>?1 OR e.leased_until>?1))63            ORDER BY o.seq LIMIT ?264            """, [ms, max(limit, 0)], Self.decodeOp)65            let until = ms + Int64(lease * 1000)66            for op in ops {67                try db.run("UPDATE ops SET leased_until=?, handed_out=1 WHERE seq=?", [until, op.id])68            }69            pending = PendingChanges() // leasing is not a change worth notifying70            return ops71        }72    }7374    /// Every queued op in FIFO order, without leasing (UI, diagnostics).75    public func queuedOps() throws -> [PendingOp] {76        try db.query("SELECT \(Self.opColumns) FROM ops ORDER BY seq", [], Self.decodeOp)77    }7879    public func opCount() throws -> Int {80        Int(try db.scalar("SELECT count(*) FROM ops"))81    }8283    /// Removes an op that succeeded (or that the server rejected for good). Unknown ids are ignored.84    public func completeOp(id: Int64) throws {85        try write {86            try db.run("DELETE FROM ops_parked WHERE seq=?", [id])87            if try db.run("DELETE FROM ops WHERE seq=?", [id]) > 0 { pending.queue = true }88        }89    }9091    // MARK: Parked ops (2.0)9293    /// Sets aside an op this build cannot execute (a kind written by a newer version of the app): it stays in the94    /// queue, is never handed out by `nextOps` and never dropped; `parkedOps()` surfaces it. Later ops on the same95    /// target are NOT held back by a parked op. Unknown ids are ignored.96    public func parkOp(id: Int64, reason: String, now: Date = Date()) throws {97        try write {98            guard try db.scalar("SELECT count(*) FROM ops WHERE seq=?", [id]) > 0 else { return }99            try db.run("INSERT INTO ops_parked(seq, reason, parked_at) VALUES(?, ?, ?) ON CONFLICT(seq) DO UPDATE SET reason=excluded.reason", [id, reason, now.millis])100            try db.run("UPDATE ops SET leased_until=0, last_error=? WHERE seq=?", [reason, id])101            pending.queue = true102        }103    }104105    /// The ops set aside by `parkOp`, oldest first.106    public func parkedOps() throws -> [ParkedOp] {107        try db.query("""108        SELECT o.seq, o.kind, o.target_id, o.payload, o.idem_key, o.created_at, o.attempts, o.next_attempt_at, o.last_error, k.reason, k.parked_at109        FROM ops o JOIN ops_parked k ON k.seq=o.seq ORDER BY o.seq110        """) { ParkedOp(op: Self.decodeOp($0), reason: $0.string(9), parkedAt: $0.date(10)) }111    }112113    public func parkedOpCount() throws -> Int {114        Int(try db.scalar("SELECT count(*) FROM ops_parked k JOIN ops o ON o.seq=k.seq"))115    }116117    /// Puts parked ops back in line: all of them, or those whose kind is in `kinds` (what an updated build now118    /// supports). Returns how many were released.119    @discardableResult120    public func unparkOps(kinds: Set<String>? = nil) throws -> Int {121        try write {122            let rows = try db.query("SELECT o.seq, o.kind FROM ops o JOIN ops_parked k ON k.seq=o.seq") { ($0.int(0), $0.string(1)) }123            var released = 0124            for (seq, kind) in rows where kinds?.contains(kind) ?? true {125                try db.run("DELETE FROM ops_parked WHERE seq=?", [seq])126                try db.run("UPDATE ops SET next_attempt_at=0, leased_until=0 WHERE seq=?", [seq])127                released += 1128            }129            // Rows left behind by an op deleted some other way.130            try db.run("DELETE FROM ops_parked WHERE seq NOT IN (SELECT seq FROM ops)")131            if released > 0 { pending.queue = true }132            return released133        }134    }135136    /// Records a failed attempt: `attempts + 1`, release of the lease, next attempt after `backoffDelay`.137    /// Returns the updated op, or nil when it no longer exists.138    @discardableResult139    public func failOp(id: Int64, error: String, now: Date = Date()) throws -> PendingOp? {140        try write {141            guard let op = try db.first("SELECT \(Self.opColumns) FROM ops WHERE seq=?", [id], Self.decodeOp) else { return nil }142            let attempts = op.attempts + 1143            let next = now.millis + Int64(Self.backoffDelay(attempts: attempts) * 1000)144            try db.run("UPDATE ops SET attempts=?, next_attempt_at=?, leased_until=0, last_error=? WHERE seq=?", [attempts, next, error, id])145            pending.queue = true146            return try requireOp(id)147        }148    }149150    /// Releases the lease of an op without counting an attempt (the engine gave up before sending, e.g. offline).151    public func releaseOp(id: Int64) throws {152        try write { _ = try db.run("UPDATE ops SET leased_until=0 WHERE seq=?", [id]) }153    }154155    /// Releases every lease. For the sync engine at start-up: a lease taken by a process that died is void.156    public func releaseAllOpLeases() throws {157        try write { _ = try db.run("UPDATE ops SET leased_until=0 WHERE leased_until<>0") }158    }159160    // MARK: - Attachments161162    private static let attachmentColumns = """163    id, page_id, filename, mime, size, sha256, page_count, created_at, deleted_at, rev, state, local_path, keep_offline, uploaded, \164    upload_attempts, last_error, last_accessed_at165    """166167    private static func decodeAttachment(_ r: Row) -> AttachmentRecord {168        AttachmentRecord(id: r.string(0), pageId: r.optString(1), filename: r.string(2), mime: r.string(3), size: r.int(4),169                         sha256: r.optString(5), pageCount: r.optInt(6).map(Int.init), createdAt: r.date(7), deletedAt: r.optDate(8),170                         rev: r.int(9), state: AttachmentState(rawValue: r.string(10)) ?? .remote, localPath: r.optString(11),171                         keepOffline: r.bool(12), uploaded: r.bool(13), uploadAttempts: Int(r.int(14)), lastError: r.optString(15),172                         lastAccessedAt: r.optDate(16))173    }174175    public func attachment(id: String) throws -> AttachmentRecord? {176        try db.first("SELECT \(Self.attachmentColumns) FROM attachments WHERE id=?", [id], Self.decodeAttachment)177    }178179    private func requireAttachment(_ id: String) throws -> AttachmentRecord {180        guard let record = try attachment(id: id) else { throw StoreError.notFound("attachment \(id)") }181        return record182    }183184    /// Attachments of a page, oldest first. Deleted ones are excluded unless asked.185    public func attachments(pageId: String, includeDeleted: Bool = false) throws -> [AttachmentRecord] {186        try db.query("""187        SELECT \(Self.attachmentColumns) FROM attachments WHERE page_id=?\(includeDeleted ? "" : " AND deleted_at IS NULL") ORDER BY created_at, id188        """, [pageId], Self.decodeAttachment)189    }190191    /// Registers a file already copied into the attachments directory, to be uploaded (`pendingUpload`).192    @discardableResult193    public func addLocalAttachment(id: String? = nil, pageId: String, filename: String, mime: String, size: Int64, sha256: String?,194                                   localPath: String, pageCount: Int? = nil) throws -> AttachmentRecord {195        try write {196            let attachmentId = (id ?? Self.newId()).lowercased()197            guard !attachmentId.isEmpty, !localPath.isEmpty else { throw StoreError.invalid("empty attachment id or path") }198            guard try attachment(id: attachmentId) == nil else { throw StoreError.invalid("attachment \(attachmentId) already exists") }199            let now = nowMillis()200            try db.run("""201            INSERT INTO attachments(id, page_id, filename, mime, size, sha256, page_count, created_at, state, local_path, uploaded, last_accessed_at)202            VALUES(?, ?, ?, ?, ?, ?, ?, ?, 'pendingUpload', ?, 0, ?)203            """, [attachmentId, pageId, filename, mime, size, sha256, pageCount, now, localPath, now])204            pending.attachments.insert(attachmentId)205            return try requireAttachment(attachmentId)206        }207    }208209    /// Files the server does not have yet (`pendingUpload` and `failed`), oldest first. `uploading` rows are in210    /// flight and not listed; `resetTransientAttachmentStates()` re-queues them after a crash.211    public func pendingUploads(limit: Int = 50) throws -> [AttachmentRecord] {212        try db.query("""213        SELECT \(Self.attachmentColumns) FROM attachments214        WHERE uploaded=0 AND deleted_at IS NULL AND state IN ('pendingUpload', 'failed') ORDER BY created_at, id LIMIT ?215        """, [limit], Self.decodeAttachment)216    }217218    private func updateAttachment(_ id: String, _ assignments: String, _ params: [any SQLBindable] = []) throws {219        try write {220            guard try db.run("UPDATE attachments SET \(assignments) WHERE id=?", params + [id]) > 0 else {221                throw StoreError.notFound("attachment \(id)")222            }223            pending.attachments.insert(id)224        }225    }226227    public func markAttachmentUploading(id: String) throws {228        try updateAttachment(id, "state='uploading'")229    }230231    /// The server has the file: state `local`, `uploaded`. Server fields the upload answered with may be given.232    public func markAttachmentUploaded(id: String, size: Int64? = nil, sha256: String? = nil, pageCount: Int? = nil) throws {233        try updateAttachment(id, """234        state='local', uploaded=1, last_error=NULL, size=coalesce(?, size), sha256=coalesce(?, sha256), page_count=coalesce(?, page_count)235        """, [size, sha256, pageCount])236    }237238    public func markAttachmentDownloading(id: String) throws {239        try updateAttachment(id, "state='downloading'")240    }241242    /// The file is on this device at `localPath` (relative): state `local`.243    public func markAttachmentDownloaded(id: String, localPath: String, at date: Date = Date()) throws {244        try updateAttachment(id, "state='local', local_path=?, last_error=NULL, last_accessed_at=?", [localPath, date])245    }246247    /// A transfer failed. Upload (file never on the server): state `failed`, `uploadAttempts + 1`, still listed248    /// by `pendingUploads`. Download: back to `remote` (or `local` when a file is present), error recorded.249    public func markAttachmentFailed(id: String, error: String) throws {250        try updateAttachment(id, """251        state=CASE WHEN uploaded=0 THEN 'failed' WHEN local_path IS NOT NULL THEN 'local' ELSE 'remote' END,252        upload_attempts=upload_attempts + (CASE WHEN uploaded=0 THEN 1 ELSE 0 END), last_error=?253        """, [error])254    }255256    public func setAttachmentKeepOffline(id: String, _ keep: Bool) throws {257        try updateAttachment(id, "keep_offline=?", [keep])258    }259260    /// Stamps `lastAccessedAt` (the file was opened): feeds the least-recently-used eviction order.261    public func touchAttachment(id: String, at date: Date = Date()) throws {262        try updateAttachment(id, "last_accessed_at=?", [date])263    }264265    /// Deletes an attachment locally. Never uploaded: the row disappears, no op. Otherwise the row is kept with266    /// `deletedAt` and a `deleteAttachment` op is queued. Returns the relative path of the local file to remove.267    @discardableResult268    public func markAttachmentDeleted(id: String) throws -> String? {269        try write {270            let record = try requireAttachment(id)271            pending.attachments.insert(id)272            if !record.uploaded {273                try db.run("DELETE FROM attachments WHERE id=?", [id])274                return record.localPath275            }276            guard record.deletedAt == nil else { return nil }277            let now = nowMillis()278            try db.run("UPDATE attachments SET deleted_at=?, state='remote', local_path=NULL WHERE id=?", [now, id])279            try insertOp(kind: OpKind.deleteAttachment, targetId: id, payload: nil, coalescable: false, now: now)280            return record.localPath281        }282    }283284    /// Files that can be removed from the device to free space: on the server, on the device, not `keepOffline`;285    /// least recently used first.286    public func evictionCandidates(limit: Int = 50) throws -> [AttachmentRecord] {287        try db.query("""288        SELECT \(Self.attachmentColumns) FROM attachments289        WHERE local_path IS NOT NULL AND uploaded=1 AND keep_offline=0 AND state='local'290        ORDER BY coalesce(last_accessed_at, 0), id LIMIT ?291        """, [limit], Self.decodeAttachment)292    }293294    /// Attachments worth downloading in the background: on the server but not on this device (state `remote`,295    /// which includes failed downloads, or `failed` without a local file), not deleted, whose page is not in the296    /// trash, and that are `keepOffline` (whatever their size), or an image (`image/*`) or a drawing source297    /// (`*.pkdrawing`) of at most `maxSize` bytes. Newest first.298    public func attachmentsWantedOffline(maxSize: Int64, limit: Int = 50) throws -> [AttachmentRecord] {299        try db.query("""300        SELECT \(Self.attachmentColumns) FROM attachments a301        WHERE a.deleted_at IS NULL AND a.uploaded=1 AND a.local_path IS NULL AND a.state IN ('remote', 'failed')302          AND NOT EXISTS(SELECT 1 FROM pages p WHERE p.id=a.page_id AND p.deleted_at IS NOT NULL)303          AND (a.keep_offline=1 OR ((a.mime LIKE 'image/%' OR a.filename LIKE '%.pkdrawing') AND a.size<=?))304        ORDER BY a.created_at DESC, a.id LIMIT ?305        """, [maxSize, max(limit, 0)], Self.decodeAttachment)306    }307308    /// The caller removed the local file of an evicted attachment: state `remote`, no path.309    public func markAttachmentEvicted(id: String) throws {310        try updateAttachment(id, "state='remote', local_path=NULL")311    }312313    /// After a crash: `uploading` → `pendingUpload`, `downloading` → `remote`/`local`. For the engine at start-up.314    public func resetTransientAttachmentStates() throws {315        try write {316            let ids = try db.query("SELECT id FROM attachments WHERE state IN ('uploading', 'downloading')") { $0.string(0) }317            guard !ids.isEmpty else { return }318            try db.run("UPDATE attachments SET state='pendingUpload' WHERE state='uploading'")319            try db.run("UPDATE attachments SET state=CASE WHEN local_path IS NOT NULL THEN 'local' ELSE 'remote' END WHERE state='downloading'")320            pending.attachments.formUnion(ids)321        }322    }323}324
Files main
Menu

Theme