| import Foundation |
| import GRDB |
| import XCTest |
| @testable import PromptGarageCore |
|
|
| final class DatabaseSchemaTests: XCTestCase { |
| private func makeDatabaseQueue() throws -> DatabaseQueue { |
| let directory = FileManager.default.temporaryDirectory |
| .appendingPathComponent("PromptGarageTests-\(UUID().uuidString)", isDirectory: true) |
| try FileManager.default.createDirectory(at: directory, withIntermediateDirectories: true) |
| addTeardownBlock { |
| try? FileManager.default.removeItem(at: directory) |
| } |
| return try DatabaseQueue(path: directory.appendingPathComponent("prompts.db").path) |
| } |
|
|
| func testSchemaCreationAndValidationAreIdempotent() throws { |
| let queue = try makeDatabaseQueue() |
|
|
| try queue.write { db in |
| try DatabaseSchema.createIfNeeded(in: db) |
| try DatabaseSchema.createIfNeeded(in: db) |
| } |
|
|
| let report = try DatabaseSchema.validate(queue) |
| XCTAssertEqual(report.integrity, "ok") |
| XCTAssertEqual(report.promptCount, 0) |
| XCTAssertEqual(report.ftsCount, 0) |
| } |
|
|
| func testFTSTriggersFollowInsertUpdateAndDelete() throws { |
| let queue = try makeDatabaseQueue() |
| try queue.write { db in |
| try DatabaseSchema.createIfNeeded(in: db) |
| try db.execute( |
| sql: "INSERT INTO prompts (title, content, tags) VALUES (?, ?, ?)", |
| arguments: ["Swift Search", "prefix matching with SQLite", "swift,search"] |
| ) |
| } |
|
|
| var counts = try queue.read { db in |
| ( |
| try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM prompts") ?? -1, |
| try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM prompts_fts") ?? -1 |
| ) |
| } |
| XCTAssertEqual(counts.0, 1) |
| XCTAssertEqual(counts.1, 1) |
|
|
| try queue.write { db in |
| try db.execute(sql: "UPDATE prompts SET content = ? WHERE id = 1", arguments: ["updated text"]) |
| } |
| let updatedContent = try queue.read { db in |
| try String.fetchOne(db, sql: "SELECT content FROM prompts_fts WHERE rowid = 1") |
| } |
| XCTAssertEqual(updatedContent, "updated text") |
|
|
| try queue.write { db in |
| try db.execute(sql: "DELETE FROM prompts WHERE id = 1") |
| } |
| counts = try queue.read { db in |
| ( |
| try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM prompts") ?? -1, |
| try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM prompts_fts") ?? -1 |
| ) |
| } |
| XCTAssertEqual(counts.0, 0) |
| XCTAssertEqual(counts.1, 0) |
| } |
|
|
| func testValidationRejectsDatabaseWithoutPromptSchema() throws { |
| let queue = try makeDatabaseQueue() |
| try queue.write { db in |
| try db.execute(sql: "CREATE TABLE unrelated (id INTEGER PRIMARY KEY)") |
| } |
|
|
| XCTAssertThrowsError(try DatabaseSchema.validate(queue)) { error in |
| XCTAssertEqual(error as? DatabaseValidationError, .missingTables) |
| } |
| } |
|
|
| func testRepairRebuildsStaleExternalContentFTSIndex() throws { |
| let queue = try makeDatabaseQueue() |
| try queue.write { db in |
| try DatabaseSchema.createIfNeeded(in: db) |
| try db.execute( |
| sql: "INSERT INTO prompts (title, content, tags) VALUES (?, ?, ?)", |
| arguments: ["Swift title", "native search", "swift"] |
| ) |
| try db.execute( |
| sql: """ |
| INSERT INTO prompts_fts(prompts_fts, rowid, title, content, tags) |
| VALUES('delete', 1, 'Swift title', 'native search', 'swift') |
| """ |
| ) |
| } |
|
|
| |
| |
| let before = try queue.read { db in |
| ( |
| try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM prompts_fts") ?? -1, |
| try Int.fetchOne( |
| db, |
| sql: "SELECT COUNT(*) FROM prompts_fts WHERE prompts_fts MATCH 'swift'" |
| ) ?? -1 |
| ) |
| } |
| XCTAssertEqual(before.0, 1) |
| XCTAssertEqual(before.1, 0) |
|
|
| try queue.write { db in |
| try DatabaseSchema.repairSearchIndexIfNeeded(in: db) |
| } |
| let matches = try queue.read { db in |
| try Int.fetchOne( |
| db, |
| sql: "SELECT COUNT(*) FROM prompts_fts WHERE prompts_fts MATCH 'swift'" |
| ) |
| } |
| XCTAssertEqual(matches, 1) |
| } |
| } |
|
|
