PromtGarageMac / Tests /PromptGarageCoreTests /DatabaseBootstrapServiceTests.swift

Initial public upload from Projekte/PromtGarageMac

4977db2 verified about 1 month ago

12.1 kB

import Foundation
import GRDB
import XCTest
@testable import PromptGarageCore

final class DatabaseBootstrapServiceTests: XCTestCase {
    func testInstallCopiesLegacyDatabaseWithoutChangingSourceOrBackfillingDates() throws {
        let root = try makeTemporaryDirectory(prefix: "PromptGarageBootstrap")
        let sourceURL = root.appendingPathComponent("legacy-prompts.db")
        try createLegacyDatabase(at: sourceURL)

        let sourceBefore = try Data(contentsOf: sourceURL)
        let service = try DatabaseBootstrapService(
            applicationSupportRoot: root.appendingPathComponent("ApplicationSupport")
        )

        let sourceReport = try service.validateSource(at: sourceURL)
        XCTAssertFalse(sourceReport.hasPromptedAt)
        XCTAssertEqual(sourceReport.promptCount, 1)

        let repository = try service.installDatabase(from: sourceURL)

        XCTAssertEqual(try Data(contentsOf: sourceURL), sourceBefore)
        XCTAssertFalse(try columnNames(at: sourceURL).contains("prompted_at"))
        XCTAssertTrue(try columnNames(at: service.databaseURL).contains("prompted_at"))
        XCTAssertNil(try repository.prompt(id: 1)?.promptedAt)
        XCTAssertEqual(try repository.legacyUndatedCount(), 1)
        XCTAssertTrue(try repository.dayGroups().isEmpty)

        let destinationReport = try DatabaseSchema.validate(
            DatabaseQueue(path: service.databaseURL.path)
        )
        XCTAssertTrue(destinationReport.hasPromptedAt)
        XCTAssertEqual(destinationReport.promptCount, destinationReport.ftsCount)
    }

    func testNewImportsAreDatedWithoutChangingLegacyTagsOrDates() throws {
        let root = try makeTemporaryDirectory(prefix: "PromptGarageChronology")
        let sourceURL = root.appendingPathComponent("legacy-prompts.db")
        try createLegacyDatabase(at: sourceURL)
        let service = try DatabaseBootstrapService(
            applicationSupportRoot: root.appendingPathComponent("ApplicationSupport")
        )
        let repository = try service.installDatabase(from: sourceURL)

        let promptedAt = "2026-07-15T22:30:00.000Z"
        let statistics = try repository.importCandidates([
            ImportCandidate(
                title: "A new Mac prompt",
                content: "Explain a safe native macOS migration with a real end to end verification step",
                source: "manual-import:0123456789ab",
                tags: "swift,mac",
                model: nil,
                promptedAt: promptedAt
            ),
        ])

        XCTAssertEqual(statistics.imported, 1)
        XCTAssertEqual(try repository.legacyUndatedCount(), 1)
        let expectedLocalDay = try localDay(for: promptedAt)
        XCTAssertEqual(
            try repository.dayGroups(),
            [CountedValue(name: expectedLocalDay, count: 1)]
        )
        XCTAssertEqual(
            try repository.page(matching: PromptQuery(promptedDay: expectedLocalDay)).total,
            1
        )
        XCTAssertEqual(try repository.prompt(id: 1)?.tags, "legacy,testing")
        XCTAssertNil(try repository.prompt(id: 1)?.promptedAt)

        let topics = try repository.topicGroups()
        XCTAssertEqual(topics.first(where: { $0.name == "legacy" })?.count, 1)
        XCTAssertEqual(topics.first(where: { $0.name == "swift" })?.count, 1)
    }

