mORMot Open Source

profh

Member

Registered: 2010-07-02

Posts: 161

delete many rows of TSQLRest with where condition

how to delete many rows  of TSQLRest with where condition?

thanks.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: delete many rows of TSQLRest with where condition

This is not handled directly by the CRUD commands of the ORM, in its current state.

A standard way could be to first retrieve a list of IDs with the where condition, then delete each item (in a BATCH deletion), within a whole transaction.
It would let cache work as expected, and trigger all internal events.

Or you may write a dedicated service on the server side, calling the DB, and explicitly handling all needed synchronization.
It may be faster, but could be unsafe in some cases (if you use caching or expect some events to be triggered, for instance).

profh

Member

Registered: 2010-07-02

Posts: 161

Re: delete many rows of TSQLRest with where condition

      dbcache := TSQLRestServerStaticInMemory.Create(TCache,nil,'');
      ......
      lastcache := 60 * 1000;
      tmpid := 1;
      Rec := TCache.Create(dbcache, 'id=%',[tmpid]);
      try
        tmpid := Rec.ID;
        cur64 := Rec.Time;
      finally
        Rec.Free;
      end;
      while (tmpid > 0) do
      begin
        if (cur64 < lastcache) then
        begin
          ab := dbcache.Delete(TCache,tmpid);
          if (not ab) then break;
        end else tmpid := tmpid + 1;

        Rec := TCache.Create(dbcache, 'id=%',[tmpid]);
        try
          tmpid := Rec.ID;
          cur64 := Rec.Time;
        finally
          Rec.Free;
        end;
      end;

it s ugly,but it works.
any good idea?

coblongpamor

Member

From: Bali-Indonesia

Registered: 2010-11-07

Posts: 130

Website

Re: delete many rows of TSQLRest with where condition

I think you just need a line of code to do so.

dbcache.EngineExecuteAll(FormatUTF8('DELETE FROM Cache WHERE Time < %d',[lastcache]));

hopefully can help.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: delete many rows of TSQLRest with where condition

@coblongpamor:
About syntax, for a SQLite3 table it is perhaps better to use EngineExecuteAllFmt() and use a prepared parameter as such:

dbcache.EngineExecuteAllFmt('DELETE FROM Cache WHERE Time < ?',[],[lastcache]));

But it will also by pass the internal caching mechanism.
So in some cases, it may break the clients.

AND in this particular case, it won't work.
The dbcache is not a SQLite3 based DB instance. But a TSQLRestServerStaticInMemory.
This particular kind of in-memory table does not allow any WHERE clause for the DELETE, and only allow one "=" statement in the where close.

@profh:
No, do not do that!
You are looping through all IDs... this is deadly slow and will use a lot of resources.
You can't directly use a WHERE clause with "Time < ..." in your statement.
But since it is a TSQLRestServerStaticInMemory instance, you can access the objects:
Something like this:

var IDs: array of integer;
     i,n: integer;
begin
  n := 0;
  Setlength(IDs,dbCache.Count);
  for i := 0 to dbCache.Count-1 do
    if TCache(dbCache.Items[i]).Time<LastCache then begin
      IDs[n] := dbCache.ID[i];
      inc(n);
    end;
  for i := 0 to n-1 do
    dbCache.Delete(TCache,IDs[i]);

I use a temporary IDs[] array in this case, but you may also write:

begin
  for i := dbCache.Count-1 downto 0 do
    if TCache(dbCache.Items[i]).Time<LastCache then
      dbCache.Delete(TCache,dbCache.ID[i]);

This will also work, and will be faster/easier, since all ID[] are stored in an increasing order (but note the for ... DOWNTO 0 is needed here since we'll delete matching entries within the loop).

But using a TSQLRestServerStaticInMemory is perhaps not a good idea, if you need to work with some SQL statements like those.

In this case, you may use a TSQLVirtualTableBinary / TSQLVirtualTableJSON kind of table, which will use TSQLRestServerStaticInMemory  for storage, but will also have the full SQLite3 SQL engine available to access it.
In this case:

dbcache.EngineExecuteAllFmt('DELETE FROM Cache WHERE Time < ?',[],[lastcache]));

Will work directly.

profh

Member

Registered: 2010-07-02

Posts: 161

Re: delete many rows of TSQLRest with where condition

it s great,
BTW,i use TSQLRestServerStaticInMemory just for cache, for real database i also use TSQLRestServerDB...

many thanks for coblongpamor and ab.

Last edited by profh (2012-05-03 08:47:44)