Foreign key column on table will not update

Hey all. So I have an application where on a certain view controller I call a few static methods which updates certain columns on certain tables. For some reason this one column just will not update not matter how many things ive tried.

Here is the method:

func addSession() {
        if let days = locationInputView?.getDays(), let hours = locationInputView?.getHours(), let locationName = locationInputView?.getLocationName() {
            locationInputView?.selectedDays = [days]
            locationInputView?.selectedHours = [hours]
            
            setHooModel(model: HoursOfOperation.insertAndInjectNewHoo()) // TODO: Write this method as two seperate methods and apply to this VC appropriately 
            
            if let view = locationInputView {
                if let locationModel = locationModel {
                    if let hooModel = hooModel {
                        HoursOfOperation.setHooDaysHours(hooid: hooModel.id, selectedDays: view.selectedDays, selectedHours: view.selectedHours)
                        updateHooModel(model: hooModel)
                        
                        Location.setLocationNameFromid(locationName: locationName, id: locationModel.id)
                        Location.setHooidForLocation(locationid: locationModel.id, hooid: hooModel.id)
                        updateLocationModel(model: locationModel)
                        print("add session: \(self.locationModel)")
                    } else {
                        print("1: Optional found nil")
                    }
                } else {
                    print("2: Optional found nil")
                }
            } else {
                print("3: Optional found nil")
            }
        } else {
            print("4: Optional found nil")
        }
    }

The updateLocationModel() and like methods simply retrieves the data from the db and re injects them into my data models

static func setHooidForLocation(locationid: String, hooid: String) {
        do {
            try AppDatabase.shared.dbwriter.write({ db in
                if var location = try Location.fetchOne(db, key: locationid) {
                    location.hooid = hooid
                    
                    if location.hooid != nil {
                        try location.update(db)
                        //try db.execute(sql: "UPDATE location SET hooid = ? WHERE id = ?", arguments: [hooid, locationid]) // FIXME: Write with raw SQL and hope for the best
                    }
                }
            })
        } catch {
            fatalError()
        }
    }

and finally the relevant pieces of the schema:

