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

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

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

@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 smile

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


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

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

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

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

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'.


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

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

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. wink

Offline

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

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

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

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

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?


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

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

cybexr
Member
Registered: 2016-09-14
Posts: 78

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

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.

Offline

Board footer

Powered by FluxBB