Repository Public
1// SP Drive iOS2// File: Core/Persistence/DriveStore.swift3// Description: The device's persistent state: mirror of the Drive's metadata, sync cursor, journal of local operations, state of local copies,4// transfers and conflicts. One store per process (the app has one, the File Provider extension has its own).5//6// Author: Simon-Pierre Boucher7// Contact: contact@spboucher.ai8import Foundation910/// A change made on this device that the server has not confirmed yet. Operations are replayed in order.11public struct PendingOp: Sendable, Identifiable, Equatable {12 public enum Kind: String, Sendable { case createFolder, rename, move, trash, restore, star, purge, uploadNew, uploadVersion }13 public enum State: String, Sendable { case pending, running, failed }14 public var id: Int6415 public var kind: Kind16 public var itemID: String17 /// Destination folder (move), parent folder (createFolder, uploadNew).18 public var targetID: String?19 public var name: String?20 public var flag: Bool21 public var state: State22 public var attempts: Int23 public var errorCode: String?24 public var createdAt: Date25}2627public struct TransferRecord: Sendable, Identifiable, Equatable {28 public enum Kind: String, Sendable { case upload, download }29 public enum State: String, Sendable { case queued, running, paused, failed, done, cancelled }30 public var id: String31 public var kind: Kind32 public var itemID: String33 public var opID: Int64?34 public var name: String35 public var size: Int6436 public var done: Int6437 public var state: State38 /// Higher runs first (a file the user is waiting for beats a background pin).39 public var priority: Int40 public var uploadID: String?41 /// Path of the source file (upload) relative to the store's file root, or of the partial file (download).42 public var localPath: String?43 public var sha256: String?44 public var errorCode: String?45 public var attempts: Int46 public var createdAt: Date47 public var updatedAt: Date48 public var fraction: Double { size > 0 ? min(1, Double(done) / Double(size)) : (state == .done ? 1 : 0) }49 public var isActive: Bool { state == .queued || state == .running }50}5152public struct ConflictRecord: Sendable, Identifiable, Equatable {53 public enum Reason: String, Sendable {54 /// The file was changed here and on the server.55 case bothModified56 /// The file was changed here and deleted (or trashed) on the server.57 case remoteDeleted58 }59 public var id: Int6460 public var itemID: String61 public var name: String62 public var reason: Reason63 public var uploadID: String?64 public var detectedAt: Date65}6667public final class DriveStore: Sendable {68 public let db: SQLiteDatabase6970 public init(path: String?) throws {71 db = try SQLiteDatabase(path: path)72 try migrate()73 }7475 private func migrate() throws {76 let v = try db.query("PRAGMA user_version").first?["user_version"].int ?? 077 if v < 1 {78 try db.transaction {79 try db.exec("""80 CREATE TABLE items (81 id TEXT PRIMARY KEY, is_folder INTEGER NOT NULL, parent_id TEXT, name TEXT NOT NULL, name_key TEXT NOT NULL, size INTEGER NOT NULL DEFAULT 0,82 sha256 TEXT NOT NULL DEFAULT '', version_id TEXT, version_seq INTEGER NOT NULL DEFAULT 1, version_count INTEGER NOT NULL DEFAULT 1, rev INTEGER NOT NULL DEFAULT 0,83 created_at REAL NOT NULL, updated_at REAL NOT NULL, trashed_at REAL, trash_root INTEGER NOT NULL DEFAULT 0, starred INTEGER NOT NULL DEFAULT 0,84 scan TEXT NOT NULL DEFAULT 'clean', has_thumb INTEGER NOT NULL DEFAULT 0, pending INTEGER NOT NULL DEFAULT 0, ext TEXT NOT NULL DEFAULT '');85 CREATE INDEX items_parent ON items (parent_id, trashed_at);86 CREATE INDEX items_name ON items (name_key);87 CREATE INDEX items_updated ON items (updated_at DESC);88 CREATE INDEX items_rev ON items (rev);89 CREATE TABLE meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);90 CREATE TABLE local_state (91 item_id TEXT PRIMARY KEY, pinned INTEGER NOT NULL DEFAULT 0, location TEXT, file_name TEXT, base_sha TEXT, base_version_id TEXT, local_size INTEGER NOT NULL DEFAULT 0,92 dirty INTEGER NOT NULL DEFAULT 0, downloaded_at REAL, opened_at REAL);93 CREATE TABLE ops (94 id INTEGER PRIMARY KEY AUTOINCREMENT, kind TEXT NOT NULL, item_id TEXT NOT NULL, target_id TEXT, name TEXT, flag INTEGER NOT NULL DEFAULT 0,95 state TEXT NOT NULL DEFAULT 'pending', attempts INTEGER NOT NULL DEFAULT 0, error_code TEXT, created_at REAL NOT NULL);96 CREATE TABLE transfers (97 id TEXT PRIMARY KEY, kind TEXT NOT NULL, item_id TEXT NOT NULL, op_id INTEGER, name TEXT NOT NULL, size INTEGER NOT NULL DEFAULT 0, done INTEGER NOT NULL DEFAULT 0,98 state TEXT NOT NULL, priority INTEGER NOT NULL DEFAULT 0, upload_id TEXT, local_path TEXT, sha256 TEXT, error_code TEXT, attempts INTEGER NOT NULL DEFAULT 0,99 created_at REAL NOT NULL, updated_at REAL NOT NULL);100 CREATE INDEX transfers_state ON transfers (state, priority DESC, created_at);101 CREATE TABLE conflicts (102 id INTEGER PRIMARY KEY AUTOINCREMENT, item_id TEXT NOT NULL, name TEXT NOT NULL, reason TEXT NOT NULL, upload_id TEXT, detected_at REAL NOT NULL);103 CREATE TABLE recent_searches (text TEXT PRIMARY KEY, at REAL NOT NULL);104 CREATE TABLE tombstones (id TEXT PRIMARY KEY, rev INTEGER NOT NULL);105 CREATE INDEX tombstones_rev ON tombstones (rev);106 PRAGMA user_version = 1;107 """)108 }109 }110 }111112 // MARK: Meta113114 public func meta(_ key: String) -> String? { (try? db.query("SELECT value FROM meta WHERE key = ?", key))?.first?["value"].string }115 public func setMeta(_ key: String, _ value: String?) {116 if let value { _ = try? db.run("INSERT INTO meta (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value", key, value) }117 else { _ = try? db.run("DELETE FROM meta WHERE key = ?", key) }118 }119 public var cursor: Int64 { get { meta("cursor").flatMap(Int64.init) ?? 0 } }120 public func setCursor(_ c: Int64) { setMeta("cursor", String(c)) }121 public var rootID: String? { meta("root") }122123 /// Forgets everything about the account (sign-out). Files on disk are removed by the caller.124 public func wipe() throws {125 try db.transaction {126 for t in ["items", "meta", "local_state", "ops", "transfers", "conflicts", "recent_searches", "tombstones"] { try db.run("DELETE FROM \(t)") }127 }128 }129130 // MARK: Items131132 static func nameKey(_ name: String) -> String { name.precomposedStringWithCanonicalMapping.lowercased() }133 /// Accent-insensitive key for local search.134 static func searchKey(_ s: String) -> String { s.folding(options: [.diacriticInsensitive, .caseInsensitive], locale: nil) }135136 static func item(_ r: SQLRow) -> DriveItem {137 DriveItem(id: r["id"].string ?? "", isFolder: r["is_folder"].bool, parentID: r["parent_id"].string, name: r["name"].string ?? "", size: r["size"].int ?? 0, sha256: r["sha256"].string ?? "",138 versionID: r["version_id"].string, versionSeq: Int(r["version_seq"].int ?? 1), versionCount: Int(r["version_count"].int ?? 1), rev: r["rev"].int ?? 0,139 createdAt: r["created_at"].date ?? .distantPast, updatedAt: r["updated_at"].date ?? .distantPast, trashedAt: r["trashed_at"].date, trashRoot: r["trash_root"].bool,140 starred: r["starred"].bool, scan: r["scan"].string ?? "clean", hasThumbnail: r["has_thumb"].bool, pending: r["pending"].bool)141 }142143 public func upsert(_ i: DriveItem) throws {144 try db.run("""145 INSERT INTO items (id, is_folder, parent_id, name, name_key, size, sha256, version_id, version_seq, version_count, rev, created_at, updated_at, trashed_at, trash_root, starred, scan, has_thumb, pending, ext)146 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)147 ON CONFLICT(id) DO UPDATE SET is_folder = excluded.is_folder, parent_id = excluded.parent_id, name = excluded.name, name_key = excluded.name_key, size = excluded.size, sha256 = excluded.sha256,148 version_id = excluded.version_id, version_seq = excluded.version_seq, version_count = excluded.version_count, rev = excluded.rev, created_at = excluded.created_at, updated_at = excluded.updated_at,149 trashed_at = excluded.trashed_at, trash_root = excluded.trash_root, starred = excluded.starred, scan = excluded.scan, has_thumb = excluded.has_thumb, pending = excluded.pending, ext = excluded.ext150 """, binds: [i.id, i.isFolder, i.parentID, i.name, Self.searchKey(i.name), i.size, i.sha256, i.versionID, i.versionSeq, i.versionCount, i.rev, i.createdAt, i.updatedAt, i.trashedAt, i.trashRoot,151 i.starred, i.scan, i.hasThumbnail, i.pending, i.fileExtension])152 }153154 /// Items changed after a revision, oldest first (File Provider change enumeration).155 public func items(changedAfter rev: Int64, limit: Int = 100_000) -> [DriveItem] {156 ((try? db.query("SELECT * FROM items WHERE rev > ? ORDER BY rev LIMIT ?", rev, limit)) ?? []).map(Self.item)157 }158 public func allItems(limit: Int, offset: Int) -> [DriveItem] {159 ((try? db.query("SELECT * FROM items WHERE parent_id IS NOT NULL ORDER BY id LIMIT ? OFFSET ?", limit, offset)) ?? []).map(Self.item)160 }161 /// Remembers that an item was purged, at the cursor where this device learnt it.162 public func addTombstone(_ id: String, rev: Int64) { _ = try? db.run("INSERT INTO tombstones (id, rev) VALUES (?, ?) ON CONFLICT(id) DO UPDATE SET rev = excluded.rev", id, rev) }163 public func tombstones(after rev: Int64) -> [String] { ((try? db.query("SELECT id FROM tombstones WHERE rev > ? ORDER BY rev", rev)) ?? []).compactMap { $0["id"].string } }164165 public func item(_ id: String) -> DriveItem? { (try? db.query("SELECT * FROM items WHERE id = ?", id))?.first.map(Self.item) }166 public func deleteItem(_ id: String) throws { try db.run("DELETE FROM items WHERE id = ?", id) }167168 public enum Sort: String, Sendable, CaseIterable {169 case name, updated, size, kind170 var sql: String {171 switch self {172 case .name: "name_key"; case .updated: "updated_at"; case .size: "size"; case .kind: "ext"173 }174 }175 public var label: String {176 switch self { case .name: "Nom"; case .updated: "Date de modification"; case .size: "Taille"; case .kind: "Type" }177 }178 }179180 /// Live children of a folder: folders first, then the requested order. Paged.181 public func children(of parentID: String, sort: Sort = .name, ascending: Bool = true, limit: Int = 100_000, offset: Int = 0) -> [DriveItem] {182 let dir = ascending ? "ASC" : "DESC"183 return ((try? db.query("SELECT * FROM items WHERE parent_id = ? AND trashed_at IS NULL ORDER BY is_folder DESC, \(sort.sql) \(dir), name_key ASC LIMIT ? OFFSET ?", parentID, limit, offset)) ?? []).map(Self.item)184 }185 public func childCount(of parentID: String) -> Int { Int((try? db.query("SELECT count(*) AS n FROM items WHERE parent_id = ? AND trashed_at IS NULL", parentID))?.first?["n"].int ?? 0) }186187 /// Root, …, parent, item (items whose chain is broken stop early).188 public func path(to id: String) -> [DriveItem] {189 var out: [DriveItem] = []190 var cur = item(id)191 var guardCount = 0192 while let c = cur, guardCount < 256 { out.insert(c, at: 0); cur = c.parentID.flatMap(item); guardCount += 1 }193 return out194 }195196 /// Every live descendant of a folder (the folder itself excluded).197 public func descendants(of folderID: String, filesOnly: Bool = false) -> [DriveItem] {198 let rows = (try? db.query("""199 WITH RECURSIVE sub(id) AS (SELECT id FROM items WHERE parent_id = ? AND trashed_at IS NULL200 UNION ALL SELECT i.id FROM items i JOIN sub ON i.parent_id = sub.id WHERE i.trashed_at IS NULL)201 SELECT items.* FROM items JOIN sub ON sub.id = items.id \(filesOnly ? "WHERE items.is_folder = 0" : "")202 """, folderID)) ?? []203 return rows.map(Self.item)204 }205206 public func recent(limit: Int = 100) -> [DriveItem] {207 ((try? db.query("SELECT * FROM items WHERE is_folder = 0 AND trashed_at IS NULL ORDER BY updated_at DESC LIMIT ?", limit)) ?? []).map(Self.item)208 }209 public func starred() -> [DriveItem] {210 ((try? db.query("SELECT * FROM items WHERE starred = 1 AND trashed_at IS NULL ORDER BY is_folder DESC, name_key")) ?? []).map(Self.item)211 }212 public func trashRoots() -> [DriveItem] {213 ((try? db.query("SELECT * FROM items WHERE trashed_at IS NOT NULL AND trash_root = 1 ORDER BY trashed_at DESC")) ?? []).map(Self.item)214 }215 public func largestFiles(limit: Int = 50) -> [DriveItem] {216 ((try? db.query("SELECT * FROM items WHERE is_folder = 0 AND trashed_at IS NULL ORDER BY size DESC LIMIT ?", limit)) ?? []).map(Self.item)217 }218 public func itemCount() -> (files: Int, folders: Int) {219 let r = (try? db.query("SELECT sum(is_folder = 0) AS f, sum(is_folder = 1) AS d FROM items WHERE trashed_at IS NULL AND parent_id IS NOT NULL"))?.first220 return (Int(r?["f"].int ?? 0), Int(r?["d"].int ?? 0))221 }222223 /// Search in the local index (works offline): name, family, extension, dates, size, folder, favourites.224 public func search(text: String, kinds: Set<FileKind> = [], ext: String? = nil, starredOnly: Bool = false, within folderID: String? = nil, from: Date? = nil, to: Date? = nil,225 minSize: Int64? = nil, maxSize: Int64? = nil, offlineOnly: Bool = false, limit: Int = 300) -> [DriveItem] {226 var sql = "SELECT items.* FROM items"227 var binds: [SQLBindable] = []228 if let folderID {229 sql = "WITH RECURSIVE sub(id) AS (SELECT ? UNION ALL SELECT i.id FROM items i JOIN sub ON i.parent_id = sub.id WHERE i.is_folder = 1) " + sql + " JOIN sub ON sub.id = items.parent_id"230 binds.append(folderID)231 }232 if offlineOnly { sql += " JOIN local_state ls ON ls.item_id = items.id AND ls.file_name IS NOT NULL" }233 sql += " WHERE items.trashed_at IS NULL AND items.parent_id IS NOT NULL"234 for word in Self.searchKey(text).split(separator: " ") where !word.isEmpty {235 sql += " AND items.name_key LIKE ? ESCAPE '\\'"236 binds.append("%" + word.replacingOccurrences(of: "\\", with: "\\\\").replacingOccurrences(of: "%", with: "\\%").replacingOccurrences(of: "_", with: "\\_") + "%")237 }238 if let ext, !ext.isEmpty { sql += " AND items.ext = ?"; binds.append(ext.lowercased().trimmingCharacters(in: CharacterSet(charactersIn: "."))) }239 if starredOnly { sql += " AND items.starred = 1" }240 if let from { sql += " AND items.updated_at >= ?"; binds.append(from) }241 if let to { sql += " AND items.updated_at <= ?"; binds.append(to) }242 if let minSize { sql += " AND items.is_folder = 0 AND items.size >= ?"; binds.append(minSize) }243 if let maxSize { sql += " AND items.is_folder = 0 AND items.size <= ?"; binds.append(maxSize) }244 sql += " ORDER BY items.is_folder DESC, items.updated_at DESC LIMIT ?"245 binds.append(kinds.isEmpty ? limit : limit * 20)246 let rows = ((try? db.query(sql, binds: binds)) ?? []).map(Self.item)247 return kinds.isEmpty ? rows : Array(rows.filter { kinds.contains($0.kind) }.prefix(limit))248 }249250 /// A free name in a folder: "rapport.pdf" → "rapport (2).pdf".251 public func freeName(_ name: String, in parentID: String, excluding id: String? = nil) -> String {252 let taken = Set(children(of: parentID).filter { $0.id != id }.map { Self.nameKey($0.name) })253 if !taken.contains(Self.nameKey(name)) { return name }254 let ext = (name as NSString).pathExtension255 let stem = (name as NSString).deletingPathExtension256 for i in 2..<10_000 {257 let candidate = ext.isEmpty ? "\(stem) (\(i))" : "\(stem) (\(i)).\(ext)"258 if !taken.contains(Self.nameKey(candidate)) { return candidate }259 }260 return "\(stem) \(UUID().uuidString.prefix(8))" + (ext.isEmpty ? "" : ".\(ext)")261 }262263 // MARK: Local state264265 static func local(_ r: SQLRow) -> LocalState {266 LocalState(itemID: r["item_id"].string ?? "", pinned: r["pinned"].bool, location: r["location"].string.flatMap(LocalState.Location.init), fileName: r["file_name"].string,267 baseSHA: r["base_sha"].string, baseVersionID: r["base_version_id"].string, localSize: r["local_size"].int ?? 0, dirty: r["dirty"].bool,268 downloadedAt: r["downloaded_at"].date, openedAt: r["opened_at"].date)269 }270 public func localState(_ id: String) -> LocalState? { (try? db.query("SELECT * FROM local_state WHERE item_id = ?", id))?.first.map(Self.local) }271 public func saveLocalState(_ s: LocalState) throws {272 try db.run("""273 INSERT INTO local_state (item_id, pinned, location, file_name, base_sha, base_version_id, local_size, dirty, downloaded_at, opened_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)274 ON CONFLICT(item_id) DO UPDATE SET pinned = excluded.pinned, location = excluded.location, file_name = excluded.file_name, base_sha = excluded.base_sha, base_version_id = excluded.base_version_id,275 local_size = excluded.local_size, dirty = excluded.dirty, downloaded_at = excluded.downloaded_at, opened_at = excluded.opened_at276 """, binds: [s.itemID, s.pinned, s.location?.rawValue, s.fileName, s.baseSHA, s.baseVersionID, s.localSize, s.dirty, s.downloadedAt, s.openedAt])277 }278 public func deleteLocalState(_ id: String) throws { try db.run("DELETE FROM local_state WHERE item_id = ?", id) }279 public func allLocalStates() -> [LocalState] { ((try? db.query("SELECT * FROM local_state")) ?? []).map(Self.local) }280 public func pinnedIDs() -> Set<String> { Set(((try? db.query("SELECT item_id FROM local_state WHERE pinned = 1")) ?? []).compactMap { $0["item_id"].string }) }281 /// Items with a copy on this device, most recently used first.282 public func localItems(pinnedOnly: Bool) -> [(DriveItem, LocalState)] {283 let rows = (try? db.query("SELECT items.*, ls.item_id, ls.pinned, ls.location, ls.file_name, ls.base_sha, ls.base_version_id, ls.local_size, ls.dirty, ls.downloaded_at, ls.opened_at FROM local_state ls JOIN items ON items.id = ls.item_id WHERE \(pinnedOnly ? "ls.pinned = 1" : "ls.file_name IS NOT NULL") ORDER BY items.is_folder DESC, items.name_key")) ?? []284 return rows.map { (Self.item($0), Self.local($0)) }285 }286 public func localUsage() -> (offlineBytes: Int64, offlineFiles: Int, cacheBytes: Int64, cacheFiles: Int) {287 let rows = (try? db.query("SELECT location, sum(local_size) AS b, count(*) AS n FROM local_state WHERE file_name IS NOT NULL GROUP BY location")) ?? []288 var out: (Int64, Int, Int64, Int) = (0, 0, 0, 0)289 for r in rows { if r["location"].string == "offline" { out.0 = r["b"].int ?? 0; out.1 = Int(r["n"].int ?? 0) } else { out.2 = r["b"].int ?? 0; out.3 = Int(r["n"].int ?? 0) } }290 return out291 }292293 // MARK: Operations journal294295 static func op(_ r: SQLRow) -> PendingOp {296 PendingOp(id: r["id"].int ?? 0, kind: PendingOp.Kind(rawValue: r["kind"].string ?? "") ?? .rename, itemID: r["item_id"].string ?? "", targetID: r["target_id"].string, name: r["name"].string,297 flag: r["flag"].bool, state: PendingOp.State(rawValue: r["state"].string ?? "") ?? .pending, attempts: Int(r["attempts"].int ?? 0), errorCode: r["error_code"].string, createdAt: r["created_at"].date ?? .now)298 }299 @discardableResult300 public func appendOp(_ kind: PendingOp.Kind, itemID: String, targetID: String? = nil, name: String? = nil, flag: Bool = false) throws -> Int64 {301 try db.transaction {302 try db.run("INSERT INTO ops (kind, item_id, target_id, name, flag, created_at) VALUES (?, ?, ?, ?, ?, ?)", kind.rawValue, itemID, targetID, name, flag, Date.now)303 return db.lastInsertID304 }305 }306 /// Every operation not yet confirmed, oldest first (failed ones included: they wait for the user's decision).307 public func ops() -> [PendingOp] { ((try? db.query("SELECT * FROM ops ORDER BY id")) ?? []).map(Self.op) }308 public func op(_ id: Int64) -> PendingOp? { (try? db.query("SELECT * FROM ops WHERE id = ?", id))?.first.map(Self.op) }309 public func setOpState(_ id: Int64, _ state: PendingOp.State, errorCode: String? = nil, bumpAttempts: Bool = false) {310 _ = try? db.run("UPDATE ops SET state = ?, error_code = ?, attempts = attempts + ? WHERE id = ?", state.rawValue, errorCode, bumpAttempts ? 1 : 0, id)311 }312 public func deleteOp(_ id: Int64) { _ = try? db.run("DELETE FROM ops WHERE id = ?", id) }313 public func hasPendingOps(for itemID: String) -> Bool {314 ((try? db.query("SELECT 1 FROM ops WHERE (item_id = ? OR target_id = ?) AND state != 'failed' LIMIT 1", itemID, itemID)) ?? []).isEmpty == false315 }316317 /// The server confirmed an item created offline: every reference to its temporary identifier now names the real one.318 public func remap(localID: String, to remoteID: String) throws {319 try db.transaction {320 try db.run("DELETE FROM items WHERE id = ?", remoteID) // the change feed may have delivered it first321 try db.run("UPDATE items SET id = ? WHERE id = ?", remoteID, localID)322 try db.run("UPDATE items SET parent_id = ? WHERE parent_id = ?", remoteID, localID)323 try db.run("DELETE FROM local_state WHERE item_id = ?", remoteID)324 try db.run("UPDATE local_state SET item_id = ? WHERE item_id = ?", remoteID, localID)325 try db.run("UPDATE ops SET item_id = ? WHERE item_id = ?", remoteID, localID)326 try db.run("UPDATE ops SET target_id = ? WHERE target_id = ?", remoteID, localID)327 try db.run("UPDATE transfers SET item_id = ? WHERE item_id = ?", remoteID, localID)328 try db.run("UPDATE conflicts SET item_id = ? WHERE item_id = ?", remoteID, localID)329 }330 }331332 // MARK: Transfers333334 static func transfer(_ r: SQLRow) -> TransferRecord {335 TransferRecord(id: r["id"].string ?? "", kind: TransferRecord.Kind(rawValue: r["kind"].string ?? "") ?? .download, itemID: r["item_id"].string ?? "", opID: r["op_id"].int, name: r["name"].string ?? "",336 size: r["size"].int ?? 0, done: r["done"].int ?? 0, state: TransferRecord.State(rawValue: r["state"].string ?? "") ?? .failed, priority: Int(r["priority"].int ?? 0), uploadID: r["upload_id"].string,337 localPath: r["local_path"].string, sha256: r["sha256"].string, errorCode: r["error_code"].string, attempts: Int(r["attempts"].int ?? 0),338 createdAt: r["created_at"].date ?? .now, updatedAt: r["updated_at"].date ?? .now)339 }340 public func saveTransfer(_ t: TransferRecord) throws {341 try db.run("""342 INSERT INTO transfers (id, kind, item_id, op_id, name, size, done, state, priority, upload_id, local_path, sha256, error_code, attempts, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)343 ON CONFLICT(id) DO UPDATE SET item_id = excluded.item_id, op_id = excluded.op_id, name = excluded.name, size = excluded.size, done = excluded.done, state = excluded.state, priority = excluded.priority,344 upload_id = excluded.upload_id, local_path = excluded.local_path, sha256 = excluded.sha256, error_code = excluded.error_code, attempts = excluded.attempts, updated_at = excluded.updated_at345 """, binds: [t.id, t.kind.rawValue, t.itemID, t.opID, t.name, t.size, t.done, t.state.rawValue, t.priority, t.uploadID, t.localPath, t.sha256, t.errorCode, t.attempts, t.createdAt, Date.now])346 }347 public func transfer(_ id: String) -> TransferRecord? { (try? db.query("SELECT * FROM transfers WHERE id = ?", id))?.first.map(Self.transfer) }348 public func transfers(states: [TransferRecord.State]? = nil) -> [TransferRecord] {349 let all = ((try? db.query("SELECT * FROM transfers ORDER BY CASE state WHEN 'running' THEN 0 WHEN 'queued' THEN 1 WHEN 'paused' THEN 2 WHEN 'failed' THEN 3 ELSE 4 END, priority DESC, created_at")) ?? []).map(Self.transfer)350 return states.map { s in all.filter { s.contains($0.state) } } ?? all351 }352 public func activeTransfer(itemID: String, kind: TransferRecord.Kind) -> TransferRecord? {353 ((try? db.query("SELECT * FROM transfers WHERE item_id = ? AND kind = ? AND state IN ('queued', 'running', 'paused', 'failed') ORDER BY created_at DESC LIMIT 1", itemID, kind.rawValue)) ?? []).first.map(Self.transfer)354 }355 public func deleteTransfer(_ id: String) { _ = try? db.run("DELETE FROM transfers WHERE id = ?", id) }356 public func clearFinishedTransfers() { _ = try? db.run("DELETE FROM transfers WHERE state IN ('done', 'cancelled')") }357358 // MARK: Conflicts359360 static func conflict(_ r: SQLRow) -> ConflictRecord {361 ConflictRecord(id: r["id"].int ?? 0, itemID: r["item_id"].string ?? "", name: r["name"].string ?? "", reason: ConflictRecord.Reason(rawValue: r["reason"].string ?? "") ?? .bothModified,362 uploadID: r["upload_id"].string, detectedAt: r["detected_at"].date ?? .now)363 }364 public func conflicts() -> [ConflictRecord] { ((try? db.query("SELECT * FROM conflicts ORDER BY detected_at DESC")) ?? []).map(Self.conflict) }365 public func conflict(itemID: String) -> ConflictRecord? { (try? db.query("SELECT * FROM conflicts WHERE item_id = ? LIMIT 1", itemID))?.first.map(Self.conflict) }366 public func addConflict(itemID: String, name: String, reason: ConflictRecord.Reason, uploadID: String?) throws {367 try db.transaction {368 try db.run("DELETE FROM conflicts WHERE item_id = ?", itemID)369 try db.run("INSERT INTO conflicts (item_id, name, reason, upload_id, detected_at) VALUES (?, ?, ?, ?, ?)", itemID, name, reason.rawValue, uploadID, Date.now)370 }371 }372 public func deleteConflict(_ id: Int64) { _ = try? db.run("DELETE FROM conflicts WHERE id = ?", id) }373374 // MARK: Recent searches375376 public func recentSearches() -> [String] { ((try? db.query("SELECT text FROM recent_searches ORDER BY at DESC LIMIT 8")) ?? []).compactMap { $0["text"].string } }377 public func rememberSearch(_ text: String) {378 let t = text.trimmingCharacters(in: .whitespaces)379 guard t.count >= 2 else { return }380 _ = try? db.run("INSERT INTO recent_searches (text, at) VALUES (?, ?) ON CONFLICT(text) DO UPDATE SET at = excluded.at", t, Date.now)381 }382}383