File size: 4,632 Bytes
4977db2 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 | 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')
"""
)
}
// External-content FTS count mirrors `prompts`, even though MATCH is
// stale. This is the exact failure that plain row parity misses.
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)
}
}
|