try db.create(table: "Location", body: { t in
                t.column("id", .text).primaryKey()
                t.column("locationName", .text)
                t.column("addressString", .text).notNull()
                t.column("latitude", .double).notNull()
                t.column("longitude", .double).notNull()
                t.column("hooid", .text).references("HoursOfOperation", column: "id", onDelete: nil, onUpdate: nil, deferred: false)
try db.create(table: "HoursOfOperation", body: { t in
                t.column("id", .text).primaryKey()
                t.column("dayOfWeek", .text).notNull()
                t.column("timeOfDay", .text).notNull()
            })

Things I have tried:

  • writing raw SQL for the update
  • ensuring ALL optionals are not nil, unwrapped and have expected values
  • changing all created db connections to dbQueue from dbPool in case of concurrency issues
  • moving setHooidForLocation() to another method in case of issues involving scope

I should mention I do not and never have had any problems with updating columns with or without foreign keys relating to other parts of my db structure.

Hello @WorldsGreatestDetectiv,

For some reason this one column just will not update not matter how many things ive tried.

All right. I have three questions:

Does your app actually execute any update? Can you set a breakpoint on the like that performs the update, and see the debugger stop on this line?

What tells you the update does not apply?

Did you try tracing executed SQL statements?

1 Like

Ok so just to be safe I did indeed set a breakpoint at the location.update(db) call and the update was executed. Now as far as the trace goes here is the two calls of relevance with the first showing the update and then the second showing the select statement to retrieve the entry. After add session: we have the printed entry in swift;

BEGIN DEFERRED TRANSACTION
SELECT * FROM "location" WHERE "id" = 'F6985E60-DA47-4811-A3A8-16E30FCF32A0' UPDATE "location" SET "locationName"='PLACE', "addressString"='sample address, city, state', "latitude"=0.0, "longitude"=0.0, "hooid"='A6B5B970-C6B9-4C2A-B20F-CCE8F4B2DC51' WHERE "id"='F6985E60-DA47-4811-A3A8-16E30FCF32A0'
COMMIT TRANSACTION                                 
                         
SELECT * FROM "location" WHERE "id" = 'F6985E60-DA47-4811-A3A8-16E30FCF32A0'
COMMIT TRANSACTION
add session: Optional(Ikigai.Location(id: "F6985E60-DA47-4811-A3A8-16E30FCF32A0", locationName: Optional("PLACE"), addressString: "sample address, city, state", latitude: Optional(0.0), longitude: Optional(0.0), hooid: nil))

I suppose I should print the table row itself to see if hooid remained null. Hopefully this is not the result of a simple oversight or flimsy schema, however this seems not to be the case.

I see an UPDATE statement and a COMMIT, so the update looks like it was performed (or you should get an error).

I suppose I should print the table row itself to see if hooid remained null.

Sure! When debugging, grabbing pieces of evidence is always helpful.

A simple print can help:

let row = try Row.fetchOne(db, sql: "SELECT * FROM location WHERE id = ?", arguments: [locationid])
print(row)
1 Like

Ok so upon printing the table entry we get:

SELECT * FROM location WHERE id = '3D2C3856-ABE4-4B37-94AB-68E723BC4181'
Optional([id:"3D2C3856-ABE4-4B37-94AB-68E723BC4181" locationName:"Zicro " addressString:"2110 high road , Tallahassee, FL" latitude:0.0 longitude:0.0 hooid:"66632479-9C4A-47F4-B829-A7B86E5ED359"])

However a potentially more interesting bug I noticed when navigating to this view controller where HoursOfOperation.insertAndInjectNewHoo() is called in viewDidLoad();

BEGIN DEFERRED TRANSACTION
PRAGMA main.table_xinfo("hoursOfOperation")
INSERT INTO "hoursOfOperation" ("id", "dayOfWeek", "timeOfDay") VALUES ('66632479-9C4A-47F4-B829-A7B86E5ED359','','')
COMMIT TRANSACTION
error: Error Domain=kCLErrorDomain Code=2 "(null)"

Here is HoursOfOperation.insertAndInjectNewHoo():

static func insertAndInjectNewHoo() -> HoursOfOperation {
        let hoo = HoursOfOperation(id: HoursOfOperation.setid(), dayOfWeek: "", timeOfDay: "")
        
        do {
            try AppDatabase.shared.dbwriter.write({ db in
                try hoo.insert(db)
            })
        } catch {
            fatalError("error: ")
        }
        return hoo
    }

kCLErrorDomain is a CoreLocation error. Maybe we should check the documentation of this Apple framework.

As for GRDB errors, which can reveal file system errors, or database misuses, I would recommend that you do not hide them as you currently do:

do {
  try ...
}  catch {
  fatalError("error: ")
}

This completely prevents you from understanding what's wrong, and fix the error accordingly.

I know that errors are scary, and maybe you don't want to be blamed for creating them. Yet the only way to prevent them from hurting you or your app is to understand them, and for that you need to look at errors. So please prefer, from now on, one of those patterns:

// Error will be visible in the crash log:
try! ...

// Error will be visible in the crash log:
do {
  try ...
} catch {
  fatalError("error: \(error)")
}

// Error is handled
do {
  try ...
} catch {
  // handle error
  ...
}

// Error bubbles up
try ...

This will greatly help you:

  • read error messages
  • do Google searches and find what are those errors, and how to deal with them
  • ask questions with detailed information, thus increasing the chances that people can help you
  • handle errors in the appropriate way (maybe show an alert to the user, or any other relevant way, depending of the needs of your app)
  • become a developer able to deal with errors, which is a very useful skill
2 Likes

Thank you. Yes error handling as a whole so far has been a pretty neglected subject for me. I think that some thorough reading and application of its section in the swift programming guide as well as its section in your GRDB manual will do. I will spend some time doing as you suggested.