PromtGarageMac / Tests /PromptGarageCoreTests /RealDatabaseMigrationTests.swift

Initial public upload from Projekte/PromtGarageMac

4977db2 verified about 1 month ago

9.78 kB

import CryptoKit
import Foundation
import GRDB
import XCTest
@testable import PromptGarageCore

/// Opt-in integration coverage for Samuel's real legacy database.
///
/// Run on the Mac with:
/// `PROMPTGARAGE_SOURCE_DB=/absolute/path/to/prompts.db swift test --filter RealDatabaseMigrationTests`
///
/// The source is hashed before and after, and all SQLite connections opened by
/// the production bootstrap service are read-only. Only a disposable copy below
/// XCTest's temporary directory is migrated.
final class RealDatabaseMigrationTests: XCTestCase {
    func testRealSourceInstallsToTemporaryDestinationWithoutSourceMutationOrBackfill() throws {
        let environment = ProcessInfo.processInfo.environment
        guard let sourcePath = environment["PROMPTGARAGE_SOURCE_DB"]?
            .trimmingCharacters(in: .whitespacesAndNewlines),
            !sourcePath.isEmpty
        else {
            throw XCTSkip("Set PROMPTGARAGE_SOURCE_DB to opt in to the real legacy DB migration test.")
        }

        let sourceURL = URL(fileURLWithPath: sourcePath).standardizedFileURL
        guard FileManager.default.fileExists(atPath: sourceURL.path) else {
            XCTFail("PROMPTGARAGE_SOURCE_DB does not exist: \(sourceURL.path)")
            return
        }

        let sourceFamilyBefore = try databaseFamilySnapshot(at: sourceURL)
        let sourceQueue = try readOnlyQueue(
            at: sourceURL,
            label: "PromptGarageTests.source.readonly"
        )
        let sourceLegacySnapshot: LegacyRowsSnapshot
        do {
            sourceLegacySnapshot = try legacyRowsSnapshot(in: sourceQueue)
            try sourceQueue.close()
        } catch {
            try? sourceQueue.close()
            throw error
        }

        let root = FileManager.default.temporaryDirectory
            .appendingPathComponent("PromptGarageRealMigration-\(UUID().uuidString)", isDirectory: true)
        try FileManager.default.createDirectory(at: root, withIntermediateDirectories: true)
        addTeardownBlock { try? FileManager.default.removeItem(at: root) }

        let service = try DatabaseBootstrapService(
            applicationSupportRoot: root.appendingPathComponent("ApplicationSupport")
        )
        let sourceReport = try service.validateSource(at: sourceURL)
        XCTAssertFalse(
            sourceReport.hasPromptedAt,
            "The opt-in source must be the untouched legacy DB, not an already migrated app copy."
        )
        XCTAssertGreaterThan(sourceReport.promptCount, 0)

        let repository = try service.installDatabase(from: sourceURL)
        let destinationQueue = try readOnlyQueue(
            at: service.databaseURL,
            label: "PromptGarageTests.destination.readonly"
        )
        defer { try? destinationQueue.close() }
        let destinationReport = try DatabaseSchema.validateReadOnly(destinationQueue)
        let destinationLegacySnapshot = try legacyRowsSnapshot(in: destinationQueue)

        let fourParityCounts = [
            sourceReport.promptCount,
            sourceReport.ftsCount,
            destinationReport.promptCount,
            destinationReport.ftsCount,
        ]
        XCTAssertEqual(
            Set(fourParityCounts).count,
            1,
            "source prompts/source FTS/destination prompts/destination FTS must stay dynamically equal: \(fourParityCounts)"
        )
        XCTAssertTrue(destinationReport.hasPromptedAt)
        XCTAssertEqual(try repository.count(), sourceReport.promptCount)
        XCTAssertEqual(try repository.legacyUndatedCount(), sourceReport.promptCount)
        XCTAssertEqual(sourceLegacySnapshot.rowCount, sourceReport.promptCount)
        XCTAssertEqual(destinationLegacySnapshot.rowCount, destinationReport.promptCount)
        XCTAssertEqual(
            destinationLegacySnapshot.columnDigests,
            sourceLegacySnapshot.columnDigests,
            "Every value in every legacy column must survive the copy exactly."
        )
        XCTAssertEqual(
            destinationLegacySnapshot.orderedRowsDigest,
            sourceLegacySnapshot.orderedRowsDigest,
            "Legacy row/column associations must remain byte-for-byte equivalent in deterministic id order."
        )

        let destinationLegacyNullCount = try destinationQueue.read { db in
            try Int.fetchOne(
                db,
                sql: "SELECT COUNT(*) FROM prompts WHERE prompted_at IS NULL"
            ) ?? -1
        }
        XCTAssertEqual(
            destinationLegacyNullCount,
            sourceReport.promptCount,
            "Adding prompted_at to the app copy must not infer or backfill dates for any legacy row."
        )

        XCTAssertEqual(
            try databaseFamilySnapshot(at: sourceURL),
            sourceFamilyBefore,
            "The selected source DB family (main, WAL, SHM, or journal) changed during a supposedly read-only migration."
        )
    }