    func testStaleFTSIsRejectedReadOnlyButRepairedInWritableAppCopy() throws {
        let root = try makeTemporaryDirectory(prefix: "PromptGarageStaleFTS")
        let databaseURL = root.appendingPathComponent("prompts.db")
        try createLegacyDatabase(at: databaseURL)

        let corruptor = try DatabaseQueue(path: databaseURL.path)
        try corruptor.write { db in
            try db.execute(sql: """
                INSERT INTO prompts_fts(prompts_fts, rowid, title, content, tags)
                VALUES('delete', 1, 'Legacy prompt',
                       'This legacy prompt must retain its original metadata without inferred dates',
                       'legacy,testing')
                """)
        }
        try corruptor.close()

        var readOnlyConfiguration = Configuration()
        readOnlyConfiguration.readonly = true
        let readOnly = try DatabaseQueue(
            path: databaseURL.path,
            configuration: readOnlyConfiguration
        )
        defer { try? readOnly.close() }
        XCTAssertThrowsError(try DatabaseSchema.validateReadOnly(readOnly)) { error in
            guard let validationError = error as? DatabaseValidationError,
                  case .ftsIndexInvalid = validationError
            else {
                return XCTFail("Expected ftsIndexInvalid, got \(error)")
            }
        }
        XCTAssertThrowsError(
            try PromptRepository(databaseURL: databaseURL, readOnly: true)
        )

        let writable = try PromptRepository(databaseURL: databaseURL)
        XCTAssertEqual(try writable.page(matching: PromptQuery(text: "Legacy")).total, 1)
    }

    func testInstallRefusesValidExistingDestinationAndPreservesEveryByte() throws {
        let root = try makeTemporaryDirectory(prefix: "PromptGarageNoReplacement")
        let sourceURL = root.appendingPathComponent("source.db")
        try createLegacyDatabase(at: sourceURL)
        let service = try DatabaseBootstrapService(
            applicationSupportRoot: root.appendingPathComponent("ApplicationSupport")
        )
        try createLegacyDatabase(at: service.databaseURL)
        let destinationBefore = try Data(contentsOf: service.databaseURL)

        XCTAssertThrowsError(try service.installDatabase(from: sourceURL)) { error in
            XCTAssertEqual(
                error as? DatabaseBootstrapError,
                .destinationAlreadyExists(service.databaseURL)
            )
            XCTAssertTrue(error.localizedDescription.contains("niemals automatisch"))
            XCTAssertTrue(error.localizedDescription.contains("verschiebe oder benenne"))
        }

        XCTAssertEqual(try Data(contentsOf: service.databaseURL), destinationBefore)
        XCTAssertFalse(try columnNames(at: service.databaseURL).contains("prompted_at"))
    }

    func testDestinationAppearingAfterLaunchCheckRefusesBeforeSourceAccessAndPreservesBytes() throws {
        let root = try makeTemporaryDirectory(prefix: "PromptGarageInvalidNoReplacement")
        let service = try DatabaseBootstrapService(
            applicationSupportRoot: root.appendingPathComponent("ApplicationSupport")
        )
        XCTAssertEqual(service.inspect(), .needsDatabase)

        // Simulate a destination appearing after AppModel's first-launch state
        // check but before its detached install operation begins. The selected
        // source is intentionally missing: destinationAlreadyExists must win,
        // proving installDatabase did not inspect or open the source first.
        let invalidBytes = Data("not a sqlite database; preserve me exactly".utf8)
        try invalidBytes.write(to: service.databaseURL)
        let missingSource = root.appendingPathComponent("missing-source.db")

        XCTAssertThrowsError(try service.installDatabase(from: missingSource)) { error in
            XCTAssertEqual(
                error as? DatabaseBootstrapError,
                .destinationAlreadyExists(service.databaseURL)
            )
        }

        XCTAssertEqual(try Data(contentsOf: service.databaseURL), invalidBytes)
    }

    func testFourWayParityGateRejectsAnyCountMismatch() throws {
        let source = DatabaseValidationReport(
            integrity: "ok",
            promptCount: 7,
            ftsCount: 7
        )
        let destination = DatabaseValidationReport(
            integrity: "ok",
            promptCount: 7,
            ftsCount: 6,
            hasPromptedAt: true
        )

        XCTAssertThrowsError(try DatabaseBootstrapService.validateCopyParity(
            source: source,
            destination: destination
        )) { error in
            XCTAssertEqual(
                error as? DatabaseBootstrapError,
                .copyParityMismatch(
                    sourcePrompts: 7,
                    sourceFTS: 7,
                    destinationPrompts: 7,
                    destinationFTS: 6
                )
            )
        }
    }

