Initial public upload from Projekte/PromtGarageMac

4977db2 verified about 1 month ago

9.25 kB

import Foundation
import GRDB

public enum DatabaseSchema {
    /// Columns that are required in both the legacy Python database and the
    /// native application database. `prompted_at` is intentionally not part of
    /// this set: source databases predate that column and must remain valid.
    public static let requiredLegacyColumns: Set<String> = [
        "id", "title", "content", "source", "is_starred", "tags", "model",
        "rating", "use_count", "created_at", "updated_at",
    ]

    public static let promptedAtColumn = "prompted_at"

    public static let createSQL = """
        CREATE TABLE IF NOT EXISTS prompts (
            id INTEGER PRIMARY KEY,
            title TEXT NOT NULL,
            content TEXT NOT NULL,
            source TEXT,
            is_starred INTEGER DEFAULT 0,
            tags TEXT,
            model TEXT,
            rating INTEGER,
            use_count INTEGER DEFAULT 0,
            created_at TEXT DEFAULT (datetime('now')),
            updated_at TEXT DEFAULT (datetime('now')),
            prompted_at TEXT
        );
        CREATE VIRTUAL TABLE IF NOT EXISTS prompts_fts USING fts5(
            title, content, tags, content=prompts, content_rowid=id
        );
        CREATE TRIGGER IF NOT EXISTS prompts_ai AFTER INSERT ON prompts BEGIN
            INSERT INTO prompts_fts(rowid, title, content, tags)
            VALUES (new.id, new.title, new.content, new.tags);
        END;
        CREATE TRIGGER IF NOT EXISTS prompts_ad AFTER DELETE ON prompts BEGIN
            INSERT INTO prompts_fts(prompts_fts, rowid, title, content, tags)
            VALUES('delete', old.id, old.title, old.content, old.tags);
        END;
        CREATE TRIGGER IF NOT EXISTS prompts_au AFTER UPDATE ON prompts BEGIN
            INSERT INTO prompts_fts(prompts_fts, rowid, title, content, tags)
            VALUES('delete', old.id, old.title, old.content, old.tags);
            INSERT INTO prompts_fts(rowid, title, content, tags)
            VALUES (new.id, new.title, new.content, new.tags);
        END;
        """

    public static func createIfNeeded(in db: Database) throws {
        try db.execute(sql: createSQL)
        try migrateDestinationIfNeeded(in: db)
        try repairSearchIndexIfNeeded(in: db)
    }

    /// Applies native-only, forward migrations. This must only be called for
    /// the application-owned copy, never while validating a selected source.
    /// Existing rows remain NULL by SQLite definition; there is deliberately
    /// no backfill from `created_at` or `updated_at`.
    public static func migrateDestinationIfNeeded(in db: Database) throws {
        let columns = try promptColumns(in: db)
        guard !columns.contains(promptedAtColumn) else { return }
        try db.execute(sql: "ALTER TABLE prompts ADD COLUMN prompted_at TEXT")
    }

    /// Verifies the actual FTS5 index against the external content table and
    /// rebuilds it only in the writable app copy when needed. `COUNT(*)` on an
    /// external-content FTS table mirrors `prompts` and cannot detect a stale
    /// or empty index by itself.
    public static func repairSearchIndexIfNeeded(in db: Database) throws {
        do {
            try verifySearchIndex(in: db)
        } catch {
            try db.execute(
                sql: "INSERT INTO prompts_fts(prompts_fts) VALUES('rebuild')"
            )
            try verifySearchIndex(in: db)
        }
    }

    /// Performs full validation on an application-owned writable database (or
    /// a writable safety copy). FTS5's external-content integrity command is
    /// syntactically an INSERT, even though it only checks the index, so this
    /// method must not be called directly on a read-only source connection.
    public static func validate(_ queue: DatabaseQueue) throws -> DatabaseValidationReport {
        let report = try validateStructureAndCounts(queue)
        do {
            try queue.writeWithoutTransaction { db in
                try verifySearchIndex(in: db)
            }
        } catch {
            throw DatabaseValidationError.ftsIndexInvalid(error.localizedDescription)
        }
        return report
    }

    /// Fully validates a read-only database without executing any write
    /// statement against it. The source is copied with SQLite's online backup
    /// API and the FTS integrity command runs only against the in-memory copy.
    public static func validateReadOnly(
        _ source: DatabaseQueue
    ) throws -> DatabaseValidationReport {
        let snapshot = try DatabaseQueue()
        defer { try? snapshot.close() }
        try source.backup(to: snapshot)
        return try validate(snapshot)
    }

