@interface DBManager : NSObject
@property (nonatomic) sqlite3 *database;
- (BOOL)openDatabase;
- (BOOL)createTables;
- (BOOL)insertReport:(NSString *)time title:(NSString *)title cal:(NSString *)cal runtime:(NSString *)runtime;
- (NSArray *)queryAllReports;
@end
- (BOOL)openDatabase {
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *docDir = paths.firstObject;
NSString *path = [docDir stringByAppendingPathComponent:@"mydb.sqlite"];
NSFileManager *fm = [NSFileManager defaultManager];
BOOL exists = [fm fileExistsAtPath:path];
if (sqlite3_open([path UTF8String], &_database) != SQLITE_OK) {
sqlite3_close(_database);
NSLog(@"❌ 打開數據庫失敗:%s", sqlite3_errmsg(_database));
return NO;
}
if (!exists) {
return [self createTables];
}
return YES;
}
- (BOOL)createTables {
const char *sql = "CREATE TABLE IF NOT EXISTS reports ("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"stime TEXT, stitle TEXT, scal TEXT, sruntime TEXT)";
char *errMsg = nil;
if (sqlite3_exec(_database, sql, NULL, NULL, &errMsg) != SQLITE_OK) {
NSLog(@"❌ 建表失敗:%s", errMsg);
sqlite3_free(errMsg);
return NO;
}
NSLog(@"✅ 表創建成功");
return YES;
}
- (BOOL)insertReport:(NSString *)time title:(NSString *)title cal:(NSString *)cal runtime:(NSString *)runtime {
const char *sql = "INSERT INTO reports (stime, stitle, scal, sruntime) VALUES (?, ?, ?, ?)";
sqlite3_stmt *stmt;
if (sqlite3_prepare_v2(_database, sql, -1, &stmt, NULL) != SQLITE_OK) {
NSLog(@"❌ 準備語句失敗:%s", sqlite3_errmsg(_database));
return NO;
}
sqlite3_bind_text(stmt, 1, [time UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 2, [title UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 3, [cal UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 4, [runtime UTF8String], -1, SQLITE_TRANSIENT);
BOOL success = (sqlite3_step(stmt) == SQLITE_DONE);
sqlite3_finalize(stmt);
if (!success) NSLog(@"❌ 插入失敗:%s", sqlite3_errmsg(_database));
return success;
}
- (NSArray *)queryAllReports {
const char *sql = "SELECT id, stime, stitle, scal, sruntime FROM reports ORDER BY id DESC";
sqlite3_stmt *stmt;
if (sqlite3_prepare_v2(_database, sql, -1, &stmt, NULL) != SQLITE_OK) return nil;
NSMutableArray *results = [NSMutableArray array];
while (sqlite3_step(stmt) == SQLITE_ROW) {
NSDictionary *row = @{
@"id": @(sqlite3_column_int(stmt, 0)),
@"time": @(sqlite3_column_text(stmt, 1) ? [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)] : @""),
@"title": @(sqlite3_column_text(stmt, 2) ? [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)] : @""),
@"cal": @(sqlite3_column_text(stmt, 3) ? [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)] : @""),
@"runtime": @(sqlite3_column_text(stmt, 4) ? [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 4)] : @"")
};
[results addObject:row];
}
sqlite3_finalize(stmt);
return results;
}
- (void)closeDatabase {
if (_database) {
sqlite3_close(_database);
_database = NULL;
}
}
- (void)dealloc { [self closeDatabase]; }
// FMDB 示例(OC)
FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:path];
[queue inDatabase:^(FMDatabase *db) {
[db executeUpdate:@"INSERT INTO reports (stime, stitle) VALUES (?, ?)", time, title];
FMResultSet *rs = [db executeQuery:@"SELECT * FROM reports"];
while ([rs next]) { NSLog(@"%@", rs.resultDictionary); }
}];
// GRDB (Swift) - 類型安全、Codable 支援、異步安全
let dbQueue = try DatabaseQueue(path: path)
try dbQueue.write { db in
try db.execute(sql: "INSERT INTO reports (stime, stitle) VALUES (?, ?)", arguments: [time, title])
}
let reports = try dbQueue.read { db in
try Report.fetchAll(db, sql: "SELECT * FROM reports")
}
原生 SQLite API 適用於極簡依賴場景;生產項目強烈建議使用成熟封裝庫。