SPB Git

Repository Public License

History.swift 18.4 KB 362 lines swift

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

Theme