Repository Public License
1// ORIX Convert — Packages/ORIXConvertCore/Sources/ConvertCore/History.swift2// Description: Conversion history in SQLite (system sqlite3, WAL): one row per finished job and one row per workflow3// run. Stores paths, formats, options, sizes, timings, warnings and validation results — never file4// contents.5// Author: Simon-Pierre Boucher — contact@spboucher.ai6import Foundation7import SQLite389/// One finished job as recorded in the history.10public struct HistoryEntry: Sendable, Hashable, Codable, Identifiable {11 /// Row identifier (0 until the entry is stored).12 public var id: Int64 = 013 public var jobID: UUID14 public var sourcePath: String15 /// Further inputs of a multi-source job (merge, archive creation).16 public var additionalSourcePaths: [String] = []17 public var sourceFormat: FormatID?18 public var destinationPaths: [String] = []19 public var destinationFormat: FormatID20 public var operation: String?21 /// Engine identifier; `a+b` for a chained plan.22 public var engine: String = ""23 public var options: ConversionOptions = .init()24 public var bytesBefore: Int64?25 public var bytesAfter: Int64?26 /// Seconds between the start and the end of the job.27 public var duration: Double?28 /// Final state: `finished`, `failed`, `cancelled` or `skipped`.29 public var status: String30 public var warnings: [ConversionWarning] = []31 public var checks: [ValidationCheck] = []32 public var error: String?33 public var createdAt: Date34 public var startedAt: Date?35 public var finishedAt: Date36 public var batchID: UUID?37 public var workflowRunID: UUID?3839 public init(jobID: UUID, sourcePath: String, destinationFormat: FormatID, status: String, createdAt: Date = Date(), finishedAt: Date = Date()) {40 self.jobID = jobID; self.sourcePath = sourcePath; self.destinationFormat = destinationFormat; self.status = status41 self.createdAt = createdAt; self.finishedAt = finishedAt42 }43}4445/// Filter and paging for `ConversionHistoryStore.entries`.46public struct HistoryQuery: Sendable, Hashable {47 /// Case-insensitive substring searched in source paths, destination paths and error messages.48 public var search: String?49 public var statuses: Set<String> = []50 public var sourceFormat: FormatID?51 public var destinationFormat: FormatID?52 /// Inclusive lower bound on the finish date.53 public var from: Date?54 /// Exclusive upper bound on the finish date.55 public var to: Date?56 public var limit: Int = 10057 public var offset: Int = 058 public var newestFirst: Bool = true59 public init() {}60}6162/// Summary row of one workflow run.63public struct WorkflowRunRecord: Sendable, Hashable, Codable, Identifiable {64 public var id: UUID65 public var workflowID: UUID66 public var workflowName: String67 public var startedAt: Date68 public var finishedAt: Date69 public var itemCount: Int70 public var succeeded: Int71 public var failed: Int72 public var filtered: Int73 public var cancelled: Int74 /// Per-item outcome: source path, status, output paths, error.75 public var items: [Item]7677 public struct Item: Sendable, Hashable, Codable {78 public var sourcePath: String79 public var status: String80 public var outputPaths: [String]81 public var error: String?82 public init(sourcePath: String, status: String, outputPaths: [String], error: String?) {83 self.sourcePath = sourcePath; self.status = status; self.outputPaths = outputPaths; self.error = error84 }85 }8687 public init(id: UUID, workflowID: UUID, workflowName: String, startedAt: Date, finishedAt: Date, itemCount: Int, succeeded: Int,88 failed: Int, filtered: Int, cancelled: Int, items: [Item]) {89 self.id = id; self.workflowID = workflowID; self.workflowName = workflowName; self.startedAt = startedAt90 self.finishedAt = finishedAt; self.itemCount = itemCount; self.succeeded = succeeded; self.failed = failed91 self.filtered = filtered; self.cancelled = cancelled; self.items = items92 }93}9495public enum HistoryError: Error, Sendable, LocalizedError, Equatable {96 case database(String)97 public var errorDescription: String? {98 switch self { case .database(let m): "The conversion history could not be read or written. \(m)" }99 }100}101102/// The history database. All access goes through the actor; the connection is opened in WAL mode.103public actor ConversionHistoryStore {104 public nonisolated let url: URL105 private nonisolated(unsafe) let db: OpaquePointer106 private static let transient = unsafeBitCast(-1, to: sqlite3_destructor_type.self)107108 /// Opens (and creates if needed) the database at `url`; parent directories are created.109 public init(url: URL = AppPaths.historyDatabase) throws {110 self.url = url111 try? FileManager.default.createDirectory(at: url.deletingLastPathComponent(), withIntermediateDirectories: true)112 var handle: OpaquePointer?113 let flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX114 guard sqlite3_open_v2(url.path, &handle, flags, nil) == SQLITE_OK, let handle else {115 let message = handle.map { String(cString: sqlite3_errmsg($0)) } ?? "open failed"116 if let handle { sqlite3_close(handle) }117 throw HistoryError.database(message)118 }119 db = handle120 sqlite3_busy_timeout(handle, 5000)121 do {122 try Self.exec(handle, "PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL;")123 try Self.exec(handle, """124 CREATE TABLE IF NOT EXISTS history (125 id INTEGER PRIMARY KEY AUTOINCREMENT,126 job_id TEXT NOT NULL,127 source_path TEXT NOT NULL,128 additional_sources TEXT NOT NULL DEFAULT '[]',129 source_format TEXT,130 destination_paths TEXT NOT NULL DEFAULT '[]',131 destination_format TEXT NOT NULL,132 operation TEXT,133 engine TEXT NOT NULL DEFAULT '',134 options TEXT NOT NULL DEFAULT '{}',135 bytes_before INTEGER,136 bytes_after INTEGER,137 duration REAL,138 status TEXT NOT NULL,139 warnings TEXT NOT NULL DEFAULT '[]',140 checks TEXT NOT NULL DEFAULT '[]',141 error TEXT,142 created_at REAL NOT NULL,143 started_at REAL,144 finished_at REAL NOT NULL,145 batch_id TEXT,146 workflow_run_id TEXT147 );148 CREATE INDEX IF NOT EXISTS history_finished ON history(finished_at);149 CREATE INDEX IF NOT EXISTS history_status ON history(status);150 CREATE TABLE IF NOT EXISTS workflow_runs (151 id TEXT PRIMARY KEY,152 workflow_id TEXT NOT NULL,153 workflow_name TEXT NOT NULL,154 started_at REAL NOT NULL,155 finished_at REAL NOT NULL,156 item_count INTEGER NOT NULL,157 succeeded INTEGER NOT NULL,158 failed INTEGER NOT NULL,159 filtered INTEGER NOT NULL,160 cancelled INTEGER NOT NULL,161 items TEXT NOT NULL DEFAULT '[]'162 );163 CREATE INDEX IF NOT EXISTS workflow_runs_started ON workflow_runs(started_at);164 PRAGMA user_version=1;165 """)166 } catch {167 sqlite3_close(handle)168 throw error169 }170 }171172 deinit { sqlite3_close(db) }173174 // MARK: Jobs175176 /// Stores an entry and returns its row identifier.177 @discardableResult178 public func record(_ entry: HistoryEntry) throws -> Int64 {179 let sql = """180 INSERT INTO history (job_id, source_path, additional_sources, source_format, destination_paths, destination_format,181 operation, engine, options, bytes_before, bytes_after, duration, status, warnings, checks, error, created_at,182 started_at, finished_at, batch_id, workflow_run_id)183 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)184 """185 try run(sql, [186 .text(entry.jobID.uuidString), .text(entry.sourcePath), .text(Self.json(entry.additionalSourcePaths)),187 entry.sourceFormat.map { .text($0.rawValue) } ?? .null, .text(Self.json(entry.destinationPaths)),188 .text(entry.destinationFormat.rawValue), entry.operation.map { .text($0) } ?? .null, .text(entry.engine),189 .text(Self.json(entry.options.redactingSecrets())), entry.bytesBefore.map { .int($0) } ?? .null, entry.bytesAfter.map { .int($0) } ?? .null,190 entry.duration.map { .real($0) } ?? .null, .text(entry.status), .text(Self.json(entry.warnings)), .text(Self.json(entry.checks)),191 entry.error.map { .text($0) } ?? .null, .real(entry.createdAt.timeIntervalSince1970),192 entry.startedAt.map { .real($0.timeIntervalSince1970) } ?? .null, .real(entry.finishedAt.timeIntervalSince1970),193 entry.batchID.map { .text($0.uuidString) } ?? .null, entry.workflowRunID.map { .text($0.uuidString) } ?? .null,194 ])195 return sqlite3_last_insert_rowid(db)196 }197198 /// Entries matching `query`, paged.199 public func entries(_ query: HistoryQuery = HistoryQuery()) throws -> [HistoryEntry] {200 let (clause, values) = Self.whereClause(query)201 let sql = "SELECT id, job_id, source_path, additional_sources, source_format, destination_paths, destination_format, operation, engine, options, bytes_before, bytes_after, duration, status, warnings, checks, error, created_at, started_at, finished_at, batch_id, workflow_run_id FROM history\(clause) ORDER BY finished_at \(query.newestFirst ? "DESC" : "ASC"), id \(query.newestFirst ? "DESC" : "ASC") LIMIT ? OFFSET ?"202 var entries: [HistoryEntry] = []203 try run(sql, values + [.int(Int64(max(0, query.limit))), .int(Int64(max(0, query.offset)))]) { row in204 var entry = HistoryEntry(jobID: UUID(uuidString: row.text(1) ?? "") ?? UUID(), sourcePath: row.text(2) ?? "",205 destinationFormat: FormatID(row.text(6) ?? ""), status: row.text(13) ?? "",206 createdAt: Date(timeIntervalSince1970: row.double(17) ?? 0), finishedAt: Date(timeIntervalSince1970: row.double(19) ?? 0))207 entry.id = row.int(0) ?? 0208 entry.additionalSourcePaths = Self.decode(row.text(3)) ?? []209 entry.sourceFormat = row.text(4).map { FormatID($0) }210 entry.destinationPaths = Self.decode(row.text(5)) ?? []211 entry.operation = row.text(7)212 entry.engine = row.text(8) ?? ""213 entry.options = Self.decode(row.text(9)) ?? .init()214 entry.bytesBefore = row.int(10)215 entry.bytesAfter = row.int(11)216 entry.duration = row.double(12)217 entry.warnings = Self.decode(row.text(14)) ?? []218 entry.checks = Self.decode(row.text(15)) ?? []219 entry.error = row.text(16)220 entry.startedAt = row.double(18).map(Date.init(timeIntervalSince1970:))221 entry.batchID = row.text(20).flatMap(UUID.init(uuidString:))222 entry.workflowRunID = row.text(21).flatMap(UUID.init(uuidString:))223 entries.append(entry)224 }225 return entries226 }227228 /// Number of entries matching `query` (ignoring its paging).229 public func count(_ query: HistoryQuery = HistoryQuery()) throws -> Int {230 let (clause, values) = Self.whereClause(query)231 var total = 0232 try run("SELECT COUNT(*) FROM history\(clause)", values) { total = Int($0.int(0) ?? 0) }233 return total234 }235236 /// Deletes entries by row identifier. Returns the number deleted.237 @discardableResult238 public func delete(ids: [Int64]) throws -> Int {239 var deleted = 0240 for id in ids {241 try run("DELETE FROM history WHERE id = ?", [.int(id)])242 deleted += Int(sqlite3_changes(db))243 }244 return deleted245 }246247 /// Deletes every job entry and every workflow run.248 public func clearAll() throws {249 try Self.exec(db, "DELETE FROM history; DELETE FROM workflow_runs;")250 }251252 // MARK: Workflow runs253254 public func recordWorkflowRun(_ record: WorkflowRunRecord) throws {255 try run("INSERT OR REPLACE INTO workflow_runs (id, workflow_id, workflow_name, started_at, finished_at, item_count, succeeded, failed, filtered, cancelled, items) VALUES (?,?,?,?,?,?,?,?,?,?,?)", [256 .text(record.id.uuidString), .text(record.workflowID.uuidString), .text(record.workflowName),257 .real(record.startedAt.timeIntervalSince1970), .real(record.finishedAt.timeIntervalSince1970), .int(Int64(record.itemCount)),258 .int(Int64(record.succeeded)), .int(Int64(record.failed)), .int(Int64(record.filtered)), .int(Int64(record.cancelled)),259 .text(Self.json(record.items)),260 ])261 }262263 /// Workflow runs, newest first.264 public func workflowRuns(workflowID: UUID? = nil, limit: Int = 100, offset: Int = 0) throws -> [WorkflowRunRecord] {265 var records: [WorkflowRunRecord] = []266 let filter = workflowID == nil ? "" : " WHERE workflow_id = ?"267 let values: [Value] = (workflowID.map { [.text($0.uuidString)] } ?? []) + [.int(Int64(max(0, limit))), .int(Int64(max(0, offset)))]268 try run("SELECT id, workflow_id, workflow_name, started_at, finished_at, item_count, succeeded, failed, filtered, cancelled, items FROM workflow_runs\(filter) ORDER BY started_at DESC LIMIT ? OFFSET ?", values) { row in269 guard let id = row.text(0).flatMap(UUID.init(uuidString:)), let workflowID = row.text(1).flatMap(UUID.init(uuidString:)) else { return }270 records.append(WorkflowRunRecord(271 id: id, workflowID: workflowID, workflowName: row.text(2) ?? "", startedAt: Date(timeIntervalSince1970: row.double(3) ?? 0),272 finishedAt: Date(timeIntervalSince1970: row.double(4) ?? 0), itemCount: Int(row.int(5) ?? 0), succeeded: Int(row.int(6) ?? 0),273 failed: Int(row.int(7) ?? 0), filtered: Int(row.int(8) ?? 0), cancelled: Int(row.int(9) ?? 0), items: Self.decode(row.text(10)) ?? []))274 }275 return records276 }277278 @discardableResult279 public func deleteWorkflowRuns(ids: [UUID]) throws -> Int {280 var deleted = 0281 for id in ids {282 try run("DELETE FROM workflow_runs WHERE id = ?", [.text(id.uuidString)])283 deleted += Int(sqlite3_changes(db))284 }285 return deleted286 }287288 // MARK: SQLite plumbing289290 private enum Value { case text(String), int(Int64), real(Double), null }291292 private struct Row {293 let statement: OpaquePointer294 func isNull(_ i: Int32) -> Bool { sqlite3_column_type(statement, i) == SQLITE_NULL }295 func text(_ i: Int32) -> String? { isNull(i) ? nil : sqlite3_column_text(statement, i).map { String(cString: $0) } }296 func int(_ i: Int32) -> Int64? { isNull(i) ? nil : sqlite3_column_int64(statement, i) }297 func double(_ i: Int32) -> Double? { isNull(i) ? nil : sqlite3_column_double(statement, i) }298 }299300 private static func exec(_ db: OpaquePointer, _ sql: String) throws {301 var message: UnsafeMutablePointer<CChar>?302 guard sqlite3_exec(db, sql, nil, nil, &message) == SQLITE_OK else {303 let text = message.map { String(cString: $0) } ?? "unknown error"304 sqlite3_free(message)305 throw HistoryError.database(text)306 }307 }308309 private func run(_ sql: String, _ values: [Value], row: ((Row) -> Void)? = nil) throws {310 var statement: OpaquePointer?311 guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK, let statement else {312 throw HistoryError.database(String(cString: sqlite3_errmsg(db)))313 }314 defer { sqlite3_finalize(statement) }315 for (i, value) in values.enumerated() {316 let index = Int32(i + 1)317 switch value {318 case .text(let s): sqlite3_bind_text(statement, index, s, -1, Self.transient)319 case .int(let n): sqlite3_bind_int64(statement, index, n)320 case .real(let d): sqlite3_bind_double(statement, index, d)321 case .null: sqlite3_bind_null(statement, index)322 }323 }324 while true {325 let code = sqlite3_step(statement)326 if code == SQLITE_ROW { row?(Row(statement: statement)) } else if code == SQLITE_DONE { return } else {327 throw HistoryError.database(String(cString: sqlite3_errmsg(db)))328 }329 }330 }331332 private static func whereClause(_ query: HistoryQuery) -> (String, [Value]) {333 var parts: [String] = []334 var values: [Value] = []335 if let search = query.search?.trimmingCharacters(in: .whitespaces), !search.isEmpty {336 let escaped = search.replacingOccurrences(of: "\\", with: "\\\\").replacingOccurrences(of: "%", with: "\\%").replacingOccurrences(of: "_", with: "\\_")337 parts.append("(source_path LIKE ? ESCAPE '\\' OR destination_paths LIKE ? ESCAPE '\\' OR IFNULL(error,'') LIKE ? ESCAPE '\\')")338 values += Array(repeating: .text("%\(escaped)%"), count: 3)339 }340 if !query.statuses.isEmpty {341 parts.append("status IN (" + query.statuses.map { _ in "?" }.joined(separator: ",") + ")")342 values += query.statuses.sorted().map { .text($0) }343 }344 if let format = query.sourceFormat { parts.append("source_format = ?"); values.append(.text(format.rawValue)) }345 if let format = query.destinationFormat { parts.append("destination_format = ?"); values.append(.text(format.rawValue)) }346 if let from = query.from { parts.append("finished_at >= ?"); values.append(.real(from.timeIntervalSince1970)) }347 if let to = query.to { parts.append("finished_at < ?"); values.append(.real(to.timeIntervalSince1970)) }348 return (parts.isEmpty ? "" : " WHERE " + parts.joined(separator: " AND "), values)349 }350351 private static func json<T: Encodable>(_ value: T) -> String {352 let encoder = JSONEncoder()353 encoder.outputFormatting = [.sortedKeys, .withoutEscapingSlashes]354 return (try? encoder.encode(value)).map { String(decoding: $0, as: UTF8.self) } ?? "null"355 }356357 private static func decode<T: Decodable>(_ text: String?) -> T? {358 guard let text, let data = text.data(using: .utf8) else { return nil }359 return try? JSONDecoder().decode(T.self, from: data)360 }361}362