    private let legacyColumns = [
        "id", "title", "content", "source", "is_starred", "tags", "model",
        "rating", "use_count", "created_at", "updated_at",
    ]

    private func readOnlyQueue(at url: URL, label: String) throws -> DatabaseQueue {
        var configuration = Configuration()
        configuration.label = label
        configuration.readonly = true
        configuration.busyMode = .timeout(5)
        return try DatabaseQueue(path: url.path, configuration: configuration)
    }

    /// Hashes every SQLite value with an explicit storage-class marker and
    /// length framing. Per-column digests prove column preservation; the full
    /// digest additionally proves that values stayed attached to the same row
    /// in deterministic primary-key order.
    private func legacyRowsSnapshot(in queue: DatabaseQueue) throws -> LegacyRowsSnapshot {
        try queue.read { db in
            let selectedColumns = legacyColumns
                .map { "\"\($0)\"" }
                .joined(separator: ", ")
            let cursor = try Row.fetchCursor(
                db,
                sql: "SELECT \(selectedColumns) FROM prompts ORDER BY id ASC"
            )

            var rowCount = 0
            var orderedRowsHasher = SHA256()
            var columnHashers = legacyColumns.map { _ in SHA256() }

            while let row = try cursor.next() {
                rowCount += 1
                update(&orderedRowsHasher, withUnsignedInteger: UInt64(rowCount))

                for (index, column) in legacyColumns.enumerated() {
                    let value: DatabaseValue = row[column]
                    update(&orderedRowsHasher, with: value)
                    update(&columnHashers[index], with: value)
                }
            }

            let columnDigests = Dictionary(uniqueKeysWithValues: zip(
                legacyColumns,
                columnHashers.map { hasher in
                    var hasher = hasher
                    return hexadecimal(hasher.finalize())
                }
            ))
            return LegacyRowsSnapshot(
                rowCount: rowCount,
                orderedRowsDigest: hexadecimal(orderedRowsHasher.finalize()),
                columnDigests: columnDigests
            )
        }
    }

    private func update(_ hasher: inout SHA256, with value: DatabaseValue) {
        switch value.storage {
        case .null:
            hasher.update(data: Data([0]))
        case let .int64(value):
            hasher.update(data: Data([1]))
            update(&hasher, withUnsignedInteger: UInt64(bitPattern: value))
        case let .double(value):
            hasher.update(data: Data([2]))
            update(&hasher, withUnsignedInteger: value.bitPattern)
        case let .string(value):
            hasher.update(data: Data([3]))
            update(&hasher, withLengthPrefixed: Data(value.utf8))
        case let .blob(value):
            hasher.update(data: Data([4]))
            update(&hasher, withLengthPrefixed: value)
        }
    }

    private func update(_ hasher: inout SHA256, withLengthPrefixed data: Data) {
        update(&hasher, withUnsignedInteger: UInt64(data.count))
        hasher.update(data: data)
    }

    private func update(_ hasher: inout SHA256, withUnsignedInteger value: UInt64) {
        var bigEndian = value.bigEndian
        withUnsafeBytes(of: &bigEndian) { bytes in
            hasher.update(data: Data(bytes))
        }
    }

    private func databaseFamilySnapshot(at mainURL: URL) throws -> [DatabaseFileSnapshot] {
        try ["", "-wal", "-shm", "-journal"].map { suffix in
            let url = URL(fileURLWithPath: mainURL.path + suffix)
            guard FileManager.default.fileExists(atPath: url.path) else {
                return DatabaseFileSnapshot(suffix: suffix, byteCount: nil, sha256: nil)
            }
            let attributes = try FileManager.default.attributesOfItem(atPath: url.path)
            let byteCount = (attributes[.size] as? NSNumber)?.uint64Value
            return DatabaseFileSnapshot(
                suffix: suffix,
                byteCount: byteCount,
                sha256: try sha256(of: url)
            )
        }
    }

    private func sha256(of url: URL) throws -> String {
        let data = try Data(contentsOf: url, options: [.mappedIfSafe])
        return hexadecimal(SHA256.hash(data: data))
    }

    private func hexadecimal<D: Sequence>(_ digest: D) -> String where D.Element == UInt8 {
        digest.map { String(format: "%02x", $0) }.joined()
    }
}

private struct LegacyRowsSnapshot: Equatable {
    let rowCount: Int
    let orderedRowsDigest: String
    let columnDigests: [String: String]
}

