Swift中同时删除TableView与SQLite3数据的问题求助

Hey there, let's work through this delete issue you're facing! The core problem here is that you're not keeping your in-memory data source (mainDelegate.people) in sync with both the SQLite database and the TableView. Let's break down what's wrong and how to fix it:

Why Your Current Code Isn't Working #

• When you call mainDelegate.removeFromDatabase() first, you delete the data from the database but leave mainDelegate.people unchanged. So when you call reloadData(), the TableView still uses the original array and shows the old rows.
• If you tried deleting from the array first then calling deleteRows, the error likely happened because you didn't properly wrap the TableView update in the main thread, or there was a mismatch between the array state and the TableView's expected state.

Step-by-Step Solution #

The correct order of operations should be:

1. Update your in-memory data source (remove the item from mainDelegate.people)
2. Update the TableView (animate the row deletion)
3. Persist the change to SQLite (delete from the database)

This way, the UI updates immediately for the user, and the data is saved permanently afterward.

Modified tableView(_:commit:forRowAt:) Method #

Replace your current commit editing method with this:

func tableView(_ tableView: UITableView, commit editingStyle: UITableViewCell.EditingStyle, forRowAt indexPath: IndexPath) {
    if editingStyle == .delete {
        let rowNum = indexPath.row
        let personId = mainDelegate.people[rowNum].id!
        
        // Step 1: Remove from in-memory data source
        mainDelegate.people.remove(at: rowNum)
        
        // Step 2: Update TableView on main thread (critical!)
        DispatchQueue.main.async {
            tableView.deleteRows(at: [indexPath], with: .fade)
        }
        
        // Step 3: Delete from SQLite database
        mainDelegate.removeFromDatabase(id: personId)
    }
}

Fixing the removeFromDatabase Method (Bonus: Avoid SQL Injection) #

Your current SQL statement uses string interpolation, which is a security risk (SQL injection). Let's rewrite it to use SQLite parameter binding instead:

func removeFromDatabase(id: Int) {
    var db: OpaquePointer? = nil
    guard sqlite3_open(databasePath, &db) == SQLITE_OK else {
        print("Failed to open database")
        return
    }
    
    defer {
        sqlite3_close(db) // Ensure database is closed even if something fails
    }
    
    let deleteStatementString = "DELETE FROM entries WHERE id = ?"
    var deleteStatement: OpaquePointer? = nil
    
    guard sqlite3_prepare_v2(db, deleteStatementString, -1, &deleteStatement, nil) == SQLITE_OK else {
        print("Couldn't prepare delete statement: \(String(cString: sqlite3_errmsg(db)!))")
        sqlite3_finalize(deleteStatement)
        return
    }
    
    // Bind the id parameter (1 is the index of the ? in the SQL string)
    sqlite3_bind_int(deleteStatement, 1, Int32(id))
    
    if sqlite3_step(deleteStatement) == SQLITE_DONE {
        print("Successfully deleted entry with id: \(id)")
    } else {
        print("Failed to delete entry: \(String(cString: sqlite3_errmsg(db)!))")
    }
    
    sqlite3_finalize(deleteStatement)
}

Key Notes to Remember #

• Always keep your data source in sync: The TableView's data comes directly from mainDelegate.people, so any change to the database must be reflected in this array (or vice versa).
• UI updates must happen on the main thread: Even if you're already in a delegate method, wrapping TableView updates in DispatchQueue.main.async ensures it runs on the correct thread.
• Avoid SQL injection: Using parameter binding instead of string interpolation protects your app from malicious input and syntax errors.

If you still run into issues, double-check that mainDelegate.people is being modified correctly (print its count before and after deletion to verify).

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