Is it possible to know if a Gdn_Model::Save() was successful, without using an autoincrement key?
I've struggling with an apparently failing Save() statement for a couple of hours, only to find out that it actually saves data correctly, but it doesn't return what one would expect. Environment is Vanilla 2.0.8.18.
The table against which the data is saved does not have an autoincrement primary key, but a UUID field, which is passed whenever Gdn_Model::Save() is called (i.e. both for Insert and Update operations). When the data is saved correctly, the following code is executed (source class.database.php):
// Did this query modify data in any way?
if ($ReturnType == 'ID') {
$this->_CurrentResultSet = $this->Connection()->lastInsertId();
} else {
if ($ReturnType == 'DataSet') {
// Create a DataSet to manage the resultset
$this->_CurrentResultSet = new Gdn_DataSet();
$this->_CurrentResultSet->Connection = $this->Connection();
$this->_CurrentResultSet->PDOStatement($PDOStatement);
}
}
The issue is on the line that says $this->_CurrentResultSet = $this->Connection()->lastInsertId();. Since there is no autoincrement primary key, there will never be a "last insert ID". Therefore, the method will always return zero as a result, making it impossible to determine if the save was successful (unless one queries the database to check if the data is there, but that would mean doubling the amount of queries).
Adding an autoincrement key to the table would not be a viable workaround. The library I'm working must perform an "INSERT OR UPDATE" import, which means that some rows must be overwritten if they have changed. Since the autoincrement field would not exist in the source table, every Gdn_Model::Save() would trigger an INSERT, thus creating a lot of duplicates.
Back to the question, is there a (reliable) way to determine the result of a Gdn_Model::Save operation, without relying on autoincrement keys?
Answers
sometimes @Todd seems willing to lend a hand. if not @Lincoln.
I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.
Oh boy...
Can you figure out how the returntype gets returned? Is there a way you can plug in or hook in to that part of the sourcecode?
There was an error rendering this rich post.
I know no answer for that, sorry.
But shouldn't that be a bug report? A framework should ensure that an action has happened and if it fails, it should give a reliable response.
Unfortunately, no.
PDO::lastInsertId()returns zero (int) when no new ID has been generated (whether because theINSERTfailed or because there was really no ID to generate).I'm afraid it won't be possible, either. There is no hook of any kind, the only solution would be modifying, or overriding, the core class.
My shop | About Me
Technically, it's not a bug, but a design choice. As long as you use a primary key, the insert returns the new key value upon
INSERT, and zero upon error.Interestingly, the logic breaks also if you have a composite primary key. The code, which I don't have handy, first fetches the primary key fields (e.g.
Field1,Field2,Field3), stores them into an array, then sets itsPrimaryKeyproperty to the name of the first field, whatever it is, which is assumed to be, once again, an autoincrement field.Despite its limitations, it looks to me a clear design choice (i.e. "we assume all tables have an autoincrement field as primary key, and that's it").
My shop | About Me
can't you use have errorInfo()/errorCode() if the PDO object returned by Connection()?
grep is your friend.
Sorry to be a pain... Let me ask in another way ...
which function call generates (returns) $ ReturnType ?
There was an error rendering this rich post.
That could be an idea. Time for an experiment!
It's set in
Gdn_SqlDriver::Query():public function Query($Sql, $Type = 'select') { switch ($Type) { case 'insert': $ReturnType = 'ID'; break; case 'update': $ReturnType = NULL; break; default: $ReturnType = 'DataSet'; break; } [...]Anyway, I decided to override base model, because I will also need to handle composite primary keys. The differences between the original model and the updated one will be the following:
Gdn_Modelexpects the primary key to be an autoincrement field, generated automatically. If a value is passed for the primary key field, the query is automatically set toUPDATE. The updated model will allow toINSERTdata when passing the primary key explicitly.Gdn_Modeldoesn't handle multi-field primary keys. Updated model will handle them.Gdn_Modelwill implement some more error handling. The result of aSave()operation will be set tofalsein case of failure.I'm planning to add all these to my free AFC plugin, which I will be using as the foundation of most of my other plugins, themes and applications. Logger (aka Rogan), Cron (aka Garan) and AFC (aka Silia) will be my "holy trinity" of Vanilla development.
My shop | About Me