private struct DatabaseFileSnapshot: Equatable {
    let suffix: String
    let byteCount: UInt64?
    let sha256: String?
}

	import CryptoKit
	import Foundation
	import GRDB
	import XCTest
	@testable import PromptGarageCore

	/// Opt-in integration coverage for Samuel's real legacy database.
	///
	/// Run on the Mac with:
	/// `PROMPTGARAGE_SOURCE_DB=/absolute/path/to/prompts.db swift test --filter RealDatabaseMigrationTests`
	///
	/// The source is hashed before and after, and all SQLite connections opened by
	/// the production bootstrap service are read-only. Only a disposable copy below
	/// XCTest's temporary directory is migrated.
	final class RealDatabaseMigrationTests: XCTestCase {
	func testRealSourceInstallsToTemporaryDestinationWithoutSourceMutationOrBackfill() throws {
	let environment = ProcessInfo.processInfo.environment
	guard let sourcePath = environment["PROMPTGARAGE_SOURCE_DB"]?
	.trimmingCharacters(in: .whitespacesAndNewlines),
	!sourcePath.isEmpty
	else {
	throw XCTSkip("Set PROMPTGARAGE_SOURCE_DB to opt in to the real legacy DB migration test.")
	}

	let sourceURL = URL(fileURLWithPath: sourcePath).standardizedFileURL
	guard FileManager.default.fileExists(atPath: sourceURL.path) else {
	XCTFail("PROMPTGARAGE_SOURCE_DB does not exist: \(sourceURL.path)")
	return
	}

	let sourceFamilyBefore = try databaseFamilySnapshot(at: sourceURL)
	let sourceQueue = try readOnlyQueue(
	at: sourceURL,
	label: "PromptGarageTests.source.readonly"
	)
	let sourceLegacySnapshot: LegacyRowsSnapshot
	do {
	sourceLegacySnapshot = try legacyRowsSnapshot(in: sourceQueue)
	try sourceQueue.close()
	} catch {
	try? sourceQueue.close()
	throw error
	}

	let root = FileManager.default.temporaryDirectory
	.appendingPathComponent("PromptGarageRealMigration-\(UUID().uuidString)", isDirectory: true)
	try FileManager.default.createDirectory(at: root, withIntermediateDirectories: true)
	addTeardownBlock { try? FileManager.default.removeItem(at: root) }

	let service = try DatabaseBootstrapService(
	applicationSupportRoot: root.appendingPathComponent("ApplicationSupport")
	)
	let sourceReport = try service.validateSource(at: sourceURL)
	XCTAssertFalse(
	sourceReport.hasPromptedAt,
	"The opt-in source must be the untouched legacy DB, not an already migrated app copy."
	)
	XCTAssertGreaterThan(sourceReport.promptCount, 0)

	let repository = try service.installDatabase(from: sourceURL)
	let destinationQueue = try readOnlyQueue(
	at: service.databaseURL,
	label: "PromptGarageTests.destination.readonly"
	)
	defer { try? destinationQueue.close() }
	let destinationReport = try DatabaseSchema.validateReadOnly(destinationQueue)
	let destinationLegacySnapshot = try legacyRowsSnapshot(in: destinationQueue)

	let fourParityCounts = [
	sourceReport.promptCount,
	sourceReport.ftsCount,
	destinationReport.promptCount,
	destinationReport.ftsCount,
	]
	XCTAssertEqual(
	Set(fourParityCounts).count,
	1,
	"source prompts/source FTS/destination prompts/destination FTS must stay dynamically equal: \(fourParityCounts)"
	)
	XCTAssertTrue(destinationReport.hasPromptedAt)
	XCTAssertEqual(try repository.count(), sourceReport.promptCount)
	XCTAssertEqual(try repository.legacyUndatedCount(), sourceReport.promptCount)
	XCTAssertEqual(sourceLegacySnapshot.rowCount, sourceReport.promptCount)
	XCTAssertEqual(destinationLegacySnapshot.rowCount, destinationReport.promptCount)
	XCTAssertEqual(
	destinationLegacySnapshot.columnDigests,
	sourceLegacySnapshot.columnDigests,
	"Every value in every legacy column must survive the copy exactly."
	)
	XCTAssertEqual(
	destinationLegacySnapshot.orderedRowsDigest,
	sourceLegacySnapshot.orderedRowsDigest,
	"Legacy row/column associations must remain byte-for-byte equivalent in deterministic id order."
	)

	let destinationLegacyNullCount = try destinationQueue.read { db in
	try Int.fetchOne(
	db,
	sql: "SELECT COUNT(*) FROM prompts WHERE prompted_at IS NULL"
	) ?? -1
	}
	XCTAssertEqual(
	destinationLegacyNullCount,
	sourceReport.promptCount,
	"Adding prompted_at to the app copy must not infer or backfill dates for any legacy row."
	)

	XCTAssertEqual(
	try databaseFamilySnapshot(at: sourceURL),
	sourceFamilyBefore,
	"The selected source DB family (main, WAL, SHM, or journal) changed during a supposedly read-only migration."
	)
	}

	private let legacyColumns = [
	"id", "title", "content", "source", "is_starred", "tags", "model",
	"rating", "use_count", "created_at", "updated_at",
	]

	private func readOnlyQueue(at url: URL, label: String) throws -> DatabaseQueue {
	var configuration = Configuration()
	configuration.label = label
	configuration.readonly = true
	configuration.busyMode = .timeout(5)
	return try DatabaseQueue(path: url.path, configuration: configuration)
	}

	/// Hashes every SQLite value with an explicit storage-class marker and
	/// length framing. Per-column digests prove column preservation; the full
	/// digest additionally proves that values stayed attached to the same row
	/// in deterministic primary-key order.
	private func legacyRowsSnapshot(in queue: DatabaseQueue) throws -> LegacyRowsSnapshot {
	try queue.read { db in
	let selectedColumns = legacyColumns
	.map { "\"\($0)\"" }
	.joined(separator: ", ")
	let cursor = try Row.fetchCursor(
	db,
	sql: "SELECT \(selectedColumns) FROM prompts ORDER BY id ASC"
	)

	var rowCount = 0
	var orderedRowsHasher = SHA256()
	var columnHashers = legacyColumns.map { _ in SHA256() }

	while let row = try cursor.next() {
	rowCount += 1
	update(&orderedRowsHasher, withUnsignedInteger: UInt64(rowCount))

	for (index, column) in legacyColumns.enumerated() {
	let value: DatabaseValue = row[column]
	update(&orderedRowsHasher, with: value)
	update(&columnHashers[index], with: value)
	}
	}

	let columnDigests = Dictionary(uniqueKeysWithValues: zip(
	legacyColumns,
	columnHashers.map { hasher in
	var hasher = hasher
	return hexadecimal(hasher.finalize())
	}
	))
	return LegacyRowsSnapshot(
	rowCount: rowCount,
	orderedRowsDigest: hexadecimal(orderedRowsHasher.finalize()),
	columnDigests: columnDigests
	)
	}
	}

	private func update(_ hasher: inout SHA256, with value: DatabaseValue) {
	switch value.storage {
	case .null:
	hasher.update(data: Data([0]))
	case let .int64(value):
	hasher.update(data: Data([1]))
	update(&hasher, withUnsignedInteger: UInt64(bitPattern: value))
	case let .double(value):
	hasher.update(data: Data([2]))
	update(&hasher, withUnsignedInteger: value.bitPattern)
	case let .string(value):
	hasher.update(data: Data([3]))
	update(&hasher, withLengthPrefixed: Data(value.utf8))
	case let .blob(value):
	hasher.update(data: Data([4]))
	update(&hasher, withLengthPrefixed: value)
	}
	}

	private func update(_ hasher: inout SHA256, withLengthPrefixed data: Data) {
	update(&hasher, withUnsignedInteger: UInt64(data.count))
	hasher.update(data: data)
	}

	private func update(_ hasher: inout SHA256, withUnsignedInteger value: UInt64) {
	var bigEndian = value.bigEndian
	withUnsafeBytes(of: &bigEndian) { bytes in
	hasher.update(data: Data(bytes))
	}
	}

	private func databaseFamilySnapshot(at mainURL: URL) throws -> [DatabaseFileSnapshot] {
	try ["", "-wal", "-shm", "-journal"].map { suffix in
	let url = URL(fileURLWithPath: mainURL.path + suffix)
	guard FileManager.default.fileExists(atPath: url.path) else {
	return DatabaseFileSnapshot(suffix: suffix, byteCount: nil, sha256: nil)
	}
	let attributes = try FileManager.default.attributesOfItem(atPath: url.path)
	let byteCount = (attributes[.size] as? NSNumber)?.uint64Value
	return DatabaseFileSnapshot(
	suffix: suffix,
	byteCount: byteCount,
	sha256: try sha256(of: url)
	)
	}
	}

	private func sha256(of url: URL) throws -> String {
	let data = try Data(contentsOf: url, options: [.mappedIfSafe])
	return hexadecimal(SHA256.hash(data: data))
	}

	private func hexadecimal<D: Sequence>(_ digest: D) -> String where D.Element == UInt8 {
	digest.map { String(format: "%02x", $0) }.joined()
	}
	}

	private struct LegacyRowsSnapshot: Equatable {
	let rowCount: Int
	let orderedRowsDigest: String
	let columnDigests: [String: String]
	}

	private struct DatabaseFileSnapshot: Equatable {
	let suffix: String
	let byteCount: UInt64?
	let sha256: String?
	}