Repository Public
1// SP Notes Core2// File: Sources/SPNotesStore/NotesStore+Queries.swift3// Description: Read queries for the UI (smart lists, tree, tags, counts, summaries without the content blob)4// and full-text search (FTS5, bm25, prefix matching, snippets, combinable filters).5//6// Author: Simon-Pierre Boucher7// Contact: contact@spboucher.ai8import Foundation910extension NotesStore {11 // MARK: - Summaries1213 static let summaryColumns = """14 p.id, p.title, p.preview, p.updated_at, p.parent_id, p.section, p.pinned, p.favorite, p.is_folder, p.icon, \15 (SELECT group_concat(name, char(31)) FROM (SELECT name FROM page_tags WHERE page_rid=p.rid ORDER BY name)), \16 p.sync_state, EXISTS(SELECT 1 FROM pages c WHERE c.parent_id=p.id AND c.deleted_at IS NULL), \17 p.type, p.position, p.archived, p.word_count, p.last_opened_at, p.deleted_at, p.inbox_at18 """19 static let summaryColumnCount = 202021 static func decodeSummary(_ r: Row) -> PageSummary {22 PageSummary(id: r.string(0), title: r.string(1), preview: r.string(2), updatedAt: r.date(3), parentId: r.optString(4),23 section: r.string(5), pinned: r.bool(6), favorite: r.bool(7), isFolder: r.bool(8), icon: r.optString(9),24 tags: decodeTags(r.optString(10)), syncState: SyncState(rawValue: r.string(11)) ?? .synced, hasChildren: r.bool(12),25 type: r.string(13), position: r.double(14), archived: r.bool(15), wordCount: Int(r.int(16)),26 lastOpenedAt: r.optDate(17), deletedAt: r.optDate(18), inboxAt: r.optDate(19))27 }2829 /// The full row of a page, or nil.30 public func page(id: String) throws -> PageRecord? {31 try loadPage(id)?.page32 }3334 public func summary(id: String) throws -> PageSummary? {35 try db.first("SELECT \(Self.summaryColumns) FROM pages p WHERE p.id=?", [id], Self.decodeSummary)36 }3738 /// Total number of page rows (trash included).39 public func pageCount() throws -> Int {40 Int(try db.scalar("SELECT count(*) FROM pages"))41 }4243 /// A smart list, optionally restricted to a section. See `SmartList` for each list's set and order.44 public func summaries(list: SmartList, section: String? = nil, limit: Int = 200, offset: Int = 0) throws -> [PageSummary] {45 let condition: String46 let order: String47 switch list {48 case .all:49 condition = "p.deleted_at IS NULL AND p.archived=0"; order = "p.updated_at DESC"50 case .recent:51 condition = "p.deleted_at IS NULL AND p.archived=0"; order = "max(p.updated_at, coalesce(p.last_opened_at, 0)) DESC"52 case .pinned:53 condition = "p.pinned=1 AND p.deleted_at IS NULL AND p.archived=0"; order = "p.updated_at DESC"54 case .favorites:55 condition = "p.favorite=1 AND p.deleted_at IS NULL AND p.archived=0"; order = "p.updated_at DESC"56 case .inbox:57 condition = "p.inbox_at IS NOT NULL AND p.deleted_at IS NULL"; order = "p.inbox_at DESC"58 case .archived:59 condition = "p.archived=1 AND p.deleted_at IS NULL"; order = "p.updated_at DESC"60 case .trash:61 condition = "p.deleted_at IS NOT NULL"; order = "p.deleted_at DESC"62 }63 var params: [any SQLBindable] = []64 var sql = "SELECT \(Self.summaryColumns) FROM pages p WHERE \(condition)"65 if let section {66 sql += " AND p.section=?"67 params.append(section)68 }69 sql += " ORDER BY \(order), p.rid DESC LIMIT ? OFFSET ?"70 params += [max(limit, 0), max(offset, 0)]71 return try db.query(sql, params, Self.decodeSummary)72 }7374 /// Children of a page, or with `parentId` nil the root pages (of `section` when given). Not in the trash;75 /// archived pages excluded unless asked. Ordered by position, then title.76 public func children(of parentId: String?, section: String? = nil, includeArchived: Bool = false) throws -> [PageSummary] {77 var params: [any SQLBindable] = []78 var sql = "SELECT \(Self.summaryColumns) FROM pages p WHERE "79 if let parentId {80 sql += "p.parent_id=?"81 params.append(parentId)82 } else {83 sql += "p.parent_id IS NULL"84 }85 if let section {86 sql += " AND p.section=?"87 params.append(section)88 }89 sql += " AND p.deleted_at IS NULL"90 if !includeArchived { sql += " AND p.archived=0" }91 sql += " ORDER BY p.position, p.title COLLATE NOCASE, p.rid"92 return try db.query(sql, params, Self.decodeSummary)93 }9495 /// Every folder page (not in the trash, not archived) of a section or of all sections, as a flat list ordered96 /// by section then position; the caller builds the tree from `parentId`.97 public func folderTree(section: String? = nil) throws -> [PageSummary] {98 var params: [any SQLBindable] = []99 var sql = "SELECT \(Self.summaryColumns) FROM pages p WHERE p.is_folder=1 AND p.deleted_at IS NULL AND p.archived=0"100 if let section {101 sql += " AND p.section=?"102 params.append(section)103 }104 sql += " ORDER BY p.section, p.position, p.title COLLATE NOCASE, p.rid"105 return try db.query(sql, params, Self.decodeSummary)106 }107108 /// Ancestors of a page from its parent up to the root (breadcrumb), nearest first.109 public func ancestors(of id: String) throws -> [PageSummary] {110 var out: [PageSummary] = []111 var seen: Set<String> = [id]112 var current = try db.first("SELECT parent_id FROM pages WHERE id=?", [id]) { $0.optString(0) } ?? nil113 while let parentId = current, seen.insert(parentId).inserted, let parent = try summary(id: parentId) {114 out.append(parent)115 current = parent.parentId116 }117 return out118 }119120 /// Pages carrying a tag (normalised), not in the trash, not archived; newest first.121 public func summaries(tag: String, limit: Int = 200, offset: Int = 0) throws -> [PageSummary] {122 guard let name = Self.normalizeTags([tag]).first else { return [] }123 return try db.query("""124 SELECT \(Self.summaryColumns) FROM page_tags t JOIN pages p ON p.rid=t.page_rid125 WHERE t.name=? AND p.deleted_at IS NULL AND p.archived=0 ORDER BY p.updated_at DESC, p.rid DESC LIMIT ? OFFSET ?126 """, [name, max(limit, 0), max(offset, 0)], Self.decodeSummary)127 }128129 /// Every tag known from the server or used locally, with its colour and its number of live notes.130 public func tags() throws -> [TagInfo] {131 try db.query("""132 SELECT n.name, t.color,133 (SELECT count(*) FROM page_tags pt JOIN pages p ON p.rid=pt.page_rid WHERE pt.name=n.name AND p.deleted_at IS NULL AND p.archived=0)134 FROM (SELECT name FROM tags UNION SELECT name FROM page_tags) n LEFT JOIN tags t ON t.name=n.name ORDER BY n.name135 """) { TagInfo(name: $0.string(0), color: $0.optString(1), count: Int($0.int(2))) }136 }137138 /// Number of pages per smart list, plus pending pushes and conflicts.139 public func counts() throws -> ListCounts {140 try db.first("""141 SELECT142 coalesce(sum(deleted_at IS NULL AND archived=0), 0),143 coalesce(sum(pinned=1 AND deleted_at IS NULL AND archived=0), 0),144 coalesce(sum(favorite=1 AND deleted_at IS NULL AND archived=0), 0),145 coalesce(sum(inbox_at IS NOT NULL AND deleted_at IS NULL), 0),146 coalesce(sum(archived=1 AND deleted_at IS NULL), 0),147 coalesce(sum(deleted_at IS NOT NULL), 0),148 coalesce(sum(\(Schema.dirtyPredicate)), 0),149 (SELECT count(*) FROM conflicts)150 FROM pages151 """) {152 ListCounts(all: Int($0.int(0)), pinned: Int($0.int(1)), favorites: Int($0.int(2)), inbox: Int($0.int(3)),153 archived: Int($0.int(4)), trash: Int($0.int(5)), pending: Int($0.int(6)), conflicts: Int($0.int(7)))154 } ?? ListCounts()155 }156157 // MARK: - Search158159 /// Turns arbitrary user input into a safe FTS5 query: the input is split on every character that is not a160 /// letter, digit or combining mark; each term is quoted (implicit AND); the last one is a prefix.161 /// Returns nil when the input holds no term.162 static func ftsQuery(_ input: String) -> String? {163 var terms: [String] = []164 var current = String.UnicodeScalarView()165 for scalar in input.unicodeScalars {166 if CharacterSet.alphanumerics.contains(scalar) {167 current.append(scalar)168 } else if !current.isEmpty {169 terms.append(String(current))170 current = String.UnicodeScalarView()171 }172 }173 if !current.isEmpty { terms.append(String(current)) }174 guard !terms.isEmpty else { return nil }175 terms = Array(terms.suffix(32))176 let last = terms.count - 1177 return terms.enumerated().map { "\"\($0.element)\"" + ($0.offset == last ? "*" : "") }.joined(separator: " ")178 }179180 /// Full-text search over title, body and tags, best match first (bm25: title weighs 10, tags 4, body 1).181 /// Every term must match; the last term matches as a prefix; case and diacritics are ignored. Any input is182 /// safe. With an empty query (no term), returns the pages matching the filters, newest `updatedAt` first.183 /// Trashed and archived pages are excluded unless the filters include them.184 public func search(query: String, filters: SearchFilters = SearchFilters(), limit: Int = 50) throws -> [SearchHit] {185 var cte = ""186 var cteParams: [any SQLBindable] = []187 var clauses = ""188 var params: [any SQLBindable] = []189 if let section = filters.section {190 clauses += " AND p.section=?"191 params.append(section)192 }193 if let folderId = filters.folderId {194 cte = """195 WITH RECURSIVE sub(id) AS (196 SELECT id FROM pages WHERE parent_id=?197 UNION198 SELECT c.id FROM pages c JOIN sub ON c.parent_id=sub.id199 )200 """201 cteParams.append(folderId)202 clauses += " AND p.id IN (SELECT id FROM sub)"203 }204 for tag in Self.normalizeTags(filters.tags) {205 clauses += " AND EXISTS(SELECT 1 FROM page_tags ft WHERE ft.page_rid=p.rid AND ft.name=?)"206 params.append(tag)207 }208 if let after = filters.updatedAfter {209 clauses += " AND p.updated_at>=?"210 params.append(after)211 }212 if let before = filters.updatedBefore {213 clauses += " AND p.updated_at<=?"214 params.append(before)215 }216 if filters.favoritesOnly { clauses += " AND p.favorite=1" }217 if filters.pinnedOnly { clauses += " AND p.pinned=1" }218 if !filters.includeArchived { clauses += " AND p.archived=0" }219 if !filters.includeTrash { clauses += " AND p.deleted_at IS NULL" }220221 guard let match = Self.ftsQuery(query) else {222 let sql = "\(cte) SELECT \(Self.summaryColumns) FROM pages p WHERE 1=1\(clauses) ORDER BY p.updated_at DESC, p.rid DESC LIMIT ?"223 return try db.query(sql, cteParams + params + [max(limit, 0)]) {224 let page = Self.decodeSummary($0)225 return SearchHit(page: page, snippet: page.preview, highlightedTitle: page.title, score: 0)226 }227 }228 let start = String(SearchHit.highlightStart)229 let end = String(SearchHit.highlightEnd)230 // FTS5 returns rows already sorted by rank, so snippets are only computed for the rows kept by LIMIT.231 let sql = """232 \(cte) SELECT \(Self.summaryColumns), snippet(pages_fts, 1, ?, ?, '…', 18), highlight(pages_fts, 0, ?, ?), pages_fts.rank233 FROM pages_fts CROSS JOIN pages p ON p.rid=pages_fts.rowid234 WHERE pages_fts MATCH ? AND pages_fts.rank MATCH 'bm25(10.0, 1.0, 4.0)'\(clauses)235 ORDER BY pages_fts.rank LIMIT ?236 """237 let n = Self.summaryColumnCount238 return try db.query(sql, cteParams + [start, end, start, end, match] + params + [max(limit, 0)]) {239 SearchHit(page: Self.decodeSummary($0), snippet: $0.string(n), highlightedTitle: $0.string(n + 1), score: $0.double(n + 2))240 }241 }242}243