mORMot Open Source

ComingNine

Member

Registered: 2010-07-29

Posts: 294

Newbie question of using TSQLRecordMany.

There is an ESQLite3Exception complaining "no such column: XXX". However, when the sqlite database file is opened with SqliteMaestro, the column is there. Could you help to comment ?

The console program source code is

program Learn2_TSQLRecordMany;

uses
  SysUtils,
  SynSQLite3Static,
  uTestTSQLRecordMany in 'uTestTSQLRecordMany.pas';

{$R *.res}

begin
 with TMyTestSuit.Create do 
   try 
//     ToConsole := @Output; // so we will see something on screen
     Run;
     readln; 
   finally 
     Free; 
   end;
end.

The test unit source code is

unit uTestTSQLRecordMany;

interface

uses
  SysUtils,
  SynCommons,
  mORMot,
  mORMotSQLite3;

type

  TMyTestTSQLRecordMany = class(TSynTestCase)
  private
    fDatabase: TSQLRest;
    fModel: TSQLModel;
  published
    procedure TestTSQLRecordMany;
  end;

  TMyTestSuit = class(TSynTests)
  published
    procedure RegMyTestSuit;
  end;

type

  TSQLSource = class;
  TSQLDest = class;
  TSQLDestPivot = class(TSQLRecordMany)
  private
    fSource: TSQLSource;
    fDest: TSQLDest;
    fTime: TDateTime;
  published
    property Source: TSQLSource read fSource;
    property Dest: TSQLDest read fDest;
    property AssociationTime: TDateTime read fTime write fTime;
  end;
  TSQLSource = class(TSQLRecordSigned)
  private
    fDestList: TSQLDestPivot;
  published
    property SignatureTime;
    property Signature;
    property DestList: TSQLDestPivot read fDestList;
  end;
  TSQLDest = class(TSQLRecordSigned)
  published
    property SignatureTime;
    property Signature;
  end;

implementation

// !!! Modified !!!
//procedure InternalTestMany(Test: TSynTestCase; aClient: TSQLRestClient);
procedure InternalTestMany(Test: TSynTestCase; aClient: TSQLRest);
var MS: TSQLSource;
    MD, MD2: TSQLDest;
    i: integer;
    sID, dID: array[1..100] of Integer;
    res: TIntegerDynArray;
  procedure CheckOK;
  begin
    if Test.CheckFailed(MS.FillTable<>nil) then
      exit;
    Test.Check(MS.FillTable.RowCount>=length(sId));
    while MS.FillOne do begin
      Test.Check(MS.DestList.Source.fID=MS.fID);
      Test.Check(MS.DestList.Dest.SignatureTime<>0);
      MS.ClearProperties;
      MS.DestList.Source.ClearProperties;
      MS.DestList.Dest.ClearProperties;
    end;
    MS.FillClose;
  end;
begin
  MS := TSQLSource.Create;
  MD := TSQLDest.Create;
  with Test do
  try
    MD.fSignatureTime := TimeLogNow;
    MS.fSignatureTime := MD.fSignatureTime;
    Check(MS.DestList<>nil);
    Check(MS.DestList.InheritsFrom(TSQLRecordMany));
// !!! Modified !!!
//    Check(aClient.TransactionBegin(TSQLSource)); // faster process
    Check(aClient.TransactionBegin(TSQLSource, CONST_AUTHENTICATION_NOT_USED)); // faster process
    for i := 1 to high(dID) do begin
      MD.fSignature := FormatUTF8('% %',[aClient.ClassName,i]);
      dID[i] := aClient.Add(MD,true);
      Check(dID[i]>0);
    end;
    for i := 1 to high(sID) do begin
      MS.fSignature := FormatUTF8('% %',[aClient.ClassName,i]);
      sID[i] := aClient.Add(MS,True);
      Check(sID[i]>0);
      MS.DestList.AssociationTime := i;
      Check(MS.DestList.ManyAdd(aClient,sID[i],dID[i])); // associate both lists
      Check(not MS.DestList.ManyAdd(aClient,sID[i],dID[i],true)); // no dup
    end;
