SwiftUI应用中SQLite执行事务后间歇性出现无法打开数据库文件错误

API 探路者

2026-7-23

问题描述

在SwiftUI应用中使用SQLite存储数据，安装后可正常连接数据库并执行少量插入、读取事务，但执行约8-12次事务后，开始间歇性出现「unable to open database file」错误。已尝试仅在应用启动时调用一次openDatabase()方法，问题仍未解决。

数据库连接代码

class DBHelper
{
    init()
    {
        db = openDatabase()
        createTable()
    }
    
    let dbPath: String = "myDb.sqlite"
    var db:OpaquePointer?
    
    func openDatabase() -> OpaquePointer?
    {
        let fileURL = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
                    .appendingPathComponent(dbPath)
        var db: OpaquePointer? = nil
        print("opened database")
        
        //=================================================================
        if sqlite3_open(fileURL.path, &db) != SQLITE_OK
        {
            let errmsg = String(cString: sqlite3_errmsg(db))
            print("error opening database = \(errmsg)")
            sqlite3_close(db)
            db = nil
            return nil
        }
        else
        {
            //print("db connection opened at = \(documentUrlPath)")
            return db
        }
    }
    
    func createTable() {
        let createNoteTableString = "CREATE TABLE IF NOT EXISTS note (id TEXT PRIMARY KEY,datetime INTEGER,title TEXT, description BLOB,type TEXT,theme TEXT, font TEXT, folderId TEXT, isBookmarked INTEGER);"
        var createNoteTableStatement: OpaquePointer? = nil
        if sqlite3_prepare_v2(db, createNoteTableString, -1, &createNoteTableStatement, nil) == SQLITE_OK
        {
            if sqlite3_step(createNoteTableStatement) == SQLITE_DONE
            {
                //print("note table created.")
            } else {
                //print(" note table could not be created.")
            }
        } else {
            //print("CREATE note TABLE statement could not be prepared.")
        }
        sqlite3_finalize(createNoteTableStatement)
    } 

    func insert(id:String, datetime:Int64, title:String, description:NSAttributedString, type:String, theme:String, font:String, folderId:String, isBookmarked:Bool)
    {
        let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
        //let data: NSData = NSKeyedArchiver.archivedData(withRootObject: description) as NSData
        var isBookmarkedInt = isBookmarked ? 1 : 0;
        let notes = readNotes()
        for p in notes
        {
            if p.id == id
            {
                return
            }
        }
        //insertContent(id: UUID().uuidString, content: description, parentId: id)
        
        let insertStatementString = "INSERT INTO note (id, datetime, title, description, type, theme, font, folderId, isBookmarked) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);"
        var insertStatement: OpaquePointer? = nil
        if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {
            sqlite3_bind_text(insertStatement, 1, (id as NSString).utf8String, -1, nil)
            sqlite3_bind_int64(insertStatement, 2, Int64(datetime))
            sqlite3_bind_text(insertStatement, 3, (title as NSString).utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 4, (description.string as NSString).utf8String, -1, nil)
            //sqlite3_bind_blob (insertStatement, 4, data.bytes, Int32(data.length), SQLITE_TRANSIENT);
            sqlite3_bind_text(insertStatement, 5, (type as NSString).utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 6, (theme as NSString).utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 7, (font as NSString).utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 8, (folderId as NSString).utf8String, -1, nil)
            sqlite3_bind_int(insertStatement, 9, Int32(isBookmarkedInt))
            
            if sqlite3_step(insertStatement) == SQLITE_DONE {
                //print("Successfully inserted row.")
            } else {
                //print("Could not insert row.")
            }
        } else {
            //print("INSERT statement could not be prepared.")
        }
        sqlite3_finalize(insertStatement)
    }
    
}

错误日志

2023-05-02 18:36:51.014049+0530 IQNote[5059:149954] [logging-persist] cannot open file at line 46922 of [554764a6e7]
2023-05-02 18:36:51.014160+0530 IQNote[5059:149954] [logging-persist] os_unix.c:46922: (0) open(/Users/nitish/Library/Developer/CoreSimulator/Devices/B08CC4B2-EE5E-41A9-A587-F265C33C209E/data/Containers/Data/Application/4FFBD6B1-CFA7-400E-95FD-9F7076AB280F/Documents/myDb.sqlite) - Undefined error: 0
2023-05-02 18:36:51.014510+0530 IQNote[5059:149954] [logging] API call with NULL database connection pointer
2023-05-02 18:36:51.014563+0530 IQNote[5059:149954] [logging] misuse at line 143209 of [554764a6e7]
2023-05-02 18:36:51.014603+0530 IQNote[5059:149954] [logging] API call with NULL database connection pointer
2023-05-02 18:36:51.014637+0530 IQNote[5059:149954] [logging] misuse at line 143209 of [554764a6e7]
2023-05-02 18:36:51.014674+0530 IQNote[5059:149954] [logging] API call with NULL database connection pointer
2023-05-02 18:36:51.014721+0530 IQNote[5059:149954] [logging] misuse at line 143209 of [554764a6e7]

