// 运动概要数据
class OPHealthWorkoutInfoModel {
///运动记录ID，唯一标识，字符最大长度32
var sportID: String = String()
///运动类型，参考OPPO运动记录类型列表
var sportType: UInt32 = 0
///运动名称
var sportName: UInt32 = 0
///运动开始时间（UTC秒）
var startTime: UInt32 = 0
///运动结束时间 （UTC秒）
var endTime: UInt32 = 0
///运动的总步数
var totalSteps: UInt32 = 0
///运动的总距离，单位米
var totalDistance: UInt32 = 0
///运动总时间。单位秒
var totalTime: UInt32 = 0
///运动总消耗的卡路里，单位千卡
var totalCalories: UInt32 = 0
///运动的累计上升高度，单位米
var totalHeight: UInt32 = 0
///平均心率
var avgHeartRate: UInt32 = 0
///平均配速，单位 秒/公里
var avgSpeed: UInt32 = 0
///最佳配速，单位 秒/公里
var maxSpeed: UInt32 = 0
///平局步频
var avgFrequency: UInt32 = 0
/// 扩展字段，字符串，不同运动类型定义不同的扩展
var extra: String = String()
var gpsModel: OPHealthWorkoutGPSModel = OPHealthWorkoutGPSModel()
}
class OPHealthWorkoutGPSModel {
///时间戳（UTC秒）
var timestamp: [UInt64] = []
///纬度 * 1000000
var latitude: [Int32] = []
///经度 * 1000000
var longitude: [Int32] = []
///状态：0正常，1暂停
var state: [UInt32] = []
///GPS速度，（单位：秒/公里）
var speed: [Float] = []
}
import Foundation
import SQLite
// 运动概要数据
class OPHealthWorkoutInfoModel {
///运动记录ID，唯一标识，字符最大长度32
var sportID: String = String()
///运动类型，参考OPPO运动记录类型列表
var sportType: UInt32 = 0
///运动名称
var sportName: UInt32 = 0
///运动开始时间（UTC秒）
var startTime: UInt32 = 0
///运动结束时间 （UTC秒）
var endTime: UInt32 = 0
///运动的总步数
var totalSteps: UInt32 = 0
///运动的总距离，单位米
var totalDistance: UInt32 = 0
///运动总时间。单位秒
var totalTime: UInt32 = 0
///运动总消耗的卡路里，单位千卡
var totalCalories: UInt32 = 0
///运动的累计上升高度，单位米
var totalHeight: UInt32 = 0
///平均心率
var avgHeartRate: UInt32 = 0
///平均配速，单位 秒/公里
var avgSpeed: UInt32 = 0
///最佳配速，单位 秒/公里
var maxSpeed: UInt32 = 0
///平局步频
var avgFrequency: UInt32 = 0
/// 扩展字段，字符串，不同运动类型定义不同的扩展
var extra: String = String()
var gpsModel: OPHealthWorkoutGPSModel = OPHealthWorkoutGPSModel()
}
class OPHealthWorkoutGPSModel {
///时间戳（UTC秒）
var timestamp: [UInt64] = []
///纬度 * 1000000
var latitude: [Int32] = []
///经度 * 1000000
var longitude: [Int32] = []
///状态：0正常，1暂停
var state: [UInt32] = []
///GPS速度，（单位：秒/公里）
var speed: [Float] = []
}
class OPHealthHeartRateModel {
var time_offset = 0.0 // 相对于 start_time 的偏移量（单位秒），可以根据 start_time + time_offset 来获取当前这条数据对应的时间戳
var heart_rate = 0.0 // 心率值
var reliability = 0 // 心率结果置信度，范围 0~3 越大越可信（目前穿戴设备在心率预警判断时会用到）
var type = 0 // 心率类型（0：日常心率，1：静息心率，2：运动心率）
}
class OPHealthBreatheRateModel {
var time_offset = 0.0 // 对应这条数据的时间戳
var breath_rate = 0 // 其他bit位的表示呼吸率值
}
class OPHealthSleepInfoModel {
var time_stamp = 0 // 时间戳
var state = 0 // 睡眠状态（深睡，浅睡，REM，清醒，短暂清醒），一分钟对应一个状态
}
class OPHealthBloodOxygenModel {
var time_stamp = 0 // 时间戳
var type = 0 // 类型(0 睡眠血氧，1 点测，2 全天自动测量)
var value = 0 // 血氧值
}
class SqliteManager {
static let shared = SqliteManager()
private let db: Connection
// Table and columns for WorkoutInfo
typealias Expression = SQLite.Expression
private let workoutTable = Table("workout_info")
private let workoutSportID = Expression<String>("sport_id")
private let workoutSportType = Expression<Int64>("sport_type")
private let workoutSportName = Expression<Int64>("sport_name")
private let workoutStartTime = Expression<Int64>("start_time")
private let workoutEndTime = Expression<Int64>("end_time")
private let workoutTotalSteps = Expression<Int64>("total_steps")
private let workoutTotalDistance = Expression<Int64>("total_distance")
private let workoutTotalTime = Expression<Int64>("total_time")
private let workoutTotalCalories = Expression<Int64>("total_calories")
private let workoutTotalHeight = Expression<Int64>("total_height")
private let workoutAvgHeartRate = Expression<Int64>("avg_heart_rate")
private let workoutAvgSpeed = Expression<Int64>("avg_speed")
private let workoutMaxSpeed = Expression<Int64>("max_speed")
private let workoutAvgFrequency = Expression<Int64>("avg_frequency")
private let workoutExtra = Expression<String>("extra")
// Table and columns for GPS data
private let gpsTable = Table("workout_gps")
private let gpsSportID = Expression<String>("sport_id")
private let gpsTimestampArray = Expression<String>("timestamp_array")
private let gpsLatitudeArray = Expression<String>("latitude_array")
private let gpsLongitudeArray = Expression<String>("longitude_array")
private let gpsStateArray = Expression<String>("state_array")
private let gpsSpeedArray = Expression<String>("speed_array")
// Table and columns for HeartRate data
private let heartRateTable = Table("heart_rate")
private let heartRateTimeOffset = Expression<Double>("time_offset")
private let heartRateValue = Expression<Double>("heart_rate")
private let heartRateReliability = Expression<Int64>("reliability")
private let heartRateType = Expression<Int64>("type")
// Table and columns for BreatheRate data
private let breatheRateTable = Table("breathe_rate")
private let breatheRateTimeOffset = Expression<Double>("time_offset")
private let breatheRateValue = Expression<Int64>("breath_rate")
// Table and columns for SleepInfo data
private let sleepInfoTable = Table("sleep_info")
private let sleepInfoTimeStamp = Expression<Int64>("time_stamp")
private let sleepInfoState = Expression<Int64>("state")
// Table and columns for BloodOxygen data
private let bloodOxygenTable = Table("blood_oxygen")
private let bloodOxygenTimeStamp = Expression<Int64>("time_stamp")
private let bloodOxygenType = Expression<Int64>("type")
private let bloodOxygenValue = Expression<Int64>("value")
private init() {
let path = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first!
do {
db = try Connection("\(path)/.dbtest.sqlite")
try createTables()
} catch {
fatalError("Unable to open database: \(error)")
}
}
private func createTables() throws {
// Create WorkoutInfo table
try db.run(workoutTable.create(ifNotExists: true) { t in
t.column(workoutSportID, primaryKey: true)
t.column(workoutSportType)
t.column(workoutSportName)
t.column(workoutStartTime)
t.column(workoutEndTime)
t.column(workoutTotalSteps)
t.column(workoutTotalDistance)
t.column(workoutTotalTime)
t.column(workoutTotalCalories)
t.column(workoutTotalHeight)
t.column(workoutAvgHeartRate)
t.column(workoutAvgSpeed)
t.column(workoutMaxSpeed)
t.column(workoutAvgFrequency)
t.column(workoutExtra)
})
// Create GPS table
try db.run(gpsTable.create(ifNotExists: true) { t in
t.column(gpsSportID, primaryKey: true)
t.column(gpsTimestampArray)
t.column(gpsLatitudeArray)
t.column(gpsLongitudeArray)
t.column(gpsStateArray)
t.column(gpsSpeedArray)
})
// Create HeartRate table
try db.run(heartRateTable.create(ifNotExists: true) { t in
t.column(heartRateTimeOffset)
t.column(heartRateValue)
t.column(heartRateReliability)
t.column(heartRateType)
})
// Create BreatheRate table
try db.run(breatheRateTable.create(ifNotExists: true) { t in
t.column(breatheRateTimeOffset)
t.column(breatheRateValue)
})
// Create SleepInfo table
try db.run(sleepInfoTable.create(ifNotExists: true) { t in
t.column(sleepInfoTimeStamp)
t.column(sleepInfoState)
})
// Create BloodOxygen table
try db.run(bloodOxygenTable.create(ifNotExists: true) { t in
t.column(bloodOxygenTimeStamp)
t.column(bloodOxygenType)
t.column(bloodOxygenValue)
})
}
// MARK: - 删除所有数据
func deleteAllData() throws {
try db.run(workoutTable.delete())
try db.run(gpsTable.delete())
try db.run(heartRateTable.delete())
try db.run(breatheRateTable.delete())
try db.run(sleepInfoTable.delete())
try db.run(bloodOxygenTable.delete())
}
// MARK: - WorkoutInfo Methods
func insertWorkoutInfo(_ workout: OPHealthWorkoutInfoModel) throws {
// Insert workout info
let insertWorkout = workoutTable.insert(or: .replace,
workoutSportID <- workout.sportID,
workoutSportType <- Int64(workout.sportType),
workoutSportName <- Int64(workout.sportName),
workoutStartTime <- Int64(workout.startTime),
workoutEndTime <- Int64(workout.endTime),
workoutTotalSteps <- Int64(workout.totalSteps),
workoutTotalDistance <- Int64(workout.totalDistance),
workoutTotalTime <- Int64(workout.totalTime),
workoutTotalCalories <- Int64(workout.totalCalories),
workoutTotalHeight <- Int64(workout.totalHeight),
workoutAvgHeartRate <- Int64(workout.avgHeartRate),
workoutAvgSpeed <- Int64(workout.avgSpeed),
workoutMaxSpeed <- Int64(workout.maxSpeed),
workoutAvgFrequency <- Int64(workout.avgFrequency),
workoutExtra <- workout.extra)
try db.run(insertWorkout)
// Insert GPS data
let timestampJSON = try JSONEncoder().encode(workout.gpsModel.timestamp).base64EncodedString()
let latitudeJSON = try JSONEncoder().encode(workout.gpsModel.latitude).base64EncodedString()
let longitudeJSON = try JSONEncoder().encode(workout.gpsModel.longitude).base64EncodedString()
let stateJSON = try JSONEncoder().encode(workout.gpsModel.state).base64EncodedString()
let speedJSON = try JSONEncoder().encode(workout.gpsModel.speed).base64EncodedString()
let insertGPS = gpsTable.insert(or: .replace,
gpsSportID <- workout.sportID,
gpsTimestampArray <- timestampJSON,
gpsLatitudeArray <- latitudeJSON,
gpsLongitudeArray <- longitudeJSON,
gpsStateArray <- stateJSON,
gpsSpeedArray <- speedJSON)
try db.run(insertGPS)
}
func fetchAllWorkoutInfo() throws -> [OPHealthWorkoutInfoModel] {
var result: [OPHealthWorkoutInfoModel] = []
for row in try db.prepare(workoutTable) {
let workout = OPHealthWorkoutInfoModel()
workout.sportID = row[workoutSportID]
workout.sportType = UInt32(row[workoutSportType])
workout.sportName = UInt32(row[workoutSportName])
workout.startTime = UInt32(row[workoutStartTime])
workout.endTime = UInt32(row[workoutEndTime])
workout.totalSteps = UInt32(row[workoutTotalSteps])
workout.totalDistance = UInt32(row[workoutTotalDistance])
workout.totalTime = UInt32(row[workoutTotalTime])
workout.totalCalories = UInt32(row[workoutTotalCalories])
workout.totalHeight = UInt32(row[workoutTotalHeight])
workout.avgHeartRate = UInt32(row[workoutAvgHeartRate])
workout.avgSpeed = UInt32(row[workoutAvgSpeed])
workout.maxSpeed = UInt32(row[workoutMaxSpeed])
workout.avgFrequency = UInt32(row[workoutAvgFrequency])
workout.extra = row[workoutExtra]
// Fetch GPS data for this workout
let gpsQuery = gpsTable.filter(gpsSportID == workout.sportID)
if let gpsRow = try db.pluck(gpsQuery) {
let timestampJSON = gpsRow[gpsTimestampArray]
let latitudeJSON = gpsRow[gpsLatitudeArray]
let longitudeJSON = gpsRow[gpsLongitudeArray]
let stateJSON = gpsRow[gpsStateArray]
let speedJSON = gpsRow[gpsSpeedArray]
workout.gpsModel.timestamp = try JSONDecoder().decode([UInt64].self, from: Data(base64Encoded: timestampJSON)!)
workout.gpsModel.latitude = try JSONDecoder().decode([Int32].self, from: Data(base64Encoded: latitudeJSON)!)
workout.gpsModel.longitude = try JSONDecoder().decode([Int32].self, from: Data(base64Encoded: longitudeJSON)!)
workout.gpsModel.state = try JSONDecoder().decode([UInt32].self, from: Data(base64Encoded: stateJSON)!)
workout.gpsModel.speed = try JSONDecoder().decode([Float].self, from: Data(base64Encoded: speedJSON)!)
}
result.append(workout)
}
return result
}
func fetchWorkoutInfoBySportID(_ sportID: String) throws -> OPHealthWorkoutInfoModel? {
let query = workoutTable.filter(workoutSportID == sportID)
if let row = try db.pluck(query) {
let workout = OPHealthWorkoutInfoModel()
workout.sportID = row[workoutSportID]
workout.sportType = UInt32(row[workoutSportType])
workout.sportName = UInt32(row[workoutSportName])
workout.startTime = UInt32(row[workoutStartTime])
workout.endTime = UInt32(row[workoutEndTime])
workout.totalSteps = UInt32(row[workoutTotalSteps])
workout.totalDistance = UInt32(row[workoutTotalDistance])
workout.totalTime = UInt32(row[workoutTotalTime])
workout.totalCalories = UInt32(row[workoutTotalCalories])
workout.totalHeight = UInt32(row[workoutTotalHeight])
workout.avgHeartRate = UInt32(row[workoutAvgHeartRate])
workout.avgSpeed = UInt32(row[workoutAvgSpeed])
workout.maxSpeed = UInt32(row[workoutMaxSpeed])
workout.avgFrequency = UInt32(row[workoutAvgFrequency])
workout.extra = row[workoutExtra]
// Fetch GPS data for this workout
let gpsQuery = gpsTable.filter(gpsSportID == sportID)
if let gpsRow = try db.pluck(gpsQuery) {
let timestampJSON = gpsRow[gpsTimestampArray]
let latitudeJSON = gpsRow[gpsLatitudeArray]
let longitudeJSON = gpsRow[gpsLongitudeArray]
let stateJSON = gpsRow[gpsStateArray]
let speedJSON = gpsRow[gpsSpeedArray]
workout.gpsModel.timestamp = try JSONDecoder().decode([UInt64].self, from: Data(base64Encoded: timestampJSON)!)
workout.gpsModel.latitude = try JSONDecoder().decode([Int32].self, from: Data(base64Encoded: latitudeJSON)!)
workout.gpsModel.longitude = try JSONDecoder().decode([Int32].self, from: Data(base64Encoded: longitudeJSON)!)
workout.gpsModel.state = try JSONDecoder().decode([UInt32].self, from: Data(base64Encoded: stateJSON)!)
workout.gpsModel.speed = try JSONDecoder().decode([Float].self, from: Data(base64Encoded: speedJSON)!)
}
return workout
}
return nil
}
func deleteWorkoutInfoBySportID(_ sportID: String) throws {
try db.run(workoutTable.filter(workoutSportID == sportID).delete())
try db.run(gpsTable.filter(gpsSportID == sportID).delete())
}
// MARK: - HeartRate Methods
func insertHeartRate(_ heartRate: OPHealthHeartRateModel) throws {
let insert = heartRateTable.insert(
heartRateTimeOffset <- heartRate.time_offset,
heartRateValue <- heartRate.heart_rate,
heartRateReliability <- Int64(heartRate.reliability),
heartRateType <- Int64(heartRate.type)
)
try db.run(insert)
}
func fetchAllHeartRate() throws -> [OPHealthHeartRateModel] {
var result: [OPHealthHeartRateModel] = []
for row in try db.prepare(heartRateTable) {
let heartRate = OPHealthHeartRateModel()
heartRate.time_offset = row[heartRateTimeOffset]
heartRate.heart_rate = row[heartRateValue]
heartRate.reliability = Int(row[heartRateReliability])
heartRate.type = Int(row[heartRateType])
result.append(heartRate)
}
return result
}
// MARK: - BreatheRate Methods
func insertBreatheRate(_ breatheRate: OPHealthBreatheRateModel) throws {
let insert = breatheRateTable.insert(
breatheRateTimeOffset <- breatheRate.time_offset,
breatheRateValue <- Int64(breatheRate.breath_rate)
)
try db.run(insert)
}
func fetchAllBreatheRate() throws -> [OPHealthBreatheRateModel] {
var result: [OPHealthBreatheRateModel] = []
for row in try db.prepare(breatheRateTable) {
let breatheRate = OPHealthBreatheRateModel()
breatheRate.time_offset = row[breatheRateTimeOffset]
breatheRate.breath_rate = Int(row[breatheRateValue])
result.append(breatheRate)
}
return result
}
// MARK: - SleepInfo Methods
func insertSleepInfo(_ sleepInfo: OPHealthSleepInfoModel) throws {
let insert = sleepInfoTable.insert(
sleepInfoTimeStamp <- Int64(sleepInfo.time_stamp),
sleepInfoState <- Int64(sleepInfo.state)
)
try db.run(insert)
}
func fetchAllSleepInfo() throws -> [OPHealthSleepInfoModel] {
var result: [OPHealthSleepInfoModel] = []
for row in try db.prepare(sleepInfoTable) {
let sleepInfo = OPHealthSleepInfoModel()
sleepInfo.time_stamp = Int(row[sleepInfoTimeStamp])
sleepInfo.state = Int(row[sleepInfoState])
result.append(sleepInfo)
}
return result
}
// MARK: - BloodOxygen Methods
func insertBloodOxygen(_ bloodOxygen: OPHealthBloodOxygenModel) throws {
let insert = bloodOxygenTable.insert(
bloodOxygenTimeStamp <- Int64(bloodOxygen.time_stamp),
bloodOxygenType <- Int64(bloodOxygen.type),
bloodOxygenValue <- Int64(bloodOxygen.value)
)
try db.run(insert)
}
func fetchAllBloodOxygen() throws -> [OPHealthBloodOxygenModel] {
var result: [OPHealthBloodOxygenModel] = []
for row in try db.prepare(bloodOxygenTable) {
let bloodOxygen = OPHealthBloodOxygenModel()
bloodOxygen.time_stamp = Int(row[bloodOxygenTimeStamp])
bloodOxygen.type = Int(row[bloodOxygenType])
bloodOxygen.value = Int(row[bloodOxygenValue])
result.append(bloodOxygen)
}
return result
}
}