// !!! Modified !!!
//    aClient.Commit;
    aClient.Commit(CONST_AUTHENTICATION_NOT_USED);
    for i := 1 to high(dID) do begin
      Check(MS.DestList.SourceGet(aClient,dID[i],res));
      if not CheckFailed(length(res)=1) then
        Check(res[0]=sID[i]);
      Check(MS.DestList.ManySelect(aClient,sID[i],dID[i]));
      Check(MS.DestList.AssociationTime=i);
    end;
    for i := 1 to high(sID) do begin
      Check(MS.DestList.DestGet(aClient,sID[i],res));
      if CheckFailed(length(res)=1) then
        continue; // avoid GPF
      Check(res[0]=dID[i]);
      Check(MS.DestList.FillMany(aClient,sID[i])=1);
      Check(MS.DestList.FillOne);
      Check(Integer(MS.DestList.Source)=sID[i]);
      Check(Integer(MS.DestList.Dest)=dID[i]);
      Check(MS.DestList.AssociationTime=i);
      Check(not MS.DestList.FillOne);
      Check(MS.DestList.DestGetJoined(aClient,'',sID[i],res));
      if not CheckFailed(length(res)=1) then
        Check(res[0]=dID[i]);
      Check(MS.DestList.DestGetJoined(aClient,'ADest.SignatureTime=:(0):',sID[i],res));
      Check(length(res)=0);
      Check(MS.DestList.DestGetJoined(aClient,
        FormatUTF8('ADest.SignatureTime=?',[],[MD.SignatureTime]),sID[i],res));
//   'ADest.SignatureTime=:('+Int64ToUTF8(MD.SignatureTime)+'):',sID[i],res));
      if CheckFailed(length(res)=1) then
        continue; // avoid GPF
      Check(res[0]=dID[i]);
      MD2 := MS.DestList.DestGetJoined(aClient,
        FormatUTF8('ADest.SignatureTime=?',[],[MD.SignatureTime]),sID[i]) as TSQLDest;
//   'ADest.SignatureTime=:('+Int64ToUTF8(MD.SignatureTime)+'):',sID[i]) as TSQLDest;
      if CheckFailed(MD2<>nil) then
        continue;
      try
        Check(MD2.FillOne);
        Check(MD2.ID=dID[i]);
        Check(MD2.Signature=FormatUTF8('% %',[aClient.ClassName,i]));
      finally
        MD2.Free;
      end;
    end;
    Check(MS.FillPrepareMany(aClient,nil,[],[]));
    CheckOK;
    Check(MS.FillPrepareMany(aClient,'DestList.Dest.SignatureTime<>?',[],[0]));
    CheckOK;
    Check(MS.FillPrepareMany(aClient,
      'DestList.Dest.SignatureTime<>% and RowID>=? and DestList.AssociationTime<>0 '+
      'and SignatureTime=DestList.Dest.SignatureTime '+
      'and DestList.Dest.Signature<>"DestList.AssociationTime"',[0],[sID[1]]));
    if CheckFailed(MS.FillTable<>nil) then
      exit;
    Check(MS.FillTable.RowCount=length(sID));
    for i := 1 to high(sID) do begin
      MS.SignatureTime := 0;
      MS.DestList.Dest.SignatureTime := 0;
      if CheckFailed(MS.FillOne) then
        break;
      Check(MS.fID=sID[i]);
      Check(MS.SignatureTime=MD.fSignatureTime);
      Check(MS.DestList.AssociationTime=i);
      Check(MS.DestList.Dest.fID=dID[i]);
      Check(MS.DestList.Dest.SignatureTime=MD.fSignatureTime);
      Check(MS.DestList.Dest.Signature=FormatUTF8('% %',[aClient.ClassName,i]));
    end;
    MS.FillClose;
// !!! Modified !!!
//    Check(aClient.TransactionBegin(TSQLDestPivot)); // faster process
    Check(aClient.TransactionBegin(TSQLDestPivot, CONST_AUTHENTICATION_NOT_USED)); // faster process
    for i := 1 to high(sID) shr 2 do
      Check(MS.DestList.ManyDelete(aClient,sID[i*4],dID[i*4]));
