Repository Public
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