[suggestion] Allow specifiying a WHERE clause in TSQLRestBatch.Update

edwinsn · 2018-04-19 06:18:30

@ab,
Suggestion
===
Would you consider adding a "aWhereClause: RawUtf8" parameter to the TSQLRestBatch.Update method? Some like:

function TSQLRestBatch.Update(Value: TSQLRecord; const CustomCSVFields: RawUTF8;
  DoNotAutoComputeFields,ForceCacheUpdate: boolean; aWhereClause: RawUtf8 = ''): integer;

When the rest server is executing the batch-update, it should generate the SQL like the one presented in the next section shown below, if the condition doesn't match, the update will failed.

Why?
===
As we know, the UPDATE statement supports the WHERE clause, and we can use it to avoid updating/overwriting an out-of-date record with the help of a time stamp field, something like:

UPDATE CustomerTable ..... WHERE CustomerTable.LastModTime = "theValueRetrievedWhenReading"...

I wish I'm clear on this

Last edited by edwinsn (2018-04-19 06:22:10)

edwinsn · 2018-04-20 03:50:29

Or how do you deal with such situation in a unit-of-work manner?
Currently my program checks for the new timestamp (TModTime) field of the target record **right before** sending the batch-update commands, but it's a separate network round trip and it's not inside a single 'transaction'.

ab · 2018-04-20 08:30:13

In practice, we use DDD aggregates for the unit-of-work pattern, so usually we even don't need a batch, but work on a single TSQLRecord, but with enhanced fields.

edwinsn · 2018-04-20 09:02:48

I'm afraid of "classes explosion" (introduce too much classes) with DDD

On the other hand, even with DDD, your unit-of-work implementation is **on the ORM level** but not on the db engine level, right? So you need to use a global lock?

cybexr · 2019-04-20 11:53:32

Hi, ab, Let's consider this situation :

Client A，Client B both wants to update the same record (eg: TSQLtask.workername ), maybe at same time.
0:00 ClientA REST-retrive table-task, id is 27
0:01 ClientB REST-retrive table-task, id is 27
0:05 ClientA REST-update table-task 27, set workername=A
0:06 CilentB REST-update table-task 27, set workername=B
now , ClientA & ClientB they all feels like gets the task, but ClientA is failed actually.

So, edwinsn's proposal is reasonable, if there is some method like :

TSQLRESTClient.Update(Value: TSQLRecord; wherefields: array of const;  wherevalues: array of const; out rowsaffected:Integer ).

We call call this new method, then eventually mormot.pas will send SQL like: update task set workername=A where id =27 and workername='' , and we'll check if rowsaffected =1, then everything will work as expected.

It seems now, mormot level does't care about syndb's ISQLDBStatement.UpdateCount, every REST Request (Create&Upate) the server received will turned as Insert&Update SQL , then server return HTTPOK if there is no exception, regardless how many rows affected. so I add a rowsaffected parameter above.

And IMHO almost every ORM framework takes care of the actual rows affected, it's import to developers. Syndb is so powerful, and REST C/S ORM is a unique &flexible feature, but this little function insufficient may requires developer to write a SOA method to fulfil the data integrity job. Hope this feature worth a consideration.

mORMot Open Source

#1 2018-04-19 06:18:30

[suggestion] Allow specifiying a WHERE clause in TSQLRestBatch.Update

#2 2018-04-20 03:50:29

Re: [suggestion] Allow specifiying a WHERE clause in TSQLRestBatch.Update

#3 2018-04-20 08:30:13

Re: [suggestion] Allow specifiying a WHERE clause in TSQLRestBatch.Update

#4 2018-04-20 09:02:48

Re: [suggestion] Allow specifiying a WHERE clause in TSQLRestBatch.Update

#5 2019-04-20 11:53:32

Re: [suggestion] Allow specifiying a WHERE clause in TSQLRestBatch.Update

Board footer