    /// Structural/count validation is intentionally separate from the full
    /// check. It is used while holding a read-only source open during an atomic
    /// backup; the resulting writable copy is fully validated before install.
    static func validateStructureAndCounts(
        _ reader: any DatabaseReader
    ) throws -> DatabaseValidationReport {
        try reader.read { db in
            let integrity = try String.fetchOne(db, sql: "PRAGMA integrity_check") ?? "unknown"
            guard integrity.lowercased() == "ok" else {
                throw DatabaseValidationError.integrityFailed(integrity)
            }

            let tableNames = Set(try String.fetchAll(
                db,
                sql: "SELECT name FROM sqlite_master WHERE type IN ('table', 'view')"
            ))
            guard tableNames.contains("prompts"), tableNames.contains("prompts_fts") else {
                throw DatabaseValidationError.missingTables
            }

            let columns = try promptColumns(in: db)
            let missingColumns = requiredLegacyColumns.subtracting(columns)
            guard missingColumns.isEmpty else {
                throw DatabaseValidationError.missingColumns(missingColumns.sorted())
            }

            let triggerNames = Set(try String.fetchAll(
                db,
                sql: "SELECT name FROM sqlite_master WHERE type = 'trigger'"
            ))
            let requiredTriggers: Set<String> = ["prompts_ai", "prompts_ad", "prompts_au"]
            let missingTriggers = requiredTriggers.subtracting(triggerNames)
            guard missingTriggers.isEmpty else {
                throw DatabaseValidationError.missingTriggers(missingTriggers.sorted())
            }

            let promptCount = try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM prompts") ?? 0
            let ftsCount = try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM prompts_fts") ?? 0
            guard promptCount == ftsCount else {
                throw DatabaseValidationError.ftsMismatch(prompts: promptCount, fts: ftsCount)
            }

            return DatabaseValidationReport(
                integrity: integrity,
                promptCount: promptCount,
                ftsCount: ftsCount,
                hasPromptedAt: columns.contains(promptedAtColumn)
            )
        }
    }

    private static func promptColumns(in db: Database) throws -> Set<String> {
        let rows = try Row.fetchAll(db, sql: "PRAGMA table_info(prompts)")
        return Set(rows.compactMap { row -> String? in row["name"] })
    }

    private static func verifySearchIndex(in db: Database) throws {
        // rank=1 asks FTS5 to compare the index with the external content
        // table, not merely to validate its internal b-tree structure.
        try db.execute(
            sql: """
                INSERT INTO prompts_fts(prompts_fts, rank)
                VALUES('integrity-check', 1)
                """
        )
    }
}

public struct DatabaseValidationReport: Equatable, Sendable {
    public let integrity: String
    public let promptCount: Int
    public let ftsCount: Int
    public let hasPromptedAt: Bool

    public init(
        integrity: String,
        promptCount: Int,
        ftsCount: Int,
        hasPromptedAt: Bool = false
    ) {
        self.integrity = integrity
        self.promptCount = promptCount
        self.ftsCount = ftsCount
        self.hasPromptedAt = hasPromptedAt
    }
}

public enum DatabaseValidationError: LocalizedError, Equatable, Sendable {
    case integrityFailed(String)
    case missingTables
    case missingColumns([String])
    case missingTriggers([String])
    case ftsMismatch(prompts: Int, fts: Int)
    case ftsIndexInvalid(String)