// !!! Modified !!!
//    aClient.Commit;
    aClient.Commit(CONST_AUTHENTICATION_NOT_USED);
    for i := 1 to high(sID) do
      if i and 3<>0 then begin
        Check(MS.DestList.ManySelect(aClient,sID[i],dID[i]));
        Check(MS.DestList.AssociationTime=i);
      end else
        Check(not MS.DestList.ManySelect(aClient,sID[i],dID[i]));
  finally
    MD.Free;
    MS.Free;
  end;
end;

{ TMyTestTSQLRecordMany }

procedure TMyTestTSQLRecordMany.TestTSQLRecordMany;
begin
  fModel := TSQLModel.Create([TSQLSource, TSQLDest, TSQLDestPivot]);;
  fDatabase := TSQLRestServerDB.Create(fModel, ChangeFileExt(paramstr(0),'.db3'));
  TSQLRestServerDB(fDatabase).CreateMissingTables(0);
  InternalTestMany(Self, fDatabase);
end;

{ TMyTestSuit }

procedure TMyTestSuit.RegMyTestSuit;
begin
  AddCase([TMyTestTSQLRecordMany]);
end;

end.

The exception is

First chance exception at $75A2C41F. Exception class ESQLite3Exception with message 'no such column: ADest.SignatureTime'. Process Learn2_TSQLRecordMany.exe (11172)

The stack-trace is

:75a2c41f KERNELBASE.RaiseException + 0x58
SynSQLite3.sqlite3_check(7583624,1)
SynSQLite3.TSQLRequest.Prepare(7583624,'SELECT Dest.RowID FROM Dest,DestPivot WHERE DestPivot.Source=? AND DestPivot.Dest=Dest.RowID AND ADest.SignatureTime=?')
SynSQLite3.TSQLStatementCached.Prepare('SELECT Dest.RowID FROM Dest,DestPivot WHERE DestPivot.Source=? AND DestPivot.Dest=Dest.RowID AND ADest.SignatureTime=?')
mORMotSQLite3.TSQLRestServerDB.GetAndPrepareStatement('SELECT Dest.RowID FROM Dest,DestPivot WHERE DestPivot.Source=:(1): AND DestPivot.Dest=Dest.RowID AND ADest.SignatureTime=:(0):')
mORMotSQLite3.TSQLRestServerDB.EngineList('SELECT Dest.RowID FROM Dest,DestPivot WHERE DestPivot.Source=:(1): AND DestPivot.Dest=Dest.RowID AND ADest.SignatureTime=:(0):',False,nil)
mORMot.TSQLRestServer.ExecuteList((...),'SELECT Dest.RowID FROM Dest,DestPivot WHERE DestPivot.Source=:(1): AND DestPivot.Dest=Dest.RowID AND ADest.SignatureTime=:(0):')
mORMot.TSQLRecordMany.DestGetJoinedTable($6E3660,'ADest.SignatureTime=:(0):',1,jkDestID,'')
mORMot.TSQLRecordMany.DestGetJoined($6E3660,'ADest.SignatureTime=:(0):',1,())
uTestTSQLRecordMany.InternalTestMany($7836D0,$6E3660)
uTestTSQLRecordMany.TMyTestTSQLRecordMany.TestTSQLRecordMany
SynCommons.TSynTests.Run
Learn2_TSQLRecordMany.Learn2_TSQLRecordMany
:753433aa kernel32.BaseThreadInitThunk + 0x12
:778b9ef2 ntdll.RtlInitializeExceptionChain + 0x63
:778b9ec5 ntdll.RtlInitializeExceptionChain + 0x36

It should be noted that even though there is no occurrence of TSynLog, the app will give a log file when compiled with Delphi 7, but no log file when compiled with Delphi XE6.

Last edited by ComingNine (2014-05-01 14:35:05)

ComingNine

Member

Registered: 2010-07-29

Posts: 294

Re: Newbie question of using TSQLRecordMany.

Sorry ! My fault !

"TSQLADest" is typed by me as "TSQLDest" in the Delphi type declarations.

Thus, when DestGetJoined is called, the parameter string needs to be changed in accordance.

That is to say, "ADest.SignatureTime" -> "Dest.SignatureTime"...

Last edited by ComingNine (2014-05-01 14:15:00)

ComingNine

Member

Registered: 2010-07-29

