MySQL: HTTP POST/PUT Request Implementation Changes

[seantleonard]

Summary

#190 was merged with PUT support for MSSQL. The SqlMutationEngine class method for REST public async Task<JsonDocument> ExecuteAsync(RestRequestContext context) was modified to facilitate one database roundtrip for mutations.

This means that an INSERT/UPSERT can no longer perform two separate queries 1) Insert and then 2) Select the new inserted record. Two operations introduces a concurrency issue where a separate request and modify the record prior to the original PUT request returning.

Implementation Ideas

For the PUT PR, I did some investigation on how to best do this for MySQL.

• Issue 1: There is no OUTPUT clause for MySql which would help an INSERT return the new record in one query. The Hawaii REST Endpoints Functional Specification.docx states that an Insert must return all fields of the new object, so returning only LAST_INSERT_ID() is not enough.
• Issue 2: Mutation operation changed for MS-SQL where the operation is wrapped in a transaction w/ UPDLOCK and is illustrated in public string Build(SqlUpsertQueryStructure structure) within MsSqlQueryBuilder.cs. The result can be TWO result sets:
  • Result Set # 1: Result of the Update operation, will be blank if no update performed.
  • Result Set # 2: Result of the Insert operation, this result set will not exist if the update was successful.
    So to accomplish this, could the following work:

/*https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html*/
INSERT INTO books(id, title, publisher_id)
VALUES (2, "modified Book", 1234)
ON DUPLICATE KEY 
UPDATE id=2,title="modified 9Book",publisher_id=1234;
SET @rowsAffected := ROW_COUNT();

 /*Result Set 1 - Updated*/
/* Manual describes potential value meanings for rows Affected
https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html*/
/* If Function usage https://dev.mysql.com/doc/refman/5.7/en/flow-control-functions.html#function_if */
SELECT IF( @rowsAffected = 2, (SELECT id FROM books where id=2) , IF(@rowsAffected = 0,'Updated nothing',null));

/*Result set 2 - Inserted*/
SELECT IF( @rowsAffected = 1, LAST_INSERT_ID(), null)

What this doesn't solve is how to output all columns of the new row inserted. This would probably need to be wrapped in a transaction.

[seantleonard]

Hey @junsu0ms, would love your or Yanmin's thoughts on this.