immudb icon indicating copy to clipboard operation
immudb copied to clipboard

add result to TxSQLExec() to enable LastInsertID() and AffectedRows() during transactions in stdlib

Open tauu opened this issue 3 years ago • 0 comments

This PR adds a result to the TxSQLExec api, which currently only returns an empty result. The new result contains number of affected rows as well as the last inserted primary key(s) for the executed statement. This values are used in stdlib to implement the driver.Result interface for statements executed as part of a transaction.

Do determine the number of affected rows and last inserted PKs the state of the transaction before executing the statement is compared to the state after executing it. The difference of the states yields the values for the result of the executed statement. Only the last inserted primary keys are returned and not the first inserted primary keys, as comparing the states may not reliably determine the first inserted primary key in some cases. E.g. if a transaction executes two distinct statements inserting values into a table, the value of tx.FirstInsertedPrimaryKey() will not be changed by the second query. This creates unfortunately a discrepancy between LastInsertID() within a transaction and outside, as outside of a transaction the first inserted primary key is returned instead of the last. Is it an option to switch this behaviour to return the last inserted pk?

gorm depends on driver.Result interface being implemented for statements executed as part of a transaction to handle associations such as hasMany / manyToMany. With this PR these associations can be enabled in a gorm driver.

Existing tests have been adjusted for the extra return value and a new integration test for the new result of TxSQLExec() has been created.

tauu avatar Feb 17 '22 17:02 tauu