Batch update based on ID

leus · 2020-12-11 16:30:35

I have some operations that require an update on a list of records that I retrieved from the database before.

In essence, I have a list of IDs that I need to update a single field. I've looking at TSQLRestBatch, but that needs the whole record loaded, I believe.

Is there a way to update a single field in a list of record Ids?

(Slowly making progress - by the way, when doing Google searchs, I get flooded with commit messages from Fossil, I think that should be addressed).

ab · 2020-12-11 17:47:53

No, you can specify the field name(s) to be updates in the batch.

Vitaly · 2020-12-11 19:22:32

If I understood it correctly, I guess, you can try smth like (not tested)

LBatch := TSQLRestBatch.Create(YourRestDB, nil, 1000);
LRecord := TSQLYourRecordClass.Create;
LRecord.FieldToUpdate := NewValue;
for i := 0 to IDsCount - 1 do
begin
LRecord.IDValue := IDs[i];
LBatch.Update(LRecord, 'FieldToUpdate');
end;
FreeAndNil(LRecord);
YourRestDB.BatchSend(LBatch);
FreeAndNil(LBatch);

But if it is possible, I would use a direct sql statement for such case, although sometimes it is not the best decision of course.

leus · 2020-12-11 19:53:01

Thanks, that really helps.

Anyways, apparently I'm doing something wrong, because the "Add" method always exits because fails this check:

if PSQLRecordClass(Value)^<>fTable then
      exit else begin // '{"Table":[...,"PUT",{object},...]}'

The debugger evaluates PSQLRecordClass(Value)^ as TSQLRecordClass($204B0) (my record class derives from TSQLRecord).

What should I use to fix this?

Last edited by leus (2020-12-11 19:54:07)

ab · 2020-12-11 20:57:57

You did specify an improper class to the TSQLRestBatch.Create() constructor.
If you specify a class here, you need to stick to this class.
I guess you update several types of classes in your batch, but you specified a given one in the constructor.

leus · 2020-12-11 21:42:20

I was about to post my code, and found out that effectively, I was using the wrong object during the update.

This is my current code, which seems to be working fine:

procedure UpdateTrackIds(docs: TSomeList; TrackId: Int64);
var
  batchUpdater: TSQLRestBatch;
  i: Integer;
  fieldBits: TSQLFieldBits;
  rec: TMyRecord;
begin
  fieldBits := TMyRecord.RecordProps.FieldBitsFromRawUTF8
    ([StringToUTF8('TrackId')]);
  batchUpdater := TSQLRestBatch.Create(ServerDB, TMyRecord);
  try
    rec := TMyRecord.Create;
    try
      rec.TrackId := TrackId;
      for i := 0 to Length(docs) - 1 do
      begin
        rec.IDValue := docs[i].Id;
        batchUpdater.Update(rec, fieldBits);
      end;
    finally
      rec.Free;
    end;
    ServerDB.BatchSend(batchUpdater);
  finally
    batchUpdater.Free;
  end;
end;

Last edited by leus (2020-12-11 21:42:39)

macfly · 2020-12-11 21:46:15

Interesting this solution.

But in relation to the cache.
Will the framewok recognize these updates and update/clear the cache?

Vitaly · 2020-12-11 22:04:51

Probably this is the answer to your question:

    // - if not all fields are specified, will reset the cache entry associated
    // with this value, unless ForceCacheUpdate is TRUE
    function Update(Value: TSQLRecord; const CustomFields: TSQLFieldBits=[];
      DoNotAutoComputeFields: boolean=false; ForceCacheUpdate: boolean=false): integer; overload; virtual;

ab · 2020-12-11 22:13:29

Note that

StringToUTF8('TrackId')

is not needed - you can use a 'TrackID' constant with no problem.

macfly · 2020-12-11 22:24:44

Thanks @Vitaly

ab · 2020-12-12 11:10:53

Warning: ForceCacheUpdate may be misleading.

If there are all fields available in the batch update, then the ORM update the cache properly.
If there are some missing fields (e.g. in your case if you only update one field), then it won't update the cache, it will just reset the cache for this entry.

Setting ForceCacheUpdate will replace the value in cache with the updated fields only.
In your case, if you make a Retrieve per ID, you will have ONLY the update field set.
Not what you expect for sure.

So, the cache is always properly set or reset. You do'nt have to worry about it.
ForceCacheUpdate is not to be used in your case.

See the TSQLRestBatch.Update method code as reference.

leus · 2020-12-12 15:53:13

I don't understand. Should I worry about cache updates? If another client retrieves any of the updated records right after this method is executed, will they get the updated ID?

macfly · 2020-12-12 17:02:08

Do not worry.
This was a question I had, but it was explained above.

Leave the default value ForceCacheUpdate = False and it should be OK.

ab · 2020-12-12 17:08:50

ab wrote:

So, the cache is always properly set or reset. You do'nt have to worry about it.
ForceCacheUpdate is not to be used in your case.

mORMot Open Source

#1 2020-12-11 16:30:35

Batch update based on ID

#2 2020-12-11 17:47:53

Re: Batch update based on ID

#3 2020-12-11 19:22:32

Re: Batch update based on ID

#4 2020-12-11 19:53:01

Re: Batch update based on ID

#5 2020-12-11 20:57:57

Re: Batch update based on ID

#6 2020-12-11 21:42:20

Re: Batch update based on ID

#7 2020-12-11 21:46:15

Re: Batch update based on ID

#8 2020-12-11 22:04:51

Re: Batch update based on ID

#9 2020-12-11 22:13:29

Re: Batch update based on ID

#10 2020-12-11 22:24:44

Re: Batch update based on ID

#11 2020-12-12 11:10:53

Re: Batch update based on ID

#12 2020-12-12 15:53:13

Re: Batch update based on ID

#13 2020-12-12 17:02:08

Re: Batch update based on ID

#14 2020-12-12 17:08:50

Re: Batch update based on ID

Board footer