Had a chat with Robert about this today. Moodle gets around this problem by forcing their update_record() method to use the "id" column to locate the record. We can't do that because we have some tables without an "id". And even then, sometimes it can be useful to run "update_record" without an ID even in cases where the table *does* have an ID. For instance, if we know some set of unique identifying information about the record, but not its ID, then this using update_record without ID can save us from having to do an extra SELECT query just to find the ID.
As Robert alluded to, the only downside to updating the WHERE columns as well, is that Postgres will do a "write" operation even if the value that you're setting via your UPDATE query is exactly the same as the old value. This can potentially cause a trigger to run, or a record to have to get re-indexed, which is an unnecessary expense, but not the end of the world. So, I think what we should do is a slight shift in the API for update_record(), which will probably bring it more in line with how developers would expect it to function anyway. This function takes three arguments: update_record($table, $dataobject, $where=null). $table is the table to update, $dataobject is the data to insert, and $where is an optional parameter that indicates which records should be updated. If $where is ommitted, then the function assumes that $dataobject contains an "id" field, and it tries to use that to identify the record to update. The $where parameter is multi-purpose. It can be: 1. A string, in which case it should hold the name of a column in $dataobject, and that column should be used to identify the record to update. 2. An array of strings, in which case it identifies multiple columns in $dataobject to use, to identify the record in the database 3. A hash of key => value pairs, in which case it represents pretty much just a normal "where" clause, where each key is a column name, and each value is the value it should match. The problem behavior here is caused, because any columns you identify in $where, via any of its three forms, are removed from $dataobject and not updated in the DB. My proposed fix is that in case #3, we don't remove the columns from $dataobject. Sure, in cases #1 and #2 it makes sense to remove them, because you're using the values in $dataobject to identify the database record. But in case #3, the value for each column is already supplied in $where, so if there is also a value supplied in $dataobject, then it indicates that you want to change that value. I've taken a quick look through the code, and I believe this approach will not cause any problems in existing code. So I'll go ahead and implement that. -- You received this bug notification because you are a member of Mahara Contributors, which is subscribed to Mahara. Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it! https://bugs.launchpad.net/bugs/1525736 Title: update_record() doesn't allow for a column listed in the 'where' object to be updated Status in Mahara: New Bug description: If I have a where object like: stdClass Object ( [localusr] => 11 [authinstance] => 2 ) And data object like: stdClass Object ( [remoteusername] => '[email protected]' [authinstance] => 4 [localusr] => 11 ) It will only update the remoteusername and not the authinstance as well? The reason for this is that inside update_record() is a foreach loop to remove any data fields if they match where fields But we probably don't need to do that. To manage notifications about this bug go to: https://bugs.launchpad.net/mahara/+bug/1525736/+subscriptions _______________________________________________ Mailing list: https://launchpad.net/~mahara-contributors Post to : [email protected] Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp
