SPB Git

Repository Public

MetadataStore.swift 19.4 KB 255 lines swift

1// Author: Simon-Pierre Boucher — contact@spboucher.ai2// File: Core/Persistence/MetadataStore.swift3// Description: Local SQLite mirror of the owner's tree, fed by the change feed: items, purged identifiers, offline pins, materialisation state, and the tables of the sync engine (journal, transfers, conflicts). Browsing and search work from it without a network.4import Foundation56/// The mirror. One file per account; the app and its File Provider extension may open the same file (macOS) or each7/// keep their own (iOS extensions) — the schema is the same. See docs/SCHEMA.md.8public final class MetadataStore: Sendable {9    public let db: SQLiteDB10    public static let schemaVersion = 11112    public init(url: URL) throws {13        try FileManager.default.createDirectory(at: url.deletingLastPathComponent(), withIntermediateDirectories: true)14        db = try SQLiteDB(path: url.path)15        try migrate()16    }17    private func migrate() throws {18        try db.transaction {19            try db.exec("CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT NOT NULL)")20            let v = Int(try meta("schema") ?? "0") ?? 021            if v < 1 {22                try db.exec("""23                CREATE TABLE items (24                  id TEXT PRIMARY KEY, kind TEXT NOT NULL, parent_id TEXT, name TEXT NOT NULL, name_key TEXT NOT NULL, ext TEXT NOT NULL DEFAULT '',25                  size INTEGER NOT NULL DEFAULT 0, sha256 TEXT NOT NULL DEFAULT '', version_id TEXT, version_seq INTEGER NOT NULL DEFAULT 1, version_count INTEGER NOT NULL DEFAULT 1,26                  content_at REAL NOT NULL, created_at REAL NOT NULL, updated_at REAL NOT NULL, starred INTEGER NOT NULL DEFAULT 0, trashed_at REAL, trash_root INTEGER NOT NULL DEFAULT 0,27                  rev INTEGER NOT NULL DEFAULT 0, scan TEXT NOT NULL DEFAULT 'clean', thumb INTEGER NOT NULL DEFAULT 0, preview_kind TEXT NOT NULL DEFAULT 'none',28                  seq INTEGER NOT NULL);29                CREATE INDEX items_parent ON items (parent_id, trashed_at);30                CREATE INDEX items_seq ON items (seq);31                CREATE INDEX items_name ON items (name_key);32                CREATE INDEX items_updated ON items (updated_at DESC);33                CREATE INDEX items_size ON items (size DESC);34                CREATE TABLE tombstones (id TEXT PRIMARY KEY, seq INTEGER NOT NULL);35                CREATE INDEX tombstones_seq ON tombstones (seq);36                CREATE TABLE pins (id TEXT PRIMARY KEY, pinned_at REAL NOT NULL);37                CREATE TABLE local_state (id TEXT PRIMARY KEY, materialized INTEGER NOT NULL DEFAULT 0, bytes INTEGER NOT NULL DEFAULT 0, updated_at REAL NOT NULL);38                -- journal of operations decided on this device and not yet confirmed by the server (and their history)39                CREATE TABLE ops (40                  id TEXT PRIMARY KEY, kind TEXT NOT NULL, payload TEXT NOT NULL, state TEXT NOT NULL, attempts INTEGER NOT NULL DEFAULT 0, error TEXT,41                  source TEXT NOT NULL DEFAULT 'app', created_at REAL NOT NULL, updated_at REAL NOT NULL, next_try REAL NOT NULL DEFAULT 0, ord INTEGER NOT NULL);42                CREATE INDEX ops_state ON ops (state, ord);43                CREATE TABLE transfers (44                  id TEXT PRIMARY KEY, direction TEXT NOT NULL, state TEXT NOT NULL, item_id TEXT, parent_id TEXT, name TEXT NOT NULL, local_path TEXT NOT NULL,45                  size INTEGER NOT NULL DEFAULT 0, done INTEGER NOT NULL DEFAULT 0, sha256 TEXT, upload_id TEXT, base_version_id TEXT, version_id TEXT, priority INTEGER NOT NULL DEFAULT 0,46                  attempts INTEGER NOT NULL DEFAULT 0, error TEXT, source TEXT NOT NULL DEFAULT 'app', created_at REAL NOT NULL, updated_at REAL NOT NULL, finished_at REAL, ord INTEGER NOT NULL);47                CREATE INDEX transfers_state ON transfers (state, priority DESC, ord);48                -- upload sessions that can be resumed: same destination + same content = same server session49                CREATE TABLE upload_sessions (key TEXT PRIMARY KEY, upload_id TEXT NOT NULL, created_at REAL NOT NULL);50                CREATE TABLE conflicts (51                  id TEXT PRIMARY KEY, item_id TEXT NOT NULL, item_name TEXT NOT NULL, copy_id TEXT, copy_name TEXT NOT NULL, kind TEXT NOT NULL, detail TEXT NOT NULL DEFAULT '',52                  detected_at REAL NOT NULL, resolved_at REAL, resolution TEXT);53                """)54                try setMeta("schema", "1")55                try setMeta("generation", UUID().uuidString)56                try setMeta("seq", "0")57            }58        }59    }6061    // MARK: Meta62    public func meta(_ key: String) throws -> String? { try db.query("SELECT value FROM meta WHERE key = ?", [.text(key)]).first?["value"]?.text }63    public func setMeta(_ key: String, _ value: String?) throws {64        if let value { try db.run("INSERT INTO meta (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value", [.text(key), .text(value)]) }65        else { try db.run("DELETE FROM meta WHERE key = ?", [.text(key)]) }66    }67    /// Identity of this mirror: changes when it is rebuilt, which invalidates every anchor derived from `seq`.68    public var generation: String { (try? meta("generation")) ?? "" }69    public var cursor: Int64 { get { Int64((try? meta("cursor")) ?? "0") ?? 0 } }70    public func setCursor(_ c: Int64) throws { try setMeta("cursor", String(c)) }71    public var rootID: String? { try? meta("root") }72    public var lastSync: Date? { (try? meta("last_sync")).flatMap { Double($0 ?? "") }.map(Date.init(timeIntervalSince1970:)) }73    /// Local change counter. Every row written gets the next value; "what changed since N" is `seq > N`.74    public var seq: Int64 { Int64((try? meta("seq")) ?? "0") ?? 0 }75    private func nextSeq() throws -> Int64 { let n = seq + 1; try setMeta("seq", String(n)); return n }7677    // MARK: Writing78    private static let columns = "id, kind, parent_id, name, name_key, ext, size, sha256, version_id, version_seq, version_count, content_at, created_at, updated_at, starred, trashed_at, trash_root, rev, scan, thumb, preview_kind, seq"79    /// Stores items as the server described them. An item older than what is known (lower revision) is ignored.80    @discardableResult public func upsert(_ items: [DriveItem]) throws -> Int {81        guard !items.isEmpty else { return 0 }82        return try db.transaction {83            var changed = 084            for i in items {85                if let known = try db.query("SELECT rev FROM items WHERE id = ?", [.text(i.id)]).first?["rev"]?.int, known >= i.rev, i.rev != 0 { continue }86                let s = try nextSeq()87                try db.run("INSERT OR REPLACE INTO items (\(MetadataStore.columns)) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", [88                    .text(i.id), .text(i.kind.rawValue), i.parentID.map(SQLValue.text) ?? .null, .text(i.name), .text(NameRules.searchKey(i.name)), .text(i.fileExtension),89                    .int(i.size), .text(i.sha256), i.versionID.map(SQLValue.text) ?? .null, .int(Int64(i.versionSeq)), .int(Int64(i.versionCount)),90                    .double(i.contentAt.timeIntervalSince1970), .double(i.createdAt.timeIntervalSince1970), .double(i.updatedAt.timeIntervalSince1970), .int(i.starred ? 1 : 0),91                    i.trashedAt.map { .double($0.timeIntervalSince1970) } ?? .null, .int(i.trashRoot ? 1 : 0), .int(i.rev), .text(i.scan), .int(i.hasThumbnail ? 1 : 0), .text(i.previewKind), .int(s)])92                try db.run("DELETE FROM tombstones WHERE id = ?", [.text(i.id)])93                if i.isRoot { try setMeta("root", i.id) }94                changed += 195            }96            return changed97        }98    }99    /// Forgets items the server deleted for good, keeping their identifiers as tombstones for the File Provider.100    public func remove(_ ids: [String]) throws {101        guard !ids.isEmpty else { return }102        try db.transaction {103            for id in ids {104                let s = try nextSeq()105                try db.run("DELETE FROM items WHERE id = ?", [.text(id)])106                try db.run("INSERT OR REPLACE INTO tombstones (id, seq) VALUES (?, ?)", [.text(id), .int(s)])107                try db.run("DELETE FROM pins WHERE id = ?", [.text(id)])108                try db.run("DELETE FROM local_state WHERE id = ?", [.text(id)])109            }110        }111    }112    /// Applies one page of the change feed and moves the cursor, atomically.113    public func apply(_ page: ChangePage) throws {114        try db.transaction {115            try upsert(page.items); try remove(page.deleted.map(\.id))116            try setCursor(page.cursor); try setMeta("root", page.rootId)117            try setMeta("last_sync", String(Date().timeIntervalSince1970))118        }119    }120    /// Empties the mirror before a full replay (the server went back in time). Pins and the journal are kept.121    public func resetMirror() throws {122        try db.transaction {123            let ids = try db.query("SELECT id FROM items").compactMap { $0["id"]?.text }124            for id in ids { let s = try nextSeq(); try db.run("INSERT OR REPLACE INTO tombstones (id, seq) VALUES (?, ?)", [.text(id), .int(s)]) }125            try db.run("DELETE FROM items"); try setCursor(0)126        }127    }128    /// Marks an item as locally changed so that the File Provider re-reads it (pin toggled, materialisation changed).129    public func touch(_ ids: [String]) throws {130        try db.transaction { for id in ids { let s = try nextSeq(); try db.run("UPDATE items SET seq = ? WHERE id = ?", [.int(s), .text(id)]) } }131    }132133    // MARK: Reading134    static func item(_ r: SQLRow) -> DriveItem {135        DriveItem(id: r["id"]?.text ?? "", kind: ItemKind(rawValue: r["kind"]?.text ?? "file") ?? .file, name: r["name"]?.text ?? "", parentID: r["parent_id"]?.text,136                  size: r["size"]?.int ?? 0, sha256: r["sha256"]?.text ?? "", versionID: r["version_id"]?.text, versionSeq: Int(r["version_seq"]?.int ?? 1), versionCount: Int(r["version_count"]?.int ?? 1),137                  contentAt: Date(timeIntervalSince1970: r["content_at"]?.double ?? 0), createdAt: Date(timeIntervalSince1970: r["created_at"]?.double ?? 0),138                  updatedAt: Date(timeIntervalSince1970: r["updated_at"]?.double ?? 0), starred: r["starred"]?.bool ?? false, trashedAt: r["trashed_at"]?.double.map(Date.init(timeIntervalSince1970:)),139                  trashRoot: r["trash_root"]?.bool ?? false, rev: r["rev"]?.int ?? 0, scan: r["scan"]?.text ?? "clean", hasThumbnail: r["thumb"]?.bool ?? false, previewKind: r["preview_kind"]?.text ?? "none")140    }141    private func items(_ sql: String, _ v: [SQLValue] = []) throws -> [DriveItem] { try db.query(sql, v).map(MetadataStore.item) }142    /// Every item of the mirror (trashed ones included), in a stable order, by pages.143    public func allItems(limit: Int, offset: Int) throws -> [DriveItem] { try items("SELECT * FROM items ORDER BY id LIMIT ? OFFSET ?", [.int(Int64(limit)), .int(Int64(offset))]) }144    public func item(_ id: String) throws -> DriveItem? { try items("SELECT * FROM items WHERE id = ?", [.text(id)]).first }145    public func root() throws -> DriveItem? { try rootID.flatMap { try item($0) } }146    /// Live children of a folder: folders first, then by name.147    public func children(of parent: String, limit: Int = 100_000, offset: Int = 0) throws -> [DriveItem] {148        try items("SELECT * FROM items WHERE parent_id = ? AND trashed_at IS NULL ORDER BY kind DESC, name_key, id LIMIT ? OFFSET ?", [.text(parent), .int(Int64(limit)), .int(Int64(offset))])149    }150    /// Every child, trashed ones included (what lies inside a trashed folder).151    public func allChildren(of parent: String) throws -> [DriveItem] { try items("SELECT * FROM items WHERE parent_id = ? ORDER BY kind DESC, name_key, id", [.text(parent)]) }152    public func child(of parent: String, named name: String) throws -> DriveItem? {153        try items("SELECT * FROM items WHERE parent_id = ? AND trashed_at IS NULL", [.text(parent)]).first { NameRules.key($0.name) == NameRules.key(name) }154    }155    public func trashRoots() throws -> [DriveItem] { try items("SELECT * FROM items WHERE trash_root = 1 ORDER BY trashed_at DESC") }156    public func recent(limit: Int = 100) throws -> [DriveItem] { try items("SELECT * FROM items WHERE kind = 'file' AND trashed_at IS NULL ORDER BY updated_at DESC LIMIT ?", [.int(Int64(limit))]) }157    public func starred() throws -> [DriveItem] { try items("SELECT * FROM items WHERE starred = 1 AND trashed_at IS NULL ORDER BY kind DESC, name_key") }158    public func largestFiles(limit: Int = 50) throws -> [DriveItem] { try items("SELECT * FROM items WHERE kind = 'file' AND trashed_at IS NULL ORDER BY size DESC LIMIT ?", [.int(Int64(limit))]) }159    /// Path from the root down to the item (the root excluded).160    public func path(to id: String) throws -> [DriveItem] {161        var out: [DriveItem] = [], cur = try item(id), guardN = 0162        while let c = cur, !c.isRoot, guardN < 256 { out.insert(c, at: 0); cur = try c.parentID.flatMap { try item($0) }; guardN += 1 }163        return out164    }165    /// Identifiers of a folder and of everything below it.166    public func subtreeIDs(of folder: String, includeTrashed: Bool = false) throws -> [String] {167        try db.query("""168            WITH RECURSIVE sub(id) AS (SELECT ? UNION ALL SELECT i.id FROM items i JOIN sub ON i.parent_id = sub.id \(includeTrashed ? "" : "WHERE i.trashed_at IS NULL"))169            SELECT id FROM sub170            """, [.text(folder)]).compactMap { $0["id"]?.text }171    }172    public func subtreeFiles(of folder: String) throws -> [DriveItem] {173        try items("""174            WITH RECURSIVE sub(id) AS (SELECT ? UNION ALL SELECT i.id FROM items i JOIN sub ON i.parent_id = sub.id WHERE i.trashed_at IS NULL AND i.kind = 'folder')175            SELECT i.* FROM items i JOIN sub ON i.parent_id = sub.id WHERE i.kind = 'file' AND i.trashed_at IS NULL176            """, [.text(folder)])177    }178    public struct Counts: Sendable, Equatable {179        public var files: Int; public var folders: Int; public var bytes: Int64; public var trashed: Int180        public init(files: Int, folders: Int, bytes: Int64, trashed: Int) { self.files = files; self.folders = folders; self.bytes = bytes; self.trashed = trashed }181    }182    public func counts() throws -> Counts {183        let r = try db.query("""184            SELECT (SELECT COUNT(*) FROM items WHERE kind='file' AND trashed_at IS NULL) AS files, (SELECT COUNT(*) FROM items WHERE kind='folder' AND trashed_at IS NULL AND parent_id IS NOT NULL) AS folders,185                   (SELECT COALESCE(SUM(size),0) FROM items WHERE kind='file' AND trashed_at IS NULL) AS bytes, (SELECT COUNT(*) FROM items WHERE trashed_at IS NOT NULL) AS trashed186            """).first ?? [:]187        return Counts(files: Int(r["files"]?.int ?? 0), folders: Int(r["folders"]?.int ?? 0), bytes: r["bytes"]?.int ?? 0, trashed: Int(r["trashed"]?.int ?? 0))188    }189190    // MARK: Changes since an anchor (File Provider working set)191    public struct LocalChanges: Sendable { public var items: [DriveItem]; public var deleted: [String]; public var seq: Int64; public var more: Bool }192    public func changes(since: Int64, limit: Int = 500) throws -> LocalChanges {193        try db.transaction {194            let rows = try db.query("SELECT * FROM items WHERE seq > ? ORDER BY seq LIMIT ?", [.int(since), .int(Int64(limit + 1))])195            let gone = try db.query("SELECT id, seq FROM tombstones WHERE seq > ? ORDER BY seq LIMIT ?", [.int(since), .int(Int64(limit + 1))])196            // merge both streams by seq and cut at `limit`197            var merged: [(seq: Int64, item: DriveItem?, deleted: String?)] = rows.map { ($0["seq"]?.int ?? 0, MetadataStore.item($0), nil) } + gone.map { ($0["seq"]?.int ?? 0, nil, $0["id"]?.text) }198            merged.sort { $0.seq < $1.seq }199            let more = merged.count > limit200            let page = Array(merged.prefix(limit))201            return LocalChanges(items: page.compactMap(\.item), deleted: page.compactMap(\.deleted), seq: page.last?.seq ?? since, more: more)202        }203    }204205    // MARK: Offline pins206    /// Pins or unpins an item itself ("keep available offline"). A pinned folder covers everything below it.207    public func setPinned(_ id: String, _ pinned: Bool) throws {208        try db.transaction {209            if pinned { try db.run("INSERT OR IGNORE INTO pins (id, pinned_at) VALUES (?, ?)", [.text(id), .double(Date().timeIntervalSince1970)]) }210            else { try db.run("DELETE FROM pins WHERE id = ?", [.text(id)]) }211            try touch(try subtreeIDs(of: id))212        }213    }214    public func isPinnedItself(_ id: String) throws -> Bool { try !db.query("SELECT 1 FROM pins WHERE id = ?", [.text(id)]).isEmpty }215    /// True when the item or one of its ancestors is pinned.216    public func isPinned(_ id: String) throws -> Bool {217        try !db.query("""218            WITH RECURSIVE up(id, parent_id, depth) AS (SELECT id, parent_id, 0 FROM items WHERE id = ? UNION ALL SELECT i.id, i.parent_id, up.depth + 1 FROM items i JOIN up ON i.id = up.parent_id WHERE up.depth < 256)219            SELECT 1 FROM up JOIN pins p ON p.id = up.id LIMIT 1220            """, [.text(id)]).isEmpty221    }222    public func pinnedItems() throws -> [DriveItem] { try items("SELECT i.* FROM items i JOIN pins p ON p.id = i.id WHERE i.trashed_at IS NULL ORDER BY i.kind DESC, i.name_key") }223    public func pinnedIDs() throws -> Set<String> { Set(try db.query("SELECT id FROM pins").compactMap { $0["id"]?.text }) }224225    // MARK: Local copies (reported by the File Provider)226    public func setMaterialized(_ id: String, _ on: Bool, bytes: Int64) throws {227        try db.run("INSERT INTO local_state (id, materialized, bytes, updated_at) VALUES (?, ?, ?, ?) ON CONFLICT(id) DO UPDATE SET materialized = excluded.materialized, bytes = excluded.bytes, updated_at = excluded.updated_at",228                   [.text(id), .int(on ? 1 : 0), .int(on ? bytes : 0), .double(Date().timeIntervalSince1970)])229    }230    /// Replaces the whole set of materialised items (the File Provider reports it in full).231    public func replaceMaterialized(_ ids: Set<String>) throws {232        try db.transaction {233            try db.run("UPDATE local_state SET materialized = 0, bytes = 0")234            for id in ids {235                let size = try db.query("SELECT size FROM items WHERE id = ? AND kind = 'file'", [.text(id)]).first?["size"]?.int ?? 0236                try setMaterialized(id, true, bytes: size)237            }238        }239    }240    public func materializedIDs() throws -> Set<String> { Set(try db.query("SELECT id FROM local_state WHERE materialized = 1").compactMap { $0["id"]?.text }) }241    public func isMaterialized(_ id: String) throws -> Bool { try !db.query("SELECT 1 FROM local_state WHERE id = ? AND materialized = 1", [.text(id)]).isEmpty }242    public struct LocalUsage: Sendable, Equatable {243        public var files: Int; public var bytes: Int64; public var pinnedBytes: Int64244        public init(files: Int, bytes: Int64, pinnedBytes: Int64) { self.files = files; self.bytes = bytes; self.pinnedBytes = pinnedBytes }245    }246    public func localUsage() throws -> LocalUsage {247        let rows = try db.query("SELECT l.id AS id, i.size AS size FROM local_state l JOIN items i ON i.id = l.id WHERE l.materialized = 1 AND i.kind = 'file'")248        var u = LocalUsage(files: 0, bytes: 0, pinnedBytes: 0)249        for r in rows { let s = r["size"]?.int ?? 0; u.files += 1; u.bytes += s; if let id = r["id"]?.text, try isPinned(id) { u.pinnedBytes += s } }250        return u251    }252    /// Materialised files that no pin covers: the copies "free up space" may evict.253    public func evictableIDs() throws -> [String] { try materializedIDs().filter { try !isPinned($0) }.sorted() }254}255
Files main
Menu

Theme