    public var errorDescription: String? {
        switch self {
        case let .integrityFailed(message):
            "Die SQLite-Integritätsprüfung ist fehlgeschlagen: \(message)"
        case .missingTables:
            "Die Datenbank enthält nicht die Tabellen prompts und prompts_fts."
        case let .missingColumns(columns):
            "Der prompts-Tabelle fehlen Spalten: \(columns.joined(separator: ", "))."
        case let .missingTriggers(triggers):
            "Der Datenbank fehlen FTS-Trigger: \(triggers.joined(separator: ", "))."
        case let .ftsMismatch(prompts, fts):
            "Prompt- und FTS-Zeilen stimmen nicht überein (\(prompts) vs. \(fts))."
        case let .ftsIndexInvalid(message):
            "Der FTS-Suchindex ist inkonsistent oder nicht prüfbar: \(message)"
        }
    }
}

	import Foundation
	import GRDB

	public enum DatabaseSchema {
	/// Columns that are required in both the legacy Python database and the
	/// native application database. `prompted_at` is intentionally not part of
	/// this set: source databases predate that column and must remain valid.
	public static let requiredLegacyColumns: Set<String> = [
	"id", "title", "content", "source", "is_starred", "tags", "model",
	"rating", "use_count", "created_at", "updated_at",
	]

	public static let promptedAtColumn = "prompted_at"

	public static let createSQL = """
	CREATE TABLE IF NOT EXISTS prompts (
	id INTEGER PRIMARY KEY,
	title TEXT NOT NULL,
	content TEXT NOT NULL,
	source TEXT,
	is_starred INTEGER DEFAULT 0,
	tags TEXT,
	model TEXT,
	rating INTEGER,
	use_count INTEGER DEFAULT 0,
	created_at TEXT DEFAULT (datetime('now')),
	updated_at TEXT DEFAULT (datetime('now')),
	prompted_at TEXT
	);

	CREATE VIRTUAL TABLE IF NOT EXISTS prompts_fts USING fts5(
	title, content, tags, content=prompts, content_rowid=id
	);

	CREATE TRIGGER IF NOT EXISTS prompts_ai AFTER INSERT ON prompts BEGIN
	INSERT INTO prompts_fts(rowid, title, content, tags)
	VALUES (new.id, new.title, new.content, new.tags);
	END;

	CREATE TRIGGER IF NOT EXISTS prompts_ad AFTER DELETE ON prompts BEGIN
	INSERT INTO prompts_fts(prompts_fts, rowid, title, content, tags)
	VALUES('delete', old.id, old.title, old.content, old.tags);
	END;

	CREATE TRIGGER IF NOT EXISTS prompts_au AFTER UPDATE ON prompts BEGIN
	INSERT INTO prompts_fts(prompts_fts, rowid, title, content, tags)
	VALUES('delete', old.id, old.title, old.content, old.tags);
	INSERT INTO prompts_fts(rowid, title, content, tags)
	VALUES (new.id, new.title, new.content, new.tags);
	END;
	"""

	public static func createIfNeeded(in db: Database) throws {
	try db.execute(sql: createSQL)
	try migrateDestinationIfNeeded(in: db)
	try repairSearchIndexIfNeeded(in: db)
	}

	/// Applies native-only, forward migrations. This must only be called for
	/// the application-owned copy, never while validating a selected source.
	/// Existing rows remain NULL by SQLite definition; there is deliberately
	/// no backfill from `created_at` or `updated_at`.
	public static func migrateDestinationIfNeeded(in db: Database) throws {
	let columns = try promptColumns(in: db)
	guard !columns.contains(promptedAtColumn) else { return }
	try db.execute(sql: "ALTER TABLE prompts ADD COLUMN prompted_at TEXT")
	}

	/// Verifies the actual FTS5 index against the external content table and
	/// rebuilds it only in the writable app copy when needed. `COUNT(*)` on an
	/// external-content FTS table mirrors `prompts` and cannot detect a stale
	/// or empty index by itself.
	public static func repairSearchIndexIfNeeded(in db: Database) throws {
	do {
	try verifySearchIndex(in: db)
	} catch {
	try db.execute(
	sql: "INSERT INTO prompts_fts(prompts_fts) VALUES('rebuild')"
	)
	try verifySearchIndex(in: db)
	}
	}

	/// Performs full validation on an application-owned writable database (or
	/// a writable safety copy). FTS5's external-content integrity command is
	/// syntactically an INSERT, even though it only checks the index, so this
	/// method must not be called directly on a read-only source connection.
	public static func validate(_ queue: DatabaseQueue) throws -> DatabaseValidationReport {
	let report = try validateStructureAndCounts(queue)
	do {
	try queue.writeWithoutTransaction { db in
	try verifySearchIndex(in: db)
	}
	} catch {
	throw DatabaseValidationError.ftsIndexInvalid(error.localizedDescription)
	}
	return report
	}

	/// Fully validates a read-only database without executing any write
	/// statement against it. The source is copied with SQLite's online backup
	/// API and the FTS integrity command runs only against the in-memory copy.
	public static func validateReadOnly(
	_ source: DatabaseQueue
	) throws -> DatabaseValidationReport {
	let snapshot = try DatabaseQueue()
	defer { try? snapshot.close() }
	try source.backup(to: snapshot)
	return try validate(snapshot)
	}

	/// Structural/count validation is intentionally separate from the full
	/// check. It is used while holding a read-only source open during an atomic
	/// backup; the resulting writable copy is fully validated before install.
	static func validateStructureAndCounts(
	_ reader: any DatabaseReader
	) throws -> DatabaseValidationReport {
	try reader.read { db in
	let integrity = try String.fetchOne(db, sql: "PRAGMA integrity_check") ?? "unknown"
	guard integrity.lowercased() == "ok" else {
	throw DatabaseValidationError.integrityFailed(integrity)
	}

	let tableNames = Set(try String.fetchAll(
	db,
	sql: "SELECT name FROM sqlite_master WHERE type IN ('table', 'view')"
	))
	guard tableNames.contains("prompts"), tableNames.contains("prompts_fts") else {
	throw DatabaseValidationError.missingTables
	}

	let columns = try promptColumns(in: db)
	let missingColumns = requiredLegacyColumns.subtracting(columns)
	guard missingColumns.isEmpty else {
	throw DatabaseValidationError.missingColumns(missingColumns.sorted())
	}

	let triggerNames = Set(try String.fetchAll(
	db,
	sql: "SELECT name FROM sqlite_master WHERE type = 'trigger'"
	))
	let requiredTriggers: Set<String> = ["prompts_ai", "prompts_ad", "prompts_au"]
	let missingTriggers = requiredTriggers.subtracting(triggerNames)
	guard missingTriggers.isEmpty else {
	throw DatabaseValidationError.missingTriggers(missingTriggers.sorted())
	}

	let promptCount = try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM prompts") ?? 0
	let ftsCount = try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM prompts_fts") ?? 0
	guard promptCount == ftsCount else {
	throw DatabaseValidationError.ftsMismatch(prompts: promptCount, fts: ftsCount)
	}

	return DatabaseValidationReport(
	integrity: integrity,
	promptCount: promptCount,
	ftsCount: ftsCount,
	hasPromptedAt: columns.contains(promptedAtColumn)
	)
	}
	}

	private static func promptColumns(in db: Database) throws -> Set<String> {
	let rows = try Row.fetchAll(db, sql: "PRAGMA table_info(prompts)")
	return Set(rows.compactMap { row -> String? in row["name"] })
	}

	private static func verifySearchIndex(in db: Database) throws {
	// rank=1 asks FTS5 to compare the index with the external content
	// table, not merely to validate its internal b-tree structure.
	try db.execute(
	sql: """
	INSERT INTO prompts_fts(prompts_fts, rank)
	VALUES('integrity-check', 1)
	"""
	)
	}
	}

	public struct DatabaseValidationReport: Equatable, Sendable {
	public let integrity: String
	public let promptCount: Int
	public let ftsCount: Int
	public let hasPromptedAt: Bool

	public init(
	integrity: String,
	promptCount: Int,
	ftsCount: Int,
	hasPromptedAt: Bool = false
	) {
	self.integrity = integrity
	self.promptCount = promptCount
	self.ftsCount = ftsCount
	self.hasPromptedAt = hasPromptedAt
	}
	}

	public enum DatabaseValidationError: LocalizedError, Equatable, Sendable {
	case integrityFailed(String)
	case missingTables
	case missingColumns([String])
	case missingTriggers([String])
	case ftsMismatch(prompts: Int, fts: Int)
	case ftsIndexInvalid(String)

	public var errorDescription: String? {
	switch self {
	case let .integrityFailed(message):
	"Die SQLite-Integritätsprüfung ist fehlgeschlagen: \(message)"
	case .missingTables:
	"Die Datenbank enthält nicht die Tabellen prompts und prompts_fts."
	case let .missingColumns(columns):
	"Der prompts-Tabelle fehlen Spalten: \(columns.joined(separator: ", "))."
	case let .missingTriggers(triggers):
	"Der Datenbank fehlen FTS-Trigger: \(triggers.joined(separator: ", "))."
	case let .ftsMismatch(prompts, fts):
	"Prompt- und FTS-Zeilen stimmen nicht überein (\(prompts) vs. \(fts))."
	case let .ftsIndexInvalid(message):
	"Der FTS-Suchindex ist inkonsistent oder nicht prüfbar: \(message)"
	}
	}
	}