Class ObservableObject => Error "ORDER BY without LIMIT on DELETE (code: 1)"

[svenover]

Hi,

I use the link https://adnan-tech.com/create-read-update-and-delete-from-sqlite-swift-swift-ui/ to understand, how I have to use sqlite in my swiftui app.

All is fine with this example but with every call of the class, the init methode is called.

Therefore I switched to an observable class. So the init is called once at the app start.

@main
struct SQLiteDemoApp: App {
    let sqliteManager = SQLiteManager()
    var body: some Scene {
        WindowGroup {
            ContentView().environmentObject(sqliteManager)
        }
    }
}

struct ContentView: View {

    @EnvironmentObject var sqliteManager : SQLiteManager

    // array of user models
    @State var userModels: [UserModel] = []

    // check if user is selected for edit
    @State var userSelected: Bool = false

    // id of selected user to edit or delete
    @State var selectedUserId: Int64 = 0

    var body: some View {

        // create navigation view
        NavigationView {

            VStack {

                // create link to add user
                HStack {
                    Spacer()

                    NavigationLink (destination: AddUserView(), label: {
                        Image(systemName: "person.crop.circle.badge.plus")
                            .resizable()
                            .frame(width: 25, height: 22, alignment: .center)
                    })
                }
                .padding(.bottom, 20)

                // navigation link to go to edit user view
                NavigationLink (destination: EditUserView(id: self.$selectedUserId), isActive: self.$userSelected) {
                    EmptyView()
                }

                // create list view to show all users
                List (self.userModels) { (model) in

                    // show name, email and age horizontally
                    HStack {
                        Text(model.name)
                        Spacer()
                        Text(model.email)
                        Spacer()
                        Text("\(model.age)")
                        Spacer()

                        // button to delete user
                        Button(action: {
                            // call delete function
                            //SQLiteManager().deleteUser(idValue: model.id)
                            sqliteManager.deleteUser(idValue: model.id)  // -> doesn't work

                            // refresh the user models array
                            //self.userModels = SQLiteManager().getUsers()
                            self.userModels = sqliteManager.getUsers()  // -> works well
                        }, label: {
                            Image(systemName: "trash")
                                .resizable()
                                .frame(width: 18, height: 20, alignment: .center)
                                .foregroundColor(Color.red)
                        })
                        // by default, buttons are full width.
                        // to prevent this, use the following
                            .buttonStyle(PlainButtonStyle())
                    }
                }
                .listStyle(.plain)

            }
            .padding()
            .navigationBarTitle("SQLite")
            .onAppear(perform: {
                //self.userModels = SQLiteManager().getUsers()
                self.userModels = sqliteManager.getUsers() // -> works well
            })
        }
    }
}

With this change, the insert is working but the delete function failed with error in the title as well the update function too.

Maybe an expert, can tell me what I'm doing wrong. Many thanks in advance.

Here are my SQLite Class

final class SQLiteManager : ObservableObject {
//class SQLiteManager {

    // sqlite instance
    private var db: Connection!

    // table instance
    private var users: Table!

    // columns instances of table
    private var id: Expression<Int64>!
    private var name: Expression<String>!
    private var email: Expression<String>!
    private var age: Expression<Int64>!

    // constructor of this class
    init () {

        // exception handling
        do {

            // path of document directory
            let documentsPath = FileManager.default.urls(for: .documentDirectory, in: .userDomainMask)[0]
            let sqliteFile = documentsPath.appendingPathComponent("database").appendingPathExtension("sqlite3")

            // creating database connection
            self.db = try Connection("\(sqliteFile)")

            // creating table object
            self.users = Table("users")

            // create instances of each column
            self.id = Expression<Int64>("id")
            self.name = Expression<String>("name")
            self.email = Expression<String>("email")
            self.age = Expression<Int64>("age")

            // if not exist, create the table
            try self.db.run(self.users.create(ifNotExists: true) { (t) in
                t.column(self.id, primaryKey: true)
                t.column(self.name)
                t.column(self.email, unique: true)
                t.column(self.age)
            })

        } catch {
            // show error message if any
            print(error.localizedDescription)
        }
    }

    public func addUser(nameValue: String, emailValue: String, ageValue: Int64) {
        do {
            try self.db.run(self.users.insert(self.name <- nameValue, self.email <- emailValue, self.age <- ageValue))
        } catch {
            print(error.localizedDescription)
        }
    }

    // function to update user
    public func updateUser(idValue: Int64, nameValue: String, emailValue: String, ageValue: Int64) {
        do {
            // get user using ID
            let user: Table = self.users.filter(self.id == idValue)

            // run the update query
            try self.db.run(user.update(self.name <- nameValue, self.email <- emailValue, self.age <- ageValue))
        } catch {
            print(error.localizedDescription)
            print(error)
        }
    }

    // function to delete user
    public func deleteUser(idValue: Int64) {
        do {
            // get user using ID
            let user: Table = self.users.filter(self.id == idValue)

            // run the delete query
            try self.db.run(user.delete())
        } catch {
            print(error.localizedDescription)
            print(error)
        }
    }
}

Many thanks for the help. Sven

[svenover]

Hi,

I add the following line in the update and delete function - all is working, why? self.users = Table("users")

When I use Table("users") instead of self.users all is working well.

    // function to delete user
    public func deleteUser(idValue: Int64) {
        do {
            // get user using ID
            let user: Table = Table("users").filter(id == idValue)

            // run the delete query
            try db.run(user.delete())
        } catch {
            print(error.localizedDescription)
            print(error)
        }
    }

Sorry, I don't understand it. The users object (table) is initiated at startup and when I check the count of it, it tells me the exact row count.

Strange.

Here both function.

    // function to update user
    public func updateUser(idValue: Int64, nameValue: String, emailValue: String, ageValue: Int64) {
        do {
            self.users = Table("users")

            // get user using ID
            let user: Table = self.users.filter(self.id == idValue)

            // run the update query
            try self.db.run(user.update(self.name <- nameValue, self.email <- emailValue, self.age <- ageValue))
        } catch {
            print(error.localizedDescription)
            print(error)
        }
    }

    // function to delete user
    public func deleteUser(idValue: Int64) {
        do {
            self.users = Table("users")

            // get user using ID
            let user: Table = self.users.filter(id == idValue)

            // run the delete query
            try db.run(user.delete())
        } catch {
            print(error.localizedDescription)
            print(error)
        }
    }

[svenover]

I found the issue. I have a function which gets all the users. Inside this function I do a sort / order of the users. This sort / order is the problem. Now, I do it in another way and now all is working.