Repository Public
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