    private func makeTemporaryDirectory(prefix: String) throws -> URL {
        let directory = FileManager.default.temporaryDirectory
            .appendingPathComponent("\(prefix)-\(UUID().uuidString)", isDirectory: true)
        try FileManager.default.createDirectory(at: directory, withIntermediateDirectories: true)
        addTeardownBlock { try? FileManager.default.removeItem(at: directory) }
        return directory
    }

    private func createLegacyDatabase(at url: URL) throws {
        let queue = try DatabaseQueue(path: url.path)
        try queue.write { db in
            try db.execute(sql: """
                CREATE TABLE 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'))
                );
                CREATE VIRTUAL TABLE prompts_fts USING fts5(
                    title, content, tags, content=prompts, content_rowid=id
                );
                CREATE TRIGGER 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 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 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;
                INSERT INTO prompts (
                    id, title, content, source, tags, created_at, updated_at
                ) VALUES (
                    1,
                    'Legacy prompt',
                    'This legacy prompt must retain its original metadata without inferred dates',
                    'manual',
                    'legacy,testing',
                    '2024-01-02 03:04:05',
                    '2024-01-02 03:04:05'
                );
                """)
        }
        try queue.close()
    }

    private func columnNames(at url: URL) throws -> Set<String> {
        var configuration = Configuration()
        configuration.readonly = true
        let queue = try DatabaseQueue(path: url.path, configuration: configuration)
        defer { try? queue.close() }
        return try queue.read { db in
            let rows = try Row.fetchAll(db, sql: "PRAGMA table_info(prompts)")
            return Set(rows.compactMap { row -> String? in row["name"] })
        }
    }

