HealthProbe
/
HealthProbeTests
/
SQLiteHealthArchiveStoreTests.swift
1 contributor
import HealthKit
import SQLite3
import XCTest
@testable import HealthProbe
final class SQLiteHealthArchiveStoreTests: XCTestCase {
private var temporaryDirectory: URL!
override func setUpWithError() throws {
temporaryDirectory = FileManager.default.temporaryDirectory
.appending(path: "HealthProbeTests-\(UUID().uuidString)", directoryHint: .isDirectory)
try FileManager.default.createDirectory(at: temporaryDirectory, withIntermediateDirectories: true)
}
override func tearDownWithError() throws {
if let temporaryDirectory {
try? FileManager.default.removeItem(at: temporaryDirectory)
}
temporaryDirectory = nil
}
func testFreshArchiveInitializesSchemaAndPassesIntegrity() async throws {
let store = SQLiteHealthArchiveStore(databaseURL: databaseURL())
let report = try await store.checkIntegrity()
let records = try await store.records(for: HealthArchiveRecordRequest(
sampleTypeIdentifier: HKQuantityTypeIdentifier.stepCount.rawValue
))
XCTAssertTrue(report.passed)
XCTAssertEqual(report.schemaVersion, 2)
XCTAssertEqual(report.sqliteIntegrityStatus, "ok")
XCTAssertEqual(report.foreignKeyIssueCount, 0)
XCTAssertTrue(report.missingTableNames.isEmpty)
XCTAssertTrue(report.requiredTableNames.contains("sample_visibility_ranges"))
XCTAssertTrue(report.requiredTableNames.contains("daily_type_aggregates"))
XCTAssertTrue(records.isEmpty)
}
func testPrototypeArchiveIsResetAndReinitializedAsV2() async throws {
let url = databaseURL()
try createPrototypeDatabase(at: url)
let store = SQLiteHealthArchiveStore(databaseURL: url)
let report = try await store.checkIntegrity()
XCTAssertTrue(report.passed)
XCTAssertEqual(report.schemaVersion, 2)
XCTAssertTrue(report.missingTableNames.isEmpty)
}
func testRepeatedSamplePageDoesNotDuplicateIdentityOrVersion() async throws {
let url = databaseURL()
let store = SQLiteHealthArchiveStore(databaseURL: url)
let sample = makeStepCountSample()
let firstWrite = try await store.upsertSamples([sample], observedAt: Date(timeIntervalSince1970: 2_000))
let secondWrite = try await store.upsertSamples([sample], observedAt: Date(timeIntervalSince1970: 2_060))
let records = try await store.records(for: HealthArchiveRecordRequest(
sampleTypeIdentifier: sample.sampleType.identifier
))
let report = try await store.checkIntegrity()
let versionDebugRows = try sampleVersionDebugRows(at: url)
XCTAssertEqual(firstWrite.insertedCount, 1)
XCTAssertEqual(firstWrite.updatedCount, 0)
XCTAssertEqual(firstWrite.unchangedCount, 0)
XCTAssertEqual(try countRows(in: "samples", at: url), 1)
XCTAssertEqual(try countRows(in: "sample_versions", at: url), 1, versionDebugRows)
XCTAssertEqual(try countRows(in: "sample_visibility_ranges", at: url), 1)
XCTAssertEqual(try countRows(in: "source_revisions", at: url), 1)
XCTAssertEqual(secondWrite.insertedCount, 0)
XCTAssertEqual(secondWrite.updatedCount, 0)
XCTAssertEqual(secondWrite.unchangedCount, 1)
XCTAssertEqual(records.count, 1)
XCTAssertEqual(records.first?.displayValue, "42.0 count")
XCTAssertTrue(report.passed)
}
func testDiffSummaryAndRecordsBetweenObservationsUseSQLVisibility() async throws {
let url = databaseURL()
let store = SQLiteHealthArchiveStore(databaseURL: url)
let firstSample = makeStepCountSample(value: 42, start: 1_000)
let secondSample = makeStepCountSample(value: 7, start: 2_000)
let typeIdentifier = HKQuantityTypeIdentifier.stepCount.rawValue
_ = try await store.upsertSamples([firstSample], observedAt: Date(timeIntervalSince1970: 3_000))
_ = try await store.upsertSamples([firstSample, secondSample], observedAt: Date(timeIntervalSince1970: 3_060))
try await store.recordDisappearance(
sampleUUIDHash: HashService.sampleUUIDHash(firstSample.uuid.uuidString),
sampleTypeIdentifier: typeIdentifier,
observedMissingAt: Date(timeIntervalSince1970: 3_120)
)
let observationIDs = try observationIDs(at: url)
XCTAssertEqual(observationIDs.count, 3)
let appearedSummary = try await store.diffSummary(HealthArchiveDiffRequest(
fromObservationID: observationIDs[0],
toObservationID: observationIDs[1],
sampleTypeIdentifier: typeIdentifier
))
let appearedRecords = try await store.diffRecords(HealthArchiveDiffRecordRequest(
fromObservationID: observationIDs[0],
toObservationID: observationIDs[1],
sampleTypeIdentifier: typeIdentifier,
kind: .appeared,
limit: 10
))
let disappearedSummary = try await store.diffSummary(HealthArchiveDiffRequest(
fromObservationID: observationIDs[1],
toObservationID: observationIDs[2],
sampleTypeIdentifier: typeIdentifier
))
let disappearedRecords = try await store.diffRecords(HealthArchiveDiffRecordRequest(
fromObservationID: observationIDs[1],
toObservationID: observationIDs[2],
sampleTypeIdentifier: typeIdentifier,
kind: .disappeared,
limit: 10
))
XCTAssertEqual(appearedSummary.appearedCount, 1)
XCTAssertEqual(appearedSummary.disappearedCount, 0)
XCTAssertEqual(appearedSummary.representationChangedCount, 0)
XCTAssertEqual(appearedRecords.map(\.displayValue), ["7.0 count"])
XCTAssertEqual(disappearedSummary.appearedCount, 0)
XCTAssertEqual(disappearedSummary.disappearedCount, 1)
XCTAssertEqual(disappearedSummary.representationChangedCount, 0)
XCTAssertEqual(disappearedRecords.map(\.displayValue), ["42.0 count"])
XCTAssertNotNil(disappearedRecords.first?.disappearedAt)
}
func testAggregateComparisonUsesMaterializedDailyAggregates() async throws {
let url = databaseURL()
let store = SQLiteHealthArchiveStore(databaseURL: url)
let firstSample = makeStepCountSample(value: 42, start: 1_000)
let secondSample = makeStepCountSample(value: 7, start: 2_000)
let typeIdentifier = HKQuantityTypeIdentifier.stepCount.rawValue
_ = try await store.upsertSamples([firstSample], observedAt: Date(timeIntervalSince1970: 3_000))
_ = try await store.upsertSamples([firstSample, secondSample], observedAt: Date(timeIntervalSince1970: 3_060))
let observationIDs = try observationIDs(at: url)
XCTAssertEqual(observationIDs.count, 2)
let rows = try await store.aggregateComparison(HealthArchiveAggregateComparisonRequest(
fromObservationID: observationIDs[0],
toObservationID: observationIDs[1],
sampleTypeIdentifier: typeIdentifier,
limit: 10
))
XCTAssertEqual(rows.count, 1)
XCTAssertEqual(rows.first?.sampleTypeIdentifier, typeIdentifier)
XCTAssertEqual(rows.first?.fromVisibleRecordCount, 1)
XCTAssertEqual(rows.first?.toVisibleRecordCount, 2)
XCTAssertEqual(rows.first?.visibleRecordDelta, 1)
XCTAssertEqual(rows.first?.fromValueSum, 42)
XCTAssertEqual(rows.first?.toValueSum, 49)
XCTAssertEqual(rows.first?.valueSumDelta, 7)
}
private func databaseURL() -> URL {
temporaryDirectory.appending(path: "Archive.sqlite")
}
private func createPrototypeDatabase(at url: URL) throws {
var db: OpaquePointer?
guard sqlite3_open_v2(url.path, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX, nil) == SQLITE_OK else {
sqlite3_close(db)
XCTFail("Could not create prototype database")
return
}
defer { sqlite3_close(db) }
let status = sqlite3_exec(db, "CREATE TABLE archive_samples (id TEXT PRIMARY KEY)", nil, nil, nil)
XCTAssertEqual(status, SQLITE_OK)
}
private func makeStepCountSample() -> HKQuantitySample {
makeStepCountSample(value: 42, start: 1_000)
}
private func makeStepCountSample(value: Double, start: TimeInterval) -> HKQuantitySample {
let quantityType = HKQuantityType.quantityType(forIdentifier: .stepCount)!
let quantity = HKQuantity(unit: .count(), doubleValue: value)
let startDate = Date(timeIntervalSince1970: start)
let endDate = Date(timeIntervalSince1970: start + 300)
return HKQuantitySample(type: quantityType, quantity: quantity, start: startDate, end: endDate)
}
private func countRows(in tableName: String, at url: URL) throws -> Int {
var db: OpaquePointer?
guard sqlite3_open_v2(url.path, &db, SQLITE_OPEN_READONLY | SQLITE_OPEN_FULLMUTEX, nil) == SQLITE_OK else {
sqlite3_close(db)
XCTFail("Could not open test database")
return 0
}
defer { sqlite3_close(db) }
var statement: OpaquePointer?
guard sqlite3_prepare_v2(db, "SELECT COUNT(*) FROM \(tableName)", -1, &statement, nil) == SQLITE_OK else {
sqlite3_finalize(statement)
XCTFail("Could not prepare count query")
return 0
}
defer { sqlite3_finalize(statement) }
guard sqlite3_step(statement) == SQLITE_ROW else {
return 0
}
return Int(sqlite3_column_int(statement, 0))
}
private func observationIDs(at url: URL) throws -> [Int64] {
var db: OpaquePointer?
guard sqlite3_open_v2(url.path, &db, SQLITE_OPEN_READONLY | SQLITE_OPEN_FULLMUTEX, nil) == SQLITE_OK else {
sqlite3_close(db)
XCTFail("Could not open test database")
return []
}
defer { sqlite3_close(db) }
var statement: OpaquePointer?
guard sqlite3_prepare_v2(db, "SELECT id FROM observations ORDER BY id", -1, &statement, nil) == SQLITE_OK else {
sqlite3_finalize(statement)
XCTFail("Could not prepare observation query")
return []
}
defer { sqlite3_finalize(statement) }
var ids: [Int64] = []
while sqlite3_step(statement) == SQLITE_ROW {
ids.append(sqlite3_column_int64(statement, 0))
}
return ids
}
private func sampleVersionDebugRows(at url: URL) throws -> String {
var db: OpaquePointer?
guard sqlite3_open_v2(url.path, &db, SQLITE_OPEN_READONLY | SQLITE_OPEN_FULLMUTEX, nil) == SQLITE_OK else {
sqlite3_close(db)
return "could not open database"
}
defer { sqlite3_close(db) }
let sql = """
SELECT v.payload_hash, v.start_date, v.end_date, v.value_kind, v.numeric_value, v.unit,
v.source_revision_id, src.bundle_identifier, sr.product_type, sr.version,
sr.operating_system_version, v.hk_device_id, v.metadata_id
FROM sample_versions v
LEFT JOIN source_revisions sr ON sr.id = v.source_revision_id
LEFT JOIN sources src ON src.id = sr.source_id
ORDER BY v.id
"""
var statement: OpaquePointer?
guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
sqlite3_finalize(statement)
return "could not prepare version debug query"
}
defer { sqlite3_finalize(statement) }
var rows: [String] = []
while sqlite3_step(statement) == SQLITE_ROW {
rows.append((0..<13).map { index in
if sqlite3_column_type(statement, Int32(index)) == SQLITE_NULL {
return "null"
}
if let text = sqlite3_column_text(statement, Int32(index)) {
return String(cString: text)
}
return "\(sqlite3_column_double(statement, Int32(index)))"
}.joined(separator: "|"))
}
return rows.joined(separator: "\n")
}
}