SPB Git

Repository Public

NotesStore+Queries.swift 12.0 KB 243 lines swift

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
Files main
Menu

Theme