    private func localDay(for timestamp: String) throws -> String {
        let parser = ISO8601DateFormatter()
        parser.formatOptions = [.withInternetDateTime, .withFractionalSeconds]
        let date = try XCTUnwrap(parser.date(from: timestamp))
        let formatter = DateFormatter()
        formatter.calendar = Calendar(identifier: .gregorian)
        formatter.locale = Locale(identifier: "en_US_POSIX")
        formatter.timeZone = .current
        formatter.dateFormat = "yyyy-MM-dd"
        return formatter.string(from: date)
    }
}

	import Foundation
	import GRDB
	import XCTest
	@testable import PromptGarageCore

	final class DatabaseBootstrapServiceTests: XCTestCase {
	func testInstallCopiesLegacyDatabaseWithoutChangingSourceOrBackfillingDates() throws {
	let root = try makeTemporaryDirectory(prefix: "PromptGarageBootstrap")
	let sourceURL = root.appendingPathComponent("legacy-prompts.db")
	try createLegacyDatabase(at: sourceURL)

	let sourceBefore = try Data(contentsOf: sourceURL)
	let service = try DatabaseBootstrapService(
	applicationSupportRoot: root.appendingPathComponent("ApplicationSupport")
	)

	let sourceReport = try service.validateSource(at: sourceURL)
	XCTAssertFalse(sourceReport.hasPromptedAt)
	XCTAssertEqual(sourceReport.promptCount, 1)

	let repository = try service.installDatabase(from: sourceURL)

	XCTAssertEqual(try Data(contentsOf: sourceURL), sourceBefore)
	XCTAssertFalse(try columnNames(at: sourceURL).contains("prompted_at"))
	XCTAssertTrue(try columnNames(at: service.databaseURL).contains("prompted_at"))
	XCTAssertNil(try repository.prompt(id: 1)?.promptedAt)
	XCTAssertEqual(try repository.legacyUndatedCount(), 1)
	XCTAssertTrue(try repository.dayGroups().isEmpty)

	let destinationReport = try DatabaseSchema.validate(
	DatabaseQueue(path: service.databaseURL.path)
	)
	XCTAssertTrue(destinationReport.hasPromptedAt)
	XCTAssertEqual(destinationReport.promptCount, destinationReport.ftsCount)
	}

	func testNewImportsAreDatedWithoutChangingLegacyTagsOrDates() throws {
	let root = try makeTemporaryDirectory(prefix: "PromptGarageChronology")
	let sourceURL = root.appendingPathComponent("legacy-prompts.db")
	try createLegacyDatabase(at: sourceURL)
	let service = try DatabaseBootstrapService(
	applicationSupportRoot: root.appendingPathComponent("ApplicationSupport")
	)
	let repository = try service.installDatabase(from: sourceURL)

	let promptedAt = "2026-07-15T22:30:00.000Z"
	let statistics = try repository.importCandidates([
	ImportCandidate(
	title: "A new Mac prompt",
	content: "Explain a safe native macOS migration with a real end to end verification step",
	source: "manual-import:0123456789ab",
	tags: "swift,mac",
	model: nil,
	promptedAt: promptedAt
	),
	])

	XCTAssertEqual(statistics.imported, 1)
	XCTAssertEqual(try repository.legacyUndatedCount(), 1)
	let expectedLocalDay = try localDay(for: promptedAt)
	XCTAssertEqual(
	try repository.dayGroups(),
	[CountedValue(name: expectedLocalDay, count: 1)]
	)
	XCTAssertEqual(
	try repository.page(matching: PromptQuery(promptedDay: expectedLocalDay)).total,
	1
	)
	XCTAssertEqual(try repository.prompt(id: 1)?.tags, "legacy,testing")
	XCTAssertNil(try repository.prompt(id: 1)?.promptedAt)

	let topics = try repository.topicGroups()
	XCTAssertEqual(topics.first(where: { $0.name == "legacy" })?.count, 1)
	XCTAssertEqual(topics.first(where: { $0.name == "swift" })?.count, 1)
	}

	func testStaleFTSIsRejectedReadOnlyButRepairedInWritableAppCopy() throws {
	let root = try makeTemporaryDirectory(prefix: "PromptGarageStaleFTS")
	let databaseURL = root.appendingPathComponent("prompts.db")
	try createLegacyDatabase(at: databaseURL)

	let corruptor = try DatabaseQueue(path: databaseURL.path)
	try corruptor.write { db in
	try db.execute(sql: """
	INSERT INTO prompts_fts(prompts_fts, rowid, title, content, tags)
	VALUES('delete', 1, 'Legacy prompt',
	'This legacy prompt must retain its original metadata without inferred dates',
	'legacy,testing')
	""")
	}
	try corruptor.close()

	var readOnlyConfiguration = Configuration()
	readOnlyConfiguration.readonly = true
	let readOnly = try DatabaseQueue(
	path: databaseURL.path,
	configuration: readOnlyConfiguration
	)
	defer { try? readOnly.close() }
	XCTAssertThrowsError(try DatabaseSchema.validateReadOnly(readOnly)) { error in
	guard let validationError = error as? DatabaseValidationError,
	case .ftsIndexInvalid = validationError
	else {
	return XCTFail("Expected ftsIndexInvalid, got \(error)")
	}
	}
	XCTAssertThrowsError(
	try PromptRepository(databaseURL: databaseURL, readOnly: true)
	)

	let writable = try PromptRepository(databaseURL: databaseURL)
	XCTAssertEqual(try writable.page(matching: PromptQuery(text: "Legacy")).total, 1)
	}

	func testInstallRefusesValidExistingDestinationAndPreservesEveryByte() throws {
	let root = try makeTemporaryDirectory(prefix: "PromptGarageNoReplacement")
	let sourceURL = root.appendingPathComponent("source.db")
	try createLegacyDatabase(at: sourceURL)
	let service = try DatabaseBootstrapService(
	applicationSupportRoot: root.appendingPathComponent("ApplicationSupport")
	)
	try createLegacyDatabase(at: service.databaseURL)
	let destinationBefore = try Data(contentsOf: service.databaseURL)

	XCTAssertThrowsError(try service.installDatabase(from: sourceURL)) { error in
	XCTAssertEqual(
	error as? DatabaseBootstrapError,
	.destinationAlreadyExists(service.databaseURL)
	)
	XCTAssertTrue(error.localizedDescription.contains("niemals automatisch"))
	XCTAssertTrue(error.localizedDescription.contains("verschiebe oder benenne"))
	}

	XCTAssertEqual(try Data(contentsOf: service.databaseURL), destinationBefore)
	XCTAssertFalse(try columnNames(at: service.databaseURL).contains("prompted_at"))
	}

	func testDestinationAppearingAfterLaunchCheckRefusesBeforeSourceAccessAndPreservesBytes() throws {
	let root = try makeTemporaryDirectory(prefix: "PromptGarageInvalidNoReplacement")
	let service = try DatabaseBootstrapService(
	applicationSupportRoot: root.appendingPathComponent("ApplicationSupport")
	)
	XCTAssertEqual(service.inspect(), .needsDatabase)

	// Simulate a destination appearing after AppModel's first-launch state
	// check but before its detached install operation begins. The selected
	// source is intentionally missing: destinationAlreadyExists must win,
	// proving installDatabase did not inspect or open the source first.
	let invalidBytes = Data("not a sqlite database; preserve me exactly".utf8)
	try invalidBytes.write(to: service.databaseURL)
	let missingSource = root.appendingPathComponent("missing-source.db")

	XCTAssertThrowsError(try service.installDatabase(from: missingSource)) { error in
	XCTAssertEqual(
	error as? DatabaseBootstrapError,
	.destinationAlreadyExists(service.databaseURL)
	)
	}

	XCTAssertEqual(try Data(contentsOf: service.databaseURL), invalidBytes)
	}

	func testFourWayParityGateRejectsAnyCountMismatch() throws {
	let source = DatabaseValidationReport(
	integrity: "ok",
	promptCount: 7,
	ftsCount: 7
	)
	let destination = DatabaseValidationReport(
	integrity: "ok",
	promptCount: 7,
	ftsCount: 6,
	hasPromptedAt: true
	)

	XCTAssertThrowsError(try DatabaseBootstrapService.validateCopyParity(
	source: source,
	destination: destination
	)) { error in
	XCTAssertEqual(
	error as? DatabaseBootstrapError,
	.copyParityMismatch(
	sourcePrompts: 7,
	sourceFTS: 7,
	destinationPrompts: 7,
	destinationFTS: 6
	)
	)
	}
	}

	private func makeTemporaryDirectory(prefix: String) throws -> URL {
	let directory = FileManager.default.temporaryDirectory
	.appendingPathComponent("\(prefix)-\(UUID().uuidString)", isDirectory: true)
	try FileManager.default.createDirectory(at: directory, withIntermediateDirectories: true)
	addTeardownBlock { try? FileManager.default.removeItem(at: directory) }
	return directory
	}

	private func createLegacyDatabase(at url: URL) throws {
	let queue = try DatabaseQueue(path: url.path)
	try queue.write { db in
	try db.execute(sql: """
	CREATE TABLE 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'))
	);
	CREATE VIRTUAL TABLE prompts_fts USING fts5(
	title, content, tags, content=prompts, content_rowid=id
	);
	CREATE TRIGGER 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 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 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;
	INSERT INTO prompts (
	id, title, content, source, tags, created_at, updated_at
	) VALUES (
	1,
	'Legacy prompt',
	'This legacy prompt must retain its original metadata without inferred dates',
	'manual',
	'legacy,testing',
	'2024-01-02 03:04:05',
	'2024-01-02 03:04:05'
	);
	""")
	}
	try queue.close()
	}

	private func columnNames(at url: URL) throws -> Set<String> {
	var configuration = Configuration()
	configuration.readonly = true
	let queue = try DatabaseQueue(path: url.path, configuration: configuration)
	defer { try? queue.close() }
	return try queue.read { db in
	let rows = try Row.fetchAll(db, sql: "PRAGMA table_info(prompts)")
	return Set(rows.compactMap { row -> String? in row["name"] })
	}
	}

	private func localDay(for timestamp: String) throws -> String {
	let parser = ISO8601DateFormatter()
	parser.formatOptions = [.withInternetDateTime, .withFractionalSeconds]
	let date = try XCTUnwrap(parser.date(from: timestamp))
	let formatter = DateFormatter()
	formatter.calendar = Calendar(identifier: .gregorian)
	formatter.locale = Locale(identifier: "en_US_POSIX")
	formatter.timeZone = .current
	formatter.dateFormat = "yyyy-MM-dd"
	return formatter.string(from: date)
	}
	}