解决方案

问题根源在于连接管理混乱和线程不安全操作，以下是修复后的代码及关键修改点：

修复后代码

import Foundation
import SQLite3

class DBHelper {
    // 单例模式：确保全局仅一个数据库连接实例
    static let shared = DBHelper()
    
    let dbPath: String = "myDb.sqlite"
    var db: OpaquePointer?
    // 串行队列：强制所有数据库操作在同一线程执行，避免线程安全问题
    private let dbQueue = DispatchQueue(label: "com.yourapp.db.queue")
    
    private init() {
        dbQueue.sync {
            db = openDatabase()
            createTable()
        }
    }
    
    private func openDatabase() -> OpaquePointer? {
        let fileManager = FileManager.default
        // 确保Documents目录存在，create参数改为true
        let documentsDir = try! fileManager.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
        let fileURL = documentsDir.appendingPathComponent(dbPath)
        
        var db: OpaquePointer? = nil
        if sqlite3_open(fileURL.path, &db) != SQLITE_OK {
            if let errPtr = sqlite3_errmsg(db) {
                let errmsg = String(cString: errPtr)
                print("error opening database = \(errmsg)")
            }
            sqlite3_close(db)
            return nil
        }
        return db
    }
    
    // 每次操作前检查连接有效性，失效则重新打开
    private func ensureValidConnection() {
        if db == nil {
            db = openDatabase()
            createTable()
        }
    }
    
    func createTable() {
        guard db != nil else { return }
        
        let createNoteTableString = "CREATE TABLE IF NOT EXISTS note (id TEXT PRIMARY KEY,datetime INTEGER,title TEXT, description BLOB,type TEXT,theme TEXT, font TEXT, folderId TEXT, isBookmarked INTEGER);"
        var createNoteTableStatement: OpaquePointer? = nil
        
        if sqlite3_prepare_v2(db, createNoteTableString, -1, &createNoteTableStatement, nil) == SQLITE_OK {
            if sqlite3_step(createNoteTableStatement) == SQLITE_DONE {
                print("note table created or already exists.")
            } else {
                print("note table could not be created.")
            }
        } else {
            print("CREATE note TABLE statement could not be prepared.")
        }
        sqlite3_finalize(createNoteTableStatement)
    }
    
