swiftuifmdb

How to use values in .onDelete to delete FMDB data


I'm a Swift / SwiftUI newbie trying to integrate FMDB tables I've built into my app. I have been able to populate my list with a direct call to the FMDB tables and now want to be able to delete an item from the list and the corresponding underlying data in the FMDB table. I can't seem to figure out how to get the underlying row data to be able to run the SQL to delete the corresponding table data. In the Class for the TABLE I have:

// Budgeted_expense record for FMDB
struct BudgetedExpenseRecord: Hashable {
    var expense_id: Int!
    var account_code: Int!
    var budget_code: Int!
    var expense_code: Int!
    var budget_year: Int!
    var budget_month: Int!
    var description: String!
    var category: String!
    var expense_budget: Double!
    var expense_spent: Double!
    var unexpected_expense: Double!
    var category_code: Int!
    var hidden: Bool!
}

The call to the DBMANAGER is:

// Select BudgetedExpense records for a given year / month
func selectBudgetedExpense(account_code: Int, budget_code: Int, year: Int, month: Int) -> [BudgetedExpense.BudgetedExpenseRecord]
{
    var budgeted_expense_results: [BudgetedExpense.BudgetedExpenseRecord]
    if openDatabase(){
        budgeted_expense_results = BudgetedExpense.shared.selectBudgetedExpense(database: database, query: BudgetedExpense.shared.selectBudgetedExpense(account_code: account_code, budget_code: budget_code, year: year, month: month))
        if checkTables {
            print("\nQUERY: BudgetedExpense.shared.selectBudgetedExpense(account_code: \(account_code), budget_code: \(budget_code), year: \(year), month: \(month)")
            print(BudgetedExpense.shared.selectBudgetedExpense(account_code: account_code, budget_code: budget_code, year: year, month: month))
        }
        database.close()
        return budgeted_expense_results
    }else {
        print("Database was not opened.  Couldn't execute the query.  Returning empty results")
        fatalError("Database was not opened.  Couldn't execute the query.  Returning empty results")
    }
}

In my SwiftUI view I can populate the list as below:

                List {
                    ForEach (DBManager.shared.selectBudgetedExpense(account_code: self.account_code, budget_code: self.budget_code, year:  self.budget_year, month: self.budget_month), id: \.self) { record in
                        BudgetedExpenseEditRow(
                            expense_id: record.expense_id,
                            account_code: record.account_code,
                            budget_code: record.budget_code,
                            budget_year: record.budget_year,
                            budget_month: record.budget_month,
                            description: record.description,
                            category: record.category,
                            expense_budget: roundToPlaces(value: record.expense_budget, 
                               places: 3),
                            expense_spent: record.expense_spent,
                            unexpected_expense: record.unexpected_expense,
                            hidden: record.hidden
                        )
                    }
                    .onDelete(perform: self.delete)

                    
                }.navigationBarTitle("Budgeted Expenses")

I haven't been able to figure out how onDelete to get the expense_id which is a unique row id in the table so that I can call a delete SQL?


Solution

  • Thanks to the comment above I was able to solve the problem I was having. I put the db call into a local array:

    var budgetedExpenses: [BudgetedExpense.BudgetedExpenseRecord] { return DBManager.shared.selectBudgetedExpense(account_code: self.account_code, budget_code: self.budget_code, year: self.budget_year, month: self.budget_month)

    }
    

    then I used that array to populate the view.

                    List {
                        ForEach (self.budgetedExpenses, id: \.self) { record in
                            BudgetedExpenseRow(
                                expense_id: record.expense_id,
                                account_code: record.account_code,
                                budget_code: record.budget_code,
                                expense_code: record.expense_code,
                                budget_year: record.budget_year,
                                budget_month: record.budget_month,
                                description: record.description,
                                category: record.category,
                                expense_budget: roundToPlaces(value: record.expense_budget, places: 3),
                                expense_spent: record.expense_spent,
                                unexpected_expense: record.unexpected_expense,
                                hidden: record.hidden
                            )
                        }
                        
                    }.navigationBarTitle("Budgeted Expenses")
    

    and in the onDelete I was able to get the data that I needed.

    func delete(at offsets: IndexSet) {
        offsets.forEach { index in
          let budgetExpense = self.budgetedExpenses[index]
            
            let queryString = ExpenseTransactions.shared.deleteExpenseTransactionsForYearQuery(acctcode: budgetExpense.account_code!, budgetcode: budgetExpense.budget_code!, expensecode: budgetExpense.expense_code!, year: budgetExpense.budget_year!) + "\n" +
                BudgetedExpenseDetails.shared.deleteBudgetedExpenseDetailsForYearQuery(acctcode: budgetExpense.account_code!, budgetcode: budgetExpense.budget_code!, expensecode: budgetExpense.expense_code!, year: budgetExpense.budget_year!) + "\n" +
                BudgetedExpense.shared.deleteBudgetedExpenseForYearQuery(acctcode: budgetExpense.account_code!, budgetcode: budgetExpense.budget_code!, expensecode: budgetExpense.expense_code!, year: budgetExpense.budget_year!) + "\n" +
                DBManagerSupport.shared.rollupBudgetTotals()
            
            if !DBManager.shared.executeSQLStatements(query: queryString)
            {
                print("Failed to delete table data")
            }
            var budget_result: Budget.BudgetRecord
               budget_result = DBManager.shared.selectBudgetYearMonth(acctCode: self.account_code, budgetCode: self.budget_code, budgetYear: self.budget_year, budgetMonth: self.budget_month)
                       self.total_budgeted_expense = budget_result.expected_expense
                       self.total_expense_spent = budget_result.expected_expense_spent
                       self.total_unexpected_expense = budget_result.unexpected_expense
            self.recordDeleted = true
            self.isEditing = false
        }
    }