Repository Public
1// SP Notes Core2// File: Sources/SPNotesStore/NotesStore+Sync.swift3// Description: Sync-facing primitives: pages to push, push acknowledgement, application of the server feed,4// conflict resolution helpers and sync state reset. Local edits are never overwritten silently.5//6// Author: Simon-Pierre Boucher7// Contact: contact@spboucher.ai8import Foundation910extension NotesStore {11 // MARK: - Push side1213 /// Pages with something to push (`isDirty`), with their base copy. Pages to create come first, parents14 /// before their children; then the others, oldest local change first. Pages in conflict are left out15 /// unless `includeConflicts`. `limit` bounds the number of full rows loaded.16 public func pagesNeedingPush(limit: Int = 100, includeConflicts: Bool = false) throws -> [PendingPage] {17 try read {18 let rows = try db.query("""19 SELECT id, parent_id, needs_create FROM pages WHERE \(Schema.dirtyPredicate)\(includeConflicts ? "" : " AND sync_state<>'conflict'")20 ORDER BY local_updated_at, rid21 """) { (id: $0.string(0), parentId: $0.optString(1), needsCreate: $0.bool(2)) }22 let creating = Dictionary(uniqueKeysWithValues: rows.filter(\.needsCreate).map { ($0.id, $0.parentId) })23 func depth(_ id: String) -> Int {24 var depth = 025 var current = creating[id] ?? nil26 while let parent = current, creating[parent] != nil, depth < 1000 {27 depth += 128 current = creating[parent] ?? nil29 }30 return depth31 }32 let creates = rows.filter(\.needsCreate).map { ($0.id, depth($0.id)) }33 let ordered = creates.enumerated().sorted { ($0.element.1, $0.offset) < ($1.element.1, $1.offset) }.map(\.element.0)34 + rows.filter { !$0.needsCreate }.map(\.id)35 var out: [PendingPage] = []36 for id in ordered.prefix(max(limit, 0)) {37 guard let loaded = try loadPage(id) else { continue }38 out.append(PendingPage(page: loaded.page, base: try loadBase(id)))39 }40 return out41 }42 }4344 /// Number of pages with something to push (conflicts included).45 public func pendingPushCount() throws -> Int {46 Int(try db.scalar("SELECT count(*) FROM pages WHERE \(Schema.dirtyPredicate)"))47 }4849 /// The base copy of a page (last synced title/content/properties), when it has pending content edits.50 public func pageBase(id: String) throws -> PageBase? {51 try loadBase(id)52 }5354 /// Acknowledges a successful push of the row as it was at `pushedEditSeq`.55 ///56 /// - `editSeq == pushedEditSeq` (no edit during the push): every dirty flag and `needsCreate` are cleared,57 /// the base copy is dropped, `serverVersion` and `updatedAt` are set, `syncState = synced`; when58 /// `serverContent` (the server's normalised content) is given it replaces the local content.59 /// - otherwise (the note changed while the push was in flight): `needsCreate` is cleared and60 /// `serverVersion` advances, but the page stays dirty. With `pushed` (what was sent), the base copy61 /// becomes that copy at `serverVersion` (content = `serverContent` when given), or, if the62 /// version-guarded fields are now identical to what was pushed (only meta/tags changed meanwhile),63 /// `dirtyContent` is cleared and the base dropped. Without `pushed` the existing base is left as is.64 ///65 /// Throws `notFound` when the page no longer exists locally (it was purged during the push).66 public func markPushed(id: String, serverVersion: Int, updatedAt: Date, pushedEditSeq: Int64, pushed: PushedCopy? = nil,67 serverContent: Data? = nil) throws {68 try write {69 let loaded = try requirePage(id)70 let page = loaded.page71 if page.editSeq == pushedEditSeq {72 try db.run("""73 UPDATE pages SET needs_create=0, dirty_content=0, dirty_meta='', tags_dirty=0, server_version=?, updated_at=?, sync_error=NULL,74 content=coalesce(?, content) WHERE rid=?75 """, [serverVersion, updatedAt, serverContent, loaded.rid])76 try db.run("DELETE FROM page_bases WHERE page_id=?", [id])77 } else {78 var dirtyContent = page.dirtyContent79 if let pushed {80 let same = page.title == pushed.title && page.content == pushed.content && PropertiesJSON.equal(page.properties, pushed.properties)81 dirtyContent = !same82 if same {83 try db.run("DELETE FROM page_bases WHERE page_id=?", [id])84 if let serverContent { try db.run("UPDATE pages SET content=? WHERE rid=?", [serverContent, loaded.rid]) }85 } else {86 try putBase(id, PageBase(title: pushed.title, content: serverContent ?? pushed.content, properties: pushed.properties, version: serverVersion))87 }88 }89 try db.run("UPDATE pages SET needs_create=0, server_version=?, dirty_content=? WHERE rid=?", [serverVersion, dirtyContent, loaded.rid])90 }91 try refreshState(id)92 pending.pages.insert(id)93 }94 }9596 /// Records a failed push: `syncError` is set and `syncState = error`. The page stays dirty and is still97 /// returned by `pagesNeedingPush()`. The next local edit or successful push clears the error.98 public func markPushFailed(id: String, error: String) throws {99 try write {100 guard try db.run("UPDATE pages SET sync_error=? WHERE id=? AND \(Schema.dirtyPredicate)", [error, id]) > 0 else { return }101 try refreshState(id)102 pending.pages.insert(id)103 }104 }105106 func putBase(_ id: String, _ base: PageBase) throws {107 try db.run("""108 INSERT INTO page_bases(page_id, title, content, properties, version) VALUES(?, ?, ?, ?, ?)109 ON CONFLICT(page_id) DO UPDATE SET title=excluded.title, content=excluded.content, properties=excluded.properties, version=excluded.version110 """, [id, base.title, base.content, base.properties, base.version])111 }112113 /// Writes every column of a page row from a record (`sync_state` is recomputed by `refreshState`).114 func storePage(rid: Int64, _ p: PageRecord) throws {115 try db.run("""116 UPDATE pages SET parent_id=?, database_id=?, type=?, section=?, title=?, icon=?, cover=?, content=?, properties=?, position=?,117 pinned=?, archived=?, server_version=?, word_count=?, created_at=?, updated_at=?, last_opened_at=?, deleted_at=?, inbox_at=?,118 capture_kind=?, rev=?, needs_create=?, dirty_content=?, dirty_meta=?, tags_dirty=?, edit_seq=?, sync_error=?, plain_text=?,119 preview=?, favorite=?, is_folder=?, local_updated_at=? WHERE rid=?120 """, [p.parentId, p.databaseId, p.type, p.section, p.title, p.icon, p.cover, p.content, p.properties, p.position,121 p.pinned, p.archived, p.serverVersion, p.wordCount, p.createdAt, p.updatedAt, p.lastOpenedAt, p.deletedAt, p.inboxAt,122 p.captureKind, p.rev, p.needsCreate, p.dirtyContent, Self.encodeMeta(p.dirtyMeta), p.tagsDirty, p.editSeq, p.syncError,123 p.plainText, p.preview, p.favorite, p.isFolder, p.localUpdatedAt, rid])124 try refreshState(p.id)125 }126127 // MARK: - Pull side128129 /// Applies one batch of the server feed in ONE transaction (all or nothing, cursor included).130 ///131 /// Pages:132 /// - unknown id → inserted as synced;133 /// - local clean → every field replaced by the remote copy, tags included (version-guarded fields are kept134 /// when the remote version is OLDER than `serverVersion`, i.e. a stale feed page after a push);135 /// - local dirty → `needsCreate` is cleared (the row exists on the server); meta fields that are not in136 /// `dirtyMeta` take the remote value; tags are replaced unless `tagsDirty`; then, for the version-guarded137 /// fields: no pending content edit → remote copy adopted; pending content edit and138 /// `remote.version > serverVersion` → if the remote title/content/properties equal the local ones (our own139 /// push whose acknowledgement was lost) the version is adopted and `dirtyContent` cleared, otherwise the140 /// page is returned in `diverged` and its local content, base and version are NOT touched; remote version141 /// ≤ `serverVersion` → nothing.142 /// - a remote page whose `rev` is lower than the local row's `rev` is ignored (stale feed page);143 /// - `deletedAt` follows the server, except for pages covered by a queued local `trash`/`restore` op.144 ///145 /// Tombstones: a clean page is removed with everything attached; a dirty page is kept and becomes146 /// `needsCreate` again (`serverVersion = 0`, base dropped). Attachment tombstones remove the row.147 /// Attachments are upserted; local file state (`state`, `localPath`, `keepOffline`…) is kept.148 /// `tags` (nil = not in this batch) replaces the tag list and colours. `cursor` (nil = unchanged) is stored.149 @discardableResult150 public func applyRemote(pages: [RemotePage] = [], attachments: [RemoteAttachment] = [], tombstones: [Tombstone] = [],151 tags: [RemoteTag]? = nil, cursor: Int64? = nil) throws -> ApplySummary {152 try write {153 var summary = ApplySummary()154 let now = nowMillis()155 let locallyTrashedTargets = Set(try db.query(156 "SELECT DISTINCT target_id FROM ops WHERE kind IN ('trash', 'restore') AND target_id IS NOT NULL") { $0.string(0) })157158 for remote in pages {159 try applyRemotePage(remote, now: now, protectedTargets: locallyTrashedTargets, summary: &summary)160 }161162 var removedPages = false163 for tombstone in tombstones {164 let id = tombstone.id.lowercased()165 switch tombstone.kind {166 case .page:167 guard let loaded = try loadPage(id) else { continue }168 if try db.run("DELETE FROM ops WHERE target_id=? AND kind IN ('trash', 'restore', 'purge')", [id]) > 0 { pending.queue = true }169 if loaded.page.isDirty {170 // The server row is gone but the user has unsynced work: keep it and create it again.171 var page = loaded.page172 page.needsCreate = true173 page.dirtyContent = true174 page.dirtyMeta = []175 page.tagsDirty = !page.tags.isEmpty176 page.serverVersion = 0177 page.rev = 0178 try db.run("DELETE FROM page_bases WHERE page_id=?", [id])179 if try db.run("DELETE FROM conflicts WHERE page_id=?", [id]) > 0 { pending.conflicts = true }180 try storePage(rid: loaded.rid, page)181 pending.pages.insert(id)182 summary.resurrectedPageIds.append(id)183 } else {184 summary.orphanedFiles += try deletePageRow(rid: loaded.rid, id: id)185 summary.deletedPageIds.append(id)186 }187 removedPages = true188 case .attachment:189 guard let row = try db.first("SELECT local_path FROM attachments WHERE id=?", [id], { $0.optString(0) }) else { continue }190 try db.run("DELETE FROM attachments WHERE id=?", [id])191 if try db.run("DELETE FROM ops WHERE target_id=? AND kind='deleteAttachment'", [id]) > 0 { pending.queue = true }192 if let path = row { summary.orphanedFiles.append(path) }193 summary.deletedAttachmentIds.append(id)194 pending.attachments.insert(id)195 }196 }197 if removedPages {198 // Unsynced pages whose parent was purged move to the root of their section, so they stay reachable199 // and their creation does not reference a missing parent.200 let orphans = try db.query("""201 SELECT id, dirty_meta FROM pages WHERE \(Schema.dirtyPredicate) AND parent_id IS NOT NULL AND parent_id NOT IN (SELECT id FROM pages)202 """) { ($0.string(0), Self.decodeMeta($0.string(1))) }203 for (id, dirty) in orphans {204 try db.run("UPDATE pages SET parent_id=NULL, dirty_meta=? WHERE id=?", [Self.encodeMeta(dirty.union([.parentId])), id])205 pending.pages.insert(id)206 }207 }208209 for remote in attachments {210 let id = remote.id.lowercased()211 try db.run("""212 INSERT INTO attachments(id, page_id, filename, mime, size, sha256, page_count, created_at, deleted_at, rev, state, uploaded)213 VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'remote', 1)214 ON CONFLICT(id) DO UPDATE SET page_id=excluded.page_id, filename=excluded.filename, mime=excluded.mime, size=excluded.size,215 sha256=coalesce(excluded.sha256, attachments.sha256), page_count=coalesce(excluded.page_count, attachments.page_count),216 created_at=excluded.created_at, rev=excluded.rev, uploaded=1,217 deleted_at=coalesce(excluded.deleted_at, CASE WHEN EXISTS(SELECT 1 FROM ops o WHERE o.kind='deleteAttachment' AND o.target_id=attachments.id)218 THEN attachments.deleted_at END),219 state=CASE WHEN attachments.state IN ('pendingUpload', 'uploading', 'failed') THEN 'local' ELSE attachments.state END220 """, [id, remote.pageId, remote.filename, remote.mime, remote.size, remote.sha256, remote.pageCount, remote.createdAt,221 remote.deletedAt, remote.rev])222 summary.changedAttachmentIds.append(id)223 pending.attachments.insert(id)224 }225226 if let tags {227 let before = try db.query("SELECT name, coalesce(color, '') FROM tags ORDER BY name") { "\($0.string(0))\u{1F}\($0.string(1))" }228 try db.run("DELETE FROM tags")229 for tag in tags where !tag.name.isEmpty {230 try db.run("INSERT INTO tags(name, color) VALUES(?, ?) ON CONFLICT(name) DO UPDATE SET color=excluded.color", [tag.name, tag.color])231 }232 // Names still used by local pages (unsynced tag edits) stay listed.233 try db.run("INSERT OR IGNORE INTO tags(name, color) SELECT DISTINCT name, NULL FROM page_tags")234 let after = try db.query("SELECT name, coalesce(color, '') FROM tags ORDER BY name") { "\($0.string(0))\u{1F}\($0.string(1))" }235 if before != after { summary.tagsChanged = true }236 }237 if summary.tagsChanged { pending.tags = true }238239 if let cursor { try putMeta(Self.syncCursorKey, String(cursor)) }240 return summary241 }242 }243244 private func applyRemotePage(_ remote: RemotePage, now: Int64, protectedTargets: Set<String>, summary: inout ApplySummary) throws {245 // Dates are compared after the same millisecond rounding as the stored ones.246 var r = remote247 r.createdAt = Date(millis: r.createdAt.millis)248 r.updatedAt = Date(millis: r.updatedAt.millis)249 r.lastOpenedAt = r.lastOpenedAt.map { Date(millis: $0.millis) }250 r.deletedAt = r.deletedAt.map { Date(millis: $0.millis) }251 r.inboxAt = r.inboxAt.map { Date(millis: $0.millis) }252 let id = r.id.lowercased()253 let remoteFlags = PropertiesJSON.flags(r.properties) ?? (false, false)254 let remoteTags = Self.uniqueTags(r.tags)255256 guard let loaded = try loadPage(id) else {257 try db.run("""258 INSERT INTO pages(id, parent_id, database_id, type, section, title, icon, cover, content, properties, position, pinned, archived,259 server_version, word_count, created_at, updated_at, last_opened_at, deleted_at, inbox_at, capture_kind, rev, sync_state,260 plain_text, preview, favorite, is_folder, local_updated_at)261 VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'synced', ?, ?, ?, ?, ?)262 """, [id, r.parentId, r.databaseId, r.type, r.section, r.title, r.icon, r.cover, r.content, r.properties, r.position, r.pinned,263 r.archived, r.version, r.wordCount, r.createdAt, r.updatedAt, r.lastOpenedAt, r.deletedAt, r.inboxAt, r.captureKind, r.rev,264 r.plainText, r.preview, remoteFlags.0, remoteFlags.1, r.updatedAt])265 let rid = db.lastInsertRowID266 if !remoteTags.isEmpty {267 try replaceTags(rid: rid, names: remoteTags)268 summary.tagsChanged = true269 }270 try reindex(rid)271 pending.pages.insert(id)272 summary.insertedPageIds.append(id)273 return274 }275276 let local = loaded.page277 // A feed page older than what this row already reflects (fetched before a newer one was applied) is ignored.278 guard r.rev >= local.rev else { return }279 var page = local280 var tagsReplaced = false281282 // Fields that are never edited locally.283 page.databaseId = r.databaseId284 page.type = r.type285 page.createdAt = r.createdAt286 // a server that does not echo the kind (2.x, or a page created before it stored one) does not erase the local one287 page.captureKind = r.captureKind ?? page.captureKind288 page.rev = r.rev289 if let remoteOpened = r.lastOpenedAt, remoteOpened > (page.lastOpenedAt ?? .distantPast) { page.lastOpenedAt = remoteOpened }290 if try !isCoveredByLocalTrashOp(id, targets: protectedTargets) { page.deletedAt = r.deletedAt }291292 // Meta fields: the remote value wins unless a local change is waiting to be pushed.293 if !page.dirtyMeta.contains(.parentId) { page.parentId = r.parentId }294 if !page.dirtyMeta.contains(.section) { page.section = r.section }295 if !page.dirtyMeta.contains(.position) { page.position = r.position }296 if !page.dirtyMeta.contains(.pinned) { page.pinned = r.pinned }297 if !page.dirtyMeta.contains(.archived) { page.archived = r.archived }298 if !page.dirtyMeta.contains(.icon) { page.icon = r.icon }299 if !page.dirtyMeta.contains(.cover) { page.cover = r.cover }300 if !page.dirtyMeta.contains(.inboxAt) { page.inboxAt = r.inboxAt }301302 // The row exists on the server.303 page.needsCreate = false304305 func adoptContent() {306 page.title = r.title307 page.content = r.content308 page.properties = r.properties309 page.plainText = r.plainText310 page.wordCount = r.wordCount311 page.preview = r.preview312 page.favorite = remoteFlags.0313 page.isFolder = remoteFlags.1314 page.serverVersion = r.version315 page.updatedAt = r.updatedAt316 }317318 if !local.dirtyContent {319 if r.version >= local.serverVersion { adoptContent() }320 } else if r.version > local.serverVersion {321 if local.title == r.title && local.content == r.content && PropertiesJSON.equal(local.properties, r.properties) {322 adoptContent()323 page.dirtyContent = false324 try db.run("DELETE FROM page_bases WHERE page_id=?", [id])325 if try db.run("DELETE FROM conflicts WHERE page_id=?", [id]) > 0 { pending.conflicts = true }326 } else {327 summary.diverged.append(DivergedPage(id: id, remote: remote))328 }329 }330331 if !local.tagsDirty, Set(remoteTags) != Set(local.tags) {332 try replaceTags(rid: loaded.rid, names: remoteTags)333 page.tags = remoteTags.sorted()334 tagsReplaced = true335 summary.tagsChanged = true336 }337338 guard page != local || tagsReplaced else { return }339 try storePage(rid: loaded.rid, page)340 if tagsReplaced || page.title != local.title || page.plainText != local.plainText { try reindex(loaded.rid) }341 pending.pages.insert(id)342 summary.updatedPageIds.append(id)343 }344345 private static func uniqueTags(_ names: [String]) -> [String] {346 var seen = Set<String>()347 return names.filter { !$0.isEmpty && !$0.contains("\u{1F}") && seen.insert($0).inserted }348 }349350 /// True when the page or one of its ancestors is the target of a queued local trash/restore op: its351 /// `deletedAt` is then a local decision that the feed must not undo before the op is pushed.352 private func isCoveredByLocalTrashOp(_ id: String, targets: Set<String>) throws -> Bool {353 guard !targets.isEmpty else { return false }354 var current: String? = id355 var hops = 0356 while let pageId = current, hops < 256 {357 if targets.contains(pageId) { return true }358 current = try db.first("SELECT parent_id FROM pages WHERE id=?", [pageId]) { $0.optString(0) } ?? nil359 hops += 1360 }361 return false362 }363364 /// For a server `reset`: the cursor goes back to 0 so that the whole feed is applied again. Nothing local365 /// is lost: pages, dirty flags, bases, queue, conflicts and attachments are untouched (only the stored366 /// server `rev` of each row is zeroed, because change sequences may restart after a reset).367 public func resetSyncState() throws {368 try write {369 try putMeta(Self.syncCursorKey, "0")370 // Server change sequences may restart after a reset: forget them so that no feed page looks stale.371 try db.run("UPDATE pages SET rev=0 WHERE rev<>0")372 try db.run("UPDATE attachments SET rev=0 WHERE rev<>0")373 }374 }375376 /// After a complete re-download from 0: removes the SYNCED pages whose id is not in `ids` (they were purged377 /// on the server while this device missed the tombstones). Dirty pages and pages never pushed are kept.378 /// Returns the removed ids and the attachment files to delete.379 @discardableResult380 public func removeSyncedPages(notIn ids: Set<String>) throws -> PurgeResult {381 try write {382 let candidates = try db.query("SELECT rid, id FROM pages WHERE NOT \(Schema.dirtyPredicate) AND server_version>0") { ($0.int(0), $0.string(1)) }383 var result = PurgeResult()384 for (rid, id) in candidates where !ids.contains(id) {385 if try db.run("DELETE FROM ops WHERE target_id=? AND kind IN ('trash', 'restore', 'purge')", [id]) > 0 { pending.queue = true }386 result.orphanedFiles += try deletePageRow(rid: rid, id: id)387 result.pageIds.append(id)388 }389 return result390 }391 }392393 // MARK: - Conflict resolution394395 /// Resolves a conflict (or a divergence) by REPLACING the local version-guarded fields with `copy`, stored as396 /// synced at `version`: `dirtyContent` and `needsCreate` cleared, base dropped, conflict record removed.397 /// The local content it replaces is first saved as a local version (reason "conflict-local") unless identical.398 /// Pending meta and tag changes stay dirty.399 public func resolveConflictUsingCopy(id: String, copy: ContentCopy, version: Int, updatedAt: Date) throws {400 try write {401 let loaded = try requirePage(id)402 guard let flags = PropertiesJSON.flags(copy.properties) else { throw StoreError.invalid("properties is not a JSON object") }403 let now = nowMillis()404 if loaded.page.title != copy.title || loaded.page.content != copy.content {405 _ = try takeSnapshot(of: loaded.page, reason: "conflict-local", now: now)406 }407 try db.run("""408 UPDATE pages SET title=?, content=?, properties=?, plain_text=?, word_count=?, preview=?, favorite=?, is_folder=?, server_version=?,409 updated_at=?, dirty_content=0, needs_create=0, edit_seq=edit_seq+1, local_updated_at=?, sync_error=NULL WHERE rid=?410 """, [copy.title, copy.content, copy.properties, copy.plainText, copy.wordCount, copy.preview, flags.favorite, flags.folder,411 version, updatedAt, now, loaded.rid])412 try db.run("DELETE FROM page_bases WHERE page_id=?", [id])413 try removeConflict(id)414 try reindex(loaded.rid)415 pending.pages.insert(id)416 }417 }418419 /// Resolves a conflict (or a divergence) by KEEPING local work: the base copy and `serverVersion` become the420 /// server's copy, so that the next push is made against that version; the page stays `dirtyContent`; the421 /// conflict record is removed. With `merged`, the local version-guarded fields are first replaced by the422 /// merge result (the previous local content is saved as a local version, reason "conflict-local").423 public func resolveConflictKeepingLocal(id: String, server: ServerCopy, merged: ContentCopy? = nil) throws {424 try write {425 let loaded = try requirePage(id)426 let now = nowMillis()427 if let merged {428 guard let flags = PropertiesJSON.flags(merged.properties) else { throw StoreError.invalid("properties is not a JSON object") }429 if loaded.page.title != merged.title || loaded.page.content != merged.content {430 _ = try takeSnapshot(of: loaded.page, reason: "conflict-local", now: now)431 }432 try db.run("""433 UPDATE pages SET title=?, content=?, properties=?, plain_text=?, word_count=?, preview=?, favorite=?, is_folder=?, updated_at=? WHERE rid=?434 """, [merged.title, merged.content, merged.properties, merged.plainText, merged.wordCount, merged.preview, flags.favorite,435 flags.folder, now, loaded.rid])436 try reindex(loaded.rid)437 }438 try putBase(id, PageBase(title: server.title, content: server.content, properties: server.properties, version: server.version))439 try db.run("""440 UPDATE pages SET server_version=?, needs_create=0, dirty_content=1, edit_seq=edit_seq+1, local_updated_at=?, sync_error=NULL WHERE rid=?441 """, [server.version, now, loaded.rid])442 try removeConflict(id)443 pending.pages.insert(id)444 }445 }446}447