mORMot Open Source

fourwhey

Member

Registered: 2017-06-26

Posts: 2

TSQLRest.Delete seems to delete everything.

I'm new to mORMot, so far I'm very impressed and I've been experimenting with things. I have grown to love C#, EF, Dapper.net so discovering that mORMot existed on for Delphi while reading StackOverflow... It just felt like an opportunity for relief from Delphi's dataset, and cursor semantics.

One scenario I've been testing is using it for a local sqlite database. I want to be able to periodically purge anything older than a few days.

Anyway, after reading through the docs and a few of the posts here, I put together a test. It works, but a little too well.

Here's some code.

type
  TSQLSession = class(TSQLRecord)
  private
    FFixtured: Boolean;
    FPadLevel: Boolean;
    FRelocate: Boolean;
    FDataAvailable: Boolean;
    FStoreId: Integer;
    FAuditsetId: Integer;
    FSessionId: Integer;
    FSessScanId: Integer;
    FStarted: RawUTF8;
    FMinAutoAge: RawUTF8;
    FSessName: RawUTF8;
    FAuditName: RawUTF8;
    FEmployee: RawUTF8;
    FPartition: RawUTF8;
    FCreatedAt: TModTime;
  published
    property StoreId: Integer read FStoreId write FStoreId;
    property SessionId: Integer read FSessionId write FSessionId;
    property AuditsetId: Integer read FAuditsetId write FAuditsetId;
    property SessScanId: Integer read FSessScanId write FSessScanId;
    property Fixtured: Boolean read FFixtured write FFixtured;
    property DataAvailable: Boolean read FDataAvailable write FDataAvailable;
    property AuditName: RawUTF8 read FAuditName write FAuditName;
    property SessName: RawUTF8 read FSessName write FSessName;
    property Started: RawUTF8 read FStarted write FStarted;
    property Employee: RawUTF8 read FEmployee write FEmployee;
    property Relocate: Boolean read FRelocate write FRelocate;
    property PadLevel: Boolean read FPadLevel write FPadLevel;
    property Partition: RawUTF8 read FPartition write FPartition;
    property MinAutoAge: RawUTF8 read FMinAutoAge write FMinAutoAge;
    property CreatedAt: TModTime read FCreatedAt write FCreatedAt;
  end;
{ TSQLSession }

procedure TFormTest.PrepareLocalData;
var
  tlNow: TTimeLog;

  procedure RemoveOldData(RecordClass: TObject);
  begin
    if Client.TableHasRows(TSQLRecordClass(RecordClass)) then
    begin
      if Client.TransactionBegin(TSQLRecordClass(RecordClass), 1) then
      begin
        try
          Client.Delete(TSQLRecordClass(RecordClass), 'CreatedAt<?', [TimeLogToSQL(tlNow)]);
          Client.Commit(1);
        except
          Client.RollBack(1);
        end;
      end;
    end;
  end;
begin
  Model := CreateModel;
  Client := TSQLRestServerDB.Create(Model, ExtractFilePath(Application.ExeName) + 'PhyLocalData.db3');
  TSQLRestServerDB(Client).CreateMissingTables;

  // Delete data created prior to today.
  tlNow := TimeLogFromDateTime(IncDay(TimeLogToDateTime(TimeLogNowUTC), -3));
  RemoveOldData(TObject(TSQLSession));
end;

When I run, PrepareLocalData, it deletes everything in the Session table. I've tried using TimeLogNow and its UTC variant, but it doesn't matter what I use, everything gets deleted.
I also used Client.ExecuteFmt and tried to use the date within "delete from session where CreatedAt < datetime('now', '-3 day')" but this doesn't seem to work because the column isn't a date column.
What am I doing wrong?

Last edited by fourwhey (2017-06-26 21:32:44)

AOG

Member

Registered: 2014-02-24

Posts: 490

Re: TSQLRest.Delete seems to delete everything.

FCreatedAt: TModTime; ---> FCreatedAt: TCreateTime;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,207

Website

Re: TSQLRest.Delete seems to delete everything.

The CreateAt field contains a TTimeLog value (as TModTime, but should better be a TCreateTime, for naming consistency).
So no need to convert to SQL date/time, which is for TDateTime fields:

Client.Delete(TSQLRecordClass(RecordClass), 'CreatedAt<?', [tlNow]);

fourwhey

Member

Registered: 2017-06-26

Posts: 2

Re: TSQLRest.Delete seems to delete everything.

The CreateAt field contains a TTimeLog value (as TModTime, but should better be a TCreateTime, for naming consistency).
So no need to convert to SQL date/time, which is for TDateTime fields:

Client.Delete(TSQLRecordClass(RecordClass), 'CreatedAt<?', [tlNow]);

Thank you for the clarification. This seems to have resolved the issue.

Cheers!