Posts: 294

Re: Newbie question of using TSQLRecordMany.

Could you help to comment another two questions:

1. It seems when running within IDE, the time consumed is much less than that when running standalone. Could you help to comment ?

      For example: running within IDE gives,
     

        - Test TSQL record many: 6,113 assertions passed  138.87ms
        Total failed: 0 / 6,113  - My test TSQL record many PASSED  139.14ms
      

      running standalone gives,
     

        - Test TSQL record many: 5,813 assertions passed  479.87ms
        Total failed: 0 / 5,813  - My test TSQL record many PASSED  481.13ms
      

2. There are two occurrences of "TransactionBegin" in "uTestTSQLRecordMany.pas". In the source code comments of "TransactionBegin", it is written "the aTable parameter is not used yet". Is this description correct ? I mean, there are descriptions in the doc v1.18 saying "Locking of the database at the record level (page 157)" and "at sqlite 3 engine leve, there is some kind of giant lock (page 249)".

If not, which aTable should one pass ? For example, in the first occurrence of "TransactionBegin" (line 89), TSQLSource is passed even though three tables are being effected. Is this procedure correct ?

3.
The source code corrected from my mistakes is pasted below for your convenience:

The console program source code is

program Learn2_TSQLRecordMany;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  SynSQLite3Static,
  uTestTSQLRecordMany in 'uTestTSQLRecordMany.pas';

{$R *.res}

begin
 with TMyTestSuit.Create do
   try
//     ToConsole := @Output; // so we will see something on screen
     Run;
     readln;
   finally 
     Free; 
   end;
end.

The test unit source code is

unit uTestTSQLRecordMany;

interface

uses
  SysUtils,
  SynCommons,
  mORMot,
  mORMotSQLite3;

type

  TMyTestTSQLRecordMany = class(TSynTestCase)
  private
    fDatabase: TSQLRest;
    fModel: TSQLModel;
  published
    procedure TestTSQLRecordMany;
  end;

  TMyTestSuit = class(TSynTests)
  published
    procedure RegMyTestSuit;
  end;

type

  TSQLSource = class;
  TSQLDest = class;
  TSQLDestPivot = class(TSQLRecordMany)
  private
    fSource: TSQLSource;
    fDest: TSQLDest;
    fTime: TDateTime;
  published
    property Source: TSQLSource read fSource;
    property Dest: TSQLDest read fDest;
    property AssociationTime: TDateTime read fTime write fTime;
  end;
  TSQLSource = class(TSQLRecordSigned)
  private
    fDestList: TSQLDestPivot;
  published
    property SignatureTime;
    property Signature;
    property DestList: TSQLDestPivot read fDestList;
  end;
  TSQLDest = class(TSQLRecordSigned)
  published
    property SignatureTime;
    property Signature;
  end;

implementation

// !!! Modified !!!
//procedure InternalTestMany(Test: TSynTestCase; aClient: TSQLRestClient);
procedure InternalTestMany(Test: TSynTestCase; aClient: TSQLRest);
var MS: TSQLSource;
    MD, MD2: TSQLDest;
    i: integer;
    sID, dID: array[1..100] of Integer;
    res: TIntegerDynArray;
  procedure CheckOK;
  begin
    if Test.CheckFailed(MS.FillTable<>nil) then
      exit;
    Test.Check(MS.FillTable.RowCount>=length(sId));
    while MS.FillOne do begin
      Test.Check(MS.DestList.Source.fID=MS.fID);
      Test.Check(MS.DestList.Dest.SignatureTime<>0);
      MS.ClearProperties;
      MS.DestList.Source.ClearProperties;
      MS.DestList.Dest.ClearProperties;
    end;
    MS.FillClose;
  end;
begin
  MS := TSQLSource.Create;
  MD := TSQLDest.Create;
  with Test do
  try
    MD.fSignatureTime := TimeLogNow;
    MS.fSignatureTime := MD.fSignatureTime;
    Check(MS.DestList<>nil);
    Check(MS.DestList.InheritsFrom(TSQLRecordMany));