    func insert(id: String, datetime: Int64, title: String, description: NSAttributedString, type: String, theme: String, font: String, folderId: String, isBookmarked: Bool) {
        dbQueue.async { [weak self] in
            guard let self = self else { return }
            self.ensureValidConnection()
            guard self.db != nil else {
                print("Database connection is nil, cannot insert")
                return
            }
            
            // 用SQL查询替代全表扫描，提升重复ID检查效率
            let existsQuery = "SELECT id FROM note WHERE id = ?;"
            var existsStatement: OpaquePointer? = nil
            var exists = false
            
            if sqlite3_prepare_v2(self.db, existsQuery, -1, &existsStatement, nil) == SQLITE_OK {
                sqlite3_bind_text(existsStatement, 1, (id as NSString).utf8String, -1, SQLITE_TRANSIENT)
                if sqlite3_step(existsStatement) == SQLITE_ROW {
                    exists = true
                }
            }
            sqlite3_finalize(existsStatement)
            
            if exists {
                return
            }
            
            let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
            let isBookmarkedInt = isBookmarked ? 1 : 0
            
            let insertStatementString = "INSERT INTO note (id, datetime, title, description, type, theme, font, folderId, isBookmarked) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);"
            var insertStatement: OpaquePointer? = nil
            
            if sqlite3_prepare_v2(self.db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {
                sqlite3_bind_text(insertStatement, 1, (id as NSString).utf8String, -1, SQLITE_TRANSIENT)
                sqlite3_bind_int64(insertStatement, 2, datetime)
                sqlite3_bind_text(insertStatement, 3, (title as NSString).utf8String, -1, SQLITE_TRANSIENT)
                
                // 修复NSAttributedString存储：用Blob归档存储完整富文本
                let data = try! NSKeyedArchiver.archivedData(withRootObject: description, requiringSecureCoding: false)
                sqlite3_bind_blob(insertStatement, 4, data.bytes, Int32(data.count), SQLITE_TRANSIENT)
                
                sqlite3_bind_text(insertStatement, 5, (type as NSString).utf8String, -1, SQLITE_TRANSIENT)
                sqlite3_bind_text(insertStatement, 6, (theme as NSString).utf8String, -1, SQLITE_TRANSIENT)
                sqlite3_bind_text(insertStatement, 7, (font as NSString).utf8String, -1, SQLITE_TRANSIENT)
                sqlite3_bind_text(insertStatement, 8, (folderId as NSString).utf8String, -1, SQLITE_TRANSIENT)
                sqlite3_bind_int(insertStatement, 9, Int32(isBookmarkedInt))
                
                if sqlite3_step(insertStatement) == SQLITE_DONE {
                    print("Successfully inserted row.")
                } else {
                    print("Could not insert row.")
                }
            } else {
                print("INSERT statement could not be prepared.")
            }
            sqlite3_finalize(insertStatement)
        }
    }
    
    // 示例读取方法，同样在串行队列执行
    func readNotes() -> [Note] {
        var notes = [Note]()
        dbQueue.sync { [weak self] in
            guard let self = self else { return }
            self.ensureValidConnection()
            guard self.db != nil else { return }
            
            let query = "SELECT id, datetime, title, description, type, theme, font, folderId, isBookmarked FROM note;"
            var statement: OpaquePointer? = nil
            
            if sqlite3_prepare_v2(self.db, query, -1, &statement, nil) == SQLITE_OK {
                while sqlite3_step(statement) == SQLITE_ROW {
                    let id = String(cString: sqlite3_column_text(statement, 0))
                    let datetime = sqlite3_column_int64(statement, 1)
                    let title = String(cString: sqlite3_column_text(statement, 2))
                    
                    let blobBytes = sqlite3_column_blob(statement, 3)
                    let blobLength = sqlite3_column_bytes(statement, 3)
                    let data = Data(bytes: blobBytes!, count: Int(blobLength))
                    let description = try! NSKeyedUnarchiver.unarchiveTopLevelObjectWithData(data) as! NSAttributedString
                    
                    let type = String(cString: sqlite3_column_text(statement, 4))
                    let theme = String(cString: sqlite3_column_text(statement, 5))
                    let font = String(cString: sqlite3_column_text(statement, 6))
                    let folderId = String(cString: sqlite3_column_text(statement, 7))
                    let isBookmarked = sqlite3_column_int(statement, 8) == 1
                    
                    let note = Note(id: id, datetime: datetime, title: title, description: description, type: type, theme: theme, font: font, folderId: folderId, isBookmarked: isBookmarked)
                    notes.append(note)
                }
            }
            sqlite3_finalize(statement)
        }
        return notes
    }
    
    // 销毁时关闭数据库连接
    deinit {
        if db != nil {
            sqlite3_close(db)
            db = nil
        }
    }
}

// 配套Note模型
struct Note {
    let id: String
    let datetime: Int64
    let title: String
    let description: NSAttributedString
    let type: String
    let theme: String
    let font: String
    let folderId: String
    let isBookmarked: Bool
}

关键修改点

单例模式：全局仅一个DBHelper实例，避免多次创建导致的连接冲突。
串行队列：所有数据库操作在同一线程执行，解决SQLite连接非线程安全的问题。
连接有效性检查：每次操作前确认连接是否可用，失效则自动重连。
优化重复ID检查：用SQL查询替代全表扫描，提升性能并减少操作耗时。
修复富文本存储：将NSAttributedString归档为Data后用Blob存储，避免纯文本丢失格式。
正确使用SQLITE_TRANSIENT：绑定字符串时传入该参数，确保SQLite复制内容，避免内存释放引发的异常。

内容的提问来源于stack exchange，提问作者nitish

SwiftUI应用中SQLite执行事务后间歇性出现无法打开数据库文件错误

数据库连接代码 #

错误日志 #

解决方案 #

修复后代码 #

关键修改点 #

数据库连接代码

错误日志

解决方案

修复后代码

关键修改点