Repository Public
1// SP Notes Core2// File: Sources/SPNotesStore/NotesStore+Pages.swift3// Description: Local page mutations (create, edit, properties, meta fields, move, tags, trash, restore, purge,4// duplicate). Each is one atomic transaction that maintains dirty flags, the base copy and the FTS index.5//6// Author: Simon-Pierre Boucher7// Contact: contact@spboucher.ai8import Foundation910extension NotesStore {11 // MARK: Tags normalisation1213 /// Normalises tag names like the server: trimmed, leading `#` removed, lowercased, at most 40 characters,14 /// empty names dropped, duplicates removed (first occurrence wins), at most 20 tags.15 public static func normalizeTags(_ names: [String]) -> [String] {16 var seen = Set<String>()17 var out: [String] = []18 for raw in names {19 var name = raw.trimmingCharacters(in: .whitespacesAndNewlines)20 while name.hasPrefix("#") { name.removeFirst() }21 name = String(name.trimmingCharacters(in: .whitespacesAndNewlines).lowercased().prefix(40))22 .trimmingCharacters(in: .whitespacesAndNewlines)23 guard !name.isEmpty, !name.contains("\u{1F}"), seen.insert(name).inserted else { continue }24 out.append(name)25 if out.count == 20 { break }26 }27 return out28 }2930 // MARK: Shared mutation helpers3132 /// Common tail of every local edit: bumps `editSeq`, stamps `localUpdatedAt` (and `updatedAt` for33 /// version-guarded edits), clears a previous push error, recomputes `syncState`, records the change event.34 func finishLocalEdit(_ id: String, now: Int64, touchUpdatedAt: Bool) throws {35 try db.run("""36 UPDATE pages SET edit_seq=edit_seq+1, local_updated_at=?, updated_at=CASE WHEN ? THEN ? ELSE updated_at END, sync_error=NULL WHERE id=?37 """, [now, touchUpdatedAt, now, id])38 try refreshState(id)39 pending.pages.insert(id)40 }4142 func lastSiblingPosition(parentId: String?) throws -> Double? {43 try db.first("SELECT position FROM pages WHERE parent_id IS ? ORDER BY position DESC LIMIT 1", [parentId]) { $0.double(0) }44 }4546 func replaceTags(rid: Int64, names: [String]) throws {47 try db.run("DELETE FROM page_tags WHERE page_rid=?", [rid])48 for name in names {49 try db.run("INSERT OR IGNORE INTO page_tags(page_rid, name) VALUES(?, ?)", [rid, name])50 try db.run("INSERT OR IGNORE INTO tags(name, color) VALUES(?, NULL)", [name])51 }52 }5354 // MARK: Create5556 /// Creates a page locally (`needsCreate`, `dirtyContent`, `syncState = pending`). Throws `invalid` when the57 /// id already exists or the properties are not a JSON object, `notFound` when the parent does not exist.58 @discardableResult59 public func createPage(_ draft: NewPage = NewPage()) throws -> PageRecord {60 try write {61 let id = try insertLocal(draft, now: nowMillis())62 return try requirePage(id).page63 }64 }6566 /// Creates many pages in ONE transaction (import, tests). Returns the ids in order. All or nothing.67 @discardableResult68 public func createPages(_ drafts: [NewPage]) throws -> [String] {69 try write {70 let now = nowMillis()71 return try drafts.map { try insertLocal($0, now: now) }72 }73 }7475 func insertLocal(_ draft: NewPage, now: Int64) throws -> String {76 let id = (draft.id ?? Self.newId()).lowercased()77 guard !id.isEmpty else { throw StoreError.invalid("empty page id") }78 guard try db.first("SELECT 1 FROM pages WHERE id=?", [id], { _ in true }) == nil else {79 throw StoreError.invalid("page \(id) already exists")80 }81 guard let flags = PropertiesJSON.flags(draft.properties) else { throw StoreError.invalid("properties is not a JSON object") }82 var section = draft.section83 if let parentId = draft.parentId {84 guard let parentSection = try db.first("SELECT section FROM pages WHERE id=?", [parentId], { $0.string(0) }) else {85 throw StoreError.notFound("parent page \(parentId)")86 }87 section = section ?? parentSection88 }89 let position = try draft.position ?? ((lastSiblingPosition(parentId: draft.parentId) ?? 0) + 1)90 let tags = Self.normalizeTags(draft.tags)91 try db.run("""92 INSERT INTO pages(id, parent_id, database_id, type, section, title, icon, cover, content, properties, position, pinned,93 word_count, created_at, updated_at, inbox_at, capture_kind, needs_create, dirty_content, tags_dirty, edit_seq, sync_state,94 plain_text, preview, favorite, is_folder, local_updated_at)95 VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1, 1, ?, 1, 'pending', ?, ?, ?, ?, ?)96 """, [id, draft.parentId, draft.databaseId, draft.type, section ?? "notes", draft.title, draft.icon, draft.cover, draft.content,97 draft.properties, position, draft.pinned, draft.wordCount, now, now, draft.inboxAt, draft.captureKind, !tags.isEmpty,98 draft.plainText, draft.preview, flags.favorite, flags.folder, now])99 let rid = db.lastInsertRowID100 if !tags.isEmpty {101 try replaceTags(rid: rid, names: tags)102 pending.tags = true103 }104 try reindex(rid)105 pending.pages.insert(id)106 return id107 }108109 // MARK: Content and properties110111 /// Saves an edit of the title and/or the content (nil = unchanged) with the derived values of the resulting112 /// document. Returns false, and writes nothing, when neither the title nor the content bytes differ.113 @discardableResult114 public func updateContent(id: String, title: String? = nil, content: Data? = nil, plainText: String, wordCount: Int, preview: String) throws -> Bool {115 try write {116 let loaded = try requirePage(id)117 return try applyContentEdit(loaded, title: title, content: content, properties: nil, plainText: plainText,118 wordCount: wordCount, preview: preview, now: nowMillis())119 }120 }121122 /// Core of every version-guarded edit. Returns false when nothing differs.123 func applyContentEdit(_ loaded: LoadedPage, title: String?, content: Data?, properties: Data?, plainText: String?, wordCount: Int?,124 preview: String?, favorite: Bool? = nil, isFolder: Bool? = nil, now: Int64) throws -> Bool {125 let page = loaded.page126 let newTitle = title ?? page.title127 let newContent = content ?? page.content128 let newProperties = properties ?? page.properties129 let propertiesChanged = properties != nil && !PropertiesJSON.equal(newProperties, page.properties)130 guard newTitle != page.title || newContent != page.content || propertiesChanged else { return false }131 var flags = (favorite: page.favorite, folder: page.isFolder)132 if propertiesChanged {133 guard let parsed = PropertiesJSON.flags(newProperties) else { throw StoreError.invalid("properties is not a JSON object") }134 flags = (favorite ?? parsed.favorite, isFolder ?? parsed.folder)135 }136 try captureBase(page.id)137 try db.run("""138 UPDATE pages SET title=?, content=?, properties=?, plain_text=?, word_count=?, preview=?, favorite=?, is_folder=?, dirty_content=1 WHERE rid=?139 """, [newTitle, newContent, propertiesChanged ? newProperties : page.properties, plainText ?? page.plainText,140 wordCount ?? page.wordCount, preview ?? page.preview, flags.favorite, flags.folder, loaded.rid])141 try finishLocalEdit(page.id, now: now, touchUpdatedAt: true)142 if newTitle != page.title || (plainText ?? page.plainText) != page.plainText { try reindex(loaded.rid) }143 return true144 }145146 /// Replaces the properties object. `favorite` / `isFolder` mirrors come from the arguments when given,147 /// otherwise from the top level of the JSON. Returns false when the object is unchanged (key order ignored).148 @discardableResult149 public func setProperties(id: String, properties: Data, favorite: Bool? = nil, isFolder: Bool? = nil) throws -> Bool {150 try write {151 let loaded = try requirePage(id)152 return try applyContentEdit(loaded, title: nil, content: nil, properties: properties, plainText: nil, wordCount: nil,153 preview: nil, favorite: favorite, isFolder: isFolder, now: nowMillis())154 }155 }156157 /// Sets or removes `favorite: true` in the properties object.158 @discardableResult159 public func setFavorite(id: String, _ favorite: Bool) throws -> Bool {160 try setPropertyFlag(id: id, key: "favorite", value: favorite)161 }162163 /// Sets or removes `folder: true` in the properties object.164 @discardableResult165 public func setFolder(id: String, _ isFolder: Bool) throws -> Bool {166 try setPropertyFlag(id: id, key: "folder", value: isFolder)167 }168169 private func setPropertyFlag(id: String, key: String, value: Bool) throws -> Bool {170 try write {171 let loaded = try requirePage(id)172 let properties = try PropertiesJSON.setting(loaded.page.properties, key: key, to: value)173 return try applyContentEdit(loaded, title: nil, content: nil, properties: properties, plainText: nil, wordCount: nil,174 preview: nil, now: nowMillis())175 }176 }177178 // MARK: Meta fields (not version-guarded)179180 private func setMetaColumn(id: String, field: MetaField, column: String, value: any SQLBindable, unchanged: (PageRecord) -> Bool) throws -> Bool {181 try write {182 let loaded = try requirePage(id)183 if unchanged(loaded.page) { return false }184 var dirty = loaded.page.dirtyMeta185 dirty.insert(field)186 try db.run("UPDATE pages SET \(column)=?, dirty_meta=? WHERE rid=?", [value, Self.encodeMeta(dirty), loaded.rid])187 try finishLocalEdit(id, now: nowMillis(), touchUpdatedAt: false)188 return true189 }190 }191192 @discardableResult193 public func setPinned(id: String, _ pinned: Bool) throws -> Bool {194 try setMetaColumn(id: id, field: .pinned, column: "pinned", value: pinned) { $0.pinned == pinned }195 }196197 @discardableResult198 public func setArchived(id: String, _ archived: Bool) throws -> Bool {199 try setMetaColumn(id: id, field: .archived, column: "archived", value: archived) { $0.archived == archived }200 }201202 @discardableResult203 public func setIcon(id: String, _ icon: String?) throws -> Bool {204 try setMetaColumn(id: id, field: .icon, column: "icon", value: icon) { $0.icon == icon }205 }206207 @discardableResult208 public func setCover(id: String, _ cover: String?) throws -> Bool {209 try setMetaColumn(id: id, field: .cover, column: "cover", value: cover) { $0.cover == cover }210 }211212 /// Puts the page in the inbox (a date) or takes it out (nil).213 @discardableResult214 public func setInbox(id: String, at date: Date?) throws -> Bool {215 try setMetaColumn(id: id, field: .inboxAt, column: "inbox_at", value: date) { $0.inboxAt?.millis == date?.millis }216 }217218 /// Moves a page. `parentId` nil = root of the section. `section` nil = the new parent's section (the current219 /// one at the root). `position` nil = after the last sibling when the parent changes, unchanged otherwise.220 /// A section change is propagated to the descendants. Throws `cycle` when `parentId` is the page itself or221 /// one of its descendants, `notFound` when the parent does not exist. Returns false when nothing changes.222 @discardableResult223 public func move(id: String, parentId: String?, section: String? = nil, position: Double? = nil) throws -> Bool {224 try write {225 let loaded = try requirePage(id)226 let page = loaded.page227 var parentSection: String?228 if let parentId {229 guard parentId != id else { throw StoreError.cycle }230 guard let found = try db.first("SELECT section FROM pages WHERE id=?", [parentId], { $0.string(0) }) else {231 throw StoreError.notFound("parent page \(parentId)")232 }233 parentSection = found234 let insideOwnSubtree = try db.first("""235 WITH RECURSIVE up(id) AS (236 VALUES(?)237 UNION238 SELECT p.parent_id FROM pages p JOIN up ON p.id=up.id WHERE p.parent_id IS NOT NULL239 ) SELECT 1 FROM up WHERE id=? LIMIT 1240 """, [parentId, id]) { _ in true } ?? false241 if insideOwnSubtree { throw StoreError.cycle }242 }243 let newSection = section ?? parentSection ?? page.section244 let parentChanged = parentId != page.parentId245 let newPosition = try position ?? (parentChanged ? ((lastSiblingPosition(parentId: parentId) ?? 0) + 1) : page.position)246 var dirty = page.dirtyMeta247 if parentChanged { dirty.insert(.parentId) }248 if newSection != page.section { dirty.insert(.section) }249 if newPosition != page.position { dirty.insert(.position) }250 guard dirty != page.dirtyMeta || parentChanged || newSection != page.section || newPosition != page.position else { return false }251 let now = nowMillis()252 try db.run("UPDATE pages SET parent_id=?, section=?, position=?, dirty_meta=? WHERE rid=?",253 [parentId, newSection, newPosition, Self.encodeMeta(dirty), loaded.rid])254 try finishLocalEdit(id, now: now, touchUpdatedAt: false)255 if newSection != page.section {256 let descendants = try db.query("""257 WITH RECURSIVE sub(id) AS (258 VALUES(?)259 UNION260 SELECT p.id FROM pages p JOIN sub ON p.parent_id=sub.id261 ) SELECT p.id, p.dirty_meta FROM pages p JOIN sub ON sub.id=p.id WHERE p.id<>? AND p.section<>?262 """, [id, id, newSection]) { ($0.string(0), Self.decodeMeta($0.string(1))) }263 for (childId, childDirty) in descendants {264 try db.run("UPDATE pages SET section=?, dirty_meta=? WHERE id=?",265 [newSection, Self.encodeMeta(childDirty.union([.section])), childId])266 try finishLocalEdit(childId, now: now, touchUpdatedAt: false)267 }268 }269 return true270 }271 }272273 // MARK: Tags274275 /// Replaces the tags of a page (normalised by `normalizeTags`). Returns false when the set is unchanged.276 @discardableResult277 public func setTags(id: String, names: [String]) throws -> Bool {278 try write {279 let loaded = try requirePage(id)280 let tags = Self.normalizeTags(names)281 guard Set(tags) != Set(loaded.page.tags) else { return false }282 try replaceTags(rid: loaded.rid, names: tags)283 try db.run("UPDATE pages SET tags_dirty=1 WHERE rid=?", [loaded.rid])284 try finishLocalEdit(id, now: nowMillis(), touchUpdatedAt: false)285 try reindex(loaded.rid)286 pending.tags = true287 return true288 }289 }290291 // MARK: Trash, restore, purge292293 /// Moves a page and its descendants that are not already in the trash to the trash (like the server) and294 /// queues ONE `trash` op for the page. Not a dirty state: `editSeq` and the dirty flags are untouched.295 /// Returns the ids put in the trash (empty when the page already was).296 @discardableResult297 public func trash(id: String) throws -> [String] {298 try write {299 let loaded = try requirePage(id)300 guard loaded.page.deletedAt == nil else { return [] }301 let now = nowMillis()302 let everything = try subtreeIds(id)303 let ids = try subtreeIds(id, liveOnly: true)304 // Trash followed by restore is a no-op on the server only when no descendant was already trashed.305 let coalescable = everything.count == ids.count306 for pageId in ids {307 try db.run("UPDATE pages SET deleted_at=?, local_updated_at=? WHERE id=?", [now, now, pageId])308 pending.pages.insert(pageId)309 }310 try insertOp(kind: OpKind.trash, targetId: id, payload: nil, coalescable: coalescable, now: now)311 return ids312 }313 }314315 /// Takes a page and ALL its descendants out of the trash (like the server) and queues a `restore` op, unless316 /// a `trash` op of the same page that was never handed to the sync engine can simply be cancelled.317 /// Returns the ids restored (empty when nothing was in the trash).318 @discardableResult319 public func restore(id: String) throws -> [String] {320 try write {321 _ = try requirePage(id)322 let now = nowMillis()323 var restored: [String] = []324 for pageId in try subtreeIds(id) {325 if try db.run("UPDATE pages SET deleted_at=NULL, local_updated_at=? WHERE id=? AND deleted_at IS NOT NULL", [now, pageId]) > 0 {326 restored.append(pageId)327 pending.pages.insert(pageId)328 }329 }330 guard !restored.isEmpty else { return [] }331 let last = try db.first("""332 SELECT seq, kind, handed_out, attempts, coalescable FROM ops WHERE target_id=? AND kind IN ('trash', 'restore', 'purge') ORDER BY seq DESC LIMIT 1333 """, [id]) { (seq: $0.int(0), kind: $0.string(1), handedOut: $0.bool(2), attempts: $0.int(3), coalescable: $0.bool(4)) }334 if let last, last.kind == OpKind.trash, !last.handedOut, last.attempts == 0, last.coalescable {335 try db.run("DELETE FROM ops WHERE seq=?", [last.seq])336 pending.queue = true337 } else {338 try insertOp(kind: OpKind.restore, targetId: id, payload: nil, coalescable: false, now: now)339 }340 return restored341 }342 }343344 /// Removes a page and all its descendants from this device for good, with their bases, conflicts, local345 /// versions, tags, index entries and attachment rows. Pages the server knows get a `purge` op; pages that346 /// were never pushed (`needsCreate`) simply disappear with their queued ops.347 @discardableResult348 public func purge(id: String) throws -> PurgeResult {349 try write {350 _ = try requirePage(id)351 let now = nowMillis()352 let rows = try db.query("""353 WITH RECURSIVE sub(id) AS (354 VALUES(?)355 UNION356 SELECT p.id FROM pages p JOIN sub ON p.parent_id=sub.id357 ) SELECT p.rid, p.id, p.parent_id, p.needs_create, p.dirty_meta FROM pages p JOIN sub ON sub.id=p.id358 """, [id]) { (rid: $0.int(0), id: $0.string(1), parentId: $0.optString(2), needsCreate: $0.bool(3), dirtyMeta: Self.decodeMeta($0.string(4))) }359 let onServer = Set(rows.filter { !$0.needsCreate }.map(\.id))360 var result = PurgeResult()361 // Drop the queued ops of the purged pages first (an op currently leased to the engine is left alone).362 for row in rows {363 if row.needsCreate {364 if try db.run("DELETE FROM ops WHERE target_id=?", [row.id]) > 0 { pending.queue = true }365 } else {366 if try db.run("DELETE FROM ops WHERE target_id=? AND kind IN ('trash', 'restore', 'purge') AND leased_until<=?", [row.id, now]) > 0 {367 pending.queue = true368 }369 }370 }371 // The server's purge removes ONE row (its descendants are not deleted with it): one op per page the server knows.372 for row in rows where !row.needsCreate {373 try insertOp(kind: OpKind.purge, targetId: row.id, payload: nil, coalescable: false, now: now)374 }375 for row in rows {376 result.orphanedFiles += try deletePageRow(rid: row.rid, id: row.id)377 result.pageIds.append(row.id)378 }379 return result380 }381 }382383 /// Deletes one page row with everything attached to it. Returns the relative paths of its attachment files.384 func deletePageRow(rid: Int64, id: String) throws -> [String] {385 let attachments = try db.query("SELECT id, local_path FROM attachments WHERE page_id=?", [id]) { ($0.string(0), $0.optString(1)) }386 if !attachments.isEmpty {387 try db.run("DELETE FROM attachments WHERE page_id=?", [id])388 for (attachmentId, _) in attachments {389 pending.attachments.insert(attachmentId)390 if try db.run("DELETE FROM ops WHERE target_id=? AND kind='deleteAttachment'", [attachmentId]) > 0 { pending.queue = true }391 }392 }393 try db.run("DELETE FROM pages_fts WHERE rowid=?", [rid])394 try db.run("DELETE FROM page_bases WHERE page_id=?", [id])395 if try db.run("DELETE FROM conflicts WHERE page_id=?", [id]) > 0 { pending.conflicts = true }396 try db.run("DELETE FROM local_versions WHERE page_id=?", [id])397 if try db.run("DELETE FROM canvases WHERE page_id=?", [id]) > 0 { pending.canvases.insert(id) }398 var remindersChanged = try db.run("DELETE FROM reminders WHERE page_id=? AND server_task_id IS NULL", [id]) > 0399 if try db.run("UPDATE reminders SET deleted=1, dirty=1, edit_seq=edit_seq+1, page_id=NULL WHERE page_id=?", [id]) > 0 { remindersChanged = true }400 if remindersChanged { pending.reminders = true }401 let hadTags = try db.scalar("SELECT count(*) FROM page_tags WHERE page_rid=?", [rid]) > 0402 try db.run("DELETE FROM pages WHERE rid=?", [rid])403 if hadTags { pending.tags = true }404 pending.pages.insert(id)405 return attachments.compactMap(\.1)406 }407408 // MARK: Duplicate, opened409410 /// Copies a page (not its descendants) as a new local page titled "<title> (copy)", placed right after the411 /// original. Tags, icon, cover and properties are copied; pinned, inbox and trash states are not.412 @discardableResult413 public func duplicate(id: String) throws -> PageRecord {414 try write {415 let source = try requirePage(id).page416 let next = try db.first("SELECT position FROM pages WHERE parent_id IS ? AND position>? ORDER BY position LIMIT 1",417 [source.parentId, source.position]) { $0.double(0) }418 let position = next.map { (source.position + $0) / 2 } ?? (source.position + 1)419 let draft = NewPage(parentId: source.parentId, databaseId: source.databaseId, type: source.type, section: source.section,420 title: source.title + " (copy)", icon: source.icon, cover: source.cover, content: source.content,421 properties: source.properties, position: position, plainText: source.plainText,422 wordCount: source.wordCount, preview: source.preview, tags: source.tags)423 let newId = try insertLocal(draft, now: nowMillis())424 return try requirePage(newId).page425 }426 }427428 /// Stamps `lastOpenedAt`. Local only: not dirty, `editSeq` untouched.429 public func markOpened(id: String, at date: Date = Date()) throws {430 try write {431 guard try db.run("UPDATE pages SET last_opened_at=? WHERE id=?", [date, id]) > 0 else { throw StoreError.notFound("page \(id)") }432 pending.pages.insert(id)433 }434 }435}436