// !!! Modified !!!
//    Check(aClient.TransactionBegin(TSQLSource)); // faster process
    Check(aClient.TransactionBegin(TSQLSource, CONST_AUTHENTICATION_NOT_USED)); // faster process
    for i := 1 to high(dID) do begin
      MD.fSignature := FormatUTF8('% %',[aClient.ClassName,i]);
      dID[i] := aClient.Add(MD,true);
      Check(dID[i]>0);
    end;
    for i := 1 to high(sID) do begin
      MS.fSignature := FormatUTF8('% %',[aClient.ClassName,i]);
      sID[i] := aClient.Add(MS,True);
      Check(sID[i]>0);
      MS.DestList.AssociationTime := i;
      Check(MS.DestList.ManyAdd(aClient,sID[i],dID[i])); // associate both lists
      Check(not MS.DestList.ManyAdd(aClient,sID[i],dID[i],true)); // no dup
    end;
// !!! Modified !!!
//    aClient.Commit;
    aClient.Commit(CONST_AUTHENTICATION_NOT_USED);
    for i := 1 to high(dID) do begin
      Check(MS.DestList.SourceGet(aClient,dID[i],res));
      if not CheckFailed(length(res)=1) then
        Check(res[0]=sID[i]);
      Check(MS.DestList.ManySelect(aClient,sID[i],dID[i]));
      Check(MS.DestList.AssociationTime=i);
    end;
    for i := 1 to high(sID) do begin
      Check(MS.DestList.DestGet(aClient,sID[i],res));
      if CheckFailed(length(res)=1) then
        continue; // avoid GPF
      Check(res[0]=dID[i]);
      Check(MS.DestList.FillMany(aClient,sID[i])=1);
      Check(MS.DestList.FillOne);
      Check(Integer(MS.DestList.Source)=sID[i]);
      Check(Integer(MS.DestList.Dest)=dID[i]);
      Check(MS.DestList.AssociationTime=i);
      Check(not MS.DestList.FillOne);
      Check(MS.DestList.DestGetJoined(aClient,'',sID[i],res));
      if not CheckFailed(length(res)=1) then
        Check(res[0]=dID[i]);
      // !!! Modified !!!
      // Check(MS.DestList.DestGetJoined(aClient,'ADest.SignatureTime=:(0):',sID[i],res));
      Check(MS.DestList.DestGetJoined(aClient,'Dest.SignatureTime=:(0):',sID[i],res));
      Check(length(res)=0);
      Check(MS.DestList.DestGetJoined(aClient,      
        // !!! Modified !!!
        // FormatUTF8('ADest.SignatureTime=?',[],[MD.SignatureTime]),sID[i],res));
        FormatUTF8('Dest.SignatureTime=?',[],[MD.SignatureTime]),sID[i],res));
//   'ADest.SignatureTime=:('+Int64ToUTF8(MD.SignatureTime)+'):',sID[i],res));
      if CheckFailed(length(res)=1) then
        continue; // avoid GPF
      Check(res[0]=dID[i]);
      MD2 := MS.DestList.DestGetJoined(aClient,
        // !!! Modified !!!
        // FormatUTF8('ADest.SignatureTime=?',[],[MD.SignatureTime]),sID[i]) as TSQLDest;
        FormatUTF8('Dest.SignatureTime=?',[],[MD.SignatureTime]),sID[i]) as TSQLDest;
//   'ADest.SignatureTime=:('+Int64ToUTF8(MD.SignatureTime)+'):',sID[i]) as TSQLDest;
      if CheckFailed(MD2<>nil) then
        continue;
      try
        Check(MD2.FillOne);
        Check(MD2.ID=dID[i]);
        Check(MD2.Signature=FormatUTF8('% %',[aClient.ClassName,i]));
      finally
        MD2.Free;
      end;
    end;
    Check(MS.FillPrepareMany(aClient,nil,[],[]));
    CheckOK;    
    Check(MS.FillPrepareMany(aClient,'DestList.Dest.SignatureTime<>?',[],[0]));
    CheckOK;
    Check(MS.FillPrepareMany(aClient,
      'DestList.Dest.SignatureTime<>% and RowID>=? and DestList.AssociationTime<>0 '+
      'and SignatureTime=DestList.Dest.SignatureTime '+
      'and DestList.Dest.Signature<>"DestList.AssociationTime"',[0],[sID[1]]));
    if CheckFailed(MS.FillTable<>nil) then
      exit;
    Check(MS.FillTable.RowCount=length(sID));
    for i := 1 to high(sID) do begin
      MS.SignatureTime := 0;
      MS.DestList.Dest.SignatureTime := 0;
      if CheckFailed(MS.FillOne) then
        break;
      Check(MS.fID=sID[i]);
      Check(MS.SignatureTime=MD.fSignatureTime);
      Check(MS.DestList.AssociationTime=i);
      Check(MS.DestList.Dest.fID=dID[i]);
      Check(MS.DestList.Dest.SignatureTime=MD.fSignatureTime);
      Check(MS.DestList.Dest.Signature=FormatUTF8('% %',[aClient.ClassName,i]));
    end;
    MS.FillClose;
// !!! Modified !!!
//    Check(aClient.TransactionBegin(TSQLDestPivot)); // faster process
    Check(aClient.TransactionBegin(TSQLDestPivot, CONST_AUTHENTICATION_NOT_USED)); // faster process
    for i := 1 to high(sID) shr 2 do
      Check(MS.DestList.ManyDelete(aClient,sID[i*4],dID[i*4]));
// !!! Modified !!!
//    aClient.Commit;
    aClient.Commit(CONST_AUTHENTICATION_NOT_USED);
    for i := 1 to high(sID) do
      if i and 3<>0 then begin
        Check(MS.DestList.ManySelect(aClient,sID[i],dID[i]));
        Check(MS.DestList.AssociationTime=i);
      end else
        Check(not MS.DestList.ManySelect(aClient,sID[i],dID[i]));
  finally
    MD.Free;
    MS.Free;
  end;
end;

{ TMyTestTSQLRecordMany }

procedure TMyTestTSQLRecordMany.TestTSQLRecordMany;
begin
  fModel := TSQLModel.Create([TSQLSource, TSQLDest, TSQLDestPivot]);;
  fDatabase := TSQLRestServerDB.Create(fModel, ChangeFileExt(paramstr(0),'.db3'));
  TSQLRestServerDB(fDatabase).CreateMissingTables(0);
  InternalTestMany(Self, fDatabase);
end;

{ TMyTestSuit }

procedure TMyTestSuit.RegMyTestSuit;
begin
  AddCase([TMyTestTSQLRecordMany]);
end;

end.

Last edited by ComingNine (2014-05-01 15:28:32)

ComingNine

Member

Registered: 2010-07-29

Posts: 294

Re: Newbie question of using TSQLRecordMany.

Could you help to comment the questions above ? i.e. (1) why the sample app above runs faster within IDE than standalone ? (2) what to pass into TransactionBegin when multiple tables are to be updated ?

Thank you very much for your time !

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Newbie question of using TSQLRecordMany.

1. I never saw this behavior.
I observed quite the contrary, in fact.

2. "the aTable parameter is not used yet" is wrong (deprecated).
I've just fixed it.
Thanks for the report.

ComingNine

Member

Registered: 2010-07-29

Posts: 294

Re: Newbie question of using TSQLRecordMany.

Thank you for your comments ! I wonder which table to pass into TransactionBegin when multiple tables (Source,Dest,Pivot) are to be updated ? Will the RollBack affect all the tables ?

For example, MySql seems not able to rollback a multiple-table transaction http://stackoverflow.com/questions/3094398, while MSSql seems capable http://social.msdn.microsoft.com/Forums … ransactsql .

Last edited by ComingNine (2014-05-08 15:10:30)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Newbie question of using TSQLRecordMany.

Yes... it will depend on the database back-end.

ComingNine

Member

Registered: 2010-07-29

Posts: 294

Re: Newbie question of using TSQLRecordMany.

Thank you for your comment !

If the internal-mode SQLite is used (i.e. TSQLRestServerDB), can RollBack affect all three (Source, Dest, Pivot) tables ?

Furthermore, could you help to comment whether these three are equivalent when all three tables are being updated ? TransactionBegin (TSQLSource), TransactionBegin (TSQLDest), TransactionBegin (TSQLDestPivot) ?

Last edited by ComingNine (2014-05-08 17:30:24)